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

Excel tanfolyam · 9.6 lecke

Helyettesítő karakterek a feltételekben

Hány olyan szakkör van, amelynek a nevében szerepel a „robot” szó? Hány diák neve kezdődik K-val? Mennyi a „Csoki”-val kezdődő termékek bevétele? Ha nem pontos egyezést keresel, hanem egy mintát, a helyettesítő karakterek segítenek: a csillag és a kérdőjel. Ezek nemcsak a függvényekben, hanem a keresésben és a szűrésben is működnek.

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

A lecke végére

  • a * és a ? karakterrel mintát adsz meg a feltételben;
  • „kezdete”, „tartalmazza”, „végződése” típusú feltételt írsz;
  • pontos karakterszámra szűrsz;
  • valódi * vagy ? karaktert keresel a ~ jellel;
  • tudod, hol működnek és hol nem a helyettesítő karakterek.

Amit már tudsz

  • a feltételes összesítő függvényeket (9.1–9.4);
  • a feltételt cellából összefűzve (9.5);
  • a szövegszűrőket és a feltételtartományt (7.3, 7.4);
  • a Keresés és cserét (2.7).

A két karakter

KarakterJelentésPéldaTalálat
* (csillag)bármennyi (akár nulla) tetszőleges karakter"K*"Kiss, Kovács, K
? (kérdőjel)pontosan egy tetszőleges karakter"9.?"9.a, 9.b, 9.c (de nem 9.ab)
~ (hullámvonal)a következő * vagy ? valódi karakter, nem helyettesítő"*~?"kérdőjelre végződő szövegek

A helyettesítő karakteres feltételeknél sem számít a kis- és nagybetű: a "*robot*" a „Robotika” szót is megtalálja.

Minták

KérdésFeltételPélda képlet
… kezdődik"Csoki*"=SZUMHA(B2:B200;"Csoki*";D2:D200): a „Csoki”-val kezdődő termékek bevétele
… tartalmazza"*robot*"=DARABTELI(A2:A30;"*robot*")
… végződik"*né"a „-né” végű nevek (például Kovácsné)
pontosan 4 karakter"????"a négybetűs szavak (Rajz, Sakk)
nem tartalmazza"<>*robot*"a „robot” szót nem tartalmazók
cellából, tartalmazza"*"&$H$1&"*"=DARABTELI(A2:A30;"*"&$H$1&"*"): amit a H1-be írsz (9.5)

🔮 Jósold meg!

Egy raktárlistában a cikkszámok számként vannak tárolva: 1001, 1002, 1150, 2001, 2002. Mennyi lesz a =DARABTELI(A2:A6;"1*") eredménye? És ha a cikkszámok szövegként vannak tárolva (2.8)?

Megoldás

Számként tárolva: 0. A helyettesítő karakterek csak szövegre működnek; a számokat a DARABTELI számként hasonlítja, és a „1*” mintát nem alkalmazza rájuk. Szövegként tárolva: 3 (1001, 1002, 1150). Ha számokat akarsz „kezdete” szerint vizsgálni, használj intervallumot: =DARABHATÖBB(A2:A6;">=1000";A2:A6;"<2000") (9.4), vagy szövegfüggvényt (11.2).

Hol működik?

MűködikNem működik
DARABTELI, SZUMHA, ÁTLAGHA és a TÖBB változatokegyszerű összehasonlítás képletben: =A2="K*" csak a szó szerinti „K*” szövegre igaz
Keresés és csere (2.7)a HA feltétele önmagában (helyette: =HA(DARABTELI(A2;"K*");…) vagy szövegfüggvény, 11.2)
AutoSzűrő keresőmező, Irányított szűrés feltételtartománya (7.3, 7.4)számként tárolt értékek
keresőfüggvények pontos egyezéssel (FKERES, HOL.VAN, 10. fejezet)
adatbázisfüggvények feltételtartománya (12. fejezet)

💡 Egy cella vizsgálata mintára

Ha egyetlen cellát kell megvizsgálnod, hogy illeszkedik-e egy mintára, használd a DARABTELI-t egycellás tartománnyal: =DARABTELI(A2;"*robot*")>0 IGAZ-at ad, ha az A2 tartalmazza a „robot” szót. Ez a HA feltételeként vagy feltételes formázásban (8.8) is használható.

Kihívás: szakkörök és termékek mintára

