Fördelar med Pivot by Data Model

När du bygger en pivottabell i Excel, i den allra första dialogrutan, där vi ombeds ställa in det initiala intervallet och välja en plats för att infoga pivottabellen, finns det en oansenlig men mycket viktig kryssruta nedan – Lägg till dessa data i datamodellen (Lägg till denna data till datamodell) och, lite högre, omkopplaren Använd den här bokens datamodell (Använd datamodellen för denna arbetsbok):

Tyvärr förstår många användare som har varit bekanta med pivottabeller länge och framgångsrikt använder dem i sitt arbete ibland inte riktigt innebörden av dessa alternativ och använder dem aldrig. Och förgäves. Att skapa en pivottabell för datamodellen ger oss trots allt flera mycket viktiga fördelar jämfört med den klassiska Excel-pivottabellen.

Men innan vi överväger dessa "bullar" på nära håll, låt oss först förstå vad denna datamodell i själva verket är?

Vad är en datamodell

Datamodell (förkortat MD eller DM = Data Model) är ett speciellt område inuti en Excel-fil där du kan lagra tabelldata – en eller flera tabeller länkade, om så önskas, till varandra. I själva verket är detta en liten databas (OLAP-kub) inbäddad i en Excel-arbetsbok. Jämfört med den klassiska lagringen av data i form av vanliga (eller smarta) tabeller på själva Excel-ark, har datamodellen flera betydande fördelar:

• Tabeller kan vara upp till 2 miljarder rader, och ett Excel-ark rymmer lite mer än 1 miljon.
• Trots den gigantiska storleken utförs behandlingen av sådana tabeller (filtrering, sortering, beräkningar på dem, byggnadssammanfattning etc.) väldigt snabbt Mycket snabbare än Excel själv.
• Med data i modellen kan du utföra ytterligare (om så önskas, mycket komplexa) beräkningar med hjälp av inbyggt DAX-språk.
  
• All information som laddas in i datamodellen är mycket starkt komprimerad med hjälp av en speciell inbyggd arkivering och ganska måttligt ökar storleken på den ursprungliga Excel-filen.
  

Modellen hanteras och beräknas av ett speciellt tillägg inbyggt i Microsoft Excel – PowerPivotsom jag redan har skrivit om. För att aktivera det, på fliken utvecklare klick COM-tillägg (Utvecklare – COM-tillägg) och markera lämplig ruta:

Om flikar utvecklare (Utvecklaren)du kan inte se det på bandet, du kan slå på det genom Arkiv – Alternativ – Ribbon Setup (Arkiv — Alternativ — Anpassa menyfliksområdet). Om du i fönstret som visas ovan i listan över COM-tillägg inte har Power Pivot, så ingår det inte i din version av Microsoft Office 🙁

På fliken Power Pivot som visas kommer det att finnas en stor ljusgrön knapp Verksamhetsledningen (Klara av), klicka på vilket öppnar Power Pivot-fönstret överst i Excel, där vi kommer att se innehållet i datamodellen för den aktuella boken:

En viktig anmärkning på vägen: en Excel-arbetsbok kan bara innehålla en datamodell.

Ladda tabeller i datamodellen

För att ladda data till modellen, förvandlar vi först tabellen till en dynamisk "smart" kortkommando ctrl+T och ge det ett vänligt namn på fliken Konstruktör (Design). Detta är ett obligatoriskt steg.

Sedan kan du använda någon av de tre metoderna att välja mellan:

• Tryck på knappen Lägg till modell (Lägg till i datamodell) fliken PowerPivot fliken Hem (Hem).
  
• Att välja lag Infoga – Pivottabell (Infoga — pivottabell) och slå på kryssrutan Lägg till dessa data i datamodellen (Lägg till denna data i datamodellen). I det här fallet, enligt data som laddats in i modellen, byggs också en pivottabell omedelbart.
  
• På fliken Avancerat Data (Datum) klicka på knappen Från tabell/sortiment (Från tabell/intervall)för att ladda vår tabell i Power Query-redigeraren. Denna väg är den längsta, men om så önskas kan du här utföra ytterligare datarensning, redigering och alla typer av transformationer, där Power Query är mycket stark.

  Sedan laddas kammade data upp till modellen med kommandot Hem — Stäng och ladda — Stäng och ladda in... (Hem — Stäng&Ladda — Stäng&Ladda till...). Välj alternativet i fönstret som öppnas Skapa bara en anslutning (Skapa bara anslutning) och, viktigast av allt, sätta en bock Lägg till dessa data i datamodellen (Lägg till denna data i datamodellen).
  

