ADATVIZUALIZCI Segdanyag a Gazdasgi informatika trgyhoz sszelltotta Pet

ADATVIZUALIZÁCIÓ Segédanyag a Gazdasági informatika tárgyához Összeállította: Pető István Frissítve: 2014. november 3.

Adatvizualizáció, dashboard-ok 2 Egy szervezet életében nagy mennyiségű adat keletkezik, melyből többnyire táblázatos jelentések, grafikonok készülnek. A döntéshozók számára ugyanakkor fontos, hogy a szakterületük történéseit gyorsan át tudják tekinteni. Ennek egy eszköze a dashboard (irányítópult), mely � � Kis méretben (nagyjából 1 képernyőn) Informatív formában (célszerű táblázatokkal, diagramokkal) közli a döntéshozóval a legfontosabb mutatók értékeit, így fókuszálva a figyelmét a lényeges kérdésekre. A következő diákon néhány olyan egyszerű Excel-megoldást mutatunk be, melyek részét képezhetik ilyen dashboard-oknak, vagy önállóan is alkalmazhatók az informatívabb adatközlés érdekében. A mintaadatsor letölthető a kurzuslapról vagy a http: //miau. gau. hu/temp/tananyag/ginf/ab_demo_kerekpar_blank. xlsx címről.

3 Feltételes formázás – adatsávok és ikonok Az értékek nagyságrendjének kifejezése a számok közlése nélkül

Értékek adatsávvá alakítása I 4 Hivatkozzuk át a az alapadatok munkalapról az első táblázat címét, fejlécét és tartalmát!

Értékek adatsávvá alakítása I 5 Jelöljük ki a formázni kívánt adattartományt, majd a Kezdőlap > Feltételes formázás > Adatsávok menüből válasszunk egy nekünk tetsző kitöltési sémát

Értékek adatsávvá alakítása I 6 Az Excel 2007 -ben csak a baloldalon látható, színátmenetes formátum érhető el. Ennek hátránya, hogy adott esetben csak nehezen állapítható meg, hogy pontosan meddig tart az adatsáv. Az Excel 2010 -ben viszont már választhatunk átmenetes és egyszínű kitöltősémák között, így ez a vizualizációs probléma nem áll fenn.

Értékek adatsávvá alakítása I 7 Ha kijelöljük a formázott adatsorunkat, majd a Kezdőlap > Feltételes formázás > Szabályok kezelés parancsot választjuk, akkor elérjük (és így módosítani, törölni tudjuk) a kijelölt területen található szabályokat.

Értékek adatsávvá alakítása I 8 A sokfajta beállítási lehetőség közül itt a Csak a sáv megjelenítése opciót emeljük ki: Ennek segítségével letüntethetjük az értékeket azt adatsávokról.

Értékek adatsávvá alakítása II 9 Ez a feltételes formázás az Excel 2007 előtt nem állt rendelkezésre. Ha pl. Excel 2003 -mal dolgozunk, hasonló hatást érhetünk el azzal, ha az értékekkel arányos mennyiségű karaktert (a példában a |-t) írunk ki a cellákba. Erre szolgál a SOKSZOR() függvény, melynek két paramétere van: 1. A Szöveg-be kerül az ismételendő karakter (itt: Alt. Gr+W) 2. A Hányszor-ba az ismétlések száma. Mivel itt az értékek túl nagyok, ezért ezerrel osztva csökkentjük a nagyságrendjüket.

Értékek adatsávvá alakítása III 10 Ha az adatsorunk tartalmaz pozitív és negatív értékeket, (mint az alapadatok munkalap 2. táblázata), akkor a képen látható „kétirányú” adatsáv a praktikus megoldás. Ez viszont csak az Excel 2010 -től érhető el – elkészítése az 5 -8. diákon leírtaknak megfelelően történik. Tehát korábbi Excel-verziókhoz más megoldás szükséges.

Értékek adatsávvá alakítása IV 11 A feladat megoldása: Külön oszlopban kezeljük a negatív és pozitív értékeket, melyet a HA() függvény segítségével érünk el: 1. Logikai vizsgálat: az alapadat értéke kisebb-e 0 -nál: alapadatok!F 3<0 2. Érték ha igaz: Ha negatív számról van szó, a korábban látott megoldás szerint kiírjuk a |-eket A -100 -zal való szorzásra a negatív tizedestörtek pozitív számmá alakításhoz van szükségünk: SOKSZOR("|"; alapadatok!F 3*-100) 3. Érték ha hamis: Ha pozitív számról van szó, akkor nem írunk ki semmit: "„ A teljes függvény: =HA(alapadatok!F 3<0; SOKSZOR("|"; alapadatok!F 3*-100); "")

Értékek adatsávvá alakítása IV 12 A következő oszlopban jelöljük meg a pozitív értékeket. A függvény hasonló, csak itt a 0 -nál nagyobb értékeket vizsgáljuk és a „ha igaz” kimenetben nincs szükség a negatív számmal való szorzásra. A függvény: =HA(alapadatok!F 3>=0; SOKSZOR("|"; alapadatok!F 3*100); "") A „kétirányú” adatsáv létrehozásához a B oszlop értékeit jobbra igazítjuk és a karakterszíneket igény szerint módosítjuk

Értékek ikonokká alakítása 13 Gyakran használt vizualizációs eszköz, hogy az érékhez szabályok alapján ikonokat (nyilakat, közlekedési lámpákat stb. ) rendelünk. Erre is a feltételes formázások között, az Ikonkészletek csoportban található eszközök alkalmasak. Az előzőekben leírtaknak megfelelően a szabályok és ikonkészletek széles határok között módosíthatók.

14 Mini diagramok Az adatsor trendjének megjelenítése a végletekig leegyszerűsített diagramokkal

Mini diagramok I 15 Az Excel 2010 -ben találunk olyan eszközt, amellyel az ilyen mini diagramokat igen egyszerűen elkészíthetjük: A Beszúrás > Értékgörbék csoportban tudunk választani többféle típus közül. A párbeszédablakban csak az adatok forrását és a célcellát (ahol a diagram megjelenik) kell megadni.

Mini diagramok II 16 A korábbi Excel-verziókban ugyanezt a hatást hagyományos (pl. oszlop-) diagramok teljes lecsupaszításával érhetjük el. Az alapadatok „Eladott darabszám régiónként és havonta” táblázatából, az Észak -Alföld adatsorának 2012 -es értékeiből készítsünk vonaldiagramot: Az értékek kijelölése után a Beszúrás > Diagramok > Vonal csoportból a legelsőre van szükségünk.

Mini diagramok II 17 A teendő innen kezdve csak annyi, hogy minden elemet (jelmagyarázat, tengelyfeliratok, rácsok stb. ) kijelölünk és kitörlünk. A diagram belső rajzterületét (lásd a mellékelt ábrán) méretezzük a külső kerethez, hogy minél jobban kitöltse a helyet. Ezt követően már csak a rendelkezésre álló helyhez kell szabni a diagram méretét.

18 Terv-tény összehasonlítás Ugyanazon jelenség két állapotának szemléltetése diagramon

Terv-tény diagram I 19 Az alapadatok munkalap Eladott darabszám havonta táblázatából készítsünk egyszerű oszlopdiagramot a terv-tény munkalapra. Ezt olyan módon kell átalakítani, hogy kitűnjön, ugyanannak a jelenségnek két állapotáról van szó.

Terv-tény diagram I 20 Ennek érdekében válasszuk ki a diagramon a Terv adatsorát és a Diagrameszközök > Tervezés > Más diagramtípus paranccsal alakítsuk át jelölővel ellátott vonaldiagrammá

Terv-tény diagram I 21 Ennek érdekében válasszuk ki a diagramon a Terv adatsorát és a Diagrameszközök > Tervezés > Más diagramtípus paranccsal alakítsuk át jelölővel ellátott vonaldiagrammá.

Terv-tény diagram I 22 Az átalakult Terv adatsort kiválasztva jobb egérgombbal elérhető az Adatsor formázása menü. Itt egyrészt a Vonal színe ablakban kapcsoljuk ki a vonal megjelenítését, másrészt a Jelölő beállításai ablakban állítsuk át a jelölőt kicsit nagyobb méretű vízszintes vonallá.

Terv-tény diagram I 23 A Diagrameszközök > Elrendezés > Diagramcím paranccsal adjunk hozzá diagramcímet, ahová hivatkozzuk be az alapadatokról a címet.

Terv-tény diagram II 24 Hasonló célt szolgál az a diagram, ahol a Terv adatsorhoz terület diagramot, a tény adatsorhoz pedig vonaldiagramot választunk.

25 Vészjelzés A diagramon emeljük ki eltérő színnel azokat az eseteket (pl. hónapok), amelyek kiemelkedően jól vagy rosszul teljesítettek a tervértékhez képest

Vészjelzés 26 A feladat megoldásához az alapadatok munkalap Eladott darabszám havonta táblázatának adatai három részre kell bontani a vészjelzés munkalapon látható határértékek szerint: Ha a tény elérte a terv 95%-át akkor a Jó, ha a tény nem érte el a terv 85%-át akkor a Rossz, ha a kettő között teljesített akkor a Közepes oszlopba kell átírni a tény érékét. A szükséges függvények: Jó: =HA(B 38>B 37*vészjelzés!$B$1; alapadatok!B 38; "") Rossz: =HA(B 38<B 37*vészjelzés!$B$2; alapadatok!B 38; "")

Vészjelzés 27 A Közepes sorhoz (a Tény a Terv két határértéke közé esik) egy HA() függvény nem elég, mivel két feltétel egyszerre való teljesülését vizsgáljuk. Ehhez be kell ágyazni a logikai vizsgálat mezőbe egy ÉS() függvényt, amely elvégzi ezt: =ÉS(B 38<=alapadatok!B 37*vészjelzés!$B$1; alapadatok!B 38>=alapadatok!B 37*vészjelzés !$B$2) A teljes megoldás: =HA(ÉS(B 38<=alapadatok!B 37*vészjelzés!$B$1; alapadatok!B 38>=alapadatok!B 37*vészje lzés!$B$2); alapadatok!B 38; "")

Vészjelzés 28 A vészjelzés munkalapra illesszünk be egy üres oszlopdiagramot (Beszúrás > Diagram > Oszlop), majd a Diagrameszközök > Tervezés > Adatok kijelölése paranccsal egyesével adjuk hozzá az adatsorokat.

Vészjelzés 29 A Hozzáadás gomb hatására megjelenő ablakban egyesével jelöljük ki az adatsorokat: adjuk meg a megfelelő adatsor neve és az adatsor éréke cella- ill. tartományhivatkozást az alapadatok munkalapról. A tengelyfeliratokat az Adatforrás kiválasztása panel jobb oldalán hasonlóan hozzá tudjuk adni.

Vészjelzés 30 Ehhez válasszuk ki bármelyik adatsort, majd jobb egérgomb után a az Adatsor formázása parancsot. Itt az Adatsor beállításai ablakban állítsuk az Átfedő adatsorok csúszkát 100%-ra: ezzel az adatsorok teljesen elfednék egymást, de mivel egy hónapban csak egy adatsor van, ezért ez nem történik meg. A Térköz szélességének csökkentésével kisebb lesz az oszlopok közötti tér, vagyis növekszik a szélességük.

Vészjelzés 31 Az oszlopok színét a Diagrameszközök > Formátum > Kitöltés paranccsal módosítsuk, pl. a Jó legyen zöld, a Közepes szürke, a Rossz piros. Ha a vészjelzős munkalapon vezérlő %-os értékeket átírjuk, az új küszöbértékeknek megfelelő színeket kapnak az oszlopok.

32 Legjobb-legrosszabb elemek A legjobb/legrosszabb x db elemet emeljük ki eltérő színnel a diagramon

Legjobb-legrosszabb 33 A feladatban legjobban ill. legrosszabbul teljesítő termékeket kell kiemelni a diagramon. Első lépésként meg kell határozni, hogy a legjobb-legrosszabb c. munkalapon lévő határértékek alapján melyik árbevétel-érték a legjobb x-edik, ill. legrosszabb y-adik. Az ehhez szükséges függvények: Felső küszöb: =NAGY(D 47: D 111; 'legjobb-legrosszabb'!B 1) Alsó küszöb: =KICSI(D 47: D 111; 'legjobb-legrosszabb'!B 2)

Legjobb-legrosszabb 34 Következő lépésként a Vészjelzés feladathoz hasonlóan az értékoszlopot a küszöbértékek alapján három halmazra kell bontani (a magyarázatot lásd ott). A szükséges függvények: Jó: =HA(D 47>=$K$46; D 47; "") Közepes: =HA(ÉS(D 47<$K$46; D 47>$K$47); D 47; "") Rossz: =HA(D 47<=$K$47; D 47; "")

Legjobb-legrosszabb 35 A Vészjelzés feladathoz hasonlóan egy üres diagramot készítünk, amihez egyesével adjuk hozzá az adatsorokat. A vízszintes tengely feliratához pedig a termékkód oszlopot használjuk.

Legjobb-legrosszabb 36 Ismét a Vészjelzés feladatban látottakra hivatkozva a tartalmuknak megfelelően módosítjuk az oszlopok kitöltési színét, illetve az Adatsor beállításai ablakban teljesen átfedővé tesszük az adatsorokat. A diagramon azonban még zavaró, hogy az értéktengely feliratai túl nagy helyet foglalnak el, ezt oldjuk meg a következő lépésben.

