Excel Formulas Cheat Sheet: Mellomveiledning

Så du har klart å mestre – kudos til deg! Nå har du bestemt deg for å sparke ting opp et hakk og dykke ned i de mer avanserte tingene, ikke sant? Vel, du er heldig fordi vi har satt sammen et hendig mellomliggende Excel-jukseark for deg!

Dette mellomliggende juksearket for Microsoft Excel dekker et bredt spekter av emner som vil øke ferdighetene dine i numerisk analyse, tekstbehandling og kompleks dato- og tidslogikk.

Ved å ha Excel Intermediate Cheat Sheet som skrivebordsreferanse, kan du dykke dypt inn i de kraftigere formlene og funksjonene til Microsoft Excel.

Nå, sørg for å lese videre, men også skriv ut og lagre juksearket nedenfor!

La oss komme i gang!

Innholdsfortegnelse

• Mellomliggende matematiske formler
  • Betinget logikk
• Mellomliggende statistiske formler
  • 1. MINA og MAXA
  • 2. COUNTA og COUNTIF
• Excel-formler for finansiell analyse
  • 1. PRODUKTfunksjon
  • 2. KVOTIENT- og LOGG-funksjoner
  • Funksjoner for investeringsscenarier
• Middels Excel dato og klokkeslett formler
  • 1. Mellommånedsfunksjoner
  • 2. Mellom ukesfunksjoner
  • 3. Mellomtidsfunksjoner
• Excel-funksjoner for betinget og logisk algebra
  • 1. HVIS-funksjon
  • 2. OG-funksjon
  • 3. ELLER-funksjon
  • 4. IKKE funksjon
• Refererer til celler
  • 1. INDIREKTE funksjon
  • 2. INDEKS Funksjon
  • 3. OFFSET-funksjon
• Mellomliggende tekstformler
  • 1. TEXTJOIN-funksjon
  • 2. ERSTATT-funksjon
  • 3. ERSTATT-funksjon
• Formler for mellomliggende oppslag
• Formler for håndtering av Microsoft Excel-feil
  • 1. IFERROR-funksjon
  • 2. IFNA-funksjon
• Snarveier for formel og funksjon
• Mellomliggende celleformatering
• Siste tanker

Mellomliggende matematiske formler

Vårt jukseark for nybegynnere dekket de vanligste matematiske funksjonene for nybegynnere som SUM-funksjonen. Middels brukere bør bli kjent med disse mer komplekse funksjonene:

• ABS() returnerer den absolutte verdien av et tall.

• SQRT() returnerer kvadratroten av et tall

• RAND() gir et tilfeldig tall mellom 0 og 1

Betinget logikk

Middels brukere bør også vite hvordan de utfører matematiske beregninger basert på flere forhold ved å bruke disse funksjonene:

• SUMMER(sum_område; kriterieområde1; kriterie1; [….])

• ANTALLHVIS(kriterieområde1; kriterium1; [kriterieområde2; kriterie2], …)

• AVERAGEIFS(kriterieområde1; kriterie1; [kriterieområde2; kriterie2], …)

Funksjonene evaluerer kriteriene som er oppgitt og produserer en logisk verdi på SANN eller USANN.

La oss si at du har følgende salgsdata i kolonnene A, B og C:

For å beregne summen av salg for røde t-skjorter, bruk SUMIFS-formelen:

=SUMIFS(C2:C6; A2:A6; "T-skjorte", B2:B6; "Rød")

For å telle antall rader med røde t-skjorter, bruk COUNTIFS-formelen:

=ANTALL(C2:C6; A2:A6; "T-skjorte", B2:B6; "Rød")

For å beregne gjennomsnittlig salg for røde t-skjorter, bruk AVERAGEIFS-formelen:

=AVERAGEIFS(C2:C6, A2:A6, "T-skjorte", B2:B6, "Rød")

Mellomliggende statistiske formler

Bortsett fra grunnleggende funksjoner som MIN og MAX, har Excel et bredere spekter av statistiske formler for middels brukere. Her er noen av de mest nyttige:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA og MAXA

De mest brukte MIN- og MAX-funksjonene ignorerer tekstverdier.

De alternative MINA- og MAXA-funksjonene vurderer både tekst og tall når de leter etter de høyeste eller laveste verdiene. Tekstverdier vurderes som om de var null.

Forskjellene er vist på bildet nedenfor. Den første raden evaluerer en minimumsverdi på 10, mens den andre raden evalueres som 0 på grunn av tilstedeværelsen av en tekstverdi.

2. COUNTA og COUNTIF

COUNTA-funksjonen brukes til å i et område.

COUNTIF-funksjonen er mindre spesifikk ved at den brukes til å telle antall celler innenfor et område som oppfyller en bestemt betingelse eller kriterier.

