Belajar Microsoft Excel: 3 Alternatif Rumus Cari Data Banyak Kriteria

Blog / Oleh
rumus cari data banyak kriteria
Masih dalam lingkup topik penelusuran data di excel. Catatan pelajaran excel kali ini akan membahas perihal cara mencari data dengan banyak kriteria.

Pada postingan sementara waktu yang kemudian perihal fungsi VLOOKUP sebenarnya telah dibahas perihal cara penelusuran data dengan dua kriteria. Namun dalam contoh tersebut dikehendaki kolom bantu yang terdiri dari data adonan (concatenate) dari standar yang ditetapkan.

Nah… sedangkan dalam peluang ini kita akan menggunakan rumus pencari data banyak standar tanpa mesti menggunakan kolom bantu.

Ada beberapa alternative formula yang sanggup digunakan. Berikut 3 diantaranya:

  1. INDEX MATCH
  2. OFFSET MATCH
  3. INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut mesti dibentuk dalam bentuk rumus array yakni dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita teruskan dengan Studi Kasus.

Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel

Anggaplah kita memiliki tabel data harga buah-buahan dari aneka macam supplier. Label kolom tabel dari kiri ke kanan adalah:

  • Kolom A = No
  • Kolom B = Buah
  • Kolom C = Supplier
  • Kolom D = Harga

Selanjutnya misalnya kita mesti mencari data harga buah dari suplier tertentu.

Dengan kata lain , ada dua standar yang mesti diamati dalam kiprah penelusuran data ini , yaitu:

  • Kriteria 1 = nama buah
  • Kriteria 2 = nama suplier

Pertanyaannya: Bagaimana rumusnya untuk mendapat data harga buah tersebut secara cepat sehingga tidak mesti menyaksikan satu persatu baris dalam tabel .

Contoh problem dan pertanyaan tersebut sanggup digambarkan selaku berikut:

apa rumus untuk cari data dengan banyak kriteria

Maaf , gambar tersebut hanyalah selaku contoh saja , dengan baris data yang sungguh sedikit. Manfaat bekerjsama akan terasa jikalau melakukan pekerjaan dengan baris data yang besar yang menyusahkan penelusuran data dengan mata langsung.
Selain itu , contoh yang ditampilkan juga cuma menggunakan 2 standar , tetapi intinya prinsip kerjanya yakni sama , baik menggunakan 2 standar , 3 standar atau lebih banyak lagi.

Selanjutnya mari kita diskusikan satu persatu.
Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array , sehingga dikala  selesai mengetik atau mengedit rumus , maka kita mesti menekan CTR+SHIFT+ENTER.

Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH

Salah satu rumus terpopuler yang lazim dipakai untuk penelusuran data yakni variasi fungsi INDEX dan MATCH.
Dalam hal contoh problem di atas , kita sanggup menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:
{=INDEX(D2:D8 ,MATCH(G1&G2 ,B2:B8&C2:C8 ,0))}
Rumus INDEX MATCH cari data

Cara Kerja Rumus INDEX MATCH

  • Operator ampersand (&) dipakai untuk memadukan standar nama buah (G1) dan supplier (G2) serta memadukan masing-masing sel sejajar pada kolom  kolom buah (B2:B8) dan supplier (C 2:C8)
  • Fungsi MATCH berkhasiat untuk mencari nomor index baris data yang dimana kolom B mesti berisi nama buah yang serupa dengan nama buah di sel G1 , serta kolom C mesti berisi nama buah yang serupa dengan sel G2. Dalam contoh yang ditampilkan , rumus ini menciptakan angka 3 yakni posisi baris yang yang berisi buah pisang dan suplier CV Subur.
  • Angka yang diperoleh dari fungsi MATCH kemudian dipakai oleh fungsi INDEX selaku row_index dalam range D2:D8. 
  • Dan sel baris ke-3 dalam range D2:D8 yakni sel D4 , sementara itu sel D4 berisi nilai 8000.
  • Sehingga hasil final yakni 8000.

Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH


Fungsi OFFSET berkhasiat untuk mendapat referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.
Dengan menggabungkannya dengan fungsi MATCH , maka kita akan menyeleksi berapa  baris jarak sel yang dicari dari sel contoh , kemudian mendapat nilai dari sel yang dicari tersebut.
{=OFFSET(D1 ,MATCH(G1&G2 ,B2:B8&C2:C8 ,0) ,0)}
Rumus OFFSET MATCH cari data banyak kriteria

Cara Kerja Rumus OFFSET-MATCH

  • Fungsi MATCH berperan sama seumpama halnya dalam rumus INDEX MATCH , dimana fungsi MATCH  dalam contoh tersebut menciptakan angka 3.
  • Angka 3 terebut kemudian dijadikan selaku argumen rows oleh fungsi OFFSET , atau sederhana sanggup dituliskan =OFFSET(D1 ,3 ,0) . Rumus berfungsi untuk mendapat nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
  • Dan sel dimaksud dengan standar tersebut yakni sel D4
  • Sementara itu sel D4 berisi nilai 8.000.   Bilangan inilah yang ialah hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Fungsi INDIRECT berkhasiat untuk mendapat nilai secara tidak eksklusif dari string yang mereferensikan suatu alamat sel tertentu.

Sedangkan fungsi ADDRESS dipakai untuk menciptakan 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)))}
Contoh Rumus INDIRECT ADDRESS Cari data

Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

  • Fungsi MATCH berkhasiat untuk mendapat nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing standar yang ditetapkan. Hasil dari proses tersebut disertakan dengan index ROW sel D1 untuk mendapat index baris dalam spreadsheet. Dalam contoh , fungsi match menciptakan angka  3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapat angka 4.
  • Index Kolom dalam spreadsheet ditemukan menggunakan santunan fungsi COLUMN ,  dan kolom D2:D8 yakni 4 (kolom D)
  • Ringkasnya rumus tersebut sanggup dikonvert menjadi =INDIRECT(ADDRESS(4 ,4))
  • ADDRESS(4 ,4) menciptakan string referensi sel “$D$4”
  • Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapat nilai dalam sel $D$4
  • Dan hasilnya yakni 8.000
  
Ringkasan.
Cari data dengan 2 standar atau lebih sanggup dilaksanakan tanpa menggunakan kolom bantu , yakni menggunakan rumus array , variasi 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 menyanggupi standar , yang berikutnya sanggup dipakai sesuai keperluan fungsi lainnya.
Demikian biar bermanfaat.

Salam

Silahkan dibaca juga postingan Belajar Excel Lainnya:

Need Help? Chat with us