Menemukan nomor terdekat

Konten

Dalam praktiknya, sangat sering ada kasus ketika Anda dan saya perlu menemukan nilai terdekat dalam himpunan (tabel) dalam kaitannya dengan angka yang diberikan. Bisa jadi, misalnya:

Perhitungan diskon tergantung pada volume.
Perhitungan jumlah bonus tergantung pada implementasi rencana.
Perhitungan tarif pengiriman tergantung jarak.
Pemilihan wadah yang sesuai untuk barang, dll.

Selain itu, pembulatan mungkin diperlukan baik ke atas maupun ke bawah, tergantung pada situasinya.

Ada beberapa cara – jelas dan tidak begitu jelas – untuk memecahkan masalah seperti itu. Mari kita lihat mereka secara berurutan.

Untuk memulainya, mari kita bayangkan pemasok yang memberikan diskon grosir, dan persentase diskon tergantung pada jumlah barang yang dibeli. Misalnya, ketika membeli lebih dari 5 buah, diberikan diskon 2%, dan ketika membeli dari 20 buah – sudah 6%, dll.

Bagaimana cara menghitung persentase diskon dengan cepat dan indah saat memasukkan jumlah barang yang dibeli?

Metode 1: JIKA Bersarang

Metode dari seri "apa yang ada untuk dipikirkan - Anda harus melompat!". Menggunakan fungsi bersarang IF (JIKA) untuk memeriksa secara berurutan apakah nilai sel masuk ke setiap interval dan menampilkan diskon untuk rentang yang sesuai. Tetapi rumus dalam hal ini bisa menjadi sangat rumit:

Menemukan nomor terdekat

Saya pikir sudah jelas bahwa men-debug "boneka monster" seperti itu atau mencoba menambahkan beberapa kondisi baru ke dalamnya setelah beberapa waktu itu menyenangkan.

Selain itu, Microsoft Excel memiliki batas bersarang untuk fungsi IF – 7 kali di versi yang lebih lama dan 64 kali di versi yang lebih baru. Bagaimana jika Anda membutuhkan lebih banyak?

Metode 2. VLOOKUP dengan tampilan interval

Metode ini jauh lebih kompak. Untuk menghitung persentase diskon, gunakan fungsi legendaris VPR (VLOOKUP) dalam mode pencarian perkiraan:

Menemukan nomor terdekat

dimana

B4 – nilai kuantitas barang dalam transaksi pertama yang kami cari diskon
$G$4:$H$8 – tautan ke tabel diskon – tanpa “tajuk” dan dengan alamat tetap dengan tanda $.
2 — nomor urut kolom dalam tabel diskon dari mana kita ingin mendapatkan nilai diskon
BENAR – di sinilah “anjing” dikuburkan. Jika sebagai argumen fungsi terakhir VPR menentukan BOHONG (SALAH) atau 0, maka fungsi akan mencari pertandingan yang ketat di kolom kuantitas (dan dalam kasus kami ini akan memberikan kesalahan #N/A, karena tidak ada nilai 49 di tabel diskon). Tapi jika sebaliknya BOHONG menulis BENAR (BENAR) atau 1, maka fungsinya tidak akan mencari yang tepat, tetapi terkecil terdekat nilai dan akan memberi kami persentase diskon yang kami butuhkan.

Kelemahan dari metode ini adalah kebutuhan untuk mengurutkan tabel diskon dalam urutan menaik menurut kolom pertama. Jika tidak ada penyortiran seperti itu (atau dilakukan dalam urutan terbalik), maka rumus kami tidak akan berfungsi:

Menemukan nomor terdekat

Oleh karena itu, pendekatan ini hanya dapat digunakan untuk mencari nilai terkecil yang terdekat. Jika Anda perlu menemukan yang terbesar terdekat, maka Anda harus menggunakan pendekatan yang berbeda.

Metode 3. Menemukan yang terbesar terdekat menggunakan fungsi INDEX dan MATCH

Sekarang mari kita lihat masalah kita dari sisi lain. Misalkan kita menjual beberapa model pompa industri dengan berbagai kapasitas. Tabel penjualan di sebelah kiri menunjukkan daya yang dibutuhkan oleh pelanggan. Kita perlu memilih pompa dengan daya maksimum atau setara terdekat, tetapi tidak kurang dari yang dibutuhkan oleh proyek.

Fungsi VLOOKUP tidak akan membantu di sini, jadi Anda harus menggunakan analognya – banyak fungsi INDEX (INDEKS) dan LEBIH TERBUKTI (COCOK):

Menemukan nomor terdekat

Di sini, fungsi MATCH dengan argumen terakhir -1 bekerja dalam mode mencari nilai terbesar terdekat, dan fungsi INDEX kemudian mengekstrak nama model yang kita butuhkan dari kolom yang berdekatan.

Metode 4. Fungsi baru VIEW (XLOOKUP)

Jika Anda memiliki versi Office 365 dengan semua pembaruan terinstal, maka alih-alih VLOOKUP (VLOOKUP) Anda dapat menggunakan analognya – fungsi VIEW (XLOOKUP), yang telah saya analisis secara rinci:

Menemukan nomor terdekat

Sini:

B4 – nilai awal dari jumlah produk yang kami cari diskon
$G$4:$G$8 – kisaran tempat kita mencari kecocokan
$H$4:$H$8 – kisaran hasil dari mana Anda ingin mengembalikan diskon
argumen keempat (-1) mencakup pencarian angka terkecil terdekat yang kita inginkan, bukan kecocokan persis.

Keuntungan dari metode ini adalah tidak perlu mengurutkan tabel diskon dan kemampuan untuk mencari, jika perlu, tidak hanya nilai terkecil terdekat, tetapi juga nilai terbesar terdekat. Argumen terakhir dalam kasus ini adalah 1.

Namun, sayangnya, belum semua orang memiliki fitur ini – hanya pemilik Office 365 yang bahagia.

Metode 5. Power Query

Jika Anda belum terbiasa dengan add-in Power Query yang canggih dan gratis untuk Excel, maka Anda telah berada di sini. Jika Anda sudah terbiasa, maka mari kita coba menggunakannya untuk menyelesaikan masalah kita.

Mari kita lakukan beberapa pekerjaan persiapan terlebih dahulu:

Mari kita ubah tabel sumber kita menjadi dinamis (pintar) menggunakan pintasan keyboard Ctrl+T atau tim Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel).
Untuk kejelasan, mari kita beri mereka nama. Penjualan и Diskon tab Pembina (Rancangan).
Muat setiap tabel secara bergantian menjadi Power Query menggunakan tombol Dari Tabel/Rentang tab Data (Data — Dari tabel/rentang). Di versi Excel terbaru, tombol ini telah diubah namanya menjadi Dengan daun (Dari lembar).
Jika tabel memiliki nama kolom yang berbeda dengan jumlah, seperti dalam contoh kami ("Jumlah barang" dan "Kuantitas dari ..."), maka mereka harus diganti namanya di Power Query dan diberi nama yang sama.
Setelah itu, Anda dapat kembali ke Excel dengan memilih perintah di jendela editor Power Query Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup&Muat — Tutup&Muat ke…) dan kemudian opsi Buat saja koneksi (Hanya buat koneksi).
Kemudian yang paling menarik dimulai. Jika Anda memiliki pengalaman dalam Power Query, maka saya berasumsi bahwa garis pemikiran selanjutnya harus ke arah penggabungan dua tabel ini dengan kueri gabungan (gabung) ala VLOOKUP, seperti yang terjadi pada metode sebelumnya. Faktanya, kita perlu menggabungkan dalam mode tambah, yang sama sekali tidak terlihat jelas pada pandangan pertama. Pilih di tab Excel Data – Dapatkan Data – Gabungkan Permintaan – Tambahkan (Data — Dapatkan Data — Gabungkan kueri — Tambahkan) dan kemudian meja kami Penjualan и Diskon di jendela yang muncul:
Setelah mengklik OK meja kami akan direkatkan menjadi satu kesatuan – di bawah satu sama lain. Harap dicatat bahwa kolom dengan jumlah barang dalam tabel ini jatuh di bawah satu sama lain, karena. mereka memiliki nama yang sama:
Jika urutan baris asli dalam tabel penjualan penting bagi Anda, maka setelah semua transformasi selanjutnya Anda dapat memulihkannya, tambahkan kolom bernomor ke tabel kami menggunakan perintah Menambahkan Kolom – Kolom Indeks (Tambahkan kolom — Kolom indeks). Jika urutan garis tidak penting bagi Anda, maka Anda dapat melewati langkah ini.
Sekarang, dengan menggunakan daftar drop-down di header tabel, urutkan berdasarkan kolom Jumlah Menaik:
Dan trik utamanya: klik kanan pada header kolom Diskon pilih tim Mengisi (Mengisi). Sel kosong dengan nol otomatis terisi dengan nilai diskon sebelumnya:
Tetap mengembalikan urutan baris asli dengan mengurutkan berdasarkan kolom Indeks (Anda dapat menghapusnya dengan aman nanti) dan singkirkan garis yang tidak perlu dengan filter nol menurut kolom Kode transaksi: