már 21

Két vonal közti terület kiszínezése

area-lineaKolléganőmtől kérték, hogy készítsen olyan grafikont, amin két év adatait mutatja be, és zölddel színezi, amikor az idei év adatai meghaladják a tavalyit, pirossal, amikor alatta maradnak. Sajnos nem volt alkalmam gyorsan segíteni neki – picit át kellett volna alakítani Roberto egy régebbi megoldását, de erre nem volt már idő – ezért ebben a bejegyzésben közzéteszem a sablon fájlt, remélve, hogy sokaknak hasznos lesz.

UPDATE: Megújítottam a fájlt, hogy negatív számokat is tudjon kezelni!

Innen letölthetitek a fájlt, amiben megtaláljátok azt is, hogyan kell beírni az adatokat. Fontos, hogy a tengelyfeliratok hozzárendelése csak 2013-as Excelben működik. Ha korábbi verziót használtok, kézzel kell a feliratokat a diagramra feltenni.

A fájl maximum 100 sornyi adat kezelésére alkalmas, a sárga cellákba írhatjátok az adatokat, a tengely oszlopba pedig, hogy mi legyen a vízszintes tengely felirata.

A szürke cellákban levő számok a vízszintes tengely pozícióit jelölik. Alapvetően 0-val kezdődő számsornak kell itt lennie. Ha xy koordináta párokat szeretnél megjeleníteni, akkor értelemszerűen ezek lesznek az x koordináták, egyéb esetben csak egy folytatólagos számozásra van szükséged.

area-linea_1

A diagramról annyit kell tudni, hogy xy scatter és terület diagram kombinációja, ahol a terület diagram elemek vízszintes tengelybeosztása át van skálázva a megfelelő vonal-keresztezések kezeléséhez. A gyakorlatban ebből annyi a lényeg, hogy ha a vonalak színét vagy a kitöltő színeket akarod megváltoztatni, akkor a megfelelő adatsor kiválasztása után a formázás menüben tudod ezt megtenni. (Vagy jobb klikk a formázandó diagram elemen: )

area-linea_2

Az diagram építés elméleti hátterét itt most nem részletezem – a munkalapon láttok sok számoszlopot, amik az adatsorokat felépítik. Akit érdekel, annak esetleg egy kávé mellett elmondom majd, hogyan működik. :-)

Örülnék, ha kommentben megírnátok, mire-hogyan használtátok a sablont! :-)

 

már 16

Hyperlink és legördülő lista kombinálása

A hyperlink egyszerű, gyakran használt eszköz a munkafüzetben való navigáció megkönnyítésére: könnyű vele egyik munkalapról a másikra ugrani. Gondoltatok rá, hogy ha sok munkalapunk van, milyen egyszerű lenne kiválasztani egy legördülő listából, hogy hová akarunk ugrani? Nem kell nagy helyet elfoglaló link-listát fenntartani minden munkalapon: csak kiválasztod és rákattintasz – ahogy az ábrán látod: a munkalap nevek olasz városok, ezek jelennek meg a legördülő listában:

H_name2

Igen, ezt meg lehet csinálni – mutatom, hogyan!

Alapvetés

Először nézzük, hogyan működik a hyperlink beszúrás! A Hyperlink beszúrása menüpontban nem csak munkalap cellákat, hanem elnevezett tartományokat is ki lehet választani – így ezekhez egyszerűen kapcsolható hyperlink, ami rögtön a tartományhoz ugrik. Azonban ha jobban megfigyelitek, nem jelennek itt meg azok az elnevezések, amelyek mögött formula áll – hiába ad vissza tartományt a formula. Csak a “statikus”, közvetlen cella referenciát tartalmazó neveket lehet itt a hyperlinkhez adni.

H_name1

Kérdés tehát: Hogyan tudunk hyperlinket kapcsolni olyan elnevezéshez, ami formulát tartalmaz?

Kicsit be kell ehhez csapni az Excelt: az elnevezést azután változtatod meg, hogy hyperlinkként használtad! Tehát:

  1. először csinálj egy elnevezést, ami tartományra hivatkozik
  2. tedd bele a hyperlinkbe
  3. majd változtasd meg az elnevezést: írd be a formulát, amit használni szeretnél.

Ez lesz a megoldásunk alapja. Nézzük lépésről-lépésre, hogyan lehet felépíteni a legördülő listával kombinált hyperlinket!

1. Munkalap nevek listája

Szükséged lesz egy elnevezett tartományra, ami a legördülő listában használni kívánt munkalap neveket tartalmazza. Írd be egy munkalapra a neveket (ezek az példa szerint az olasz városnevek) és nevezd a tartományt Sheets_List -nek.

H_name3

2. Érvényesítési lista

Fontos, hogy minden munkalapon ugyanabba a cellába kerüljön majd az érvényesítés! Az egyik munkalapon, ahol használni akarod a trükköt, készíts érvényesítést (Data validation) legördülő listával, a Sheets_List nevet használva.

H_name4

Ez eddig ugye semmi extra: ki lehet választani egy munkalap nevet a listából, és nem történik semmi. Nézzük tovább!

3. Elnevezés készítése a hyperlinkhez

Csinálj egy elnevezést My_Sheet_Choice névvel, ami az aktuális aktív cellára hivatkozik – ezt fogjuk majd megváltoztatni.

H_name5

4. Hyperlink felrakása a cellára

Szúrd be a hyperlinket a cellára, ahova a legördülő listát tetted! A hyperlink a My_Sheet_Choice névre mutasson – ahogy az alábbi ábrán látod.

H_name6

5. A trükk: hivatkozás átírása

Változtasd meg a hivatkozást a My_Sheet_Choice elnevezésben! Olyan formulát fogunk berakni a név referenciájába, ami az adott legördülő listában kiválasztott munkalap celláját adja vissza – így fog a hyperlink a megfelelő munkalapra ugrani.

=INDIRECT( ADDRESS( 1,2,,,INDIRECT( ADDRESS(1,2) ) ) )

=INDIREKT( CÍM( 1;2;;;INDIREKT( CÍM(1;2) ) ) )

Figyelem! a fenti formula a példában bemutatott esethez tartozik, amikor a B1 cellában van a legördülő lista. Ha te máshova tetted, mindkét CÍM formulában kell átírnod a sor és oszlop számot: első paraméter a sor, második az oszlop száma: 1;2 -> B1.

H_name7

6. Hyperlink másolása

Minden munkalapra, ahol használni szeretnéd a legördülő listás hyperlinket, másold át a cellát, ami a trükkös linket tartalmazza. Figyelj, hogy minden munkalapon ugyanoda kerüljön a linkes cella!

Hogyan működik?

A beágyazott INDIREKT( CÍM(1;2) ) formula kiolvassa a munkalap nevet, amit a B1 cellába írtál. (B1-nek a CÍM formulában az 1;2 paraméter felel meg.) Ez a munkalap név lesz a külső CÍM formula 5. paramétere. Az első két paraméterrel együtt, ami itt is az 1;2 (sor;oszlop) hivatkozás, a formula cella referenciát ad vissza. Összerakja a cellát és a munkalap nevet a megfelelő (szöveges) formátumba. Például: ‘New York’!$B$1 -> aposztrófok közé kerül a munkalap név, és felkiáltójel jön a cellahivatkozás elé. Ebből a szöveges referenciából csinál az INDIREKT valódi cella hivatkozást, amit aztán az elnevezett tartományon keresztül megkap a hyperlink, így ez a dinamikus hivatkozás fogja a hyperlinket a megfelelő munkalapra vezetni.

Innen letölthetitek a példa fájlunkat, amiben fel van építve egy működő modell.

Az elnevezett tartományok alapjairól itt olvashatsz.

Ez az írás egy korábbi angol cikkünk alapján készült.

 

már 03

Mennyiségi egység beírása a szám mögé: egyéni adat formázás

1kgEbben a bejegyzésben visszatérünk az alapokhoz: röviden leírom, hogyan kell adatot bevinni az Excelbe – úgy, hogy azzal utána számolni is tudjunk. A cikk alapvetően kezdőknek szól, de haladók sem mindig ismerik azt a formázási beállítást, amit mutatni fogok: hogyan írjuk a számadat mögé, hogy kg, fő, liter, stb.

Az Excelt általában azért használjuk, mert számolni szeretnénk vele. Számolni pedig csak számokkal lehet – ez elég triviálisan hangzik. De amikor a kezdő felhasználó elkezdi összerakni az első táblázatát, amibe beírja, hogy naponta hány kg terméket állítottak elő, akkor adódhat némi “félreértés” a felhasználó és az Excel között.

Az így bevitt adatot nem lehet összeadni:

kg1

Fontos, hogy a cellába mindig csak egyféle adat kerüljön. Amit itt látunk az két adat: szám és mennyiségi egység. Ezeket külön kell választani, hogy a számok önmagukban szerepeljenek:

kg2

A mennyiségi egységet (kg) külön oszlopba írtam, így a SZUM képlet már működik. Figyeld meg, hogy a számok jobbra vannak igazítva, a szöveg pedig balra. Ez az Excel alap beállítása, erről fel lehet ismerni, hogy számadat van a cellában.

Másik megoldás: Egyéni formázás

Haladó felhasználók választhatják azt a megoldást, hogy az Excelt “kérik meg”, hogy írja a számadat mögé a mennyiségi egységet. Ezt nagyon egyszerűen a Formátum beállításával tudod megtenni. Jelöld ki a számokat, majd a Kezdőlap menü közepén a Szám menücsoportban válaszd a legördülő menüből a További számformátumokat.

A Kategória alatt kattints az Egyénire, és írd be a formátumkódot: 0″ kg”. (0 után idézőjelben egy szóköz és kg)

kg3

A formátum kód nagyon egyszerűen épül fel: a 0 azt jelenti, hogy a cellába írt számot megjeleníti, tizedes jegy nélkül. Utána időzőjelbe a szám mögé írandó karaktersort tesszük: a szóköz azért kell, hogy szépen elváljon a mennyiségi egység a számtól. A Minta mezőben látható, hogyan fog kinézni az adat.

Így néz ki a formázott adat:

kg4

Figyeld meg, hogy a cellában balra van igazítva a szöveg – tehát ez itt szám adat! A szerkesztőlécen az eredetileg beírt számot látod, a kg csak a cellában jelenik meg! (Úgy is mondhatnám, a kg valójában nincs ott, nincs beírva, csak az Excel mutatja nekünk.)

Ha tizedes jegyet is szeretnél megjeleníteni, így írd a kódot: 0,0″ kg” – ahány nullát írsz a kódba a vessző után, annyi tizedes jegyet fogsz látni a munkalapon is.

Kapcsolódó írás:

“Ott van a számban a pont”

 

feb 27

Sor, oszlop beszúrás letiltása a munkalap egy részén

NemszurbeVan egy nagyon hasznos, de kevéssé ismert trükkös megoldás arra, hogyan lehet megakadályozni sorok, oszlopok beszúrását a munkalap egy részén – a nélkül, hogy a lapvédelmet bekapcsolnád.

Szükség lehet erre például olyan, komplexebb munkalapok esetében, ahol valami összesítés, lista, fix tábla van a munkalap tetején, amit nem szabad “szétvagdosni” sor beszúrással.

 

Íme a trükk:

A sárgával jelölt részen szeretném megakadályozni a sorok beszúrását:

Nemszurbe1a

  1. Jelöld ki a táblázat bal oldalán levő tartományt
  2. Írd be a szerkesztőlécre ezt a formulát: =”x” (egyenlőségjel után egy x idézőjelben)Nemszurbe2
  3. A formulát Ctrl+Shift+Enter-rel zárd le: a kijelölt tartomány minden cellájában megjelenik az x.
    Nemszurbe3
  4. Jöjjön a teszt: sor beszúráskor hibaüzenetet kapsz:
    Nemszurbe4

Ugyanezt meg tudod csinálni az oszlopokra is, a táblázat feletti cellák kijelölésével.

A x helyett más karaktert is beírhatsz, sőt, üres string-et is: =”” (két idézőjel). Arra figyelj, hogy az üres string esetében nem fogod látni, hol van a képlet!

S most egy pici elmélet: Mit csináltunk itt?

Tömbképletet írunk be a cellákba. Erről itt olvashatsz részletesen, de a legfontosabbakat összefoglalom:

  • A tömbképletbe nem szúrható be és nem törölhető oszlop/sor.
  • A tömbképletet az Excel mindig egy képletként kezeli.
  • A tömbképletet csak Ctrl+Shift+Enter-rel tudod módosítani. A tömbképletet bármely cellában módosíthatod, a módosítás az egész tömbre érvényes lesz
  • A tömbképlet kitörléséhez az egész tömböt ki kell jelölni, így Delete gombbal törölhető.

Az oszlopot, sort el lehet rejteni, hogy a felhasználót ne zavarja az, ami a cellákban megjelenik. Én nagyon sokszor használom ezt a megoldást – és eddig nem sok embernek árultam el a trükköt. :-)

 

feb 04

Hogyan távolíthatóak el a kimutatás mező szűrő listából a nem létező elemek?

wtf_s

 

Találkoztatok már olyannal, hogy a kimutatás mező szűrő listájában oda nem illő, régi adatok jelentek meg?

Már réges-rég nincs ilyen vevő, olyan termék, a szűrő lista mégis tele van ilyen “szeméttel”.

Miért történik ez? És hogyan lehet ezeket eltüntetni?

 

 

Így néz ki a kimutatás, amiben az összes vevő látszik, ami az adattáblában szerepel:

Pivot_rendezett_9b

Ez pedig a mező szűrő listája – tele “szeméttel”:

Pivot_rendezett_10b

Hogyan kerülnek ezek be a szűrő listába?

A kimutatás frissítésekor a szűrők megjegyzik (megőrzik) az összes elemet, ami eddig az adott mezőben szerepelt. Ha például az előző havi adatokban még volt “A”, “B”, “C” vevő, de az aktuális hónapban már nincs, hiába törlöd ki az előző havi adatokat és teszed be helyette az újat, ezek a régi vevők továbbra is szerepelnek a szűrő listában. Ez az kimutatás egy sajátossága, de meg lehet változtatni.

Mit lehet tenni?

Be kell állítani, hogy ezeket a régi elemeket ne őrizgesse tovább a szűrő. Jobb kattintás a kimutatáson, válaszd a Kimutatás beállításai menüt. Itt az Adatok fülön találod a Mezőnként megőrzendő elemek száma opciót. A Semmennyit kell beállítanod, majd utána frissíteni a kimutatást, hogy mindig csak az adattáblában létező elemeket lásd.
(Angol Excelben: a PivotTable Options menüben a Data fülön a Retain Items rész alatt válaszd a None-t.)

Pivot_rendezett_11b

Nálunk a gyakorlatban időnkét előfordul, hogy véletlenül egy oszloppal elcsúszva, vagy rossz oszlop sorrenddel másoljuk be az új adatokat az adat táblába. Például a vevő alá kerülnek a márkák. A kimutatás frissítése után a márkák megjelennek a vevő mező szűrőjében, és ott is ragadnak, miután az oszlop sorrendet javítottuk. Ha beállítjuk a megőrzendő elemek számát Semennyire, a listából eltűnnek az oda nem illő elemek.

Érdemes emlékezni erre a beállításra, ha gyakran változnak az adatok a kimutatásod háttértáblájában!

Utóirat: ne felejtsétek el a kimutatást frissíteni a Semennyi beállítása után! :-)

 

feb 02

Kimutatás jelentésszűrő mező legördülő listájának rendezése

A kimutatás és sorba rendezés témakört folytatva ez a bejegyzés is egy kollégám kérdésére ad választ. Ez a kérdés azonban sok évvel ezelőtt hangzott el, és kellett egy kis Google segítséget kérnem annak idején, hogy megtaláljam a megoldást.

Hogyan lehet a kimutatás szűrő mezőjének (jelentésszűrő) legördülő listáját sorba rendezni?

Pivot_rendezett_6a

Látható a képen is, hogy itt nincs sorba rendezési lehetőség, ezért én sem gondoltam arra, hogy ezt meg lehet csinálni. Pedig ez egy jogos felhasználói igény: ha sok elemű listából kell választani, akkor ABC sorrendben könnyebb megtalálni, amit keresünk.

A trükk egyszerű: Le kell rakni a mezőt a jelentésszűrőkből a sorcímkék közé.

Pivot_rendezett_7a

Itt már be lehet állítani a rendezést:

Pivot_rendezett_8a

…majd visszarakva a jelentésszűrőkhöz, a lista ott is rendezett marad.

Ennyi az egész!

S hogy miért látunk a legördülő listában időnként olyan elemeket, amiknek már régen nem kellene ott lennie? Erről szól a következő bejegyzés.

 

feb 01

Sorba rendezés a kimutatásban érték szerint

Régen írtam már kollégám kérdésére választ adó cikket, ezért örültem a mai kérdésnek – duplán is: rövid kérdés, egyszerű válasz, és megint egy hasznos apróság.

Feladat: Kimutatásban kell sorba rendezni az adatokat. Azt szeretnénk, hogy árbevétel szerint csökkenő sorrendben legyenek a márkák:

Pivot_rendezett_1

Ilyen beállításokkal már kezdő felhasználók is lényegre törő, hasznos kimutatásokat tudnak készíteni!

A megoldás nagyon egyszerű, mindössze 3 kattintás:

A Sorcímkék mellett kattints a lefele nyil gombon, és válaszd a További rendezési lehetőségek (More sort option) menüpontot.

Pivot_rendezett_2

A kis ablakban beállíthatod, hogy milyen sorrendben és melyik mező alapján akarsz rendezni:

Pivot_rendezett_3

Alul egy kis összegzést is olvashatsz: “Márka rendezése Összeg / Árbevétel k EUR szerint csökkenő sorrendben”.

Ha a sorcímkékhez több mező is be van húzva, a menüpont tetején tudod kiválasztani a mezőt, amelynek rendezését be szeretnéd állítani:

Pivot_rendezett_5

Így már azt is egyszerűen meg tudod csinálni, hogy a márkákon belül a vevők is az árbevétel nagysága szerint legyenek rendezve. Először állítsd be a márkákra a rendezést árbevétel szerint, aztán a vevőkre is ugyanezt:

Pivot_rendezett_4

Egyszerű, ugye?

És ami a legjobb: frissítés után is megmarad a sorrend – vagyis az Excel automatikusan újra rendezi az adatokat.

A következő bejegyzésben arról olvashattok, hogyan lehet a jelentésszűrő mezőt rendezni.

 

dec 12

PowerPivot: konstans a számított mezőben – tapasztalatok

Nemrég részt vettem az Önkiszolgáló BI workshop-on (itt írtam erről). Azóta már fel is építettem egy adatbázist, amit nagy megelégedéssel használnak a kollégák. Az építgetés közben azonban szembetalálkoztam egy furcsa jelenséggel, ami nekünk a gyakorlatban sajnos bosszúságot okozott. Ebben a cikkben bemutatom, mi volt ez, és hogyan sikerült áthidalni a problémát.

Az adatbázis

Az adatbázisunk cikkszámonkénti értékesítési adatokat (mennyiség, árbevétel, stb.) és egy cikktörzs táblát tartalmaz. A cikktörzs táblában a cikkszámok különböző szempontok szerint csoportosítása van tárolva (termék kategória, márka, stb.) A két táblát a cikkszám mező kapcsolja össze. Fontos, hogy a cikktörzs táblában több cikkszám van, mint amennyire árbevételünk van az adott időszakban (tartalmaz régi cikkeket, más országokban értékesített cikkeket és technikai cikkszámokat is).

A megfigyelt probléma

A PowerPivot-ra épített pivot táblában olyan cikkek, cikk csoportok is megjelentek, melyekre egyáltalán nem volt értékesítési adat. Ez sok esetben több száz üres, adattal nem rendelkező sort jelent, amik bekeverednek a többi cikk közé:

PP_const_1

Miért kerülnek be ezek a cikkek a pivot táblába?

Nyomozás

Megfigyeltem, hogy a gyanús cikkeknél csak a olyan mezőben van érték, ami kalkulált mező, ÉS konstans van a formulában!

Készítettem egy kis példát, hogy jobban érthető legyen.

Ez lesz a cikktörzs tábla:

PP_const_4

Ez pedig az adat tábla:

PP_const_3

Mindkettőt betöltöttem PowerPivot-ba, és a cikkszám mezővel összekapcsoltam.

Látható, hogy a 7, 8, 9 és 10-es számú cikkekre nincs adat – ezek az x és y csoportba tartoznak.

Ezután kalkulált mezőt készítettem az árbevétel növekedés számszerűsítésére:

Növekedés % 1    := DIVIDE( [árbevétel 2015] ; [árbevétel 2014] ; 0 )

A növekedést cikk csoportonként mutatja a pivot tábla:

PP_const_5

Nálunk azonban a növekedés mutatószám definíció szerint a 100%-on felüli részt jelenti, tehát így kell számolnom:

Növekedés % 2    := DIVIDE( [árbevétel 2015] ; [árbevétel 2014] ; 1 ) – 1

Ezzel a mutatószámmal a pivot tábla így néz ki:

PP_const_6

Látható, hogy megjelent az x és y csoport, amire nem volt adat az értékesítési adatok táblában!

A problémát egyértelműen a formula végén található -1 okozza. Felépítettem a kalkulációt IFERROR-ral is:

Növekedés % 3    := IFERROR( [árbevétel 2015] / [árbevétel 2014] – 1 ; 0 )

Ebben az esetben ugyanúgy megjelentek az felesleges sorok.

Csak akkor sikerült kiküszöbölni ezeket a sorokat, ha számítást konstans nélkülivé alakítottam:

Növekedés % 4    := IFERROR( ( [árbevétel 2015] – [árbevétel 2014] ) / [árbevétel 2014] ; 0 )

Mi történt itt?

A táblák közötti kapcsolat (JOIN) segítségével összeállítódik a kalkuláció (virtuális) alaptáblája. Ebben minden cikkszám szerepel, ami megjelenik akár a cikk törzs, akár az adat táblában. Azok a sorok, amelyek üresek (BLANK), vagyis nem szerepelnek az adat táblában, azok nem fognak megjelenni a pivot táblában.

A probléma a kalkulált mezőből ered: a PowerPivot-ban a BLANK + konstans = konstans. Ebben az esetben tehát a kalkuláció az üres sorokra is értéket ad, vagyis ezek benn maradnak a pivot táblában.

Gábor: Ha SQL-ben gondolkodunk, úgy néz ki, mintha a táblákat összekapcsoló JOIN típusa változna meg: mintha INNER JOIN-ból OUTER JOIN lenne. Ez így nem jó, mert olyan kalkulált adatok jelennek meg a végeredményben, amelyek a valóságban nem léteznek – csak azért mert egy bizonyos módon írtuk fel a képletet: ha másképp írjuk fel, akkor ezek a nem létező eredmények nem jelennek meg.

Megoldás

Nos… Nem tudom, miért hasznos ez a furcsa BLANK kezelés, nekem egyenlőre bosszúságot okoz. Szerencsére csak olyan kalkulált mezőket kell felépítenem, ahol a hányadosba be tudom építeni a konstanst – így átalakítva már nem okoz problémát a pivot táblában.

Remélem, másnak is hasznos lesz ez a tapasztalat! Innen letölthetitek a példa fájlunkat is, amiben látjátok a kalkulációt és a pivot táblákat.

nov 22

Pivot cache-ről: együtt frissülő pivot táblák készítése

Pivot1Nem túlzás, ha azt mondom, nálunk, egy nagy multinacionális cégnél a pénzügyi riportok 99%-a pivot táblán alapul – nem tudnánk létezni pivot tábla nélkül… Ahhoz, hogy jól tudjuk használni, felépíteni a pivot táblákat, tudni kell, mi történik a háttérben: mi az a pivot cache. Technikai dolognak tűnik, de aki nem csak “egyszer használatos” pivot táblát készít, az sok kellemetlenségtől megkímélheti magát, ha megfogadja az alábbi javaslatokat – enélkül a pivot tábláink könnyen hatalmas, belassult Excel fájlokat növesztenek.

Tovább a teljes tartalomhoz »

nov 02

Kimutatás szűrő mezők több oszlopba rendezése

Érdekes megoldást láttam egy belső kimutatásban pár nappal ezelőtt: Két oszlopba voltak darabolva a Pivot tábla szűrő mezői (magyarul lapozó mezők, vagy jelentésszűrők).

Valahogy így nézett ki:

Pivot2col01a

Nos, NAGYON egyszerű ezt megcsinálni!

Tovább a teljes tartalomhoz »

okt 13

PowerPivot szeletelő (Slicer) szűrőfeltételeinek kiolvasása tömbképlettel

A tömbképletekről szóló cikksorozatot (első és második rész) kicsit megakasztotta az adat vizualizációs tréninghez írt sorozat, de most újra felvesszük a fonalat.

Rögtön egy különleges használati esetet mutatok be: hogyan lehet tömbképlettel kiolvasni a PowerPivot adatbázist szűrő szeletelők szűrési feltételeit?

Tovább a teljes tartalomhoz »

okt 11

10 éves a BI projekt blog!

Rendszeres olvasója vagyok a BI projekt blognak, s ezúton is szeretnék gratulálni Attilának, hogy immár 10 éve folyamatosan publikál adattárház és üzleti intelligencia témában! Nosztalgiázva olvastam a megemlékező bejegyzést, és visszagondoltam, valóban mennyi minden megváltozott ez alatt a pár év alatt.

Én azt tapasztalom az utóbbi időben, hogy egyre több adattal dolgozunk, az üzleti igények egyre komplexebbek és sokkal gyorsabb adatszolgáltatást vár el az üzleti terület a pénzügytől. Közben az adatszolgáltatás (mi úgy mondjuk, riportok) minősége is átalakul: sokkal határozottabb az igény az egységes, szépen formázott, gyorsan áttekinthető táblázatokra, valamint az hatékony adat vizualizációra. (Nálunk most a vízesés grafikon a sztár!) A riport legyen összesített, de részletes is, szűrhető és átalakítható is és dinamikus is…

Ezek a folyamatos kihívások minket is a PowerPivot irányába tereltek, s az önkiszolgáló BI workshoppon való részvétel után lassan elkészül az első PowerPivot alapú riport fejlesztésem. Közben azon is gondolkodom, milyen tapasztalatokat, ötleteket tudok majd veletek is megosztani – az első téma már készülőben van! :-)

 

okt 10

Mindentudó vízesés grafikon (Waterfall chart 4. rész)

Közkívánatra elkészítettem az előző bejegyzésben szereplő, automatikus tengely minimum beállítást lehetővé tevő vízesés grafikon extra-haladó változatát.

Ebben már nem csak a tengely minimum jelenik meg egy kis feliratban, hanem az egész tengely látható, a beállított lépésköznek megfelelő beosztással:

waterfall19

A tengely persze nem igazi, egy adatsor és némi trükközés segítségével raktam rá a grafikonra. A technikai részletekre most nem térek ki, csak a modellt szeretném közzétenni, hogy a saját adataitokkal használhassátok.

Tovább a teljes tartalomhoz »

sze 21

Automatikus tengely minimum vízesés grafikonon (Waterfall chart 3. rész)

Tréninget tartok vízesés grafikon készítéséről, ezért is szól most ennyi bejegyzés erről a témáról. Holnap lesz az utolsó alkalom – ez a csoport már a tananyag minden részét olvashatja majd írott formában is. A tréninget pedig már playback-ről fogják hallgatni. :-)

A vízesés diagramot általában automatikus függőleges tengellyel készítjük, így az Excelre hagyjuk, hogy olyan tengely minimumot állítson be, ami mellett jól látszik a változás akkor is, ha nagyon eltérő a kiinduló adat nagyságrendje:

waterfall15

Tovább a teljes tartalomhoz »

sze 20

Adatfeliratok vízesés grafikonra (Waterfall chart 2. rész)

Az előző bejegyzésben bemutattam, hogyan kell pozitív-negatív eltérés sávok segítségével vízesés grafikont készíteni. Ez a módszer szerintem könnyen érthető, és biztosítja, hogy helyes ábrát kapjunk abban az esetben is, ha az adatok átlógnak a negatív tartományba.

Ebben a bejegyzésben megmutatom, hogyan lehet adat feliratokat tenni erre a grafikonra.

Sajnos a vízesés grafikon esetében ez nem olyan egyszerű mint gondolnánk, azonban a 2013-as Excel egy újításának köszönhetően szerencsére gyorsan meg fogjuk oldani. A módszer más grafikonok használatakor is hasznos lehet, mert ezzel a technikával tulajdonképpen bárhová, bármilyen adat feliratot fel tudsz majd rakni.

waterfall09

