Tóm lược
Trong một số trường hợp nhất định, bạn có thể sử dụng SUMIFS giống như một công thức tra cứu để truy xuất một giá trị số. Trong ví dụ được hiển thị, công thức trong G6 là:
=SUMIFS(sales,region,G4,quarter,G5)
trong đó vùng (B5: B20), quý (C5: C20) và doanh số (D5: D20) là các phạm vi được đặt tên.
Kết quả là doanh số quý 3 cho khu vực miền Trung là 127.250.
Giải trình
Nếu bạn chưa quen với hàm SUMIFS, bạn có thể tìm thấy tổng quan cơ bản với nhiều ví dụ tại đây.
Hàm SUMIFS được thiết kế để tính tổng các giá trị số dựa trên một hoặc nhiều tiêu chí. Tuy nhiên, trong các trường hợp cụ thể, bạn có thể sử dụng SUMIFS để "tra cứu" một giá trị số đáp ứng tiêu chí bắt buộc. Những lý do chính để làm điều này là sự đơn giản và nhanh chóng.
Trong ví dụ được hiển thị, chúng tôi có dữ liệu bán hàng hàng quý cho bốn khu vực. Chúng tôi bắt đầu bằng cách cho SUMIFS một phạm vi tổng và điều kiện đầu tiên, kiểm tra vùng cho giá trị trong G4, "Trung tâm":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Phạm vi tổng là doanh số (D5: D20)
- Phạm vi tiêu chí 1 là vùng (B5: B20)
- Tiêu chí 1 là G4 ("Trung tâm")
Sau đó, chúng tôi thêm cặp phạm vi / tiêu chí thứ hai, cặp này sẽ kiểm tra hàng quý:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Tiêu chí 2 là một phần tư (C5: C20)
- Tiêu chí 2 là G5 ("Q3")
Với các tiêu chí này, SUMIFS trả về 127.250, con số bán hàng của Trung tâm Quý 3.
Hành vi của SUMIFS là tính tổng tất cả các giá trị phù hợp. Tuy nhiên, bởi vì chỉ có một giá trị phù hợp, kết quả giống với giá trị của chính nó.
Dưới đây, chúng tôi xem xét một số tùy chọn công thức tra cứu.
Tra cứu các tùy chọn công thức
Phần này đánh giá ngắn gọn các tùy chọn công thức khác mang lại kết quả tương tự. Ngoại trừ SUMPRODUCT (ở dưới cùng), đây là các công thức tra cứu truyền thống hơn để xác định vị trí của giá trị đích và trả về giá trị tại vị trí đó.
Với hàm VLOOKUP
Thật không may, VLOOKUP không phải là một giải pháp tốt cho vấn đề này. Với cột trợ giúp, bạn có thể xây dựng công thức VLOOKUP để phù hợp với nhiều tiêu chí (ví dụ ở đây), nhưng đó là một quá trình khó khăn đòi hỏi bạn phải điều chỉnh dữ liệu nguồn.
Với INDEX và MATCH
INDEX và MATCH là một tổ hợp tra cứu rất linh hoạt có thể được sử dụng cho tất cả các loại vấn đề tra cứu và ví dụ này cũng không ngoại lệ. Với INDEX và MATCH, chúng tôi có thể tra cứu doanh số bán hàng theo khu vực và quý bằng công thức mảng như sau:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Mẹo với cách tiếp cận này là sử dụng logic boolean với các phép toán mảng bên trong hàm MATCH để xây dựng một mảng 1 và 0 làm mảng tra cứu. Sau đó, chúng ta có thể yêu cầu hàm MATCH tìm số 1. Khi mảng tra cứu được tạo, công thức sẽ giải quyết thành:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Chỉ còn lại 1 trong mảng tra cứu, hàm MATCH trả về vị trí 11 cho hàm INDEX và hàm INDEX trả về số doanh thu ở vị trí đó, 127,250.
Để biết thêm chi tiết, hãy xem: INDEX và MATCH với nhiều tiêu chí
Với XLOOKUP
XLOOKUP là một hàm mới linh hoạt trong Excel có thể xử lý các mảng nguyên bản. Với XLOOKUP, chúng ta có thể sử dụng chính xác cách tiếp cận giống như với INDEX và MATCH, sử dụng logic boolean và các phép toán mảng để tạo mảng tra cứu:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Khi các hoạt động của mảng đã chạy, công thức sẽ giải quyết thành:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Và XLOOKUP trả về kết quả tương tự như trên, 127,250.
Thêm: XLOOKUP với nhiều tiêu chí
Với LOOKUP
Hàm LOOKUP là một hàm cũ hơn trong Excel mà nhiều người thậm chí không biết về nó. Một trong những điểm mạnh chính của LOOKUP là nó có thể xử lý các mảng một cách nguyên bản. Tuy nhiên, LOOKUP có một số điểm yếu riêng biệt:
- Không thể bị khóa ở "chế độ đối sánh chính xác"
- Luôn giả định dữ liệu tra cứu được sắp xếp, AZ
- Luôn trả về kết quả so khớp gần đúng (nếu không tìm thấy kết quả chính xác)
Tuy nhiên, LOOKUP có thể được sử dụng để giải quyết vấn đề này một cách độc đáo như sau:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
đơn giản hóa thành:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Nếu tình huống không đáp ứng cả hai yêu cầu, SUMIFS không phải là một lựa chọn tốt.
