vágólapos tábla-készítés
2019-03-07    Excel 2019
Sorold el Pistike, milyen módszerekkel tudunk táblát készíteni a PowerPivot bővítményben! Igen, Tanító bácsi! Importálással, adatbázis-táblázat átalakításával és vágólappal. Ez az, Pistike! Ma a vágólapos tábla-készítéssel fogunk foglalkozni, a bővítmény 2019-es változatát használva.
    Ha munkafüzetből vagy text fájlból importálunk, akkor a PowerPivot először az adatokról másolatot készít a számítógép operatív tár­já­ba. Ugyanez a művelet hajtódik végre akkor is, amikor a programban kijelölünk egy táblázatot és kiadjuk a Másolás parancsot. Tehát nem meg­le­pő, hogy a bővítmény a „vágólapon” álló adatokból közvetlenül is képes táblát készíteni.
    Vegyük sorra a PowerPivot vágólap-műveleteit! Beillesztés: a művelet egy új táblát hoz létre a vágólapon lévő adatokkal. Be­il­lesz­tés hoz­zá­fű­zés­sel: a művelet a vágólapon lévő adatokat az aktív táblába helyezi, a már ott lévő rekordok után. Beillesztés cse­ré­vel: a művelet törli az aktív tábla rekordjait, majd az üres táblába helyezi a vágólapon lévő rekordokat. Másolás: a művelet vágólapra he­lye­zi a kijelölt ob­jek­tu­mot.
a vágólapos tábla-készítés parancsai az Excel 2019-es verziójában
A vágólapos táblakészítés elsősorban különböző sor-számú, de azonos nevű oszlopokat, azonos sorrendben tartalmazó táblázatok, egy­sze­rű­en, azonos felépítésű táblázatok, egyesítésére alkalmazható. Másszóval, a forrás-táblázatok adatait egyetlen táblába másoljuk, majd azt, adatbázisba ágyazottan vagy önállóan pivot táblával elemezzük. Ebből következően a vágólap műveletekkel létrehozott táblát, ál­ta­lá­no­sít­va egyesítő táblának is nevezhetjük. Az egyesítő tábla és forrás-táblázatai tetszőleges munkafüzetben állhatnak. A vágólappal lét­re­ho­zott tábla nem frissíthető! Az importálással lét­re­ho­zott tábláknál a Beillesztés hozzáfűzéssel és a Beillesztés cserével utasítások nem el­ér­he­tők.
    Az egyesítő tábla létrehozása egyet jelent az első forrás-táblázat beolvasásával. Másoljuk a táblázatot a vágólapra, majd adjuk ki Be­il­lesz­tés parancsot a bővítmény ablakában, amelynek hatására a PowerPivot megjeleníti a beillesztendő táblázat tizenkilenc soros vil­lám­né­ze­tét. A parancstáblán megadhatjuk a tábla nevét és jelölőnégyzettel deklarálhatjuk az első sorban álló oszlop-neveket. A beillesztés vil­lám­né­ze­te nem tartalmazza a szűrés eszközeit, tehát a forrás-táblázatok sorait, ha szükséges, akkor a vágólapra helyezés előtt kell sze­lek­tál­nunk.
    Azonos adattípusú bejegyzéseket tartalmazó oszlopok esetén a bővítmény: [1] megkülönbözteti az egész számokat- és a tizedes tör­te­ket és ennek megfelelően állítja be a mező adattípusát, [2] a pénznem típusú forrásadatokat négy tizedesjegyre kerekíti, [3] a dá­tu­mo­kat a 0:00:00 időponttal egészíti ki, [4] az időpontok elé a 1899.12.31. dátumot illeszti, és dátum adattípusú mezőt hoz létre számukra.
    A vegyes adattípusú forrás-oszlop számára a bővítmény szöveg adattípusú mezőt hoz létre, ha abban akár csak egyetlen szöveg vagy logikai vag dátum típusú adat áll. Ha a felsorolt három adattípus hiányzik a vegyes adattípusú oszlopból, akkor a mező adat­tí­pu­sa pénz­nem lesz, ha akár csak egyetlen adata pénznem. A számokat tartalmazó forrás-oszlop számára a bővítmény tizedes tört adat­tí­pu­sú mezőt hoz létre, ha akár csak egyetlen adata tizedes tört.
    A PowerPivot a fenti szabályoknak megfelelően létrehozza a táblát, de ha valamelyik mezőjében az egyik adattípus bejegyzéseinek szá­ma eléri a rekordok számának kilencvenöt százalékát, felfelé, egész százalékra kerekítve, akkor a bővítmény felajánlja egy számított mező automatikus létrehozását, amely az eredeti mező homogenizált bejegyzéseit tartalmazza. A következő kép a homogenizáló mező fel­a­ján­lá­sá­nak egyetlen feltételét mutatja, két különböző megfogalmazásban.
automatikus számított mező létrehozásának feltétele PowerPivot tábla vágólapos létrehozásakor
A szolgáltatás nem terjed ki a döntően szöveg- és a többségében pénznem bejegyzéseket tartalmazó mezőkre. Az automatikus ho­mo­ge­ni­zá­ló oszlop létrehozásának lehetőségére a mezőnév mellett álló jelzés figyelmeztet.
automatikus számított mezők PowerPivot tábla vágólapos létrehozásakor
A homogenizáló mező létrehozását a jelzett mező egy bejegyzésére-, majd a bejegyzés mellett megjelenő, Ctrl feliratra kattintva, a Szá­mí­tott oszlop hozzáadása és logikai/numerikus/dátum adattípusúra konvertálása paranccsal kezdeményezhetjük.
mezők adattípus szerinti homogenizálása PowerPivot tábla vágólapos létrehozásakor
A vegyes adattípusú, döntően idő bejegyzéseket (ó:pp:mm) tartalmazó mező automatikus homogenizáló képlete hibás! A képletet a me­ző tetszőleges bejegyzésére kattintva, a szerkesztőlécen javíthatjuk: =IF( NOT( ISERROR( TIMEVALUE( [mezőnév] ))) ; TIMEVALUE( [me­ző­név] )). A többségében dátum-idő (éééé. hh. nn. ó:pp:mm) bejegyzéseket tartalmazó mező homogenizálására a bővítmény kép­le­te nem al­kal­mas! A minden dátum-tartalomra használható képlet ez: =IF( NOT( ISERROR( DATEVALUE( [mezőnév] ))) ; DATEVALUE( [me­ző­név] ) + TIMEVALUE( [mezőnév] )).
    A pénznem és tizedes tört adattípusú bejegyzéseket az INT függvény, a számegyenesen balra elmozdulva, egészre alakítja át. Pél­dá­ul a tizenkettő egész három (12,3) tizenkettő (12), a mínusz tizenkettő egész három (-12,3) mínusz tizenhárom (-13) lesz.
    A homogenizáló számított mező adattípusát a bővítmény a Kezdőlap, Formátum, Adattípus: felirat után jeleníti meg „Automatikus (adat­tí­pus neve)” formában.
    Az oszlopok homogenizálását a tábla-készítés előtt is elvégezhetjük. Az egyik megoldás lehet, egy segédoszlop alkalmazása, amely­ben a kívánt adattípustól eltérő cellatartalmakat képlettel töröljük. A művelethez az Excel típusellenőrző függvényeit használhatjuk. A szö­veg-, a logikai-, a pénznem-, az egész-, és a tizedes tört adattípusok detektálása ezekkel a függvényekkel nem okoz problémát, de a dá­tu­mok és az időpontok azonosítására alkalmas CELLA függvény, magyar felhasználói környezetben, erre a feladatra, csak erősen kor­lá­to­zott mér­ték­ben alkalmas.
oszlopok homogenizálása PowerPivot tábla vágólapos létrehozása előtt
A képletek nem tökéletesek, mert a fent ismertetett probléma miatt, az egész számok között a dátumok, a tizedes törtek között az idő­pon­tok is megjelennek.
    A tábla-készítést megelőző felhasználói tevékenységek része lehet egy képleten alapuló feltételes formázás, amellyel megjeleníthetjük a homogenizálás során „törlődő” adatokat.
PowerPivot tábla vágólapos készítését megelőző homogenizálás törlődő adatainak feltételes formázása
Természetesen ezek a képletek sem tökéletesek, a fent ismertetett okok miatt: az egész számok között a dátumok, a tizedes törtek kö­zött az időpontok is formázatlanul jelennek meg.
PowerPivot tábla vágólapos készítését megelőző homogenizálás törlődő adatainak feltételes formázása
A Kezdőlap, Vágólap, Beillesztés hozzáfűzéssel utasítással újabb rekordokat másolhatunk az elkészült táblába. Amennyiben a vágólapon álló táblázat oszlopainak száma azonos a kibővítendő tábla mező-számával, akkor a bővítmény megjeleníti a Beillesztés villámnézete pa­rancs­táb­lát. A panel tartalmazza a bővítendő tábla első húsz rekordját (felül) és a hozzáfűzni szándékolt táblázat első tizenkilenc sorát (a­lul). A vágólapon lévő adatok első sorában álló mezőneveket a két listát követő jelölőnégyzettel deklarálhatjuk. A tábla rekordjait meg­je­le­ní­tő területrész magasságát a terület alsó szegélye alatti, láthatatlan vezérlővel szabályozhatjuk. Az eszközt csak az átalakuló egér-mu­ta­tó jelzi.
    Az új tábla létrehozásakor és az újabb rekordok hozzáadásakor megjelenített parancstáblák különbözőek, de feliratuk azonos: Be­il­lesz­tés villámnézete. Megnyitáskor a panel alján megjelenített üzenetet, „Típuseltérés. A(z) „<oszlopnév>” oszlopba illesztendő adatok típusa nem helyes (<oszlop adattípusa>) legalább egy sor esetében.”, lehet, hogy csak a még nem deklarált oszlop-nevek generálják!
beillesztés hozzáfűzéssel művelet villámnézete
A felső, piros nyíl a láthatatlan vezérlőre, az alsó a még nem deklarált oszlop-nevekre mutat. Az egyesítő tábla létrehozása után a további táblázatok sorainak beillesztésekor a bővítmény a mező adattípusára konvertálja az attól eltérő típusú adatokat. Ha ez nem lehetséges, ak­kor a PowerPivot a Beillesztés villámnézete panel alján a Típuseltérés… kezdetű, a cél-mező nevét is tartalmazó szöveget jelenít meg. Több hiba esetén, jobbról balra haladva, az első megsértett adattípusú cél-mező nevét olvashatjuk az üzenetben. Az adattípus-hiba meg­hi­ú­sít­ja a sorok beillesztését. Ha az oszlop-neveket is a vágólapra másoltuk, akkor ne felejtsük el ezt, a panel bal alsó sarkában álló je­lö­lő­négy­zet­tel közölni, különben a nevek is adattípus-hibát okoznak. Az alábbi táblázatban számba veszem az egyes adattípusok ke­ze­lé­sé­nek módját Beillesztés hozzáfűzéssel utasítást követően. A táblázatban az üres cellák az eredeti adat beillesztését jelentik.
adattípus-kezelés beillesztés hozzáfűzéssel művelet során
Tanító bácsi! Déjà vu érzésem van! Nem láthattam én már ezt a táblázatot valahol? Jól emlékszel, Pistike! Az adattípus-kezelés kap­csán, már beszéltem a vágólapos tábla-készítésről… Azért hoztam most újra elő, mert a bővítmény 2019 verziójában kijavították azt a hi­bát, amely­ről ott részletesen meséltem.
letöltés pdf-ben
margitfalvi.arpad@gmail.com