Formula Excel Untuk Mencari Nilai Terdekat Di Excel
Tutorial rumus excel untuk mendapatkan atau mencari nilai terdekat pada excel dari suatu daftar nilai angka.
Pada bimbingan sebelumnya , Kelas Excel sudah membahas perihal rumus excel untuk mencari nilai tertinggi atau paling rendah dengan patokan tertentu. Kali ini kita akan mencar ilmu excel ihwal bagaimana cara menerima atau mencari nilai terdekat dengan rumus excel.
Nilai terdekat disini maksudnya yaitu nilai angka pada daftar angka yang mempunyai selisih terkecil dengan nilai yang sedang kita cari , sehingga nilai angka yang mesti didapatkan ini bisa lebih besar atau sebaliknya lebih kecil dengan nilai yang sedang kita cari.
Sebagai citra amati pola data berikut.
Pada teladan diatas , kita mempunyai daftar angka acak antara 10-100 pada kolom A. Dari data tersebut kita ingin menerima 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 membuat angka 30 sebagai nilai terdekatnya.
Kasus seperti ini tidak akan bisa kita selesaikan dengan memakai fungsi-fungsi Pencarian semisal dengan fungsi Lookup , Fungsi VLookup atau Fungsi HLookup biasa.
Dari pola duduk perkara sederhana di atas bagaimanakah penyelesaian yang bisa kita terapkan pada file excel untuk mendapatkan nilai terdekat tersebut?
Mencari Nilai Terdekat Dengan Rumus Excel Biasa
Cara pertama yang bisa kita kerjakan untuk menghasilkan nilai terdekat dengan excel yakni memakai 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.
Pada contoh ini rumus santunan ini aku 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 menetralisir tanda minus dari hasil pengurangan setiap sel dengan nilai yang sedang kita cari. Rumus ini berniat untuk memperoleh nilai selisih otoriter atau nilai jarak setiap sel pada daftar dengan nilai yang sedang kita tentukan nilai terdekatnya.
Pada masalah pencarian nilai terdekat ini rumus pertolongan di atas dapat juga kita balik , karena kesannya akan sama saja , menjadi:
=ABS($F$2-A2)Tanda dollar ($) pada rujukan sel (F2) rumus beliau atas bermaksud semoga ketika kita copy paste ke bawah tumpuan tersebut absolut sehingga tidak berganti atau tetap. Pembahasan perihal acuan otoriter ini telah kita diskusikan pada bimbingan excel yang kemudian. Jika merasa untuk dipelajari silahkan menuju halaman berikut:
Mengenal Referensi Absolut ($) pada Microsoft Excel
Selanjutnya jikalau kolom dummy atau rumus pemberian tersebut sudah kita selesaikan , selanjutnya kita tinggal memakai kolom bantu tersebut pada rumus penelusuran nilai terdekat yang pada pola 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 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 lalu digunakan oleh fungsi INDEX untuk menentukan nilai pada baris keberapa dari INDEX A2:A10 yang mau dihasilkan.
Penjelasan lebih lengkap wacana masing- masing fungsi yang dipakai pada rumus ini bisa anda pelajari pada halaman berikut:
Tidak adakah cara lain yang tidak perlu memakai kolom sumbangan untuk menentukan nilai terdekat pada excel ini?
Jika anda tidak menginginkan adanya kolom bantu , cara lain yakni dengan memakai rumus Array yang akan kita diskusikan pada cuilan berikutnya dibawah ini.
Cara Menemukan Nilai Terdekat di Excel Dengan Rumus Array
Jika anda tidak menginginkan adanya kolom dummy column yang berisi rumus sumbangan semoga nilai terdekat bisa didapatkan , kita bisa menggunakan rumus Array excel berikut untuk memperoleh nilai terdekat suatu 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 menuntaskan penulisan rumus ini dengan menekan tombol Ctrl + Shift + Enter untuk memberikan tanda … seperti pada gambar.
Cara kerja rumus excel pada array ini bahwasanya seperti 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 penghematan nilai pada setiap sel A2:A10 dengan angka yang sedang kita cari nilai terdekatnya (D2). Array yang dihasilkan yakni selaku berikut:
12;22;18;2;38;68;8;48;28Jika dimasukkan ke dalam rumus maka akan mirip 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 lalu 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 selaku gosip 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 jikalau daftar nilai tersebut tersusun secara horisontal ke samping? Bagaimana kalau nilai terdekat yang kita cari tersusun dalam beberapa kolom? bagaimana kalau kita ingin menemukan nilai pada kolom yang sebaris dengan nilai terdekat tersebut?
Jangan banyak-banyak dulu lah. Lain waktu kita diskusikan beberapa pertanyaan tadi. Syukur-syukur bila anda malah telah mampu mengobrak abrik rumus excel diatas untuk menjawab pertanyaan-pertanyaan di atas.
Salam Kelas Excel.>> Gabung Telegram Channel <<