Membuat Drop down list dan Fungsi INDEX serta MATCH pada Microsoft Excel

Nah loh, ada apa ini ?? Sejak kapan Asaljeplak utak-atik Excel??

Jadi gini ceritanya, kemaren lagi sok2an buka excel di komputer, niatnya lagi mau ngerakit PC, tapi bingung sendiri dan kerepotan mbandingin harga2 sparepart.

Akhirnya, iseng2 sok jago mau buat template biar nanti pas milih jenis sparepartnya (misalnya processor, ram, atau lainnya) langsung muncul harganya di kolom sebelahnya.

Ngerti ga maksudnya? kaya’ gini loh…

1. Bikin daftar sparepart di sheet baru (jangan di sheet1), terserah mau sebanyak apa isinya, contoh harga yg ada di gambar itu harga yg didapet dari toko komputer :

(ini contoh untuk 1 jenis sparepart aja, disini contohnya CPU/Processor)

2. Bikin template utama di sheet 1 :

udah keliatan kan maksudnya asaljeplak? jadi nantinya ketika kolom sparepart diisi (nanti ngisinya ala dropdown gitu, akan dijelasin lebih lanjut…nanti ye), maka otomatis harga sparepart yg dipilih, sesuai yg ditulis pada sheet 2, akan muncul pada kolom harga di sheet 1

3. Bikin drop down list untuk sparepart yang diinginkan.

Drop…down? maksudnya begimane? begini nihhh…

Jadi nanti ketika kolom sparepart, dalam contoh ini CPU dipilih, maka akan muncul daftar menurun (hayah) dari jenis-jenis sparepart yang udah dibikin di sheet 2 sebelumnya.

Caranya gimana ?

– Pilih kolom sparepart yang masih kosong di sheet 1 (dalam contoh ini CPU, jadi pilih kolom kosong di sebelah tulisan CPU pada sheet 1), lalu Pilih tab DATA, dan pilih DATA VALIDATION.

– Lanjut, nanti akan terbuka pop-up baru, pada tab SETTING, pilih LIST pada kolom ALLOW.

– Lalu pada kolom SOURCE, tekan tombol kecil yang ada di ujung kolom tersebut.

– Begitu tampilan berubah jadi seperti ini :

(notes : ga usah dipikirin tulisan ‘=CPU’ yang ada di gambar, nanti ada penjelasannya, kalo baru bikin dari awal mestinya emang kosong kok)

– Pindah ke sheet 2, lalu Highlight seluruh sparepart yang diinginkan (dalam contoh ini CPU) yang ada di sheet 2, nama/jenis sparepart nya aja ya, judul kuning2 di atasnya dan harganya ga usah dipilih

– Nanti otomatis kolom kosong akan terisi dengan rentang cell seperti ini :

– tekan ENTER, dan pilih OK begitu kembali pada menu pop-up, jadi sudah dropdown list nya 🙂

Apabila sudah berhasil sampai sini, berarti tinggal satu hal lagi, yaitu begimana memunculkan harga sparepart yang dipilih pada dropdown secara OTOMATIS. Yap, benar….OTOMATIS…. jejejengg…

4. Fungsi INDEX dan MATCH untuk memunculkan harga secara OTOMATIS (jejejeng….)

Oke, ini apa lagi ya? INDEX? MATCH? INDEX alamat? INDEX Saham? MATCH? Pertandingan? pertandingan apa? Bola? Badminton? atau…

STOP! Kalau ada yang masih nanyain hal itu berarti perlu baca lagi judul artikel ini -.-”

Tentunya ini fungsi INDEX serta MATCH pada Microsoft Excel lah.

Gunanya apa? Secara umum ga ngerti jelasinnya, tapi langsung ke prakteknya pada kasus ini aja deh :

– Pertama-tama, marilah kita panjatkan… eh maksudnya, kita liat contoh dari fungsi yang akan digunakan nantinya ini.

=INDEX(Sheet2!A2:C83; MATCH(Sheet1!B2; CPU; 0); 2)

– Penjelasan dari fungsi di atas adalah, pada cell terpilih dia akan mencocokkan data dari cell B2 pada Sheet 1, yang sebelumnya data tersebut sudah didaftarkan dengan nama grup CPU, dan berada di dalam indeks rentang cell A2-C83 pada Sheet 2, dan akan diambil bagian kolom ke-2.

Bingung ? pastinya. Begini langkah-langkahnya :

– Bikin nama grup dari spare part yang ada di sheet 2, kita namakan saja CPU, caranya gini :

Highlight sparepart (sparepartnya aja, harga ama judul ga usah) pada Sheet 2 seperti tadi sewaktu membuat dropdown list, kemudian ganti nama pada kolom kiri atas menjadi CPU (atau terserah aja namanya apa)

– Kalo sudah, ke Sheet 1 lagi, kemudian ketikkan fungsi seperti yang ada di atas pada kolom Harga yang masih kosong.

Kalau fungsinya benar, maka nanti akan muncul secara OTOMATIS harga dari sparepart yang dipilih dari kolom dropdown 🙂 .

Rincian penjelasan fungsi :

a. Sheet2!A2:C83 adalah data sparepart yang ada pada sheet 2 dan dipilih semuanya baik sparepart dan harga (judul tidak usah dipilih) adalah dari cell A2 hingga cell C83 untuk membuat INDEX, apabila pada penerapannya datanya tidak sebanyak ini atau lebih banyak dari ini tentunya akan beda rentang cell yang muncul.

b. Sheet1!B2 adalah kolom drop down sparepart yang sudah dibuat sebelumnya, tujuannya untuk me-MATCH-kan data ini dengan harga yang akan dimunculkan.

c. CPU adalah nama grup dari sparepart yang sudah dibuat sebelumnya, nama ini tergantung nama grup nya tadi dibuat nya apa ya.

d. angka 0 itu menunjukkan… ah ga ngerti ini, tapi yang pasti harus dimasukin ke rumus hahaha :p

e. angka 2 itu menunjukkan bahwa data yang akan diambil dari sheet 2 adalah data pada kolom ke dua, yaitu harga sparepart. Apabila data yang ingin diambil ada pada kolom ketiga ya tulisnya 3, dst dst.

file nya bisa diunduh disini :

fungsi-indexmatch

Fiuh kelar juga, ini asaljeplak tumben bener ya begini hahaha, but anyway , semoga terbantu bagi para pembaca 😉

Selamat Mencoba dan Salam Olahraga…eh, Olahdata!