Rumus Excel Untuk Mencari Nilai Terdekat Di Excel

Pada tutorial sebelumnya, sudah membahas wacana rumus excel untuk mencari nilai tertinggi atau terendah dengan kriteria tertentu. Kali ini kita akan berguru excel wacana bagaimana cara mendapat atau mencari nilai terdekat dengan rumus excel.

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

Sebagai citra perhatikan teladan data berikut.



Pada teladan diatas, kita mempunyai daftar angka acak antara 10-100 pada kolom A. Dari data tersebut kita ingin mendapat nilai terdekat dari angka 36 misalnya, maka hasil yang kita harapkan yakni angka 40 dari daftar tersebut. Sedangkan bila nilai yang dicari yakni 32 maka rumus excel harus menghasilkan angka 30 sebagai nilai terdekatnya.

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

Dari teladan kasus sederhana di atas bagaimanakah solusi yang dapat kita terapkan pada file excel untuk mendapat 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 yang dapat kita lakukan untuk menghasilkan nilai terdekat dengan excel yakni memakai 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.



Pada teladan ini rumus tunjangan 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 dipakai untuk menghilangkan tanda minus dari hasil pengurangan setiap sel dengan nilai yang sedang kita cari. Rumus ini bertujuan untuk mendapat nilai selisih diktatorial atau nilai jarak setiap sel pada daftar dengan nilai yang sedang kita tentukan nilai terdekatnya.

Pada kasus pencarian nilai terdekat ini rumus tunjangan di atas dapat juga kita balik, alasannya yakni akibatnya akan sama saja, menjadi:

=ABS($F$2-A2)

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

Mengenal Referensi Absolut ($) pada Microsoft Excel

Selanjutnya bila kolom dummy atau rumus tunjangan tersebut sudah kita selesaikan, berikutnya kita tinggal memakai kolom bantu tersebut pada rumus pencarian nilai terdekat yang pada teladan ini saya letakkan pada sel E3 sebagai berikut:

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

Dimana Range A2:A10 yakni range pencarian data, sedangkan range B2:B10 yakni 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 dipakai oleh fungsi INDEX untuk memilih nilai pada baris keberapa dari INDEX A2:A10 yang akan dihasilkan.

Penjelasan lebih lengkap wacana masing- masing fungsi yang dipakai pada rumus ini dapat anda pelajari pada halaman berikut:

• Fungsi INDEX
• Fungsi MATCH
• Fungsi MIN

Tidak adakah cara lain yang tidak perlu memakai kolom tunjangan untuk memilih nilai terdekat pada excel ini?

Jika anda tidak menghendaki adanya kolom bantu, cara lain yakni dengan memakai rumus Array yang akan kita bahas pada bab berikutnya dibawah ini.

Cara Menemukan Nilai Terdekat di Excel Dengan Rumus Array

Jika anda tidak menghendaki adanya kolom dummy column yang berisi rumus tunjangan biar nilai terdekat dapat ditemukan, kita dapat memakai rumus Array excel berikut untuk menemukan nilai terdekat sebuah angka dari daftar angka yang sudah 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 {...} menyerupai pada gambar.

Cara kerja rumus excel pada array ini bahwasanya menyerupai 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 yakni sebagai berikut:

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

Jika dimasukkan ke dalam rumus maka akan menyerupai 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 dipakai oleh fungsi INDEX sebagai gosip 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 gampang bukan? Bagiamana bila daftar nilai tersebut tersusun secara horisontal ke samping? Bagaimana bila nilai terdekat yang kita cari tersusun dalam beberapa kolom? bagaimana bila kita ingin mendapat nilai pada kolom yang sebaris dengan nilai terdekat tersebut?

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

Salam .

Location:

Share :