Excel Formulas Cheat Sheet: Avansert veiledning

Så du har cruiset gjennom det grunnleggende, taklet mellomtingene, og nå er du klar til å kjempe med de store kanonene - Excels avanserte formler! Excel har mange avanserte funksjoner og formler for sofistikerte beregninger, så det er nyttig å ha en guide som identifiserer de du trenger.

Dette juksearket for Excel-formler dekker avanserte prognoseformler, statistisk analyse, datamanipulasjonsfunksjoner, feilhåndtering og mer.

Denne referansen vil utstyre deg med kunnskap om hvordan du bruker disse avanserte funksjonene. Hver formel er ledsaget av klare forklaringer, syntaks og praktiske eksempler for å hjelpe middels Excel-brukere til å bli avanserte superbrukere.

Last ned og skriv ut juksearket og ha det lett tilgjengelig.

Ok, la oss komme i gang.

Først, la oss komme inn på Array-formler.

Innholdsfortegnelse

• Matriseformler
  • 1. UNIK funksjon
  • 2. SORTERING Funksjon
  • 3. FILTER Funksjon
• Randomisering av Excel-funksjoner
  • 1. RANDBETWEEN-funksjon
  • 2. RANDARRAY-funksjon
• Avanserte prognoseformler i Microsoft Excel
  • 1. FORECAST.ETS-funksjon
  • 2. TREND-funksjon
• Avanserte statistiske formler
  • 1. PERSENTIL Funksjon
  • 2. KVARTIL-funksjon
• Avanserte dataanalyse- og manipulasjonsformler
  • 1. TRANSPOSER-funksjon
  • 2. FREKVENS Funksjon
  • 3. GETPIVOTDATA-funksjon
• Avansert feilhåndtering
• Avanserte oppslagsformler
  • 1. XLOOKUP-funksjon
  • 2. XMATCH-funksjon
• Siste tanker

Matriseformler

Vår viser deg hvordan du sorterer og filtrerer dataene dine manuelt. Avanserte brukere gjør dette programmatisk med matriseformler.

Matriseformler lar deg utføre beregninger på flere celler samtidig. Dette er tre nøkkelfunksjoner:

1. UNIK

2. SORTERE

3. FILTER

Noen av disse funksjonene er bare tilgjengelige i de nyeste versjonene av Microsoft Excel.

1. UNIK funksjon

UNIK-funksjonen godtar et område og returnerer en liste med unike verdier.

Anta at du har salgsdata for klesplagg. For å finne de unike elementene i kolonne A, bruk denne formelen:

=UNIKK(A2:A6)

2. SORTERING Funksjon

SORTERING-funksjonen sorterer innholdet i et område. Syntaksen er:

SORT(matrise, [sorteringsindeks], [sorteringsrekkefølge], [etter_kolonne])

• array : området med verdier som skal sorteres.

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

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

• by_col : TRUE for å sortere etter kolonne (standard) eller FALSE for å sortere etter rad.

De tre siste argumentene er valgfrie, og standardinnstillingene er vanligvis det du ønsker.

For å sortere eksempeldataene etter den første kolonnen, bruk denne formelen:

=SORT(A2:C6)

3. FILTER Funksjon

FILTER-funksjonen lar deg filtrere et område på en bestemt tilstand. Dette er syntaksen:

=FILTER(matrise, inkludere, [hvis_tom])

• array : området som skal filtreres.

• inkluderer : betingelsen som bestemmer hvilke verdier som skal filtreres.

• if_empty : spesifiserer hva som skal returneres hvis ingen verdier oppfyller filtreringskriteriene (standard er "").

Anta at du vil filtrere radene i eksempeldataene for å bare vise hvor salgsverdien er større enn $15. Bruk denne formelen:

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

Randomisering av Excel-funksjoner

Vår mellomjukse viser hvordan du bruker RAND-funksjonen som produserer et tilfeldig tall mellom 0 og 1.

Avanserte Excel-brukere vet hvordan de bruker randomiseringsfunksjonene for raskt å generere eksempeldata.

1. RANDBETWEEN-funksjon

RANDBETWEEN-funksjonen er mer fleksibel enn RAND fordi du kan spesifisere bunn- og topptallene som noe annet enn 0 og 1.

For å generere data med tall mellom 1 og 100, skriv inn denne formelen i celle A1:

=RANDBETWEEN(1; 100)

Deretter kopierer du cellen til så mange rader og kolonner du vil. Det tar sekunder å produsere et rutenett med randomiserte tall:

2. RANDARRAY-funksjon

Du tenker kanskje at det ville være fint å unngå den manuelle kopien av RANDBETWEEN-funksjonen. For å bli superavansert kan du bruke den nye RANDARRAY-funksjonen i den nyeste versjonen av Microsoft Excel.

Syntaksen er:

RANDARRAY([rader], [kolonner], [min], [maks], [helt antall])

• rader : antall rader

• kolonner : antall kolonner

• min : laveste tall

• maks : høyeste antall

• heltall : standard til TRUE, ellers bruker desimaltall.

For å generere tilfeldige tall mellom 1 og 100 på tvers av seks rader og ni kolonner, bruk denne formelen:

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

Avanserte prognoseformler i Microsoft Excel

Excels prognosefunksjoner brukes til å forutsi fremtidige verdier basert på eksisterende datatrender. Disse funksjonene hjelper til med å identifisere mønstre og prosjekttrender basert på dataene dine.

1. FORECAST.ETS-funksjon

Den eldre FORECAST-funksjonen ble erstattet med et sett med nyere funksjoner i Excel 2016.

Du velger funksjonen basert på den spesifikke prognosemodellen du ønsker. For eksempel bruker FORECAST.ETS-funksjonen algoritmen for eksponentiell utjevning .

Syntaksen er:

FORECAST.ETS(måldato, verdier, tidslinje)

• target_date : datoen du vil ha en beregnet verdi for.

• verdier : de historiske dataene.

• tidslinje : en rekke datoer

Anta at du har datoer fra 1. til 5. januar i kolonne A og salgsbeløp i kolonne B. Denne formelen vil forutsi neste salgsbeløp:

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

2. TREND-funksjon

TREND-funksjonen projiserer et sett med verdier basert på minste kvadraters metode. Den returnerer en matrise. Syntaksen er:

TREND(kjent_y, [kjent_x], [ny_x], [konst])

• kjent_y : område av y-verdier

• kjent_x : område med x-verdier

• new_x : område med beregnede verdier

Ofte er den kjente_y datapunktene, mens de kjente_x er datoene.

Ved å bruke de samme dataene som i forrige eksempel kan du skrive inn formelen nedenfor i celle C2. Et sett med verdier vil bli generert.

=TREND(B2:B6; A2:A6)

Avanserte statistiske formler

De avanserte statistiske funksjonene inkluderer beregning av persentiler og kvartiler. Noen matematiske funksjoner er tilgjengelige for bakoverkompatibilitet, men det anbefales å bruke de mest oppdaterte versjonene.

1. PERSENTIL Funksjon

Denne funksjonen beregner prosentandelen av datapunkter som faller under en bestemt verdi. Syntaksen er:

PERCENTIL.INK(matrise, k)

• array : celleområdet

• k : persentilen fra 0 til 1

Anta at du vil beregne den 70. persentilen av data i kolonne B. Bruk denne formelen:

=PROSENTIL.INK(B2:B6; 0,7)

2. KVARTIL-funksjon

Denne funksjonen er en variant av persentilfunksjonen, men bruker kvartaler for å dele dataene. Dette er syntaksen:

QUARTILE.INC(matrise, quart)

• array : rekkevidden av data

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

Formelen nedenfor vil beregne den første kvartilen av data i kolonne A.

=QUARTILE.INC(A2:A5; 1)

Avanserte dataanalyse- og manipulasjonsformler

Flere avanserte funksjoner lar deg bytte format på data, analysere frekvensfordelinger og trekke ut data fra pivottabeller.

1. TRANSPOSERER

2. FREKVENS

3. GETPIVOTDATA

1. TRANSPOSER-funksjon

Noen ganger vil du flytte dataene i radene dine til kolonner og omvendt. Du kan gjøre dette manuelt eller bruke TRANSPOSER-funksjonen i stedet.

Anta at du har elementene «T-skjorte», «hettegenser» og «jeans» i cellene A2, A3 og A4. Du vil gjøre disse til kolonneoverskrifter. Denne funksjonen returnerer verdiene i en enkelt rad:

=TRANSPOSER(A2:A4)

2. FREKVENS Funksjon

Denne funksjonen beregner frekvensfordelingen av verdier i et datasett. Dette er syntaksen:

FREKVENS(datamatrise; bins_matrise)

• data_array : verdiområde.

• bins_array : intervallene som skal brukes.

Anta at du har salgsdata i kolonne B, og du vil analysere frekvensfordelingen av verdiene basert på hvor mange beløp som er:

• under 20.

• fra 20 til 80.

• over 80.

Det representerer tre binger og kan beregnes med denne formelen:

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

For mer om frekvensdistribusjoner i Excel, sjekk ut denne videoen:

3. GETPIVOTDATA-funksjon

Denne funksjonen lar deg trekke ut oppsummert informasjon fra pivottabeller. Dette er syntaksen:

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

• data_field : datafeltet eller verdien du vil hente fra pivottabellen.

• pivot_table : en referanse til pivottabellen.

• felt1, element1, osv. : felt/vare-parene som skal filtreres etter.

Anta at du har en pivottabell basert på fargen på varene som selges. For å trekke ut salget for røde varer, bruk denne formelen:

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

Avansert feilhåndtering

Selv de mest grunnleggende Excel-formlene kan gi feil. Middels brukere bør vite hvordan de bruker ISERROR for å håndtere feil. Avanserte brukere bør også være kjent med ERROR.TYPE-funksjonen for feilidentifikasjon.

ERROR.TYPE-funksjonen hjelper til med å identifisere den spesifikke typen feil i en celle eller formel.

Den returnerer en numerisk verdi som tilsvarer forskjellige feiltyper, for eksempel #N/A, #VERDI!, #REF! og mer.

Anta at du har en feil i celle A1 og du vil identifisere feiltypen. Følgende formel vil returnere tallet som tilsvarer den spesifikke feilen:

=FEIL.TYPE(A1)

Du kan kombinere dette med flere funksjoner for å reagere forskjellig avhengig av type feil. Dette er de vanligste feilene og deres verdier:

1. #NULL! (ingen vanlig celle funnet i et område)

2. #DIV/0! ( divisjon med null eller en tom celle)

3. #VERDI! (upassende datatype eller argument i en formel)

4. #REF! (en referert celle er slettet eller det er en )

5. #NAVN? (Excel gjenkjenner ikke funksjonen eller området)

6. #NUM! (ugyldig numerisk verdi)

7. #N/A (verdi kan ikke finnes)

Anta at du vil håndtere tre spesifikke feiltyper. Bruk denne formelen til å vise en spesifikk feilmelding basert på typen:

=HVIS(FEIL(C2), HVIS(FEIL.TYPE(C2)=2, “Del med null feil”, HVIS(FEIL.TYPE(C2)=3, “Ugyldig verdifeil”, HVIS(FEIL.TYPE(C2) )=7, «Verdi ikke funnet feil», «Annen feil»))), C2)

Avanserte oppslagsformler

Juksearkene våre for nybegynnere og middels dekket en . Her er noen avanserte alternativer:

1. XLOOKUP

2. XMATCH

1. XLOOKUP-funksjon

Denne oppslagsfunksjonen lar deg søke etter en verdi i et område og returnere en tilsvarende verdi fra en annen kolonne eller område.

Den tilbyr mer allsidighet enn enklere oppslagsfunksjoner som . Dette er syntaksen:

XLOOKUP(oppslagsverdi; oppslagsmatrise; returmatrise; [samsvarsmodus], [søkemodus], [hvis_ikke_funnet])

• lookup_value : verdien du vil søke etter.

• lookup_array : området for oppslaget.

• return_array : området som vil vise den tilsvarende verdien.

• match_mode : eksakt samsvar (0), neste mindre (1), neste større (-1), eller jokertegn (2).

• search_mode : -1 for topp til bunn, 1 for bunn til topp, eller 2 for binært søk.

• if_not_found : setter verdien som skal returneres hvis ingen samsvar blir funnet.

Anta at du vil søke i en rekke data for den første forekomsten av en klesplagg og returnere salgsbeløpene. Denne formelen vil se etter teksten "Hoodie" og returnere verdien i den tilstøtende cellen hvis den blir funnet:

=XLOOKUP(“Htegenser”, A2:A6, B2:B6)

2. XMATCH-funksjon

Denne funksjonen lar deg finne posisjonen til en spesifisert verdi innenfor et område eller en matrise. Dette er syntaksen:

XMATCH(oppslagsverdi, oppslagsmatrise, [samsvarstype], [søkemodus])

• lookup_value : verdien du vil finne.

• lookup_array : Området du vil søke i.

• match_type : eksakt samsvar (0), nest minste (-1), nest størst (1).

• search_mode : binært søk (1) eller lineært søk (2).

Anta at du vil finne den første forekomsten av et gult element i et område innenfor kolonne B. Bruk denne formelen:

=XMATCH(“Gul”, B2:B6, 0)

Siste tanker

Dette juksearket har dekket et bredt spekter av funksjoner, fra statistisk analyse, oppslagsformler, datamanipulasjonsteknikker og feilhåndteringsstrategier.

De medfølgende eksemplene og forklaringene hjelper til med å avmystifisere disse avanserte formlene, og gjør dem tilgjengelige selv for de med begrenset erfaring.

Når du begynner å inkorporere dem i Excel-oppgavene dine, er du på vei til å øke Excel-ferdighetene dine til et avansert nivå.

Men husk, dette juksearket er bare toppen av isfjellet. De virkelig fantastiske tingene skjer når du blir kreativ, blander og matcher disse formlene, og skreddersyr dem for å løse dine unike utfordringer. Excel er som et lerret, og disse formlene er paletten din - så fortsett, mal mesterverket ditt!