Mencari Pemilik Nilai Maksimum pada Aplikasi Excel


Seorang peserta training mengajukan sebuah pertanyaan yang sekilas tampak sederhana, yaitu "Bagaimana Cara Mencari Pemilik Nilai Maksimum dalam sebuah tabel pada aplikasi Excel?" Solusi untuk pertanyaan ini sekilas tampak mudah, yaitu gunakan saja bantuan function karena seperti kita ketahui bahwa Excel memiliki function MAX yang dapat digunakan untuk mencari nilai maksimum dalam sebuah range. Aturan penulisan function MAX ini sangat sederhana, yaitu:
=MAX(sel awal:sel akhir) atau =MAX(range)
Contoh penggunaan function MAX ini dapat dilihat pada tabel berikut ini.
image
Untuk mencari nilai maksimum pada tabel tersebut, maka pada sel yang kosong kita tinggal memasukan function:
=MAX(C4:C7)
Dan hasil yang diperoleh adalah 100.
Namun, bukan nilai 100 ini yang ditanyakan oleh peserta training tersebut, melainkan siapa pemiliknya. Jika kita lihat pada tabel tersebut pemilik nilai 100 ini jelas adalah mata kuliah IPA. Dan hal inilah yang justru ditanyakan, yaitu bagaimana cara mencari "IPA" yang merupakan pemilik nilai maksimum pada tabel tersebut dengan bantuan function.

Mengenal Function INDEX dan MATCH
Sebagai solusinya, kita dapat menggunakan function INDEX. Kegunaan dari function INDEX ini adalah untuk mengambil data dari sebuah tabel atau array, berdasarkan perpotongan baris dan kolom. Aturan penulisannya sebagai berikut:
=INDEX(array, baris keberapa, kolom keberapa)
Sebagai contoh, pada tabel diatas, jika kita memasukan function:
=INDEX(B3:C7,3,1)
Maka hasilnya adalah IPA. Yaitu data pada sel yang merupakan perpotongan baris ke-3 dan kolom ke-1 pada range B3:C7.
Namun function INDEX ini belum cukup untuk menyelesaikan pertanyaan. Kita harus menggunakan satu function lagi yaitu function MATCH. Function ini bisa kita gunakan untuk mengambil posisi tertentu (umumnya berupa nomor baris) dari sebuah range berdasarkan nilai kunci tertentu. Aturan penulisannya sebagai berikut:
=MATCH(nilai kunci, range, tipe pencarian)
Untuk field "tipe pencarian" pada function MATCH ini, ada 3 jenis, yaitu:
  • 1 : Untuk mencari nilai terbesar yang lebih kecil atau sama dengan nilai kunci. Syarat penggunaan tipe ini adalah data pada range harus tersusun secara menaik (ascending)
  • -1 : Untuk mencari nilai terkecil yang lebih besar atau sama dengan nilai kunci. Syarat penggunaan tipe ini adalah data pada range harus tersusun secara menurun (descending)
  • 0 : Untuk mencari nilai pertama yang sama dengan nilai kunci. Data pada range dapat tersusun secara menaik ataupun menurun.
Nilai defaultnya untuk tipe pencarian ini adalah 1. Namun untuk mencari hasil yang eksak atau pasti maka kita bisa menggunakan nilai 0. Sebagai contoh, pada tabel diatas, jika kita memasukan function:
=MATCH(100,C3:C7,0)
Maka hasilnya adalah 3. Penjelasannya adalah pada range C3:C7, nilai 100 itu berada di baris keberapa? dan hasilnya jelas baris ke-3.

Penggabungan Function
Sekarang kembali ke pokok pertanyaan, untuk menampilkan siapa pemilik nilai maksimum tadi, maka nilai kunci pada function MATCH ini akan kita ganti dengan function MAX dan kemudian function MATCH ini juga akan digabungkan dengan function INDEX, yaitu menggantikan field "baris keberapa".
Untuk mempermudah, kita atur dulu tabel diatas seperti berikut ini:
image
Pada sel C9, kita akan memasukan function INDEX dengan range B3:B7. Hasil yang diharapkan adalah mata pelajaran yang berada di kolom pertama maka kita masukan 1 pada field "kolom keberapa". Sementara untuk field "baris keberapa" jelas belum diketahui karena baris ini tentunya akan bervariasi. Variasi baris ini akan diselesaikan dengan function MATCH. Dan yang terakhir, nilai kunci pada function MATCH akan diganti dengan function MAX. Untuk field "tipe pencarian" jelas digunakan nilai 0 karena datanya sudah pasti.
Agar lebih jelas, berikut ini urutannya, dimulai dari aturan penulisan function INDEX terlebih dahulu:
=INDEX(array, baris keberapa, kolom keberapa)
=INDEX(B3:C7,baris ??,1)
=INDEX(B3:C7,MATCH(nilai kunci, range, tipe pencarian),1)
=INDEX(B3:C7,MATCH(nilai kunci ??,C3:C7,0),1)
=INDEX(B3:C7,MATCH(MAX(C4:C7),C3:C7,0),1)
Nah, akhirnya dengan bantuan ketiga function tersebut, kita dapat juga menemukan siapa pemilik nilai maksimum tersebut, yaitu dengan function akhir sebagai berikut ini:
=INDEX(B3:C7,MATCH(MAX(C4:C7),C3:C7,0),1)
Untuk memeriksa hasilnya, cobalah untuk merubah angka di kolom nilai dan lihat di sel C9, apakah mengalami perubahan atau tidak. Jika ingin mencoba-nya secara langsung, silakan gunakan tabel berikut ini:

Pengembangan Lebih Lanjut
Tulisan ini jelas hanya membahas dasar-dasar dari penggunaan function INDEX dan MATCH yang paling sederhana pada Excel 2007 dan Excel 2010. Penggunaan lebih lanjut jelas sangat dimungkinkan. Yang paling sederhana misalnya untuk mencari pemilik nilai minimum maka function MAX tinggal kita ganti dengan function MIN atau untuk mencari pemilik nilai rata-rata maka kita tinggal menggantinya dengan function AVERAGE, dst.

Tautan Referensi
Referensi resmi untuk function INDEX dan MATCH ini bisa didapatkan di tautan berikut ini:

Penutup
Aplikasi Excel menawarkan banyak sekali perintah dan fasilitas yang tentunya bertujuan untuk mempercepat pekerjaan kita. Tinggal lakukan banyak ekplorasi lebih lanjut untuk mempelajari penggunaan perintah-perintah tersebut agar pekerjaan kita bisa lebih efisien dan tentunya lebih produktif lagi.
Demikian dan mudah-mudahan tulisan ini ada gunanya :)




Sumber : Arhiez.net