Featured Post

Cara Menggunakan Rumus untuk Data Validation

Bagaimana cara menggunakan rumus untuk validasi data di Excel? Dengan rumus, kita bisa melakukan kustomisasi data validation. Yuk kita lihat...

Saturday, December 1, 2018

Cara Menggunakan Rumus untuk Data Validation

Bagaimana cara menggunakan rumus untuk validasi data di Excel? Dengan rumus, kita bisa melakukan kustomisasi data validation. Yuk kita lihat contoh penerapan custom validation di excel. Termasuk diantaranya: contoh rule validasi yang mengizinkan hanya entri angka saja, atau text saja. Selain itu ada juga contoh rule validasi untuk memastikan input text supaya dimulai, diakhiri, atau mengandung beberapa karakter tertentu. Dengan custom validation, kita juga bisa memastikan input entri unik, mencegah entri duplikat, dan lain sebagainya.

Dalam tutorial sebelumnya sudah dibahas mengenai dasar-dasar cara menggunakan fitur data validation di Excel: Apa kegunaannya, bagaimana fitur ini bekerja, dan bagaimana memanfaatkan predefined rules untuk validasi data di excel. Sekarang kita akan melangkah lebih maju dengan menggali aspek-aspek penting custom data validation di excel, sehingga bisa melejitkan pemanfaatan fitur data validation bukan hanya sekedar yang biasa-biasa saja.

Secara garis besar, pembahasan mencakup:

Namun sebelum melangkah lebih jauh. Alangkah baiknya bila pembaca sudah memahami terlebih dahulu dasar-dasar penggunaan data validation di excel. Pembahasan mendasar data validation dapat dilihat pada artikel: Cara Menggunakan Data Validation di Excel.

Jika anda sudah siap, mari kita bahas satu persatu topik pembahasan custom validation.

Cara Membuat Custom Validation Berdasarkah Formula


Excel sudah menyediakan sejumlah rule validasi siap pakai (predefined rules) untuk angka, tanggal, dan text. Namun, predefined rule ini hanya bisa menanggulangi skenario yang bersifat basic saja. Jika kita menginginkan validasi sel dengan kriteria yang lebih fleksible tanpa terpatok pada pilihan kriteria yang sudah tersedia, maka kita perlu membuat custom validation berdasarkan sebuah rumus. Berikut ini langkah dasar cara membuat custom validation rule:
  1. Seleksi satu atau sejumlah sel  yang akan divalidasi.
  2. Buka jendela Data validation dengan cara masuk ke tab Data > klik tombol Data Validation. Anda bisa juga menggunakan shortcut keyboard Alt > D > L , ditekan terpisah namun berurutan. 
  3. Dalam jendela Data Validation, pada kotak Allow pilih Custom dan kemudian masukan rumus pada kotak formula.
  4. Selanjutnya klik OK.

    Custom data validation di Excel


    Selain hal di atas, anda juga bisa menambahkan pesan input dan pesan error. Pesan input akan bekerja pada saat user menyeleksi sel tervalidasi, sedangkan pesan error aka tampil ketika user memasukan data invalid.

    Catatan: Semua rule data validation di excel, baik built-in maupun custom, hanya mampu memverifikasi data baru yang diketik pada sel tervalidasi. Sedangkan entri data dengan cara copy paste, tidak divalidasi. Demikian juga data yang sudah ada dalam sel sebelum rule validasi dibuat. Untuk menemukan data entri yang tidak sesuai dengan kriteria pada sel tervalidasi maka gunakan fitur Circle Invalid Data seperti yang sudah dibahas dalam artikel sebelumnya.

    Data Validation Untuk Mengizinkan Entri Angka Saja


    Sebenarnya excel sudah menyediakan rule validasi siap pakai untuk angka yaitu whole number, decimal, date dan time. Namun rule validasi tersebut mengharuskan adanya nilai minimal dan maksimal atau salah satunya. Jika anda ingin memaksa user untuk memasukan angka tanpa dibatasi nilai minimum atau maksimumnya maka anda dapat menerapkan custom validation berdasarkan formula, menggunakan fungsi ISNUMBER seperti contoh berikut ini:

    =ISNUMBER(C2)

    Dimana C2 adalah sel teratas pada range yang ingin kita validasi.


    Formula Data Validation Mengizinkan Angka Saja


    Catatan: fungsi ISNUMBER hanya mengizinkan entri data numerik pada sel tervalidasi. Data numerik ini bisa berupa bilangan bulat, desimal, pecahan, termasuk tanggal dan waktu yang juga dikenali sebagai bilangan oleh excel.

    Data validation untuk mengizinkan entri text saja.

    Sebagai kebalikan dari yang pertama, kita juga bisa membuat rule validasi yang mengizinkan hanya entri berupa text saja, menggunakan fungsi ISTEXT.

    Contoh :

    =ISTEXT(D2) Dimana D2 adalah sel teratas pada range yang akan divalidasi.


    Formula Data Validation Mengizinkan Entri Text Saja


    Selain menggunakan fungsi ISTEXT, kita juga bisa memaksai entri text dengan menggunakan fungsi ISNUMBER yang bernilai FALSE, sehingga rumus =ISTEXT(D2) dapat menghasilkan efek yang sama dengan rumus : =ISNUMBER(D2)=FALSE

    Validasi supaya entri text dimulai dengan awalan karakter atau kata tertentu.


    Jika semua value dalam range tertentu harus dimulai dengan karakter atau frase kata tertentu, maka gunakanlah custom validation berdasarkan fungsi COUNTIF dikombinasikan dengan karakter wildcard. Secara sederhana, syntax rumus dapat dituliskan sebagai berikut:

      =COUNTIF(sel,"text*")

    Sebagai contoh, untuk memastikan semua entri kode ID pada kolom A dimulai dengan awalan "XX-", "xx-", "Xx-" atau "xX-"  (tidak memandang jenis huruf kapital maupun huruf kecil) maka kita dapat mendefinisikan sebuah custom rule dengan rumus berikut:

    =COUNTIF(A2,"xx-*")



    Formuls Data Validation Entri Awalan Tertentu


    Formula validasi dengan logika OR (Multiple Criteria)


    Dalam kasus dimana ada 2 atau lebih awalan yang valid, maka gunakanlah beberapa fungsi COUNTIF, sehingga data validation bekerja dengan logika OR.

    Contoh:

    =COUNTIF(A2,"xx-*")+COUNTIF(A2,"yy-*")


    Formula Data Validation, Entri Multi Kriteria



    Formula Validasi bersifat Case Sensitive


    Jika perbedaan jenis huruf kapital dan huruf kecil menjadi masalah, maka kita bisa menggunakan fungsi EXACT dikombinasikan dengan fungsi LEFT. Hal ini akan menciptakan rule validasi yang bersifat case sensitive, sehingga entri data pada  sel tervalidasi harus berupa text berawalan karakter tertentu dengan jenis huruf kapital maupun huruf kecil yang ditentukan.

    =EXACT(LEFT(sel, jumlah_karakter), text)

    Sebagai contoh, untuk mengizinkan kode ID dimulai dengan awalan "XX-" (bukan "xx-" maupun "Xx-"

    =EXACT(LEFT(A2,3),"XX-")

    Fungsi LEFT digunakan untuk mengekstrak 3 karakter dari text yang ada di sel A2. Selanjutnya fungsi EXACT menjalankan perbadingan case sensitive dengan kode substring yang ditentukan (contoh : "XX-"). validasi akan bernilai benar (TRUE) jika 3 karakter pertama berupa text "XX-" sama persis termasuk jenis  huruf kapital atau kecilnya.


    Formula Data Validation Supaya Case Sensitive


    Mengizinkan entri yang mengandung kata tertentu.


    Untuk mengizinkan entri supaya mengandung kata tertentu pada bagian mana saja (permulaan, pertengahan ataupun pada akhir text), maka gunakanlah fungsi ISNUMBER dikombinasikan dengan dalah satu fungsi FIND atau SEARCH, tergantung apakah anda ingin membuat validasi text case sensitive atau tidak.

     Validasi tidak case sensitive menggunakan kombinasi fungsi ISNUMBER dan SEARCH.
     =ISNUMBER(SEARCH(text, sel))

     Validasi case sensitive menggunakan kombinasi fungsi ISNUMBER dan FIND.
    =ISNUMBER(FIND(text, sel))

    Dengan menggunakan tabel contoh sebelumnya, untuk mengizinkan hanya entri tertentu yang mengndung kata “AA” dalam sel A2:A7, maka gunakan rumus berikut:

    Case-insensitive: =ISNUMBER(SEARCH("XX", A2))
    Case-sensitive: =ISNUMBER(FIND("XX", A2))

    Formula tersebut bekerja dengan logika sebagai berikut:
    • Fungsi FIND atau SEARCH digunakan untuk mencari substring "XX" pada sel A2. Sedangkan fungsi ISNUMBER digunakan untuk menguji hasil fungsi FIND atau SEARCH apakah berupa angka atau bukan (error).
    • Jika substring "XX" ditemukan dalam sel A2 maka fungsi FIND atau SEARCH menghasilkan sebuah bilangan yang merupakan nomor urut posisi substring tersebut dalam text yang ada di sel A2. Sebuah bilangan jika dicek dengan fungsi ISNUMBER akan menghasilkan nilai TRUE, → memenuhi kriteria → entri diizinkan.
    • Jika substring "XX" tidak ditemukan dalam sel A2 maka fungsi FIND atau SEARCH menghasilkan nilai error #VALUE!. Nilai error ini jika dicek dengan fungsi ISNUMBER akan menghasilkan nilai FALSE, → tidak memenuhi kriteria → entri tidak diizinkan.
    Berikut  screenshot contoh penerapan validasi case sensitive menggunakan kombinasi fungsi ISNUMBER dan FIND:

    Formula Data Validation Text Ada String Tertentu

    Dapat kita perhatikan dari gambar di atas: dengan rumus validasi ISNUMBER FIND, maka data yang dimasukan ke sel tervalidasi harus mengandung substring XX berupa huruf kapital semua.

    Hal ini dikarenakan fungsi FIND bersifat case sensitive. Selanjutnya silahkan dicoba untuk mengganti fungsi FIND dengan fungsi SEARCH, dan bandingkan perbedaannya.

    Data validation untuk mencegah duplikat entri.


    Dalam situasi dimana kolom tertentu tidak boleh berisi data duplikat, maka kita bisa mengkonfigurasikan custom data validation berikut untuk hanya mengizinkan input data unik.

      =COUNTIF(range, sel_terAtas)<=1

    Sebagai contoh, untuk memastikan bahwa hanya nilai unik yang diinput pada kolom sampai dengan A7, maka gunakan formula berikut sebagai custom rule:

    =COUNTIF($A$2:$A$7, A2)<=1

    Ketika sebuah value unik dimasukan ke sel tervalidasi, maka formula akan menghasilkan nilai TRUE, dan validasi berhasil. Jika value yang sama sudah ada di sel lainnya dalam range tervalidasi, maka COUNTIF menghasilkan FALSE, dan input tidak memenuhi kriteria validasi.

    Harap diperhatikan bahwa range harus dikunci dengan referensi absolute (A$2:$A$7) dan gunakan referensi relative untuk sel paling atas (A2) supaya mendapatkan rumus untuk mengatur secara tepat masing-masig sel dalam range tervalidasi.


    Formula Data Validation Mencegah Duplikat


    Catatan: contoh formula validasi di atas data tidak bersifat case sensitive, artinya tidak membedakan keberadaan huruf kapital maupun huruf kecil.

    Formula Validasi untuk tanggal dan waktu.


    Fitur data validation di excel sudah menyediakan cukup banyak kriteria siap pakai yang dapat digunakan untuk membatasi entri tanggal, yaitu: antara dua tanggal, sebelum, sesudah, atau sama dengan tanggal ditentukan. Jika anda menginginkan kontrol lebih terhadap data validation di dalam spreadsheet, maka anda dapat melakukan kustomisasi rule dengan mengetikan rumus anda sendiri untuk meningkatan kapabilitas data validation terhadap entri tanggal di excel.

    Mengizinkan entri tanggal dalam rentang tertentu (antara 2 tanggal)


    Untuk membatasi entri data dengan rentang tanggal tertentu, anda dapat menggunakan salah satu rule siap pakei “between” atau menggunakan custom validation rule dengan rumus umum sebagai berikut: AND(sel>=tanggal_mulai), sel<=tanggal_selesai) dimana:
    • sel adalah sel paling kiri atas dalam range yang divalidasi.
    • tanggal_mulai dan tanggal_selesai merupakan tanggal valid yang disediakan melalui fungsi DATE atau referensi terhadap sel yang berisi tanggal.
    Sebagai contoh, untuk mengizinkan hanya entri tanggal dalam bulan Juli tahun 2018 pada kolom D, maka gunakan formula berikut:

    =AND(D2>=DATE(2018,7,1),D2<=DATE(2018,7,31))

    Atau,masukan tanggal_mulai dan tanggal_selesai pada beberapa sel (contoh G1 dan G2), kemudian referensikan sel-sel tersebut dalam formula.

    =AND(D2>=$G$1, D2<=$G$2)


    Formula data validation rentang tanggal

    Harap diperhatikan bahwa batasan tanggal  harus dikunci oleh referensi absolute

    Mengizinkan hanya entri tanggal hari kerja dan akhir pekan.


    Untuk membatasi user supaya hanya memasukan hari kerja dan/atau akhir pekan, maka lakukan konfigurasi rule validasi menggunakan fungsi WEEKDAY. Dengan argumen return_type di set ke angka 2, maka fungsi WEEKDAY akan menghasilkan nilai integer mulai angka 1 (Senin) sampai dengan 7 (Sabtu), sehingga untuk hari kerja (senin ke jumat) bernilai kurang dari 6 dan weekend (sabtu dan minggu) bernilai lebih dari 5.

    Hanya mengizinkan entri tanggal hari kerja:
    WEEKDAY(sel,2)<6

    Hanya mengizinkan tanggal akhir pekan:
    WEEKDAY(sel,2)>5

    Sebagai contoh, rumus dibawah ini bisa digunakan untuk mengizinkan entri hanya berupa hari kerja pada sel C2:C7:

    =WEEKDAY(C2,2)<6

    Formula data validation entri tanggal hari kerja


    Validasi data berdasarkan tanggal saat ini.


    Dalam beberapa situasi, anda mungkin ingin menjadikan tanggal saat ini sebagai acuan tanggal awal yang dizinkan. Untuk mendapatkan tanggal saat ini, kita bisa menggunakan fugsi TODAY.

    Selanjutnya tambahkan sebuah bilangan yang menunjukan jumlah hari setelah tanggal saat ini untuk menghitung tanggal akhir. Misalnya, untuk membatasi entri data maksimal 6 hari dari sekarang (7 hari termasuk hari ini), kita akan menggunakan rule siap pakai dengan kriteria berdasarkan formula:
    • Pada kotak allow, pilih Date.
    • Selanjutnya pada kotak data, pilih between
    • Pada kotak start date, masukan rumus =TODAY()
    • Dalam kotak End date, masukan rumus =TODAY()+6
    Dengan cara yang serupa, anda dapat membatasi entri tanggal sebelum dan sesudah tanggal saat ini. Untuk itu, pilih salah satu less then atau greater than pada kotak Data, kemudian masukan =TODAY() dalam kotak end date atau start date


    Formula data validation tanggal hari ini ke depan

    Validasi waktu berdasarkan waktu saat ini


    Untuk memvalidasi data berdasarkan waktu saat ini, kita dapat memodifikasi rule predefined untuk waktu dengan menggunakan formula.

    Pada kotak Allow, pilih Time Dalam kotak Data, anda dapat mengambil salah satu kriteria, apakah less than untuk mengizinkan hanya waktu sebelum saat ini, ataupun greather than untuk mengizinkan waktu setelah saat ini.

    Dalam kotak end time atau start time (tergantung kriteria yang anda seleksi pada tahap sebelumnya), masukan salah satu formula berikut:

    Untuk memvalidasi tanggal dan waktu berdasarkan tanggal dan waktu sekarang: =NOW()

    Untuk memvalidasi waktu berdasarkan waktu sekarang:

    =TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

    Screenshot dibawah ini memperlihatkan sebuah rule yang mengizinkan input hanya waktu setelah saat ini.

    Formula data validation waktu sekarang ke depan


    Custom data validation tidak bekerja.


    Jika data validaton berdasarkan rumus atau formula tidak bekerja sesuai   harapan, maka ada 3 hal yang perlu dicek:
    1. Formula validasi harus benar.
    2. Formula Validasi tidak boleh mereferensi ke sel kosong.
    3. Harus menggunakan referensi sel yang tepat.

    Cara mengecek benar tidaknya sebuah formula data validation.

    • Untuk memulai, copy formula validasi kedalam sejumlah sel untuk memastikan bahwa formula tersebut tidak menghasilkan nilai error seperti #N/A, #VALUE or #DIV/0!.
    • Jika anda membuat sebuah custom rule, formula harus menghasilkan nilai logika TRUE atau FALSE, atau nilai 1 dan 0.
    • Jika anda menggunakan sebuah kriteria berdasakan formula dalam sebuah rule build in (seperti sudah kita buat untuk validasi waktu berdasarkan waktu sekarang), ini juga akan menghasilkan nilai numerik lainnya.

      Formula data validation tidak boleh mereferensi ke sel kosong.


      Dalam beberapa situasi, jika anda memilih kotak Ignore Blank ketika mendefinisikan rule (biasanya terseleksi secara default), kemudian ada satu atau lebih sel kosong dalam range referensi maka nilai apapun akan diizinkan dalam sel tervalidasi. Berikut screenshot yang yang menggambarkan masalah tersebut:

      Contoh penyebab formula data validation tidak berfungsi


      Dari screenshot diatas, kita dapat mengetahui: dikarenakan sel yang dijadikan referensi nilai maksimal (sel B2) tidak ada konten apapun di dalamnya, alias kosong, maka validasi tidak bekerja dengan benar pada range D2:D4 dimana angka berapapun bisa diinput, tidak lagi dibatasi nilai minimum maupun maksimum.

      Penggunaan referensi relative dan absolute dalam rumus validasi.


      Ketika anda men-setting sebuah rule validasi berdasarkan formula, pastikan bahwa semua referensi sel dalam formula bersifat relative terhadap sel paling kiri atas dalam range diseleksi.

      Penjelasan mengenai perbedaan referensi relative dan absolute dapat disimak pada artikel: Perbedaan alamat sel relatif, absolut dan semi absolut.

      Jika anda membuat sebuah rule untuk lebih dari satu sel dan kriteria validasi anda tergantung pada sel-sel tertentu, pastikan gunakan referensi absolute (menggunakan tanda dolar, misalnya $A$2). Jika tidak maka rule validasi tidak akan bekerja sesuai yang diharapkan.

      Untuk menggambarkan hal ini, mari kita perhatikan contoh berikut: Anggaplah, anda ingin membatasi data entri di sel D2 s/d D4 dengan bilangan minimum dan maksimum yang mereferensi ke sel A2 (minimum) dan B2 (maksimum).

      Kemudian anda membuat rule validasi whole number – between – dan memasukan rumus pada kotak minimum dan maksimum seperti terlihat dalam gambar berikut ini:

      Formula data validation referensi tidak tepat


      Perhatikan gambar di atas. Secara sepintas, rumus pada kotak minimum dan maksimum sepertinya benar. Namun jika anda mencoba memasukan entri data pada sel D2, D3 dan D4, anda akan mendapati hanya di sel D2 validasi bekerja secara benar.

      Setelah diteliti, ternyata masalahnya adalah rumus pada kotak minimum dan maksimum bersifat relatif, sehingga rule validasi di sel D2, D3, dan D4 berbeda beda.

       Mari kita cek rule validasi di sel D3 dengan cara tekan seleksi sel D3, kemudian tekan Alt → D → L  secara berurutan untuk masuk ke jendela Data Validation seperti screenshot di bawah ini.


      Formula data validation referensi relative


      Perhatikan referensi pada kotak minimum dan maksimum. Ternyata referensi berubah secara relatif terhadap referensi awal (A2 menjadi A3 dan B2 menjadi B3). Inilah yang mengakibatkan rule validasi tidak bekerja dengan benar di sel D3, karena sel A3 dan B3 sebagai referensi minimum dan maksimum merupakan sel kosong.

      Untuk mengatasi masalah tersebut, cukup dengan mengetikan tanda dolar “$” sebelum referensi kolom dan baris untuk menguncinya :
      • kotak minimum   =$A$2
      • kotak maksimum =$B$2
      Dalam kasus penentuan referensi sebenarnya tidak ada keharusan apakah harus menggunakan absolute atau relative. Ini semua tergantung pada pola data dan tabel kriteria yang tersedia. anda bisa menggunakan relative, absolute atau kombinasi keduanya dalam situasi yang lebih komplek.

      Demikian pembahasan mengenai cara menggunakan custom validation di excel. Semoga penjelasannya mudah difahami. Kritik dan saran dari pembaca sangat diharapkan untuk perbaikan kedepannya. Salam.

      Silahkan dicek juga catatan pelajaran excel lainnya:

      Sunday, November 11, 2018

      Cara Menggunakan Data Validation di Excel

      Catatan pelajaran excel ini menjelaskan hal basic mengenai cara menggunakan fitur data validation di Excel. Termasuk diantaranya: bagaimana membuat validation rule untuk angka, tanggal dan text, membuat validasi list, cara meng-copy validation ke sel lainnya, bagaimana menemukan entri yang tidak valid, serta bagaimana cara memperbaiki dan menghapus validasi.

      Untuk pembahasan lebih lanjut mengenai kustomisasi data validation dapat disimak pada artikel: Cara Menggunakan Rumus untuk Data Validation.

      Secara garis besar, topik pembahasan dasar-dasar cara menggunakan data validation di excel ini mencakup:

      Ketika anda mempersiapkan sebuah template excel untuk sebuah laporan, tentu anda ingin memastikan bahwa nantinya semua user dapat menginput data sesuai kriteria yang anda harapkan, secara akurat dan persisten.

      Secara teknis, anda perlu mengkontrol informasi apa saja yang semestinya diinput pada sel tertentu oleh user. Misalnya: apakah berupa bilangan atau tanggal dengan kisaran tertentu, atau berupa text dengan jumlah karakter maksimum tertentu. Anda bahkan bisa menyediakan list entri tertentu untuk mengeliminasi kemungkinan kesalahan pengetikan.

      Fitur Data validation di excel mengizinkan anda untuk mempersiapan semua hal tersebut di atas. Fitur ini tersedia baik pada excel versi lama (2003 ke bawah), maupun versi baru (2007, 2010, 2013 dan versi yang lebih baru).

      Apakah yang dimaksud dengan Data Validation di Excel ?


      Data Validation adalah fitur excel untuk membatasi inputan user pada sebuah worksheet. Praktisnya, anda dapat membuat rule validation yang dapat meng-kontrol jenis data apa saja yang boleh diinput pada cell tertentu.

      Berikut beberapa hal apa saja yang dapat dilakukan oleh data validation di excel:

      • Mengizinkan input bilangan atau text saja pada sebuah sel
      • Hanya mengizinkan input bilangan pada rentang tertentu saja.
      • Mengizinkan data entri yang spesifik.
      • Mencegah input tanggal dan waktu diluar rentang yang ditentukan.
      • Mencegah entri selain dari data yang ada di dropdown list
      • Validasi sebuah entri berdasarkan konten sel lainnya.
      • Menyampaikan pesan input ketika user menyeleksi sebuah sel tervalidasi.
      • Memperlihatkan pesan pemberitahuan / peringatan ketika user menginput entri yang tidak tepat.
      • Menemukan entri yang tidak benar pada sel tervalidasi.

      Sebagai contoh, kita dapat men-set up sebuah rule untuk membatasi entri data hanya 5 digit diantara 10000 dan 99999. Ketika seorang user mengetik entri data yang tidak sesuai kriteria  dan kemudian menekan enter, maka excel akan memunculkan pesan error yang memberitahu masalah error input tidak valid. Dengan demikian user dapat mengetahui kesalahan input dan segera memperbaikinya.

      Screenshot di bawah ini memperlihatkan sebuah contoh pesan pemberitahuan di excel mengenai input tidak valid pada sel tervalidasi.


      data validation invalid input allert

      Bagaimana cara menambahkan data validation di excel?


      Ikuti langkah-langkah berikut untuk menambahkan validasi data di excel.

      1. Buka kotak dialog data validation

      Blok satu atau lebih sel yang akan divalidasi. Kemudian melalui tab Data > Klik tombol Data Validation  (Excel 2003: menu Data > Validation…)

      Anda juga dapat membuka jendela data validation dengan cara menekan shortcut ALT + D + L  (tekan tombol keyboard secara berurutan, bukan bersamaan)



      2. Membuat Rule validasi data.

      Pada tab settings, tentukan kriteria validasi sesuai kebutuhan anda. Untuk Validation criteria, anda dapat mengisinya baik dalam bentuk value langsung, referensi cell maupun dalam bentuk rumus / formula.
      • Values – mengetik angka dalam kotak kriteria secara langsung seperti terlihat dalam screenshot di bawah (nilai minimum dan maximum)
      • Cell reference – untuk membuat sebuah rule berdasarkan konten satu atau lebih cell. Misalnya nilai minimum dan maximum berdasarkan bilangan yang terdapat dalam 2 cell referensi..
      • Formulas – mengizinkan anda untuk mengekpresikan kondisi validasi yang lebih komplek. Misalnya membuat nilai minimum dan maksimum berdasarkan formula tertentu.
      Sebagai latihan, mari kita membuat sebuah rule yang membatasi user untuk menginput data di antara 10000 dan 99999 seperti terlihat dalam screenshot di bawah ini.
        data validation whole number minimum maximum



        Setelah konfigurasi rule validasi selesai dilakukan,  selanjutnya anda dapat pindah ke tab Input Message atau Error Alert untuk menambah sebuah pesan arahan input maupun pesan error (error alert). Jika sudah dirasa cukup, selanjutnya klik OK untuk eksekusi dan sekaligus keluar dari jendela Data Validation.


        3. Menambahkan pesan input (opsional)

        Jika anda ingin memunculkan sebuah pesan yang menjelaskan kepada user mengenai kriteria data yang diizinkan untuk diinput pada sebuah sel, maka buka tab Input Message dan ikuti langkah berikut:

        Pastikan  kotak "Show input message when cell is selected" sudah dipilih (dicentang).

        Isi Title dan Input Message pada kotak text yang sesuai, lalu klik Ok untuk keluar dari kotak dialog Data Validation.



        Pesan yang sudah kita buat seperti contoh di atas akan muncul serta merta ketika user menyeleksi sel  tervalidasi, seperti terlihat dalam screenshot di bawah ini.



        4. Memunculkan Peringatan Error (opsional)

        Sebagai tambahan, anda juga bisa memunculkan pemberitahuan error (error alert) ketika user menginput data yang tidak valid pada sel tervalidasi. Excel menyediakan 3 pilihan alert type yaitu:

        Alert type : Stop(default)

        data validation alert type stop (default)


        Pemberitahuan untuk mencegah user memasukan data invalid. Anda bisa klik Retry untuk mencoba kembali dengan nilai lainnya, atau klik Cancel untuk membatalkan dan menghapus entri.

        Alert type : Warning


        data validation alert type warning


        Memperingatkan user bahwa data yang dimasukan tidak valid. Tetapi tidak berusaha mencegah input data selanjutnya.

        Alert type : Information


        data validatin alert type information


        Type alert information hanya menginformasikan kepada user mengenai data entri yang tidak valid.
        Klik OK untuk memasukan inputan yang tidak valid atau klik Cancel untuk membatalkan entri data.


        Untuk mengkonfigurasi pesan error, pergi ke tab Error Alert dan tentukan parameter-parameter berikut:

        • Centang kotak Show error alert after invalid data is entered (biasanya sudah tercentang secara default)
        • Pada kotak Style, plih type alert yang diinginkan, misalnya "Stop"
        • Masukan title dan pesan error ke dalam  kotak yang sesuai, misalnya text "Masukan 5 Digit Angka 10000 s/d 99999"
        • Klik OK.
        • Untuk legih jelasanya dapat dilihat pada screenshot di bawah ini.




        Hasilnya: jika anda memasukan data tidak valid pada sel yang sudah divalidasi dengan konfigurasi sesuai contoh di atas, maka excel akan memunculkan pesan khusus yang menjelaskan error, seperti dapat kita lihat pada contoh di bagian awal tutorial excel ini.

        Catatan: jika anda tidak membuat pesan error khusus, maka secara default, excel akan memuncukan default Stop Alert ketika user memasukan data yang tidak valid, yang bunyinya: "This value does not match the data validation restrictions defined for this cell".


        Contoh dan Jenis Data Validation.


        Pada saat menambahkan rule validasi data di excel, kita bisa memilih salah satu setting yang sudah tersedia. Kita juga bisa membuat kriteria sendiri berdasarkan formula yang dibuat sendiri. Di bawah ini kita akan diskusikan lebih lanjut mengenai masing-masing opsi validasi data bawaan excel.

        Seperti sudah kita ketahui, kriteria validasi didefinisikan pada tab Setting dalam kotak dialog Data Validation  yang dapat diakses melalui tab (Excel 2007, 2010, 2013 dst...: tab Data > Data Validation  (Excel 2003: Menu Data > Validations...)

        Whole numbers dan decimals


        Untuk membatasi entri data jenis "whole number" maupun "decimal", pilih item yang sesuai pada box Allow, dan kemudian pada kotak data pilih salah satu dari kriteria dari dropdown list seperti terlihat dalam berikut:

        data validation whole number rule


        • Between : Mengizinkan input value diantara bilangan tertentu.
        • Not between : Mengizinkan input value kecuali nilai diantara bilangan tertentu.
        • Equal to : Hanya satu nilai tertentu saja yang boleh diinput
        • Not equal to : Mengizinkan input semua bilangan kecuali nilai tertentu.
        • Greater than: hanya boleh input bilangan yang lebih besar dari nilai tertentu.
        • Less than: hanya boleh input bilangan yang lebih kecil dari nilai tertentu.
        • Greather than or equal to: hanya boleh input bilangan yang lebih besar atau sama dengan nilai tertentu.
        • Less then or equal to: hanya boleh input bilangan yang lebih kecil atau sama dengan nilai tertentu.


        Screenshot dibawah ini memperlihatkan contoh pengaturan rule validasi yang hanya mengizinkan input bilangan minimal 1 dan maksimal 10.

        data validation rule maximum minimum

        Validasi Tanggal dan Waktu di Excel


        Untuk validasi tanggal, pilih "Date" pada kotak Allow, kemudian ambil kriteria yang sesuai pada kotak Date. Ada cukup banyak opsi yang tersedia yang dapat dipilih: "allow only dates between two dates", "equal to", "greater than" atau "less than" dan kriteria lainnya.

        Demikian juga untuk validasi waktu, pilih Time pada kotak Allow, dan kemudian tentukan kriteria yang diperlukan
        .
        Contoh: untuk mengizinkan hanya tanggal diantara tanggal awal di B1 dan tanggal akhir di B2, maka coba terapkan rule validasi berikut ini.


        data validation between date

        Text length


        Dengan memilih kategori Text length, maka kita bisa membatasi entri text pada jumlah karakter tertentu sesuai kebutuhan, dengan pilihan between, not between dan seterusnya.
        Sebagai contoh: untuk membatasi input maksimal 100 karakter, maka buatlah rule validasi “less then or equal to”, seperti terlihat dalam screenshot di bawah ini.

        data validaton maximum text length


        Catatan:  opsi Texth length membatasi jumlah karakter tetapi tidak dengan type data. Artinya rule diatas tidak membatasi apakah data yang bisa diinput berupa huruf atau angka.

        Validasi Data Berbentuk Drop Down List:


        Untuk menambahkan drop-down list  item-item tertentu pada sebuah sel atau sejumlah sel, pilih target sel, kemudian ikuti langkah berikut:

        • Buka kotak dialog Data Validation (tab Data > Data Validaton)
        • Pada tab Setting, pilih list pada kotak Allow
        • Dalam kotak Source, ketik item-item yang anda inginkan untuk muncul dalam validation list. masing-masing item dipisahkan oleh koma, misalnya: untuk membatasi inputan atas 3 pilihan "YA", "TIDAK" atau "RAGU-RAGU"
        • Pastikan kotak In-cell dropdown dicentang supaya tanda panah dropdown bisa muncul di sel.
        • Selanjutnya klik OK.

        data validation create dropdown list

        Hasilnya maka data validation di excel akan terlihat seperti berikut ini:


        data validation dropdown list example


        Catatan: hati-hati dengan opsi Ignore Blank, yang secara default sudah terseleksi (centang). Dalam kondisi opsi Ignore Blank tercentang (TRUE), jika anda membuat dropdown list menggunakan referensi nama range, pastikan tidak ada sel kosong pada referensi nama range tersebut. Jika ada cell kosong, maka user akan bisa menginput data apapun pada sel tervalidasi.

        Bagaimana cara meng-edit data validation pada excel.


        Untuk merubah sebuah rule validasi, lakukan langkah-langkah berikut:
        • Seleksi satu atau sejumlah sel tervalidasi yang akan di-edit validasinya.
        • Buka kotak dialog Data  Validation (tab Data > Data Validation)
        • Lakukan perubahan yang diperlukan.
        • Pilih checkbox "Apply these changes to all other cells with the same settings" untuk meng-copy perubahan yang anda sudah buat ke semua sel dengan kriteria validasi awal yang sama.
        • Klik OK untuk menyimpan perubahan.

        Sebagai contoh, anda dapat mengedit list validasi data dengan menambahkan atau menghapus item tertentu dari kotak Source, dan anda akan melihat perubahan ini diaplikasikan ke semua sel yang memiliki drop-down list yang sama.

        How to edit data validation rule


        Perhatikan gambar di atas: anda bisa merubah isi kotak  Source, dengan menambah item list atau pun menguranginya. Masing-masing item dipisahkan dengan tanda koma.

        Bagaimana cara meng-copy rule validasi ke sel lain


        Jika anda membuat sebuah rule validasi untuk sebuah sel dan anda ingin menerapkannya ke sel lainnya maka anda tidak perlu membuatnya pada sel lainnya. Anda cukup mengcopy data validation dengan cara berikut:

        Seleksi sel yang memiliki rule validasi dan kemudian tekan Ctrl + Copy untuk mengcopy nya.

        • Pilih sel lainnya yang ingin anda validasi juga. Untuk memilih sel yang tidak berdekatan, tekan dan tahan tombol Ctrl sembari menyeleksi sel.
        • Klik kanan pada range tujuan dan klik Paste Special, kemudian pilih Validation. Sebagai alternatif , tekan Paste Special > Validation, short cut CTRL + ALT + V, kemudian N.
        • Klik OK

        Copy paste data validation only


        Tips: daripada anda harus mengcopy data validation ke sel lain. Anda dapat menghemat tenaga anda dengan cara mengkonversi data set menjadi excel tabel. Setiap kali anda menambahkan baris baru pada data tabel, excel juga akan menambahkan validasi secara otomatis sesuai validasi pada baris di atasnya.


        Bagaimana cara menemukan sel tervalidasi di excel


        Untuk mendapatkan lokasi sel tervalidasi secara cepat, pada worksheet, pergi ke tab Home > Editing Grup > klik Find & Select > Data Validation.


        Cara menemukan cell tervalidasi


        Langkah tersebut akan menyeleksi semua sel yang diterapkan data validasi terhadapnya.



        How to select all validated cells

        Bagaimana menghapus data validaton pada excel


        Setidaknya, ada dua cara untuk menghapus data validasi di excel. Pendekatan standar (cara reguler)  serta tehnik copy paste special.

        Metode 1: Cara regular untuk menghapus data validation


        Normalnya, anda dapat melakukan langkah berikut untuk menghapus validasi di excel:

        • Blok sel yang yang memiliki data validasi
        • Melaluir ribbon, pada tab Data , klik tombol Data Validation untuk masuk ke jendela Data Validation.
        • Pada tab Setting, klik tombol Clear All, dan kemudian klik OK.
        How to delete all data validation



        Tips:
        Tips: untuk menghapus data validation dari semua sel dalam sheet aktive gunakan fitur Find & Select untuk menyeleksi semua sel tervalidasi dengan cara yang sudah dijelaskan di atas mengenai Cara Menemukan Sel Tervalidasi di Excel.

        Untuk menghapus rule validasi tertentu, pilih salah satu cell dengan rule tersebut, buka kotak dialog Data Validation, dan cek "Appy these changes to all other cells with the same settings". Dan kemudian klik tombol Clear All.

        Sebagaimana anda lihat, metode standar ini cukup mudah dan cepat.  Namun bagi anda yang menginginkan cara yang lebih simple dan sedikit lebih cepat , maka pendekatan berikut mungkin akan terasa lebih nyaman.

        Metode 2: Paste Special untuk menghapus rule validasi.


        Secara teknis. paste special biasanya digunakan untuk paste elemen spesifik dari sel yang di-copy. Salah satu elemen tersebut adalah validation. Dengan demikian kita bisa menghapus data validation dengan cara copy cell yang tidak ada validasinya kemudian paste special validation pada cell target..

        Berikut langkah-langkahnya:
        • Pilih sebuah sel tanpa validasi dan tekan Ctrl + C untuk meng-copy sel tersebut.
        • Pilih sel atau sekumpulan dimana anda ingin untuk menghapus data validation-nya
        • Tekan Alt + E + S + V + N secara berurutan sebagai short cut untuk Paste Special  Validation
        • Tekan Enter, dan selesai.


        Metode ke-2 ini dipersembahkan khusus terutama bagi anda yang lebih suka bekerja menggunakan keyboard dibanding mouse. Namun hal ini tidak menutup cara bahwa anda juga bisa menggunakan mouse untuk melakukan copy paste special validation.

        Tips seputar data validation di excel.


        Sekarang, anda sudah mengetahui dasar-dasar cara menggunakan fitur data validation di excel. Selanjutnya izinkan saya untuk berbagi tips bagaimana menjadikan rule validasi lebih effektive.

        Data validation berdasarkan konten sel.


        Daripada mengetik nilai kriteria secara langsung pada kotak criteria di jendela data validation, lebih baik kita memasukan nilai kriteria ke beberapa sel dan membuat referensi ke sel tersebut. Cara ini sangat efektif karena jika di kemudian hari anda memutuskan untuk merubah kondisi validasi, maka anda hanya perlu mengedit nilai pada cell referensi tanpa harus membuka jendela data validation untuk merubah rule.

        Untuk memasukan referensi sel: klik dimana saja dalam kotak value, kemudian seleksi sel yang akan dijadikan sebagai referensi menggunakan mouse. Selain itu anda juga dapat mengetikannya secara langsung pada kotak value.

        Sebagai contoh: untuk memvalidasi sel E2 supaya hanya mengizinkan input bilangan dengan nilai minimum sesuai bilangan di sel B10, ambil kriteria not equal pada kotak data dan ketikan =$A$1 di kotak Minimum.

        data validation minimum value




        Pada tahap yang lebih lanjut, Anda juga dapat memasukan sebuah formula pada referensi sel, dan excel akan memvalidasi input berdasarkan formula tersebut.

        Sebagai contoh: untuk mencegah user memasukan tanggal sebelum tanggal hari ini, maka masukan rumus  =TODAY()  pada sebuah sel, anggaplah se. B1, kemudian set up rule validasi berdasarkan sel tersebut.


        data validation formula TODAY


        Selain itu, anda juga bisa memasukan rumus =TODAY()  secara langsung pada kotak Start Date untuk memberikan hasil validasi yang sama.


        Validation Rules Menggunakan Formula


        Dalam situasi dimana tidak mungkin mendefinisikan kriteria validasi yang diinginkan dalam sebuah value atau referensi sel, maka anda dapat mengespresikannya menggunakan sebuah formula.
        Sebagi contoh, untuk membatasi entri dengan nilai minimum dan maksimum pada list bilangan yang sudah anda, katakanlah range A1:A10, maka gunakan formula berikut:

        =MIN($A$1:$A$10)

        =MAX($A$1:$A$10)


        data validation rule formula


        Harap perhatikan bahwa kita harus mengunci range dengan tanda dolar (referensi sel absolut) sehingga validasi bekerja secara tepat untuk sel yang diseleksi.

        Bagaimana cara menemukan data tidak valid dalam worksheet.


        Meskipun excel mengizinkan data validasi terhadap sel-sel yang sudah memiliki data, namun excel tidak memberitahu anda jika beberapa data yang sudah ada tidak sesuai dengan kriteria validasi. Masalah ini juga kerap muncul ketika kita merubah nilai pada sel yang terkait sebagai referensi validasi.

        Untuk menemukan data yang tidak valid, pergi ke tab Data dan klik Data Validation > Circle Invalid Data.



        Cara ini akan meng-higlight semua sel yang tervalidasi tetapi kontennya tidak sesuai dengan kriteria validasi.

        how to circle invalid data


        Segera setelah anda mengklik circel invalid data, maka lingkaran (circle) merah akan akan muncul secara otomatis melingkari data invalid. Untuk menghapus circle, pergi ke tab Data dan klik Data Validation > Clear Validation Circles.

        Bagaimana cara proteksi worksheet tervalidasi.


        Jika anda ingin memproteksi worksheet atau workbook dengan pasword, maka lakukan langkah-langkah berikut:

        • Tentukan range sel yang akan divalidasi.
        • Lakukan konfigurasi pengaturan data validation yang diinginkan dengan cara yang sudah dijeskan sebelumnya. (melalui tab Data Validation)
        • Atur status proteksi cell melalui format cell menjadi unlock. Caranya: klik kanan pada seleksi range yang akan divalidasi > Format Cells… >  masuk ke tab Protection > hilangkan centang pada checkbock Locked. >> klik OK
        • Langkah selanjutnya lakukan protect worksheet, melalui ribbon, tab Review > Protect Sheet.

        Bagaimana cara sharing sebuah workbook dengan data validation.


        Untuk mengizinkan beberapa user berkolaborasi dalam sebuah workbook. Pastikan untuk share workbook setelah anda selesai membuat data validation.

        Caranya:

        • Melalui Tab Review > Share Workbook > centang kotak Allow Changes by more than one user at the same time > klik OK.
        • Jika anda masih menggunakan excel 2003: melalui menu Tools > Share Workbook…

        Setelah sharing workbook, data validation akan tetap bekerja, namun anda tidak akan bisa merubahnya, maupun menambahkan rule baru.

        Kenapa data validation tidak berfungsi 


        Jika data validation tidak berfungsi dengan benar dalam worksheet, kemungkinan disebabkan hal-hal berikut:

        Data validation tidak bekerja untuk data yang dicopy


        Data validation di excel didesain untuk mencegah user mengetik data yang tidak valid pada sebuah sel. Namun fitur ini tidak bisa mencegah user untuk mengcopy data yang tidak valid. .

        Meskipun tidak ada cara untuk men-disable copy paste (kecuali dengan VBA), setidaknya kita bisa mencegah copy data yang dilakukan dengan drag dan drop cell.

        Untuk men-disable drag & drop cell,  lakukan langkah berikut:

        • Excel 2010,2013,2016:  File > Options > Advanced > Editing Options > hilangkan centang pada checkbox Enable Fill Handle and Cell drag and drop.
        • Excel 2007 : Office Button (logo office di pojok kiri atas) > Excel Options > Editing Options > hilangkan centang pada checkbox Enable Fill Handle and Cell drag and drop.
        • Excel 2003 : Klik menu Tools > Options > masuk ke tab Edit > hilangkan centang pada checkbox Allow cell drag and drop.


        Berikut contoh pengaturan di excel 2010

        how to disable dag & drop



        Data Validation tidak bisa di-ubah ketika cell dalam mode edit.


        Pada mode edit, yaitu pada saat user meinput atau mengetik pada sebuah sel, maka command data validation tidak tersedia (warna menjadi abu-abu). Dalam kondisi ini user tidak bisa melakukan edit data validation.

        Untuk dapat melakukan penambahan atau mengedit data validation, maka terlebih dahulu harus keluar dari mode Edit, dengan cara tekan tombol Enter, atau ESC.

        Fitur data validation tidak berfungsi pada workbook yang diproteksi dan di-share.


        Meskipun rule validasi yang sudah ada masih bisa tetap bekerja pada workbook yang diproteksi dan di-share, namun user tidak akan bisa mengedit atau menambah rule validasi dalam kondisi demikian.
        Untuk bisa mengedit rule validasi, maka terlebih dahulu proteksi harus dihilangkan dan mode share workbook harus di-nonactive-kan.

        Formula pada Data Validation tidak benar.


        Ketika mengerjakan data validation berdasarkan formula pada excel, maka ada tiga hal penting yang perlu di cek, yaitu:

        • Rumus validasi tidak menghasilkan nilai error
        • Rumus validasi tidak mereferensikan sel kosong.
        • Gunakan cel referensi yang tepat.

        Kalkulasi Otomatis tidak aktif


        Jika setting kalkulasi di-set pada mode manual, maka formula yang tidak terkalkulasi akan menyebabkan validasi tidak benar. Untuk merubah opsi kalkulasi di excel ke mode otomatis, pergi ke tab Formula > grup Calculation > klik tombol Calculation > klik Automatic. (excel 2003: Tools > Options > Calculation > pilih Automatic > OK).

        Untuk informasi yang lebih lengkap mengenai kalkulasi otomatis, bisa di lihat di artikeal Kenapa Rumus Excel Tidak Berfungsi.

        Demikianlan pembahasan bagaimana cara menggunakan data validation di excel, Terimakasih sudah membaca sampai selesai. Semoga bermanfaat dan sampai jumpa kembali dalam artikel berikutnya.

        Ingin lebih mendalami fitur data validation di excel? silahkan dicek dalam artikel lanjutannya : Menggunakan Rumus untuk Data Validation

        Mungkin anda tertarik dengan tutorial excel lainnya:



        Referensi:
        https://www.ablebits.com/office-addins-blog/2017/08/16/data-validation-excel/


        Sunday, October 28, 2018

        Cara VLOOKUP Gambar dan Foto di Excel

        Bagaimana cara VLOOKUP gambar atau Foto Di Excel? Artikel ini akan membahas bagaimana menggunakan fungi VLOOKUP supaya bisa digunakan untuk lookup Gambar. Oh iya, catatan ini melengkapi artikel sebelumnya mengenai cara Lookup Gambar menggunakan fungsi INDEX MATCH.

        Dalam artikel Lookup Gambar dengan INDEX MATCH sudah dijelaskan bahwa sebuah objek gambar harus link ke referensi range supaya bisa menangkap visual range tersebut dalam bentuk picture link. Sedangkan output dari rumus VLOOKUP adalah berupa value, bukan referensi. Oleh karena itu, maka fungsi VLOOKUP tidak bisa berdiri sendiri jika hendak digunakan dalam lookup Gambar.





        Sebagai solusinya, maka kita bisa menggabungkan fungsi VLOOKUP dengan fungsi INDEX. Dalam rumus gabungan ini, fungsi VLOOKUP berperan untuk mencari nomor index baris. Kemudian tugas selanjutnya diserahkan ke fungsi INDEX. Mengapa fungsi INDEX? Karena fungsi index menghasilkan output berupa referensi cell.

        Untuk lebih mudah memahami bagaimana cara kerja rumus VLOOKUP untuk gambar, mari kita simak penjelasan tips excel dan contoh berikut:

        Bagaimana Cara VLOOKUP Gambar di Excel


        Anggaplah kita sudah memiliki sejumlah foto atau gambar jenis-jenis  hewan seperti terlihat dalam screenshot di atas.

        Database binatang tersimpan dalam sebuah tabel terdiri atas list hewan/binatang lengkap dengan nomor urutnya serta foto binatang terkait (lihat kolom A s/d C). Selanjutnya kita ingin menampilkan foto binatang yang dicari di sel F2 berdasarkan nama binatang yang diinput di sel E2. (lihat kolom E dan F).

        Bagaimana caranya menggunakan fungsi VLOOKUP untuk  gambar / foto dan dimana kita bisa menempatkan rumus untuk mencari Photo.

        Ikuti langkah-langkah berikut.

        Supaya memudahkan dalam pembuatan dan pembacaan rumus, disarankan untuk memberi nama range yang akan dijadikan referensi yaitu:


        • Range A2:B5  = "listBinatang"
        • Range C2:C5 = "listFotoBinatang"
        • Range E2 = "namaBinatang"


        Pemberian nama range bisa dilakukan dengan cara mem-block range yang akan diberi nama, misalnya A2:B5, kemudian nama range ("listBinatang") diketik pada name box yang posisinya terletak di sebelah kiri kotak formula, biasanya tepat di atas header kolom A.

        Screenshot di bawah ini memperlihatkan bagaimana cara memberi nama pada range A2:B5 = "listBinatang"

        Cara Memberi Nama Range di Excel


        Kemudian lakukan cara yang yang sama untuk memberi nama range C2:C5  ("listFotoBinatang") dan sel E2 ("namaBinatang")

        Mengenai pemberian nama range di excel, juga dapat dibaca  pada artikel Cara Memberi Nama Range Menggunakan Name Box.

        Langkah berikutnya adalah membuat nama referensi untuk foto binatang yang nantinya akan dijadikan sebagai referensi dalam picture link.

        Melalui tab Formula  → klik tombol Define Name

        Cara Define Name Excel


        Maka kemudian kita akan dibawa ke jendela New Name.

        Pada field Name: ketikan "fotoBinatang", tentu saja tanpa tanda kutip
        Setelah itu, pada field Refers To, copy paste atau ketik rumus di bawah ini :

        =INDEX(listFotoBinatang,VLOOKUP(namaBinatang,listBinatang,2,0))

        Cara Define Name untuk Data Validation di Excel


        Selanjutnya klik OK.

        Sekarang, saatnya kita menerapkan defined name yang sudah dibuat sebagai sebagai referensi picture link pada sebuah objek gambar sehingga objek gambar tersebut bisa memperlihatkan foto binatang tertentu.

        Caranya: Buat obyek gambar baru dengan cara meng-copy salah satu gambar yang ada dalam list foto binatang (misalnya foto singa) dan paste pada kotak sel F2. Dalam keadaan gambar hasil copy masih terseleksi, di kotak formula ketikan rumus berikut:

        =fotoBinatang

        Setelah itu tekan Enter.

        Mengenai langkah terakhir ini, lebih jelasnya dapat dilihat dalam screenshot di bawah ini.

        Cara Membuat Picture Link Excel


        Jika semua langkah-langkah yang sudah diuraikan di atas sudah diikuti dengan benar, maka sampai pada tahap ini kita sudah berhasil membuat bagaimana menggunakan fungsi VLOOKUP untuk gambar.

        Silahkan diuji hasil latihan kita dengan cara menggonta-ganti nama binatang yang ada di sel E2 dengan "Singa", "Gajah", "Kuda" dan "Harimau".

        Kita juga bisa mengganti nama binatang yang diinginkan dengan mudah dan cepat melalui dropdown list jika menambahkan validation list pada sel E2 ("namaBinatang"). Caranya: melalui tab Data → klik Data Validation → masuk ke jendela data validationcriteria: Allow = "list" dan source = "=listBinatang".

        Maka hasilnya dapat dilihat dalam gambar gif di bawah ini.


        Cara VLOOKUP Gambar Binatang di Excel





        Penjelasan cara kerja rumus VLOOKUP untuk Gambar?


        Fungsi VLOOKUP dalam pencarian gambar sebenarnya hanya berperan sebagai fungsi pembantu saja, sedangkan fungsi utamanya adalah fungsi INDEX. Fungsi VLOOKUP digunakan untuk mencari nomor index gambar yang dicari. Selanjutnya nomor index gambar digunakan oleh fungsi INDEX untuk mendapatkan alamat referensi range foto / gambar yang dicari.

        Oleh karena itu. Pada tabel binatang dan fotonya, pada kolom ke-2 ditampilkan nomor urut binatang. Nomor tersebut harus di susun berurutan dari mulai angka1 sebagai index pertama.

        Mari kita lihat kembali rumus yang dijadikan referensi dalam defined name fotoBinatang.

        =INDEX(listFotoBinatang,VLOOKUP(namaBinatang,listBinatang,2,0))

        Anggaplah kita ingin mencari tahu foto Harimau itu seperti apa sich?

        Di sel E2 (nama range = "namaBinatang") ketik "harimau" tanpa tanda kutip

        Maka rumus VLOOKUP akan mecari harimau pada kolom ke-1 dalam range A2:B5 (nama range = "listBinatang") dan memberikan output value = 4, yaitu nilai yang terletak pada kolom ke-2 dalam range "listBinatang" yang sejajar dengan value "harimau" di kolom pertama.

        Value 4 yang merupakan output dari rumus VLOOKUP, kemudian dijadikan sebagai index row dalam fungsi INDEX, sehingga rumus dapat dituliskan sebagai berikut:

        fotoBinatang =INDEX(listFotoBinatang,4)

        Rumus INDEX tersebut akan menghasilkan output berupa referensi baris ke-4 dalam range C2:C5 (nama range = “listFotoBinatang”). Dan referensi dimaksud adalah sel C5 yang berisi foto harimau. Sehingga munculah Foto Harimau pada obyek gambar yang menggunakan referensi fotoBinatang.

        Pada contoh yang sudah dibahas di atas, kita menggunakan contoh foto binatang sebabah bahan latihan. Dalam pekerjaan sehari-hari mungkin kita bisa menerapkannya untuk VLOOKUP Foto orang, misalnya untuk VLOOKUP Foto Mahasiswa / Pelajar, VLOOKUP Foto Anggota Organisasi dan VLOOKUP Foto Karyawan. Anda juga bisa mencobanya untuk VLOOKUP Foto barang, dan lain sebagainya.

        Sampai disini mudah-mudahan pembahasan bagaimana menggunakan rumus VLOOKUP untuk gambar dapat mudah difahami, dan tentu harapannya semoga bermanfaat bagi pembaca semua.  Namun tentu saja penulis tidak luput dari kesalahan. Kritik dan saran dari pembaca sangat diharapkan jika ada menemukan kesalahan baik dalam rumus maupun penjelasan yang kurang tepat dalam artikel ini.

        Terimakasih, dan Salam Sukses untuk semua.

        Artikel Terkait:





        Thursday, October 25, 2018

        Lookup Gambar Dengan INDEX MATCH

        Apakah Pencarian Gambar bisa dilakukan di excel? Pertanyaan ini sangat menarik sekali untuk dibahas. Jika pembaca mengikuti blog ini, pada pembahasan sebelumnya sudah pernah dijelaskan beberapa cara untuk melakukan pencarian data atau lookup. Namun output yang dihasilkan adalah berupa value data. Nah, dalam kesempatan ini je-xcel akan membahas bagaimana melakukan  lookup menggunakan rumus INDEX MATCH untuk menghasilkan output berupa gambar.




        Sebelum membahas bagaimana caranya, mari kita perhatikan contoh soal berikut:

        VLOOKUP Gambar di Excel


        Anggaplah kita sudah memiliki sejumlah foto atau gambar buah-buahan seperti terlihat dalam screenshot di atas.

        Data base buah-buah tersimpan dalam sebuah tabel terdiri atas list nama buah dan fotonya (lihat kolom B dan C). Selanjutnya kita ingin menampilkan foto buah yang dicari di sel E2 berdasarkan nama buah yang diinput di sel D2. (lihat kolom D dan E).

        Bagaimana caranya lookup gambar / foto dan dimana kita bisa menempatkan rumus untuk mencari gambar.

        Ikuti langkah-langkah berikut.

        Supaya memudahkan pembuatan rumus, disarankan untuk memberi nama range yang akan dijadikan referensi yaitu:


        • Range A2:A5  = "listBuah"
        • Range B2:B5 = "listFotoBuah"
        • Range D2 = "namaBuah"


        Pemberian nama range bisa dengan cara menyeleksi range yang akan diberi nama, misalnya A2:A5, kemudian ketikan nama range ("listBuah") pada name box yang biasanya terletak di pojok kiri, tepat di atas header kolom atau sebelah kiri kotak formula.

        Untuk lebih jelasnya, perhatikan screen shot di bawah ini menggambarkan cara memberi nama pada range A2:A5 = "listBuah"

        Cara Memberi Nama Range Excel Dengan Name Box


        Lakukan hal yang sama pada range B2:B5  ("listFotoBuah") dan sel D2 ("namaBuah")

        Untuk lebih jelasnya, cara memberi nama range di excel, silahkan dicek artikel Cara Memberi Nama Range Menggunakan Name Box.

        Langkah selanjutnya adalah membuat nama referensi untuk foto buah.

        Melalui tab Formula, klik tombol Define Name

        Define Name Excel


        Selanjutnya kita akan dibawa ke jendela New Name.

        Pada field Name: ketikan "fotoBuah" tanpa tanda kutip

        Lalu pada field Refers To, ketikan rumus berikut:

        =INDEX(listFotoBuah,MATCH(namaBuah,listBuah,0))

        Memberi Nama Excel Define Name


        Selanjutnya klik OK.

        Langkah terakhir adalah menerapkan defined name yang sudah kita buat pada gambar supaya bisa berubah secara otomatis sesuai kata kunci yang kita input.

        Copy salah satu gamber yang ada dalam list foto buah, dan paste pada kotak sel E2. Dalam keadaan gambar hasil copy masih terseleksi, di kotak formula ketikan rumus berikut:

        =fotoBuah

        Setelah itu tekan Enter.

        Mengenai langkah terakhir ini, lebih jelasnya dapat dilihat dalam screenshot di bawah ini.

        Rumus Excel Lookup Foto


        Jika semua langkah-langkah yang sudah dipaparkan di atas sudah diikuti dengan benar, maka sampai pada tahap ini kita sudah berhasil membuat formula untuk melakukan lookup gambar.

        Cobalah dengan mengganti nama buah yang ada di sel D2 dengan “Apel”,, “Durian”, “Pisang” dan “Semangka”.

        Anda juga bisa menambahkan validation list di sel D2 (melalui tab Data  Data Validation  validation criteria: Allow = “list” dan source “=listBuah”). Dengan cara ini kita bisa mengganti nama buah yang diinginkan dengan mudah. Cukup dengan cara cara klik nama buah yang ada di dropdown list seperti dapat dilihat dalam gambar gif berikut ini:

        Excel Lookup Foto


        Apakah Kita Bisa menggunakan rumus VLOOKUP Gambar?






        Dalam tehnik yang kita bahas di sini, rumus VLOOKUP tidak bisa diterapkan sebagai fungsi utama untuk lookup gambar, karena akan menyebagkan error atau gambar / foto tidak muncul sesuai yang diharapkan.

        Sebagai solusinya, kita bisa menggunakan rumus INDEX MATCH untuk mencari gambar. Namun rumus INDEX MATCH ini pun tidak bisa diketikan secara langsung di kotak formula, melainkan diketik  sebagai referensi pada dynamic range yang dalam contoh pembahasan ini diberi nama “fotoBuah”. Define name “namaBuah” ini yang kemudian dijadikan sebagai referensi rumus pada sebuah objek gambar.

        Selanjutnya mari kita mencoba mencari tahu kenapa kita tidak bisa menggunakan VLOOKUP untuk mencari gambar (dalam metode yang dibahas dalam artikel ini)

        Jawabannya: ternyata fungsi VLOOKUP memberikan hasil sebuah value secara langsung, sedangkan rumus INDEX MATCH menghasilkan sebuah referensi range (cell).

        Contoh rumus dalam screenshot di bawah ini mudah-mudahan bisa menguatkan pernyataan di atas..


        Keunggulan INDEX MATCH dibanding VLOOKUP


        Dapat kita lihat dari gambar di atas. Baik rumus VLOOKUP maupun INDEX  MATCH sama-sama menghasilkan data yang benar  ketika digunakan untuk mencari data.

        Fungsi CELL digunakan untuk menguji apakah sebuah hasil rumus berupa value atau sebuah referensi range. Logikanya, sebuah range mestinya memiliki sebuah alamat atau address, sedangkan sebuah value tidak memiliki alamat.

        Anda akan mendapatkan pesan error ketika mencoba menggunakan rumus CELL pada VLOOKUP, sedangkan Rumus CELL INDEX MATCH menghasilkan output sebuah text yang merupakan alamat sel. Alamat sel tersebut menunjukan posisi hasil yang diperoleh (contoh: alamat sel $B$2 mereferensikan sel yang berisi text “Foto Apel”

        Kenyataan ini juga menambah list bukti keunggulan rumus INDEX MATCH dibandingkan VLOOKUP.

        Sampai sini pembahasan bagaimana melakukan lookup gambar. Mudah-mudahan penjelasannya mudah difahami dan tentunya semoga bermanfaat bagi pembaca semua.  Kritik dan saran dari pembaca sangat diharapkan jika ada yang salah dalam artikel ini.