Riktlinjer för och exempel på matrisformler

Riktlinjer för och exempel på matrisformler

Viktigt!: Den här artikeln är maskinöversatt, se ansvarsfriskrivningen. Den engelska versionen av den här artikeln finns här för din referens.

Om du vill bli en Excel power användare måste du veta hur du använder matrisformler som kan utföra beräkningar som du inte kan göra med hjälp av icke-matrisformler. I följande artikel baseras på en serie Excel Power User kolumner skrivet av Colin Wilcox och anpassas från kapitel 14 och 15 i Formler i Excel 2002, en bok som skrivits av John Walkenbach, ett Excel-MVP.

Lär dig mer om matrisformler

Matrisformler kallas ibland för Ctrl+Skift+Retur-formler, eftersom du trycker på Ctrl+Skift+Retur för att slutföra formeln i stället för bara Retur.

Varför ska du använda matrisformler?

Om du är van vid att använda formler i Excel vet du redan att du kan göra några relativt avancerade saker. Du kan till exempel beräkna totalkostnaden för ett lån över ett visst antal år. Du kan använda matrisformler för komplexa beräkningar, till exempel:

  • Räkna antalet tecken som finns i ett cellområde.

  • Summera enbart tal som uppfyller vissa villkor, till exempel de lägsta värdena i ett sifferintervall som faller inom en övre och en nedre gräns.

  • Summera var n:e värde i en rad med värden.

En snabbintroduktion till matriser och matrisformler

En matrisformel är en formel som kan användas för olika beräkningar med ett eller flera element i en matris. En matris är en rad eller en kolumn med värden, eller en kombination av flera rader och kolumner med värden. Matrisformler kan returnera antingen flera resultat eller ett enstaka resultat. Du kan till exempel skapa en matrisformel i ett cellområde och använda matrisformeln för att räkna ut en kolumn eller rad med delsummor. Du kan också placera en matrisformel i en enstaka cell och sedan beräkna en enskild summa. En matrisformel som sträcker sig över flera celler kallas en flercellsformel, och en matrisformel i en enstaka cell kallas en encellsformel.

Exemplen i avsnittet nedan visar hur du skapar flercells- och encellsmatrisformler.

Prova själv!

I den här övningen lär du dig att använda flercells- och encellsmatrisformler för att beräkna en uppsättning försäljningssiffror. I de första stegen används en flercellsformel för att räkna ut delsummor. I den andra delen används en encellsformel för att räkna ut en totalsumma.

Flercellsmatrisformel

Här finns en arbetsbok som är inbäddad i webbläsaren. Även om arbetsboken innehåller exempeldata bör du känna till att du inte kan skapa eller ändra matrisformler i en inbäddad arbetsbok. Du måste använda programmet Excel. Du se svaren i den inbäddade arbetsboken, och viss text som förklarar hur matrisformeln fungerar, men om du verkligen vill sätta dig in i hur formler används ska du öppna hela arbetsboken i Excel.

Skapa en flercellsmatrisformel
  1. Kopiera hela tabellen nedan och klistra in den i cell A1 i ett tomt kalkylblad i Excel.

    Säljare

    Biltyp

    Antal
    sålda exemplar

    Enhetspris

    Total försäljning

    Barnhill

    Sedan

    5

    33000

    Kupé

    4

    37000

    Ingle

    Sedan

    6

    24000

    Kupé

    8

    21000

    Jordan

    Sedan

    3

    29000

    Kupé

    1

    31000

    Pica

    Sedan

    9

    24000

    Kupé

    5

    37000

    Sanchez

    Sedan

    6

    33000

    Kupé

    8

    31000

    Formel (slutsumma)

    Slutsumma

    '=SUMMA(C2:C11*D2:D11)

    =SUMMA(C2:C11*D2:D11)

  2. Om du vill visa Total försäljning av kupéer och sedaner för varje säljare markerar du E2:E11, anger formeln =C2:C11*D2:D11 och trycker på Ctrl+Skift+Retur.

  3. Om du vill visa Totalsumma för alla sålda objekt markerar du cell F11, anger formeln =SUMMA(C2:C11*D2:D11) och trycker på Ctrl+Skift+Retur.

Du kan hämta den här arbetsboken genom att klicka på den gröna Excel-knappen i det svarta fältet längst ned i arbetsboken. Sedan kan du öppna filen i Excel, markera de celler som innehåller matrisformlerna och trycka på Ctrl+Skift+Retur för att få formeln att fungera.

Om du följer med i exemplet i Excel måste du se till att Blad1 är aktivt, och sedan markera cellerna E2:E11. Tryck på F2 och skriv formeln =C2:C1*D2:D11 i den aktuella cellen, E2. Om du trycker på Retur ser du att formeln bara matas in i cell E2, och att resultatet visar 165 000. Tryck i stället Ctrl+Skift+Retur när du har skrivit formeln, och inte bara på Retur. Nu ser du resultaten i cellerna E2:E11. Observera att formeln visas som {=C2:C11*D2:D11} i formelfältet. Det betyder att du arbetar med en matrisformel. Detta visas nedan.

När du trycker på Ctrl+Skift+Retur omges formeln med klammerparenteser ({ }) och en instans av formeln infogas i varje cell i det markerade området. Det går väldigt snabbt, så det du ser i kolumn E är den totala försäljningssumman för varje biltyp per försäljare. Om du markerar E2 och sedan E3, E4 och så vidare ser du att samma formel visas: {=C2:C11*D2:D11}.

Summorna i kolumn E beräknas av en matrisformel

Skapa en encellsmatrisformel

Skriv följande i cell F10 i arbetsboken, och tryck sedan på Ctrl+Skift+Retur:

=SUMMA(C2:C11*D2:D11)

I det här fallet multipliceras värdena i matrisen (cellområdet C2 till D11) i Excel och sedan används funktionen SUMMA för att räkna ihop totalsumman. Resultatet blir en totalsumma på 1 590 000 $ i försäljning. I det här exemplet kan du se hur effektiv den här typen av formel kan vara. Tänk dig till exempel att du har 1 000 rader med data. Du kan summera delar av eller hela den här informationsmängden genom att skapa en matrisformel i en enda cell, i stället för att dra formeln ned över alla 1 000 raderna.

Lägg också märke till att encellsformeln (i cell B13) är helt fristående från flercellsformeln (formeln i cell E2 till E11). Det här är en annan fördel med att använda matrisformler – flexibilitet. Du kan ändra formlerna i kolumn E eller ta bort den kolumnen helt och hållet, utan att formeln i cell G11 påverkas.

Matrisformler har dessutom följande fördelar:

  • Konsekvens    Om du klickar i några celler nedanför E2 kan du se att samma formel används. Med konsekvens blir resultatet också mer korrekt.

  • Säkerhet    Du kan inte skriva över en komponent i en flercellsmatrisformel. Klicka till exempel på cell E3 och tryck på Delete. Du måste antingen markera hela cellområdet (E2 till E11) och ändra formeln för hela matrisen, eller låta matrisen vara som den här. Som en extra säkerhetsåtgärd måste du trycka på Ctrl+Skift+Retur och bekräfta ändringen av formeln.

  • Mindre filstorlekar    Ofta kan du använda en enda matrisformel i stället för flera delformler. I arbetsboken används till exempel en matrisformel för att beräkna resultatet i kolumnen E. Om du hade använt standardformler (till exempel =C2*D2, C3*D3, C4*D4) hade du behövt använda 11 olika formler för att räkna ut samma resultat.

Matrisformlernas uppbyggnad

I allmänhet används vanlig formelsyntax i matrisformler. De börjar alltid med ett likhetstecken (=) och du kan använda de flesta av de inbyggda Excel-funktionerna i matrisformler. Den stora skillnaden när du använder en matrisformel är att du måste trycka på Ctrl+Skift+Retur när du vill ange formeln. När du gör det omges matrisformeln med klammerparenteser – om du skriver in klammerparenteserna manuellt konverteras formeln till en textsträng och fungerar inte.

Matrisfunktioner är ett mycket effektivt sätt att skapa komplexa formler. Matrisformeln =SUMMA(C2:C11*D2:D11) har samma resultat som det här: =SUMMA(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Ange och ändra matrisformler

Viktigt    Tryck på Ctrl+Skift+Retur när du behöver ange eller redigera en matrisformel. Detta gäller både encells- och flercellsformler.

När du arbetar med flercellsformler ska du dessutom tänka på det här:

  • Markera cellområdet där resultatet ska visas innan du anger formeln. Detta gjorde du när du skapade flercellsmatrisformeln när du markerade cell E2 till E11.

  • Du kan inte ändra innehållet i en enskild cell i en matrisformel. Prova själv genom att markera cell E3 i kalkylbladet och trycka på Delete. Ett felmeddelande om att du inte kan ändra en del av matrisen visas.

  • Du kan flytta eller ta bort en hel matrisformel, men det går inte att flytta eller ta bort delar av den. Med andra ord: om du vill minska ned en matrisformel tar du först bort formeln och börjar sedan om från början.

  • Om du vill ta bort en hel matrisformel markerar du hela formeln (till exempel =C2:C11*D2:D11), trycker på Delete och trycker sedan på Ctrl+Skift+Retur.

  • Det går inte att infoga tomma celler i eller ta bort celler från en flercellsmatrisformel.

Utöka en matrisformel

Ibland kan du behöva utöka en matrisformel. Det är inte svårt att göra det, men du måste tänka på att följa reglerna i avsnittet ovan.

I det här kalkylbladet har vi lagt till några fler försäljningsrader, i raderna 12 till 17. Här vill vi uppdatera matrisformlerna så att de nu innehåller de ytterligare raderna.

Se till att du gör det här i skrivbordsprogrammet Excel (efter att du har hämtat arbetsboken till din dator).

Utöka en matrisformel
  1. Kopiera den här tabellen till cell A1 i ett Excel-kalkylblad.

    Säljare

    Biltyp

    Antal
    sålda exemplar

    Enhetspris

    Total försäljning

    Barnhill

    Sedan

    5

    33000

    165000

    Kupé

    4

    37000

    148000

    Ingle

    Sedan

    6

    24000

    144000

    Kupé

    8

    21000

    168000

    Jordan

    Sedan

    3

    29000

    87000

    Kupé

    1

    31000

    31000

    Pica

    Sedan

    9

    24000

    216000

    Kupé

    5

    37000

    185000

    Sanchez

    Sedan

    6

    33000

    198000

    Kupé

    8

    31000

    248000

    Toth

    Sedan

    2

    27000

    Kupé

    3

    30000

    Wang

    Sedan

    4

    22000

    Kupé

    1

    41000

    Young

    Sedan

    5

    32000

    Kupé

    3

    36000

    Slutsumma

  2. Markera cell E18 och ange formeln Slutsumma =SUMMA(C2:C17*D2:D17) i cell A20 och tryck sedan på Ctrl+Skift+Retur.
    Svaret bör vara 2 310 000.

  3. Markera det cellområde som innehåller den nuvarande matrisformeln (E2:E11), plus de tomma cellerna (E12:E17) som finns bredvid nya data. Markera helt enkelt cellerna E2:E17.

  4. Tryck på F2 för att byta till redigeringsläge.

  5. Ändra C11 till C17 och ändra D11 till D17 i formelfältet, och tryck sedan på Ctrl+Skift+Retur.
    Formeln uppdateras i cell E2 till E11 och en instans av formeln placeras i de nya cellerna, E12 till E17.

  6. Skriv matrisformeln = SUMMA(C2:C17*D2*D17) i cell F17 så att den refererar till cellerna från rad 2 till rad 17, och tryck på Ctrl+Skift+Retur för att ange den nya matrisformeln.
    Den nya totalsumman ska vara 2 131 000.

Nackdelar med att använda matrisformler

Matrisformler är mycket effektiva, men det finns några nackdelar:

  • Ibland kanske du glömmer att trycka på Ctrl+Skift+Retur, även om du har arbetat mycket med Excel. Kom ihåg att alltid trycka på den här knappkombinationen när du vill ange eller redigera en matrisformel.

  • Andra som använder din arbetsbok kanske inte förstår hur formlerna fungerar. I praktiken är det sällan som det finns någon dokumentation om matrisformler i ett kalkylblad, så om andra personer behöver redigera dina arbetsböcker bör du antingen låta bli att använda matrisformler eller se till att andra användare känner till matrisformler och vet hur de ska ändra dem, om det skulle behövas.

  • Beroende på datorns kapacitet kan stora matrisformler göra att beräkningar går långsammare.

Överst på sidan

Lär dig mer om matriskonstanter

Matriskonstanter är komponenter i matrisformler. Du skapar en matriskonstant genom att ange en lista över element och sedan manuellt omge listan med klammerparenteser ({ }), på det här sättet:

={1,2,3,4,5}

Vid det här laget vet du antagligen att du måste trycka på Ctrl+Skift+Retur när du skapar matrisformler. Eftersom matriskonstanter är komponenter i matrisformler omger du konstanterna med klammerparenteser manuellt genom att skriva in dem. Sedan trycker du på Ctrl+Skift+Retur när du vill ange hela formeln.

Om du avgränsar elementen med kommatecken skapar du en vågrät matris (en rad). Om du avgränsar elementen med semikolon skapar du en lodrät matris (en kolumn). Om du vill skapa en tvådimensionell matris avgränsar du elementen på varje rad med kommatecken och varje rad med semikolon.

Här är ett exempel på en rad: {1; 2; 3; 4}. Här är exempel på en kolumn: {1\2\3\4}. Och här är ett exempel på två rader och fyra kolumner: {1;2;3;4\5;6;7;8}. I matrisen med två rader innehåller den första raden elementen 1, 2, 3 och 4, och den andra raden innehåller 5, 6, 7 och 8. Ett omvänt snedstreck används för att avgränsa de båda raderna, mellan elementet 4 och elementet 5.

Precis som med matrisformler kan du använda matriskonstanter med de flesta av de inbyggda funktionerna i Excel. I avsnitten nedan får du en förklaring på hur du skapar de olika typerna av konstanter och hur du använder konstanterna med funktioner i Excel.

Överst på sidan

Skapa endimensionella och tvådimensionella konstanter

I procedurerna nedan får du öva dig på att skapa vågräta, lodräta och tvådimensionella konstanter.

Skapa en vågrät konstant

  1. Använd arbetsboken från tidigare exempel eller skapa en ny arbetsbok.

  2. Markera cellerna A1 till och med E1.

  3. Ange följande formel i formelfältet och tryck sedan på Ctrl+Skift+Retur:

    ={1,2,3,4,5}

    I det här fallet bör du skriva in inledande och avslutande klammerparenteser ({ }).

    Följande resultat visas.

    Vågrät matriskonstant i formel

Skapa en lodrät konstant

  1. Markera en kolumn med fem celler i kalkylbladet.

  2. Ange följande formel i formelfältet och tryck sedan på Ctrl+Skift+Retur:

    ={1;2;3;4;5}

    Följande resultat visas.

    Lodrät matriskonstant i matrisformel

Skapa en tvådimensionell konstant

  1. Markera ett cellblock som är fyra kolumner brett och tre rader högt.

  2. Ange följande formel i formelfältet och tryck sedan på Ctrl+Skift+Retur:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    Följande resultat visas.

    Tvådimensionell matriskonstant i matrisformel

Använda konstanter i formler

Här är ett enkelt exempel som använder konstanter:

  1. Skapa ett nytt kalkylblad i exempelarbetsboken.

  2. Skriv 3 i cell A1 och sedan 4 i B1, 5 i C1, 6 i D1 och 7 i E1.

  3. Skriv in följande formel i cell A3, och tryck sedan på Ctrl+Skift+Retur:

    =SUMMA(A1:E1*{1,2,3,4,5})

    Lägg märke till att konstanten omges med ytterligare två klammerparenteser, eftersom du angav den som en matrisformel.

    Matrisformel med matriskonstant

    Värdet 85 visas i cell A3.

I nästa avsnitt kan du se hur formeln fungerar.

Matriskonstanters uppbyggnad

Formeln som du precis använde innehåller flera delar.

Syntax för matrisformel med matriskonstant

1. Funktion

2. Sparad matris

3. Operator

4. Matriskonstant

Det sista elementet innanför parentesen är matriskonstanten: {1;2;3;4;5}. Kom ihåg att matriskonstanter inte omges med klammerparenteser av Excel: det måste du göra själv. Kom också ihåg att när du har lagt till en konstant i en matrisformel ska du trycka på Ctrl+Skift+Retur när du anger formeln.

Eftersom åtgärder utförs på uttryck som omges av parenteser först blir de nästa två elementen som kommer in i ekvationen de värden som sparas i kalkylbladet (A1:E1) och operatorn. I det här läget multipliceras värdena i den sparade matrisen med de motsvarande värdena i konstanten. Det motsvarar:

=SUMMA(A1*1;B1*2;C1*3;D1*4;E1*5)

Slutligen summeras värdena med funktionen SUMMA och summan 85 visas i cell A3:

För att undvika att använda den sparade matrisen och för att bara spara åtgärden helt och hållet i minnet ersätter du den sparade matrisen med en annan matriskonstant:

=SUMMA({3,4,5,6,7}*{1,2,3,4,5})

Prova detta genom att kopiera funktionen, markera en tom cell i kalkylbladet, klistra in formeln i formelfältet och tryck sedan på Ctrl+Skift+Retur. Du kan se samma resultat som du gjorde i den tidigare övningen med matrisformeln:

=SUMMA(A1:E1*{1,2,3,4,5})

Element som du kan använda i konstanter

Matriskonstanter kan innehålla tal, text, logiska värden (till exempel SANT och FALSKT) och felvärden (till exempel #SAKNAS). Du kan använda heltal, decimaltal och tal i exponentialform. Om du använder text måste du omge texten med dubbla citattecken (").

Matriskonstanter kan inte innehålla ytterligare matriser, formler eller funktioner. Med andra ord: de kan endast innehålla text eller tal som separeras av semikolon eller omvänt snedstreck. I Excel visas ett varningsmeddelande när du anger en formel, till exempel {1;2;A1:D4} eller {1;2;SUMMA(Q2:Z8)}. Numeriska värden kan inte innehålla procenttecken, dollartecken, punkter eller parenteser.

Namnge matriskonstanter

Ett av de bästa sätten att använda matriskonstanter är att namnge dem. Namngivna konstanter kan vara mycket enklare att använda och namnet kan dölja lite av komplexiteten med matrisformlerna för andra användare. Så här gör du när du vill namnge en matriskonstant och använda den i en formel:

  1. Klicka på Definiera namn i gruppen Definierade namn på fliken Formler.
    Dialogrutan Definiera namn visas.

  2. I rutan Namn skriver du Kvartal1.

  3. Ange följande konstant i rutan Refererar till (kom ihåg att skriva in klammerparenteserna manuellt):

    ={”Januari”,”Februari”,”Mars”}

    Innehållet i dialogrutan ser nu ut så här:

    Dialogrutan Redigera namn med formel

  4. Klicka på OK och markera en rad med tre tomma celler.

  5. Skriv in följande formel och tryck sedan på Ctrl+Skift+Retur.

    =Kvartal1

    Följande resultat visas.

    Namngiven matris som anges som formel

När du använder en namngiven konstant som en matrisformel ska du komma ihåg att ange likhetstecknet. Om du inte gör det tolkas matrisen som en textsträng och formeln fungerar inte som avsett. Kom också ihåg att du kan använda kombinationer av text och tal.

Felsöka matriskonstanter

Om matriskonstanterna inte fungerar kan du undersöka om följande problem har uppstått:

  • Vissa element kanske inte har avgränsats med rätt tecken. Om du utelämnar ett semikolon eller ett omvänt snedstreck, eller om du placerar ett tecken på fel ställe, kanske matriskonstanten inte skapas korrekt, eller så kan ett varningsmeddelande visas.

  • Du kan ha markerat ett cellområde som inte motsvarar antalet element i konstanten. Om du till exempel markerar en kolumn med sex celler som ska användas i en konstant med fem celler, visas felvärdet #SAKNAS i den tomma cellen. Omvänt gäller att om du markerar för få celler utelämnas de värden som saknar en motsvarande cell.

Matriskonstanter i praktiken

I exemplen nedan visas några sätt som du kan använda matriskonstanter i matrisformler. I vissa exempel används funktionen TRANSPONERA för att konvertera rader till kolumner och tvärtom.

Multiplicera alla element i en matris

  1. Skapa ett nytt kalkylblad och markera ett block med tomma celler som är fyra kolumner brett och tre rader högt.

  2. Skriv in följande formel och tryck sedan på Ctrl+Skift+Retur.

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Upphöja elementen i en matris i kvadrat

  1. Markera ett block med tomma celler som är fyra kolumner brett och tre rader högt.

  2. Skriv in följande matrisformel och tryck sedan på Ctrl+Skift+Retur.

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Du kan också ange den här matrisformeln som använder operatorn cirkumflex (^):

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Transponera en endimensionell rad

  1. Markera en kolumn med fem tomma celler.

  2. Skriv in följande formel och tryck sedan på Ctrl+Skift+Retur.

    =TRANSPONERA({1,2,3,4,5})

Även om du angav en vågrät matriskonstant konverteras matriskonstanten till en kolumn med funktionen TRANSPONERA.

Transponera en endimensionell kolumn

  1. Markera en rad med fem tomma celler.

  2. Skriv in följande formel och tryck sedan på Ctrl+Skift+Retur.

    =TRANSPONERA({1;2;3;4;5})

Även om du angav en lodrät matriskonstant konverteras konstanten till en rad med funktionen TRANSPONERA.

Transponera en tvådimensionell konstant

  1. Markera ett block med celler som är tre kolumner brett och fyra rader högt.

  2. Ange följande konstant och tryck sedan på Ctrl+Skift+Retur:

    =TRANSPONERA({1,2,3,4;5,6,7,8;9,10,11,12})

Med funktionen TRANSPONERA konverteras varje rad till en serie kolumner.

Överst på sidan

Börja använda grundläggande matrisformler

Det här avsnittet innehåller exempel på grundläggande matrisformler.

Skapa matriser och matriskonstanter från befintliga värden

I exemplet nedan får du veta hur du använder matrisformler för att skapa länkar mellan cellområden i olika kalkylblad. Du får också se hur du skapar en matriskonstant från samma uppsättning värden.

Skapa en matris från befintliga värden

  1. Markera cellerna C8:E10 i ett kalkylblad i Excel och ange den här formeln:

    ={10,20,30;40,50,60;70,80,90}

    Glöm inte att ange { (vänster klammerparentes) innan du anger 10 och } (höger klammerparentes) efter att du anger 90 eftersom du skapar en matris med tal.

  2. Tryck sedan på Ctrl+Skift+Retur. Kommandot anger den här matrisen med tal i cellområde C8:E10 med hjälp av en matrisformel.
    I kalkylbladet ska C8 till E10 se ut så här:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Markera cellområdet C1 till E3.

  4. Ange följande formel i formelfältet och tryck sedan på Ctrl+Skift+Retur:

    =C8:E10

    En 3x3-matris av celler visas i cellerna C1 till E3 med samma värden som visas i C8 till E10.

Skapa en matriskonstant från befintliga värden

  1. Växla till redigeringsläge genom att trycka på F2 när cellerna C1:C3 är markerade.
    Matrisformeln ska fortfarande vara = C8:E10.

  2. Tryck på F9 för att konvertera cellreferenserna till värden. I
    Excel konverteras värden till en matriskonstant. Formeln ska nu vara ={10;20;30\40;50;60\70;80;90}, precis som i C8:E10.

  3. Tryck på Ctrl+Skift+Retur för att ange matriskonstanten som en matrisformel.

Räkna tecken i ett cellområde

I följande exempel visas hur du räknar antalet tecken, inklusive mellanslag, i ett cellområde.

  1. Kopiera den här tabellen och klistra in den i cell A1 i ett kalkylblad.

  2. Markera cell A9 och tryck på Ctrl+Skift+Retur om du vill visa det totala antalet tecken i cellerna A2:A6 (66).

  3. Markera cell A12 och tryck på Ctrl+Skift+Retur om du vill visa innehållet i de längsta cellerna A2:A6 (cell A3).

Data

Det här är ett

antal celler som

samlas

för att bilda en

enda mening.

Totalt antal tecken i A2:A6

=SUMMA(LÄNGD(A2:A6))

Innehåll i längsta cell (A3)

=INDEX(A2:A6,MATCHA(MAX(LÄNGD(A2:A6)),LÄNGD(A2:A6),0),1)

Följande formel används i cell A9 och räknar det sammanlagda antalet tecken (66) i cellerna A2 till A6.

=SUMMA(LÄNGD(A2:A6))

I det här fallet returnerar funktionen LÄNGD längden på varje textsträng i var och en av cellerna i området. Sedan adderas dessa värden med funktionen SUMMA och resultatet (66) visas i cellen som innehåller formeln, A9.

Hitta de n lägsta värdena i ett område

I exemplet visas hur du hittar de tre lägsta värdena i ett cellområde.

  1. Markera cellerna A16 till A18.
    Den här gruppen celler kommer att innehålla resultatet som returneras av matrisformeln.

  2. Ange följande formel i formelfältet och tryck sedan på Ctrl+Skift+Retur:

    =MINSTA(A5:A14,{1;2;3})

Värdena 400, 475 och 500 visas i cell A16 till A18.

I den här formeln används en matriskonstant för att utvärdera funktionen MINSTA tre gånger och returnera det lägsta (1), det näst lägsta (2) och det tredje lägsta (3) talen i matrisen som omfattar cellerna A1:A10. Om du vill hitta fler värden lägger du till argument i konstanten och ett motsvarande antal resultatceller i området A12:A14. Du kan också använda ytterligare funktioner med den här formeln, till exempel SUMMA eller MEDEL. Till exempel:

=SUMMA(MINSTA(A 5 :A1 4 ,{1;2;3}))

=MEDEL(MINSTA(A 5 :A1 4 ,{1;2;3}))

Hitta de n högsta värdena i ett område

Om du vill hitta de högsta värdena i ett område kan du ersätta funktionen MINSTA med funktionen STÖRSTA. Dessutom används funktionerna RAD och INDIREKT i exemplet nedan.

  1. Markera cellerna A1 till A3.

  2. Ange den här formeln i formelfältet och tryck sedan på Ctrl+Skift+Retur:

    =STÖRSTA(A5:A14,RAD(INDIREKT("1:3")))

Värdena 3200, 2700 och 2000 visas i cell A1 till A3.

I det här läget kan det vara bra att känna till lite grann om funktionerna RAD och INDIREKT. Du kan använda funktionen RAD när du vill skapa en matris med upprepade heltal. Markera till exempel en tom kolumn med 10 celler i övningsarbetsboken, ange den här matrisformeln i cellerna A5:A14 och tryck sedan på Ctrl+Skift+Retur:

=RAD(1:10)

Med formeln skapas en kolumn med 10 upprepade heltal. Om du vill se ett problem som skulle kunna uppstå kan du infoga en rad ovanför området som innehåller matrisformeln (dvs. ovanför rad 1). Radreferenserna justeras och heltal från 2 till 11 genereras. Korrigera problemet genom att lägga till funktionen INDIREKT i formeln:

=RAD(INDIREKT("1:10"))

I funktionen INDIREKT används textsträngar som argument (vilket är anledningen till att området 1:10 omges av dubbla citattecken). Textvärden justeras inte när du infogar rader eller flyttar matrisformeln på något annat sätt. Resultatet blir att funktionen RAD alltid genererar den matris med heltal som du vill ha.

Vi tittar närmare på den formel som du använde tidigare – =STÖRSTA(A5:A14,RAD(INDIREKT("1:3"))) . Vi börjar från den innersta parentesen och jobbar oss utåt. Funktionen INDIREKT returnerar en uppsättning textvärden, i det här fallet värdena 1 till 3. Funktionen RAD genererar en matris med en trecellskolumn. I funktionen STÖRSTA används värdena i cellområdet A5:A14 och den utvärderas tre gånger: en gång för varje referens som returneras av funktionen RAD. Värdena 3200, 2700 och 2000 returneras till matrisen med trecellskolumnen. Om du vill hitta fler värden lägger du till ett större cellområde i funktionen INDIREKT.

Slutligen kan du använda den här formeln med andra funktioner, till exempel SUMMA och MEDEL.

Hitta den längsta textsträngen i ett cellområde

Formeln fungerar endast när ett dataområde innehåller en enda cellkolumn. Skriv in följande formel i cell A16 på Blad3, och tryck på Ctrl+Skift+Retur:

=INDEX(A6:A9,PASSA(MAX(LÄNGD(A6:A9)),LÄNGD(A6:A9),0),1)

Texten "bunch of cells that" visas i cell A16.

Vi tittar närmare på formeln. Vi börjar med de innersta elementen och jobbar oss utåt. Funktionen LÄNGD returnerar längden på varje element i cellområdet A6:A9. Med funktionen MAX beräknas det högsta värdet bland elementen, vilket motsvarar den längsta textsträngen, som finns i cell A7.

Det är nu det börjar bli komplicerat. Med funktionen PASSA beräknas adressen (den relativa positionen) för den cell som innehåller den längsta textsträngen. För detta behövs tre argument: ett sökvärde, en sökmatris och en passningstyp. Funktionen PASSA söker efter sökvärdet i sökmatrisen. I det här fallet är sökvärdet den längsta textsträngen:

(MAX(LÄNGD( A6 : A9 ))

och den strängen finns i den här matrisen:

LÄNGD( A6:A9 )

Argumentet för passningstypen är 0. Passningstypen kan bestå av värdet 1, 0 eller -1. Om du anger 1 returnerar PASSA det högsta värdet som är lägre än eller lika med sökvärdet. Om du anger 0 returnerar PASSA det första värdet som är exakt lika med sökvärdet. Om du anger -1 returnerar PASSA det lägsta värdet som är högre än eller lika med det angivna sökvärdet. Om du utelämnar passningstypen används 1 som standard.

Slutligen används de här argumenten av funktionen INDEX: en matris samt ett värde som anger rad och kolumn inom den matrisen. Cellområdet A6:A9 står för matrisen, funktionen PASSA står för celladressen och det sista argumentet (1) anger att värdet kommer från den första kolumnen i matrisen.

Överst på sidan

Börja använda avancerade matrisformler

Det här avsnittet innehåller exempel på avancerade matrisformler.

Summera ett område som innehåller felvärden

Funktionen SUMMA i Excel fungerar inte när du försöker summera ett område som innehåller ett felvärde, till exempel #N/A. I det här exemplet får du veta hur du summerar värdena i ett område med namnet Data som innehåller fel.

=SUMMA(OM(ÄRFEL(Data),"",Data))

Formeln skapar en ny matris som innehåller de ursprungliga värdena minus eventuella felvärden. Om vi börjar från de innersta funktionerna och jobbar oss utåt söker funktionen ÄRFEL efter fel i cellområdet (Data). Funktionen OM returnerar ett visst värde om ett villkor som du anger valideras som SANT och ett annat värde om det valideras till FALSKT. I det här fallet returneras tomma strängar ("") för alla felvärden eftersom de valideras till SANT, och de återstående värdena från området (Data) returneras, eftersom de valideras till FALSKT, vilket innebär att de inte innehåller felvärden. Funktionen SUMMA räknar sedan ut totalsumman för den filtrerade matrisen.

Räkna antalet felvärden i ett område

Det här exemplet påminner om formeln ovan, men den returnerar antalet felvärden i ett område med namnet Data i stället för att filtrera ut dem:

=SUMMA(OM(ÄRFEL(Data),1,0))

Den här formeln skapar en matris som innehåller värdet 1 för de celler som innehåller fel och värdet 0 för celler som inte innehåller fel. Du kan förenkla formeln och få samma resultat genom att ta bort det tredje argumentet till funktionen OM, på det här sättet:

=SUMMA(OM(ÄRFEL(Data),1))

Om du inte anger argumentet returnerar funktionen IF värdet FALSKT om en cell inte innehåller ett felaktigt värde. Du kan förenkla formeln ännu mer:

=SUMMA(OM(ÄRFEL(Data)*1))

Den här varianten fungerar eftersom SANT*1=1 och FALSKT*1=0.

Summera värden baserat på villkor

I vissa situationer kan du behöva summera värden baserat på villkor. I den här matrisformeln summeras till exempel enbart positiva heltal i ett område med namnet Försäljning:

=SUMMA(OM(Försäljning>0,Försäljning))

Funktionen OM skapar en matris med positiva värden och falska värden. Funktionen SUMMA ignorerar praktiskt taget de falska värdena eftersom 0+0=0. Cellområdet som du använder i den här formeln kan bestå av vilket antal rader och kolumner som helst.

Du kan också summera värden som uppfyller fler än ett villkor. I den här matrisformeln beräknas till exempel värden som är högre än 0 och lägre än eller lika med 5:

=SUMMA((Försäljning>0)*(Försäljning<=5)*(Försäljning))

Kom ihåg att den här formeln returnerar ett fel om området innehåller en eller flera celler som inte innehåller tal.

Du kan också skapa matrisformler som använder ett slags ELLER-villkor. Du kan till exempel summera värden som är lägre än 5 och högre än 15:

=SUMMA(OM((Försäljning<5)+(Försäljning>15),Försäljning))

Funktionen OM hittar alla värden som är lägre än 5 och högre än 15 och lämnar sedan vidare dessa värden till funktionen SUMMA.

Det går inte att använda funktionerna OCHoch ELLER direkt i matrisformler eftersom dessa funktioner returnerar ett enda resultat, antingen SANT eller FALSKT, och matrisfunktionerna kräver resultatmatriser. Du kan lösa problemet genom att använda den logik som visas i den föregående formeln. Med andra ord: du utför matematiska operationer, till exempel addition eller multiplikation, på värden som uppfyller villkoret ELLER eller OCH.

Beräkna ett genomsnitt som utesluter nollvärden

I det här exemplet beskrivs hur du tar bort nollvärden från ett område när du behöver räkna ut ett genomsnitt för värdena i det området. I formeln används ett dataområde med namnet Försäljning:

=MEDEL(OM(Försäljning<>0,Försäljning))

Funktionen OM skapa en matris med värden som inte är lika med 0 och lämnar sedan dessa värden vidare till funktionen MEDEL.

Beräkna antalet skillnader mellan två cellområden

I den här matrisformeln jämförs värdena i två cellområden med namnen Minadata och Dinadata och antalet skillnader mellan de två returneras. Om innehållet i de två områdena är identiskt returnerar formeln 0. Om du vill använda den här formeln måste cellområdena ha samma storlek och samma dimension (om Minadata till exempel är ett område med 3 rader och 5 kolumner måste Dinadata också bestå av 3 rader och 5 kolumner):

=SUMMA(OM( Minadata =Dinadata,0,1))

Formeln skapar en ny matris i samma storlek som de områden som du jämför. Funktionen OM fyller i matrisen med värdet 0 och värdet 1 (0 för felaktiga matchningar och 1 för identiska celler). Funktionen SUMMA returnerar summan av värdena i matrisen.

Du kan förenkla formeln så här:

=SUMMA(1*( Minadata <> Dinadata ))

Precis som formeln som räknar felvärden i ett område, fungerar den här formeln eftersom SANT*1=1 och FALSKT*1=0.

Hitta platsen för det högsta värdet i ett område

Den här matrisformeln returnerar radnumret för det högsta värdet i ett enkolumnsområde med namnet Data:

=MIN(OM(Data=MAX(Data);RAD(Data);""))

Funktionen OM skapar en ny matris som motsvarar området Data. Om en motsvarande cell innehåller det högsta värdet i området innehåller matrisen radnumret. I annat fall innehåller matrisen en tom sträng (""). Funktionen MIN använder den nya matrisen som sitt andra argument och returnerar det lägsta värdet, som motsvarar radnumret för det högsta värdet i området Data. Om området Data innehåller identiska högsta värden returnerar formeln raden för det första värdet.

Om du vill returnera den faktiska celladressen för ett maxvärde kan du använda den här formeln:

=ADRESS(MIN(OM(Data=MAX(Data);RAD(Data);""));KOLUMN(Data))

Överst på sidan

Behöver du mer hjälp?

Du kan alltid fråga en expert i Excel Tech Community, få support i Answers-communityn eller föreslå en ny funktion eller förbättringar på Excel User Voice.

Obs!: Ansvarsfriskrivning för maskinöversättning: Den här artikeln har översatts av ett datorsystem utan mänsklig inblandning. Microsoft erbjuder dessa maskinöversättningar för att hjälpa icke engelskspråkiga användare att ta del av information om Microsofts produkter, tjänster och tekniker. Eftersom artikeln är maskinöversatt kan den innehålla fel i ordval, syntax och grammatik.

Mer information finns i

Översikt över formler

Utöka dina kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×