A Pivot by Data Model előnyei

Amikor Excelben pivot táblát készítünk, a legelső párbeszédablakban, ahol a kezdeti tartomány megadását és a pivot tábla beszúrásának helyét kell megadnunk, lent van egy nem feltűnő, de nagyon fontos jelölőnégyzet – Adja hozzá ezeket az adatokat az adatmodellhez (Adja hozzá ezeket az adatokat adatmodellhez) és kicsit feljebb a kapcsolót Használja a könyv adatmodelljét (Használja a munkafüzet adatmodelljét):

Sajnos sok felhasználó, aki régóta ismeri a pivot táblákat, és sikeresen használja őket munkája során, néha nem igazán érti ezeknek a lehetőségeknek a jelentését, és soha nem használja őket. És hiába. Végül is, ha az adatmodellhez pivot táblát hozunk létre, számos nagyon fontos előnnyel rendelkezünk a klasszikus Excel pivot táblához képest.

Mielőtt azonban közelebbről megvizsgálnánk ezeket a „zsemléket”, először is értsük meg, mi is valójában ez az adatmodell?

Mi az adatmodell

Adatmodell (rövidítve MD vagy DM = adatmodell) egy speciális terület az Excel-fájlon belül, ahol táblázatos adatokat tárolhat – egy vagy több táblázatot, ha szükséges, összekapcsolva egymással. Valójában ez egy kis adatbázis (OLAP-kocka), amely egy Excel-munkafüzetbe van beágyazva. A hagyományos (vagy intelligens) táblázatok formájában az Excel lapjain történő adattároláshoz képest az adatmodellnek számos jelentős előnye van:

• A táblázatok legfeljebb 2 milliárd sor, egy Excel-lapon pedig kicsivel több, mint 1 millió elfér.
• A gigantikus méret ellenére az ilyen táblázatok feldolgozása (szűrés, rendezés, számítások, épületösszegzés stb.) megtörténik. nagyon gyors Sokkal gyorsabb, mint maga az Excel.
• A Modellben lévő adatokkal további (ha szükséges, nagyon összetett) számításokat végezhet a segítségével beépített DAX nyelv.
  
• Az Adatmodellbe betöltött összes információ nagyon erősen összenyomva speciális beépített archiváló segítségével, és meglehetősen mérsékelten növeli az eredeti Excel fájl méretét.
  

A modellt a Microsoft Excelbe beépített speciális bővítmény kezeli és számítja ki – powerpivotamiről már írtam. Az engedélyezéséhez a lapon fejlesztő kettyenés COM bővítmények (Fejlesztő – COM-bővítmények) és jelölje be a megfelelő négyzetet:

Ha lapok fejlesztő (Fejlesztő)a szalagon nem látszik, át lehet kapcsolni Fájl – Beállítások – Szalagbeállítás (Fájl — Beállítások — Szalag testreszabása). Ha a fenti ablakban, a COM-bővítmények listájában nincs Power Pivot, akkor azt nem tartalmazza a Microsoft Office verziója 🙁

A megjelenő Power Pivot lapon egy nagy, világoszöld gomb látható Támogató (Kezelés), amelyre kattintva megnyílik az Excel tetején a Power Pivot ablak, ahol az aktuális könyv Adatmodelljének tartalmát fogjuk látni:

Egy fontos megjegyzés az út során: egy Excel-munkafüzet csak egy adatmodellt tartalmazhat.

Töltsön be táblákat az adatmodellbe

Az adatok Modellbe való betöltéséhez először a táblázatot dinamikus „intelligens” billentyűparancsmá alakítjuk Ctrl+T és adj neki egy barátságos nevet a lapon építész (Tervezés). Ez egy kötelező lépés.

Ezután a három módszer közül választhat:

• nyomja meg a gombot Hozzáadás a modellhez (Hozzáadás az adatmodellhez) lap powerpivot lap Kezdőlap (Itthon).
  
• Csapatok kiválasztása Beszúrás – PivotTable (Beszúrás – Pivot Table) és kapcsolja be a jelölőnégyzetet Adja hozzá ezeket az adatokat az adatmodellhez (Adja hozzá ezeket az adatokat az adatmodellhez). Ebben az esetben a Modellbe betöltött adatok szerint azonnal felépül egy pivot tábla is.
  
• A Speciális lapon dátum (Dátum) kattintson a gombra Táblázatból/Tartományból (A táblázatból/Tartományból)hogy betöltsük a táblázatunkat a Power Query szerkesztőbe. Ez az út a leghosszabb, de kívánság szerint itt további adattisztítást, szerkesztést és mindenféle átalakítást végezhet, amiben a Power Query nagyon erős.

  Ezután a fésült adatok a paranccsal feltöltődnek a Modellbe Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Kezdőlap – Bezárás&Betöltés – Bezárás&Betöltés…). A megnyíló ablakban válassza ki a lehetőséget Csak hozzon létre egy kapcsolatot (Csak kapcsolat létrehozása) és ami a legfontosabb, tegyél egy pipát Adja hozzá ezeket az adatokat az adatmodellhez (Adja hozzá ezeket az adatokat az adatmodellhez).
  

