Excel-formler snydeark: Avanceret vejledning

Så du har gennemgået det grundlæggende, tacklet de mellemliggende ting, og nu er du klar til at kæmpe med de store kanoner - Excels avancerede formler! Excel har mange avancerede funktioner og formler til sofistikerede beregninger, så det er nyttigt at have en guide, der udpeger dem, du har brug for.

Dette snydeark med Excel-formler dækker avancerede prognoseformler, statistisk analyse, datamanipulationsfunktioner, fejlhåndtering og mere.

Denne reference vil udstyre dig med viden om, hvordan du bruger disse avancerede funktioner. Hver formel er ledsaget af klare forklaringer, syntaks og praktiske eksempler for at hjælpe mellemliggende Excel-brugere med at blive avancerede superbrugere.

Download og print snydearket ud og hold det ved hånden.

Ok, lad os komme i gang.

Lad os først komme ind på Array-formler.

Indholdsfortegnelse

• Array formler
  • 1. UNIK funktion
  • 2. SORTERINGSfunktion
  • 3. FILTER Funktion
• Randomisering af Excel-funktioner
  • 1. RANDMELLEM Funktion
  • 2. RANDARRAY Funktion
• Avancerede prognoseformler i Microsoft Excel
  • 1. FORECAST.ETS funktion
  • 2. TREND Funktion
• Avancerede statistiske formler
  • 1. PERCENTIL Funktion
  • 2. KVARTIL Funktion
• Avancerede dataanalyse- og manipulationsformler
  • 1. TRANSPOSER-funktion
  • 2. FREKVENS Funktion
  • 3. GETPIVOTDATA Funktion
• Avanceret fejlhåndtering
• Avancerede opslagsformler
  • 1. XLOOKUP-funktion
  • 2. XMATCH-funktion
• Afsluttende tanker

Array formler

Vores viser dig, hvordan du sorterer og filtrerer dine data manuelt. Avancerede brugere gør dette programmatisk med matrixformler.

Matrixformler giver dig mulighed for at udføre beregninger på flere celler samtidigt. Disse er tre nøglefunktioner:

1. ENESTÅENDE

2. SORTERE

3. FILTER

Nogle af disse funktioner er kun tilgængelige i de seneste versioner af Microsoft Excel.

1. UNIK funktion

Funktionen UNIK accepterer et område og returnerer en liste med unikke værdier.

Antag, at du har salgsdata for beklædningsgenstande. For at finde de unikke elementer i kolonne A, brug denne formel:

=UNIK(A2:A6)

2. SORTERINGSfunktion

SORT-funktionen sorterer indholdet af et område. Syntaksen er:

SORT(matrix, [sorteringsindeks], [sorteringsrækkefølge], [efter_kolonne])

• matrix : rækken af ​​værdier, der skal sorteres.

• sort_index : kolonnen, der skal sorteres (1 som standard)

• sort_order : 1 for stigende (standard) eller 2 for faldende).

• by_col : TRUE for at sortere efter kolonne (standard) eller FALSE for at sortere efter række.

De sidste tre argumenter er valgfrie, og standardindstillingerne er normalt, hvad du ønsker.

Brug denne formel for at sortere eksempeldataene efter den første kolonne:

=SORT(A2:C6)

3. FILTER Funktion

FILTER-funktionen lader dig filtrere et område på en bestemt tilstand. Dette er syntaksen:

=FILTER(matrix, inkludere, [hvis_tom])

• matrix : det område, der skal filtreres.

• include : betingelsen, der bestemmer, hvilke værdier der skal filtreres.

• if_empty : angiver, hvad der skal returneres, hvis ingen værdier opfylder filtreringskriterierne (standard er "").

Antag, at du vil filtrere rækkerne i eksempeldataene til kun at vise, hvor salgsværdien er større end 15 USD. Brug denne formel:

=FILTER(A2:C6; C2:C6>15)

Randomisering af Excel-funktioner

Vores mellemsnyd viser, hvordan man bruger RAND-funktionen, som producerer et tilfældigt tal mellem 0 og 1.

Avancerede Excel-brugere ved, hvordan man bruger randomiseringsfunktionerne til hurtigt at generere prøvedata.

1. RANDMELLEM Funktion

RANDBETWEEN-funktionen er mere fleksibel end RAND, fordi du kan angive de nederste og øverste tal som noget andet end 0 og 1.

For at generere data med tal mellem 1 og 100 skal du indtaste denne formel i celle A1:

=RANDMELLEM(1; 100)

Kopier derefter cellen til så mange rækker og kolonner, som du vil. Det tager sekunder at producere et gitter af randomiserede tal:

2. RANDARRAY Funktion

Du tænker måske, at det ville være rart at undgå den manuelle kopi af RANDBETWEEN-funktionen. For at blive superavanceret kan du bruge den nye RANDARRAY-funktion i den seneste version af Microsoft Excel.

Syntaksen er:

