Excel Formler Snydeark: Mellemvejledning

Så du har formået at mestre – kudos til dig! Nu har du besluttet dig for at sætte gang i tingene og dykke ned i de mere avancerede ting, ikke? Nå, du er heldig, fordi vi har sammensat et praktisk mellemliggende Excel-snydeark til dig!

Dette mellemliggende snydeark i Microsoft Excel dækker en bred vifte af emner, der vil øge dine færdigheder inden for numerisk analyse, tekstbehandling og kompleks dato- og tidslogik.

Ved at have Excel Intermediate Cheat Sheet som skrivebordsreference kan du dykke dybt ned i de mere kraftfulde formler og funktioner i Microsoft Excel.

Sørg nu for at læse videre, men print og gem også snydearket nedenfor!

Lad os komme igang!

Indholdsfortegnelse

• Mellemliggende matematiske formler
  • Betinget logik
• Mellemliggende statistiske formler
  • 1. MINA og MAXA
  • 2. COUNTA og COUNTIF
• Excel-formler til finansiel analyse
  • 1. PRODUKT Funktion
  • 2. KVOTIENT- og LOG-funktioner
  • Funktioner til investeringsscenarier
• Mellem Excel dato- og tidsformler
  • 1. Mellemmånedsfunktioner
  • 2. Mellemugefunktioner
  • 3. Mellemtidsfunktioner
• Excel-funktioner til betinget og logisk algebra
  • 1. HVIS funktion
  • 2. OG Funktion
  • 3. ELLER Funktion
  • 4. IKKE funktion
• Referenceceller
  • 1. INDIREKTE Funktion
  • 2. INDEX Funktion
  • 3. OFFSET-funktion
• Mellemtekstformler
  • 1. TEXTJOIN-funktion
  • 2. UDSKIFT Funktion
  • 3. ERSTAT Funktion
• Formler for mellemliggende opslag
• Formler til håndtering af Microsoft Excel-fejl
  • 1. IFERROR-funktion
  • 2. IFNA funktion
• Formel- og funktionsgenveje
• Mellemcelleformatering
• Afsluttende tanker

Mellemliggende matematiske formler

Vores snydeark for begyndere dækkede de mest almindelige matematikfunktioner for begyndere, såsom SUM-funktionen. Mellembrugere bør blive fortrolige med disse mere komplekse funktioner:

• ABS() returnerer den absolutte værdi af et tal.

• SQRT() returnerer kvadratroden af ​​et tal

• RAND() giver et tilfældigt tal mellem 0 og 1

Betinget logik

Mellemliggende brugere bør også vide, hvordan man udfører matematiske beregninger baseret på flere forhold ved hjælp af disse funktioner:

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

• ANTALHVIS(kriterieområde1; kriterium1; [kriterieområde2; kriterium2], …)

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

Funktionerne evaluerer de angivne kriterier og producerer en logisk værdi af TRUE eller FALSE.

Lad os sige, at du har følgende salgsdata i kolonne A, B og C:

For at beregne summen af ​​salg for røde t-shirts skal du bruge SUMIFS-formlen:

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

For at tælle antallet af rækker med røde t-shirts, brug COUNTIFS-formlen:

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

For at beregne det gennemsnitlige salg for røde t-shirts skal du bruge AVERAGEIFS-formlen:

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

Mellemliggende statistiske formler

Bortset fra grundlæggende funktioner som MIN og MAX, har Excel et bredere udvalg af statistiske formler til mellembrugere. Her er nogle af de mest nyttige:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA og MAXA

De mere almindeligt anvendte MIN- og MAX-funktioner ignorerer tekstværdier.

De alternative MINA- og MAXA-funktioner tager både tekst og tal i betragtning, når de leder efter de højeste eller laveste værdier. Tekstværdier evalueres, som om de var nul.

Forskellene er vist på billedet nedenfor. Den første række evaluerer en minimumsværdi på 10, mens den anden række evalueres som 0 på grund af tilstedeværelsen af ​​en tekstværdi.

2. COUNTA og COUNTIF

Funktionen COUNTA bruges til i et område.

Funktionen COUNTIF er mindre specifik, idet den bruges til at tælle antallet af celler inden for et område, der opfylder en specifik betingelse eller kriterier.

Du kan også bruge disse funktioner til at tælle antallet af forskellige værdier i en kolonne. Denne video viser, hvordan du gør det.

Excel-formler til finansiel analyse

Der er flere formler, du vil bruge, når du udfører finansielle analyser, såsom prognoser for investeringer:

• PRODUKT(nummer1, [nummer2…])

• KVOTIENT(tæller; nævner)

• LOG(tal; [grundlag])

1. PRODUKT Funktion

Antag, at du vil beregne den fremtidige værdi af en initial investering på $1.000 (celle B1) til en årlig rente på 4% (celle B2) efter fem år (celle B3).

Brug PRODUKT-funktionen til at beregne den fremtidige værdi med denne formel:

=B1 * PRODUKT(1 + B2)^B3

Denne formel vil returnere den fremtidige værdi af din investering.

2. KVOTIENT- og LOG-funktioner

Næste skridt er at beregne, hvor mange år det vil tage for en investering at fordoble i værdi til den givne rente.

Brug QUOTIENT-funktionen i kombination med LOG-funktionen til at beregne dette som følger:

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

Denne formel returnerer 17 med prøvedataene, hvilket indikerer, at det vil tage 17 år for investeringen at fordoble i værdi til en årlig rente på 4 %.

Det her billede :

Funktioner til investeringsscenarier

Dette er nogle funktioner, der er velegnede til specifikke økonomiske scenarier. En af dem kan være præcis, hvad du har brug for:

• NPV – Funktionen NPV (Net Present Value) vil beregne nettonutidsværdien af ​​en investering baseret på en række fremtidige pengestrømme baseret på en diskonteringssats.

• ACCRINT – Funktionen ACCRINT beregner de påløbne renter af et værdipapir, der betaler periodiske renter. Dette er nyttigt til at bestemme renten optjent på et værdipapir siden sidste betalingsdato og frem til en given afregningsdato.

• INTRAT – Funktionen INTRAT beregner renten for et fuldt investeret værdipapir.

• PMT – PMT -funktionen beregner den samlede betaling på et gældspapir.

• IRR – IRR -funktionen giver den interne rente for et afkast.

• YIELD – Funktionen YIELD giver udbyttet af et værdipapir baseret på rente, pålydende værdi og løbetid.

Mellem Excel dato- og tidsformler

De grundlæggende dato- og tidsfunktioner i Excel inkluderer funktionerne NU og I DAG for den aktuelle dato. Mellembrugere bør også vide, hvordan man udtrækker komponenter fra en given dato ved hjælp af:

• DAG(dato)

• MÅNED(dato)

• ÅR (dato)

Formlen =MÅNED(“23. april 2023″) vil returnere et resultat på 4 for den 4. måned. Tilsvarende vil funktionerne DAG og ÅR returnere henholdsvis 23 og 2023.

1. Mellemmånedsfunktioner

Mellemliggende brugere vil nogle gange beskæftige sig med at tilføje eller og finde slutningen af ​​en måned.

• EDATE(startdato, antal_måneder)

• EOMONTH(startdato, antal_måneder)

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

Formlen =EOMONTH(23. april 2023”, 2) beregner slutningen af ​​måneden to måneder senere.

Resultatet er "30. juni 2023", som tager højde for, at der kun er tredive dage i juni. Hvis du angiver 3 måneder, vil resultatet være "31. juli 2023".

2. Mellemugefunktioner

Du kan bruge en kombination af SUM- og WEEKDAY-funktionerne til at tælle antallet af hele arbejdsdage inden for en række datoer.

WEEKDAY-funktionen returnerer ugedagen for en given dato. Den anden parameter bestemmer nummereringssystemet med standarden søndag til lørdag.

Hvis du har en række datoer i et celleområde på B1:B40, skal du bruge denne formel til at beregne arbejdsdagene:

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

3. Mellemtidsfunktioner

Funktionen ÅRFRAK beregner brøkdelen af ​​et år mellem to datoer.

Funktionen TIDSVÆRDI bruges til at konvertere en tid repræsenteret som tekst til et decimaltal, der repræsenterer andelen af ​​en 24-timers dag.

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

Antag, at du har en tid i tekstformat "17:45" i celle A1, og du vil konvertere den til et decimaltal . Dette er formlen:

=TIDSVÆRDI(A1)

Værdien 0,74 repræsenterer andelen af ​​24-timers døgnet, der er gået på tidspunktet femten minutter før klokken seks.

Tag til for at ændre tidscellen fra et tidsformat til et tekstformat. Du vil måske formatere dette som en procentdel for at være tydeligere.

Excel-funktioner til betinget og logisk algebra

Betingede og logiske funktioner er afgørende for beslutningstagning i Excel. Følgende funktioner kan kombineres med de mere grundlæggende Excel-formler til kraftfuld logik:

1. HVIS(betingelse, værdi_hvis_sand, værdi_hvis_falsk):

2. OG(betingelse1, betingelse2, …)

3. ELLER(betingelse1, betingelse2, …)

4. IKKE (betingelse1, betingelse2, …)

HVIS-funktionen evaluerer en betingelse og returnerer forskellige værdier afhængigt af, om betingelsen er sand eller falsk.

OG-, ELLER- og IKKE-funktionerne kontrollerer, hvilke betingelser der er sande, og lader dig træffe beslutninger i overensstemmelse hermed.

Her er nogle eksempler baseret på de salgsdata, vi brugte før.

1. HVIS funktion

Antag, at du vil vise teksten "Lavt salg", hvis salget for varerne er mindre end 15. Hvis salget er højere, vil du vise "Højt salg" i en ny kolonne.

Brug denne formel og kopier den ned til de andre rækker:

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

2. OG Funktion

Antag, at du vil vise TRUE eller FALSE for t-shirts med mindst 15 salg. Brug denne formel:

=OG(A3=”T-shirt”, C3>=15)

3. ELLER Funktion

Antag, at du vil teste flere betingelser og returnere TRUE, hvis nogen af ​​betingelserne er opfyldt og FALSK ellers, så ville du bruge OR-funktionen.

For eksempel, hvis vi har score for en test i cellerne A1 og B1, og vi vil vide, om en af ​​pointene er over 80, kunne vi bruge: =ELLER (A1>80, B1>80) . Dette vil returnere SAND, hvis en af ​​pointene (eller begge) er over 80, og FALSK, hvis begge er 80 eller mindre.

4. IKKE funktion

Antag, at du vil tjekke, om varen ikke er en hættetrøje. Brug denne formel:

=IKKE(A2=”Hættetrøje”)

Referenceceller

En nybegynder vil hurtigt blive fortrolig med at bruge absolutte og relative referencer såsom $A$1 eller B2. Mellemliggende brugere bør blive fortrolige med at bruge indirekte referencer, indekser og offsets:

1. INDIREKTE(ref_tekst)

2. INDEX(område; række_nummer; kolonnenummer)

3. OFFSET(referencecelle, rækker, kolonner):

1. INDIREKTE Funktion

Funktionen INDIREKTE returnerer værdien af ​​den angivne cellereference indtastet som tekst. Brug denne formel for at referere til celle B3:

=INDIREKTE(“B3”)

Fordelen i forhold til at bruge simple cellereferencer er, at denne funktion giver dig en dynamisk reference. Det betyder, at Excel automatisk opdaterer referencen, når strukturen i regnearket ændres, f.eks. slettes en række.

2. INDEX Funktion

INDEX-funktionen bruges til at referere til celler inden for et specificeret interval baseret på række- og kolonnenumre.

Brug denne formel for at referere til celle B3 i en tabel med seks rækker:

=INDEKS(B1:B6; 3)

3. OFFSET-funktion

Funktionen FORSKYDNING returnerer en celle eller et område, der er et angivet antal rækker og kolonner væk fra en referencecelle.

Hvis du vil vise celleværdien, der er to celler på tværs og en celle ned fra A1, skal du bruge denne formel:

=OFFSET(A1; 2; 1)

Her er formlerne i aktion:

Mellemtekstformler

Begyndere bør kende tekstfunktioner som VENSTRE, som udtrækker et eller flere tegn fra venstre side af en streng. En superbruger bør være bekendt med funktioner som disse:

1. TEXTJOIN(afgrænser, ignorer_tom, tekst1, [tekst2, …])

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

3. SUBSTITUTE(tekst; gammel_tekst; ny_tekst; [forekomst_nummer])

1. TEXTJOIN-funktion

TEXTJOIN-funktionen sammenkæder celler med den afgrænsning, du angiver. Du kan også angive, hvor tomme celler skal ignoreres.

For at lave en kommasepareret liste over nogle elementer i kolonne A skal du bruge denne formel:

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

2. UDSKIFT Funktion

Udskiftningsfunktionen lader dig angive startpositionen og længden af ​​den streng, du vil bytte til målet.

For at erstatte de to første tegn i en streng med "XX" skal du bruge denne formel:

=TEKSTJOIN(A2; 1; 2; "XX")

3. ERSTAT Funktion

Erstatningsfunktionen erstatter specificerede forekomster af en tekststreng i en anden tekststreng med en ny tekststreng.

For at erstatte forekomster af ordet "Jeans" med "Leggings", skal du bruge denne formel:

=SUBSTITUTER(A3, "Jeans", "Belægninger")

Her er formlerne i aktion:

Formler for mellemliggende opslag

Almindelige opslagsfunktioner som VLOOKUP og MATCH er dækket af begynderens snydeark.

Mellemliggende brugere bør blive fortrolige med VÆLG opslagsfunktionen:

• VÆLG(indeks_tal; værdi1; [værdi2; …])

Funktionen VÆLG returnerer en værdi fra en liste over værdier baseret på et angivet indeksnummer.

Antag, at du har en kolonne med t-shirtstørrelser mærket som 1, 2 eller 3. Du vil vise en kategori med henholdsvis lille, medium og stor i samme række. Brug denne formel:

=VÆLG(A1, "Small", "Medium", "Large")

Formler til håndtering af Microsoft Excel-fejl

Snydearket for begyndere listede de typiske fejlmeddelelser, du vil se, når du arbejder med Excel. Mellemliggende brugere bør være i stand til at bruge formler til at håndtere fejlene elegant.

1. IFERROR(værdi; hvis_fejl)

2. IFNA(værdi; værdi_hvis_na)

1. IFERROR-funktion

Division med nul fejl er almindelige, men Excels standardvisning på #DIV/0! vil ikke være tydelig for alle brugere.

Brug IFERROR-funktionen til at erstatte standardfejlen med "Kan ikke dividere med nul" med denne formel:

=IFERROR(A1/B1, "Kan ikke dividere med nul")

Dette billede viser den rå fejl i tredje række og IFERROR-funktionen, der håndterer den fjerde række.

2. IFNA funktion

IFNA-funktionen i Excel bruges til at fange og håndtere #N/A fejl.

Et andet afsnit af dette snydeark viste funktionen VÆLG. Denne funktion vil returnere en N/A fejl, hvis indeksnummeret ikke er et heltal.

Antag, at en t-shirtstørrelse er blevet indtastet som 1,5, hvor brugeren fejlagtigt tror, ​​at dette vil vise en kategori et sted mellem small og medium. I stedet vil den vise en #N/A fejl.

Brug denne formel for at give en mere nyttig fejl:

=IFNA(VÆLG(A2, "Lav", "Medium", "Høj"), "Ugyldigt indeks")

Formel- og funktionsgenveje

Der er flere tastaturgenveje, der kan få dig til at arbejde mere effektivt med dit regneark.

• F2 : Rediger den aktive celle og placer indsættelsespunktet i slutningen af ​​celleindholdet.

• F9 : Beregn og vis resultatet af den valgte del af en formel.

• Ctrl + Shift + Enter : Indtast en matrixformel.

• Shift + F3 : Åbn dialogboksen Indsæt funktion.

• Esc : Annuller indtastningen af ​​en formel og vend tilbage til det oprindelige celleindhold.

Nogle af disse Excel-genveje er muligvis ikke tilgængelige på alle sprog eller tastaturlayout.

Mellemcelleformatering

I finansiel analyse er korrekt formatering af celler, der indeholder tal, datoer og valutaer, afgørende. Følg disse trin for at formatere celler:

1. Vælg de celler, du vil formatere.

2. Højreklik på de valgte celler og vælg "Formater celler".

3. Vælg den relevante kategori (f.eks. Antal, Valuta, Dato) og anvend det ønskede format.

Nogle almindelige formateringstyper at overveje i finans inkluderer:

• Valuta : $1.234,56

• Procent : 12,34 %

• Regnskab : ($1.234,00)

• Dato : 10-maj-2023

Afsluttende tanker

Efterhånden som du bliver mere komfortabel med de forskellige formler og funktioner, der er fremhævet i denne , vil du finde dig selv bedre rustet til at håndtere stadigt mere komplekse opgaver og projekter.

Practice is key to mastering any skill, so don’t be afraid to experiment with these formulas in your day-to-day work. They will bring your expertise from beginner level to intermediate level.

This is just a taste of what Excel’s intermediate functions can do to simplify your work, streamline your analyses, and help you uncover insights from your data.

The real magic happens when you start combining the functions in our cheat sheet and tailor them to suit your unique needs. So, don’t stop here. Keep exploring, keep experimenting, and you’ll find that Excel is not just a tool, it’s a game-changer!

Feel like you want to take things to the next level? Check our