Pivottabeller och diagram – ”miniskola” – avancerad – Del 1 av 2
Hej igen,
Som jag förstår dig vill du få hjälp med följande frågeställningar.
- ” Det här sättet med beräknande fält och beräknande element verkar inte vara kompatibelt med mina järnceller....”
- ”Det ser ju snyggt ut med att jag får Summan av Calc mean "gratis" och jag kan ställa om till Medel av Calc mean. Men de går ju inte att ha med i beräkningarna.”
- ”Till slut var jag inne på att jag behöver räkna ut MV i en vanlig tabell och sedan försöka att hämta data från två olika tabeller, men jag kanske bara håller på och krånglar till allt?”
- ”Så jag är så tacksam om du har ytterligarer något tips på hur jag ska lägga in i beräknande fält/beräknande element för att få fram bias och som dessutom fungerar när ytterligare kvartal läggs till i RådataL?”
Vi börjar med en kort sammanfattning innan ”miniskola avancerad” påbörjas.
- Ett beräknat fält är samma sak som en kolumn med värden som inte finns tillgänglig i datakällan, men som du vill presentera i din pivottabell. Du skapar ett nytt beräknat fält och beräknar värdena baserat på andra fält i pivottabellen. Exempelvis kan man räkna ut en bonus baserad på en procentsats av försäjningsvolymen. Ett beräknat element är en post i en kolumn som du vill presentera, men som inte finns i datakällan. Exempelvis en post som heter ”Q1” som summerar värdena för månaderna januari, februari och mars i samma fält.
- Det är sant. Man kan tyvärr inte använda ”Totalsumma” eller ”Delsumma” i ett beräknat fält eller element i en vanlig pivottabell för det krävs PowerPivot/PowerQuery och DAX.
- Du har stött på en av begränsningarna med vanliga pivottabeller. Det är tyvärr inte möjligt att kombinera två olika tabeller/cellområden i en och samma pivottabell. Då måste man använda Power Pivot och Power Query med DAX – vilket är ”överkurs deluxe”.
- Det är huvudsakligen denna frågeställning som denna ”miniskola avancerad” kommer handla om.
Pivot-cache
Till att börja med måste vi gå igenom hur en pivottabell fungerar ”bakom kulisserna”. När man skapar en pivottabell gör Excel en kopia av cellområdet (datakällan) man valt och sparar denna kopia både i minnet samt som standard även på disk när arbetsboken sparas. Detta medför att arbetsboken växer i storlek. Om man från början har en väldigt stor arbetsbok, som efter man skapat sin pivottabell blir ohanterligt stor kan man om man vill ändra inställningarna på datafliken i pivottabellalternativ, så att Excel inte sparar pivot-cachen på disk.
Dena inställning medför att det kan ta något längre tid att öppna arbetsboken eftersom Excel måste återskapa pivot-cachen varje gång man öppnar den.
Pivot-cache - begränsningar
Excel skapar som standard enbart en pivot-cache per datakälla. Detta är praktisk ur resurssynpunkt eftersom man sparar både minne och diskutrymme. Nackdelen med att det endast skapas en cache per datakälla är att om man använder någon av nedan funktioner så delas dessa mellan pivottabellerna.
- Alla grupperingar som tillämpas på datum eller andra fält tillämpas i alla pivottabeller som delar cache.
- Alla beräknade fält och element skapas i alla pivottabeller som delar cache.
- Vid uppdatering av en pivottabell uppdateras alla pivottabeller som delar cache.
Jag kan åskådliggöra detta problem genom att skapa en ny pivottabell i vårat befintliga projekt. Så här ser pivottabellen för ”%CV ALAT Låg nivå” ut.
Vi skapade tidigare det beräknade elementet ”%kontrollgräns*2” med tillhörande formel; =0,04 (4,0%). Om man nu skapar en ny pivottabell baserad på tabellen ”Rådata”och lägger in fältet ”Analyzer” som radetikett blir resultatet följande.
Det beräknade elementet ”%kontrollgräns*2” dyker upp som radetikett även i den nya tabellen på grund av att Pivot-cachen delas av båda tabellerna eftersom båda använder samma datakälla.
I Excel kan man inte se hur många pivot-cacher det finns. Man kan däremot använda VBA för att få fram den informationen. Starta först VBA-editorn med ALT+F11. Öppna direktfönstret med CTRL+G. I direktfönstret skriv in följande och tryck ENTER.
Som synes har jag nu endast en pivot-cache i min arbetsbok. Båda pivottabellerna delar denna cache och därför återfinns det beräknade elementet ”%kontrollgräns*2” även i min nya pivottabell – fast jag kanske inte vill använda samma beräknade element i denna tabell.
Separata pivot-cacher med samma datakälla
Det finns olika sätt att skapa en ny pivottabell med en separat cache. Det överlägset enklaste sättet (enligt min mening) är att använda den gamla Pivottabellguiden som fanns i Excel 2003, men som är dold i Excel 2007 och senare. Klicka i en tom cell på arbetsbladet är du vill infoga den nya pivottabellen och använd tangentbordskombinationen ALT+D+P för att starta Pivottabellguiden.
På sida 1 av 3 välj enligt ovan och klicka på ”Nästa”.
I steg 2 av 3 ange tabellnamnet ”Rådata” och klicka på ”Nästa”.
Nu dyker det upp ett varningsmeddelande.
Klickar man på ”Ja” skapar Excel den nya pivottabellen baserat på samma cache som den första. Väljer man däremot ”Nej” skapas en separat cache för den nya pivottabellen, vilket är precis vad vi vill göra.
I steg 3 av 3 välj du var vill placera pivottabellen i din arbetsbok, klicka sedan på ”Slutför”.
Nu kan vi återgå till VBA-editorn och kontrollera antalet pivot-cacher igen.
Som synes har vi nu två pivot-cacher - en för varje tabell, vilket är precis vad vi ville åstadkomma. När vi lägger in fältet ”Analyzer” som radetikett i den nyskapade tabellen kommer det beräknade elementet ”%kontrollgräns*2” inte finnas med (se bild nedan) eftersom det elementet enbart finns i den första cachen, som tillhör till den första pivotabellen vi skapade.
fortsättning följer strax...