Padanan Tepat berbanding Anggaran

Dengan jadual harga kami (A100 Epal 0.50, B200 Pisang 0.30, C300 Ceri 1.20), carian tepat bagi kod yang wujud berfungsi dengan sempurna:

Mengembalikan 1.20. Namun, jika anda mencari kod yang tidak wujud, seperti "Z999", anda mendapat #N/A. Ralat itu sebenarnya berguna: ralat tersebut memberitahu anda bahawa item itu benar-benar tiada, bukannya mengembalikan item bersebelahan yang salah.

Padanan anggaran mencari nilai terbesar yang kurang daripada atau sama dengan nilai carian anda. Padanan ini direka untuk julat dan peringkat, bukan ID yang tepat.

Kegunaan klasiknya ialah jadual peringkat: kurungan cukai, julat kos penghantaran, sempadan gred atau diskaun kuantiti, apabila sesuatu nilai berada di antara dua ambang.

Satu peraturan penting berkaitan TRUE akan diterangkan dalam bahagian seterusnya.

Untuk padanan anggaran berfungsi, lajur pertama mesti diisih dalam tertib menaik (paling kecil ke paling besar). VLOOKUP bergerak ke bawah lajur dan berhenti pada nilai terakhir yang tidak melebihi nilai carian anda.

Jika lajur tidak diisih, TRUE mengembalikan jawapan yang salah dan tidak menentu tanpa sebarang ralat. Kegagalan senyap ini menyebabkan ramai orang mengelakkan TRUE kecuali mereka benar-benar memerlukan pembahagian mengikut julat.

Andaikan jadual penggredan berada dalam A1:B5, diisih menaik mengikut markah minimum:

• 0 = F
• 60 = D
• 70 = C
• 80 = B
• 90 = A

Markah 76 sepatutnya mengembalikan C kerana 76 berada dalam julat 70-79. Formula menggunakan TRUE supaya ia mencari ambang tertinggi yang tidak melebihi 76:

Dengan nilai carian 76 dan TRUE, VLOOKUP membaca ambang yang telah diisih: 0, 60, 70, 90... Ia membandingkan setiap nilai.

• 0 kurang daripada atau sama dengan 76: teruskan
• 60 kurang daripada atau sama dengan 76: teruskan
• 70 kurang daripada atau sama dengan 76: teruskan
• 90 lebih besar daripada 76: berhenti

Ia berundur ke baris sah terakhir (70) dan mengembalikan julatnya: C. Padanan anggaran sebenarnya ialah carian antara ambang.

Jika anda meninggalkan argumen keempat sepenuhnya, VLOOKUP menggunakan TRUE secara lalai (anggaran). Hal ini mengejutkan ramai orang yang menjangkakan tingkah laku padanan tepat.

Formula seperti =VLOOKUP(A2, Data!A:B, 2) pada senarai yang tidak diisih boleh mengembalikan nilai yang salah secara senyap. Amalan selamat: sentiasa taip FALSE kecuali anda sengaja melakukan padanan julat pada jadual yang telah diisih.

Berikut ialah perbezaannya secara ringkas:

• FALSE / tepat: jadual tidak perlu diisih, nilai yang tiada mengembalikan #N/A, terbaik untuk ID dan kod
• TRUE / anggaran: jadual mesti diisih menaik, tidak pernah mengembalikan #N/A untuk nilai dalam julat, terbaik untuk peringkat dan julat

Pilih berdasarkan soalan yang sedang anda jawab: "adakah item tepat ini wujud?" menggunakan FALSE; "dalam julat manakah nilai ini berada?" menggunakan TRUE.

Berat dalam D2 memerlukan kos penghantaran daripada jadual julat yang telah diisih dalam A2:B6 (ambang 0, 1, 5, 10, 20 kg). Padanan anggaran memilih peringkat yang betul:

Jika D2 ialah 7, nilainya berada dalam julat 5 kg dan mengembalikan kos bagi julat tersebut. Tukar berat dan peringkat itu dikemas kini serta-merta tanpa memerlukan senarai setiap berat yang mungkin.

Terdapat juga aspek prestasi yang lebih halus. Pada jadual terisih yang sangat besar, padanan anggaran (TRUE) boleh menjadi lebih pantas kerana hamparan boleh melangkau dengan cepat antara nilai yang telah diisih, bukannya mengimbas setiap baris.

Namun, kelajuan tidak pernah mengatasi ketepatan. Jika data anda tidak diisih atau anda memerlukan ID yang tepat, sentiasa pilih FALSE. Jawapan salah yang pantas lebih buruk daripada jawapan betul yang sedikit lebih perlahan. Untuk hamparan moden dan saiz jadual biasa, perbezaannya jarang ketara, jadi gunakan FALSE sebagai pilihan lalai demi keselamatan.

Kesimpulan utama:

• FALSE = padanan tepat, jadual yang tidak diisih tidak menjadi masalah, nilai yang tiada mengembalikan #N/A
• TRUE = padanan anggaran, lajur pertama mesti diisih menaik, mencari nilai terbesar yang tidak melebihi sasaran anda
• Meninggalkan argumen menjadikannya TRUE secara lalai, jadi sentiasa nyatakan argumen ini
• Gunakan padanan tepat untuk ID dan kod; gunakan padanan anggaran untuk peringkat dan julat

Seterusnya, anda akan menukar arah dan mencari merentasi baris dengan HLOOKUP.