Podívejte se na video na zástupné znaky aplikace Excel
Existují pouze 3 zástupné znaky aplikace Excel (hvězdička, otazník a vlnovka) a pomocí nich lze hodně udělat.
V tomto tutoriálu vám ukážu čtyři příklady, kde jsou tyto zástupné znaky aplikace Excel absolutními záchranáři.
Zástupné znaky aplikace Excel - úvod
Zástupné znaky jsou speciální znaky, které mohou mít jakékoli místo jakéhokoli znaku (odtud název - zástupný znak).
V aplikaci Excel jsou tři zástupné znaky:
- * (hvězdička) - Představuje libovolný počet znaků. Například Ex* může znamenat Excel, Excels, Example, Expert atd.
- ? (otazník) - Představuje jeden jediný znak. Například Tr? Mp může znamenat Trump nebo Tramp.
- ~ (vlnovka) - Používá se k identifikaci zástupných znaků (~, *,?) V textu. Řekněme například, že chcete v seznamu najít přesnou frázi Excel*. Pokud jako vyhledávací řetězec použijete Excel*, poskytne vám jakékoli slovo, které má na začátku Excel, následované libovolným počtem znaků (například Excel, Excels, Excellent). Abychom konkrétně hledali excel*, musíme použít ~. Náš vyhledávací řetězec by tedy byl excel ~*. Zde přítomnost ~ zajišťuje, že Excel čte následující znak tak, jak je, a ne jako zástupný znak.
Poznámka: Nesetkal jsem se s mnoha situacemi, kdy potřebujete použít ~. Přesto je to dobrá funkce.
Pojďme si nyní projít čtyři úžasné příklady, kdy zástupné znaky zvládnou všechno těžké.
Zástupné znaky aplikace Excel - příklady
Nyní se podívejme na čtyři praktické příklady, kde mohou být zástupné znaky aplikace Excel velmi užitečné:
- Filtrování dat pomocí zástupného znaku.
- Částečné vyhledávání pomocí zástupných znaků a VLOOKUP.
- Najděte a nahraďte dílčí shody.
- Počítejte neprázdné buňky obsahující text.
#1 Filtrujte data pomocí zástupných znaků aplikace Excel
Zástupné znaky aplikace Excel se hodí v případě, že máte velké množiny dat a chcete filtrovat data na základě podmínky.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
V datovém filtru můžete použít zástupný znak s hvězdičkou (*) a získat tak seznam společností, které začínají abecedou A.
Postupujte takto:
- Vyberte buňky, které chcete filtrovat.
- Přejděte na Data -> Třídit a filtrovat -> Filtr (Klávesová zkratka - Control + Shift + L).
- Klikněte na ikonu filtru v buňce záhlaví
- Do pole (pod volbou Textový filtr) napište A*
- Klikněte na OK.
Tím se výsledky okamžitě vyfiltrují a získáte 3 jména - ABC Ltd., Amazon.com a Apple Stores.
Jak to funguje? - Když za A přidáte hvězdičku (*), Excel bude filtrovat cokoli, co začíná na A. Důvodem je, že hvězdička (zástupný znak Excelu) může představovat libovolný počet znaků.
Nyní se stejnou metodikou můžete filtrovat výsledky pomocí různých kritérií.
Pokud například chcete filtrovat společnosti, které začínají abecedou A a obsahují v ní abecedu C, použijte řetězec A*C. Získáte tak pouze 2 výsledky - ABC Ltd. a Amazon.com.
Pokud používáte A? C místo toho získáte pouze ABC Ltd jako výsledek (protože mezi „a“ a „c“ je povolen pouze jeden znak)
Poznámka: Stejný koncept lze použít také při použití rozšířených filtrů aplikace Excel.
#2 Částečné vyhledávání pomocí zástupných znaků a VLOOKUP
Částečné vyhledávání je nutné, pokud musíte v seznamu hledat hodnotu a neexistuje přesná shoda.
Předpokládejme například, že máte sadu dat, jak je uvedeno níže, a chcete vyhledat společnost ABC v seznamu, ale seznam má místo ABC společnost ABC Ltd.
V tomto případě nemůžete použít běžnou funkci VLOOKUP, protože vyhledávací hodnota nemá přesnou shodu.
Pokud použijete VLOOKUP s přibližnou shodou, přinese vám to špatné výsledky.
K získání správných výsledků však můžete použít zástupný znak ve funkci VLOOKUP:
Do buňky D2 zadejte následující vzorec a pro ostatní buňky ji přetáhněte:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Jak tento vzorec funguje?
Ve výše uvedeném vzorci je místo použití hodnoty vyhledávání tak, jak je, lemována na obou stranách hvězdičkou zástupného znaku aplikace Excel (*) - „*“ & C2 & ”*“
To říká Excelu, že musí vyhledat jakýkoli text, který obsahuje slovo v C2. Může mít libovolný počet znaků před nebo za textem v C2.
Vzorec tedy hledá shodu a jakmile shodu získá, vrátí tuto hodnotu.
3. Najděte a nahraďte dílčí shody
Zástupné znaky aplikace Excel jsou poměrně univerzální.
Můžete jej použít ve složitém vzorci i v základních funkcích, jako je Najít a nahradit.
Předpokládejme, že máte data, jak je uvedeno níže:
Ve výše uvedených datech byla oblast zadána různými způsoby (například Severozápad, Severozápad, Severozápad).
To je často případ údajů o prodeji.
Chcete -li tato data vyčistit a zajistit jejich konzistentnost, můžeme použít funkce Najít a nahradit zástupnými znaky aplikace Excel.
Postupujte takto:
- Vyberte data, která chcete najít, a nahraďte text.
- Přejděte na Domů -> Najít a vybrat -> Přejít na. Otevře se dialogové okno Najít a nahradit. (Můžete také použít klávesovou zkratku - Control + H).
- Do dialogového okna Najít a nahradit zadejte následující text:
- Najít co: Sever*Z*
- Nahradit: Severozápad
- Klikněte na Nahradit vše.
To okamžitě změní všechny různé formáty a bude konzistentní se severozápadem.
Jak to funguje?
V poli Najít jsme použili Sever*Z* který najde jakýkoli text, který má slovo sever a kdekoli za ním obsahuje abecedu „W“.
Proto pokrývá všechny scénáře (NorthWest, North West a North-West).
Funkce Najít a nahradit najde všechny tyto instance a změní ji na severozápad a zajistí její konzistentnost.
4. Spočítat neprázdné buňky obsahující text
Vím, že jste chytrý a myslíte si, že Excel již má vestavěnou funkci, jak to udělat.
Máš naprostou pravdu!!
To lze provést pomocí funkce COUNTA.
ALE … Je s tím jeden malý problém.
Když importujete data nebo používáte list jiných lidí, mnohokrát si všimnete, že jsou prázdné buňky, i když to tak nemusí být.
Tyto buňky vypadají prázdné, ale obsahují = ””. Problém je, že
Problém je v tom, že funkce COUNTA to nepovažuje za prázdnou buňku (počítá to jako text).
Viz příklad níže:
Ve výše uvedeném příkladu používám funkci COUNTA k nalezení buněk, které nejsou prázdné, a vrací 11 a ne 10 (ale jasně vidíte, že pouze 10 buněk má text).
Důvod, jak jsem zmínil, je ten, že nepovažuje A11 za prázdnou (i když by měla).
Ale takhle funguje Excel.
Řešením je použít ve vzorci zástupný znak aplikace Excel.
Níže je vzorec používající funkci COUNTIF, který počítá pouze buňky, které obsahují text:
= COUNTIF (A1: A11, "?*")
Tento vzorec říká Excelu, aby počítal, pouze pokud má buňka alespoň jeden znak.
V ?* kombo:
- ? (otazník) zajišťuje, že je přítomen alespoň jeden znak.
- * (hvězdička) dává prostor libovolnému počtu dalších znaků.
Poznámka: Výše uvedený vzorec funguje, pokud mají v buňkách pouze textové hodnoty. Pokud máte seznam, který obsahuje text i čísla, použijte následující vzorec:
= COUNTA (A1: A11)-ÚČETNÍ ZÁKAZKA (A1: A11)
Podobně můžete použít zástupné znaky v mnoha dalších funkcích Excelu, jako jsou IF (), SUMIF (), AVERAGEIF () a MATCH ().
Je také zajímavé poznamenat, že zatímco zástupné znaky můžete použít ve funkci HLEDAT, nemůžete je použít ve funkci NAJÍT.
Doufáme, že vám tyto příklady dodají atmosféru všestrannosti a síly zástupných znaků aplikace Excel.
Pokud máte nějaký jiný inovativní způsob, jak ho použít, podělte se o to se mnou v sekci komentáře.
Následující návody k Excelu vám mohou být užitečné:
- Používání COUNTIF a COUNTIFS s více kritérii.
- Vytvoření rozevíracího seznamu v aplikaci Excel.
- Intersect Operator v Excelu
