Extrakce a čištění dat je první a nejdůležitější úkol každého projektu analýzy dat. Za pět let své praxe jsem obdržel data, která vypadala jako odpadky, ale pomocí excelových funkcí VPRAVO a VLEVO jsem je dokázal proměnit ve smysluplné zprávy.
Čištění dat v Excelu je snadné. Zde vysvětlím, jak můžete z buňky extrahovat konkrétní text v aplikaci Excel 2016 a zanechat za sebou odpadky.
Na výše uvedeném obrázku jsou mi poskytnuta data v Excelu. Má to název, Stáří, a pokud osoba má nějaké pojištění nebo žádné (ano/ne). Chcete -li tato data použít, potřebuji extrahovat data v různých sloupcích. Každý sloupec by měl obsahovat pouze jeden druh hodnoty. Jakmile to bude hotové, můžeme v Excelu vytvářet kontingenční zprávy, které nám o datech řeknou lépe. Začněme tedy.
Funkce Excel RIGHT k extrahování dat zprava od textu
Začněme tím nejjednodušším. V tomto příkladu máme „Má pojištění?“ data na konci řetězce. A to je pouze jeden znak (Y nebo N). K extrahování těchto dat použijeme PRAVÝ vzorec, který Excel poskytuje. Funkce VPRAVO je k dispozici v Excelu 2016, Excelu 2013, Excelu 2010 a starších verzích Excelu.
PRAVÁ funkce v Excelu přebírá pouze dva argumenty. Nejprve TEXT a za druhé POČET ZNAKŮ, které chcete v Excelu extrahovat zprava.
Obecný vzorec:
= RIGHT (text, počet_ znaků)
Tady máme své text v buňce B3 a počet znaků, které chceme extrahovat, je 1. Napište tento vzorec v buňce v Excelu D3 nebo kdekoli chcete mít tato data.
= SPRÁVNĚ (B3,1)
Výsledek získáte v buňce D3. stačí zkopírovat nebo přetáhnout dolů tento vzorec v níže uvedených buňkách.
Funkce LEFT v Excelu k extrahování dat z levé části textu
Pojďme nyní k našemu věku. Než vysvětlím, ukážu vám vzorec. Zkopírujte tento vzorec do požadovaných buněk.
= VLEVO (VPRAVO (B3,4), 2)
Ve výše uvedeném vzorci jsme zkombinovali funkce VLEVO a VPRAVO, abychom získali konkrétní text z řetězce. Takhle to vypadá.
Pojďme nyní porozumět tomuto maličkému vzorci.
Každá funkce funguje od vnitřku k vnějšku. Začněme tedy:
SPRÁVNĚ (B3,4): Jednoduše extrahuje 4 znaky z daného textu. To nám dává 30_Y. Proč 4? Protože víme, že věk je pouze jednou ze dvou postav a máme 1 „_“ a 1 Y nebo N.
VLEVO (VPRAVO (B3,4), 2): Nyní máme text, který začíná věkem, který má 2 znaky. Zde přichází do činnosti funkce DOLEVA. Trvá to SPRÁVNĚ (B3,4) jako textový argument a extrahuje z toho 2 znaky. To nám dává 30 pro první buňku. Zkopírujte vzorec do níže uvedených buněk a získáte zbytek dat, jak je znázorněno na obrázku výše.
Obecný vzorec pro VLEVO:
= VLEVO (text, počet_ znaků)
Stejně jako funkce RIGHT v aplikaci Excel, funkce LEFT v aplikaci Excel má také dva stejné argumenty. Pouze extrahuje data z levé části poskytnutého řetězce.
Funkce LEFT a Right Excel k extrahování neznámé délky textu z řetězce
Stačí vložit tento vzorec do požadovaných buněk a extrahovat název z textu v aplikaci Excel.
= VLEVO (B3, LEN (B3) -5)
Ve výše uvedených příkladech jsme znali přesný počet textů, které je třeba z daného textu extrahovat. Ale teď nevíme, jak dlouhé je jméno. Co lze udělat?
Víme, že za jménem následuje přesných 5 znaků. 1 pro mezeru, 2 pro věk, 1 pro podtržítko „_“ a jedno pro Y.
Pokud můžeme získat veškerý počet textů a odečíst od toho 5, pak budeme mít přesný počet textů pro jméno.
Pro zjištění délky textu v Excelu můžeme použít funkci LEN. Funkce LEN v Excelu vrací počet zapojených znaků.
Syntax:
= LEN (text)
Tak LEN (B3) vrací 20.
LEN (B3) -5 vrací 15. A toto je délka jména v buňce B3
Nakonec VLEVO (B3, LEN (B3) -5) nám dává Georgina Schalk jako výsledek. Když zkopírujete vzorec do níže uvedených buněk, získáte všechna jména.
Nyní můžete připravovat sestavy na základě extrahovaných dat pomocí kontingenčních tabulek. Říká nám, že polovina našich subjektů má pojištění a druhá polovina ne.
LEFT and RIGHT FUNCTION of excel je velmi výkonná a užitečná pro čištění dat. V kombinaci s funkcí LEN, funkcí FIND a funkcí SUBSTITUTE v aplikaci Excel mohou extrahovat jakýkoli podřetězec v řetězci v aplikaci Excel.
V budoucnu se v Excelu dočkáme složitější extrakce dat. Zatím ahoj. A ano, zanechte své myšlenky v sekci komentáře níže. Rád vám pomůžu.