adattípus-kezelés vágólapos tábla-készítéskor
2018-07-24    Excel 2016
A cikksorozat eddigi darabjaiban az importálásos-, ebben a részben a vágólapos tábla-készítés során alkalmazott adattípus-kezelő a­u­to­ma­tiz­mu­so­kat ismertetem. Az azonos felépítésű, tehát azonos oszlopokat, azonos sorrendben tartalmazó, táblázatokat vágólappal e­gye­sít­het­jük. Az egye­sí­tő tábla létrehozása a bővítmény ablakában, a Beillesztés-, feltöltése a Beillesztés hozzáfűzéssel pa­ranccsal történik.
    Előfordulhat, hogy már a műveletsor első lépésében elakadunk. A villámnézet jóváhagyását követően a bővítmény hibaüzenetet jelenít meg. Az alábbi képen, egy ilyen esetet látunk: a háttérben a vágólapra helyezett tábla, az előtérben a hibaüzenet áll.
PowerPivot tábla vágólapos készítésekor a tizedesvessző okozta programhiba
Ha egyesével próbáljuk az oszlopokat a PowerPivot ablakban „beilleszteni”, akkor kiderül, hogy a problémát a negyedik oszlop okozza. Pon­to­sab­ban: a negyedik oszlopban álló tizedes vesszők. Sajnos ez a jelenség program-hiba, az Excel 2016-os, magyar nyelvű ver­zi­ó­já­ban! Az angol verzió, ebből a szempontból, hibátlan. Az egyetlen, de nem minden esetben tökéletes, megoldás: a tizedes törtek át­a­la­kí­tá­sa beépített, HUF pénznem-formátummá. Természetesen az összes táblázatban! Miután létrehoztuk az egyesítő táblát, a mező a­dat­tí­pu­sát visszaalakíthatjuk Tizedes tört számmá a Kezdőlap, Formátum, Adattípus paranccsal!
    A továbbiakban az Excel 2016-os, angol nyelvű változatában mutatom be, az egyesítő tábla létrehozásával kapcsolatos adattípus mű­ve­le­te­ket. A Paste (Beillesztés) parancsot követő első automatizmus, az egyes mezők adattípusának meghatározása. Először vizs­gál­juk meg az azonos adattípusú adatokat tartalmazó, tehát homogén, forrás-oszlopok új adattípusát.
PowerPivot tábla vágólapos készítésekor alkalmazott adattípus konverziók
A kép hátterében a létrehozott tábla, előtte a vágólapra helyezett Excel táblázat áll. A mezőnevek az automatikusan beállított adattípust mu­tat­ják. A bővítmény szövegnek tekinti a karakter-láncokat, az aposztróffal beírt számokat és a hibaértékeket (például #DIV/0!-t, ma­gya­rul #ZÉRÓOSZTÓ!). A vágólapról beillesztett szövegként formázott számokat, százalékokat, természetes törteket, normál alakban felírt szá­mo­kat, egyéni kóddal formázott számokat és pénzeket a PowerPivot egész vagy tizedes tört számnak veszi. A beépített pénz­nem for­má­tu­mú (HUF) számokat a bővítmény négy tizedesjegyre kerekíti valamint a dátumokat a 0:00:00 időponttal, az időpontokat a 1899.12.31. dá­tum­mal egészíti ki.
    A különböző típusú adatokat tartalmazó forrás-oszlopból a bővítmény Text (Szöveg) adattípusú mezőt készít, két kivétellel: [1] az egész és tizedes tört számokat, [2] számokat és beépített formátumú pénzeket tartalmazó oszlopok. Az előbbi oszlop mindig Decimal Number (Tizedes tört szám), utóbbi Currency (Pénznem) adattípusú mezőt eredményez, az egyes adattípusok előfordulásától füg­getlenül.
PowerPivot tábla vágólapos készítésekor alkalmazott adattípus konverziók
A képen felül a vágólapra helyezett táblázatot, alul az elkészült táblát látjuk. A mezőnevek az automatikusan beállított adattípust mutatják.
    A nem homogén forrás-oszlopú mezők tehát Text (Szöveg) adattípusúak lesznek. Ha azonban az egyik adattípus bejegyzéseinek szá­ma eléri a rekordok számának 95 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ő au­to­ma­ti­kus létrehozását, amelynek adattípusa a többségben lévő adattípus lesz.
a homogenizációs oszlop felajánlásának feltétele PowerPivot tábla vágólapos készítésekor
A képen a homogenizáló mező felajánlásának egyetlen feltétellét látjuk, két különböző megfogalmazásban. A lehetőségre a mezőnév mel­lett álló, pici jelzés figyelmeztet. A vegyes adattípusú, döntően szöveg vagy pénznem adattípusú bejegyzéseket tartalmazó mezők esetén au­to­ma­ti­kus homogenizálás nem lehetséges.
a homogenizációs oszlop felajánlása PowerPivot tábla vágólapos készítésekor
Ha rámutatunk a mezőnév mellett álló, felkiáltójeles ábrára, akkor a bővítmény a Click any cell of the column for more information (To­váb­bi információért kattintson az oszlop bármelyik cellájára) feliratot jeleníti meg, majd követve az utasítást, az Add a calculated column and con­vert to <az oszlopban többségben lévő adattípus> data type (Számított oszlop hozzáadása és < … > adattípusra konvertálása) pa­ranccsal kér­het­jük a mező létrehozását.
    Az alábbi táblázatban tekintsük át, mi történik a többségben lévő típustól eltérő adatokkal a homogenizáló mezőben. Az oszlopok tar­tal­maz­zák a többségben lévő-, a sorok az attól eltérő adattípusokat.
adattípus konverzió PowerPivot tábla vágólapos készítésekor
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ó képlete, amely minden dátum-tar­ta­lom­ra általánosan is használható, ez legyen: =IF( NOT( ISERROR( DATEVALUE( [mezőnév] ))) ; DATEVALUE( [mezőnév] ) + TI­ME­VA­LUE( [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ás során törölt bejegyzések nem szerepelnek majd a pivot táblás elemzésben, ezért célszerű, még az egyesítő tábla el­ké­szí­té­se előtt, megtekinteni ezeket az adatokat. Erre a célra a legalkalmasabb a képleten alapuló feltételes formázás. A képletben az Ex­cel 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 e­zek­kel a függvényekkel nem okoz problémát, de a dátumok és az időpontok azonosítására alkalmas CELLA (CELL) függvény, magyar fel­hasz­ná­lói környezetben, erre a feladatra, csak erősen korlátozott mértékben alkalmas.
adatok homogenizációja PowerPivot tábla vágólapos készítése elött
A képletek nem tökéletesek, mert a fent ismertetett probléma miatt, [1] az egész számok között a dátumok, [2] a tizedes törtek között az idő­pon­tok is formázatlanul jelennek meg.
PowerPivot tábla vágólapos készítése elött alkalmazott homogenizáció hibái
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 el­té­rő típusú adatokat. Ha ez nem lehetséges, akkor a PowerPivot a Paste Preview (Beillesztés villámnézete) panel alján a Type Mismatch… (Tí­pus­el­té­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ő meg­sér­tett a­dat­tí­pu­sú cél-mező nevét olvashatjuk az üzenetben. Az adattípus-hiba meghiúsítja a sorok beillesztését. Ha az oszlopneveket is a vá­gó­lap­ra má­sol­tuk, 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 okoz­nak. Az alábbi táb­lá­zat­ban számba veszem az egyes adattípusok kezelésének módját Paste Append (Beillesztés hozzáfűzéssel) uta­sí­tást kö­ve­tő­en. A táb­lá­zat­ban az üres cellák az eredeti adat beillesztését jelentik.
a beillesztés hozzáfűzéssel művelet során alkalmazott adattípus-kezelés
Visszatérve a magyar verzió hibájára és javítására: a tizedes törtek átformálása pénznemmé, majd az egyesítő tábla elkészülte után, visszaalakítása tizedes törtté, adatvesztéssel járhat. Ennek oka, mint láttuk, a Currancy (Pénznem) adattípusú mező forrás-adatainak négy tizedes jegyre történő kerekítése. Az angol verzióban bemutatott minden további művelet a magyar változatban is működik, de a hiba ja­ví­tá­sá­ból eredő, esetleges plusz feladatokról nem szabad elfelejtkeznünk!
ismertető letöltése pdf-ben munkafüzet letöltése
margitfalvi.arpad@gmail.com