Pencocokan Perkiraan untuk Tabel Bertingkat

Tabel tingkatan mengelompokkan nilai kontinu ke dalam beberapa rentang. Contohnya mencakup lapisan pajak, biaya pengiriman berdasarkan berat, diskon volume, dan nilai huruf berdasarkan skor.

Anda tidak memerlukan satu baris untuk setiap nilai yang mungkin, melainkan hanya ambang awal setiap rentang. Skor 87 tidak memiliki entri yang persis sama, tetapi termasuk dalam rentang yang dimulai dari 80.

Di sinilah pencocokan perkiraan sangat berguna: metode ini menemukan rentang yang tepat tanpa mengharuskan kecocokan persis.

Sejauh ini kita menggunakan MATCH(value, range, 0) untuk pencocokan persis. Argumen ketiga 0 berarti "temukan nilai ini secara tepat atau kembalikan #N/A".

Untuk tingkatan, kita menggunakan jenis pencocokan 1. Jenis ini menemukan nilai terbesar yang kurang dari atau sama dengan nilai pencarian. Begitulah perilaku yang tepat untuk pencarian rentang.

Ada satu aturan penting: dengan jenis pencocokan 1, daftar ambang harus diurutkan dalam urutan menaik.

Bayangkan sebuah tabel penilaian. Kolom E berisi ambang bawah yang diurutkan menaik: 0, 60, 70, 80, 90. Kolom F berisi label: F, D, C, B, A.

Skor 0 hingga 59 mendapat F, 60 hingga 69 mendapat D, dan seterusnya. Kita hanya menyimpan awal setiap rentang, bukan setiap skor.

Tujuan kita: berdasarkan skor di G1, mengembalikan nilai hurufnya.

Gunakan MATCH perkiraan untuk menemukan rentang tempat suatu skor berada. MATCH(G1, E2:E6, 1) dengan skor 87 mencari ambang terbesar yang kurang dari atau sama dengan 87.

Ambangnya adalah 0, 60, 70, 80, 90. Nilai terbesar yang tidak melebihi 87 adalah 80, yang berada pada posisi 4. Jadi, MATCH mengembalikan 4.

Posisi tersebut menunjuk ke rentang yang benar meskipun 87 sendiri tidak ada dalam daftar.

Sekarang masukkan posisi tersebut ke INDEX pada kolom label F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) mengambil posisi 4 dan mengembalikan label keempat, "B".

Jadi, skor 87 dipetakan dengan benar ke nilai B. Ubah G1 menjadi 95 dan MATCH mengembalikan 5, sehingga hasilnya "A"; ubah menjadi 55 dan MATCH mengembalikan 1, sehingga hasilnya "F".

MATCH perkiraan (jenis 1) memerlukan urutan menaik pada rentang pencarian. MATCH mengasumsikan data terus meningkat dan berhenti segera setelah melewati nilai pencarian.

Jika ambang Anda tidak berurutan, MATCH mungkin berhenti terlalu cepat dan mengembalikan posisi yang salah tanpa peringatan kesalahan. Selalu urutkan kolom ambang dari yang terkecil ke terbesar sebelum mengandalkan pencarian tingkatan.

XLOOKUP juga dapat melakukan pencocokan perkiraan. Argumen kelimanya, yaitu mode pencocokan, menerima -1 untuk "pencocokan persis atau item berikutnya yang lebih kecil", yang sangat cocok untuk tabel tingkatan.

Fungsi ini menemukan ambang terbesar yang kurang dari atau sama dengan G1 dan mengembalikan label yang sesuai tanpa memerlukan INDEX. Untuk pencarian rentang, fungsi ini sering kali lebih mudah dibaca daripada INDEX-MATCH.

Sekarang kita gunakan contoh diskon volume. Ambang di E (jumlah yang dipesan): 0, 10, 50, 100. Diskon di F: 0%, 5%, 10%, 15%.

• Pesanan 7: ambang terbesar yang kurang dari atau sama dengan 7 adalah 0, posisi 1, sehingga hasilnya 0%.
• Pesanan 60: ambang terbesar yang kurang dari atau sama dengan 60 adalah 50, posisi 3, sehingga hasilnya 10%.
• Pesanan 200: ambang terbesar yang kurang dari atau sama dengan 200 adalah 100, posisi 4, sehingga hasilnya 15%.

Satu rumus menangani semua jumlah.

Bagaimana jika suatu nilai lebih kecil daripada semua ambang? Dengan MATCH perkiraan, tidak ada nilai yang kurang dari atau sama dengannya, sehingga MATCH mengembalikan #N/A.

Untuk menghindarinya, pastikan ambang pertama mencakup batas bawah (biasanya 0), atau bungkus rumus dengan IFERROR untuk menampilkan pesan yang jelas ketika masukan berada di luar rentang.

Perhatikan jebakan umum pada tabel tingkatan berikut:

• Ambang tidak berurutan: penyebab nomor satu dari hasil yang salah tanpa peringatan.
• Menggunakan jenis pencocokan 0: memaksa pencocokan persis dan mengembalikan #N/A untuk nilai apa pun yang berada di antaranya.
• Menyimpan akhir rentang, bukan awalnya: MATCH jenis 1 mengharapkan batas bawah setiap rentang, bukan batas atasnya.
• Ambang berupa teks: angka yang disimpan sebagai teks merusak perbandingan; simpan angka dalam format numerik.

Anda dapat menggabungkan pencocokan perkiraan dengan teknik dua arah. Bayangkan biaya pengiriman berdasarkan rentang berat (baris) dan rentang zona (kolom).

Gunakan satu MATCH perkiraan (jenis 1) untuk menemukan baris berat dan satu lagi untuk menemukan kolom zona, lalu masukkan keduanya ke INDEX. Karena kedua sumbu berisi ambang yang telah diurutkan, setiap MATCH berhenti pada rentang yang benar.

Dengan cara ini, INDEX-MATCH-MATCH berpadu dengan logika tingkatan untuk membuat tabel tarif yang lebih lengkap.

Untuk pencarian tingkatan dan rentang:

• Simpan ambang bawah setiap rentang dan urutkan dalam urutan menaik.
• Gunakan MATCH(value, thresholds, 1) untuk menemukan posisi rentang (nilai terbesar yang kurang dari atau sama dengan masukan).
• Bungkus dengan INDEX(labels, ...) untuk mengembalikan rentang, atau gunakan XLOOKUP(..., -1) untuk mendapatkan hasil yang sama.

Pastikan batas bawah tercakup dengan ambang 0 atau gunakan IFERROR untuk masukan di luar rentang, dan jangan pernah membiarkan ambang tidak berurutan.