azonos forrású pivot táblák

2018-09-11    Excel 2016
A pivot tábla beszúrását megelőzően, a program létrehozza a számítógép operatív tárjában az elemző tábla forrásának átstrukturált másolatát, amelyet a szakzsargon pivot cache-nek (ejtsd: kes) nevez. Ez az adatszerkezet biztosítja a pivot tábla módosításakor a program gyors reagálását. Ebben a cikkben a pivot cache tulajdonságait mutatom be az Excel 2016-os verziójában.
Gyakorlati példáink a javítások adatbázis-táblázat elemzésére létrehozott pivot táblák lesznek. Az adatbázis-táblázat vállalati autók javításait tartalmazza. Az autók javítását, rendszám szerint szerződött szervizek végzik. Minden autót csak a saját szervizébe lehet javíttatni.
azonos forrású pivot táblák, példa
Készítsünk pivot táblát a javítások elemzésére! A beszúrást követően adjunk nevet az elemző táblának a Kimutatás beállításai parancstáblán, amelyet a Kimutatáseszközök, Elemzés, Kimutatás, Beállítások utasítással jeleníthetünk meg! A név jogi sajáthibás legyen.
A SZŰRŐK területen a sajáthibás, a SOROK területen a típus mező álljon! Csoportosítsuk a típus mező tételeit gyártó szerint: VW (Audik, Seatok, Skodák, Volkswagenek), PSA (Citroenek, Opelek, Peugeot-k), TATA (Jaguárok, Land Roverek). A származtatott mező neve legyen: gyártó! Távolítsuk el a típus mezőt a pivot táblából.
Hozzunk létre számított mezőt, bruttó néven, amely kiszámolja a javítások bruttó összegét, ezresre kerekítve, a nettó mező alapján. Képlete: =KEREKÍTÉS( nettó * ( 1 + 27% ) ; -3 ). Összegezzük az új mezőt „összesen” néven! Magyarul, helyezzük a bruttó mezőt az ÉRTÉKEK területre. Szűrjünk a sajáthibás javításokra! Hozzunk létre külső szűrőt az osztály mező tételeivel! Az új vezérlőn válasszuk ki a jogi osztályt!
azonos forrású pivot táblák, példa
Hozzunk létre egy másik, azonos felépítésű pivot táblát, amely a nem sajáthibás javításokat összegzi! Ezt a feladatot megoldhatjuk: [1] új pivot tábla beszúrásával, [2] vágólap segítségével, [3] vagy utasítással, amely a szűrő mező tételeit külön-külön pivot táblákban jeleníti meg. Az új elemző tábla, a létrehozás módjától függetlenül, a már meglévő cache-t fogja használni.
Az első módszer nem okozhat problémát. Szúrjuk be az új pivot táblát! A neve jogi nem sajáthibás legyen! A program a származtatott és a számított mezőket a cache-ben tárolja, ezért az új elemző tábla mezőlistáján is megtalálhatjuk a gyártó és a bruttó mezőket. Az előbbit típus2 néven. Építsük fel a pivot táblát és szűrjük a nem sajáthibás javításokra.
A program a külső szűrőt nem csatolja automatikusan az új elemző táblához, ezt nekünk kell megtennünk, a Szeletelőeszközök, Beállítások, Szeletelő, Jelentéskapcsolatok utasítással megjeleníthető parancstáblán.
azonos forrású pivot táblák, külső szűrők csatolása
Természetesen semmi akadálya, hogy a program automatikus neveit módosítsuk a pivot táblában: gyártó (típus2), VW (Csoport1), PSA (Csoport2). A program a segédablakban és a szűrőlistákban is érvényesíti a változtatásainkat.
Töröljük a második pivot táblát! Csak a tartalom törlése műveletet vagy a Kezdőlap, Cellák, Törlés lista parancsait használhatjuk, hogy a cache ne sérüljön! A legegyszerűbb megoldás: először kiadjuk a Kimutatáseszközök, Elemzés, Műveletek, Kijelölés, Teljes kimutatás utasítást, majd megnyomjuk a Delete billentyűt.
Hozzuk létre ismét a „jogi nem sajáthibás” elemzést, de most a vágólapos módszerrel! Helyezzük a pivot táblát másolatként a vágólapra, majd jelöljük ki a létrehozandó objektumot magában foglaló tartomány bal felső celláját! A beillesztést követően adjunk nevet az új pivot táblának és szűrjük a sajáthibás mezőt HAMIS értékre.
Az eredmény csodás! Nem csak az eredeti neveket tartotta meg a program, de a külső szűrőt is csatolta az új pivot táblához. Az elemző tábla másolása általános művelet, nem csak ehhez a feladat-típushoz alkalmazható. A pivot tábla aktuális adataival statikus táblázatot is létrehozhatunk az irányított beillesztés Értékek utasításával.
A harmadik eljárás lényege, hogy a program a kiválasztott szűrő mező tételeit, az aktuálissal azonos felépítésű, külön munkalapokon álló, pivot táblákban jeleníti meg. Másként fogalmazva: a program külön-külön munkalapokra, másolatokat készít az aktuális pivot tábláról és az elemző táblákat, az aktuálisat is beleértve, a kiválasztott szűrő mező egy-egy tételére szűri. Tehát a pivot táblák darabszáma a szűrő mező tételszámával lesz azonos. Az új munkalapok az eredeti pivot táblát tartalmazó lap előtt állnak majd. A program az elemző táblákat a munkalap bal felső sarkához illeszti.
Nézzük mindezt a gyakorlatban. Módosítsuk az eredeti feladatot: ne a sajáthibás-, hanem a szervizek szerint vizsgáljuk a javításokat! [1] Töröljük a sajáthibás mező szűrőjét, [2] majd adjuk hozzá a SZŰRŐ területhez a szerviz mezőt [3] és végül nevezzük át a pivot táblát „eredeti”-re!
azonos forrású pivot táblák, a szűrő mező tételei külön munkalapon
Ezután adjuk ki a Kimutatáseszközök, Elrendezés, Kimutatás, Jelentésszűrők oldalankénti megjelenítése… (értd: a szűrő mező tételeinek önálló megjelenítése) utasítást, majd válasszuk ki a részletezni kívánt mezőt!
azonos forrású pivot táblák, a szűrő mező tételei külön munkalapon
A vállalat négy autójavítóval van szerződésben, ezért négy új pivot táblát kapunk, egy-egy autójavító-tételre szűrve. Az új munkalapok az elemzett autójavító, vagyis a megjelenített tétel nevét kapják. Ez az eljárás a külső szűrő csatolását nem végzi el.
A közös cache-t használó pivot táblák csoportot alkotnak. A származtatott és a számított mezők törlése a teljes csoportot érinti. Tetszőleges elemző táblában csoportbontással távolítsuk el a gyártó mezőt, majd töröljük a bruttó mezőt is! Az összes pivot táblában újra megjelennek az autótípusok és a csoport minden tagjában eltünik a statisztikai mező, amely a javítások bruttó árát összesítette.
Mielőtt folytatnánk a cache körüli kirándulásunkat. Töröljük a létrehozott munkalapokat, és dolgozzunk tovább az eredeti pivot táblánkkal! Távolítsuk el az elemző tábla szűrő mezőit és külső szűrőjét! Majd töröljük a javítások adatbázis-táblázatból a Jaguár XF, a Jaguár XJ és a Land Rover Discovery típusok javításait.
A frissítést követően a pivot táblából is eltűnik a három tétel, de a mező szűrőlistájában továbbra is megtalálhatók. Ennek oka, hogy a program a szokásos beállítások mellett a cache-ből nem távolítja el a törölt tételeket. Persze bizonyos esetekben ez zavaró lehet, ezért a tervezők lehetőséget biztosítottak a működés felhasználói szabályozására. Jelenítsük meg a pivot tábla tulajdonságait a Kimutatáseszközök, Elemzés, Kimutatás, Beállítások utasítással! Az összetett parancstábla Adatok lapján találunk egy listát, amelynek Semennyi elemével tilthatjuk meg a törölt tételek tárolását. A módosítást követően a már törölt tételek csak a pivot tábla frissítése után tűnnek el a szűrőlistáról.
azonos forrású pivot táblák, törölt tételek a cache-ből nem törlődnek
Tehát a program az új pivot táblának nem hoz létre önálló cache-t, ha forrását már egy másik elemző tábla is vizsgálja. Ez nem mindig elfogadható! Saját cache-sel működő újabb elemző táblát varázslóval hozhatunk létre. Parancsgombját nekünk kell elhelyezni a gyorselérésű eszköztáron!
azonos forrású pivot táblák, pivot tábla varázsló ikonja
Ezután indítsuk el a segédprogramot. Az első lépésben meg kell határoznunk a forrás-típusát és a létrehozandó objektumot. A második parancstáblán a forrást deklaráljuk. Ha már létezik olyan pivot tábla, amely a deklarált forrást vizsgálja, a program megkérdezi, mit tegyen…
azonos forrású pivot táblák, a várázsló cache-re vonatkozó kérdése
…és nekünk a Nem-t kell választani. Ha a varázsló indításának billentyűparancsát meg tudjuk jegyezni, akkor még a gyorselérésű eszköztárt sem kell módosítanunk: Alt+d+i. Tehát az Alt billentyű folyamatos nyomva tartása mellett, először a dé, majd az i betűt kell leütnünk.
A program nem csak a pivot táblát, de az őt működtető adatszerkezetet is a munkafüzetbe menti. Ez a szokásos működés, amelyet a Kimutatás beállításai, Adatok, A forrásadatok mentése a fájllal vezérlő kiválasztott állapota biztosít. Ha a szolgáltatást letíltjuk, akkor a munkafüzet minden megnyitásakor a pivot táblát frissítenünk kell. A fenti parancstábla Adatfrissítés a fájl megnyitásakor utasításával automatizálhatjuk a műveletet.
azonos forrású pivot táblák, a cache törlése
[így készült] Néhány képen, a kiegyensúlyozott kompozíció érdekében, a program kezelő felületének néhány, lényegtelen vezérlőjét leradíroztam, illetve átalakítottam.
margitfalvi.arpad@gmail.com