Pemformatan bersyarat unggul dengan rumus

Jika Anda adalah penggemar fitur pemformatan bersyarat Excel, Anda mungkin menemukan semakin banyak cara untuk menyorot informasi berguna dalam data Anda. Sebuah pertanyaan yang sering muncul di antara “pecandu pemformatan bersyarat” ini adalah Dapatkah saya menggunakan rumus Jika/Maka untuk memformat sel?

Jawabannya adalah ya dan tidak. Argumen pemformatan bersyarat apa pun harus menghasilkan hasil yang BENAR, artinya pada tingkat literal, aturan pemformatan bersyarat Anda adalah pernyataan Jika/Kemudian di sepanjang baris "Jika kondisi ini BENAR, MAKA format sel dengan cara ini"

Apa yang tidak dapat dilakukan pemformatan bersyarat dalam satu aturan adalah ketentuan IF/THEN/ELSE seperti "Jika # lebih besar dari 10 format merah, jika tidak format hijau". Sebagai gantinya, ini membutuhkan DUA aturan, satu untuk “lebih dari 10” dan satu untuk “kurang dari 10”

Mari kita lihat beberapa skenario untuk memahami bagaimana kita dapat membuat efek pemformatan bersyarat JIKA/MAKA, bahkan jika kita tidak dapat menggunakannya dalam fitur itu sendiri

Untuk mengikuti contoh kami, unduh 04-If-Then Conditional Formatting. xlsx

Skenario 1 (tab Ulang Tahun) . Anda ingin menyorot semua karyawan di departemen Anda yang berulang tahun bulan ini dengan warna Merah, dan semua departemen lainnya berwarna biru.

Solusi. Buat dua aturan – satu untuk departemen Anda, satu untuk yang lainnya

Langkah 1 – Sorot ulang tahun di departemen Anda

Rumus untuk mengidentifikasi ulang tahun di bulan ini akan menjadi (lihat artikel ini untuk lebih lanjut tentang menggunakan tanggal dalam pemformatan bersyarat)

Contoh ini dibuat pada bulan April, jadi ulang tahun April akan disorot. Jika Anda mengulang latihan di bulan yang berbeda, Anda akan melihat hasil yang berbeda.

Lalu, buat aturan kedua untuk rentang yang sama menggunakan rumus ini untuk menyorot ulang tahun yang tidak ada di departemen Anda

BONUS. Dalam contoh ini, kami menerapkan aturan ke sel departemen untuk menunjukkan hubungan ke rumus. Namun, dengan mengubah Terapkan ke rentang, Anda dapat dengan mudah menyorot sel yang berbeda – seperti tanggal lahir – atau seluruh baris. Lihat Mendapatkan Hasil Maksimal dari Pemformatan Bersyarat Excel untuk ide lainnya.

Skenario 2 (tab Pengikut). Anda memiliki tabel tentang berapa jam karyawan Anda telah bekerja untuk klien tertentu, dan Anda memiliki tabel tentang berapa jam yang dimiliki setiap klien dalam anggaran pengikutnya. Anda ingin menyoroti klien yang melebihi pengikut mereka.

Solusi 1 . Buat kolom pembantu menggunakan rumus IF/THEN untuk mengetahui apakah klien melebihi anggaran pengikutnya. Jika lembar kerja Anda sudah memiliki logika JIKA/MAKA/ELSE yang Anda butuhkan disematkan dalam sel, Pemformatan Bersyarat dapat bertindak berdasarkan hasil tersebut. Anda tidak perlu mereproduksi logika dalam aturan itu sendiri.

Dalam contoh ini, kami sudah memiliki rumus JIKA/MAKA yang mengembalikan hasil "YA" jika klien kami melebihi anggaran pengikutnya. Aturan Pemformatan Bersyarat kami, selanjutnya hanya perlu mencari string teks "YES" dan menerapkan pemformatan jika benar

Sorot rentang sel, Klik Pemformatan Bersyarat > Sorot Aturan Sel > Teks yang Berisi untuk membuat Aturan, lalu ketik YA di . Text that Contains dialog box.

Solusi 2 . Buat rumus untuk menghitung anggaran retainer.

Jika Anda tidak memiliki, atau tidak ingin membuat, kolom pembantu dengan pernyataan JIKA/MAKA, Anda dapat menggunakan metode yang sama seperti skenario pertama dengan membuat aturan yang menentukan apakah klien melebihi anggaran. Dalam contoh ini, kami menerapkan aturan ke sel Klien dan rumusnya adalah

If you are used to creating complex formulas that cover all cases in one cell, it may take a little re-learning to figure out the approach for conditional formatting that works more incrementally. The best hint is to remember that you can apply multiple rules to the same cells – break up your formatting criteria into separate steps, and you’ll most likely be able to get where you need to be!

Pemformatan bersyarat di Excel memungkinkan Anda menyorot sel dengan warna tertentu, bergantung pada nilai sel

Sorot Aturan Sel

Untuk menyorot sel yang lebih besar dari nilai, jalankan langkah-langkah berikut

1. Pilih rentang A1. A10

2. Pada tab Beranda, di grup Gaya, klik Pemformatan Bersyarat

3. Klik Sorot Aturan Sel, Lebih Besar Dari

4. Masukkan nilai 80 dan pilih gaya pemformatan

5. Klik Oke

Hasil. Excel menyoroti sel yang lebih besar dari 80

6. Ubah nilai sel A1 menjadi 81

Hasil. Excel mengubah format sel A1 secara otomatis

Catatan. Anda juga dapat menggunakan kategori ini (lihat langkah 3) untuk menyorot sel yang kurang dari nilai, antara dua nilai, sama dengan nilai, sel yang berisi teks tertentu, tanggal (hari ini, minggu lalu, bulan depan, dll. ), duplikat atau nilai unik

Aturan yang Jelas

Untuk menghapus aturan pemformatan bersyarat, jalankan langkah-langkah berikut

1. Pilih rentang A1. A10

2. Pada tab Beranda, di grup Gaya, klik Pemformatan Bersyarat

3. Klik Hapus Aturan, Hapus Aturan dari Sel yang Dipilih

Atas bawah

Untuk menyorot sel yang di atas rata-rata, jalankan langkah-langkah berikut

1. Pilih rentang A1. A10

2. Pada tab Beranda, di grup Gaya, klik Pemformatan Bersyarat

3. Klik Aturan Atas/Bawah, Di Atas Rata-Rata

4. Pilih gaya pemformatan

5. Klik Oke

Hasil. Excel menghitung rata-rata (42. 5) dan memformat sel yang berada di atas rata-rata ini

Catatan. Anda juga dapat menggunakan kategori ini (lihat langkah 3) untuk menyorot n item teratas, n persen teratas, n item terbawah, n persen terbawah, atau sel yang di bawah rata-rata

Pemformatan Bersyarat dengan Rumus

Tingkatkan keahlian Excel Anda ke level berikutnya dan gunakan rumus untuk menentukan sel mana yang akan diformat. Rumus yang menerapkan pemformatan bersyarat harus bernilai BENAR atau SALAH

1. Pilih rentang A1. E5

2. Pada tab Beranda, di grup Gaya, klik Pemformatan Bersyarat

3. Klik Aturan Baru

4. Pilih 'Gunakan rumus untuk menentukan sel mana yang akan diformat'

5. Masukkan rumus =ISODD(A1)

6. Pilih gaya pemformatan dan klik OK

Hasil. Excel menyoroti semua angka ganjil

Penjelasan. selalu tulis rumus untuk sel kiri atas dalam rentang yang dipilih. Excel secara otomatis menyalin rumus ke sel lain. Jadi, sel A2 berisi rumus =ISODD(A2), sel A3 berisi rumus =ISODD(A3), dll.

Ini contoh lainnya

7. Pilih rentang A2. D7

8. Ulangi langkah 2-4 di atas

9. Masukkan rumus =$C2="USA"

10. Pilih gaya pemformatan dan klik OK

Hasil. Excel menyoroti semua pesanan AS

Penjelasan. kami memperbaiki referensi ke kolom C dengan menempatkan simbol $ di depan huruf kolom ($C2). Akibatnya, sel B2, C2 dan sel D2 juga berisi rumus =$C2="USA", sel A3, B3, C3 dan D3 berisi rumus =$C3="USA", dll.

Bisakah saya menggunakan rumus IF dalam pemformatan bersyarat?

Bagaimana Anda menampilkan rumus dalam pemformatan bersyarat?

Bagaimana cara menerapkan pemformatan bersyarat ke banyak sel menggunakan rumus?

Bagaimana cara mewarnai sel di Excel berdasarkan rumus?