Dalam video ini, kita akan melihat bagaimana untuk menyoroti pencarian pencocokan anggaran dengan format bersyarat.
Di sini kita mempunyai jadual carian ringkas yang menunjukkan kos bahan untuk pelbagai ketinggian dan lebar. Rumus dalam K8 menggunakan fungsi INDEX dan MATCH untuk mendapatkan kos yang betul berdasarkan nilai lebar dan tinggi yang dimasukkan dalam K6 dan K7.
Perhatikan bahawa pencarian dibuat berdasarkan padanan anggaran. Oleh kerana nilai berada dalam urutan menaik, MATCH memeriksa nilai sehingga nilai yang lebih besar dicapai, dan kemudian melangkah mundur dan mengembalikan kedudukan sebelumnya.
Mari membina peraturan pemformatan bersyarat untuk menonjolkan baris dan lajur yang dipadankan.
Seperti biasa dengan pemformatan bersyarat yang lebih rumit, saya cadangkan anda bekerja dengan formula palsu terlebih dahulu, dan kemudian pindahkan formula kerja terus ke peraturan pemformatan bersyarat. Dengan cara ini, anda boleh menggunakan semua alat Excel semasa anda menyahpepijat formula, yang akan menjimatkan banyak masa.
Saya akan menyediakan formula lebar terlebih dahulu. Kita perlu mengembalikan BENAR untuk setiap sel di baris 7, dengan lebar yang sesuai adalah 200.
Ini bermaksud kita memulakan formula dengan $ B5 =, dan kita perlu mengunci lajur.
= $ B5 =
Sekarang, kita tidak dapat mencari 275 di lajur lebar, kerana tidak ada di sana. Sebagai gantinya, kita memerlukan padanan anggaran yang menjumpai 200, seperti formula carian kita.
Cara termudah adalah dengan menggunakan fungsi LOOKUP. LOOKUP secara automatik melakukan pertandingan yang hampir, dan, bukannya mengembalikan kedudukan seperti MATCH, LOOKUP mengembalikan nilai padanan sebenar. Oleh itu, kita boleh menulis:
$ B5 = PENCARIAN ($ K $ 6, $ B $ 6: $ B $ 12)
Dengan lebar input kami untuk nilai carian dan semua lebar dalam jadual untuk vektor hasil.
Sekiranya saya menggunakan F9, anda dapat melihat nilai LOOKUP kembali.
Sekarang apabila saya memasukkan formula di seberang meja, kita mendapat BENAR untuk setiap sel dalam baris 200 lebar.
Sekarang kita perlu memperluas formula agar sesuai dengan lajur tinggi. Untuk melakukan ini, saya akan menambahkan fungsi ATAU, dan kemudian formula kedua yang sesuai dengan ketinggian.
Kami akan memulakan formula dengan cara yang sama, tetapi kali ini kami perlu mengunci baris:
= B $ 5
Kemudian kami menggunakan fungsi LOOKUP sekali lagi dengan ketinggian untuk mencari nilai dan dan semua ketinggian dalam jadual sebagai vektor hasil.
= ATAU ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Apabila saya menyalin formula di seluruh jadual, kami mendapat BENAR untuk setiap sel di lajur yang dipadankan dan setiap sel di baris yang sesuai - hanya yang kami perlukan untuk pemformatan bersyarat.
Saya hanya dapat menyalin formula di sel kiri atas dengan tepat, dan membuat peraturan baru.
Sekarang jika saya mengubah lebar atau tinggi, penonjolan berfungsi seperti yang diharapkan.
Akhirnya, jika anda hanya ingin menonjolkan nilai carian itu sendiri, itu adalah perubahan yang mudah. Cukup edit formula dan gantikan fungsi OR dengan fungsi AND.
= DAN ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Kursus
Pemformatan BersyaratJalan pintas yang berkaitan
Masukkan data yang sama dalam beberapa sel Ctrl + Enter ⌃ + Return Paparkan kotak dialog Tampal Khas Ctrl + Alt + V ⌘ + ⌃ + V Togol rujukan mutlak dan relatif F4 ⌘ + T