Legjobb-legrosszabb 37 A függőleges tengelyre jobb egérgombbal kattintva válasszuk a Tengely formázása > Szám menüablakot. Az Egyéni csoportban a Formátumkód mezőbe írjuk be a # "M" kódot: A #-jel az eredeti éréket jelenti, utána két szóköz szerepel, mely 1 -1 ezres nagyságrenddel visszaosztja az értéket, végül "M" szerepel mértékegységként, így az eredeti értékünkből millióban kifejezett érték lett.

Legjobb-legrosszabb 38 Zárásként adjunk hozzá diagramcímet. A munkalap tetején lévő vezérlőértékekkel módosíthatjuk, hogy mekkora legyen a legjobb-legrosszabb intervallum.

39 Pareto-diagram Bemutatja egy jelenség (pl. minőségi problémák, megrendelések) alkotóelemeinek halmozott meg-oszlását a Pareto-elvvel összhangban (kb. „A hibák 80%-a az okok 20%-ára vezethető vissza”. )

Pareto-diagram 40 Az árbevétel partnerenkénti megoszlását rendezzük csökkenő sorrendbe. Számítsuk ki az összes árbevételből való részesedésüket. Készítsük el a részesedés halmozott nagyságát pl. a következő függvénnyel: =SZUM($C$117: C 117) Hozzuk létre azt a küszöbértéket, amihez a halmozott részarányt fogjuk viszonyítani.

Pareto-diagram 41 Készítsünk oszlopdiagramot az árbevételből és a halmozott részesedésből. Mivel a két adatsor nagyságrendje nagyon eltérő, a halmozott részesedés egyáltalán nem lesz látható.

Pareto-diagram 42 A nehezen kiválasztható adatsor formázásához a Diagrameszközök > Elrendezés > Aktuális kijelölés legördülőből válasszuk ki a Sorozat „Halmozott” tételt, majd a Kijelölés formázása menüpontot. Az Adatsor formázása ablakban rendeljük hozzá a Másodlagos tengely opciót.

Pareto-diagram 43 Ennek hatására a Halmozott értékek saját, nagyságrendjüknek megfelelő értéktengelyt kapnak a diagram jobb oldalán, így ez az adatsor is értelmezhetővé válik. A jobb áttekinthetőség érdekében állítsuk át az adatsor diagramtípusát vonaldiagramra.

Pareto-diagram 44 Zárásként adjuk hozzá a Küszöb adatsort, így láthatóvá válik, hogy a halmozott árbevétel melyik partnernél lépi át ezt a szintet. (Mivel véletlenszámokkal szimulált adatsort használunk, a Pareto-elv itt nem érvényesül. )

45 Vezérlés űrlapelemekkel A dashboard-ok adattartalmának megjelenítését interaktívabbá tehetjük különféle űrlapelemek (legördülő, jelölőnégyzet, gördítősáv stb. ) segítségével. Ezek közül a legördülő menüt mutatjuk be példaként. Ehhez azonban aktiválni kell a Fejlesztőeszközök menüsávot. Ezt a Fájl > Beállítások > Menüszalag testreszabása

Vezérlés űrlapelemekkel 46 A Fejlesztőeszközök > Vezérlők > Beszúrás ikonon keresztül illesszünk be egy Beviteli lista elemet. Erre jobb egérgombbal kattintva válasszuk a Vezérlő formázása parancsot. Az ablakban a Bemeneti tartomány mezőbe adjuk meg, melyik tartományból származzon a legördülő tartalma (régiók neve az alapadatok munkalapról), a Cellacsatolás mezőbe pedig hogy hova írja kiválasztott elemet sorszámát (tehát nem magát a listaelemet).

Vezérlés űrlapelemekkel 47 A legördülőből kiválasztott régió sorszáma alapján az INDEX() függvénnyel oszloponként jelenítjük meg a szükséges régiónevet ill. értéket. A függvény egy tartomány valahányadik celláját adja vissza. Ehhez használjuk a következő függvényt: =INDEX(alapadatok!A 29: A 31; $E$2) A fejléchez hivatkozzuk át az alapadatok munkalapról a hónapok nevét.

Vezérlés űrlapelemekkel 48 Az adatsorból készítsünk diagramot, mely tartalma a legördülőben szereplő aktuális elemnek megfelelően fog módosulni.