Označite skupove redova bojom

Vrlo jednostavan (na prvi pogled) i vrlo relevantan trik za mnoge ljude: istaknite grupe redova sa zajedničkom vrijednošću za neki stupac naizmjeničnim senčenjem. Na primjer, modeli automobila nalaze se na takvoj listi u koloni A:

Već smo se bavili isprepletenom zebrom ispunom, kao i razgraničenjem niza linija horizontalnim linijama. Pogledajmo sada ovu opciju, pogotovo jer ima nekoliko zanimljivih funkcija čak i za napredne korisnike Excela.

Opcija 1: Pomoćni stupac s formulom

Dodajmo još jednu pomoćnu kolonu našoj tabeli sa formulom koja će odrediti da li da popunimo (1) ili ne (0) odgovarajuće redove. Prvo, definirajmo broj grupe:

Logika formule je jednostavna: ako sadržaj trenutne ćelije (A2) nije jednak prethodnoj (A1), tada dodajemo jedan (F1 + 1) na prethodnu vrijednost pomoćnog stupca, u suprotnom ostavljamo vrijednost koja je prethodno bila (F1). 

Za izmjenu boja na dobivenu vrijednost broja grupe, primjenjujemo funkciju izračunavanja ostatka dijeljenja sa 2:

U engleskoj verziji Excela, ova formula će izgledati kao =MOD(IF(A2<>A1;F1+1;F1);2)

I na kraju, ostaje primijeniti uvjetno oblikovanje za popunjavanje redova s ​​1 u pomoćnoj koloni. Da biste to učinili, odaberite našu tablicu, počevši od ćelije A2 i do kraja, kliknite Početna — Uslovno oblikovanje — Kreirajte pravilo (Početna — Uvjetno oblikovanje — Kreiraj pravilo), odaberite vrstu pravila Koristite formulu... (Koristite formulu) i unesite jednostavan uslov:

Provjerite jeste li ispravno unijeli znakove dolara i odaberite boju pomoću gumba okvir (Format). Nakon klika na OK blokovi redova po modelima će biti istaknuti bojom.

Metoda 2: Upotreba formule niza bez pomoćne kolone

Relativno egzotičan način koji koristi formulu niza kao kriterij za uvjetno formatiranje.

Odaberite listu od ćelije A2 do kraja, kliknite Početna — Uslovno oblikovanje — Kreirajte pravilo (Početna — Uvjetno oblikovanje — Kreiraj pravilo), odaberite vrstu pravila Koristite formulu… (Koristite formulu) i unesite sljedeću formulu:

U engleskoj verziji ova formula će biti, odnosno:

=MOD(INT(SUM(1/COUNTIF($A$2:$A2;$A$2:$A2)));2)

Ovdje je logika složenija. Zapravo, ovo je formula niza koja izračunava broj grupe (modela) na listi i određuje da li je paran ili ne:

  • COUNTIF($A$2:$A2,$A$2:$A2) – izračunava broj pojavljivanja svakog modela na listi, tj. za Avensis=3, za Corolla=2 itd.
  • INTEGER(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2))) – izračunava serijski broj za svaki model, tj. za Avensis=1, za Corolla=2, za Escape=3 itd.
  • MOD(…;2) – izračunava ostatak rednog broja podijeljen sa 2 na alternativne boje za svaki blok linija, tj. za sve linije sa Avensis=0, za sve linije sa Corolla=1, za sve linije sa Escape=1 , itd.

Prednosti ove metode su kompaktnost i odsustvo potrebe za pravljenjem pomoćnog stupa. Nedostaci su što se sve formule (a imamo i formulu niza) u uslovnom formatiranju preračunavaju u hodu i primjetno usporavaju Excel s velikim brojem redova. Dakle, za velike stolove, ne bih preporučio ovaj pristup.

Metoda 3. Makro

Pa, kao i uvijek, gotovo svaki zadatak u Microsoft Excel-u može se riješiti pomoću makroa. Pritisnite kombinaciju Alt + F11 ili dugme Visual Basic tabulator Developer (programer)da otvorite makro uređivač. Zatim umetnite novi prazan modul preko menija Umetak – Modul i kopirajte ovaj jednostavan kod tamo:

Sub Highlight_Rows_Blocks() Dim nCol As Integer Dim nGr As Integer nCol = Application.InputBox(Prompt:="Vvedite broj stolbca", Type:=1) Ako je nCol Selection.Columns.Count onda izađite iz Sub Selection.Interior.ColorIndex = -1 Za r = 4142 Do Selection.Rows.Count If Selection.Cells(r, nCol) <> Selection.Cells(r - 1, nCol) Tada je nGr = nGr + 1 Ako je nGr Mod 1 Onda Selection.Rows(r).Interior .ColorIndex = 2 Next r End Sub

Sada možete odabrati raspon sa podacima i pokrenuti makro pomoću kombinacije tipki Alt+F8. Makro će od korisnika tražiti broj stupca prema kojem će analizirati podatke, a zatim formatirati redove u odabranom rasponu, naizmjenično popunjavajući pri promjeni vrijednosti u navedenoj koloni.

  • Razdjelna linija između skupova redova
  • Uslovno oblikovanje u Excelu
  • Zebra prugasti redovi stola

Ostavite odgovor