Formula generik
=SUMPRODUCT(costs,--(range="x"))
Ringkasan
Untuk mengira harga paket produk menggunakan "x" sederhana untuk memasukkan atau mengecualikan produk, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di D11 adalah:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Penjelasan
Fungsi SUMPRODUCT menggandakan julat atau tatasusunan bersama dan mengembalikan jumlah produk. Ini terdengar membosankan, tetapi SUMPRODUCT adalah fungsi yang elegan dan serba boleh, yang ditunjukkan oleh contoh ini dengan baik.
Dalam contoh ini, SUMPRODUCT dikonfigurasi dengan dua tatasusunan. Susunan pertama adalah julat yang menahan harga produk:
$C$5:$C$9
Perhatikan bahawa rujukan mutlak untuk mengelakkan perubahan kerana formula disalin ke kanan. Julat ini menilai susunan berikut:
(99;69;129;119;49)
Susunan kedua dihasilkan dengan ungkapan ini:
--(D5:D9="x")
Hasil D5: D9 = "x" adalah susunan nilai BENAR BENAR seperti ini:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Negatif berganda (-) menukar nilai BENAR PALSU ini menjadi 1s dan 0s:
(1;1;0;0;0)
Oleh itu, di dalam SUMPRODUCT kita mempunyai:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Fungsi SUMPRODUCT kemudian mengalikan item yang sepadan dalam setiap array bersama-sama:
=SUMPRODUCT((99;69;0;0;0))
dan mengembalikan jumlah produk, 168 dalam kes ini.
Secara berkesan, susunan kedua bertindak sebagai penapis untuk nilai dalam larik pertama. Nol dalam array2 membatalkan item dalam array1, dan 1 dalam array2 membenarkan nilai dari array1 sampai ke hasil akhir.
Dengan satu susunan
SUMPRODUCT disiapkan untuk menerima pelbagai tatasusunan, tetapi anda boleh mempermudah formula ini sedikit dengan menyediakan satu array pada permulaan:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Operasi matematik (pendaraban) secara automatik mengumpulkan nilai BENAR PALSU dalam ungkapan kedua kepada satu dan nol, tanpa perlu negatif dua kali.
