Anda mungkin akrab dengan fungsi Excel penting seperti jumlah dan produk, tetapi terdapat juga fungsi luar biasa yang dikenali sebagai Sumproduct . Fungsi sumproduct dalam Excel adalah alat yang berkuasa yang membolehkan pengguna melakukan pengiraan kompleks dengan mudah . Ia mengira jumlah produk unsur -unsur yang sepadan dalam julat atau tatasusunan. Fungsi ini boleh digunakan untuk menentukan gaji purata pekerja, skor min pada peperiksaan, atau harga jualan purata inventori syarikat, jumlah nilai pelaburan , antara kegunaan lain.

Takeaways Kunci:

• Fungsi SUMPRODUCT dalam Excel sangat serba boleh dan boleh diintegrasikan dengan fungsi lain seperti ISNUMBER dan SEARCH untuk menjalankan carian kompleks dalam rentetan teks. Dengan menggunakan SUMPRODUCT dengan array syarat, pengguna boleh menilai pelbagai kriteria dalam satu sel dan jumlah contoh di mana kriteria berpuas hati.
• Dengan menggabungkan SUMPRODUCT dengan pengendali unary berganda ( -) untuk menukar nilai sebenar/palsu kepada 1s dan 0, pengguna Excel boleh merumuskan ungkapan yang mengira kejadian substrings tertentu dalam sel. Ini amat berguna untuk analisis data teks dan mengenal pasti kehadiran kata kunci atau frasa tertentu.
• Menggabungkan SUMPRODUCT ke dalam fungsi IF dapat meningkatkan fungsi dan keramahan pengguna lembaran kerja Excel. Pengguna boleh menyampaikan output tersuai dan bukannya nilai benar/palsu semata -mata, menjadikan spreadsheet lebih mudah dibaca dan disesuaikan dengan keperluan analisis data tertentu atau keutamaan pengguna.

Muat turun buku kerja Excel di bawah untuk mengikuti dan memahami cara menggunakan sumproduct dalam excel- muat turun buku kerja excel sumproduct-in-excel.xlsx

Jadual Kandungan

Pengenalan kepada Sumproduct dalam Excel

Fungsi sumproduct dalam Excel adalah alat serba boleh yang mengira jumlah jumlah dari produk unsur -unsur yang sepadan dalam julat atau tatasusunan yang ditentukan. Sintaks Sumbroduct dalam Excel adalah seperti berikut:

= Sumproduct (array1, [array2], [array3], ...)

• Array1 -argumen array pertama yang unsur -unsur yang anda ingin kalikan dan kemudian tambah. Diperlukan.
• Array2, Array3, ... - Argumen Array Tambahan 2 hingga 255 yang unsur -unsur yang anda ingin kalikan dan kemudian tambah. Pilihan.

Sila ambil perhatian - tatasusunan mesti mempunyai dimensi yang sama, atau fungsi itu akan mengembalikan ralat.

Mari kita periksa contoh untuk lebih memahami.

#1 - Contoh Asas

Katakan anda mempunyai dataset yang menyenaraikan kuantiti pelbagai item yang dijual dan harga masing -masing. Anda ingin mengira jumlah pendapatan yang dijana daripada jualan ini. Di sinilah sumproduct berguna.

Langkah 1: Masukkan fungsi sumproduct.

= Sumproduct

Langkah 2: Masukkan hujah pertama, iaitu, Array1. Di sini, ia adalah julat yang mengandungi kuantiti item yang dijual (B2: B9).

