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

Gitiho Learning 3
14 Jan 2021

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ể.

Cách tính tổng với nhiều điều kiện chỉ trên một cột duy nhất.

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 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.

KTG04 - Nguyên lý kế toán cho người mới bắt đầu

Ứng dụng 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.

Dùng cả hai 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,

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 LOOKUP với nhau.

CHỈ 7 GIỜ HỌC BÀI BẢN, TIẾP KIỆM HÀNG CHỤC NGHÌN GIỜ TRA CỨU

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.

EXG01 - Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

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ư:

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

- COUNTIFCOUNTIFS để 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. 

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..

Bạn đọc cũng có thể đọc thêm các bài viết bổ ích khác của Gitiho.com  như:

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ

@ 2020 - Bản quyền của Công ty TNHH Công Nghệ Giáo Dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội