Publisert av: pertm | 19.09.09

Automatisk oppsummering av data

Dette er noe jeg har likt å gjøre hvordan få sammendrag fra data i Excel. Vanligvis har jeg da minst to regneark minst et med data og minst et med oppsummering, men det kan være oppsummering på mange måter.

Hva er lett å oppsummere?

Ikke alle ting er like lett å oppsummere her er noe jeg synes er enkelt å få oppsummere, og det kan være praktisk å oppsummere. Her er mitt forslag til ting som kan være nyttig

  • Sum
  • Størst
  • Minst
  • Antall
  • Gjennomsnitt
  • N største Bare for 2007 versjon
  • N minste Bare for 2007 versjon

Hva en summerer opp er avhengig av rådata hva slags data er det vi har sammendrag av. En kan få mest ut av data som er på et tallformat selv om tekst også er nyttig. Bare vær klar over at datoer som kanskje ikke ser ut som tall fortsatt er det. Skriver du «5 feb» i en celle blir dette formatert til 39849, vel så lenge det året er 2009. Skriver en «30 feb» så blir dette tekst siden det ikke eksisterer og «29 feb» blir bare om det et år med skuddår eller en skriver inn «29 feb 1900», som er en liten bug i alle Windows versjoner av Excel.

Et eksempel på bruk sykkeldagbok

Siden jeg sykler en del tenkte jeg det kan være greit å ha et eksempel som jeg har selv, men dette er en del forenklet fra hva jeg har gjort.

Ark1 med data

Her er et forsalg til hva en kan ha

  • A: dato
  • B: lengde
  • C: tid brukt
  • D: Snitt fart
  • E: Maks fart
  • F: Sykkel brukt
  • G: Rute brukt
  • H: Kommentar

Data i kolonne A – E får jeg fra sykkelcomputeren etter en tur, har en mer avansert sykkelcomputer kan en ha flere data

Dataene vil være fra rad 2 og nedover, de trenger ikke være i kronologisk rekkefølge, men det er nok praktisk å ha dem kronologisk om en skriver inn etterhvert.

Ark2 med oppsummering

Ark2 skal vi ha oppsummering etter måned og har disse kolonnene:

  • A: måned og år(Det er vanlig dato formatert slik at man ikke ser dagen)
  • B: lengde
  • C: tid brukt
  • D: Snitt fart totalt
  • E: Maks fart måned
  • F: Høyeste snittfart
  • G: Antall turer
  • H: Lengste tur
  • I: Snitt lengde
  • J: Lengde sykkel1
  • K: Lengde sykkel2

Det første jeg må velge er hva jeg skal ta oppsummering i forhold til? Jeg tenker i forhold til måned, men det er mange andre muligheter.

I kolonne A starter man å få dato av månedene der informasjonen er, dette er en vanlig dato men vi viser ikke dagen. For 2009 kan det bli slik starter i A2

  • 1.1.2009 vises Jan 2009
  • 1.2.2009 vises Feb 2009
  • osv

I kolonnene B-L eller lenger kan man få formlene. I kolonne B kan denne formelen settes inn i B2

{=SUMMER(HVIS(ÅR($A2)=ÅR(‘Ark1’!$A$2:$A$101);(HVIS(MÅNED($A2)=MÅNED(‘Ark1′!$A$2:$A$101);’Ark1’!$B2:$B101))))}

Den vil kunne kopieres nedover og til C kolonnen siden dataene vi er ute etter er i kolonner ved siden av hverandre. Dollar tegn gjør at adresse til en celle ikke flyttes når en formel forandres.

OBS Dette er en array formel så ikke skriv inn klamme parentes, for å få formelen i gang må det ikke bruke vanlig ENTER, men SHIT + ALT + ENTER

I kolonne D så kan man ikke kopiere formelen fra B & C siden det ville gitt summen av alle snittfart og ville vært ubrukelig, selv om man delte på antallet ville det vært bortkastet så D2 blir slik.

=B2/C2/24

En deler på 24 siden klokke formatet i Excel gir 1 er et 24timer.  Hvis det kopieres til celler uten data der C2 er 0 så vil denne formelen gi feil. Da kan en sette opp en kontrol som:

=HVISFEIL(B2/C2/24;0) Bare for 2007 versjon

=HVIS(C2=0;0;B2/C2/24)

Kolonne E, F, G, H og I blir tilsvarende som kolonne B, men en må bruke en annen formel enn SUMMER her er formlene, STØRST, ANTALL, GJENNOMSNITT noen som kan brukes. En må da sørge for at det blir brukt på riktig kolonner i Ark1 og Ark2. Det er da bruk av dollar tegnet kan være til stor hjelp siden det låser referansen når det kopieres.

For Kolonne J og K så må en ha et navn på sykkelen, en kan ha det i formelen, men det er lite praktisk om en får en ny sykkel å måtte forandre alle formlene. Da blir formelen for J2 nesten lik den i B2, jeg tenker en har sykkelnavn i J1 og så mange kolonner som en har sykkelen.

=SUMMER(HVIS(ÅR($A2)=ÅR(‘Ark1’!$A$2:$A$101);(HVIS(MÅNED($A2)=MÅNED(‘Ark1′!$A$2:$A$101);HVIS(K$1=’Ark1′!$F$2:$F$101;’Ark1’!$B2:$B101)))))

Andre formler som kan brukes er selvsagt MINST, selv om jeg tror ikke jeg har funnet noen god bruk for det i min sykkeldagbok så kan dette brukes til mye annet enn lengder og tider.

Bare for Excel 2007 er formlene N. MINST og N.STØRST som plukker ut det nte laveste/høyeste tallet. Har du ikke 2007 så er dette noe som er vanskelig å få til på tidligere versjoner.

Uten Array formler

Det er mulig men det fører til at en gjerne må ha mange celler eller for å gjøre det mer oversiktlig et ekstra ark bare for mellomregning. Det finnes noen triks som kan hjelpe til med å få det uten. F.eks i Ark1 kan en ha en skjult kolonne som gir år og måned slik

200901

200902

Formelen som gir dette blir ikke så veldig vanskelig om a2 er en dato:

=ÅR(a2)*100+MÅNED(a2)

For sum, og antall og antall er det enkelt å bruke formelene SUMMERHVIS og ANTALL.HVIS og bruke som kriterium for oppsummering samme verdi i en celle i Ark2. For de andre formelene er det langt mer komplisert og en trenger mange flere celler med omregning for å få fram resultatet.


Legg igjen en kommentar

Fyll inn i feltene under, eller klikk på et ikon for å logge inn:

WordPress.com-logo

Du kommenterer med bruk av din WordPress.com konto. Logg ut / Endre )

Twitter picture

Du kommenterer med bruk av din Twitter konto. Logg ut / Endre )

Facebookbilde

Du kommenterer med bruk av din Facebook konto. Logg ut / Endre )

Google+ photo

Du kommenterer med bruk av din Google+ konto. Logg ut / Endre )

Kobler til %s

Kategorier

%d bloggers like this: