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

 
  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.

Celladresser till de största/minsta värdena
Här visas en relativ komplex matrisformel för att erhålla celladresserna till de största/minsta värdena i en större datamängd.

På köpet får man också en rankning av de största/minsta värdena.

Tabellen nedan visar exemplet i sin helhet:

Utifrån denna tabell ska vi identifiera celladresserna till de 15 högsta värdena. Tabellen exklusive rad 1 har definierats med namnet Data.

Det sker med hjälp av följande matrisformel:

  • {=ADRESS(MIN(OM(DataMatris=STÖRSTA(DataMatris;RAD(2:2));
    RAD(DataMatris);""));MIN(OM(DataMatris=STÖRSTA(DataMatris;
    RAD(2:2));KOLUMN(DataMatris);"")))}

Denna formel skapas i den första målcellen och kopieras nedåt i kolumnen lika många rader som antal adresser man önskar.

För att erhålla värdena i sig används följande formel:

  • =INDIREKT(H2)

Resultatet av dessa formler blir för exemplet (enbart de 5 första visas):

Räkna med PERCENTIL

Antag att du har en större datamängd och du vill erhålla:

  • Medelvärdet för de 20 % största värdena
     
  • Medelvärdet för de 20 % lägsta värdena och
     
  • Medelvärdet för de resterande 60 % av värdena.

Bilden nedan visar exemplet:

20 % av de högsta värdena utgörs här av värdena 90 och 100. Medelvärdet blir 95.

För att erhålla medelvärdet för de 20 % största värdena används följande matrisformel:

  • {=MEDEL(OM(Listan>=PERCENTIL(Listan;C2);Listan;""))}

20 % av de lägsta värdena utgörs här av värdena 10 och 20. Medelvärdet blir 15.

För att erhålla medelvärdet för de 20 % lägsta värdena används följande matrisformel:

  • {=MEDEL(OM(Listan<=PERCENTIL(Listan;C3);Listan;""))}

De kvarvarande 60 % utgörs här av värdena 30 till 80 i listan. Medelvärdet blir 55.

Följande matrisformeln genererar det önskade resultatet:

  • {=MEDEL(OM(Listan<=PERCENTIL(Listan;C3);"";OM(Listan>=
    PERCENTIL(Listan;C2);"";Listan)))}

Vill man visa i vilka kategorier värdena i listan ingår kan man skapa följande formel i en kolumn:

  • {=OM($A$1:$A$10>=PERCENTIL($A$1:$A$10;0,8);"20 % Högsta";OM($A$1:$A$10>=PERCENTIL($A$1:$A$10;0,2);
    "Mellan 60%";"20% Lägsta"))}

Det går även bra att använda sig av ovanstående tekniker för att skapa villkorsstyrd formatering.

Antag att vi vill utföra beräkningar där vi först vill filtrera listan utifrån vissa villkor innan beräkning sker.

Nedanstående exempel demonstrerar hur vi kan hantera denna situation

Exempel:

Följande formel möjliggör beräkningen:

  • {=PERCENTIL(OM((A2:A13=F1)*(B2:B13=F2);C2:C13);E5)}

Minsta & största värdena i "mitten"
Antag att du har en större datamängd och vill få reda på det minsta respektive största värdet för de 50 % av värdena som ligger i mitten.

KVARTIL-funktionen ger oss en hel del information om datamängden i sin helhet, t ex min- och maxvärdet och medelvärdet. Detta ska vi ta med oss för att lösa problemet i detta exempel.

Tabellen nedan visar exemplet:

För att erhålla det minsta värdet används följande formel:

  • {=MIN(OM(A2:A8>KVARTIL(A2:A8;1);A2:A8;MAX(A2:A8)))}

För att erhålla det största värdet används formeln:

  • {=MAX(OM(A2:A8<KVARTIL(A2:A8;3);A2:A8;MIN(A2:A8)))}

Årssummeringar
Antag att vi har en större lista som vi fyller i och vi vill löpande summera ihop belopp på årsbasis. Det finns här ett flertal tekniker att tillgå men då det ska vara löpande summering presenteras här en lösning utifrån det villkoret.

Tabellen nedan visar förutsättningarna för exemplet:

Som framgår är tabellen sorterad och summering sker vid varje årsskifte.

Följande matrisformel ligger till grund för beräkningen och kopieras nedåt i kolumnen.

  • {=SUMMA(N(ÅR(A2)<>ÅR(N(A3)))*N(ÅR(A2)=
    ÅR($A$2:$A$7))*$B$2:$B$7)}

N-funktionen är en förutsättning för att få önskad summering.

Hitta "riktig" data
Ibland kan vi behöva få fram data ur en lista som innehåller t ex #Saknas!- felmeddelandet.

Här demonstreras hur vi kan erhålla maximumvärdet från en sådan lista:

För att erhålla värdet ifråga används följande matrisformel:

  • {=INDEX(A2:A9;MAX((ÄRSAKNAD(A2:A9)=FALSKT)
    *RAD(A2:A9))
    -1;1)}

Vill man istället erhålla minimumvärdet används matrisformeln:

  • {=INDEX(A2:A9;MIN((ÄRSAKNAD(A2:A9)=FALSKT)*RAD(A2:A9));1)}

Medelvärdet av två matriser
Här presenteras en relativ avancerad teknik för att erhålla medelvärdet av två matriser. Dock är tidsvinsten såpass stor att det överväger komplexiteten.

Vi börjar med följande tabell:

