Excel Formler Fuskblad: Mellanvägledning

Så du har lyckats bemästra – kudos till dig! Nu har du bestämt dig för att skjuta upp saker och ting och dyka in i de mer avancerade sakerna, eller hur? Tja, du har tur eftersom vi har satt ihop ett praktiskt mellanliggande Excel-fuskblad bara för dig!

Detta mellanliggande fuskblad i Microsoft Excel täcker ett brett utbud av ämnen som kommer att höja dina färdigheter i numerisk analys, textbearbetning och komplex datum- och tidslogik.

Genom att ha Excel Intermediate Cheat Sheet som skrivbordsreferens kan du dyka djupt in i de mer kraftfulla formlerna och funktionerna i Microsoft Excel.

Se nu till att läsa vidare, men även skriv ut och spara fuskbladet nedan!

Låt oss börja!

Innehållsförteckning

• Mellanliggande matematiska formler
  • Villkorlig logik
• Mellanliggande statistiska formler
  • 1. MINA och MAXA
  • 2. COUNTA och COUNTIF
• Excel-formler för finansiell analys
  • 1. PRODUKT Funktion
  • 2. KVOTIENT- och LOGG-funktioner
  • Funktioner för investeringsscenarier
• Mellanliggande Excel datum och tid formler
  • 1. Mellanmånadsfunktioner
  • 2. Mellanveckofunktioner
  • 3. Mellantidsfunktioner
• Excel-funktioner för villkorlig och logisk algebra
  • 1. OM-funktion
  • 2. OCH Funktion
  • 3. ELLER-funktion
  • 4. INTE Funktion
• Refererande celler
  • 1. INDIREKT Funktion
  • 2. INDEX Funktion
  • 3. OFFSET-funktion
• Mellanliggande textformler
  • 1. TEXTJOIN-funktion
  • 2. BYT Funktion
  • 3. ERSÄTTNINGSfunktion
• Formler för mellanliggande uppslag
• Formler för hantering av Microsoft Excel-fel
  • 1. IFERROR-funktion
  • 2. IFNA-funktion
• Formel och funktionsgenvägar
• Mellanliggande cellformatering
• Slutgiltiga tankar

Mellanliggande matematiska formler

Vårt fuskblad för nybörjare täckte de vanligaste matematiska funktionerna för nybörjare som SUM-funktionen. Mellanliggande användare bör bli bekanta med dessa mer komplexa funktioner:

• ABS() returnerar det absoluta värdet av ett tal.

• SQRT() returnerar kvadratroten ur ett tal

• RAND() ger ett slumptal mellan 0 och 1

Villkorlig logik

Mellanliggande användare bör också veta hur man utför matematiska beräkningar baserat på flera villkor med hjälp av dessa funktioner:

• SUMIFS(summa_intervall, kriterieområde1, kriterie1, [….])

• ANTAL.OMF(kriterieområde1; kriterium1; [kriterieområde2; kriterium2], …)

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

Funktionerna utvärderar de angivna kriterierna och ger ett logiskt värde på TRUE eller FALSE.

Låt oss säga att du har följande försäljningsdata i kolumnerna A, B och C:

För att beräkna summan av försäljningen för röda t-shirts, använd SUMIFS-formeln:

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

För att räkna antalet rader med röda t-shirts, använd COUNTIFS-formeln:

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

För att beräkna den genomsnittliga försäljningen för röda t-shirts, använd AVERAGEIFS-formeln:

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

Mellanliggande statistiska formler

Bortsett från grundläggande funktioner som MIN och MAX, har Excel ett bredare utbud av statistiska formler för medelanvändare. Här är några av de mest användbara:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA och MAXA

De vanligare MIN- och MAX-funktionerna ignorerar textvärden.

De alternativa MINA- och MAXA-funktionerna tar hänsyn till både text och siffror när man letar efter de högsta eller lägsta värdena. Textvärden utvärderas som om de vore noll.

Skillnaderna visas på bilden nedan. Den första raden utvärderar ett lägsta värde på 10, medan den andra raden utvärderas som 0 på grund av närvaron av ett textvärde.

2. COUNTA och COUNTIF

Funktionen COUNTA används för att i ett intervall.

Funktionen COUNTIF är mindre specifik eftersom den används för att räkna antalet celler inom ett intervall som uppfyller ett specifikt villkor eller kriterier.

Du kan också använda dessa funktioner för att räkna antalet distinkta värden i en kolumn. Den här videon visar hur du gör det.

Excel-formler för finansiell analys

