számított mező
2018-11-16    Excel 2016
A bővítményben három, DAX képleten alapuló, objektumot hozhatunk létre: [1] a táblában számított mezőt, [2] a pivot táblában a­u­to­ma­ti­kus- és [3] egyéni összesítést. A számított objektumokat felhasználói névvel azonosítjuk, tárolásuk a tartalmazó táblában (számított me­ző), az ak­tív táblában (automatikus összesítés) illetve egy kiválasztott táblában (egyéni összesítés) történik. Ebben a cikkben a számított me­zőt mu­ta­tom be. A szöveg a PowerPivot 2016-os verziója alapján készült.
    A bővítmény táblájában a rekordok nem módosíthatók, de a számított mező lehetőséget nyújt adatainak feldolgozására. A számított me­ző tehát egy felhasználói névvel azonosított, származtatott objektum. Másként fogalmazva, a számított mező egy DAX képlet, a­me­lyet a bő­vít­mény a tábla minden egyes rekordjában elhelyez és kiszámol. A számított mező egyenrangú a tábla többi mezőjével, így má­sik szá­mí­tott me­ző képletében is szerepelhet.
    A tábla forrás-mezői után álló oszlop egy cellájára kattintva vagy az F2 billentyűt megnyomva foghatunk hozzá a számított mező lét­re­ho­zá­sá­hoz. A mezőnevek felett álló szerkesztősor felépítése azonos a programéval. A képletet a bővítményben is egyenlőségjellel kell kez­de­ni, de a pluszjel, mint képlet-jelző a DAX-ban nem használható. A szerkesztést a szalagról is indíthatjuk a Tervezés, Oszlopok, Hoz­zá­a­dás u­ta­sí­tás­sal.
    A nevek beírását a bővítmény névkiegészítője segíti. Listájában a le- és a fel nyíl-billentyűkkel lépegethetünk. A kiválasztott elemet a TAB bil­len­tyű­vel vagy rámutatással és dupla kattintással tudjuk beíratni a képletbe. A listában a függvény-, a tábla és a mezőnevek meg­kü­lön­böz­te­té­sét egyedi ikonok segítik.
PowerPivot, a DAX képlet szintaktikai szabályai
Ahogy a képen is megfigyelhetjük a táblanevek aposztrófok, a mezőnevek szögletes zárójelek között állnak. Ha a táblanév nem tartalmaz szó­közt és ékezetes karaktert, akkor az aposztrófok elhagyhatók. A mezőnevek a számított mező képletében általában önállóan állnak, de a sta­tisz­ti­ka­i függvény többsége a teljes mezőnevet kéri, ’táblanév’[mezőnév] formában. A teljes nevet a bővítmény „minősített” név­ként em­le­ge­ti. A névkiegészítő, számos esetben, mindkét változatot tartalmazza. Ezt látjuk a következő képen.
PowerPivot, a DAX képlet szintaktikai szabályai
A gépelés nélküli képletszerkesztést nem csak a névkiegészítő, de a kattintásos névbevitel is segíti. A tábla nevét a fülére, a mező nevét tet­sző­le­ges bejegyzésére, az összesítés nevét a számítási területen álló cellájára kattintva is beírathatjuk a képletbe.
    A programhoz hasonlóan, a bővítmény is megjeleníti a szerkesztés alatt álló függvény argumentum-listáját, de a kis szövegdoboz itt sta­ti­kus, nem mozgatható, elemei sem nyújtják a megszokott szolgáltatásokat: függvény leírás, argumentum kijelölés. A szerkesztés alatt álló ar­gu­men­tum nevét azonban a bővítmény is félkövér betűkkel jelzi a listában és a szögletes zárójelek itt is az argumentum el­hagy­ha­tó­sá­gát je­lö­lik.
    A függvények bevitelét további két szolgáltatás is segíti. [1] A névkiegészítő mindig csak a szerkesztés alatt álló argumentum meg­a­dá­sá­hoz szükséges objektum-neveket jeleníti meg. Tehát, a bővítmény figyeli, hogy melyik argumentumot szerkesztjük, és ennek meg­fe­le­lő­en össze­gyűj­ti a szóba jöhető objektumokat. [2] A hibás argumentumra a bővítmény színes aláhúzással figyelmeztet a képlet szer­kesz­té­se köz­ben.
a DAX képlet összeállítását segítő szolgáltatások
A hátsó, nagy képen a SUMMARIZE függvényt látjuk szerkesztés közben. A névkiegészítő listája csak a függvény második ar­gu­men­tu­má­ban használható függvényeket és objektumokat tartalmazza. Az elől álló, kis képen a bővítmény hibásnak ítélte a SUMX függvény első ar­gu­men­tu­má­ban megadott mezőt, mert ott egy tábla nevének kellene állnia.
    Sajnos a bővítmény függvény varázslójának nem adtak varázspálcát: se a függvényről, se argumentumairól nem ad plusz információt: a függ­vény és argumentumainak leírása azonos a névkiegészítős bevitelkor megjelenítettel. Az argumentumok szerkesztésére szolgáló fe­lü­let pedig hiányzik. Egyszóval: gagyi.
    A szerkesztőlécen dolgozva használhatjuk a kijelölés (Shift+Jobbra nyíl/Balra nyíl, Shift+Home/End, Ctrl+A) és a vágólap-műveletek (Ctrl+X, Ctrl+C, Ctrl+V) billentyűparancsait. Utóbbi művelet-csoport a szerkesztőléc helyi menüjében is megtalálható, Az összes ki­je­lö­lé­se utasítással együtt. A szerkesztőléc magasságát a felhasználó, a programban megismert módokon szabályozhatja. Egyedül a dupla kat­tin­tá­sos magasság-állítás nem működik az alsó szegélyen.
    A szerkesztés az Esc billentyűvel vagy a képletszerkesztő blokk Mégse nyomógombjával szakítható meg. A képlet kiértékelését az En­ter billentyűvel vagy a képletszerkesztő blokk OK gombjával kezdeményezhetjük. Az egyetlen függvényből álló képletek záró zá­ró­je­lé­nek a­u­to­ma­ti­kus bevitele a bővítmény ablakában nem működik, de az argumentum nélküli függvények névkiegészítős bevitelekor a Pow­er­Pivot mind­két zárójelét beírja a képletbe.
    A képlet szerkesztésének befejezése után a bővítmény rekordonként kiértékeli a kifejezést és az eredményeket a memóriában tárolja. Ha a művelet minden rekordban sikeres, akkor az eredmények megjelennek a mezőben, melynek automatikus neve: Számított mező: <sor­szám>. A számított mező létrehozása után a bővítmény a tábla jobb oldalán ismét megjeleníti az Oszlop hozzáadás feliratú osz­lo­pot. A me­ző­név me­nü­jé­ből az automatikus név felhasználói névre cserélhető.
    A bővítmény 2016-os verziójában a számított mező neve a táblában sötétebb háttérszínnel van formázva, de a névkiegészítőben és a Pow­er­Piv­ot mezőlistán már semmi megkülönböztetés sincs. Akit zavar ez az egyformaság, például engem, az használjon nagybetűket a szár­maz­ta­tott mező nevében.
PowerPivot, számított mező megjelenítése
Ha bénák voltunk, akkor a kiértékelhetetlen kifejezés #HIBA bejegyzést eredményez a tábla minden rekordjában. A hibát pici rombusz je­lö­li [1] a tábla fülén, [2] a mezőnévben és [3] a hibás mező aktív bejegyzése mellett. A színes rajzocskára mutatva a bővítmény fi­gyel­mez­te­tő ü­ze­ne­tet jelenít meg. A bejegyzés jobb oldalán álló gomb menüjében két utasítást találunk: Hiba megjelenítése… és Ugrás az első hibára. Utóbbi felirat magyarul: ugrás az első rekordra.
PowerPivot, hibás képlet jelölése
A számított mező másolásakor a bővítmény nem a képleteket, hanem annak eredményét helyezi a vágólapra. A kijelölt számított mezőt tö­röl­het­jük [1] a Tervezés, Oszlopok, Törlés paranccsal, [2] nevének menüjéből az Oszlopok törlése utasítással és [3] a Delete bil­len­tyű­vel. A bő­vít­mény nem sokat laca-facázik! Egy pillanat és már el is tűnt a mező. És a megerősítéssel mi lesz, tessék mondani! Az ki­ma­radt! Ott a vissza­vo­nás parancsgomb a gyorselérésű eszköztáron!
    Most, hogy már mindent tudunk a számított mezőről, beszéljünk egy kicsit a képlet tartalmáról is. Először vegyünk egy háromtáblás a­dat­bá­zist, amely városok EU-os támogatásait tartalmazza. A táblák és mezőik a következők. [1] régiók: régió, alap (az adott régió vá­ro­sa­i­nak járó alaptámogatás, milliárd forintban). [2] megyék: megye, régió. [3] városok: város, megye, osztály (az adott város tá­mo­ga­tott­sá­gá­nak mér­té­két mutató tizedestört szám).
számított mező PowerPivot adatbázisban
A városok táblában létre kellene hoznunk egy számított mezőt, TÁMOGATÁS néven, amely a tényleges támogatást mutatná, ami az alap és az osztály mezők szorzata. Valahogy így: =’régiók’[alap] * [osztály]. A számított objektum a városok táblában áll, ezért a városok táb­la me­ző­i­re táblanevük elhagyásával is hivatkozhatunk ( [osztály] ), de egy másik tábla mezőivel ezt már nem tehetjük meg ( ’régiók’[alap] ).
    Tehát a bővítménynek ki kellene számolnia a városok tábla minden rekordjában a képletünket. Vegyük a városok tábla első rekordját! Tab. (Nem a billentyű, hanem a város.) Most a két mező melyik bejegyzésével dolgozzon a bővítmény? Hiszen a két hivatkozás nem egy-egy be­jegy­zést, hanem két bejegyzés-halmazt azonosít!
    Ez azonban csak általánosságban igaz, mert a mezőhivatkozás értelmezését két tényező határozza meg. [1] Az objektum típusa, amely­nek a képletében az azonosító áll. Ez lehet számított mező illetve automatikus- vagy egyéni összesítés. [2] Maga a képlet, amely­ben a hi­vat­ko­zás áll. Önállóan szerepel az azonosító a képletben, vagy egy függvény argumentumában? Ha a hivatkozás függvény-ar­gu­men­tum, ak­kor melyik függvény argumentuma?
    Az értelmezést meghatározó második tényező megértéséhez mutatok egy Excelből vett példát! Van egy táblázatunk: januári és feb­ru­á­ri gyü­mölcs-eladások, mázsában. A táblázat két adat-tartománya a januári eladásokat tartalmazó cellák és a februári eladásokat tar­tal­ma­zó cel­lák. Nevezzük el a tartományokat: január, február! Adjuk össze a januári-, majd a februári eladásainkat! Számoljuk ki az egyes gyü­möl­csök el­a­dá­sa­i­nak változását a januári értékekhez viszonyítva!
a mezőnevek értelmezése a DAX-ban
A SZUM függvény argumentumában a tartomány-hivatkozás a januári illetve a februári összes eladásokat azonosítja, de ugyanez a hi­vat­ko­zás a gyümölcsök képleteiben a képlettel azonos sorban álló gyümölcs, januári- illetve februári eladását tartalmazó cellát a­zo­no­sít­ja. Te­hát a program „rugalmasan” értelmezi a képletben álló tartomány-hivatkozásokat.
    A bővítmény hasonlóan „rugalmas”! De térjünk vissza Tab város támogatásának kiszámításához. A számított mezőt tartalmazó tábla szem­pont­já­ból beszélhetünk saját- (osztály) illetve idegen mezökről (alap). A bővítmény számára a számított mező képletében álló, saját mezőre történő hivatkozás, a feldolgozás alatt álló rekord, hivatkozott mezőjének bejegyzését jelenti. Magyarul, a városok tábla első re­kord­já­nak kép­le­té­ben az [osztály] hivatkozás Tab város osztályát, azaz hármat (3) jelent.
    A bővítmény számára, a számított mező képletében álló, idegen mezőre történő hivatkozás, a hivatkozott mező összes bejegyzését je­len­ti. Magyarul, a bővítmény nem tudja kiszámítani Tab város EU-s támogatását, mert nem határoztuk meg, hogy az alap mező hét be­jegy­zé­se kö­zül melyikkel dolgozzon a bővítmény.
    [Megfejtés] Tab város alaptámogatását a kapcsolatok segítségével tudjuk megállapítani. A feldolgozás alatt álló rekord megye me­ző­jé­nek (a városok és a megyék táblák kapcsoló mezője) bejegyzése „Somogy”. Tehát meg kell néznünk a megyék tábla „Somogy” re­kord­já­nak ré­gi­ók me­ző­jé­ben (a megyék és a régiók táblák kapcsoló mezője) álló bejegyzést: „Dél-Dunántúl”. Most már csak a régiók tábla „Dél-Du­nán­túl” re­kord­já­nak alap mezőjében álló bejegyzését kell megkeresnünk. 3,9 milliárd Ft. Tehát akkor: 3 * 3,9 = 11,7 milliárd Ft.
    Ezt a műveletsort a képletben a RELATED függvény végzi el. Egyetlen argumentuma egy mezőnév. Általánosan megfogalmazva: a RE­LAT­ED függvény a képletet tartalmazó rekord kapcsolódó rekordjában, az argumentumával meghatározott mező bejegyzését e­red­mé­nye­zi. Most már összeállíthatjuk a végleges képletünket: = RELATED( ’régiók’[alap] ) * [osztály].
    Egy másik megfogalmazásban: a RELATED függvény az egy oldali táblában keresi a kapcsolódó rekordot: a város megyéjét, majd a me­gye régióját. De hogyan történik mindez a másik „irányban”? Hozzunk létre számított mezőt a régiók táblában, V_SZÁMA néven, amely ki­szá­mol­ja az egyes régiók városainak számát.
DAX, a RELATED függvény alkalmazása
Most tehát a több oldali táblában keressük a kapcsolódó rekordokat: a régió megyéit, majd az eredményül kapott megyék városait. Ezt a mű­ve­let­sort a RELATEDTABLE függvény végzi a képletben. Eredménye egy virtuális tábla (városok), amelyben a feldolgozás alatt álló ré­gi­ó vá­ro­sa­i­nak rekordjai állnak. A függvény egyetlen argumentuma a kapcsolódó rekordokat tartalmazó tábla. A virtuális tábla rekordjait a COUNT­ROWS függvénnyel számoltathatjuk meg. A függvény egyetlen argumentuma a megszámlálandó rekordokat tartalmazó tábla. Most már össze tudjuk állítani a V_SZÁMA számított mező képletét: =COUNTROWS( RELATEDTABLE( 'városok' )).
    A régiók-megyék és a megyék-városok tábla-párosok egy a többhöz kapcsolatban állnak egymással. De két tábla között létezhet több a több­höz viszony is, ami azt jelenti, hogy a táblák egy tetszőleges rekordjához a másik tábla több rekordja tartozhat. Vegyük egy fordító i­ro­da a­dat­bá­zi­sá­nak fordítók és nyelvek tábláit: egy fordító több nyelv és egy nyelv több fordító. A táblákat a nyelvtudás tábla kapcsolja össze, a­mely nemcsak a két tábla kulcs mezőit, de az adott nyelvismeret szintjét is tárolja.
számított mező a PowerPivot adatbázisban
A fordítók tábla fizetés mezőjében a munkatárs alapbérét tároljuk. Ezt az összeget növeli a felsőfokú nyelvtudásárért járó kiegészítés. Az e­gyes nyelvek után járó pótlékok különbözőek. Hozzunk létre számított mezőt a fordítók táblában, JÖVEDELEM néven, amely a fizetés és a pótlékok összegét adja eredményül.
    Bontsuk elemeire a műveletsort. Először ki kell válogatni a feldolgozás alatt álló fordító kapcsolódó rekordjait a nyelvtudás táblában: RE­LAT­ED­TABLE( ’nyelvtudás’ ).
    De nekünk csak a felsőfokú rekordok kellenek az eredményül kapott virtuális táblából. Ezt a műveletet a CALCULATETABLE függvény vég­zi el, amelynek első argumentuma a virtuális tábla, további argumentumai pedig szűrőfeltételek: CALCULATETABLE( RE­LAT­ED­TABLE( ’nyelvtudás’ ) ; ’nyelvtudás’[szint] = ”felsőfok” ).
    A szűrt virtuális tábla minden rekordjához a nyelvek tábla egy rekordja kapcsolódik. Az ezekben a rekordokban álló pótlékokat a RE­LAT­ED függvény adja eredmányül: RELATED( ’nyelvek’[pótlék] ).
    A pótlékok összegét a SUMX függvénnyel képezhetjük, amely az első argumentumával meghatározott tábla minden rekordjában el­vég­zi a második argumentumával deklarált műveletet, majd az így képzett eredmény-halmaz elemeit összeadja. A mi esetünkben nem mű­ve­let­ről, csak egy mező bejegyzéseiről van szó: SUMX( CALCULATETABLE( RELATEDTABLE( ’nyelvtudás’ ) ; ’nyelvtudás’[szint] = ”felsőfok” ) ; RE­LAT­ED( ’nyelvek’[pótlék] )).
    És végül a fizetéshez hozzá kell adni a SUMX függvény eredményét.
DAX, a SUMX és a RELATED függvény alkalmazása
Kedves Gyerekek! Elég hosszú lett már ez a mese, nem fárasztalak benneteket a képletek újraszámolásával! Azt majd egy másik al­ka­lom­mal mondom el nektek, a táblák frissítése kapcsán. Csókolja a lelketeket: Josef K.!
ismertető letöltése pdf-ben munkafüzet letöltése
margitfalvi.arpad@gmail.com