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á­so­la­tá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 re­a­gá­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 ja­ví­tá­sa­it 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 ja­vít­tat­ni.
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 pa­rancs­táb­lá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 (Au­dik, Seatok, Skodák, Volkswagenek), PSA (Citroenek, Opelek, Peugeot-k), TATA (Jaguárok, Land Roverek). A származtatott mező ne­ve le­gyen: 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ép­le­te: =KEREKÍTÉS( nettó * ( 1 + 27% ) ; -3 ). Összegezzük az új mezőt „összesen” néven! Magyarul, helyezzük a bruttó mezőt az ÉR­TÉ­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 jo­gi 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 je­le­ní­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őb­bit 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­ál­lí­tá­sok, Sze­le­te­lő, 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 (Cso­port2). 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 ki­mu­ta­tás uta­sí­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ó­lap­ra, 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áb­lá­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 elem­ző 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ét­re­hoz­ha­tunk 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áb­lá­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áb­lá­kat a mun­ka­lap 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áb­lát „e­re­de­ti”-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é­te­le­i­nek ö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. Tet­sző­le­ges 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 meg­je­len­nek 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áb­lánk­kal! 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. En­nek oka, hogy a prog­ram 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 za­va­ró 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áb­la tu­laj­don­sá­ga­it a Ki­mu­ta­tás­esz­kö­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, amely­nek Se­mennyi e­le­mé­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áb­la fris­sí­té­se után tűnnek el a szű­rő­lis­tá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 el­fo­gad­ha­tó! 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 gyors­e­lé­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 pa­rancs­táb­lá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ű esz­köz­tá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 mun­ka­fü­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 au­to­ma­ti­zál­hat­juk 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 le­ra­dí­roz­tam, illetve átalakítottam.
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com