Rumus untuk menghitung jumlah kemunculan String teks dalam suatu range pada Excel

Rumus untuk menghitung jumlah kemunculan String teks dalam suatu range

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

dimana berbagai adalah kisaran sel yang bersangkutan dan "teks" digantikan oleh string teks tertentu yang ingin Anda hitung.

Rumus harus dibagi dengan panjang string teks karena jumlah panjang karakter pada range akan menurun beberapa dari setiap kemunculan string teks. Rumus ini dapat menggantikan semua rumus berikutnya dalam artikel ini kecuali rumus untuk menghitung jumlah kata dalam sel.

Contoh 1: Menghitung jumlah munculnya string teks dalam range

1. Memulai Excel, dan kemudian membuka buku kerja baru.
2. Ketik data berikut pada sheet1:

   A1: Fruit
   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 dalam range.
Catatan: Rumus di atas harus dimasukkan sebagai array formula. Untuk memasukkan rumus sebagai array dalam Excel untuk Windows, tekan CTRL + SHIFT + ENTER. Untuk memasukkan rumus sebagai array di Excel untuk Macintosh, tekan COMMAND + RETURN.

Rumus untuk menghitung jumlah kemunculan satu karakter dalam satu sel.

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

dimana cell_ref referensi sel, dan "" digantikan oleh karakter yang ingin Anda hitung.

Catatan: Rumus ini tidak perlu dimasukkan sebagai array formula.

Contoh 2: Menghitung jumlah munculnya karakter dalam satu sel.

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

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

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

Rumus untuk menghitung jumlah kemunculan satu karakter dalam range

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

mana berbagai kisaran sel yang bersangkutan, dan "" digantikan oleh karakter Anda ingin menghitung.

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

Contoh 3: Menghitung jumlah kemunculan karakter dalam satu range

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

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

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

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

Formula untuk menghitung jumlah kata-kata yang dipisahkan oleh sebuah karakter dalam sel

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

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

Catatan: Tidak ada spasi dalam rumus diatas. beberapa baris yang digunakan hanya agar rumus sesuai pada dokumen ini. Tidak termasuk spasi ketika Anda mengetik itu ke dalam sel. Rumus ini tidak perlu dimasukkan sebagai array formula.

Contoh 4: Menghitung jumlah kata-kata yang dipisahkan oleh spasi dalam sel

Untuk menghitung jumlah kata dalam sel dimana kata-kata dipisahkan oleh sebuah karakter ruang, ikuti langkah berikut:

1. Memulai Excel, dan kemudian membuka buku kerja baru.
2. Ketik data berikut pada sheet1:
   A1: Mobil melaju cepat
   A2: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN (PENGGANTI (A1,"","")) + 1)

Rumus sel A2 mengembalikan nilai 4 untuk mencerminkan bahwa string mengandung empat kata dipisah dengan spasi. Jika kata-kata yang dipisahkan oleh beberapa ruang atau jika kata-kata mulai atau berakhir dalam ruang, tidak masalah. Fungsi TRIM menghapus spasi karakter dan mulai dan berakhir ruang karakter dalam teks sel.

Dalam Excel, Anda juga dapat menggunakan makro untuk menghitung kemunculan karakter tertentu dalam sel, atau kisaran sel. 

Sumber: http://support.microsoft.com/kb/213889/id-id