Bagaimana mengubah warna baris di Excel dengan kondisi, tergantung pada kondisi

Dalam artikel ini, Anda akan mempelajari cara cepat mengubah latar belakang baris berdasarkan nilai tertentu dalam spreadsheet. Berikut adalah panduan dan berbagai rumus untuk teks dan angka dalam sebuah dokumen.

Sebelumnya, kita telah membahas metode untuk mengubah warna latar belakang sel berdasarkan teks atau nilai numerik di dalamnya. Rekomendasi juga akan disajikan di sini tentang cara menyorot baris yang diperlukan di versi Excel terbaru, berdasarkan konten satu sel. Selain itu, di sini Anda akan menemukan contoh rumus yang berfungsi sama baiknya untuk semua format sel yang memungkinkan.

Cara mengubah tampilan baris berdasarkan angka di sel tertentu

Misalnya, Anda memiliki dokumen yang terbuka dengan tabel transaksi organisasi seperti ini.

Misalkan Anda perlu menyorot baris dalam nuansa yang berbeda, dengan fokus pada apa yang tertulis dalam sel di kolom Qty, untuk memahami dengan jelas transaksi mana yang paling menguntungkan. Untuk mencapai hasil ini, Anda perlu menggunakan fungsi "Pemformatan Bersyarat". Ikuti petunjuk langkah demi langkah:

1. Pilih sel yang ingin Anda format.
2. Buat aturan pemformatan baru dengan mengklik item yang sesuai di menu konteks yang muncul setelah mengklik tombol "Pemformatan Bersyarat" pada tab "Beranda".

1. Setelah itu, kotak dialog akan muncul di mana Anda perlu memilih pengaturan "gunakan rumus untuk menentukan sel yang diformat." Selanjutnya, tuliskan rumus berikut: = $ C2> 4 dalam kotak di bawah ini. Secara alami, Anda dapat memasukkan alamat sel Anda sendiri dan teks Anda sendiri, serta mengganti tanda > dengan < atau =. Selain itu, penting untuk tidak lupa meletakkan tanda $ di depan referensi sel untuk memperbaikinya saat menyalin. Ini memungkinkan untuk mengikat warna garis ke nilai sel. Jika tidak, saat menyalin, alamatnya akan "pindah".
2. Klik "Format" dan beralih ke tab terakhir untuk menentukan warna yang diinginkan. Jika Anda tidak menyukai warna yang disarankan oleh program ini, Anda selalu dapat mengklik "More Colors" dan memilih warna yang Anda butuhkan.
3. Setelah menyelesaikan semua operasi, Anda harus mengklik dua kali tombol "OK". Anda juga dapat mengatur jenis pemformatan lain (jenis font atau gaya batas sel tertentu) pada tab lain dari jendela ini.
4. Di bagian bawah jendela ada panel pratinjau tempat Anda dapat melihat seperti apa sel setelah memformat.
5. Jika semuanya cocok untuk Anda, klik tombol "OK" untuk menerapkan perubahan. Semuanya, setelah melakukan tindakan ini, semua baris di mana sel berisi angka lebih besar dari 4 akan berwarna biru.

Seperti yang Anda lihat, mengubah rona baris berdasarkan nilai sel tertentu di dalamnya bukanlah tugas yang paling sulit. Anda juga dapat menggunakan rumus yang lebih kompleks agar lebih fleksibel dalam menggunakan pemformatan bersyarat untuk mencapai tujuan ini.

Terapkan beberapa aturan sesuai dengan prioritasnya

Contoh sebelumnya menunjukkan opsi untuk menggunakan satu aturan pemformatan bersyarat, tetapi Anda mungkin ingin menerapkan beberapa aturan sekaligus. Apa yang harus dilakukan dalam kasus seperti itu? Misalnya, Anda dapat menambahkan aturan sesuai dengan garis mana dengan angka 10 atau lebih yang akan disorot dalam warna merah muda. Di sini perlu untuk menuliskan formula tambahan = $ C2> 9, dan kemudian menetapkan prioritas sehingga semua aturan dapat diterapkan tanpa saling bertentangan.

1. Pada tab "Beranda" di grup "Gaya", Anda perlu mengklik "Pemformatan Bersyarat" dan di menu yang muncul, pilih "Kelola Aturan" di bagian paling akhir daftar.
2. Selanjutnya, Anda harus menampilkan semua aturan khusus untuk dokumen ini. Untuk melakukan ini, Anda perlu menemukan daftar di bagian atas "Tampilkan aturan pemformatan untuk", dan pilih item "Lembar ini" di sana. Juga, melalui menu ini, Anda dapat mengonfigurasi aturan pemformatan untuk sel tertentu yang dipilih. Dalam kasus kami, kami perlu mengelola aturan untuk seluruh dokumen.
3. Selanjutnya, Anda perlu memilih aturan yang ingin Anda terapkan terlebih dahulu dan memindahkannya ke bagian atas daftar menggunakan panah. Anda akan mendapatkan hasil seperti itu.
4. Setelah menetapkan prioritas, Anda perlu mengklik "OK", dan kita akan melihat bagaimana garis yang sesuai telah berubah warnanya, sesuai dengan prioritas. Pertama, program memeriksa untuk melihat apakah nilai pada kolom Qty lebih besar dari 10, dan jika tidak, apakah lebih besar dari 4.

Mengubah warna seluruh baris berdasarkan teks yang tertulis di dalam sel

Asumsikan bahwa saat bekerja dengan spreadsheet, Anda mengalami kesulitan dengan cepat melacak item mana yang telah dikirimkan dan mana yang belum. Atau mungkin ada yang sudah ketinggalan zaman. Untuk menyederhanakan tugas ini, Anda dapat mencoba memilih baris berdasarkan teks yang ada di sel "Pengiriman". Misalkan kita perlu menetapkan aturan berikut:

1. Jika pesanan terlambat setelah beberapa hari, warna latar belakang garis yang sesuai akan menjadi oranye.
2. Jika barang sudah dikirim, jalur yang sesuai berubah menjadi hijau.
3. Jika pengiriman barang terlambat, maka pesanan yang sesuai harus disorot dengan warna merah.

Dengan kata sederhana, warna garis akan berubah tergantung pada status pesanan.

Secara umum, logika tindakan untuk pesanan terkirim dan jatuh tempo akan sama seperti pada contoh yang dijelaskan di atas. Penting untuk meresepkan formula di jendela pemformatan bersyarat =$E2=»Terkirim» и =$E2=»Telah Jatuh Tempo» masing-masing. Tugas yang sedikit lebih sulit untuk transaksi yang akan kedaluwarsa dalam beberapa hari.

Seperti yang kita lihat, jumlah hari dapat bervariasi antar baris, dalam hal ini rumus di atas tidak dapat digunakan.

Untuk kasus ini ada fungsi =SEARCH(“Telah jatuh tempo”, $E2)>0di mana:

• argumen pertama dalam tanda kurung adalah teks yang terkandung dalam semua sel yang dijelaskan,
• dan argumen kedua adalah alamat sel yang nilainya ingin Anda navigasikan.

Dalam versi bahasa Inggris dikenal sebagai =SEARCH. Ini dirancang untuk mencari sel yang sebagian cocok dengan kueri input.

Tips: Parameter >0 dalam rumus berarti tidak masalah di mana kueri input berada di teks sel.

Misalnya, kolom "Pengiriman" mungkin berisi teks "Mendesak, Jatuh Tempo dalam 6 Jam" dan sel terkait akan tetap diformat dengan benar.

Jika perlu menerapkan aturan pemformatan ke baris di mana sel kunci dimulai dengan frasa yang diinginkan, maka Anda harus menulis =1 dalam rumus alih-alih >0. 

Semua aturan ini dapat ditulis dalam kotak dialog yang sesuai, seperti pada contoh di atas. Hasilnya, Anda mendapatkan yang berikut:

Bagaimana cara mengubah warna sel berdasarkan nilai di sel lain?

Sama seperti dengan baris, langkah-langkah di atas dapat diterapkan ke satu sel atau rentang nilai. Dalam contoh ini, pemformatan hanya diterapkan ke sel di kolom "Nomor pesanan":

Bagaimana menerapkan beberapa kondisi untuk pemformatan

Jika Anda perlu menerapkan beberapa aturan pemformatan bersyarat ke string, maka alih-alih menulis aturan terpisah, Anda perlu membuatnya dengan rumus =ATAU or =И. Yang pertama berarti "salah satu aturan ini benar", dan yang kedua berarti "kedua aturan ini benar."

Dalam kasus kami, kami menulis rumus berikut:

=ИЛИ($F2=»Utang dalam 1 Hari», $F2=»Utang dalam 3 Hari»)

=ИЛИ($F2=»Utang dalam 5 Hari», $F2=»Utang dalam 7 Hari»)

Dan rumusnya =И dapat digunakan, misalnya, untuk memeriksa apakah nomor di kolom Qty adalah. lebih besar dari atau sama dengan 5 dan kurang dari atau sama dengan 10.

Pengguna dapat menggunakan lebih dari satu kondisi dalam rumus.

Sekarang Anda tahu apa yang perlu Anda lakukan untuk mengubah warna baris berdasarkan sel tertentu, Anda memahami cara mengatur beberapa kondisi dan memprioritaskannya, dan cara menggunakan beberapa rumus sekaligus. Selanjutnya, Anda perlu menunjukkan imajinasi.