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

 
  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.

Intervallsummering
I vissa sammanhang vill man summera intervall, dvs summering ska ske efter viss grupptillhörighet. I nedanstående tabell visas ett exempel där summering sker efter åldersgrupp.

För varje grupp räknas antal förekomster fram i tre steg. Först måste villkoren vara uppfyllda, dvs värdet måsta vara inom intervallet t ex 21-25 år. Därefter sker en multiplikation mellan utfallen av respektive villkor och slutligen sker en summering. Utfallet blir antal förekomster per grupp. För att erhålla total poäng per grupp sker ytterligare en multiplikation (med Poänggraden) innan summering sker.

Alternativt kan följande formler användas:

  • Antal: =PRODUKTSUMMA((A2:A6>=21)*(A2:A6<=25))
     
  • Summa: =PRODUKTSUMMA((A2:A6>=21)*(A2:A6<=25)*B2:B6)

Antag istället att det är bara en kolumn med data och som man vill gruppera, t ex 0 - 24, 25-74 och 75 - 100. Följande formler löser problemet:

0 - 24

  • {=SUMMA(ANTAL.OM(Cellområde;{">=0";">24"})*{1;-1})}

  • =PRODUKTSUMMA((Cellområde>=0)*(Cellområde<=24)) 

25 - 74

  • {=SUMMA(ANTAL.OM(Cellområde;{">=25";">74"})*{1;-1})}

  • =PRODUKTSUMMA((Cellområde>=25)*(Cellområde<=74)) 

75 - 100

  • =ANTAL.OM(Cellområde;">=75")

  • =PRODUKTSUMMA(N(Cellområde>=75))

Beräkna vartannat, vart tredje tal...
Hm, hur ska man kunna t e x summera vartannat tal i en lista? En lösning är att använda ett flertal funktioner i kombination med varandra. Tabellen nedan visar exempel på det, summering av vartannat tal.

För att förstå matrisformeln bör vi börja med att studera villkorets innehåll. RAD-funktionen genererar radnumrerna för cellområdet, dvs {2|3|4|5|6}. Dessa tal divideras med den s k divisorn (2) i REST-funktionen. REST-funktionen genererar serien {0|1|0|1|0}, vilken utvärderas i villkoret. De rader som uppfyller påståendet (=0) summeras följaktligen.

För att beräkna t ex vart tredje eller vart fjärde tal anges divisorn till 3 eller 4. Vill man beräkna t ex vartannat tal med start från det andra talet så blir villkoret =1. Andra beräkningar kan också ske t ex kolumnvis (KOLUMN-funktionen) och t ex m h a MEDEL-funktionen mfl - Pröva!

Räkna antal tecken i en lista
Om man ska beräkna antal tecken i enstaka celler kan man använda sig av LÄNGD-funktionen men hur beräkna antal tecken i t ex en lista? M h a en matrisformel! I tabellen nedan visas ett exempel på detta. LÄNGD-funktionen räknar ut antal tecken (såväl numeriska som alfanumeriska tecken) per cell och som sedan summeras ihop.

Flera matrisvillkor
För att räkna antal celler med tal mellan 25 och 75 kan en matrisformel användas. Exemplet i tabellen nedan visar en lösning på problemet.

Vill man få fram antal rader som uppfyller två villkor ur två listor i två separata kolumner (se "Antal rader 1" i exemplet ovan) så kan det också ske m h a en matrisformel. Noterbart är att båda villkoren måste vara uppfyllda för att en rad ska räknas. Om det bara krävs att ett av de två angivna villkoren uppfylls kan det lösas m h a formeln för "Antal rader 2" i exemplet ovan.

Ett alternativ sätt att lösa behovet av flera matrisvillkor är att använda sig av N-funktionen. I tabellen nedan visas ett exempel.

Först sker en utvärdering av villkoren. Om någon rad uppfyller villkoren returneras värdet SANT. Detta värde omvandlas m h a N-Funktionen till ett tal, i exemplet talet 1. Därefter sker en summering av talen.

Hitta högsta/lägsta intervallvärde
Här demonstreras ytterligare tekniker för att få fram det högsta respektive lägsta värdet i en datamängd. Här med det kravet att värdet ska ligga in ett visst intervall.

Villkoren ska i exemplet utläsas som >=50 och <=125.

För att erhålla hösta värdet kan en av följande två lösningar användas:

  • {=MAX(OM((A2:A8>=B2)*(A2:A8<=B3);A2:A8;""))}
     
  • {=STÖRSTA(OM((A2:A8>=B2)*(A2:A8<=B3);A2:A8;"");1)}

Vill man erhålla det lägsta värdet ersätts MAX-funktionen av MIN-funktionen.

Summering av högsta/lägsta värdena
Hur ska man göra om man t ex vill summera de tre högsta värdena i en data- mängd? M h a en matrisformel bestående av funktionerna SUMMA och STÖRSTA. I exemplet nedan visas den tekniska lösningen.

RAD(INDIREKT("1:3")) i formeln ger de tre största n-värdena i STÖRSTA- funktionen. För att erhålla summan av de tre lägsta värdena ersätts STÖRSTA- funktionen med MINSTA.

Alternativt kan man använda sig av följande formel för att erhålla summan av de tre högsta värdena:

  • =SUMMA(STÖRSTA(A2:A6;{1|2|3}))

Här används en konstantmatris för att ge de tre högsta värdena, vilken kan expanderas / minskas ytterligare. Samma lösning kan självklart användas för summering av de lägsta värdena. Denna lösning är pedagogiskt sett bättre än ovanstående.

Allt eftersom tiden går så uppdagas bättre alternativ, dvs formler som ger samma resultat men har mindre antal tecken. De ovan nämnda formlerna fungerar alldeles utmärkt men jag vill ändå visa på följande formler:

  • {=SUMMA(STÖRSTA(A2:A6;RAD(1:3)))} eller
     
  • {=SUMMA(A2:A6)-SUMMA(MINSTA(A2:A6;RAD(1:2)))} för att summera de tre största talen i listan.

Vill man inte använda sig av matrisformler kan följande formel användas:

  • =PRODUKTSUMMA(STÖRSTA(A2:A6;{1;2;3}))

För att summera de tre minsta talen ersätt STÖRSTA med MINSTA och vice versa i formlerna ovan.

Hitta ifyllda / tomma celladresser
Hm, det kan ibland vara av intresse att få fram celladresser som är tomma alternativt ifyllda. Uppgiften kan sedermera ligga till grund för andra kalkyler mm. Hur löser man det m h a kalkylfunktioner? Vi får till att börja med skilja på numerisk (talvärden) och alfanumerisk data (textvärden). Exemplena har sin utgångspunkt i numeriska datamängder.

I det första exemplet demonstreras hur man kan identifiera den första ifyllda celladressen och den första tomma celladressen i ett cellområde. M h a matrisformler innehållande ett flertal funktioner kan dessa celladresser identifieras. Det föreligger här en omvänd logik, för att hitta den första cellen med innehåll används funktionen ÄRTOM och för att hitta den första tomma cellen används ÄRTAL. Båda dessa informationsfunktioner returnerar sant eller falskt och ger data för evaluering. Multiplikation med 1 sker för att konvertera text till tal.

Alternativ kan följande matrisformel användas för att hitta första ifyllda cell i raden:

  • {=ADRESS(1;MIN(OM(1:1<>"";KOLUMN(1:256);""));4)}

För att hitta sista ifyllda celladress i raden kan följande formel användas:

  • {=ADRESS(1;MAX(OM(1:1<>"";KOLUMN(1:256);""));4)}

Vill du identifiera den första ifyllda/tomma cellen i en kolumn måste argumenten spegelvändas för ADRESS-funktionen, vilket visas i tabellen nedan.

N-funktionen kan ersätta multiplikationen med 1 enligt följande:

  • {=ADRESS(RAD($A$1);PASSA(0;N(ÄRTOM($A$1:$F$1));))}

Alternativ kan följande formel användas för att hitta första ifyllda cell i kolumnen:

  • {=ADRESS(MIN(OM(A1:A5<>"";RAD(A1:A5);""));KOLUMN(A1:A5);4)}

För att hitta sista ifyllda celladressen i kolumnen kan följande formel användas:

  • {=ADRESS(MAX(OM(A1:A5<>"";RAD(A1:A5);""));KOLUMN(A1:A5);4)}

Arbetar man med alfanumerisk (textvärden) data måste funktionen ÄRTAL ersättas med ÄRTEXT.

Vill man t ex utföra en summering men där man inte kan använda SUMMA-funktionen direkt kan man ta hjälp av ovanstående enligt följande:

  • {=SUMMA(INDIREKT(ADRESS(MIN(OM(A1:A4<>"";RAD(A1:A4)));
    KOLUMN(A1:A4);4)&":"&ADRESS(MAX((A1:A4<>"")*RAD(A1:A4));
    KOLUMN(A1:A4);4)))}

Rad & celladress med max-värdet?
I vissa sammanhang kan det vara en fördel att få reda på vilken rad eller vilken celladress som innehåller maxvärdet i en datamängd.

Här demonstreras två tekniker för att lösa uppgiften ifråga. Tabellen nedan innehåller datamängden.

För att erhålla radnumret som håller maxvärdet används formeln:

  • =PASSA(MAX(A2:A4);A2:A4;0)

För att erhålla celladressen krävs en mer komplicerat formel enligt följande:

  • {=ADRESS(MAX(((A2:A4)=(MAX(A2:A4)))*RAD(A2:A4));
    MAX(((A2:A4)=(MAX(A2:A4)))*KOLUMN(A2:A4));4)}

Två "enklare" matrisformler, där MIN kan ersätta MAX-funktionen är:

  • {=ADRESS(MIN(OM(MIN(A1:A4)=A1:A4;RAD(A1:A4);""));1)}
     
  • {=ADRESS(MIN(OM(MIN(A1:A4)=A1:A4;RAD(A1:A4);""));1;4)}

Den förstnämnda formel genererar en absolut cellreferens medan den andra relativ referens.

Det går också att erhålla celladressen mha av två enklare icke-matrisformler:

  • =CELL("adress";FÖRSKJUTNING(A2;PASSA(MAX
    (A2:A4);A2:A4;FALSKT)-1;0))
     
  • =ADRESS(RAD(A2)+PASSA(MAX(A2:A4);
    A2:A4;FALSKT)-1;KOLUMN(A2))

Båda formlerna ger absoluta cellreferens - $A$3

Hitta celladressen!
Här demonstreras en teknik för att identifiera den första cellen som innehåller ett visst värde.

Tabellen nedan visar förutsättningarna:

Formeltekniskt sätt bygger lösningen på en matrisformel enligt följande:

  • {=ADRESS(MIN(OM(A2:A6=B2;RAD(A2:A6);""));MIN(OM
    (A2:A6=B2;KOLUMN(A:A);"")))}

Vill vi erhålla flera celladresser, som har värdet, kan man kopiera formeln nedåt i kolumnen och vill man samtidigt meddela att ett värde saknas kan vi använda oss av följande formel:

  • =OM(ÄRFEL(ADRESS(MINSTA(OM($A$2:$A$6=$B$2;RAD
    ($A$2:$A$6);"");RAD(1:1));KOLUMN(A:A))); "Saknas";ADRESS
    (MINSTA(OM($A$2:$A$6=$B$2;RAD($A$2:$A$6);"");RAD(1:1));
    KOLUMN(A:A)))

Vill man erhålla den sista celladressen i listan som innehåller det önskade värdet ersätts MIN-funktionen med MAX-funktionen enligt följande:

  • {=ADRESS(MAX(OM(A2:A6=B2;RAD(A2:A6);""));MAX(OM
    (A2:A6=B2;KOLUMN(A:A);"")))}

Hitta cellområdet
Här demonstreras en formelteknik för att identifiera i vilket cellintervall ett visst värde ligger inom.

En förutsättning är att listan är sorterad, vilket visas i bilden nedan:

För att få fram startcellen i en sorterad lista används följande formel:

  • {=ADRESS(MIN(OM(ICKE(ÄRFEL(HITTA(B2;A2:A7)));RAD(A2:A7)));1)}

För att få fram slutcellen används följande formel:

  • {=ADRESS(MAX(OM(ICKE(ÄRFEL(HITTA(B2;A2:A7)));RAD(A2:A7)));1)}

För att erhålla cellintervallet i en och samma formel kombineras dessa formler enligt följande:

  • {=ADRESS(MIN(OM(ICKE(ÄRFEL(HITTA(B2;A2:A7)));RAD
    (A2:A7)));1)&":" &ADRESS(MAX(OM(ICKE(ÄRFEL(HITTA
    (B2;A2:A7)));RAD(A2:A7)));1)}

Hitta sista värdet i kolumnen/raden
Hm, vilket är det sista ifyllda värdet i en tabell? Jo, svaret kan man erhålla genom att använda sig av en matrisformel. I tabellen nedan visas ett exempel på det. M h a ADRESS-funktionens resultat kan man erhålla det önskade värdet genom att INDIREKT referera till celladressen.

Ett annat alternativ är:

  • {=INDEX(A:A;MAX((LÄNGD(A1:A1000)>0)*RAD(A1:A1000)))}

En ännu kortare formel för att hitta senast inmatade värde är:

  • {=INDEX(A:A;MAX(RAD(2:6)*(A2:A6>0)))}

Alternativ kan vi också använda oss av:

  • {=FÖRSKJUTNING(A1;MAX((A1:A6<>"")*RAD(A1:A6))-1;0)}
     
  • {=INDEX(A1:A6;MAX((A1:A6<>"")*RAD(A1:A6)))}

Vill vi också erhålla det näst senaste inmatade revideras ovanstående formel med:

  • {=INDEX(A:A;MAX(RAD(2:6)*(A2:A6>0)-1))}

Om man vill hämta det sista värdet från en rad får man "spegelvända" funktionen enligt följande:

  • {=INDIREKT(ADRESS(RAD(9:9);MAX((KOLUMN(A:D)*
    (A9:D9<>"")))))}

Eller med en kortare formel:

  • {=INDEX(1:1;MAX(KOLUMN(A:D)*(A1:D1>0)))}

Vill man inte använda sig av matrisformler så finns det alternativ till att lösa problemet!

Hitta sista värdet i kolumnen:

  • =FÖRSKJUTNING(A1;PASSA(MAX(A1:A7)+1;A1:A7)-1;0)

Hitta sista värdet i raden:

  • =FÖRSKJUTNING(A1;0;PASSA(MAX(A1:G1)+1;A1:G1;1)-1)

Har man en lista i en kolumn och som inte innehåller tomma celler kan det sist inmatade värdet erhållas genom följande (enkla) formel:

  • =INDEX(A:A;ANTALV(A:A))

Det sista inmatade värdet i en rad får man genom att ersätta kolumnangivelsen i formeln med radnumret:

  • =INDEX(9:9;ANTALV(9:9))
Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin