Contoh Rumus Excel Praktis: Fungsi LOWER

Saturday, April 28, 2018

Merubah Jenis Huruf Besar dan Kecil

Rumus Excel Merubah Jenis Huruf Besar Kecil

Di excel kita bisa merubah jenis huruf besar dan kecil dengan mudah menggunakan rumus. Baik itu mengubah huruf besar (kapital) menjadi kecil ataupun sebaliknya mengkonversi huruf kecil menjadi besar. Selain itu kita juga bisa menentukan hanya huruf pertama dari setiap kata yang dijadikan huruf besar. Adapun fungsi excel yang dapat digunakan dalam rumus untuk merubah jenis huruf besar/kecil adalah fungsi UPPER, LOWER dan PROPER.

Baiklah, mari kita bahas satu persatu.

1. Fungsi UPPER

Fungsi UPPER seperti tersurat dalam namanya, beguna untuk merubah jenis huruf text menjadi huruf kapital (upper case) semuanya. Adapun syntax fungsi ini dapat dituliskan sebagai berikut:

=UPPER(text)

Dimana text adalah sebuah karakter huruf ataupun sekumpulan karakter dalam susunan tertentu yang membentuk kata ataupun kalimat (baik kalimat yang dapat dimengerti ataupun tidak)

Contoh rumus UPPER

=UPPER("jakarta") ⇒ Hasil = "JAKARTA"

=UPPER("Jakarta") ⇒ Hasil = "JAKARTA"

=UPPER("jaKArta") ⇒ Hasil = "JAKARTA"

Dari contoh contoh di atas, dapat diketahui bahwa dengan menggunakan fungsi UPPER, kita bisa menyeragamkan jenis huruf dari text "jakarta", "Jakarta", maupun "jaKArta" menjadi huruf besar semuanya ("JAKARTA") tanpa kecuali.

2. Fungsi LOWER

Fungsi LOWER merupakan kebalikan dari fungsi UPPER. Fungsi ini berguna untuk merubah semua huruf penyusun sebuah text menjadi huruf kecil (lower case) semuanya tanpa kecuali. Syntax fungsi LOWER sangat sederhana, hanya diperlukan satu argumen text, dan dapat dituliskan sebagai berikut:

=LOWER(text)

Contoh rumus LOWER

=LOWER("BANDUNG") ⇒ Hasil = "bandung"

=LOWER("Bandung") ⇒ Hasil = "bandung"

=LOWER("BanDUNG") ⇒ Hasil = "bandung"

Dapat kita lihat dari contoh di atas: fungsi LOWER selalu menghasilkan text "bandung" dimana semua huruf penyusunnya adalah huruf kecil, tidak peduli jenis huruf awalnya bagaimana.

3. Fungsi PROPER

Adapun fungsi PROPER berguna untuk merubah jenis huruf pertama dari setiap kata dalam text menjadi huruf besar atau kapital, sedangkan sisanya berupa huruf kecil.

Contoh rumus PROPER

=PROPER("bandung lautan api") ⇒ Hasil = "Bandung Lautan Api"

=PROPER("BANDUNG LAUTAN API") ⇒ Hasil = "Bandung Lautan Api"

=PROPER("bandung LAUTan API") ⇒ Hasil = "Bandung Lautan Api"

Contoh rumus diatas, membuktikan bahwa hasil dari rumus PROPER adalah berupa text dimana setiap huruf pertama pada setiap kata berupa huruf kapital / besar.

Masalah rumus PROPER.

Salah satu masalah rumus PROPER adalah pada saat mengolah sebuah text nama yang memiliki tanda petik satu di tengahnya, seperti nur'aini, ma'ruf, sema'un dan sebagainya.

Misalnya kita ingin merubah kata "nur'aini" menjadi "Nur'aini". Kemudian dicobalah fungsi PROPER

=PROPER("nur'aini") ⇒ Hasilnya = "Nur'Aini"

Perhatikan hasil "Nur'Aini" (huruf "A" setelah tanda petik berupa huruf kapital, padahal kita menginginkan hasil "Nur'aini" dimana huruf "a" setelah tanda petik harusnya tetap huruf kecil.

Untuk mengatasi kasus seperti ini kita bisa menggunakan bantuan fungsi SUBSTITUTE. Adapun rumusnya secara umum dapat dituliskan sebagai berikut:

=SUBSTITUTE(PROPER(SUBSTITUTE(text,"'","xxx")),"xxx","'")

Jika diterapkan untuk text = "nur'aini" , maka rumusnya menjadi:

=SUBSTITUTE(PROPER(SUBSTITUTE("nur'aini","'","xxx")),"xxx","'")

Rumus ini akan lebih berguna jika menggunakan referensi range, alih-alih menuliskan text langsung pada rumus.

Misalnya jika text "nur'aini" ada di sel A1, maka rumus dapat dituliskan sebagai berikut:

=SUBSTITUTE(PROPER(SUBSTITUTE(A1,"'","xxx")),"xxx","'")

Perhatikan penggunaan string "xxx". String tersebut hanya sebagai contoh saja, kita sebenarnya bisa menggunakan string apa saja yang diasumsikan tidak mungkin ada dalam text yang kita olah. String ini berguna untuk menggantikan sementara tanda petik satu (‘) sebelum diolah oleh fungsi PROPER. Setelah diproses oleh PROPER, kemudian tanda petik satu dikembalikan lagi menggantikan "xxx".

Proses menggantikan tanda petik satu menjadi "'" menjadi "xxx" maupun sebaliknya ini menggunakan fungsi SUBSTITUTE, sehingga diperlukan dua fungsi SUBSTITUTE yang bekerja sebelum dan sesudah fungsi PROPER.

Demikain pembahasan singkat mengenai contoh rumus excel untuk merubah jenis huruf besar dan kecil di Excel menggunakan fungsi UPPER, LOWER, dan PROPER, serta bantuan fungsi SUBSTITUTE untuk mengatasi permasalahan dalam rumus PROPER.

Semoga bermanfaat.

Artikel terkait:

Thursday, April 26, 2018

Menghitung Jumlah Kata Tertentu Pada Cell dan Range

Contoh Rumus Excel Menghitung Jumlah Kata Tertentu

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:

Contoh Soal Rumus Excel Menghitung Jumlah Kata Tertentu

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

Apakah jumlah kata tertentu akan yang dihitung terletak dalam satu sel atau dalam range.
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

Contoh Rumus Excel Menghitung Jumlah Kata dalam Sel

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")

Contoh Rumus Excel Menghitung Jumlah Kata tidak case senseitive

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")

contoh formula excel menghitung jumlah kata tertentu

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"))

contoh rumus excel menghitung jumlah kata tertentu dalam range

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.