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

Khi làm việc, soạn thảo với nhiều nội dùng, bạn sẽ gặp khó khăn và tốn thời gian trong việc tìm kiếm một từ hay cụm từ nào đó. Vậy làm thế nào để có thể tìm kiếm nhanh chóng một từ khóa hay một chuỗi ký tự nào đó…Xem chi tiết: Hướng dẫn cách tìm và thay thế trong Word
Hiện nay, việc nộp thuế qua mạng đã trở nên rất phổ biến bởi sự tiện lợi và nhanh chóng hơn rất nhiều so với cách nộp truyền thống. Bài viết dưới đây chúng tôi sẽ hướng dẫn nộp thuế điện tử chi tiết nhất. Bạn sẽ thực hiện theo…Xem chi tiết: Hướng dẫn nộp thuế điện tử chi tiết nhất
Trong soạn thảo văn bản trên Word, việc định dạng cỡ chữ là điều cần thiết để thu hút sự chú ý của người đọc vào các phần người viết mong muốn cũng như đánh dấu các ý chính, đề mục,... Bài viết dưới đây sẽ hướng dẫn bạn cách…Xem chi tiết: Hướng dẫn định dạng cỡ chữ trong văn bản Word
Khi trình bày văn bản trong Word, đặc biệt là loại văn bản dài trên nhiều trang giấy thì không thể nào thiếu việc đánh số trang trong Word. Điều này giúp cho việc theo dõi trình tự các trang khi in được chính xác, đúng theo thứ tự đã…Xem chi tiết: Hướng dẫn cách đánh dấu trang trong Word 2010 2013 2016
Biết cách căn chỉnh lề trong Word là kĩ năng không thể thiếu đối với một nhân viên văn phòng, học sinh, sinh viên để thực hiện in tài liệu, báo cáo, luận văn,... Bạn đã thực sự biết căn chỉnh lề trong Word đúng cách chưa? Nếu chưa thì đừng…Xem chi tiết: Hướng dẫn cách căn chỉnh lề trong Word 2010 2013 2016
Đượ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 *