Menggunakan Formula untuk Pemformatan Bersyarat dalam Excel

Menambah pemformatan bersyarat dalam Excel membolehkan anda menggunakan opsyen pemformatan yang berbeza ke sel atau julat sel yang memenuhi syarat tertentu yang anda tetapkan. Pilihan pemformatan hanya digunakan apabila sel terpilih memenuhi syarat yang ditetapkan.

Pilihan pemformatan yang boleh digunakan termasuk perubahan fon dan warna latar belakang, gaya fon, sempadan sel, dan menambah pemformatan nombor kepada data.

Excel mempunyai beberapa pilihan terbina dalam untuk keadaan yang biasa digunakan seperti mencari nombor yang lebih besar daripada atau kurang daripada nilai tertentu atau mencari nombor yang berada di atas atau di bawah nilai purata.

Sebagai tambahan kepada pilihan pra-tetapkan ini, juga mungkin untuk membuat peraturan pemformatan bersyarat tersuai menggunakan formula Excel untuk menguji keadaan pengguna yang ditentukan.

01 04

Menerapkan Peraturan Pelbagai di Excel

Lebih daripada satu peraturan boleh digunakan untuk data yang sama untuk menguji keadaan yang berbeza. Sebagai contoh, data anggaran mungkin mempunyai syarat yang menetapkan perubahan format apabila tahap tertentu, seperti 50%, 75%, dan 100%, dari jumlah belanjawan dibelanjakan.

Dalam keadaan sedemikian, Excel mula-mula menentukan sama ada pelbagai peraturan bercanggah, dan, jika ya, program tersebut mengikuti susunan set keutamaan untuk menentukan kaedah pemformatan bersyarat yang digunakan untuk data.

Menemukan Data yang Melebihi 25% dan 50% Meningkat

Dalam contoh berikut, dua peraturan pemformatan bersyarat tersuai akan digunakan pada julat sel B2 kepada B5.

• Peraturan pertama memeriksa untuk melihat apakah data masuk sel A2 kepada A5 adalah lebih besar daripada nilai yang sama dalam B2 kepada B5 oleh lebih daripada 25%.
• Peraturan kedua menyemak untuk melihat jika data yang sama masuk A2: A5 melebihi nilai yang sama dalam B2: B5 oleh lebih daripada 50%.

Seperti yang dapat dilihat dalam imej di atas, jika salah satu daripada syarat di atas adalah benar, warna latar belakang sel atau sel dalam julat B1: B4 akan berubah.

• Untuk data di mana perbezaannya lebih besar daripada 25%, warna latar belakang sel akan berubah menjadi hijau.
• Sekiranya perbezaannya lebih besar daripada 50% warna latar belakang sel akan berubah menjadi merah.

Peraturan yang digunakan untuk menyelesaikan tugas ini akan dimasukkan menggunakan pemformatan bersyarat Peraturan Pemformatan Baharu kotak dialog. Mulailah dengan memasukkan data sampel ke dalam sel A1 hingga C5 seperti yang dilihat pada imej di atas

Di dalam langkah ketiga tutorial akan menambah formula ke sel C2: C4 yang menunjukkan perbezaan peratusan yang tepat antara nilai dalam sel A2: A5 dan B2: B5 untuk memeriksa ketepatan peraturan pemformatan bersyarat.

02 04

Menetapkan Peraturan Pemformalan Bersyarat

Seperti yang dinyatakan, peraturan pemformatan bersyarat yang memeriksa kedua-dua syarat akan dimasukkan menggunakan pemformatan bersyarat Peraturan Pemformatan Baharu kotak dialog. Mari bermula dengan menetapkan pemformatan bersyarat tersuai kami:

Pemformatan Bersyarat untuk mencari lebih daripada 25 Peratus kenaikan

= (A2-B2) / A2> 25%

1. Sorotan sel B2 kepada B5 dalam lembaran kerja.
2. Klik pada Tab Laman Utama daripada riben.
3. Klik pada Pemformatan Bersyarat ikon dalam riben untuk membuka lungsur turun.
4. Pilih Peraturan Baru untuk membuka Peraturan Pemformatan Baharu kotak dialog.
5. Daripada Gaya: kotak, pilih Klasik pilihan.
6. Dalam drop-down kedua, klik pada pilihan terakhirGunakan formula untuk menentukan sel mana yang hendak di format.
7. Taipkan formula disediakan di atas ke ruang yang disediakan.
8. Klik pada Format dengan: drop-down kemudian pilih Isi Merah Merah dengan Teks Merah Gelap pilihan.
9. Klik okey untuk menutup kotak dialog dan kembali ke lembaran kerja.
10. Pada ketika ini, warna latar belakang sel B3 dan B5 harus hijau.

Pemformatan Bersyarat untuk Meningkat Lebih Daripada 50 Peratus

= (A2-B2) / A2> 50%

Sorotan sel B2 kepada B5 dalam lembaran kerja.

1. Klik pada Tab Laman Utama daripada riben.
2. Klik pada Pemformatan Bersyarat ikon dalam riben untuk membuka lungsur turun.
3. Pilih Peraturan Baru untuk membuka Peraturan Pemformatan Baharu kotak dialog.
4. Daripada Gaya: kotak, pilih Klasik pilihan.
5. Dalam drop-down kedua, klik pada pilihan terakhirGunakan formula untuk menentukan sel mana yang hendak di format.
6. Taipkan formula disediakan di atas ke ruang yang disediakan.
7. Klik pada Format dengan: drop-down kemudian pilih Cahaya Hijau Isi dengan Teks Hijau Gelap pilihan.
8. Klik okey untuk menutup kotak dialog dan kembali ke lembaran kerja.

Warna latar belakang sel B3 masih harus hijau menunjukkan bahawa perbezaan peratus antara nombor dalam sel A3 dan B3 adalah lebih besar daripada 25 peratus tetapi kurang daripada atau sama dengan 50 peratus. Warna latar belakang sel B5 harus berubah menjadi merah yang menunjukkan bahawa perbezaan peratus antara nombor masuk sel A5 dan B5 lebih besar daripada 50 peratus.

03 dari 04

Memeriksa Peraturan Pemformalan Bersyarat

Untuk memastikan peraturan pemformatan bersyarat yang dimasukkan adalah betul, kami boleh memasukkan formula ke dalam sel C2: C5 yang akan mengira perbezaan peratus tepat antara nombor dalam julat A2: A5 dan B2: B5.

= (A2-B2) / A2

1. Klik pada sel C2 untuk menjadikannya sel aktif.
2. Taipkan formula di atas dan tekan Masukkan kekunci pada papan kekunci.
3. Jawapan 10% sepatutnya muncul sel C2, menunjukkan bahawa nombor dalam sel A2 adalah 10% lebih besar daripada nombor dalam sel B2.
4. Mungkin perlu mengubah pemformatan sel C2 untuk memaparkan jawapan sebagai peratus.
5. Menggunakan mengisi pemegang untuk menyalin formula dari sel C2 kepada sel C3 kepada C5.
6. Jawapan untuk sel C3 kepada C5 harus 30%, 25%, dan 60%.

Jawapan di dalam sel-sel ini menunjukkan bahawa peraturan pemformatan bersyarat yang dibuat adalah betul kerana perbezaan antara sel A3 dan B3 adalah lebih besar daripada 25 peratus dan perbezaan antara sel A5 dan B5 lebih besar daripada 50 peratus.

Sel B4 tidak berubah warna kerana perbezaan antara sel-sel A4 dan B4 sama dengan 25 peratus, dan peraturan pemformatan bersyarat kami menetapkan bahawa peratusan lebih besar daripada 25 peratus diperlukan untuk warna latar belakang berubah menjadi hijau.

04 04

Order Precedence untuk Pemformatan Bersyarat

Apabila berbilang kaedah digunakan untuk julat data yang sama, Excel mula-mula menentukan jika peraturannya bertentangan. Kaedah yang bertentangan adalah mereka yang mana pilihan pemformatan yang dipilih untuk setiap peraturan tidak boleh digunakan untuk data yang sama.

Dalam contoh yang digunakan dalam tutorial ini, peraturan bertentangan kerana kedua-dua peraturan menggunakan pilihan pemformatan yang sama - iaitu mengubah warna sel latar belakang.

Dalam keadaan di mana peraturan kedua adalah benar (perbezaan nilai lebih besar daripada 50 peratus di antara dua sel) maka aturan pertama (perbezaan nilai yang lebih besar daripada 25 peratus) juga benar.

Pesanan Precedence Excel

Oleh kerana sel tidak boleh mempunyai latar belakang merah dan hijau pada masa yang sama, Excel perlu mengetahui kaedah pemformatan bersyarat yang harus dipakai.

Peraturan yang diterapkan ditentukan oleh urutan keutamaan Excel, yang menyatakan bahawa peraturan yang lebih tinggi dalam senarai dalam kotak dialog Pengurus Peraturan Pengaturan Bersyarat mempunyai keutamaan.

Seperti yang ditunjukkan dalam imej di atas, peraturan kedua yang digunakan dalam tutorial ini lebih tinggi dalam senarai dan, oleh itu, mempunyai keutamaan di atas peraturan pertama. Akibatnya, warna latar belakang sel B5 berubah menjadi merah.

Secara lalai, peraturan baru ditambah ke bahagian atas senarai dan, dengan itu, mempunyai keutamaan yang lebih tinggi. Untuk menukar susunan keutamaan menggunakan Atas dan bawah butang anak panah dalam kotak dialog seperti yang dikenal pasti dalam imej di atas.

Memohon Peraturan Tidak Berlawanan

Sekiranya dua atau lebih peraturan pemformatan bersyarat tidak bertentangan kedua-duanya digunakan apabila syarat setiap peraturan sedang dijalankan menjadi kenyataan.

Jika peraturan pemformatan bersyarat pertama dalam contoh kami diformatkan julat sel B2: B5 dengan sempadan hijau dan bukan warna latar hijau, kedua-dua peraturan pemformatan bersyarat tidak akan bertentangan kerana kedua-dua format itu boleh digunakan tanpa mengganggu yang lain.

Akibatnya, sel B5 akan mempunyai sempadan hijau dan warna latar belakang merah, kerana perbezaan di antara nombor dalam sel A5 dan B5 adalah lebih besar daripada kedua-dua 25 dan 50 peratus.

Pemformatan Bersyarat vs. Pemformatan Biasa

Dalam kes konflik antara peraturan pemformatan bersyarat dan pilihan format pemformatan secara manual, peraturan pemformatan bersyarat sentiasa diutamakan dan akan digunakan dan bukan pilihan pemformatan yang ditambah secara manual.

Jika warna latar kuning diletakkan pada mulanya sel B2 kepada B5 dalam contoh, sebaik sahaja peraturan pemformatan bersyarat ditambah, hanya sel B2 dan B4 akan kekal kuning. Kerana peraturan pemformatan bersyarat dimasukkan ke dalam sel B3 dan B5, warna latar belakang mereka akan berubah dari kuning ke hijau dan merah masing-masing.