Matriser
Matrisformler och funktioner är kraftfulla verktyg, styrkan ligger i att de utför flera
beräkningar samtidigt. Cellområden med värden eller formler utgör vanligtvis en matris och
som XL behandlar som en grupp. Resultatet av matrisberäkningar kan visas antingen i nya
matriser, dvs i nya cellområden, eller i enstaka celler.
I tabellen nedan visas ett enklare exempel.
I exemplet så multipliceras cellområdena B2:B4 och C2:C4 med varandra och resultatet
visas i en ny matris. XL hämtar här data från en två-dimensionell matris ( tre rader och två
kolumner) och visar resultatet i en endimensionell matris (en kolumn) med 3 celler
(E2:E4).
För att skapa matrisformler måste följande steg vidtas:
- Markera det cellområde som matrisformeln ska skapas i.
- Skriv in formeln i den aktiva cellen.
- Avsluta inmatningen genom att trycka på CTRL + Skift + Retur
För att revidera matrisformler måste följande steg vidtas:
- Markera hela cellområdet som innehåller matrisformeln.
- Revidera formeln.
- Avsluta inmatningen genom att trycka på CTRL + Skift + Retur.
Matriskonstanter
Istället för att använda sig av cellreferenser kan man ange konstanter direkt i en matrisformel. Matriskonstanter måste inneslutas med klamrar, som man manuellt matar in. De yttre klamrarna skapas genom kommandot Ctrl + Skift + Retur. Konstanter avskiljs m h a semikolon (;) och m h a det s k "pipe-tecknet" (|), dvs ett vertikalt streck. Semikolon visar konstanterna i separata kolumner och "pipe-tecknen" separerar konstanterna från varandra radvis. I tabellen nedan visas exempel på detta.
För att denna formel ska fungera i version 2000 behövs pipe"-tecknet ersättas med "backslash"-tecknet.
Ps: Man kan också namnge matriskonstanter!
Mest & minst frekventa värde
Vi kan också m h a matrisformler få fram vilket värde som förekommer mest alternativt
minst i en lista. Tabellen nedan demonstrerar ett exempel på det.
Här används INDEX- och PASSA-funktionerna, vilka beskrivs mer under
"Sökning i osorterad tabell" och
"Två-dimensionell uppslagstabell". Funktionen ANTAL.OM
används här på ett litet annorlunda sätt, dvs antal förekomster av alla värden i listan . Detta
är "nyckeln" till lösningen då den genererar underlaget för beräkning.
För att erhålla det minst frekventa värdet i en lista så ersätts MAX-funktionen med
MIN-funktionen.
Vill man bara erhålla det mest frekventa värde kan ovanstående formel reduceras avsevärt.
- =TYPVÄRDE(A2:A6) ger resultatet 4
Räkna förekomster i rad och kolumn
I hur många rader eller kolumner förekommer ett visst värde? Svaret på den frågan kan FREKVENS-funktionen i kombination med RAD- och KOLUMN-funktionen svara på.
Utifrån exemplet nedan ska vi räkna fram i hur många rader värdet 1 förekommer i.
För att få fram antal rader används följande matrisformel:
- {=SUMMA(OM(FREKVENS(OM(A1:C4=F2;RAD(A1:C4));OM(A1:C4=F2;RAD(A1:C4)))>0;1;0))}
Cellen F2 innehåller värdet 1. Vill man istället få fram antal kolumner värdet förekommer i måste RAD-funktionen i ovanstående formel ersättas med KOLUMN-funktionen.
Hitta det x:e största & minsta värde
Behöver du visuellt se vilket värde i en lista som är t ex det näst största? I tabellen nedan
demonstreras den formeltekniska lösningen på frågan. Vilken rangordningsnummer som
ska visas styrs av det sista argumentet, nedan utgörs den av värdet" 2".
Vill man istället få fram det näst minsta värdet i listan så låter man helt enkelt STÖRSTA-
och MINSTA-funktionen byta plats med varandra i formeln!
Se också:
- Hitta högsta/lägsta intervallvärde
- Summering av högsta/lägsta värdena
- Högsta och lägsta värdet i en tabell
Matrissummering
Matrissummering, såsom den visas i nedanstående tabell, förutsätter att man använder sig av namn för cellområden. Annars fungerar helt enkelt inte formeln!
I ovanstående tabell utvärderas först ett villkor. I villkoret används sammanfogningstecknet,
vilket möjliggör att summera utifrån två kriterium (Avdelning och Produktnummer). Båda
villkoren "Avdelning" och "Produktnummer" måste vara "SANT" för att beloppet ifråga ska
läggas till. Slutprodukten är en summering av de belopp (Belopp) där båda kriterierna
uppfylls i formelmatrisen.
Matrissummering kan också ske utifrån villkor. Anta att summering ska ske först när första
värdet i listan är inmatat, obeaktande av andra inmatningar i listan, hur gör man då?
I tabellen nedan visas ett matrisexempel. Givet att första värdet i listan är inmatat sker en
summering av intervallet, om inte så sker ingen beräkning alls!
Månad- och Veckosummeringar
En vanlig förekommande aktivitet är att göra månatliga sammanställningar över ekonomiska flöden. I tabellen nedan visas en lösning på det.
För att denna formel ska fungerar måste man först skapa den i en cell och därefter kopiera den till ytterligare 11 celler, dvs 12 månader måste skapas! Kriteriet för att summering av celler ska ske är att datumcellernas månadsvärde (t ex 5) överensstämmer med radnumret (t ex 5). Se också Villkorsstyrd summering och Räkna med logiska operatorer.
En bättre lösning är att använda sig av följande kortare formel:
- {=SUMMA((MÅNAD(A2:A5)=5)*(B1:B5))}
Vill man inte använda sig av en matrisformel kan man lösa det m h a
följande formel:
- =PRODUKTSUMMA(N(MÅNAD(A2:A5)=5);B1:B5)
Konstanten i formeln kan ersättas med en cellreferens.
En annat problem är löpande summeringar, t ex årsvis eller månatligen.
I tabellen nedan visas förutsättningarna och utfallet.
Matrisformeln skapas i cellen C2 och kopieras vidare i kolumnen. Den
formeltekniska lösningen ser ut på följande sätt:
- {=SUMMA(N(MÅNAD(A2)<>MÅNAD(N(A3)))*N(MÅNAD(A2)=MÅNAD($A$2:$A$9))*$B$2:$B$9)}
Cellerna i C-kolumnen är villkorsstyrda formaterade och där värdet 0 ger
vit teckenfärg.
En förutsättning är att listan är sorterad efter datum - se tabell ovan.
MÅNAD-funktionen kan ersättas med ÅR-funktionen.
Antag att du vill göra summeringar per vecka istället för per månad. Här skulle man kunna tro att VECKONR-funktionen skulle ingå i en lösning men den fungerar inte i matrissammanhang.
Nedan visas ett utdrag ur en daglig kassabok.
För att erhålla summa per vecka krävs följande matrisformel:
- { =SUMMA((HELTAL((Datum-$A$1)/7+1)=D2)*Utfall)}
Där namnet "Datum" refererar till A-kolumnen och "Utfall" till B-kolumnen. Cellreferensen D2 refererar till önskat veckonummer.
Se också Årssummeringar och Månadsstatistik.
Matrismedelvärde
Även m h a matristekniken kan medelvärde beräknas. Den kan vara mer rättfram än
SUMMA.OM-funktionen i detta sammanhang.
I tabellen nedan visas ett exempel på matrisformeln som genererar resultatet i en cell.
I exemplet reduceras intäkterna med kostnaderna per månad. Därefter sker en beräkning
av periodens genomsnittliga månatliga resultat.
I tabellen nedan beräknas medelvärdet för tal som är större än 10.
I exemplet utvärderar OM-funktionen om värdena i intervallet A2:A5 är större än 10 eller inte. Om SANT så returneras värdet och ingår i beräkningen av medelvärdet.
Se också Villkorsstyrt medelvärde och Medelvärde.