startsida < företaget < organisera < datorer < tips
 
Matriskalkylering I

 
  Kalkylering
Kalkylering I
Kalkylering II
Kalkylering III
Kalkylering IV
Kalkylering V
Kalkylering VI
Kalkylering VII
Villkorsstyrd
kalkylering
Villkorsstyrd
formatering I
Villkorsstyrd
formatering II
Felhanterings-
kalkylering
Matriskalkylering I
Matriskalkylering II
Matriskalkylering III
Matriskalkylering IV
Matriskalkylering V
Datumkalkylering I
Datumkalkylering II
Datumkalkylering III
Datavalidering I
Datavalidering II
Cirkelreferenser
   
  Innehåll
expowera

 

Materialet publicerat med tillstånd från Dennis Wallentin.

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

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 samman- fogningstecknet, 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).

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.

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 medel- värdet.
 
Upp

Senast uppdaterad: 2005-10-05 © Dennis Wallentin