Trong bài viết này, Học Excel Online sẽ chia sẻ với các bạn một vài ví dụ về cách kết hợp hàm VLOOKUP và hàm SUM hoặc SUMIF trong Excel để tra cứu và tính tổng dựa trên một hoặc nhiều điều kiện.

Có phải bạn đang muốn tạo ra một file báo cáo trong Excel? Trong báo cáo này, bạn cần tìm tất cả những giá trị cụ thể nhất định sau đó tính tổng các giá trị liên quan tới giá trị cụ thể đó (nằm trên cùng 1 dòng chẳng hạn)?  Hoặc có thể bạn cần tính tổng giá trị toàn bộ hoá đơn của một nhà cung cấp cụ thể?

Các nhiệm vụ có thể khác nhau, nhưng bản chất là giống nhau – bạn muốn tra cứu và tính tổng các giá trị với một hoặc một số tiêu chí trong Excel. Kiểu dữ liệu nào? Bất cứ dữ liệu kiểu số nào. Loại tiêu chí nào? Bất kỳ loại nào 🙂 Bắt đầu từ một số hoặc tham chiếu đến một ô có chứa giá trị phù hợp và kết thúc bằng các phép toán logic và kết quả được trả về bởi các công thức Excel.

Vì vậy, Microsoft Excel có bất kỳ chức năng nào có thể giúp thực hiện các công việc trên không? Tất nhiên là có! Bạn có thể tìm ra giải pháp bằng cách kết hợp hàm VLOOKUP hoặc LOOKUP của Excel với các hàm SUM hoặc hàm SUMIF. Các ví dụ về công thức sau đây sẽ giúp bạn hiểu cách hoạt động của các hàm Excel này và cách áp dụng chúng vào dữ liệu thực.

Xin lưu ý, đây là những ví dụ nâng cao mà bạn đã quen thuộc với các nguyên tắc và cú pháp của hàm VLOOKUP. Nếu không, phần đầu tiên của hướng dẫn VLOOKUP dành cho người mới bắt đầu chắc chắn đáng để bạn chú ý – cú pháp hàm VLOOKUP trong Excel và cách sử dụng.

Excel VLOOKUP và SUM – tìm tổng các giá trị phù hợp

Nếu bạn làm việc với dữ liệu số trong Excel, bạn thường không chỉ trích xuất các giá trị liên quan từ một bảng khác mà còn tính tổng các số trong một số cột hoặc hàng. Để làm điều này, bạn có thể sử dụng kết hợp các hàm SUM và VLOOaKUP như minh họa bên dưới.

Nguồn dữ liệu:

Giả sử, bạn có một danh sách sản phẩm với số liệu bán hàng trong vài tháng, một cột là một  tháng. Dữ liệu nguồn nằm trên sheet có tên Monthly sales:

Bây giờ, bạn muốn lập một bảng tổng hợp với tổng doanh thu cho từng sản phẩm.

Giải pháp là sử dụng một mảng trong tham số thứ 3 (col_index_num) của hàm VLOOKUP trong Excel. Đây là một công thức chung:

SUM(VLOOKUP(lookup value, lookup range, {2,3,…,n}, FALSE))

Như bạn đã thấy, chúng ta sử dụng một hằng số mảng bằng tham số thứ 3 để thực hiện tra cứu trong cùng một công thức VLOOKUP để lấy tổng các giá trị trong 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 cho dữ liệu để tìm tổng doanh thu trong các cột B – M trong bảng trên:

=SUM(VLOOKUP(B2, ‘Monthly sales’! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))

Quan trọng! Vì bạn đang xây dựng một công thức mảng, hãy nhớ nhấn tổ hợp phím Ctrl + Shift + Enter thay vì nhấn phím Enter đơn giản khi bạn nhập xong. Khi bạn làm điều này, Microsoft Excel sẽ đặt công thức của bạn trong dấu ngoặc nhọn như sau:

