Perkongsian kemahiran Excel Praktikal: Gunakan 'pemformatan bersyarat' dan 'formula fungsi' bersama-sama

Dalam artikel sebelumnya " Berkongsi kemahiran Excel praktikal: "Kesahan Data" boleh digunakan dengan cara ini! ", kami mempelajari 3 petua untuk menjadikan kesahihan data lebih cekap. Hari ini kita akan bercakap tentang pemformatan bersyarat dengan formula fungsi Melalui dua contoh, kami akan memperkenalkan cara "pemformatan bersyarat" dan "formula fungsi" digunakan bersama.

Semua orang boleh bermain dengan pemformatan bersyarat, tetapi saya percaya anda tidak tahu cara mencari nilai maksimum setiap baris dalam 100 baris data pada masa dan label yang sama ia. Hari ini kita akan menggunakan 2 contoh untuk mempelajari cara menggunakan pemformatan bersyarat dengan formula fungsi.

Contoh 1 [Pemarkahan warna skor tertinggi untuk data berbilang baris]

Bercakap tentang menggunakan pemformatan bersyarat untuk menandakan nilai maksimum, saya percaya semua orang tahu, Ini boleh dicapai dengan menggunakan item dalam "Peraturan Pemilihan Projek" seperti yang ditunjukkan dalam rajah di bawah, tetapi operasi ini agak terhad. Jika kita mempunyai 100 baris data, bagaimana jika kita ingin menandakan nilai tertinggi bagi setiap baris pada masa yang sama? Biar saya terangkan kepada anda formula untuk menyelesaikan masalah ini.

Baru-baru ini, kami akan menjalankan penilaian ke atas empat mata pelajaran termasuk excel, word, ppt dan komprehensif. Markah ujian pelajar direkodkan dalam jadual Sekarang anda perlu mengisi sel dengan markah tertinggi setiap pelajar dengan warna hijau.

Langkah operasi:

(1) Pilih kawasan data D2:G13 dan klik [Syarat] dalam [Mula] tab Format] dan pilih [Peraturan Baharu].

(2) Buka kotak dialog [New Format Rule] dan pilih jenis peraturan [Gunakan formula untuk menentukan sel yang akan diformatkan]. Masukkan formula =AND(D2=MAX($D2:$G2),D2"") dalam huraian peraturan pengeditan di bawah. Klik [Format] untuk menetapkan warna isian sel kepada hijau.

Analisis formula fungsi

(1) D2=MAX($D2:$G2) menentukan sama ada D2 bersamaan dengan $ D2: Nilai maksimum dalam kawasan $G2, mengembalikan TRUE jika ia adalah sama, jika tidak, mengembalikan FALSE. Simbol $ ditambah pada koordinat lajur untuk menunjukkan rujukan mutlak, supaya data akan dinilai dalam lajur D-G. Koordinat baris adalah rujukan relatif Setiap kali anda bergerak ke bawah satu baris, formula secara automatik bertukar kepada koordinat baris yang sepadan.

(2) D2"" bermakna D2 lebih besar daripada atau kurang daripada sel kosong, kemudian TRUE dikembalikan, jika tidak FALSE dikembalikan.

(3) Fungsi AND menentukan sama ada kedua-dua syarat di atas adalah benar Jika kedua-duanya benar, ia mengembalikan BENAR dan sel akan diisi dengan warna hijau.

Selepas mengedit peraturan pemformatan, klik [OK] untuk memasuki [Pengurus Pemformatan Bersyarat] (Nota: Pengurus Pemformatan Bersyarat boleh difahami sebagai menyimpan pemformatan bersyarat yang ditetapkan)

Saya melihat pemformatan bersyarat yang baru ditetapkan dalam pengurus Kawasan di mana pemformatan bersyarat semasa digunakan ditetapkan pada julat sel $D$2:$G$13. Jika julat data kami berubah, ubah suai sahaja julat sel aplikasi.

Klik [Guna]-[OK] dalam gambar di atas untuk melengkapkan penandaan warna skor tertinggi. Keputusan akhir adalah seperti berikut.

Anda boleh cuba menukar formula dan mewarnai markah terendah!

Contoh 2 [Peringatan Tamat Tempoh Kontrak]

Bagaimana untuk menetapkan peringatan tamat tempoh kontrak dalam borang? Saya percaya bahawa semua rakan kongsi dalam pengurusan kontrak mempunyai keperluan sedemikian. Cara paling mudah ialah menggunakan "Tarikh Kejadian" dalam "Pemformatan Bersyarat" untuk memaparkan sel yang mengandungi data yang akan tamat tempoh dalam warna khas.

Tetapi kaedah ini hanya mempunyai pilihan berikut Bagaimana jika saya ingin mengetahui kontrak yang akan tamat dalam masa 5 hari? Anda perlu menggunakan formula fungsi.

Jadual berikut ialah jadual rekod kontrak buruh untuk kakitangan Jabatan Pemasaran China Unicom Group Corporation Jadual merekodkan tarikh kemasukan pekerja dan tarikh penamatan kontrak. Sekarang kita perlu menggunakan pemformatan bersyarat untuk peringatan kod warna secara automatik untuk pekerja yang kontraknya akan tamat.

Langkah operasi:

(1) Pilih kawasan data H2:H13 dan klik [Conditional Formatting] dalam tab [Start] . Pilih [Peraturan Format Baharu], buka kotak dialog [Peraturan Format Baharu] dan pilih [Gunakan formula untuk menentukan sel yang akan diformatkan]

(2) Dalam pengeditan perihalan peraturan Dalam keadaan tetapan input, kami masukkan =DATEDIF(TODAY(),H2,"d")

Mungkin ramai kawan-kawan yang tak berapa kenal dengan maksud DATEDIF(TODAY(),H2,"d")

Di bawah ini kami menggunakan jadual untuk memperkenalkan secara ringkas fungsi ini:

Seperti yang anda lihat, kami menggunakan tarikh kemasukan dan tarikh semasa ( hari ini ()Kembalikan tarikh semasa ) untuk perbandingan, menggunakan "y", "m", dan "d" sebagai unit pengiraan masing-masing dan kembalikan bilangan tahun, bulan dan hari antara dua tarikh itu secara bergilir-gilir. Nota: Parameter pertama formula fungsi ialah tarikh kecil, dan parameter kedua ialah tarikh besar.

Setelah memahami fungsi DATEDIF, tidak sukar untuk kami memahami formula DATEDIF(TODAY(),H2,"d")

Hari ini kami berkongsi dengan anda dua contoh cara menggabungkan formula dalam pemformatan bersyarat untuk menilai dan melabel data. Terdapat banyak contoh yang berkaitan dalam kerja sebenar, seperti:

1 Data berulang boleh ditanda melalui fungsi =COUNTIF($H$3:$H$13,H2)>1.

2. Gunakan fungsi =VLOOKUP($H2,$M:$M,1,0)"#N/A" untuk mewarna secara langsung sel yang dipadankan. Anda boleh mencubanya!

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel Praktikal: Gunakan 'pemformatan bersyarat' dan 'formula fungsi' bersama-sama. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!