Pemformatan Bersyarat dengan Rumus

Pemformatan Bersyarat dengan Rumus di Excel

Excel, secara default, telah memberi kami banyak jenis pemformatan bersyarat untuk dilakukan dengan data kami. Tetap saja, kami ingin melakukan pemformatan pada kriteria atau formula tertentu sebagai pengguna. Kita dapat melakukannya dengan memilih tab “Beranda” di bagian “Pemformatan Bersyarat”. Kemudian, saat kita mengklik “Aturan Baru”, kita dapat menemukan opsi untuk pemformatan bersyarat dengan rumus di mana kita dapat memasukkan rumus yang akan menentukan sel yang akan diformat.

Pemformatan bersyarat dengan formula mengubah format sel berdasarkan kondisi atau kriteria yang diberikan oleh pengguna. Misalnya, Anda mungkin pernah menggunakan pemformatan bersyarat Pemformatan Bersyarat Pemformatan bersyarat adalah teknik di Excel yang memungkinkan kita memformat sel di lembar kerja berdasarkan kondisi tertentu. Ini dapat ditemukan di bagian gaya pada tab Beranda. Baca lebih lanjut untuk menyorot nilai teratas dalam rentang dan nilai duplikat.

Hal baiknya adalah kami memiliki rumus dalam pemformatan bersyarat di excel. Semua rumus harus logis. Hasilnya akan menjadi “BENAR” atau “SALAH”. Jika tes logis lulus, kami akan mendapatkan pemformatan bersyarat. Jika tes logika excelExcel Logical TestSebuah tes logika di Excel menghasilkan output analitik, benar atau salah. Setara dengan operator, ‘=,’ adalah tes logis yang paling umum digunakan.Baca lebih lanjut gagal, kita tidak akan mendapatkan apa-apa.

Anda bebas menggunakan gambar ini di situs web Anda, templat, dll., Harap berikan kami
tautan atribusi

Ringkasan

Izinkan saya memperkenalkan Anda pada pemformatan bersyarat dengan jendela rumus.

Di bagian artikel di bawah ini, kita akan melihat berbagai macam contoh untuk menerapkan pemformatan bersyarat dengan rumus.

Bagaimana Cara Menggunakan Pemformatan Bersyarat Excel dengan Rumus?

#1 – Sorot Sel yang memiliki Nilai Kurang dari 500

Asumsikan Anda bekerja dengan angka penjualan yang diberikan di bawah ini.

Berikut adalah harga jual per unit.

Kemudian, kita harus pergi ke “Pemformatan Bersyarat” dan klik “Aturan Baru”.

Sekarang, pilih “Gunakan formula untuk menentukan sel mana yang akan diformat.”

Sekarang, di bawah “Format nilai di mana rumus benar,” terapkan rumus sebagai A2<500 dan klik “Format” untuk menggunakan pemformatan excel.

Kemudian, kita harus memilih format sesuai pilihan kita.

Sekarang, kita bisa melihat pratinjau formatnya di kotak pratinjau.

Sekarang, klik “OK” untuk menyelesaikan pemformatan. Akibatnya, itu menyoroti semua sel dengan angka <500.

#2 – Sorot Satu Sel Berdasarkan Sel Lain

Kami dapat menyorot satu sel berdasarkan nilai sel lain. Misalnya, asumsikan kita memiliki data “Produk” dan “Harga Jual” di dua kolom pertama.

Kita perlu menyorot produk dari tabel di atas jika harga jual >220.

Langkah 1: Pertama, kita harus memilih rentang “Produk”, buka “Pemformatan Bersyarat”, dan klik “Aturan Baru”.

Langkah 2: Dalam rumus, kita harus menerapkan rumus sebagai B2 > 220.

Langkah 3: Kemudian, klik tombol “Format” dan terapkan format sesuai pilihan.

Langkah 4: Kemudian klik “OK.” Sekarang, kita dapat melihat bahwa pemformatan sudah siap.

#3 – Sorot Semua Sel Kosong di Rentang

Asumsikan kita memiliki data yang diberikan di bawah ini.

Kita harus menyorot semua sel kosong atau kosong pada data di atas karena kita perlu menggunakan rumus ISBLANK Rumus ISBLANK Rumus ISBLANK di Excel adalah fungsi logis yang memeriksa apakah sel target kosong atau tidak. Ini mengembalikan output ‘benar’ jika sel kosong (kosong) atau ‘salah’ jika sel tidak kosong. Ini juga dikenal sebagai fungsi lembar kerja referensi dan dikelompokkan di bawah fungsi informasi Excel baca lebih lanjut di pemformatan bersyarat Excel.

Terapkan rumus di bawah ini di bagian rumus.

Sekarang, pertama, kita harus memilih format sesuai keinginan kita.

Klik “OK.” Ini akan menyorot semua sel kosong dalam rentang yang dipilih.

#4 – Gunakan DAN Berfungsi untuk Menyoroti Sel

Pertimbangkan data di bawah ini untuk rumus ini.

Jika wilayahnya Tengah dan nilai penjualannya >500, kita perlu menyorot baris lengkapnya.

Langkah 1: Kita harus memilih seluruh data terlebih dahulu.

Langkah 2: Selanjutnya, kita perlu menggunakan fungsi AND excel. Gunakan Fungsi AND ExcelFungsi AND di Excel diklasifikasikan sebagai fungsi logika; ia mengembalikan TRUE jika kondisi yang ditentukan terpenuhi, jika tidak ia mengembalikan FALSE.baca lebih lanjut untuk menguji dua kondisi di sini. Kedua kondisi tersebut harus “BENAR”. Kemudian, kita harus menerapkan rumus di bawah ini.

Langkah 3: Kemudian, klik “Format.”

Langkah 4: Kita harus pergi ke “FILL” dan memilih warna dan efek yang diperlukan.

Langkah 5: Sekarang, klik “OK.” Akibatnya, kita dapat melihat baris yang disorot jika kedua kondisinya “BENAR”.

# 5- Gunakan Fungsi ATAU untuk Menyoroti Sel

Sebagai contoh, perhatikan data di bawah ini.

Kami ingin menyorot nama kota “Bangalore” dan “Mysore” dalam rentang data di atas.

Langkah 1: Kita harus memilih data terlebih dahulu, pergi ke “Pemformatan Bersyarat”, dan klik “Aturan Baru”.

Langkah 2: Karena kita perlu menyorot salah satu dari dua sel nilai, kita harus menerapkan fungsi OR excelTerapkan Fungsi OR ExcelFungsi OR di Excel digunakan untuk menguji berbagai kondisi, memungkinkan Anda membandingkan dua nilai atau pernyataan di Excel. Jika setidaknya salah satu argumen atau kondisi bernilai BENAR, itu akan mengembalikan BENAR. Demikian pula, jika semua argumen atau kondisi adalah FALSE, itu akan mengembalikan FASLE.baca lebih lanjut.

Langkah 3: Kemudian, klik “Format” dan pilih format yang diperlukan.

Langkah 4: Setelah pemformatan diterapkan, klik “OK” untuk menyelesaikan tugas. Akibatnya, rumus akan menyorot semua sel dengan nilai “Bangalore” dan “Mysore”.

#6 – Gunakan Fungsi COUNTIF untuk Menyoroti Sel

Asumsikan Anda bekerja dengan database pelanggan. Pertama, Anda perlu mengidentifikasi semua klien yang namanya muncul lebih dari lima kali dalam daftar. Di bawah ini adalah datanya.

Pertama, kita harus memilih data dan menerapkan rumus COUNTIF di bagian rumus.

Kemudian, klik “Format” dan terapkan pemformatan yang diperlukan.

Akibatnya, rumus akan menyorot semua nama jika hitungannya lebih dari 5.

Amber adalah satu-satunya nama yang muncul lebih dari lima kali dalam daftar.

#7 – Sorot Setiap Baris Alternatif

Kita juga dapat memiliki rumus untuk menyorot setiap baris alternatif di excel Menyoroti Setiap Baris Alternatif Di ExcelDi Excel, kita dapat menyorot setiap baris lainnya dengan salah satu dari tiga cara: menggunakan tabel Excel, pemformatan bersyarat, atau pemformatan kustom.baca lebih lanjut datanya. Misalnya, asumsikan di bawah ini adalah data yang sedang kami kerjakan.

Kami akan memilih data terlebih dahulu dan menerapkan rumus di bawah ini.

=MOD(BARIS (), 2)

Kami akan menerapkan format sesuai keinginan kami dan klik “OK.” Akibatnya, kami memiliki setiap baris pengganti yang disorot oleh rumus.

#8 – Sorot Setiap Kolom Alternatif

Seperti bagaimana kami menyoroti setiap baris alternatif dengan cara yang sama, kami dapat menyorot setiap kolom alternatif. Sekali lagi, pertimbangkan data yang sama sebagai contoh.

Dan terapkan rumus di bawah ini:

=MOD(KOLOM(), 2)

Sekarang, salin rumus di atas dan terapkan di bagian rumus pemformatan bersyarat.

Klik “OK.” Kita dapat melihat sekarang bahwa baris alternatif telah disorot seperti yang ditunjukkan di bawah ini.

Jika kita ingin mengabaikan kolom pertama dan menyorot kolom kedua, kita dapat menggunakan rumus di bawah ini.

Oleh karena itu, rumus ini akan menyorot kolom alternatif mulai dari kolom kedua.

Hal-hal untuk diingat

Pemformatan bersyarat hanya menerima rumus logis yang hasilnya hanya “BENAR” atau “SALAH”.
Pratinjau pemformatan bersyarat hanyalah indikasi tampilan pemformatan.
Kita tidak boleh menggunakan acuan mutlak seperti pada rumus. Saat menerapkan rumus, jika kita memilih sel secara langsung, itu akan menjadikannya referensi mutlak. Kita perlu membuatnya menjadi referensi relatif di excel Referensi Relatif Di ExcelDi Excel, referensi relatif adalah jenis referensi sel yang berubah ketika rumus yang sama disalin ke sel atau lembar kerja yang berbeda. Katakanlah kita memiliki =B1+C1 di sel A1, dan kita menyalin rumus ini ke sel B2 dan menjadi C2+D2.baca lebih lanjut.

Artikel yang Direkomendasikan

Artikel ini telah menjadi panduan untuk Pemformatan Bersyarat Excel dengan Rumus. Kami membahas penggunaan pemformatan bersyarat menggunakan rumus seperti COUNTIF, AND, OR, MOD, dll., contoh praktis, dan template Excel yang dapat diunduh. Anda dapat mempelajari lebih lanjut tentang Excel dari artikel berikut: –