Formula generik
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Ringkasan
Untuk mengira berapa kali kata tertentu (atau substring) muncul di dalam sel, anda boleh menggunakan formula berdasarkan fungsi SUBSTITUT, LEN, dan SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di C11 adalah:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Catatan: Rumus di halaman ini mengira sebilangan kata dalam julat. Sebagai contoh, jika sel mengandungi dua contoh kata, ia akan menyumbang 2 kepada jumlah keseluruhan. Sekiranya anda hanya mahu mengira sel yang mengandungi kata tertentu, lihat formula ringkas ini berdasarkan fungsi COUNTIF.
Penjelasan
Dalam versi formula umum, rng mewakili rentang yang harus diperiksa, dan txt adalah kata atau substring untuk dihitung.
Dalam contoh yang ditunjukkan, B5: B8 adalah rentang yang harus diperiksa, dan C2 berisi teks (kata atau substring) untuk dihitung.
Untuk setiap sel dalam julat, SUBSTITUTE menghilangkan substring dari teks asal dan LEN mengira panjang teks tanpa substring. Nombor ini kemudian dikurangkan dari panjang teks asalnya. Hasilnya adalah jumlah watak yang dikeluarkan oleh SUBSTITUTE.
Kemudian, bilangan watak yang dikeluarkan dibahagi dengan panjang substring. Oleh itu, jika substring atau kata panjangnya 5 aksara, dan ada 10 aksara yang hilang setelah dikeluarkan dari teks asalnya, kita tahu substring / kata itu muncul dua kali dalam teks asal.
Kerana pengiraan di atas dibungkus dalam fungsi SUMPRODUCT, hasilnya adalah array yang mengandungi nombor untuk setiap sel dalam julat. Nombor-nombor ini mewakili jumlah kejadian substring di setiap sel. Untuk contoh ini, susunan kelihatan seperti ini: (1; 1; 0; 1)
Akhirnya, SUMPRODUCT mengumpulkan semua item dalam array untuk mendapatkan jumlah kejadian substring dalam julat sel.
Mengabaikan kes
SUBSTITUTE adalah fungsi sensitif huruf, jadi ia akan sesuai dengan huruf besar ketika menjalankan penggantian. Sekiranya anda perlu mengira kejadian huruf besar dan kecil dari kata atau substring, gunakan fungsi UPPER di dalam SUBSTITUTE untuk menukar teks ke huruf besar sebelum menjalankan penggantian:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