= Sumproduct (b2: b9

Langkah 3: Masukkan hujah kedua, iaitu, array 2. Di sini, ia adalah julat yang mengandungi harga unit barang yang dijual (C2: C9).

= Sumproduct (b2: b9, c2: c9)

Mari menilai formula ini:

= Sumproduct (b2: b9, c2: c9)

= Sumproduct ({300; 250; 150; 200; 100; 50; 80; 70}, {10; 5; 20; 15; 15; 50; 25; 40})

= Sumproduct ({3000,1250,3000,6000,1500,2500,2000,2800})

= 22050

Excel mengumpulkan setiap item dalam Array1 dengan item yang sepadan dalam Array2 dan kemudian merumuskan hasilnya. Fungsi sumproduct menyebarkan apa yang memerlukan beberapa langkah, menjadikannya alat yang tidak ternilai untuk pengiraan mudah.

#2 - Sumproduct dengan pengendali aritmetik lain

Walaupun pendaraban adalah operasi lalai , penambahan, penolakan, dan pembahagian juga boleh digunakan . Gunakan sumproduct dengan cara yang biasa, tetapi bukannya menggunakan koma untuk memisahkan argumen array, gunakan pengendali aritmetik yang dikehendaki (*, /,, -). Sebagai contoh, untuk menggunakan bahagian dengan 2 tatasusunan:

= Sumproduct (array1 / array2)

Katakan anda mempunyai dataset dengan jumlah jualan dan harga unit item yang berbeza yang dijual . Anda ingin menentukan jumlah item yang dijual berdasarkan data yang disediakan. Untuk melakukan ini, anda perlu terlebih dahulu membahagikan jumlah jualan dengan harga unit untuk setiap item dan kemudian jumlah hasilnya untuk mendapatkan jumlah kuantiti yang dijual.

Sebagai alternatif, anda boleh menggunakan fungsi sumproduct dengan menggantikan koma dengan tanda bahagian untuk mendapatkan hasilnya:

Langkah 1: Masukkan fungsi sumproduct.

= Sumproduct

Langkah 2: Masukkan hujah pertama, iaitu, Array1. Di sini, ia adalah julat yang mengandungi jumlah jumlah jualan (B2: B9).

= Sumproduct (b2: b9

Langkah 3: Masukkan pengendali bahagian (/).

= Sumproduct (b2: b9/

Langkah 4: Masukkan hujah kedua, iaitu, array 2. Di sini, ia adalah julat yang mengandungi harga unit barang yang dijual (C2: C9).

= Sumproduct (b2: b9/c2: c9)

= Sumproduct (b2: b9/c2: c9)

= Sumproduct ({3000; 1250; 3000; 6000; 1500; 2500; 2000; 2800}/{10; 5; 20; 30; 15; 50; 25; 40})

= Sumproduct ({300,250,150,200,100,50,80,70})

= 1200

Excel mengira jumlah unit yang dijual dengan membahagikan jumlah jualan dengan harga unit dan kemudian menjumlahkan hasilnya.

#3 - purata berwajaran

Sumproduct juga boleh digunakan untuk mengira purata berwajaran dalam pelbagai senario.

Katakan anda telah melabur dalam portfolio stok , dan anda ingin mengira pulangan purata berwajaran untuk pelaburan anda. Anda mempunyai jumlah yang dilaburkan dan pulangan yang sama dalam peratusan untuk semua saham . Menggunakan fungsi sumproduct, anda boleh menentukan pulangan purata berwajaran keseluruhan portfolio anda.

Pertama, gunakan fungsi sumproduct untuk mengira jumlah pulangan (dalam $) pelaburan dengan mengalikan jumlah yang dilaburkan dengan pulangan% dan kemudian menjumlahkan hasilnya. Kemudian, bahagikan ini dengan jumlah pelaburan yang dibuat untuk mendapatkan pulangan purata portfolio.

Langkah 1: Masukkan fungsi sumproduct.

= Sumproduct

Langkah 2: Masukkan hujah pertama, iaitu, Array1. Di sini, ia adalah julat yang mengandungi jumlah yang dilaburkan (B2: B8).

= Sumproduct (b2: b8

Langkah 3: Masukkan hujah kedua, iaitu, array 2. Di sini, ia adalah julat yang mengandungi pulangan setiap pelaburan yang dibuat (C2: C8).

= Sumproduct (b2: b8, c2: c8)

Sekarang bahawa anda mempunyai jumlah pulangan, anda boleh dengan mudah mengira pulangan purata (%) dengan membahagikannya dengan jumlah yang dilaburkan.

Langkah 4: Masukkan pengendali bahagian (/) dan kemudian fungsi jumlah.

= Sumproduct (b2: b8, c2: c8)/sum (

Langkah 5: Pilih julat yang mengandungi jumlah yang dilaburkan, iaitu, b2: b8.

= Sumproduct (b2: b8, c2: c8)/jumlah (b2: b8)

Menggunakan fungsi SumProduct dan SUM, anda boleh dengan mudah mengira purata berwajaran dalam mana -mana senario.

Menyelesaikan masalah masalah sumroduct biasa

Menyelesaikan #Value yang ditakuti! Ralat

Apabila anda menemui #Value! Ralat Semasa menggunakan sumproduct, ia sering menunjukkan campuran teks dan data berangka dalam julat yang anda pilih. Kadang -kadang, kesilapan ini timbul kerana sel tidak betul diformat sebagai teks, atau ia mungkin mengandungi hasil dari formula lain yang tidak serasi dengan nombor. Untuk menyelesaikannya, pastikan semua sel dalam julat yang anda kerjakan dengan nombor yang mengandungi atau boleh ditukar kepada nombor. Anda akan terkejut berapa kerap memeriksa jenis data menyelesaikan masalah anda!

DEBUGGING masalah sintaks sumproduct

Menangani isu sintaks dalam sumproduct boleh sama dengan menyelesaikan teka -teki. Pertama sekali, ingatlah bahawa julat anda perlu saiz yang sama. Sekiranya mereka tidak, Sumproduct akan mengembalikan ralat kerana ia tidak dapat memasangkan unsur -unsur yang sepadan. Seterusnya, berjaga -jaga untuk kurungan yang salah, yang boleh membuang formula anda dari keseimbangan. Setiap hujah harus dilampirkan dengan betul untuk memastikan fungsi itu dapat membaca dan memproses data anda dengan tepat.

Berhati -hati dengan pengendali juga. Jika anda telah memutuskan untuk menambah beberapa bakat matematik ke formula anda dengan memasukkan penambahan atau penolakan, pastikan pengendali ini tidak menyebabkan konflik dengan salah letak. Formula berstruktur yang jelas dan betul bukan sahaja berjalan lancar tetapi juga lebih mudah bagi orang lain yang mengintip ke dalam buku kerja anda untuk memahami dan mengekalkan.

Amalan dan petua terbaik untuk penggunaan yang cekap

Memahami Array1, Array2 Notation

Menyelam ke dalam sumproduct tanpa memahami notasi adalah seperti cuba membaca peta tanpa mengetahui simbol -simbol. Nasib baik, ia agak mudah apabila anda mendapat sambutan hangat. Array1 merujuk kepada kumpulan pertama nilai yang anda ingin berlipat ganda bersama. Ini adalah titik permulaan anda, yang mengandungi set multiplicands yang sumproduct akan mula berfungsi.

Kemudian, Array2 (dan mana -mana susunan berikutnya seperti Array3, Array4, dan lain -lain) menyertai parti sebagai tetamu pilihan. Anda boleh memasukkannya apabila anda perlu membiak item yang sepadan di pelbagai rentang. Fikirkan array ini sebagai lapisan tambahan data yang sumproduct akan melelehkan, mengalikan dan kemudian menjumlahkan produk mereka kepada jumlah yang besar.

Dalam erti kata lain, Array1 adalah undangan yang sangat diperlukan untuk peristiwa formula sumproduct anda, dan Array2 dan seterusnya adalah ditambah, ada untuk meningkatkan perkara apabila keadaan memanggilnya. Merangkul notasi, dan anda akan membuka kunci keupayaan pengiraan baru dalam Excel.

Menjaga cepat dan bersih dengan sumproduct

Mengekalkan kelajuan dan kecekapan dalam buku kerja Excel anda adalah penting, terutamanya apabila berurusan dengan set data yang besar. Dengan sumproduct, matlamatnya adalah untuk menjaga keadaan pantas dan kemas. Anda boleh mempercepatkan perkara dengan menggunakan julat bernama untuk merujuk kepada data anda. Ini bukan sahaja menjadikan formula anda lebih mudah dibaca tetapi juga mengurangkan peluang merujuk kesilapan.

Kedua, elakkan menggunakan keseluruhan rujukan lajur dalam formula sumproduct anda. Walaupun ia mungkin menggoda untuk hanya menggunakan 'B: B' bukannya 'B1: B100', ini boleh membawa kepada masa pengiraan yang lebih perlahan apabila proses Excel berpuluh -puluh ribu sel yang tidak perlu. Sebaliknya, bersesuaian dengan julat anda -amalan yang akan memastikan buku kerja anda beroperasi dengan cepat.

Juga, ingatlah bahawa menjaga formula langsung dan ke titik bantuan dalam mengekalkan kebersihan buku kerja. Sekiranya anda dapat mencapai sesuatu yang elegan dengan satu formula sumproduct, jarang ada keperluan untuk menggagalkan buku kerja anda dengan beberapa operasi yang lebih rumit.

Soalan Lazim: Soalan Sumroduct anda dijawab

Mengapa menggunakan sumproduct ke atas fungsi jumlah lain?

SumProduct adalah pergi ke fleksibiliti dan kekuatan dalam pengiraan. Tidak seperti fungsi jumlah lain yang mungkin memukul sekatan jalan dengan syarat -syarat tertentu atau memerlukan menubuhkan beberapa langkah perantara, sumproduct melampaui hanya menambahkan nombor -ia melipatgandakan unsur -unsur yang sepadan di seluruh tatasusunan dan kemudian merangkum produk tersebut. Keupayaan unik ini bermakna anda boleh melakukan formula kompleks dalam satu-liner, di mana orang lain seperti Sum atau Sumif mungkin memerlukan bantuan tambahan atau tidak dapat mengatasi.

Lebih-lebih lagi, kerana sumproduct tidak memerlukan formula yang dimasukkan dengan pelbagai, anda boleh mengelakkan proses kawalan kawalan rumit, menyelaraskan pengalaman Excel anda lebih banyak lagi. Sekiranya anda menghargai spreadsheet yang bersih dan menghargai kecekapan, Sumproduct telah mendapat punggung anda.

Bolehkah sumproduct mengendalikan atau logik berjaya?

Sudah tentu, dan itulah salah satu acara penting menggunakan sumproduct - ia agak multitasker ketika datang untuk menggunakan keadaan logik! Sumproduct boleh proses atau logik dengan mudah dalam kriteria anda, yang membezakannya daripada adik -beradiknya sumifs dan countifs, yang tegas mematuhi dan logik.

Untuk bekerja di dalam itu atau logik, anda akan menggunakan operator plus () untuk memasukkan pelbagai syarat. Fikirkan ia sebagai memberitahu sumproduct, "Hei, jumlah jumlah nilai jika ini atau kriteria itu dipenuhi." Fungsi itu kemudiannya dengan tekun mengikuti, menjumlahkan nilai -nilai apabila salah satu daripada syarat -syarat yang diberikan berlaku.

Oleh itu, jika anda mendapati diri anda memerlukan jumlah yang pelbagai dalam pelbagai keadaan tanpa menulis saga formula yang berasingan, ingatlah bahawa sumproduct boleh mengurus atau logik dengan baik, memudahkan kehidupan spreadsheet anda.

Bagaimana cara menyesuaikan sumproduct untuk mengira kejadian unik?

Mengira kejadian yang unik mungkin bukan apa yang diketahui oleh sumproduct, tetapi dengan beberapa tweak, ia pasti sampai kepada cabaran. Menyesuaikan sumproduct untuk tujuan ini dengan menggabungkannya dengan fungsi lain seperti COUNTIF. Pada asasnya, anda akan menyediakan formula yang membalikkan item berulang, jadi jika item muncul lebih dari sekali, ia menyumbang kurang kepada kiraan akhir.

Formula mungkin kelihatan seperti ini: =SUMPRODUCT(1/COUNTIF(range, range)) , yang bijak memastikan bahawa setiap item unik dikira sekali. Bahagian COUNTIF mengira berapa kali item muncul dalam julat, dan kemudian merangkum merangkumi timbal balik dari jumlah tersebut - dengan berkesan mengira setiap item yang berbeza sekali.

Ingat, transformasi Nifty ini amat berguna jika anda bekerja dengan versi Excel yang tidak menyokong fungsi unik. Menggunakan sumproduct dengan cara ini untuk mengira kejadian yang unik menggambarkan betapa banyak pisau tentera Swiss yang boleh berada di alat Excel anda.

Sumproduct mengira jumlah produk unsur -unsur yang sepadan dalam susunan atau julat yang diberikan. Fungsi ini mendapati aplikasi dalam pelbagai senario, termasuk pengiraan asas seperti jumlah pendapatan daripada jualan, operasi kompleks yang melibatkan pengendali aritmetik yang berbeza, dan mengira purata berwajaran, seperti menentukan pulangan purata berwajaran pelaburan dalam portfolio yang pelbagai.

Atas ialah kandungan terperinci 3 Contoh untuk menguasai sumproduct dalam excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!