Tovább a teljes tartalomhoz »

sze 14

Vízesés grafikon útmutató (Waterfall chart 1. rész)

Nagyon népszerű ez a diagram típus nálunk, sokféle prezentációban használjuk. Bemutathatjuk vele egy üzleti mutatószám változását egyik időszakról a másikra, valamint azt, hogy a változás milyen hatásokból ered.

Például: a bal oldalon kékkel mutatjuk a 2014-es év profitját. Ezután zölddel és pirossal mutatjuk, hogy az egyes hatások mennyivel növelték illetve csökkentették a profitot, így érkezünk el a jobb oldali kék oszlophoz, a 2015-ös profithoz.

Íme:

waterfall01a

Tovább a teljes tartalomhoz »

aug 06

Növekedés/csökkenés ábrázolása havonta: grafikon hibasávokból

Ma egy kedves kolléganőm kért segítséget egy grafikon felépítéséhez. Halmozott oszlop diagramot szeretett volna készíteni, amin be tudja mutatni, hogy egyik évről a másikra hogyan változott havonta az értékesítés.

novcsokk01

A 2014-es adatokat szerette volna mutatni, s erre rátenni a növekedés/csökkenést.

Azonban a negatív értékeket a halmozott oszlop diagram nem úgy kezeli, ahogy szeretnénk, ezek a 0 tengely alatt jelennek meg:

novcsokk02

Tovább a teljes tartalomhoz »

jul 14

Tömbképletek 2. rész: Abszolút értékek összegzése

A nagy meleg nyárban azért csak folytatni kell ezt a cikksorozatot, de most egy egyszerű, rövid rész következik – azonban aki jól megérti, hogyan működnek a tömbképletek, az valóban csodákat művelhet ezekkel. Mi pár éve nagyon sok “formula challenge”-et oldottunk meg ilyen formulákkal, úgyhogy elég alaposan feszegettünk az Excel határait. :-)

Nos, egy egyszerű példán keresztül fogom bemutatni egy új aspektusát a tömbképleteknek. Feladat: számok abszolút értékét kellene összeadni. Ezt hagyományos formulákkal csak segéd cellák igénybevételével tudjuk megtenni: az adatok alatt kiszámoljuk az abszolút értéket (ABS) és ezt összegezzük.

Tovább a teljes tartalomhoz »

jul 01

Tömbképletek 1. rész: TRANSZPONÁLÁS

Írnom kell a tömbképletekről. Nem csak azért, mert hasznosak, hanem azért is, mert a PowerPivot tréningen felmerült egy érdekes használati eset… de erről inkább a következő bejegyzésben lesz szó. Most maradjunk az alapoknál, és kezdjük egyszerű példával. Két megoldást mutatok meg, amiket összehasonlítva könnyű lesz megérteni, hogy hogyan dolgoznak a tömbképletek, s miben térnek el a hagyományos képletektől.

Egy oszlopban egymás alá vannak írva a hét napjainak nevei – ezt kellene egy sorba átrendezni.

Tovább a teljes tartalomhoz »

jún 12

FKERES balra is: INDEX és HOL.VAN segítségével – INDEX formula 3. rész

Az előző bejegyzésekben elmagyaráztam az INDEX formula alapjait, és mutattam egy hasznos és egyszerű dinamikus összegző formulát.

Ebben a cikkben megmutatom, hogyan tudjuk az INDEX-et a HOL.VAN (MATCH) segítségével felhasználni arra, hogy egy táblázat egyik oszlopában megkeressünk egy adatot, s eredményül egy másik oszlopban levő adatot adjunk vissza. Ezt általában az FKERES ((VLOOKUP) formulával szoktuk csinálni: a keresési értéket a tartomány első oszlopában megkeresi, majd a megadott számú oszlopban levő adattal tér vissza. A gyakorlatban azonban nem mindig az első oszlopban szeretnénk keresni, előfordul, hogy az adat, amit vissza szeretnénk kapni, a keresési oszloptól balra van. Az INDEX és HOL.VAN alkalmazásával könnyen túlléphetünk ezen a korlátozáson, sőt, a kalkulációt is gyorsabbá tehetjük.

Tovább a teljes tartalomhoz »

Régebbi bejegyzések «

» Újabb bejegyzések

css.php