Rumus Excel Untuk Mencari Nilai Terdekat Di Excel
Pada tutorial sebelumnya, Kelas Excel sudah membahas perihal rumus excel untuk mencari nilai tertinggi atau terendah dengan kriteria tertentu. Kali ini kita akan berguru excel ihwal bagaimana Tutorial mendapatkan atau mencari nilai terdekat dengan rumus excel.
Nilai terdekat disini maksudnya ialah nilai angka pada daftar angka yang memiliki selisih terkecil dengan nilai yang sedang kita cari, sehingga nilai angka yang harus ditemukan ini mampu lebih besar atau sebaliknya lebih kecil dengan nilai yang sedang kita cari.
Sebagai ilustrasi perhatikan contoh data berikut.
Pada teladan iatas, kita mempunyai 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 yaitu angka 40 dari daftar tersebut. Sedangkan kalau nilai yang dicari adalah 32 maka rumus excel harus menghasilkan angka 30 sebagai nilai terdekatnya.
Kasus semacam ini tidak akan bisa kita selesaikan dengan memakai fungsi-fungsi Pencarian semisal dengan fungsi Lookup, Fungsi VLookup atau Fungsi HLookup biasa.
Dari teladan perkara sederhana di atas bagaimanakah solusi yang bisa kita terapkan pada file excel untuk menerima nilai terdekat tersebut?
Mencari Nilai Terdekat Dengan Rumus Excel Biasa
Cara pertama yang mampu kita lakukan untuk menghasilkan nilai terdekat dengan excel yaitu 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.
Pada teladan ini rumus donasi 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 menghilangkan tanda minus dari hasil pengurangan setiap sel dengan nilai yang sedang kita cari. Rumus ini bertujuan untuk menerima 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 bantuan di atas mampu juga kita balik, karena kesudahannya akan sama saja, menjadi:
=ABS($F$2-A2)Tanda dollar ($) pada referensi sel (F2) rumus beliau atas bertujuan agar dikala kita copy paste ke bawah referensi tersebut diktatorial sehingga tidak berubah atau tetap. Pembahasan mengenai acuan sewenang-wenang ini sudah kita bahas pada tutorial excel yang kemudian. bila merasa untuk dipelajari silahkan menuju halaman berikut:
Mengenal tumpuan sewenang-wenang ($) pada Microsoft Excel
Selanjutnya jika kolom dummy atau rumus donasi tersebut sudah kita selesaikan, berikutnya kita tinggal memakai kolom bantu tersebut pada rumus pencarian nilai terdekat yang pada teladan ini aku 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 ialah 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 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 donasi untuk menentukan nilai terdekat pada excel ini?
Jika anda tidak menghendaki adanya kolom bantu, Cara lain yaitu dengan memakai rumus Array yang akan kita bahas pada kepingan berikutnya dibawah ini.
Cara Menemukan Nilai Terdekat di Excel Dengan Rumus Array
Jika anda tidak menghendaki adanya kolom dummy column yang berisi rumus bantuan agar nilai terdekat bisa ditemukan, kita mampu 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 ... seperti pada gambar.
Cara kerja rumus excel pada array ini bekerjsama 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;28Jika dimasukkan ke dalam rumus maka akan seolah-olah 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 praktis bukan? Bagiamana bila daftar nilai tersebut tersusun secara horisontal ke samping? Bagaimana jika nilai terdekat yang kita cari tersusun dalam beberapa kolom? bagaimana jikalau 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 jikalau anda malah sudah bisa mengobrak abrik rumus excel diatas untuk menjawab pertanyaan-pertanyaan di atas.
Salam Kelas Excel.
Komentar
Posting Komentar