Tips Microsoft Excel: Membuat Detail Data Pencarian atas Database Excel

Tulisan ini saya persembahkan buat temen-temen yang masih ingin belajar tentang Fungsi IF, VLOOKUP, LOOKUP, dan TEXT.

Karena Fungsi tersebut bisa kita aplikasikan dalam pekerjaan sehari-hari untuk membantu meringankan pekerjaan kita.

Alhamdulillah tulisan ini atau postingan kali ini terbilang istimewa buat saya pribadi karena ide itu datang ketika tulisan pertama sebelumnya sudah beres yaitu yang berjudul Tips Microsoft Excel : Membuat Kriteria (Menu) Pencarian Data atas Database Excel.

Intinya sama dengan tulisan pertama tapi ada perbedaannya atau bisa dibilang ini adalah pengembangannya. Jadi selain ada kriteria pencarian yang tersedia di Menu Pencarian kita diberikan sebuah pilihan untuk melihat data tersebut secara Detail ya atau tidak.

Ok biar tidak membingungkan perhatikan gambar1 dibawah ini :

gambar1

Dan sekarang perhatikan gambar2 dibawah ini :

gambar2

Terlihat pada gambar1, ketika pilihan Detail Data memilih Tidak maka dibawah tidak ada detail data dimaksud, tapi ketika pilihan data tersebut kita pilih Ya maka hasilnya semua data tentang siswa tersebut muncul sesuai database.

Penasaran dan ingin lanjut atau malas silakan saja buka dan baca pastinya postingan dibawah ini karena nantinya akan berhubungan satu sama lain :

• Tips Microsoft Excel : Membuat Data Base Sederhana Dan Tabel Pencarian Data
• Tips Microsoft Excel : Tips Membuat Combo Box
• Tips Microsoft Excel : Mengunci Kolom atau Baris di Worksheet Excel
• Tips Microsoft Excel : Membuat Kriteria (Menu) Pencarian Data atas Database Excel

Jika sudah faham sebelumnya atau emang sudah terlanjur penasaran, ya sudah mari kita lanjutkan…..

Sekarang misalkan kita mempunyai database seperti gambar3 atau kita beri nama DB berikut ini :

gambar3 (DB)

Dari data tersebut kita ingin ada sebuah menu pencarian (gambar1 dan 2) atas data tersebut dan pastinya ada kriteria pencarian ditambah detail dari data tersebut.

Sekarang yang perlu dilakukan adalah membuat menu Menu Pencarian seperti pada gambar1, dan untuk memulainya adalah Tulisan Menu Pencarian ada di Cell D6, atau perhatikan gambar4 dibawah ini supaya heunteu lieurrr alias ga bingung.

gambar4

Sudah mengerti kan di Cell mana untuk membuat Kriteria Pencarian, Hasil pencarian dan yang lainnya.

Selanjutnya ada membuat List atau Dropdown untuk membuat criteria pilihan Nama atau NIS dan untuk memahaminya silakan kamu buka tulisan saya yang berjudul :

• Tips Microsoft Excel : Tips Membuat Combo Box

Selanjutnya adalah membuat rumus untuk menampilkan hasil percarian yang disesuaikan dengan criteria pencariannya.

Sekarang coba perhatikan Cell G9 (gambar4) : dimana di cell tersebut akan muncul Nama atau Nis dari data yang kita cari, jadi rumusnya adalah :

=IF($E$8=”Nama”,LOOKUP($G$8,DB!$C$4:$C$10,DB!$B$4:$B$10),
VLOOKUP($G$8,DB!$B$4:$H$10,2,FALSE))

Lalu bagaimana untuk membuat Pilihan Detail Ya atau Tidak, caranya sama dengan membuat list atau dropdown.

Memunculkan Kata-Kata Secara Otomatis

Cara untuk memunculkan detail dari data dimaksud ketika kita memilih pilihan detail data Ya.

• Sekarang coba perhatikan ketika Pilihan Detail kita pilih Ya maka akan muncul garis secara otomatis (Cell D12), dan rumusnya adalah

=IF($G$10=”Ya”,”—————————————————————“,”")

• Rumus untuk memunculkan kata Detail Data Siswa (Cell D13) adalah :

=IF($G$10=”Ya”,”Detail Data Siswa :“,”")

• Rumus untuk memunculkan kata Nama (Cell D14) adalah :

=IF($G$10=”Ya”,”Nama“,”")

• Rumus untuk memunculkan kata NIS (Cell D15) adalah :

=IF($G$10=”Ya”,”NIS“,”")

• Rumus untuk memunculkan kata Tempat Lahir (Cell D16) adalah :

=IF($G$10=”Ya”,”Tempat Lahir“,”")

• Rumus untuk memunculkan kata Tanggal Lahir (Cell D17) adalah :

=IF($G$10=”Ya”,”Tanggal Lahir“,”")

• Rumus untuk memunculkan kata Jenis Kelamin (Cell D18) adalah :

=IF($G$10=”Ya”,”Jenis Kelamin“,”")

• Rumus untuk memunculkan kata Wali Murid (Cell D19) adalah :

=IF($G$10=”Ya”,”Wali Murid“,”")

• Rumus untuk memunculkan kata Status Pekerjaan (Cell D20) adalah :

=IF($G$10=”Ya”,”Status Pekerjaan“,”")

• Rumus memunculkan garis (Cell D21), adalah :

=IF($G$10=”Ya”,”—————————————————————“,”")

• Untuk membuat titik dua (:) yang berada di Cell F14 dan muncul secara otomatis maka rumusnya adalah :

=IF($G$10=”Ya”,”:“,”")

Selanjutnya tinggal copy paste sesuai urutan data yang akan muncul.

Memunculkan Data Pencarian

Untuk memunculkan Nama yang dimaksud sesuai pilihan yang kita buat maka rumusnya adalah sebagai berikut :

• Untuk memunculkan Data Nama Siswa (Cell G14)

=IF($G$10=”Tidak”,”",IF($E$8=”Nama”,$G$8,$G$9))

• Untuk memunculkan Data NIS Siswa (Cell G15)

=IF($G$10=”Tidak”,”",IF($E$8=”Nama”,
VLOOKUP($G$8,DB!$B$4:$H$10,2,FALSE),VLOOKUP($G$9,DB!$B$4:$H$10,2,FALSE)))

• Untuk memunculkan Data Tempat Lahir (Cell G16)

=IF($G$10=”Tidak”,”",IF($E$8=”Nama”,
VLOOKUP($G$8,DB!$B$4:$H$10,3,FALSE),VLOOKUP($G$9,DB!$B$4:$H$10,3,FALSE)))

• Untuk memunculkan Data Tanggal Lahir (Cell G17)

=IF($G$10=”Tidak”,”",IF($E$8=”Nama”,TEXT(VLOOKUP($G$8,DB!$B$4:$H$10,4,FALSE),”dd-mmm-yy”),
TEXT(VLOOKUP($G$9,DB!$B$4:$H$10,4,FALSE),”dd-mmm-yy”)))

• Untuk memunculkan Data Jenis Kelamin (Cell G18)

=IF($G$10=”Tidak”,”",IF($E$8=”Nama”,
VLOOKUP($G$8,DB!$B$4:$H$10,5,FALSE),VLOOKUP($G$9,DB!$B$4:$H$10,5,FALSE)))

• Untuk memunculkan Data Wali Murid (Cell G19)

=IF($G$10=”Tidak”,”",IF($E$8=”Nama”,
VLOOKUP($G$8,DB!$B$4:$H$10,6,FALSE),VLOOKUP($G$9,DB!$B$4:$H$10,6,FALSE)))

• Untuk memunculkan Data Status Pekerjaan (Cell G20)

=IF($G$10=”Tidak”,”",IF($E$8=”Nama”,
VLOOKUP($G$8,DB!$B$4:$H$10,7,FALSE),VLOOKUP($G$9,DB!$B$4:$H$10,7,FALSE)))

Nah jika itu benar kamu lakuin oke sekarang sekarang kamu test aplikasinya dengan criteria Nama dan kamu tulis nama Andika Pratama dan jangan lupa pilihan Detail datanya kamu pilih Ya dan hasilnya adalah seperti pada gambar5 dibawah ini :

gambar5

Namun dalam pengetesan aplikasi ini, penulis menemukan bahwa FUNGSI LOOKUP terkadang tetap membaca hasil atau memunculkan Hasil Pencarian sebuah data walau nama yang diketikkan tersebut nantinya berbeda dengan Detail Pencarian, sekarang perhatikan gambar6 dibawah ini :

Ketika kita memasukan pilihan nama dengan sebuah nama yang tidak ada didatabase, FUNGSI LOOKUP tetap membaca seolah-olah nama tersebut ada sehingga akan muncul di Hasil Pencarian (Cell G9) adalah NIS dari nama tersebut padahal didetail data ternyata berbeda dimana NIS tersebut untuk nama sesuai database karena rumus di detail data yaitu membaca NIS yang muncul di Hasil Pencarian (Cell G9).

Untuk mengantisipasi hal tersebut maka kita buat sebuah Pesan Error yang akan muncul secara otomatis tatkala sebuah pencarian tidak sesuai dengan yang diharapkan, perhatikan pesan yang muncul di Cell D11 yang bertuliskan :

Data Error, Periksa Kembali Data Yang Anda Cari

Maka rumus yang kita buat adalah :

=IF(ISERROR($G$16),”Data Error, Periksa Kembali Data Yang Anda Cari“,”")

Dan ketika kita mencari data dengan kriteria NIS, dan data yang dimaksudkan benar-benar tidak ada didatabase maka rumus tersebut tetap berfungsi.

Masih belum faham juga, tetaplah belajar karena sesuatu akan mudah kita pelajari jika memang kita sungguh-sungguh dalam mempelajarinya….tapi jika malah pusing lihat tulisan diatas sok atuh….

Unduh tulisan ini – Contoh Aplikasi.

Salam.