Extrakcia a čistenie údajov je prvou a najdôležitejšou úlohou akéhokoľvek projektu analýzy údajov. Za päť rokov praxe som získal údaje, ktoré vyzerali ako odpadky, ale pomocou funkcií Excelu SPRÁVNE a VĽAVO som ich dokázal zmeniť na zmysluplné správy.
Čistenie údajov v programe Excel je jednoduché. Tu vysvetlím, ako môžete z bunky extrahovať konkrétny text v programe Excel 2016 a zanechať za sebou odpadkové údaje.
Na vyššie uvedenom obrázku sú mi poskytnuté údaje v programe Excel. Má názov, Vek, a ak osoba má akékoľvek poistenie alebo žiadne (áno/nie). Aby som tieto údaje mohol použiť, potrebujem extrahovať údaje v rôznych stĺpcoch. Každý stĺpec by mal obsahovať iba jeden druh hodnoty. Akonáhle je to hotové, môžeme v Exceli vytvoriť kontingenčné správy, ktoré nám o údajoch povedia lepšie. Začnime teda.
Funkcia Excel RIGHT na extrahovanie údajov sprava od textu
Začnime tým najľahším. V tomto prípade máme „Má poistenie?“ údaje na konci reťazca. A to je iba jedna postava (Y alebo N). Na extrahovanie týchto údajov použijeme SPRÁVNY vzorec, ktorý Excel poskytuje. Funkcia VPRAVO je k dispozícii v Exceli 2016, Exceli 2013, Exceli 2010 a starších verziách Excelu.
PRAVÁ funkcia v programe Excel obsahuje iba dva argumenty. Najprv TEXT a potom POČET ZNAKOV, ktoré chcete v Exceli extrahovať sprava.
Generický vzorec:
= RIGHT (text, počet_znakov)
Tu máme svoje text v cele B3 a počet znakov, ktoré chceme extrahovať, je 1. Napíšte tento vzorec v bunke Excel D3 alebo kdekoľvek chcete mať tieto údaje.
= SPRÁVNE (B3,1)
Výsledok budete mať v bunke D3. stačí skopírovať alebo ťahať nadol tento vzorec v bunkách nižšie.
Funkcia Excel LEFT na extrahovanie údajov z ľavej časti textu
Teraz poďme na svoj vek. Pred vysvetlením vám ukážem vzorec. Skopírujte tento vzorec do požadovaných buniek.
= VĽAVO (VPRAVO (B3,4), 2)
Vo vyššie uvedenom vzorci sme skombinovali funkcie VĽAVO a VPRAVO, aby sme získali konkrétny text z reťazca. Takto to vyzerá.
Teraz porozumieme tejto chúlostivej formulke.
Každá funkcia funguje zvnútra smerom von. Začnime teda:
SPRÁVNE (B3,4): Jednoducho extrahuje 4 znaky z daného textu. To nám dáva 30_Y. Prečo 4? Pretože vieme, že vek je iba jednou z dvoch postáv a máme 1 „_“ a 1 Y alebo N.
VĽAVO (VPRAVO (B3,4), 2): Teraz máme text, ktorý začína vekom, ktorý má 2 znaky. Tu prichádza do činnosti funkcia DOLEVA. Trvá to SPRÁVNE (B3,4) ako textový argument a extrahuje z neho 2 znaky. To nám dáva 30 pre prvú bunku. Skopírujte vzorec do buniek nižšie a získajte ostatné údaje tak, ako je to znázornené na obrázku vyššie.
Obecný vzorec pre VĽAVO:
= VĽAVO (text, počet_ znakov)
Rovnako ako funkcia RIGHT v programe Excel, aj funkcia LEFT v programe Excel má dva rovnaké argumenty. Iba extrahuje údaje z ľavej časti poskytnutého reťazca.
Funkcia Excel Vľavo a vpravo na extrakciu neznámej dĺžky textu z reťazca
Jednoducho vložte tento vzorec do požadovaných buniek a extrahujte názov z textu v programe Excel.
= VĽAVO (B3, LEN (B3) -5)
V uvedených príkladoch sme poznali presný počet textov, ktoré sme z daného textu mali extrahovať. Teraz však nevieme, aké dlhé je meno. Čo sa dá robiť
Vieme, že za menom nasleduje presných 5 znakov. 1 pre priestor, 2 pre vek, 1 pre podčiarkovník „_“ a jeden pre písmeno Y.
Ak dokážeme získať celý počet textov a potom od nich odčítať 5, budeme mať presný počet textov pre meno.
Na zistenie dĺžky textu v programe Excel môžeme použiť funkciu LEN. Funkcia LEN v Exceli vracia počet zahrnutých znakov.
Syntax:
= LEN (text)
Takže LEN (B3) vráti 20.
LEN (B3) -5 vráti 15. A toto je dĺžka názvu v bunke B3
Nakoniec LEFT (B3, LEN (B3) -5) dáva nám Georgina Schalk ako výsledok. Keď skopírujete vzorec do buniek nižšie, získate všetky mená.
Teraz môžete pripraviť správy takto na základe extrahovaných údajov pomocou kontingenčných tabuliek. Hovorí nám, že polovica našich subjektov má poistenie a druhá polovica nie.
ĽAVÁ a SPRÁVNA FUNKCIA excelu je veľmi účinná a užitočná pri čistení údajov. V kombinácii s funkciou LEN, funkciou FIND a funkciou SUBSTITUTE v programe Excel môžu extrahovať všetky podreťazce v reťazci v programe Excel.
V budúcnosti sa v Exceli dočkáme komplexnejšej extrakcie dát. Zatiaľ ahoj. A áno, nechajte svoje myšlienky v sekcii komentárov nižšie. Rád vám pomôžem.