Rumus Konversi Huruf Kolom Menjadi Angka

Di Excel kita bisa membuat rumus untuk mengkonversi huruf kolom menjadi angka nomor urut kolom dalam spreadsheet.

Contohnya :

• A menjadi 1
• B menjadi 2
• C menjadi 3
• Z menjadi 26
• AA menjadi 27
• … dan seterusnya.

Adapun cara atau formula yang dapat digunakan adalah dengan memanfaatkan kombinasi fungsi COLUMN dan INDIRECT

Contoh Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka

Untuk lebih jelasnya mari kita lihat contoh rumus berikut. Silahkan copy tabel berikut ke dalam spreadsheet sel A1 sehingga kita bisa melihat hasilnya.

A	B	C
2	Contoh Rumus	Penjelasan
3	=COLUMN(INDIRECT("B1"))	Mengkonversi huruf B menjadi angka. Nomor urut kolom B adalah 2
4	=COLUMN(INDIRECT(B9&1))	Mengkonversi text kolom pada sel B9 (AA) menjadi angka. Nomor urut kolom AA adalah 27
5	=COLUMN(INDIRECT("XYZ1"))	Mencoba mengkonversi huruf XYZ menjadi nomor. Menghasilkan error #REF! karena ZYZ melebihi batas maksimuk kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
6	=COLUMN(INDIRECT(B10&1))	Mencoba mengkonversi huruf yang ada pada sel B10 yaitu ZZZ menjadi nomor. Menghasilkan error #REF! karena ZZZ melebihi batas maksimum kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
7
8	Test Ref:
9	AA
10	ZZZ

Penjelasan Cara Kerja Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka (Nomor Urut Kolom)

Pertama :

Fungsi INDIRECT digunakan untuk mendapatkan referensi sel sesuai text referensi yang diberikan.

Misalnya jika text yang diumpankan adalah "B1", maka INDIRECT akan mengarah ke sel referensi B1.

Contoh:

• Rumus INDIRECT("C2") akan mengarahkan ke referensi C2 dan karena sel C2 berisi text "Penjelasan" maka rumus tersebut akan menghasilkan text "Penjelasan"
• Rumus INDIRECT("B2") akan mengarahkan ke referensi B2 dan karena sel B2 berisi text "Contoh Rumus" maka rumus tersebut akan menghasilkan text "Contoh Rumus"

Kedua:

Fungsi COLUMN berguna untuk mendapatkan nomor urut kolom dari referensi yang diberikan.

Contoh:

• Rumus COLUMN(B1) akan mendapatkan angka nomor kolom dari sel B1 yaitu 2
• Rumus COLUMN(A1) akan mendapatkan angka nomor kolom dari sel A1 yaitu 1

Dari penjelasan cara kerja masing-masing fungsi di atas, kemudian dapat dijelaskan alur kerja formula excel untuk mengkonversi huruf kolom menjadi nomor sesuai contoh berikut :

• Rumus masih lengkap
• =COLUMN(INDIRECT("B1"))
• Referensi Text “B1” sudah dirubah menjadi referensi B1 oleh fungsi INDIRECT
• =COLUMN(B1)
• Fungsi COLUMN menghasilkan angka 2 yang merupakan nomor urut kolom B 

Hal yang sama berlaku juga jika kita menggunakan referensi sel untuk menempatkan text referensi (perhatikan contoh pada tabel di atas baris ke-4

• Rumus masih lengkap
• =COLUMN(INDIRECT(B9&1))
• Sel B9 berisi text "AA", dan jika digabung dengan angka 1 (rumus B9&1), maka hasilnya "AA1"
• =COLUMN(INDIRECT("AA1"))
• Referensi Text “AA1” kemudian dirubah menjadi referensi AA1 oleh fungsi INDIRECT, yang selanjutnya diolah oleh fungsi COLUMN
• =COLUMN(AA1)
• Fungsi COLUMN menghasilkan angka 27 yang merupakan nomor urut kolom AA dalam lembar kerja excel.

Perlu diperhatikan: Dalam contoh – contoh formula diatas kita menggunakan index baris 1, misalnya B1, XYZ1. Sebenarnya angka tersebut bisa diubah dengan angka berapa saja sepanjang masih dalam lingkup baris yang dibatasi dalam lembar kerja excel.

Kenapa Error:

Error terjadi karena penggunaan text kolom yang diluar batas jumlah kolom yang disediakan oleh excel.

Dalam contoh diatas jika kita menggunakan kolom XYZ atau ZZZ maka rumus akan menghasilkan nilai error #REF! karena XYZ dan ZZZ tidak tersedia di excel.

Kolom maksimal pada excel versi 2007. 1010, 2013 dan 2016 adalah XFD atau kolom ke-16384. Sedangkan untuk excel versi 2003, kolom maksimalnya adalah kolom IV atau kolom ke-256

Demikian tips singkat mengenai cara membuat rumus excel untuk mengkonversi huruf kolom menjadi angka nomor urut kolom.

Salam..

Baca juga, artikel tutorial belajar excel lainnya:

• Kenapa Header Kolom Berubah Menjadi Angka
• Cara Menghitung Jumlah Huruf dan Kata
• Cara Memisahkan Kata Per Huruf

3 Alternatif Rumus Cari Data Banyak Kriteria

Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.

Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.

Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.

Ada beberapa alternative formula yang dapat digunakan. Berikut 3 diantaranya:

1. INDEX MATCH
2. OFFSET MATCH
3. INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut harus dibuat dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita lanjutkan dengan Studi Kasus.

Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel

Anggaplah kita memiliki tabel data harga buah-buahan dari berbagai supplier. Label kolom tabel dari kiri ke kanan adalah:

• Kolom A = No
• Kolom B = Buah
• Kolom C = Supplier
• Kolom D = Harga

Selanjutnya misalnya kita harus mencari data harga buah dari suplier tertentu.

Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam tugas pencarian data ini, yaitu:

• Kriteria 1 = nama buah
• Kriteria 2 = nama suplier

Pertanyaannya: Bagaimana rumusnya untuk mendapatkan data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .

Contoh kasus dan pertanyaan tersebut dapat digambarkan sebagai berikut:

Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat sebenarnya akan terasa jika bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.

Selain itu, contoh yang ditampilkan juga hanya menggunakan 2 kriteria, namun pada dasarnya prinsip kerjanya adalah sama, baik menggunakan 2 kriteria, 3 kriteria atau lebih banyak lagi.

Selanjutnya mari kita bahas satu persatu.

Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array, sehingga ketika  selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.

Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH

Salah satu rumus terpopuler yang biasa digunakan untuk pencarian data adalah kombinasi fungsi INDEX dan MATCH.

Dalam hal contoh kasus di atas, kita bisa menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:

{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}

Cara Kerja Rumus INDEX MATCH

• Operator ampersand (&) digunakan untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom  kolom buah (B2:B8) dan supplier (C 2:C8)
• Fungsi MATCH berguna untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
• Angka yang diperoleh dari fungsi MATCH kemudian digunakan oleh fungsi INDEX sebagai row_index dalam range D2:D8. 
• Dan sel baris ke-3 dalam range D2:D8 adalah sel D4, sementara itu sel D4 berisi nilai 8000.
• Sehingga hasil akhir adalah 8000.

Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH

Fungsi OFFSET berguna untuk mendapatkan referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.

Dengan menggabungkannya dengan fungsi MATCH, maka kita akan menentukan berapa  baris jarak sel yang dicari dari sel acuan, kemudian mendapatkan nilai dari sel yang dicari tersebut.

{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}

Cara Kerja Rumus OFFSET-MATCH

• Fungsi MATCH berperan sama seperti halnya dalam rumus INDEX MATCH, dimana fungsi MATCH  dalam contoh tersebut menghasilkan angka 3.
• Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana dapat dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapatkan nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
• Dan sel dimaksud dengan kriteria tersebut adalah sel D4
• Sementara itu sel D4 berisi nilai 8.000.   Bilangan inilah yang merupakan hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string yang mereferensikan sebuah alamat sel tertentu.

Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.

{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}

Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

• Fungsi MATCH berguna untuk mendapatkan nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapatkan index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka  3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapatkan angka 4.
• Index Kolom dalam spreadsheet didapatkan menggunakan bantuan fungsi COLUMN,  dan kolom D2:D8 adalah 4 (kolom D)
• Ringkasnya rumus tersebut dapat dikonvert menjadi =INDIRECT(ADDRESS(4,4))
• ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
• Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapatkan nilai dalam sel $D$4
• Dan hasilnya adalah 8.000

  

Ringkasan.

Cari data dengan 2 kriteria atau lebih dapat dilakukan tanpa menggunakan kolom bantu, yaitu menggunakan rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya dapat digunakan sesuai kebutuhan fungsi lainnya.

Demikian semoga bermanfaat.

Salam

Silahkan dibaca juga artikel Belajar Excel Lainnya:

• 3 Alternative Rumus VLOOKUP Ke Kiri
• Fungsi MATCH dan Contoh Rumusnya
• Mengenal Fungsi dan Rumus VLOOKUP
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Mengenal Rumus Array

5 Alternative Rumus VLOOKUP Ke Kiri

Perlu Rumus excel untuk cari data? VLOOKUP jawabannya. Namun VLOOKUP saja masih belum cukup, terutama jika kita ingin cari data ke sebelah kiri.

Kenapa?... Karena ternyata fungsi ini masih memiliki kelemahan, yaitu hanya dapat mencari data dari kiri ke kanan tabel. Sedangkan dalam prakteknya, struktur atau susunan kolom dalam tabel tidak selalu bisa diprediksi dan tidak selalu cocok dengan kondisi ideal penggunaan formula VLOOKUP.

Namun demikian tidak perlu khawatir, karena excel sudah menyediakan berbagai alternatif solusi pencarian data. Dan atas dasar hal tersebut juga, maka catatan pelajaran excel  ini dipersiapkan untuk membahas 5 alternative rumus vlookup data di sebelah kiri.

Adapun kelima alternative rumus cari data tersebut adalah:

1. Rumus VLOOKUP CHOOSE
2. Rumus HLOOKUP MATCH
3. Rumus INDEX MATCH
4. Rumus OFFSET MATCH
5. Rumus INDIRECT ADDRESS MATCH  COLUMN

Perlu dicatat, meskipun contoh-contoh rumus yang disampaikan dalam pembahasan ini ditujukan untuk melakukan VLOOKUP data di sebelah kiri, namun rumus-rumus tersebut juga dapat digunakan untuk VLOOKUP data ke sebelah kanan. Penekanan pembahasan cari data ke sebelah kiri karena memang itulah yang biasanya sering menjadi pertanyaan, terutama dari pengguna excel yang baru mengenal atau menggunakan rumus VLOOKUP.

Contoh Kasus Rumus VLOOKUP Untuk Cari Data Sebelah Kiri

Anggaplah kita memiliki sebuah tabel berisi data karyawan pada kolom A s.d E, dengan susunan label kolom dari kiri ke kanan: "No", "Nama", "Umur", "ID", dan "Alamat".

Tugas selanjutnya adalah bagaimana mencari nama karyawan berdasarkan nomor ID yang sudah diketahui seperti yang ditunjukan dalam gambar berikut:

Dari gambar diatas, dapat dilihat bahwa data yang diketahui (ID) terletak di kolom D, sedangkan data yang di diinginkan (Nama) terletak di kolom B. Artinya data yang dicari terletak di sebelah kiri kolom acuan.

Jadi.. rumus apa yang dapat digunakan untuk mencari nama karyawan pada kolom yang terletak di sebelah kiri kolom berisi nomor ID ?

Mari kita bahas satu persatu...

Cari Data Ke Kiri Menggunakan Rumus VLOOKUP-CHOOSE

Dengan fungsi CHOOSE, ternyata kita bisa mendefinisikan sendiri kolom mana yang akan dianggap sebagai kolom pertama dan ke dua oleh fungsi VLOOKUP. 

Oleh karenanya kita akan menggunakan fungsi CHOOSE untuk menjadikan kolom "ID" sebagai kolom pertama , dan kolom "Nama" sebagai kolom kedua.

Adapun formula pencarian data di sebeah kiri dapat dituliskan sebagai berikut:

=VLOOKUP(H1,CHOOSE({1,2},D:D,B:B),2,0)

Rumus tersebut dapat menghasilkan nama yang dicari seperti ditunjukan dalam gambar  berikut:

Perhatikan kembali fragmen rumus CHOOSE({1,2},D:D,B:B)

• Dalam hal ini fungsi CHOOSE digunakan untuk menjadikan kolom D (kolom berisi data ID) sebagai kolom pertama dan kolom B (kolom berisi data Nama) sebagai kolom kedua. 

• Kemudian setelah digabung dengan rumus VLOOKUP, maka VLOOKUP akan mencari ID di kolom pertama dan memberikan hasil data nama  dari kolom ke-2, pada baris yang sama dengan baris sel berisi ID yang diketahui.

Cari Data Ke Kiri Menggunakan Rumus HLOOKUP-MATCH

Dengan membalik orientasi vertikal menjadi horizontal, sebenarnya  kita bisa melakukan pencarian data di sebelah kiri. 

=HLOOKUP("Nama",A:E,MATCH(H1,D:D,0),0)

• Rumus MATCH(H1,D:D,0) berguna untuk mendapatkan angka baris sebuah sel di kolom D yang berisi nilai ID yang diketahui. Rumus ini menghasilkan angka 3 yaitu nomor baris sel yang berisi nilai ID=13043

• Kemudian rumus HLOOKUP akan mencari posisi kolom "Nama" pada baris pertama  dalam range “A:E”. Selanjutnya memberikan informasi dari baris 3 pada kolom "Nama". Dan hasilnya adalah "Dulah"

Cari Data Ke Kiri Menggunakan Rumus INDEX-MATCH

Rumus ini tampaknya merupakan rumus terpopuler alternative VLOOKUP ke  kiri. Terbukti dengan banyaknya tutorial yang membahas hal ini. Coba saja cari kata kunci 'vlookup ke kiri' di Google. Maka kebanyakan link di halaman pertama google akan mengarah ke halaman web/blog yang menjelaskan INDEX MATCH, bukannya menjelaskan VLOOKUP.

Terkecuali jika halaman yang sedang anda baca ini ini bisa muncul di halaman pertama Google (penuh harap… 😇😇 ),  maka mungkin akan menambah warna sendiri mengenai cara pencarian data di sebelah kiri…. 

Baiklah, selanjutnya rumus INDEX MATCH dalam contoh kasus ini dapat dituliskan sebagai berikut:

=INDEX(B:B,MATCH(H1,D:D,0))

Fungsi INDEX digunakan untuk mendapatkan nilai dari Sel sesuai index baris dan kolom dalam range yang ditentukan. 

Misalnya:

=INDEX(range,x,y)

Maka rumus tersebut akan menghasilkan nilai dari baris ke-x dan kolom ke-y dalam range. nomor kolom bersifat opsional sehingga dapat ditiadakan seperti ini:

=INDEX(range,x)

Rumus tersebut akan menghasilkan nilai dari baris ke-x ,kolom ke-1 dalam range.

Konsep terakhir inilah yang digunakan dalam fungsi INDEX-MATCH untuk mencari data di sebelah kiri. Sebenarnya tidak mesti di sebelah kiri sich, mencari data ke kanan pun boleh.

Kembali ke contoh rumus INDEX MATCH untuk mendapatkan nama karyawan sesuai ID yang diketahui.

=INDEX(B:B,MATCH(H1,D:D,0))

• Fungsi MATCH digunakan untuk mencari posisi baris sel pada kolom D yang berisi nilai ID yang diketahui (sesuai yang diisi pada sel H1). Dan hasilnya adalah angka 3 (nomor baris sel berisi ID=13043)

• Kemudian fungsi INDEX menggunakan angka 3 tersebut untuk mengambil data padanannya pada kolom B

• Hasilnya adalah "Dulah" yaitu data dalam sel yang terletak pada baris 3 kolom B.

Cari Data Ke Kiri Menggunakan Rumus OFFSET  MATCH

Cara kerja rumus ini adalah dengan menggunakan posisi relative baris data yang dicari dari sel acuan dalam fungsi OFFSET. Perhatikan gambar berikut:

• Fungsi MATCH digunakan untuk mendapatkan angka baris sel yang berisi nilai ID yang diketahui pada range D:D. Hasil dari rumus MATCH ini dikurangi angka 1 untuk menyesuaikan angka posisi relative dari baris pertama (sel B1). Fungsi MATCH dalam contoh menghasilkan bilangan 3, kemudian 3 dikurangi 1 menjadi 2. 

• Hasilnya kemudian digunakan oleh fungsi OFFSET untuk mendapatkan nilai dari sel yang jaraknya 2 baris ke arah bawah dari dari sel B1.

• Dan hasilnya adalah "Dulah" yaitu data dalam sel yang jaraknya 2 baris ke arah bawah dari sel B1.

Cari Data Ke Kiri Menggunakan Rumus INDIRECT - ADDRESS - MATCH - COLUMN

Nah, kalau rumus ini bekerja dengan cara menentukan alamat sel yang berisi data yang dicari, kemudian mendapatkan nilainya.

• Rumus MATCH(H1,D:D,0) menghasilkan angka 3 yaitu posisi baris sel yang berisi ID 13043
• Rumus COLUMN(B:B) menghasilkan angka 2  yaitu nomur urut kolom B dalam lembar kerja excel
• Fungsi ADDRESS digunakan untuk mendapatkan alamat sel sesuai index baris dan kolom yang diperoleh dari rumus MATCH dan COLUMN.  Sehingga jika diringkas menjadi  =ADDRESS(3,2) , rumus ini menghasilkan nilai $B$3
• Fungsi INDIRECT digunakan untuk mendapatkan nilai dalam alamat sel yang diketahui. Dan nilai yang terdapat dalam sel $B$3 adalah “Dulah”

Ringkasan:

Ada banyak cara untuk menyelesaikan tugas Excel. Termasuk untuk melakukan pencarian data di excel. Dalam kasus VLOOKUP ke kiri, kita bisa menggunakan beberapa alternative seperti 5 contoh rumus yang sudah dijelaskan yaitu: kombinasi VLOOKUP – CHOOSE, kombinasi HLOOKUP-MATCH, kombinasi INDEX-MATCH, kombinasi OFFSET-MATCH dan kombinasi INDIRECT-ADDRESS-MATCH-COLUMN.

Demikian semoga bermanfaat.

Salam..

Baca juga tutorial Belajar Excel Lainnya:

• Lookup Gambar Dengan Fungsi INDEX MATCH
• VLOOKUP - Rumus Excel Yang Perlu Dikuasai
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Fungsi MATCH - Kegunaan dan Contoh Rumusnya
• Penggunaan Fungsi OFFSET untuk Transpose Data

Cara Menghitung Jumlah Huruf dan Kata

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:

• Cara Memisahkan Kata Per Huruf
• Fungsi UPPER, LOWER dan PROPER
• Fungsi LEN untuk Menghitung Jumlah Huruf Pada Excel