Összefoglalót készítünk az Adatmodellről

Összefoglaló adatmodell felépítéséhez a három megközelítés bármelyikét használhatja:

• Nyomja meg a gombot összefoglaló táblázat (Pivot tábla) a Power Pivot ablakban.
• Válassza ki a parancsokat az Excelben Beszúrás – PivotTable és váltson módba Használja a könyv adatmodelljét (Beszúrás — Kimutatástábla — A munkafüzet adatmodelljének használata).
• Csapatok kiválasztása Beszúrás – PivotTable (Beszúrás – Pivot Table) és kapcsolja be a jelölőnégyzetet Adja hozzá ezeket az adatokat az adatmodellhez (Adja hozzá ezeket az adatokat az adatmodellhez). A jelenlegi „okos” tábla betöltődik a Modellbe, és összefoglaló tábla készül a teljes Modellhez.

Most, hogy rájöttünk, hogyan töltsünk be adatokat az adatmodellbe, és készítsünk rá egy összefoglalót, vizsgáljuk meg, milyen előnyökkel jár ez számunkra.

1. előny: A táblázatok közötti kapcsolatok képletek használata nélkül

Rendszeres összesítés csak egy forrástábla adataiból készíthető. Ha több is van belőlük, például értékesítés, árlista, vásárlói névjegyzék, szerződésnyilvántartás stb., akkor először az összes táblából egybe kell gyűjtenie az adatokat olyan funkciókkal, mint a VLOOKUP (VLOOKUP), INDEX (INDEX), KIÉPÍTETTebb (MÉRKŐZÉS), SUMMESLIMN (SUMIFS) és a hasonlók. Ez hosszú, fárasztó, és egy nagy mennyiségű adatot tartalmazó „gondolatba” hajtja az Excelt.

Az Adatmodell összefoglalása esetén minden sokkal egyszerűbb. Elég egyszer a Power Pivot ablakban beállítani a táblák közötti kapcsolatokat – és kész. Ehhez a lapon powerpivot nyomja meg a gombot Támogató (Kezelés) majd a megjelenő ablakban – a gombot Diagram nézet (Diagram nézet). A hivatkozások létrehozásához a közös (kulcs) oszlopneveket (mezőket) kell húzni a táblázatok között:

Ezt követően az Adatmodell összefoglalójában az összesítő területbe (sorok, oszlopok, szűrők, értékek) tetszőleges kapcsolódó táblák tetszőleges mezőit bedobhatja – minden automatikusan összekapcsolódik és kiszámításra kerül:

2. előny: Egyedi értékek számolása

A szokásos pivot tábla lehetőséget ad arra, hogy válasszon egyet a számos beépített számítási függvény közül: összeg, átlag, számolás, minimum, maximum stb. Az adatmodell összefoglalójában ez a standard lista egy nagyon hasznos funkcióval egészül ki a egyedi (nem ismétlődő értékek) száma. Segítségével például könnyedén megszámolhatja, hogy az egyes városokban hány egyedi árucikk (választék) kerül forgalomba.

Kattintson a jobb gombbal a mezőre – parancs Értékmező beállításai és a lapon Művelet A pop-art design, négy időzóna kijelzése egyszerre és méretének arányai azok az érvek, amelyek a NeXtime Time Zones-t kiváló választássá teszik. Válassza a Különböző elemek száma (Különböző szám):

3. előny: Egyéni DAX-képletek

Néha különféle további számításokat kell végeznie a pivot táblákban. A szokásos összegzéseknél ez számított mezők és objektumok használatával történik, míg az adatmodell-összegzés egy speciális DAX nyelven (DAX = Data Analysis Expressions) használt mértékeket használ.

Mértékegység létrehozásához válassza a lehetőséget a lapon powerpivot parancs Intézkedések – Hozzon létre intézkedést (Intézkedések – Új intézkedés) vagy egyszerűen kattintson a jobb gombbal a táblázatra a Pivot Fields listában, és válassza ki Adjon hozzá mértéket (Mérték hozzáadása) a helyi menüben:

A megnyíló ablakban állítsa be:

• Tábla neveahol a létrehozott mérték tárolódik.
• A mérés neve – bármilyen név, amit az új mezőhöz ért.
• Leírás - választható.
• Képlet – a legfontosabb, mert itt vagy kézzel írunk be, vagy kattintunk a gombra fx és válasszunk ki egy DAX függvényt a listából, aminek ki kell számítania az eredményt, amikor bedobjuk a mértékünket az Értékek területre.
• Az ablak alsó részében azonnal beállíthatja a listában szereplő mérték számformátumát Kategória.
  

A DAX nyelv nem mindig könnyen érthető, mert nem egyedi értékekkel, hanem egész oszlopokkal és táblázatokkal operál, azaz némi gondolkodásmódot igényel a klasszikus Excel képletek után. Megéri azonban, mert nehéz túlbecsülni a nagy mennyiségű adat feldolgozásában rejlő képességeit.

4. előny: Egyéni mezőhierarchiák

A szabványos jelentések létrehozásakor gyakran ugyanazokat a mezők kombinációit kell bedobni a pivot táblákba egy adott sorrendben, pl. Év-negyed-hónap-napvagy Kategória-Termékvagy Ország-város-ügyfél stb. Az Adatmodell összefoglalójában ez a probléma könnyen megoldható saját létrehozásával hierarchiák — egyedi terepkészletek.

A Power Pivot ablakban a gombbal váltson diagram módba Diagram nézet lap Kezdőlap (Kezdőlap — Diagram nézet), válassza ki a gombbal Ctrl a kívánt mezőket, és kattintson rájuk a jobb gombbal. A helyi menü tartalmazza a parancsot Hierarchia létrehozása (Hierarchia létrehozása):

A létrehozott hierarchia átnevezhető és az egérrel behúzható a szükséges mezők, így később egy mozdulattal be lehet dobni az összefoglalóba:

5. előny: Egyedi sablonok

Az előző bekezdés gondolatát folytatva, az Adatmodell összefoglalójában minden mezőhöz saját elemkészletet is létrehozhat. Például a városok teljes listájából könnyedén összeállíthat csak azokat, amelyek az Ön felelősségi körébe tartoznak. Vagy csak ügyfeleit, áruit stb. gyűjtse egy speciális készletbe.

Ehhez a lapon Pivot tábla elemzés a legördülő listában Mezők, elemek és készletek megfelelő parancsok vannak (Elemzés – Fields, Itételek és készletek — Készlet létrehozása sor/oszlopelemek alapján):

A megnyíló ablakban tetszőleges elemet választhatóan eltávolíthat, hozzáadhat vagy megváltoztathatja a pozícióját, és új néven mentheti a kapott készletet:

Az összes létrehozott készlet a Kimutatásmezők panelen, egy külön mappában jelenik meg, ahonnan szabadon áthúzhatók bármely új kimutatás sorai és oszlopai:

6. előny: A táblázatok és oszlopok szelektív elrejtése

Bár ez kicsi, de bizonyos esetekben nagyon kellemes előny. A Power Pivot ablakban a mező nevére vagy a táblázat fülére jobb gombbal kattintva kiválaszthatja a parancsot Elrejtés a Client Toolkitből (Elrejtés az Ügyféleszközök közül):

A rejtett oszlop vagy táblázat eltűnik a kimutatás mezőlista ablaktáblájáról. Nagyon kényelmes, ha el kell rejtenie a felhasználó elől néhány segédoszlopot (például kiszámított vagy kulcsértékekkel rendelkező oszlopokat a kapcsolatok létrehozásához), vagy akár teljes táblázatokat.

Előny 7. Speciális drill-down

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Az adatmodell összefoglalójában ez a praktikus eszköz finomabban működik. Bármelyik cellára állva a minket érdeklő eredménnyel, rákattinthat a mellette felugró nagyítóval ellátott ikonra (ún. Expressz Trendek), majd bármelyik kapcsolódó táblázatban válassza ki az Önt érdeklő mezőket:

Ezt követően az aktuális érték (Model = Explorer) a szűrőterületre kerül, és az összesítést irodák készítik:

Természetesen egy ilyen eljárás sokszor megismételhető, következetesen az Önt érdeklő irányba ásva az adataiban.

8. előny: Konvertálja a Pivot-ot kockafüggvényekké

Ha kiválaszt egy cellát az adatmodell összegzésében, majd kiválasztja a lapon Pivot tábla elemzés parancs OLAP eszközök – Konvertálás képletekre (Elemzés — OLAP-eszközök — Konvertálás képletekre), akkor a teljes összegzés automatikusan képletté alakul. Most a sor-oszlop területen lévő mezőértékek és az értékterületen lévő eredmények lekérhetők az adatmodellből a speciális kockafüggvények segítségével: CUBEVALUE és CUBEMBER:

Ez technikailag azt jelenti, hogy most nem összegzéssel, hanem több képletekkel ellátott cellával van dolgunk, azaz könnyen elvégezhetünk jelentésünkkel minden olyan átalakítást, ami az összefoglalóban nem található meg, például új sorokat vagy oszlopokat szúrhatunk be a közepébe. fejezetében, végezzen további számításokat az összefoglalón belül, rendezze el azokat tetszőleges módon stb.

Ugyanakkor a forrásadatokkal való kapcsolat természetesen megmarad, és a jövőben ezek a képletek a források változásával frissülni fognak. A szépség!

• Terv-tényelemzés kimutatástáblázatban Power Pivot és Power Query segítségével
• Kimutatástábla többsoros fejléccel
• Hozzon létre adatbázist Excelben a Power Pivot segítségével