Rumus Excel Untuk Mencari Nilai Terdekat Di Excel | Kelas Excel

23 Feb, 2021 Posting Komentar

Tutorial rumus excel untuk mendapat atau mencari nilai terdekat pada excel dari suatu daftar nilai angka.

Rumus Excel Untuk Mencari Nilai Terdekat Di Excel

Pada bimbingan sebelumnya , Kelas Excel telah membahas wacana rumus excel untuk mencari nilai tertinggi atau paling rendah dengan patokan tertentu. Kali ini kita akan menuntut ilmu excel wacana bagaimana cara mendapat atau mencari nilai terdekat dengan rumus excel.

Nilai terdekat disini tujuannya merupakan nilai angka pada daftar angka yang memiliki selisih terkecil dengan nilai yang sedang kita cari , sehingga nilai angka yang mesti didapatkan ini dapat lebih besar atau sebaliknya lebih kecil dengan nilai yang sedang kita cari.

Sebagai citra amati pola data berikut.

Cara Mencari Nilai Terdekat Pada Excel

Pada pola diatas , kita memiliki daftar angka acak antara 10-100 pada kolom A. Dari data tersebut kita ingin mendapat nilai terdekat dari angka 36 umpamanya , maka hasil yang kita kehendaki merupakan angka 40 dari daftar tersebut. Sedangkan jika nilai yang dicari merupakan 32 maka rumus excel mesti menciptakan angka 30 selaku nilai terdekatnya.

Kasus seperti ini tidak akan dapat kita tuntaskan dengan menggunakan fungsi-fungsi Pencarian semisal dengan fungsi Lookup , Fungsi VLookup atau Fungsi HLookup biasa.

Dari pola kasus sederhana di atas bagaimanakah penyelesaian yang bisa kita terapkan pada file excel untuk mendapat nilai terdekat tersebut?

Mencari Nilai Terdekat Dengan Rumus Excel Biasa

Cara pertama yang bisa kita laksanakan untuk menciptakan nilai terdekat dengan excel merupakan menggunakan rumus excel biasa. Untuk cara pertama ini kita memerlukan 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 pola ini rumus pemberian 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 menetralisir tanda minus dari hasil penghematan setiap sel dengan nilai yang sedang kita cari. Rumus ini berniat untuk mendapat nilai selisih sewenang-wenang atau nilai jarak setiap sel pada daftar dengan nilai yang sedang kita tentukan nilai terdekatnya.

Pada kasus penelusuran nilai terdekat ini rumus pemberian di atas bisa juga kita balik , alasannya balasannya akan sama saja , menjadi:

=ABS($F$2-A2)

Tanda dollar ($) pada acuan sel (F2) rumus ia atas berniat biar dikala kita copy paste ke bawah acuan tersebut sewenang-wenang sehingga tidak berubah atau tetap. Pembahasan tentang acuan sewenang-wenang ini telah kita diskusikan pada bimbingan excel yang lalu. Jika merasa untuk dipelajari silahkan menuju halaman berikut:

Mengenal Referensi Absolut ($) pada Microsoft Excel

Selanjutnya jika kolom dummy atau rumus pemberian tersebut telah kita tuntaskan , selanjutnya kita tinggal menggunakan kolom bantu tersebut pada rumus penelusuran nilai terdekat yang pada pola ini saya letakkan pada sel E3 selaku berikut:

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

Baca Juga

Dimana Range A2:A10 merupakan range penelusuran data , sedangkan range B2:B10 merupakan 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 menyeleksi nilai pada baris keberapa dari INDEX A2:A10 yang mau dihasilkan.

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

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

Jika anda tidak mengharapkan adanya kolom bantu , cara lain merupakan dengan menggunakan rumus Array yang mau kita diskusikan pada bab selanjutnya dibawah ini.

Cara Menemukan Nilai Terdekat di Excel Dengan Rumus Array

Jika anda tidak mengharapkan adanya kolom dummy column yang berisi rumus pemberian biar nilai terdekat dapat didapatkan , kita dapat menggunakan rumus Array excel berikut untuk mendapatkan nilai terdekat suatu angka dari daftar angka yang telah ada.

Mencari Nilai Terdekat Dengan Rumus Array Excel

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 menyelesaikan penulisan rumus ini dengan menekan tombol Ctrl + Shift + Enter untuk memperlihatkan tanda {...} menyerupai pada gambar.

Cara kerja rumus excel pada array ini bekerjsama menyerupai dengan rumus biasa sebelumnya. Hanya saja penelusuran selisih terdekat pada rumus ini dimasak oleh excel dalam bentuk array.

Pada rumus di atas rumus ABS(A2:A10-D2) akan meghasilkan suatu array yang merupakan hasil penghematan nilai pada setiap sel A2:A10 dengan angka yang sedang kita cari nilai terdekatnya (D2). Array yang dihasilkan merupakan selaku 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 menciptakan angka 2 selaku 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 selaku isu nomor baris pada index dari daftar angka yang mau dihasilkan. Angka inilah yang merupakan nilai terdekat dari angka yang kita cari tadi dan menciptakan 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 mendapat nilai pada kolom yang sebaris dengan nilai terdekat tersebut?

Jangan banyak-banyak dahulu lah. Lain waktu kita diskusikan beberapa pertanyaan tadi. Syukur-syukur jika anda malah telah dapat mengobrak abrik rumus excel diatas untuk menjawab pertanyaan-pertanyaan di atas.

Salam Kelas Excel.