Vi bygger en sammanfattning av Datamodellen

För att bygga en sammanfattande datamodell kan du använda någon av tre metoder:

• tryck på knappen Översiktstabell (pivottabell) i Power Pivot-fönstret.
• Välj kommandon i Excel Infoga – Pivottabell och växla till läge Använd den här bokens datamodell (Infoga — Pivottabell — Använd denna arbetsboks datamodell).
• Att välja lag Infoga – Pivottabell (Infoga — pivottabell) och slå på kryssrutan Lägg till dessa data i datamodellen (Lägg till denna data i datamodellen). Den aktuella "smarta" tabellen kommer att laddas in i modellen och en sammanfattningstabell kommer att byggas för hela modellen.

Nu när vi har listat ut hur man laddar in data i datamodellen och bygger en sammanfattning av den, låt oss utforska fördelarna och fördelarna som detta ger oss.

Fördel 1: Relationer mellan tabeller utan att använda formler

En vanlig sammanfattning kan endast skapas med hjälp av data från en källtabell. Om du har flera av dem, till exempel försäljning, prislista, kundregister, avtalsregister etc., så måste du först samla in data från alla tabeller till en med funktioner som SÖKNING (VISNING), INDEX (INDEX), MER EXPONERAD (MATCH), SUMMESLIMN (SUMIFS) och liknande. Detta är långt, tråkigt och driver din Excel till en "tanke" med en stor mängd data.

När det gäller en sammanfattning av datamodellen är allt mycket enklare. Det räcker att ställa in relationer mellan tabeller en gång i Power Pivot-fönstret – och det är klart. För att göra detta, på fliken PowerPivot tryck på knappen Verksamhetsledningen (Klara av) och sedan i fönstret som visas – knappen Diagramvy (Diagramvy). Det återstår att dra vanliga (nyckel) kolumnnamn (fält) mellan tabeller för att skapa länkar:

Efter det, i sammanfattningen för datamodellen, kan du slänga i sammanfattningsområdet (rader, kolumner, filter, värden) alla fält från alla relaterade tabeller - allt kommer att länkas och beräknas automatiskt:

Fördel 2: Räkna unika värden

En vanlig pivottabell ger oss möjlighet att välja en av flera inbyggda beräkningsfunktioner: summa, medelvärde, antal, minimum, maximum, etc. I datamodellsammanfattningen läggs en mycket användbar funktion till denna standardlista för att räkna antal unika (icke-repeterande värden). Med dess hjälp kan du till exempel enkelt räkna antalet unika varor (sortiment) som vi säljer i varje stad.

Högerklicka på fältet – kommando Alternativ för värdefält och på fliken Drift Välja Antal olika element (Distinkt antal):

Fördel 3: Anpassade DAX-formler

Ibland måste man utföra olika ytterligare beräkningar i pivottabeller. I vanliga sammanställningar görs detta med hjälp av beräknade fält och objekt, medan datamodellsammanfattningen använder mått på ett speciellt DAX-språk (DAX = Data Analysis Expressions).

För att skapa ett mått, välj på fliken PowerPivot Kommando Åtgärder – Skapa mått (Åtgärder — Nytt mått) eller högerklicka bara på tabellen i listan Pivotfält och välj Lägg till mått (Lägg till mått) i snabbmenyn:

I fönstret som öppnas ställer du in:

• Tabellnamndär det skapade måttet kommer att lagras.
• Mätnamn – vilket namn du förstår för det nya fältet.
• Beskrivning - valfritt.
• Formel – det viktigaste, för här går vi antingen in manuellt eller klickar på knappen fx och välj en DAX-funktion från listan, som ska beräkna resultatet när vi sedan kastar vårt mått i värdeområdet.
• I den nedre delen av fönstret kan du direkt ställa in talformatet för måttet i listan Kategori.
  

DAX-språket är inte alltid lätt att förstå eftersom det inte fungerar med individuella värden, utan med hela kolumner och tabeller, dvs kräver en viss omstrukturering av tänkandet efter de klassiska Excel-formlerna. Det är dock värt det, eftersom kraften i dess förmåga att bearbeta stora mängder data är svår att överskatta.

Fördel 4: Anpassade fälthierarkier

När du skapar standardrapporter måste du ofta kasta samma kombinationer av fält i pivottabeller i en given sekvens, till exempel År-kvartal-månad-dag, eller Kategori-Produkt, eller Land-Stad-Kund etc. I datamodellsammanfattningen löses detta problem enkelt genom att skapa en egen hierarkier — anpassade fältuppsättningar.

I Power Pivot-fönstret byter du till sjökortsläge med knappen Diagramvy fliken Hem (Hem — diagramvy), välj med ctrl önskade fält och högerklicka på dem. Kontextmenyn kommer att innehålla kommandot Skapa hierarki (Skapa hierarki):

Den skapade hierarkin kan döpas om och dras in i de obligatoriska fälten med musen, så att de senare i en rörelse kan kastas in i sammanfattningen:

Fördel 5: Anpassade stenciler

Om du fortsätter med idén i föregående stycke, i sammanfattningen av datamodellen, kan du också skapa dina egna uppsättningar av element för varje fält. Till exempel, från hela listan över städer, kan du enkelt skapa en uppsättning av endast de som är inom ditt ansvarsområde. Eller samla bara dina kunder, dina varor etc. i en speciell uppsättning.

För att göra detta, på fliken Pivottabellanalys i rullgardinsmenyn Fält, objekt och uppsättningar det finns motsvarande kommandon (Analysera — Fields, Items & sets — Skapa set baserat på rad-/kolumnobjekt):

I fönstret som öppnas kan du selektivt ta bort, lägga till eller ändra placeringen av alla element och spara den resulterande uppsättningen under ett nytt namn:

Alla skapade uppsättningar kommer att visas i panelen Pivottabellfält i en separat mapp, varifrån de fritt kan dras till rad- och kolumnområdena i alla nya pivottabeller:

Fördel 6: Dölj tabeller och kolumner selektivt

Även om detta är en liten, men mycket trevlig fördel i vissa fall. Genom att högerklicka på fältnamnet eller på tabellfliken i Power Pivot-fönstret kan du välja kommandot Dölj från Client Toolkit (Göm från klientverktyg):

Den dolda kolumnen eller tabellen försvinner från rutan PivotTable Field List. Det är mycket bekvämt om du behöver dölja några hjälpkolumner för användaren (till exempel beräknade eller kolumner med nyckelvärden för att skapa relationer) eller till och med hela tabeller.

Fördel 7. Avancerad drill-down

Om du dubbelklickar på en cell i värdeområdet i en vanlig pivottabell, visar Excel på ett separat ark en kopia av källdatafragmentet som var involverat i beräkningen av denna cell. Detta är en mycket praktisk sak, officiellt kallad Drill-down (i brukar de säga "misslyckas").

I datamodellsammanfattningen fungerar det här praktiska verktyget mer subtilt. Genom att stå på valfri cell med resultatet som intresserar oss kan du klicka på ikonen med ett förstoringsglas som dyker upp bredvid (det kallas Expresstrender) och välj sedan alla fält du är intresserad av i någon relaterad tabell:

Efter det kommer det aktuella värdet (Model = Explorer) att gå in i filterområdet, och sammanfattningen kommer att byggas av kontor:

Naturligtvis kan en sådan procedur upprepas många gånger, konsekvent gräva ner i dina data i den riktning du är intresserad av.

Fördel 8: Konvertera pivot till kubfunktioner

Om du markerar någon cell i sammanfattningen för datamodellen och sedan väljer på fliken Pivottabellanalys Kommando OLAP-verktyg – Konvertera till formler (Analysera — OLAP-verktyg — Konvertera till formler), då konverteras hela sammanfattningen automatiskt till formler. Nu kommer fältvärdena i rad-kolumnområdet och resultaten i värdeområdet att hämtas från datamodellen med hjälp av de speciella kubfunktionerna: CUBEVALUE och CUBEMEMBER:

Rent tekniskt betyder det att vi nu inte har att göra med en sammanfattning utan med flera celler med formler, dvs vi kan enkelt göra eventuella transformationer med vår rapport som inte finns i sammanfattningen, till exempel infoga nya rader eller kolumner i mitten av rapporten, göra eventuella ytterligare beräkningar i sammanfattningen, ordna dem på valfritt sätt osv.

Samtidigt finns givetvis kopplingen till källdata kvar och i framtiden kommer dessa formler att uppdateras när källorna ändras. Skönheten!

• Planfaktaanalys i en pivottabell med Power Pivot och Power Query
• Pivottabell med flerradshuvud
• Skapa en databas i Excel med Power Pivot