Trong bài hướng dẫn này, bạn sẽ tìm thấy một vài ví dụ công thức nâng cao diễn giải cách kết hợp hàm VLOOKUP với hàm SUM hoặc hàm SUMIF trong Excel để tìm và tính tổng các giá trị dựa trên một hay vài tiêu chí.
Có phải bạn đang cố tạo ra một tập tin tóm tắt trong Excel – tập tin mà sẽ xác định tất cả trường hợp của một gái trị cụ thể rồi tính tổng tất cả các giá trị liên quan đến các trường hợp đó? Hay, có phải bạn cần tìm tất cả giá trị trong một mảng đáp ứng như cầu của bạn rồi tính tổng các giá trị liên quan ở một bảng tính khác? Hay có thể bạn đang gặp phải một thử thách cụ thể hơn thế, chẳng hạn như xem qua bảng hóa đơn của công ty, xác định tất cả hóa đơn của một đại lý cụ thể, rồi tính tổng tất cả giá trị hóa đơn đó?
Nhiệm vụ có thể khác nhau, nhưng có cùng bản chất – bạn muốn tìm và tính tổng các giá trị dựa trên một hay nhiều tiêu chí trong Excel. Đó là loại giá trị gì? Bất cứ giá trị số nào. Vậy tiêu chí là gì? Bất kỳ tiêu chí nào 🙂 Bắt đầu từ một chữ sô hay một tham chiếu đến một ô chứa giá trị chính xác, rồi kết thúc bằng toán tử logic và công thức trong Excel trả về kết quả.
Vậy, Microsoft Excel có bất cứ tính năng nào có thể giải quyết các nhiệm vụ trên hay không? Tất nhiên là có rồi! Bạn có thể tìm ra giải pháp bằng cách kết hợp hàm VLOOKUP với hàm SUM hay hàm SUMIF trong Excel. Các ví dụ công thức dưới đây sẽ giúp bạn hiểu cách các hàm này hoạt động và cách sử dụng chúng cho dữ liệu thực tế.
Hãy lưu ý rằng, đây là các ví dụ nâng cao – điều này có nghĩa là bạn đã quen thuộc với các quy tắc và cú pháp cơ bản của hàm VLOOKUP. Nếu không, bạn chắc chắn phải xem qua phần đầu tiên của bài hướng dẫn về hàm VLOOKUP cho người mới bắt đầu.
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 bạn thao tác dữ liệu số trên Excel, thì thông thường bạn không phải chỉ xuất các dữ liệu liên quan từ một bảng khác mà còn tính tổng các con số từ một vài cột hay hàng. Để thực hiện thao tác này, bạn có thể kết 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 muốn lập bảng tính tổng về tổng doanh số của mỗi sản phẩm.
Giải pháp là sử dụng mảng trong thông số thứ ba (col_index_num) của hàm VLOOKUP trong Excel. Đây là công thức hàm VLOOKUP mẫu:
=SUM(VLOOKUP(lookup_value, lookup_range, {2,3,4}, FALSE))
Như bạn có thể thấy, chúng ta 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 giá trị ở 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 đối với dữ liệu của chúng ta để tính tổng tất cả 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 công thức mảng, hãy chắc rằng bạn nhấp Ctrl+Shift+Enter thay vì chỉ nhấn mỗi Enter khi bạn gõ xong. Khi bạn thực hiện xong, Microsoft Excel sẽ đóng khung công thức 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 bạn nhấp Enter, thì chỉ có giá tị đầu tiên trong mảng được xử lý, điều này sẽ cho kết quả sai.
Mẹo. Bạn có thể tò mò là tại sao công thức lại hiển thị [@Product] như là giá trị cần tìm trong ảnh chụp màn hình trên. Đó là vì 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 thao tác với bảng Excel có đầy đủ chức năng hơn là các dải ô. Ví dụ, khi 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ư bạn có thể thấy, việc sử dụng hàm VLOOKUP cùng với hàm SUM rất dễ. Tuy nhiên, đây không phải là giải pháp hoàn hảo, đặc biệt khi bạn đang thao tác với bảng lớn. Điểm mấu chốt ở đây là việc sử dụng công thức mảng có thể gây ảnh hưởng ngược lại hoạt động của sổ làm việc vì mỗi giá trị trong mảng cần một hàm VLOOKUP riêng biệt. Vì thế, càng có nhiều giá trị trong mảng thì càng có nhiều công thức mảng trong sổ làm 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ẽ cung cấp cho bạn vài ví dụ công thức trong bài viết tiếp theo.
CÁCH THỰC HIỆN CÁC PHÉP TÍNH KHÁC VỚI HÀM VLOOKUP TRONG EXCEL
Vài phút trước, chúng ta thảo luận ví dụ về cách bạn có thể xuất các giá trị từ một vài cột trong bảng cần tìm và tính tổng các giá trị đó. Tương tự, bạn có thể thực hiện các phép tính khác có kết quả được trả về bởi hàm VLOOKUP. Đây là một vài ví dụ công thức:
Thao tác
|
Ví dụ công thức
|
Mô tả
|
Tính giá trị trung bình
|
{=AVERAGE(VLOOKUP(A2, ‘Bảng cần
tìm’!$A$2:$D$10,{2,3,4},FALSE))}
|
Công thức tìm giá trị từ ô A2
trong “Bảng cần tìm’ và tính giá trị trung bình ở các cột B,C,D trên cùng một
hàng.
|
Tìm giá trị lớn nhất
|
{=MAX(VLOOKUP(A2,’Bảng cần
tìm’$A$2:$D$10,{2,3,4},FALSE))}
|
Công thức tìm giá trị từ cột A2
trong ‘Bảng cần tìm’ rồi tìm giá trị lớn nhất ở các cột B, C, D trên cùng một
hàng.
|
Tìm giá trị nhỏ nhất
|
{=MIN(VLOOKUP(A2,’Bảng cần tìm’$A$2:$D$10,{2,3,4},FALSE))}
|
Công thức tìm giá trị từ cột A2
trong ‘Bảng cần tìm’ rồi tìm giá trị nhỏ nhất ở các cột B, C, D trên cùng một
hàng.
|
Tính phần trăm trên tổng số
|
{=0.3*SUM(VLOOKUP(A2,’Bảng cần
tìm’$A$2:$D$10,{2,3,4},FALSE))}
|
Công thức tìm giá trị từ cột A2
trong ‘Bảng cần tìm’, tính tổng giá trị ở các cột B, C, D trên cùng một hàng,
rồi tính 30% trên tổng số.
|
Nếu ta thêm các công thức trên vào bảng “Tổng doanh số” của ví dụ trước, thì kết quả sẽ tương tự như thế này:Lưu ý. Vì các công thức trên đều là công thức mảng, nên hãy nhớ nhấn Ctrl+Shift+Enter để nhập chúng vào ô được chính xác.
HÀM LOOKUP VÀ HÀM SUM – TÌM KIẾM TRONG MẢNG VÀ TÍNH TỔNG CÁC GIÁ TRỊ TRÙNG KHỚP
Trong trường hợp thông số cần tìm là một mảng thay vì một giá trị riêng lẻ, thì hàm VLOOKUP sẽ không có tác dụng bởi vì nó không thể tìm kiếm trong các mảng dữ liệu. Trong trường hợp này, bạn có thể sử dụng hàm LOOKUP – hàm tương tự với hàm VLOOKUP nhưng có thao tác với mảng và với các giá trị riêng lẻ.
Hãy xem xét các ví dụ sau, để bạn hiểu hơn điều mà tôi nói. Giả sử, bạn có một bảng liệt kê tên khách hàng, sản phẩm mua, và số lượng (Bảng chính). Bạn cũng có bảng thứ hai liệt kê giá sản phẩm (Bảng cần tìm). Nhiệm vụ của bạn là lập công thức tính tổng các đơn hàng đặt bởi các khách hàng xác định.
Có thể bạn vẫn nhớ, bạn không thể sử dụng hàm VLOOKUP trong Excel bởi vì giá trị cần tìm (mảng dữ liệu) xuất hiện rất nhiều lần. Thay vào đó, bạn kết hợp hàm LOOKUP với hàm SUM như thế này:
=SUM(LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
Bởi vì đây là công thức mảng, hãy nhớ nhấn Ctrl+Shift+Enter để hoàn thành.
Và bây giờ, hãy phân tích các phần của công thức để bạn hiểu mỗi hàm hoạt động ra sao và có thể chỉnh sửa nó cho dữ liệu của riêng bạn.
Chúng ta sẽ đặt hàm SUM qua một bên, bởi vì công dụng của nó quá rõ ràng, và chỉ tập trung vào 3 thành phần được được nhân với nhau:
- LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)
Hàm LOOKUP này tìm kiếm hàng hóa được liệt kê trong cột C trong bảng chính, và trả về giá tiền tương ứng từ cột B trong bảng cần tìm.
Thông số này trả về số lượng mỗi sản phẩm mua bởi khách hàng, số lượng được liệt kê ở cột D trong bảng chính. Khi được nhân với giá tiền, điều này được trả về bởi hàm VLOOKUP ở trên, hàm này sẽ cung cấp cho bạn giá tiền của mỗi sản phẩm đã mua.
Công thức này so sánh tên khách hàng ở cột B với tên khách hàng ở ô G1. Nếu trùng khớp, nó sẽ trả về “1”, nếu không thì nó sẽ trả về “0”. Bạn sử dụng nó chỉ để xóa tên khách hàng không xuất hiện ở ô G1, bởi vì tất cả chúng ta đều biết bất cứ số nào nhân với 0 đều bằng 0.
Bởi vì công thức của chúng ta là công thức mảng nên nó lặp lại quá trình trên cho mỗi giá trị trong mảng cần tìm. Và cuối cùng, hàm SUM tính tổng các kết quả của phép nhân. Chẳng có gì khó khăn cả, đúng không nào?
Lưu ý. Để công thức LOOKUP hoạt động chính xác thì bạn cần 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). Nếu việc lọc không được chấp nhận đối với dữ liệu của bạn, thì hãy thử qua công thức SUM/TRANSPOSE được gợi ý bởi Leo.
HÀM VLOOKUP VÀ HÀM SUMIF – TÌM KIẾM VÀ TÍNH TỔNG CÁC GIÁ TRỊ ĐÁP ỨNG CÁC TIÊU CHUẨN NHẤT ĐỊNH
Hàm SUMIF trong Excel thì tương tự với hàm SUM về mặt tính tổng các giá trị. Điểm khác biệt chính là hàm SUMIF tính tổng chỉ các giá trị đáp ứng tiêu chuẩn mà bạn đã định rõ. Ví dụ, công thức SUMIF đơn giản nhất =SUMIF(A2:A10,”>10″) tính tổng các giá trị từ ô A2 đến ô A10, các giá trị lớn hơn 10.
Rất dễ, đúng không? Và bây giờ, hãy cân nhắc một trường hợp phức tạp hơn nhiều. Giả sử, bạn có một bảng liệt kê tên người bán và số chứng minh nhân dân (bảng cần tìm). Bạn có một bảng khác có cùng số chứng minh nhân dân và các con số liên quan đến doanh số (bảng chính). Nhiệm vụ của bạn là tìm tổng doanh số của một người xác định bằng số chứng minh nhân dân của họ. Và, có hai nhân tố phức tạp:
Bảng chính chứa nhiều mục có cùng số chứng minh nhân dân theo thứ tự ngẫu nhiên.
Bạn không thể thêm cột “Tên người bán” vào bảng chính.
Và bây giờ, hãy lập công thức mà, đầu tiên, tìm kiếm tất cả doanh số của một người xác định, và thứ hai, tính tổng các giá trị.
Trước khi lập công thức, hãy để tôi nhắc bạn cú pháp của hàm SUMIF:
SUMIF(range, criteria, [sum_range])
range – thông số này đã tự giải thích cho chính nó rồi, chỉ là một dải ô mà bạn muốn đánh giá thông qua các tiêu chuẩn nhất định.
criteria – điều kiện nói cho công thức biết cần tính tổng giá trị nào. Nó có thể được dùng dưới dạng số, tham chiếu ô, biểu thức, hay hàm Excel khác.
sum_range – thông số này là tùy chọn, nhưng rất quan trọng đối với chúng ta. Nó xác định dải ô nơi mà các giá trị của ô tương ứng sẽ được cộng. Nếu loại bỏ, Excel sẽ tính tổng tất cả giá trị của các ô đã được xác định ở câu lệnh thứ nhất (thông số đầu tiên).
Hãy nhớ những điều trên, giờ chúng ta hãy xác định ba thông số cho hàm SUMIF của mình. Có thể bạn vẫn còn nhớ, chúng ta muốn tính tổng tất cả doanh số của một người xác định. Tên của người đó được nhập vào ô F2 trong bảng chính (hãy xem lại hình ảnh ở trên).
- Range – bởi vì chúng ta đang tìm kiếm thông qua số chứng minh nhân dân của người bán, nên thông số range cho hàm SUMIF của chúng ta chính là cột B ở bảng chính. Bây giờ, bạn có thể nhập dải ô B:B, hay nếu bạn đổi dữ liệu của mình thành bảng, bạn có thể sử dụng tên cột để thay thế: Main_table[ID]
- Criteria – bởi vì chúng ta có tên người bán hàng ở bảng khác (bảng khác), nên chúng ta phải công thức VLOOKUP để tìm số chứng minh nhân dân tương ứng của người đó. Tên của người đó được nhập ở ô F2 trong bảng chính, vì thế chúng ta sử dụng công thức này để tìm kiếm nó:
VLOOKUP($F$2,Lookup_table,2,FALSE)
Tất nhiên, bạn có thể nhập tên trong tiêu chuẩn cần tìm của hàm VLOOUP, nhưng sử dụng tham chiếu ô tuyệt đối thì tốt hơn bởi vì nó tạo ra công thức chung có hiệu quả cho bất cứ tên nào được nhập vào ô xác định.
- Sum range – đây là phần dễ nhất. Bởi vì các con số thể hiện doanh thu của chúng ta nằm ở cột C có tên “Doanh số”, chúng ta chỉ cần đặt Main_table[Sales].
Bây giờ, tất cả những gì bạn cần là tập hợp tất cả các phần của công thức lại và công thức SUMIF + VLOOKUP của bạn đã sẵn sàng:
=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])
