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 phối 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 chuẩn.
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ận toàn bộ trường hợp của một gái trị cụ thể rồi tính tổng toàn bộ các giá trị liên quan đến các trường hợp đó? Hay, có phải bạn cần tìm toàn bộ giá trị trong một mảng thỏa mãn 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ế, ví dụ như xem qua bảng hóa đơn của doanh nghiệp, xác nhận toàn bộ hóa đơn của một đại lý cụ thể, rồi tính tổng toàn bộ 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 chuẩn trong Excel. Đó là loại giá trị gì? Bất kể giá trị số nào. Vậy tiêu chuẩn là gì? Bất kỳ tiêu chuẩn nào ? Xuất phát từ một chữ sô hay một tham chiếu đến một ô chứa giá trị đúng đắn, 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 kể tính năng nào có thể khắc phục các nhiệm vụ trên hay không? Tất nhiên là có rồi! Bạn có thể tìm thấy phương án bằng cách phối 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 tiễn.
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 đã thân 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 cú phải xem qua phần trước nhất của bài hướng dẫn về hàm VLOOKUP cho người mới khởi đầu.
Table of Contents
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ể phối 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 lợi nhuận 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
Hiện tại, bạn muốn lập bảng tính tổng về tổng lợi nhuận của mỗi sản phẩm.
Phương án là sử dụng mảng trong thông số kỹ thuật 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, tất cả 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à hiện giờ, hãy điều chỉnh sự phối hợp của hàm VLOOKUP và hàm SUM so với dữ liệu của tất cả chúng ta để tính tổng toàn bộ lợi nhuận 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))
Trọng yếu! 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ư vậy 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ị trước nhất 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à vì 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 tabvàgt;Table). Tôi cảm thấy rất thuận tiện khi thao tác với bảng Excel có đầy đủ tính 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 phương pháp này, nó sẽ giúp bạn tiết kiệm vài giây quý báu ?
Xem thêm: Thủ thuật Excel nâng cao
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à phương án 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 tác động trái 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 tách 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ẽ phân phối cho bạn vài ví dụ công thức trong nội dung 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, tất 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ố.
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 đúng đắn.
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ư vậy này:
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ố kỹ thuật cần tìm là một mảng thay vì một giá trị riêng rẽ, 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 rẽ.
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ận.
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 phối hợp hàm LOOKUP với hàm SUM như vậy 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 thiện.
Và hiện 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.
Tất cả chúng ta sẽ đặt hàm SUM qua một bên, bởi vì tác 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ề mức giá tương ứng từ cột B trong bảng cần tìm.
- $D$2:$D$10
Thông số kỹ thuật 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 mức giá, điều này được trả về bởi hàm VLOOKUP ở trên, hàm này sẽ phân phối cho bạn giá tiền tài mỗi sản phẩm đã mua.
- $B$2:$B$10=$G$1
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ì toàn bộ tất cả chúng ta đều biết bất kể số nào nhân với 0 đều bằng 0.
Bởi vì công thức của tất cả 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 đúng đắn 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 thuận so 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 nổi bật chính là hàm SUMIF tính tổng chỉ các giá trị thỏa mãn 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ị to hơn 10.
Rất dễ, đúng không? Và hiện giờ, hãy xem xét 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ố minh chứng nhân dân (bảng cần tìm). Bạn có một bảng khác có cùng số minh chứng nhân dân và các con số liên quan đến lợi nhuận (bảng chính). Nhiệm vụ của bạn là tìm tổng lợi nhuận của một người xác nhận bằng số minh chứng 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ố minh chứng 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à hiện giờ, hãy lập công thức mà, trước nhất, tìm kiếm toàn bộ lợi nhuận của một người xác nhận, 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ố kỹ thuật 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ận xét 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ố kỹ thuật này là tùy chọn, nhưng rất trọng yếu so với tất cả chúng ta. Nó xác nhận 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 toàn bộ giá trị của các ô đã được xác nhận ở câu lệnh thứ nhất (thông số kỹ thuật trước nhất).
Hãy nhớ những điều trên, giờ tất cả chúng ta hãy xác nhận ba thông số kỹ thuật cho hàm SUMIF của mình. Có thể bạn vẫn còn nhớ, tất cả chúng ta muốn tính tổng toàn bộ lợi nhuận của một người xác nhận. 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ì tất cả chúng ta đang tìm kiếm thông qua số minh chứng nhân dân của người bán, nên thông số kỹ thuật range cho hàm SUMIF của tất cả chúng ta chính là cột B ở bảng chính. Hiện tại, 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ì tất cả chúng ta có tên người bán hàng ở bảng khác (bảng khác), nên tất cả chúng ta phải công thức VLOOKUP để tìm số minh chứng 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ế tất cả 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 kể tên nào được nhập vào ô xác nhận.
- Sum range – đây là phần dễ nhất. Bởi vì các con số trổ tài thu nhập của tất cả chúng ta nằm ở cột C có tên “Doanh số”, tất cả chúng ta chỉ cần đặt Main_table[Sales].
Hiện tại, toàn bộ những gì bạn cần là tập hợp toàn bộ 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])
Để có thể ứng dụng tốt Excel vào trong công việc, tất cả chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp ứng dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những tri thức này các bạn đều có thể học được trong khóa học
