Pemformatan bersyarat dalam Excel – secara terperinci dengan contoh

Jika anda perlu menyampaikan maklumat secara visual, maka sangat mudah untuk melakukan ini menggunakan pemformatan bersyarat. Pengguna yang menggunakan perkhidmatan ini menjimatkan sejumlah besar tenaga dan masa. Untuk mendapatkan maklumat yang diperlukan, paparan pantas fail sudah memadai.

Bagaimana untuk melakukan pemformatan bersyarat dalam Excel

Anda boleh menemui "Pemformatan Bersyarat" pada tab pertama reben dengan pergi ke bahagian "Gaya". 

Seterusnya, anda perlu mencari ikon anak panah sedikit ke kanan dengan mata anda, gerakkan kursor kepadanya. Kemudian tetapan akan dibuka, di mana anda boleh mengkonfigurasi semua parameter yang diperlukan secara fleksibel. 

Seterusnya, anda perlu memilih operator yang sesuai untuk membandingkan pembolehubah yang dikehendaki dengan nombor. Terdapat empat pengendali perbandingan – lebih besar daripada, kurang daripada, sama dengan, dan antara. Mereka disenaraikan dalam menu peraturan. 

Seterusnya, kami menentukan satu siri nombor dalam julat A1:A11.

Mari format set data yang sesuai. Kemudian kami membuka menu tetapan "Pemformatan Bersyarat" dan tetapkan kriteria pemilihan data yang diperlukan. Dalam kes kami, sebagai contoh, kami akan memilih kriteria "Lagi". 

Tetingkap dengan satu set pilihan akan dibuka. Di bahagian kiri tetingkap terdapat medan di mana anda perlu menentukan nombor 15. Di bahagian kanan, kaedah untuk menyerlahkan maklumat ditunjukkan, dengan syarat ia memenuhi kriteria yang ditentukan sebelumnya. Hasilnya akan dipaparkan serta-merta.

Seterusnya, lengkapkan konfigurasi dengan butang OK.

Untuk apa pemformatan bersyarat?

Pada tahap tertentu, kita boleh mengatakan bahawa ia adalah alat, selepas belajar yang bekerja dengan Excel tidak akan pernah sama. Ini kerana ia menjadikan hidup lebih mudah. Daripada menetapkan pemformatan sel secara manual yang sepadan dengan keadaan tertentu setiap kali, dan juga cuba menyemaknya sendiri untuk pematuhan dengan kriteria ini, anda hanya perlu menetapkan tetapan sekali, dan kemudian Excel akan melakukan segala-galanya sendiri.

Sebagai contoh, anda boleh menjadikan semua sel yang mengandungi nombor lebih daripada 100 bertukar merah. Atau, tentukan berapa hari lagi sehingga pembayaran seterusnya, dan kemudian warnakan dengan hijau sel-sel yang tarikh akhir masih belum cukup jauh. 

Mari kita ambil contoh.

Jadual ini menunjukkan inventori yang ada dalam stok. Ia mempunyai lajur. sebagai produk, purata jualan (diukur dalam unit seminggu), stok dan berapa minggu produk ini tinggal.

Selanjutnya, tugas pengurus pembelian adalah untuk menentukan kedudukan tersebut, yang mana penambahannya diperlukan. Untuk melakukan ini, anda perlu melihat lajur keempat dari kiri, yang merekodkan stok barang mengikut minggu.

Katakan kriteria untuk menentukan punca panik adalah kurang daripada 3 minggu inventori. Ini menunjukkan bahawa kita perlu menyediakan pesanan. Jika stok barang kurang dari dua minggu, maka ini menunjukkan keperluan untuk membuat pesanan segera. Jika jadual mengandungi sejumlah besar jawatan, maka semak secara manual berapa minggu yang tinggal agak bermasalah. Walaupun anda menggunakan carian. Dan sekarang mari kita lihat bagaimana jadual itu, yang menyerlahkan barangan yang terhad dalam warna merah.

Dan sememangnya, ia adalah lebih mudah untuk menavigasi. 

Benar, contoh ini adalah pendidikan, yang agak dipermudahkan berbanding dengan gambaran sebenar. Dan saat dan minit yang disimpan dengan penggunaan biasa jadual sedemikian bertukar menjadi jam. Sekarang cukup untuk melihat jadual untuk memahami barang mana yang kekurangan, dan tidak menghabiskan berjam-jam menganalisis setiap sel (jika terdapat beribu-ribu item komoditi tersebut).

Sekiranya terdapat barangan "kuning", maka anda perlu mula membelinya. Jika kedudukan yang sepadan berwarna merah, maka anda perlu melakukannya dengan segera. 

Dengan nilai sel lain

Sekarang mari kita lihat contoh praktikal berikut.

