Belajar Microsoft Excel: 3 Alternatif Rumus Cari Data Banyak Kriteria

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 diperlukan kolom bantu yang terdiri dari data adonan (concatenate) dari persyaratan yang ditetapkan.

Nah… sedangkan dalam peluang ini kita akan menggunakan rumus pencari data banyak persyaratan 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 banyak sekali 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 persyaratan 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 .

Baca Juga  Belajar Microsoft Excel: Shortcut Ctr + Home Dan Ctr + End Tidak Melakukan Pekerjaan - Apa Masalahnya?

Contoh kasus 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 gotong royong 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 persyaratan , tetapi intinya prinsip kerjanya merupakan sama , baik menggunakan 2 persyaratan , 3 persyaratan 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 saat  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 merupakan variasi fungsi INDEX dan MATCH.
Dalam hal contoh kasus 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 persyaratan 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 mempunyai fungsi 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 merupakan sel D4 , sementara itu sel D4 berisi nilai 8000.
  • Sehingga hasil tamat merupakan 8000.
Baca Juga  Menampilkan Data Pencarian Dengan Enter Textbox

Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH


Fungsi OFFSET mempunyai fungsi 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 teladan , 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 persyaratan tersebut merupakan 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 mempunyai fungsi 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 mempunyai fungsi untuk mendapat nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing persyaratan 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 sumbangan fungsi COLUMN ,  dan kolom D2:D8 merupakan 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 merupakan 8.000
Baca Juga  Belajar Microsoft Excel: Inilah 5 Cara Transpose Data Excel
  
Ringkasan.
Cari data dengan 2 persyaratan 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 persyaratan , yang berikutnya sanggup dipakai sesuai keperluan fungsi lainnya.
Demikian agar bermanfaat.

Salam

Silahkan dibaca juga postingan Belajar Excel Lainnya:

Need Help? Chat with us