Formula umum
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Ringkasan
Jika Anda perlu menghitung berapa kali kata tertentu (atau substring) muncul di dalam sel, Anda bisa menggunakan rumus yang menggunakan SUBSTITUTE dan LEN. Dalam bentuk umum rumus di atas, "teks" mewakili sel yang berisi teks, dan "kata" mewakili kata atau substring yang dihitung. Dalam contoh, kami menggunakan rumus ini:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Penjelasan
B4 adalah sel tempat kita menghitung kata, dan C4 berisi substring (kata atau substring apa pun) yang Anda hitung.
SUBSTITUTE menghapus substring dari teks asli dan LEN menghitung panjang teks tanpa substring. Jumlah ini kemudian dikurangi dari panjang teks asli. Hasilnya adalah jumlah karakter yang telah dihapus oleh SUBSTITUTE.
Terakhir, jumlah karakter yang dihapus dibagi dengan panjang substring. Jadi, jika substring terdiri dari 5 karakter, dan ada 10 karakter yang hilang setelah dihapus dari teks asli, kami tahu bahwa substring tersebut muncul dua kali dalam teks aslinya.
Kasus penanganan
SUBSTITUTE adalah fungsi case-sensitive, jadi akan cocok dengan case saat menjalankan substitusi. Jika Anda perlu menghitung kemunculan huruf besar dan kecil dari sebuah kata atau substring, gunakan fungsi UPPER di dalam SUBSTITUTE untuk mengonversi teks menjadi huruf besar sebelum menjalankan substitusi:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Karena rumus ini mengonversi substring dan teks menjadi huruf besar sebelum melakukan substitusi, rumus ini akan berfungsi sama baiknya dengan teks dalam kasus apa pun.
Penanganan masalah
Menghitung kata di Excel itu rumit karena Excel tidak mendukung ekspresi reguler. Akibatnya, sulit untuk menargetkan kata-kata yang ingin Anda hitung dengan tepat, saat mengabaikan substring dan kecocokan parsial lainnya (misalnya, temukan "rubah" tetapi bukan "rubah"). Variasi tanda baca dan huruf membuat masalah ini cukup menantang.
Salah satu solusinya adalah dengan menggunakan rumus lain di kolom pembantu untuk "menormalkan teks" sebagai langkah pertama. Kemudian gunakan rumus di halaman ini untuk menghitung kata yang dibungkus dalam karakter spasi untuk mendapatkan hitungan yang akurat (yaitu Anda dapat mencari "rubah" dalam teks yang dinormalisasi.
Catatan: pendekatan ini hanya sebaik teks yang dinormalisasi yang dapat Anda buat, dan Anda mungkin perlu menyesuaikan rumus normalisasi berkali-kali untuk mendapatkan hasil yang Anda butuhkan.
