Ak v programe Microsoft Excel pracujete s importovanými alebo cudzími údajmi, pravdepodobne budete musieť údaje pred použitím trochu vyčistiť. Možno budete musieť odstrániť medzery na začiatku alebo konci hodnôt alebo preformátovať stĺpec hodnôt z textu na čísla alebo naopak. Je tiež bežné analyzovať hodnoty, keď stĺpec obsahuje viac ako jednu hodnotu.
Dobrým príkladom sú mená. Často nájdete celý názov v jednom stĺpci, čo sťažuje prácu s týmito hodnotami. V tomto návode vám ukážem dva rýchle spôsoby analýzy údajov pomocou funkcií Flash Fill a Power Query v Exceli.
Používam Microsoft 365 v systéme Windows 10 64-bit. Reťazcové funkcie môžete použiť v starších verziách vrátane formátu zoznamu .xls. Flash Fill je k dispozícii v Exceli 2013 a novšom a Power Query je k dispozícii v Exceli 2010 a novšom. Excel pre web navyše podporuje flashovú výplň. Existujúce dotazy Power Query môžete otvoriť aj v Exceli pre web, ale nemôžete ich vytvoriť vo verzii prehliadača. Stiahnite si ukážkové súbory.
Ako analyzovať pomocou reťazcových funkcií programu Microsoft Excel
Pred Flash Fill si väčšina analýzy vyžadovala kombináciu funkcií reťazca, ako sú RIGHT(), LEFT(), MID(), FIND() a LEN(). Napríklad výraz v stĺpci D na obrázku A.
= IFERROR(RIGHT(B3, LEN(B3) -IFERROR(NÁJSŤ(““”, B3, NÁJSŤ(““”, B3) +1), NÁJSŤ(““, B3)))), B3)
Vyššie uvedený výraz vráti priezvisko hodnôt v stĺpci B. IFERROR() spracuje chybu, ak nie je žiadna druhá medzera. Našťastie sú naše hodnoty mien vo forme konzistentné, ale nebude to tak vždy. Aj keď je IFERROR() odstránený, stále je to zložitý a únavný výraz, s ktorým sa treba prepracovať.
Flash Fill dokáže dokončiť mnoho úloh analýzy v novších verziách programu Microsoft Excel.
Ako používať Flash Fill na analýzu údajov v programe Microsoft Excel
Funkcia Flash Fill v Exceli existuje od Excelu 2013, takže ju možno poznáte. Keď ho použijete na analýzu, zadajte prvú hodnotu do cieľového stĺpca, aby Excel mohol vyhodnotiť vzor. Niekedy Flash Fill vyžaduje iba jednu hodnotu. Keďže naše údaje sú konzistentné, táto analytická úloha bude jednoduchá.
Pomocou Flash Fill analyzujeme priezviská z hodnôt mien v stĺpci B.
Najprv zadajte Harkins v C3 a stlačte Ctrl + Enter. Flash Fill s najväčšou pravdepodobnosťou nevyplní stĺpec.
Ďalej zadajte niekoľko prvých znakov do Smyth, ako je znázornené na obrázku B. Ako vidíte, Flash Fill teraz obsahuje štýl a šírky na vyplnenie celého stĺpca.
Stlačením klávesu Enter vyplňte zostávajúce bunky, ako je znázornené na obrázku C.
Flash Fill v Exceli je oveľa rýchlejší a jednoduchší ako kombinovanie reťazcových funkcií. Aj keď údaje nie sú konzistentné, táto funkcia robí dobrú prácu pri hľadaní vzoru.
Excel pre web podporuje flashovú výplň. Nájdite ho na karte Údaje. Namiesto stlačenia Ctrl + Enter kliknite na Flash Fill v skupine Data Tools.
Ako používať Power Query na analýzu údajov v programe Microsoft Excel
Power Query je k dispozícii pre používateľov programu Microsoft Excel 2010 a novších. Jeho účelom je obnoviť a vyčistiť dáta, no je nabitý funkciami, ktoré dokážu viac. Pomocou Power Query pridáme stĺpec a analyzujeme priezviská v tomto stĺpci.
Najprv musíme načítať údaje do Power Query takto:
- Kliknite kdekoľvek v tabuľke, ktorú chcete načítať do Power Query.
- Kliknite na kartu Údaje.
- V skupine Získať a transformovať kliknite na položku Z tabuľky/rozsahu.
- Ak ste údaje nenaformátovali ako tabuľku, Power Query vás vyzve, aby ste tak urobili. Zobrazovacia tabuľka je TablePersonnel. Tabuľku nemusíte pomenovať, no s pomenovanou tabuľkou sa pracuje o niečo jednoduchšie.
Obrázok D znázorňuje TablePersonnel v Power Query.
Keď sú údaje v doplnku Power Query, pridajte stĺpec založený na stĺpci Ľudia.
Najprv kliknutím na hlavičku jednotlivcov vyberte stĺpec a kliknite na kartu Pridať stĺpec.
V skupine Všeobecné vyberte z výberu z rozbaľovacieho zoznamu stĺpec príkladov (obr. E). Power Query pridá nový prázdny stĺpec. Na zadanie nových hodnôt priezviska pre tento stĺpec použijeme špeciálnu verziu Flash Fill Power Query.
Do prvej bunky nového stĺpca, stĺpec 1, zadajte Harkins (obr. F). Power Query odpovedá poskytnutím poľa hodnôt priezviska pre celý stĺpec. Pozri sa na nich trochu. Ak nie je správne, zadajte iné priezvisko. Power Query však vráti správny zoznam iba s jedným názvom na nastavenie štýlu pre naše údaje.
Kliknite "OKVytvorí a vyplní nový stĺpec priezviskami, ako je znázornené na obrázku G.
Zostáva len uložiť upravené údaje v programe Microsoft Excel takto:
- Kliknite na kartu Domov.
- V skupine Zavrieť kliknite na položku Zavrieť a načítať v rozbaľovacej ponuke Zavrieť a načítať.
Obrázok H zobrazuje analyzované rodové mená v hárku programu Excel. Power Query používa na pomenovanie nového hárka názov TablePersonnel. V tomto bode môžete premenovať nový stĺpec. Môžete to urobiť aj v Power Query pred uložením údajov.
Flash Fill bol určite rýchlejší, ale znalosť metódy Power Query vám pomôže, keď už pracujete s údajmi v Power Query.
