SUMPRODUCT yang maha kuasa untuk mempelajari fungsi Excel!

Dalam artikel sebelumnya " AGREGAT ajaib pembelajaran fungsi Excel, seseorang sebenarnya boleh bernilai 19! 》, kami belajar tentang fungsi statistik yang berkuasa. Hari ini kita akan bercakap tentang fungsi SUMPRODUCT Fungsi sumproduct boleh mendapatkan hasil tambah dua lajur data Walau bagaimanapun, terdapat banyak perkara yang perlu diberi perhatian apabila menggunakan fungsi tersebut, terutamanya selepas menggunakan nilai logik dan tatasusunan. Mari kita lakukan bersama-sama.

1 Intipati SUMPRODUCT

Fungsi SUMPRODUCT sebenarnya sangat mudah iaitu To. dapatkan jumlah produk dua lajur data, kami menggunakan contoh mudah untuk menggambarkan fungsi asas fungsi:

Gambar di atas ialah jadual yang sangat mudah untuk hitung jumlah harga Secara umumnya, harga unit * kuantiti dikira dan kemudian dijumlahkan Hasilnya ditunjukkan dalam D8. Jika fungsi SUMPRODUCT digunakan, jumlah harga boleh dikira terus menggunakan harga unit dan kuantiti Formula 1 ialah: =SUMPRODUCT(B2:B7,C2:C7), dan hasilnya ditunjukkan dalam D9.

Dalam formula ini, dua parameter digunakan iaitu luas harga unit (B2:B7) dan luas kuantiti (C2:C7 Fungsi fungsi ialah untuk menukar yang pertama Data dalam parameter pertama (harga unit) dan parameter kedua (kuantiti) didarab secara sepadan dan kemudian dijumlahkan.

2. Punca kebanyakan ralat

Ramai rakan sering mendapat nilai ralat apabila menggunakan fungsi ini, kebanyakannya kerana pemilihan saiz kawasan adalah tidak konsisten. Contohnya, dalam kes berikut, parameter pertama mempunyai 7 sel dan parameter kedua hanya mempunyai 6 sel:

Gunakan Fungsi SUMPRODUCT mesti memastikan bahawa saiz kawasan setiap parameter adalah sama, tetapi ramai rakan tidak menyedarinya.

3. Satu lagi cara penulisan yang biasa ialah menukar koma kepada tanda darab (*)

Untuk contoh ini, terdapat satu lagi cara penulisan yang lebih Untuk kegunaan biasa, formulanya adalah seperti berikut: =SUMPRODUCT(B2:B7*C2:C7)

Anda boleh lihat bahawa hasil pengiraan adalah sama seperti =SUMPRODUCT(B2:B7,C2: C7) adalah konsisten.

Keputusan yang konsisten telah membawa kepada soalan yang ramai kawan hairan: Apakah perbezaan antara kedua-duanya?

4 Perbezaan antara tanda koma dan darab (*)

Walaupun ia hanya menukar koma dalam formula pertama menjadi tanda darab. (*), tetapi makna formula berubah. Formula pertama (SUMPRODUCT(B2:B7,C2:C7)) mempunyai dua parameter, manakala formula kedua (B2:B7*C2:C7) mempunyai satu parameter. (Untuk menentukan berapa banyak parameter yang ada, anda perlu melihat sama ada ia dipisahkan dengan koma.) Dalam formula pertama, langkah mendarab dua kawasan dilengkapkan dengan fungsi Pertama, biarkan dua kawasan Data yang sepadan didarabkan, dan kemudian produk ditambah. Dalam formula kedua, pendaraban dua kawasan dilakukan dengan pengiraan tatasusunan, dan fungsi hanya melakukan satu perkara, iaitu menambah nilai produk.

Apakah kesan daripada perubahan makna?

Mari kita lihat melalui contoh:

Dalam formula di atas, koma (,) digunakan dan terdapat dua parameter bebas. Fungsi SUMPRODUCT terlebih dahulu mendarabkan dua set data secara sepadan Semasa pendaraban, ia menyemak data dan menganggap data bukan angka sebagai 0, dan kemudian menambah produk. Oleh itu, B1 "Harga Seunit" dan C1 "Kuantiti" akan dianggap sebagai 0, dan formula boleh memperoleh hasil yang betul.

Apabila kita menggantikan koma dengan tanda *, hasil formula adalah salah. kenapa? Fungsi SUMPRODUCT hanya bertanggungjawab untuk menambah produk pada masa ini. Parameter B2:B7*C2:C7 ialah operasi pendaraban tatasusunan Oleh kerana kawasan pengiraan mengandungi teks (teks tidak boleh didarab), terdapat nilai ralat dalam hasil pengiraan tatasusunan ini. Pilih "B2:B7*C2:C7" dalam formula dan tekan F9 untuk melihat hasil operasi B2:B7*C2:C7:

Anda boleh melihat yang pertama ( Hasil operasi (harga unit * kuantiti) ialah nilai ralat. Seterusnya, SUMPRODUCT menjumlahkan data yang mengandungi nilai ralat, dan hasilnya mestilah ralat.

5 Perkara utama menggunakan tanda darab (*) dalam SUMPRODUCT

Kandungan di atas mempunyai dua maksud:

Pertama, menggunakan koma dan menggunakan * kadangkala mempunyai hasil yang sama, tetapi maknanya berbeza sama sekali.

Kedua, apabila menggunakan tanda darab (*) dalam fungsi SUMPRODUCT, anda mesti memberi perhatian kepada dua perkara: pertama, tidak boleh ada kandungan yang tidak dikira, seperti teks, kedua, jika dua atau lebih set tatasusunan didarab , saiz kawasan data adalah sama. Apabila menggunakan koma, anda hanya perlu memastikan bahawa saiz kawasan data adalah konsisten.

6 Malah, tanda darab juga membawa kelebihan yang lebih besar

Kami menggunakan A, B digantikan dengan SUMPRODUCT (A, B) dan SUMPRODUCT (A*B). Apabila ia adalah koma, A dan B mesti kedua-duanya adalah nilai angka atau tatasusunan pada masa yang sama Satu tidak boleh menjadi nilai angka dan satu lagi ialah tatasusunan Apabila ia adalah tanda darab, A dan B boleh menjadi kedua-dua angka nilai atau tatasusunan pada masa yang sama, atau satu boleh menjadi nilai angka dan satu lagi tatasusunan.

Iaitu:

koma,		Tanda pendaraban*
SUMPRODUCT(C1,B1)	√	SUMPRODUCT(C1*B1)	√
SUMPRODUCT(C1:C9,D1:D9)	√	SUMPRODUCT（C1:C9*D1:D9）	√
SUMPRODUCT（C1:D9,F1:G9）	√	SUMPRODUCT（C1:D9*F1:G9）	√
SUMPRODUCT（C1:C9,D1）	×	SUMPRODUCT（C1:C9*D1）	√
SUMPRODUCT（C1:D9,F1）	×	SUMPRODUCT（C1:D9*F1）	√

Oleh itu, aplikasi fungsi SUMPRODUCT dikembangkan dengan tanda darab. Perkara yang anda akan lihat di bawah ialah aplikasi fungsi SUMPRODUCT menggunakan tanda darab.

7. Adakah anda faham formula SUMPRODUCT ini?

Jika anda faham kandungan di atas, bermakna penggunaan asas fungsi ini tiada masalah. Tetapi kemungkinan besar anda masih tidak memahami banyak formula SUMPRODUCT, seperti yang ini:

Ini sebenarnya masalah pengiraan bersyarat Lihat yang ini sekali lagi:

Ini ialah masalah penjumlahan berbilang syarat, dan terdapat ini:

Dalam jadual ini, hampir semua jenis masalah statistik ialah Anda boleh menggunakan fungsi SUMPRODUCT untuk menyelesaikannya, jadi saya tidak akan memberikan contoh satu demi satu. Mungkin tidak ramai rakan yang dapat memahami hanya tiga formula yang disenaraikan di atas.

Mengapa saya faham cara menggunakan fungsi SUMPRODUCT tetapi masih tidak dapat memahami formula ini, apatah lagi menggunakannya untuk menyelesaikan masalah sendiri? Sebabnya ialah anda tidak memahami dua mata pengetahuan ini: nilai logik dan tatasusunan.

8 Pelajari sedikit tentang nilai logik dan tatasusunan

Mari kita bincangkan tentang nilai logik dahulu nilai, BENAR dan SALAH. Apabila kita membuat beberapa jenis perbandingan atau pertimbangan dalam formula, nilai logik akan dijana Ambil SUMPRODUCT((I2:I22="Zhang San")*1) sebagai contoh, dengan (I2:I22="Zhang San". ) ) adalah penghakiman. Biasanya, kami membuat pertimbangan berdasarkan sel I2 = "Zhang San" bermaksud menilai sama ada kandungan sel I2 adalah "Zhang San". Apabila kita menggunakan kawasan untuk menilai, kita akan mendapat satu set data, yang sebenarnya adalah tatasusunan. Contohnya, I2:I22="Zhang San" akan mendapat satu set nilai logik Anda boleh menggunakan F9 untuk melihat hasil pengiraan:

Jika anda berminat, anda. boleh membandingkan jurujual satu demi satu Dan nilai hasil, anda akan mendapati bahawa nilai Zhang San yang sepadan semuanya BENAR.

Oleh kerana nilai logik tidak boleh dijumlahkan secara langsung, ia mesti ditukar kepada nombor Kaedah penukaran adalah untuk melakukan sebarang operasi seperti penambahan, penolakan, pendaraban atau pembahagian pada nilai logik. Dalam formula ini, *1 mempunyai kesan ini Anda boleh melihat kesannya:

Selepas operasi *1, BENAR menjadi 1, dan SALAH menjadi 0. Jangan tanya saya mengapa, ini adalah cara Excel menetapkannya, dan ini adalah hubungan yang sepadan antara nilai logik dan nombor.

Baiklah, kita mula-mula mendapatkan satu set nilai logik, kemudian gunakan *1 untuk mengubahnya menjadi satu set nombor, dan kemudian menjumlahkannya untuk mencapai tujuan pengiraan berdasarkan syarat. Adakah anda faham formula =SUMPRODUCT((I2:I22="Zhang San")*1) sekarang?

Kini kami memahami nilai logik dan prinsip pertama operasi tatasusunan: apabila sekumpulan nombor dikira dengan nombor, setiap data dalam kumpulan nombor dikira dengan nombor ini. Ini adalah bagaimana ia dikira dalam formula tadi.

9 Tidak kira betapa rumitnya formula SUMPRODUCT, fahami semuanya!

Mari kita lihat formula kedua: =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22).

Jangan lihat panjangnya formula ini sebenarnya sangat mudah untuk memecahkannya menggunakan pengetahuan yang baru kita pelajari. Formula masih mempunyai hanya satu parameter, tetapi parameter ini terdiri daripada empat tatasusunan, tiga daripadanya adalah nilai logik, iaitu (BULAN(F2:F22)=3), (H2:H22="Cawangan Kedua") dan (G2: G22="baju"). Tiga set nilai logik ini melengkapkan tiga pertimbangan dan sepadan dengan tiga syarat: bulan = 3, kedai = cawangan kedua dan nama = baju. Kandungan khusus adalah seperti yang ditunjukkan dalam gambar:

Nampak padat, tetapi selepas darab, ia menjadi sekumpulan 1s dan 0s Hasilnya adalah seperti ini:

Hasil produk hanya mempunyai dua 1, yang sebenarnya sepadan dengan dua keping data tentang baju dari cawangan kedua pada bulan Mac. Formula pada masa ini menjadi satu set nilai logik (sudah 0 dan 1) didarab dengan set data (kuantiti), dan kemudian fungsi melengkapkan jumlah.

Dalam contoh ini, anda perlu memberi perhatian kepada prinsip kedua pengiraan tatasusunan: apabila berbilang (termasuk dua) tatasusunan dikira (empat tatasusunan dalam contoh ini), data pada kedudukan yang sepadan dalam tatasusunan ialah dikira. Jumlah data yang terkandung dalam tatasusunan adalah sama.

Setakat ini kami telah memahami dua prinsip pengiraan tatasusunan Berikut ialah contoh mudah untuk menggambarkan:

Suatu tatasusunan (A1:A9) dan data Apabila (. B1) didarab, kumpulan nombor ini masing-masing didarab dengan data ini apabila

apabila dua tatasusunan (A1:A9 dan B1:B9) didarab, ia adalah Darab data dalam kedudukan yang sepadan bagi set nombor pertama dan set nombor kedua.

Mengimbas kembali formula ini =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22) sekarang, ia sepatutnya kurang sakit kepala.

Sebenarnya, prinsip pengiraan tatasusunan adalah lebih rumit Rakan-rakan yang berminat boleh menyemak maklumat yang berkaitan Anda boleh mempelajari prinsip mudah di sini. Mari kita lihat formula ketiga: =SUMPRODUCT((H2:H22=H2)*(G2:G22={"毛衣","衬衫"})*L2:L22). Formula ini kelihatan sedikit berbeza daripada dua sebelumnya (G2:G22={"毛衣","衬衫"}) Bahagian ini menggunakan tatasusunan tetap Sebenarnya, formula ini sepatutnya ditulis seperti ini: =SUMPRODUCT((H2:H22=H2)*(G2:G22="毛衣")*L2:L22) SUMPRODUCT((H2:H22=H2)*(G2:G22="衬衫")*L2:L22).

Formula ini menggunakan dua fungsi SUMPRODUCT Yang pertama adalah untuk mengira jualan baju sejuk di kedai utama, dan yang kedua adalah untuk mengira jualan baju di kedai utama. .Setiap orang harus dapat memahami. Kedua-dua fungsi SUMPRODUCT adalah sama kecuali untuk "sweater" dan "baju".

Melalui penjelasan hari ini, kami telah memahami formula SUMPRODUCT biasa, menyelesaikan kekeliruan menggunakan tanda darab fungsi SUMPRODUCT dan menguasai lebih banyak aplikasi fungsi SUMPRODUCT. Kami juga mempelajari beberapa asas tentang nilai logik dan tatasusunan - yang akan membantu kami memahami formula kompleks lain.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci SUMPRODUCT yang maha kuasa untuk mempelajari fungsi Excel!. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!