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

 
  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.

Identifiera heltal
I detta exempel ska vi djupare se hur vi kan erhålla heltalet i ett artikelnummer. Detta tal ska användas till vissa beräkningar.

Varje artikelnummer ser ut på följande sätt: 9CD*7WC014. Det är heltalet efter *-tecknet som ska extraheras ur numret, dvs i exemplet är det talet 7 som ska erhållas. *-tecknet återfinns inte alltid i 4:e positionen, såsom i exemplet, utan kan ha olika positioner.

För att erhålla heltalet i artikelnumret används följande "fula" formel:

  • =EXTEXT(A2;HITTA("*";A2)+1;MIN(OM(ÄRF(HITTA(EXTEXT
    (EXTEXT(A2;HITTA("*";A2)+1;1024);RAD(2:1025);1);
    "0123456789"));RAD(2:1025)))-1)+0

Vilken månad?
Här demonstreras ett sätt att identifiera i vilken kolumnhuvud som de största värdena återfinns i.

Utgångspunkten är följande exempel:

För att få de största värdena används följande formel:

  • =STÖRSTA($C$2:$G$5;B8)

För att erhålla vilka månader som håller de största värdena används matris- formeln:

  • {=INDIREKT(ADRESS(1;KOLUMN(INDIREKT(ADRESS
    (MIN(OM($C$2:$G$5=C8;RAD($C$2:$G$5);""));
    MIN(OM($C$2:$G$5=C8;KOLUMN($C$2:$G$5);"")))))))}

Vilka har de högsta värdena?
Här fortsätter vi på ovanstående exempel men istället för att identifiera de månaderna med de högsta värdena ska vi nu ta reda på vilka personer som har dessa.

Här visas exemplet i sin helhet:

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

  • {=FÖRSKJUTNING(INDIREKT(ADRESS(MIN(OM($B$2:$F$5=B8;
    RAD($A$2:$A$5);""));MIN(OM($B$2:$F$5=B8;KOLUMN(B:F);
    ""))));0;
    1-KOLUMN(INDIREKT(ADRESS(MIN(OM($B$2:$F$5=B8;
    RAD($A$2:$A$5);""));MIN(OM($B$2:$F$5=B8;KOLUMN(B:F)))))))}

Beroende på var denna formel placeras i arbetsbladet måste det rödmarkerade talet (i exemplet talet 1) justeras utifrån sin position.

Om vi vill få reda på vilka som har de lägsta värdena så får formeln i cell B8 - STÖRSTA($B$2:$F$5;A8) - ersättas med formeln:

  • MINSTA($B$2:$F$5;A8)

Räkna med cellområden i tabeller
Här demonstreras ett grundläggande förfarande för att t ex summera ett cellområde i en tabell.

Utgångspunkten är följande uppställning:

(1, 2, 3 osv representerar dygnet timmar i tabellen ovan)

För att identifiera vilka och antalet rader behöver vi erhålla uppgifter från användaren i form av start- och slutdatum.

För att erhålla vilka och antalet kolumner behöver vi uppgifter om start- och sluttid.

Namnkonventioner i exemplet:

  • Namnet "Datum" refererar till cellområdet A2:A11.
     
  • Namnen Start- och Slutdatum/tid refererar till cellerna H1, H2, H3 och H4.

Följande matrisformel ger oss adressen till startcellen för cellområdet i tabellen:

  • {=ADRESS(MIN(OM(ICKE(ÄRFEL(HITTA(Startdatum;Datum)));
    RAD(Datum)));1+Starttid)}

Matrisformeln nedan ger i sin tur adressen till den sista cellen för cellområdet:

  • {=ADRESS(MAX(OM(ICKE(ÄRFEL(HITTA(Slutdatum;Datum)));RAD
    (Datum)));1+Sluttid)}

När dessa formler sammanfogas erhålls adressen till cellområdet i tabellen, som beräkning ska ske för.

Då den sammanfogade matrisformeln genererar en textsträng för cellområdet måste det beaktas i den slutgiltiga formeln, vilket bäst sker mha av funktionen INDIREKT.

Slutligen löser följande formel problemet med att summera cellområdet:

  • {=SUMMA(INDIREKT(ADRESS(MIN(OM(ICKE(ÄRFEL(
    HITTA(Startdatum;Datum)));RAD(Datum)));1+Starttid)
    &":"&ADRESS(MAX(OM(ICKE(ÄRFEL(HITTA(Slutdatum;Datum)))
    ;RAD(Datum)));1+Sluttid)))}

För de övriga beräkningarna har SUMMA-funktionen i formeln ersatts av MEDEL-, MIN- och MAX-funktionerna.

Radsammanställning
Här demonstreras en teknik för att hämta inmatad data oavsett i vilken kolumn det anges i.

Utgångspunkten för exemplet är att på varje rad anges endast ett värde. I vilken kolumn det anges i är inte på förhand givet. Däremot är inmatningsområdet känt, dvs värdena finns inom ett begränsat cellområde.

Följande bild visar exemplet i sin helhet:


För att erhålla sammanställningen i D-kolumnen används följande matrisformel, vilken kopieras nedåt i kolumnen:

  • {=INDIREKT(ADRESS(RAD();MAX((A2:C2<>"")*KOLUMN(A2:C2))))}

Summera texttal
Tipset är kanske inte det mest nyttjade men demonstrerar en teknisk lösning för att summera tal där talen är en del av textsträngar.

Bilden nedan visar förutsättningarna:

Uppgiften är att summera de texttal, där övrig text i strängen består av bokstaven b. Som framgår av bilden är det två strängar som uppfyller villkoret, 1b och 2b.

Följande matrisformel ger lösningen:

  • {=SUMMA(OM(KOD(EXTEXT(A2:A7;LÄNGD(A2:A7)-1;1))
    <58;VÄNSTER(A2:A7;LÄNGD(A2:A7)-1))*(HÖGER(A2:A7)=D2))}

Givetvis kan även andra beräkningar än summering utföras.

Hitta post med största antalet
Här demonstreras en teknik för att identifiera t ex artikelnumret med det största antalet i en lista.

Exemplet i sin helhet framgår av följande uppställning:

Formeln för att erhålla vilken artikelnummer som har största antalet i listan är:

  • {=FÖRSKJUTNING(Artikelnr;MAX((SUMMA.OM(Artikelnr;
    Artikelnr;Antal)=MAX(SUMMA.OM(Artikelnr;Artikelnr;Antal)))
    *RAD(Artikelnr))-1;0)}

Artikelnr refererar till cellområdet A1:A9 och Antal till cellområdet B1:B9.

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

  • =SUMMA.OM(Artikelnr;C2;Antal)

Radposition
Här visas ett exempel på hur vi kan erhålla vilken radposition en post har utifrån två uppslagsvärden.

Bilden nedan visar exemplet i sin helhet:

Utifrån värdena i cell D2 och E2 hämtas postens radposition. Om den saknas så returernas 0.

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

  • {=MAX(RAD(A2:A6)*(A2:A6=D2)*(B2:B6=E2))}

Datum för senast inmatade värdet
Ibland kan det vara av vikt att få reda på t ex datumet för senast inmatade värde i en lista. Här i detta exempel demonstreras ett sätt att lösa det på.

I nedanstående bild visas förutsättningarna för exemplet:

Formeln för att erhålla datumet utgörs av:

  • {=INDEX(A1:A100;PASSA(1;(ÄRTOM(B1:B100)*1);0)-1)}

För att erhålla det senaste angivna beloppet används:

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

Antal unika kunder
Antag att vi har en situation där vi har flera poster i en lista som representerar en och samma order. Om vi då vill erhålla antal kunder per order och per månad hur gör vi då?

Exemplet utgår från följande uppställning:

Utsökningen sker utifrån kriterierna order "115" och att månaden är februari ("2"). Följande matrisformel ger det korrekta antalet kunder:

  • {=SUMMA(N(FREKVENS(OM((B2:B10=F1)*(MÅNAD(C2:C10)=F2);
    PASSA(A2:A10;A2:A10;0));PASSA(A2:A10;A2:A10;0))>0))}

Saknade serievärden
I en kolumn har vi en lista över en talserie. Nu vill vi få fram vilka serievärden som saknas i listan.

Exemplet i sin helhet visas i följande bild:

Följande formel skrivs in samtidigt i cellområdet B2:B8 och då det är en matrisformel bekräftas inmatningen med Ctrl+Skift+Enter:

  • {=MINSTA(OM(ÄRSAKNAD(PASSA(RAD(INDIREKT(MIN(A2:A8)&":"
    &MAX(A2:A8)));A2:A8;0));RAD(INDIREKT(MIN(A2:A8)&":"&
    MAX(A2:A8)));"");RAD(INDIREKT("1:"&(MAX(A2:A8)-MIN
    (A2:A8)-ANTAL(A2:A8)+1))))}

Felmeddelandet erhålls när inte fler serievärden saknas i listan.

Skapa unik lista från två unika listor
I detta tips demonstreras hur vi kan skapa en unik lista från två lika stora unika listor. Med unik lista avses här värden som endast förekommer i Lista 1 men ej i Lista 2.

Bilden nedan visar exemplet i sin helhet:

De två listor som ska jämföras får endast innehålla värden som endast förekommer en gång i respektive lista. Cellområdena för Lista 1 och Lista 2 måste vara lika stora och cellområdet för den nya listan måste vara lika stort som för Lista 1 och Lista 2.

Om inga unika värden förekommer i Lista 1 jämfört med Lista 2 genereras felmeddelandet #Referens!.

Följande matrisformel anges i cellområdet C2:C5:

  • {=INDEX(A2:A5;MINSTA(OM(ÄRSAKNAD(PASSA(A2:A5;B2:B5;0));
    RAD(INDIREKT("1:"&RADER(A2:A5))));RAD(INDIREKT("1:"&SUMMA
    (OM(ÄRSAKNAD(PASSA(A2:A5;B2:B5;0));1;0))))))}

Hämta värden ur textsträngar
Här visas några lösningar mha matrisformler för att hämta värden ur textsträngar. Detta kan komma till pass vid kontroll av artikelnummer eller om behov finns att extrahera tal ur importerad textmassa och t ex utföra beräkningar på talen.

Tipset har sin utgångspunkt och lösningar i följande bild:

För lista 1 används följande matrisformel, vilken kopieras nedåt i kolumn B:

  • {=VÄNSTER(A2;ANTAL(1*EXTEXT(A2;RAD($1:$25);1)))*1}

För lista 2 används följande matrisformel, vilken kopieras nedåt i kolumn D:

  • {=HÖGER(B2;ANTAL(1*EXTEXT(B2;RAD($1:$25);1)))*1}

För lista 3 används följande matrisformel, vilken kopieras nedåt i kolumn F:

  • {=EXTEXT(E2;ANTAL(1*EXTEXT(E2;RAD($1:$25);1));3)*1}

Den sistnämnda formel kräver att vi på förhand vet antal tecken som ska hämtas och är inte lika generell till sin karaktär som de två förstnämnda utan här måste vi pröva oss fram utifrån aktuell textmassa.

Är cellområdet tomt?
I vissa sammanhang kan det vara värdefullt att utvärdera huruvida ett cellområde är tomt eller inte. Detta tips visar hur vi kan göra det mha en matrisformel.

Bilden nedan visar exemplet i sin helhet:

Formel som ger utfallet SANT eller FALSKT:

  • {=OCH(ÄRTOM(A1:A8))}

Skapa ny sammanhängande lista

Här demonstreras en teknik för att skapa en ny sammanhängande lista från en annan lista som har tomma rader.

Exemplet utgår från följande uppställning:

