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 két karakter
| Karakter | Jelentés | Példa | Talá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és | Feltétel | Pé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ödik | Nem működik |
|---|---|
| DARABTELI, SZUMHA, ÁTLAGHA és a TÖBB változatok | egyszerű ö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”.
- Hányan jelentkeztek olyan szakkörre, amelynek nevében szerepel a „robot” szó?
- 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!)
- Hány jelentkező jár bármelyik 9. osztályba (
"9.?"), és hány a 10.-be? - Mennyi a „Csok”-kal kezdődő termékek összes bevétele (SZUMHA)?
- 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).
- 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.
