Sledujte video o zástupných znakoch Excelu
Existujú iba 3 zástupné znaky programu Excel (hviezdička, otáznik a vlnovka) a pomocou nich sa dá veľa urobiť.
V tomto tutoriále vám ukážem štyri príklady, kde tieto zástupné znaky programu Excel absolútne zachraňujú život.
Zástupné znaky programu Excel - úvod
Zástupné znaky sú špeciálne znaky, ktoré môžu nahradiť akékoľvek miesto ľubovoľného znaku (odtiaľ názov - zástupný znak).
V programe Excel sú tri zástupné znaky:
- * (hviezdička) - Predstavuje ľubovoľný počet znakov. Napríklad Ex* môže znamenať Excel, Excel, Príklad, Expert atď.
- ? (otáznik) - Predstavuje jeden jediný znak. Tr? Mp môže napríklad znamenať Trump alebo Tramp.
- ~ (vlnovka) - Používa sa na identifikáciu zástupných znakov (~, *,?) V texte. Povedzme napríklad, že chcete v zozname nájsť presnú frázu Excel*. Ak ako vyhľadávací reťazec použijete Excel*, poskytne vám akékoľvek slovo, ktoré má na začiatku Excel, za ktorým bude nasledovať ľubovoľný počet znakov (napríklad Excel, Excels, Excellent). Aby sme konkrétne hľadali excel*, musíme použiť ~. Náš vyhľadávací reťazec by bol teda vynikajúci ~*. Prítomnosť ~ tu zaisťuje, že Excel číta nasledujúci znak tak, ako je, a nie ako zástupný znak.
Poznámka: Nestretol som sa s mnohými situáciami, kedy potrebujete použiť ~. Napriek tomu je to dobrá funkcia.
Teraz sa pozrime na štyri úžasné príklady, kde zástupné znaky zvládajú všetko ťažké.
Zástupné znaky programu Excel - príklady
Teraz sa pozrime na štyri praktické príklady, kde môžu byť zástupné znaky programu Excel veľmi užitočné:
- Filtrovanie údajov pomocou zástupných znakov.
- Čiastočné vyhľadávanie pomocou zástupných znakov a VLOOKUP.
- Nájdite a nahraďte čiastočné zhody.
- Počítajte prázdne bunky, ktoré obsahujú text.
#1 Filtrujte údaje pomocou zástupných znakov programu Excel
Zástupné znaky programu Excel sa hodia, ak máte veľké množiny údajov a chcete filtrovať údaje na základe podmienky.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
V dátovom filtri môžete použiť zástupný znak hviezdička (*) na získanie zoznamu spoločností, ktoré začínajú abecedou A.
Postupujte takto:
- Vyberte bunky, ktoré chcete filtrovať.
- Prejdite na položku Údaje -> Zoradiť a filtrovať -> Filter (Klávesová skratka - Ctrl + Shift + L).
- Kliknite na ikonu filtra v bunke hlavičky
- Do poľa (pod možnosťou Textový filter) zadajte A*
- Kliknite na tlačidlo OK.
Výsledky sa budú okamžite filtrovať a dajú sa vám 3 názvy - ABC Ltd., Amazon.com a Apple Stores.
Ako to funguje? - Keď za A pridáte hviezdičku (*), Excel by filtroval všetko, čo začína na A. Je to spôsobené tým, že hviezdička (zástupný znak programu Excel) môže predstavovať ľubovoľný počet znakov.
Teraz s rovnakou metodológiou môžete na filtrovanie výsledkov používať rôzne kritériá.
Ak chcete napríklad filtrovať spoločnosti, ktoré začínajú abecedou A a obsahujú v nej abecedu C, použite reťazec A*C. Získate tak iba 2 výsledky - ABC Ltd. a Amazon.com.
Ak použijete A? C. namiesto toho dostanete iba ABC Ltd ako výsledok (pretože medzi „a“ a „c“ je povolený iba jeden znak)
Poznámka: Rovnaký koncept je možné použiť aj pri použití rozšírených filtrov programu Excel.
#2 Čiastočné vyhľadávanie pomocou zástupných znakov a VLOOKUP
Čiastočné vyhľadávanie je potrebné, ak musíte v zozname hľadať hodnotu a neexistuje presná zhoda.
Predpokladajme napríklad, že máte súbor údajov, ako je uvedené nižšie, a chcete vyhľadať spoločnosť ABC v zozname, ale v zozname je namiesto ABC spoločnosť ABC Ltd.
V tomto prípade nemôžete použiť bežnú funkciu VLOOKUP, pretože vyhľadávaná hodnota nemá presnú zhodu.
Ak použijete VLOOKUP s približnou zhodou, poskytne vám nesprávne výsledky.
Na získanie správnych výsledkov však môžete použiť zástupný znak vo funkcii VLOOKUP:
Do bunky D2 zadajte nasledujúci vzorec a pre ostatné bunky ho presuňte:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, NEPRAVDA)
Ako tento vzorec funguje?
Vo vyššie uvedenom vzorci je namiesto použitia hodnoty vyhľadávania tak, ako je, lemované na oboch stranách hviezdičkou zástupného znaku programu Excel (*) - „*“ A C2 a „*“
To hovorí Excelu, že musí vyhľadať akýkoľvek text, ktorý obsahuje slovo v C2. Pred alebo za textom v C2 môže mať ľubovoľný počet znakov.
Vzorec teda hľadá zhodu a akonáhle nájde zhodu, vráti túto hodnotu.
3. Nájdite a nahraďte čiastočné zhody
Zástupné znaky programu Excel sú veľmi univerzálne.
Môžete ho použiť v komplexnom vzorci aj v základných funkciách, ako je napríklad Nájsť a nahradiť.
Predpokladajme, že máte údaje uvedené nižšie:
Vo vyššie uvedených údajoch bol región zadaný rôznymi spôsobmi (napríklad Severozápad, Severozápad, Severozápad).
To je často prípad údajov o predaji.
Na vyčistenie týchto údajov a ich zosúladenie môžeme použiť funkciu Hľadať a nahradiť zástupnými znakmi programu Excel.
Postupujte takto:
- Vyberte údaje, na ktorých chcete nájsť text, a nahraďte ho.
- Prejdite na položku Domov -> Nájsť a vybrať -> Prejsť na. Otvorí sa dialógové okno Hľadať a nahradiť. (Môžete tiež použiť klávesovú skratku - Ctrl + H).
- Do dialógového okna Hľadať a nahradiť zadajte nasledujúci text:
- Nájsť čo: Sever*Z*
- Nahradiť s: Severozápad
- Kliknite na položku Nahradiť všetko.
To okamžite zmení všetky rôzne formáty a bude konzistentné so severozápadom.
Ako to funguje?
V poli Hľadať sme použili Sever*Z* ktorý nájde akýkoľvek text, ktorý má slovo sever a kdekoľvek za ním obsahuje abecedu „W“.
Preto pokrýva všetky scenáre (severozápad, severozápad a severozápad).
Funkcia Hľadať a nahradiť nájde všetky tieto inštancie a zmení ich na severozápad a zaistí ich konzistentnosť.
4. Spočítajte prázdne bunky obsahujúce text
Viem, že ste múdry a myslíte si, že Excel už má na to vstavanú funkciu.
Máš absolútnu pravdu!!
To je možné vykonať pomocou funkcie COUNTA.
ALE … Je s tým jeden malý problém.
Pri importovaní údajov alebo použití pracovného hárka iných ľudí si mnohokrát všimnete, že existujú prázdne bunky, aj keď to tak nemusí byť.
Tieto bunky vyzerajú prázdne, ale majú v sebe = ””. Problém je v tom, že
Problém je v tom, že funkcia COUNTA to nepovažuje za prázdnu bunku (považuje to za text).
Pozrite sa na príklad nižšie:
V uvedenom príklade používam funkciu COUNTA na nájdenie buniek, ktoré nie sú prázdne, a vráti 11 a nie 10 (ale jasne vidíte, že iba 10 buniek má text).
Dôvod, ako som už spomenul, je ten, že A11 nepovažuje za prázdny (aj keď by mal).
Ale takto funguje Excel.
Oprava je použiť vo vzorci zástupný znak programu Excel.
Nasleduje vzorec používajúci funkciu COUNTIF, ktorý počíta iba bunky, ktoré obsahujú text:
= COUNTIF (A1: A11, "?*")
Tento vzorec hovorí, že Excel má počítať iba vtedy, ak má bunka aspoň jeden znak.
V ?* kombo:
- ? (otáznik) zaisťuje, aby bol prítomný aspoň jeden znak.
- * (hviezdička) vytvára priestor pre ľubovoľný počet ďalších znakov.
Poznámka: Vyššie uvedený vzorec funguje, ak majú v bunkách iba textové hodnoty. Ak máte zoznam, ktorý obsahuje text aj čísla, použite nasledujúci vzorec:
= COUNTA (A1: A11) -ÚČETNÁ ZÁKAZKA (A1: A11)
Podobne môžete použiť zástupné znaky v mnohých ďalších funkciách programu Excel, ako napríklad IF (), SUMIF (), AVERAGEIF () a MATCH ().
Je tiež zaujímavé poznamenať, že zatiaľ čo vo funkcii VYHĽADÁVAŤ môžete používať zástupné znaky, nemôžete ich používať vo funkcii NÁJSŤ.
Dúfame, že vám tieto príklady poskytnú atmosféru všestrannosti a sily zástupných znakov programu Excel.
Ak máte iný inovatívny spôsob použitia, podeľte sa o to so mnou v sekcii komentárov.
Nasledujúce návody k Excelu vám môžu byť užitočné:
- Použitie COUNTIF a COUNTIFS s viacerými kritériami.
- Vytvorenie rozbaľovacieho zoznamu v programe Excel.
- Operátor kríženia v Exceli
