Mencari nombor terdekat

Contents [show]

Dalam amalan, selalunya terdapat kes apabila anda dan saya perlu mencari nilai terdekat dalam set (jadual) berhubung dengan nombor tertentu. Ia boleh jadi, sebagai contoh:

Pengiraan diskaun bergantung kepada volum.
Pengiraan jumlah bonus bergantung kepada pelaksanaan pelan.
Pengiraan kadar penghantaran bergantung pada jarak.
Pemilihan bekas yang sesuai untuk barangan, dsb.

Selain itu, pembundaran mungkin diperlukan ke atas dan ke bawah, bergantung pada keadaan.

Terdapat beberapa cara - jelas dan tidak begitu jelas - untuk menyelesaikan masalah sedemikian. Mari kita lihat mereka secara berurutan.

Sebagai permulaan, mari kita bayangkan pembekal yang memberikan diskaun secara borong, dan peratusan diskaun bergantung pada kuantiti barang yang dibeli. Sebagai contoh, apabila membeli lebih daripada 5 keping, diskaun 2% diberikan, dan apabila membeli dari 20 keping - sudah 6%, dsb.

Bagaimana dengan cepat dan cantik mengira peratusan diskaun apabila memasukkan kuantiti barang yang dibeli?

Kaedah 1: IF Bersarang

Kaedah dari siri "apa yang perlu difikirkan - anda perlu melompat!". Menggunakan fungsi bersarang IF (JIKA) untuk menyemak secara berurutan jika nilai sel jatuh ke dalam setiap selang dan memaparkan diskaun untuk julat yang sepadan. Tetapi formula dalam kes ini boleh menjadi sangat rumit:

Mencari nombor terdekat

Saya rasa jelas bahawa menyahpepijat "anak patung raksasa" sedemikian atau cuba menambah beberapa syarat baharu padanya selepas beberapa ketika adalah menyeronokkan.

Selain itu, Microsoft Excel mempunyai had bersarang untuk fungsi IF – 7 kali dalam versi lama dan 64 kali dalam versi yang lebih baharu. Bagaimana jika anda memerlukan lebih banyak lagi?

Kaedah 2. VLOOKUP dengan paparan selang

Kaedah ini jauh lebih padat. Untuk mengira peratusan diskaun, gunakan fungsi legenda VPR (TAMPILAN) dalam mod carian anggaran:

Mencari nombor terdekat

di mana

B4 – nilai kuantiti barang dalam transaksi pertama yang kami cari diskaun
$G$4:$H$8 – pautan ke jadual diskaun – tanpa “pengepala” dan dengan alamat yang ditetapkan dengan tanda $.
2 — nombor ordinal lajur dalam jadual diskaun dari mana kita ingin mendapatkan nilai diskaun
TRUE – di sinilah “anjing” itu dikebumikan. Jika sebagai hujah fungsi terakhir VPR nyatakan BERBOHONG (SALAH) atau 0, maka fungsi akan mencari perlawanan ketat dalam lajur kuantiti (dan dalam kes kami ia akan memberikan ralat #N/A, kerana tiada nilai 49 dalam jadual diskaun). Tetapi jika sebaliknya BERBOHONG menulis TRUE (BENAR) atau 1, maka fungsi itu tidak akan mencari yang tepat, tetapi terkecil terdekat nilai dan akan memberi kami peratusan diskaun yang kami perlukan.

Kelemahan kaedah ini ialah keperluan untuk mengisih jadual diskaun dalam tertib menaik mengikut lajur pertama. Jika tiada pengisihan sedemikian (atau ia dilakukan dalam susunan terbalik), maka formula kami tidak akan berfungsi:

Mencari nombor terdekat

Sehubungan itu, pendekatan ini hanya boleh digunakan untuk mencari nilai terkecil yang terdekat. Jika anda perlu mencari yang terbesar terdekat, maka anda perlu menggunakan pendekatan yang berbeza.

Kaedah 3. Mencari terbesar terdekat menggunakan fungsi INDEX dan MATCH

Sekarang mari kita lihat masalah kita dari sisi lain. Katakan kami menjual beberapa model pam industri pelbagai kapasiti. Jadual jualan di sebelah kiri menunjukkan kuasa yang diperlukan oleh pelanggan. Kita perlu memilih pam dengan kuasa maksimum atau sama yang terdekat, tetapi tidak kurang daripada apa yang diperlukan oleh projek.

Fungsi VLOOKUP tidak akan membantu di sini, jadi anda perlu menggunakan analognya - sekumpulan fungsi INDEX (INDEX) dan LEBIH TERDEDAH (PERLAWANAN):

Mencari nombor terdekat

Di sini, fungsi MATCH dengan hujah terakhir -1 berfungsi dalam mod mencari nilai terbesar terdekat, dan fungsi INDEX kemudian mengekstrak nama model yang kita perlukan daripada lajur bersebelahan.

Kaedah 4. Fungsi baharu VIEW (XLOOKUP)

Jika anda mempunyai versi Office 365 dengan semua kemas kini dipasang, maka bukannya VLOOKUP (TAMPILAN) anda boleh menggunakan analognya – fungsi VIEW (XLOOKUP), yang telah saya analisis secara terperinci:

Mencari nombor terdekat

Di sini:

B4 – nilai awal kuantiti produk yang kami cari diskaun
$G$4:$G$8 – julat tempat kami mencari padanan
$H$4:$H$8 – julat hasil yang anda ingin pulangkan diskaun
hujah keempat (-1) termasuk carian nombor terkecil terdekat yang kita inginkan dan bukannya padanan tepat.

Kelebihan kaedah ini ialah tidak perlu mengisih jadual diskaun dan keupayaan untuk mencari, jika perlu, bukan sahaja terkecil terdekat, tetapi juga nilai terbesar terdekat. Hujah terakhir dalam kes ini ialah 1.

Tetapi, malangnya, tidak semua orang mempunyai ciri ini - hanya pemilik Office 365 yang gembira.

Kaedah 5. Power Query

Jika anda belum biasa dengan alat tambah Power Query yang berkuasa dan percuma sepenuhnya untuk Excel, maka anda berada di sini. Jika anda sudah biasa, mari kita cuba menggunakannya untuk menyelesaikan masalah kita.

Mari buat kerja persediaan dahulu:

Mari tukar jadual sumber kami kepada dinamik (pintar) menggunakan pintasan papan kekunci Ctrl+T atau pasukan Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual).
Untuk kejelasan, mari beri mereka nama. Jualan и Diskaun tab Pembina (Reka Bentuk).
Muatkan setiap jadual pada gilirannya ke dalam Power Query menggunakan butang Daripada Jadual/Julat tab Tarikh (Data — Daripada jadual/julat). Dalam versi Excel terkini, butang ini telah dinamakan semula kepada Dengan daun (Dari helaian).
Jika jadual mempunyai nama lajur yang berbeza dengan kuantiti, seperti dalam contoh kami ("Kuantiti barang" dan "Kuantiti daripada ..."), maka ia mesti dinamakan semula dalam Power Query dan dinamakan sama.
Selepas itu, anda boleh kembali ke Excel dengan memilih arahan dalam tetingkap editor Power Query Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke…) dan kemudian pilihan Buat sambungan sahaja (Hanya buat sambungan).
Kemudian yang paling menarik bermula. Jika anda mempunyai pengalaman dalam Power Query, maka saya mengandaikan bahawa garis pemikiran selanjutnya harus ke arah menggabungkan kedua-dua jadual ini dengan pertanyaan gabungan (gabungan) a la VLOOKUP, seperti yang berlaku dalam kaedah sebelumnya. Malah, kita perlu bergabung dalam mod tambah, yang tidak begitu jelas pada pandangan pertama. Pilih dalam tab Excel Data – Dapatkan Data – Gabungkan Permintaan – Tambah (Data — Dapatkan Data — Gabungkan pertanyaan — Tambah) dan kemudian meja kami Jualan и Diskaun dalam tetingkap yang muncul:
Selepas mengklik OK meja kami akan dilekatkan menjadi satu keseluruhan - di bawah satu sama lain. Sila ambil perhatian bahawa lajur dengan kuantiti barang dalam jadual ini jatuh di bawah satu sama lain, kerana. mereka mempunyai nama yang sama:
Jika urutan baris asal dalam jadual jualan adalah penting kepada anda, maka supaya selepas semua transformasi berikutnya anda boleh memulihkannya, tambah lajur bernombor pada jadual kami menggunakan arahan Menambah Lajur – Lajur Indeks (Tambah lajur — lajur indeks). Jika urutan baris tidak penting kepada anda, maka anda boleh melangkau langkah ini.
Sekarang, menggunakan senarai juntai bawah dalam pengepala jadual, susun mengikut lajur Kuantiti Menaik:
Dan helah utama: klik kanan pada pengepala lajur Diskaun memilih pasukan Isi - Bawah (Isi - Bawah). Sel kosong dengan sifar diisi secara automatik dengan nilai diskaun sebelumnya:
Ia kekal untuk memulihkan urutan asal baris dengan mengisih mengikut lajur indeks (anda boleh memadamnya dengan selamat kemudian) dan singkirkan garisan yang tidak perlu dengan penapis sifar mengikut lajur Kod transaksi: