Cara menghitung kemunculan teks, karakter dan kata-kata pada Excel

Rumus untuk Menghitung Jumlah Kemunculan Untaian Karakter Teks dalam Rentang

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

dengan rentang adalah rentang sel yang dipertanyakan dan "teks" diganti oleh untaian karakter teks tertentu yang ingin Anda hitung.


Catatan Formula di atas harus dimasukkan sebagai rumus array. Untuk memasukkan rumus sebagai array di Excel untuk Windows, tekan CTRL+SHIFT+ENTER. Untuk memasukkan rumus sebagai array di Excel untuk Macintosh, tekan COMMAND+RETURN.

Rumus harus dipisahkan oleh panjang untaian karakter teks karena jumlah panjang karakter dari rentang dikurangi oleh kelipatan setiap kemunculan untaian karakter teks. Rumus ini dapat mengganti semua rumus terbaru di artikel ini kecuali rumus untuk menghitung jumlah kata-kata di sel.

Contoh 1: Menghitung Jumlah Kemunculan Untaian Karakter Teks dalam Rentang

1. Mulai Excel, kemudian buka buku kerja baru.
2. Ketik berikut ini pada lembar1:

   A1: Buah
   A2: apple,apple
   A3: orange
   A4: apple,orange
   A5: grape
   A6: orange,grape
   A7: grape, apple
   A8: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple")
    

Nilai sel A8 adalah 4 karena teks "apple" muncul empat kali di dalam rentang.

Rumus untuk Menghitung Jumlah Kemunculan Karakter Tunggal dalam Satu Sel

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,"a",""))

di mana cell_ref adalah referensi sel, dan "a" diganti oleh karakter yang ingin Anda hitung.


Catatan Rumus ini tidak perlu dimasukkan sebagai rumus array.

Contoh 2: Menghitung Jumlah Kemunculan Karakter di Satu Sel

Menggunakan data yang sama dari contoh sebelumnya; dengan asumsi Anda ingin menghitung jumlah kemunculan karakter "p" di A7. Ketik rumus berikut ini di sel A9:

A9: =LEN(A7)-LEN(SUBSTITUTE(A7,"p",""))

Nilai dari sel A9 adalah 3 karena karakter "p" muncul tiga kali di sel A7.

Rumus untuk Menghitung Jumlah Kemunculan Karakter Tunggal dalam Rentang

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))

di mana rentang adalah rentang sel yang dipertanyakan, dan "a" diganti oleh karakter yang ingin Anda hitung.


Catatan Formula di atas harus dimasukkan sebagai rumus array. Untuk memasukkan rumus sebagai rumus array di Excel, tekan CTRL+SHIFT+ENTER. 

Contoh 3: Menghitung Jumlah Kemunculan Karakter dalam Rentang

Menggunakan data yang sama dari contoh sebelumnya; dengan asumsi Anda ingin menghitung jumlah kemunculan atau karakter "p" di A2:A7. Ketik rumus berikut ini di sel A10:

A10: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"p","")))

Catatan Formula di atas harus dimasukkan sebagai rumus array. Untuk memasukkan rumus sebagai rumus array di Excel, tekan CTRL+SHIFT+ENTER.


Nilai dari sel A10 adalah 11 karena karakter "p" muncul 11 kali di sel A2:A7.

Rumus untuk Menghitung Jumlah Kata-kata yang Dipisahkan oleh Karakter dalam Sel

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,""))+1)

di mana cell_ref adalah referensi sel, dan char adalah karakter yang memisahkan kata-kata.


Catatan Tidak ada spasi dalam rumus di atas; beberapa baris hanya digunakan untuk menyesuaikan rumus ke dalam dokumen ini. Jangan menyertakan spasi apapun saat Anda mengetiknya ke dalam sel. Rumus ini tidak perlu dimasukkan sebagai rumus array.

Contoh 4: Menghitung Jumlah Kata-kata yang Dipisahkan oleh Spasi dalam Sel

Untuk menghitung jumlah kata-kata dalam sel di mana kata-kata dipisahkan oleh karakter spasi, ikuti langkah-langkah berikut ini:

1. Mulai Excel, kemudian buka buku kerja baru.
2. Ketik berikut ini pada lembar1:
   A1: The car drove fast
   A2: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

Rumus di sel A2 mengembalikan nilai 4 untuk merefleksikan bahwa untaian berisi empat kata yang dipisahkan oleh spasi. Jika kata-kata dipisahkan oleh beberapa spasi atau jika kata-kata dimulai atau diakhiri dalam spasi, ini bukanlah masalah. Fungsi TRIM menghapus karakter spasi ekstra dan memulai serta mengakhiri karakter spasi di dalam teks dari sel.


Di Excel, Anda juga dapat menggunakan makro untuk menghitung kemunculan karakter tertentu dalam sel, atau rentang sel. 
Sumber : support.microsoft.com