Belajar Excel 2007, Data Validasi

A. Data Validation

Data Validation merupakan fitur Excel yang digunakan untuk membatasi masukan data atau formula. Dalam sel atau range yang divalidasi, pengguna hanya bisa memasukkan data yang diperbolehkan. Pengaturan validasi data dilakukan melalui kotak dialog Data Validation. Untuk menampilkan kotak dialog Data Validation klik tombol Data Validation yang terdapat dalam tab Data group Data Tools.

1. Tab Settings
Kotak dialog Data Validation mempunyai 3 tab yaitu Settings, Input Message dan Error Alert. Tab Settings dalam kotak dialog Data Validation mempunyai fungsi untuk melakukan pengaturan validasi data yang diinginkan. Pada kotak pilihan Allow: Anda dapat memilih opsi jenis data yang tersedia, yaitu Any Value (tanpa ada pembatasan), Whole number (data angka), Decimal (data desimal), List (daftar yang berasal dari referensi sel atau kotak dialog), Date (data tanggal), Time (data waktu/jam), Text length (data teks dengan jumlah huruf tertentu) dan Custom (data yang diatur dengan menggunakan formula).

Gambar Kotak dialog Data Validation – tab Input Settings.

Pada pilihan Ignore blank, Anda dapat mengatur apakah nilai kosong dalam sel yang divalidasi diperbolehkan atau tidak. Jika Anda memberi tanda contreng (v) pada pilihan tersebut, maka sel yang divalidasi boleh tidak diisi (dibiarkan kosong). Jika Anda memilih opsi List pada kotak pilihan Allow:, maka muncul pilihan In-cell dropdown. Beri tanda contreng (v) pada pilihan tersebut agar sel yang divalidasi menampilkan drop down daftar pilihan pada saat diseleksi.

Untuk pilihan selain List dan Custom, Anda dapat melakukan pengaturan lanjutan pada kotak pilihan Data:, yaitu between (data dengan kisaran nilai minimum sampai nilai maksimum yang ditentukan), not between (data di luar kisaran nilai minimum sampai nilai maksimum yang ditentukan), equal to (data harus sama dengan nilai yang ditentukan), not equal to (data harus tidak sama dengan nilai yang ditentukan), greater than (data dengan nilai lebih besar dari nilai yang ditentukan), less than (data dengan nilai lebih kecil dari nilai yang ditentukan), greater than or equal to (data dengan nilai lebih besar atau sama dengan nilai yang ditentukan) dan less than or equal to (data dengan nilai lebih kecil atau sama dengan nilai yang ditentukan).

2. Tab Input Message
Tab Input Message mempunyai fungsi untuk menambahkan informasi bagi pengguna tentang data apa yang boleh atau tidak boleh dimasukkan ke dalam sel yang divalidasi. Informasi secara default akan ditampilkan ketika pengguna menyeleksi sel yang divalidasi.

Gambar Kotak dialog Data Validation – tab Input Message.

Pilihan Show input message when cell is selected digunakan untuk mengatur apakah Input Message akan ditampilkan saat sel yang divalidasi dipilih. Beri tanda contreng (v) untuk menampilkan Input Message. Untuk memberikan judul Input Message, ketikkan judul yang Anda inginkan pada kotak isian Title: Anda dapat memasukkan informasi validasi yang akan ditampilkan pada kotak isian Input message: Tampilan Input Message
pada sel yang divalidasi terlihat seperti pada Gambar 2.20.

Gambar Tampilan Input Message pada sel yang divalidasi.

3. Tab Error Alert
Jika pengguna memasukkan data yang tidak diperbolehkan (tidak valid) ke dalam sel yang divalidasi, maka muncul kotak pesan error seperti terlihat pada Gambar 2.21.

Gambar Data yang dimasukkan tidak valid.

Anda dapat mengatur tampilan kotak pesan error agar lebih sesuai dengan validasi data. Pengaturan kotak pesan error validasi data dilakukan melalui tab Error Alert. Beri tanda contreng (v) pada pilihan Show error alert after invalid data is entered untuk menampilkan kotak pesan peringatan ketika pengguna memasukkan data yang tidak valid.

Gambar Kotak dialog Data Validation – tab Error Alert.

Pada kotak pilihan Style: Anda dapat memilih tampilan ikon kotak pesan error. Selain menampilkan ikon kotak pesan error, pilihan ini juga akan berpengaruh pada tindakan selanjutnya yang dapat dilakukan pengguna. Jika Anda memilih style Warning dan Information, maka data yang tidak valid masih dapat dimasukkan dengan memilih tombol Yes pada style Warning dan tombol OK pada style Information. Jika Anda memilih style Stop, maka data yang tidak valid tetap tidak dapat dimasukkan.


B. Membuat Nama Sel atau Range

Nama sel atau range akan sangat membantu kita pada saat menggunakan Form Controls, misalnya untuk memasukkan daftar pilihan dalam List Box. Sebelum memberi nama range, Anda sebaiknya mengetahui beberapa aturan pemberian nama sel/range sebagai berikut:

1. Karakter yang boleh digunakan dalam nama sel atau nama range adalah tanda underscore (_) dan titik (.).
2. Nama sel atau nama range harus dimulai dari huruf atau tanda underscore (_), bukan angka atau karakter lainnya.
3. Nama sel atau nama range yang terdiri dari dua kata atau lebih tidak boleh dipisahkan dengan spasi. Anda dapat menggunakan tanda underscore (_) atau titik (.) untuk menggantikan spasi. Misalnya, nama sel Harga Barang depat Anda ganti dengan Harga_Barang atau Harga.Barang.
4. Dalam satu workbook, nama sel atau nama range harus bersifat unik (tidak ada nama sel atau nama range yang sama dalam satu workbook). Nama range dapat dibuat dengan tiga cara yaitu melalui Name Box pada Formula Bar, kotak dialog Create Names from Selection dan kotak dialog New Name.
   

1. Name Box
Cara termudah untuk membuat nama sel atau nama range adalah melalui Name Box pada Formula Bar. Sebelum membuat nama sel atau nama range melalui Name Box pastikan Formula Bar sudah ditampilkan.

1. Untuk menampilkan Formula Bar klik Office Button kemudian tekan tombol Excel Options. Muncul kotak dialog Excel Options.
2. Pilih opsi Costumize. Beri tanda contreng (v) pada pilihan Show formula bar kemudian klik tombol OK.Gambar Kotak dialog Excel Options.
3. Buka file Membuat Nama Sel atau Range.xlsx
4. Untuk membuat nama sel diskon sandal, tempatkan pointer pada sel B3. Arahkan kursor mouse pada Name Box kemudian ketikkan Diskon. Tekan tombol Enter pada keyboard.Gambar Membuat nama sel melalui Name Box.
5. Untuk membuat nama range B5:B11 yang berisi data harga sandal, blok range B5:B11. Arahkan kursor mouse pada Name Box kemudian ketikkan Harga. Tekan tombol Enter pada keyboard.Gambar Membuat nama range melalui Name Box.
   

2. Kotak dialog Create Names form Selectio.
Kotak dialog Create Names form Selection digunakan untuk membuat nama pada range yang diseleksi. Nama range yang digunakan adalah data dalam sel pada baris teratas (top row), baris terbawah (bottom row), kolom paling kiri (left column) atau kolom paling kanan (right column) dari range yang diseleksi. Jika data dalam sel yang akan digunakan sebagai nama range mempunyai spasi, maka spasi tersebut secara otomatis akan diganti dengan tanda underscore (_). Dalam contoh kali ini kita akan membuat nama range penjualan sandal.

1. Pastikan workbook Membuat Nama Sel atau Range.xlsx masih terbuka. Blok range C4:C11 yang berisi data penjualan.
2. Klik tombol Create from Selection yang terdapat dalam tab Formulas group Defined Names. Muncul kotak dialog Create Names from Selection. Kotak dialog Create Names from Selection juga dapat ditampilkan dengan menekan kombinasi tombol Ctrl+Shift+F3 secarabersamaan.
   
   Gambar Kotak dialog Create Names from Selection.
3. Beri tanda contreng (v) pada pilihan Top row untuk menggunakan data dalam sel baris paling atas, yaitu Penjualan, sebagai nama range. Klik tombol OK.

3. Kotak dialog New Name
Kotak dialog yang digunakan untuk membuat nama range dalam Excel 2007 adalah New Name. Salah satu kelebihan kotak dialog New Name adalah kita dapat mengatur cakupan (scope) nama range yang akan dibuat. Jika Anda memilih Workbook, maka nama range dapat digunakan untuk seluruh worksheet. Jika Anda memilih salah satu worksheet untuk membuat nama range, maka untuk menggunakan nama range pada worksheet yang berbeda Anda harus menuliskan nama worksheet diikuti tanda seru!. Misalnya, Anda membuat nama range Pendapatan pada worksheet Sheet1. Untuk menghitung jumlah pada worksheet selain Sheet1, maka formula yang harus Anda ketikkan adalah =Sum(Sheet1!Pendapatan).

1. Pastikan workbook Membuat Nama Sel atau Range.xlsx masih terbuka. Klik tombol Define Name yang terdapat dalam tab Fomulas group Defined Nam es kemudian muncul kotak dialog New Name.
   
   Gambar Kotak dialog New Name.
2. Untuk membuat nama range pendapatan, ketikkan Pendapatan pada kotak isian Name:
3. Pada kotak isian Scope: pilih Workbook, yang berarti nama range Pendapatan dapat digunakan untuk seluruh worksheet.
4. Ketikkan =Sheet1!$D$5:$D$11 pada kotak isian Refers to: Dalam contoh tersebut, range yang dibuat adalah range D5:D11 pada worksheet Sheet1. Langkah tersebut juga dapat dilakukan dengan cara klik tombol pada kotak sebelah kanan Refers to:. Muncul
   collapse dialog New Name – Refers to:
   Gambar Collapse dialogs New Name – Refers to:
5. Blok range D5:D11 (hasil blok ditandai dengan garis putus-putus). Jika sudah, klik tombol hingga Muncul kembali kotak dialog New Name kemudian klik tombol OK.
6. Untuk membuat nama sel Jumlah_Pendapatan klik tombol Define Name yang terdapat dalam tab Fomulas group Defined Names. Muncul kotak dialog New Name.
7. Ketikkan Jumlah_Pendapatan pada kotak isian Name: Pada kotak
   isian Scope: pilih Workbook. Ketikkan =Sheet1!$D$12 pada kotak
   isian Refers to: Dalam contoh tersebut, sel yang dibuat adalah sel D12
   pada worksheet Sheet1. Jika sudah, klik tombol OK.
   
   Gambar Membuat nama sel Jumlah_Pendapatan.

4. Edit Nama Range
Nama sel atau nama range yang telah Anda buat tidaklah bersifat baku.Anda dapat mengubah nama sel atau nama range sesuai dengan kebutuhan. Edit nama sel atau nama range dapat dilakukan melalui kotak dialog Nama Manager.

1. Klik tombol Name Manager yang terdapat dalam tab Fomulas group Defined Names kemudian muncul kotak dialog Name Manager.
   Gambar Kotak dialog Name Manager.
2. Pilih nama sel atau nama range yang akan diedit. Klik tombol Edit... kemudian muncul kotak dialog Edit Name.
   Gambar Kotak dialog Edit Name.
3. Cara mengedit nama sel atau nama range pada prinsipnya sama dengan cara membuat nama sel atau nama range. Namun demikian, kita tidak dapat mengedit Scope (cakupan) nama sel atau nama range. Lakukan pengeditan yang diperlukan kemudian klik tombol OK.
   

5. Menghapus Nama Range
Apabila Anda sudah tidak membutuhkan nama sel atau nama range, Anda dapat menghapusnya melalui kotak dialog Name Manager. Apabila dalam workbok masih terdapat formula atau fungsi yang secara langsung ataupun tidak langsung menggunakan nama sel atau nama range, maka
perhitungan tersebut akan menghasilkan error #REF! Oleh karena itu, pastikan nama sel atau nama range yang akan dihapus memang sudah benar-benar tidak dibutuhkan.

1. Untuk menghapus nama sel atau nama range klik tombol Name Manager yang terdapat dalam tab Fomulas group Defined Names kemudian tekan muncul kotak dialog Name Manager.
2. Pilih nama sel atau nama range yang akan dihapus. Jika sudah, klik tombol Delete. Muncul kotak pesan
3. Klik tombol OK untuk menghapus nama sel atau nama range. Jika
   Anda berniat membatalkan, klik tombol Cancel.