Det finns flera formler du kommer att vilja använda när du utför finansiell analys, som att prognostisera investeringar:

• PRODUKT(nummer1, [nummer2...])

• KVOTIENT(täljare, nämnare)

• LOG(tal, [bas])

1. PRODUKT Funktion

Anta att du vill beräkna det framtida värdet av en initial investering på 1 000 $ (cell B1) med en årlig ränta på 4% (cell B2) efter fem år (cell B3).

Använd funktionen PRODUKT för att beräkna det framtida värdet med denna formel:

=B1 * PRODUKT(1 + B2)^B3

Denna formel kommer att returnera det framtida värdet av din investering.

2. KVOTIENT- och LOGG-funktioner

Nästa steg är att beräkna hur många år det tar för en investering att fördubblas i värde vid den givna räntan.

Använd funktionen KVOTIENT i kombination med LOGG-funktionen för att beräkna detta enligt följande:

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

Denna formel returnerar 17 med provdata, vilket indikerar att det kommer att ta 17 år för investeringen att fördubblas i värde vid en årlig ränta på 4%.

Den här bilden :

Funktioner för investeringsscenarier

Det här är några funktioner som är lämpliga för specifika ekonomiska scenarier. En av dem kan vara precis vad du behöver:

• NPV – Funktionen NPV (Nuvärde) beräknar nuvärdet av en investering baserat på en serie framtida kassaflöden baserat på en diskonteringsränta.

• ACCRINT – Funktionen ACCRINT beräknar den upplupna räntan för ett värdepapper som betalar periodisk ränta. Detta är användbart för att bestämma räntan som tjänats in på ett värdepapper sedan den senaste betalningsdagen fram till en given likviddag.

• INTRATE – Funktionen INTRATE beräknar räntan för ett fullt investerat värdepapper.

• PMT – PMT -funktionen beräknar den totala betalningen på ett skuldpapper.

• IRR – IRR -funktionen ger den interna avkastningen.

• AVKASTNING – Funktionen AVKASTNING ger avkastningen för ett värdepapper baserat på ränta, nominellt värde och löptid.

Mellanliggande Excel datum och tid formler

De grundläggande datum- och tidsfunktionerna i Excel inkluderar funktionerna NU och IDAG för det aktuella datumet. Mellanliggande användare bör också veta hur man extraherar komponenter från ett givet datum med hjälp av:

• Dag datum)

• MÅNAD(datum)

• ÅR(datum)

Formeln =MÅNAD(“23 april 2023″) ger resultatet 4 för den fjärde månaden. På samma sätt kommer funktionerna DAG och ÅR att återkomma 23 respektive 2023.

1. Mellanmånadsfunktioner

Mellanliggande användare kommer ibland att ta itu med att lägga till eller och hitta slutet på en månad.

• EDATE(startdatum, antal_månader)

• EOMONTH(startdatum, antal_månader)

Till exempel kommer formeln =EDATE(“23 april 2023”, 2) att beräkna datumet två månader senare.

Formeln =EOMONTH(23 april 2023”, 2) beräknar slutet av månaden två månader senare.

Resultatet är ”30 juni 2023”, vilket tar hänsyn till att det bara är trettio dagar i juni. Om du anger 3 månader blir resultatet "31 juli 2023".

2. Mellanveckofunktioner

Du kan använda en kombination av funktionerna SUMMA och WEEKDAY för att räkna antalet hela arbetsdagar inom ett datumintervall.

Funktionen VECKODAG returnerar veckodagen för ett givet datum. Den andra parametern bestämmer numreringssystemet med standardvärdet söndag till lördag.

Om du har en serie datum i cellintervallet B1:B40, använd den här formeln för att beräkna arbetsdagarna:

=SUMMA(–(VARDAG(B1:B40,2)>5))

3. Mellantidsfunktioner

Funktionen YEARFRAC beräknar bråkdelen av ett år mellan två datum.

Funktionen TIDVÄRDE används för att konvertera en tid som representeras som text till ett decimaltal som representerar andelen av en 24-timmars dag.

Resultatet är ett Excel-serienummer där 1 representerar en hel 24-timmarsdygn, 0,5 representerar 12 timmar, 0,25 representerar 6 timmar, och så vidare.

Anta att du har en tid i textformat "17:45" i cell A1 och du vill konvertera den till ett decimaltal . Detta är formeln:

=TIDSVÄRDE(A1)

Värdet 0,74 representerar andelen av 24-timmarsdygnet som har passerat vid tiden femton minuter före klockan sex.

