Menambah pemformatan bersyarat dalam Excel membolehkan anda menggunakan pilihan pemformatan yang berbeza pada sel, atau julat sel, yang memenuhi syarat khusus yang anda tetapkan. Menetapkan syarat sedemikian boleh membantu menyusun hamparan anda dan memudahkan untuk mengimbas. Pilihan pemformatan yang boleh anda gunakan termasuk perubahan warna fon dan latar belakang, gaya fon, sempadan sel dan menambah pemformatan nombor pada data.
Excel mempunyai 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 pratetap ini, anda juga boleh membuat peraturan pemformatan bersyarat tersuai menggunakan formula Excel.
Arahan ini digunakan untuk Excel 2019, 2016, 2013, 2010 dan Excel untuk Microsoft 365.
Memohon Berbilang Syarat dalam Excel
Anda boleh menggunakan lebih daripada satu peraturan pada data yang sama untuk menguji syarat yang berbeza. Sebagai contoh, data belanjawan mungkin mempunyai syarat yang ditetapkan yang menggunakan perubahan pemformatan apabila tahap perbelanjaan tertentu dicapai, seperti 50%, 75% dan 100%, daripada jumlah belanjawan.
Dalam keadaan sedemikian, Excel terlebih dahulu menentukan sama ada pelbagai peraturan bercanggah dan, jika ya, atur cara mengikut susunan keutamaan yang ditetapkan untuk menentukan peraturan pemformatan bersyarat yang hendak digunakan pada data.
Mencari Data yang Melebihi 25% dan 50% Meningkat
Dalam contoh berikut, dua peraturan pemformatan bersyarat tersuai akan digunakan pada julat sel B2 hingga B5.
- Peraturan pertama menyemak untuk melihat sama ada data dalam sel A2:A5 lebih besar daripada nilai yang sepadan dalam B2:B5 oleh lebih daripada 25%.
- Peraturan kedua menyemak untuk melihat sama ada 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 yang perbezaannya melebihi 25%, warna latar belakang sel akan bertukar kepada hijau.
- Jika perbezaan lebih besar daripada 50%, warna latar belakang sel akan bertukar kepada merah.
Peraturan yang digunakan untuk menyelesaikan tugasan ini akan dimasukkan menggunakan kotak dialog Peraturan Pemformatan Baharu. Mulakan dengan memasukkan data sampel ke dalam sel A1 hingga C5 seperti yang dilihat dalam imej di atas.
Dalam bahagian akhir tutorial kami akan menambah formula pada sel C2:C4 yang menunjukkan perbezaan peratusan tepat antara nilai dalam sel A2:A5 dan B2:B5; ini akan membolehkan kami menyemak ketepatan peraturan pemformatan bersyarat.
Menetapkan Peraturan Pemformatan Bersyarat
Pertama, kami akan menggunakan pemformatan bersyarat untuk mencari peningkatan 25 peratus atau lebih ketara.
Fungsi akan kelihatan seperti ini:
=(A2-B2)/A2>25%
- Serlahkan sel B2 hingga B5 dalam lembaran kerja.
- Klik pada tab Laman Utama daripada reben.
- Klik pada ikon Pemformatan Bersyarat dalam reben untuk membuka menu lungsur.
-
Pilih Peraturan Baharu untuk membuka kotak dialog Peraturan Pemformatan Baharu.
- Di bawah Pilih Jenis Peraturan, klik pilihan terakhir: Gunakan formula untuk menentukan sel mana yang hendak diformat.
- Taip formula yang dinyatakan di atas ke dalam ruang di bawah Formatkan nilai yang formula ini benar:
- Klik butang Format untuk membuka kotak dialog. Klik tab Isi dan pilih warna.
- Klik OK untuk menutup kotak dialog dan kembali ke lembaran kerja.
- Warna latar belakang sel B3 dan B5 hendaklah bertukar kepada warna yang anda pilih.
Kini, kami akan menggunakan pemformatan bersyarat untuk mencari peningkatan 50 peratus atau lebih. Formula akan kelihatan seperti ini:
- Ulangi lima langkah pertama di atas.
- Taip formula yang disediakan di atas dalam ruang di bawah Formatkan nilai yang formula ini benar:
- Klik butang Format untuk membuka kotak dialog. Klik tab Isi dan pilih warna yang berbeza daripada yang anda lakukan dalam set langkah sebelumnya.
- Klik OK untuk menutup kotak dialog dan kembali ke lembaran kerja.
Warna latar belakang sel B3 hendaklah kekal sama yang menunjukkan bahawa peratus perbezaan antara nombor dalam sel A3 danB3 lebih besar daripada 25 peratus tetapi kurang daripada atau sama dengan 50 peratus. Warna latar belakang sel B5 hendaklah bertukar kepada warna baharu yang anda pilih menunjukkan bahawa peratus perbezaan antara nombor dalam sel A5 dan B5 lebih besar daripada 50 peratus.
Menyemak Peraturan Pemformatan Bersyarat
Untuk mengesahkan bahawa peraturan pemformatan bersyarat yang dimasukkan adalah betul, kami boleh memasukkan formula ke dalam sel C2:C5 yang akan mengira perbezaan peratusan tepat antara nombor dalam julatA2:A5 dan B2:B5.
Formula dalam sel C2 kelihatan seperti ini:
=(A2-B2)/A2
- Klik pada sel C2 untuk menjadikannya sel aktif.
- Taip formula di atas dan tekan kekunci Enter pada papan kekunci.
- Jawapan 10% sepatutnya muncul dalam sel C2, menunjukkan bahawa nombor dalam sel A2 adalah 10% lebih besar daripada nombor dalam sel B2.
- Mungkin perlu menukar pemformatan pada sel C2 untuk memaparkan jawapan sebagai peratus.
- Gunakan pemegang isi untuk menyalin formula daripada sel C2 kepada sel C3 ke C5.
- Jawapan untuk sel C3 hingga C5 hendaklah 30%, 25% dan 60%.
Jawapan dalam sel ini menunjukkan bahawa peraturan pemformatan bersyarat adalah tepat kerana perbezaan antara sel A3 dan B3 adalah lebih besar daripada 25 peratus dan perbezaan antara sel A5 dan B5 adalah lebih besar daripada 50 peratus.
Sel B4 tidak berubah warna kerana perbezaan antara sel A4 dan B4 sama 25 peratus dan peraturan pemformatan bersyarat kami menyatakan bahawa peratusan yang lebih besar daripada 25 peratus diperlukan untuk warna latar belakang berubah.
Tertib Keutamaan untuk Pemformatan Bersyarat
Apabila anda menggunakan berbilang peraturan pada julat data yang sama, Excel terlebih dahulu menentukan sama ada peraturan itu bercanggah. Peraturan yang bercanggah ialah peraturan yang pilihan pemformatan tidak boleh digunakan pada data yang sama.
Dalam contoh kami, peraturan bercanggah kerana kedua-duanya menggunakan pilihan pemformatan yang sama - menukar warna sel latar belakang.
Dalam situasi di mana peraturan kedua adalah benar (perbezaan nilai lebih daripada 50 peratus antara dua sel) maka peraturan pertama (perbezaan nilai lebih besar daripada 25 peratus) juga benar.
Memandangkan sel tidak boleh mempunyai kedua-dua latar belakang warna yang berbeza pada masa yang sama, Excel perlu mengetahui peraturan pemformatan bersyarat yang harus digunakan.
Tertib keutamaan Excel menyatakan bahawa peraturan yang lebih tinggi dalam senarai dalam kotak dialog Pengurus Peraturan Pemformatan Bersyarat digunakan dahulu.
Seperti yang ditunjukkan dalam imej di atas, peraturan kedua yang digunakan dalam tutorial ini adalah lebih tinggi dalam senarai dan, oleh itu, mempunyai keutamaan berbanding peraturan pertama. Akibatnya, warna latar belakang sel B5 adalah hijau.
Secara lalai, peraturan baharu pergi ke bahagian atas senarai; untuk menukar susunan, gunakan butang anak panah Atas dan Bawah dalam kotak dialog.
Memohon Peraturan Tidak Bercanggah
Jika dua atau lebih peraturan pemformatan bersyarat tidak bercanggah, kedua-duanya digunakan apabila syarat setiap peraturan ujian menjadi benar.
Jika peraturan pemformatan bersyarat pertama dalam contoh kami memformatkan julat sel B2:B5 dengan jidar oren dan bukannya warna latar belakang oren, kedua-dua peraturan pemformatan bersyarat tidak akan konflik kerana kedua-dua format boleh digunakan tanpa mengganggu yang lain.
Pemformatan Bersyarat lwn. Pemformatan Biasa
Dalam kes percanggahan antara peraturan pemformatan bersyarat dan pilihan pemformatan yang digunakan secara manual, peraturan pemformatan bersyarat sentiasa diutamakan dan akan digunakan berbanding sebarang pilihan pemformatan yang ditambahkan secara manual.
