Featured Post

Cara Menggunakan Data Validation di Excel

Catatan pelajaran excel ini menjelaskan bagaimana cara menggunakan fitur data validation di Excel . Termasuk diantaranya: bagaimana membuat...

Sunday, November 11, 2018

Cara Menggunakan Data Validation di Excel

Catatan pelajaran excel ini menjelaskan bagaimana 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.

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

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


  • Apa yang dimaksud data validation di excel
  • Bagaimana cara menambahkan data validation di excel
  • Contoh-contoh data validation
    • Bilangan keseluruhan atau desimal
    • Validasi Tanggal dan Waktu
    • Panjang Text
    • Data validasi dengan dropdown list
  • Bagaimana cara mengedit rule data validation.
  • Bagaimana cara meng-copy rule data validation antar sel.
  • Bagaimana cara menemukan data validation di Excel
  • Bagaimana cara menghapus data validation di excel
  • Tips Menarik Seputar Data Validation.
    • Data validation berdasarkan konten sel.
    • Data validation menggunakan formula
    • Cara menemukan data tidak valid
    • Proteksi worksheet tervalidasi.
    • Berbagi / share workbook tervalidasi.
  • Kenapa data validation tidak bekerja


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.

    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.



    Tuesday, October 23, 2018

    Mengekstrak Angka dari Text Data Entri

    Meng-extract porsi angka dari sebuah entri data dapat dilakukan dengan berbagai cara. Jika sejumlah entri data mempunya pola urutan huruf dan angka yang tetap serta panjang text-nya konstan, maka kita bisa mengambil porsi angka dengan mudah menggunakan fungsi pengolah text seperti LEFT, RIGHT dan MID dikombinasikan dengan fungsi CONCATENATE. Namun jika pola urutan kombinasi angka dan huruf tidak tetap, maka fungsi standar pengolah text di excel tidak bisa menyelesaikan kasus tersebut. Untuk itu diperlukan sebuah UDF (User Defined Function) untuk menyelesaikan tugas ini.

    Catatan pelajaran excel kali ini akan membahas bagaimana membuat dan menerapkan fungsi ambilAngka(), dimana fungsi ini berguna untuk men-ektrak porsi angka dari sebuah entri text.







    Screenshot berikut memperlihatkan bagaimana fungsi ambilAngka() bisa mengextract porsi angka dari entri data, tidak peduli bagaimana pola susunan karakter serta panjang data entri.

    Mengextrak Angka dari Text


    Selanjutnya mari kita simak baik-baik bagaimana menerapkan code VBA untuk membuat fungsi ambilAngka sehingga bisa diterapkan pada spreadsheet seperti gambar di atas.

    Contoh kode VBA untuk extract angka dari text.


    Berikut contoh kode VBA yang dapat digunakan untuk extrak porsi angka dari data entri.

    Function ambilAngka(txt As String) As String
    Dim i As Integer, iKarakter As String, Angka As String
    For i = 1 To Len(txt)
      iKarakter = Mid(txt, i, 1)
      If IsNumeric(iKarakter) Then
        Angka = Angka & iKarakter
      End If
    Next
    ambilAngka = Angka
    End Function


    Supaya code diatas dapat digunakan maka harus diketikan atau dicopy ke modul VBA. Jika pembaca sudah mengenal dasar – dasar VBA sebelumnya, tentunya bukan hal yang sulit bagi anda untuk segera mengcopy kan code di atas ke modul VBA.

    Bagi pembaca yang masih baru mengenal VBA tidak perlu khawatir. VBA itu sangat menyenangkan, apalagi jika kita bisa merasakan manfaatnya yang luar biasa dalam meningkatkan efisiensi dan efektifitas kerja menggunakan microsoft Excel.

    Baiklah mari kita lanjutkan. Bagaimana masuk ke modul VBA.

      • Untuk excel 2007 atau yang lebih baru, pastikan tab developer tersedia dan setting macro security enable. Demikian juga jika anda masih menggunakan excel 2003, pastikan macro security enable.
      • Untuk masuk ke module VBA, tekan shortcut ALT = F11 atau melalui ribbon dengan cara klik icon Visual Basic pada tab developer.

      cara menampilkan jendela visual basic

      • Pada jendela VBA, klik menu Insert → klik Module
      cara insert module vba


      • Langkah selanjutnya ketikan atau copy kode VBA di atas pada module seperti diperlihatkan dalam screenshot di bawah ini.
      cara copy code di modul vba

      • Setelah code diketik/ di copy ke modul VBA, maka fungsi ambilAngka() sudah tersedia dan siap digunakan.
      • Simpan file dengan extension .xlsm (Excel Macro - Enable Workbook) atau dengan extension .xlsb (Excel Binary Workbook) jika anda menggunakan excel 2007, 2010 atau versi yang lebih baru.

      Cara menggunakan fungsi ambilAngka()


      Gambaran cara menggunakan fungsi ambilAngka() sudah diperlihatkan pada bagian awal catatan ini., silahkan di scroll kembali ke bagian atas untuk melihat screenshot contoh penerapannya di excel.

      Cara penulisan rumusnya sangat sederhana. Yaitu:

      =ambilangka(entri)

      Misalnya kita menuliskan rumus sebagai berikut:

      =ambilangka("AB12cfgR44Db")

      Maka ouput dari rumus di atas adalah : "1244" yang merupakan porsi angka dari "AB12cfgR44Db"
      Karena data entri terletak dalam sel excel, maka rumus ambilAngka dapat dituliskan dengan menggunakan referensi sel:

      Misalnya:

      =ambilAngka(A1)

      Rumus ini berguna untuk mengambil porsi angka dari text data entri yang terletak pada sel A1.

      Demikian pembahasan singkat mengenai contoh kode macro / vba yang dapat digunakan untuk mengekstrak porsi angka dari entri text. Semoga bermanfaat.

      Salam.

      Artikel terkait:




      Sunday, October 21, 2018

      Konversi Angka Berformat Text Menjadi Angka Nyata

      Ketika bekerja dengan data di excel, kita mungkin pernah menemukan jenis data yang seolah-olah berupa angka, namun excel memperlakukannya tidak sebagai data numerik, melainkan sebagai text. Data jenis ini, karena sifatnya sebagai text, bila dikalkulasi menggunakan beberapa fungsi seperti fungsi SUM, maka nilainya akan dianggap sebagai Nol. Untuk menjadikan data tersebut dapat diolah dalam fungsi pengolah bilangan, maka angka berformat text terlebih dahulu harus dikonversi menjadi bilangan yang benar-benar bilangan. Catatan singkat ini akan menjelaskan bagaimana mengkonversi angka berformat text menjadi angka real.



      Mengenali Angka berformat text


      Bilangan berformat text sering kita jumpai pada data hasil import dari aplikasi lain. Data ini juga bisa diperoleh dari hasil rumus pengolah text (misal fungsi RIGHT, LEFT dan MID) yang mengambil porsi angka dari text tertentu.

      Screenhoot dibawah ini bisa membantu kita mengenali bilangan berformat text dengan lebih jelas.

      Cara membedakan angka dengan text


      Dapat kita lihat pada gambar di atas. Apabila format alignment dibiarkan sesuai default, maka semua data berjenis text akan rata kiri, sedangkan data berjenis angka atau numerik akan rata kanan.

      Selain itu, text bilangan yang diketik dengan menambahkan tanda petik satu di depannya, juga dapat dikenali dengan adanya tanda segitiga kecil berwarna hijau di pojok kiri atas sel berisi data tersebut.
      Perhatikan bilangan 1000 pada baris ke-2 dan ke-3. Data tersebut merupakan bilangan berformat text dan dapat dikenali dengan alignment rata kiri, serta adanya tanda segitiga kecil pada pojok kiri atas sel.

      Efeknya lebih lanjut: pada saat kita mencoba membuat rumus SUM menggunakan referensi range berisi campuran data jenis text dan bilangan (contoh : range A2:A5) maka data text akan dikesampingkan, sehingga rumus =SUM(A2:A5) akan menghasilkan nilai 2000, bukan 4000. Hasil rumus ini mungkin bukan yang kita harapkan sehingga perlu dicari solusinya.

      Cara Konversi Angka Berformat Text Menjadi Angka Nyata


      Supaya angka berformat text bisa diolah lebih lanjut dalam formula pengolah data numerik, maka terlebih dahulu kita harus mengkonversi angka berformat text tersebut menjadi angka nyata.

      Berikut akan kita ulas beberapa tehnik atau cara konversi angka berformat text menjadi angka nyata.

      Cara 1: Menggunakan Alat Pemeriksa Kesalahan


      Pada setiap sel yang berisi angka berformat text, khususnya yang diketik didahului tanda petik satu, kalau kita klik biasanya akan muncul pemeriksa kesalahan dengan logo tanda seru berlatar segi empat ketupat berwarna kuning. Jika tanda tersebut kita klik maka akan muncul beberapa menu terkait error pada sel tersebut. Kita bisa meng-klik Convert to Number untuk mengkonversi angka atau bilangan berformat text menjadi angka real.

      Cara menggunakan alat pemeriksa kesalahan excel


      Langkah-Langkah nya cukup sederhana yaitu sebagai berikut:

      Seleksi range yang yang akan dikonversi. Penting: sel pertama, yaitu baris dan kolom pertama range yang diseleksi, harus mengandung angka berformat text yang akan dikonversi. Sedangkan baris / kolom selanjutnya bebas.

      Pada saat seleksi range, tanda pemeriksa kesalahan akan muncul disamping sel pertama. Klik tanda tersebut sehingga muncul pilihan menu, kemudian klik Convert To Number.

      Cara pertama ini cukup efektif dapat merubah semua angka berformat text menjadi angka real  pada range yang diseleksi.  Namun ada sedikit kelemahannya yaitu ketika baris data yang akan dikonversi cukup banyak dan posisi sel pertama range yang diseleksi berada belum diketahui lokasinya. Hal ini biasanya agak menyulitkan untuk mencari sel pertama-nya.

      Cara 2: Menggunakan Copy Paste Special


      Fitur Copy Paste Special juga ternyata dapat digunakan untuk mengkonversi angka berformat text menjadi angka real. Hal inilah yang mungkin sedikit kurang disadari oleh kebanyakan pengguna excel.

      Berikut langkah-langkahnya:

      • Pilih sebuah sel kosong (blank) kemudian klik kanan → Copy.
      • Seleksi range yang akan dikovert yaitu range yang mengandung sel berisi data angka berformat text.
      • Kemudian klik kanan → paste special
      • Pada jendela paste special, tik opsi Values, kemudian tik opsi Add
      • Terakhir : klik OK


      Untuk lebih jelasnya, perhatikan langkah-langkah dalam gambar Gif berikut:

      konversi text menjadi angka dengan copy paste special


      Cara ke-3 : Menggunakan Fungsi VALUE dan Rumus





      Cara ke-3 ini efektif digunakan untuk mengkonversi text angka menjadi angka real dalam sebuah formula jika text angka yang dikonversi merupakan hasil dari sebuah formula pengolah text, misalnya fungsi MID, LEFT, dan RIGHT.

      Untuk lebih jelasnya, perhatikan gambar berikut:

      Hasil Formula RIGHT Text


      Sesuai screenshot di atas, kolom A berisi Kode, dan kolom B berisi 2 digit angka yang merupakan output dari sebuah fungsi RIGHT yang mengambil 2 karakter terakhir dari kode di kolom A.

      Secara visual, data di kolom B nampak sebagai bilangan atau angka, namun sebenarnya data tersebut merupakan text. Kita dapat mengenalinya dengan alignment default rata kiri. Selain itu kita juga dapat mengetesnya dengan membuat rumus SUM menggunakan referensi range B2:B5. Rumus SUM memberikan output 0 karena memang tidak ada angka nyata dalam referensi B2:B5.

      Supaya kolom B bisa berisi angka real yang dapat dikalkulasi lebih lanjut, maka kita bisa mengkonversinya menggunakan fungsi VALUE.

      Dalam hal ini, rumus  =RIGHT(A2,2) dapat dilengkapi dengan fungsi VALUE, sehinggar rumus menjadi =VALUE(RIGHT(A2,2))

      Silahkan perhatikan gambar berikut untuk lebih jelasnya:

      Fungsi VALUE konversi text menjadi angka


      Setelah dilewatkan pada fungsi VALUE, kita bisa lihat text angka di kolom B sudah dikonversi menjadi anaka real. Angka real dapat dikenali dengan alignment dafault rata kanan, serta fungsi SUM menghasilkan bilangan hasil penjumlahan sesuai harapan.

      Alternative Rumus:


      Selain menggunakan fungsi VALUE, kita juga bisa mengkonversi text angka dengan menggunakan rumus perkalian, penjumlahan, dan pengurangan. Lebih tepatnya, saya sebut Rumus Kali Satu, Rumus Tambah Nol dan Rumus Kurang Nol:

      Dengan melengkapi contoh rumus =RIGHT(A2,2) sesuai pembahasan di atas, maka kita bisa memodifikasinya untuk mendapatkan output angka real sebagai berikut:


      • Rumus Kali Satu: =RIGHT(A2,2)*1
      • Rumus Tambah Nol:   =RIGHT(A2,2)+0
      • Rumus Kurang Nol:   =RIGHT(A2,2)-0


      Eh, ternyata masih ada satu lagi rumus yang patut dicoba, yaitu menggunakan minus ganda atau double unary. Cukup menambahkan 2 tanda minus sebelum formula yang menghasilkan text angka.


      • Rumus Minus Ganda:  =--RIGHT(A2,2)


      Demikian pembahasan singkat mengenai bagaimana cara mengkonversi  bilangan atau angka berformat text menjadi angka nyata. Ada beberapa alternative yang sudah saya jelaskan di atas. Dalam prakteknya mungkin masih ada tehnik lain yang bisa dicoba. Apabila pembaca ada punya cara lainnya, saya sangat senang sekali apabila pembaca bisa berbagi dengan melengkapinya di kolom komentar.

      Salam.

      Sunday, September 9, 2018

      Mengisi Data Pada Beberapa Worksheet Sekaligus

      Pada aplikasi excel, kita bisa mengisi data yang sama ke dalam beberapa worksheet sekaligus dengan cara menggabungkan atau grouping worksheet-worksheet tersebut. Grouping worksheet dapat dilakukan secara manual maupun secara otomatis menggunakan kode VBA. Dengan memahami tehnik ini diharapkan dapat membantu kita untuk menghemat waktu ketika harus mengisi data yang sama pada beberapa worksheet.

      Grouping worksheet secara manual.


      Ikuti langkah-langkah berikut untuk grouping worksheet secara manual, serta untuk mengisi data dan edit format sekaligus pada beberapa worksheet:



      • Pada keyboard, tekan tombol Ctrl, kemudian dengan menggunakan mouse, klik tab worksheet yang akan di-group.



      Cara Grouping Worksheet


      • Lakukan isi data pada salah satu sheet yang di-group.
      • Silahkan lihat pada worsheet lainnya dalam group.
      • Maka kita bisa melihat semua sheet dalam group sudah terisi data yang sama.
      • Silahkan lakukan edit format pada salah satu sheet dalam grup dan kemudian lihat hasilnya pada masing-masing worksheet.
      • Maka semua sheet dalam grup akan memiliki format yang sama.
      • Untuk mengembalikan ke mode ungroup, silahkan klik salah satu tab sheet.


      Kelemahan Grouping Worksheet secara manual


      • Editing pada sebuah worksheet dalam group akan merubah worksheet lainnya dalam grup, tidak peduli dimana posisi sel yang diisi data atau di-edit. Padahal mungkin kita hanya perlu merubah atau mengisi data yang sama pada range sel tertentu saja.
      • Sangat riskan user lupa melakukan ungroup setelah melakukan isi data yang diperlukan, sehingga tidak menyadari apa yang diedit pada sebuah worksheet ternyata merubah worksheet lainnya. Padahal belum tentu diperlukan.


      Grouping worksheet secara otomatis.


      Sebagai solusi untuk meniadakan resiko yang tidak diinginkan atas grouping worksheet secara manual, maka disarankan untuk grouping worksheet secara otomatis. Dengan cara ini kita bisa menetapkan grouping worksheet ketika hanya perlu mengedit atau mengisi data pada range tertentu saja. Selain itu kita tidak perlu khawatir kelupaan melakukan ungroup,  karena excel akan mengerjakannya secara otomatis.

      Langkah-langkah grouping worksheet secara otomatis.

      Anggaplah kita ingin memasukan data yang sama ke dalam sheet1, sheet2 dan sheet 3 pada range B3:E10

      • Pastikan setting macro security sudah enable
      • Klik kanan pada salah satu worksheet yang akan di-grup, misalnya sheet1, kemudian klik View Code.


      Cara memunculkan jendela VBA

      • Maka kita akan di bawa ke jendela VBA, modul object Sheet1. Selanjutnya copy code berikut ke dala  modul VBA.


        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Intersect(Range("B3:E10"), Target) Is Nothing Then
          Me.Select
        Else
          Sheets(Array("sheet1", "sheet2", "sheet3")).Select
        End If
        End Sub



        Untuk lebih jelasnya bisa dilihat pada screenshot di bawah ini.
      Modul Worsheet Excel VBA


      • Lakukan hal yang sama pada sheet lainnya yang akan di grup (sheet2 dan sheet3) sehingga semua modul object sheet1, sheet2 dan sheet 3 sudah memiliki kode VBA seperti contoh di atas.
      • Sekarang saatnya untuk menguji hasilnya:
      • Silahkan seleksi salah satu sel dalam range B3:E10 pada salah satu sheet1, sheet2 atau sheet3.  Kemudian seleksi sembarang sel lainnya di luar range B3:E10 dan perhatikan perbedaan reaksi excel.
      • Ketika kita menyeleksi salah satu sel pada range B3:E10 maka otomatis sheet1, sheet2 dan sheet3 akan di-group. Sebaliknya ketika kita menyeleksi sel di luar range B3:E10 maka sheet1, sheet2 dan sheet3 akan di-ungroup.
      • Lakukan isi data atau edit format pada range B3:E10 dan bandingkan hasilnya dengan isi data / edit format pada range diluar B3:E10.
      • Maka data yang sama atau format yang sama hanya akan berlaku jika kita mengedit sel dalam range B3:B10 saja.
      • Hal ini tentu saja sangat berguna untuk memastikan isi data yang sama hanya pada range tertentu saja.



      Untuk menentukan sheet dan range spesifik mana yang diinginkan supaya terisi data yang sama, maka kita bisa memodifikasi code sesuai contoh di atas.

      Misalnya:
      Code macro berikut dapat digunakan untuk grouping secara otomatis sheet1, sheet2, sheet3 dan sheet4 jika posisi aktive cell terletak pada range A1:B10


      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Intersect(Range("A1:B10"), Target) Is Nothing Then
        Me.Select
      Else
        Sheets(Array("sheet1", "sheet2", "sheet3","sheet4")).Select
      End If
      End Sub


      Silahkan dicoba dengan cara mengcopy kode tersebut pada ke modul VBA object sheet1, sheet2, sheet3 dan sheet4 sesuai langkah-langkah yang sudah dijelaskan sebelumnya. Kemudian perhatikan hasilnya ketika kita mengedit data pada sel dalam range A1:B10 dan bandingkan dengan hasil edit data pada range lainnya.

      Demikian pembahasan singkat mengenai tips mengisi data sekaligus pada beberapa worksheet dengan cara grouping worksheet, baik secara manual maupun otomatis. Semoga bermanfaat.