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

 
  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.

Driftstopp första dagen
I detta tips demonstreras hur vi kan identifiera första dagen i månaden med driftstopp för en eller flera maskiner.

I nedanstående tabell representerar värdet 1 normal drift och 0 driftstopp. Uppgiften är att identifiera vilken dag i månaden som det första driftstoppet inträffar vid.

Sammanställningen för nämnda maskiner visas i nästa tabell:

För att erhålla första dagen för maskin "AA" används följande matrisformel:

  • {=OM(SUMMA($B2:$G2)=KOLUMNER($B2:$G2);"Fungerar";
    OM(SUMMA($B2:$G2)=0;"Ej i bruk";OM(B$2=0;$B$1;
    FÖRSKJUTNING($B$1;0;MAX(($B2:$G2>0)*(KOLUMN($B2:$G2)
    -KOLUMN($A2)))))))}

Följande utvärderingar sker:

  1. Om maskinen fungerar utan avbrott - Fungerar!
     
  2. Om maskinen ej är i bruk - Ej i bruk.
     
  3. Om driftstopp inträffar första dagen.
     
  4. Om driftstopp inträffar under någon annan dag.

Hämta värden ur en lista från en annan list
Ibland kan det vara önskvärt att slå upp ett värde i en lista och hämta dess motsvarighet i en annan lista (på samma rad).

Här visas ett smidigt förfarande för att lösa detta problem.

Formeln som möjliggör denna lösning är:

  • =FÖRSKJUTNING(A2;PASSA(C2;B2:B5;FALSKT)-1;0)

Hämta sista värdet för ett givet år
Antag att vi har en lång lista med datumangivelser och belopp. Ur denna vill vi hämta det sista angivna värdet för ett givet år.

Tekniken kan tillämpas inom ett flertal områden och situationer men exempli- fieras här med år.

Tabellen nedan visar exemplet i sin helhet:

Hur löser vi detta formeltekniskt sett? Jo, mha av en matrisformel som ser ut på följande sätt:

  • {=INDEX(B2:B9;MAX(OM(ÅR(A2:A9)=C2;RAD(INDIREKT
    ("1:"&RADER(A2:A9)));"")))}

Skapa dynamisk hyperlänklista
I detta tips demonstreras en teknik för att snabbt komma till en post i en större lista, oavsett i vilken cell posten återfinns i.

Följande förutsättningar gäller för exemplet:

  • I bladet "Blad1" finns en förteckning över enhetsnummer.
     
  • I bladet "Blad2" återfinns en tabell, i vilken dessa nummer kan förekomma i skilda celler. Denna tabell är dynamisk, poster kan läggas till eller tas bort.

I nedanstående bild visas uppställningen i "Blad2". Cellområdet C2:C5 är namngivet till "Nummer":

I nästa bild visas den färdiga förteckningen med hyperlänkar i "Blad1". Om ett enhetsnummer får ändrad position i listan i "Blad2" sker automatiskt uppdatering av berörd hyperlänk:

I B-kolumen ligger följande "knöliga" matrisformeln till grund för att erhålla ett enhetsnummer cellreferens:

  • {="[hyperlankar.xls]Blad2!"&ADRESS(MIN(OM(Nummer=
    Blad1!A2;RAD(Nummer);""));MIN(OM(Nummer=
    Blad1!A2;KOLUMN(Nummer);"")))}

Formeln kopieras nedåt i B-kolumnen. Namnet "Nummer" i formeln refererar till cellområdet: C2:C5 i "Blad2".

För att skapa hyperlänkarna i C-kolumnen används följande funktion:

  • =HYPERLÄNK(B2;A2)

Noterbart
Direkthjälpen i XL är direkt felaktig när det gäller syntaxen för hur vi skapar hyperlänkar inom en och samma arbetsbok.

Ange ingen basadress för hyperlänk i arbetsbokens egenskapfönster - De interna länkarna slutar att fungera om så sker!

Summera uppslagsvärden
Scenariot för detta tips är att vi vill summera flera värden utifrån flera uppslagsvärden.

Utgångspunkten är följande exempel:

Det första alternativet som står till buds är att ange flera LETARAD-funktioner i en och samma cell:

  • =LETARAD(E2;A2:C6;3;0)+LETARAD(E3;A2:C6;3;0)+
    LETARAD(E4;A2:C6;3;0)

Behöver vi inte använda en dylik formel ofta kan det vara en lösning men vid upprepade gånger blir dt mer tidskrävande.

Det andra alternativet - en matrisformel - kan därför vara tidsbesparande:

  • {=SUMMA(N(FÖRSKJUTNING(A2:C6;PASSA(E2:E4;
    FÖRSKJUTNING(A2:C6;;;;1))-1;2)))}

Månadsstatistik
I detta exempel belyses hur vi relativt enkelt mha av matrisformler kan sammanställa data på månadsbasis och därmed erhålla en översiktlig månadsstatistik.

För att erhålla summa per månad används följande formel:

  • {=SUMMA(OM((MÅNAD($A$2:$A$19)=RAD()-1)*LÄNGD
    ($B$2:$B$19);$B$2:$B$19;FALSKT))}

För de övriga kolumnvärdena ersätts SUMMA-funktionen med:

  • MEDEL
     
  • STDAV
     
  • MAX
     
  • MIN

Transponera tabeller
Har du funderat på hur vi kan vända på tabeller? Säkert har du stött på funktionen TRANSPONERA eller verktyget Transponera i dialogrutan "Klistra in special".

Här demonstreras ett annat förfaringssätt, som löser en del problem som de andra möjligheterna inte klarar av.

Exemplet i sin helhet visas i följande bild:

Som framgår av exemplet ska tabellen transponeras med det kravet att det ska vara ett mellanrum om en rad mellan posterna.

Området A2:D5 har döpts till Tabell.

Följande formel skapas i cellen F1 och kopieras nedåt i kolumnen:

  • =OM(REST(RAD()-RAD($A$1)+1;KOLUMNER(Tabell)+1)>0;INDEX
    (Tabell;1+HELTAL((RAD()-RAD($A$1)+1)/(KOLUMNER(Tabell)+1));
    REST(RAD()-RAD($A$1)+1;KOLUMNER(Tabell)+1));"")

Leta upp med INDIREKT
Här demonstreras en teknik för att leta upp värde med hjälp av funktionen INDIREKT.

Bilden nedan visar exemplet i sin helhet:

Följande namn används:

  • Kv1 - cellområde A2:B4
  • Kv 2 - cellområde A7:B9
  • Kv 3 - cellområde A12:B14
  • Kv 4 - cellområde A17:B19
  • Skv1 - cellområde B2:B4
  • Skv2 - cellområde B7:B9
  • Skv3 - cellområde B12:B14
  • Skv4 - cellområde B17:B19

Önskat tabellnamn väljs i en lista (dataverifiering) i cellen E1 och uppslagsvärdet väljs ur en lista i cell E2 (dito).

För att få fram det aktuella värdet i cellen E3 används följande formel:

  • =LETARAD($E$2;INDIREKT($E$1);2;0)

För att erhålla summan för aktuellt kvartal i cellen E4 används följande formel:

  • =SUMMA(INDIREKT("S"&E1))

Visa hela postens rad
I detta tips visas hur vi kan skapa förutsättningarna för att visa hela raden för en post med största respektive minsta värdet.

Bilden nedan visar exemplet i sin helhet:

För att erhålla hela raden för posten med den största omsättningen används följande grundformel i cellen F2:

  • =INDEX($A$1:$C$6;PASSA(MAX($B$1:$B$6);
    $B$1:$B$6;0);KOLUMN(A:A))

Denna kopieras till cellerna G2:H2

För att erhålla hela raden för posten med den minsta omsättningen används följande grundformel i cellen E2:

  • =INDEX($A$1:$C$6;PASSA(MIN($B$1:$B$6);$B$1:$B$6;0);
    KOLUMN(A:A))

Formeln kopieras till cellområdet G3:H3.

Skapa automatiskt löpnummer
Här visas hur vi mha av en formel kan skapa löpnummer för poster i en sorterad lista.

Bilden nedan visar tipset i sin helhet:

B-kolumnen kan med fördel döljas efter det att löpnumrerna har skapats.

Följande formel skapas i cellen A2 och kopieras nedåt i kolumn A

  • =OM(ÄRTAL(PASSA(B2;$B1:B1;0));INDEX($A1:A1;PASSA
    (B2;$B1:B1;0));MAX($A1:A1)+1)
Upp
Senast uppdaterad: 2005-10-05 © Dennis Wallentin