Fordele ved Pivot by Data Model

Når vi bygger en pivottabel i Excel, er der i den allerførste dialogboks, hvor vi bliver bedt om at indstille startområdet og vælge et sted at indsætte pivottabellen, et upåfaldende, men meget vigtigt afkrydsningsfelt nedenfor – Føj disse data til datamodellen (Tilføj disse data til datamodel) og lidt højere kontakten Brug denne bogs datamodel (Brug datamodel for denne projektmappe):

Desværre forstår mange brugere, der har været bekendt med pivottabeller i lang tid og med succes bruger dem i deres arbejde, nogle gange ikke rigtigt betydningen af ​​disse muligheder og bruger dem aldrig. Og forgæves. Når alt kommer til alt, giver det os flere meget vigtige fordele at lave en pivottabel til Datamodellen sammenlignet med den klassiske Excel-pivottabel.

Men før vi overvejer disse "boller" tæt på, lad os først forstå, hvad denne datamodel i virkeligheden er?

Hvad er en datamodel

Datamodel (forkortet som MD eller DM = Data Model) er et særligt område inde i en Excel-fil, hvor du kan gemme tabeldata – en eller flere tabeller knyttet til hinanden, hvis det ønskes. Faktisk er dette en lille database (OLAP-terning) indlejret i en Excel-projektmappe. Sammenlignet med den klassiske lagring af data i form af almindelige (eller smarte) tabeller på selve Excel-ark, har datamodellen flere væsentlige fordele:

• Tabeller kan være op til 2 milliarder linjer, og et Excel-ark kan rumme lidt mere end 1 mio.
• På trods af den gigantiske størrelse udføres behandlingen af ​​sådanne tabeller (filtrering, sortering, beregninger på dem, bygningsoversigt osv.) meget hurtig Meget hurtigere end selve Excel.
• Med dataene i modellen kan du udføre yderligere (om ønsket meget komplekse) beregninger vha indbygget DAX-sprog.
  
• Al information indlæst i datamodellen er meget stærkt komprimeret ved hjælp af en speciel indbygget arkivering og ret moderat øger størrelsen af ​​den originale Excel-fil.
  

Modellen styres og beregnes af et særligt tilføjelsesprogram indbygget i Microsoft Excel – powerpivotsom jeg allerede har skrevet om. For at aktivere det, på fanen developer klik COM-tilføjelser (Udvikler — COM-tilføjelser) og marker det relevante felt:

Hvis faner developer (Udvikler)du kan ikke se det på båndet, du kan tænde det igennem Fil – Indstillinger – Opsætning af bånd (Fil — Indstillinger — Tilpas bånd). Hvis du i vinduet vist ovenfor på listen over COM-tilføjelser ikke har Power Pivot, er det ikke inkluderet i din version af Microsoft Office 🙁

På Power Pivot-fanen, der vises, vil der være en stor lysegrøn knap Management (Styre), ved at klikke på hvilket åbner Power Pivot-vinduet øverst i Excel, hvor vi vil se indholdet af datamodellen for den aktuelle bog:

En vigtig note med på vejen: en Excel-projektmappe kan kun indeholde én datamodel.

Indlæs tabeller i datamodellen

For at indlæse data i modellen gør vi først tabellen til en dynamisk "smart" tastaturgenvej Ctrl+T og giv den et venligt navn på fanen Constructor (Design). Dette er et påkrævet trin.

Så kan du bruge en af ​​de tre metoder at vælge imellem:

• Tryk på knappen Tilføj til model (Tilføj til datamodel) fanen powerpivot fanen Home (Hjem).
  
• Valg af hold Indsæt – Pivottabel (Indsæt — pivottabel) og slå afkrydsningsfeltet til Føj disse data til datamodellen (Tilføj disse data til datamodel). I dette tilfælde, ifølge de data, der er indlæst i modellen, bygges der også straks en pivottabel.
  
• På fanen Avanceret data (Dato) klik på knappen Fra tabel/område (Fra tabel/område)for at indlæse vores tabel i Power Query-editoren. Denne vej er den længste, men hvis det ønskes, kan du her udføre yderligere datarensning, redigering og alle former for transformationer, hvor Power Query er meget stærk.

  Derefter uploades de kæmmede data til modellen med kommandoen Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk&indlæs — Luk&indlæs til...). Vælg indstillingen i det vindue, der åbnes Bare opret en forbindelse (Opret kun forbindelse) og vigtigst af alt, sæt et flueben Føj disse data til datamodellen (Tilføj disse data til datamodel).
  

