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

 
  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.

Tabellutsökning mha MAX-värden
Nedan visas exempel på hur man med hjälp av MAX- och FÖRSKJUTNING- funktionen kan slå upp värden i en tabell. Beroende på förutsättningarna kan MAX-funktionen ersättas av andra funktioner.

Rent tekniskt sett så ställs det ett villkor (=) som ger värdet SANT för den 3:e raden. Vid multiplikation med RAD-värdena {2|3|4|5|6} erhålls värdet 4. För att få rätt position måste värdet reduceras med 1 (-1). Den sista 1:an i formeln ger värdet i kolumn 2 (Avdelning). Som framgår av exemplet kan den ändras till önskad kolumn.


 
Om ovanstående formel modifieras en aning kan man t ex erhålla den avdelning som har högst utfall för en period. Exemplet nedan demonstrerar detta.

Följande matrisformel krävs för att erhålla den avdelning som har det högsta utfallet:

  • {=INDEX(A2:A5;MAX((B2:D5=MAX(B2:D5))*RAD(A1:A4)))}

Nedanstående matrisformel ger vilket kvartal som innehåller det högsta värdet:

  • {=INDEX(B1:D1;MAX((B2:D5=MAX(B2:D5))*KOLUMN(A1:C1)))}

Högsta och lägsta värdet i en tabell
Antag att du har en större datamängd och att du behöver snabbt få fram vilken enhet som har det högsta utfallet för perioden samt det lägsta utfallet. Hur gör du? Här är i alla fall en lösning på problemet.

Vill man erhålla det lägsta utfallet för perioden så kan man ersätta MAX-funktionen med MIN-funktionen i formeln.

Istället för att få fram data kan det ibland vara önskvärt att få information om vilken celladress i en lista som har t ex det högsta eller det lägsta värdet.

För att få fram den celladress som har det högsta värdet i en kolumnlista kan svaret erhållas m h a följande formel:

  • =CELL("adress";FÖRSKJUTNING(A1;PASSA
    (MAX(A1:A4);A1:A4;0)-1;0))

För att kunna använda sig av denna teknik för en radlista bör följande formel användas:

  • =CELL("adress";FÖRSKJUTNING(A6;0;PASSA
    (MAX(A6:F6);A6:F6;0)-1))

Vill man erhålla celladressen för det lägsta värdet ersätts MAX-funktionen med MIN-funktionen i ovanstående formler. Viktigt att påpeka är att CELL-funktionen enbart genererar textvärden.

Leta till vänster?
Vissa omständigheter kan göra att en tabell inte kan stuvas om för att kunna leta upp värden till höger om uppslagskolumnen. M a o, att hitta värden till vänster om uppslagskolumnen är det enda möjliga alternativ som finns. I tabellen nedan demonstreras en teknik för att lösa situationen.

Uppslagsvärdet är avdelningstillhörighet och på basis av värdet i cellen F2 erhålls såväl "Internnr" som "Budget". RAD-funktionen anger vilka rader som utsökningen ska ske i och PASSA-funktionen ger det faktiska radnumret för det angivna värdet. För korrigering av radnummer måste värdet "-1" anges. KOLUMN-funktionen ger kolumnvärdet för "Avdelning" och då utsökning ska ske två kolumner till vänster om "Avdelning" måste kolumnvärdet reduceras med 2, dvs 3 - 2 = Kolumn 1. För "Budget" anges värdet "-1" för antal kolumner till vänster som ska sökas igenom. ADRESS-funktionen ger värdet $A$3 och där INDIREKT-funktionen slutligen ger värdet i cellen, dvs 422.

En bättre lösning är att använda sig av INDEX-funktionen och PASSA-funktionen tillsammans såsom följande:

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

  • =INDEX(A1:C4;PASSA(F2;C2:C4;0)+1;1)

För att erhålla värdet ur Budget-kolumnen används följande formel:

  • =INDEX(A1:C4;PASSA(F2;C2:C4;0)+1;2)

Veckoutfall med summering
I exemplet nedan visas ytterligare en teknik för att slå upp värden i en tabell, här m h a INDEX-funktionen. Namnet "Tabell" omfattar cellområdet B2:F7. Då den första skärningspunkten i exemplet är tom måste detta förhållande beaktas vid formelkonstruktionen genom att helt enkelt lägga till värdet 1 för såväl rad som kolumn. Därmed uppstår en förskjutning och rätt utfall erhålls.

Det andra exemplet är desto mer komplicerat. Det önskade resultatet ska avse två veckor, dvs summering ska ske för utfallet utifrån vald startvecka plus en vecka till.

Då både "Station" och "Veckonummer" kan variera medför det att lösningen måste vara flexibel. Frågan som kräver sitt svar är: Vilken cellreferens avses i tabellen? Startpunkten för problemets lösning utgörs av funktionen ADRESS och där RAD- och KOLUMN-funktionerna tillsammans genererar cellnamnet. För rätt rad- och kolumnposition krävs det att värdena från cellerna I2 och I1 adderas. För att få adressen och inte värdet måste man referera indirekt till ADRESS-funktionens resultat, dvs använda sig av INDIREKT-funktionen. Cellnamnet ligger till grund för FÖRSKJUTNING-funktionens startcell och där argumentet "2" används för att XL ska beakta veckan därefter. Slutligen sker det en summering - Pröva själv!