{=SUM(VLOOKUP(B2, ‘Monthly sales’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}

Nếu bạn nhấn phím Enter như bình thường, chỉ giá trị đầu tiên trong mảng mới được xử lý, điều này sẽ tạo ra kết quả không chính xác.

Mẹo: Bạn có thể tò mò vì sao công thức hiển thị [@Product] làm giá trị tra cứu trong hình trên. Điều này là do vùng dữ liệu trên đã được chuyển đổi dữ liệu thành bảng (Thẻ Insert trên thanh công cụ Ribbon > Table). Điều này thuận tiện khi làm việc với bảng Excel có đầy đủ chức năng và tham chiếu có cấu trúc của chúng. Ví dụ: khi bạn nhập công thức vào một ô, Excel sẽ tự động sao chép công thức trên toàn bộ cột và theo cách này, bạn sẽ tiết kiệm được thời gian.

Như bạn thấy, việc kết hợp hàm VLOOKUP và SUM trong Excel rất dễ dàng. Tuy nhiên, đây không phải là giải pháp lý tưởng, đặc biệt nếu bạn đang làm việc với những cái bảng lớn. Vấn đề là việc sử dụng công thức mảng có thể ảnh hưởng xấu đến hiệu suất của workbook vì mỗi giá trị trong mảng thực hiện một lệnh gọi riêng của hàm VLOOKUP. Vì vậy, bạn càng có nhiều giá trị mảng và càng có nhiều công thức mảng trong sổ làm việc của mình thì Excel càng hoạt động chậm hơn.

Bạn có thể bỏ qua vấn đề này bằng cách sử dụng kết hợp các hàm INDEX và MATCH thay vì hàm SUM và VLOOKUP, và tôi sẽ chỉ cho bạn một vài ví dụ về 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

Cách đây ít lâu, chúng ta đã thảo luận về một ví dụ về cách bạn có thể trích xuất giá trị từ một số cột trong bảng tra cứu và tính tổng các giá trị đó. Theo cách tương tự, bạn có thể thực hiện các phép tính toán học khác với kết quả được trả về bởi hàm VLOOKUP. Dưới đây là một số ví dụ về công thức:

Lưu ý: Vì tất cả các công thức trên đều là công thức mảng, hãy nhớ nhấn Ctrl + Shift + Enter để nhập chính xác vào một ô.

Nếu chúng ta thêm các công thức trên vào bảng ‘Summary Sales’ từ ví dụ trước, kết quả sẽ giống như sau:

LOOKUP VÀ SUM – tra cứu trong một mảng và tính tổng các giá trị phù hợp

Trong trường hợp tham số tra cứu của bạn là một mảng chứ không phải một giá trị đơn lẻ, thì hàm Vlookup không có ích vì nó không thể tra cứu trong mảng dữ liệu. Trong trường hợp này, bạn có thể sử dụng hàm LOOKUP của Excel tương tự với hàm VLOOKUP nhưng hoạt động với các mảng cũng như với các giá trị riêng lẻ.

Hãy xem xét ví dụ sau để bạn có thể hiểu rõ hơn những gì tôi đang nói đến. 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 (Main Table). Bạn cũng có một bảng thứ 2 chứa giá sản phẩm (Lookup Table). Nhiệm vụ của bạn là lập một công thức tìm tổng số đơn hàng của khách hàng nhất định.

Như bạn đã biết, bạn không thể sử dụng hàm Vlookup trong Excel vì bạn có nhiều trường hợp của giá trị tra cứu (mảng dữ liệu). Thay vào đó, bạn sử dụng kết hợp các hàm SUM và LOOKUP như sau:

=SUM(LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))

Vì đây là công thức mảng nên hãy nhớ nhấn Ctrl + Shift + Enter để hoàn thành.

Và bây giờ, hãy phân tích các thành phần của công thức để bạn hiểu cách hoạt động của từng chức năng và có thể điều chỉnh nó cho dữ liệu của riêng bạn.

Chúng ta sẽ tạm gác hàm SUM sang một bên vì mục đích là làm rõ và tập trung vào 3 thành phần được nhân:

1. LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)

Hàm LOOKUP này tra cứu hàng hóa được liệt kê ở cột C trong bảng chính và trả về giá tương ứng từ cột B trong bảng tra cứu.

1. $D$2:$D$10

Thành phần này trả về số lượng của mỗi sản phẩm được mua bởi mỗi khách hàng, được liệt kê trong cột D trong bảng chính. Nhân với giá, được trả về bởi hàm LOOKUP ở trên, nó sẽ cung cấp cho bạn chi phí của mỗi sản phẩm đã mua.

1. $B$2:$B$10=$G$1

Công thức này so sánh tên của khách hàng trong cột B với tên trong ô G1. Nếu tìm thấy một kết quả phù hợp, nó sẽ trả về “1”, ngược lại là “0”. Bạn sử dụng nó đơn giản để “cắt bỏ” tên của khách hàng khác với tên trong ô G1, vì tất cả chúng ta đều biết rằng bất kỳ số nào nhân với 0 cũng đều bằng không.

Bởi vì đây là công thức mảng, nó lặp lại quá trình được mô tả ở trên cho mỗi giá trị trong mảng tra cứu. Và cuối cùng, hàm SUM tính tổng các tích của tất cả các phép nhân.

Ghi chú: Để công thức LOOKUP hoạt động chính xác, bạn cần sắp xếp cột tra cứu trong bảng Tra cứu của mình theo thứ tự tăng dần (từ A đến Z). Nếu việc sắp xếp không được chấp nhận trên dữ liệu của bạn, hãy xem qua công thức SUM và TRANSPOSE.

VLOOKUP và SUMIF – tra cứu và tính tổng các giá trị với tiêu chí

Cách dùng hàm SUMIF trong Excel tương tự như hàm SUM mà chúng ta đã thảo luận cách mà nó tính tổng giá trị. Sự khác biệt là hàm SUMIF chỉ tính tổng những giá trị đáp ứng tiêu chí bạn chỉ định. Ví dụ: công thức SUMIF đơn giản nhất = SUMIF (A2: A10, “> 10”) thêm các giá trị trong ô A2 đến A10 lớn hơn 10.

Và bây giờ chúng ta hãy xem xét một trường hợp phức tạp hơn. Giả sử bạn có một bảng liệt kê tên và số ID của nhân viên bán hàng (Lookup_table). Bạn có một bảng khác có cùng ID và số liệu bán hàng được liên kết (Main_table). Nhiệm vụ của bạn là tìm tổng doanh số bán hàng của một người nhất định theo ID của họ. Và bây giờ, có 2 vấn đề:

• Bảng thư chứa nhiều mục nhập cho cùng một ID theo thứ tự ngẫu nhiên.
• Bạn không thể thêm cột “Tên người bán hàng” vào bảng chính.

Và bây giờ, chúng ta hãy tạo một công thức, thứ nahats, tìm tất cả doanh số bán hàng của một người nhất định và thứ 2, tính tổng các giá trị tìm được.

Trước khi chúng ta bắt đầu 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 có thể hiểu được, chỉ đơn giản là một dải ô mà bạn muốn đánh giá theo các tiêu chí đã chỉ định.
• Criteria – điều kiện cho công thức biết giá trị nào cần tính tổng. Nó có thể được cung cấp dưới dạng một số, tham chiếu ô, biểu thức hoặc một hàm Excel khác.
• Sum_range – tham số này tùy chọn, nhưng rất quan trọng. Nó xác định phạm vi nơi các giá trị của ô tương ứng sẽ được thêm vào. Nếu bị bỏ qua, Excel sẽ tính tổng các giá trị của ô được chỉ định trong tham số phạm vi (tham số thứ nhất).

Lưu ý: hãy xác định 3 tham số cho hàm SUMIF. Như bạn biết, chúng tôi muốn tính tổng tất cả doanh số bán hàng được thực hiện bởi một người nhất định có tên được nhập vào ô F2 trong bảng chính (vui lòng xem hình trên).

1. Range – vì đang tìm kiếm ID nhân viên bán hàng, tham số phạm vi của hàm SUMIF là cột B trong bảng chính. Vì vậy, bạn có thể nhập phạm vi B:B hoặc nếu bạn chuyển đổi dữ liệu của mình thành một bảng, bạn có thể sử dụng tên của cột để thay thế: Main_table[ID]
2. Criteria – bởi vì chúng ta có tên của nhân viên bán hàng trong một bảng khác (bảng tra cứu), chúng ta phải sử dụng công thức VLOOKUP để tìm ID tương ứng với một người nhất định. Tên của người đó được viết trong ô F2 trong bảng chính, vì vậy tra cứu bằng công thức sau: VLOOKUP($F$2,Lookup_table,2,FALSE)

Tất nhiên, bạn có thể nhập tên vào tiêu chí tra cứu của hàm VLOOKUP, nhưng sử dụng tham chiếu ô tuyệt đối là cách tốt hơn vì điều này tạo ra một công thức chung mà hoạt động cho bất kỳ tên nào được thêm vào ô.

      3. Sum_range – đây là phần dễ nhất. Vì số bán hàng nằm trong cột C có tên là “Sales”, 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 các phần của công thức 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])

Kết hợp hàm hàm VLOOKUP với hàm SUM/ hàm SUMIF trong Excel là một kỹ thuật Excel cơ bản nhưng khá dễ nhầm. Học Excel Online hy vọng các bạn đã nắm rõ cách kết hợp qua bài viết này. Trân trọng!