Hàm tìm kiếm và tính tổng trong Excel: VLOOKUP, SUM và SUMIF

Bài viết dưới đây chúng ta sẽ cùng nhau tìm hiểu chi tiết về cách sử dụng các hàm tìm kiếm và tính tổng trong Excel: VLOOKUP, SUM và SUMIF.

KẾT HỢP HÀM VLOOKUP VÀ SUMIF

Chúng ta bắt đầu với ví dụ đầu tiên. Yêu cầu của ví dụ này đó chính là thực hiện tính tổng sản lượng của Cam trong tháng 1,2,3 bằng cách dùng công thức sau đây: =SUM(VLOOKUP( A2 , A1:I8 , {2,3,4} , FALSE )) Sau khi bạn đã thực hiện nhập công thức này, hãy lưu ý rằng vì đây là công thức mảng, nên sau khi tiến hành nhập công thức, bạn cần sử dụng tổ hợp phím CTRL + SHIFT + ENTER để có thể thực hiện nhập được công thức và cho ra kết quả đúng nhất. Để bạn có thể hiểu hơn, ta sẽ cùng nhau phân tích công thức mảng này bằng việc đưa ra 3 công thức tương đương như sau:

=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 công thức mảng phía trên có nghĩa là cột 2,3,4 trong mảng dữ liệu A1:I8. Chắc hẳn khi đọc đến đây các bạn có thể sẽ đặt ra câu hỏi, vì sao ta lại không sử dụng công thức sau đây để tính tổng

=SUM(B2:D2)

Lí giải về thắc mắc này như sau, mục đích đưa ra các công thức phức tạp này là để chúng ta sẽ phục vụ cho việc tạo báo cáo hoặc dashboard. Nếu như bạn dùng một công thức để tính tổng đơn thuần như là hàm SUM thì khi bạn có 1 ô chứa các loại hàng hóa và muốn thay đổi ô này để tiến hành tính tổng sản lượng cho 1 sản phẩm thì sẽ rất mất thời gian cho việc thay đổi công thức SUM theo. 

Vì thế, khi sử dụng công thức mảng kết hợp SUM và VLOOKUP, bạn sẽ dễ dàng tạo ra một báo cáo về sản lượng cho các sản phẩm như hình minh họa bên dưới đây: Liên hệ ví dụ ở trên, bạn có thể thực hiện thay thế hàm SUM bằng 1 số hàm khác bất cứ khi nào để phục vụ mục đích nhất định của bạn như hàm MIN, MAX, AVERAGE hay các phép toán khác. Hãy lưu ý rằng các công thức này đều sẽ là công thức mảng nên bạn vẫn luôn cần dùng đến tổ hợp phím CTRL + SHIFT + ENTER khi thực hiện nhập công thức này trong Excel.

TỔNG HỢP DỮ LIỆU KHÔNG THÊM CỘT PHỤ, KẾT HỢP HÀM LOOKUP VÀ SUM:

Ta có ví dụ như sau, có 2 bảng dữ liệu, trong đó bảng thứ nhất gồm có sản phẩm và đơn giá và bảng thứ 2 bao gồm khách hàng, sản phẩm cùng số lượng sản phẩm mà khách hàng đã mua. Bạn đang muốn đi tính tổng giá trị của 1 khách hàng

Bình thường, bạn sẽ cần dùng cột phụ như sau: Tham khảo các công thức bên dưới:

G2=VLOOKUP(E2,bang_SP,2,FALSE) H2=F2*G2 K2=SUMIF(D:D,K1,H:H)

Đối với trường hợp không thêm được cột phụ, bạn có thể sử dụng công thức sau tại K2:

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ú ý rằng khi dùng công thức kết hợp SUM và LOOKUP này:

1. Cột A lúc này phải được sắp xếp theo thứ tự tăng dần hoặc là từ A đến Z, để hàm LOOKUP có thể cho ta được giá trị đúng.
2. Đảm bảo rằng công thức được nhập vào bằng việc dùng tổ hợp phím CTRL + SHIFT + ENTER. Nếu như không muốn sử dụng tổ hợp phím này, bạn cũng có thể thay hàm SUM bằng hàm SUMPRODUCT

Công thức này được hiểu như thế nào?

1. Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) sẽ cho ta kết quả như là cột đơn giá trong hình chụp phía trên
2. Phần $F$2:$F$8: mảng số lượng các sản phẩm
3. Phần ($D$2:$D$8=K1) tạo ra 1 mảng bao gồm các giá trị đúng và sai, khi thực hiện lấy mảng này nhân với 1 số, quy tắc sau đây sẽ được áp dụng: (TRUE được định nghĩa là 1, FALSE được định nghĩa là 0)
4. Cuối cùng, hàm SUM sẽ tiến hành 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ụ sau đây:

Tại ví dụ này, ta sẽ có 2 bảng, 1 bảng sẽ là tên Telesale và ID của họ và 1 bảng khác chỉ có ID và doanh số. Nhiệm vụ của bạn ở ví dụ này đó là cần đi tính tổng doanh số của bất kì Telesale nào dựa trên tên của họ. Công thức được sử dụng ở đây sẽ là

=SUMIF(D:D,VLOOKUP(H1,A1:B8,2,FALSE),E:E)

VLOOKUP lúc này sẽ có nhiệm vụ đưa lại mã ID của Telesale với tên tương ứng, rồi sau đó dựa vào ID này ta áp dụng hàm SUMIF như một ví dụ đơn giản bình thường.

TÍNH TỔNG NHIỀU ĐIỀU KIỆN Ở CÙNG 1 CỘT:

Ta có ví dụ tính tổng sau đây, trong đó điều kiện tính tổng đều nằm ở 1 cột: Thực hiện tính tổng của các giao dịch với đầu mã là 111 và 131 Để có thể làm được điều này, ta có 3 cách tính. Trong đó với cách mà công thức có dấu {}, tức là công thức mảng, bạn cần nhớ nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau khi đã nhập công thức để được kết quả

=SUMIF(A:A,”111*”,C:C)+SUMIF(A:A,”131*”,C:C)

{=SUM(SUMIF(A:A,{“111*”,”131*”},C:C))}

{=SUM(C2:C14*(–(LEFT(A2:A14,3)={“111″,”131”})))}

Vậy là chúng ta đã vừa cùng nhau tìm hiểu về một số cách để kết hợp công thức, các hàm tính tổng cùng với những hàm dò tìm, từ đó giải quyết được các nhu cầu làm báo cáo nhanh chóng, dễ dàng mà không cần dùng đến cột phụ.

Hi vọng những thông tin chia sẻ trong bài viết trên đây sẽ giúp ích được cho bạn nhiều trong công việc, đặc biệt là lĩnh vực kế toán, lập báo cáo trong kho,…