Pencarian Multi Variabel dengan Match dan Index Excel

Pencarian Multi Variabel dengan Match dan Index Excel

Pertanyaan


Saya lagi cari rumus utk bisa mengembalikan nilai dari pencarian yang menggunakan 3 variabel.. misalnya saya punya sheet Daftar Harga Barang dgn kolom sbb:
  • Kolom 1 : Berupa Kode/ID
  • Kolom 2 : Berupa tipe konsumen (reseller/langsung)
  • Kolom 3 : Harga barang.
di sheet yang lain saya punya rincian transaksi penjualan yang disusun perhari. Pada sheet ini sy punya kolom:
  • Kolom 1: Tanggal
  • Kolom 2: Kode/ID
  • Kolom 3: Tipe konsumen
Nah saya menginginkan pada Kolom 4 dapat muncul harga barang tersebut utk Kode dan tipe konsumen yg sudah saya catat sebelumnya.
Bagaimana caranya? function apa yg perlu sy gunakan dan gimana rumusnya.

Jawab

Solusi untuk masalah ini adalah fungsi yang digunakan adalah Index dan Match, tetapi dengan pendekatan Array Formula. Berikut adalah rincian jawabannya.

Untuk menjawab pertanyaan ini, kami menggunakan file yang dapat Anda download pada alamat http://adf.ly/MBQfJ. Workbooknya akan terlihat sebagai berikut.

Workbook ini memiliki sheet "data" dan "master_harga" (klik pada tab dengan nama sheet tersebut untuk melihat rincian dari tiap sheet).



Sekarang kita akan mengisi kolom Harga yang didapatkan dari pencarian kombinasi kolom Kode / ID dan Tipe ke sheet master_harga. (klik gambar untuk memperbesar)


Berikut adalah langkah-langkah untuk melakukan kombinasi pencarian tersebut :
  1. Jalankan aplikasi Microsoft Excel 2007 atau ke atas.
  2. Buka file yang Anda download tersebut.
  3. Pada sheet data, klik cell pada alamat D2 dan masukkan rumus di bawah ini. Perhatikan bahwa kita menggunakan operator dan ( & ) untuk menggabungkan cell B dan C (kolom Kode / ID dan Tipe).

    =INDEX(master_harga!$C$2:$C$11,MATCH(B2&C2,master_harga!$A$2:$A$11&master_harga!$B$2:$B$11))

    Tekan CTRL + SHIFT + ENTER untuk menyelesaikan formula tersebut (bukan Enter seperti biasanya).

  4. Hasilnya akan tampak seperti pada gambar screenshot sebagai berikut. Perhatikan bahwa rumus yang kita masukkan diapit oleh kurung kurawal {}. Ini pertanda bahwa formula tersebut adalah array formula.
  5. Copy rumus tersebut ke kolom Harga yang masih kosong.

  6. Selesai.

Referensi

Mengaktifkan Solver di MS Excel 2007

Mengaktifkan Solver di MS Excel 2007

Solver adalah fasilitas di Excel yang terdiri dari kumpulan fungsi / perintah untuk menemukan pemecahan yang optimal dari suatu permasalahan (dalam suatu batasan dan formulasi).


Contoh worksheet dari penggunaan Solver ini disertakan dalam distribusi Microsoft Excel 2007, lokasi dan nama filenya adalah :
  • C:\Program Files\Microsoft Office\Office12\SAMPLES\SOLVSAMP.XLS

Akses Menu Solver

  1. Solver dapat digunakan melalui menu tab "Data" | group "Analysis" | "Solver".

  2. Apabila menu tersebut belum ada maka lakukan petunjuk berikut ini untuk mengaktifkan menu solver :
    1. Klik tombol "Office Button"

    2. Klik tombol "Excel Options"

    3. Pada dialog "Excel Options", klik "Add-Ins" dan pilih "Solver Add-in" dari daftar aplikasi add-in yang tidak aktif - Inactive Application Add-Ins.

    4. Klik tombol "Go" pada bagian "Manage Excel Add-Ins".

    5. Pastikan "Solver Add-In" terpilih. Klik tombol "OK".

Contoh Penggunaan Fungsi IF

Contoh Penggunaan Fungsi IF

Pendahuluan

SUMIF adalah fungsi penjumlahan nilai-nilai yang terdapat pada suatu range data, namun nilai yang diambil adalah berdasarkan pada satu pengkondisian atau filter yang ditentukan oleh kita.


Syntax dari fungsi SUMIF adalah sebagai berikut :

SUMIF(range, criteria, [sum_range])

Keterangan :
  • range : adalah range dari cell-cell yang ingin kita evaluasi berdasarkan kriteria yang akan kita tentukan.
  • criteria : kondisi atau aturan filter yang ingin kita gunakan.
  • sum_range : jika ini disebutkan maka range ini yang akan jadi acuan untuk mengambil nilai, tetapi posisinya sesuai dengan kolom - baris dari range yang terfilter.

Contoh Penggunaan

  1. Sebagai contoh, kita memiliki data range seperti terlihat di bawah ini  (A2:D10). Yang ingin kita lakukan adalah mengambil total dari Kacang saja kemudian ditempatkan hasilnya pada D11.

  2. Tempatkan cursor pada cell D11 dan ketikkan formula berikut :

    =SUMIF(D3:D10,"=Kacang",C3:C10)

    ini artinya dari range kriteria dari D3:D10, kita mengambil yang nilainya Kacang saja. Setelah itu ambil range nilai yang akan dijumlah yaitu C1:C10.

  3. Hasilnya akan terlihat sebagai berikut. Nilai 15 didapatkan sebagai total jumlah untuk Kacang saja.

Fungsi IF pada EXCEL

Fungsi IF pada EXCEL

Pada Excel 2007 dan versi sebelumnya, fungsi IF digunakan untuk mendapatkan nilai berdasarkan kondisi tertentu.


Fungsi ini sering disebut sebagai fungsi keputusan, dimana kita menetapkan rule / aturan yang akan mengambil keputusan pengembalian nilai yang berbeda dari input yang masuk.
If sebagai fungsi keputusan dari suatu kondisi


Syntax dasar dari fungsi IF adalah sebagai berikut

IF(logical_test_value, value_if_true, value_if_false)

Contoh Penggunaan

Misalkan kita memiliki contoh data penjualan sederhaan berikut ini. Total Bayar pada kolom "Total Bayar" (G) akan diisi dengan potongan diskon 10% dari "Total" jika pada kolom "Diskon" terdapat nilai "Y".


Pemecahannya untuk cell G2 adalah sebagai berikut :

=F2*IF(C2="Y",0.9,1)

Penjelasan :
  • Nilai G2 (Total Bayar) didapat dari hasil perkalian F2 (Total) dengan 0.9 (diskon 10%) jika nilai C2 (Diskon) adalah "Y".
  • Jika nilai C2 (Diskon) bukan "Y" maka tidak ada diskon, dan nilai G2 sama dengan nilai F2.
Pengguna'an ISBLANK pada EXCEL

Pengguna'an ISBLANK pada EXCEL

Fungsi ISBLANK digunakan untuk melakukan pengecekan apakah suatu cell berisi nilai kosong atau null. Jika memang kosong atau null, maka akan mengembalikan nilai TRUE (BENAR), sebaliknya mengembalikan FALSE (SALAH).


Sebagai contoh, pada Gambar.1 berikut cell C3 berisi rumus =ISBLANK(B3). Rumus ini digunakan untuk melakukan pengecekan apakah cell B3 kosong. Ternyata cell B3 berisi teks "Satu", dengan demikian tidak kosong dan fungsi ISBLANK(B3) akan mengembalikan nilai FALSE.

Gambar.1 Contoh Pengecekan ISBLANK yang mengembalikan nilai FALSE


Sedangkan pada Gambar.2 berikut cell C4 berisi rumus =ISBLANK(B4). Rumus ini digunakan untuk melakukan pengecekan apakah cell B4 kosong. Dan ternyata hasilnya memang kosong. Dengan demikian fungsi ISBLANK(B4) akan mengembalikan nilai TRUE.

Gambar.2 Contoh Pengecekan ISBLANK yang mengembalikan nilai TRUE

Demikian contoh penggunaan sederhana dari ISBLANK. Semoga bisa bermanfaat bagi para pengunjung sekalian. Saran, kritik dan komentar apapun dengan senang hati kami terima demi kemajuan situs ini. Terima kasih.