Công thức Excel: Định giá gói dễ dàng với SUMPRODUCT -

Mục lục

Công thức chung

=SUMPRODUCT(costs,--(range="x"))

Tóm lược

Để tính giá gói sản phẩm bằng cách sử dụng "x" đơn giản để bao gồm hoặc loại trừ một sản phẩm, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong D11 là:

=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))

Giải trình

Hàm SUMPRODUCT nhân các dải hoặc mảng với nhau và trả về tổng các sản phẩm. Điều này nghe có vẻ nhàm chán, nhưng SUMPRODUCT là một chức năng thanh lịch và linh hoạt, ví dụ này minh họa một cách độc đáo.

Trong ví dụ này, SUMPRODUCT được cấu hình bằng hai mảng. Mảng đầu tiên là phạm vi giữ giá sản phẩm:

$C$5:$C$9

Lưu ý rằng tham chiếu là tuyệt đối để ngăn các thay đổi khi công thức được sao chép sang bên phải. Phạm vi này đánh giá mảng sau:

(99;69;129;119;49)

Mảng thứ hai được tạo với biểu thức này:

--(D5:D9="x")

Kết quả của D5: D9 = "x" là một mảng các giá trị TRUE FALSE như sau:

(TRUE;TRUE;FALSE;FALSE;FALSE)

Âm kép (-) chuyển đổi các giá trị TRUE FALSE này thành 1s và 0s:

(1;1;0;0;0)

Vì vậy, bên trong SUMPRODUCT, chúng ta có:

=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))

Sau đó, hàm SUMPRODUCT nhân các mục tương ứng trong mỗi mảng với nhau:

=SUMPRODUCT((99;69;0;0;0))

và trả về tổng sản phẩm, 168 trong trường hợp này.

Về mặt hiệu quả, mảng thứ hai hoạt động như một bộ lọc cho các giá trị trong mảng đầu tiên. Zeros trong array2 hủy bỏ các mục trong array1 và 1s trong array2 cho phép các giá trị từ array1 chuyển qua kết quả cuối cùng.