Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.
Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.
Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.
Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.
Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.
Ada beberapa alternative formula yang dapat digunakan. Berikut 3 diantaranya:
- INDEX MATCH
- OFFSET MATCH
- INDIRECT-ADDRESS-MATCH-ROW-COLUMN
Ketiga formula tersebut harus dibuat dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.
Baiklah kita lanjutkan dengan Studi Kasus.
Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel
Anggaplah kita memiliki tabel data harga buah-buahan dari berbagai supplier. Label kolom tabel dari kiri ke kanan adalah:
- Kolom A = No
- Kolom B = Buah
- Kolom C = Supplier
- Kolom D = Harga
Selanjutnya misalnya kita harus mencari data harga buah dari suplier tertentu.
Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam tugas pencarian data ini, yaitu:
- Kriteria 1 = nama buah
- Kriteria 2 = nama suplier
Pertanyaannya: Bagaimana rumusnya untuk mendapatkan data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .
Contoh kasus dan pertanyaan tersebut dapat digambarkan sebagai berikut:
Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat sebenarnya akan terasa jika bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.
Selain itu, contoh yang ditampilkan juga hanya menggunakan 2 kriteria, namun pada dasarnya prinsip kerjanya adalah sama, baik menggunakan 2 kriteria, 3 kriteria atau lebih banyak lagi.
Selanjutnya mari kita bahas satu persatu.
Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array, sehingga ketika selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.
Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH
Salah satu rumus terpopuler yang biasa digunakan untuk pencarian data adalah kombinasi fungsi INDEX dan MATCH.
Dalam hal contoh kasus di atas, kita bisa menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:
{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}
Cara Kerja Rumus INDEX MATCH
- Operator ampersand (&) digunakan untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom kolom buah (B2:B8) dan supplier (C 2:C8)
- Fungsi MATCH berguna untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
- Angka yang diperoleh dari fungsi MATCH kemudian digunakan oleh fungsi INDEX sebagai row_index dalam range D2:D8.
- Dan sel baris ke-3 dalam range D2:D8 adalah sel D4, sementara itu sel D4 berisi nilai 8000.
- Sehingga hasil akhir adalah 8000.
Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH
Fungsi OFFSET berguna untuk mendapatkan referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.
Dengan menggabungkannya dengan fungsi MATCH, maka kita akan menentukan berapa baris jarak sel yang dicari dari sel acuan, kemudian mendapatkan nilai dari sel yang dicari tersebut.
{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}
Cara Kerja Rumus OFFSET-MATCH
- Fungsi MATCH berperan sama seperti halnya dalam rumus INDEX MATCH, dimana fungsi MATCH dalam contoh tersebut menghasilkan angka 3.
- Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana dapat dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapatkan nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
- Dan sel dimaksud dengan kriteria tersebut adalah sel D4
- Sementara itu sel D4 berisi nilai 8.000. Bilangan inilah yang merupakan hasil akhirnya.
Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN
Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string yang mereferensikan sebuah alamat sel tertentu.
Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.
Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.
{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}
Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN
- Fungsi MATCH berguna untuk mendapatkan nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapatkan index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka 3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapatkan angka 4.
- Index Kolom dalam spreadsheet didapatkan menggunakan bantuan fungsi COLUMN, dan kolom D2:D8 adalah 4 (kolom D)
- Ringkasnya rumus tersebut dapat dikonvert menjadi =INDIRECT(ADDRESS(4,4))
- ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
- Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapatkan nilai dalam sel $D$4
- Dan hasilnya adalah 8.000
Ringkasan.
Cari data dengan 2 kriteria atau lebih dapat dilakukan tanpa menggunakan kolom bantu, yaitu menggunakan rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya dapat digunakan sesuai kebutuhan fungsi lainnya.
Demikian semoga bermanfaat.
Salam
0 Komentar