Fungsi SUMIFS, cara menggunakan fungsi untuk menjumlahkan berbilang syarat dalam Excel

Fungsi Sumifs dalam Excel amat berguna apabila anda perlu menjumlahkan banyak syarat. Di bawah ialah butiran tentang cara menggunakan fungsi Sumifs dalam Microsoft Excel .

Fungsi SUMIFS dalam Excel ialah salah satu fungsi asas Excel , fungsi pengiraan yang biasa digunakan dalam Excel. Untuk mengira jumlah dalam Excel, kita akan menggunakan fungsi SUM . Jika kita ingin menambah syarat tertentu pada jumlah fungsi, kita akan menggunakan fungsi SUMIF . Dalam kes di mana jadual data memerlukan jumlah dengan banyak syarat, kita mesti menggunakan fungsi SUMIFS. Artikel di bawah akan membimbing anda cara menggunakan fungsi SUMIFS dalam Excel.

• MS Excel - Pelajaran 5: Formula dan fungsi Excel
• Bagaimana untuk menggabungkan fungsi Sumif dan fungsi Vlookup dalam Excel
• 3 cara untuk mengira jumlah dalam Excel
• Bagaimana untuk membetulkan ralat fungsi SUM tidak menambah dalam Excel

Jadual kandungan artikel

• Sintaks fungsi SUMIFS dalam Excel
• Perhatikan apabila menggunakan fungsi SUMIFS dalam Excel
• Arahan untuk menggunakan fungsi SUMIFS dalam Excel
  • Gunakan kad bebas
  • Gunakan Julat Dinamakan dengan fungsi SUMIFS
  • Contoh fungsi SUMIFS dalam Excel
• Had fungsi SUMIFS

Sintaks fungsi SUMIFS dalam Excel

Sintaks fungsi SUMIFS dalam Excel ialah =SUMIFS(julat_jumlah,julat_kriteria1,julat_kriteria1,julat_kriteria2,kriteria2,…).

Di sana:

• Sum_range: adalah sel yang perlu dijumlahkan dalam jadual data, nilai kosong dan nilai teks diabaikan, parameter yang diperlukan.
• Kriteria_julat1: julat yang perlu diuji menggunakan kriteria kriteria1, ialah nilai yang diperlukan.
• Kriteria1: syarat digunakan pada kriteria_julat1, boleh menjadi nombor, ungkapan, rujukan sel untuk menentukan sel mana dalam julat_kriteria1 akan dijumlahkan, juga nilai yang diperlukan.
• Kriteria_julat2, kriteria2,…: julat dan syarat tambahan pilihan, maksimum 127 pasangan julat_kriteria, kriteria.

Perhatikan apabila menggunakan fungsi SUMIFS dalam Excel

• Untuk setiap sel dalam julat, julat sum_range akan dijumlahkan apabila semua syarat sepadan yang ditentukan sebagai benar untuk sel itu dipenuhi.
• Sel dalam julat_sum_yang mengandungi BENAR dianggap 1, sel yang mengandungi FALSE dianggap 0.
• Setiap julat_kriteria mesti mempunyai saiz pemilihan yang sama seperti julat_jumlah, julat_kriteria dan julat_jumlah mesti mempunyai bilangan baris dan bilangan lajur yang sama.
• Kriteria boleh menggunakan aksara tanda soal (?) dan bukannya satu aksara, asterisk (*) dan bukannya rentetan. Jika syarat ialah tanda soal atau asterisk, anda mesti memasukkan tanda ~ di hadapan, nilai syarat ialah teks yang disertakan dalam ".
• Pelbagai syarat digunakan menggunakan logik AND, iaitu kriteria1 DAN kriteria2, dsb.
• Setiap julat tambahan mesti mempunyai bilangan baris dan lajur yang sama dengan jumlah julat, tetapi julat tidak perlu bersebelahan. Jika anda memberikan julat yang tidak sepadan, anda akan mendapat ralat #VALUE.
• Rentetan teks dalam kriteria mesti disertakan dalam tanda petikan (""), iaitu "apple", ">32", "jap*"
• Rujukan sel dalam kriteria tidak boleh disertakan dalam petikan, iaitu "<>
• SUMIF dan SUMIFS boleh mengendalikan julat, tetapi tidak boleh mengendalikan tatasusunan. Ini bermakna anda tidak boleh menggunakan fungsi lain seperti YEAR pada julat kriteria kerana hasilnya ialah tatasusunan. Jika anda memerlukan fungsi ini, gunakan fungsi SUMPRODUCT.
• Susunan hujah antara fungsi SUMIFS dan SUMIF adalah berbeza. Julat jumlah ialah argumen pertama dalam SUMIFS, tetapi argumen ketiga dalam SUMIF.

Arahan untuk menggunakan fungsi SUMIFS dalam Excel

Gunakan kad bebas

Aksara kad bebas seperti '*' dan '?' boleh digunakan dalam hujah kriteria apabila menggunakan fungsi SUMIFS. Menggunakan kad bebas ini akan membantu anda mencari padanan yang serupa tetapi bukan tepat.

Asterisk (*) - Ia sepadan dengan mana-mana rentetan aksara, yang boleh digunakan selepas, sebelum atau dengan kriteria sekeliling untuk membenarkan penggunaan separa kriteria carian.

Contohnya, jika anda menggunakan kriteria berikut dalam fungsi SUMIFS:

• N * - Menyiratkan semua sel dalam julat bermula dengan N
• * N - Menyiratkan semua sel dalam julat yang berakhir dengan N
• *N* - Sel yang mengandungi N

Tanda soal (?) - Padan dengan mana-mana aksara tunggal. Katakan anda menggunakan N?r sebagai kriteria. "?" di sini akan menggantikan satu aksara. N?r akan sepadan dengan Utara, Nor, dll. Walau bagaimanapun, ia tidak akan mengambil kira Nama.

Bagaimana jika data yang diberikan mengandungi asterisk atau tanda soal sebenar?

Dalam kes ini, anda boleh menggunakan tilde (~). Anda perlu menaip "~" sebelum tanda soal dalam kes itu.

Gunakan Julat Dinamakan dengan fungsi SUMIFS

Julat Dinamakan ialah nama deskriptif bagi set sel atau julat sel dalam lembaran kerja. Anda boleh menggunakan Julat Dinamakan semasa menggunakan fungsi SUMIFS.

Contoh fungsi SUMIFS dalam Excel

Kami akan mengira jumlah dengan jadual data di bawah dengan beberapa syarat yang berbeza.

Contoh 1 : Kira jumlah produk yang dijual oleh pekerja Hoai dengan harga seunit kurang daripada 400,000 VND.

Dalam kotak input hasil, kami masukkan formula =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") dan kemudian="" press="">

Di sana:

• D2:D7 ialah kawasan di mana jumlah item perlu dikira.
• C2:C7 ialah julat nilai syarat nama pekerja.
• “Hoai” ialah syarat untuk nama pekerja yang perlu mengira bilangan item yang terkandung dalam kawasan C2:C7.
• E2:E7 ialah keadaan item.
• “400,000” ialah syarat untuk mengandungi item di kawasan E2:E7.

Hasilnya ialah jumlah bilangan item seperti yang ditunjukkan di bawah.

Contoh 2 : Kira jumlah bilangan item yang dijual oleh pekerja Rusia dengan nombor siri <>

Dalam kotak input hasil, kami masukkan formula =SUMIFS(D2:D7,C2:C7,"=Russia",A2:A7,"<5") dan kemudian="" press="" enter.="" di = "" name="" multiplication="" tablet="" "nga"="" enter="" sign="or" remove="" sign="even">

Hasilnya, kami mendapat jumlah bilangan item yang dijual oleh pekerja Rusia dengan nombor siri < 5="" iaitu 550 item="">

Contoh 3 : Kira jumlah bilangan produk yang dijual oleh pekerja Rusia, tidak termasuk produk selendang.

Dalam kotak input hasil, kami masukkan formula =SUMIFS(D2:D7,C2:C7,“Rusia”,B2:B7,“<>selendang”). Di mana tanda <> digunakan untuk mengecualikan objek tertentu dalam kawasan data sebagai syarat.

Hasilnya ialah jumlah bilangan produk seperti yang ditunjukkan di bawah.

Contoh 4:

Kira jumlah semua statistik untuk Pokemon jenis Air generasi 1:

Syarat di sini ialah Air dan Generasi adalah 1.

Nota: Set data penuh diteruskan selepas baris 14, sehingga ke baris 759.

Berikut ialah butiran langkah demi langkah:

1. Pilih sel H3 .
2. Taip =SUMIFS.
3. Klik dua kali pada arahan SUMIFS.
4. Pilih jumlah julat sebagai C2:C759.
5. taip,
6. Pilih julat untuk syarat pertama B2:B759(nilai jenis 1)
7. Pilih kriteria (sel F3, dengan nilai Water)
8. jenis,
9. Pilih julat untuk syarat kedua daripada D2:D759(Nilai penjanaan)
10. taip,
11. Tentukan kriteria (sel G3 mempunyai nilai 1).
12. Tekan enter .

Nota: Anda boleh menambah lebih banyak syarat dengan mengulangi langkah 9 hingga 12 sebelum menekan Enter.

Fungsi ini kini mengira jumlah statistik untuk generasi 1 Pokemon air. Fungsi ini boleh diulang untuk generasi akan datang untuk membandingkannya.

Di atas ialah beberapa contoh cara menggunakan fungsi SUMIFS, mengira jumlah nilai dengan banyak syarat gabungan. Kita mesti menyusun julat dan syarat yang disertakan dengan betul supaya Excel dapat mengenali formula fungsi untuk pengiraan.

Had fungsi SUMIFS

Fungsi SUMIFS mempunyai beberapa batasan yang perlu anda ketahui:

• Keadaan dalam SUMIFS digabungkan menggunakan logik DAN. Dalam erti kata lain, semua syarat mesti BENAR untuk sel dimasukkan dalam jumlah. Untuk menjumlahkan sel menggunakan logik ATAU, anda boleh menggunakan penyelesaian alternatif dalam kes mudah.
• Fungsi SUMIFS memerlukan julat sebenar untuk semua hujah julat; anda tidak boleh menggunakan tatasusunan. Ini bermakna anda tidak boleh melakukan perkara seperti mengekstrak tahun daripada tarikh dalam fungsi SUMIFS. Untuk menukar nilai yang muncul dalam hujah julat sebelum menggunakan kriteria, fungsi SUMPRODUCT ialah penyelesaian yang fleksibel.
• SUMIFS tidak sensitif huruf besar-besaran. Untuk menjumlahkan nilai berdasarkan keadaan sensitif huruf besar-besaran, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT dengan fungsi EXACT.

Cara paling biasa untuk mengatasi had di atas ialah menggunakan fungsi SUMPRODUCT. Dalam versi semasa Excel, pilihan lain ialah menggunakan fungsi BYROW dan BYCOL yang lebih baharu.

Cara membetulkan ralat #VALUE dalam fungsi SUMIFS Excel

Isu: Formula merujuk kepada sel dalam buku kerja tertutup

Fungsi SUMIF yang merujuk sel atau julat sel dalam buku kerja tertutup akan mengakibatkan ralat #VALUE!.

Nota: Ini adalah ralat biasa. Anda juga sering menemui mesej ini apabila menggunakan fungsi Excel lain seperti COUNTIF, COUNTIFS, COUNTBLANK...

Penyelesaian:

Buka buku kerja yang terkandung dalam formula ini, tekan F9 untuk memuat semula formula. Anda juga boleh menyelesaikan masalah dengan menggunakan kedua-dua fungsi SUM dan IF dalam formula tatasusunan.

Isu: Rentetan kriteria lebih panjang daripada 255 aksara

Fungsi SUMIFS mengembalikan hasil yang salah apabila anda cuba menggabungkan rentetan yang lebih panjang daripada 255 aksara.

Penyelesaian:

Pendekkan tali jika boleh. Jika itu tidak mungkin, gunakan fungsi CONCATENATE atau operator & untuk membahagikan nilai kepada berbilang rentetan. Sebagai contoh:

=SUMIF(B2:B12,"long string"&"another long string")

Isu: Dalam SUMIFS, hujah criteria_range tidak konsisten dengan hujah sum_range.

Argumen julat mestilah sentiasa sama dalam SUMIFS. Ini bermakna argumen criteria_range dan sum_range harus merujuk kepada bilangan baris dan lajur yang sama.

Dalam contoh berikut, formula mengembalikan jumlah jualan epal harian di Bellevue. Walau bagaimanapun, hujah sum_range (C2:C10) tidak sepadan dengan bilangan baris dan lajur dalam julat_kriteria (A2:A12 & B2:B12). Menggunakan formula =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)akan memberikan ralat #VALUE.

Penyelesaian:

Tukar sum_rangekepada C2:C12 dan cuba formula sekali lagi.

Semoga berjaya!