01 01
Excel Shading Rows / Columns Formula
Sebagian besar waktu, pemformatan bersyarat digunakan untuk mengubah sel atau warna font sebagai tanggapan terhadap data yang dimasukkan ke dalam sel seperti tanggal yang terlambat atau pengeluaran anggaran yang terlalu tinggi, dan biasanya ini dilakukan dengan menggunakan kondisi preset Excel.
Selain opsi pra-set, juga dimungkinkan untuk membuat aturan pemformatan bersyarat kustom menggunakan rumus Excel untuk menguji kondisi yang ditentukan pengguna.
Salah satu rumus yang menggabungkan fungsi MOD dan ROW , dapat digunakan untuk secara otomatis memadankan baris data alternatif yang dapat membuat data pembacaan dalam lembar kerja besar, jauh lebih mudah.
Dinamis Shading
Keuntungan lain untuk menggunakan rumus untuk menambahkan shading baris adalah bahwa shading bersifat dinamis yang artinya berubah jika jumlah baris berubah.
Jika baris disisipkan atau dihapus bayangan baris menyesuaikan dirinya untuk mempertahankan pola tersebut.
Catatan: Baris alternatif bukan satu-satunya opsi dengan rumus ini. Dengan mengubahnya sedikit, seperti yang dibahas di bawah ini, rumus dapat menaungi pola baris apa pun. Ia bahkan dapat digunakan untuk menaungi kolom alih-alih baris jika Anda memilihnya.
Contoh: Formula Shading Rows
Langkah pertama adalah menyorot rentang sel yang akan diarsir karena rumus hanya mempengaruhi sel-sel yang dipilih.
- Buka lembar kerja Excel — lembar kerja kosong akan berfungsi untuk tutorial ini
- Sorot rentang sel di lembar kerja
- Klik pada tab Home dari pita
- Klik pada ikon Pemformatan Bersyarat pada untuk membuka menu drop down
- Pilih opsi Aturan Baru untuk membuka kotak dialog Aturan Pemformatan Baru
- Klik pada Gunakan Rumus untuk menentukan sel mana yang akan memformat opsi dari daftar di bagian atas kotak dialog
- Masukkan rumus berikut ke dalam kotak di bawah nilai Format di mana nilai ini adalah opsi yang benar di bagian bawah kotak dialog = MOD (ROW (), 2) = 0
- Klik tombol Format untuk membuka kotak dialog Format Cells
- Klik tab Isi untuk melihat opsi warna latar belakang
- Pilih warna yang akan digunakan untuk mem-bayangan baris alternatif dari rentang yang dipilih
- Klik OK dua kali untuk menutup kotak dialog dan kembali ke lembar kerja
- Baris alternatif dalam kisaran yang dipilih sekarang harus diarsir dengan warna isi latar belakang yang dipilih
Menafsirkan Formula
Bagaimana rumus ini dibaca oleh Excel adalah:
- angka 2 dalam rumus menentukan bahwa pola bayangan mengulangi setiap baris kedua dalam rentang yang dipilih
- kondisi = 0 dalam rumus menentukan bahwa baris pertama dalam rentang tidak berbayang - yang dilakukan karena baris ini sering berisi judul yang memiliki formatnya sendiri.
Apa MOD dan ROW Lakukan
Pola tergantung pada fungsi MOD dalam rumus. Apa yang MOD lakukan adalah membagi nomor baris (ditentukan oleh fungsi ROW) dengan angka kedua di dalam kurung dan mengembalikan sisanya atau modulus seperti yang kadang-kadang disebut.
Pada titik ini, pemformatan bersyarat mengambil alih dan membandingkan modulus dengan nomor setelah tanda sama dengan. Jika ada kecocokan (atau lebih tepatnya jika kondisi tersebut BENAR), barisnya diarsir, jika angka di kedua sisi tanda yang sama tidak cocok, kondisi tersebut SALAH dan tidak ada bayangan terjadi untuk baris tersebut.
Sebagai contoh, pada gambar di atas, ketika baris terakhir dalam rentang yang dipilih 18 dibagi dengan 2 oleh fungsi MOD, sisanya adalah 0, sehingga kondisi 0 = 0 adalah TRUE, dan baris diarsir.
Baris 17, di sisi lain, ketika dibagi dengan 2 daun sisa 1, yang tidak sama dengan 0, sehingga baris dibiarkan tidak berubah.
Kolom Shading Alih-alih Rows
Seperti yang disebutkan, rumus yang digunakan untuk membayangi baris alternatif dapat dimodifikasi untuk memungkinkan kolom bayangan juga. Perubahan yang diperlukan adalah menggunakan fungsi COLUMN daripada fungsi ROW dalam rumus. Dengan demikian, rumus akan terlihat seperti ini:
= MOD (COLUMN (), 2) = 0Catatan: Perubahan rumus shading rows untuk mengubah pola bayangan yang diuraikan di bawah ini juga berlaku untuk rumus kolom bayangan.
Ubah Rumus, Ubah Pola Shading
Mengubah pola bayangan mudah dilakukan dengan mengubah salah satu dari dua angka dalam rumus.
- Untuk memiliki bayangan baris dimulai dengan baris pertama, bukan yang kedua, di akhir rumus, ubah = 0 menjadi = 1 ;
- Agar setiap baris ketiga atau keempat berarsir bukannya baris alternatif, ubah 2 dalam rumus menjadi 3 atau 4.
Pembagi tidak boleh Zero atau One
Nomor di dalam kurung disebut pembagi karena itu adalah nomor yang membagi dalam fungsi MOD. Jika Anda ingat kembali dalam kelas matematika yang membagi nol tidak diizinkan dan itu tidak diperbolehkan di Excel juga. Jika Anda mencoba menggunakan nol di dalam tanda kurung sebagai pengganti 2, seperti:
= MOD (ROW (), 0) = 2Anda tidak akan mendapatkan bayangan sama sekali dalam jangkauan.
Atau, jika Anda mencoba menggunakan nomor satu untuk pembagi sehingga rumusnya terlihat seperti:
= MOD (ROW (), 1) = 0setiap baris dalam rentang akan diarsir. Ini terjadi karena nomor apa pun yang dibagi dengan satu meninggalkan sisa nol, dan ingat, ketika kondisi 0 = 0 adalah TRUE, baris akan diarsir.
Ubah Operator, Ubah Pola Shading
Untuk benar-benar mengubah pola, ubah operator kondisional atau pembanding (tanda sama dengan) yang digunakan dalam rumus ke tanda kurang dari (<).
Dengan mengubah = 0 hingga <2 (kurang dari 2) misalnya, dua baris bersama dapat diarsir. Buat itu <3, dan arsiran akan dilakukan dalam kelompok tiga baris.
Satu-satunya pengecualian untuk menggunakan operator yang kurang adalah untuk memastikan bahwa angka di dalam kurung lebih besar dari angka di akhir rumus. Jika tidak, setiap baris dalam rentang akan diarsir.