För ytterligare information om FÖRSKJUTNING-funktionen se direkthjälpen. SUMMA-funktionen kan t ex ersättas av MEDEL-funktionen.

Hitta dubbletter mha formler
Att leta efter dubbletter i en lista kan vara tidsödande, i synnerhet om det sker visuellt. Nedan visas ett sätt, som utvärderar listan.

Skapa tabellunderlag
Om du ofta arbetar med tabelluppställningar och där data hämtas från ett mindre antal underliggande tabeller kan detta vara en intressant ansats. Nedan visas en underliggande datatabell, ur vilken belopp ska hämtas givet viss avdelning och datum.

Formeln söker reda på det första beloppet som motsvarar kritierierna "datum" och "avdelning". Det andra beloppet erhålls genom att man kopierar formeln en rad ned. Felmeddelandet #Ogiltigt! säger att det inte finns fler belopp att hämta utifrån de uppställda villkoren.

Vill du erhålla data i en rad i stället för i en kolumn måste ovanstående formel justeras till följande:

  • {=INDEX($C$2:$C$6;MINSTA(OM(($A$2:$A$6=$E$2)*
    ($B$2:$B$6=$F$2);RAD($1:$6);"");KOLUMN(A:A)))}

Skapa formeln och kopiera den i raden. Vill man få fram det sista beloppet först så kan man ersätta MINSTA-funktionen med STÖRSTA-funktioner.

Nästlad leta upp
Hm, antag att du har en tabell med flera kolumner, i vilka ett uppslagsvärde kan förekomma, men det är inte givet på förhand vilken kolumn värdet finns i.

I tabellen nedan återges förutsättningarna i exemplet.

Mha OM-funktionen kan vi skapa en nästlad letaupp-funktion som ger rätt resultat.

  • =OM(ÄRFEL(LETAUPP(A8;Lista1;Värdelista_1));LETAUPP
    (A8;Lista2;Värdelista_2);LETAUPP(A8;Lista1;Värdelista_1))

Leta i flera tabeller
Här demonstreras hur man mha INDIREKT-funktionen kan leta upp värden i flera tabeller.

Utgångspunkten för exemplet framgår av följande bild:

På basis av ort och vikt ska vi erhålla pris för en sändning men här med bara två alternativ för ort, Sto och Gbg.

Det sker bäst med en OM-sats och användande av INDIREKT- funktionen enligt följande:

  • =LETARAD(B16;INDIREKT(OM
    (A16="Sto";"A4:B6";"A10:B12"));2;FALSKT)

Mha av denna formel erhålls följande resultat:

Om vi har tre olika destinationer så måste vi använda oss av en annan formel men vi nyttjar fortfarande INDIREKT-funktionen:

  • =LETARAD(B20;INDIREKT(INDEX({"A4:B6";"A10:B12";"C4:D6"};
    PASSA(A20;{"Sto";"Gbg";"Mmö"};0)));2;FALSKT)

Klamrarna anges manuellt i formeln. Antal alternativ kan givetvis utökas till hur många som helst (nästan).

Resultatet blir enligt följande:

Vill man inte använda sig av fasta argument för orterna kan man referera till celler istället:

  • =LETARAD(B26;INDIREKT(LETARAD
    (A26;A22:B24;2;FALSKT));2;FALSKT)

Med samma resultat:

Dynamisk sortering
Här presenteras en intressant lösning på automatiskt uppdatering av en sorterad lista.

I nedanstående bild visas såväl källdatan som de två sorterade listorna. Dessa listor förändras automatisk när ny data anges i intervallet (A2:A6).

För att sortera fallande så används följande matrisformel:

  • {=STÖRSTA(A2:A6;RAD(INDIREKT("1:"&RADER(A2:A6))))}

Vill man inte använda sig av en matrisformel kan man lösa det mha:

  • =OM(RAD()-RAD($B$2:$B$6)+1>5;"";STÖRSTA
    ($A$2:$A$6;RAD()-RAD($B$2:$B$6)+1))

Cellintervallet $B$2:$B$6 är målområdet och värdet 5 representerar antal värden som ska rangordnas. Vill man bara rangordna t ex de tre främsta anges värdet 3 istället för 5.

För stigande sortering används nedanstående formel:

  • {=MINSTA(A2:A6;RAD(INDIREKT("1:"&RADER(A2:A6))))}

Vill man inte använda sig av en matrisformel kan man lösa det mha:

  • =OM(RAD()-RAD($B$2:$B$6)+1>5;"";MINSTA
    ($A$2:$A$6;RAD()-RAD($B$2:$B$6)+1))

Vill man hantera felvärden kan man lösa det med följande formel:

  • {=OM(ÄRFEL(MINSTA(A2:A6;RAD(INDIREKT("1:"&RADER
    (A2:A6)))));"";MINSTA(A2:A6;RAD(INDIREKT("1:"&RADER(A2:A6)))))}

Noterbart är att dessa formler enbart fungerar för tal och inte text. 
 
Upp

Senast uppdaterad: 2005-10-05 © Dennis Wallentin