Dalam video ini, kita akan melihat cara menyorot perkiraan pencarian kecocokan dengan pemformatan bersyarat.
Di sini kami memiliki tabel pencarian sederhana yang menunjukkan biaya material untuk berbagai ketinggian dan lebar. Rumus di K8 menggunakan fungsi INDEX dan MATCH untuk mengambil biaya yang benar berdasarkan nilai lebar dan tinggi yang dimasukkan di K6 dan K7.
Perhatikan bahwa pencarian didasarkan pada perkiraan kecocokan. Karena nilai berada dalam urutan menaik, MATCH memeriksa nilai hingga nilai yang lebih besar tercapai, lalu mundur dan mengembalikan posisi sebelumnya.
Mari buat aturan pemformatan bersyarat untuk menyorot baris dan kolom yang cocok.
Seperti biasa dengan pemformatan bersyarat yang lebih rumit, saya sarankan Anda bekerja dengan rumus dummy terlebih dahulu, lalu mentransfer rumus yang berfungsi langsung ke aturan pemformatan bersyarat. Dengan cara ini, Anda dapat menggunakan semua alat Excel saat Anda men-debug rumus, yang akan menghemat banyak waktu.
Saya akan menyiapkan rumus untuk lebar terlebih dahulu. Kita perlu mengembalikan TRUE untuk setiap sel di baris 7, di mana lebar yang cocok adalah 200.
Ini berarti kita memulai rumus kita dengan $ B5 =, dan kita perlu mengunci kolomnya.
= $ B5 =
Sekarang, kita tidak bisa mencari 275 di kolom lebar, karena tidak ada. Sebagai gantinya, kita memerlukan kecocokan yang menemukan 200, seperti rumus pencarian kita.
Cara termudah untuk melakukannya adalah dengan menggunakan fungsi LOOKUP. LOOKUP secara otomatis melakukan perkiraan kecocokan, dan, alih-alih mengembalikan posisi seperti MATCH, LOOKUP mengembalikan nilai kecocokan yang sebenarnya. Jadi, kita bisa menulis:
$ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12)
Dengan lebar masukan untuk nilai pencarian dan semua lebar dalam tabel untuk vektor hasil.
Jika saya menggunakan F9, Anda dapat melihat nilai LOOKUP kembali.
Sekarang ketika saya memasukkan rumus ke seberang tabel, kita mendapatkan TRUE untuk setiap sel di baris dengan lebar 200.
Sekarang kita perlu memperluas rumus agar sesuai dengan kolom tinggi. Untuk melakukan ini, saya akan menambahkan fungsi OR, lalu rumus kedua untuk mencocokkan ketinggian.
Kami akan memulai rumus dengan cara yang sama, tetapi kali ini kami perlu mengunci baris:
= B $ 5
Kemudian kita menggunakan fungsi LOOKUP lagi dengan ketinggian untuk nilai pencarian dan semua ketinggian dalam tabel sebagai vektor hasil.
= ATAU ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Saat saya menyalin rumus ke seberang tabel, kami mendapatkan TRUE untuk setiap sel di kolom yang cocok dan setiap sel di baris yang cocok - yang kami butuhkan untuk pemformatan bersyarat.
Saya cukup menyalin rumus di sel kiri atas dengan tepat, dan membuat aturan baru.
Sekarang jika saya mengubah lebar atau tinggi, penyorotan berfungsi seperti yang diharapkan.
Terakhir, jika Anda hanya ingin menyorot nilai pencarian itu sendiri, itu adalah perubahan sederhana. Cukup edit rumus dan ganti fungsi ATAU dengan fungsi DAN.
= DAN ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Kursus
Pemformatan BersyaratPintasan terkait
Masukkan data yang sama dalam beberapa sel Ctrl + Enter ⌃ + Return Tampilkan kotak dialog Tempel Spesial Ctrl + Alt + V ⌘ + ⌃ + V Alihkan referensi absolut dan relatif F4 ⌘ + T
