Fungsi COUNTIFS, cara menggunakan fungsi kiraan sel mengikut berbilang syarat dalam Excel

Fungsi COUNTIFS dalam Excel mempunyai banyak aplikasi praktikal. Inilah yang anda perlu tahu tentang menggunakan fungsi COUNTIFS dalam Excel .

Microsoft Excel ialah perisian hamparan yang terkenal di dunia dengan banyak ciri berguna. Kemunculan perisian menjadikan kemasukan data dan pengiraan siri nombor lebih mudah berbanding sebelum ini. Dan apabila menggunakan Excel, anda pasti perlu mengetahui fungsi COUNTIFS .

Fungsi COUNTIFS dalam Excel digunakan untuk mengira sel yang memenuhi banyak syarat tertentu. Fungsi COUNTIFS ialah salah satu fungsi Excel statistik yang paling banyak digunakan dalam Excel. Ia adalah fungsi lanjutan bagi fungsi COUNTIF yang hanya mengira sel dengan keadaan tertentu. Apabila bekerja dengan fungsi COUNTIFS, pengguna boleh mencari sel hasil yang memenuhi syarat yang diberikan dalam permintaan dengan mudah. Syarat boleh berupa nombor, tarikh, teks atau sel yang mengandungi data. Artikel di bawah akan membimbing anda cara menggunakan fungsi COUNTIFS dalam Excel.

• Cara membandingkan data pada 2 lajur Excel
• Cara menggunakan fungsi DSUM untuk mengira jumlah dengan keadaan kompleks dalam Excel
• Cara menggunakan fungsi SUMIF untuk mengira jumlah bersyarat dalam Excel
• Penggunaan fungsi VLOOKUP dan contoh khusus

Arahan untuk menggunakan fungsi COUNTIFS dalam Excel

Sintaks fungsi COUNTIFS ialah =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…) .

Di sana:

• Criteria_range1 ialah julat pilihan pertama yang memerlukan statistik, nilai yang diperlukan.
• Kriteria1 ialah syarat yang digunakan pada kriteria_julat1 pemilihan, nilai yang diperlukan boleh menjadi sel, ungkapan atau teks.
• [julat_kriteria2, kriteria2] ialah pasangan pemilihan dan syarat tambahan, membenarkan sehingga 127 pasangan pemilihan dan syarat.

Perhatikan apabila menggunakan fungsi COUNTIFS:

• Pilihan tambahan perlu mempunyai bilangan baris dan lajur yang sama dengan kawasan criteria_range1 dan boleh dipisahkan antara satu sama lain.
• Jika syarat pemilihan merujuk kepada sel kosong, fungsi COUNTIFS secara automatik menganggap nilai sebagai 0.
• Watak ? boleh digunakan. Untuk menggantikan aksara tertentu, tanda * menggantikan keseluruhan rentetan aksara. Jika anda perlu mencari tanda? atau tanda * sebenar, taip ~ di hadapan watak itu.
• Ambil perhatian bahawa fungsi COUNTIFS tidak sensitif huruf besar.
• Secara umum, nilai teks perlu disertakan dalam petikan, tetapi nombor tidak. Walau bagaimanapun, apabila pengendali logik disertakan dengan nombor, nombor dan operator mesti disertakan dalam petikan seperti yang ditunjukkan di bawah:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Nota : Syarat tambahan mesti mengikut peraturan yang sama.

• Apabila menggunakan nilai daripada sel lain dalam keadaan, rujukan sel mesti digabungkan dengan operator apabila digunakan. Dalam contoh di bawah, COUNTIFS akan mengira nilai dalam A1:A10 yang kurang daripada nilai dalam sel B1. Ambil perhatian bahawa operator kurang daripada (iaitu teks) disertakan dalam petikan, tetapi rujukan sel tidak diperlukan:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

Nota : COUNTIFS ialah salah satu daripada beberapa fungsi yang membahagikan keadaan kepada dua bahagian: Julat + kriteria. Ini menyebabkan beberapa ketidakkonsistenan dengan formula dan fungsi lain.

• COUNTIFS boleh mengira sel kosong atau bukan kosong. Formula di bawah mengira sel kosong dan bukan kosong dalam julat A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• Cara paling mudah untuk menggunakan COUNTIFS dengan tarikh ialah merujuk kepada tarikh yang sah dalam sel lain yang mempunyai rujukan sel. Contohnya, untuk mengira sel dalam A1:A10 yang mengandungi tarikh yang lebih besar daripada tarikh dalam B1, anda boleh menggunakan formula seperti ini:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Contoh cara menggunakan fungsi COUNTIFS dalam Excel

1. Lembaran data No. 1

Kami mempunyai jadual data di bawah untuk membuat beberapa permintaan kepada jadual.

Contoh 1: Kira bilangan pekerja lelaki dengan 25 hari bekerja.

Dalam sel di mana kita perlu memasukkan hasil, masukkan formula =COUNTIFS(C2:C7,"Male",D2:D7,25) dan tekan Enter.

Di sana:

• C2:C7 ialah kawasan 1 kiraan wajib dengan Jantina pekerja.
• Selatan ialah keadaan kawasan kiraan 1.
• C2:C7 ialah kawasan kiraan ke-2 dengan Hari Kerja pekerja.
• 25 ialah keadaan kiraan 2 wilayah.

Hasilnya, kami mempunyai 2 pekerja, Nam, dengan 25 hari bekerja.

Contoh 2: Kira bilangan pekerja lelaki yang hari cutinya ialah 0.

Dalam kotak input formula, masukkan =COUNTIFS(C2:C7,"Nam",E2:E7,0) dan tekan Enter.

Keputusan menunjukkan 1 pekerja lelaki dengan 0 hari cuti.

Contoh 3: Kira bilangan pekerja lelaki yang mempunyai 25 hari bekerja dan mempunyai hari cuti kurang daripada atau sama dengan 2 hari.

Kami memasukkan formula =COUNTIFS(C2:C7,"Male",D2:D7,25,E2:E7,"<> dan kemudian tekan Enter.

Akibatnya, terdapat 2 orang pekerja lelaki yang memenuhi syarat cuti kurang daripada atau sama dengan 2 hari.

2. Nombor helaian data 2

Contoh 1: Kira jumlah produk Taiwan dengan harga seunit kurang daripada 200,000 VND.

Dalam kotak input hasil, kami masukkan formula pengiraan =COUNTIFS(B2:B7,"*Taiwan",C2:C7,"<> dan kemudian tekan Enter.

Hasilnya ialah 2 produk Taiwan yang memenuhi keperluan.

Contoh 2: Kira jumlah produk Taiwan dengan harga seunit antara 100,000 VND dan 150,000 VND.

Dalam kotak input hasil, masukkan formula =COUNTIFS(B2:B7,"*Taiwan",C2:C7,">100000",C2:C7,"<> kemudian tekan Enter.

Hasilnya, terdapat 2 produk Taiwan berharga antara 100,000 VND - 150,000 VND.

Cara menggunakan fungsi COUNTIFS digabungkan dengan SUM dalam Excel

Fungsi COUNTIFS pada asasnya ialah versi yang lebih kompleks bagi fungsi COUNTIF. Kelebihan utama COUNTIFS berbanding COUNTIF ialah ia menyokong berbilang syarat dan julat.

Walau bagaimanapun, anda juga boleh menentukan julat dan satu syarat untuk fungsi COUNTIFS, sama seperti yang anda lakukan dengan fungsi COUNTIF.

Satu perkara penting yang perlu anda fahami tentang fungsi COUNTIFS sebelum menggunakannya ialah ia melakukan lebih daripada sekadar menjumlahkan hasil sel yang memenuhi kriteria untuk setiap julat sel.

Sebenarnya, jika anda mempunyai dua syarat untuk dua julat, sel dalam julat pertama ditapis dua kali: Sekali melalui keadaan pertama dan kemudian melalui syarat kedua. Ini bermakna fungsi COUTNIFS hanya akan mengembalikan nilai yang memenuhi kedua-dua syarat, dalam julat yang diberikan.

Anda boleh lebih memahami fungsi fungsi COUNTIFS dengan mengkaji contoh di bawah.

Contoh fungsi COUNITFS dalam Excel

Selain warna epal, terdapat juga lajur yang menerangkan saiznya. Matlamat utama dalam contoh ini adalah untuk mengira bilangan epal merah yang besar.

1. Pilih sel di mana anda ingin memaparkan keputusan. (Dalam contoh ini, artikel akan memaparkan sejumlah besar epal merah dalam sel E3).

2. Pergi ke bar formula dan masukkan formula di bawah:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. Dengan arahan ini, formula menyemak sel A3 hingga A11 untuk keadaan "Merah". Sel yang lulus ujian ini kemudiannya diuji semula dalam julat B3 hingga B11 untuk keadaan "Besar" .

4. Tekan butang Enter.

5. Sekarang, Excel akan mengira bilangan epal merah yang besar.

Contoh hasil fungsi Excel COUNTIFS

Perhatikan bagaimana formula mengira sel yang mempunyai kedua-dua sifat merah dan besar. Formula mengambil sel A3 hingga A11 dan mengujinya untuk mencari keputusan merah. Sel yang melepasi keadaan ini kemudiannya diuji semula terhadap keadaan seterusnya dalam julat kedua, dalam kes ini, keadaan besar.

Kesimpulannya, julat dan keadaan selepas julat dan keadaan pertama semakin menyempitkan penapis pengiraan dan tidak bebas antara satu sama lain. Jadi, hasil akhir resipi adalah epal merah dan besar. Anda boleh mengira bilangan epal merah atau besar dengan menggabungkan fungsi COUNTIF dengan fungsi SUM .

1. Pilih sel di mana anda ingin memaparkan hasil formula. (Dalam contoh ini, artikel akan menggunakan sel E6).

2. Masukkan formula di bawah:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Formula ini akan mengira sel yang mengandungi epal merah, kemudian bilangan sel yang mengandungi epal besar, dan akhirnya, ia akan menjumlahkan dua nombor ini.

4. Tekan butang Enter.

5. Excel kini akan mengira dan memaparkan bilangan epal besar atau merah.

Gabungkan fungsi COUNTIF dengan fungsi SUM

Di atas ialah 2 jadual data dengan penggunaan untuk fungsi COUNTIFS dalam Excel. Pengguna mesti menulis syarat yang mengiringi kawasan data keadaan untuk fungsi COUNTIFS untuk menentukan dengan betul.

Cara membetulkan ralat biasa apabila menggunakan fungsi COUNTIFS dalam Excel

COUNTIFS tidak berfungsi semasa mengira nilai teks

Semasa mengira rentetan teks, ia mesti dimasukkan ke dalam tanda petikan. Jika tidak, fungsi COUNTIFS tidak akan dapat mengira rentetan teks dan akan mengembalikan nilai 0. Dalam imej contoh berikut, rentetan teks belum diletakkan dalam tanda petikan. Oleh itu, formula ini mengembalikan 0.

Untuk membetulkan ralat ini, anda hanya perlu menulis semula formula dengan tepat: =COUNTIFS(E5:E12, "Kereta")

COUNTIFS tidak berfungsi kerana rujukan julat adalah salah

Apabila menggunakan lebih daripada satu kriteria dalam fungsi COUNTIFS, julat sel untuk kriteria lain mesti mempunyai bilangan sel yang sama. Jika tidak, fungsi COUNTIFS tidak akan berfungsi.

Katakan dalam contoh ini kita mahu mengira jualan kereta di Austin. Formula yang dimasukkan ialah =COUNTIFS(E5:E12,"Kereta",D5:D11,"Austin"). Melihat dengan teliti formula, anda akan melihat bahawa julat kriteria pertama di sini ialah E5:E12 tetapi julat kriteria kedua ialah D5:D11. Bilangan sel dalam julat untuk kriteria ini tidak sama.

Sekarang tekan Enter , formula akan mengembalikan #VALUE! .

Tulis semula formula dengan betul seperti berikut: =COUNTIFS(E5:E12,"Kereta",D5:D12,"Austin")

COUNTIFS tidak berfungsi kerana ralat dalam formula

Jika formula tidak dimasukkan dengan betul, fungsi COUNTIFS tidak akan berfungsi. Apabila menggunakan mana-mana pengendali matematik, seperti lebih besar daripada (>), kurang daripada (<), sama dengan="" (=")" dan "not="" equals=""> <> ) , kedua-dua operator dan kriteria berangka mesti dimasukkan dalam persamaan yang sama. Contohnya, jika anda ingin mencari jualan melebihi $100,000, anda perlu memasukkan formula berikut:

=COUNTIFS(F5:F12,">" 100000)

Di sini, hanya pengendali yang dimasukkan ke dalam persamaan, tiada kriteria berangka.

Jika anda menekan Enter, kotak dialog mesej Microsoft Excel akan memaparkan: "Ada masalah dengan formula ini".

Untuk menyelesaikan masalah. Taip formula yang tepat:

=COUNTIFS(F5:F12,">100000")

Sekarang kita telah memasukkan kedua-dua pengendali dan kriteria di dalam kurungan. Sekarang formula ini akan mengembalikan kuantiti yang diperlukan.

Tekan enter.

Hasilnya, anda akan menerima jumlah jualan lebih daripada 100,000USD.

Semoga berjaya!