Hướng dẫn, bài tập kết hợp hàm VLOOKUP và SUM siêu dễ 2022

Hướng dẫn thủ thuật Excel kết hợp hàm vlookup và sum – Với những ai thường xuyên xử lý bảng dữ liệu Excel và tính toán các số liệu, thì chắc chắn sẽ biết đến 2 hàm Excel phổ biến là Sum và hàm Vlookup. Hàm Sumif dùng để tính tổng các giá trị, còn hàm Vlookup để tìm kiếm giá trị trong mảng. Và việc kết hợp 2 hàm Sumif và hàm Vlookup trong Excel cùng được áp dụng rất nhiều.

Bạn đang xem bài viết: Hướng dẫn, bài tập kết hợp hàm VLOOKUP và SUM siêu dễ 2022

Hàm VLOOKUP là hàm gì?

Khác với Hàm Sum, hàm Vlookup không tính toán bất cứ một dữ liệu nào mà chỉ Lựa chọn dữ liệu đấy. Hàm cho phép người sử dụng tra cứu dữ liệu trên một chuối cụ thể theo các điều kiện đề ra sao cho kết quả hiển thị thỏa mãn, thông thường hàm Vlookup trong Excel hay được sử dụng để tra cứu những loại mã số như mã học sinh, mã nhân viên thuận tiện cho việc truy xuất dữ liệu.

công thức hàm Vlookup: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Lookup_value: giá trị dùng để dò tìm
Table_array: Bảng thành quả dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4)
Col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.
Range_lookup: Phạm vi Lựa chọn, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).

Hàm SUM là hàm gì?

Hàm Sum là hàm mà ta có thể dùng tính tổng các giá trị của một vùng dữ liệu nào đó

Cú pháp:

=SUM(num1,num2, ... num_n)

Cách kết hợp hàm Vlookup và Sum dễ nhất 2022 trong phần mềm Google Sheet

Cách kết hợp 2 hàm Vlookup và Sum như sau:

Trong đó:

VD minh họa: tích hợp hàm SUM và hàm VLOOKUP trong Google Sheet để tính tổng sản lượng desktop.

công thức:

trong số đó:

tích hợp hàm SUM và hàm VLOOKUP trong Google Sheet để tính tổng sản lượng sản phẩm desktop

Hàm Vlookup và hàm SUM trong Excel – Tính tổng của các giá trị trùng khớp

nếu như bạn thao tác dữ liệu số trên Excel, thì bình thường bạn không phải chỉ xuất các dữ liệu ảnh hưởng từ một bảng khác mà còn tính tổng các con số từ một số cột hay hàng. để thực hiện thao tác này, chúng ta có thể tích hợp hàm VLOOKUP với hàm SUM.

Giả sử, bạn có danh sách các sản phẩm có đính doanh số trong vài tháng, một cột là một tháng.

DỮ LIỆU NGUỒN – DOANH SỐ HÀNG THÁNG

Bây giờ, bạn mong muốn lập bảng tính tổng về tổng doanh số của mỗi sản phẩm.

phương án là sử dụng mảng trong thông số thứ ba (col_index_num) của hàm VLOOKUP trong Excel. đây chính là công thức hàm VLOOKUP mẫu:

=SUM(VLOOKUP(lookup_value, lookup_range, 2,3,4, FALSE))

Như chúng ta có thể thấy, con người sử dụng mảng 2,3,4 trong câu lệnh thứ ba để hiển thị vài giá trị cần tìm trong cùng công thức VLOOKUP để tính tổng các thành quả ở cột 2, 3, và 4.

Và bây giờ, hãy Điều chỉnh sự kết hợp của hàm VLOOKUP và hàm SUM so với dữ liệu của chúng ta để tính tổng toàn bộ doanh số từ cột B đến cột M trong bảng trên:

=SUM(VLOOKUP(B2, ‘Doanh số hàng tháng’!$A$2:$M$9, 2,3,4,5,6,7,8,9,10,11,12,13,FALSE))

Quan trọng! Vì bạn đang tạo một bí quyết mảng, hãy chắc rằng bạn nhấp Ctrl+Shift+Enter thay vì chỉ nhấn mỗi Enter khi mà bạn gõ xong. khi bạn thực hiện xong, Microsoft Excel sẽ đóng khung bí quyết của bạn trong dấu ngoặc nhọn như thế này:

=SUM(VLOOKUP(B2, ‘Doanh số hàng tháng’!$A$2:$M$9, 2,3,4,5,6,7,8,9,10,11,12,13,FALSE))

nếu như bạn nhấp Enter, thì chỉ có giá tị trước tiên trong mảng được xử lý, việc này sẽ cho kết quả sai.

Lưu ý về cách kết hợp hàm VLOOKUP và SUM

Bạn có thể tò mò là tại sao bí quyết lại hiển thị [@Product] như là thành quả cần tìm trong ảnh chụp màn hình trên. đấy là do tôi đã đổi dữ liệu của mình thành bảng (Insert tab>Table). Tôi cảm thấy rất thuận tiện khi thực hành các bước với bảng Excel có được đầy đủ công dụng hơn là các dải ô. ví dụ, khi mà bạn gõ công thức vào một ô, Excel sẽ tự động sao chép nó cho toàn bộ cột và trong cách này, nó sẽ giúp bạn tiết kiệm vài giây quý báu.

Như chúng ta có thể thấy, việc dùng hàm VLOOKUP cùng với hàm SUM rất dễ. tuy nhiên, đây không hẳn là phương án hoàn hảo, đặc biệt khi mà bạn đang thao tác với bảng lớn. Điểm quan trọng nhất ở Nó là việc sử dụng công thức mảng có thể gây tác động ngược lại hoạt động của sổ thực hiện công việc vì mỗi thành quả trong mảng cần một hàm VLOOKUP riêng biệt. bởi vậy, càng có nhiều giá trị trong mảng thì càng có nhiều bí quyết mảng trong sổ thực hiện công việc, Excel sẽ chạy càng chậm.

Bạn có thể loại bỏ vấn đề này bằng cách sử dụng hàm INDEX và hàm MATCH thay vì hàm VLOOKUP và hàm SUM, và tôi sẽ trao cho bạn vài VD bài tập ngay bên dưới

Hướng dẫn kết hợp hàm VLOOKUP và SUM dễ nhất 2022

con người bắt tay vào làm với VD đầu tiên. yêu cầu của ví dụ này đấ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 bí quyết sau đây: =SUM(VLOOKUP( A2 , A1:I8 , 2,3,4 , FALSE ))

Sau khi chúng ta đã thực hiện nhập công thức này, hãy chú ý rằng vì Nó là công thức mảng, nên một khi tiến hành nhập bí quyết, bạn phải cần 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 thông qua việc nói 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 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, tại 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 tiêu đưa ra các bí quyết 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 bạn sử dụng một bí quyết để tính tổng đơn thuần như là hàm SUM thì khi mà bạn có 1 ô chứa các kiểu hàng hóa và muốn chỉnh sửa ô 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.

bởi vậy, khi sử dụng công thức mảng tích hợp SUM và VLOOKUP, bạn có thể đơn giản làm ra một báo cáo về sản lượng cho các sản phẩm như hình ảnh minh họa phía dưới đây:  liên hệ VD ở 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 cụ thể của chúng ta như hàm MIN, MAX, AVERAGE hay các phép toán khác. Hãy chú ý rằng các bí quyết này đều sẽ là bí quyết mảng nên bạn vẫn luôn cần sử dụng đến tổ hợp phím CTRL + SHIFT + ENTER khi thực hiện nhập bí quyết 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 siêu dễ 2022

Ta có VD như sau, có 2 bảng dữ liệu, trong số đó bảng thứ nhất gồm có sản phẩm và đơn giá và bảng thứ 2 gồm có người mua hàng, sản phẩm cùng số lượng hàng hóa mà người mua hàng đã mua. Bạn đang cầ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)

so với hoàn cảnh không thêm được cột phụ, bạn có thể trải nghiệm 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 sử 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 thành quả đúng.
2. cam kết rằng bí quyết được nhập vào thông qua việc dùng tổ hợp phím CTRL + SHIFT + ENTER. nếu không muốn sử dụng tổ hợp phím này, bạn cũng có khả năng thay hàm SUM bằng hàm SUMPRODUCT

