Rumus Excel Untuk Mencari Nilai Terdekat Di Excel

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

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

Sebagai gambaran perhatikan contoh data brkut.



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 yg kita harapkan adalah angka 40 dari daftar tersebut. Se&gkan jika nilai yg dicari adalah 32 maka rumus excel harus menghasilkan angka 30 sbgai 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 yg bisa kita terapkan pada file excel untuk mendapatkan nilai terdekat tersebut?

1. Mencari Nilai Terdekat Dengan Rumus Excel Biasa
2. Mencari Nilai Terdekat Dengan Rumus Array Excel

Mencari Nilai Terdekat Dengan Rumus Excel Biasa

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



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 yg se&g kita cari. Rumus ini bertujuan untuk mendapatkan nilai selisih absolut atau nilai jarak setiap sel pada daftar dengan nilai yg se&g 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 sdh kita bahas pada tutorial excel yg lalu. Jika merasa untuk dipelajari slahkan menuju halaman brkut:

Mengenal Referensi Absolut ($) pada Microsoft Excel

Selanjutnya jika kolom dummy atau rumus bantuan tersebut sdh kita selesaikan, brkutnya kita tinggal menggunakan kolom bantu tersebut pada rumus pencarian nilai terdekat yg pada contoh ini saya letakkan pada sel E3 sbgai brkut:

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

Dimana Range A2:A10 adalah range pencarian data, se&gkan range B2:B10 adalah kolom dummy selisih setiap sel dengan angka yg se&g kita cari nilai terdekatnya.

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

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

• Fungsi INDEX
• Fungsi MATCH
• Fungsi MIN

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

Jika anda tidak menghendaki a&ya kolom bantu, cara lain adalah dengan menggunakan rumus Array yg akan kita bahas pada bagian brkutnya dibawah ini.

Cara Menemukan Nilai Terdekat di Excel Dengan Rumus Array

Jika anda tidak menghendaki a&ya kolom dummy column yg berisi rumus bantuan agar nilai terdekat bisa ditemukan, kita bisa menggunakan rumus Array excel brkut untuk menemukan nilai terdekat sebuah angka dari daftar angka yg sdh ada.

=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 {...} sprti 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 dlm bentuk array.

Pada rumus di atas rumus ABS(A2:A10-D2) akan meghasilkan sebuah array yg adalah hasil pengurangan nilai pada setiap sel A2:A10 dengan angka yg se&g kita cari nilai terdekatnya (D2). Array yg dihasilkan adalah sbgai brkut:

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

Jika dimasukkan ke dlm rumus maka akan sprti 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 sbgai 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 yg dihasilkan tadi angka 2 berada pada posisi urutan ke-4.

=INDEX(A2:A10;4)

Angka 4 yg dihasilkan oleh fungsi MATCH tadi kemudian digunakan oleh fungsi INDEX sbgai informasi nomor baris pada index dari daftar angka yg akan dihasilkan. Angka inilah yg adalah nilai terdekat dari angka yg kita cari tadi & menghasilkan nilai angka 30.

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

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

Salam .
Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: