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

 
  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.

Sökning i osorterad tabell
De inbyggda leta-funktionerna i XL förutsätter att underliggande tabeller är sorterade. Anta att tabeller inte kan sorteras - hur gör man då? M h a av två funktioner, INDEX och PASSA kan önskat resultat erhållas.

Funktionen PASSA returnerar den relativa radpositionen för önskat värde i ett cellområde. INDEX-funktionen returnerar cellinnehållet för den cell utifrån den relativa rad- och kolumnposition som cellen har i cellområdet. PASSA- funktionen används som ett argument i INDEX-funktionen, vilket möjliggör sökning i osorterade tabeller. Tabellen nedan visar exempel på det.

Funktionen PASSA i exemplet ovan returnerar talet 2, dvs radpositionen för Artikelnr "23451" i första kolumnen. INDEX-funktionen returnerar värdet 250, dvs den relativa rad- och kolumnpositionen {2;3} resulterar i cell C3, vars värdet returneras.

Skifteslägekänslig sökning
Funktionen EXAKT möjliggör skifteslägekänslig sökning, dvs funktionen skiljer på gemener och versaler. Det gör att funktionaliteten i leta-funktionerna utökas.

M h a OM-funktionen sker en utvärdering mellan önskat värde och tabell, där villkoret bygger på EXAKT-funktionen. Givet sorterad tabell fungerar denna lösning för alla leta-funktioner. Tabellen nedan visar ett exempel på LETAUPP- funktionen.

Föreligger en osorterad tabell kan EXAKT-funktionen kombineras med INDEX och PASSA såsom exemplet i tabellen nedan visar.

XL har ett inbyggt verktyg, Dataverifiering, som också kan lösa detta problem.

Skapa 2D uppslagstabell
Ibland vill man kanske leta upp värden i ett cellområde på basis av två variabler. I tabellen nedan visas ett exempel där de två variablerna utgörs av "Produkt" och "Volym". Genom att låta PASSA-funktionen vara argument för de relativa rad- och kolumnpositionerna i INDEX-funktionen erhålls det önskade värdet. Noterbart är att variabeln "Volym" är intervallbaserad i exemplet, dvs första intervallet utgörs av 1-99 st, nästa är 100 - 999 st och slutligen 1000 st eller fler.

Den formeltekniska lösningen ser ut på följande sätt:

  • =INDEX(A1:D5;PASSA(F1;A1:A5);PASSA(F2;A1:D1))

En annan situation, se tabell nedan, kan göra att lösningen skiljer sig från den ovanstående:

Den formeltekniska lösningen ser ut på följande sätt:

  • =LETARAD(B15;A9:E13;PASSA(B16;INDEX(A9:E13;1;);0))

Ett annat angreppssätt har sin utgångspunkt i följande tabell:

Det önskade värdet erhålls genom följande formel:

  • =FÖRSKJUTNING(A1;PASSA(A9;A2:A6;0);PASSA(B9;B2:E2;0);1;1)

En ytterligare situation är scheman. Här demonstreras ett exempel där varje elev skrivs in och där den totala beläggningen per vecka fås automatiskt.

Tabellen nedan visar den första tabellen i exemplet:

Utifrån detta underlag skapas följande beläggningsschema:

För att få namnen på rätt plats i schemat används följande matrisformel, vilken skapas och kopieras till övriga delen av tabellen:

  • {=INDEX(Elev;PASSA($B$9&$A10;Speldag&Speltid;0))}

Namnen i formeln refererar till Tabell 1.

För att dölja felmeddelandet - #Saknas! - används här formeln =ÄRSAKNAD i den villkorsstyrda formateringen.

Skapa 3D uppslagstabell
Kan man göra beräkningar på tabelldata som utsökts utifrån tre villkor? Exemplet i tabellen nedan visar på en lösning m h a en matrisformel som använder sig av sammanfogningstecknet (&). 

Formeln kan också användas för såväl mindre antal villkor som för fler villkor!

För den som inte vill använda sig av en matrisformel så kan problemet också lösas på följande sätt:

  • =PRODUKTSUMMA((A2:A5=E3)*(B2:B5=F3)*(C2:C5=G3);D2:D5)

Betygsättning
M h a funktionen LETAKOLUMN och en matrisformel kan man skapa ett enkelt system för betygssättning. I tabellen nedan visas ett exempel.

Skapa ett namn, "Betyg", och ange matrisformel enligt exemplet i tabellen.

Skriv in formeln =LETARAD(B2;Betyg,2)

Kopiera formeln i kolumnen - klart!

För att formeln även ska fungera i version 2000 måste "pipe"-tecknet ersättas med "backslash"-tecknet.

Tabellberäkningar
XL har många funktioner för att bearbeta tabelldata men den som vanligtvis gör mest "nytta" är DELSUMMA-funktionen då den:

Kan beräkna filtrerad data, dvs utför enbart beräkningar på de synliga cellerna och

Antalet matematiska beräkningar som funktionen kan utföra uppgår till hela 12 stycken!

I tabellen nedan visas förutsättningar för exemplet. Summering ska ske för tabelldatan varför koden "9" anges i funktionen - för övriga kodnummer se direkthjälpen.

I nedanstående tabell visas utfallet efter det att filtrering av tabelldata har skett - Voila!

Man kan också lösa vissa tabellberäkningar m h a ANTAL-funktionen.

Jämföra listor
Ibland händer det att man måste stämma av listor mot varandra. Hur gör man det på smidigaste sätt? Jo, m h a OM- och PASSA-funktionen. Exemplet i tabellen nedan belyser detta.

Matchning sker utifrån en jämförelse mellan en cells värde och ett cellområde. Finns cellvärdet i listan skrivs ett meddelande ut i målcellen annars sker ingenting.

Här demonstreras ett ytterligare sätt att matcha områden med varandra. Värdet i en cell jämförs med värdena i ett cellområde. Om det finns representerad i cellområdet anges dess värde i målcellen. Se tabellen nedan.

För att få en lista utan tomma rader kan följande formel användas istället.

  • =OM(ANTAL.OM($A$2:$A$6;B2)>0;B2;"")

Ett ännu enklare (!) sätt är att jämföra listor mha en matrisformel:

  • {=SUMMA(($A$2:$A$5=$B$2:$B$5)*1)=ANTAL($A$2:$A$5)}

Om alla värdena i lista 1 återfinns i lista 2 returneras SANT annars FALSKT.

Listig listsummering
OK, här är problemet: Du har två listor, vilka ska summeras men summering ska enbart ske för de rader som är ifyllda i båda listorna. Hur lösa detta problem då? Vi får helt enkelt ta hjälp av matrisformler . I exemplet nedan har vi två listor. Villkoret för listsummeringarna är att en rad inte är tom (<>"") i respektive lista. Det är alltså intervallen A2:A3 respektive B2:B3 som ska summeras då B4 är tom.

Om man vill lägga till en ytterligare lista kan formeln för det se ut enligt följande:

  • {=SUMMA(((C2:C5)<>"")*(C2:C5)*((A2:A5)<>"")*((B2:B5)<>""))}

En annan möjlig lösning är att använda sig av INDIREKT-funktionen enligt följande:

  • =SUMMA(INDIREKT("A2:A"&ANTAL(B2:B5)+1))

SUMMA-funktionen kan ersättas av t ex MEDEL, MAX- eller MIN-funktionen.

Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin