Formula generik
=SUMPRODUCT(expression,range)
Ringkasan
Untuk menapis hasil SUMPRODUCT dengan kriteria tertentu, anda dapat menerapkan ungkapan logik sederhana secara langsung pada tatasusunan dalam fungsi, dan bukannya menggunakan fungsi IF. Dalam contoh yang ditunjukkan, formula dalam H5: H7 adalah:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
di mana julat bernama berikut ditentukan:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Sekiranya anda lebih suka mengelakkan julat bernama, gunakan julat di atas yang dimasukkan sebagai rujukan mutlak. Ungkapan logik dalam H6 dan H7 dapat digabungkan, seperti yang dijelaskan di bawah.
Penjelasan
Contoh ini menggambarkan salah satu kekuatan utama fungsi SUMPRODUCT - keupayaan untuk menyaring data dengan ungkapan logik asas dan bukannya fungsi IF. Di dalam SUMPRODUCT, susunan pertama adalah ungkapan logik untuk menapis warna "merah":
--(color="red")
Ini menghasilkan nilai array atau TRUE FALSE, yang dipaksa menjadi satu dan nol dengan operasi negatif ganda (-). Hasilnya ialah susunan ini:
(1;0;1;0;0;0;1;0;0;0)
Perhatikan array mengandungi 10 nilai, satu untuk setiap baris. Yang satu menunjukkan baris di mana warnanya "merah" dan sifar menunjukkan baris dengan warna lain.
Seterusnya, kami mempunyai dua susunan lagi: satu untuk kuantiti dan satu untuk harga. Bersama dengan hasil dari array pertama, kami mempunyai:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Memperluas tatasusunan, kami mempunyai:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Tingkah laku teras SUMPRODUCT adalah membiak, kemudian susun atur. Oleh kerana kami bekerja dengan tiga tatasusunan, kami dapat memvisualisasikan operasi seperti yang ditunjukkan dalam tabel di bawah, di mana lajur hasil adalah hasil dari mengalikan array1 * array2 * array3 :
| susunan1 | susunan2 | susunan3 | hasil |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Perhatikan array1 berfungsi sebagai penapis - nilai sifar di sini nilai "sifar keluar" dalam baris di mana warnanya tidak "merah". Mengembalikan hasilnya menjadi SUMPRODUCT, kami mempunyai:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Yang mengembalikan hasil akhir 480.
Menambah kriteria tambahan
Anda boleh memperluas kriteria dengan menambahkan ungkapan logik yang lain. Sebagai contoh, untuk mencari jumlah jualan di mana warnanya "Merah" dan keadaannya "TX", H6 mengandungi:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Catatan: SUMPRODUCT tidak peka huruf besar kecil.
Memudahkan dengan satu array
Pro Excel selalunya akan menyederhanakan sintaks di dalam SUMPRODUCT sedikit dengan mengalikan tatasusunan secara langsung di dalam array1 seperti ini:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Ini berfungsi kerana operasi matematik (pendaraban) secara automatik membentuk nilai BENAR dan SALAH dari dua ungkapan pertama menjadi satu dan sifar.
