Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tính toán "sumproduct if", trả về tổng các tích của mảng hoặc phạm vi dựa trên tiêu chí.
Hàm SUMPRODUCT
Hàm SUMPRODUCT được sử dụng để nhân các mảng số, tính tổng mảng kết quả.
Để tạo một “Sumproduct If”, chúng ta sẽ sử dụng Hàm SUMPRODUCT cùng với Hàm IF trong một công thức mảng.
SUMPRODUCT IF
Bằng cách kết hợp SUMPRODUCT và IF trong một công thức mảng, về cơ bản chúng ta có thể tạo một hàm “SUMPRODUCT IF” hoạt động tương tự như cách hoạt động của hàm SUMIF tích hợp sẵn. Hãy xem qua một ví dụ.
Chúng tôi có một danh sách doanh số đạt được của người quản lý ở các khu vực khác nhau với tỷ lệ hoa hồng tương ứng:
Giả sử chúng tôi được yêu cầu tính số tiền hoa hồng cho mỗi người quản lý như vậy:
Để thực hiện điều này, chúng ta có thể lồng một hàm IF với người quản lý như tiêu chí của chúng tôi bên trong hàm SUMPRODUCT như vậy:
= SUMPRODUCT (IF (=, *))
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
Khi sử dụng Excel 2022 trở về trước, bạn phải nhập công thức bằng cách nhấn CTRL + SHIFT + ENTER để lấy dấu ngoặc nhọn xung quanh công thức (xem hình trên cùng).
Công thức hoạt động như thế nào?
Công thức hoạt động bằng cách đánh giá mỗi ô trong phạm vi tiêu chí của chúng tôi là TRUE hoặc FALSE.
Tính tổng hoa hồng cho Olivia:
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
= SUMPRODUCT (IF ({TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Tiếp theo, Hàm IF thay thế mỗi giá trị bằng FALSE nếu điều kiện của nó không được đáp ứng.
= SUMPRODUCT ({928,62; 668,22; FALSE; FALSE; FALSE; 480,564; FALSE; FALSE; FALSE})
Bây giờ, Hàm SUMPRODUCT bỏ qua các giá trị FALSE và tính tổng các giá trị còn lại (2.077,40).
SUMPRODUCT IF với nhiều tiêu chí
Để sử dụng hàm SUMPRODUCT IF với nhiều tiêu chí (tương tự như cách hoạt động của hàm SUMIFS tích hợp sẵn), chỉ cần lồng thêm các hàm IF vào hàm SUMPRODUCT như sau:
= SUMPRODUCT (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUCT (IF ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
Một cách tiếp cận khác đối với SUMPRODUCT IF
Thông thường trong Excel, có nhiều cách để dẫn đến kết quả mong muốn. Một cách khác để tính toán "sản phẩm nếu" là bao gồm các tiêu chí ở trong hàm SUMPRODUCT dưới dạng một mảng sử dụng kép một ngôi như vậy:
= SUMPRODUCT (- ($ B $ 2: $ B $ 10 = $ G2), - ($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)
Phương thức này sử dụng hàm đơn phân kép (-) để chuyển đổi mảng TRUE FALSE thành số không và số một. Sau đó SUMPRODUCT nhân các mảng tiêu chí đã chuyển đổi với nhau:
= SUMPRODUCT ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Các mẹo và thủ thuật:
- Nếu có thể, hãy luôn khóa tham chiếu (F4) phạm vi và đầu vào công thức của bạn để cho phép tự động điền.
- Nếu bạn đang sử dụng Excel 2022 hoặc mới hơn, bạn có thể nhập công thức mà không cần Ctrl + Shift + Enter.
SUMPRODUCT IF trong Google Trang tính
Hàm SUMPRODUCT IF hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
