- Kết hợp hàm VLOOKUP và SUMIF
- Tổng hợp dữ liệu không thêm cột phụ, kết hợp hàm LOOKUP và SUM
- Kết hợp hàm VLOOKUP và SUMIF tra cứu và tổng hợp dữ liệu theo điều kiện
- Tính tổng nhiều điều kiện ở cùng 1 cột
Kết hợp hàm VLOOKUP và SUMIF
Trước khi bắt đầu vào bài này, chắc chắn các bạn đã thành thạo hàm VLOOKUP và hàm SUMIF rồi. Do đó, chúng ta có thể bắt đầu ngay ví dụ đầu tiên. Trong ví dụ này, chúng tôi sẽ tính tổng sản lượng của Cam trong tháng 1, tháng 2, tháng 3 bằng cách sử dụng công thức sau đây=SUM[VLOOKUP[ A2 , A1:I8 , {2,3,4} , FALSE ]]
Sau khi nhập công thức này, các bạn lưu ý vì đây là công thức an ninh, vì vậy sau khi nhập công thức, bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER
để có thể nhập công thức và có kết quả đúng. Để các bạn có thể hiểu rõ hơn, chúng tôi sẽ phân tích công thức mảng này bằng cách đưa ra 3 công thức tương đương sau đây. =VLOOKUP[ A2 , A1:I8 , 2 , FALSE ] + VLOOKUP[ A2 , A1:I8 , 3 , FALSE ] + VLOOKUP[ A2 , A1:I8 , 4 , FALSE ]
Phần {2,3,4} nằm trong mảng công thức theo nghĩa là cột 2,3,4 trong mảng dữ liệu A1. I8. Đọc đến đây, có thể các bạn sẽ đặt câu hỏi, tại sao chúng ta không sử dụng công thức sau đây để tính tổng=SUM[B2:D2]
Mục đích đưa ra những công thức phức tạp này, chúng ta sẽ phục vụ cho việc tạo báo cáo hoặc bảng điều khiển. Nếu chúng ta sử dụng một công thức tính tổng đơn thuần bằng hàm SUM, khi chúng ta có 1 ô chứa các loại hàng hoá, muốn thay đổi ô này để tính tổng sản lượng của 1 sản phẩm, chúng ta phải thay đổi công thức . Với công thức mảng kết hợp hàm SUM và VLOOKUP như ở trên, chúng ta có thể tạo ra 1 báo cáo về sản lượng của các sản phẩm 1 cách nhanh chóng như sau. Tất nhiên, từ ví dụ đưa ra trên, ta có thể thay hàm SUM bằng 1 số hàm khác để phục vụ mục tiêu đích của chúng ta như hàm AVERAGE, hàm MIN, hàm MAX hoặc thực hiện các phép tính toán khác … Và lưu ý vì Tổng hợp dữ liệu không thêm cột phụ, kết hợp hàm LOOKUP và SUM
Chúng ta có ví dụ sau, trong ví dụ này, chúng ta có 2 bảng dữ liệu, 1 bảng bao gồm sản phẩm và đơn giá; . Chúng ta sẽ muốn tính tổng giá trị của 1 khách hàng Excel nâng cao, kết hợp hàm SUM và LOOKUPBình thường, chúng ta sẽ cần sử dụng các cột phụ như sau. Các công thức như sauG2=VLOOKUP[E2,bang_SP,2,FALSE]
H2=F2*G2
K2=SUMIF[D:D,K1,H:H]
K2=SUM[LOOKUP[$E$2:$E$8,$A$2:$A$8,$B$2:$B$8]*$F$2:$F$8*[$D$2:$D$8=K1]]
Chú ý khi sử dụng công thức kết hợp SUM và LOOKUP này- Cột A phải được sắp xếp theo thứ tự tăng dần hoặc từ A đến Z, để hàm LOOKUP cho chúng ta giá trị đúng
- Công thức được nhập bằng tổ hợp phím CTRL + SHIFT + ENTER Nếu không sử dụng tổ hợp phím này, bạn có thể thay hàm SUM bằng hàm SUMPRODUCT
- Phần
LOOKUP[$E$2:$E$8,$A$2:$A$8,$B$2:$B$8]
cho chúng ta kết quả là cột đơn giá trong ảnh chụp phía trên - Phần
$F$2:$F$8
là mảng số lượng các sản phẩm - Phần
CTRL + SHIFT + ENTER
0 tạo ra 1 mảng bao gồm các giá trị đúng và sai, khi lấy mảng này nhân với 1 số, thì quy tắc sau đây được áp dụng. [TRUE được định nghĩa là 1, FALSE được định nghĩa là 0] - Và cuối cùng, hàm SUM sẽ tính tổng và cho ta kết quả cuối cùng
Kết hợp hàm VLOOKUP và SUMIF tra cứu và tổng hợp dữ liệu theo điều kiện
Ví dụ. Excel nâng cao, kết hợp VLOOKUP và SUMIFTrong ví dụ này, ta có 2 bảng, 1 bảng là tên Telesale và ID của họ, 1 bảng khác chỉ có ID và doanh số. Nhiệm vụ trong ví dụ này. Cần tính toán tổng doanh thu của các Telesale bất kỳ dựa trên tên của họ. Công thức chúng ta sẽ sử dụng ở đây làCTRL + SHIFT + ENTER
1
VLOOKUP sẽ có nhiệm vụ gửi lại ID mã của Telesale với tên tương ứng, dựa trên ID này, chúng tôi áp dụng SUMIF như một ví dụ đơn giản thông thườngTính tổng nhiều điều kiện ở cùng 1 cột
Chúng ta có ví dụ tính tổng sau đây, điều kiện tính tổng năm ở 1 cột. Tính tổng của các giao dịch với mã đầu là 111 và 131 Để làm được điều này, chúng ta có 3 cách tính, với cách mà công thức có dấu {}, nghĩa là công thức mảng, bạn cần nhấn tổ hợp phím tắt CTRL . A,”111*”,C. C]+SUMIF[A. A,”131*”,C. C]{=SUM[SUMIF[A. A,{“111*”,”131*”},C. C]]}{=SUM[C2. C14*[–[TRÁI[A2. A14,3]={“111″,”131”}]]]} Hậu quả là trong bài này, chúng ta đã cùng nhau tìm hiểu một số cách kết hợp công thức, hàm tính tổng với các hàm tìm kiếm để giải quyết . Hi vọng bài viết có ích cho công việc của các bạn đặc biệt là trong công việc kế toán, lập báo cáo trong kho. Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, công cụ khác của Excel. Một số hàm cơ bản thường gặp như- SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
- COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
- Các hàm xử lý dữ liệu dạng chuỗi, dạng tháng, dạng số…
- Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
- Định dạng theo điều kiện với Định dạng có điều kiện
- Thiết lập điều kiện nhập dữ liệu với Xác thực dữ liệu
- Cách đặt Tên và sử dụng Tên trong công thức
- Lập báo cáo với Pivot Table…
Các ví dụ Excel nâng cao. kết hợp VLOOKUP, SUM và SUMIF
Cách sử dụng hàm Sumif kết hợp hàm vlookup
Để giúp chúng ta dễ hình dung được khi nào thì sử dụng hàm Sumif kết hợp hàm vlookup, hãy xem ví dụ sau đây. Cách sử dụng hàm Sumif kết hợp hàm vlookup Chúng ta có 1 bảng dữ liệu từ A1. C15, bao gồm các cột Mã hàng, Ngày bán, Số lượng bán được của từng mặt hàng. Tuy nhiên, yêu cầu của chúng ta là tính tổng số lượng bán theo Tên mặt hàng được chọn trong ô F11. In data table A1. C15 không có cột tên mặt hàng. Muốn biết Tên mặt hàng đối ứng với Mã hàng nào, chúng ta phải tham khảo trong bảng E1. F8. such- Yêu cầu tính tổng theo điều kiện => Sử dụng hàm SUMIF để xác định kết quả
- Điều kiện không xác định được 1 cách trực tiếp mà phải tham chiếu tới 1 bảng khác => Sử dụng VLOOKUP xác định điều kiện
Viết hàm SUMIF
Constructor of function SUMIF bao gồm.CTRL + SHIFT + ENTER
2- Phạm vi. vùng chứa điều kiện. At in the A1 table. C15 chỉ có cột Mã hàng là có liên quan đến điều kiện => Chọn vùng A2. A15 [không chọn dòng tiêu đề]
- Tiêu chuẩn. điều kiện của đề bài là Tên hàng. Nhưng Range lại là Mã hàng. Do đó phải tham chiếu ra Mã hàng dựa vào Tên hàng => Sử dụng hàm VLOOKUP
- tổng_dải. kết quả cần tính là Number. Chọn vùng C2 đó. C15
Viết hàm VLOOKUP
Chúng ta muốn tham khảo Mã hàng dựa vào Tên hàng, do đó hàm VLOOKUP sẽ viết như sau. =VLOOKUP[giá trị tìm kiếm, vùng tham chiếu, cột chứa kết quả, phương thức tìm kiếm]- Search value. is name row at ô F11
- Tham chiếu khu vực. Vùng bảng E2. F8 [có thể bỏ qua dòng tiêu đề]
- Cột chứa kết quả. column column is the second column
- Phương thức tìm kiếm. tìm kiếm chính xác theo tên hàng => nhập số 0
CTRL + SHIFT + ENTER
3
Hướng dẫn cách sử dụng hàm Sumifs kết hợp hàm vlookup trong Excel
Hàm SUMIFS là một hàm rất hữu ích trong Excel giúp chúng ta có thể tính tổng theo nhiều điều kiện cùng lúc. Nhưng trong một số trường hợp điều kiện của báo cáo lại không xác định được một cách trực tiếp mà phải tham chiếu đến 1 vùng dữ liệu khác. Khi đó chúng ta cần phải kết hợp hàm SUMIFS với hàm VLOOKUP. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn biết sử dụng hàm Sumifs kết hợp hàm vlookup trong Excel. Trước tiên hãy thử ví dụ sau đây. Trong yêu cầu trên, chúng ta thấy có tới 3 điều kiện.- Điều kiện 1. Từ ngày 01/06/2018
- Điều kiện 2. Đến ngày 30/06/2018
- Điều kiện 3. Tên hàng là Mận Hà Giang loại 2 [tương ứng với mã là M102 trong bảng E1. F8]
Cách viết hàm SUMIFS
Cấu trúc hàm SUMIFS với 3 điều kiện bao gồm.CTRL + SHIFT + ENTER
4- tổng vùng tính toán. Tính tổng số lượng, do đó lấy theo vùng C2. C15
- Zone điều kiện thứ 1. cột Ngày [vì chứa điều kiện 1 là ngày] vùng B2. B15
- Điều kiện thứ 1. Từ ngày 01/06/2018 is value at ô F11. Tuy nhiên từ ngày được hiểu là >=, do cách viết đó là “>=”&F11
- Zone điều kiện thứ 2. cột Ngày [vì chứa điều kiện 2 là ngày] vùng B2. B15
- Điều kiện thứ 2. Đến ngày 30/06/2018 is value at ô F12. Tuy nhiên từ ngày được hiểu là