Hur du Tvättar Data i Excel och 4 exempel på Absolut och Relativ Cellreferens
Tvätta data! Låter kanske inte så kul men ett måste för att kunna använda den rådata vi har fått fram som vårt analys- eller rapportunderlag. Detta kommer vi titta på under de kommande inläggen och det behöver inte vara så krångligt eller tidsödande om man kan knepen?.
Vill du läsa inlägget på engelska? Klicka här
Rådata
Rådata är ett begrepp som kommer från att man fått obehandlad data från något system. Det kan vara ett ERP (ekonomi) system, BI-system eller tex ett CRM system där man lagrar kundinformation.
Datan kan komma i alla former och ha formatering som kanske inte alls passar våra Excelmodeller. Datan behöver ”tvättas” eller bearbetas innan den går att nyttja i en rapport. Det kan handla om att justera rubriker, kolumner eller rader så att man kan göra en pivottabell eller med en SUMMA.OM-formel länka till ett standardformat. I BI-systemet eller ERP systemet kan man med fördel skapa en rådata rapport som är klar att nyttja som underlag till tex en ledningsrapport så att man inte behöver tvätta innehållet varje månad. Annars brukar ofta problemen, i det man får ut från systemet, vara:
- Textformat i rådatan
- Blanka celler
- Centrerade kolumner
- Element etiketterna är inte upprepade
- Punkt istället för komma eller komma vid tusental
- Flera faktorer i samma cell istället för i individuella kolumner
- Tidsformatet är felaktigt
Skit in…skit ut
Om man inte lägger ner tid eller sätter processer för hur man handskas med rådatan kommer det påverka kvaliteten på det man får ut i slutändan. Genom att kartlägga de olika delarna som behöver justeras kan man reducera tiden som krävs. Det bästa är att sätta upp rapporter i, låt oss säga, BI-systemet som är så pass användbara som möjligt så att det är så lite ”tvättande” som möjligt som behövs. Sen är det vid varje utdrag bara att följa protokollet för vad man behöver åtgärda så att det går snabbt och med automatik. Eventuellt kan man ha en färdig modell som behandlar rådatan.
Om man inte behandlar rådatan korrekt så kommer analys eller resultatrapporten bli mer eller mindre fel. För att illustrera hur snett det kan gå så har jag ett exempel nedan. Ser ni felet…? Cell C5 har en punkt istället för ett komma vilket innebär att Q1’s totala intäkter saknar 8% då de 100 tkr inte räknas med. Man vill inte komma med en sådan miss till ett ledningsgruppsmöte med CEO! Sitter man med siffror dagarna i ända kan man lätt bli sifferblind men en CEO kommer definitivt ifrågasätta datan bakom rapporten☹.
Vi fortsätter att tvätta data i nästa inlägg …
Absolut och relativ cellreferens
Låter krångligt eller hur? Var inte orolig, det är skitenkelt.✌ Utgångspunkten är att en cellreferens är en relativ referens, vilket betyder att referensen är föränderlig och inte absolut eller med andra ord definitiv.
Se lektionen som lär dig tekniken med absoluta och relativa cellreferenser. Från Learnesys kurs Excelfunktioner Fördjupning
Utan justering om du kopierar en formel som plockar upp data från en cell (A1) och klistrar in en kolumn åt höger, så kommer formeln leta efter värdet i cell B2…vilket blir fel.
Om du vill att cellreferensen ska vara absolut, dvs att formeln ska fortsätta plocka upp värdet från A1 så ”låser” man cellen med hjälp av ett dollartecken ($) via F4.
Att arbeta med absoluta cellreferenser och lära sig hur man låser celler definitivt, horisontellt eller vertikalt kan göra dina beräkningar mycket lättare.
- F4-knappen är den knapp som kan göra underverk för dig i ditt arbete.
- Ett klick på F4: referensen är absolut och cellen är låst i alla riktningar ($A$2)
- Två klick på F4: referensen blandas och cellen låses i horisontell riktning (A$2)
- Tre klick på F4: referensen blandas och cellen låses i vertikal riktning ($A2)
- Inget klick på F4: referensen är relativ och cellen är inte låst i någon riktning (A2)
Exempel på nyttan relativ cellreferens ser ni i nedan analys. Här vill vi att formeln skall löpa på både horisontellt och vertikalt då vi vill beräkna alla månader och konton mot varandra. Formeln skrevs in i cell J5 och kopierades sedan ner hela vägen till L25.
Användningen för relativ + absolut (blandad) cellreferens med horisontell låsning ser ni nedan. Här vill vi beräkna kostnadernas del av månadsintäkten. Hur vi än kopierar formeln så är samma intäktsrader låsta.
Tittar man på hur jag arbetar själv så tror jag att jag använder relativ + absolut cellreferens med vertikal låsning oftast. Nyttan av denna funktion är mycket stor vid beräkningar där kolumnen ska vara densamma såsom nedan där vi kalkylerar månadens andel av kvartalets total.
Nästa inlägg
I detta inlägg har vi pratat om nyttan av absolut och relativ cellreferens vid beräkningar och analyser samt vikten att behandla datan innan den används. I nästa inlägg kommer jag presentera praktiska exempel av att tvätta data.
Välkomna att connecta med mig på LinkedIn samt gå in på Learnesys hemsida för mer information.
Carl Stiller i samarbete med Learnesy
