Halo sobat.. Ketika membuat sebuah rumus pada Microsoft Excel, kita tidak bisa lepas sepenuhnya dari yang namanya error. Ya error tidak selalu bisa dihindari. Ia sering hadir meski tidak diinginkan. Ia sering datang meski tidak diundang. Jangan khawatir, kita tidak perlu menghindar dan berlari. Error tidak mungkin dibuang selamanya, Temani saja dia. Tapi ingat, jangan sampai kamu terbawa error juga. Seperti pembahasan kali ini: Menjumlahkan Tanpa Dipengaruhi Oleh Error Dengan Fungsi Aggregate.
Di excel, cobalah mengetik sebuah rumus. Rumus SUM misalnya. Kemungkinan besar sobat sudah tahu. Fungsi SUM biasanya menjumlahkan data pada referensi berupa range sel.
Apa yang akan terjadi kalau salah satu sel dalam referensi itu ada nilai error? Anggaplah error #N/A, #REF!, #DIV/0! dan sebagainya. ? Maka rumus yang kita buat juga akan menghasilkan nilai error.
Seperti diperlihatkan dalam gambar contoh rumus di bawah ini.
Perhatikan gambar di atas. Kenapa rumus =SUM(C2:C6)menghasilkan nilai error? Coba sobat telusuri pada referensi yang digunakan oleh rumus SUM, Yaitu range C2:C6. Oh.. ternyata ada salah satu sel berisi nilai error #N/A. Akibatnya rumus yang menggunakan referensi tersebut menjadi error. Padahal cuman satu sel saja. Nila Setitik Rusak Susu Sebelanga.
Apakah ada solusi untuk hal ini? Tentu saja. Excel sudah menyediakan seperangkat fungsi yang memungkinkan user bisa berdamai dengan error.
Dalam kasus ini, sobat bisa menggunakan fungsi AGGREGATE.
Caranya: ketik rumus =AGGREGATE(9;6;C2:C6)
Seperti terlihat dalam gambar di bawah ini
Keterangan argument rumus;
Argument ke-1: Angka 9 merupakan konstanta untuk memberitahu fungsi AGGREGATE supaya memproses data dalam referensi dengan fungsi SUM.
Argument ke-2: Angka 7 merupakan konstanta untuk memberitahu fungsi AGGREGATE supaya tidak menghiraukan keberadaan nilai error dalam referensi yang akan diproses dalam fungsi AGGREGATE.
Argument ke-3: range C2:C6 merupakah referensi sel-sel berisi data yang akan diproses penjumlahan.
Sampsi sini mudah-mudahan sobat sudah bisa memahami bagaimana membuat rumus penjumlahan bebas dari error.
Contoh rumus yang dijelaskan di atas dapat digunakan sebagai alternatif rumus SUM. Sobat bisa berexperiment dengan memanfaatkan fungsi AGGREGATE untuk menghitung rata - rata (AVERAGE), nilai maksimal (MAX), nilai minimal (MIN) dan sebagainya. Caranya: sobat tinggal atur saja argument-argument fungsi yang digunakan. Mudah bukan?
Kamu juga mungkin tertarik dengan pembahasan dalam link di bawah ini?
Pernahkan sobat mendapati text data yang bercampur antara text huruf yang bisa dimengerti dengan karakter-karakter aneh? Hal seperti ini biasanya ditemui ketika kamu mengexport data dari semuah aplikasi ke microsoft excel. Seperti contoh di bawa ini:
Teman-teman bisa perhatikan contoh di atas. Ada karakter yang tidak biasa sebelum text yang bisa dimengerti (dataku, datamu, datakita)
Bagaimana cara menghilangkan karakter-karakter aneh tersebut? apakah dengan menghapusnya satu persatu? Tidak !! Kita bisa memanfaatkan fitur REPLACE untuk menghilangkan karakter-karakter aneh dengan cepat.
Caranya sangat mudah.
Pilih salah satu sel yang mengandung karakter aneh,
Lalu klik dua kali di situ atau tekan F2.
Kemudian pada formula bar, highlight karakter aneh yang dimaksud.
Kemudian copy dengan cara menekan shortcut Ctrl + C
Masuk ke jendela REPLACE dengan menekan shortcut Ctrl + H
Pada field Find What: paste dengan menekan shortcut Ctrl + V
Klik tombol Replace ALL
Walhasil karekter aneh yang mau kita hapus pun hilang seketika.
Demikian tutorial singkat mengenai cara menghapus karakter aneh pada excel. Semoga bermanfaat.
Mungkin teman - teman juga tertarik untuk mengetahui tips - tips excel lainnya seperti:
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:
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:
Halo Sobat. Sebagai pengguna Excel, sobat pasti pernah nemuin yang namanya error pada rumus Excel. Pun pada rumus VLOOKUP. Kejadian tersebut tidak dapat dihindari. Pastinya sangat menyebalkan kalau sudah berhadapan dengan error. Sementara kita sedang dikejar waktu. Kalau kesalnya sudah overload, tidak jarang keybord jadi sasaran kekesalan... hihihi...
Ups jangan begitu kawan. Error itu wajar karena tidak ada yang sempurna di dunia ini. Yang penting kita tahu cara mengatasinya. Yuk kita fahami bersama cara mengatasi error pada rumus VLOOKUP.
Berikut tips untuk mengatasi error pada rumus VLOOKUP.
Identifikasi jenis error
Ketahui penyebabnya
Perbaiki penyebab terjadinya error.
Ada dua jenis error yang paling sering dijumpai pada rumus VLOOKUP, yaitu
Error #N/A
Error #REF!
Mari kita bahas satup persatu cara mengatasi jenis - jenis error tersebut:
Mengatasi Error #N/A pada rumus VLOOKUP
Error #N/A dikarenakan data yang dicari menggunakan VLOOKUP tidak dijumpai pada tabel sumber. Jika kita mencari data numerik (angka) menggunakan VLOOKUP maka kemungkinan ada perbedaan type data antara data yang di cari dengan data di kolom kunci pada tabel sumber.
Supaya lebih mudah difahami, mari kita perhatikan gambar di bawah ini:
Perhatikan rumus VLOOKUP di sel C3 dalam gambar di atas:
Menurut sobat, kira-kira apa penyebab error pada nilai yang dihasilkan rumus VLOOKUP tersebut?
Apakah penulisan rumus VLOOKUP nya salah?
Ternyata tidak ada kesalahan pada rumus VLOOKUP.
Masalahnya adalah perbedaan type data antara NIP yang dijadikan kata kunci untuk pencarian (1151) dengan type data pada kolom NIP dalam tabel sumber.
NIP yang dicari ditulis sebagai angka nyata (sel C2), sedangkan NIP pada kolom NIP merupakan text yang seolah-olah berupa angka.
Bagaimana membedakan angka nyata dan text yang seolah-olah berupa angka. Jika kamu tidak merubah format horizontal aligmment maka kita bisa dengan mudah membedakan mana data text dan mana data angka.
Secara default, data angka berformat rata kanan, sedangkan data text berformat rata kiri.
Perhatikan: NIP yang ditulis di sel C2 berupa angka dikenali dengan horizontal alignment rata kanan, sedangkan NIP di kolom NIP berupa text, dikenali dengan horizontal alignment rata kiri.
Baiklah kita sudah mengetahui kenapa rumus VLOOKUP menghasilkan error #N/A. Sekarang bagaimana cara mengatasinya?
Jika penyebabnya adalah karena memang kata kunci yang dicari tidak dijumpai pada kolom pertama tabel sumber / rujukan, maka kamu harus memperbaiki tabel sumbernya.
Jika penyebabnya adalah karena ada perbedaan type data pada kata kunci yang dicari dengan type data pda kolom pertama tabel rujukan maka kita bisa mengatasinya dengan cara berikut:
Untuk kasus yang dicontohkan dalam gambar di atas, kita bisa mengatasinya dengan menyamakan format NIP. Misalnya samakan saja sebagai text. Caranya ketikan tanda kutip satu atau apostrope sebelum kamu menuliskan nomor NIP di sel C2.
Perhatikan gambar di atas. Rumus VLOOKUP tidak lagi menghasilkan error #N/A. Hal ini karena kita sudah mengkoreksi penulisan NIP di sel C2.
Dalam hal ini kita sama sekali tidak perlu memperbaiki rumus VLOOKUP. Yang perlu kita lakukan hanya memperbaiki type data pada sel-sel yang menjadi referensi dalam penulisan rumus VLOOKUP.
Jika kamu malas mengetik tanda kutip satu setiap kali mengetik NIP di sel C2, maka kamu bisa menghindarinya dengan cara memodifikasi rumus VLOOKUP di sel C3. Seperti contoh dalam gambar berikut:
Dari gambar di atas dapat kita lihat bahwa kita tidak merubah penulisan NIP di cell C2. Akan tetapi kita merevisi rumus VLOOKUP
Dari :
=VLOOKUP($C$2;$B$7:$C$13;2;0)
Menjadi :
=VLOOKUP(TEXT($C$2;"0000");$B$7:$C$13;2;0)
Sampai disini, mudah-mudahan cukup untuk menjelaskan dan solusi atas permasalahan Error #N/A pada rumus VLOOKUP.
Selanjutnya kita beralih ke error lainnya:
Mengatasi Error #REF! pada rumus VLOOKUP
Error #REF! pada rumus VLOOKUP disebabkan referensi yang tidak valid.
Referensi tidak valid bisa terjadi karena:
Kesalahan penulisan referensi ketika mengetik Rumus
Penjelasan gambar: Contoh Error #REF! pada rumus VLOOKUP disebabkan kesalahan penulisan referensi tabel rujukan yang seharusnya C7:E13, namun ditulis C7:D13
Solusi: Perbaiki penulisan referensi rujukan dari C7:D13 menjadi C7:E13
Kesalahan penulisan nomor kolom index ketika mengetik rumus VLOOKUP
Penjelasan gambar: Contoh Error #REF! pada rumus VLOOKUP disebabkan kesalahan penulisan nomor index kolom melebihi jumlah kolom maksimal dari tabel rujukan. Tabel rujukan C7:E13 memiliki jumlah kolom maksimal 3, sementara di rumus VLOOKUP diketik nomor index kolom = 4. Untuk mencari nama jabatan seharusnya diketik 3.
Solusi: Dalam mengetik rumus VLOOKUP selalu pastikan penentuan nomor index kolom sesuai dengan dengan nomor urut kolom tersebut yang dihitung dimulai dari kolom pertama dalam tabel rujukan
Mengcopy rumus ke lokasi tertentu yang menyebabkan pergeseran referensi. Ini terjadi kalau kita tidak mengunci referens dengan tanda dolar.
Penjelasan gambar: Contoh Error #REF!pada rumus VLOOKUP disebabkan user mengcopy konten sel dari range D2:E4 ke range A2:B4, sehingga terjadi pergeseran referensi ke lokasi yang tidak valid.
Solusi: Jika kamu merencanakan membuat rumus yang bisa dicopy ke lokasi lain, pastikan gunakan kuncian referensi yang tepat. Gunakan tanda dolar untuk mengunci referensi.
Demikian penjelasan mengenai cara mengatasi error pada rumus VLOOKUP. Barangkali teman-teman tertarik dengan materi berikut, silahkan di cek linknya:
