Menghitung Jumlah Kata Tertentu Pada Cell dan Range

Masih terkait dengan pengolahan data text, dalam kesempatan ini kita akan membahas contoh rumus untuk menghitung jumlah kata tertentu dalam sebuah sel dan range. Istilah “kata” disini lebih tepatnya disebut sub string, yaitu sekumpulan karakter dalam susunan tertentu. Untuk menghitung jumlah kata tertentu diperlukan bantuan beberapa fungsi excel. Secara umum fungsi yang paling diperlukan adalah LEN dan SUBSTITUTE. Bantuan fungsi UPPER atau LOWER digunakan jika kita hendak menghitung jumlah kata tanpa mempedulikan huruf besar maupun kecil. Sedangkan fungsi SUMPRODUCT diperlukan untuk menghitung jumlah kata tertentu sekaligus dalam sebuah range.

Contoh Soal.

Anggaplah sudah tersedia sejumlah text kalimat. Masing-masing kalimat terletak dalam sel A2, A3 dan A4, atau terletak dalam rangel A2:A4. Kemudian kita hendak menghitung ada berapa sich jumlah text "Kota" dalam sel-sel tersebut. 

Contoh kasus soal diperlihatkan dalam gambar berikut:

Untuk menjawab soal diatas perlu diperhatikan beberapa hal, antara lain:

1. Apakah jumlah kata tertentu akan yang dihitung terletak dalam satu sel atau dalam range. 
2. Apakah kata bersifat case sensitive atau tidak.

Selanjutnya mari kita bahas dengan menampilkan contoh rumus.

Menghitung jumlah kata tertentu dalam sel dengan membedakan huruf besar dan kecil (case sensitive)

Rumus umum

=(LEN(sel)-LEN(SUBSTITUTE(sel,kataDihitung,"")))/LEN(kataDihitung)

Formula diatas dapat diterjemahkan kira-kira begini: Menghitung selisih antara jumlah karakter text penuh dalam sebuah sel dengan jumlah karakter text yang sama namun yang sudah dihilangkan kata kataDihitung. Kemudian membagi hasilnya dengan jumlah karakter dalam kataDihitung.

Parameter kataDihitung bisa kita definisikan sendiri, misalnya kata "Kota".  Sehingga rumus tersebut jika diterapkan dalam contoh soal diatas maka dapat dituliskan sebagai berikut:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"Kota","")))/LEN("Kota")

Berikut contoh penerapannya dalam spreadsheet

Perhatikan hasil yang diperoleh dari rumus tersebut. Rumus perhitungan jumlah kata "Kota" bersifat case sensitive, artinya membedakan huruf besar dan kecil. Oleh karena itu meskipun di sel A2 (text "Kota Bandung adalah Ibu kota Periangan") terdapat kata "Kota" (huruf pertama kapital) dan "kota" (huruf kecil semua), akan tetapi yang terhitung hanya kata "Kota" (huruf pertama besar) sehingga hasilnya 1 saja. 

Menghitung jumlah kata tertentu dalam sel tanpa membedakan huruf besar dan kecil 

Rumus umum:

=(LEN(sel)-LEN(SUBSTITUTE(UPPER(sel),kataDihitung_besar,""))) /LEN(kataDihitung)

Dengan kata lain, rumus di atas dapat dibaca seperti ini: Menghitung selisih antara jumlah karakter text dalam sebuah sel  dengan jumlah karakter text tersebut yang dirubah hurufnya menyadi huruf besar semua dan sudah dikurangi  dengan jumlah karakter kataDihitung dalam huruf besar.

Jika diterapkan dalam contoh soal maka rumus menjadi.

=(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"KOTA","")))/LEN("Kota")

Cara kerja rumus.

• Fungsi LEN ke-1 digunakan untuk menghitung jumlah karakter dalam sel A2. Hasilnya =38
• Fungsi UPPER digunakan untuk merubah text dalam sel A2 menjadi huruf kapital semuanya = "KOTA BANDUNG ADALAH IBU KOTA PERIANGAN"
• Fungsi SUBSTITUTE digunakan untuk menghilangkan text "KOTA" dari "KOTA BANDUNG ADALAH IBU KOTA PERIANGAN" sehingga menjadi " BANDUNG ADALAH IBU  PERIANGAN"
• Fungsi LEN ke-2 digunakan untuk menghitung jumlah karakter dari text " BANDUNG ADALAH IBU  PERIANGAN". Hasilnya = 32
• Fungsi LEN ke-3 digunakan untuk menghitung jumlah karakter dalam string yang dihitung yaitu ("Kota"). Hasilnya = 4
• Langkah Terakhir=  (LEN pertama – LEN kedua)/LEN ketiga  = (38 – 32) / 4 = 2

Selain menggunakan fungsi UPPER, kita juga bisa menggunakan bantuan fungsi LOWER untuk menghitung jumlah kata tanpa mempedulikan huruf besar dan kecil.  Fungsi LOWER berguna untuk merubah semua huruf dari sebuah text menjadi huruf kecil semua. Proses ini kebalikan dari cara kerja fungsi UPPER.

Dengan menggunakan fungsi LOWER, maka formula di atas dapat dimodifikasi menjadi:

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"kota","")))/LEN("Kota")

Menghitung jumlah kata tertentu dalam range dengan membedakan huruf besar dan kecil (case sensitive)

Untuk keperluan ini kita hanya sedikit memodifikasi rumus sebelumnya yaitu dengan menambahkan fungsi SUMPRODUCT  dan merubah referensi sel menjadi referensi range.

Rumus umum:

=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,kataDihitung,""))) /LEN(kataDihitung))

Berikut contoh terapannya dalam spreasheet:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,"Kota",""))) /LEN("Kota"))

Menghitung jumlah kata tertentu dalam range tanpa membedakan huruf besar dan kecil.

Seperti halnya menghitung jumlah text dalam sel, untuk menghitung jumlah text tertentu dalam sebuah range, kita juga bisa memanfaatkan fungsi UPPER dan LOWER.

Menggunakan fungsi UPPER:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),"KOTA",""))) /LEN("Kota"))

Menggunakan fungsi LOWER:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(LOWER(A2:A4),"kota",""))) /LEN("Kota"))

Perhatikan perbedaan bagian rumus yang dihgh light. Ketika kita menggunakan fungsi UPPER, maka kita harus menggunakan text "KOTA" (huruf kapital semua) sebagai parameter text yang disubstitusi. Sebaliknya jika menggunakan fungsi LOWER, maka kita gunakan text "kota" (huruf kecil semua).

Catatan:

Kelemahan dalam rumus-rumus yang disampaikan di atas adalah tidak bisa membedakan apakah kata yang dihitung merupakan bagian atau substring dari string text kata lainnya atau merupakan kata yang berdiri sendiri. Misalnya jika kita menuliskan "Ibu kota" (dengan spasi) ataupun "Ibukota" (tanpa spasi) maka rumus akan memperlakukannya dengan cara yang sama, sehingga akan terhitung jumlah kata "kota" sejumlah 1 buah baik pada text  "Ibu kota" maupun pada text "Ibukota".

Demikian pembahasan singkat mengenai contoh rumus untuk menghitung banyaknya kata tertentu dalam sel dan range, baik dengan pendekatan case sensitive maupun sebaliknya.

Semoga bermanfaat.

Artikel terkait: 

• Menghitung Jumlah Huruf Tertentu Pada Cell dan Range
• Memisahkan Text Per Huruf dan Karakter
• Menghitung Total Jumlah Kata
• Menghitung Total Jumlah Huruf