11.2 lecke · 2/3 · kb. 6 perc
Formázás képlettel, ismétlődések kiemelése
A beépített szabályok sok mindent tudnak, de a legrugalmasabb a képletes szabály: bármilyen feltételt megfogalmazhatsz, amelynek az eredménye IGAZ vagy HAMIS. Ebben a leckében teljes sorokat színezel, ismétlődéseket jelölsz meg, és csíkos táblázatot készítesz.
Hogyan működik a képletes szabály?
- Jelöld ki a formázandó tartományt (a fejléc nélkül).
- Válaszd a
Kezdőlap › Stílusok › Feltételes formázás › Új szabály…parancsot. - Kattints A formázandó cellák kijelölése képlettel szabálytípusra.
- Az Értékek formázása, ha ez a képlet igaz mezőbe írd be a képletet, egyenlőségjellel kezdve.
- A Formátum… gombbal állítsd be a megjelenést, majd kattints az OK gombra.
A képletnek logikai értéket kell adnia: egy összehasonlítást (=$C2>100000) vagy logikai függvényt (ÉS, VAGY, PÁROSE…). Ahol az eredmény IGAZ, ott megjelenik a formázás.
A legfontosabb szabály
A képletet úgy írd meg, mintha a kijelölt tartomány bal felső cellájába írnád. Az Excel a többi cellára úgy alkalmazza, mintha oda másoltad volna: a relatív hivatkozások elcsúsznak, az abszolútak ($) nem. Ha az A2:D6 tartományt jelölted ki, a képletben a 2. sorra és az A oszlopra gondolj.
Teljes sor kiemelése vegyes hivatkozással
Feladat: emeld ki azokat a teljes sorokat, ahol a rendelés értéke több mint 100 000 Ft.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ügyfél | város | rendelés | dátum |
| 2 | Hegyi Félix | Vác | 85 000 | 2024.03.04 |
| 3 | Szőke Petra | Baja | 142 500 | 2024.03.05 |
| 4 | Molnár Ádám | Paks | 99 900 | 2024.03.05 |
| 5 | Tóth Edina | Ócsa | 100 000 | 2024.03.06 |
| 6 | Varga Bence | Vác | 230 000 | 2024.03.07 |
A szabály az A2:D6 tartományra vonatkozik. A Tóth Edina sora (pontosan 100 000) nem kap kiemelést.
Miért pont $C2?
- A $C rögzíti az oszlopot: a sor minden cellája (A, B, C, D) a saját sorának C cellájára nézzen.
- A 2 nincs rögzítve: a 3. sorban a képlet már
=$C3>100000-ként értékelődik ki, a 4.-ben=$C4>100000-ként.
A tipikus hibák:
=C2>100000(dollárjel nélkül): a B oszlop celláinál a képlet a D oszlopra nézne, az A-nál a C-re, így a sor csak töredékesen színeződik.=$C$2>100000(teljesen abszolút): minden cella a C2-re nézne, ezért vagy az egész tartomány színes, vagy semmi.
Több feltétel: ÉS, VAGY
Egy tanár így fogadja el a félévet: a hallgató tartott kiselőadást vagy írt esszét, és a beszámolója legalább 50 pontos.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | hallgató | kiselőadás | esszé | beszámoló |
| 2 | Szép Vince | HAMIS | HAMIS | 93 |
| 3 | Cseh Gergő | IGAZ | IGAZ | 98 |
| 4 | Tóth Antal | IGAZ | HAMIS | 23 |
| 5 | Ráth Bódog | HAMIS | IGAZ | 72 |
| 6 | Rácz Timót | IGAZ | HAMIS | 50 |
A szabály az A2:A6 tartományra vonatkozik: a félévet teljesítők neve kiemelve.
Szép Vince 93 pontja hiába jó, mert sem kiselőadása, sem esszéje nincs. Tóth Antalnak van kiselőadása, de csak 23 pontja. Rácz Timót 50 ponttal éppen teljesít, mert a feltétel „legalább 50” (>=).
Ismétlődések kiemelése
Ismétlődő adatokra a beépített szabály is van (Cellakiemelési szabályok › Ismétlődő értékek…), de nem mindig azt csinálja, amit vársz. Három dolgot érdemes megkülönböztetni:
- Ismétlődő érték (a beépített szabály szerint): ami legalább kétszer előfordul, annak minden előfordulása, az első is.
- Ismétlés (másolat): egy érték második, harmadik… előfordulása. Ezeket törölnéd, ha takarítasz.
- Első előfordulás: minden értékből egy példány, a legfelső. Ez adja a lista egyedi tételeit.
Az utóbbi kettőhöz képletes szabály kell, a DARABTELI (COUNTIF) függvénnyel. A trükk a növekvő tartomány: $A$2:$A2. A kezdete rögzített, a vége relatív, ezért a 2. sorban csak az A2-t, az 5. sorban az A2:A5-öt nézi. A DARABTELI így azt számolja meg, hányszor fordult elő az aktuális érték eddig, felülről számítva.
Ez csak az ismétléseket emeli ki (a második előfordulástól kezdve). Az első előfordulásokhoz írd ezt:
Az alábbi munkalap mindhárom oszlopában ugyanaz a lista áll, de mindegyikre más szabály vonatkozik:
| A | B | C | |
|---|---|---|---|
| 1 | beépített | csak ismétlések | első előfordulás |
| 2 | piros | piros | piros |
| 3 | zöld | zöld | zöld |
| 4 | kék | kék | kék |
| 5 | zöld | zöld | zöld |
| 6 | fehér | fehér | fehér |
| 7 | kék | kék | kék |
| 8 | zöld | zöld | zöld |
A: Ismétlődő értékek beépített szabály. B: =DARABTELI($B$2:$B2;$B2)>1. C: =DARABTELI($C$2:$C2;$C2)=1.
Ellenőrizd: a zöld háromszor szerepel (3., 5., 8. sor). A beépített szabály mindhármat kiemeli, a „csak ismétlések” az 5. és a 8. sort, az „első előfordulás” csak a 3.-at. A piros és a fehér egyszer szerepel, ezért a beépített szabály nem jelöli őket.
Több oszlop együtt
Ha egy sor akkor ismétlés, ha két oszlop együtt egyezik (például név és kód), használd a DARABHATÖBB (COUNTIFS) függvényt ugyanezzel a növekvő tartománnyal: =DARABHATÖBB($A$2:$A2;$A2;$B$2:$B2;$B2)>1. A DARABTELI és a DARABHATÖBB nem különbözteti meg a kis- és nagybetűt.
Csíkos sorok: PÁROSE(SOR())
Széles táblázatban könnyű elcsúszni a sorok között. Segít, ha minden második sor halvány hátteret kap. Ehhez a képletnek nem a cella tartalmát, hanem a helyét kell vizsgálnia.
A SOR (ROW) argumentum nélkül annak a cellának a sorszámát adja, amelyre a szabályt éppen kiértékelik. A PÁROSE (ISEVEN) megvizsgálja, hogy ez páros-e, és IGAZ vagy HAMIS értéket ad. Így a páros sorok kapnak formázást. A páratlanokhoz a =PÁRATLANE(SOR()) való.
| A | B | C | |
|---|---|---|---|
| 1 | név | jan. | febr. |
| 2 | Sárközi Richárd | 555 000 | 403 000 |
| 3 | Csiszár Csongor | 434 000 | 521 000 |
| 4 | Palotás Tivadar | 371 000 | 573 000 |
| 5 | Solymos Tivadar | 463 000 | 474 000 |
| 6 | Csordás Kelemen | 535 000 | 419 000 |
A páros sorszámú sorok (2, 4, 6) kiemelve.
Oszlopokra ugyanez: =PÁROSE(OSZLOP()). Egy körmérkőzéses táblázatban (sorokban és oszlopokban ugyanaz a csapatlista) a =SOR()=OSZLOP() szabály szürkíti az átlót, ahol egy csapat önmagával „játszana”; ez akkor működik, ha az első csapat a 2. sorban és a B oszlopban áll.
Jó tudni
A csíkozás akkor is helyes marad, ha sort szúrsz be vagy törölsz, mert a szabály mindig a cella aktuális helyét vizsgálja. Ha a táblázatot Ctrl+T billentyűkkel táblázattá alakítod, a sávos sorokat a táblázatstílus is megadja, szabály nélkül.
Összefoglalás
- A képletes szabály képletét a kijelölt tartomány bal felső cellájára írd meg, logikai eredménnyel.
- Teljes sor kiemeléséhez rögzítsd az oszlopot, ne a sort:
=$C2>100000. - A beépített Ismétlődő értékek minden előfordulást jelöl;
=DARABTELI($A$2:$A2;$A2)>1csak az ismétléseket,=1az első előfordulásokat. - A
=PÁROSE(SOR())a páros sorokat jelöli: így készül a csíkos táblázat.
Gyakorlás
Töltsd le a gyakorló munkafüzetet, és oldd meg a megadott munkalapok feladatait. A sárga cellákba kerülnek a képletek. Ha elakadsz, nézd meg a megoldást.
Feltételes formázás: gyakorló feladatok
- Határidők Lejárt, még nem kész feladatok egész sorának kiemelése képletes szabállyal (MA, ÉS).
- Ismétlődések Kétszer rögzített számlák kiemelése: beépített ismétlődés-szabály és DARABTELI képlet.