Monday, April 30, 2018

Menghitung Cell Kosong dan Berisi Data


Rumus Excel Menghitung Cell Kosong
Untuk menghitung jumlah cell kosong dan  berisi data (tidak kosong) di excel dapat dilakukan dengan mudah menggunakan rumus.  Fungsi yang dapat digunakan untuk menghitung jumlah sel kosong adalah fungsi COUNTBLANK. Sedangkan untuk menghitung cell yang berisi data atau tidak kosong, kita bisa menggunakan fungsi COUNTA. Selain itu kita juga bisa memanfaatkan fungsi lainnya seperti COUNTIF, COUNTIFS dan SUMPRODUCT+LEN, baik untuk menghitung banyaknya sel kosong maupun sel yang berisi data.


Contoh Soal:



Misalnya kita ambil range A1:A8 dalam sebuah sheet, dimana range tersebut berisi data jenis buah-buahan, atau apalah yang anda inginkan. Namun sebagian sel dalam range A1:A8 tersebut dikosongkan karena kita ingin menguji bagaimana rumus penghitung sel kosong dan terisi dapat bekerja dengan benar.

Gambaran contoh soal dapat dilihat dalam screenshot di bawah ini.

Soal Excel Menghtitung Cell Kosong dan Tidak Kosong


Contoh Rumus Untuk Menghitung Sel Kosong


Untuk menghitung sel kosong kita bisa menggunakan fungsi COUNTBLANK. Fungsi ini dapat dituliskan dengan syntax yang sangat sederhana yaitu:

=COUNTBLANK(range)

Jika range kita ganti dengan referensi yang kita gunakan sebagai contoh yaitu A1:A8 maka rumus COUNTBLANK dapat kita tuliskan seperti di bawah ini:

=COUNTBLANK(A1:A8)

Karena dalam range A1:A8 ada 2 sel kosong yaitu sel A4 dan sel A7, maka rumus akan memberikan hasil informasi bilangan 2. 

Fungsi COUNTBLANK menghitung cell kosong


Dari gambar diatas, rumus COUNTBLANK sudah berhasil menghitung jumlah sel kosong pada range A1:A8.  Gambaran dalam contoh mungkin nampak spele, karena data yang kita gunakan sangat sedikit. Manfaat rumus ini akan terasa ketika kita bekerja dengan data yang banyak.

Contoh Rumus untuk Menghitung Sel Tidak Kosong


Untuk menghitung sel yang berisi data , alias tidak kosong bin tidak blank, kita bisa menggunakan fungsi COUNTA. Sama hal nya dengan fungsi COUNTBLANK, fungsi COUNTA juga memiliki syntax yang sangat sederhana dengan cukup hanya satu argumen fungsi saja. Fungsi ini dapat dituliskan sebagai berikut:

=COUNTA(range)

Dimana range adalah range data yang ingin kita hitung jumlah sel yang berisi data dalam range tersebut. Selanjutnya jika referensi range contoh A1:A8 kita masukan sebagai argumen fungsi COUNTA,   maka kita bisa menuliskannya sebagai berikut:

=COUNTA(A1:A8)

Fungsi COUNTA menghitung cell tidak kosong


Gambar di atas memperlihatkan bagaimana rumus excel COUNTA berhasil menghitung jumlah sel berisi data pada range A1:A8

Menghitung sel kosong dan tidak kosong menggunakan fungsi COUNTIF


Selain fungsi COUNTA dan COUNTBLANK, excel juga menyediakan fungsi lainnya yang dapat digunakan untuk menghitung sel kosong dan tidak kosong. Salah satunya adalah fungsi COUNTIF.
Fungsi COUNTIF dapat dituliskan dengan syntax:

=COUNTIF(range, criteria)

Dimana range adalah referensi range cell yang akan dicek, sedangkan criteria adalah string yang menunjukan syarat perhitungan. Kita dapat menuliskan criteria sebagai berikut:

Menghitung cell kosong: criteria   "<>*"
Menghitung cell berisi data: criteria "*"

Sehingga dengan menggunakan contoh range sebelumnya (range A1:A8) maka kita dapat menuliskan rumus sebagai berikut:

Menghitung cell Kosong:
=COUNTIF(A1:A8,"<>*")

Menghitng cell berisi data (tidak kosong)
=COUNTIF(A1:A8,"=*")

Fungsi COUNTIF menghitung cell kosong dan tidak kosong.


Dari gambar diatas kita bisa mengetahui bahwa fungsi COUNTIF dapat digunakan secara lebih luas. Fungsi ini mampu menghitung jumlah cell kosong maupun tidak kosong. Kuncinya adalah parameter criteria yang digunakan.

