10 väsentliga Microsoft Excel-funktioner för dataanalys

1. KONKATENERA

=CONCATENATE är en av de mest avgörande funktionerna för dataanalys eftersom det låter dig kombinera text, siffror, datum etc. från flera celler till en. Funktionen är särskilt användbar för att kombinera data från olika celler till en enda cell. Det är till exempel praktiskt för att skapa spårningsparametrar för marknadsföringskampanjer, bygga API-frågor, lägga till text i ett talformat och flera andra saker.

I exemplet ovan ville jag ha månad och försäljning tillsammans i en enda kolumn. För det har jag använt formeln =CONCATENATE(A2, B2) i cellen C2 för att få Jan$700 som resultat.

Formel: =KONKATERA(celler du vill kombinera)

2. LEN

=LEN är en annan praktisk funktion för dataanalys som i huvudsak matar ut antalet tecken i en given cell. Funktionen är övervägande användbar när du skapar titeltaggar eller beskrivningar som har en teckenbegränsning. Det kan också vara användbart när du försöker ta reda på skillnaderna mellan olika unika identifierare som ofta är ganska långa och inte i rätt ordning.

I exemplet ovan ville jag räkna siffrorna för antalet visningar jag fick varje månad. För detta använde jag formeln =LEN(C2) i cellen D2 för att få 5 som resultat.

Formel: =LAN(cell)

3. VLOOKUP

=VLOOKUP är förmodligen en av de mest igenkännliga funktionerna för alla som är bekanta med dataanalys. Du kan använda den för att matcha data från en tabell med ett indatavärde. Funktionen erbjuder två lägen för matchning - exakt och ungefärlig, som styrs av uppslagsområdet. Om du ställer in intervallet på FALSE kommer det att leta efter en exakt matchning, men om du ställer in det på TRUE kommer det att leta efter en ungefärlig matchning.

I exemplet ovan ville jag slå upp antalet visningar under en viss månad. För det använde jag formeln =VLOOKUP("Jun", A2:C13, 3) i cellen G4 och jag fick 74992 som resultat. Här är "Jun" uppslagsvärdet, A2:C13 är tabellmatrisen där jag letar efter "Jun" och 3 är numret på kolumnen där formeln hittar motsvarande vyer för juni.

Den enda nackdelen med att använda den här funktionen är att den bara fungerar med data som har ordnats i kolumner, därav namnet — vertikal lookup. Så om du har dina data ordnade i rader måste du först göra det omvandla raderna till kolumner.

Formel: =SÖKUP(lookup_value, table_array, col_index_num, [range_lookup])

4. INDEX/MATCH

Ungefär som funktionen VLOOKUP, kommer INDEX- och MATCH-funktionen väl till pass för att söka efter specifik data baserat på ett inmatningsvärde. INDEX och MATCH, när de används tillsammans, kan övervinna VLOOKUP: s begränsningar för att leverera fel resultat (om du inte är försiktig). Så när du kombinerar dessa två funktioner kan de lokalisera datareferensen och söka efter ett värde i en enstaka dimensionsmatris. Det returnerar koordinaterna för data som ett tal.

I exemplet ovan ville jag slå upp antalet visningar i januari. För det använde jag formeln =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Här är A2:C13 kolumnen med data som jag vill att formeln ska returnera, "Jan" är värdet jag vill matcha, A2:A13 är kolumn där formeln hittar "Jan" och nollan betyder att jag vill att formeln ska hitta en exakt matchning för värde.

Om du vill hitta en ungefärlig matchning måste du ersätta nollan med 1 eller -1. Så att 1:an kommer att hitta det största värdet som är mindre än eller lika med uppslagsvärdet och -1 kommer att hitta det minsta värdet som är mindre än eller lika med uppslagsvärdet. Observera att om du inte använder 0, 1 eller -1 kommer formeln att använda 1, by.

Om du nu inte vill hårdkoda månadens namn kan du ersätta det med cellnumret. Så vi kan ersätta "Jan" i formeln som nämns ovan med F3 eller A2 för att få samma resultat.

Formel: =INDEX(kolumn för de data du vill returnera, MATCH(gemensam datapunkt som du försöker matcha, kolumn för den andra datakällan som har den gemensamma datapunkten, 0))

5. MINIFER/MAXIFER

=MINIFS och =MAXIFS påminner mycket om funktionerna =MIN och =MAX, förutom det faktum att de låter dig ta den minsta/maximiuppsättningen värden och matcha dem på vissa kriterier också. Så i huvudsak letar funktionen efter minimi-/maximivärdena och matchar den med indatakriterier.

I exemplet ovan ville jag hitta lägsta poäng baserat på elevens kön. För det använde jag formeln =MINIFS (C2:C10, B2:B10, "M") och jag fick resultatet 27. Här är C2:C10 kolumnen där formeln ska leta efter poängen, B2:B10 är en kolumn där formeln letar efter kriterierna (könet), och "M" är kriteriet.

På samma sätt, för maximala poäng, använde jag formeln =MAXIFS(C2:C10, B2:B10, "M") och fick resultatet 100.

Formel för MINIFS: =MINIFS(min_range, criteria_range1, criteria1,...)

Formel för MAXIFS: =MAXIFS(max_range, criteria_range1, criteria1,...)

6. AVERAGEIFS

Funktionen =AVERAGEIFS låter dig hitta ett medelvärde för en viss datamängd baserat på ett eller flera kriterier. När du använder den här funktionen bör du komma ihåg att varje kriterium och medelintervall kan vara olika. Men i funktionen =MEDELOMMÅSTE både kriterieintervallet och summaintervallet ha samma storleksintervall. Lägger du märke till skillnaden mellan singular och plural mellan dessa funktioner? Tja, det är där du måste vara försiktig.

I det här exemplet ville jag hitta medelpoängen baserat på elevernas kön. För det använde jag formeln =AVERAGEIFS(C2:C10, B2:B10, "M") och fick 56,8 som resultat. Här är C2:C10 intervallet där formeln söker efter medelvärdet, B2:B10 är kriterieintervallet och "M" är kriteriet.

Formel: =MEDELIFS(medelintervall, kriterieintervall1, kriterie1,...)

7. COUNTIFS

Om du nu vill räkna antalet instanser en datamängd uppfyller specifika kriterier, måste du använda funktionen =COUNTIFS. Den här funktionen låter dig lägga till obegränsade kriterier till din fråga, och gör det därmed det enklaste sättet att hitta antalet baserat på inmatningskriterierna.

I det här exemplet ville jag hitta antalet manliga eller kvinnliga studenter som fick godkänt betyg (dvs >=40). För det använde jag formeln =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Här är B2:B10 intervallet där formeln söker efter det första kriteriet (kön), "M" är det första kriteriet, C2:C10 är intervallet där formeln kommer att leta efter det andra kriteriet (betyg), och ">=40" är det andra kriterier.

Formel: =COUNTIFS(criteria_range1, criteria1,...)

8. SUMPRODUKT

Funktionen =SUMMAPRODUKT hjälper dig att multiplicera intervall eller arrayer tillsammans och returnerar sedan summan av produkterna. Det är en ganska mångsidig funktion och kan användas för att räkna och summera arrayer som COUNTIFS eller SUMIFS, men med extra flexibilitet. Du kan också använda andra funktioner inom SUMPRODUCT för att utöka dess funktionalitet ytterligare.

I det här exemplet ville jag hitta summan av alla sålda produkter. För det använde jag formeln =SUMPRODUKT(B2:B8, C2:C8). Här är B2:B8 den första matrisen (mängden sålda produkter) och C2:C8 är den andra matrisen (priset för varje produkt). Formeln multiplicerar sedan kvantiteten av varje såld produkt med dess pris och lägger sedan ihop allt för att leverera den totala försäljningen.

Formel: =SUMMAPRODUKT(array1, [array2], [array3],...)

9. TRIM

=TRIM-funktionen är särskilt användbar när du arbetar med en datamängd som har flera mellanslag eller oönskade tecken. Funktionen låter dig enkelt ta bort dessa mellanslag eller tecken från dina data, vilket gör att du kan få exakta resultat medan du använder andra funktioner.

I det här exemplet ville jag ta bort alla extra mellanslag mellan orden Mouse och pad i A7. För det använde jag formeln =TRIM(A7).

Formeln tog helt enkelt bort de extra mellanslagen och gav resultatet Musmatta med ett enda mellanslag.

Formel: =TRIMMA(text)

10. HITTA/SÖK

Avrundning är funktionerna FIND/SEARCH som hjälper dig att isolera specifik text i en datamängd. Båda funktionerna är ganska lika i vad de gör, förutom en stor skillnad - funktionen =FIND returnerar bara skiftlägeskänsliga matchningar. Samtidigt har =SÖK-funktionen inga sådana begränsningar. Dessa funktioner är särskilt användbara när du letar efter anomalier eller unika identifierare.

I det här exemplet ville jag hitta antalet gånger "Gui" dök upp inom Guiding Tech för vilka jag använde formeln =FIND(A2, B2), som gav resultatet 1. Om jag nu vill hitta antalet gånger "gui" förekom i Guiding Tech istället, skulle jag behöva använda =SÖK-formeln eftersom den inte är skiftlägeskänslig.

Formel för att hitta: =HITTA(hitta_text, inom_text, [startnummer])

Sökformel: =SÖK(hitta_text, inom_text, [startnummer])