Formula Formula Bersyarat 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.

Sejak Excel 2007, 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 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.

Memohon Peraturan Pelbagai

Lebih daripada satu peraturan boleh digunakan untuk data yang sama untuk menguji keadaan yang berbeza. Contohnya, data anggaran mungkin mempunyai syarat yang menetapkan perubahan format apabila tahap tertentu - seperti 50%, 75%, dan 100% - daripada 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.

Contoh: Menemukan Data yang melebihi 25% dan 50% Meningkatkan dengan Pemformatan Bersyarat

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

• pemeriksaan pertama memeriksa apakah data dalam sel A2 hingga A5 lebih besar daripada nilai yang sama dalam B2 hingga B5 dengan lebih dari 25%.
• pemeriksaan kedua memeriksa jika data yang sama dalam A2: A5 melebihi nilai yang sepadan dalam B2: B5 dengan 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 biru.
• jika perbezaannya lebih besar daripada 50% warna latar sel akan berubah menjadi merah.

Peraturan yang digunakan untuk mencapai matlamat ini,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

akan dimasukkan menggunakan kotak dialog Rangka Pemformatan Pemformatan bersyarat yang bersyarat.

Memasuki Data Tutorial

1. Masukkan data ke dalam sel A1 hingga C5 seperti yang dilihat pada imej di atas

Nota: Langkah 3 tutorial akan menambah formula kepada sel C2: C4 yang menunjukkan perbezaan peratusan tepat antara nilai dalam sel A2: A5 dan B2: B5 untuk memeriksa ketepatan peraturan pemformatan bersyarat.

Menetapkan Peraturan Pemformatan Condtional

Seperti yang disebutkan, peraturan pemformatan bersyarat yang memeriksa kedua-dua syarat akan dimasukkan menggunakan kotak dialog Pemformatan Rangka Pemformatan bersyarat.

Menetapkan pemformatan bersyarat untuk mencari peningkatan melebihi 25%

1. Serlahkan sel-sel B2 ke B5 dalam lembaran kerja.
2. Klik pada tab Rumah pada reben.
3. Klik pada ikon Pemformalan Bersyarat dalam reben untuk membuka menu lungsur.
4. Pilih Peraturan Baru untuk membuka kotak dialog Peraturan Pemformatan Baharu seperti yang dilihat pada imej di atas.
5. Dalam bahagian atas kotak dialog, klik pada pilihan terakhir: Gunakan formula untuk menentukan sel mana yang hendak di format.
6. Di bahagian bawah kotak dialog, klik pada nilai Format di mana formula ini benar: baris.
7. Taipkan formula : = (A2-B2) / A2> 25% di ruang yang disediakan
8. Klik pada butang Format untuk membuka kotak dialog Format Sel.
9. Dalam kotak dialog ini, klik pada tab Isi dan pilih warna mengisi biru.
10. Klik OK dua kali untuk menutup kotak dialog dan kembali ke lembaran kerja.
11. Pada ketika ini, warna latar belakang sel B3 dan B5 mestilah berwarna biru.

Menetapkan pemformatan bersyarat untuk mencari peningkatan melebihi 50%

1. Dengan sel B2 hingga B5 masih dipilih, ulang langkah 1 hingga 6 di atas.
2. Taipkan formula: = (A2-B2) / A2> 50% di ruang yang disediakan.
3. Klik pada butang Format untuk membuka kotak dialog Format Sel.
4. Klik pada tab Isi dan pilih warna isian merah.
5. Klik OK dua kali untuk menutup kotak dialog dan kembali ke lembaran kerja .
6. Warna latar belakang sel B3 masih harus biru menunjukkan bahawa perbezaan peratus antara bilangan sel dalam A3 dan B3 lebih besar daripada 25% tetapi kurang daripada atau sama dengan 50%.
7. Warna latar belakang sel B5 harus berubah menjadi merah yang menunjukkan bahawa perbezaan peratus antara nombor dalam sel A5 dan B5 lebih besar daripada 50%.

Memeriksa Peraturan Pemformalan Bersyarat

Mengira% Perbezaan

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 lingkungan A2: A5 dan B2: B5.

1. Klik pada sel C2 untuk menjadikannya sel aktif.
2. Ketik formula = (A2-B2) / A2 dan tekan kekunci Enter pada papan kekunci.
3. Jawapan 10% sepatutnya muncul dalam sel C2, menunjukkan bahawa nombor dalam sel A2 adalah 10% lebih besar daripada jumlah dalam sel B2.
4. Ia mungkin perlu mengubah pemformatan pada sel C2 untuk memaparkan jawapan sebagai peratus.
5. Gunakan pemegang isian untuk menyalin formula dari sel C2 ke sel C3 hingga C5.
6. Jawapan untuk sel C3 hingga C5 mestilah: 30%, 25%, dan 60%.
7. Jawapan di dalam sel-sel ini menunjukkan bahawa peraturan pemformatan bersyarat yang dibuat adalah betul kerana perbezaan antara sel A3 dan B3 lebih besar daripada 25% dan perbezaan antara sel A5 dan B5 lebih besar daripada 50%.
8. Sel B4 tidak berubah warna kerana perbezaan antara sel A4 dan B4 sama dengan 25%, dan peraturan pemformatan bersyarat kami menetapkan bahawa peratusan lebih besar daripada 25% diperlukan untuk warna latar belakang berubah menjadi biru.

Order Precedence untuk Peraturan Pemformalan Bersyarat

Memohon Peraturan Pemformalan Bersyarat Berlawanan

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% di antara dua sel) maka peraturan pertama (perbezaan nilai yang lebih besar daripada 25%) juga benar.

Pesanan Precedence Excel

Oleh kerana sel tidak boleh mempunyai kedua-dua latar belakang merah dan biru pada masa yang sama, Excel perlu mengetahui kaedah pemformatan bersyarat yang perlu diterapkan.

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 (= (A2-B2) / A2> 50%) 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 butang anak panah Atas dan Bawah 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.

Sekiranya aturan pemformatan bersyarat pertama dalam contoh kami (= (A2-B2) / A2> 25%) diformatkan pelbagai sel B2: B5 dengan sempadan biru dan bukan warna latar biru, kedua-dua peraturan pemformatan bersyarat tidak akan bertentangan sejak kedua-dua format boleh digunakan tanpa mengganggu yang lain.

Akibatnya, sel B5 akan mempunyai sempadan biru dan warna latar belakang merah, kerana perbezaan antara nombor dalam sel A5 dan B5 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 belakang kuning pada mulanya digunakan pada sel B2 hingga B5 dalam contoh, sebaik sahaja peraturan pemformatan bersyarat ditambahkan, hanya sel B2 dan B4 akan kekal kuning.

Kerana peraturan pemformatan bersyarat yang dimasukkan ke sel B3 dan B5, warna latar belakang mereka akan berubah dari kuning ke biru dan merah masing-masing.