🔍 Mintákkal válaszolj!

Készíts egy kitalált jelentkezési listát (Név, Osztály, Szakkör) legalább 25 sorral, és egy büfétermék-listát (Termék, Eladott db, Bevétel). Legyenek köztük hasonló nevűek: „Robotika”, „Robotfoci”, „Programozás és robotépítés”; „Csokis kifli”, „Csokoládé”, „Kakaós csiga”.

  1. Hányan jelentkeztek olyan szakkörre, amelynek nevében szerepel a „robot” szó?
  2. Hány diák vezetékneve kezdődik „K”-val, és hány utóneve végződik „a”-ra? (Ha a név egy cellában van, gondold végig, melyik mintát használod!)
  3. Hány jelentkező jár bármelyik 9. osztályba ("9.?"), és hány a 10.-be?
  4. Mennyi a „Csok”-kal kezdődő termékek összes bevétele (SZUMHA)?
  5. Készíts kereső cellát (H1): amit beírsz (például „kifli”), annak a tartalmazó termékeknek a darabszámát és bevételét mutassa (9.5).
  6. Emeld ki feltételes formázással (8.8) azokat a sorokat, amelyekben a szakkör neve tartalmazza a H1 szövegét.

Hibavadászat

🐞 1. Peti kevés robotosa

Peti képlete: =DARABTELI(C2:C60;"robot"). Az eredmény 0, pedig a listán sokan járnak „Robotika” és „Robotfoci” szakkörre.

Mi a megoldás?

Csillag nélkül a feltétel pontos egyezést jelent: csak a pontosan „robot” értékű cellákat számolja (1. rész). A „tartalmazza” mintához mindkét oldalra csillag kell: "*robot*"; a „kezdődik” mintához a végére: "robot*".

🐞 2. Lili mindent tudó kérdőjele

Lili egy kérdőív szöveges válaszai között azokat akarta megszámolni, amelyek kérdőjellel végződnek: =DARABTELI(D2:D80;"*?"). Az eredmény az összes nem üres válasz száma lett.

Mit tegyen?

A ? helyettesítő karakter (bármelyik egy karakter), így a "*?" minden legalább egy karakteres szövegre illeszkedik. A valódi kérdőjelhez elé kell tenni a hullámvonalat: "*~?" (1. rész).

Összefoglalás

  • * = bármennyi karakter; ? = pontosan egy karakter; ~ = a következő * vagy ? valódi.
  • Kezdete: "K*"; tartalmazza: "*robot*"; végződése: "*né"; pontos hossz: "????".
  • Cellából: "*"&$H$1&"*".
  • Működik a feltételes összesítő, kereső- és adatbázisfüggvényekben, a szűrésben és a keresésben; nem működik egyszerű összehasonlításban és számokon.
  • Egy cella mintára vizsgálata: =DARABTELI(A2;"minta")>0.

Ellenőrizd magad!

Mit jelent a "*ka" feltétel?

„ka”-ra végződő szöveg.

Hogyan keresel valódi csillagot egy szövegben?

~* formában, például "*~**".

Miért nem működik a "1*" a számként tárolt cikkszámokon?

Mert a helyettesítő karakterek csak szövegre alkalmazhatók.

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (feltételtől függő számítások, szövegkezelés), információkeresés mintával; kapcsolódás az adatbázis-kezeléshez (SQL LIKE operátor: % és _) és a programozáshoz (mintaillesztés).

Módszertan

A lecke a helyettesítő karaktereket mintaillesztésként tanítja, és táblázatba gyűjti, hol működnek és hol nem, mert ez a leggyakoribb tévedés forrása. A jóslás az adattípus szerepét (szám vagy szöveg) a 2.8 tudására építve mutatja meg. Az SQL LIKE operátorral való párhuzam az adatbázis-kezelés témakörét is előkészíti. Lili esete a ~ feloldó jel szükségességét tapasztalati úton mutatja be.

Időterv

A két karakter: 6 perc. Minták és jóslás: 10 perc. Hol működik: 7 perc. Kihívás: 18 perc. Hibavadászat: 4 perc.

Következő lecke: 9.7 Részösszegek és szűrt adatok

Összeg, amely a szűréshez igazodik: a RÉSZÖSSZEG és az ÖSSZESÍT függvény, és az Adatok lap Részösszeg parancsa.