Excel Formula Cheat Sheet: Avancerad guide

Så du har gått igenom grunderna, tagit itu med de mellanliggande sakerna och nu är du redo att brottas med de stora kanonerna - Excels avancerade formler! Excel har många avancerade funktioner och formler för sofistikerade beräkningar, så det är användbart att ha en guide som pekar ut de du behöver.

Det här fuskbladet för Excel-formler täcker avancerade prognosformler, statistisk analys, datamanipuleringsfunktioner, felhantering och mer.

Denna referens kommer att förse dig med kunskap om hur du använder dessa avancerade funktioner. Varje formel åtföljs av tydliga förklaringar, syntax och praktiska exempel för att hjälpa mellanliggande Excel-användare att bli avancerade avancerade användare.

Ladda ner och skriv ut fuskbladet och ha det till hands.

Okej, låt oss börja.

Först, låt oss komma in på Array-formler.

Innehållsförteckning

• Matrisformler
  • 1. UNIK funktion
  • 2. SORTERINGSfunktion
  • 3. FILTER Funktion
• Randomisering av Excel-funktioner
  • 1. RANDBETWEEN Funktion
  • 2. RANDARRAY Funktion
• Avancerade prognosformler i Microsoft Excel
  • 1. FORECAST.ETS-funktion
  • 2. TREND Funktion
• Avancerade statistiska formler
  • 1. PERCENTIL Funktion
  • 2. KVARTIL Funktion
• Avancerad dataanalys och manipulationsformler
  • 1. TRANSPOSERA Funktion
  • 2. FREKVENS Funktion
  • 3. GETPIVOTDATA-funktion
• Avancerad felhantering
• Avancerade uppslagsformler
  • 1. XLOOKUP-funktion
  • 2. XMATCH-funktion
• Slutgiltiga tankar

Matrisformler

Vår visar hur du sorterar och filtrerar dina data manuellt. Avancerade användare gör detta programmatiskt med matrisformler.

Matrisformler låter dig utföra beräkningar på flera celler samtidigt. Det här är tre nyckelfunktioner:

1. UNIK

2. SORTERA

3. FILTRERA

Vissa av dessa funktioner är endast tillgängliga i de senaste versionerna av Microsoft Excel.

1. UNIK funktion

Funktionen UNIK accepterar ett intervall och returnerar en lista med unika värden.

Anta att du har försäljningsdata för klädesplagg. För att hitta de unika föremålen i kolumn A, använd denna formel:

=UNIQUE(A2:A6)

2. SORTERINGSfunktion

SORTERINGSfunktionen sorterar innehållet i ett intervall. Syntaxen är:

SORT(matris, [sorteringsindex], [sorteringsordning], [efter_kolum])

• array : intervallet av värden som ska sorteras.

• sort_index : kolumnen som ska sorteras (1 som standard)

• sort_order : 1 för stigande (standard) eller 2 för fallande).

• by_col : TRUE för att sortera efter kolumn (standard) eller FALSE för att sortera efter rad.

De tre sista argumenten är valfria, och standardinställningarna är vanligtvis vad du vill ha.

För att sortera exempeldata efter den första kolumnen, använd den här formeln:

=SORT(A2:C6)

3. FILTER Funktion

FILTER-funktionen låter dig filtrera ett intervall efter ett specifikt tillstånd. Detta är syntaxen:

=FILTER(matris, inkludera, [om_tom])

• array : intervallet som ska filtreras.

• include : villkoret som bestämmer vilka värden som ska filtreras.

• if_empty : anger vad som ska returneras om inga värden uppfyller filtreringskriterierna (standard är "").

Anta att du vill filtrera raderna i exempeldatan för att endast visa där försäljningsvärdet är större än 15 USD. Använd denna formel:

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

Randomisering av Excel-funktioner

Vårt mellanfusk visar hur man använder RAND-funktionen som ger ett slumptal mellan 0 och 1.

Avancerade Excel-användare vet hur man använder randomiseringsfunktionerna för att snabbt generera exempeldata.

1. RANDBETWEEN Funktion

RANDBETWEEN-funktionen är mer flexibel än RAND eftersom du kan ange botten- och toppnumren som något annat än 0 och 1.

För att generera data med siffror mellan 1 och 100, skriv in den här formeln i cell A1:

=RANDBETWEEN(1, 100)

Kopiera sedan cellen till så många rader och kolumner du vill. Det tar sekunder att skapa ett rutnät med slumpmässiga tal:

2. RANDARRAY Funktion

Du kanske tänker att det skulle vara trevligt att undvika den manuella kopian av RANDMELLAN-funktionen. För att bli superavancerad kan du använda den nya RANDARRAY-funktionen i den senaste versionen av Microsoft Excel.

Syntaxen är:

RANDARRAY([rader], [kolumner], [min], [max], [helt antal])

• rader : antal rader

• kolumner : antal kolumner

• min : lägsta antal

• max : högsta antal

• heltal : har som standard SANT, annars används decimaltal.

För att generera slumpmässiga tal mellan 1 och 100 över sex rader och nio kolumner, använd den här formeln:

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

Avancerade prognosformler i Microsoft Excel

Excels prognosfunktioner används för att prognostisera framtida värden baserat på befintliga datatrender. Dessa funktioner hjälper till att identifiera mönster och projekttrender baserat på dina data.

1. FORECAST.ETS-funktion

Den äldre FORECAST-funktionen ersattes med en uppsättning nyare funktioner i Excel 2016.

Du väljer funktionen utifrån den specifika prognosmodell du vill ha. Funktionen FORECAST.ETS använder till exempel algoritmen för exponentiell utjämning .

Syntaxen är:

FORECAST.ETS(måldatum, värden, tidslinje)

• target_date : det datum du vill ha ett beräknat värde för.

• värden : historiska data.

• tidslinje : ett datumintervall

Anta att du har datum från den 1:a till den 5:e januari i kolumn A och försäljningsbelopp i kolumn B. Denna formel förutsäger nästa försäljningsbelopp:

=PROGNOS.ETS(“6 januari 2023”, B2:B6, A2:A6)

2. TREND Funktion

TREND-funktionen projicerar en uppsättning värden baserat på minsta kvadratmetoden. Den returnerar en array. Syntaxen är:

TREND(känd_y, [känd_x], [ny_x], [konst])

• känd_y : intervall av y-värden

• känd_x : intervall av x-värden

• new_x : intervall av beräknade värden

Ofta är känd_y datapunkterna, medan känd_x är datum.

Med samma data som i föregående exempel kan du ange formeln nedan i cell C2. En uppsättning värden kommer att genereras.

=TREND(B2:B6, A2:A6)

Avancerade statistiska formler

De avancerade statistiska funktionerna inkluderar beräkning av percentiler och kvartiler. Vissa matematiska funktioner är tillgängliga för bakåtkompatibilitet, men det rekommenderas att använda de senaste versionerna.

1. PERCENTIL Funktion

Den här funktionen beräknar procentandelen datapunkter som faller under ett visst värde. Syntaxen är:

PERCENTIL.INC(matris, k)

• array : cellområdet

• k : percentilen från 0 till 1

Anta att du vill beräkna den 70:e percentilen av data i kolumn B. Använd denna formel:

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

2. KVARTIL Funktion

Denna funktion är en variant av percentilfunktionen men använder fjärdedelar för att dela upp data. Detta är syntaxen:

QUARTILE.INC(array, quart)

• array : dataintervallet

• quart : 1 för den 25:e percentilen, 2 för den 50:e, 3 för den 75:e och 4 för den maximala.

Formeln nedan kommer att beräkna den första kvartilen av data i kolumn A.

=QUARTILE.INC(A2:A5; 1)

Avancerad dataanalys och manipulationsformler

Flera avancerade funktioner låter dig byta dataformat, analysera frekvensfördelningar och extrahera data från pivottabeller.

1. TRANSPONERA

2. FREKVENS

3. GETPIVOTDATA

1. TRANSPOSERA Funktion

Ibland vill du flytta data i dina rader till kolumner och vice versa. Du kan göra detta manuellt eller använda TRANSPOSE-funktionen istället.

Anta att du har föremålen "T-shirt", "Hoodie" och "Jeans" i cellerna A2, A3 och A4. Du vill göra dessa till kolumnrubriker. Denna funktion returnerar värdena i en enda rad:

=TRANSPOSERA(A2:A4)

2. FREKVENS Funktion

Denna funktion beräknar frekvensfördelningen av värden inom en datauppsättning. Detta är syntaxen:

FREKVENS(data_array, bins_array)

• data_array : värdeintervall.

• bins_array : intervallerna som ska användas.

Anta att du har försäljningsdata i kolumn B och du vill analysera frekvensfördelningen av värdena baserat på hur många belopp som är:

• under 20.

• från 20 till 80.

• över 80.

Det representerar tre fack och kan beräknas med denna formel:

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

För mer om frekvensdistributioner i Excel, kolla in den här videon:

3. GETPIVOTDATA-funktion

Den här funktionen låter dig extrahera sammanfattad information från pivottabeller. Detta är syntaxen:

GETPIVOTDATA(datafält, pivottabell, [fält1, objekt1], [fält2, objekt2], …)

• data_field : datafältet eller värdet som du vill hämta från pivottabellen.

• pivot_table : en referens till pivottabellen.

• fält1, objekt1, etc. : fält-/objektparen att filtrera efter.

Anta att du har en pivottabell baserat på färgen på sålda varor. För att extrahera försäljningen för röda varor, använd denna formel:

=GETPIVOTDATA("Försäljning", A1, "Färg", "Röd")

Avancerad felhantering

Även de mest grundläggande Excel-formlerna kan ge fel. Mellanliggande användare bör veta hur man använder ISERROR för att hantera fel. Avancerade användare bör också vara bekanta med funktionen ERROR.TYPE för felidentifiering.

Funktionen ERROR.TYPE hjälper till att identifiera den specifika typen av fel i en cell eller formel.

Den returnerar ett numeriskt värde som motsvarar olika feltyper, såsom #N/A, #VALUE!, #REF! och mer.

Anta att du har ett fel i cell A1 och du vill identifiera dess feltyp. Följande formel returnerar numret som motsvarar det specifika felet:

=ERROR.TYPE(A1)

Du kan kombinera detta med flera funktioner för att svara olika beroende på typ av fel. Dessa är de vanligaste felen och deras värden:

1. #NULL! (ingen vanlig cell hittades i ett intervall)

2. #DIV/0! ( division med noll eller en tom cell)

3. #VÄRDE! (olämplig datatyp eller argument i en formel)

4. #REF! (en refererad cell har tagits bort eller det finns en )

5. #NAMN? (Excel känner inte igen funktionen eller intervallet)

6. #NUM! (ogiltigt numeriskt värde)

7. #N/A (värdet kan inte hittas)

Anta att du vill hantera tre specifika feltyper. Använd den här formeln för att visa ett specifikt felmeddelande baserat på typen:

=OM(FEL(C2), OM(FEL.TYP(C2)=2, “Dividera med nollfel”, OM(FEL.TYP(C2)=3, “Ogiltigt värdefel”, OM(FEL.TYP(C2) )=7, "Värde ej hittat fel", "Annat fel"))), C2)

Avancerade uppslagsformler

Våra fuskblad för nybörjare och medelnivå täckte en . Här är några avancerade alternativ:

1. XLOOKUP

2. XMATCH

1. XLOOKUP-funktion

Denna uppslagsfunktion låter dig söka efter ett värde i ett intervall och returnera ett motsvarande värde från en annan kolumn eller intervall.

Den erbjuder mer mångsidighet än enklare uppslagsfunktioner som . Detta är syntaxen:

XLOOKUP(uppslagsvärde, uppslagsmatris, returmatris, [matchningsläge], [sökläge], [om_inte_hittades])

• lookup_value : värdet du vill söka efter.

• lookup_array : intervallet för uppslagningen.

• return_array : intervallet som visar motsvarande värde.

• match_mode : exakt matchning (0), nästa mindre (1), nästa större (-1) eller jokerteckenmatchning (2).

• search_mode : -1 för topp till botten, 1 för botten till topp, eller 2 för binär sökning.

• if_not_found : ställer in värdet som returneras om ingen matchning hittas.

Anta att du vill söka i en rad data för den första förekomsten av en klädvara och returnera försäljningsbeloppen. Denna formel kommer att leta efter texten "Hoodie" och returnera värdet i den intilliggande cellen om den hittas:

=XLOOKUP(”Hoodie”, A2:A6, B2:B6)

2. XMATCH-funktion

Denna funktion låter dig hitta positionen för ett angivet värde inom ett område eller en matris. Detta är syntaxen:

XMATCH(uppslagsvärde, uppslagsmatris, [matchningstyp], [sökläge])

• lookup_value : värdet du vill hitta.

• lookup_array : Området du vill söka efter.

• match_type : exakt matchning (0), näst minsta (-1), näst största (1).

• sökläge : binär sökning (1) eller linjär sökning (2).

Anta att du vill hitta den första förekomsten av ett gult objekt i ett intervall inom kolumn B. Använd denna formel:

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

Slutgiltiga tankar

Detta fuskblad har täckt ett brett utbud av funktioner, från statistisk analys, uppslagsformler, datamanipuleringstekniker och felhanteringsstrategier.

De medföljande exemplen och förklaringarna hjälper till att avmystifiera dessa avancerade formler, vilket gör dem tillgängliga även för dem med begränsad erfarenhet.

När du börjar införliva dem i dina Excel-uppgifter är du på väg att öka dina Excel-kunskaper till en avancerad nivå.

Men kom ihåg, det här fuskbladet är bara toppen av isberget. De riktigt fantastiska sakerna händer när du blir kreativ, mixar och matchar dessa formler och skräddarsyr dem för att lösa dina unika utmaningar. Excel är som en duk, och dessa formler är din palett - så fortsätt, måla ditt mästerverk!