Excel konzultáció
This presentation is the property of its rightful owner.
Sponsored Links
1 / 30

Excel konzultáció 4. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22 PowerPoint PPT Presentation


  • 40 Views
  • Uploaded on
  • Presentation posted in: General

Excel konzultáció 4. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22 Számlaszám: 50400113-11065546 Telephely: 7666 Pogány, Széchenyi u. 1. Tel: 30/9015-488 E-mail: pauler @ t-online.hu. A gyakorlat tartalma.

Download Presentation

Excel konzultáció 4. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Excel konzult ci 4 gyakorlat dr pauler g bor egyetemi docens ev ad sz m 63673852 3 22

Excel konzultáció

4. Gyakorlat

Dr. Pauler Gábor, egyetemi docens, ev.

Adószám: 63673852-3-22

Számlaszám: 50400113-11065546

Telephely: 7666 Pogány, Széchenyi u. 1.

Tel: 30/9015-488

E-mail: [email protected]


A gyakorlat tartalma

A gyakorlat tartalma

3. Házi feladat ellenőrzése: számok szöveggé konvertálása

  • A felsővezetői jelentési rendszer alapfogalmai

  • On-Line Analytical Processing (OLAP) alapfogalmai

    • OLAP rendszerek adatkockái

    • OLAP rendszerek használata

    • OLAP rendszerek relációs adatbázis háttere

    • OLAP rendszerek aggregációs diagrammjai

      • 4-4.PÉLDA:Tops áruházlánc

    • OLAP rendszerek formuláinak működése

    • OLAP rendszerek mértékegység-egyeztetése

  • Az Excel Kimutatások fogalma, helyük az objektumok közt

    • Kimutatások tipikus adatforrásai

    • Kimutatások adatforrás-megadása

    • Kimutatások beállításai

    • Kimutatások elrendezése

    • Kimutatások kezelőszervei

    • Kimutatások formulái

    • Kimutatás-diagrammok

    • Kimutatások adatai normál Excel-diagrammokon

    • Kimutatások adatforrásának átállítása

    • Kimutatások másolási szabályai

      4-1.Házi feladat: Továbbszámolás Excel kimutatásból: Volatilitások


A fels vezet i jelent si rendszer alapfogalmai

A felsővezetői jelentési rendszer alapfogalmai

  • A relációs adatbáziskezelővel az űrlapokhoz (lásd: Lesson1) teljesen hasonló módon jelentéseket (Reports) is generálhatunk. Ezek három dologban különböznek az űrlapoktól:

    • Nem a szervezet alsó szintjén dolgozók használják fel őket rutinszerű egyszerű döntésekhez, hanem a felsővezetés (Top Management) a nem rutinjellegű stratégiai döntésekhez

    • Nem rögzíthetünk beléjük adatot, csak megjelenítik az adatbázis adatait, ezt viszont szöveges, táblázatos és diagrammos formában is meg tudják tenni

    • Ezek az adatok nem a szervezet által végzett tevékenységek elemi, széttagolt tranzakció-jellegű adatai (Transaction Processing) (pl. egy adott számla végösszege) hanem régiókra, ügyfelekre, alkalmazottakra, termékekre, stb. csoportosított és összesített, aggregált adatok (Aggregate Data) (pl. Dél-Dunántúl régió 3. negyedéves összforgalma romlandó élelmiszerekből)

  • A jelentések statikus szerkezetűek (Static Structure): a mutatott adatok fajtái, csoportosítása és aggregációi fixek, csak az adattartalom frissül folyamatosan az adatbázisból.

  • Az üzleti életben azonban olyan nagy a bizonytalanság, hogy a legnagyobb pénzt érő döntési problémák nem struktúráltak (Ill Structured Decision Problems) – a felsővezető nem tudja megadni, hogy milyen adatokra lesz szüksége 3 vagy 6 hónap múlva! Bármi kellhet, attól függ, mi lesz!

  • Ezért rendszerfejlesztéskor általában elkezd követelni mindenféle fajta jelentést, ami csak eszébe jut: pl. „kérem a termékkategóriánkénti eladásokat negyedévenként, régiónként, üzletkötőnként, fogyasztói csoportonként”

  • Egyszerűen nincsen tudatában, hogy ezzel a félmondattal egy több száz oldalas jelentést definiált, mert ezen halmazok elemei összeszorzódnak: pl. a 44 kategória diagrammja × 4 negyedév × 5 régió × 8 üzletkötő × 12 fogyasztói csoport = 1920 db diagramm

  • A tapasztalatlan adatbázis szakember ezt el is készíti, és még büszke is rá, hogy milyen ügyes volt.

  • A menedzser bele fog fulladni a kinyomtatott jelentésekbe! Senki nem olvas el 1920 oldalt, pláne nem egy elfoglalt felsővezető (max. 5 oldal/nap)

  • Nagyon elégedetlen lesz, mert bár a rendszer működik, és sok pénzt fizettek érte, mégsem tudja használni semmire, hiába kap egy több mint ezer oldalas jelentést, neki csak az az öt szám kellene, amit pont nem talál benne. Ezt a jelenséget nevezzük jelentéstenger-csapdának (Report Flood Trap).


4 5 elrettent p lda tops friendly markets inc

Mikor az áruház marketingesei közölték a kívánságaikat, összeszámoltam: 6664 oldal lett volna

Két hét alatt, késhegyre menő, keserves videokonferenciák sorozatában lealkudtam 653 darab diagrammra

Generálták a jelentést az indiai beosztottak...

És ez így ment 653 oldalon keresztül...

Mikor a marketingesek látták, hogy 653 oldal, bele sem néztek soha...

Ki sem fizették soha...

4-5.ELRETTENTŐ PÉLDA: Tops Friendly Markets Inc.


On line analytical processing olap alapfogalmai 1

On-Line Analytical Processing (OLAP) alapfogalmai 1

A jelentéstenger csapda on-line analitikus feldolgozás (On-Line Analytical Processing, OLAP) bevezetésével oldható fel, amely egy, a relációs adatbáziskezelésre épülő, de annál fejlettebb adatkezelési módszertan:

  • Lehetővé teszi az adatok dinamikus, futás közben megváltoztatható struktúrában (Dynamic Structure) történő megjelenítését

  • A felhasználó számára biztosítja a csoportosítások, aggregációk, rendezések bonyolult SQL programozás és adatbázis áttervezés nélküli, egyszerű grafikus felhasználói felületen (Graphic User Inteface, GUI) keresztül történő azonnali megváltoztatását, így nem kell az adatbázis programozókra várni

  • Tárolási rendszere lehetővé teszi az ehhez szükséges nagytömegű, de viszonylag egyszerű aggregációs számítás gyors elvégzését

    Az OLAP tárolási rendszerének hierarchikus részei a nagy egységektől a kicsi felé haladva:

  • Adattárház (Data Warehouse, DW): egy szervezet szabványos mezőtipusokat használó, összefüggő adatbázis terv alapján, azonos relációs adatbáziskezelőben tárolt összes, tisztított, szinkronizált, kompatibilis adata. Mivel az adattárház elég divatos frázis, gyakran büszkélkednek vele olyan szervezetek, akik nagyon messze vannak még tőle: NEM adattárház, amikor egy szervezet különálló informatikai szigetalkalmazásait furfangos programozók alkalmi megoldásokkal összekötögetik. Az ilyen rendszer recsegve-ropogva eleget tesz vezetői jelentésgenerációs feladatoknak de OLAP-ot és adatbányászatot már nem tud kiszolgálni (Ilyenre tipikus példa az átlagos magyar vegetáló kis-középvállalatok (KKV) rendszerei). A valódi adattárház több évnyi és százmillió forintnyi erőfeszítésre van ettől a helyzettől: az eredetileg elszigetelt adatbázisok terveinek egyesítése, tartalmuk szinkronizálása nem úszható meg a szervezet egészére kiterjedő BPR nélkül, ami költséges és sok konfliktussal járó dolog. Így önálló adattárház fejlesztése csak a legnagyobb cégek esetén térül meg. Ezért egy KKV számára sokkal reálisabb lehetőség adattárház létrehozására integrált vállalatirányítási (Enterprise Resoulce Planning, ERP) szoftver megvásárlása:

    • Olyan adattárház, amely a szervezet különböző funkciói (pl. könyvelés, pénzügy, értékesítés, gyártás) szerint önmagukban is működőképes modulokra (Module) van osztva, és részenként is megvásárolható és bevezethető. A modulokat az OLAP terminológájával adattárnak (Data Mart) nevezzük, és olyan magas szinten vannak integrálva, hogy a teljes szervezeti működés összes folyamatára biztosítják a redundancia- és adatvesztésmentességet, valamint az egyértelmű hivatkozásokat

    • Az ERP szoftver gyártók általában tanácsadói szolgáltatást is adnak a bevezetéskor


On line analytical processing olap alapfogalmai 2

On-Line Analytical Processing (OLAP) alapfogalmai 2

  • Az ERP szoftvergyártók a KKV-k igényeihez és finanszírozási lehetőségeihez igazodva különféle méretbeli, árbeli és tudásbeli verziókban kínálják termékeiket:

  • Csak a nagy gyártók (1. http://www.sap.com/index.epx 2. http://www.microsoft.com/dynamics/nav/default.mspx ) gyártanak minden funkcionális modult, a kisebbek nem

  • Az ERP rendszerek sok szektor-specifikus tudást igényelnek: másképp működik egy bank, egy egyetem, egy olajtársaság. A nagy gyártók szépen elosztották egymás közt a szektorokat, hogy ne kelljen versenyezniük:

  • Ezenkívül, nem olyan rugalmasak, mint azt fennen hirdetik magukról: a bevezetéskor inkább a szervezetnek kell a folyamatait az ERP-hez igazítani, mint fordítva. A gyártóknak annyi igazsága van gazságuk mellett, hogy egy kaotikus szervezetben nem lehet eredményesen ERP-t bevezetni, és így kikényszerítik a bevezetés során a drága és hosszadalmas BPR-t, amit általában szintén ők végeznek

  • Ezért egy ERP-bevezetés költsége még egy jól működő céget is megráz: kb. 2.5-3 év, mire az ERP nem a veszteségeket meg a gondokat termeli, hanem elkezd stratégiai verseny-eszközzé válni, igaz attól kezdve a szervezetnek felmérhetetlen előnye van olyan versenytársakkal szemben, akiknek nincs, vagy kényelmességből, takarékosságból csak 1-2 modult vezettek be. Erre például szolgálhat a finn elektronikai alkatrészgyártó, az Elcoteq (http://www.elcoteq.com/en ) tündöklése és hanyatlása: bevezették a Baan rendszer (http://www.ssaglobal.com/solutions/erp/ln.aspx ) könyvelés és pénzügy modulját, azonban a logisztikát nem, és kicsi, toldozott-foltozott szigetalkalmazásokkal pótolták. A felsőveze-tés szűklátókörűsége eredményeképpen 2006-ban negatív üzleti-üzemi eredményt sikerült elérniük egy évi 10-12%-kal dinamikusan bővülő piacon!


Olap rendszerek adatkock i

  • Az adatkockát tulajdonképpen a programozásból már ismert többdimenziós tömbök és az adatbázis táblák „közös gyermekeként” képzelhetjük el:

  • Az adatbázis táblában csak sorok és oszlopok lehetnek, de sort/oszlopot bármikor be lehet szúrni/törölni

  • A többdimenziós tömbben nemcsak sorok, oszlopok, hanem több dimenzió is lehet, de nem lehet deklaráció után dimenzió-értékeket beszúrni/törölni

  • Az adatkocka objektum lehet sok dimenziós, és bármikor lehet dimenzió értékeket beszúrni/törölni, viszont szerkezete jóval bonyolultabb az előbbieknél

OLAP rendszerek adatkockái

Ha a szervezet szert tett működő adattárházra, ettől kezdve az adatok sem úgy jelennek meg az elemzés számára mint adott adatbázistáblák adott mezői, holott az OLAP relációs adatbázis kezelésen alapul: pl. ha biztosak lehetünk benne, hogy a Dátum vagy Régiókód az egész adattárház több 1000 táblájában ugyanolyan típussal, ugyanazt jelentik, akkor mindegy hogy melyik táblában tárolódnak, egyfajta „virtuális mezővé” válnak az OLAP tárolási rendszerében:

  • Dimenzió (Dimension, DIM): egy adott típusú, értékhatárú mező Pl.: Sales, Profit

  • Pozíció (Position, POS): egy dimenzió egy lehetséges értéke Pl.: Sales={Low, High}, Profit={Low, High}

  • Adatkocka (Data Cube, DC): több dimenzió értékeinek összes lehetséges kombinációja, Pl.: Sales×Profit={ (Low,Low), (Low,High), (High,Low), (High,High) } cellákból áll

  • Változó/mérték (Measure, MS): egy dimenziókból álló adatkocka celláiban megfigyelt azonos típusú értékek Pl.: Number of Customers

  • Hierarchikus szintek (Hierarchic Levels, HL):

    • Egy dimenzió halmaz-részhalmaz kapcsolatban álló felső/alsó szintű értékhalmazai

    • A felsőbb szint egy értékéhez az alsóbb szint összes értéke tartozik

    • Pl.: havi_eladások={jan..dec}  heti_eladások={1..4}  napi_eladások={H..P}

  • Aggregáció (Aggregation): a hierarchikus szintek mentén történő felfele mozgás aggregációs függvények (Count:darab, Sum:összeg, Avg:átlag, Min, Max, Stdev:szórás, Var:variancia) segítségével

  • Lefúrás (Drill): a hierarchikus szintek mentén történő lefele mozgás az aggregátumok mögött álló elemek kibontásával


Olap rendszerek haszn lata

OLAP rendszerek használata

Miért pont Buffaloban csökkentek a legjobban az eladások?

Képlet (Formula):

  • Több adatkocka measure-jeiből (pl. eladás, ár, árrés) függvényekkel, matematikai műveletekkel kiszámított újabb adatkocka (pl. profit)

  • A résztvevő adatkockák dimenzióinak NEM kell teljesen azonosnak lennie, elég, ha valamely hierarchikus szintű bontásukban kompatibilisek egymással

  • A dimenzionális egyeztetést és a számításokat az OLAP motor (OLAP engine) automatikusan elvégzi

    Kivételekben történő bányászkodás (Mining by Exceptions): amikor a felsővezető az aggregált adatok közül kiugrót, szokatlant 1 kattintással, 1 perc alatt – programozás nélkül - lefúrással részekre bontja, és megtalálja a döntéshez szükséges infót (pl. a veszteségek oka), anélkül, hogy átnyálazná a mögöttes 1000000 rekordot!

katt

A lojális vevőinket vesztjük el!

Miért pont az alacsony –közepes profitabilitású csoportban nagy az esés?

katt


A gyakorlat tartalma1

A gyakorlat tartalma

3. Házi feladat ellenőrzése: számok szöveggé konvertálása

  • A felsővezetői jelentési rendszer alapfogalmai

  • On-Line Analytical Processing (OLAP) alapfogalmai

    • OLAP rendszerek adatkockái

    • OLAP rendszerek használata

    • OLAP rendszerek relációs adatbázis háttere

    • OLAP rendszerek aggregációs diagrammjai

      • 4-4.PÉLDA:Tops áruházlánc

    • OLAP rendszerek formuláinak működése

    • OLAP rendszerek mértékegység-egyeztetése

  • Az Excel Kimutatások fogalma, helyük az objektumok közt

    • Kimutatások tipikus adatforrásai

    • Kimutatások adatforrás-megadása

    • Kimutatások beállításai

    • Kimutatások elrendezése

    • Kimutatások kezelőszervei

    • Kimutatások formulái

    • Kimutatás-diagrammok

    • Kimutatások adatai normál Excel-diagrammokon

    • Kimutatások adatforrásának átállítása

    • Kimutatások másolási szabályai

      4-1.Házi feladat: Továbbszámolás Excel kimutatásból: Volatilitások


Olap rendszerek rel ci s adatb zis h ttere

Szegmens

# SzegmKód

* Név

Tulajdonos

# TuldKód

* Név

Cég

# Adószám

* Név

* TuldKód

Vásárló

# Vásárlókód

* Név

* SzegmKód

Számla

# Számlaszám

* Dátum

* Végösszeg

* Adószám

* Vásárlókód

Tétel

# Tételkód

* Mennyiség

* Érték

* Szlaszám

* Vonalkód

Termék

# Vonalkód

* Név

* Egységár

* ITJ-kateg

ITJ

# ITJ-kateg

* Név

OLAP rendszerek relációs adatbázis háttere

  • OLAP rendszereket számos gyártó kínál, ezek két csoportra oszthatók:

    • Multidimenzionális OLAP (MOLAP): az adatkockák sokdimenziós linkrendszer segítségével fizikailag tárolódnak. Ez gyorsabb, de óriási a tárigénye, és drága

    • Relációs OLAP (ROLAP): az adatkockák nem tárolódnak fixen, hanem mindig relációs adatbázisból számolódnak ki. Ez lassabb, de kisebb tárigénye van, és olcsóbb. Ezen rend-szerek egyik legegyszerűbb, de legköltség-takarékosabb változata az Excel kimutatás!

      Az OLAP adatkockák csillag/hópehely struktúrának (Star/Snowflake Structure) nevezett tábla-csoportokban tárolódnak az adattárházban:

  • A struktúra középpontjában mindig tranzakciókat (pl. eladások), az üzleti folyamat műveleteit leíró egyedek találhatók, ezek OLAP-ban a tény- egyedek (Facts)

  • A tényegyedekben szereplő elsődleges-/ idegen kulcsmezőket tényattribútumoknak (Fact Attribute) nevezzük, más mezőik mértékek (Measure) lesznek

  • A tényegyedekre csatolódnak (Fact-Dimension Join) körben a dimenzió leíró egyedek (Dimensions)

  • A dimenziók önmagukban is 1:több csatolásokkal (Intra-Dimension Join) láncba kapcsolt egyedekből álló fix szint-(Level) számú hierarchiát (Hierarchy) alkothatnak

  • A dimenziókban szereplő elsődleges- és idegen kulcsok a dimenzió attribútumoknak (Dimension attribute), más mezőik a dimenzió leíró adatok (Dimension-related Data)


Olap rendszerek aggreg ci s diagrammjai 4 6 p lda tops ruh zl nc

Tényadatok

79$

Rossz

JóVevő

26$

Tétel

# TételKód

* Mennyiség

* TranzKód

* ÁrazKód

Tranzakció

# TranzKód

* Dátum

* ÜzletKód

* KártyaKód

1.Hét

58$

25$

26$

több:1

Anya

2.Hét

72$

20$

Csavar

Szegmens

# SzegKód

* SzegNév

* LojKód

* ProfKód

Lojalitás

# LojKód

* LojNév

OLAP rendszerek aggregációs diagrammjai: 4-6.PÉLDA:Tops áruházlánc

Háztartás

# HáztKód

* Cím, demo

* Duráció

* SzegmKód

* LegjobbÜzl

Kártya

# KártyKód

* VevőNév

* HáztKód

Profit

# ProfKód

* ProfNév

Bonyolultabb csillag- és hópehely struktúrákat aggregációs diag-rammon (Aggregation Diagram) ábrázolhatunk, amely egy 2 ten-gelyű koordináta rendszerbe írt, dimenzió és tranzakció egyedek kapcsolati diagrammja:

  • A vízszintes tengelyen az alkal-mazási probléma aggregációs szintjei (alsó,közép,felső) vannak

  • A függőleges tengely sávjaiban a probléma dimenziói vannak, 1:több kapcsolt felső:alsó szintű egyedekkel. Ezek lehetnek elá-gazó (Fork) dimenziók: 1 egyed-hez több felettes egyed tartozik, és lehetnek rekurzív (Recursive) dimenziók: egy szint egyedjének önmagára mutató 1:többkapcso-lata van, nem fix szintszámú hi-erarchiát alkotva (pl. TermCsop)

  • A kocka-aggregátum egyedek (Cube Aggregates) a tényadatok sávjában jelennek meg, tartal-muk adott ügyfél igényeitől függ

  • De neveik mindig a résztvevő dimenziók rövid neveiből állnak

  • Idegen kulcsok mutatnak bennük az aggregáló dimenziók értékeire

  • Normál mezőik pedig a mérték mezők aggregátumai(Sum,Avg..)

  • Ha két aggregátum rekordjai közt 1:több kapcsolat van, akkor kom-patibilisnek (Compatible) hívjuk

VersCsop

# VersCsop

* CsopNév

Üzlet

# ÜzlKód

* RégióKód

* VersCsop

Régió

# RégióKód

* RégióNév

Árazódás

# ÁrazKód

* VonalKód

* Hétszám

* RégióKód

* EgységÁr

* EgysKtg

Termék

# Vonalkód

* Leírás

* TermKód

Kupon

# Vonalkód

* Típus

* Engedm

* ÁrazKód

Dimenziók: Tér Idő Termék Üzlet Fogyasztó

TermCsop

# TermKód

* TermNév

* FelettKód

Negyedév

# NegyÉv

* ÉvSzám

Hét

# HétSzám

* NegyÉv

Nap

# Dátum

* HétSzám

Év

# ÉvSzám

HétSzegTerm

# HéSZeTeKód

* Fogyaszt

* HétSzám

* SzegKód

* TermKód

HétSzeg

# HéSZeKód

* Fogyaszt

* HétSzám

* SzegKód

Alsó- Közép- Felső- aggreg.szintek


Olap formul k m k d se

Sum

Sum

Avg

Sum

Sum

Szegmens

# SzegKód

* SzegNév

* LojKód

Lojalitás

# LojKód

* LojNév

Háztartás

# HáztKód

* Cím, demo

* Duráció

* SzegmKód

OLAP formulák működése

SzegAggr

# SzegKód

* SumOfForgalom

* SumOfProfit

* „Profitabilit,%”

LojalitAggr

# LojKód

* SumOfForgalom

* SumOfProfit

* „Profitabilit,%”

HaztAggr

# HáztKód

* SzegmKód

* Forgalom

* Profit

  • Egy bonyolult OLAP rendszerben sok

    szinten (Háztartás< fogyasztói cso-

    port/Szegmens/ <Lojalitási csoport)

  • Többféle mértéket(Forgalom,$, Profit,

    $) vagy kiszámított mezőt (Formula)

    (Profitabilitás,% = Profit/Forgalom)

  • Akár több dimenzió (Üzlet, Hét) sze-

    rint aggregálunk különböző aggregáci-

    ós függvényekkel (Count, Sum, Avg)

  • Ilyen körülmények közt elég könnyű

    számolási hibát csinálni, ami egy fel-

    sővezetői jelentésben óriási kárt okoz.

    Ezek elkerülése érdekében jegyezzük

    meg a következőket:

  • Az adatkocka mindig előbb a formulá-

    ban operanduszként résztvevő mérté-

    kekre előírt aggregációt számítja ki (pl

    Sum), és csak utána a formulát!!!

  • Az aggregáció szintjein fölfelé haladva mindig csak Count vagy Sum-mal aggregált mérté-keket aggregálhatok tovább, nem szabad olyan számított mezőt tovább aggregálni, aminek kiszámításában osztás, Avg, Stdev aggregáció volt! Ez az OLAP többszintű abszolút aggregációs szabálya (Multi-Level Absolute Aggregation Rule) Miért van ez?

    4-6.PÉLDA: Ha több szinten, pl. a Szegmensek, és a Lojalitási csoportok szintjén is szeretném látni a Profitabilitás%-ot, akkor a számlálót (Profit) és a nevezőt (Forgalom) külön-külön aggregálom a szinteken fölfelé Sum-mal, és csak a felhasználás helyén számítom ki az arányukat, de azzal nem számolhatok tovább! Pl. ha a jó vevők (Good) és a rossz vevők (Bad) szegmens-szintű profitabilitásait (50% és 10%) átlagolnám, 30% összesített profitabilitást kapnék a teljes 1-es lojalitási csoportra. De ez téves, mert sokkal több rossz vevő volt, mint jó vevő, csak súlyozottan lehetne helyesen átlagolni a profitabilitásukat, az OLAP viszont nem tud súlyozottan átlagolni. Ha viszont külön összeadogatom a legfelső szintű profitot (900$) és a forgalmat (5000$), és a felső szinten osztom el, akkor helyesen 18% profitabilitást kapok, ami sokkal kevesebb mint a torzított eredmény!!!

„Profitabilitás,%”=SumOfProfit/SumOfForgalom

„Profitabilitás,%”=SumOfProfit/SumOfForgalom

Aggregációs szintek


Olap rendszerek m rt kegys g egyeztet se

OLAP rendszerek mértékegység-egyeztetése

  • Több szinten, több dimenzióban folyó automatikus OLAP számításokban elég könnyen bekövetkezhet az a helyzet, hogy már nem is tudjuk mit jelentenek a kiszámított számok:

    „Mennyi?” – „30” – „Mi 30?” – „Mi mennyi?” /szakállas vicc/

    „Az élet értelme: 42” /John Adams: Galaktikus utikalauz stopposoknak/

  • Ennek elkerülése érdekében erősen ajánlott választanunk egy alapmértékegységet (Basic Measure Unit) az egész aggregációs rendszer számára: pl. ha a sebességet az egyik táblában mérföld/h-ban, a másikban km/h-ban, a 3.-ban öl/miatyánkban tartjuk nyilván, egészen biztosan el fogjuk rontani a konverziókat. A Tops áruházi rendszerben ezért minden mennyiséget (Forgalom, Profit, Eladott mennyiség, stb.) $/hét/háztartás mértékegységben tartottunk nyilván (és ezt ajánljuk minden kereskedelmi rendszerbe):

    • A különböző fogyasztói csoportokban/alcsoportokban eltérő számú háztartás lehet, a méret természetesen erősen befolyásolja a csoport teljesítményét. Ennek hatását tűnteti el a háztartások darabszámával (/Count(HáztKód)) történő osztás

    • Egy-egy háztartás eltérő számú hét óta birtokolhat hűségkártyát. Ezt nevezzük durációnak (Duration). Egy rossz vevő, aki már sok-sok hete bent van a rendszerben, összegszerűen jóval többet vásárolt, mint egy újonnan csatlakzott jó vevő. Ezt a torzítást tűnteti el az adott csoport átlag durációjával történő osztás(/Avg(Duration))

  • Sajnos, csak a legdrágább OLAP rendszerek kezelik a mértékek mértékegységét, az olcsóbbak garantáltan nem (pl. IBM DB2 CubeView vagy Excel Kimutatás). Ezért nekünk kell gondoskodnunk arról, hogy a képlettel számított mértékek nevében ott legyen a megfelelő mértékegység, amit a fizikai képleteknél középiskolában megismerthez hasnló módon, a mértékegységek képleten keresztüli végigszámolásával, kiegyszerűsítéssel állítunk elő:

„Profitabilitás, %” = „Profit, $/háztartás/hét” / „Forgalom, $/háztartás/hét”

„Profit, $/háztartás/hét” = „SumOfProfit, $” / „CountOfHáztKód, db” / „AvgOfDuráció, hét”

„Forgalom,$/háztartás/hét”=„SumOfForgalom,$”/„CountOfHáztKód,db”/„AvgOfDuráció,hét”


4 7 gyakorl p lda aggreg ci s rendszer tervez se

4-7.Gyakorló Példa: Aggregációs rendszer tervezése

Az OLAP-pal kapcsolatos elméleti ismeretek gyakorlására, rajzolja fel egy bankhálózat aggregációs diagrammját *.ppt-formátumban, jelen gyakorlatban található diagramm átalakításával:

  • Egy üzleti év több negyedévből, azon belül hónapokból, azonbelül változó számú hétből, azon belül üzleti napokból, azonbelül nyitvatartási/zárva időszakokból áll

  • Egy bankhálózat több régióból, azon belül több településből, azon belül több fiókból/ATM-ből, azon belül több üzletágból áll

  • Egy háztartás több lakossági ügyfélből áll, azok lehetnel tulajdonosok több cégben (de a cégeknek is lehet több tulajdonosa, és a cégek is lehetnek ügyfelek), egy cég több fiókból áll, azon belül öbb osztályból, azon belül több alkalmazottból

  • Egy számla több alszámlából áll, azokon belül több, különféle típusú tranzakció történhet adott időszakra és régióra érvényes költségen, egy tranzakcióhoz több +/- pénzáram tartozhat

  • Egy összetett banki termék több tranzakciót generál

  • Összesíteni kell régióra, fiókra, háztartásra, cégre, cégtulajdonosra a számlák forgalmát, a tranzakciók költségét, a komplex banki termékek forgalmát, profitját

    A megoldás: 4-7GyakorloMegoldas.ppt


A gyakorlat tartalma2

A gyakorlat tartalma

3. Házi feladat ellenőrzése: számok szöveggé konvertálása

  • A felsővezetői jelentési rendszer alapfogalmai

  • On-Line Analytical Processing (OLAP) alapfogalmai

    • OLAP rendszerek adatkockái

    • OLAP rendszerek használata

    • OLAP rendszerek relációs adatbázis háttere

    • OLAP rendszerek aggregációs diagrammjai

      • 4-4.PÉLDA:Tops áruházlánc

    • OLAP rendszerek formuláinak működése

    • OLAP rendszerek mértékegység-egyeztetése

  • Az Excel Kimutatások fogalma, helyük az objektumok közt

    • Kimutatások tipikus adatforrásai

    • Kimutatások adatforrás-megadása

    • Kimutatások beállításai

    • Kimutatások elrendezése

    • Kimutatások kezelőszervei

    • Kimutatások formulái

    • Kimutatás-diagrammok

    • Kimutatások adatai normál Excel-diagrammokon

    • Kimutatások adatforrásának átállítása

    • Kimutatások másolási szabályai

      4-1.Házi feladat: Továbbszámolás Excel kimutatásból: Volatilitások


Az excel kimutat sok fogalma hely k az objektumok k zt

Az Excel Kimutatás (Pivot Table) az OLAP esz-

közök leegyszerűsített, olcsó változata:

Olyan dinamikus struktúrájú jelentés, ahol a

felhasználó grafikus felhasználói felületről

megváltoztathatja a csoportosítást, rende-

zést, aggregációt és szűrést

Ehhez nem kell SQL-ben prgramozni, sem

kódszerkesztőben, sem grafikus felületen

A kimutatás eredményeit nagyon gyorsan

meg tudja jeleníteni diagrammokon, jelen-

tések tervezése nélkül

A kimutatás eredményeiből azonnal további

elemzéseket, statisztikákat készíthet a mun-

kalapokon az Excel cellafüggvényei segítségével, ez még a profi OLAP rendszerekhez képest is előny (MS Access-ben is van Pivot Table formátumú jelentés, de azt soha ne használjuk, mert nagyon lebutították, és külön exportálni kell ahhoz az eredmányeket, hogy tovább tudjunk számolni belőle)

Az SQL-hez képest kevésbé rugalmasan kezelhető, de a számításokat nagyon gyorsan újraszámítja rengeteg verzióban

A kimutatások helye az Excel objektum hierarchiájában:

A munkafüzet (WorkBook) objektum pivottár (PivotCahe) nevű, nem látható (Non-Visual) objektuma tárolja a látható kimutatás mögötti többdimenziós OLAP adatkocka adatait. Ez kétféle adatforrásból dolgozhat:

Bármely cellatartományból (Range), amelynek tartalma adatbázis-tábla szerűen néz ki: az oszlpokban azonos típusú mezők, a sorokban rekordok vannak, az első sor pedig a mezők egyedi neveit tartalmazza. Ez az egyszerűbb, de kisebb kapacitású adatforrás

A kimutatás lehetőségeit akkor lehet igazán kihasználni, ha egy adatbázisból

Először a Windows OLE Database Connector (ODBC) szerverén, majd az

MSQuery-n (ld: Session4) keresztül szed fel nagymennyiségű tranzakció adatot

A pivottárból szedi ki az adatokat és jeleníti meg interaktívan a felhasználó számára a kimutatás (PivotTable) nevű látható (Visual) objektum, ami a munkalap részobjektuma

A pivottár 1:több kapcsolatban áll a kimutatással: egy pivottárhoz kapcsolódhat több kimutatás, de egy kimutatás csak egy pivottárhoz kapcsolódhat

Adatbázis

Windows ODBC

MSQuery

Excel Munkafüzet (WorkBook)

MunkaLap2 (WorkSheet2)

MunkaLap1 (WorkSheet1)

PivotTár1 (PivotCache1)

CellaTömb1 (CellRange1)

PivotTár2 (PivotCache2)

Kimutatás1 (PivotTable1)

Kimutatás1 (PivotTable1)

Kimutatás2 (PivotTable2)

Kimutatás2 (PivotTable2)

Az Excel Kimutatások fogalma, helyük az objektumok közt


Kimutat sok tipikus adatforr sai

Kimutatások tipikus adatforrásai

A kimutatáshoz csatolt relációs adatbázis adatforrással oldható fel a Session1-ben tárgyalt dilemma, miszerint az Excel, mint táblázatkezelő alkalmas üzleti elemző rendszerek létrehzására, de nem igazán hatékony az ezekhez szükséges bonyolult szerkezetű, nagymennyiségű (több millió rekord) tranzació adatok tárolásában:

  • Itt a kimutatás és a belőle továbbszámoló cellaképletek végzik az elemzést

  • Az adatbázis adatforrás oldja meg a tranzakció adatok kezelését. Ez akkor tud hatéko-nyan, helytakarékosan nagymennyiségű adatot tárolni, ha a normalizáció révén fix hosz-szúságú adatszerkezetekre tudja vágni a nem fix hosszúságú gyakorlati adatstruktúrát (ld. Lesson1). Ezért igen valószínű, hogy a kimutatáshoz szükséges adatok nem egy táblában lesznek. Erre két megoldási lehetőség van:

    • Előre elkészítem az adatbázisban egy SQL lekérdezéssel a kimutatás adatforrás tábláját és fixen letárolom. Ez gyorsabb, de több tárolóhelyet igényel az adatbázisban

    • Nézet táblát készítek, aminek csak az SQL lekérdezés kódja tárolódik, és ezt hívom meg adatforrásként, erre automatikusan lefut. Ez a lassabb, de kisebb helyigényű

      4-7.PÉLDA: Excel Kimutatás a Tops Friendly Markets Inc. áruházlánc értékesítési adataira

  • A 4-7Pelda.mdb Access adatbázis SampleWeekHouseCatAggr01 táblájában a következő aggregált adatokat tároljuk az áruházlánc törzsvásárló háztartásairól:

    • A kódjuk (Household), jövedelmük (Income), $/év, képzettségük (Educat) (0:közép..4:egyetem), családméretük (Famsize), fő, 2001-es piaci szegmens-tagságuk (Segment01) (1:rossz..12:jó),

    • Hetenként (Week, WeekDate), kategóriánként (Sweet23Code, Sweet23Name) (0:egyéb..42:fagyasztott gyümölcs)

      • Bruttó forgalma (Payment), megtermelt profitja (ItemMargin),cent/háztartás/hét,

      • Vásárolt termékek mennyisége (Quantity), db vagy font/háztartás/hét,

      • hűségkártya/ gyártói engedményei (Card/ManufCoupon), cent/háztartás/hét

  • Készítsünk tetszőleges kombinációban kiválasztható háztartásra/ szegmensre/ jövedelmi/ képzési csoportra kimutatást és diagrammot a kategóriánkénti eladási idősorokról, amiből kiderülhet, ki mikor akar átpártolni a konkurrenciához (Churn Analysis)!


Kimutat sok adatforr s megad sa

Kimutatást az Adatok|Kimutatás(Data|Pivot Table) menüből induló varázsló hoz létre:

Mindig kimutatás táblát (Pivot Table) és nem diagrammot kérünk, mert előbbiből az utóbbit lehet csinálni, de fordítva nem!

Az adatforrás lehet adatbázistábla formá-jú tartalommal bíró cellatartomány (Excel list/database), egérhúzással kijelölve

Vagy külső adatbázis(External data), ek-kor Get Data-val indítjuk az MSQuery-t:

Az Adatforrás kiválasztás|Gépi forrás| Új (Select Data Source|Machine sour-ce|New) menüben ODBC meghajtót (Dri-ver) választunk: ez az adatbázis gyártójá-nak honlapjárol letölthető, Windows\Sys-tem32 könyvtárba települő kis *.DLL fájl, ami lehetővé teszi adott adatbázis olvasá-sát, akkor is ha nincs a gépre telepítve

Erre kapcsolódni próbál a driverhez,

Adjuk meg az adatbázis elérési útját (Path) vagy URL-jét és nevét(Name)

Ha kell, adjunk Usernevet, Jelszót

Csatlakozni próbál az adatbázishoz

Az adatforrás beállításait lementhet-jük egy *.DSN kiterjesztésű fájlba

Az adatbázisból kiválasztjuk a szükséges fix/nézet táblát és mezőit ( ) gombbal

Az MSQuery ekkor rákérdez, hogy szeretnénk-e a visszahozott adatok közt rendezni vagy szűrni. Mindenhol hagyjuk üresen ezeket a beállításokat, mert az ilyen feladatokat a kimutatással csináljuk

katt

Kimutatások adatforrás-megadása

katt

katt

húz

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt


Kimutat sok be ll t sai

Kimutatások beállításai

katt

katt

  • Végül állítsuk be, hogy a lekérdezett adatokat az MSQuery adja vissza az Excelnek(Return data to Excel) és a Befejez(Finish)gombbal zárjuk be

    Ekkor visszatérünk a kimutatás varázslóba, ahol az Egyebek(Options) gombot lenyitva állítsuk be:

    Szándékos Excel-lebutítás: hiába adjuk meg a Nevet(Name), nem csinál nevesített tartományt!

  • Mutasson sor/oszlop főtotálokat(Grand Total)!

  • Ne auto-formázza a táblát (AutoFormat)!

  • Őrizze meg a kézi formázást(Preserve Format)!

  • Az üres cellában mit mutasson Empty(””)helyett?

    • Az adatforrás tartalmától függően, a kimuta-tás legtöbb cellája felvehet üres értéket (pl. nincs megfelelő rekord a táblában)

    • Ha továbbszámolok a munkalapon a kimuta-tásból cellaképletekkel, akkor előnyösebb ””-t lecserélni 0-ra, mert egy csomó cellafügg-vény nem működik, ha ””-t kap paraméterül

    • Ha a kimutatásban számolok sokat OLAP formulák segítségével, akkor a fenti cserével vigyázni kell, mert állandóan #Zéróosztó (#DivByZero) cellahibát fog eredményezni, valahányszor egy nevező hiányzik

  • Mentse-e a munkafüzetbe a pivot tár OLAP kocka tartalmát (Save data with table layout)?

    • Ha mentem, akkor az Excel fájl nagyobb lesz, de gyorsan nyílik és frissül a kimutatás

    • Ha nem mentem, az Excel fájl pici lesz, de a Kimutatás lassan nyílik meg és frissül, mert újra vissza kell hozni az adatokat az adatbá-zisból, és ki kell számolni az adatkockákat. Több millió rekordos adatforrás esetén csak ez járható út, mert a túl nagy Excel fájl=fagyi!

  • Mentse az adatforrás jelszót (Save Password)

  • Frissítés(Refresh): nyitásra vagy X percenként

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt


Kimutat sok elrendez se

húz

Kimutatások elrendezése

kat

kat

kat

kat

kat

kat

Ezután a kimutatás varázsló Elrendezés (Layout) gombját lenyitva állítsuk be a következőket:

  • Sor-/Oszlop-/Lapszűrő mezők behúzása egérrel a mezőlistából a táblába, Mezőgombok lesznek

  • Sor/oszlopmező gombon duplakattal állítsuk:

    • A részaggregáció (Subtotal) típusát = Sum, Count, Min, Max, Avg, Stdev, Var

    • Az aggregátum nevét (Name): ez automati-kusan „AggrFüggvényOfMezőnév” formá-tumú, de célszerű átállítani, mert ez hosszú! Nem lehet már létező mezőnév, de üthetünk mögé 1 szóközt, ha szeretnénk,hogy hason-lítson a mezőnévre! (pl.„Week””Week_„)

    • A teljesen üres sort/oszlopotis mutassa (Show Items with no data)  így nem fog csúszkálni a táblában a kihagyott részek mi-att a sorok/oszlopok pozíciója, ez a kimuta-tás cellaképletekben hivatkozása esetén jó!

    • Speciális(Advanced) gombnál állítsuk be:

      • Sor/oszlop Rendezés(AutoSort):

        • Manuális(Manual):sorok/oszlopok egérhúzással átrendezhetők,

        • Mező(Field):1 mező szerint növek-vő(Ascending), csökkenő(Desc.)

      • A sorban legelső(Top)/-utolsó(Bottom) X db sor/oszlop mutatása(AutoShow)

  • A Lapszűrő mező beállításainál ezeken túl ki-kapcsolható adott lapok mutatása (Hide items)

  • A Tartalom mezőnél a fentieken túl Beállítások (Options) gombra előjön az adatmutatás sza-bályzó (Show data as) legördülő lista:

    • Normál (Normal): eredeti abszolút adatok

    • Sor% (Row%): megoszlás, sortotál=100%

    • Oszlop% (Column%): oszloptotál=100%

    • Összeg% (Total%): tábla főtotál=100%

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt


Kimutat sok kezel szervei

Ezután kimutatásvarázslóban megadjuk a munkala-pon a kimutatás bal felső kezdőcelláját és Befe-jez(Finish) gombbal zárjuk:

Erre a kimutatás megjelenik, mint a munkalapba ágyazott önálló objektum, de minden cellaér-

téke a lap alatta lévő cellájába is beíródik!

A lapszűrő-mezőkből legördülő menük lesz-

nek, melyekkel a megadott érték-kombináci-

ókra szűrni tudjuk a táblában mutatott infókat

A sor/oszlopmezők menüből be/kikapcsolható

értékei szerint bontva látjuk a tartalom mező aggregált értékeit az adott aggregáció mellett

A táblázatot sor/oszlop totál sorok összesítik

A kimutatás kijelölő gomb a bal felső sarok-

ban van, létrehozásakor ettől balra és le rak-

ja le a kimutatást, ezért oda ne rakjunk sem-

mi mást. Ha n lap szűrő mezőnk van, akkor

n+1sort hagyjunk ki a kezdőcella felett nekik

A Nézet|Eszközök|Kimutatás(View|Tools|Pi-

vot table)menüvel jön elő az eszközsora:

A Mezőlista( )gombbal behozhatjuk az adatfor-rás eredeti és számított mezőit, és egérrel ki/be húzhatjuk őket a kimutatás különböző részeibe

A ( )gombbal frissíthetjük a táblát

( )gombra külön lapon diagrammot csinál belőle

A Kimutatás(Pivottable)gombra nyíló menüből:

Wizard-dal újraindíthatjuk a Varázslót

A Csoportosítás és részletek mutatása (Group and Show detail) menüben:

Részletek mutatása/elrejtés(Show/Hide detail) menüre megmutatja az egérrel kijelölt aggregált kimutatás-tartalomcel-lák mögötti rekordokat külön munkalapon

Csoportosítás(Group)menüvel egérrel kijelölt sor/oszlopmező értékeket cso-portosít össze, vagy hierarchikus szer-kezetű mezőnél (pl. idő) megadható a bontási szint (év, 1/4év, hó, nap, óra)

húz

Kimutatások kezelőszervei

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt

katt


A gyakorlat tartalma3

A gyakorlat tartalma

3. Házi feladat ellenőrzése: számok szöveggé konvertálása

  • A felsővezetői jelentési rendszer alapfogalmai

  • On-Line Analytical Processing (OLAP) alapfogalmai

    • OLAP rendszerek adatkockái

    • OLAP rendszerek használata

    • OLAP rendszerek relációs adatbázis háttere

    • OLAP rendszerek aggregációs diagrammjai

      • 4-4.PÉLDA:Tops áruházlánc

    • OLAP rendszerek formuláinak működése

    • OLAP rendszerek mértékegység-egyeztetése

  • Az Excel Kimutatások fogalma, helyük az objektumok közt

    • Kimutatások tipikus adatforrásai

    • Kimutatások adatforrás-megadása

    • Kimutatások beállításai

    • Kimutatások elrendezése

    • Kimutatások kezelőszervei

    • Kimutatások formulái

    • Kimutatás-diagrammok

    • Kimutatások adatai normál Excel-diagrammokon

    • Kimutatások adatforrásának átállítása

    • Kimutatások másolási szabályai

      4-1.Házi feladat: Továbbszámolás Excel kimutatásból: Volatilitások


Kimutat sok formul i k ls hivatkoz sa s form z sa

Igen gyakran az adatforrás mezői nem a kívánt formában tartalmazzák az adatokat, hanem pl. a mértékegysé-gek egyezetetése miatt át kell számolni őket. Erre a Kimutatás|Képletek|Kiszámított mezők(Pivottable| Formulas|Calculated field) menünél egy képletszer-kesztő panelt kapunk:

A képletírás szintaktikai szabályai, az alkalmazható elemek megegyeznek az Excel cellafüggvényekével

A képlet neve nem egyezhet meg létező mezőnévvel, ha szeretnénk, hogy hasonlítson, írjunk utána szóközt

Egy képletben használhatunk más, korábban definiált mezőket, képleteket Beszúrás(Insert field)gombbal

A Képletek|Összegzés a képletekről(Formulas|List formulas) menüvel egy külön munkalapon összefog-laló táblázatot készíthetünk a kimutatás képleteinek szerkezetéről és a számítási sorrendjükről

A képlet-definíciók nem a kimutatásban, hanem a mö-göttes pivottár objektumban tárolódnak!

Mivel a kimutatás bármely eleme bármikor lehet ”” vagy 0, ha épp nincs mögötte forrásadat, az osztást tartalmazó képletekben igyekezzünk kizárni a #Zéró-osztó(#DivByZero) hiba lehetőségét egy Ha(If) függ-vénnyel: =Ha(If)(Osztó=0,0,Osztandó/Osztó)

A kimutatás celláira a munkalap más részén lévő képle-tekben kétféleképp hivatkozhatok:

Egyszerű cellahivatkozás (kézzel írom be a képletbe): ez mindig az adott munkalap-cellából veszi a kimuta-tás által beírt értéket, és nem figyel arra, hogy a kimu-tatás átrendezése miatt ez más és más lehet!

GetPivotData függvény (egérrel kattintom be a kép-letbe): ez követi a kimutatás funkcionális részét, amíg csak a kimutatásban van,akárhogy is rendezem azt át

Végezetül, ne feledkezzünk meg a kimutatás grafikai for-mázásáról: a sor/oszlop fejléc cellákra kattintva kijelöl-hetek sorokat/oszlopokat, vagy ezek alcsoportjait, a lapszűrő mezőknél kiválaszthatom, mely lapokon for-mázom ezeket, a megszokott formázó eszközökkel

Kimutatások formulái, külső hivatkozása és formázása

katt

katt

katt

katt

katt

katt

=$C$11

=GetPivotData(

EredményMezőNév,

KimutatásKezdőCella,

OszlopMezőNév,

OszlopMezőÉrték,

SorMezőNév,

SorMezőÉrték)

katt

katt

katt


Kimutat sok adatb zis t bla szer elrendez se

katt

húz

Kimutatások adatbázis tábla-szerű elrendezése

A gyakorlatban elég sokszor van szükség olyan kimutatásra, aminek oszlopaiban az eredeti adatforrás tábla oszlopai vannak, sorai viszont csoportosítják és aggregálják annak rekordjait (pl. hetekre),ez a csopor-tosító tábla (Gyroup By Table):

Szándékos Excel-lebutítás: ha intuitive elkezdjük behúzogatni egérrel az adatfor-rás mérték-jellegű mezőit a kimutatás Tar-talom részébe, ez nem egymás melletti oszlopokba rakja ki őket, ahogy normáli-san várnánk, hanem belerakja őket egy Data nevű, több mezőt összefogó gyűjtő (Collector) mezőbe, aminek több sorba egymás alá rakja a mezőit, többsoros agg-regált rekordokat gyártván, ami agyrém!

  • A problémát úgy lehet megoldani, hogy a Data gyűjtőmezőt áthúzzuk a kimutatás oszlopmező részébe, és ekkor egymás melletti oszlopokba rakja ki a részmezőit

  • Semmiképp ne keverjük ezt össze azzal, amikor több dimenzió-leíró jellegű adatfor-rás mezőt (pl.Jövedelem, Képzettség) húzunk be a kimutatás sor/oszlop fejlécé-be. Ez a sorok/oszlopok többszintű hie-rarchikus csoportosítását eredményezi a dimenzió-leíró mezők értékei szerint, pl:

katt

katt


Kimutat s diagrammok

A Kimutatás eszközsor|Kimutatásdiag-ramm( ) gombbal hozhatunk létre a kész kimutatásokból kimutatás dia-grammokat (Pivot Cart).

Ezek előnyei:

Hasonlóan a kimutatáshoz, a felhasz-náló menükből, vagy egérhúzással meg tudja változtatni az X, Y, Z tengelyeken mutatott változókat, csoportosításukat, aggregációjukat, rendezésüket, stb.

Hátrányaik:

Mindig külön munkalapon jönnek létre, egész munkalapos diagrammként, ezért prezentációkba, vagy Word doku-mentumba problémásabban illeszthetők be Szerkesztés|Irányított beillesz-tés(Edit|Paste special) menüvel Kép, metafájl (Picture, Metafile)-ként, mint egy normál diagramm: több helyet pazarolnak a széles margójukkal, ami nem állítható, mert a diagramm teljes munkalapos

A bontásuk és adattartalmuk oda-vissza szinkronizált a kimutatással. Ha a diagrammon egérrel áthúzogatjuk a tengelyek közt a mutatott mezőket, vagy a lap filter mezőkön megváltoz-tatjuk a szűrést, akkor ez a változás a kimutatásban is jelentkezik, és ott eltűn-tethet bizonyos finomabb formázási be-állításokat

Nem képesek a kimutatás csak egy adott részét megjeleníteni, mindig a rajta szereplő összes adatot felhozzák, ami néha túl sok ahhoz, hogy áttekint-hető legyen.

húz

Kimutatás-diagrammok


Kimutat sok megjelen t se norm l excel diagrammokon

A kimutatás adatait nor

mál Excel diagrammo-

kon is megjeleníthetjük

Ezek általában a kimu-

tatás tábla és lap filter

mezők közti üres, meg

növelt magasságú sor

ban kapnak helyet

Mivel egy Excel dia-

gramm csak fix cella

tartományból képes

felszedni az adatokat,

nem közvetlenül a ki-

mutatás celláit adjuk

meg a diagramm cel-

latartományának, mert

ott a cellák tartalma a

kimutatás állásától

függően mozoghat. Ál-

talában a lap filter me-

zők felett egy külön kis

táblázatot (lásd sárga

mezők) hozunk létre a

diagramm cellatarto-

mányául, ahova Get-

PivotData függvényekkel szedjük ki az adatokat a kimutatásból, amik követik annak változásait. Így a diagramm ugyanazt mutathatja a kimutatás különböző állapotaiban is.

A normál diagrammok sokkal rugalmasabban formázhatók, mint a kimutatás diagram-mok, és prezentációkba, vagy Wordbe is jobban illeszthetők „Kép, metafájl”-ként, mert átméretezhetők, még a kimutatás diagramm nem. De ezenkívül más előnyeik is vannak:

4-5.PÉLDA: Excelben nincs olyan oszlopdiagramm, ami egymásra rakott és több oszlopos megjelenítést tudna kombinálni, pedig ügyfelünknek pont ez volt a kívánsága. De egy 3D oszlopdiagramm 2D-be fordításával még ezt is ábrázolni tudjuk, az oszlopok kitakarását felhasználva! Kimutatás diagrammal ilyet nem lehet megtenni!

húz

Kimutatások megjelenítése normál Excel diagrammokon

=GetPivotData(…)

=Chart(…)


Kimutat sok adatforr s nak t ll t sa

Egy kimutatás formázása és diagrammolása igen munkaigényes. Ezért, ha a kimutatást más forrásadatokból is meg kell csinálni (pl. a következő negyedévben ugyanezt a jelentést), akkor lemásoljuk az Excel fájlt egy új példányba, és igyekszünk a kimutatás adatforrását átállítani, vagyis klónozzuk (Cloning)

Ha a kimutatás adatforrása egy cellatartomány, akkor a Kimutatás| Varázsló(Pivot table|Wizard) menüvel a varázslót újraindítva, benne visszalépegetve az adatforrás kijelölésig egyszerűen átállíthatjuk a cellatartományt:

Így bővíthetjük új sorokkal az eredeti adatforrás-tartományt, ha nem dinamikus tartományként adtuk meg (lásd: Lesson2)

Ha új cellatartományt adunk meg, ügyeljünk rá, hogy ab-

ban ugyanolyan nevű, típusú, sorrendű mezők legyenek, mint a régiben, különben a kimutatás tönkremegy

Viszont, ha egy már létező adatforrás cellatartományban átnevezünk egy mezőt, akkor a kimutatás képes lekezelni a névváltást, és nem megy tönkre, feltéve, hogy a me-

zőnevek egyediek maradnak

Szándékos Excel-lebutítás:Ha a kimutatás adatforrása

külső adatbázis, ezt csak fix elérési útról képes felszedni

(nem lehet relatív út, pl: „../Adaforras.mdb”), ezért ha a kimutatást klónozzuk, vagy átvisszük más gépre, más könvtárstruktúrába, akkor is át kell állitani az adatforrást!

Viszont Office2007 előtti verziójú Excelben hiába állítjuk

át varázslóban az adatbázis fájlt,nem vesz róla tudomást

Ezt a következő módon oldhatjuk meg:

Először a varázslóban vissza kell állítani az adatforrást 1

olyan cellatartományra, ami teljesen azonos mezőszer-

kezetű a forrástáblával, és 3-5db minta rekordot tartal-

maz belőle, majd Finish gombbal zárjuk a varázslót.

Ezután újra lefuttatjuk a varázslót, külső adatbázisra állít-

va az adatforrást, és megadva az új adatbázis fájlnevét

A Kimutatás adatforrását makrókból is állíthatjuk:

Cellatartománynál a KimutNev.SourceData-t állítjuk át

Külső adatbázisnál PivotCache objektum .Connection

és .CommandText tulajdonságait állíthatjuk.De ha több

kimutatás lóg rajta, akkor ezek csak olvashatók!!!

katt

Kimutatások adatforrásának átállítása

katt

katt

húz

Worksheets(MunkalapNev). _

PivotTables(KimutatasNev). _

SourceData=UjForrasTartomany

With Worksheets(Munkalap) _

.PivotTables(KimutatasNev) _

.PivotCache _

.Connection = Array( _

"ODBC;", _

"DSN=MS Access Database;", _

"DBQ='EleresiUt/Fajlnev';", _

"DefaultDir='EleresiUt';", _

"DriverId=25;", _

"'FIL=MS Access;", __

"MaxBufferSize=2048;", _

"PageTimeout=5;'")

.CommandText = Array( _

"SELECT", _

"Mezo1, Mezo2", _

"FROM", _

"`EleresiUt/Fajlnev`.", _

"AdatBazisNevTablaNev")

End With


Kimutat sok m sol si szab lyai

Adatbázis1

Windows ODBC

MSQuery

Excel Munkafüzet (WorkBook)

MunkaLap2 (WorkSheet2)

MunkaLap1 (WorkSheet1)

Kimutatás1 (PivotTable2)

CellaPivotTár1 (PivotCache1)

Adatbázis2

Windows ODBC

MSQuery

Excel Munkafüzet (WorkBook)

MunkaLap2 (WorkSheet2)

MunkaLap1 (WorkSheet1)

Kimutatás1 (PivotTable1)

KülsőPivotTár1 (PivotCache1)

Kimutatások másolási szabályai

CellaTart3 (CellRange3)

CellaPivotTár2 (PivotCache2)

Kimutatás1 (PivotTable2)

Fontos még azt tisztázni, hogy ha a kimutatáso-kat klónozás közben másolgatjuk, milyen objektumok jönnek létre az Excelben, és hogyan alakulnak az adatkapcsolataik:

Ha az adatforrás cellatartomány:

  • A kimutatást (pl. Kimutatás1) a bal felső kimutatás kijelölő gombbal kijelölve, vágóla-pon Ctrl+C, Ctrl+V vel átmásolhatom ugyan-azon munkalapra (pl. Munkalap1) - de ez nem célszerű, mert egymásba érhetnek - vagy másik munkalapra (pl. Munkalap2). A másolat megőrzi az eredeti összes grafikai formázását, ugyanabból a pivottárból (CellaPivotTár1) dolgozik,ezért ugyanazok a mezők és képletek lesznek benne definiálva

  • Ha az eredeti kimutatás varázslójából váltok adatforrás cellatartományt (pl. CellaTart1-ről CellaTart2-re), a CellaPivotTár1-en lógó mindkét kimutatás átvált erre az adatforrásra

  • Ha a másolat varázslójából váltok cellatarto-mányt, az elengedi az eredeti pivottárat, és újra vált (CellaPivotTár2) új adatforrással (CellaTart3)

    Ha az adatforrás külső adatbázis (Adatbázis1):

  • A kimutatás ugyanúgy másolható vágólapon mint az előbb,a másolatok azonos pivottáron lógnak (pl. KülsőPivotTár1)

  • Ekkor a pivottár a 2007 előtti Excel verziók-ban nem tud adatforrást váltani, mert a .Connection és .Command csak olvasható

  • Ezért a másolat (Kimutatás2) adatforrását egy a táblával kompatibilis szerkezetű tarto-mányra állítom (CellaTart1), amihez új pivot-tár fog tartozni (CellaPivotTár3), a kimutatás erre vált az eredeti pivottárról

CellaTart1 (CellRange1)

CellaTart2 (CellRange2)

Kimutatás1 (PivotTable1)

Kimutatás2 (PivotTable2)

KülsőPivotTár2 (PivotCache2)

CellaTart1 (CellRange1)

CellaPivotTár3 (PivotCache3)

  • Ezután megint külső adatbázisra állítom Ki-mutatás2 adatforrását (Adatbázis2), ami-hez új pivottárat csinál (KülsőPivotTár2)

  • Ekkor CellaPivotTár3 ott marad az Excel fájlban hasznavehetelen, inaktív objektum-ként, bár szerencsére kis méretű, mert csak pár mintarekord van benne


A gyakorlat tartalma4

A gyakorlat tartalma

3. Házi feladat ellenőrzése: számok szöveggé konvertálása

  • A felsővezetői jelentési rendszer alapfogalmai

  • On-Line Analytical Processing (OLAP) alapfogalmai

    • OLAP rendszerek adatkockái

    • OLAP rendszerek használata

    • OLAP rendszerek relációs adatbázis háttere

    • OLAP rendszerek aggregációs diagrammjai

      • 4-4.PÉLDA:Tops áruházlánc

    • OLAP rendszerek formuláinak működése

    • OLAP rendszerek mértékegység-egyeztetése

  • Az Excel Kimutatások fogalma, helyük az objektumok közt

    • Kimutatások tipikus adatforrásai

    • Kimutatások adatforrás-megadása

    • Kimutatások beállításai

    • Kimutatások elrendezése

    • Kimutatások kezelőszervei

    • Kimutatások formulái

    • Kimutatás-diagrammok

    • Kimutatások adatai normál Excel-diagrammokon

    • Kimutatások adatforrásának átállítása

    • Kimutatások másolási szabályai

      4-1.Házi feladat: Továbbszámolás Excel kimutatásból: Volatilitások


4 1 h zi feladat tov bbsz mol s excel kimutat sb l volatilit sok

Készítsen a 4-6Pelda.mdbSampleWeekHouseCatAggr01 táblájából kimutatás tábláza-tot +diagrammot, ami tetszőleges kombinációban kiválasztott háztartás/ szegmens/ jöve-delmi-/ képzettségi csoport vásárlási adatait tartalmazza heti és termékkategóriák szerint

Számítsa ki a kategóriánként megjelenő 83 hétnyi értékesítési idősorok relatív szórását (Relative Standard Deviation), másnéven volatilitását (Volatility):

Volatilitás, % = Szórás(IdoSorCellaTart) / Átlag(IdoSorCellaTart)

Volatility, % = Stdev(TimeSeriesRange) / Average(TimeSeriesRange)

Ez azért fontos, mert a relatív szórás arra utal, hogy a kategóriánkénti forgalom idősorok a saját átlagukhoz képest jelentősen ingadoznak az adott csoportnál.

Ha feltételezhetjük, hogy a kategóriák heti kereslete nagyjából egyenletes, akkor az ingadozás a konkurrencia akcióinak az átcsábító hatásából ered.

A kész jelentés tegye lehetővé ennek csoportonkénti, kategóriánkénti tanulmányozását

A megoldás: 4-1HaziMegoldas.xls

4-1.Házi Feladat: Továbbszámolás Excel kimutatásból: Volatilitások


  • Login