Katakan kita mempunyai jadual sedemikian, dan kita berhadapan dengan tugas untuk menyerlahkan baris yang mengandungi nilai tertentu. 

Jadi, jika projek itu masih dilaksanakan (iaitu, ia masih belum siap, oleh itu ia ditandakan dengan huruf "P"), maka kita perlu menjadikan latar belakangnya merah. Projek yang telah siap ditandakan dengan warna hijau.

Urutan tindakan kami dalam situasi ini adalah seperti berikut:

1. Pilih julat nilai. 
2. Klik pada butang "Pemformatan Bersyarat" - "Buat Peraturan".
3. Sila ambil perhatian bahawa dalam kes kami, anda perlu menggunakan formula dalam bentuk peraturan. Seterusnya, kami menggunakan fungsi tersebut IFuntuk menyerlahkan baris yang sepadan. 

Seterusnya, isikan baris seperti yang ditunjukkan dalam gambar ini.

Penting! Rentetan mesti dirujuk sepenuhnya. Jika kita merujuk kepada sel, maka dalam kes ini ia bercampur (dengan penetapan lajur).

Begitu juga, peraturan dibuat untuk projek kerja yang belum disiapkan sehingga kini. 

Inilah rupa kriteria kami.

Dan akhirnya, kami mempunyai pangkalan data sedemikian sebagai hasilnya.

Pelbagai Syarat

Untuk kejelasan, mari ambil julat A1:A11 daripada jadual untuk menunjukkan bagaimana, dalam contoh praktikal sebenar, anda boleh menggunakan beberapa syarat untuk menentukan pemformatan sel.

Syaratnya sendiri adalah seperti berikut: jika nombor dalam sel melebihi nilai 6, maka ia diserlahkan dengan warna merah. Jika hijau, maka warnanya hijau. Dan akhirnya, nombor terbesar, lebih daripada 20, akan diserlahkan dengan warna kuning. 

Terdapat beberapa cara bagaimana pemformatan bersyarat dijalankan mengikut beberapa peraturan.

1. Kaedah 1. Kami memilih julat kami, selepas itu, dalam menu tetapan pemformatan bersyarat, pilih peraturan pemilihan sel seperti "Lagi". Nombor 6 ditulis di sebelah kiri, dan pemformatan ditetapkan di sebelah kanan. Dalam kes kami, isi hendaklah berwarna merah. Selepas itu, kitaran diulang dua kali, tetapi parameter lain sudah ditetapkan - lebih daripada 10 dan warna hijau dan lebih daripada 20 dan warna kuning, masing-masing. Anda akan mendapat hasil seperti itu.
   

   

2. Kaedah 2. Pergi ke menu tetapan utama alat "Pemformatan Bersyarat" Excel. Di sana kita dapati menu "Buat Peraturan" dan klik kiri pada item ini.
   

   

    Selepas itu, Seterusnya, pilih item "Gunakan formula ..." (diserlahkan dalam rajah dengan bingkai merah) dan tetapkan syarat pertama. Selepas itu, klik pada OK. Dan kemudian kitaran diulang untuk keadaan berikutnya dengan cara yang sama seperti yang diterangkan di atas, tetapi formula digunakan.

   

Adalah penting untuk mengambil kira perkara ini. Dalam contoh kami, beberapa sel sepadan dengan beberapa kriteria sekaligus. Excel menyelesaikan konflik ini seperti berikut: peraturan di atas digunakan dahulu. 

Dan berikut adalah tangkapan skrin untuk pemahaman yang lebih baik.

Sebagai contoh, kita mempunyai nombor 24. Ia memenuhi ketiga-tiga syarat pada masa yang sama. Dalam kes ini, akan ada pengisian yang memenuhi syarat pertama, walaupun pada hakikatnya ia jatuh lebih banyak di bawah syarat ketiga. Oleh itu, jika penting nombor di atas 20 diisi dengan warna kuning, syarat ketiga mesti diletakkan di tempat pertama. 

Pemformatan Tarikh Bersyarat

Sekarang mari kita lihat cara pemformatan bersyarat berfungsi dengan tarikh. Kami mempunyai rangkaian yang begitu keren.

Dalam kes ini, anda perlu menetapkan peraturan pemformatan bersyarat seperti "Tarikh".

Selepas itu, kotak dialog dibuka dengan keseluruhan set syarat. Anda boleh berkenalan dengan mereka secara terperinci dalam tangkapan skrin ini (mereka disenaraikan di sebelah kiri skrin dalam bentuk senarai).

Kami hanya perlu memilih yang sesuai dan klik pada butang "OK".

Kami melihat bahawa tarikh yang merujuk kepada minggu terakhir pada masa julat ini dilukis telah diserlahkan dengan warna merah. 

Menggunakan formula

Set peraturan pemformatan bersyarat standard agak besar. Tetapi situasi berbeza, dan senarai standard mungkin tidak mencukupi. Dalam kes ini, anda boleh membuat peraturan pemformatan anda sendiri menggunakan formula. 

Untuk melakukan ini, klik butang "Buat Peraturan" dalam menu pemformatan bersyarat, dan kemudian pilih item yang dipaparkan dalam tangkapan skrin.

Baris (mengikut nilai sel)

Katakan kita perlu menyerlahkan baris yang mengandungi sel dengan nilai tertentu. Dalam kes ini, kita perlu melakukan urutan tindakan yang sama seperti di atas, tetapi untuk rentetan. Iaitu, anda boleh memilih julat mana pemformatan bersyarat harus digunakan. 

Cara membuat peraturan

Untuk membuat peraturan, anda mesti menggunakan pilihan yang sesuai dalam bahagian "Pemformatan Bersyarat". Mari kita lihat urutan tindakan tertentu. 

Mula-mula anda perlu mencari item "Pemformatan Bersyarat" pada reben pada tab "Rumah". Ia boleh dikenali dengan ciri sel berbilang warna dengan saiz yang berbeza dengan gambar di sebelahnya dengan tanda sama yang dicoret. 

Terdapat butang "Buat Peraturan". Anda juga boleh mengklik butang "Urus peraturan", selepas itu kotak dialog akan dibuka, yang melaluinya anda juga boleh membuat peraturan dengan mengklik butang yang sepadan di sudut kiri atas. 

Anda juga boleh melihat dalam tetingkap ini peraturan yang telah digunakan.

Peraturan pemilihan sel

Okay, kita bercakap banyak tentang cara membuat peraturan, tetapi apakah itu? Peraturan pemilihan sel merujuk kepada ciri yang diambil kira oleh program untuk memutuskan cara memformat sel yang sesuai dengannya. Contohnya, syarat boleh lebih besar daripada nombor tertentu, kurang daripada nombor tertentu, formula dan sebagainya. Terdapat satu set keseluruhan daripada mereka. Anda boleh membiasakan diri dengan mereka dan mengamalkan penggunaannya "dalam kotak pasir". 

Memformat semua sel berdasarkan nilainya

Seseorang boleh menetapkan julat secara bebas: satu baris, keseluruhan dokumen atau satu sel. Program ini hanya boleh menumpukan pada nilai satu sel. Ini menambah fleksibiliti kepada proses. 

Formatkan sel unik atau pendua sahaja

Terdapat sejumlah besar peraturan yang disambungkan terus ke dalam program. Anda boleh berkenalan dengan mereka dalam menu "Pemformatan Bersyarat". Khususnya, agar pemformatan digunakan secara eksklusif pada nilai pendua atau hanya pada nilai unik, terdapat pilihan khas - "Nilai pendua".

Jika anda memilih item ini, tetingkap dengan tetapan terbuka, di mana anda juga boleh memilih pilihan yang bertentangan - pilih hanya nilai unik. 

Format nilai dalam julat di bawah dan di atas purata

Untuk memformat nilai di bawah atau di atas purata, terdapat juga pilihan khas dalam menu yang sama. Tetapi anda perlu memilih submenu lain - "Peraturan untuk memilih nilai pertama dan terakhir". 

Memformat hanya nilai pertama dan terakhir

Dalam submenu yang sama, adalah mungkin untuk menyerlahkan hanya nilai pertama dan terakhir dengan warna khas, fon dan kaedah pemformatan lain. Mengikut piawaian, terdapat pilihan untuk memilih sepuluh elemen pertama dan terakhir, serta 10% daripada jumlah bilangan sel yang termasuk dalam julat ini. Tetapi pengguna boleh menentukan secara bebas berapa banyak sel yang dia perlu pilih.

Memformatkan hanya sel dengan kandungan tertentu

Untuk memformatkan hanya sel dengan kandungan tertentu, anda mesti memilih peraturan pemformatan Equal To atau Text Contains. Perbezaan di antara mereka ialah dalam kes pertama, rentetan mesti sepadan dengan kriteria sepenuhnya, dan dalam kes kedua, hanya sebahagiannya. 

Seperti yang anda lihat, pemformatan bersyarat ialah ciri pelbagai fungsi program Excel yang memberikan sejumlah besar kelebihan kepada orang yang telah menguasainya. Kami tahu bahawa pada pandangan pertama, semua ini mungkin kelihatan rumit. Tetapi sebenarnya, tangan tertarik kepadanya sebaik sahaja ia kelihatan menyerlahkan sekeping teks tertentu dalam warna merah (atau memformatkannya dengan cara lain), berdasarkan kandungannya atau mengikut kriteria lain. Fungsi ini disertakan dalam set asas pengetahuan Excel, tanpanya, kerja amatur dengan hamparan dan pangkalan data adalah mustahil.