Pemformatan Bersyarat dengan Formula di Excel
Secara lalai, excel telah menyediakan kami banyak jenis format bersyarat yang ada hubungannya dengan data kami, tetapi jika ada pengguna yang ingin pemformatan dilakukan berdasarkan kriteria atau formula tertentu, kami dapat melakukannya di tab beranda dari bahagian pemformatan bersyarat ketika kami klik pada peraturan baru kita akan mencari pilihan untuk pemformatan bersyarat dengan formula di mana kita dapat menulis formula yang akan menentukan sel yang akan diformat.
Pemformatan bersyarat dengan Formula tidak lain adalah mengubah format sel berdasarkan keadaan atau kriteria yang diberikan oleh pengguna. Anda mungkin menggunakan pemformatan bersyarat untuk menonjolkan nilai teratas dalam julat, menonjolkan nilai pendua juga.
Perkara yang baik ialah kita mempunyai formula dalam format bersyarat di excel. Semua formula mesti logik; hasilnya akan BENAR atau SALAH. Sekiranya ujian logik lulus, maka kita akan mendapat pemformatan bersyarat, dan jika ujian logik excel gagal, maka kita tidak akan mendapat apa-apa.
Gambaran keseluruhan
Izinkan saya memperkenalkan anda kepada format bersyarat dengan tetingkap formula.
Pada bahagian artikel di bawah ini, kita akan melihat pelbagai contoh untuk menerapkan format bersyarat dengan formula.
Bagaimana Menggunakan Format Bersyarat Excel dengan Formula?
# 1 - Serlahkan Sel yang mempunyai Nilai Kurang dari 500
Andaikan di bawah adalah nombor jualan yang anda bekerjasama, dan di bawah adalah harga jualan seunit.
- Langkah 1: Pergi ke Pemformatan Bersyarat dan klik pada Peraturan Baru.
- Langkah 2: Sekarang pilih " Gunakan formula untuk menentukan sel yang akan diformat."
- Langkah 3: Sekarang, di bawah " Nilai format di mana formula benar," gunakan formula sebagai A2 <500 dan kemudian klik pada Format untuk menerapkan pemformatan excel.
- Langkah 4: Pilih format mengikut kehendak anda.
- Langkah 5: Sekarang, anda dapat melihat pratonton format di kotak pratonton.
- Langkah 6: Sekarang, klik OK untuk menyelesaikan pemformatan. Sekarang ia telah menyoroti semua sel yang mempunyai nombor <500.
# 2 - Tandakan Satu Sel Berdasarkan Sel Lain
Kita boleh menonjolkan satu sel berdasarkan nilai sel yang lain. Anggaplah kita mempunyai data Nama Produk dan Harga Produk dalam dua lajur pertama.
Dari jadual di atas, kita perlu mengetengahkan produk sekiranya harga jualan> 220.
- Langkah 1: Pilih rangkaian produk dan kemudian, Pergi ke pemformatan bersyarat dan klik pada Peraturan Baru.
- Langkah 2: Dalam formula, terapkan formula sebagai B2> 220.
- Langkah 3: Klik pada tombol Format dan terapkan format mengikut kehendak anda.
- Langkah 5: Klik OK; kita sudah siap format.
# 3 - Sorot Semua Sel Kosong dalam Julat
Andaikan di bawah adalah data yang anda ada.
Dalam data di atas, anda mesti menyorot semua sel, yang kosong atau kosong. Kita perlu menggunakan formula ISBLANK dalam format bersyarat Excel.
Terapkan formula di bawah di bahagian formula.
Pilih format mengikut kehendak anda.
Klik ok ia akan menyerlahkan semua sel kosong dalam julat yang dipilih.
# 4 - Gunakan DAN Fungsi untuk Menyoroti Sel
Pertimbangkan data di bawah untuk formula ini.
Sekiranya wilayah itu Tengah dan nilai jualan> 500, kita perlu menyoroti baris yang lengkap.
- Langkah 1: Pilih keseluruhan data terlebih dahulu.
- Langkah 2: Di sini, kita perlu menggunakan fungsi AND excel kerana kita perlu menguji dua keadaan di sini, dan kedua-dua syarat tersebut mestilah BENAR. Gunakan formula di bawah.
- Langkah 3: Klik pada Format.
- Langkah 4: Pergi ke FILL dan pilih warna dan kesan yang diperlukan.
- Langkah 5: Klik, OK. Kami akan melihat baris yang disorot jika kedua-dua keadaan itu BENAR.
# 5- Gunakan ATAU Fungsi untuk Menyoroti Sel
Pertimbangkan data di bawah untuk contohnya.
Dalam julat data di atas, saya ingin mengetengahkan nama bandar Bangalore dan Mysore.
- Langkah 1: Pilih data terlebih dahulu dan pergi ke Pemformatan Bersyarat, kemudian klik pada Peraturan Baru.
- Langkah 2: Oleh kerana kita perlu menonjolkan salah satu daripada dua sel nilai, gunakan fungsi OR excel.
- Langkah 3: Klik pada format dan pilih format yang diperlukan.
- Langkah 4: Setelah pemformatan diterapkan, klik OK untuk menyelesaikan tugas. Rumus akan menyoroti semua sel yang mempunyai nilai Bangalore & Mysore.
# 6 - Gunakan Fungsi COUNTIF untuk Menyoroti Sel
Andaikan anda bekerja dengan pangkalan data pelanggan. Anda perlu mengenal pasti semua pelanggan yang namanya muncul lebih daripada lima kali dalam senarai. Berikut adalah data.
Pilih data dan terapkan formula COUNTIF di bahagian formula.
Klik pada format dan terapkan format yang diperlukan.
Sekarang formula akan menyerlahkan semua nama jika kiraannya lebih daripada 5.
Amber adalah satu-satunya nama yang muncul lebih daripada lima kali dalam senarai.
# 7 - Serlahkan Setiap Baris Alternatif
Kita juga dapat memiliki formula untuk menonjolkan setiap baris alternatif yang unggul dalam data. Andaikan di bawah adalah data yang sedang anda jalankan.
Pilih data dan terapkan formula di bawah.
= MOD (ROW (), 2)
Terapkan ke format mengikut kehendak anda dan klik OK; kita akan mempunyai setiap baris alternatif yang disorot oleh formula.
# 8 - Sorot Setiap Lajur Alternatif
Seperti bagaimana kita telah menonjolkan setiap baris alternatif dengan cara yang sama, kita dapat menyoroti setiap lajur alternatif. Pertimbangkan data yang sama untuk contohnya.
Gunakan formula di bawah.
= MOD (COLUMN (), 2)
Salin formula di atas dan terapkan di bahagian formula pemformatan bersyarat.
Klik OK; ia akan menyerlahkan baris gantian.
Sekiranya anda ingin meninggalkan ruangan pertama dan sorotan dari lajur kedua, anda perlu menggunakan formula di bawah.
Ia akan menonjolkan lajur alternatif bermula dari lajur kedua.
Perkara yang Perlu Diingat
- Pemformatan bersyarat hanya menerima formula logik yang hasilnya hanya BENAR atau SALAH.
- Pratonton pemformatan bersyarat hanyalah petunjuk bagaimana rupa pemformatan.
- Jangan sekali-kali menggunakan rujukan mutlak seperti dalam formula. Semasa anda menggunakan formula, dan jika anda memilih sel secara langsung, ia akan menjadikannya sebagai rujukan mutlak, anda perlu membuatnya menjadi rujukan relatif di excel.
