Excel táblázatkezelés kezdőknek › 8.8 lecke

Excel tanfolyam · 8.8 lecke

Feltételes formázás saját képlettel

A beépített szabályok egy cella saját értékét vizsgálják. De mi van, ha azt szeretnéd, hogy a 40 pont alatti diákok teljes sora piros legyen? Vagy csak a 9. b-sek közül emelnéd ki a legjobbakat? Ilyenkor saját képletet írsz a feltételes formázásba. A kulcs ugyanaz, mint a 4.6-ban: a dollárjel helye dönti el, mit figyel a képlet.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 9. osztálytól

A lecke végére

  • saját képlettel készítesz feltételes formázási szabályt;
  • a képletet a kijelölés bal felső cellájára írod;
  • vegyes hivatkozással egész sorokat emelsz ki;
  • ÉS-sel, VAGY-gyal több feltételt figyelsz;
  • sávos sorokat, maximumot és lejárt dátumot jelölsz.

Amit már tudsz

  • a feltételes formázás alapjait (8.7);
  • a vegyes hivatkozást (4.6);
  • az ÉS és a VAGY függvényt (8.3);
  • a véletlenszámokat és a MARADÉK függvényt (5.6).

A képletes szabály

  1. Jelöld ki a formázandó tartományt a 2. sortól (fejléc nélkül), például a teljes listát: A2:D31. A kijelölés bal felső cellája (itt A2) az aktív cella.
  2. Kezdőlap → Feltételes formázás → Új szabály → A formázandó cellák kijelölése képlettel.
  3. Írd be a képletet úgy, mintha az aktív cellába (A2) írnád, és IGAZ vagy HAMIS eredményt adna: például =$C2<40 (a 2. sor pontszáma kisebb, mint 40).
  4. Formátum… gomb: válaszd ki a kitöltést, a betűszínt. OK, OK.
  5. Az Excel a képletet a kijelölés minden cellájára „lemásolja”, ugyanúgy, mint a kitöltőfogantyú (4.4): a dollárjel nélküli részek elmozdulnak, a dollárjelesek maradnak.

💡 Próbáld ki előbb a munkalapon!

Ha bizonytalan vagy, írd be a képletet egy üres oszlop 2. sorába (például =$C2<40), töltsd ki lefelé, és nézd meg, hol lesz IGAZ. Ha jó, másold be a szabályba, a segédoszlopot pedig töröld.

A dollárjel helye

🔮 Jósold meg!

Kijelölöd az A2:D31 tartományt (Név, Osztály, Pontszám, Jegy). A Pontszám a C oszlopban van. Mi lesz kiemelve az egyes képletekkel? (a) =$C2<40 (b) =C2<40 (c) =$C$2<40

Megoldás

(a) A 40 alatti pontszámú diákok teljes sora: az oszlop rögzített (mindig a C-t nézi), a sor pedig vándorol (minden sor a sajátját). (b) Kuszaság: az A oszlop a C-t, a B oszlop a D-t, a C oszlop az E-t nézi… mert az oszlop is elmozdul. (c) Vagy minden cella ki van emelve, vagy egy sem: mindegyik cella a C2-t, az első diák pontszámát vizsgálja. Egész sor kiemeléséhez tehát: dollár az oszlop elé, a sor elé nem (vegyes hivatkozás, 4.6).

CélHivatkozásPélda
egész sor egy oszlop alapján$C2=$C2<40
egész oszlop egy sor alapjánC$1=C$1="hétvége"
rögzített határ (bemeneti cella)$H$1=$C2<$H$1
csak a cella saját értékeC2 (ha csak a C oszlopot jelölöd ki)=C2<40

Hasznos minták

FeladatKéplet (aktív cella: A2)
9. b-s és legalább 40 pontos sorok=ÉS($B2="9.b";$C2>=40)
9. a-s vagy 9. b-s sorok=VAGY($B2="9.a";$B2="9.b")
minden második sor (sávozás)=MARADÉK(SOR();2)=0 (a SOR függvény a cella sorszámát adja)
a sor legnagyobb értéke (B2:E2 kijelölve, aktív: B2)=B2=MAX($B2:$E2)
lejárt határidő (a D oszlopban dátum)=$D2<MA() (a MA függvény a mai dátumot adja, 11.6)
hiányzó adat a sorban=DARABÜRES($A2:$D2)>0
véletlenszám 33 és 66 között (A1:A10 kijelölve)=ÉS(A1>=33;A1<=66)

✅ A szín követi az adatot

A képletes szabály a rendezés és a szűrés után is helyes marad, mert minden sor a saját adatait vizsgálja. Ez az, amire a 6.9-ben a lebegő nyíl nem volt képes, és amiért a „szín nem adat” elve (7.1) így teljesül: a szín az adatból következik, nem helyette áll.

