Mencari Nilai dengan Fungsi Index #Bag. 2

Artikel kali ini merupakan lanjutan dari artikel sebelumnya yaitu “Mencari Nilai dengan Fungsi Index #Bag. 1“, pada artikel sebelumnya anda harus melakukan secara manual ketika akan mencari nilai pada suatu cel dengan menggunakan fungsi index. Perhatikan kembali screenshoot dibawah ini :

Sebagai contoh untuk mencari jumlah pendaftar prodi kehutanan – S1 pada tahun 2008 maka anda harus melakukan penentuan index kolom dan index baris secara manual.

Pembuatan formula index apabila penentuan index kolom dan baris dilakukan secara manual sungguh merepotkan apabila data tersebar dalam spreedsheet yang begitu banyak, nah pada artikel kali ini akan dibuat pengisian cell untuk mencari jumlah pendaftar menggunakan menu, sehingga pengguna tinggal memilih nama jurusan dan tahun pendaftaran maka otomatis akan ditampilkan hasil pencarian data dalam cell. Perhatikan kedua gambar dibawah ini !

Dari kedua gambar diatas untuk mencari jumlah pendaftar anda tinggal memilih program studi – jenjang serta tahun pada drop down yang disediakan. Nah pada aritikel kali ini kita akan memodifikasi artikel sebelumnya menjadi seperti tampak pada kedua gamabr diatas.

Berikut langkah-langkah pengerjaan :

1. Blok cell C7 sampai H11 (C7:H11) dan beri nama data dengan menggunakan fasilitas insert name define.

2. Buat tampilan seperti pada gambar ke-3 (ke-4)

3. Blok cell B7 sampai B11 (B7:B11) dan beri nama prodi dengan menggunakan fasilitas insert name define.

4. Blok cell C6 sampai H6 (C6:H6) dan beri nama tahun dengan menggunakan fasilitas insert name define.

5. Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K7 untuk membuat drop down jurusan, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =prodi, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

6. Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K8 untuk membuat drop down tahun, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =tahun, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

7. Simpan kursor di K9 untuk mencari jumlah pendaftar, dan ketik formula berikut :

=IF(OR(ISBLANK(K7),ISBLANK(K8)),””,INDEX(data,IF(ISERROR(MATCH(K7,prodi,0)),””,MATCH(K7,prodi,0)),IF(ISERROR(MATCH(K8,tahun,0)),””,MATCH(K8,tahun,0))))

Untuk bahan latihan silahkan download filenya :

DOWNLOAD

Membatasi Tipe Data Pada Cell

Microsoft Excel secara garis besar membagi data yang bisa diinput kedalam cell menjadi empat jenis data yaitu text/string, numerc, date dan time. Dan secara default pula cell bisa menerima seluruh tipe data sehingga kita bisa meninput apapun kedalam sebuah cell, misal pada cell A1 anda bisa mengisi tanggal, nama, jam atau bahkan jumlah uang.

Namun adakalanya kita menginginkan agar file excell yang kita buat hanya bisa disi tipe data tertentu saja pada suatu range area cell. Untuk jelasnya perhatikan gambar berikut :

Dari gambar diatas tampak formulir pendataan Kolom nomor pendaftar yang hanya boleh berisi angka, nama pendaftar boleh diisi karakter apapun, tanggal lahir hanya boleh berisi tanggal dari tanggal 01 Jan 1995 s.d 31 Des 1998, dan nila rata-rata Ujian Nasional yang hanya boleh berisi angka decimal dengan rentang 0,00 s.d 10,00

Untuk Jelasnya perhatikan lembar kerja diatas yang merupakan lembar kerja pendataan pendaftar siswa baru pada suatu sekolah. Dari lembar kerja diatas berdasarkan ketentuan pada penjelasan sebelumnya bahwa kolom Nomor pendaftaran hanya boleh diisi angka dengan jangakauan antara 12001 sampai dengan 13000, maka cara pengerjaannya adalah :

Pengaturan Kolom No. Daftar

Simpan pointer di cell B7

Klik ribbon Data kemudian tombol Data Validation

Pada Tab setting tentukan aturan seperti tampak pada gambar dibawah ini !

Kemudian pada tab Error Alert isi seperti tampak pada gambar dibawah ini !

Lakukan copy cell B7 ke B8 sampai B16
Isi dengan data angka antara 12001 sampai 13000, dan jika diisi diluar jangkauan tersebut akan keluar pesan error seperti berikut :

Pengaturan Kolom Tanggal Lahir

Berikutnya kita buat aturan agar kolom tanggal lahir hanya boleh diisi dengan tanggal dengan jangkauan antara 01 Jan 1995 sampai dengan 31 Des 1998, jika tanggal lahir diisi dengan tanggal di luar jangkauan tersebut atau datanya bukan berupa tanggal maka akan tampak pesan error seperti berikut :

Adapun proses pengerjaanya adalah :

Simpan pointer di cell D7

Buka kotak dialog data validation (perhatikan gambar), tentukan seting seperti pada gambar dibawah ini :

Kemudian klik tombol Error Alert dan isi setting seperti tampak pada gambar dibawah ini :

Lakukan copy cell D7 ke cell D8 sampai D16, dan coba lakukan input data.

Pengaturan Kolom Rata-rata Nilai UN

Simpan pointer di cell E7

Buka kotak dialog Data Validation dan pada tab seting isi seperti tampak pada gambar dibawah ini :

Berikutnya pada tab error alert isi seperti tampak pada gambar dibawah ini :

Klik Ok untuk mengakhiri

Copy cell E7 ke cell E8 sampai E16, dan lakukan pengujian, jika nilai yang diinput tidak berada pada jangkauan 0.00 sampai 10.00 maka akan keluar sepert pada gambar berikut :

Data Validation # Kasus Error Warning

Bagi anda yang terbiasa membuat format data isian di Excel artikel ini mungkin akan sangat berguna untuk menjaga agar user tidak salah memasukan nilai kedalam cell yang telah ditentukan, untuk mudahnya penulis memberikan contoh sederhana sebagai berikut :

Dari gambar diatas adalah contoh pengisian nilai hasi tes masuk pada suatu sekolah, dimana area berwarna hijau merupakan data dari bagian pendaftaran, sedangkan area kuning merukapakan area yang harus diisi oleh bagian tes masuk untuk mengisi nilai tes masuk.

Dari gambar diatas, area warna kuning sangat mungkin sekali diisi data apa saja baik huruf (a-z), tanda baca, angka, gabungan angka dan huruf bahkan gabungan karakter apapun, padahal panitia bagian tes masuk hanya diperbolehkan mengisi nilai angka yang diberi kisaran antara 1 sampai 100, jadi jika panitia bagian tes masuk mengisi selain angka 1 sampai 100 maka akan dikeluarkan error message dan dipaksa untuk mengisi angka 1 sampai 100, dengan cara ini maka dapat kita peroleh secara konsisten, inilah yang dimaksud dengan Data Validation dengan contoh kasus Error Warning

Untuk memulai membuat data validation rule, siapkan terlebih dahulu data yang akan dibuat, sebagai contoh silahkan anda download file latihan disini.

1. Blok area cell yang akan anda berikan rule validasi nya, dalam gambar contoh diatas adalah area warna kuning (perhatikan gambar dibawah )

2. Berikutnya pilih Menu Data –> Data Valdation –> Data Validation …, maka akan tampak kotak dialog data validation

3. Karena kasus ini akan mem-validasi input nilai antara 1 sampai dengan 100 maka pada Drop Down Allow Pilih Whole Number dan ceklis pilihan Ignore Blank jika anda mengizinkan cell warna kuning untuk dikosongkan. Pada bagian minimum isi dengan =1 (nilai minimal) dan pada bagian maximum isi dengan =100 (nilai maksimum).

4. Berikutnya klik tab Input Message, maka akan akan tampak kotak dialog seperti tampak pada gambar dibawah

5. Dari gambar diatas, cek list Show input message when cell is selected, untuk mengaktifkan pesan aturan input saat cell isian data dipilih, kemudian bagian Title Isi dengan judul pesan, dan pada bagian Input Message isi dengan pesan input.

6. Langkah terakhir anda klik tab Error Alert maka akan tampak pengisian error message seperti pada tampak gambar dibawah

7. Dari gambar diatas pada kotak Style pilih Stop artinya jika cell yang telah diberi aturan dipaksa diisi diluar aturan yang ditetapkan maka akan dihentikan dan dipaksa untuk dibatalkan, Kemudian pada bagian Title Isi dengan judul pesan kesalahan, dan pada Error Message isi pesan kesalahan yang akan disampaikan apa bila user salah mengisi data.

8. Klik Ok dan siap digunakan, dan hasilnya tampak seperti berikut :

TESTING
Sebagai percobaan, coba anda input pada area kuning dengan angka 1 sampai 100, bagaimana hasilnya ?? bisa masuk ?? ya pasti bisa , sekarang anda coba masukan karakter selain angka misal huruf atau tanda baca , bagai mana hasilnya ?? jika tampil seperti berikut :

berarti rule anda berhasil dibuat, bahkan saat anda mengetikan angka 0 atau -1 bahkan 101 pun anda akan dihadapkan pada error message dan dipaksa memasukan angka 1 sampai dengan 100 … dengan rule validation ini dijamin data anda akan konsisten.