I exemplet skapas formeln i cell B2 och kopieras till dess att felvärdet visas i kolumnen.

Vill vi visa felmeddelandet används följande matrisformel:

  • {=INDEX($A$1:$A$99;MINSTA(OM(ÄRTAL($A$1:$A$99);
    RAD($A$1:$A$99);"");RAD(1:1))}

Vill vi eliminera felmeddelandet och visa tom cell istället får vi hantera det mha följande formel:

  • {=OM(ÄRFEL(INDEX($A$1:$A$99;MINSTA(OM(ÄRTAL
    ($A$1:$A$99);RAD($A$1:$A$99);"");RAD(1:1))));"";INDEX
    ($A$1:$A$99;MINSTA(OM(ÄRTAL($A$1:$A$99);RAD
    ($A$1:$A$99);"");RAD(1:1))))}

Lista största värdena utan dubbletter
Antag att vi har en lista med värden, där flera värden förekommer flera gånger. Antag vidare att vi vill lista de största värdena men utan att dubbletter erhålls av de största värdena. Detta tips visar hur vi kan lösa det mha en matrisformel.

Följande bild visar exemplet i sin helhet:

Följande formel matas in i cell C1:

  • =MAX(A2:A10)

Följande formel matas in i cellen C2 och kopieras nedåt till dess att cellvärdet är lika med tom (""):

  • {=OM(ELLER(C2=MIN($A$2:$A$10);$C$2="");"";STÖRSTA
    ($A$2:$A$10;ANTAL.OM($A$2:$A$10;">="&C2)+1))}

Erhålla största & minsta värdet
Här demonstreras en teknik för att hämta det största värdet i en rad och det matchande minsta värdet i en annan rad. Om det största värdet förekommer fler gånger i rad 1 så hämtas det lägsta värdet från rad 2.

Den formeltekniska lösningen fungerar också om det är två kolumner värdena är angivna i.

Bilden nedan visar hela exemplet:

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

  • {=SAMMANFOGA(OM(B2:G2=MAX(B3:G3);B3:G3;MAX(B2:G2));"-";
    MIN(OM(B2:G2=MAX(B2:G2);B3:G3)))}

Namnsökning
Antag att vi har en lista med förnamn och en lista med fullständiga namn (förnamn, efternamn och ibland mellannamn). Antag vidare att vi vill göra en utsökning om ett eller fler namn i den första listan förekommer i den andra listan. Hur kan vi erhålla önskat utfall?

Ett sätt visas här och utifrån följande tabelluppställning:

Namnet Eva i Lista 1 förekommer också i Lista 2 medan t ex Dennis inte förekommer i Lista 2.

Följande matrisformel anges i cellen C2 och kopieras nedåt så långt det är önskvärt i C-kolumnen.

  • {=FÖRSKJUTNING($A$2; PASSA(MAX(OM(ÄRTAL(SÖK
    (" " & $A$2:$A$6 & " "; " " &B2 & " "));SÖK
    (" " & $A$2:$A$6& " "; " " & B2 & " ")));SÖK
    (" " &$A$2:$A$6 & " "; " " & B2 & " ");0)-1;0)}

Som framgår relativt tydligt så förekommer Viktor i båda listorna med visas inte i Resultat-kolumnen. Det får ses som en begräsning i formeln då den endast tar första förekomsten av namn, dvs här så har formeln identifierat Adam, vilken kommer före Viktor i Lista 1. Därför redovisas Adam men inte Viktor i uppställningen.

Hitta det numeriska värdet i raden
Här visas en teknik för att hämta det första numeriska värdet i en rad.

Bilden nedan visar exemplet i sin helhet:

Följande matrisformel skapas i cellen A1 och kopieras nedåt i kolumnen.

  • {=OM(ANTAL(B2:D2)=0;"";FÖRSKJUTNING(B2;0;MIN(OM(ÄRTAL
    (B2:D2)=SANT;KOLUMN(B2:D2)-2;9999));1;1))}
Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin