Riktlinjer för och exempel på matrisformler

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

Kopiera hela tabellen nedan och klistra in den i cell A1 i ett tomt kalkylblad.

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)

1. Om du vill se den totala försäljningen av kupé och sedaner för varje säljare markerar du cellerna E2:E11, anger formeln =C2:C11*D2:D11och trycker sedan på Ctrl+Skift+Retur.

2. 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.

När du trycker på Ctrl+Skift+Returomges formeln av 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}. 

• Skapa en encellsmatrisformel

Skriv in följande formel i cell D13, 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 D13 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 om du vill bekräfta en ändring i 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.

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 kan vara ett 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).

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 matrisformel markerar du hela formelområdet (till exempel E2:E11), och trycker sedan på Delete.

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

Ibland kan du behöva utöka en matrisformel. Markera den första cellen i det befintliga matrisområdet och fortsätt tills du har markerat hela området som du vill utöka formeln till. Tryck på F2 för att redigera formeln och tryck sedan på CTRL + SKIFT + RETUR för att bekräfta formeln när du har justerat formelområdet. Nyckeln är att markera hela området, med början i den översta vänstra cellen i matrisen. Cellen längst upp till vänster är den som redigeras.

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

• Ibland glömmer du att trycka på Ctrl + Skift + Retur. Det kan hända även till de mest erfarna Excel-användarna. Kom ihåg att trycka på den här tangentkombinationen när du anger eller redigerar 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.

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 5, 6, 7, och den andra raden innehåller 8 och 4. 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.

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. Markera cell A1 till E1 i ett tomt kalkylblad.

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

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

   I det här fallet ska du skriva inledande och avslutande klammerparenteser ({ }), så lägger Excel till den andra uppsättningen åt dig.

   Följande resultat visas.

   

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.

   

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.

   

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.

   

   Värdet 85 visas i cell A3.

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

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

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+Returr 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+Returr. Du kan se samma resultat som du gjorde i den tidigare övningen med matrisformeln:

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

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.

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:

   

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.

   

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.

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.

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.

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 påCtrl+Skift+Retur, som anger den här matrisen med tal i cellområdet C8:E10 med hjälp av en matrisformel. I kalkylbladet bör 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. 

2. Tryck F9 om du vill konvertera cellreferenserna till värden. Excel konverterar värdena till en matriskonstant. Formeln ska nu vara ={10\20\30;40\50\60;70\80\90}.

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.

   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)

2. Markera cell A8 och tryck sedan på Ctrl+Skift+Retur för att se det totala antalet tecken i cellerna A2:A6 (66).

3. Markera cell A10 och tryck sedan på Ctrl+Skift+Retur för att se innehållet i den längsta av cellerna A2:A6 (cell A3).

Följande formel används i cell A8 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.

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. Ange vissa slumptal i cellerna A1:A11.

2. Markera cellerna C1 till och med C3. Den här uppsättningen celler innehåller de resultat som returneras av matrisformeln.

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

   =SMALL(A1:A11,{1;2;3})

Den här formeln använder en matriskonstant för att utvärdera funktionen SMALL tre gånger och returnera det minsta (1), sekunds minsta (2) och tredje minsta (3) medlemmar i matrisen som finns i cellerna A1:A10 Om du vill hitta fler värden lägger du till fler argument till konstanten. Du kan också använda ytterligare funktioner med den här formeln, till exempel SUMMA eller MEDEL. Till exempel:

=SUMMA(LITEN(A1:A10,{1\2\3})

=MEDEL(LITEN(A1:A10,{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 D1 till och med D3.

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

   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

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. Du kan till exempel markera en tom kolumn med 10 celler i arbetsboken med övningen, ange den här matrisformeln och sedan trycka på Ctrl+Skift+Retur:

=RAD(1:10)

Formeln skapar en kolumn med tio heltal i följd. Om du vill se ett potentiellt problem infogar du en rad ovanför området som innehåller matrisformeln (d.v.s. ovanför rad 1). Radreferenserna justeras i Excel och formeln genererar heltal från 2 till 11. Du kan åtgärda 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 tar en titt på formeln som du använde tidigare – =STÖRSTA(A5:A14;RAD(INDIREKT("1:3"))) – med början från den inre parentesen och arbetar utåt: Funktionen INDIREKT returnerar en uppsättning textvärden, i det här fallet värdena 1 till 3. Funktionen RAD genererar i sin tur en kolumnmatris med tre celler. Funktionen STOR använder värdena i cellområdet A5:A14 och utvärderas tre gånger, en gång för varje referens som returneras av funktionen RAD. The values 3200, 2700, and 2000 are returned to the three-cell columnar array. If you want to find more values, you add a greater cell range to the INDIRECT function.

Som med tidigare exemplar, använda den här formeln med andra funktioner, till exempel SUMMA och MEDEL.

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

Gå tillbaka till det tidigare exemplet med textsträngar, ange följande formel i en tom cell och tryck på Ctrl+Skift+Retur:

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

Texten "flera celler som" visas.

Vi tittar närmare på formeln, med början från inre element och arbetar utåt. Funktionen LÄNGD returnerar längden på varje post i cellområdet A2:A6. Funktionen MAX beräknar det största värdet bland dessa element, som motsvarar den längsta textsträngen, som finns i cell A3.

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(A2:A6))

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

LÄNGD(A2:A6)

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 A2:A6 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.

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))