Loncat ke daftar isi utama

Bagaimana cara mengganti warna baris berdasarkan grup di Excel?

Di Excel, untuk mewarnai setiap baris lainnya mungkin lebih mudah bagi kebanyakan dari kita, tetapi, pernahkah Anda mencoba mewarnai baris secara bergantian berdasarkan perubahan nilai kolom - Kolom A seperti yang ditunjukkan gambar berikut, dalam artikel ini, saya akan berbicara tentang caranya untuk mengganti warna baris berdasarkan grup di Excel.

Warnai baris secara bergantian berdasarkan perubahan nilai dengan kolom helper dan Conditional Formatting

Warnai baris secara bergantian berdasarkan perubahan nilai dengan fitur yang berguna

Warnai baris secara bergantian dengan dua warna berdasarkan perubahan nilai dengan kolom pembantu dan Pemformatan Bersyarat


Warnai baris secara bergantian berdasarkan perubahan nilai dengan kolom helper dan Conditional Formatting

Untuk menyorot baris secara bergantian berdasarkan grup, tidak ada cara langsung bagi Anda, jadi Anda perlu membuat kolom pembantu dan kemudian menerapkan fungsi pemformatan bersyarat untuk mewarnainya. Harap lakukan sebagai berikut:

1. Di sel D1, baris yang sama dari header, masukkan nomornya 0.

2. Dan di sel D2, ketikkan rumus ini: =IF(A2=A1,D1,D1+1) , lalu seret rumus ini ke sel yang ingin Anda terapkan, lihat tangkapan layar:

Note: Dalam rumus di atas, A1, A2 adalah sel pertama dan kedua dari kolom yang nilainya berubah, D1 adalah sel yang Anda masukkan nomor pembantu 0.

3. Kemudian pilih rentang data A2: D18 yang termasuk kolom rumus pembantu, dan klik Beranda > Format Bersyarat > Aturan baru, lihat tangkapan layar:

4. di Aturan Pemformatan Baru kotak dialog, pilih Gunakan rumus untuk menentukan sel mana yang akan diformat bawah Pilih Jenis Aturan bagian, dan masukkan rumus ini = DAN (LEN ($ A2)> 0, MOD ($ D2,2) = 0) ke dalam Memformat nilai yang rumus ini benar kotak teks, lihat tangkapan layar:

Note: A2 adalah sel pertama dari kolom yang Anda warnai berdasarkan, dan D2 adalah sel pertama dari kolom pembantu yang Anda buat dari rentang yang dipilih

5. Lalu klik dibentuk tombol untuk pergi ke Format Cells dialog, dan pilih satu warna yang Anda suka di bawah Mengisi tab, lihat tangkapan layar:

6. Lalu klik OK > OK untuk menutup dialog, dan baris telah disorot secara bergantian berdasarkan kolom tertentu yang nilainya berubah, lihat tangkapan layar:


Warnai baris secara bergantian berdasarkan perubahan nilai dengan fitur yang berguna

Jika metode di atas sulit bagi Anda, Anda dapat menggunakan alat yang berguna-Kutools untuk Excel, Dengan yang Perbedaan yang mencolok fitur, Anda dapat dengan cepat mewarnai baris berdasarkan grup secara bergantian di Excel.

Tip:Untuk menerapkan ini Perbedaan yang mencolok fitur, pertama, Anda harus mengunduh Kutools untuk Excel, lalu terapkan fitur dengan cepat dan mudah.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Klik Kutools > dibentuk > Perbedaan yang mencolok, lihat tangkapan layar:

2.Dalam Distingush perbedaan dengan kolom kunci kotak dialog, lakukan operasi berikut yang Anda butuhkan. Lihat tangkapan layar:

3Setelah menyelesaikan pengaturan, klik Ok untuk menutup dialog, dan Anda akan mendapatkan hasil berikut sesuai kebutuhan:

Klik untuk Mengunduh Kutools for Excel dan uji coba gratis Sekarang!


Warnai baris secara bergantian dengan dua warna berdasarkan perubahan nilai dengan kolom pembantu dan Pemformatan Bersyarat

Jika Anda ingin membuat bayangan baris dengan dua warna berbeda secara bergantian berdasarkan perubahan nilai seperti gambar berikut yang ditunjukkan, ini juga dapat diselesaikan di Excel dengan Pemformatan Bersyarat.

1. Pertama, Anda harus membuat kolom dan rumus pembantu baru sebagai metode pertama dari langkah 1 hingga langkah 2, Anda akan mendapatkan tangkapan layar berikut:

2. Kemudian pilih rentang data A2: D18, lalu klik Beranda > Format Bersyarat > Kelola Aturan, lihat tangkapan layar:

3. di Manajer Aturan Pemformatan Bersyarat kotak dialog, klik Aturan baru tombol, lihat tangkapan layar:

4. Di muncul keluar Aturan Pemformatan Baru dialog, klik Gunakan rumus untuk menentukan sel yang akan diformat bawah Pilih Jenis Aturan, lalu masukkan rumus ini = ISODD ($ D2) (D2 adalah sel pertama dari kolom pembantu yang Anda buat rumusnya), lalu klik dibentuk untuk memilih warna isian yang Anda suka untuk baris ganjil grup, lihat tangkapan layar:

5. Lalu klik OK untuk keluar dari dialog ini untuk mengembalikan yang sebelumnya Manajer Aturan Pemformatan Bersyarat kotak dialog, silakan klik Aturan baru tombol lagi untuk membuat aturan lain untuk baris genap grup.

6. di Aturan Pemformatan Baru kotak dialog, klik Gunakan rumus untuk menentukan sel yang akan diformat bawah Pilih Jenis Aturan sebagai mantan, lalu masukkan rumus ini = ISEVEN ($ D2) (D2 adalah sel pertama dari kolom pembantu yang Anda buat rumusnya), lalu klik dibentuk untuk memilih warna latar belakang lain untuk baris genap grup, lihat tangkapan layar:

7. Lalu klik OK mengembalikan Manajer Aturan Pemformatan Bersyarat, dan Anda dapat melihat dua aturan dibuat sebagai berikut:

8. Lalu klik OK untuk menutup dialog ini, dan Anda dapat melihat rentang data yang Anda pilih telah diarsir dengan dua warna berbeda secara bergantian berdasarkan perubahan nilai kolom.

  • Catatan:
  • 1. Setelah mewarnai baris secara bergantian, Anda dapat menyembunyikan kolom pembantu sesuai kebutuhan, tetapi Anda tidak dapat menghapusnya.
  • 2. Jika tidak ada header dalam rentang data Anda, Anda cukup memasukkan 1 sebagai angka pertama di kolom helper, dan kemudian menerapkan rumus pembantu seperti biasa.

Artikel lainnya:

  • Angka Tambahan Saat Nilai Berubah Di Kolom Lain
  • Misalkan, Anda memiliki daftar nilai di kolom A, dan sekarang Anda ingin menambah angka sebesar 1 di kolom B saat nilai di kolom A berubah, yang berarti angka di kolom B bertambah hingga nilai di kolom A berubah, lalu kenaikan angka dimulai dari 1 lagi seperti screenshot kiri yang ditampilkan. Di Excel, Anda dapat menyelesaikan pekerjaan ini dengan metode berikut.
  • Sisipkan Baris Kosong Saat Nilai Berubah Di Excel
  • Misalkan Anda memiliki berbagai data, dan sekarang Anda ingin menyisipkan baris kosong di antara data ketika nilai berubah, sehingga Anda dapat memisahkan nilai yang sama berurutan dalam satu kolom seperti yang ditunjukkan tangkapan layar berikut. Pada artikel ini, saya akan berbicara tentang beberapa trik bagi Anda untuk mengatasi masalah ini.
  • Menjumlahkan Sel Saat Nilai Berubah Di Kolom Lain
  • Saat Anda mengerjakan lembar kerja Excel, terkadang, Anda mungkin perlu menjumlahkan sel berdasarkan kelompok data di kolom lain. Misalnya, di sini, saya ingin menjumlahkan pesanan di kolom B saat data berubah di kolom A untuk mendapatkan hasil sebagai berikut. Bagaimana Anda bisa mengatasi masalah ini di Excel?
  • Sisipkan Hentian Halaman Saat Nilai Berubah Di Excel
  • Misalkan, saya memiliki berbagai sel, dan sekarang, saya ingin memasukkan jeda halaman ke dalam lembar kerja ketika nilai di kolom A berubah seperti gambar di sebelah kiri yang ditampilkan. Tentu saja, Anda dapat memasukkannya satu per satu, tetapi adakah cara cepat untuk menyisipkan hentian halaman sekaligus berdasarkan nilai yang diubah dari satu kolom?

Alat Produktivitas Kantor Terbaik

Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

tab kte 201905


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
is it possible to make this work when you use filters? if i apply a filter and the helper column are both 2 different odd numbers, then both rows have the same color.... how can we make this apply to filtered data, and still work when unfiltered?
This comment was minimized by the moderator on the site
Hey, thanks for the help. I tried this and the coloring is off by one. Any ideas?
This comment was minimized by the moderator on the site
Modify the formula to apply the conditional formatting to the header as well.=AND(LEN($A1)>0,MOD($D1,2)=0)
This works better for me than trying to highlight just my data rows.
This comment was minimized by the moderator on the site
When you highlight the selection don't highlight the header row.
This comment was minimized by the moderator on the site
I hate helper columns. I'd rather have a huge formula before I put in a helper column. Sometimes Macros are easier, but I'm not always allowed to use macro enabled spreadsheets. When I want to use a macro, I usually run it from my Personal.xlsb, but that doesn't really help with avoiding a helper column.
This comment was minimized by the moderator on the site
Thank you very much for this, it was much appreciated.
This comment was minimized by the moderator on the site
just use 1-prev_value, and you get alternating 1 and 0 s
This comment was minimized by the moderator on the site
Thanks for this formula, been thinking it wasn't possible for a while. Here's a question: Can you format each group to be a different color? Or are you limited to 2 alternating colors?

Thanks!
This comment was minimized by the moderator on the site
Great piece of information. you helped me to present my analysis results better.
This comment was minimized by the moderator on the site
Thank you so much for this post, I've wanted to be able to change row color based on group for a long time. Easy to follow and much appreciated!
This comment was minimized by the moderator on the site
This formula (when used in conditional formatting) would alternate the row color based on group without the assistance of a helper row: =ISODD(SUMPRODUCT(1/COUNTIF($A$1:$A2,$A$1:$A2)))
This comment was minimized by the moderator on the site
This formula works great if your table only has 100 rows. But it really slows down when you have 5,000 rows. Any suggestions to speed up the formula on larger datasets?
This comment was minimized by the moderator on the site
Hello, Barb,
You can try the below formula:
=MOD(SUMPRODUCT(--($A$1:$A1<>$A$2:$A2)),2)


Note: When you select the data range, please exclude the first header row.

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Fantastic! Well done!
This comment was minimized by the moderator on the site
Justin, your formula is nice because it doesn't require a helper column, but for some reason it requires extra RAM and slows the workbook down noticeably. I would recommend using the formula given in the article and just dealing with the helper column.
This comment was minimized by the moderator on the site
Hi Justin. When I use either the helper row method or yours, it formats one row off. Do you know why?
This comment was minimized by the moderator on the site
More than likely - as I did - you selected the whole columns, and not just the data (excl. any headings)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations