Rumus Excel Untuk Mencari Nilai Terdekat Di Excel

Rumus Excel Untuk Mencari Nilai Terdekat Di Excel

Rumus Excel Untuk Mencari Nilai Terdekat Di Excel

Pada tutorial sebelumnya, Kelas Excel sudah membahas tentang rumus excel untuk mencari nilai tertinggi atau terendah dengan kriteria tertentu. Kali ini kita akan belajar excel tentang bagaimana cara rumus excel yang digunakan untuk mendapatkan atau mencari nilai terdekat dengan excel.

Nilai terdekat disini maksudnya adalah nilai angka pada daftar angka yang memiliki selisih terkecil dengan nilai yang sedang kita cari, sehingga nilai angka yang harus ditemukan ini bisa lebih besar atau sebaliknya lebih kecil dengan nilai yang sedang kita cari.

Sebagai gambaran perhatikan contoh data berikut.

Cara Mencari Nilai Terdekat Pada Excel

Pada contoh diatas, kita memiliki daftar angka acak antara 10-100 pada kolom A. Dari data tersebut kita ingin mendapatkan nilai terdekat dari angka 36 misalnya, maka hasil yang kita harapkan adalah angka 40 dari daftar tersebut. Sedangkan jika nilai yang dicari adalah 32 maka rumus excel harus menghasilkan angka 30 sebagai nilai terdekatnya.

Kasus semacam ini tidak akan bisa kita selesaikan dengan menggunakan fungsi-fungsi Pencarian semisal dengan fungsi Lookup, Fungsi VLookup atau Fungsi HLookup biasa.

Dari contoh kasus sederhana di atas bagaimanakah solusi yang bisa kita terapkan pada file excel untuk mendapatkan nilai terdekat tersebut?


CARA MENCARI NILAI TERDEKAT DI EXCEL DENGAN RUMUS EXCEL BIASA


Cara pertama yang bisa kita lakukan untuk menghasilkan nilai terdekat dengan excel adalah menggunakan rumus excel biasa. Untuk cara pertama ini kita membutuhkan rumus atau kolom bantu yang berisi selisih nilai masing-masing sel dengan nilai yang sedang kita cari nilai terdekatnya.

Mencari Nilai Terdekat Dengan Rumus Excel

Pada contoh ini rumus bantuan ini saya letakkan pada kolom B dengan rumus excel pada sel B2:

=ABS(A2-$F$2)

kemudian copy-kan rumus tersebut pada sel-sel lain dibawahnya.

Pada rumus ini fungsi ABS digunakan untuk menghilangkan tanda minus dari hasil pengurangan setiap sel dengan nilai yang sedang kita cari. Rumus ini bertujuan untuk mendapatkan nilai selisih absolut atau nilai jarak setiap sel pada daftar dengan nilai yang sedang kita tentukan nilai terdekatnya.

Pada kasus pencarian nilai terdekat ini rumus bantuan di atas bisa juga kita balik, sebab hasilnya akan sama saja, menjadi:

=ABS($F$2-A2)

Tanda dollar ($) pada referensi sel (F2) rumus dia atas bertujuan agar saat kita copy paste ke bawah referensi tersebut absolut sehingga tidak berubah atau tetap. Pembahasan mengenai referensi absolut ini sudah kita bahas pada tutorial excel yang lalu. Jika merasa untuk dipelajari silahkan menuju halaman berikut:

Mengenal Referensi Absolut ($) pada Microsoft Excel

Selanjutnya jika kolom dummy atau rumus bantuan tersebut sudah kita selesaikan, berikutnya kita tinggal menggunakan kolom bantu tersebut pada rumus pencarian nilai terdekat yang pada contoh ini saya letakkan pada sel E3 sebagai berikut:

=INDEX(A2:A10;MATCH(MIN(B2:B10);B2:B10;0))

Dimana Range A2:A10 adalah range pencarian data, sedangkan range B2:B10 adalah kolom dummy selisih setiap sel dengan angka yang sedang kita cari nilai terdekatnya.

Pada rumus excel di atas fungsi MATCH mencari posisi relatif dari nilai selisih terkecil yang dihasilkan oleh fungsi excel MIN. Nilai yang dihasilkan oleh fungsi MATCH ini kemudian digunakan oleh fungsi INDEX untuk menentukan nilai pada baris keberapa dari INDEX A2:A10 yang akan dihasilkan.

Penjelasan lebih lengkap tentang masing- masing fungsi yang digunakan pada rumus ini bisa anda pelajari pada halaman berikut:


Tidak adakah cara lain yang tidak perlu menggunakan kolom bantuan untuk menentukan nilai terdekat pada excel ini?

Jika anda tidak menghendaki adanya kolom bantu, cara lain adalah dengan menggunakan rumus Array yang akan kita bahas pada bagian berikutnya dibawah ini.

CARA MENEMUKAN NILAI TERDEKAT DI EXCEL DENGAN RUMUS ARRAY EXCEL


Jika anda tidak menghendaki adanya kolom dummy column yang berisi rumus bantuan agar nilai terdekat bisa ditemukan, kita bisa menggunakan rumus Array excel berikut untuk menemukan nilai terdekat sebuah angka dari daftar angka yang sudah ada.

Mencari Nilai Terdekat Dengan Rumus Array Excel

=INDEX(A2:A10;MATCH(MIN(ABS(A2:A10-D2));ABS(A2:A10-D2);0))

Karena ini rumus array jangan lupa untuk mengakhiri penulisan rumus ini dengan menekan tombol Ctrl+Shift+Enter untuk menampilkan tanda {...} seperti pada gambar.

Cara kerja rumus excel pada array ini sebenarnya mirip dengan rumus biasa sebelumnya. Hanya saja pencarian selisih terdekat pada rumus ini diolah oleh excel dalam bentuk array.

Pada rumus di atas rumus ABS(A2:A10-D2) akan meghasilkan sebuah array yang merupakan hasil pengurangan nilai pada setiap sel A2:A10 dengan angka yang sedang kita cari nilai terdekatnya (D2). Array yang dihasilkan adalah sebagai berikut:

{12;22;18;2;38;68;8;48;28}

JIka dimasukkan ke dalam rumus maka akan seperti ini:

=INDEX(A2:A10;MATCH(MIN({12;22;18;2;38;68;8;48;28});{12;22;18;2;38;68;8;48;28};0))

Selanjutnya fungsi MIN akan menghasilkan angka 2 sebagai nilai terkecil dari array tersebut.

=INDEX(A2:A10;MATCH(2;{12;22;18;2;38;68;8;48;28};0))

Oleh fungsi MATCH angka 2 ini kemudian di cari posisi relatifnya pada array tersebut. Pada Array yang dihasilkan tadi angka 2 berada pada posisi urutan ke-4.

=INDEX(A2:A10;4)

Angka 4 yang dihasilkan oleh fungsi MATCH tadi kemudian digunakan oleh fungsi INDEX sebagai informasi nomor baris pada index dari daftar angka yang akan dihasilkan. Angka inilah yang merupakan nilai terdekat dari angka yang kita cari tadi dan menghasilkan nilai angka 30.

Nah cukup mudah bukan? Bagiamana jika daftar nilai tersebut tersusun secara horisontal ke samping? Bagaimana jika nilai terdekat yang kita cari tersusun dalam beberapa kolom? bagaimana jika kita ingin mendapatkan nilai pada kolom yang sebaris dengan nilai terdekat tersebut?

Jangan banyak-banyak dulu lah. Lain waktu kita bahas beberapa pertanyaan tadi. Syukur-syukur jika anda malah sudah bisa mengobrak abrik rumus excel diatas untuk menjawab pertanyaan-pertanyaan di atas.

Salam Kelas Excel.

0 komentar