Tổng hợp những cách kết hợp của hàm VLOOKUP, SUM và SUMIF.

Bài viết này Gitiho.com sẽ cùng các bạn tộng hợp lại những cách để kết hợp ba loại hàm VLOOKUP, SUM và SUMIF để tìm và tính tổng dựa trên một số điều kiện cụ thể.

Xem thêm: Hướng dẫn cách dùng hàm vlookup trong Excel đơn giản dễ hiểu

Cách tính tổng với nhiều điều kiện chỉ trên một cột duy nhất bằng cách kết hợp hàm VLOOKUP, SUM và SUMIF.

Với bảng tính dưới đây, nhiệm vụ của chúng ta là tính tổng đều nằm trên một cột. Hay cụ thể là tính tổng của các giao dịch với đầu mã 111 và 131.

Sẽ có 3 cách để chúng ta giải được bài toán này. Với cách tín mà công thức chứa dấu {} thì đồng nghĩa đây là công thức mảng. Để ra được công thức, bạn sẽ cần nhấn tổ hợp phím CTRL + SHIFT + ENTER.

3 công thức này gồm.

=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”})))}

Kết hợp hàm VLOOKUP với SUMIF.

Trong ví dụ dưới đây, ta sẽ cần tính tổng sản lượng tháng 1 - 2 - 3 của mặt hàng Cam.

Để giải ví dụ này, ta sẽ cần công thức sau.

=SUM(VLOOKUP( A2 , A1:I8 , {2,3,4} , FALSE ))

Lưu ý: giống như ví dụ về tính tổng nhiều điều kiện trong một cột, vì đây là công thức mảng nên chúng ta bắt buộc phải sử dụng tổ hợp Ctrl + Enter + Shift để nhập công thức và ra kết quả đúng.

Nếu bạn chưa hiểu rõ, Gitiho.com sẽ cùng bạn phân tích công thức mảng này với 3 công thức tương tự.

=VLOOKUP( A2 , A1:I8 , 2 , FALSE ) + VLOOKUP( A2 , A1:I8 , 3 , FALSE ) + VLOOKUP( A2 , A1:I8 , 4 , FALSE )

{2,3,4} ở đây tương ứng với cột 2,3 và 34 trong phạm vi dữ liệu A1:I8.  

Tuy nhiên, vẫn nhiều bạn thắc mắc sao ta không dùng ngay công thức =SUM(B2:D2) để tính tổng. Mục đích của công thức "dài dòng" trên là bởi nó không chỉ cho ra kết quả mà còn phục vụ cho việc tạo báo cáo hoặc dashboard sau này, Nếu ta chỉ sử dụng một công thức tính tổng duy nhất bằng hàm SUM thì khi chúng ta có một ô bao gồm các loại hàng hóa mà muốn thay đổi ô này để tính tổng của một sản phẩm khác thì buộc ta phải đổi cả công thức SUM theo.

Sau đây là hình ảnh kết quả khi kết hợp hàm SUM cùng VLOOKUP như trên.

Ngoài ra, các bạn cũng có thể thay hàm SUM bằng hàm khác để linh hoạt trong công việc. Những hàm có thể thay thế gồm AVERAGE, MIN, MAX hay thậm chí là thực hiện các phép tính hoàn toàn khác. Điều quan trọng ở đây là bạn cần nhớ tổ hợp Ctrl + Shift + Enter mỗi khi muốn áp dụng công thức mảng.

Xem thêm: Cách dùng hàm SUMIFS kết hợp VLOOKUP trong Excel

Kết hợp hàm VLOOKUP và SUMIF để tổng hợp dữ liệu và tìm kiến dữ liệu theo điều kiện.

Ta có ví dụ sau: 2 bảng này gồm một bảng có tên Telesale và một bảng chứa ID của nhân viên, bảng còn lại chỉ có ID và doanh số. Công việc của chúng ta là tính tổng doanh số của bất kì Telesale nào chỉ dựa vào tên của họ.

 

Đây sẽ là công thức ta cần áp dụng.

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

Hàm VLOOKUP sẽ dựa trên mã ID của Telesale với tên tương ứng. Với dòng ID này thì ta áp dụng SUMIF như bình thường.

Kết hợp hàm LOOKUP và SUM để tổng hợp dữ liệu mà không cần thêm cột phụ.

Chúng ta sẽ có trường hợp giả định như sau: sẽ có 2 bảng dữ liệu, bảng thứ nhất có sản phẩm và đơn giá. Bảng thứ hai sẽ có dữ liệu của khách hàng, sản phẩm và số lượng sản phẩm mà khách đã mua. Nhiệm vụ của chúng ta là tính tổng giá trị của một khách hàng bất kỳ.

Với những cách thông thường ta sẽ phải dùng đến cột phụ.

Tới đây ta sẽ có những công thức sau để áp dụng cách thức kết hợp hàm LOOKUP và SUM

G2=VLOOKUP(E2,bang_SP,2,FALSE)

H2=F2*G2

K2=SUMIF(D:D,K1,H:H)

Với trường hợp không thể thêm được cột phụ thì ta dùng công thức sau.

K2=SUM(LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8)*$F$2:$F$8*($D$2:$D$8=K1))

Ta sẽ cùng phân tích công thức này.

1. LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) : mảng này sẽ cho ra kết quả như cột đơn giá trong bức hình trên.

2. $F$2:$F$8 : mảng này gồm số lượng các sản phẩm.

3. ($D$2:$D$8=K1) : mảng này tạo ra một mảng mang các giá trị TRUE và FALSE (1 là TRUE mà 0 là FALSE). Khi lấy mảng này nhân với một số thì quy tắc sẽ được áp dụng

4. SUM sẽ tính tổng cùng với đó là đưa ra kết quả cuối.

Sau đây là một số lưu ý khi kết hợp SUM và LOOKUP với nhau.

1. Bắt buộc phải nhập bằng tổ hợp Ctrl + Shift + Enter. Nếu không muốn sử dụng tổ hợp này thì bạn có thể thay bằng hàm SUM bằng SUMPRODUCT.

2. Cột phải được xếp thứ tự từ A đến Z để hàm LOOKUP cho ra kết quả chính xác nhất.

Bên cạnh đó ta cũng nên biết thêm một số hàm cơ bản thường gặp như:

- SUMIF và SUMIFS để tính tổng theo một và nhiều điều kiện

- COUNTIF và COUNTIFS để phục vụ cho việc thống kê, đếm một điều kiện hoặc nhiều điều kiện.

- Các hàm xử lý dữ liệu bằng dạng chuỗi, dạng thời gian (ngày/tháng/năm), dạng số,...

- Các  hàm dò tìm tham chiếu như INDEX + MATCH, SUMPRODUCT,...

Cùng với đó là những công cụ hữu ích như:

- Conditional Fomarting để định dạng điều kiện

- Pivot Table để lập báo cáo.

- Data Validation để thiết lập điều kiện dữ liệu

- Cách đặt Name và dùng Name trong công thức.

Xem thêm: Cách sử dụng hàm VLOOKUP trong Google Sheets và Excel với các ví dụ cụ thể

Kết luận

Hi vọng bài viết này sẽ đem đến sự nhanh chóng và chính xác trong công việc, nhất là đối với những người đang làm việc ở những vị trí như kế toán hay quản lý kho..