Tutorial Excel: Cara menguji pemformatan bersyarat dengan formula palsu

Dalam video ini, saya akan menunjukkan kepada anda cara cepat menguji peraturan pemformatan bersyarat anda dengan formula palsu.

Apabila anda menerapkan pemformatan bersyarat dengan formula, rumus sukar dilakukan agar formula berfungsi dengan baik, kerana anda tidak dapat melihat apa yang terjadi pada formula ketika peraturan tersebut diterapkan.

Anda boleh menganggap pemformatan bersyarat sebagai "overlay" formula yang tidak kelihatan yang berada di atas sel.

Apabila formula dalam hamparan mengembalikan BENAR untuk sel tertentu, pemformatan diterapkan.

Formula yang tidak mengembalikan BENAR (atau yang setara) tidak menghasilkan apa-apa.

Masalahnya ialah anda tidak dapat melihat ini berlaku, jadi anda harus menggunakan percubaan dan kesilapan, yang boleh membuat anda kecewa dan memakan masa.

Kaedah yang baik untuk mempercepat adalah dengan menggunakan apa yang saya panggil "formula dummy".

Rumus palsu membolehkan anda menggambarkan bagaimana formula akan bertindak sebelum anda membuat peraturan.

Mari saya gambarkan dengan contoh yang sangat mudah. Katakan kita mahu menonjolkan nilai lebih dari 100 dalam kumpulan data ini.

Untuk memulakan, saya akan memilih kawasan di sebelah, berbaris dengan barisan.

Seterusnya, saya akan menulis formula pertama, berkaitan dengan sel kiri atas dalam data.

Dalam kes ini, itu B4, jadi rumusnya adalah

= B4> 100

Sekarang saya akan menyalin formula di seberang dan bawah.

Perhatikan bahawa kita mendapat hasil yang BENAR atau SALAH di setiap sel. Sekiranya kita memeriksa sebilangan rujukan, anda dapat melihat bahawa setiap formula menilai sel dalam data, berbanding B4.

Sekarang bayangkan hasil ini dialihkan terus ke atas data. Di mana anda melihat nilai BENAR, pemformatan akan diterapkan.

Di mana anda melihat SALAH, tidak ada yang berlaku.

Formula dummy ini kelihatan bagus, jadi mari kita mencubanya dalam peraturan pemformatan bersyarat.

Pertama, saya menyalin formula dummy pertama. Kemudian saya memilih data, dan membuat peraturan baru.

Di kawasan formula, saya hanya menampal formula. Kemudian saya menetapkan format, dan menyimpan peraturan.

Sekarang semua nilai lebih dari 100 diserlahkan, tepat seperti yang diramalkan oleh formula dummy.

Mari cuba idea yang sama dengan formula yang lebih rumit. Mari serlahkan baris dalam jadual ini dengan keutamaan "A".

Seperti sebelumnya, langkah pertama adalah mencari tahu di mana meletakkan formula dummy. Kami mempunyai banyak ruang di sebelah kanan, jadi saya akan bermula di sel G5.

Oleh kerana kami ingin menonjolkan tugas dengan keutamaan "A", mari cuba

= B5 = "A"

Apabila saya menyalin formula, anda dapat melihat ini tidak akan berfungsi.

Hasil BENAR menunjukkan hanya nilai pada lajur B yang akan diserlahkan. Kami ingin menyerlahkan keseluruhan baris, jadi saya perlu menyesuaikan formula untuk mengunci rujukan lajur dengan menambahkan tanda dolar:

= $ B5 = "A"

Sekarang formula dummy berfungsi. Kami mendapat baris penuh BENAR apabila keutamaannya adalah "A".

Mari cuba rumus dalam peraturan baru, mengikuti proses yang sama seperti sebelumnya.

Semasa saya menetapkan format dan menyimpan, peraturan baru berfungsi dengan sempurna pada kali pertama.

Kali berikutnya anda perlu menerapkan pemformatan bersyarat dengan formula yang mencabar, siapkan formula palsu di samping data, dan ubah formula sehingga anda mendapat hasil yang anda perlukan.

Dengan bekerja secara langsung di lembaran kerja, anda mempunyai akses penuh ke semua alat formula Excel, dan anda boleh menyelesaikan masalah dan menyesuaikan formula dengan mudah sehingga ia berfungsi dengan sempurna.