Masih perihal rumus yang bekerja pada data text. Kali ini kita akan membahas bagaimana cara menghitung jumlah huruf , karakter atau kata tertentu dalam sebuah text atau string. Menghitung jumlah bagian text tertentu bisa dilakukan dengan satu formula saja, baik itu menghitung text dalam satu sel saja , maupun menghitung text yang terdapat dalam range atau kumpulan sel .
Secara garis besar, struktur pembahasan belajar excel kali ini mencakup:
Cara Menghitung Jumlah Huruf dan Kata Pada Excel
- Menghitung jumlah karakter atau kata dalam satu sel.
- Menghitung huruf atau karakter tunggal
- Dengan membedakan huruf besar/kecil
- Tanpa Membedakan huruf besar/kecil
- Menghitung jumlah kata
- Dengan membedakan huruf besar/kecil
- Tanpa membedakan huruf besar/kecil
- Menghitung jumlah karakter atau kata dalam kumpulan sel.
- Menghitung huruf atau karakter tunggal
- Dengan membedakan huruf besar/kecil
- Tanpa Membedakan huruf besar/kecil
- Menghitung jumlah kata
- Dengan membedakan huruf besar/kecil
- Tanpa membedakan huruf besar/kecil
Menghitung jumlah karakter atau kata dalam satu sel.
Anggaplah kita memiliki text yang akan dihitung jumlah huruf, karakter atau kata dalam text tersebut.Text : | Saya anak yang baik. Anak-anak lain suka saya |
Lokasi : | Sel B2 |
Maka rumus yang bisa digunakan dapat dilihat dalam tabel contoh di bawah ini.
No | Menghitung | Hasil | Formula |
1 | Semua Karakter | 45 | =LEN(B2) |
2 | Huruf "a" | 13 | =LEN(B2)-LEN(SUBSTITUTE(B2,"a","")) |
3 | Huruf "A" | 1 | =LEN(B2)-LEN(SUBSTITUTE(B2,"A","")) |
4 | Huruf "a" dan "A" | 14 | =LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A","")) |
5 | Kata "anak" | 2 | =(LEN(B2)-LEN(SUBSTITUTE(B2,"anak","")))/LEN("anak") |
6 | Kata "Anak" | 1 | =(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak") |
7 | Kata "anak" dan "Anak" | 3 | =(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak") |
Bisa diperhatikan kembali pada contoh diatas, bahwa secara umum fungsi yang dibutuhkan untuk keperluan perhitungan karakter atau kata adalah LEN dan SUBSTITUTE serta UPPER atau LOWER
- Fungsi LEN berguna untuk menghitung jumlah karakter dalam text.
- Fungsi SUBSTITUTE berguna untuk mengganti bagian text atau karakter tertentu dengan text atau karakter lainnya. Fungsi ini bersifat Case Sensitive, sehingga menganggap beda antara huruf kecil dengan kapital (contoh : huruf “a” dan huruf “A”).
- Fungsi UPPER berguna untuk merubah semua huruf dalam text menjadi huruf kapital semuanya
- Fungsi LOWER berguna untuk merubah semua huruf dalam text menjadi huruf kecil semuanya
Cara Kerja Rumus Excel Untuk Menghitung Jumlah Huruf Dan Karakter Tertentu.
Menghitung karakter atau huruf tunggal dengan membedakan huruf besar/kecil
Contoh: Menghitung jumlah huruf "a" dalam text yang berada dalam sel B2
=LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))
- Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung dengan text kosong
- Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
- Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
- Selisih itulah yang merupakan jumlah karakter yang dicari.
Menghitung karakter atau huruf tunggal tanpa membedakan huruf besar/kecil
Contoh: Menghitung jumlah huruf "a" dan "A" dalam text yang berada dalam sel B2
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))
- Fungsi UPPER akan merubah semua huruf dalam text menjadi huruf besar semua
- Fungsi SUBSTITUTE akan menukar karakter atau huruf yang dihitung (A) dengan text kosong
- Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
- Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
- Selisih itulah yang merupakan jumlah karakter yang dicari.
- Dengan konsep yang serupa, kita juga bisa mengganti fungsi UPPER menjadi LOWER
- =LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"a",""))
Menghitung jumlah kata dengan membedakan huruf besar/kecil
Contoh: Menghitung jumlah huruf "Anak" dalam text yang berada dalam sel B2
=(LEN(B2)-LEN(SUBSTITUTE(B2,"Anak","")))/LEN("Anak")
- Fungsi SUBSTITUTE akan menukar kata "Anak" dengan text kosong
- Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
- Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
- Selisihnya kemudian dibagi dengan banyaknya karakter kata "Anak"
- Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak"
Menghitung jumlah kata Tanpa membedakan huruf besar/kecil
Contoh: Menghitung jumlah huruf "Anak" dan "Anak" dalam text yang berada dalam sel B2
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),"anak","")))/LEN("anak")
- Fungsi LOWER akan merubah semua huruf dalam text menjadi huruf kecil semua
- Fungsi SUBSTITUTE akan menukar kata "anak" dengan text kosong
- Fungsi LEN menghitung jumlah karakter awal dan jumlah karakter setelah di SUBSTITUTE
- Operasi matematika pengurangan menghitung selisih atara jumlah karakter awal dan jumlah karakter setelah di-SUBSTITUTE
- Selisihnya kemudian dibagi dengan banyaknya karakter kata "anak"
- Hasil pembagian itulah yang merupakan hasil akhirnya yaitu jumlah kata "Anak" dan "anak"
- Dengan konsep yang serupa, kita juga bisa mengganti fungsi LOWER menjadi UPPER
- =(LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"ANAK","")))/LEN("ANAK")
Menghitung jumlah karakter atau kata dalam kumpulan sel.
Anggaplah text yang akan kita hitung adalah seperti berikut:
Text : | Nama saya Saleh |
Saya anak yang baik | |
Anak-anak lain suka saya | |
Apalagi anak komplek | |
Lokasi : | Sel B2:B5 |
Maka rumus untuk menghitung kehadiran karakter atau kata dalam kumpulan sel B2:B5 dapat diformulasikan seperti contoh dalam tabel di bawah ini:
No | Menghitung | Jumlah | Formula |
1 | Semua Karakter | 78 | =SUMPRODUCT(LEN(B2:B5)) |
2 | Huruf "a" | 22 | =SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a",""))) |
3 | Huruf "A" | 2 | =SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A",""))) |
4 | Huruf "a" dan "A" | 24 | =SUMPRODUCT(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A",""))) |
5 | Kata "anak" | 3 | =SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak")) |
6 | Kata "Anak" | 1 | =SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak")) |
7 | Kata "anak" dan "Anak" | 4 | =SUMPRODUCT((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak")) |
Dapat kita perhatikan dari contoh diatas, bahwa perbedaan mendasar dari rumus yang digunakan untuk menghitung banyaknya huruf atau kata dalam kumpulan sel dibandingkan dalam satu sel saja adalah adanya penggunaan fungsi SUMPRODUCT.
Cara kerja rumus untuk menghitung huruf dan kata pada kumpulan sel sebenarnya hampir sama saja dengan cara kerja rumus untuk menghitung huruf dan kata pada satu sel.
Yang membedakannya adalah karena bekerja pada array atau kumpulan sel maka perlu ditambahkan fungsi untuk menjumlahkan hasil dari masing-masing sel. Tambahan fungsi yang digunakan di sini adalah SUMPRODUCT.
Sebagai alternative lainnya, kita juga bisa menggunakakan rumus array CSE. Cukup ganti SUMPRODUCT menjadi SUM, dan jangan lupa untuk tekan CTR+SHIFT+ENTER setelah mengetikan rumusnya.
No | Menghitung | Jumlah | Formula |
1 | Semua Karakter | 78 | {=SUM(LEN(B2:B5))} |
2 | Huruf "a" | 22 | {=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"a","")))} |
3 | Huruf "A" | 2 | {=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"A","")))} |
4 | Huruf "a" dan "A" | 24 | {=SUM(LEN(B2:B5)-LEN(SUBSTITUTE(UPPER(B2:B5),"A","")))} |
5 | Kata "anak" | 3 | {=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"anak","")))/LEN("anak"))} |
6 | Kata "Anak" | 1 | {=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,"Anak","")))/LEN("Anak"))} |
7 | Kata "anak" dan "Anak" | 4 | {=SUM((LEN(B2:B5)-LEN(SUBSTITUTE(LOWER(B2:B5),"anak","")))/LEN("anak"))} |
Ringkasan:
Cara menghitung banyaknya huruf atau kata tertentu pada data excel dapat dilakukan menggunakan rumus kombinasi fungsi LEN,SUBSTITUTE,UPPER/LOWER serta operasi matematika pengurangan dan pembagian. Jika text yang dihitung terdapat pada kumpulan sel, maka cukup tambahkan fungsi SUMPRODUCT pada rumus kombinasi tadi. Sebagai alternative, fungsi SUMPRODUCT juga bisa diganti fungsi SUM dalam bentuk rumus array.
Demikian semoga bermanfaat.
Salam.. 😉
Baca juga tutorial belajar excel lainnya:
0 Komentar