startsida < företaget < organisera < datorer < tips
 
Tabeller III

 
  Tabeller
Tabeller I
Tabeller II
Tabeller III
Tabeller IV
Tabeller V
Tabellfunktioner
Tabellhantering
Skapa arbets-
schema
   
  Innehåll
expowera

 

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

Räkna med logiska operatorer
XL har ett flertal s k logiska operatorer, såsom "OM", "OCH", "ELLER", "="," >", "<" och "<>". Här demonstreras hur man mha av dessa kan både räkna antal och summera poster som uppfyller vissa kriterium.

Funktionerna OCH och ELLER fungerar inte i s k matrisformler. För att få samma funktionalitet som dessa måste man använda sig av:

  • Multiplikationstecknet (*) för OCH
     
  • Plustecknet (+) för ELLER.

Exemplet bygger på följande tabelldata:

I nedanstående tabell visas villkoren och resultaten av de önskade beräkningarna.

I det första exemplet belyses hur man ställer upp s k OCH-villkor. Villkoren ska utläsas på följande sätt: Räkna antal poster som uppfyller villkoren "Jan" och "BP" och ">110". Antal poster räknas fram mha:

  • {=SUMMA((Månad=E2)*(Avd=F2)*(Utfall>H2))}

För att erhålla summan som dessa poster totalt representerar används följande matrisformel:

  • {=SUMMA((Månad=E2)*(Avd=F2)*(Utfall>H2)*Utfall)}

I nästa exempel demonstreras hur man ställer upp s k ELLER-villkor. 

Villkoren ska utläsas på följande sätt: Räkna antal poster som uppfyller villkoren "FEB" eller "EE" eller ">110". Antal poster räknas fram mha:

  • {=SUMMA(OM((Månad=E3)+(Avd=F3)+(Utfall>H3);1))}

För att få fram summan som dessa poster totalt representerar används följande formel:

  • {=SUMMA(OM((Månad=E3)+(Avd=F3)+(Utfall>H3);1;0)*Utfall)}

I det sista exemplet demonstreras kombinationen av OCH- och ELLER-villkor, där antal poster fås fram genom formeln:

  • {=SUMMA((Månad=E4)*OM((Avd=F4)+(Avd=G4);1))}

Summan som dessa poster totalt representerar erhålls mha matrisformeln:

  • {=SUMMA((Månad=E4)*OM((Avd=F4)+(Avd=G4);1)*Utfall)}

Noterbart är är att villkoren i ovanstående formler kan utökas till oändlighet (nästan...).

Enkel tariffkalkylering
En av de mest frekventa frågorna till frågelådan är hur man ska göra för att kunna hitta rätt pris givet vissa intervall. Många funderar på att lösa det mha OM-funktionen men inser snabbt att den har sina begränsningar. Nå, hur gör löser det då?

Lösningen ligger i hur man ställer upp sin tabell och att man använder LETARAD-funktionen.

Givet denna uppställning så kan man använda sig av följande formel:

  • =LETARAD(D3;A3:C8;3)

Enkel lösning på ett upplevt svårlöst problem!

Avancerad tariffkalkylering
Tänk dig att du har en pristariff där det totala priset är avhängt flera prisintervaller. För t ex de första fem enheterna betalas ett pris och för de fyra enheterna därutöver betalas ett annat lägre pris. Hur kan man lösa detta komplexa kalkylproblem? I nedanstående exempel visas en lösning. För de första 100 enheterna får man betala 40 kr / enhet, för de 100 enheterna därefter betalas 30 kr / enhet osv. Givet en volym om 201 st så betalar man 7020 kr (100*40 + 100*30 + 1*20) i rörlig avgift. Värdena 100, 100 och 1 genereras av de nästlade OM-funktionerna och därefter sker en multiplikation med värdena i cellintervallet B3:B6 och slutligen sker en summering!

Multipel LETAUPP
Här demonstreras en teknik där ett flertal uppslagsvärden används samtidigt och där resultatet är en summering!

Förutsättningarna för exemplet framgår i tabellen nedan.

Hm, hur kan formeln se ut för att erhålla värdet 900, dvs de poäng som startnummer 1, 2 och tre samlat ihop totalt?

Följande matrisformel löser det upplevda problemet:

  • {=SUMMA(N(FÖRSKJUTNING(A2:B7;PASSA
    (C2:C4;FÖRSKJUTNING(A2:B7;;;;1))-1;1;1)))}

Finns i listorna?
Antag att vi har tre listor där vi vill att värden från den 3:e listan visas om värdena i den 1:a listan återfinns i den 2:a listan. Hm, låter det krångligt?

Förhoppningsvis ska exemplet nedan räta ut frågetecknet!

I Lista 1 finns värdet 3, vilket också återfinns i Lista 2 varför XL ska visa värdet från Lista 3 på samma rad som i Lista 2. Som framgår så visas värdet 10 på rad 2 i kolumnen för utfall.

Hur ser den formeltekniska lösningen ut? Jo, här tar vi hjälp av ÄRSAKNAD- funktionen i kombination med en evaluering och PASSA-funktionen enligt följande:

  • =OM(ÄRSAKNAD(PASSA(B2;$A$2:$A$11;0));"";C2)

Denna formel återfinns i cell D2 i exemplet ovan och kopieras nedåt i kolumnen lika långt som listorna är.

Summera arbetsorders
Nedan visas en teknik för att summera grupper av arbetsorders eller liknande.

Den formeltekniska lösningen för summering av arbetsorders tillhörande gruppen "AT" utgörs av matrisformeln:

  • {=SUMMA((VÄNSTER($A$2:$A$6;2)=C2)*$B$2:$B$6)}

För att erhålla summan för arbetsorders tillhörande gruppen "B" kan en enklare matrisformel användas:

  • {=SUMMA((VÄNSTER($A$2:$A$6)=C3)*$B$2:$B$6)}

Avancerad uppslagsfunktion
Här introduceras två funktioner, TRANSPONERA och MMULT, vilka ligger till grund för att lösa problemet ifråga. Direkthjälpen ger bra förklaring av funktionerna.

Uppgiften är att utifrån tre referensuppgifter visa det önskade värdet i tabellen.

Tabellen nedan visar förutsättningarna:

Området A1:C5 har namngivits till Omrade.

Position anger för XL att leta efter den första förekomsten av radvärdet. I exemplet ska XL leta efter det första c-värdet i kolumn A.

Rad anger det värde XL ska leta efter i den första kolumnen. Kolumnvärdet anger i vilken kolumn XL ska leta i, här ska XL leta i den 2:a kolumnen, dvs i B-kolumnen.

Följande formel ger det önskade resultatet:

  • =INDEX(Omrade;PASSA(E1;MMULT(N(RAD(Omrade)>=
    TRANSPONERA(RAD(Omrade)));N(INDEX(Omrade;;1)=E2));0);E3)

Ett tips: Markera de olika funktionerna i formeln och tryck F9-tangenten så ser man vad de genererar för värden.

Hitta Första & Sista värdet i en tabell
Här demonstreras två enkla tekniker för att slå upp det sista och det första värdet.

Följande tabell utgör exemplet ifråga:

För att erhålla det sista värdet används den enklaste formeln:

  • =LETAUPP(D1;A2:A5;B2:B5)

För att få fram det första värdet används en kombinerad formel:

  • =INDEX(A2:B5;PASSA(D1;A2:A5;0);2)

Hitta rätt kolumn
Händer det att du har behov av att veta i vilken namngiven kolumn ett värde finns i?

Här visas ett lösning på detta problem. Tabellen nedan visar exemplet i sin helhet:

Utifrån Avdelning och värde ska vi överlåta till XL att tala om i vilken kolumn (1998 - 2001) värdet återfinns i.

Följande formel ligger till grund för lösningen:

  • =LETAUPP(B8;INDEX(B2:E5;PASSA(A8;A2:A5;0););B1:E1)

Det som är både intressant och viktigt är att formeln genererar det första kolumnnamnet där värdet återfinns i.

Dessutom bör vi ha i åtanke att om listan är sorterad enligt exemplet - stigande värden i varje rad - så utgör värdena startvärden i intervallen.

T ex raden för Avd AA så refererar intervallen:

5 - 9  år 1998
10 - 19  år 1999
20 - 29  år 2000
30 -  år 2001

Leta upp flera värden
I detta exempel visas hur vi mha matrisformler kan leta upp flera värden samtidigt utifrån listpositioner.

Utgångspunkten för exemplet är följande lista:

Vi börjar med att leta upp värdena i kolumn A som har positionerna 2,3 och 4.

  • {=LETAKOLUMN(D2;A2:A6;{2\3\4})}

(Noterbart är att ett faktiskt värde (från listan) måste anges i cellen D2 men att värdet saknar betydelse för sammanhanget!)

I nästa steg letar vi upp värdena i kolumn B som har värdena "AA", "BB" och "CC" i kolumn A.

  • {=LETARAD({"AA"\"BB"\"CC"};A2:B6;2)}

Alternativt kan vi använda oss av följande matrisformel:

  • {=LETAUPP({"AA"\"BB"\"CC"};A2:A6;B2:B6)}

Resultatet av dessa beräkningar blir följande:

Om vi vill presentera resultatet per rad istället för som ovan per kolumn får vi använda oss av följande formler:

  • {=LETAKOLUMN(D2;A2:B6;{2;3;4})}
     
  • {=LETARAD({"AA";"BB";"CC"};A2:B6;2)}
     
  • {=LETAUPP({"AA";"BB";"CC"};A2:B6;B2:B6)}

Resultatet av dessa beräkningar blir följande:

För samtliga formler gäller att vi först markerar de celler som ska ta emot data. Därefter skriver vi in formeln och slutligen bekräftar vi inmatningen med Ctrl+Skift+Enter.

Noterbart - Skillnad mellan XL 2000 och XL 97

  • För presentation av värden i kolumn i XL 97 måste "\"-tecknet ersättas med pipe-tecknet "|".
Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin