Cara menggunakan fungsi Excel SUMPRODUCT -

Isi kandungan

Ringkasan

Fungsi SUMPRODUCT menggandakan julat atau tatasusunan bersama dan mengembalikan jumlah produk. Ini terdengar membosankan, tetapi SUMPRODUCT adalah fungsi yang sangat serba boleh yang dapat digunakan untuk mengira dan menjumlahkan seperti COUNTIFS atau SUMIFS, tetapi dengan lebih banyak fleksibiliti. Fungsi lain boleh digunakan dengan mudah di dalam SUMPRODUCT untuk memperluas fungsi dengan lebih jauh.

Tujuan

Gandakan, kemudian susun atur

Nilai pulangan

Hasil tatasusunan berganda dan dijumlahkan

Sintaks

= SUMPRODUCT (array1, (array2),…)

Hujah

  • array1 - Susunan atau julat pertama yang berlipat kali ganda, kemudian tambahkan.
  • array2 - (pilihan) Susunan atau julat kedua untuk didarabkan, kemudian tambahkan.

Versi

Excel 2003

Catatan penggunaan

Fungsi SUMPRODUCT berfungsi dengan tatasusunan, tetapi tidak memerlukan sintaks array biasa (Ctrl + Shift + Enter) untuk masuk. Tujuan fungsi SUMPRODUCT adalah untuk memperbanyak, kemudian menjumlahkan, tatasusunan. Sekiranya hanya satu array dibekalkan, SUMPRODUCT hanya akan menjumlahkan item dalam array. Sehingga 30 susunan boleh dibekalkan.

Ketika pertama kali menemui SUMPRODUCT, mungkin kelihatan membosankan, kompleks, dan bahkan tidak berguna. Tetapi SUMPRODUCT adalah fungsi serba boleh yang luar biasa dengan banyak kegunaan. Kerana ia dapat menangani susunan dengan baik dan tanpa keluhan, anda dapat menggunakannya untuk memproses rangkaian sel dengan cara yang cerdik dan elegan (lihat pautan ke contoh formula di halaman ini).

Untuk menggambarkan bagaimana SUMPRODUCT berfungsi, berikut adalah beberapa contoh biasa.

RINGKASAN untuk jumlah dan jumlah bersyarat

Anggaplah anda mempunyai beberapa data pesanan di A2: B6, dengan Negeri di lajur A, Penjualan di lajur B:

A B
1 Nyatakan Jualan
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

Dengan menggunakan SUMPRODUCT, anda boleh mengira jumlah penjualan untuk Texas ("TX") dengan formula ini:

=SUMPRODUCT(--(A2:A6="TX"))

Dan anda boleh menjumlahkan jumlah penjualan untuk Texas ("TX") dengan formula ini:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Catatan: Jangan keliru dengan negatif ganda. Ini adalah muslihat biasa yang digunakan dalam formula Excel yang lebih maju untuk memaksakan nilai BENAR dan SALAH menjadi 1 dan 0. Lihat lebih banyak di bawah …

Untuk contoh jumlah di atas, berikut adalah gambaran maya dari dua tatasusunan yang pertama kali diproses oleh SUMPRODUCT:

susunan1 susunan2
SALAH 75
SALAH 100
BENAR 125
SALAH 125
BENAR 150

Setiap susunan mempunyai 5 item. Array pertama mengandungi nilai BENAR / SALAH yang terhasil dari ungkapan A2: A6 = "TX", dan susunan kedua adalah kandungan B2: B6. Setiap item dalam array pertama akan didarabkan dengan item yang sesuai dalam array kedua. Namun, dalam keadaan semasa, hasil SUMPRODUCT akan menjadi sifar kerana nilai BENAR dan SALAH akan dianggap sebagai sifar. Kita memerlukan item dalam array1 berangka - mereka perlu "dipaksakan" menjadi satu dan nol. Di sinilah negatif berganda masuk.

Dengan menggunakan double negatif - (double unary, untuk anda jenis teknikal) kami dapat memaksakan TRUE / FALSE ke dalam nilai angka satu dan sifar, seperti yang ditunjukkan dalam representasi maya di bawah. Lajur terakhir "Produk" mewakili hasil mengalikan dua susunan bersama-sama. Hasil penjumlahan, 275, adalah nilai yang dikembalikan oleh SUMPRODUCT.

susunan1 susunan2 Produk
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Jumlah 275

Menggunakan sintaks pendakap keriting untuk tatasusunan, contohnya seperti ini selepas paksaan:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

dan seperti ini selepas pendaraban:

=SUMPRODUCT((0,0,125,0,150))

Contoh ini memperluaskan idea di atas dengan lebih terperinci.

RINGKASAN dengan fungsi lain

SUMPRODUCT dapat menggunakan fungsi lain secara langsung. Anda mungkin melihat SUMPRODUCT digunakan dengan fungsi LEN untuk menghitung jumlah watak dalam julat, atau dengan fungsi seperti ISBLANK, ISTEXT, dan lain-lain. Ini biasanya bukan fungsi array, tetapi apabila mereka diberi julat, mereka membuat "array hasil". Kerana SUMPRODUCT dibina untuk berfungsi dengan tatasusunan, ia dapat melakukan pengiraan pada tatasusunan secara langsung. Ini boleh menjadi cara yang baik untuk menjimatkan ruang dalam lembaran kerja, dengan menghilangkan keperluan untuk kolom "penolong".

Sebagai contoh, anggap anda mempunyai 10 nilai teks yang berbeza dalam A1: A10 dan anda mahu mengira jumlah aksara untuk semua 10 nilai. Anda boleh menambahkan kolum pembantu di lajur B yang menggunakan formula ini: LEN (A1) untuk mengira watak di setiap sel. Maka anda boleh menggunakan SUM untuk menambahkan semua 10 nombor. Walau bagaimanapun, dengan menggunakan SUMPRODUCT, anda boleh menulis formula seperti ini:

=SUMPRODUCT(LEN(A1:A10))

Apabila digunakan dengan julat seperti A1: A10, LEN akan mengembalikan array 10 nilai. Kemudian SUMPRODUCT hanya akan menjumlahkan semua nilai dan mengembalikan hasilnya, tanpa kolom penolong diperlukan.

Lihat contoh di bawah banyak cara lain untuk menggunakan SUMPRODUCT.

Catatan:

  1. SUMPRODUCT memperlakukan item bukan angka dalam tatasusunan sebagai sifar.
  2. Argumen larik mestilah berukuran sama. Jika tidak, SUMPRODUCT akan menghasilkan # NILAI! nilai ralat.
  3. Ujian logik di dalam tatasusunan akan mewujudkan nilai BENAR dan SALAH. Dalam kebanyakan kes, anda mesti memaksa ini menjadi 1 dan 0.
  4. SUMPRODUCT sering dapat menggunakan hasil fungsi lain secara langsung (lihat contoh formula di bawah)

Artikel menarik...