Validasi Data Berdasarkan List di Sheet Lain

Validasi data berguna untuk memastikan user mengikuti rule yang anda buat. Ada beberapa kriteria validasi data seperti: any value, whole number, Decimal,  List, Date, Time, Text Length, and Custom. Di bahasan ini, saya akan membahas bagaimana membuat validasi data berdasarkan List. Khususnya List yang ada pada sheet yang berbeda dengan sheet sel yang divalidasi.

Validasi Data Berdasarkan List Pada Worksheet Yang Berbeda

Tidak sulit untuk membuat validasi data berdasarkan list pada sheet yang berbeda. Anda tinggal ikuti saja contoh langkah-langkah pembuatan data  validation berikut:

1. Buat list yang akan anda jadikan sebagai rule validasi data.

    Karena mimin tukang ngitung pupuk, jadilah contoh list pupuk:

    

    

2. Buat validasi data
    

    Sekarang waktunya bikin rule validasi data: simak langkah-langkahnya dengan baik.

• Pilih atau highlight range sel yang akan kita pasang validasi.
• Masuk ke Tab Data ⇨ Grup Data Tools ⇨ Data Validation ...
• Pada checkbox Allow,  pilih : List 
• Klik tanda panah atas yang ada pada field source sebelah kanan
• Pilih highlight list yang sudah dipersiapkan sebelumnya, kemudian teken Enter
• Klik Ok, atau tekan Enter lagi.

Itulah contoh singkat bagaimana cara membuat data validation berdasarkan list yang ada pada sheet lain.

Mungkin anda juga tertarik untuk mengetahui tips-tips excel lainnya, silahkan di telusuri via Daftar Isi.

VLOOKUP Sebagai Alternatif Fungsi IF Bertingkat

Tahukah sobat, bahwa dalam kasus tertentu ternyata fungsi VLOOKUP mampu menggantikan fungsi IF bertingkat? bahkan VLOOKUP diakui jauh lebih efisien dari fungsi IF.  

Lho kok bisa? Jika kamu belum tau, dan penasaran ingin mengetahui lebih jauh, maka mari kita simak pembahasan berikut.

Kita pakai contoh yang paling umum digunakan saja ya. Contoh soal yang populer dalam kursus-kursus excel: Bagaimana cara membuat rumus untuk  menentukan grade nilai siswa. Atau grade nilai mahasiswa juga boleh dech...

Anggaplah grade ditentukan berdasarkan range poin nilai sebagai berikut:

• Poin 100 ⇨ Grade A
• Poin 91-99 ⇨ Grade B
• Poin 81-90 ⇨ Grade C
• Poin 71-80 ⇨ Grade D
• Poin 61-70 ⇨ Grade E
• Poin 51-60 ⇨ Grade F
• Poin 0-50 ⇨ Grade G

Apa yang terpikir oleh sobat pertama kali ketika diminta untuk mengelompokan poin - poin nilai tersebut kedalam grade?

Jika yang terlintas dalam fikiranmu adalah rumus IF bertingkat, maka sobat masih perlu meneruskan membaca bahasan ini. 

Namun apabila rumus VLOOKUP otomatis muncul dalam fikiranmu, maka sebaiknya kamu tidak perlu meneruskan baca bahasan ini. Karena kamu sudah memahaminya.

Yang masih mendukung fungsi IF, mari kita lanjutkan pembahasannya dan perhatikan dengan seksama:

Screenshot berikut menggambarkan bagaimana contoh penggunaan rumus IF untuk mendapatkan Grade berdasarkan pencapaian poin nilai siswa.

 

Pada gambar di atas, ditunjukan sebuah rumus IF bertingkat pada sel C2. Rumus tersebut digunakan untuk menentukan grade siswa. Dalam contoh: Nilai 91 berarti grade = B.

Perhatikan rumus IF bertingkat yang cukup panjang:

=IF(B3<=50;"G";IF(B3<=60;"F";IF(B3<=70;"E";IF(B3<=80;"D";IF(B3<=90;"C";IF(B3<=99;"B";"A"))))))

Rumusnya cukup pajang bukan? Excel memang memberikan batas toleransi yang cukup tinggi yaitu maksimal 64 fungsi IF dalam satu rumus. 

Rumus IF bertingkat dengan level kategori yang terlalu banyak sangat tidak disarankan. Mengapa? karena rumus terlalu panjang, tidak efisien, tidak fleksibel dan rentan humman error dalam proses pengetikan rumus. 

Sebagai solusinya maka disarankan beralih ke fungsi VLOOKUP.

Bagaimana caranya:

Pertama, kita harus menyusun tabel rujukan dengan ketentuan yang bisa difahami oleh fungsi VLOOKUP.

Screenshot berikut mudah-mudahan bisa bisa menjadi gambaran contoh penggunaan fungsi VLOOKUP untuk menentukan grade siswa:

Pehatikan rumus pada sel D2, kita hanya perlu menuliskan rumus =VLOOKUP(C3;C6:D12;2)

Terpampang sangat nyata: Rumus VLOOKUP terbukti lebih singkat dibandingkan rumus IF.

Hebatnya lagi, meskipun jumlah kategori grade ditambah pun, rumus VLOOKUP tidak bertambah panjang. Ini sangat berbeda dengan rumus IF yang akan semakin panjang dengan semakin banyaknya kategori grade.

Namun untuk menjadikan rumus VLOOKUP memberikan hasil data yang benar maka kita harus memenuhi persyaratanya:

• Poin nilai harus disusun menanjak, terkecil di atas, dan terbesar di bawah
• Poin nilai hanya dituliskan nilai terendah saja pada masing-masing baris kategori (perhatikan kolom C mulai bari ke-6 dalam gambar di atas.

Sampai disini penjelasan mengenai bagaimana menggunkan fungsi VLOOKUP sebagai pengganti fungsi IF bertingkat. Lebih spesifiknya bagaimana menggunakan rumus VLOOKUP untuk  menentukan grade nilai siswa.

Masih ingin lebih memahami rumus VLOOKUP? postingan berikut mungkin yang sedang kamu cari:

• Memahami rumus VLOOKUP dengan Studi Kasus
• VLOOKUP Rumus Jitu yang Wajib Dikuasai

Kamu juga bisa menelusuri semua konten blog ini via Daftar Isi

Semoga bermanfaat.

Salam...

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

Fungsi PROPER Untuk Text Belakang Tanda Petik

Dalam postingan sebelumnya sudah dibahas perihal cara menggunakan fungsi PROPER, UPPER dan LOWER untuk menyesuaikan besar kecilnya huruf dalam text. Khusus fungsi PROPER digunakan untuk menjadikan semua kata dalam text memiliki huruf awal kapital (uppercase), sedangkan huruf lainnya menjadi huruf kecil (lowercase). Permasalahan yang muncul seputar penggunaan fungsi PROPER, diantaranya adalah pada saat menggunakan fungsi tersebut pada text yang mengandung tanda petik.

Misalnya adalah text nama dengan tanda petik 1 (apostrope) seperti: nur'aini, syafi'i, ma'ruf dan sebagainya. Jika text tersebut diproses melalui rumus PROPER, maka huruf dibelakang tanda petik menjadi huruf besar. 

=PROPER("nur'aini")➜   hasilnya: "Nur'Aini"

=PROPER("syafi'i") ➜   hasilnya: "Syafi'I"

=PROPER("ma'ruf")  ➜   hasilnya: "Ma'Ruf"

Atau jika nama-nama tersebut disimpan dalam sel, maka rumus dan hasilnya dapat diilustrasikan sebagai berikut:

Padahal yang diharapkan hanya huruf pertama saja yang menjadi huruf kapital.

Bagaimana cara mengakali rumus PROPER untuk text bertanda kutip supaya menghasilkan text yang yang diharapkan? 

Dengan kata lain, bagaimana caranya agar huruf di belakang tanda kutip harus tetap sebagai huruf kecil?

Hal ini bisa menggunakan bantuan fungsi SUBSTITUSI dan text yang unik dan jarang digunakan dalam kata atau kalimat yang normal.

Misalnya buat saja rumus seperti ini untuk mem-PROPER text "nur'aini" menjadi "Nur'aini" , bukan menjadi "Nur'Aini".

=SUBSTITUTE(PROPER(SUBSTITUTE("nura'aini","'","vwxyz")),"vwxyz","'")

Penjelasan cara kerja rumus

• Rumus SUBSTITUTE("nura'aini","'","vwxyz") , pertama-tama akan merubah tanda petik 1 dalam text "nur'aini" menjadi text "vwxyz", sehingga menghasilkan text "nurvwxyzaini"

• Kemudian fungsi PROPER, akan mengkonvert text "nurvwxyzaini" menjadi "Nurvwxyzaini"

• Terakhir fungsi SUBSTITUTE berguna untuk mengembalikan "text vwxyz" dalam text "Nurvwxyzaini" menjadi tanda petik kembali sehingga hasil akhirnya adalah text "Nur'aini".

• Persis hasilnya sesuai yang diharapkan. 

• Jika diterapkan dengan referensi sel, maka dapat diilustrasikan seperti gambar dibawah ini:

Perhatikan text "vwxyz" dalam contoh rumus di atas.  Text tersebut bisa diganti dengan text lainnya yang menurut anda tidak mungkin ada  dalam nama orang, nama benda atau dalam kalimat dan kata yang normal digunakan.

Demikian pembahasan singkat bagaimana menggunakan fungsi PROPER untuk mengolah text yang bertanda petik satu (apostrope) supaya dapat mempertahankan huruf dibelakang tanda petik  tetap sebagi huruf kecil.

Semoga bermanfaat.

Belajar Excel.. Excellent!

Baja juga artikel tutorial belajar excel lainnya:

• Cara Menggunakan Fungsi LOWER, UPPER, dan PROPER
• Cara Memisahkan Kata Per Huruf
• Cara Menghilangkan Spasi Berlebih Dari Text
• List Artikel Belajar Excel