6. Membuat Nama Range Dinamis
Anda dapat menggunakan kombinasi fungsi OFFSET dan fungsi COUNTA untuk membuat nama range dinamis. Kelebihan nama range dinamis adalah range secara otomatis akan selalu menyesuaikan dengan data yang baru dimasukkan. Dengan demikian, Anda tidak perlu melakukan penyesuaian nama range secara manual. Dalam contoh kali ini, kita akan menggunakan nama range dinamis pada fungsi VLOOKUP.

Untuk melengkapi range H2:H6 yang masih kosong, lakukan langkah-langkah sebagai berikut:

• Ketikkan nama salah satu salesman ke dalam sel H2, misalnya ketikkan Ningsih Utami.
• Ketikkan formula =VLOOKUP(H2;A3:E12;2;FALSE) pada sel H3 untuk memasukkan NIK salesman kemudian tekan tombol Enter.
• Ketikkan formula =VLOOKUP(H2;A3:E12;3;FALSE) pada sel H4 untuk memasukkan tanggal lahir salesman kemudian tekan tombol Enter.
• Ketikkan formula =VLOOKUP(H2;A3:E12;4;FALSE) pada sel H5 untuk memasukkan tinggi badan salesman kemudian tekan tombol Enter.
• Ketikkan formula =VLOOKUP(H2;A3:E12;5;FALSE) pada sel H6 untuk memasukkan berat badan salesman kemudian tekan tombol Enter.

Untuk memasukkan data baru ke dalam tabel, lakukan langkahlangkah sebagai berikut:

• Ketikkan Ahmad Irawan ke dalam sel A13.
• Ketikkan CD012602 ke dalam sel B13.
• Ketikkan 22/01/1985 ke dalam sel C13.
• Ketikkan 166 ke dalam sel D13.
• Ketikkan 55 ke dalam sel E13.

Ketikkan Ahmad Irawan (nama salesman yang baru diinput) ke dalam sel H2. Range H3:H6 kemudian akan menampilkan nilai error #N/A karena fungsi VLOOKUP belum disesuaikan dengan masukkan record data baru.

Gambar Fungsi VLOOKUP belum disesuaikan.

Klik tombol Define Name yang terdapat dalam tab Formulas group
Defined Names. Muncul kotak dialog New Name.

Gambar Membuat nama range dinamis.

Ketikkan Tabel_Salesman pada kotak isian Name: Pilih Workbook pada kotak pilihan Scope: Pada kotak isian Refers to: ketikkan formula =OFFSET(Sheet1!$A$2;1;0;COUNTA(Sheet1!$A:$A)-2;5) kemudian klik tombol OK.

Berikut penjelasan formula yang digunakan:

• OFFSET merupakan fungsi yang akan menyalin hasil yang ada dalam suatu range dengan jumlah baris dan kolom yang disebutkan jaraknya dari sel sumber.
• Sheet1!$A$2 merupakan sel sumber yang dijadikan dasar dalam penggunaan fungsi OFFSET.
• 1 menunjukkan arah baris ke bawah (positif). Karena nilainya 1, maka sel yang ditunjuk adalah sel 1 baris di bawah sel yang dijadikan dasar dalam penggunaan fungsi OFFSET.
• 0 merupakan arah kolom. Karena nilainya 0, maka sel yang ditunjuk terletak dalam kolom yang sama dengan sel yang dijadikan dasar dalam penggunaan fungsi OFFSET.
• COUNTA(Sheet1!$A:$A)-2 merupakan tinggi range yang diwakili jumlah baris pada range dalam kolom A yang tidak kosong. Karena ada sel yang tidak digunakan dalam range (sel A1 dan A2) maka tinggi range dikurangi 2.
• 5 merupakan lebar range yang diwakili jumlah kolom pada range.

Ubah formula pada range H3:H6 dengan memasukkan nama range Tabel_Salesman dalam formula:

• Ketikkan =VLOOKUP(H2;Tabel_Salesman;2;FALSE) pada sel H3 kemudian tekan tombol Enter.
• Ketikkan =VLOOKUP(H2;Tabel_Salesman;3;FALSE) pada sel H4 kemudian tekan tombol Enter.
• Ketikkan =VLOOKUP(H2;Tabel_Salesman;4;FALSE) pada sel H5 kemudian tekan tombol Enter.
• Ketikkan =VLOOKUP(H2;Tabel_Salesman;5;FALSE) pada sel H6 kemudian tekan tombol Enter.

Untuk menguji hasilnya masukkan data baru ke dalam tabel, dengan langkah-langkah sebagai berikut:

• Ketikkan Budi Dewanto ke dalam sel A14.
• Ketikkan CD012618 ke dalam sel B14.
• Ketikkan 13/07/1985 ke dalam sel C14.
• Ketikkan 163 ke dalam sel D14.
• Ketikkan 53 ke dalam sel E14.

Ketikkan Budi Dewanto ke dalam sel H2. Jika semua berjalan dengan benar, maka range H3:H6 secara otomatis akan menampilkan detail biodata Budi Dewanto karena fungsi VLOOKUP secara otomatis akan menyesuaikan dengan data yang baru dimasukkan.

Gambar Fungsi VLOOKUP menyesuaikan data baru.

C. Conditional Formatting

Conditional Formatting merupakan fitur Excel yang digunakan untuk memberi format sel pada data dengan kriteria tertentu. Sebagian besar pembahasan dalam buku ini menggunakan formula sebagai kriteria dalam Conditional Formatting. Penggunaan formula sebagai kriteria dilakukan karena kita dapat mengeksplorasi fitur Conditional Formatting dengan lebih optimal. Dalam contoh berikut kita akan menggunakan formula dalam Conditional Formatting untuk mendeteksi adanya sel dengan nilai error.

1. Buat workbook baru (Tekan Ctrl+N) kemudian simpan dengan nama Conditional Formatting.xlsx.
2. Ketikkan Yudhy Wicaksono pada sel A1 dan 10 pada sel A2. Pada sel A3 ketikkan formula =A1*A2 sehingga perhitungan akan menghasilkan nilai error #Value!.
3. Blok range A1:A3. Klik tombol Conditional Formatting yang terdapat dalam tab Home group Styles kemudian pilih menu New Rule… Muncul kotak dialog New Formatting Rule.
4. Pilih Use a formula to determine which cells to format pada daftar pilihan Select a Rule Type:
5. Ketikkan formula =ISERROR(A1) pada kotak isian Format values where this formula is true:
6. Klik tombol Format... kemudian muncul kotak dialog Format Cells. Atur format sel yang akan digunakan dalam Conditional Formatting. Jika sudah, klik tombol OK.
   
   Gambar Conditional Formatting untuk mendeteksi nilai error.
7. Muncul kembali kotak dialog New Formatting Rule kemudian klik tombol OK. Sel A3 dengan nilai error kemudian ditampilkan sesuai format yang Anda tentukan dalam Conditional Formatting.
8. 
   Gambar Conditional Formatting formula error.

Anda dapat melakukan manajemen Conditional Formatting melalui kotak dialog Conditional Formatting Rules Manager. Untuk menampilkan kotak dialog Conditional Formatting Rules Manager, klik tombol Conditional Formatting yang terdapat dalam tab Home group Styles kemudian pilih menu Manage Rules…

Gambar Kotak dialog Conditional Formatting Rules Manager.

1. Pilih lokasi Conditional Formatting pada kotak pilihan Show formatting rules for:
2. Untuk membuat Conditional Formatting baru, klik tombol New Rule... Muncul kotak dialog New Formatting Rule. Buat kriteria dan format Conditional Formatting yang diinginkan kemudian klik tombol OK.
3. Untuk mengedit Conditional Formatting yang sudah ada, pilih Conditional Formatting yang akan diedit kemudian klik tombol Edit Rule... Muncul kotak dialog Edit Formatting Rule. Edit kriteria dan format Conditional Formatting yang diinginkan kemudian klik tombol OK.
4. Untuk menghapus Conditional Formatting, pilih Conditional Formatting yang akan dihapus kemudian klik tombol Delete Rule.
5. Klik tombol atau untuk memindahkan posisi Conditional Formatting. Pengaturan posisi ini sangat penting terutama jika dalam suatu range digunakan Conditional Formatting lebih dari satu.