Fungsi Excel COUNTIF membolehkan anda mengira bilangan sel yang memenuhi kriteria atau syarat tertentu dalam julat yang diberikan.
Fungsi COUNTIF ialah salah satu fungsi statistik dalam Excel iaitu gabungan fungsi COUNT dan IF atau fungsi COUNTA. Apabila digunakan dalam fomula, fungsi mengira bilangan sel yang sepadan dengan kriteria atau syarat tertentu dalam julat yang sama atau berbilang. Fungsi COUNTIF membantu mengira sel yang mengandungi teks, nombor atau tarikh yang memenuhi kriteria tertentu.
Anda boleh mengira sel menggunakan fungsi COUNTIF atau COUNTIFS dalam Excel. Perbezaan antara fungsi COUNTIF dan COUNTIFS ialah COUNTIF digunakan untuk mengira sel yang memenuhi satu kriteria dalam satu julat, manakala COUNTIFS mengira sel yang memenuhi berbilang syarat dalam julat yang sama atau berbilang.
Artikel ini akan menunjukkan kepada anda cara menggunakan dua fungsi COUNTIF dan COUNTIFS dalam Excel.
Fungsi Excel COUNTIF
Fungsi COUNTIF membolehkan anda melakukan kiraan data berdasarkan kriteria atau syarat tertentu. Keadaan yang digunakan dalam fungsi berfungsi dengan operator logik (, , =, >=, <=) dan aksara kad bebas (*, ?) untuk padanan separa.
Sintaks Fungsi COUNTIF
Struktur fungsi COUNTIF ialah:
=COUNTIF(julat, kriteria)Parameter:
julat– Julat sel untuk dikira.kriteria– Keadaan menentukan sel mana yang harus dimasukkan dalam kiraan dalam julat yang ditentukan. Kriteria boleh menjadi nilai angka, teks, rujukan kepada alamat sel atau persamaan.
Menggunakan Fungsi COUNTIF untuk Mengira Nilai Angka
Seperti yang kita bincangkan di atas, kriteria (hujah kedua) dalam fungsi COUNTIF mentakrifkan keadaan yang memberitahu fungsi sel yang hendak dikira.
Fungsi ini membantu anda mengira bilangan sel dengan nilai yang memenuhi syarat logik seperti sama dengan, lebih besar daripada, kurang daripada, atau tidak sama dengan nilai yang ditentukan, dsb.
Dalam contoh di bawah, formula mengira sel yang mengandungi nilai yang sama dengan 5 (kriteria). Anda boleh terus memasukkan '5 dalam formula atau menggunakan rujukan kepada alamat sel yang mempunyai nilai (sel D2 dalam contoh di bawah).
=COUNTIF(B2:B11,D2)
Formula di atas mengira bilangan sel dalam julat sel (B2:B11) yang mengandungi nilai yang sama dengan nilai dalam sel D2.
Formula berikut mengira sel yang mempunyai nilai kurang daripada 5.
=COUNTIF(B2:B11,"<5")Operator kurang daripada (<) memberitahu formula untuk mengira sel dengan nilai kurang daripada '5' dalam julat B2:B11. Setiap kali anda menggunakan operator dalam keadaan, pastikan anda menyertakannya tanda petikan berganda (“”).
Kadangkala apabila anda ingin mengira sel dengan memeriksanya terhadap kriteria (nilai) dalam sel. Dalam kes sedemikian, buat kriteria dengan menyertai operator dan rujukan sel. Apabila anda berbuat demikian, anda perlu melampirkan pengendali perbandingan dalam petikan berganda (“”), dan kemudian letakkan ampersand (&) antara pengendali perbandingan dan rujukan sel.
=COUNTIF(B2:B11,">="&D2)
Gambar di bawah menunjukkan beberapa contoh formula dan hasilnya.
Menggunakan Fungsi COUNTIF untuk Mengira Nilai Teks
Untuk mengira sel yang mengandungi rentetan teks tertentu, gunakan rentetan teks itu sebagai hujah kriteria atau sel yang mengandungi rentetan teks. Sebagai contoh, dalam jadual di bawah, jika kita ingin mengira semua sel dalam julat (B21:D27) dengan nilai teks dalam sel B21 (sam) di dalamnya, kita boleh menggunakan formula berikut:
=COUNTIF(B21:D27,B21)
Seperti yang kita bincangkan sebelum ini, kita boleh menggunakan teks 'sam' secara langsung dalam formula atau menggunakan rujukan sel yang mempunyai kriteria (B21). Rentetan teks hendaklah sentiasa disertakan dalam petikan berganda (“”) apabila ia digunakan dalam formula dalam excel.
=COUNTIF(B21:D27,"sam")
Untuk mengira sel yang tidak mengandungi teks tertentu, gunakan formula di bawah:
=COUNTIF(B21:D27,""&B21)Pastikan untuk menyertakan 'tidak sama dengan' "" operator dalam petikan berganda.
Jika anda menggunakan teks 'sam' secara langsung dalam formula, anda perlu melampirkan operator '' dan rentetan teks bersama-sama ("sam") dalam petikan berganda.
=COUNTIF(B21:D27,"sam") 
Menggunakan Kad Liar dalam Fungsi COUNTIF Excel (Padanan Separa)
Anda boleh menggunakan formula COUNTIF dengan aksara kad bebas untuk mengira sel yang mengandungi perkataan, frasa atau huruf tertentu. Terdapat tiga aksara kad bebas yang anda boleh gunakan dalam fungsi Excel COUNTIF:
*(asterisk) – Ia digunakan untuk mengira sel dengan sebarang bilangan aksara/huruf permulaan dan akhir. (cth., St* boleh bermaksud Stark, Stork, Stacks, dsb.?(tanda soal) – Ia digunakan untuk mencari sel dengan mana-mana aksara tunggal. (cth., St?rk boleh bermaksud Stark atau Stork.~(tilde) – Ia digunakan untuk mencari dan mengira bilangan sel yang mengandungi tanda soal atau aksara asterisk (~, *, ?) dalam teks.
Mengira Sel Bermula atau Berakhir dengan Aksara Tertentu
Untuk mengira sel yang bermula atau berakhir dengan teks tertentu dengan sebarang bilangan aksara lain dalam sel, gunakan kad bebas asterisk (*) dalam hujah kedua fungsi COUNTIF.
Gunakan formula sampel ini:
=COUNTIF(A1:A10,"A*") – untuk mengira sel yang bermula dengan “A”.
=COUNTIF(A19:A28,"*er") – untuk mengira bilangan sel yang berakhir dengan aksara “er”.
=COUNTIF(A2:A12,"*QLD*") – untuk mengira sel yang mengandungi teks "QLD" di mana-mana dalam rentetan teks.
A ? mewakili tepat satu aksara, gunakan kad bebas ini dalam fungsi COUNTIF di bawah untuk mengira bilangan sel yang mengandungi tepat +1 aksara di mana '?' digunakan.
=COUNTIF(A1:A10,"Par?s")
Mengira Sel Kosong dan Tidak Kosong dengan Fungsi COUNTIF
Formula COUNTIF juga membantu apabila mengira bilangan sel kosong atau tidak kosong dalam julat tertentu.
Kira Sel Bukan Kosong
Jika anda ingin mengira hanya sel yang mengandungi sebarang nilai 'teks', gunakan formula di bawah. Formula ini menganggap sel dengan tarikh dan nombor sebagai sel kosong dan tidak akan memasukkannya ke dalam kiraan.
=COUNTIF(A1:B12,"*")Kad bebas * sepadan dengan hanya nilai teks dan mengembalikan kiraan semua nilai teks dalam julat yang diberikan.
Jika anda ingin mengira semua sel bukan kosong dalam julat tertentu, cuba formula ini:
=COUNTIF(A1:B12,"")
Kira Sel Kosong
Jika anda ingin mengira sel kosong dalam julat tertentu, gunakan fungsi COUNTIF dengan * watak kad bebas dan operator dalam hujah kriteria untuk mengira sel kosong.
Formula ini mengira sel yang tidak mengandungi sebarang nilai teks:
=COUNTIF(A1:B12,""&"*")Sejak * kad bebas sepadan dengan sebarang nilai teks, formula di atas akan mengira semua sel yang tidak sama dengannya *. Ia mengira sel dengan tarikh dan nombor sebagai kosong juga.
Untuk mengira semua tempat kosong (semua jenis nilai):
=COUNTIF(A1:B12,"")Fungsi ini hanya mengira sel kosong dalam julat.
Menggunakan Fungsi COUNTIF untuk Mengira Tarikh
Anda boleh mengira sel dengan tarikh (sama seperti yang anda lakukan dengan kriteria nombor) yang memenuhi syarat logik atau tarikh atau tarikh yang ditentukan dalam sel rujukan.
Untuk mengira sel yang mengandungi tarikh yang ditentukan (05-05-2020), kami akan menggunakan formula ini:
=COUNTIF(B2:B10,"05-05-2020")
Anda juga boleh menentukan tarikh dalam format yang berbeza sebagai kriteria dalam fungsi COUNTIF seperti yang ditunjukkan di bawah:
Jika anda ingin mengira sel yang mengandungi tarikh sebelum atau selepas tarikh tertentu, gunakan operator kurang daripada (sebelum) atau lebih besar daripada (selepas) bersama-sama dengan tarikh atau rujukan sel tertentu.
=COUNTIF(B2:B10,">=05/05/2020")
Anda juga boleh menggunakan rujukan sel yang mengandungi tarikh dengan menggabungkannya dengan operator (dalam petikan berganda).
Untuk mengira bilangan sel dalam julat A2:A14 dengan tarikh sebelum tarikh dalam E3, gunakan formula di bawah, dengan pengendali yang lebih besar daripada (<) bermaksud sebelum tarikh dalam E3.
=COUNTIF(A2:A14,"<"&E3)
Beberapa contoh formula dan hasilnya:
Kira Tarikh berdasarkan Tarikh Semasa
Anda boleh menggabungkan fungsi COUNTIF dengan fungsi Tarikh Excel tertentu iaitu, TODAY() untuk mengira sel yang mempunyai tarikh semasa.
=COUNTIF(A2:A14,">"&TODAY())Fungsi ini mengira semua tarikh dari hari ini dalam julat (A2:A14).
Kira Tarikh antara Julat Tarikh Tertentu
Jika anda ingin mengira semua tarikh antara dua tarikh, anda perlu menggunakan dua kriteria dalam formula.
Kita boleh melakukan ini dengan menggunakan dua kaedah: fungsi COUNTIF dan COUNTIFS.
Menggunakan fungsi Excel COUNTIF
Anda perlu menggunakan dua fungsi COUNTIF untuk mengira semua tarikh antara dua tarikh yang ditentukan.
Untuk mengira tarikh antara '09-02-2020' dan '20-08-2021', gunakan formula ini:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Formula ini mula-mula mencari bilangan sel yang mempunyai tarikh selepas 2 Februari dan menolak kiraan sel dengan tarikh selepas 20 Ogos. Sekarang kita mendapat no. sel yang mempunyai tarikh yang datang selepas 2 Februari dan pada atau sebelum 20 Ogos (kiraan ialah 9).
Jika anda tidak mahu formula mengira 2 Februari dan 20 Ogos, gunakan formula ini sebaliknya:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Hanya gantikan operator ‘>’ dengan ‘>=’ dalam kriteria kedua.
Menggunakan fungsi Excel COUNTIFS
Fungsi COUNTIFS juga menyokong pelbagai kriteria dan tidak seperti fungsi COUNTIF, ia mengira sel hanya selepas semua syarat dipenuhi. Jika anda ingin mengira sel dengan semua tarikh antara dua tarikh yang ditentukan, masukkan formula ini:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
Jika anda ingin memasukkan tarikh yang ditentukan juga dalam kiraan, gunakan operator ‘>=’ dan ‘<=’. Di sini, gunakan formula ini:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")Kami menggunakan tarikh secara langsung dalam kriteria dan bukannya rujukan sel untuk contoh ini.
Cara Mengendalikan COUNTIF dan COUNTIFS dengan Pelbagai Kriteria dalam Excel
Fungsi COUNTIF kebanyakannya digunakan untuk mengira sel dengan kriteria tunggal (keadaan) dalam satu julat. Tetapi anda masih boleh menggunakan COUNTIF untuk mengira sel yang sepadan dengan berbilang keadaan dalam julat yang sama. Walau bagaimanapun, fungsi COUNTIFS boleh digunakan untuk mengira sel yang memenuhi berbilang keadaan dalam julat yang sama atau berbeza.
Cara Mengira Nombor Dalam Julat
Anda boleh mengira sel yang mengandungi nombor antara dua nombor yang ditentukan menggunakan dua fungsi: COUNTIF dan COUNTIFS.
COUNTIF untuk Mengira Nombor Antara Dua Nombor
Salah satu kegunaan biasa untuk fungsi COUNTIF dengan berbilang kriteria ialah mengira nombor antara dua nombor yang ditentukan, mis. untuk mengira nombor yang lebih besar daripada 10 tetapi kurang daripada 50. Untuk mengira nombor dalam julat, gabungkan dua atau lebih fungsi COUNTIF bersama-sama dalam satu formula. Biar kami tunjukkan caranya.
Katakan anda ingin mengira sel dalam julat B2:B9 dengan nilai lebih besar daripada 10 dan kurang daripada 21 (tidak termasuk 10 dan 21), gunakan formula ini:
=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")Perbezaan antara dua nombor didapati dengan menolak satu formula daripada yang lain. Formula pertama mengira nombor lebih besar daripada 10 (iaitu 7), formula kedua mengembalikan kiraan nombor lebih besar daripada atau sama dengan 21 (iaitu 4), dan hasil formula kedua ditolak daripada formula pertama (7 -4) untuk mendapatkan kiraan nombor antara dua nombor (3).
Jika anda ingin mengira sel dengan nombor lebih besar daripada 10 dan kurang daripada 21 dalam julat B2:B14, termasuk nombor 10 dan 21, gunakan formula ini:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS untuk Mengira Nombor Antara 2 Nombor
Untuk mengira nombor antara 10 dan 21 (tidak termasuk 10 dan 21) terkandung dalam sel B2 hingga B9, gunakan formula ini:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
Untuk memasukkan 10 dan 21 dalam kiraan, hanya gunakan 'lebih besar daripada atau sama dengan' (>=) bukannya 'lebih besar daripada' dan 'kurang daripada atau sama dengan' (<=) dan bukannya operator 'kurang daripada' dalam formula .
COUNTIFS untuk Mengira Sel dengan Pelbagai Kriteria (DAN Kriteria)
Fungsi COUNTIFS ialah pasangan jamak bagi fungsi COUNTIF yang mengira sel berdasarkan dua atau lebih kriteria dalam julat yang sama atau berbilang. Ia dikenali sebagai 'DAN logik' kerana fungsi ini dibuat untuk mengira sel hanya apabila semua syarat yang diberikan adalah BENAR.
Sebagai contoh, kita ingin mengetahui berapa kali (kiraan sel) roti itu (nilai dalam lajur A) telah dijual kurang daripada 5 (nilai dalam lajur C).
Kita boleh menggunakan formula ini:
=COUNTIFS(A2:A14,"Roti",C2:C14,"<5")
COUNTIF untuk Mengira Sel dengan Berbilang Kriteria (OR Kriteria)
Jika anda ingin mengira bilangan sel yang memenuhi berbilang kriteria dalam julat yang sama, gabungkan dua atau lebih fungsi COUNTIF bersama-sama. Sebagai contoh, jika anda ingin mengetahui berapa kali 'Roti' atau 'Keju' diulang dalam julat yang ditentukan (A2:A14), gunakan formula di bawah:
=COUNTIF(A2:A14,"Roti")+COUNTIF(A2:A14,"Keju")Formula ini mengira sel yang mana sekurang-kurangnya satu syarat adalah BENAR. Itulah sebabnya ia dipanggil 'OR logik'.
Jika anda ingin menilai lebih daripada satu kriteria dalam setiap fungsi, lebih baik menggunakan COUNTIFS dan bukannya COUNTIF. Dalam contoh di bawah, kami ingin mendapatkan kiraan status "Dipesan" dan "Dihantar" untuk 'Roti', jadi kami akan menggunakan formula ini:
=COUNTIFS(A2:A14,"Roti",C2:C14,"Dipesan")+COUNTIFS(A2:A14,"Roti",C2:C14,"Dihantar")
Kami berharap ini mudah, tetapi tutorial yang panjang akan memberi anda sedikit idea tentang cara menggunakan fungsi COUNTIF dan COUNTIF dalam Excel.