Innehåll
Slicers i pivottabeller kan användas inte bara på det klassiska sättet – för att filtrera källdata, utan också för att växla mellan olika typer av beräkningar i värdeområdet:
Att implementera detta är väldigt enkelt – allt du behöver är ett par formler och en extra tabell. Tja, vi kommer att göra allt detta inte i den vanliga sammanfattningen, utan i sammanfattningen byggd enligt Power Pivot Data Model.
Steg 1. Ansluta Power Pivot-tillägget
Om flikarna i Power Pivot-tillägget inte är synliga i din Excel måste du först aktivera det. Det finns två alternativ för detta:
- Fliken utvecklare - knapp COM-tillägg (Utvecklare – COM-tillägg)
- Arkiv – Alternativ – Tillägg – COM-tillägg – Gå (Arkiv — Alternativ — Tillägg — COM-tillägg — Gå till)
Om detta inte hjälper, försök sedan starta om Microsoft Excel.
Steg 2: Ladda data till Power Pivot-datamodellen
Vi kommer att ha två tabeller som initiala data:
Den första är en tabell med försäljning, enligt vilken vi senare kommer att bygga en sammanfattning. Den andra är en extra tabell, där namnen på knapparna för den framtida skivan skrivs in.
Båda dessa tabeller måste konverteras till "smarta" (dynamiska) med en kortkommando ctrl+T eller team Hem – Formatera som en tabell (Hem — Formatera som tabell) och det är önskvärt att ge dem vettiga namn på fliken Konstruktör (Design). Låt det vara t.ex. Försäljning и Tjänster.
Därefter måste varje tabell i sin tur laddas in i Datamodellen – för detta använder vi på fliken PowerPivot Knappen Lägg till i datamodell (Lägg till i datamodell).
De beräknade fälten i en pivottabell efter datamodell anropas åtgärder. Låt oss skapa ett mått som visar namnet på den nedtryckta knappen på det framtida segmentet. För att göra detta, i någon av våra tabeller, välj valfri tom cell i den nedre beräkningspanelen och skriv in följande konstruktion i formelfältet:
Här kommer åtgärdens namn först (Tryckt på knappen), och sedan efter ett kolon och ett likhetstecken, en formel för att beräkna det med funktionen VÄRDEN DAX inbyggd i Power Pivot.
Om du upprepar detta inte i Power Pivot, utan i Power BI, så behövs inte kolon och istället VÄRDEN du kan använda dess modernare motsvarighet – funktionen VALT VÄRDE.
Vi uppmärksammar inte fel i den nedre delen av fönstret som visas efter att ha angett formeln - de uppstår eftersom vi ännu inte har en sammanfattning och en skiva där något klickas.
Nästa steg är att skapa ett mått för olika beräkningsalternativ beroende på värdet på föregående mått Tryckt på knappen. Här är formeln lite mer komplicerad:
Låt oss dela upp det bit för bit:
- Funktion KONTAKT – en analog av kapslade IF – kontrollerar att de angivna villkoren är uppfyllda och returnerar olika värden beroende på uppfyllelsen av några av dem.
- Funktion SANN() – ger ett logiskt "sant" så att de villkor som kontrolleras senare av SWITCH-funktionen fungerar endast om de är uppfyllda, dvs sanning.
- Sedan kontrollerar vi värdet på det knapptryckta måttet och beräknar slutresultatet för tre olika alternativ – som summan av kostnaden, medelkontrollen och antalet unika användare. För att räkna unika värden, använd funktionen DISTINCTCOUNT, och för avrundning – RUNT.
- Om inget av ovanstående tre villkor är uppfyllt, visas det sista argumentet för SWITCH-funktionen – vi ställer in det som en dummy med funktionen TOM().
Steg 5. Bygg en sammanfattning och lägg till en skiva
Det återstår att gå tillbaka från Power Pivot till Excel och bygga en pivottabell där för alla våra data och åtgärder. För att göra detta, i Power Pivot-fönstret på Den huvudsakliga fliken välj kommando Översiktstabell (Hem — Pivottabell).
Sedan:
- Vi kastar fältet Produkt av tabellen Försäljning till området rader (rader).
- Att kasta ett fält där Resultat av tabellen Tjänster.
- Högerklicka på fältet Resultatoch välj ett lag Lägg till som skiva (Lägg till som skivare).
- Kastar det andra måttet Slutsats av tabellen Tjänster till området Värden (Värden).
Här finns faktiskt alla knep. Nu kan du klicka på skärningsknapparna – och summorna i pivottabellen växlar till den funktion du behöver.
Skönhet 🙂
- Fördelar med Pivot by Data Model
- Planfaktaanalys i en pivottabell på Power Pivot
- Skapa en databas i Excel med Power Pivot-tillägget