RANDARRAY([rækker], [kolonner], [min], [maks], [helt antal])

• rækker : antal rækker

• kolonner : antal kolonner

• min : laveste tal

• max : højeste antal

• heltal : standard til TRUE, ellers bruger decimaltal.

For at generere tilfældige tal mellem 1 og 100 på tværs af seks rækker og ni kolonner, skal du bruge denne formel:

=RANDARRAY(6; 9; 1; 100; SAND)

Avancerede prognoseformler i Microsoft Excel

Excels prognosefunktioner bruges til at forudsige fremtidige værdier baseret på eksisterende datatendenser. Disse funktioner hjælper med at identificere mønstre og projekttendenser baseret på dine data.

1. FORECAST.ETS funktion

Den ældre FORECAST-funktion blev erstattet med et sæt nyere funktioner i Excel 2016.

Du vælger funktionen ud fra den specifikke prognosemodel, du ønsker. For eksempel bruger FORECAST.ETS-funktionen den eksponentielle udjævningsalgoritme .

Syntaksen er:

FORECAST.ETS(måldato, værdier, tidslinje)

• target_date : den dato, du ønsker en beregnet værdi for.

• værdier : de historiske data.

• tidslinje : en række datoer

Antag, at du har datoer fra den 1. til den 5. januar i kolonne A og salgsbeløb i kolonne B. Denne formel forudsiger det næste salgsbeløb:

=PROGNOSE.ETS("6. januar 2023", B2:B6, A2:A6)

2. TREND Funktion

TREND-funktionen projicerer et sæt værdier baseret på mindste kvadraters metode. Det returnerer et array. Syntaksen er:

TREND(kendt_y, [kendt_x], [ny_x], [konst])

• kendt_y : område af y-værdier

• kendt_x : række af x-værdier

• new_x : række af beregnede værdier

Ofte er de kendte_y datapunkterne, mens de kendte_x er datoerne.

Ved at bruge de samme data som i det foregående eksempel kan du indtaste nedenstående formel i celle C2. Et sæt værdier vil blive genereret.

=TREND(B2:B6; A2:A6)

Avancerede statistiske formler

De avancerede statistiske funktioner omfatter beregning af percentiler og kvartiler. Nogle matematiske funktioner er tilgængelige for bagudkompatibilitet, men det anbefales at bruge de mest opdaterede versioner.

1. PERCENTIL Funktion

Denne funktion beregner procentdelen af ​​datapunkter, der falder under en bestemt værdi. Syntaksen er:

PERCENTIL.INC(matrix, k)

• array : celleområdet

• k : percentilen fra 0 til 1

Antag, at du vil beregne den 70. percentil af data i kolonne B. Brug denne formel:

=PERCENTIL.INC(B2:B6; 0,7)

2. KVARTIL Funktion

Denne funktion er en variation af percentilfunktionen, men bruger kvartaler til at dividere dataene. Dette er syntaksen:

KVARTILE.INC(matrix, kvart)

• array : rækken af ​​data

• quart : 1 for 25. percentil, 2 for 50., 3 for 75. og 4 for maksimum.

Formlen nedenfor vil beregne den første kvartil af data i kolonne A.

=KVARTIL.INC(A2:A5; 1)

Avancerede dataanalyse- og manipulationsformler

Adskillige avancerede funktioner lader dig skifte dataformat, analysere frekvensfordelinger og udtrække data fra pivottabeller.

1. TRANSPOSERER

2. FREKVENS

3. GETPIVOTDATA

1. TRANSPOSER-funktion

Nogle gange vil du flytte dataene i dine rækker til kolonner og omvendt. Du kan gøre dette manuelt eller bruge TRANSPOSER-funktionen i stedet for.

Antag, at du har elementerne "T-shirt", "Hoodie" og "Jeans" i cellerne A2, A3 og A4. Du ønsker at gøre disse til kolonneoverskrifter. Denne funktion returnerer værdierne i en enkelt række:

=TRANSPOSER(A2:A4)

2. FREKVENS Funktion

Denne funktion beregner frekvensfordelingen af ​​værdier i et datasæt. Dette er syntaksen:

FREKVENS(dataarray, bins_array)

• data_array : række af værdier.

• bins_array : de intervaller, der skal bruges.

Antag, at du har salgsdata i kolonne B, og du vil analysere frekvensfordelingen af ​​værdierne baseret på, hvor mange beløb der er:

• under 20.

• fra 20 til 80.

• over 80.

Det repræsenterer tre bins og kan beregnes med denne formel:

=FREKVENS(A2:A6; {20,80})

For mere om frekvensfordelinger i Excel, se denne video:

3. GETPIVOTDATA Funktion

Denne funktion lader dig udtrække opsummerede oplysninger fra pivottabeller. Dette er syntaksen:

GETPIVOTDATA(datafelt, pivottabel, [felt1, element1], [felt2, element2], …)

• data_field : det datafelt eller den værdi, du vil hente fra pivottabellen.