bí quyết 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 hàng hóa
3. Phần ($D$2:$D$8=K1) tạo ra 1 mảng gồm có các thành quả đú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 có thể được áp dụng: (TRUE được khái niệm là 1, FALSE được khái niệm 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

Các lỗi thường gặp khi kết hợp hàm VLOOKUP và SUM cần biết

Lỗi #N/A

Cần lưu ý khi làm thủ thuật Excel này là lỗi #N/A xuất hiện khi hàm không tìm thấy dữ liệu cần tìm mà công thức đòi hỏi, công thức bạn dùng đang chứa đối tượng mục tiêu cần dò tìm, tham chiếu vẫn chưa có sẵn trong vùng dữ liệu cần tra cứu, dẫn tới không thể tính toán, hoàn thành công thức được. Đây là lỗi thường xảy ra khi sử dụng các hàm dò tìm, tham chiếu.

ví dụ: Lỗi #NA do nhập nhầm đối tượng dò tìm từ A4 – đối tượng mục tiêu dò tìm thành A2 – Ô trống.

Cách khắc phục: Để sửa lỗi này cần nhập đúng giá trị đối tượng mục tiêu dùng để chọn lựa là A4.

Lỗi #N/A xuất hiện khi hàm không tìm thấy dữ liệu cần tìm mà công thức yêu cầu

Lỗi #REF!

Lỗi #REF! Hiển thị khi công thức tham chiếu đến ô không hợp lệ. điều này thường xuyên xảy ra nhất khi các ô được bí quyết tham chiếu bị xóa hoặc bị dán đè.

ví dụ: Dải ô tham chiếu ở công thức là A17:F29 không chứa đủ giới hạn tham chiếu là cột Tháng 6 bởi vậy đã lỗi #REF!.

Cách khắc phục: Khắc phục bằng việc mở rộng giải ô tham chiếu thành A17:G29.

Lỗi #REF! Hiển thị khi công thức tham chiếu đến ô không hợp lệ

Lỗi #ERROR

Lỗi này nghĩa là Google Sheet không hiểu công thức bạn đã nhập vì nó không thể phân tích cú pháp công thức để thực thi.

ví dụ: Hàm phía dưới bị thiếu dấu ; sau A4 nên xảy ra lỗi #ERROR.

Cách khắc phục: Để khắc phục cần thêm dấu ; vào đúng cú pháp =ArrayFormula(SUM(VLOOKUP(A4;A17:G29;2;3;4;5;6;7;FALSE))), nên cẩn thận coi xét kỹ câu lệnh.

Google Sheet không hiểu công thức bạn đã nhập sẽ xuất hiện lỗi #ERROR

Bài tập kết hợp hàm VLOOKUP và hàm SUM

Bài tập VD: Cho bảng dữ liệu ngành hàng 6 tháng đầu năm 2020, tính tổng sản lượng từng loại mặt hàng.

Tính tổng sản lượng từng loại sản phẩm trong bảng dữ liệu sau

Lọc cột Ngành hàng trong bảng đề cho theo thứ tự tăng dần (từ A đến Z)

công thức:

trong đó:

Kết quả sao khi tích hợp hàm SUM và hàm VLOOKUP

Một vài chú ý khi kết hợp hàm VLOOKUP và SUM

– Để bí quyết VLOOKUP hoạt động chính xác thì bạn buộc phải lọc cột cần tìm trong bảng cần tìm theo thứ tự tăng dần (từ A đến Z).

– Vì Nó là bí quyết mảng, vậy nên sau khi nhập công thức, bạn phải dùng tổ hợp phím CTRL + SHIFT + ENTER để có khả năng nhập được công thức và có kết quả đúng.

một vài chú ý khi kết hợp hàm VLOOKUP và SUM

Tổng kết

Cách kết hợp hàm VLOOKUP và SUM từ blogphanmem.vn gửi đến bạn để tìm kiếm dữ liệu không những giúp ta khi nắm vững được các hàm sẽ tiết kiệm hơn mà còn phải dùng tốt cả các công cụ của Excel. Và việc kết hợp này có thể tìm kiếm dữ liệu nhanh hơn và loại bỏ tính chất thủ công rườm rà. Ngoài ra, còn rất nhiều hàm nâng cao khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công cụ như Data validation, Pivot table, Power Query…