Hva vil vi gå igjennom?
Nedenfor har jeg tatt utgangspunkt i Microsoft Excel 2016, men forklaringene gjelder for alle tidligere versjoner av Excel også.
1. Enkel visualisering
«Et bilde sier mer enn tusen ord»: Når datamengden når en viss størrelse, kan det være vanskelig å se mønstre. Ved bruk av sparklines og betinget formatering kan du enkelt se hvor landet ligger, selv om du må forholde deg til tusenvis av datapunkter.
Sparklines
Sparklines er nyttig for å avdekke trender og avvik i en rad eller kolonne med data. Marker området du ønsker at sparklinen skal ta utgangspunkt i, trykk på Insert –> Sparklines –> velg ønsket type sparkline og hvilken celle den skal inn i, og trykk OK. Sparklinen kan formateres på forskjellige måter etter smak og behov.
Sparklines finner du ved å gå til «Insert» og se etter «Sparklines»-knappene til høyre.
Sparklines er et nyttig verktøy i Excel for å raskt avdekke trender og mønstre i dataserier. Marker en rad/kolonne med data, trykk på Insert og velg ønsket sparkline (linje, søyler eller win/loss), definer hvor den skal inn (i cellene N2-N4 i dette tilfellet) og trykk OK.
Betinget formatering: Highlights
Excel kan automatisk formatere celler som verdier som er større enn, mindre enn, i mellom eller lik en bestemt verdi, eller bestemt type tekst eller datoer. For eksempel, om du har en kolonne som inneholder antall produkter solgt, og du ønsker å se alle radene/kundene hvor det er solgt mer enn feks 10 enheter, velger du å formatere alle celler som inneholder tallet 10 eller høyere.
Betinget formatering kan brukes på mange måter i Excel avhengig av behov og fantasi. Forsøk forskjellige muligheter for å finne ut hva som funker.
Du har ofte bruk for å fjerne duplikater fra kolonner, og bruker gjerne betinget formatering for å kjapt se om det faktisk finnes duplikater. For eksempel, i en kundeliste fra en salgsdatabase er det en effektiv måte å sjekke at samme kunde ikke forekommer mer enn én gang.
Med betinget formatering i Excel kan du for eksempel få duplikater fargelagt automatisk. Her ser vi at «Erik» står to ganger i samme kolonne.
Betinget formatering: Søyler og ikoner
En annen variant er at hver eneste celle i et område du definerer får en liten søyle som viser cellens verdi relativt til de andre i det samme området. Dermed kan du kjapt se etter mønstre og avvik uten å måtte lage egne grafer.
Med enkel, betinget formatering kan du visualisere data i Excel og kjapt se etter avvik og interessante forekomster.
Pro Tip
Du kan bruke filtreringssfunksjonen i Excel til å både filtrere og sortere tabeller basert på formatering også!
Du kan filtrere og sortere tabeller basert på betinget formatering. I dette tilfellet er det mulig å filtrere eller sortere etter farger som definert av betinget formatering (nevnt ovenfor).
2. Rydde i data
Når du har med større tabeller å gjøre ønsker du kanskje å rydde opp i rader og kolonner, hente ut deler av informasjonen i en celle, og så videre. I disse tilfellene har du hatt stor nytte av LEFT, RIGHT, LEN, FIND og IFERROR.
Et enkelt eksempel er om du må hente ut kun postnummer fra en celler som består av postnummer og poststed. Hvis det står «0191 Oslo» i celle A2, og du vet at postnummeret alltid består av fire tegn, kan du med formelen =LEFT(A2;4) hente ut de fire første tegnene fra venstre – i dette tilfellet «0191».
Et annet praktisk eksempel er dersom du ønsker å hente kun fornavn eller etternavn fra en celle som inneholder et helt navn. Her kan lengden på fornavn og etternavn variere, slik at formelen ovenfor ikke vil fungere. La oss si at du har navnene «Ola Hedmarking», «Kari Bergenser» og «Peder Aas» – og du ønsker å hente ut kun etternavn. Da kan du med følgende formel hente ut etternavnene: =RIGHT(A2;LEN(A2)-FIND(» «;A2;1)). Denne formelen sier «hent ut det som står til høyre for mellomrommet». For å finne ut hvor mange tegn det er til høyre for mellomrommet må vi først finne ut hvor mange tegn cella består av totalt – det gjør vi med LEN-formelen – og deretter finner vi ut hvor mange tegn det er fra starten av cellen frem til mellomrommet. I praksis sier formelen «vis 8 tegn fra høyre». Dersom du vil ha kun fornavn blir formelen =LEFT(A2;FIND(» «;A2;1)).
Pro Tip
IFERROR er i mine øyne den kanskje nyttigste formelen i Excel. Det den sier er at «dersom formelen som skal kjøres returnerer en feil, så gjør følgende». For eksempel, hvis du har formel som dividerer en teller på en nevner, og nevneren i brøken mangler eller er en tekst (ikke en verdi), vil du få feilen «#DIV/0!». Ikke bare ser det stygt ut med feil, men det kan også skape følgefeil dersom andre beregninger i regnearket inkluderer cellen med feil. Løsningen er IFERROR: Skriver du =IFERROR(B2/C2;0) i stedet for =B2/C2, vil du få «0» i stedet «#DIV/0!» dersom feil oppstår.
Eksempler på hvordan du kan bruke IFERROR, LEFT, RIGHT, LEN og FIND i Excel for å finne data i celler. Her ser vi i B3 at det står «Nevner mangler» fordi formelen forsøker å dividere celle B1 og B2, men B2 inneholder ingenting, og dermed feiler formelen. På grunn av IFERROR vil det derfor stå «Nevner mangler». I celle B5 og B6 ser vi hvordan kombinasjonen av LEFT, RIGHT, LEN og FIND effektivt kan hente ut spesifikk informasjon fra celler.
3. Jobbe med datoer
I mange uttrekk vil du få med en kolonne med dato, det være seg registreringsdato, faktureringsdato eller fødselsdato. Ofte trenger du å gruppere datoer etter uker, måneder eller år. Excel har noen gode formler for dette. Hvis vi antar at det står et tidspunkt/dato i celle A2:
WEEKDAY(A2;[return_type]) forteller deg hvilken dag i uka datoen er. Merk at du må skrive WEEKDAY(A2;2) dersom du ønsker at uka skal telles fra mandag til søndag.
WEEKNUM(A2;[return_type]) forteller deg hvilken uke i året datoen tilhører. Merk at du må skrive WEEKNUM(A2;2) dersom du ønsker at uka skal telles fra mandag til søndag (som er det vanligste).
MONTH(A2) forteller deg hvilken måned datoen tilhører, fra 1 til 12.
YEAR(A2) forteller deg hvilket år datoen tilhører.
Dersom du ønsker at dag og måned beskrives med tekst, kan du bruke formelen TEXT(A2;format_text) der du bytter ut [format_text] med «ddd» eller «dddd» for å få dag, eller «mmm» eller «mmmm» for å få måned. Tre bokstaver gir forkortet versjon, feks «ons» eller «nov» mens fire bokstaver gir fullversjon, feks «onsdag» eller «november».
En annen favoritt er å bruke =TODAY(); Excel vil da alltid returnere dagens dato. Hvis du har fakturadato i celle A2 og ønsker å se antall dager som har passert siden fakturadato frem til i dag skriver du: =TODAY()-A2.
Pro Tip
Hurtigtasten for å skrive inn dagens dato er ctrl+Shift+komma, og hurtigtast for å skrive inn klokkeslett er ctrl+shift+punktum.
Eksempler på måter du kan formatere datoer i Excel som kan være til stor nytte.
4. Oppslag mellom tabeller
Ofte vil du trenge å slå sammen data fra flere tabeller. For eksempel om du har en kundeliste inkludert postadresse i én tabell og du ønsker å gruppere kundene etter fylke vil du trenge å legge til en kolonne med hvilket fylke hver kunde tilhører. Hvorfor gjøre dette manuelt når en formel i Excel gjør det automatisk for deg?
Hvis du ikke allerede har en tabell med postnummer og fylkesinformasjon må du lage en tabell som inneholder samtlige postnummer i Norge samt tilhørende fylke. Du kan finne en ved å laste ned fra http://www.bedreinnsikt.no/datasett/DimPostnummer.xlsx. Her finner du en tabell som heter DimPostnummer, hvor postnummer står i kolonne A, mens fylke står i kolonne D. I dette eksempelet legger vi tabellen DimPostnummer inn i regnearket med kundeinformasjon som et eget faneark.
I kundetabellen din må du deretter lage en ny kolonne som du kaller «Fylke», og så skriver du en formel som basert på postnummeret i den raden gjøres det et oppslag mot.
Den vanligste er å bruke VLOOKUP, der formelen blir =VLOOKUP(B2;DimPostnummer[#All];4;FALSE) under forutsetning av at postnummeret står i celle B2 og at kolonnen med fylkesnavn står i fjerde kolonne fra venstre. Formelen sier i praksis «slå opp postnummeret i celle B2 i første kolonne i DimPostnummer-tabellen og returner verdien som står i cellen i fjerde kolonne».
Pro Tip
Den proffere måten å gjøre det på er å bruke INDEX og MATCH i kombinasjon, der formelen blir INDEX(DimPostnummer[Fylke];MATCH(B2;DimPostnummer[Postnummer])). Formelen sier i praksis «returner navnet på fylket i kolonnen [Fylke] i DimPostnummer-tabellen ved å matche tallet som står i B2 mot tilsvarende tall som står i kolonnen [Postnummer]». Denne varianten har to fordeler: Rekkefølgen på kolonnen spiller ingen rolle – kolonnen med fylkesnavn kan godt stå både til høyre og til venstre for postnummer-kolonnen – og denne formelen er raskere dersom det er snakk om mange tusen rader.
I Excel kan både VLOOKUP og INDEX/MATCH brukes for å få samme resultat. Proffene velger sistnevnte.
5. Tabeller
Nesten uten unntak må du forholde deg til en eller annen tabell når du jobber i Excel. Eksempler inkluderer oversikter over kunder, produkter, priser og salg, timelister, fakturaoversikter, og så videre. Ofte ser jeg at det som behandles som en tabell i praksis er en matrise med informasjon, uten at området er definert som en tabell i Excel. Ved å markere det aktuelle området og trykke «Format as Table» –> velge et design –> huke av for «Table has headers» dersom du har inkludert overskriftene –> OK, så vil Excel forstå at området er en tabell og behandle det deretter.
Sånn ser en «tabell» gjerne ut i Excel før den blir definert som en tabell.
Fordelene med å definere et område som en tabell – i stedet for å bare ha tall stående fritt – er mange, men det viktigste er at alt blir mer dynamisk. Dersom en kolonne i tabellen består av en formel (feks antall * pris eller et oppslag på produktnummer mot produktnavn), vil formelen automatisk bli med inn i alle nye rader du legger til i tabellen.
I en tabell i Excel vil formlene se litt annerledes ut enn du er vant til.
I en tabell i Excel kan du definere at en formel skal gjelde for hele kolonnen. Når nye rader legges til, blir formelen med.
Med tabeller kan du enkelt sortere og filtrere informasjon etter behov. Og best av alt: En tabell er et utmerket utgangspunkt for en pivottabell eller grafer, slipper du å endre området som pivottabellen baserer seg på; dersom du legger til kolonner eller rader vil pivottabellen eller grafen automatisk fange opp det!
Pro Tip
Gi tabellen et fornuftig navn, så blir det enklere å finne frem i formlene og henvisningene. Dette gjør du ved å trykke på «Design»-fliken øverst, og deretter skrive inn navnet du vil gi tabellen under «Table Name» øverst til venstre. Velg et kort og beskrivende navn, så blir det enklere å finne frem i formler senere.
Sånn ser tabellen ut etter den er blitt opprettet.
Har du behov for kompetanse innenfor mer avansert analyse og business intelligence?