Du kan også bruke disse funksjonene til å telle antall distinkte verdier i en kolonne. Denne videoen viser hvordan du gjør det.

Excel-formler for finansiell analyse

Det er flere formler du vil bruke når du utfører finansiell analyse, for eksempel prognoser for investeringer:

• PRODUKT(nummer1, [nummer2…])

• KVOTIENT(teller; nevner)

• LOGG(tall; [grunntall])

1. PRODUKTfunksjon

Anta at du vil beregne den fremtidige verdien av en initial investering på $1000 (celle B1) med en årlig rente på 4% (celle B2) etter fem år (celle B3).

Bruk PRODUKT-funksjonen til å beregne den fremtidige verdien med denne formelen:

=B1 * PRODUKT(1 + B2)^B3

Denne formelen vil returnere den fremtidige verdien av investeringen din.

2. KVOTIENT- og LOGG-funksjoner

Neste steg er å beregne hvor mange år det vil ta før en investering dobles i verdi med den gitte renten.

Bruk QUOTIENT-funksjonen i kombinasjon med LOG-funksjonen for å beregne dette på følgende måte:

=KVOTIENT(LOG(2) / LOG(1 + B2), 1)

Denne formelen vil returnere 17 med prøvedataene, noe som indikerer at det vil ta 17 år før investeringen dobles i verdi med en årlig rente på 4 %.

Dette bildet :

Funksjoner for investeringsscenarier

Dette er noen funksjoner som passer for spesifikke økonomiske scenarier. En av dem kan være akkurat det du trenger:

• NPV – Funksjonen NPV (Net Present Value) vil beregne netto nåverdi av en investering basert på en serie fremtidige kontantstrømmer basert på en diskonteringsrente.

• ACCRINT – ACCRINT -funksjonen beregner påløpte renter for et verdipapir som betaler periodiske renter. Dette er nyttig for å bestemme renten opptjent på et verdipapir siden siste betalingsdato frem til en gitt oppgjørsdato.

• INTRATE – INTRATE -funksjonen beregner renten for et fullt investert verdipapir.

• PMT – PMT -funksjonen beregner den totale betalingen på et gjeldspapir.

• IRR – IRR -funksjonen gir internrenten for en avkastning.

• YIELD – YIELD -funksjonen gir avkastningen til et verdipapir basert på rente, pålydende og løpetid.

Middels Excel dato og klokkeslett formler

De grunnleggende dato- og klokkeslettfunksjonene i Excel inkluderer funksjonene NÅ og I DAG for gjeldende dato. Middels brukere bør også vite hvordan de trekker ut komponenter fra en gitt dato ved å bruke:

• DAG(dato)

• MONTH(dato)

• ÅR(dato)

Formelen =MONTH(“23. april 2023″) vil returnere et resultat på 4 for den 4. måneden. Tilsvarende vil funksjonene DAG og ÅR returnere henholdsvis 23 og 2023.

1. Mellommånedsfunksjoner

Middels brukere vil noen ganger håndtere å legge til eller finne slutten av en måned.

• EDATE(startdato, antall_måneder)

• EOMONTH(startdato, antall_måneder)

For eksempel vil formelen =EDATE(“23. april 2023”, 2) beregne datoen to måneder senere.

Formelen =EOMONTH(23. april 2023”, 2) beregner slutten av måneden to måneder senere.

Resultatet er «30. juni 2023», som tar hensyn til at det kun er tretti dager i juni. Hvis du angir 3 måneder, vil resultatet være "31. juli 2023".

2. Mellom ukesfunksjoner

Du kan bruke en kombinasjon av SUM- og WEEKDAY-funksjonene for å telle antall hele arbeidsdager innenfor en rekke datoer.

WEEKDAY-funksjonen returnerer ukedagen for en gitt dato. Den andre parameteren bestemmer nummereringssystemet med standard søndag til lørdag.

Hvis du har en serie datoer i et celleområde på B1:B40, bruker du denne formelen til å beregne arbeidsdagene:

=SUM(–(UKEDAG(B1:B40;2)>5))

3. Mellomtidsfunksjoner

YEARFRAC-funksjonen beregner brøkdelen av et år mellom to datoer.

TIDVERDI-funksjonen brukes til å konvertere en tid representert som tekst til et desimaltall som representerer andelen av en 24-timers dag.

Resultatet er et Excel-serienummer der 1 representerer en hel 24-timers dag, 0,5 representerer 12 timer, 0,25 representerer 6 timer, og så videre.

Anta at du har en tid i tekstformat "17:45" i celle A1 og du vil konvertere den til et desimaltall . Dette er formelen:

=TIDSVERDI(A1)

Verdien 0,74 representerer andelen av 24-timersdøgnet som har gått på tidspunktet femten minutter før klokken seks.

