Datautvinning och rengöring är den första och mest avgörande uppgiften för alla dataanalysprojekt. Under mina fem års erfarenhet har jag fått data som såg ut som skräp, men med hjälp av Excel HÖGER och VÄNSTER -funktioner kunde jag göra dem till meningsfulla rapporter.
Datastädning i excel är enkelt. Här ska jag förklara hur du kan extrahera specifik text i excel 2016 från en cell och lämna efter dig skräpdata.
I bilden ovan får jag data i Excel. Det har namn, Ålder, och om personen har någon försäkring eller ingen (J/N). För att använda dessa data måste jag extrahera data i olika kolumner. Varje kolumn bör bara innehålla ett slags värde. När det är klart kan vi göra pivotrapporter i excel som berättar bättre för oss om data. Så låt oss komma igång.
Excel RIGHT -funktion för att extrahera data från höger om texten
Låt oss börja med den enklaste. I det här exemplet har vi "Har försäkring?" data i slutet av strängen. Och det är bara ett tecken (Y eller N). För att extrahera dessa data använder vi RIGHT Formula Excel tillhandahåller. HÖGER -funktionen är tillgänglig i Excel 2016, Excel 2013, Excel 2010 och äldre versioner av Excel.
RIGHT -funktionen i excel tar bara två argument. Först TEXTEN, och sedan ANTALET KARAKTER som du vill extrahera från höger i excel.
Generisk formel:
= HÖGER (text, antal_tecken)
Här har vi vår text i cellen B3 och antalet tecken vi vill extrahera är 1. Skriv bara denna formel i Excel i cell D3 eller var du vill ha dessa data.
= HÖGER (B3,1)
Du kommer att ha ditt resultat i cell D3. bara kopiera eller dra ner denna formel i cellerna nedan.
Excel VÄNSTER -funktion för att extrahera data från vänster om texten
Låt oss nu få vår ålder. Innan jag förklarar, låt mig visa dig formeln. Kopiera denna formel i önskade celler.
= VÄNSTER (HÖGER (B3,4), 2)
I ovanstående formel har vi kombinerat VÄNSTER- och HÖGER -funktioner för att få vår specifika text från strängen. Så här ser det ut.
Låt oss nu förstå den här snålformeln.
Varje funktion fungerar från inåt till utåt. Så låt oss börja med:
HÖGER (B3,4): Det extraherar helt enkelt 4 tecken från en given text. Det ger oss 30_Y. Varför 4? Eftersom vi vet att ålder bara är en av två tecken, och vi har 1 “_” och 1 Y eller N.
VÄNSTER (HÖGER (B3,4), 2): Nu har vi vår text som börjar med ålder på 2 tecken. Här kommer funktionen VÄNSTER till handling. Det tar HÖGER (B3,4) som dess textargument och extraherar 2 tecken från det. Det ger oss 30 för den första cellen. Kopiera formeln i cellerna nedan så får du resten av data enligt bilden ovan.
Generisk formel för VÄNSTER:
= VÄNSTER (text, antal_tecken)
Precis som RIGHT -funktionen i Excel LEFT -funktionen i Excel också tar två samma argument. Det extraherar bara data från vänster om den angivna strängen.
Excel VÄNSTER och höger funktion för att extrahera okänd textlängd från en sträng
Klistra in den här formeln i önskade celler för att extrahera namnet från texten i excel.
= VÄNSTER (B3, LEN (B3) -5)
I exemplen ovan visste vi det exakta antalet texter att extrahera från den givna texten. Men nu vet vi inte hur långt namnet är. Vad kan göras?
Vi vet att namnet följs av exakt 5 tecken. 1 för mellanslag, 2 för åldern, 1 för “_” understrykning och en för Y.
Om vi kan få allt antal text och sedan subtrahera 5 från det så kommer vi att ha det exakta antalet text för namnet.
För att hitta längden på en text i Excel kan vi använda LEN -funktionen. Excel -LEN -funktionen returnerar antalet inblandade tecken.
Syntax:
= LEN (text)
Så LEN (B3) returnerar 20.
LEN (B3) -5 returnerar 15. Och detta längden på namnet i cellen B3
Äntligen VÄNSTER (B3, LEN (B3) -5) ger oss Georgina Schalk som ett resultat. När du kopierar formeln i cellerna nedan får du alla namn.
Nu kan du förbereda sådana här rapporter baserat på extraherad data med hjälp av pivottabeller. Det berättar att hälften av våra försökspersoner har försäkring och den andra hälften inte.
VÄNSTER och HÖGER FUNKTION för excel är mycket kraftfull och användbar för datarengöring. I kombination med LEN -funktionen, FIND -funktionen och SUBSTITUTE -funktionen i excel kan de extrahera alla delsträngar i en sträng i excel.
I framtiden kommer vi att se mer komplex dataextraktion i excel. För nu hejdå. Och ja, lämna dina tankar i kommentarfältet nedan. Jag hjälper dig mer än gärna.