• pivot_table : en reference til pivottabellen.

• felt1, element1 osv. : felt/varepar, der skal filtreres efter.

Antag, at du har en pivottabel baseret på farven på de solgte varer. For at udtrække salget for røde varer, brug denne formel:

=GETPIVOTDATA("Salg", A1, "Farve", "Rød")

Avanceret fejlhåndtering

Selv de mest basale Excel-formler kan give fejl. Mellemliggende brugere bør vide, hvordan man bruger ISERROR til at håndtere fejl. Avancerede brugere bør også være fortrolige med funktionen ERROR.TYPE til fejlidentifikation.

Funktionen ERROR.TYPE hjælper med at identificere den specifikke fejltype i en celle eller formel.

Det returnerer en numerisk værdi, der svarer til forskellige fejltyper, såsom #N/A, #VALUE!, #REF! og mere.

Antag, at du har en fejl i celle A1, og du vil identificere dens fejltype. Følgende formel returnerer det tal, der svarer til den specifikke fejl:

=FEJL.TYPE(A1)

Du kan kombinere dette med flere funktioner for at reagere forskelligt afhængigt af typen af ​​fejl. Disse er de mest almindelige fejl og deres værdier:

1. #NUL! (ingen fælles celle fundet i et område)

2. #DIV/0! ( division med nul eller en tom celle)

3. #VÆRDI! (upassende datatype eller argument i en formel)

4. #REF! (en refereret celle er blevet slettet, eller der er en )

5. #NAVN? (Excel genkender ikke funktionen eller rækkevidden)

6. #NUM! (ugyldig numerisk værdi)

7. #N/A (værdi kan ikke findes)

Antag, at du vil håndtere tre specifikke fejltyper. Brug denne formel til at vise en specifik fejlmeddelelse baseret på typen:

=HVIS(FEJL(C2), HVIS(FEJL.TYPE(C2)=2, "Divider med nul fejl", HVIS(FEJL.TYPE(C2)=3, "Ugyldig værdifejl", HVIS(FEJL.TYPE(C2) )=7, "Værdi ikke fundet fejl", "Anden fejl"))), C2)

Avancerede opslagsformler

Vores snydeark for begyndere og øvede dækkede en . Her er nogle avancerede muligheder:

1. XLOOKUP

2. XMATCH

1. XLOOKUP-funktion

Denne opslagsfunktion giver dig mulighed for at søge efter en værdi i et område og returnere en tilsvarende værdi fra en anden kolonne eller et andet område.

Det giver mere alsidighed end simplere opslagsfunktioner som . Dette er syntaksen:

XLOOKUP(opslagsværdi, opslagsmatrix, returmatrix, [match-tilstand], [søgetilstand], [hvis_ikke_fundet])

• lookup_value : den værdi, du vil søge efter.

• lookup_array : området for opslag.

• return_array : det område, der viser den tilsvarende værdi.

• match_mode : eksakt match (0), næste mindre (1), næste større (-1) eller jokertegn match (2).

• search_mode : -1 for top til bund, 1 for bund til top eller 2 for binær søgning.

• if_not_found : indstiller værdien til at returnere, hvis der ikke findes noget match.

Antag, at du vil søge i en række data for den første forekomst af en tøjvare og returnere salgsbeløbene. Denne formel vil lede efter teksten "Hoodie" og returnere værdien i den tilstødende celle, hvis den findes:

=XLOOKUP(“Hættetrøje”, A2:A6, B2:B6)

2. XMATCH-funktion

Denne funktion giver dig mulighed for at finde positionen af ​​en specificeret værdi inden for et område eller en matrix. Dette er syntaksen:

XMATCH(opslagsværdi, opslagsmatrix, [matchtype], [søgetilstand])

• lookup_value : den værdi, du vil finde.

• lookup_array : Det område, du vil søge efter.

• match_type : eksakt match (0), næstmindst (-1), næststørst (1).

• search_mode : binær søgning (1) eller lineær søgning (2).

Antag, at du vil finde den første forekomst af et gult element i et område inden for kolonne B. Brug denne formel:

=XMATCH("Gul", B2:B6, 0)

Afsluttende tanker

Dette snydeark har dækket en bred vifte af funktioner, lige fra statistisk analyse, opslagsformler, datamanipulationsteknikker og fejlhåndteringsstrategier.

De medfølgende eksempler og forklaringer hjælper med at afmystificere disse avancerede formler, hvilket gør dem tilgængelige selv for dem med begrænset erfaring.

Når du begynder at inkorporere dem i dine Excel-opgaver, er du på vej til at øge dine Excel-færdigheder til et avanceret niveau.

Men husk, dette snydeark er kun toppen af ​​isbjerget. De virkelig fantastiske ting sker, når du bliver kreativ, blander og matcher disse formler og skræddersy dem til at løse dine unikke udfordringer. Excel er som et lærred, og disse formler er din palet - så gå i gang, mal dit mesterværk!