Ta till för att ändra tidscellen från ett tidsformat till ett textformat. Du kanske vill formatera detta som en procentsats för att bli tydligare.

Excel-funktioner för villkorlig och logisk algebra

Villkorliga och logiska funktioner är väsentliga för beslutsfattande i Excel. Följande funktioner kan kombineras med de mer grundläggande Excel-formlerna för kraftfull logik:

1. OM(villkor, värde_om_sant, värde_om_falskt):

2. OCH(villkor1, villkor2, …)

3. ELLER(villkor1, villkor2, …)

4. INTE (villkor1, villkor2, …)

OM-funktionen utvärderar ett villkor och returnerar olika värden beroende på om villkoret är sant eller falskt.

OCH-, ELLER- och INTE-funktionerna kontrollerar vilka villkor som är sanna och låter dig fatta beslut därefter.

Här är några exempel baserade på försäljningsdata vi använde tidigare.

1. OM-funktion

Anta att du vill visa texten "Låg försäljning" om försäljningen för varorna är mindre än 15. Om försäljningen är högre vill du visa "Hög försäljning" i en ny kolumn.

Använd den här formeln och kopiera ner den till de andra raderna:

=OM(C2<15, "Låg försäljning", "Hög försäljning")

2. OCH Funktion

Anta att du vill visa TRUE eller FALSE för t-shirts med minst 15 reor. Använd denna formel:

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

3. ELLER-funktion

Anta att du vill testa flera villkor och returnera TRUE om något av villkoren är uppfyllt och FALSE annars, då skulle du använda ELLER-funktionen.

Till exempel, om vi har poäng för ett test i cellerna A1 och B1, och vi vill veta om någon av poängen är över 80, kan vi använda: =ELLER( A1>80, B1>80) . Detta kommer att returnera TRUE om endera poängen (eller båda) är över 80, och FALSK om båda är 80 eller lägre.

4. INTE Funktion

Anta att du vill kontrollera om föremålet inte är en hoodie. Använd denna formel:

=INTE(A2=”Hoodie”)

Refererande celler

En nybörjare kommer snabbt att bli bekant med att använda absoluta och relativa referenser som $A$1 eller B2. Mellanliggande användare bör bli bekanta med att använda indirekta referenser, index och offset:

1. INDIREKT(ref_text)

2. INDEX(intervall, radnummer, kolumnnummer)

3. OFFSET(referenscell, rader, kolumner):

1. INDIREKT Funktion

Funktionen INDIREKTA returnerar värdet för den angivna cellreferensen som angetts som text. För att referera till cell B3, använd denna formel:

=INDIREKT(“B3”)

Fördelen jämfört med att använda enkla cellreferenser är att den här funktionen ger dig en dynamisk referens. Det innebär att Excel automatiskt uppdaterar referensen när strukturen i kalkylbladet ändras, t.ex. raderas en rad.

2. INDEX Funktion

Funktionen INDEX används för att referera till celler inom ett specificerat intervall baserat på rad- och kolumnnummer.

Använd denna formel för att referera till cell B3 i en tabell med sex rader:

=INDEX(B1:B6; 3)

3. OFFSET-funktion

OFFSET-funktionen returnerar en cell eller ett område som är ett angivet antal rader och kolumner borta från en referenscell.

Om du vill visa cellvärdet som är två celler tvärs över och en cell ner från A1, använd den här formeln:

=OFFSET(A1; 2; 1)

Här är formlerna i aktion:

Mellanliggande textformler

Nybörjare bör känna till textfunktioner som LEFT, som extraherar ett eller flera tecken från den vänstra sidan av en sträng. En avancerad användare bör vara bekant med funktioner som dessa:

1. TEXTJOIN(avgränsare, ignorera_tom, text1, [text2, …])

2. REPLACE(gammal_text, startnummer, antal_tecken, ny_text)

3. SUBSTITUTE(text; gammal_text; ny_text; [instansnummer])

1. TEXTJOIN-funktion

Funktionen TEXTJOIN sammanfogar celler med den avgränsare som du anger. Du kan också ange var tomma celler ska ignoreras.

För att skapa en kommaavgränsad lista över vissa objekt i kolumn A, använd den här formeln:

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

2. BYT Funktion

Ersätt-funktionen låter dig specificera startpositionen och längden på strängen du vill byta till målet.

För att ersätta de två första tecknen i en sträng med "XX", använd den här formeln:

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

3. ERSÄTTNINGSfunktion

Ersättningsfunktionen ersätter specificerade förekomster av en textsträng i en annan textsträng med en ny textsträng.

För att ersätta förekomster av ordet "Jeans" med "Leggings", använd denna formel:

=ERSÄTTNING(A3, "Jeans", "Båren")

Här är formlerna i aktion:

Formler för mellanliggande uppslag

Vanliga uppslagsfunktioner som VLOOKUP och MATCH täcks av nybörjarens fuskblad.

Mellanliggande användare bör bekanta sig med funktionen VÄLJ uppslag:

• VÄLJ(index_tal; värde1; [värde2; …])

Funktionen VÄLJ returnerar ett värde från en lista med värden baserat på ett angivet indexnummer.

Anta att du har en kolumn med t-shirtstorlekar märkta som 1, 2 eller 3. Du vill visa en kategori av små, medelstora respektive stora på samma rad. Använd denna formel:

=VÄLJ(A1, "Small", "Medium", "Large")

Formler för hantering av Microsoft Excel-fel

Fuskbladet för nybörjare listade de typiska felmeddelanden du kommer att se när du arbetar med Excel. Mellanliggande användare bör kunna använda formler för att hantera felen på ett elegant sätt.

1. IFERROR(värde, om_fel)

2. IFNA(värde, värde_om_na)

1. IFERROR-funktion

Division med noll fel är vanliga men Excels standardvisning av #DIV/0! kommer inte att vara tydlig för alla användare.

Använd IFERROR-funktionen för att ersätta standardfelet med "Kan inte dividera med noll" med denna formel:

=IFERROR(A1/B1, “Kan inte dividera med noll”)

Den här bilden visar råfelet i den tredje raden och IFERROR-funktionen som hanterar den fjärde raden.

2. IFNA-funktion

IFNA-funktionen i Excel används för att fånga och hantera #N/A-fel.

En annan del av detta fuskblad visade funktionen VÄLJ. Denna funktion returnerar ett N/A-fel om indexnumret inte är ett heltal.

Anta att en t-shirtstorlek har angetts som 1,5, där användaren av misstag tror att detta kommer att visa en kategori någonstans mellan small och medium. Istället kommer det att visa ett #N/A- fel.

För att ge ett mer användbart fel, använd den här formeln:

=IFNA(VÄLJ(A2, "Låg", "Medium", "Hög"), "Ogiltigt index")

Formel och funktionsgenvägar

Det finns flera kortkommandon som kan få dig att arbeta mer effektivt med ditt kalkylblad.

• F2 : Redigera den aktiva cellen och placera insättningspunkten i slutet av cellinnehållet.

• F9 : Beräkna och visa resultatet av den valda delen av en formel.

• Ctrl + Shift + Enter : Ange en matrisformel.

• Skift + F3 : Öppna dialogrutan Infoga funktion.

• Esc : Avbryt inmatningen av en formel och återgå till det ursprungliga cellinnehållet.

Vissa av dessa Excel-genvägar kanske inte är tillgängliga på alla språk eller tangentbordslayouter.

Mellanliggande cellformatering

I finansiell analys är korrekt formatering av celler som innehåller siffror, datum och valutor avgörande. För att formatera celler, följ dessa steg:

1. Välj de celler du vill formatera.

2. Högerklicka på de markerade cellerna och välj "Formatera celler".

3. Välj lämplig kategori (t.ex. Antal, Valuta, Datum) och använd önskat format.

Några vanliga formateringstyper att överväga inom ekonomi inkluderar:

• Valuta : 1 234,56 USD

• Procent : 12,34 %

• Bokföring : (1 234,00 USD)

• Datum : 10 maj 2023

Slutgiltiga tankar

När du blir mer bekväm med de olika formlerna och funktionerna som lyfts fram i detta kommer du att hitta dig själv bättre rustad att hantera allt mer komplexa uppgifter och projekt.

Övning är nyckeln till att bemästra alla färdigheter, så var inte rädd för att experimentera med dessa formler i ditt dagliga arbete. De kommer att ta din expertis från nybörjarnivå till medelnivå.

Det här är bara ett smakprov på vad Excels mellanfunktioner kan göra för att förenkla ditt arbete, effektivisera dina analyser och hjälpa dig att avslöja insikter från dina data.

Den verkliga magin uppstår när du börjar kombinera funktionerna i vårt cheat sheet och skräddarsy dem för att passa dina unika behov. Så sluta inte här. Fortsätt utforska, fortsätt experimentera, så kommer du att upptäcka att Excel inte bara är ett verktyg, det är en spelförändring!

Känner du att du vill ta saker till nästa nivå? Kolla vår