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 automatikus- é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 mező), az aktí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 mezőt mutatom 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 mező 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, amelyet a bővítmé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 mező 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étrehozá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 kezdeni, 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, Hozzáadás utasítással.
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 billentyű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 megkülönböztetését egyedi ikonok segítik.
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 statisztikai 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évként emlegeti. A névkiegészítő, számos esetben, mindkét változatot tartalmazza. Ezt látjuk a következő képen.
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 tetszőleges 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 statikus, 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ó argumentum 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 elhagyhatóságát jelö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 megadá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 megfelelően összegyűjti 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 szerkesztése közben.
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 argumentumá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ő argumentumá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üggvény és argumentumainak leírása azonos a névkiegészítős bevitelkor megjelenítettel. Az argumentumok szerkesztésére szolgáló felü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 kijelö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 kattintá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 Enter 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ójelének automatikus 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 PowerPivot mindké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ő: <sorszá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ú oszlopot. A mezőnév menü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 PowerPivot 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ármaztatott mező nevében.
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 jelö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 figyelmeztető üzenetet 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.
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ölhetjü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 billentyűvel. A bővítmé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 kimaradt! Ott a visszavoná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 adatbá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árosainak 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ámogatottságának mértékét mutató tizedestört szám).
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ábla mezőire 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 bejegyzé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, amelynek 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, amelyben a hivatkozá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-argumentum, akkor 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 februá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 tartalmazó cellá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ölcsök eladásainak változását a januári értékekhez viszonyítva!
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 hivatkozá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 azonosítja. Tehá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 szempontjá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ő rekordjának képleté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 jelenti. 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 bejegyzé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 mező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” rekordjának régiók mező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-Dunántúl” rekordjá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 RELATED függvény a képletet tartalmazó rekord kapcsolódó rekordjában, az argumentumával meghatározott mező bejegyzését eredményezi. 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 megye 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 kiszámolja az egyes régiók városainak számát.
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űveletsort 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árosainak rekordjai állnak. A függvény egyetlen argumentuma a kapcsolódó rekordokat tartalmazó tábla. A virtuális tábla rekordjait a COUNTROWS 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öbbhö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ó iroda adatbázisá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, amely nemcsak a két tábla kulcs mezőit, de az adott nyelvismeret szintjét is tárolja.
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 egyes 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: RELATEDTABLE( ’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égzi el, amelynek első argumentuma a virtuális tábla, további argumentumai pedig szűrőfeltételek: CALCULATETABLE( RELATEDTABLE( ’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 RELATED 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 elvégzi 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űveletről, csak egy mező bejegyzéseiről van szó: SUMX( CALCULATETABLE( RELATEDTABLE( ’nyelvtudás’ ) ; ’nyelvtudás’[szint] = ”felsőfok” ) ; RELATED( ’nyelvek’[pótlék] )).
És végül a fizetéshez hozzá kell adni a SUMX függvény eredményét.
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 alkalommal mondom el nektek, a táblák frissítése kapcsán. Csókolja a lelketeket: Josef K.!
margitfalvi.arpad@gmail.com