Vi bygger et resumé af datamodellen

For at opbygge en opsummerende datamodel kan du bruge en af ​​tre tilgange:

• Tryk på knappen oversigtstabel (pivottabel) i Power Pivot-vinduet.
• Vælg kommandoer i Excel Indsæt – Pivottabel og skifte til tilstand Brug denne bogs datamodel (Indsæt — Pivottabel — Brug denne projektmappes datamodel).
• Valg af hold Indsæt – Pivottabel (Indsæt — pivottabel) og slå afkrydsningsfeltet til Føj disse data til datamodellen (Tilføj disse data til datamodel). Den aktuelle "smart" tabel vil blive indlæst i modellen, og en oversigtstabel vil blive bygget for hele modellen.

Nu hvor vi har fundet ud af, hvordan man indlæser data i datamodellen og bygger et resumé på det, lad os undersøge fordelene og fordelene, som dette giver os.

Fordel 1: Relationer mellem tabeller uden brug af formler

En almindelig oversigt kan kun bygges ved hjælp af data fra én kildetabel. Hvis du har flere af dem, f.eks. salg, prisliste, kundekartotek, kontraktregister osv., så skal du først samle data fra alle tabeller til én ved hjælp af funktioner som VLOOKUP (OPLYSNING), INDEKS (INDEKS), MERE UDSAT (MATCH), SUMMESLIMN (SUMIFS) og lignende. Dette er langt, kedeligt og driver din Excel ind i en "tanke" med en stor mængde data.

I tilfælde af et resumé af datamodellen er alt meget enklere. Det er nok at oprette relationer mellem tabeller én gang i Power Pivot-vinduet – og det er gjort. For at gøre dette, på fanen powerpivot tryk på knappen Management (Styre) og så i det vindue, der vises – knappen Kortvisning (Diagramvisning). Det er tilbage at trække almindelige (nøgle) kolonnenavne (felter) mellem tabeller for at oprette links:

Derefter kan du i oversigten for datamodellen smide i oversigtsområdet (rækker, kolonner, filtre, værdier) alle felter fra alle relaterede tabeller - alt vil blive linket og beregnet automatisk:

Fordel 2: Tæl unikke værdier

En almindelig pivottabel giver os mulighed for at vælge en af ​​flere indbyggede beregningsfunktioner: sum, gennemsnit, antal, minimum, maksimum osv. I datamodeloversigten er der tilføjet en meget nyttig funktion til denne standardliste til at tælle antal unikke (ikke-gentagende værdier). Med dens hjælp kan du for eksempel nemt tælle antallet af unikke varer (sortiment), som vi sælger i hver by.

Højreklik på feltet – kommando Valgmuligheder for værdifelt og på fanen Produktion Vælg Antal forskellige elementer (Særskilt antal):

Fordel 3: Brugerdefinerede DAX-formler

Nogle gange skal du udføre forskellige ekstra beregninger i pivottabeller. I almindelige opsummeringer sker dette ved hjælp af beregnede felter og objekter, mens datamodelresuméet bruger mål i et særligt DAX-sprog (DAX = Data Analysis Expressions).

For at oprette et mål skal du vælge på fanen powerpivot Kommando Mål – Opret mål (Foranstaltninger — Nyt mål) eller bare højreklik på tabellen i Pivot Fields-listen og vælg Tilføj mål (Tilføj mål) i kontekstmenuen:

Indstil i vinduet, der åbnes:

• Tabelnavnhvor det oprettede mål vil blive gemt.
• Mål navn – ethvert navn, du forstår for det nye felt.
• Beskrivelse - valgfri.
• Formula – det vigtigste, for her kommer vi enten manuelt ind, eller klikker på knappen fx og vælg en DAX-funktion fra listen, som skal beregne resultatet, når vi så smider vores mål i værdiområdet.
• I den nederste del af vinduet kan du med det samme indstille talformatet for takten i listen Boligtype.
  

DAX-sproget er ikke altid let at forstå, fordi det ikke opererer med individuelle værdier, men med hele kolonner og tabeller, dvs. kræver en vis omstrukturering af tænkningen efter de klassiske Excel-formler. Det er dog det værd, fordi styrken af ​​dens evner til at behandle store mængder data er svær at overvurdere.

Fordel 4: Brugerdefinerede felthierarkier

Ofte skal man, når man laver standardrapporter, smide de samme kombinationer af felter ind i pivottabeller i en given rækkefølge, f.eks. År-kvartal-måned-dag eller Kategori-Produkt eller Land-by-klient osv. I datamodeloversigten løses dette problem nemt ved at oprette din egen hierarkier — brugerdefinerede feltsæt.

Skift til korttilstand med knappen i Power Pivot-vinduet Kortvisning fanen Home (Hjem — diagramvisning), vælg med Ctrl ønskede felter og højreklik på dem. Genvejsmenuen vil indeholde kommandoen Opret hierarki (Opret hierarki):

Det oprettede hierarki kan omdøbes og trækkes ind i det med musen de påkrævede felter, så de senere i en bevægelse kan smides ind i resuméet:

Fordel 5: Brugerdefinerede stencils

For at fortsætte ideen fra det foregående afsnit, i resuméet af datamodellen, kan du også oprette dine egne sæt af elementer for hvert felt. For eksempel fra hele listen over byer kan du nemt lave et sæt af kun dem, der er i dit ansvarsområde. Eller saml kun dine kunder, dine varer osv. i et særligt sæt.

For at gøre dette, på fanen Pivottabel analyse i rullelisten Felter, elementer og sæt der er tilsvarende kommandoer (Analyse - Fields, Items & Sets — Opret sæt baseret på række-/kolonneelementer):

I vinduet, der åbnes, kan du selektivt fjerne, tilføje eller ændre placeringen af ​​elementer og gemme det resulterende sæt under et nyt navn:

Alle oprettede sæt vil blive vist i panelet Pivottabelfelter i en separat mappe, hvorfra de frit kan trækkes til række- og kolonneområderne i enhver ny pivottabel:

Fordel 6: Skjul tabeller og kolonner selektivt

Selvom dette er en lille, men meget behagelig fordel i nogle tilfælde. Ved at højreklikke på feltnavnet eller på tabelfanen i Power Pivot-vinduet kan du vælge kommandoen Skjul fra Client Toolkit (Skjul fra klientværktøjer):

Den skjulte kolonne eller tabel forsvinder fra ruden PivotTable Field List. Det er meget praktisk, hvis du har brug for at skjule nogle hjælpekolonner for brugeren (for eksempel beregnede eller kolonner med nøgleværdier til at skabe relationer) eller endda hele tabeller.

Fordel 7. Avanceret drill-down

Hvis du dobbeltklikker på en celle i værdiområdet i en almindelig pivottabel, viser Excel på et separat ark en kopi af kildedatafragmentet, der var involveret i beregningen af ​​denne celle. Dette er en meget praktisk ting, officielt kaldet Drill-down (i de plejer at sige "fail").

I datamodeloversigten fungerer dette praktiske værktøj mere subtilt. Ved at stå på en hvilken som helst celle med det resultat, der interesserer os, kan du klikke på ikonet med et forstørrelsesglas, der popper op ved siden af ​​(det kaldes Express Trends) og vælg derefter ethvert felt, du er interesseret i, i en hvilken som helst relateret tabel:

Derefter vil den aktuelle værdi (Model = Explorer) gå ind i filterområdet, og oversigten vil blive bygget af kontorer:

Selvfølgelig kan en sådan procedure gentages mange gange, konsekvent dykke ned i dine data i den retning, du er interesseret i.

Fordel 8: Konverter pivot til kubefunktioner

Hvis du vælger en celle i oversigten for datamodellen og derefter vælger på fanen Pivottabel analyse Kommando OLAP-værktøjer – Konverter til formler (Analyser - OLAP-værktøjer - Konverter til formler), så vil hele oversigten automatisk blive konverteret til formler. Nu vil feltværdierne i række-kolonne området og resultaterne i værdiområdet blive hentet fra datamodellen ved hjælp af de specielle kubefunktioner: CUBEVALUE og CUBEMEMBER:

Teknisk set betyder det, at vi nu ikke har at gøre med en oversigt, men med flere celler med formler, dvs. vi kan sagtens lave de transformationer med vores rapport, som ikke er tilgængelige i oversigten, f.eks. indsætte nye rækker eller kolonner i midten af rapporten, foretage eventuelle yderligere beregninger inde i resuméet, arrangere dem på den ønskede måde osv.

Samtidig forbliver sammenhængen med kildedataene naturligvis, og i fremtiden vil disse formler blive opdateret, når kilderne ændres. Skønheden!

• Plan-fakta-analyse i en pivottabel med Power Pivot og Power Query
• Pivottabel med multiline header
• Opret en database i Excel ved hjælp af Power Pivot