Penggunaan Fungsi Index dan Match pada Excel 2007

» » Penggunaan Fungsi Index dan Match pada Excel 2007

Pasangan Match dan Index sering digunakan untuk mencari data secara dinamis

Apa itu Fungsi Index dan Match ?

Dalam keseharian pengolahan data dalam Excel hampir dipastikan Anda akan terlibat dalam dua kondisi berikut :
  • Melakukan pencarian dari suatu nilai terhadap range data tertentu (referensi).
  • Pencarian terhadap referensi tersebut harus cukup dinamis, ini dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan.
Untuk keperluan hal tersebut, kita dapat melakukannya dengan mudah dari penggunaan dua fungsi Excel, yaitu INDEX dan MATCH.

Fungsi INDEX

Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.

Pencarian digunakan berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Syntax fungsi INDEX adalah sebagai berikut :

INDEX(array, row_num, [column_num])

Keterangan :
  • array : adalah table / range data yang terdiri dari satu atau beberapa kolom dan baris.
  • row_num : adalah angka yang menunjukkan posisi baris dengan acuan dari cell pertama ( kolom / baris ujung kiri atas ) dari array.
  • column_num :  adalah angka yang menunjukkan posisi kolom dengan acuan dari kolom / baris pertama dari array. Argumen ini bersifat opsional (boleh digunakan atau tidak).

Penjelasan mengenai fungsi INDEX ini dapat diilustrasikan pada Gambar 1 di bawah ini.

Gambar 1. Ilustrasi Penggunaan Fungsi Index
Dengan formula "=INDEX(B3:B8, 4, 2)", kita mengambil jarak data dari posisi acuan B3 sebesar 2 kolom ke kanan dan 4 baris ke bawah. Kenapa kita mengambil B3 sebagai acuan ? Karena B3 adalah  cell pertama dari array / data range B3:D4.

Hasil dari "navigasi cell" pada fungsi ini adalah nilai dari cell C6, yaitu angka 20.

Gambar berikutnya (gambar 2) menunjukkan hasil penggunaan fungsi INDEX pada worksheet Excel.

Gambar 2. Contoh Penggunaan Fungsi Index (klik untuk memperbesar)

Fungsi MATCH

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya.

Syntax fungsi MATCH adalah sebagai berikut :

MATCH(lookup_value, lookup_array, [match_type])

Keterangan :
  • lookup_value : adalah nilai yang ingin dicari pada lookup_array.
  • lookup_array : adalah range data dari suatu kolom ataupun baris.
  • match_type :  adalah angka yang menunjukkan tipe pencocokan sebagai berikut :
    • 1 : jenis pencocokan dimana lookup_array dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
    • 0 :  jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara sekuensial.
    • -1 :  jenis pencocokan dimana lookup_array dalam keadaan terurut secara descending (besar ke kecil).  Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Untuk kejelasan apa yang dimaksud dengan  match_type ini, perhatikan ilustrasi pada Gambar 3 di bawah ini.

Pada contoh tersebut nilai lookup adalah 3, yang kemudian dicari pada data array dengan tiga kelompok susunan data seperti tampak pada gambar.


Gambar 3. Ilustrasi Contoh Penggunaan Match Type - Skema Pertama

Terlihat dengan pilhan match_type mulai dengan nilai  -1, 0 dan 1 didapatkan hasil posisi yang berbeda dari fungsi match, masing-masing yaitu 5, 2, dan 4.


Contoh lainnya terlihat pada Gambar 4 di bawah ini. Pada kasus ini nilai lookupnya adalah 4 yang dicari pada data array dengan nilai 1, 2, 3, 3, 5, 5 dan 6 (sama dengan contoh sebelumnya). Dengan pilhan tiap  tipe mulai dari -1, 0 dan 1 didapatkan posisi dari fungsi match masing-masing adalah 3, NA (Not Available / Tidak Ditemukan), dan 4.

Gambar 4. Ilustrasi Contoh Penggunaan Match Type - Skema Kedua

Screenshot berikut menunjukkan beberapa contoh lebih lanjut penggunaan match pada Excel 2007 (klik pada gambar untuk memperbesar).

Gambar 5. Contoh Penggunaan Match pada Excel (1)

Gambar 6. Contoh Penggunaan Match pada Excel 2007 (2)

Gambar 7. Contoh Penggunaan Match pada Excel 2007 (3)

Penggunaan dari Gabungan Fungsi INDEX dan MATCH

Seperti dijelaskan sebelumnya, penggabungan fungsi INDEX dan MATCH akan menghasilkan solusi pencarian data yang cukup powerful dimana kita dapat mencari dari referensi berdasarkan kolom / baris yang kita inginkan.

Syntax dari penggabungan fungsi ini tampak seperti berikut :

INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), column_num)

jika yang dicari adalah data range baris pada suatu kolom,

atau...

INDEX(array, row_num, MATCH(lookup_value, lookup_array, [match_type]))

jika yang dicari adalah data range kolom pada suatu baris.

Sekilas solusi ini mirip dengan fungsi VLOOKUP yang telah kita bahas sebelumnya. Namun dengan fungsi VLOOKUP kita terbatas pada pencarian pada kolom pertama pada data range referensi dan harus terurut, sedangkan dengan penggabungan fungsi ini kita bisa mencari dari kolom manapun dan tidak perlu dalam keadaan terurut (sesuai match_type tentunya).

Berikut adalah dua gambar contoh penggunaan dari gabungan kedua fungsi INDEX dan MATCH.

Gambar 8. Contoh Penggunaan Index dan Match (1)

Gambar 9. Contoh Penggunaan Index dan Match (2)

Kesimpulan

Fungsi MATCH dan INDEX masing-masing merupakan fungsi untuk melakukan pencarian dan navigasi dari suatu table / data range. Bedanya fungsi MATCH mengembalikan nilai posisi sedangkan fungsi INDEX mengembalikan nilai dari suatu posisi cell.

Penggabungan kedua fungsi tersebut menjadi solusi yang sangat baik sebagai alternatif dari fungsi VLOOKUP yang telah dikenali sebagai fungsi untuk mencari / lookup suatu nilai referensi.

Update

Contoh file Excel yang dibahas disini dapat didownload di URL berikut : http://goo.gl/MzkpW.

Sumber Referensi


Pasangan Match dan Index sering digunakan untuk mencari data secara dinamis

Apa itu Fungsi Index dan Match ?

Dalam keseharian pengolahan data dalam Excel hampir dipastikan Anda akan terlibat dalam dua kondisi berikut :
  • Melakukan pencarian dari suatu nilai terhadap range data tertentu (referensi).
  • Pencarian terhadap referensi tersebut harus cukup dinamis, ini dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan.
Untuk keperluan hal tersebut, kita dapat melakukannya dengan mudah dari penggunaan dua fungsi Excel, yaitu INDEX dan MATCH.

Fungsi INDEX

Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.

Pencarian digunakan berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Syntax fungsi INDEX adalah sebagai berikut :

INDEX(array, row_num, [column_num])

Keterangan :
  • array : adalah table / range data yang terdiri dari satu atau beberapa kolom dan baris.
  • row_num : adalah angka yang menunjukkan posisi baris dengan acuan dari cell pertama ( kolom / baris ujung kiri atas ) dari array.
  • column_num :  adalah angka yang menunjukkan posisi kolom dengan acuan dari kolom / baris pertama dari array. Argumen ini bersifat opsional (boleh digunakan atau tidak).

Penjelasan mengenai fungsi INDEX ini dapat diilustrasikan pada Gambar 1 di bawah ini.

Gambar 1. Ilustrasi Penggunaan Fungsi Index
Dengan formula "=INDEX(B3:B8, 4, 2)", kita mengambil jarak data dari posisi acuan B3 sebesar 2 kolom ke kanan dan 4 baris ke bawah. Kenapa kita mengambil B3 sebagai acuan ? Karena B3 adalah  cell pertama dari array / data range B3:D4.

Hasil dari "navigasi cell" pada fungsi ini adalah nilai dari cell C6, yaitu angka 20.

Gambar berikutnya (gambar 2) menunjukkan hasil penggunaan fungsi INDEX pada worksheet Excel.

Gambar 2. Contoh Penggunaan Fungsi Index (klik untuk memperbesar)

Fungsi MATCH

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya.

Syntax fungsi MATCH adalah sebagai berikut :

MATCH(lookup_value, lookup_array, [match_type])

Keterangan :
  • lookup_value : adalah nilai yang ingin dicari pada lookup_array.
  • lookup_array : adalah range data dari suatu kolom ataupun baris.
  • match_type :  adalah angka yang menunjukkan tipe pencocokan sebagai berikut :
    • 1 : jenis pencocokan dimana lookup_array dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
    • 0 :  jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara sekuensial.
    • -1 :  jenis pencocokan dimana lookup_array dalam keadaan terurut secara descending (besar ke kecil).  Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Untuk kejelasan apa yang dimaksud dengan  match_type ini, perhatikan ilustrasi pada Gambar 3 di bawah ini.

Pada contoh tersebut nilai lookup adalah 3, yang kemudian dicari pada data array dengan tiga kelompok susunan data seperti tampak pada gambar.


Gambar 3. Ilustrasi Contoh Penggunaan Match Type - Skema Pertama

Terlihat dengan pilhan match_type mulai dengan nilai  -1, 0 dan 1 didapatkan hasil posisi yang berbeda dari fungsi match, masing-masing yaitu 5, 2, dan 4.


Contoh lainnya terlihat pada Gambar 4 di bawah ini. Pada kasus ini nilai lookupnya adalah 4 yang dicari pada data array dengan nilai 1, 2, 3, 3, 5, 5 dan 6 (sama dengan contoh sebelumnya). Dengan pilhan tiap  tipe mulai dari -1, 0 dan 1 didapatkan posisi dari fungsi match masing-masing adalah 3, NA (Not Available / Tidak Ditemukan), dan 4.

Gambar 4. Ilustrasi Contoh Penggunaan Match Type - Skema Kedua

Screenshot berikut menunjukkan beberapa contoh lebih lanjut penggunaan match pada Excel 2007 (klik pada gambar untuk memperbesar).

Gambar 5. Contoh Penggunaan Match pada Excel (1)

Gambar 6. Contoh Penggunaan Match pada Excel 2007 (2)

Gambar 7. Contoh Penggunaan Match pada Excel 2007 (3)

Penggunaan dari Gabungan Fungsi INDEX dan MATCH

Seperti dijelaskan sebelumnya, penggabungan fungsi INDEX dan MATCH akan menghasilkan solusi pencarian data yang cukup powerful dimana kita dapat mencari dari referensi berdasarkan kolom / baris yang kita inginkan.

Syntax dari penggabungan fungsi ini tampak seperti berikut :

INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), column_num)

jika yang dicari adalah data range baris pada suatu kolom,

atau...

INDEX(array, row_num, MATCH(lookup_value, lookup_array, [match_type]))

jika yang dicari adalah data range kolom pada suatu baris.

Sekilas solusi ini mirip dengan fungsi VLOOKUP yang telah kita bahas sebelumnya. Namun dengan fungsi VLOOKUP kita terbatas pada pencarian pada kolom pertama pada data range referensi dan harus terurut, sedangkan dengan penggabungan fungsi ini kita bisa mencari dari kolom manapun dan tidak perlu dalam keadaan terurut (sesuai match_type tentunya).

Berikut adalah dua gambar contoh penggunaan dari gabungan kedua fungsi INDEX dan MATCH.

Gambar 8. Contoh Penggunaan Index dan Match (1)

Gambar 9. Contoh Penggunaan Index dan Match (2)

Kesimpulan

Fungsi MATCH dan INDEX masing-masing merupakan fungsi untuk melakukan pencarian dan navigasi dari suatu table / data range. Bedanya fungsi MATCH mengembalikan nilai posisi sedangkan fungsi INDEX mengembalikan nilai dari suatu posisi cell.

Penggabungan kedua fungsi tersebut menjadi solusi yang sangat baik sebagai alternatif dari fungsi VLOOKUP yang telah dikenali sebagai fungsi untuk mencari / lookup suatu nilai referensi.

Update

Contoh file Excel yang dibahas disini dapat didownload di URL berikut : http://goo.gl/MzkpW.

Sumber Referensi

terkait dengan Penggunaan Fungsi Index dan Match pada Excel 2007 :

{ 0 komentar... read them below or add one }


Posting Komentar