Hej på er, hoppas ni har en trevlig arbetsvecka. Detta inlägg går i pivottabellens tecken samt behandlar beräknade fält och element plus ett tips avseende motsvarande i Power Pivot.

Beräknade fält är en funktion som är perfekt att använda när man har stor mängder data och där vi inte behöver eller vill lägga till kolumner för beräkningar i rådatan. Vi vill göra dessa beräkningar direkt i pivottabellen vilket gör att de nya fälten kan vridas runt precis som övriga faktorer i pivottabellen. Detta är speciellt användbart om vi skapat en tabell av rådatan som uppdateras varje månad….då behöver vi bara uppdatera vår pivot istället för att justera rådatan. Jag använder beräknade fält till främst:

• Säljanalyser
• Marginalanalyser
• Beräkning av deltan eller förändringar
• Kontoavstämningar
• Indexberäkningar

Se nedan för en rad exempel på beräkningar som är användbara inom analytiskt arbete.

Låt oss ta ett exempel där vi har en dataflik med försäljningsinformation. Vi kan med fördel göra denna till en tabell innan vi skapar vår pivot. Vi vill nu se intäkten per modell för 2018 respektive 2019 vilket inte finns i datan utan här finner vi enbart antal, pris samt KSV per modell och produktkategori.

1. Vi skapar först en tom pivot med enbart modell samt kategori och går till menyfliken ”Analysera för pivottabell” där vi finner nedan val….där vi väljer ”Beräkna fält”.
2. Vi adderar ett namn för fältet vi vill beräkna
3. Hoppar ner till formelfältet där vi multiplicerar pris*antal och gör detta för både 2018 och 2019

Om vi vill beräkna deltat avseende bruttomarginalen så skriver vi den lite längre formeln där vi räknar fram marginalen för de två åren och sedan beräknar differensen.

= ((‘2019 pris’-‘2019 KSV’)*’2019 antal’)-((‘2018 pris’-‘2018 KSV’)*’2018 antal’)

Om vi skulle göra denna beräkning i databladet så skulle det bli något mer komplicerat varmed vi nu arbetat effektivt med Excels funktioner. Betänk om vi skulle ha en SQL-databas kopplad till datafliken vilket jag tex hade på Apoteksgruppen där jag arbetade med säljstatistik. Innan jag här började nyttja beräknade fält kunde applikationen hänga sig flera gånger för att det blev för mycket att processa.

Vi kan även dra ut en lista på formler för att verifiera logiken. Vi ser här att vi i exemplet den nya formeln nummer 3 bygger på de två första formlerna vi satte ihop.

Vi kan även beräkna ökningen eller minskningen månad för månad för tex intäkter enligt nedan. Vi går då till analysera funktionen och väljer ”Fält, objekt och uppsättningar:

Vi erhåller här en procentuell ökning per månad. Denna typ av beräkning kan även appliceras på ackumulerad data eller ”Year-on-year”. Man kan även göra den på avvikelseanalys utfall mot budget.

Ett användbart trick är att om man inte vill att det ska stå ”Summa av” så kan man ställa sig i namnet och skriva tex Januari (obs ej Jan som redan finns).

Detta gör att rapporten ser betydligt mer trevlig och presentabel ut:

I detta fall har vi säljdata i en tabell där vi har definierat om en modell var såld på kampanj eller till ordinarie pris. Då vi har denna indelning och kategorisering av säljtyp behöver vi nyttja beräknat element.

Vi fångar upp faktorerna vi behöver till beräkningen i den högra rutan som definierar fältens element och skriver vår formel enligt bilden. Vi får nedan resultat!

Normalt brukar jag vid månadsbokslut stämma av konton, ERP och BI system emellan, genom PRODUKTSUMMA eller LETARAD. Nedan variant är dock bra om man har en stor mängd data såsom 30 st kostnadsställen och behöver kunna vrida och vända på analysen.

Vi har i nedan fall endast två kostnadsställen samt inhämtat balanser i tabellform från ERP systemet samt BI systemet. Vi vill nu lägga till ett delta mellan dessa system efter att vi satt upp pivottabellen. OBSERVERA att för att kunna lägga till ett nytt element i fältet för system så behöver jag i detta exempel stå i cell I4 i pivottabellen, annars känner Excel inte av att det är här som en formel ska in.

Vi klickar på OK och det nya elementet läggs till i fältet för system samt i pivottabellen. Vi kan nu filtrera på kostnadsställen och göra vår avstämning. Vi ser att vårt exempel har flera avvikelser där balanserna miss stämmer samt där det i vissa fall inte finns några balanser i BI systemet. Dags att kontakta redovisningsavdelningen samt kontrollera exporten från ERP till BI systemet! ?

Om jag har en databas med produkter samt produktkategorier, såsom i exempel 1 ovan, så vill jag naturligtvis kunna se beräkningarna inte bara per produkt utan även per kategori. Vi kan då inte nyttja beräknade fält i Excel utan behöver då använda oss av Power Pivot och DAX-formler. Anledningen är att totalerna inte kommer bli korrekt per kategori då Excel kommer summera ihop alla priser och multiplicera med alla antal i ett svep och således bli en för stor total. Vi kommer inte gå igenom hur man använder Power Pivot i detta inlägg men jag tycker att det är värt att nämna hur man behöver arbeta för att komma runt denna problematik. Det inte är ovanligt att man sitter som ett frågetecken ett bra tag innan man ger upp beräkningen i Excel.

Efter att ha laddat upp den data som behövs och ha skapat relationer i Excel filen, så behöver man sen gå till det som kallas för mått under Power Pivot fliken…man arbetar alltså inte här med analys för pivottabell. Här skriver man in =SUMX plus formeln för till exempel marginalberäkningen.

Som ni ser ovan så summerar alla individuella modeller ihop till korrekt kategori-total för både försäljning samt GP?.

Vi har idag gått igenom beräknade fält samt element i pivottabeller samt hur det kan effektivisera dina analyser. 

Välkomna att connecta med mig på Linkedin samt gå in på Learnesys hemsida för mer information.

Carl Stiller i samarbete med Learnesy

Är du nyfiken på att lära dig mer har vi en onlinekurs i pivottabeller. Pivottabeller Fördjupning

Läs inlägget på Engelska här