Selain menggunakan fungsi COUNTIFS sebenarnya masih ada fungsi lainnya yang dapat digunakan untuk menghitung sel kosong dan tidak kosong, misalnya fungsi COUNTIFS dan SUMPRODUCT.
Alternative Rumus Menghitung Sel Kosong  dan Tidak Kosong.

Menghitung Cell Kosong:

=COUNTIFS(A1:A8,"<>*")

=SUMPRODUCT(--(LEN(A1:A8)=0))

Menghitung Cell Tidak Kosong

=COUNTIFS(A1:A8,"=*")

=SUMPRODUCT(--(LEN(A1:A8)>0))

Catatan penting: 
  • Fungsi COUNTA, COUNTBLANK, COUNTIF, COUNTIFS dan SUMPRODUCT&LEN akan menghitung sel yang berisi karakter yang tidak nampak sebagai sel yang berisi data atau tidak kosong. Keberadaan spasi dalam sel yang seperti kosong, sering menyebabkan hasil rumus tersebut tidak valid.
  • Kecuali COUNTBLANK, semua fungsi yang dicontohkan diatas juga akan menghitung sel berisi data dengan jumlah karakter Nol yang merupakan hasil dari formula lain sebagai sel yang kosong, padahal ada formula di dalamnya. Misal sel berisi formula =""
Demikan pembahasan mengenai beberapa alternative rumus untuk menghitung banyaknya sel kosong dan sel berisi data (tidak kosong).



Sunday, April 29, 2018

Konversi Text Menjadi Bilangan


Rumus Excel Konversi Text Numerik
Konversi text menjadi bilangan (numerik) cukup penting untuk diketahui karena ada beberapa kasus rumus yang bisa menghasilkan nilai error gara-gara masalah type data ini. Sebagai contoh: rumus VLOOKUP akan menghasilkan nilai error jika digunakan untuk mencari bilangan dalam sebuah kolom  berisi text yang nampak seperti bilangan. Untuk mengatasi problem ini, maka mau tidak mau kita harus mengkonversi terlebih dahulu text menjadi bilangan.

Mengenali Data Type Text dan Numerik (Bilangan)




Sebelum melangkah lebih jauh, alangkah baiknya kita memahami terlebih dahulu cara membedakan mana data type text dan mana data type numerik.

Yang dimaksud text disini adalah text yang menyerupai numerik karena penulisan bilangan didahului tanda petik 1 ( ‘ ) sehingga excel membacanya sebagai text. Kita sebut saja “text bilangan”. Aplikasi excel pun sebenarnya sudah menyediakan cara yang sangat mudah untuk mengidentifikasi data mana yang berupa text dan mana yang merupakan bilangan, seperti terlihat dalam screenshot di bawah ini:

Membedakan text dan numerik pada excel

Dari gambar di atas, type data text dapat diketahui dari 2 cara.
  • Pertama : data text bilangan memiliki tanda segitiga kecil warna hijau pada pojok kiri atas sel.
  • Kedua : Pada kondisi format default data text akan rata kiri, sedangkan data bilangan rata kanan.

Selain menggunakan cara visual seperti di atas, kita juga bisa mengenali data text dengan menggunakan bantuan fungsi ISTEXT. Fungsi ini akan memberikan nilai TRUE, jika parameter yang di-cek memang berupa text.

Fungsi ISTEXT dapat dituliskan dengan syntax sebagai berikut:

=ISTEXT(value)

Dimana value adalah data yang dicek, dalam hal ini bisa berupa referensi sebuah sel.
Misalnya: untuk mengecek data yang ada di sel A2 apakah merupakan text atau tidak, maka kita bisa menuliskan rumus seperti di bawah ini:

=ISTEXT(A1)

Rumus ISTEXT


Perhatikan gambar diatas. Rumus =ISTEXT(A1) menghasilkan nilai FALSE, karen sel A1 berisi data type text, sedangkan pada baris ke-2, rumus =ISTEXT(A2) menghasilkan nilai TRUE karena sel A2 berisi data type numerik.

Setelah kita bisa membedakan mana text bilangan dan mana yang benar-benar bilangan, mari kita lanjut ke pembahasan utama yaitu contoh rumus untuk konversi text bilangan menjadi bilangan.

Contoh Rumus Konversi Text Menyerupai Bilangan Menjadi Numerik


Ada beberapa rumus yang dapat digunakan untuk mengkonversi text menjadi bilangan, diantaranya :

  • Mengalikan text bilangan dengan Satu.
  • Menambahkan text bilangan dengan Nol
  • Menggunakan double unary ( -- )
  • Menggunakan fungsi VALUE

Anggaplah kita ingin mengkonversi sebuah text bilangan yang terletak di sel A2. 

Contoh Rumus 1: Kita bisa membuat rumus perkalian sederhana  dengan referensi sel A2 dikalikan dengan angka 1.

=A2*1

Rumus Excel Perkalian

Dari hasil perkalian tersebut, nampak hasilnya adalah data yang benar-benar bilangan yang ditandai format alignment rata kanan pada kondisi default.

Contoh Rumus 2:  Dengan cara menambahkan text bilangan dengan angka Nol

=A2*1

Rumus Excel Penambahan

Contoh Rumus 3: Dengan menggunakan tanda minus ganda atau double unary (--)
tanda minus dua atau double unary nampaknya bekerja dengan mengalikan data terhadap -1 sejumlah 2 kali. Secara matematis, negatif dikali negatif samadengan positif.

=--A2

excel double unary


Contoh Rumus 4: Menggunakan fungsi VALUE

Fungsi VALUE dapat dituliskan dengan syntax =VALUE(text), jika text kita ganti dengan referensi  A1 maka rumus dapat dituliskan:

=VALUE(A2)

Cara Menggunakan Rumus VALUE di Excel


Selain menggunakan fungsi VALUE, kita juga bisa mencobanya dengan fungsi lainnya. Jika output yang dihasilkan berupa bilangan yang ditandai dengan format alignment rata kanan secara default, maka berarti fungsi tersebut berhasil mengkonversi text bilangan menjadi bilangan.

Dari contoh-contoh yang sudah diperlihatkan di atas dapat disimpulkan bahwa untuk mengkonversi text bilangan menjadi benar-benar bilangan (numerik) hanya diperlukan rumus yang sangat sederhana. Namun meskipun sangat sederhana, pemahaman mengenai hal sangat penting karena excel akan memperlakukan data text dan bilangan / numerik secara berbeda, dan hal ini bisa berpengaruh terhadap hasil kalkulasi data.

Demikian pembahasan singkat mengenai cara mengkonversi text bilangan menjadi bilangan / numerik. Semoga bermanfaat.

Artikel terkait:


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:


Friday, April 27, 2018

Menghilangkan Spasi Berlebih


Contoh Rumus Excel Menghilangkan Spasi BerlebihMenghapus spasi berlebih di excel terkadang gampang-gampang susah. Gampang jika sudah tahu caranya. Sangat susah kalau tidak tau caranya dan kemudian mencoba menghilangkan spasi berlebih secara manual, menghapusnya satu persatu. Pusing kalau sedikit tahu rumus untuk menghapus spasi tapi belum begitu mengerti konsepnya. Bagi kebanyakan user yang sudah agak tahu cara menghapus menghilangkan spasi berlebih adalah menggunakan fungsi TRIM. Namun ternyata itu saja belum tentu cukup. Ada beberapa kasus dimana menghapus extra spasi menggunakan fungsi TRIM tidak memberikan hasil yang diharapkan.

Contoh-contoh rumus dalam bahasan ini menggunakan parameter text secara langsung di rumus tanpa menggunakan referensi sel, supaya mudah membuat gambarannya. Selain itu, karena sulit menggambarkan jenis spasi dalam screenshot excel, maka sepertinya screenshot tidak perlu ditampilkan.



Fungsi TRIM


Fungsi ini secara umum digunakan untuk menghapus spasi berlebih yang ada di awal kata, di akhir kata dan antara dua kata sehingga hanya menyisakan satu spasi di antara dua kata. Yang lainnya di hapus.

Fungsi TRIM dapat dituliskan sebagai berikut:

=TRIM(text)

Dimana text merupakan string text yang akan dihapus spasi extra-nya.

Misalnya: text  "   AKU     ANAK    SEHAT      " merupakan contoh text dimana ada kelebihan spasi, baik di awal, di antara dua kata, maupun di akhir kalimat

Dengan menggunakan fungsi TRIM, maka kita bisa menghilangkan kelebihan spasi tersebut:

=TRIM("   AKU     ANAK    SEHAT      ")

Dan hasilnya adalah "AKU ANAK SEHAT" tanpa tanda spasi.

Untuk mengecek jumlah karakter yang normal dari text "AKU ANAK SEHAT" , silahkan gunakan fungsi LEN

=LEN("AKU ANAK SEHAT")

Dan hasilnya adalah 14  (catat jumlah ini untuk keperluan pengecekan pada contoh berikutnya)

Catatan: dalam pembahasan ini digunakan tanda petik 2 ( " ). Gunakan tanda tersebut ketika mengambil contoh rumus, sedangkan ketika membaca pembahasan, tanda petik tersebut tidak dihitung dan jangan diambil sebagai bagian dari kalimat.

Mengatasi Masalah Fungsi TRIM tidak Bekerja Sesuai Harapan.

Seperti disampaikan pada bagian pembuka, terkadang penggunaan fungsi TRIM saja tidak cukup untuk menyelesaikan masalah kelebihan extra spasi.

Contoh: copykan rumus berikut ke sebuah sel dalam lembar kerja excel.

=TRIM("   AKU     ANAK    SEHAT      ")

Dan ternyata hasilnya adalah: "  AKU   ANAK    SEHAT   ", masih ada spasi extra baik di awal, di akhir maupun di antara dua kata.

Kemudian kalau di cek menggunakan fungsi LEN maka jumlah karakter hasil rumus trim tersebut adalah:

=LEN(TRIM("   AKU     ANAK    SEHAT      "))
=24

Padahal jumlah karakter text ("AKU ANAK SEHAT" yang normal (tanpa spasi berlebih) seharusnya 14 saja. 

Pasti ada karakter spasi yang belum terhapus.

Apa penyebabnya?

Silahkan dicoba dengan cara mencopy langsung contoh rumus ini (jangan diketik):

  • Rumus 1:   =CODE(" ")
  • Rumus 2:   =CODE(" ")


Dan bagaimana hasilnya?
Ternyata rumus 1 dan rumus 2 meskipun kenapakannya sama, akan tetapi memberikan hasil yang berbeda. Rumus 1 menghasilkan bilangan 32, sedangkan rumus 2 menghasilkan bilangan 160.

Nah disini letak permasalahannya. Ternyata fungsi TRIM hanya bisa bekerja menghapus spasi yang memiliki kode 32, namun tidak mempan terhadap spasi ber-code 160.

Bagaimana Solusinya?

Untuk mengatasi masalah tersebut kita bisa menggunakan bantuan fungsi SUBSTITUTE untuk menghapus karakter spasi ber code 160. Sehingga contoh rumus TRIM yang terakhir di atas dapat kita rubah menjadi:

=TRIM(SUBSTITUTE("   AKU     ANAK    SEHAT      ",CHAR(160),""))

Hasilnya 14  "AKU ANAK SEHAT", artinya rumus bekerja dengan baik, karena semua extra spasi sudah dihilangkan.

Untuk mengecek apakah rumus sudah bekerja dengan benar, silahkan di cek menggunakan fungsi LEN

=LEN(TRIM(SUBSTITUTE("   AKU     ANAK    SEHAT      ",CHAR(160),"")))

Hasilnya adalah 14, terbukti rumus bekerja sesuai harapan karena jumlah karakter pada text "AKU ANAK SEHAT" yang normal (tanpa spasi berlebih) adalah 14 seperti yang sudah dijelaskan pada bagian atas.

Demikian pembahasan singkat bagaimana menghapus spasi berlebih pada excel. Untuk praktikalnya anda bisa menggunakan referensi sel yang berisi text. 

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:

  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

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.
Semoga bermanfaat.

Artikel terkait: 


Wednesday, April 25, 2018

Menghitung Jumlah Huruf Tertentu Pada Cell dan Range


Contoh Rumus Excel Untuk Menghitung Jumlah Huruf Tertentu
Bahasan kali ini mengenai Menghitung jumlah huruf atau karakter tertentu, merupakan kelanjutan dari bahasan beberapa waktu yang lalu mengenai contoh rumus untuk menghitung total jumlah jumlah huruf. Untuk menghitung huruf atau karakter tertentu dalam sebuah cell diperlukan keterlibatan beberapa fungsi excel, yaitu Fungsi LEN, Fungsi SUBSTITUTE dan Fungsi UPPER. Sedangkan jika referensi yang kita gunakan berupa range, maka diperlukan tambahan fungsi SUMPRODUCT supaya rumus dapat memberikan hasil yang diharapkan.

Contoh Soal



Di sini sudah disediakan contoh range A2:A4 berisi text nama kota besar. Tugas kita selanjutnya adalah menghitung karakter yang ditentukan, anggaplah huruf “K” (Kapital) sebagai contoh.


Contoh Soal Excel Menghitung Huruf

  • Pertanyaan 1. Bagaimana rumus untuk menghitung huruf "K" pada masing-masing sel A2, A3 dan A4.
  • Pertanyaan 2. Bagaimana rumus untuk menghitung total huruf "K" pada range A2:A4.


Mari kita bahas satu persatu:

Menjawab Pertanyaan 1: Gunakan rumus untuk menghitung jumlah karakter tertentu pada sebuah sel


Rumus umum yang dapat digunakan untuk keperluan ini adalah:

=LEN(text)-LEN(SUBSTITUTE(UPPER(text),huruf,""))

Jika parameter text diganti dengan referensi sel A2 dan parameter huruf diganti dengan "K", maka rumus menjadi 

=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"K",""))

Rumus diatas dapat diterjemahkan bahwa banyaknya huruf "K" sama dengan selisih antara jumlah karakter dalam text dengan jumlah karakter dalam text tanpa huruf "K". 

Lalu apa kegunaan fungsi UPPER? 

Fungsi ini bertugas untuk merubah semua huruf menjadi huruf kapital. Ini dimaksudkan supaya semua huruf "K" maupun "k" dapat diproses oleh fungsi SUBSTITUTE, karena fungsi ini bersifat case sensitive. Fungsi SUBSTITUTE sendiri digunakan untuk mengganti huruf "K" (baik huruf kecil maupun kapital) menjadi "" atau menghapusnya supaya dapat diketahui jumlah karakter dalam text tanpa huruf K.

Perhatikan konten sel A2 = "Kota Pekanbaru"
  • Fungsi LEN pertama menghitung jumlah karakter dalam text "Kota Pekanbaru" = 14
  • Fungsi UPPER merubah "Kota Pekanbaru" menjadi "KOTA PEKANBARU"
  • Fungsi SUBSTITUTE menghilangkan huruf "K" dalam text "KOTA PEKANBARU" = "OTA PEANBARU"
  • Fungsi LEN ke-2 menghitung jumlah karakter dalam text "OTA PEANBARU" =12
  • Jumlah huruf "K" = selisih antara hasil fungsi LEN ke-1 dan LEN ke-2.  Hasilnya = 14-12=2
  • Screenshot berikut menggambarkan contoh pererapan rumus dalam lembar kerja excel.


Contoh Soal Excel Menghitung Huruf Pada Sel

Catatan: jika kita ingin menghitung jumlah huruf secara case sensitif atau membedakan huruf kecil dan besar, maka kita bisa merubah fungsi di atas dengan menghilangkan keterlibatan fungsi UPPER. Dengan demikian, rumus dapat dituliskan sebagai berikut:

=LEN(A2)-LEN(SUBSTITUTE(A2,"K",""))

Rumus di atas akan menghasilkan bilangan 1, karena jumlah huruf "K" (huruf kapital) dalam text "Kota Pekanbaru" hanya ada satu buah, yaitu huruf pertama saja.

Menjawab Pertanyaan 2: Gunakan rumus untuk menghitung jumlah karakter tertentu pada range.


Untuk menghitung jumlah huruf K (besar dan kecil) dalam range tidak terlalu sulit. Kita hanya cukup memodifikasi rumus pertama dengan menambahkan fungsi SUMPRODUCT dan merubah referensi sel menjadi range.

Sehingga rumus dapat dituliskan sebagai berikut:

=SUMPRODUCT(LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),"K","")))


SUMPRODUCT berguna untuk menjumlahkan komponen array yang dihasilkah oleh proses dalam rumus LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),"K","")) yaitu {2;2;1}

=SUMPRODUCT({2;2;1}) = 5

Kenampakan rumus dalam lembar kerja excel mungkin akan seperti ini:

Contoh Rumus Excel Menghitung Huruf pada Range


Sama  halnya dengan rumus untuk menghitung jumlah karakter dalam   sel, kita juga dapat menghilangkan peranan fungsi UPPER dalam rumus menghitung jumlah karakter dalam range supaya rumus bersifat case sensitive:

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

Contoh rumus terakhir diatas akan menghasilkan bilangan 3 karena memang yang terhitung hanya huruf "K" Kapital saja.

Demikian pembahasan singkat mengenai contoh rumus untuk menghitung jumlah huruf tertentu, baik dalam sel dan range. Semoga bermanfaat.

Artikel terkait:


Tuesday, April 24, 2018

Memisahkan Text Per Huruf dan Karakter


Masih terkait otak-atik data text, pada kesempatan ini akan dibahas bagaimana memisahkan sebuah text menjadi huruf atau karakter penyusunnya dalam beberapa sel yang terpisah. Kasus seperti ini mungkin pernah anda alami pada saat pengisian sebuah form. Selain menuliskan text secara kontinyu terkadang sebuah form isian meminta kita untuk menuliskan data dalam kotak-kotak kecil yang disediakan, misalnya untuk menuliskan data nama, alamat, tanggal lahir, dan sebagainya. Di excel, salah satu cara untuk memisahkan text menjadi per huruf adalah menggunakan rumus, diantaranya: rumus kombinasi fungsi MID dan COLUMNS.

Contoh Soal.


Dalam sebuah lembar kerja excel sudah disediakan data NAMA, ALAMAT, dan KOTA. Selain itu juga sudah disediakan kotak-kotak kecil berupa cell excel yang diformat lebar kolom yang lebih sempit untuk menampung satu huruf atau satu karakter per kotak sel. Tugas selanjutnya adalah bagaimana membuat rumus untuk memisahkan text nama, alamat dan kota kedalam kotak-kotak kecil tersebut, satu huruf per kotak.

Perhatikan gambar contoh soal di bawah ini.




Rumus Excel Untuk Memisahkan Text Menjadi Per Huruf.

Sebagaimana sudah disinggung pada bagian pembuka, salah satu cara untuk memisakan text menjadi per huruf adalah menggunakan rumus yang merupakan kombinasi dari fungsi MID dan COLUMNS.

  • Fungsi MID berguna untuk mengambil karakter pada nomor urut tertentu dari text yang ditentukan
  • Fungsi COLUMNS berguna untuk menghitung banyaknya kolom pada range terpilih. Jumlah kolom 1, 2, 3 dan seterusnya bisa dimanfaatkan untuk menentukan nomor urut karakter yang selanjutnya diolah oleh fungsi MID.


Secara general, rumus untuk memisahkan text menjadi perhuruf adalah sebagai berikut:

=MID($sel1,COLUMNS($sel1:cell2),1)

Dimana sel 1 adalah cell dimana lokasi text lengkap berada. Sel1 dan sel2 mereferensikan cell yang sama pada kotak pertama. Sel 1 harus menggunakan tanda dolar pada bagiah kode kolom sehingga tidak berubah pada saat formula di copy ke sebelah kanan. Sel2 akan mereferensikan kolom dengan bergesar satu langkah ke kanan setiap kali kita mengcopy rumus ke sebelah kanan.

Jika diterapkan pada contoh soal, maka rumus pemecah text menjadi perhuruf dapat kita tuliskan pada kotak cell isian paling kiri, yaitu cell D2, dengan rumus sebagai berikut:

=MID($B2,COLUMNS($B2:B2),1)

Selanjutnya rumus dapat di copy ke sel berikutnya sehingga semua huruf dan karakter komponen text dapat diambil dan diletakan pada kotak sel yang berbeda, seperti terlihat pada screenshot di bawah ini:

Contoh rumus excel memisahkan huruf

Cara Kerja Rumus:

Penting diperhatikan penggunaan tanda dolar ($). Tanda tersebut berguna untuk menjadikan referensi absolute, artinya referensi tidak berubah pada saat rumus dicopy dari sebuah sel ke lokasi sel lainnya. Karena yang perlu dikunci hanya kolomnya saja maka tanda dolar diletakan sebelum kode kolom (dalam contoh kolom B)

Perhatikan bagian rumus yang dihighlight  =MID($B2,COLUMNS($B2:B2),1)
Anggaplah kita hanya mengambil bagian yang di highlight saja COLUMNS($B2:B2) dan rumus tersebut terletak di sel A2. Jika rumus di copy sel di sebelahnya di kanan, maka referensi $B2:B2 akan berubah menjadi $B2:C2 demikian seterusnya jika rumus dicopy ke sel berikutnya di sebelah kanan:

Fungsi COLUMNS ini digunakan untuk menghitung jumlah kolom dari referensi terpilih.
COLUMNS($B2:B2) = 1 
COLUMNS($B2:C2) = 2
COLUMNS($B2:D2) = 3
COLUMNS($B2:E2) = 4

Bilangan Jumlah kolom yang dihasilkan oleh fungsi COLUMNS selanjutnya dimanfaatkan oleh fungsi MID untuk menentukan nomor urut huruf dan karakter dari text. Setiap kotak akan mendapatkan jumlah kolom yang berbeda yang meningkat satu tingkat semakin ke kanan.

Rumus di cell D2  =MID($B2,COLUMNS($B2:B2),1) =MID($B2,1,1)   
Rumus di cell E2  =MID($B2,COLUMNS($B2:C2),1) =MID($B2,2,1)   
Rumus di cell F2  =MID($B2,COLUMNS($B2:D2),1) =MID($B2,3,1)   
Rumus di cell G2  =MID($B2,COLUMNS($B2:E2),1) =MID($B2,4,1)   

Dan seterusnya.

Jika referensi $B2 kita ganti dengan text "UNYIL CUPLIS" maka deret rumus akan menjadi seperti ini

Rumus di Cell D2  =MID("UNYIL CUPLIS",1,1)  = "U"   
Rumus di Cell E2  =MID("UNYIL CUPLIS",2,1)  = "N"
Rumus di Cell F2  =MID("UNYIL CUPLIS",3,1)  = "Y"
Rumus di Cell G2  =MID("UNYIL CUPLIS",4,1)  = "I"

Dan seterusnya sehingga setiap kotak berisi satu huruf atau satu karakter sesuai nomor urut karakter tersebut dari kiri ke kanan.

Demikian pembahasan mengenai rumus untuk memisahkan text menjadi per huruf atau karakter dalam kotak sel yang terpisah, Semoga bermanfaat.

Artikel terkait:


Monday, April 23, 2018

Menghitung Total Jumlah Kata


Rumus Excel Untuk Menghitung Jumlah Kata
Melanjutkan pembahasan sebelumnya mengenai cara menghitung total jumlah huruf, kali ini kita akan membahas bagaimana menghitung total jumlah kata dalam cell dan range. Untuk menyelesaikan tugas ini diperlukan beberapa fungsi, antara lain Fungsi LEN dan SUBSTITUTE jika text berada dalam satu cell. Sedangkan jika text berada dalam range cell, maka diperlukan tambahan fungsi lainnya yaitu SUMPRODUCT. Fungsi TRIM kadang-kadang diperlukan untuk mengatasi extra spasi yang bisa menimbulkan kesalahan perhitungan penjumlahan kata.

Contoh Soal:


Kita gunakan contoh text yang juga digunakan pada artikel Menghitung Jumlah Huruf. Hanya saja digunakan untuk menghitung jumlah kata, seperti terlihat dalam gambar berikut:

Contoh Soal Rumus Excel Menghitung Jumlah Kata


Sebelum melanjutkan ke pembahasan rumus, Perlu diperhatikan dahulu asumsi dalam melakukan perhitungan jumlah kata.

  • Kata diartikan sebagai susunan kumpulan huruf yang dipisahkan oleh satu spasi dengan kumpulan huruf lainnya. Dalam excel, kata bisa berupa kumpulan huruf yang mempunyai arti atau bisa juga berupa kumpulan huruf tanpa arti. Misalnya kita bisa saja mengetikan text  “abzc rga#r heuxp$r” (tanpa tanda petik). Excel akan mebbaca string “abzc”, “rga#r” dan “heuxp$r” sebagai sebuah kata yang berdiri secara individual.
  • Jumlah kata dalam sebuah kalimat sama dengan jumlah spasi ditambah 1. Hal ini juga harus mengasumsikan bahwa hanya ada satu spasi diantara dua kata, dan tidak ada spasi pada awal dan akhir kalimat. Untuk lebih jelasnya, perhatikan gambar di bawah ini:


Konsep Menghitung Jumlah Kata di Excel

Dengan asumsi demikian,  maka dapat dibuat deret bilangan seperti di bawah ini:

0 spasi = 1 kata atau tidak ada kata sama sekali
1 spasi = 2 kata
2 spasi = 3 kata
3 spasi = 4 kata
n spasi = n+1 kata

Sehingga dapat disimpulkan bahwa menghitung jumlah kata sama dengan menghitung jumlah spasi kemudian menambahkan satu terhadap hasil perhitungan jumlah spasi.

Baiklah, mari kita lanjutkan pembahasan rumus.

Rumus 1 : Menghitung total jumlah kata dalam satu sel


Pendekatan umum untuk menghitung jumlah kata pada sel excel adalah: 

= Panjang_text – panjang_text_tanpa_spasi + 1

Untuk tugas ini diperlukan bantuan fungsi LEN dan fungsi SUBSTITUTE. Fungsi LEN digunakan untuk menghitung jumlah karakter dalam sebuah text. Fungsi SUBSTITUTE digunakan untuk membuang karakter spasi dari text. Sehingga rumusnya, secara umum dapat dituliskan sebagai berikut:

=LEN(text)-LEN(SUBSTITUTE(text," ",""))+1

Mari kita coba praktekan: Jika text diganti dengan kalimat seperti ("Nama Saya Cecep") maka rumusnya akan seperti ini:

=LEN(("Nama Saya Cecep")-LEN(SUBSTITUTE(("Nama Saya Cecep"," ",""))+1

Perhatikan rumus SUBSTITUTE. Rumus ini akan menghasilkan text baru dimana beberapa karakter diantaranya diganti karakter baru atau ditiadakan. Dalam kasus ini karakter spasi  " " diganti dengan karakter kosong "" atau ditiadakan, sehingga text "Nama Saya Cecep" menjadi "NamaSayaCecep", sehingga rumus tadi menjadi:

=LEN(("Nama Saya Cecep")-LEN("NamaSayaCecep")+1
=15-13+1
=3

Uraian di atas menjelaskan sekilas mengenai cara kerja rumus untuk menghitung jumlah kata dalam sebuah sel. Perhatikan kembali rumus umum untuk menghitung 

=LEN(text)-LEN(SUBSTITUTE(text," ",""))+1

Jika text ditempatkan dalam sebuah referensi sel, anggaplan di sel A2 sesuai tabel contoh soal  di atas. Maka kita dapat menuliskan rumusnya menjadi:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Rumus tersebut bisa dicopy ke sel bawahnya untuk menghitung total jumlah kata di sel berikutnya, seperti nampak dalam screenshot di bawah ini:

Contoh Rumus Menghitung Jumlah Kata di Excel

Rumus 2 : Menghitung total jumlah kata dalam range.


Untuk tugas ini diperlukan tambahan fungsi SUMPRODUCT, selain LEN dan SUBSTITUTE.

Rumus umum

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range," ",""))+1)

Kalau anda perhatikan, rumus ke-2 ini hanya sedikit bedanya bila dibandingkan dengan rumus ke-1. Selain tambahan SUMPRODUCT, yang lainnya kita hanya tinggal mengganti parameter text atau referensi sel dengan range.

Apabila range tersebut diganti dengan range A2:A5 sesuai tabel contoh yang digunakan dalam artikel ini maka rumusnya menjadi seperti berikut ini:

=SUMPRODUCT(LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5," ",""))+1)

Dan berikut screenshot contoh penggunaan rumus di atas dalam spreadsheet excel:

Contoh Formula Menghitung Jumlan Kata dalam Range Excel

Untuk menganalisa cara kerja rumus, silahkan dicoba langkah-langkah berikut:

Seleksi porsi rumus LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5," ",""))+1
Kemudian tekan F9, sehingga akan dihasilkan data array  {3;4;5;4}

Cara Mengecek Rumus Menghitung Jumlah Kata

Masing-masing komponen array tersebut merupakan jumlah kata pada masing-masing sel. Selanjutnya tugas SUMPRODUCT untuk menjumlahkan semua komponen array tersebut sehingga diperoleh total kata dalam range.

Bagaimana Jika ada Extra Spasi?


Sebagaimana dijelaskan bahwa diasumsikan jumlah kata = jumlah spasi + 1. Dengan asumsi tersebut tentunya rumus akan memberikan hasil yang tidak benar jika terdapat extra spasi atau spasi berlebih, misalnya ada lebih dari satu spasi di antara dua kata, atau ada spasi di awal dan akhir text.

Untuk mengatasi masalah ini, kita bisa menggunakan bantuan fungsi TRIM. Fungsi ini memang sudah didesign untuk menghapus semua spasi, kecuali spasi tunggal diantara dua kata.

Syntax fungsi TRIM sangat sederhana, hanya perlu sebuah parameter text, atau sebuah referensi berisi text.

=TRIM(text)

Terkait pembahasan dalam artikel ini, maka fungsi TRIM dapat dimasukan ke rumus:

Rumus ke-1 : menghitung jumlah kata dalam sel
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

Rumus ke-2 : menghitung jumlah kata dalam range
=SUMPRODUCT(LEN(TRIM(A2:A5))-LEN(SUBSTITUTE(TRIM(A2:A5)," ",""))+1)

Silahkan untuk mencoba kedua rumus tersebut, dan lihat efeknya bila kita menambahkan extra spasi pada sel A2 atau range A2:A5. Kemudian bandingkan dengan hasil kedua rumus tanpa TRIM yang sudah dijelaskan sebelumnya.

Sampai disini pembahasan mengenai contoh rumus untuk menghitung total jumlah kata dalam sel dan range pada excel. Semoga bermanfaat.

Artikel terkait: