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árjá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 meglepő, 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. Beillesztés hozzáfűzéssel: 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 cseré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 helyezi a kijelölt objektumot.
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, egyszerű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, általánosítva 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étrehozott tábla nem frissíthető! Az importálással létrehozott tábláknál a Beillesztés hozzáfűzéssel és a Beillesztés cserével utasítások nem elérhető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 Beillesztés parancsot a bővítmény ablakában, amelynek hatására a PowerPivot megjeleníti a beillesztendő táblázat tizenkilenc soros villámnézeté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 villámnézete 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 szelektálnunk.
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örteket é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átumokat 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ő adattípusa pénznem 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 adattípusú 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ő felajánlásának egyetlen feltételét mutatja, két különböző megfogalmazásban.
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 homogenizáló oszlop létrehozásának lehetőségére a mezőnév mellett álló jelzés figyelmeztet.
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.
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 mező tetszőleges bejegyzésére kattintva, a szerkesztőlécen javíthatjuk: =IF( NOT( ISERROR( TIMEVALUE( [mezőnév] ))) ; TIMEVALUE( [mező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éplete nem alkalmas! A minden dátum-tartalomra használható képlet ez: =IF( NOT( ISERROR( DATEVALUE( [mezőnév] ))) ; DATEVALUE( [mező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éldá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 (adattí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, amelyben 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átumok é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 korlátozott mértékben alkalmas.
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őpontok 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.
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.
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 parancstáblá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 (alul). 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 megjelení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-mutató 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: Beilleszté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!
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, akkor 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 meghiúsítja 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ó jelölőnégyzettel 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 kezelé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.
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 kapcsá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 hibát, amelyről ott részletesen meséltem.
margitfalvi.arpad@gmail.com
