10 væsentlige Microsoft Excel-funktioner til dataanalyse

1. FORBINDE

=CONCATENATE er en af ​​de mest afgørende funktioner til dataanalyse, da det giver dig mulighed for at kombinere tekst, tal, datoer osv. fra flere celler til én. Funktionen er især nyttig til at kombinere data fra forskellige celler til en enkelt celle. For eksempel er det praktisk til at oprette sporingsparametre for marketingkampagner, bygge API-forespørgsler, tilføje tekst til et talformat og flere andre ting.

I eksemplet ovenfor ville jeg have måneden og salget samlet i en enkelt kolonne. Til det har jeg brugt formlen =CONCATENATE(A2, B2) i cellen C2 for at få Jan$700 som resultat.

Formel: =SAMLET(celler, du vil kombinere)

2. LEN

=LEN er en anden praktisk funktion til dataanalyse, der i det væsentlige udsender antallet af tegn i en given celle. Funktionen er overvejende brugbar, mens du opretter titeltags eller beskrivelser, der har en tegnbegrænsning. Det kan også være nyttigt, når du prøver at finde ud af forskellene mellem forskellige unikke identifikatorer, som ofte er ret lange og ikke i den rigtige rækkefølge.

I eksemplet ovenfor ville jeg tælle tallene for antallet af visninger, jeg fik hver måned. Til dette brugte jeg formlen =LEN(C2) i cellen D2 for at få 5 som resultat.

Formel: =LANG(celle)

3. VOPSLAG

=VLOOKUP er sandsynligvis en af ​​de mest genkendelige funktioner for alle, der er bekendt med dataanalyse. Du kan bruge den til at matche data fra en tabel med en inputværdi. Funktionen tilbyder to måder at matche på - nøjagtig og omtrentlig, som styres af rækkevidden af ​​opslag. Hvis du indstiller området til FALSK, vil det lede efter et nøjagtigt match, men hvis du indstiller det til TRUE, vil det lede efter et omtrentligt match.

I eksemplet ovenfor ønskede jeg at slå antallet af visninger op i en bestemt måned. Til det brugte jeg formlen =VLOOKUP("Jun", A2:C13, 3) i cellen G4, og jeg fik 74992 som resultat. Her er "Jun" opslagsværdien, A2:C13 er tabelarrayet, hvor jeg leder efter "Jun", og 3 er nummeret på den kolonne, hvor formlen finder de tilsvarende visninger for juni.

Den eneste ulempe ved at bruge denne funktion er, at den kun virker med data, der er blevet arrangeret i kolonner, deraf navnet — vertikal opslag. Så hvis du har dine data arrangeret i rækker, skal du først omsætte rækkerne til kolonner.

Formel: =VOPSLAG(opslagsværdi, tabelmatrix, kolonneindekstal, [områdeopslag])

4. INDEX/MATCH

Ligesom VLOOKUP-funktionen er INDEX- og MATCH-funktionen nyttige til at søge efter specifikke data baseret på en inputværdi. INDEX og MATCH, når de bruges sammen, kan overvinde VLOOKUP's begrænsninger med at levere de forkerte resultater (hvis du ikke er forsigtig). Så når du kombinerer disse to funktioner, kan de lokalisere datareferencen og søge efter en værdi i et enkelt dimensionsarray. Det returnerer koordinaterne for dataene som et tal.

I eksemplet ovenfor ville jeg slå antallet af visninger op i januar. Til det brugte jeg formlen =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Her er A2:C13 den kolonne med data, jeg vil have formlen til at returnere, "Jan" er den værdi, jeg vil matche, A2:A13 er den kolonne, hvor formlen vil finde "Jan", og 0 betyder, at jeg ønsker, at formlen skal finde et nøjagtigt match for værdi.

Hvis du vil finde et omtrentligt match, skal du erstatte 0'et med 1 eller -1. Således at 1'eren vil finde den største værdi mindre end eller lig med opslagsværdien og -1 vil finde den mindste værdi mindre end eller lig med opslagsværdien. Bemærk, at hvis du ikke bruger 0, 1 eller -1, vil formlen bruge 1, by.

Hvis du nu ikke ønsker at fastkode månedens navn, kan du erstatte det med cellenummeret. Så vi kan erstatte "Jan" i formlen nævnt ovenfor med F3 eller A2 for at få det samme resultat.

Formel: =INDEKS(kolonne med de data, du vil returnere, MATCH(fælles datapunkt, du forsøger at matche, kolonne for den anden datakilde, der har det fælles datapunkt, 0))

5. MINIFS/MAXIFS

=MINIFS og =MAXIFS minder meget om funktionerne =MIN og =MAX, bortset fra det faktum, at de tillader dig at tage minimum/maksimum sæt værdier og matche dem på bestemte kriterier også. Så i det væsentlige leder funktionen efter minimums-/maksimumværdierne og matcher dem med inputkriterier.

I eksemplet ovenfor ønskede jeg at finde minimumsscore baseret på elevens køn. Til det brugte jeg formlen =MINIFS (C2:C10, B2:B10, "M") og jeg fik resultatet 27. Her er C2:C10 den kolonne, hvor formlen vil lede efter scorerne, B2:B10 er en kolonne, hvor formlen vil lede efter kriterierne (kønnet), og "M" er kriterierne.

Tilsvarende brugte jeg for maksimale scores formlen =MAXIFS(C2:C10, B2:B10, "M") og fik resultatet 100.

Formel for MINIFS: =MINIFS(min_range, criteria_range1, criteria1,...)

Formel for MAXIFS: =MAXIFS(max_range, criteria_range1, criteria1,...)

6. AVERAGEIFS

Funktionen =AVERAGEIFS giver dig mulighed for at finde et gennemsnit for et bestemt datasæt baseret på et eller flere kriterier. Mens du bruger denne funktion, skal du huske på, at hvert kriterium og gennemsnitsinterval kan være anderledes. I funktionen =MIDDELHVIS skal både kriterieområdet og sumområdet dog have samme størrelsesområde. Lægger du mærke til forskellen på ental og flertal mellem disse funktioner? Nå, det er der, du skal være forsigtig.

I dette eksempel ville jeg finde den gennemsnitlige score baseret på elevernes køn. Til det brugte jeg formlen = AVERAGEIFS(C2:C10, B2:B10, "M") og fik 56,8 som resultat. Her er C2:C10 det område, hvori formlen leder efter gennemsnittet, B2:B10 er kriterieområdet, og "M" er kriterierne.

Formel: =AVERAGEIFS(gennemsnitsområde, kriterieområde1, kriterie1,...)

7. COUNTIFS

Hvis du nu vil tælle antallet af forekomster af et datasæt, der opfylder specifikke kriterier, skal du bruge funktionen =COUNTIFS. Denne funktion giver dig mulighed for at tilføje ubegrænsede kriterier til din forespørgsel, og gør det derved til den nemmeste måde at finde optællingen ud fra inputkriterierne.

I dette eksempel ønskede jeg at finde antallet af mandlige eller kvindelige studerende, der fik beståede karakterer (dvs. >=40). Til det brugte jeg formlen =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Her er B2:B10 det område, hvori formlen vil søge efter det første kriterium (køn), "M" er det første kriterium, C2:C10 er det område, hvori formlen vil søge efter det andet kriterium (karakterer), og ">=40" er det andet kriterier.

Formel: =COUNTIFS(kriterier_område1, kriterier1,...)

8. SUMPRODUKT

Funktionen =SUMPRODUKT hjælper dig med at gange intervaller eller arrays sammen og returnerer derefter summen af ​​produkterne. Det er en ganske alsidig funktion og kan bruges til at tælle og summere arrays som COUNTIFS eller SUMIFS, men med ekstra fleksibilitet. Du kan også bruge andre funktioner i SUMPRODUCT for at udvide dets funktionalitet yderligere.

I dette eksempel ønskede jeg at finde summen af ​​alle de solgte produkter. Til det brugte jeg formlen =SUMPRODUKT(B2:B8, C2:C8). Her er B2:B8 det første array (mængden af ​​solgte produkter) og C2:C8 er det andet array (prisen på hvert produkt). Formlen multiplicerer derefter mængden af ​​hvert solgt produkt med dets pris og lægger det hele sammen for at levere det samlede salg.

Formel: =SUMPRODUKT(matrix1, [array2], [array3],...)

9. TRIMME

=TRIM-funktionen er især nyttig, når du arbejder med et datasæt, der har flere mellemrum eller uønskede tegn. Funktionen giver dig mulighed for nemt at fjerne disse mellemrum eller tegn fra dine data, så du kan få nøjagtige resultater, mens du bruger andre funktioner.

I dette eksempel ville jeg fjerne alle de ekstra mellemrum mellem ordene Mouse og pad i A7. Til det brugte jeg formlen =TRIM(A7).

Formlen fjernede simpelthen de ekstra mellemrum og leverede resultatet Musemåtte med et enkelt mellemrum.

Formel: =TRIM(tekst)

10. FIND/SØG

Afrunding er FIND/SØG-funktionerne, som hjælper dig med at isolere specifik tekst i et datasæt. Begge funktioner er ret ens i, hvad de gør, bortset fra en stor forskel - =FIND-funktionen returnerer kun store og små bogstaver. I mellemtiden har =SØG-funktionen ingen sådanne begrænsninger. Disse funktioner er særligt nyttige, når du leder efter uregelmæssigheder eller unikke identifikatorer.

I dette eksempel ønskede jeg at finde antallet af gange 'Gui' dukkede op i Guiding Tech, hvor jeg brugte formlen =FIND(A2, B2), som leverede resultatet 1. Hvis jeg nu ville finde antallet af gange 'gui' dukkede op i Guiding Tech i stedet, ville jeg være nødt til at bruge =SØG-formlen, fordi den ikke skelner mellem store og små bogstaver.

Formel til at finde: =FIND(find_tekst, inden_tekst, [startnummer])

Formel til søgning: =SØG(find_tekst, inden_tekst, [startnummer])