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

Trong Excel nâng cao sẽ kết hợp hàm SUM và LOOKUP

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:

Trong Excel nâng cao, sử dụng kết hợp VLOOKUP và SUMIF

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,…

Hyperlink trong Word là những liên kết đến một trang web hay một thư mục nào đó rất hữu ích trong việc tìm kiếm và kết nối thông tin. Tuy nhiên, đôi lúc người dùng sẽ không cần đến những liên kết này và muốn xóa nó đi. Vậy phải…Xem chi tiết: Hướng dẫn cách xóa Hyperlinks trong Word cực đơn giản
Ta biết đến Excel với trang tính có chứa rất nhiều công thức, hàm khác nhau thì ít ai biết rằng trong Word cũng có thể thao tác các hàm cụ thể để người dùng sử dụng. Vậy Word cung cấp những hàm nào và có thông dụng như Excel?…Xem chi tiết: Hướng dẫn cách tạo văn bản ngẫu nhiên trong Word
Bạn đang muốn lưu tài liệu word của mình dưới dạng trang web nhưng chưa biết làm cách nào? Vậy thì bài viết dưới đây sẽ giúp bạn trả lời chi tiết nhất! CÁCH LƯU TÀI LIỆU DƯỚI DẠNG TRANG WEB Lựa chọn tốt nhất để lưu tài liệu dưới…Xem chi tiết: Hướng dẫn cách lưu tài liệu dưới dạng trang web
Trong lúc soạn thảo văn bản trên Word, bạn luôn muốn biết đoạn văn bản của mình đã được bao nhiêu từ. Thao tác thực hiện rất đơn giản, chỉ cần mở bảng thống kê là kiểm tra được. Hãy cùng tìm hiểu chi tiết cách thực hiện trong bài…Xem chi tiết: Hướng dẫn mở bảng thống kê chữ trong Word
Trong quá trình thao tác với Word, bạn sẽ không thể không dùng đến dấu đóng mở ngoặc đơn để mô tả chi tiết thêm cho một vấn đề nào đó hay là một câu ghi chú,.... Tuy nhiên, nếu trong trường hợp bạn muốn xóa bỏ tất cả các…Xem chi tiết: Hướng dẫn xóa bỏ phần văn bản nằm trong dấu ngoặc đơn
Được đăng trong Kiến thức.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *