Cara Menggunakan Berbilang Syarat dalam Excel COUNTIF dan COUNTIFS

Excel mempunyai banyak fungsi di mana pengguna perlu menentukan satu atau lebih syarat untuk mendapatkan hasilnya.Contohnya, jika anda ingin mengira sel berdasarkan berbilang kriteria, anda boleh menggunakan fungsi COUNTIF atau COUNTIFS dalam Excel.

Tutorial ini merangkumi pelbagai cara untuk menggunakan kriteria tunggal atau berbilang dalam fungsi COUNTIF dan COUNTIFS Excel.

Walaupun saya akan memberi tumpuan terutamanya pada fungsi COUNTIF dan COUNTIFS dalam tutorial ini, semua contoh ini juga boleh digunakan dengan fungsi Excel lain yang mengambil beberapa kriteria sebagai input (seperti SUMIF, SUMIFS, AVERAGEIF dan AVERAGEIFS).

Pengenalan kepada fungsi Excel COUNTIF dan COUNTIFS

Mari kita fahami dahulu cara menggunakan fungsi COUNTIF dan COUNTIFS dalam Excel.

Fungsi Excel COUNTIF (dengan satu kriteria)

Fungsi Excel COUNTIF berfungsi paling baik apabila anda ingin mengira sel berdasarkan satu kriteria.Jika anda ingin mengira berdasarkan berbilang kriteria, gunakan fungsi COUNTIFS.

sintaks

=COUNTIF(julat, keadaan)

Parameter input

• skop -  Julat sel yang anda ingin kira.
• 條件 -Kriteria yang mesti dinilai terhadap julat sel yang akan dikira.

Fungsi Excel COUNTIFS (dengan berbilang kriteria)

Fungsi Excel COUNTIFS berfungsi paling baik apabila anda ingin mengira sel berdasarkan berbilang kriteria.

sintaks

=COUNTIFS(julat_kriteria1,kriteria1,[julat_kriteria2,kriteria2]…)

Parameter input

• julat_kriteria1 – Julat sel yang anda ingin nilai berdasarkan kriteria 1.
• kriteria1 – Kriteria yang anda ingin nilai ialah criterion_range1 untuk menentukan sel yang hendak dinilai.
• [julat_kriteria2] – Julat sel yang anda ingin nilai berdasarkan kriteria 2.
• [kriteria2] – Kriteria yang anda ingin nilai ialah criterion_range2 untuk menentukan sel yang hendak dikira.

Sekarang mari kita lihat beberapa contoh menggunakan berbilang kriteria dalam fungsi COUNTIF Excel.

Gunakan syarat NUMBER dalam fungsi Excel COUNTIF

#1 Kira sel apabila kriteria sama dengan nilai

Untuk mendapatkan kiraan sel di mana parameter keadaan adalah sama dengan nilai yang ditentukan, anda boleh memasukkan syarat secara terus atau menggunakan rujukan sel yang mengandungi syarat tersebut.

Di bawah ialah contoh di mana kita mengira sel yang mengandungi nombor 9 (yang bermaksud parameter keadaan bersamaan dengan 9).Berikut adalah formulanya:

=COUNTIF($B$2:$B$11,D3)

Dalam contoh di atas (dalam gambar), kriteria adalah dalam sel D3.Anda juga boleh memasukkan syarat terus ke dalam formula.Sebagai contoh, anda juga boleh menggunakan:

=COUNTIF($B$2:$B$11,9)

#2 Kira sel apabila keadaan lebih besar daripada nilai

Untuk mendapatkan kiraan sel dengan nilai yang lebih besar daripada nilai yang ditentukan, kami menggunakan operator yang lebih besar (">").Kita boleh menggunakannya secara langsung dalam formula atau kita boleh menggunakan rujukan sel dengan syarat.

Setiap kali kami menggunakan operator dalam keadaan dalam Excel, kami perlu memasukkannya ke dalam petikan berganda.Sebagai contoh, jika syarat lebih besar daripada 10, maka kita perlu memasukkan ">10" sebagai syarat (lihat imej di bawah):

Berikut adalah formulanya:

=COUNTIF($B$2:$B$11,”>10″)

Anda juga boleh memasukkan syarat dalam sel dan menggunakan rujukan sel sebagai syarat.Dalam kes ini, anda tidak perlu meletakkan syarat dalam petikan berganda:

=COUNTIF($B$2:$B$11,D3)

Ini juga boleh berlaku apabila anda mahu keadaan berada dalam sel tetapi tidak mahu menggunakannya dengan pengendali.Sebagai contoh, anda mungkin mahu nombor dalam sel D3 menjadi 10 dan bukannya >10.

Dalam kes ini, anda perlu mencipta parameter bersyarat, iaitu gabungan operator dan rujukan sel (lihat imej di bawah):

=COUNTIF($B$2:$B$11,”>”&D3)

Nota: Apabila anda menggabungkan operator dan rujukan sel, operator sentiasa disertakan dalam petikan berganda.Pengendali dan rujukan sel disambungkan oleh ampersand (&).

#3 Kira sel apabila kriteria kurang daripada nilai

Untuk mendapatkan kiraan sel dengan nilai kurang daripada nilai yang ditentukan, kami menggunakan operator kurang daripada ("<").Kita boleh menggunakannya secara langsung dalam formula atau kita boleh menggunakan rujukan sel dengan syarat.

Setiap kali kami menggunakan operator dalam keadaan dalam Excel, kami perlu memasukkannya ke dalam petikan berganda.Sebagai contoh, jika kriterianya ialah kuantiti hendaklah kurang daripada 5, maka kita perlu memasukkan "<5" sebagai kriteria (lihat imej di bawah):

=COUNTIF($B$2:$B$11,”<5”)

Anda juga boleh memasukkan syarat dalam sel dan menggunakan rujukan sel sebagai syarat.Dalam kes ini, anda tidak perlu meletakkan standard dalam petikan berganda (lihat imej di bawah):

=COUNTIF($B$2:$B$11,D3)

Juga, anda mungkin mahu keadaan berada dalam sel, tetapi tidak dengan pengendali.Sebagai contoh, anda mungkin mahu sel D3 mempunyai nombor 5 dan bukannya <5.

Dalam kes ini, anda perlu mencipta parameter bersyarat yang merupakan gabungan operator dan rujukan sel:

=COUNTIF($B$2:$B$11,"<"&D3)

Nota: Apabila anda menggabungkan operator dan rujukan sel, operator sentiasa disertakan dalam petikan berganda.Pengendali dan rujukan sel disambungkan oleh ampersand (&).

#4 Kira sel dengan berbilang kriteria – antara dua nilai

Untuk mendapatkan kiraan nilai antara dua nilai, kita perlu menggunakan berbilang syarat dalam fungsi COUNTIF.

Berikut ialah dua cara untuk melakukannya:

Kaedah XNUMX: Gunakan fungsi COUNTIFS

Fungsi COUNTIFS boleh mengendalikan berbilang syarat sebagai argumen dan mengira sel hanya jika semua syarat adalah BENAR.Untuk mengira sel dengan nilai antara dua nilai yang ditentukan, seperti 5 dan 10, kita boleh menggunakan fungsi COUNTIFS berikut:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Nota: Formula di atas tidak mengira sel yang mengandungi 5 atau 10.Jika anda ingin memasukkan sel ini, gunakan operator yang lebih besar daripada atau sama dengan (>=) dan kurang daripada atau sama dengan (<=).Berikut adalah formulanya:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Anda juga boleh memasukkan syarat ini dalam sel dan menggunakan rujukan sel sebagai syarat.Dalam kes ini, anda tidak perlu meletakkan standard dalam petikan berganda (lihat imej di bawah):

Anda juga boleh menggunakan gabungan rujukan sel dan pengendali (memasukkan operator terus dalam formula).Apabila menggabungkan operator dan rujukan sel, operator sentiasa disertakan dalam petikan berganda.Pengendali dan rujukan sel disambungkan oleh ampersand (&).

Kaedah 2: Gunakan Dua Fungsi COUNTIF

Jika anda mempunyai berbilang syarat, anda boleh menggunakan COUNTIFS atau mencipta gabungan fungsi COUNTIF.Formula berikut akan melakukan perkara yang sama:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

Dalam formula di atas, kita mula-mula mencari bilangan sel dengan nilai lebih daripada 5 dan kemudian menolak bilangan sel dengan nilai lebih daripada 10.Ini akan memberi kita hasil 5 (iaitu dengan nilai lebih daripada 5 dan kurang daripada atau sama dengan 10).

Jika anda mahu formula mengandungi kedua-dua 5 dan 10, gunakan formula berikut sebagai gantinya:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Jika anda mahu formula mengecualikan "5" dan "10" daripada kiraan, gunakan formula berikut:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Anda boleh memasukkan syarat ini dalam sel dan menggunakan rujukan sel, atau anda boleh menggunakan gabungan pengendali dan rujukan sel.

Gunakan syarat TEXT dalam fungsi Excel

#1 Kira sel apabila keadaan sama dengan teks yang ditentukan

Untuk mengira sel yang mengandungi padanan tepat kepada teks yang ditentukan, kita hanya boleh menggunakan teks tersebut sebagai syarat.Contohnya, dalam set data (ditunjukkan di bawah), jika saya ingin mengira semua sel bernama Joe, saya boleh menggunakan formula berikut:

=COUNTIF($B$2:$B$11,"Joe")

Oleh kerana ini adalah rentetan teks, saya perlu meletakkan syarat teks dalam petikan berganda.

Anda juga boleh memasukkan keadaan dalam sel dan kemudian menggunakan rujukan sel tersebut (seperti ditunjukkan di bawah):

=COUNTIF($B$2:$B$11,E3)

Nota: Anda mungkin mendapat hasil yang salah jika terdapat ruang hadapan/belakang dalam julat keadaan atau keadaan.Pastikan anda membersihkan data sebelum menggunakan formula ini.

#2 Kira sel apabila keadaan tidak sama dengan teks yang ditentukan

Sama seperti yang kita lihat dalam contoh di atas, anda juga boleh mengira sel yang tidak mengandungi teks tertentu.Untuk melakukan ini, kita perlu menggunakan operator not equals (<>).

Dengan mengandaikan anda ingin mengira semua sel yang tidak mengandungi nama JOE, berikut ialah formula yang boleh melakukan ini:

=COUNTIF($B$2:$B$11,”<>Joe”)

Anda juga boleh memasukkan syarat dalam sel dan menggunakan rujukan sel sebagai syarat.Dalam kes ini, anda tidak perlu meletakkan standard dalam petikan berganda (lihat imej di bawah):

=COUNTIF($B$2:$B$11,E3)

Ini juga boleh berlaku apabila anda mahu keadaan berada dalam sel tetapi tidak mahu menggunakannya dengan pengendali.Sebagai contoh, anda mungkin mahu sel D3 mempunyai nama Joe dan bukannya <>Joe.

Dalam kes ini, anda perlu mencipta parameter bersyarat, iaitu gabungan operator dan rujukan sel (lihat imej di bawah):

=COUNTIF($B$2:$B$11,”<>”&E3)

Apabila menggabungkan operator dan rujukan sel, operator sentiasa disertakan dalam petikan berganda.Pengendali dan rujukan sel disambungkan oleh ampersand (&).

Menggunakan Syarat DATE dalam Fungsi COUNTIF dan COUNTIFS Excel

Excel menyimpan tarikh dan masa sebagai nombor.Jadi kita boleh menggunakannya seperti nombor.

#1 Kira sel apabila keadaan sama dengan tarikh yang ditentukan

Untuk mendapatkan kiraan sel yang mengandungi tarikh yang ditentukan, kami akan menggunakan operator sama (=) bersama dengan tarikh.

Untuk bekerja dengan tarikh, saya mengesyorkan menggunakan fungsi DATE, kerana ia menghapuskan sebarang kemungkinan nilai tarikh salah.Jadi, sebagai contoh, jika saya ingin menggunakan tarikh 2015 September 9, saya boleh menggunakan fungsi DATE seperti ini:

=TARIKH(2015,9,1)

Walaupun terdapat perbezaan serantau, formula ini akan mengembalikan tarikh yang sama.Contohnya, 01-09-2015 ialah 2015 September 9 di bawah sintaks tarikh AS dan 1 Januari 2015 di bawah sintaks tarikh UK.Walau bagaimanapun, formula ini akan sentiasa kembali pada 2105 September 9.

Berikut ialah formula untuk mengira bilangan sel yang mengandungi tarikh 02-09-2015:

=COUNTIF($A$2:$A$11,TARIKH(2015,9,2))

#2 Kira sel apabila keadaan sebelum atau selepas tarikh yang ditentukan

Untuk mengira sel yang mengandungi tarikh sebelum atau selepas tarikh yang ditentukan, kami boleh menggunakan kurang daripada/lebih besar daripada operator.

Sebagai contoh, jika saya ingin mengira semua sel yang mengandungi tarikh selepas 2015 September 9, saya boleh menggunakan formula berikut:

=COUNTIF($A$2:$A$11,”>”&TARIKH(2015,9,2))

Begitu juga, anda juga boleh mengira bilangan sel sebelum tarikh yang ditentukan.Jika anda ingin memasukkan tarikh dalam kiraan, gunakan pengendali "sama" bersama-sama dengan pengendali "lebih daripada/kurang daripada".

Anda juga boleh menggunakan rujukan sel yang mengandungi tarikh.Dalam kes ini, anda perlu menggunakan ampersand (&) untuk menggabungkan operator (dalam petikan berganda) dengan tarikh.

Lihat contoh di bawah:

=COUNTIF($A$2:$A$11,”>”&F3)

#3 Kira sel dengan berbilang kriteria – antara dua tarikh

Untuk mendapatkan kiraan nilai antara dua nilai, kita perlu menggunakan berbilang syarat dalam fungsi COUNTIF.

Kita boleh melakukan ini dalam dua cara - satu fungsi COUNTIFS atau dua fungsi COUNTIF.

Kaedah XNUMX: Gunakan fungsi COUNTIFS

Fungsi COUNTIFS boleh mengambil berbilang syarat sebagai argumen dan mengira sel hanya jika semua syarat adalah BENAR.Untuk mengira sel dengan nilai antara dua tarikh tertentu, seperti 9 September dan 2 September, kita boleh menggunakan fungsi COUNTIFS berikut:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Formula di atas tidak mengira sel yang mengandungi tarikh yang ditentukan.Jika anda juga ingin memasukkan tarikh ini, gunakan operator yang lebih besar daripada atau sama dengan (>=) dan kurang daripada atau sama dengan (<=).Berikut adalah formulanya:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Anda juga boleh memasukkan tarikh dalam sel dan menggunakan rujukan sel sebagai kriteria.Dalam kes ini, anda tidak boleh meminta operator memasukkan tarikh dalam sel.Anda perlu menambah operator secara manual pada formula (disertakan dalam petikan berganda) dan menggunakan ampersand (&) untuk menambah rujukan sel.Lihat di bawah:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Kaedah 2: Gunakan fungsi COUNTIF

Jika anda mempunyai berbilang syarat, anda boleh menggunakan fungsi COUNTIFS atau mencipta gabungan dua fungsi COUNTIF.Formula berikut juga akan membantu:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Dalam formula di atas, kita mula-mula mencari bilangan sel yang tarikhnya selepas 9 September, dan kemudian menolak bilangan sel yang tarikhnya selepas 2 September.Ini akan memberikan hasil 9 (iaitu, bilangan sel dengan tarikh selepas 7 September dan pada atau sebelum 7 September).

Jika anda tidak mahu formula mengira kedua-dua 9 September dan 2 September, gunakan formula berikut:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Jika anda ingin mengecualikan dua tarikh daripada kiraan, gunakan formula berikut:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

Selain itu, anda boleh menetapkan tarikh standard dalam sel dan menggunakan rujukan sel (dan pengendali dalam petikan berganda dengan ampersand).

Menggunakan kad bebas dalam keadaan fungsi COUNTIF dan COUNTIFS

Terdapat tiga kad bebas dalam Excel:

1. *(Asterisk) - ia mewakili sebarang bilangan aksara.Contohnya, ex* mungkin bermaksud cemerlang, cemerlang, contoh, pakar, dsb.
2. ? (tanda soal) - Ia mewakili satu watak.Contohnya, Trömp mungkin bermaksud Trump atau Tramp.
3. ~ (tilde) - Digunakan untuk mengenal pasti aksara kad bebas (~, *, ?) dalam teks.

Anda boleh menggunakan fungsi COUNTIF dengan kad bebas untuk mengira sel apabila fungsi pengiraan terbina dalam lain gagal.Sebagai contoh, katakan anda mempunyai set data yang kelihatan seperti ini:

Sekarang mari kita ambil beberapa contoh:

#1 Kira sel yang mengandungi teks

Untuk mengira sel dengan teks di dalamnya, kita boleh menggunakan kad bebas * (asterisk).Memandangkan asterisk mewakili sebarang bilangan aksara, ia akan mengira semua sel yang mengandungi sebarang teks.Berikut adalah formulanya:

=COUNTIFS($C$2:$C$11,"*")

Nota: Formula di atas mengabaikan sel yang mengandungi nombor, sel kosong dan nilai logik, tetapi mengira sel yang mengandungi apostrof (dan oleh itu kelihatan kosong) atau yang mungkin telah disertakan sebagai sebahagian daripada formula.

Berikut ialah tutorial terperinci tentang pengendalian kes dengan rentetan kosong atau apostrof.

#2 Kira sel bukan kosong

Jika anda sedang mempertimbangkan untuk menggunakan fungsi COUNTA, fikirkan semula.

Cubalah, ia mungkin mengecewakan anda.COUNTA juga akan mengira sel yang mengandungi rentetan kosong (biasanya dikembalikan dengan formula sebagai ="", atau apabila orang hanya memasukkan apostrof dalam sel).Sel yang mengandungi rentetan kosong kelihatan kosong tetapi tidak, jadi dikira oleh fungsi COUNTA.

COUNTA juga akan mengira sel yang mengandungi rentetan kosong (biasanya dikembalikan dengan formula sebagai ="", atau apabila orang hanya memasukkan apostrof dalam sel).Sel yang mengandungi rentetan kosong kelihatan kosong tetapi tidak, jadi dikira oleh fungsi COUNTA.

Jadi jika anda menggunakan formula = COUNTA (A1:A11), ia mengembalikan 11 apabila ia sepatutnya mengembalikan 10.

Inilah pembaikan:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Mari kita fahami formula ini dengan memecahkannya:

#3 Kira sel yang mengandungi teks tertentu

Katakan kita mahu mengira semua sel di mana nama wakil jualan bermula dengan J.Ini boleh dicapai dengan mudah dengan menggunakan kad bebas dalam fungsi COUNTIF.Berikut adalah formulanya:

=COUNTIFS($C$2:$C$11,"J*") 

Keadaan J* menentukan bahawa teks dalam sel harus bermula dengan J dan boleh mengandungi sebarang bilangan aksara.

Jika anda ingin mengira sel yang mengandungi huruf di mana-mana dalam teks, kelilinginya dengan asterisk.Contohnya, jika anda ingin mengira sel yang mengandungi huruf "a", gunakan *a* sebagai syarat.

Berbanding dengan artikel saya yang lain, artikel ini luar biasa panjangnya.Harap awak suka.Beritahu saya pendapat anda dengan meninggalkan ulasan.