Medelvärdet för Matris 1 är, =MEDEL(A2:A5), dvs 2 och för Matris 2 är det 2,5. Om vi räknar ut medelvärdet för hela datamängden uppgår det till 2,25. Men då underlaget utgörs här av två matriser blir medelvärdet för matriserna 4,5 (2+2,5).

Ingår större datamängder i matrisunderlaget och vi använder de enkla funktionerna som ovan så blir det tidskrävande uträkningar.

En generell formel för att erhålla medelvärdet för två matriser är:

  • {=MEDEL(MMULT(Data/MMULT(N(ICKE(ÄRTAL(Data)));
    TRANSPONERA(KOLUMN(Data)*0+1));
    TRANSPONERA(KOLUMN(Data)*0+1)))}

Namnet Data refererar till den datamängd som uträkning ska ske för men också utifrån de krav som MMULT-funktionen kräver - Se direkthjälpen.

I exemplet avser Namnet Data cellområdet A2:C5.

Medelvärdet för unika värden
Antag att vi vill få reda på medelvärdet för alla talen i en lista men där vi inte ska räkna med dubbletter av värdena, dvs för unika värden.

Tabellen nedan utgör exemplet ifråga:

Medelvärdet ska alltså beräknas för värdena 1, 2 och 3.

Mha följande (härliga!) matrisformel kan vi erhålla medelvärdet:

  • {=SUMMA((A2:A6)*(OM(ÄRTAL(A2:A6);1/ANTAL.OM
    (A2:A6;A2:A6))))/SUMMA(OM(ÄRTAL(A2:A6);1/
    ANTAL.OM(A2:A6;A2:A6)))}

Räkna nummerserier
I detta exempel belyses hur vi kan räkna antal artikelnummer som slutar på ett visst tal.

Antag att vi har följande artikelserienummer och där vi vill räkna antalet som har 0 eller 8 i slutet - Se nedanstående bild:

För att erhålla antalet används följande matrisformel:

  • {=SUMMA(N(REST($A$2:$A$6;10)=B2))}

Nyckeln till lösningen ligger i REST-funktionen och markera gärna denna del av formeln och tryck på F9-tangenten!

Räkna med textvärden
I detta tips visas hur vi kan ta fram data utifrån textvärden, där textvillkoret ej behöver vara fullständigt.

Antag att vi har en projektorganisation som är indelad i flera regioner. Regionerna är indelade i väderstreck, t ex NordVästra och NordÖstra.

Nu vill vi erhålla antal projekt och totalbelopp per väderstreck, dvs alla regioner som ingår i "Nord". I XL-termer är det villkoret "Nord*" som gäller.

De projekt som ska räknas är alla som börjar på "A", dvs ett ytterligare villkor är således "A*".

Tabellen nedan belyser exemplet i sin helhet:


Som framgår kan vi erhålla de önskade uppgifterna på flera sätt.

Följande matrisformel använder sig av SÖK-funktionen för att erhålla antal projekt:

  • {=SUMMA(ICKE(ÄRFEL(SÖK($E$2;$A$2:$A$6)))
    *ICKE(ÄRFEL(SÖK($F$2;$B$2:$B$6))))}

För att beräkna den totala projektsumman utvecklas denna formel och inkluderar även kolumn C:

  • {=SUMMA(ICKE(ÄRFEL(SÖK($E$2;$A$2:$A$6)))
    *ICKE(ÄRFEL(SÖK($F$2;$B$2:$B$6)))*(C2:C6))}

En annan lösning baseras också på matrisformel men med HITTA-funktionen istället:

  • {=SUMMA(ÄRTAL(HITTA($E$2;$A$2:$A$6))
    *(ÄRTAL(HITTA($F$2;$B$2:$B$6))))}

Vill vi inte använda oss av matristekniken kan följande lösning användas:

  • =PRODUKTSUMMA((ÄRTAL(HITTA($E$2;$A$2:$A$6))
    *(ÄRTAL(HITTA($F$2;$B$2:$B$6)))))

En skillnad mellan SÖK- och HITTA-funktionen är att den sistnämnda är skifteskänslig.

Kontrollera duplikat vid inmatning
Händer det att du ska mata in värden i en rad eller kolumn och där det bara får förekomma unika värden? Om ja, så kan detta tips vara något för dig.

Bilden nedan visar två möjliga exempel, antingen fylla i en rad eller en kolumn.

För kontroller vid inmatning i rad används följande matrisformel:

  • {=OM(SUMMA(ANTAL.OM(A1:E1;A1:E1))<>(KOLUMNER
    (A1:E1)-ANTAL.TOMMA(A1:E1));"Duplikat i raden";"")}

För kontroller vid inmatning i kolumn:

  • {=OM(SUMMA(ANTAL.OM(A2:A4;A2:A4))<>(RADER
    (A2:A4)-ANTAL.TOMMA(A2:A4));"Duplikat i kolumnen";"")}

Summera värdena i ett tal
Här demonstreras en teknik på hur vi kan summera de värden som ett tal representerar:

Tal

Ger summan

1456

16 (1+4+5+6)

121

4 (1+2+1)

225678912

42 (2+2+5+6+7+8+9+1+2)

1010

2 (1+0+1+0)

Antag att talet återfinns i cell A1 så ger följande matrisformel summan av talet:

  • {=OM(ÄRTAL(A1);SUMMA(1*EXTEXT(A1;RAD
    (INDIREKT("1:"&LÄNGD(A1)));1));0)}
     
Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin