Fungsi Excel boleh menjadi sangat berkuasa jika anda terbiasa menggabungkan formula yang berbeza.Apa yang pernah kelihatan mustahil tiba-tiba boleh menjadi seperti permainan kanak-kanak.
Satu contoh sedemikian ialah mencari nilai terdekat dalam set data Excel.
Terdapat beberapa fungsi carian berguna dalam Excel (seperti VLOOKUP dan INDEX MATCH) yang boleh mencari yang paling hampir值(seperti yang ditunjukkan dalam contoh di bawah).
Tetapi bahagian yang terbaik ialah anda boleh menggabungkan fungsi carian ini dengan fungsi Excel lain untuk melakukan lebih banyak lagi (termasuk mencari padanan paling hampir dengan nilai carian dalam senarai yang tidak diisih).
Dalam tutorial ini, saya akan menunjukkan kepada anda cara menggunakan formula carian untuk mencari padanan paling hampir dengan nilai carian dalam Excel.
Terdapat banyak senario berbeza di mana anda perlu mencari padanan terdekat (atau nilai padanan terdekat).
Berikut ialah contoh yang akan saya kupas dalam artikel ini:
- Cari kadar komisen berdasarkan jualan
- Cari calon terbaik (berdasarkan pengalaman terdekat)
- Cari tarikh acara seterusnya
Mari mulakan!
Contents [show]
Cari kadar komisen (cari nilai jualan terdekat)
Katakan anda mempunyai set data seperti di bawah di mana anda ingin mencari kadar komisen untuk semua jurujual.
Komisen diagihkan berdasarkan nilai jualan.Ini dikira menggunakan jadual di sebelah kanan.
Sebagai contoh, jika jumlah jualan jurujual ialah 5000, komisen ialah 0%, dan jika jumlah jualannya ialah 15000, komisen ialah 5%.
Untuk mendapatkan kadar komisen, anda perlu mencari julat jualan terdekat yang berada di bawah nilai jualan.Sebagai contoh, untuk 15000 jualan, komisen ialah 10,000 (atau 5%), dan untuk 25000 jualan, kadar komisen ialah 20,000 (atau 7%).
Untuk mencari nilai jualan terdekat dan mendapatkan kadar komisen, anda boleh menggunakan pemadanan anggaran dalam VLOOKUP.
Formula berikut boleh melakukan ini:
=VLOOKUP(B2,$E$2:$F$6,2,1)
Ambil perhatian bahawa dalam formula ini, parameter terakhir ialah 1, yang memberitahu formula untuk menggunakan carian anggaran.Ini bermakna formula akan melalui nilai jualan dalam lajur E dan mencari nilai betul-betul di bawah nilai carian.
Formula VLOOKUP kemudiannya akan memberikan kadar komisen untuk nilai tersebut.
注意: Untuk melakukan ini, anda perlu mengisih data dalam tertib menaik.
Cari calon terbaik (berdasarkan pengalaman terdekat)
Dalam contoh di atas, data perlu diisih dalam tertib menaik.Tetapi mungkin terdapat kes di mana data tidak diisih.
Jadi mari kita ambil contoh dan lihat bagaimana kita boleh menggunakan gabungan formula untuk mencari padanan terdekat dalam Excel.
Di bawah ialah contoh set data di mana saya perlu mencari nama pekerja yang pengalaman kerjanya paling hampir dengan nilai yang dikehendaki.Nilai jangkaan dalam kes ini ialah 2.5 tahun.
Ambil perhatian bahawa data tidak diisih.Selain itu, pengalaman terdekat mungkin lebih sedikit atau lebih daripada pengalaman tertentu.Contohnya, 2 tahun dan 3 tahun adalah sama dekat (perbezaan 0.5 tahun).
Berikut ialah formula yang memberikan kita hasilnya:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Caranya dalam formula ini ialah menukar tatasusunan carian dan nilai carian untuk mencari perbezaan empirikal terkecil antara nilai yang dikehendaki dan sebenar.
Mari kita fahami cara anda melakukannya secara manual (dan kemudian saya akan menerangkan cara formula ini berfungsi).
Apabila anda melakukan ini secara manual, anda melalui setiap sel dalam lajur B dan mencari perbezaan antara pengalaman yang anda inginkan dan pengalaman yang dimiliki.Sebaik sahaja anda mempunyai semua perbezaan, anda mencari yang terkecil dan mendapatkan nama orang itu.
Itulah yang kita lakukan dengan formula ini.
Biar saya jelaskan.
Nilai carian dalam formula MATCH ialah MIN(ABS(D2-B2:B15)).
Bahagian ini memberikan anda perbezaan minimum antara pengalaman yang diberikan (iaitu 2.5 tahun) dan semua pengalaman lain.Dalam kes ini ia mengembalikan 0.3
Ambil perhatian bahawa saya menggunakan ABS untuk memastikan saya mencari yang paling dekat (mungkin lebih atau kurang daripada pengalaman yang diberikan).
Kini, nilai minimum ini menjadi nilai carian kami.
Tatasusunan carian dalam fungsi MATCH ialah ABS(D2-$B$2:$B$15).
Ini memberi kita susunan nombor yang daripadanya 2.5 (pengalaman diperlukan) ditolak.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
Fungsi MATCH mencari kedudukan 0.3 dalam tatasusunan ini, iaitu kedudukan nama orang yang mempunyai pengalaman paling hampir.
Fungsi INDEX kemudian menggunakan nombor kerja untuk mengembalikan nama orang itu.
Nota: Jika terdapat berbilang calon dengan pengalaman minimum yang sama, formula di atas akan memberikan nama pekerja pertama yang sepadan.
Cari tarikh acara seterusnya
Berikut ialah satu lagi contoh di mana anda boleh menggunakan formula carian untuk mencari tarikh acara seterusnya berdasarkan tarikh semasa.
Di bawah ialah set data tempat saya mempunyai nama acara dan tarikh acara.
Apa yang saya mahukan ialah nama acara seterusnya dan tarikh acara acara akan datang ini.
Berikut ialah formula yang memberikan nama acara yang akan datang:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Formula berikut akan memberikan tarikh acara yang akan datang:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Biar saya terangkan bagaimana formula ini berfungsi.
Untuk mendapatkan tarikh acara, fungsi MATCH melihat dalam lajur B untuk tarikh semasa.Dalam kes ini, kami tidak mencari padanan tepat, tetapi padanan anggaran.Oleh itu, parameter terakhir fungsi MATCH ialah 1 (ia mendapati nilai terbesar kurang daripada atau sama dengan nilai carian).
Jadi fungsi MATCH akan mengembalikan kedudukan sel yang tarikhnya kurang daripada atau sama dengan tarikh semasa.Jadi dalam kes ini acara seterusnya akan berada dalam sel seterusnya (memandangkan senarai disusun dalam tertib menaik).
Jadi, untuk mendapatkan tarikh acara yang akan datang, cuma tambah satu pada kedudukan sel yang dikembalikan oleh fungsi MATCH dan ia akan memberikan anda kedudukan sel tarikh acara seterusnya.
Nilai ini kemudiannya diberikan oleh fungsi INDEX.
Untuk mendapatkan nama acara, gunakan formula yang sama dan tukar julat dalam fungsi INDEX daripada lajur B kepada lajur A.
Contoh ini terlintas di fikiran saya apabila seorang rakan membuat permintaan.Dia menyenaraikan hari lahir semua rakan/saudaranya dalam lajur dan ingin tahu hari lahir seterusnya akan datang (dan nama orang itu).
Tiga contoh di atas menunjukkan cara menggunakan formula carian untuk mencari nilai terdekat dalam Excel.