Ta til for å endre tidscellen fra et tidsformat til et tekstformat. Det kan være lurt å formatere dette som en prosentandel for å være tydeligere.

Excel-funksjoner for betinget og logisk algebra

Betingede og logiske funksjoner er avgjørende for beslutningstaking i Excel. Følgende funksjoner kan kombineres med de mer grunnleggende Excel-formlene for kraftig logikk:

1. HVIS(betingelse, verdi_hvis_sann, verdi_hvis_falsk):

2. OG(betingelse1, betingelse2, …)

3. ELLER(betingelse1, betingelse2, …)

4. IKKE (tilstand1, tilstand2, …)

HVIS-funksjonen evaluerer en betingelse og returnerer forskjellige verdier avhengig av om betingelsen er sann eller usann.

OG-, ELLER- og IKKE-funksjonene sjekker hvilke forhold som er sanne og lar deg ta avgjørelser deretter.

Her er noen eksempler basert på salgsdataene vi brukte tidligere.

1. HVIS-funksjon

Anta at du vil vise teksten "Lavt salg" hvis salget for varene er mindre enn 15. Hvis salget er høyere, vil du vise "Høyt salg" i en ny kolonne.

Bruk denne formelen og kopier den ned til de andre radene:

=HVIS(C2<15, "Lavt salg", "Høyt salg")

2. OG-funksjon

Anta at du vil vise TRUE eller FALSE for t-skjorter med minst 15 salg. Bruk denne formelen:

=AND(A3=”T-skjorte”, C3>=15)

3. ELLER-funksjon

Anta at du vil teste flere betingelser og returnere TRUE hvis noen av betingelsene er oppfylt og FALSE ellers, så vil du bruke ELLER-funksjonen.

For eksempel, hvis vi har score for en test i cellene A1 og B1, og vi vil vite om en av poengsummene er over 80, kan vi bruke: =ELLER (A1>80, B1>80) . Dette vil returnere TRUE hvis en av poengsummene (eller begge) er over 80, og FALSE hvis begge er 80 eller mindre.

4. IKKE funksjon

Anta at du vil sjekke om varen ikke er en hettegenser. Bruk denne formelen:

=IKKE(A2=”Htegenser”)

Refererer til celler

En nybegynner vil raskt bli kjent med å bruke absolutte og relative referanser som $A$1 eller B2. Middels brukere bør bli kjent med å bruke indirekte referanser, indekser og forskyvninger:

1. INDIREKTE(ref_tekst)

2. INDEX(område; radnummer; kolonnenummer)

3. OFFSET(referansecelle, rader, kolonner):

1. INDIREKTE funksjon

INDIREKTE-funksjonen returnerer verdien til den angitte cellereferansen angitt som tekst. For å referere til celle B3, bruk denne formelen:

=INDIREKTE(“B3”)

Fordelen fremfor å bruke enkle cellereferanser er at denne funksjonen gir deg en dynamisk referanse. Dette betyr at Excel automatisk oppdaterer referansen når strukturen i regnearket endres, f.eks. slettes en rad.

2. INDEKS Funksjon

INDEKS-funksjonen brukes til å referere til celler innenfor et spesifisert område basert på rad- og kolonnenummer.

For å referere til celle B3 i en tabell med seks rader, bruk denne formelen:

=INDEKS(B1:B6; 3)

3. OFFSET-funksjon

OFFSET-funksjonen returnerer en celle eller et område som er et spesifisert antall rader og kolonner unna en referansecelle.

Hvis du vil vise celleverdien som er to celler på tvers og en celle ned fra A1, bruker du denne formelen:

=OFFSET(A1; 2; 1)

Her er formlene i aksjon:

Mellomliggende tekstformler

Nybegynnere bør kjenne til tekstfunksjoner som VENSTRE, som trekker ut ett eller flere tegn fra venstre side av en streng. En superbruker bør være kjent med funksjoner som disse:

1. TEXTJOIN(skilletegn; ignorer_tom; tekst1; [tekst2, …])

2. REPLACE(gammel_tekst; startnummer; antall_tegn; ny_tekst)

3. ERSTATTER(tekst; gammel_tekst; ny_tekst; [forekomstnummer])

1. TEXTJOIN-funksjon

TEXTJOIN-funksjonen setter sammen celler med skilletegnet du oppgir. Du kan også angi hvor tomme celler skal ignoreres.

For å lage en kommadelt liste over noen elementer i kolonne A, bruk denne formelen:

=TEXTJOIN(“, “, TRUE, A2:A4)

2. ERSTATT-funksjon

Erstatt-funksjonen lar deg spesifisere startposisjonen og lengden på strengen du vil bytte inn i målet.

For å erstatte de to første tegnene i en streng med "XX", bruk denne formelen:

=TEKST BLI MED(A2; 1; 2; "XX")

3. ERSTATT-funksjon

Erstatningsfunksjonen erstatter spesifiserte forekomster av en tekststreng i en annen tekststreng med en ny tekststreng.

For å erstatte forekomster av ordet "Jeans" med "Leggings", bruk denne formelen:

=ERSTATT(A3, "Jeans", "Belegg")

Her er formlene i aksjon:

Formler for mellomliggende oppslag

Vanlige oppslagsfunksjoner som VLOOKUP og MATCH er dekket i nybegynnerens jukseark.

Middels brukere bør bli kjent med VELG oppslagsfunksjonen:

• VELG(indeksnummer; verdi1; [verdi2; …])

VELG-funksjonen returnerer en verdi fra en liste med verdier basert på et spesifisert indeksnummer.

Anta at du har en kolonne med t-skjortestørrelser merket som 1, 2 eller 3. Du vil vise en kategori med henholdsvis liten, medium og stor i samme rad. Bruk denne formelen:

=VELG(A1, "Liten", "Middels", "Stor")

Formler for håndtering av Microsoft Excel-feil

Juksearket for nybegynnere listet opp de typiske feilmeldingene du vil se når du arbeider med Excel. Middels brukere bør kunne bruke formler for å håndtere feilene på en elegant måte.

1. IFERROR(verdi; hvis_feil)

2. IFNA(verdi; verdi_hvis_na)

1. IFERROR-funksjon

Divisjon med null feil er vanlige, men Excels standardvisning på #DIV/0! vil ikke være tydelig for alle brukere.

Bruk IFERROR-funksjonen til å erstatte standardfeilen med "Kan ikke dele på null" med denne formelen:

=FEIL(A1/B1; "Kan ikke dividere med null")

Dette bildet viser råfeilen i den tredje raden og IFERROR-funksjonen som håndterer den fjerde raden.

2. IFNA-funksjon

IFNA-funksjonen i Excel brukes til å fange opp og håndtere #N/A-feil.

En annen del av dette juksearket viste VELG-funksjonen. Denne funksjonen vil returnere en N/A feil hvis indeksnummeret ikke er et heltall.

Anta at en t-skjortestørrelse har blitt angitt som 1,5, der brukeren feilaktig tror at denne vil vise en kategori et sted mellom liten og medium. I stedet vil den vise en #N/A- feil.

For å gi en mer nyttig feil, bruk denne formelen:

=IFNA(VELG(A2, "Lav", "Middels", "Høy"), "Ugyldig indeks")

Snarveier for formel og funksjon

Det er flere hurtigtaster som kan få deg til å jobbe mer effektivt med regnearket.

• F2 : Rediger den aktive cellen og plasser innsettingspunktet på slutten av celleinnholdet.

• F9 : Beregn og vis resultatet av den valgte delen av en formel.

• Ctrl + Shift + Enter : Skriv inn en matriseformel.

• Shift + F3 : Åpne dialogboksen Sett inn funksjon.

• Esc : Avbryt inntastingen av en formel og gå tilbake til det opprinnelige celleinnholdet.

Noen av disse Excel-snarveiene er kanskje ikke tilgjengelige på alle språk eller tastaturoppsett.

Mellomliggende celleformatering

I finansiell analyse er riktig formatering av celler som inneholder tall, datoer og valutaer avgjørende. Følg disse trinnene for å formatere celler:

1. Velg cellene du vil formatere.

2. Høyreklikk på de valgte cellene og velg "Formater celler."

3. Velg riktig kategori (f.eks. Antall, Valuta, Dato) og bruk ønsket format.

Noen vanlige formateringstyper å vurdere i finans inkluderer:

• Valuta : $1 234,56

• Prosent : 12,34 %

• Regnskap : ($1 234,00)

• Dato : 10. mai 2023

Siste tanker

Etter hvert som du blir mer komfortabel med de ulike formlene og funksjonene som er fremhevet i denne, vil du finne deg selv bedre rustet til å håndtere stadig mer komplekse oppgaver og prosjekter.

Øvelse er nøkkelen til å mestre alle ferdigheter, så ikke vær redd for å eksperimentere med disse formlene i ditt daglige arbeid. De vil bringe ekspertisen din fra nybegynnernivå til middels nivå.

Dette er bare en smakebit på hva Excels mellomfunksjoner kan gjøre for å forenkle arbeidet ditt, strømlinjeforme analysene dine og hjelpe deg med å avdekke innsikt fra dataene dine.

Den virkelige magien skjer når du begynner å kombinere funksjonene i juksearket vårt og skreddersyr dem for å passe dine unike behov. Så, ikke stopp her. Fortsett å utforske, fortsett å eksperimentere, og du vil oppdage at Excel ikke bare er et verktøy, det er en spillskifter!

Føler du at du vil ta ting til neste nivå? Sjekk vår