Tekniker för att sammanställa data

I detta tips demonstreras:

• Hur vi skapar formler för tabeller med hänsyn till relativa och absoluta cellreferenser.
• Hur vi sammanställer data med matrisformler och pivottabell-verktyget.
   

• Summering av data.
• Beräkning av antal förekomster.
• Arbetsschema

Lösningsförslagen är ej inbördes rangordnade.

Se också:

• Summa.om & Antal.om
• Matriskalkylering
• Avancerat Filter
• Pivottabeller

Utgångspunkten för exemplet är följande tabelluppställning:

 

Summering

Här visas hur vi sammanställer summeringar av data, där kategori och kundnamn är det två styrande faktorerna.

Lösningsförslag 1

Här använder vi oss av en matrisformel enligt följande:

 

Formeln skapas i den vänstra cellen längst upp - F3 - och kopieras såväl nedåt i kolumnen som till höger i raden:

• {=SUMMA(($B$2:$B$9=$E3)*($A$2:$A$9=F$2)*$C$2:$C$9)}

 

Det som gör det möjligt att kopiera i kolumnen som i raden är att

• Kolumn E är en absolut kolumnreferens.
• Rad 2 är en absolut radreferens.

Lösningsförslag 2

Här använder vi oss av en matrisformel som ger samma resultat som ovan:

• =PRODUKTSUMMA(($B$2:$B$9=$E3)*($A$2:$A$9=F$2)*$C$2:$C$9)

Lösningsförslag 3

Här används en variant av PRODUKTSUMMA-funktionen, som formeltekniskt sett är mer konsekvent till hur den egentligen är tänkt att fungera än ovanstående tillämpning:

• =PRODUKTSUMMA(N($A$2:$A$9=F$2);N($B$2:$B$9=$E3);$C$2:$C$9)

Lösningsförslag 4

Här används pivottabell-verktyget och vi erhåller följande uppställning:

Antal

Här visas hur vi beräknar antal förekomster, där kategori och kundnamn är det två styrande faktorerna.

Samtliga lösningar här baseras på de formler som ges under Summering ovan.

Lösningsförslag 1

Här används en matrisformel enligt följande:

 

• {=SUMMA(($B$2:$B$9=$J2)*($A$2:$A$9=K$1))}

Lösningsförslag 2

Här används en ytterligare matrisformel som ger samma utfall:

• =PRODUKTSUMMA(($B$2:$B$9=$J2)*($A$2:$A$9=K$1))

Lösningsförslag 3

En formeltekniskt sett mer konsekvent användning av PRODUKTSUMMA-funktionen:

• =PRODUKTSUMMA(N($A$2:$A$9=K$1);N($B$2:$B$9=$J2))
   

Lösningsförslag 4

Här används beräkningsfunktionen "Antal" för variabeln "Belopp" och genererar följande uppställning:

Sammanfattning

För större datamängder kan Pivottabell-verktyget med fördel användas medan de andra teknikerna lämpar sig bäst för både mindre datamängder och där vi vill extrahera data för några variabler men inte alla.

Arbetsschema

Här demonstreras ett utökat exempel där vi ska sammanställa arbetad tid för varje person per pass och totalt.

Följande bild visar dels uppslagstabellen för pass och arbetstid samt arbetsschemat som fylls i:

I tabellen nedan sammanställs data per anställd och per pass:

 

Följande formel skapas i cell B31 och kopieras såväl i raderna som i kolumnerna:

• =PRODUKTSUMMA(($A$25:$A$27=$A31)*($B$25:$H$27=B$30))*LETARAD(B$30;$J$24:$K$27;2;0)

 

Alternativt kan följande formler användas:

• {=SUMMA(($A$25:$A$27=$A31)*($B$25:$H$27=B$30))*LETARAD(B$30;$J$24:$K$27;2;0)}