Kihívás: kiemelések képlettel

🎲 1. Véletlenszámok sávja

  1. Az A1:A10 tartományba generálj tíz véletlen egész számot 1 és 100 között (5.6).
  2. Emeld ki képletes feltételes formázással azokat, amelyek 33 és 66 között vannak (a határokat is beleértve).
  3. Nyomd meg néhányszor az F9-et: a kiemelés mindig a jó számokat követi?
  4. Oldd meg ugyanezt a beépített „Között” szabállyal is. Mi a különbség?

📋 2. A pontszámlista teljes sorai

  1. A 8.4 pontszámlistáján jelöld ki a teljes listát (fejléc nélkül), és emeld ki halványpirossal azoknak a teljes sorát, akik a határ (bemeneti cella) alatt vannak.
  2. Egy második szabállyal emeld ki félkövérrel a 9. b-s, legalább 4-es jegyű diákok sorát (ÉS).
  3. Adj a listának sávos sorokat (minden második sor halványszürke).
  4. Rendezd a listát pontszám szerint (7.2): a kiemelések a helyes sorokkal mozognak?
  5. Nyisd meg a Szabálykezelőt, és állítsd be a sorrendet úgy, hogy a piros kiemelés a sávozás „felett” legyen.

Hibavadászat

🐞 1. Peti csúszó kiemelése

Peti a teljes listát (A2:D31) jelölte ki, és ezt a képletet írta: =C2<40. A Név oszlopban jó sorok lettek pirosak, a többi oszlopban viszont összevissza.

Mi a megoldás?

Dollárjel nélkül az oszlop is elmozdul: a B oszlop a D-t, a C az E-t vizsgálja (2. rész). Az oszlopot rögzíteni kell: =$C2<40. A Szabálykezelőben szerkessze a szabályt.

🐞 2. Lili eggyel elcsúszott sorai

Lili az A1:D31 tartományt jelölte ki (a fejléccel együtt), és a képletbe ezt írta: =$C2<40. A kiemelés mindig egy sorral a megfelelő diák fölé került.

Mit tegyen?

A képletnek a kijelölés bal felső cellájára kell vonatkoznia (1. rész). Mivel a kijelölés az A1-ben kezdődik, az első sor (A1) a C2-t vizsgálta, a második (A2) a C3-at, és így tovább. Lili jelölje ki a tartományt a 2. sortól (A2:D31), vagy ha az A1-től jelöl, a képlet legyen =$C1<40 (de akkor a fejléc is vizsgálódik). A legjobb: fejléc nélkül kijelölni.

Összefoglalás

  • Új szabály → A formázandó cellák kijelölése képlettel; a képlet IGAZ vagy HAMIS eredményt ad.
  • A képlet a kijelölés bal felső (aktív) cellájára vonatkozik, és a többire „lemásolódik”.
  • Egész sor: $C2 (oszlop rögzítve, sor vándorol); rögzített határ: $H$1.
  • ÉS, VAGY, MAX, MARADÉK(SOR();2), MA() a képletben is használható.
  • A képletes kiemelés rendezés és szűrés után is helyes marad.

Ellenőrizd magad!

Milyen hivatkozás kell egy egész sor kiemeléséhez a C oszlop alapján?

$C2 (vegyes hivatkozás: oszlop rögzítve).

Melyik cellára kell írni a képletet?

A kijelölés bal felső (aktív) cellájára.

Mit csinál a =MARADÉK(SOR();2)=0 szabály?

Minden második (páros) sort kiemel.

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (feltételes formázás, logikai függvények, relatív, abszolút és vegyes hivatkozás). Az NKP-okostankönyvre épülő emelt szóbeli tétel feladatának („generáljon 10 véletlenszámot, és emelje ki képlettel, feltételes formázással a 33 és 66 közé esőket”) közvetlen gyakorlása; az érettségi feladatok gyakori eleme (sorok kiemelése több feltétel alapján).

Módszertan

A lecke a képletes feltételes formázást a már ismert hivatkozástípusokra (4.4–4.6) vezeti vissza: a jóslás a három hivatkozásváltozat hatását tapasztalati úton mutatja meg. A „próbáld ki előbb a munkalapon” tanács átvihető hibakeresési stratégia. Peti és Lili esete a két leggyakoribb hibát (hiányzó dollár, elcsúszott aktív cella) dolgozza fel. A rendezés utáni ellenőrzés a 6.9-ben és a 7.1-ben felvetett kérdést zárja le.

Időterv

A képletes szabály: 8 perc. A dollárjel helye és jóslás: 10 perc. Hasznos minták: 7 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.

Következő lecke: 8.9 Mini projekt: pontszámok és minősítések

A 8. fejezet zárása: egy verseny értékelőtáblája logikai függvényekkel, hibakezeléssel és feltételes formázással.