1 feladat (általános) (2 pont).

Táblázat segítségével dolgozza fel az adatokat statisztikai függvényekkel.
1. Az osztály tanulóiról (10 fő) tájékoztatást adunk, beleértve a matematika egy hónapos osztályzatait is. Számolja meg az ötösök, négyesek, kettesek és hármasok számát, keresse meg az egyes tanulók átlagpontszámát és az egész csoport átlagpontszámát! Hozzon létre egy diagramot, amely szemlélteti az osztályzatok százalékos arányát egy csoportban.

2.1 feladat (2 pont).

Négy barát három közlekedési móddal utazik: vonattal, repülővel és hajóval. Nikolai 150 km-t úszott egy gőzhajón, 140 km-t utazott vonaton és 1100 km-t repült repülőgépen. Vaszilij 200 km-t vitorlázott egy gőzösön, 220 km-t utazott vonaton és 1160 km-t repült repülőgépen. Anatolij 1200 km-t repült repülőgépen, 110 km-t utazott vonaton és 125 km-t vitorlázott gőzhajón. Maria 130 km-t utazott vonattal, 1500 km-t repült repülővel és 160 km-t vitorlázott hajóval.
A fenti adatok alapján készítsen táblázatot!

Adjon hozzá egy oszlopot a táblázathoz, amely megjeleníti az egyes srácok által megtett kilométerek számát.

Számolja ki, hogy a srácok összesen hány kilométert tettek meg a vonaton, repültek a gépen és hajóztak a hajón (minden közlekedési mód külön-külön).

Számold ki az összes barátod kilométereinek számát.

Határozza meg a barátok által megtett kilométerek maximális és minimális számát minden közlekedési módhoz.

Határozza meg az összes közlekedési mód átlagos kilométerszámát!

2.2 feladat (2 pont).

Hozzon létre egy táblázatot „Európa tavai” a következő adatok felhasználásával a területre (m2) és a legmélyebb mélységre (m): Ladoga 17 700 és 225; Onega 9510 és 110; Kaszpi-tenger 371 000 és 995; Venern 5550 és 100; Chudskoye Pskovval 3560 és 14; Balaton 591 és 11; Genf 581 és 310; Vättern 1900 és 119; Konstanz 538 és 252; Mälaren 1140 és 64. Határozza meg a területet tekintve legnagyobb és legkisebb tavat, a legmélyebb és legsekélyebb tavat!

2.3 feladat (2 pont).

Hozzon létre egy táblázatot „Európa folyói” a következő hossz (km) és medenceterület (ezer négyzetkilométer) adatok felhasználásával: Volga 3688 és 1350; Duna 2850 és 817; Rajna 1330 és 224; Elba 1150 és 148; Visztula 1090 és 198; Loire 1020 és 120; Ural 2530 és 220; Don 1870 és 422; Szajna 780 és 79; Temze 340 és 15. Határozza meg a leghosszabb és legrövidebb folyókat, számítsa ki a vízgyűjtők teljes területét, a folyók átlagos hosszát Oroszország európai részén.

3 feladat (2 pont).

A bank több szervezetnek kihelyezett hitelek kifizetésének időszerűségét rögzíti. Ismert a hitelösszeg és a szervezet által már befizetett összeg. Bírságokat állapítanak meg az adósokra: ha a cég 70 százalékot meghaladó mértékben fizette vissza a hitelt, akkor a bírság a tartozás 10 százaléka, ellenkező esetben a bírság 15 százalék. Számítsa ki az egyes szervezetekre vonatkozó bírságot, az átlagos bírságot, a teljes pénzösszeget, amelyet a bank még kap. Határozza meg a költségvetési szervezetek átlagos bírságot!

Keressen anyagot bármely leckéhez,

Egy szabványos kimutatásban a forrásadatok a helyi merevlemezen tárolódnak. Így bármikor kezelheti és átszervezheti őket, még akkor is, ha nincs hozzáférése a hálózathoz. Ez azonban semmiképpen sem vonatkozik az OLAP kimutatásokra. Az OLAP PivotTable-ban a gyorsítótár soha nem tárolódik a helyi merevlemezen. Ezért azonnal a helyi hálózatról való leválasztás után a pivot tábla elveszíti funkcionalitását. Egyik mezőt sem tudja majd áthelyezni benne.

Ha az offline állapotba kapcsolás után is elemeznie kell az OLAP-adatokat, hozzon létre egy offline adatkockát. Az offline adatkocka az külön fájl, amely egy kimutatás-gyorsítótár, amely a helyi hálózatról való leválasztás után megtekintett OLAP-adatokat tárolja. A pivot táblába másolt OLAP adatok kinyomtathatók, a http://everest.ua oldal ezt részletesen leírja.

Egy önálló adatkocka létrehozásához először hozzon létre egy OLAP-kimutatást. Vigye a kurzort a kimutatásba, és kattintson az OLAP-eszközök gombra az Eszközök környezeti lapon, amely a Kimutatáseszközök környezeti lapcsoport része. Válassza ki az Offline OLAP parancsot (9.8. ábra).

Rizs. 9.8. Hozzon létre egy offline adatkockát

Megjelenik az Offline OLAP Data Cube beállítások párbeszédpanel. Kattintson az Offline adatfájl létrehozása gombra. Elindította az Adatkockafájl létrehozása varázslót. Az eljárás folytatásához kattintson a Tovább gombra.

Először meg kell adnia azokat a méreteket és szinteket, amelyek szerepelni fognak az adatkockában. A párbeszédpanelen ki kell választania az OLAP adatbázisból importált adatokat. Az ötlet az, hogy csak azokat a méreteket adja meg, amelyekre szükség lesz, miután a számítógépet leválasztják a helyi hálózatról. Minél több dimenziót ad meg, annál nagyobb lesz az offline adatkocka.

Kattintson a Tovább gombra, hogy továbblépjen a varázsló következő párbeszédpaneljéhez. Lehetővé teszi olyan tagok vagy adatelemek megadását, amelyek nem fognak szerepelni a kockában. Különösen nem lesz szüksége az Internetes értékesítés kiterjesztett összegére, így az nem lesz bejelölve a listában. A jelölőnégyzet törlése azt jelzi, hogy a megadott elem nem lesz importálva, és extra helyet foglal el a helyi merevlemezen.

Az utolsó lépésben adja meg az adatkocka helyét és nevét. Esetünkben a kockafájl neve MyOfflineCube.cub lesz, és a Work mappában található.

Az adatkocka-fájlok kiterjesztése .kölyök

Egy idő után az Excel elmenti az offline adatkockát a megadott mappába. A teszteléshez kattintson duplán a fájlra, amely automatikusan létrehoz egy Excel-munkafüzetet, amely a kiválasztott adatkockához társított kimutatást tartalmaz. Létrehozása után az offline adatkockát szétoszthatja minden érdeklődő felhasználó számára, aki offline LAN módban dolgozik.

Miután csatlakozott a helyi hálózathoz, megnyithatja és frissítheti az offline adatkockafájlt, valamint a megfelelő adattáblázatot. Fő elv azt mondja, hogy az offline adatkocka csak akkor működik, ha a helyi hálózat megszakad, de a kapcsolat helyreállítása után szükségszerűen frissítik. Az offline adatkocka frissítésére tett kísérlet a kapcsolat megszakadása után sikertelen lesz.

Munka offline kockafájlokkal

Az offline kockafájl (.cub) OLAP (Online Analytical Processing) kocka formájában tárolja az adatokat. Ezek az adatok az OLAP-kiszolgálón lévő OLAP-adatbázis részét képezhetik, vagy az OLAP-adatbázistól függetlenül is előállíthatók. Használjon offline kockafájlt a kimutatás és kimutatás jelentésekkel való munka folytatásához, amikor a kiszolgáló nem elérhető, vagy offline állapotban van.

Biztonsági megjegyzés: Legyen óvatos, amikor olyan offline kockafájlt használ vagy terjeszt, amely érzékeny vagy személyes adatokat tartalmaz. Kockafájl helyett ajánlatos az adatokat munkafüzetben tárolni, hogy az adatokhoz való hozzáférést a jogkezeléssel szabályozhassuk. további információ megtalálható az Információs jogok kezelése az Office-ban.

Ha OLAP-kiszolgáló forrásadatokon alapuló kimutatással vagy kimutatásdiagram jelentéssel dolgozik, az Offline kocka varázsló segítségével a forrásadatokat egy külön offline kockafájlba másolhatja a számítógépen. Ezen offline fájlok létrehozásához olyan OLAP-adatszolgáltatóra van szükség, amely támogatja ezt a képességet, például a Microsoft SQL Server Analysis Services MSOLAP-ját, amely telepítve van a számítógépre.

Jegyzet: A Microsoft SQL Server Analysis Services szolgáltatásból származó offline kockafájlok létrehozása és használata feltételekhez és licencekhez kötött. Microsoft telepítések SQL szerver. Tekintse át a vonatkozó SQL Server-kiadás licencinformációit.

Munka az Offline Cube varázslóval

Offline kockafájl létrehozásához kiválaszthatja az adatok egy részhalmazát az OLAP-adatbázisban az offline kockavarázsló segítségével, majd mentheti azt. A jelentésnek nem kell tartalmaznia a fájlban található összes mezőt, és nem kell ezek közül egyet sem kijelölni, illetve az OLAP adatbázisban elérhető adatmezőket. A fájl minimális számának csökkentése érdekében csak azokat az adatokat szerepeltetheti, amelyeket meg szeretne jeleníteni a jelentésben. Az összes dimenziót elhagyhatja, és a legtöbb dimenziótípus esetében kizárhatja azokat az alacsonyabb szintű információkat és a felső szintű elemeket is, amelyeket nem szeretne megjeleníteni. Az összes felvett elem esetében az adatbázisban az adott elemekhez rendelkezésre álló tulajdonságmezőket a rendszer az offline fájlban is tárolja.

Adatok átvitele ide offline módés fordított kapcsolatuk

Ehhez először létre kell hoznia egy kimutatást vagy kimutatást a kiszolgáló adatbázisa alapján, majd a jelentésből önálló kockafájlt kell létrehoznia. Ezt követően bármikor átkapcsolhatja a jelentést a szerver adatbázis és az offline fájl között. Például ha használ laptop otthoni és videós utazáshoz, majd csatlakoztassa újra a számítógépet a hálózathoz.

Az alábbiakban ismertetjük a követendő fő lépéseket elem élettartam az adatokkal, majd vigye vissza az adatokat az internetre.

Hozzon létre vagy nyisson meg egy kimutatást vagy kimutatást az offline módban elérni kívánt OLAP-adatok alapján.

Hozzon létre egy offline kockafájlt a számítógépén. fejezetben Hozzon létre egy offline kockafájlt egy OLAP-kiszolgáló adatbázisából(a cikkben lent).

Leválasztás a hálózatról és offline kockafájl használata.

Lépjen online, és csatolja újra a kockafájlt offline módban. Nézze meg a részt Az offline kockafájl újracsatlakoztatása az OLAP-kiszolgáló adatbázisához(a cikkben lent).

Frissítse az offline kockafájlt új adatokkal, és hozza létre újra az offline kockafájlt. Nézze meg a részt frissítse és hozza létre újra az offline kockafájlt(a cikkben lent).

BLOG

Csak minőségi hozzászólások

Mik azok az Excel PivotTable-ok és az OLAP-kockák?

Nézze meg a cikkhez készült videót:

OLAP- ez angol. online analitikai feldolgozás, valós idejű analitikai adatfeldolgozási technológia. közérthető nyelven- többdimenziós adatokkal rendelkező tároló (Cube), még egyszerűbb - csak egy adatbázis, amelyből Excelben lehet adatokat gyűjteni és az Excel eszközzel elemezni - PivotTables.

Pivot táblák egy felhasználói felület többdimenziós adatok megjelenítésére. Más szóval - egy speciális fajta táblázat, amellyel szinte bármilyen jelentést készíthet.

Az egyértelműség érdekében hasonlítsuk össze a "Szabványos táblázatot" a "Pivot Table"-val.

Normál asztal:

Pivot tábla:

Fő különbség Pivot táblák az ablak jelenléte Pivot tábla mezőlista amelyek közül választhat Szükséges Mezőkés automatikusan kap minden asztalt!

Hogyan kell használni

nyisd ki Excel fájl, amely az OLAP kockához csatlakozik, például "BIWEB":

Most ez mit jelent, és hogyan kell használni?

Húzza át a szükséges mezőket, hogy például a következő táblázatot kapja meg:

« Pluszok» lehetővé teszi, hogy részletezze a jelentést. Ebben a példában a „Márka” a „Rövidített nevek”-ig, a „Negyed” pedig a „Hónap”-ig lesz lefúrva, azaz. Így:

Analitikai függvények az Excelben (kockafüggvények)

A Microsoft folyamatosan új funkciókkal bővíti az Excelt az adatok elemzése és megjelenítése terén. Az Excelben az információkkal való munka három viszonylag független rétegként ábrázolható:

• "helyesen" rendszerezett forrásadatok
• az adatfeldolgozás matematikája (logikája).
• adatábrázolás

Rizs. 1. Adatelemzés Excelben: a) nyers adatok, b) mérés Power Pivotban, c) műszerfal; A kép nagyításához kattintson rá jobb gombbal, és válassza ki Kép megnyitása új lapon

Jegyzet letöltése itt Word formátum vagy pdf, excel példák

Kockafüggvények és pivot táblák

Az adatok bemutatásának legegyszerűbb és egyben nagyon hatékony eszközei a pivot táblák. Felépíthetők az alábbi adatokból: a) egy Excel-munkalap, b) egy OLAP-kocka vagy c) egy Power Pivot adatmodell. Az utolsó két esetben a pivot tábla mellett analitikus függvényekkel (kockafüggvényekkel) készíthetünk jelentést Excel-lapon. A pivot táblák egyszerűbbek. A kockafüggvények összetettebbek, de nagyobb rugalmasságot biztosítanak, különösen a jelentéskészítésben, ezért széles körben használják az irányítópultokban.

A következő tárgyalás a Power Pivot modellen, illetve néhány esetben az OLAP-kockákon alapuló kockaképletekre és kimutatástáblákra vonatkozik.

Egy egyszerű módja annak, hogy megkapja a kockák funkcióit

Amikor (ha) elkezdte tanulni a VBA-kódot, megtanulta, hogy a kód beszerzésének legegyszerűbb módja egy makró rögzítése. Továbbá a kód szerkeszthető, ciklusok, ellenőrzések, stb. adhatók hozzá. Hasonlóképpen, a kockafüggvények halmazának beszerzésének legegyszerűbb módja a pivot tábla konvertálása (2. ábra). Álljon a pivot tábla bármelyik cellájára, és lépjen a lapra Elemzés, kattintson a gombra Felszerelés OLAP, és nyomja meg Konvertálás képletekre.

Rizs. 2. PivotTable konvertálása kockafüggvénykészletté

A számok mentésre kerülnek, és ezek nem értékek, hanem képletek, amelyek adatokat nyernek ki a Power Pivot adatmodellből (3. ábra). A kapott táblázatot formázhatja. Különösen törölhet és szúrhat be sorokat és oszlopokat a táblázatba. A szelet megmarad, és ez befolyásolja a táblázat adatait. Az eredeti adatok frissítésekor a táblázatban szereplő számok is frissülnek.

Rizs. 3. Kockaképletek alapján készült táblázat

CUBEVALUE() függvény

Talán ez a kockák fő funkciója. Egyenértékű a területtel Értékek Pivot tábla. A CUBEVALUE lekéri az adatokat egy Power Pivot kockából vagy modellből, és megjeleníti azokat a kimutatástáblán kívül. Ez azt jelenti, hogy Önt nem korlátozzák a pivot tábla határai, és számtalan lehetőséggel készíthet jelentéseket.

Képlet írása a semmiből

Nem kell konvertálnia a kész kimutatást. Bármilyen kockaképletet a semmiből írhat. Például a következő képletet írjuk be a C10 cellába (4. ábra):

Rizs. 4. A C10 cellában lévő CUBEVALUE() függvény minden év kerékpárértékesítését adja vissza, mint a kimutatásban

Kis trükk. A kockaképletek könnyebb olvashatósága érdekében kívánatos, hogy minden sorba csak egy argumentum kerüljön. Az Excel ablakot kicsinyítheti. Ehhez kattintson az ikonra Gurulj be az ablakba a képernyő jobb felső sarkában található. Ezután állítsa be az ablak méretét vízszintesen. Alternatív lehetőség– kényszeríti a képlet szövegét a tördelésre új sor. Ehhez a képletsorban vigye a kurzort arra a helyre, ahová az átvitelt szeretné végrehajtani, és nyomja meg az Alt + Enter billentyűt.

Rizs. 5. Az ablak kicsinyítése

A CUBEVALUE() függvény szintaxisa

Az Excel súgója teljesen pontos és teljesen használhatatlan kezdőknek:

CUBEVALUE(kapcsolat, [elem_kifejezés1], [elem_kifejezés2], ...)

Kapcsolat kötelező érv; egy szöveges karakterlánc, amely a kockával való kapcsolat nevét reprezentálja.

elem_kifejezés– opcionális érv; egy MDX-et képviselő szöveges karakterlánc, amely egy elemet vagy sort ad vissza a kockában. Ezenkívül az "elem_kifejezés" a CUBESET függvény segítségével definiált halmaz is lehet. Használja a "member_expression"-t szeletként, hogy meghatározza a kocka azon részét, amelyre összesített értéket szeretne visszaadni. Ha nincs mérték megadva az elem_kifejezésben, akkor a rendszer az adott kocka alapértelmezett mértékét használja.

Mielőtt folytatná a CUBEVALUE függvény szintaxisának magyarázatát, néhány szót a kockákról, az adatmodellekről és a rejtjelekről tuple.

Néhány háttér az OLAP-kockákról és a Power Pivot adatmodellekről

OLAP adatkockák ( O n l ine A elemző P rocessing - operatív adatelemzés) kifejezetten analitikai feldolgozásra és gyors adatkinyerésre lettek kifejlesztve belőlük. Képzeljünk el egy háromdimenziós teret, ahol a tengelyek időszakok, városok és áruk (5a. ábra). Egy ilyen koordináta rács csomópontjai különféle mérőszámok értékeit tartalmazzák: értékesítési volumen, nyereség, költségek, eladott egységek száma stb. Most képzelje el, hogy több tucat vagy akár több száz mérés van... és van még egy sok intézkedés. Ez lesz a többdimenziós OLAP-kocka. Az OLAP-kockák létrehozása, konfigurálása és naprakészen tartása az informatikai szakemberek dolga.

Rizs. 5a. 3D OLAP kocka

Az Excel elemző képletek (kockaképletek) kivonják a tengelycímeket (például Idő), az elemek nevei ezeken a tengelyeken (augusztus, szeptember), a mértékek értéke a koordináták metszéspontjában. Ez a struktúra teszi lehetővé, hogy a kocka alapú pivot táblák és kockaképletek olyan rugalmasak legyenek, és alkalmazkodjanak a felhasználók igényeihez. Pivot táblák alapúak Excel lapok ne használjon mértékeket, így azok nem olyan rugalmasak az adatelemzési célokra.

Power Pivot - Viszonylag új funkció Microsoft. Ez egy beépített Excel és némileg független környezet ismerős felülettel. A Power Pivot sokkal jobb, mint a szabványos kimutatások. Ugyanakkor a kockák fejlesztése a Power Pivotban viszonylag egyszerű, és ami a legfontosabb, nem igényel informatikus részvételt. A Microsoft megvalósítja szlogenjét: „Üzleti elemzés – a tömegekhez!”. Bár a Power Pivot modellek nem 100%-os kockák, kockáknak is nevezhetjük őket (további részletekért lásd Mark Moore Power Pivot bevezető tanfolyamát és Rob Colley hosszabb kiadását, a Power Pivot DAX Formulas-t).

A kocka fő összetevői a dimenziók, a hierarchiák, a szintek, az elemek (vagy tagok; angolul Members) és a mértékek (mértékek). Mérés - az elemzett adatok fő jellemzője. Például termékkategória, időszak, értékesítési földrajzi terület. A dimenzió olyan dolog, amelyet a pivot tábla egyik tengelyére helyezhetünk. Minden dimenziótól eltekintve egyedi értékek tartalmaz egy elemet, amely összesíti ennek a dimenziónak az összes tagját.

A mérések alapja hierarchia. Például egy termékkategória felosztható alkategóriákra, majd modellekre, végül terméknevekre (5b. ábra) A hierarchia lehetővé teszi összefoglaló adatok létrehozását és elemzését a szerkezet különböző szintjein. Példánkban a hierarchia Kategória tartalmazza a 4 szint.

Elemek(egyéni tagok) minden szinten jelen vannak. Például a Kategória szint négy elemből áll: Kiegészítők, Kerékpárok, Ruházat, Alkatrészek. A többi szintnek megvannak a maga elemei.

intézkedéseket kiszámított értékek, például értékesítési mennyiség. A kockákban lévő mértékek saját dimenziójukban, úgynevezett (lásd a 9. ábrát lent). Az intézkedéseknek nincs hierarchiája. Minden mérték kiszámít és tárol egy értéket minden dimenzióhoz és minden taghoz, és az alapján szeletel, hogy mely méretelemeket helyezzük el a tengelyen. Azt is megmondják, hogy milyen koordinátákat állítsunk be, vagy melyik szűrőkörnyezetet állítjuk be. Például a 2. ábrán. 5a minden kis kockában ugyanazt a mértéket számítják ki - Profit. A mérték által visszaadott érték pedig a koordinátáktól függ. Az 5a. ábra jobb oldalán az látható, hogy a Profit (három koordinátában) Moszkvában októberben almán = 63 000 rubel. A mérték értelmezhető, és a mérések egyikeként. Például a 2. ábrán. 5a tengely helyett Áruk, helytengely Intézkedések elemekkel Az értékesítés volumene, Nyereség, Egység eladva. Ezután minden cella lesz valamilyen érték, például Moszkva, szeptember, értékesítési volumen.

Tuple- több elem különböző dimenziók, amely a kocka tengelyei mentén határozza meg a koordinátákat, amelyben a mértéket számítjuk ki. Például a 2. ábrán. 5a Tuple= Moszkva, október, alma. Szintén érvényes sor a Perm, alma. Egy másik az alma, augusztus. A leíróban nem szereplő dimenziók implicit módon jelen vannak a leíróban, és az alapértelmezett tag képviseli őket. Így egy többdimenziós tér cellája mindig definiálva van teljes készlet koordinátákat, még akkor is, ha néhányat kihagyunk a sorból. Nem foglalhat két azonos dimenziójú elemet egy sorba, a szintaxis ezt nem teszi lehetővé. Például az érvénytelen sor Moszkva és Perm, alma. Egy ilyen többdimenziós kifejezés megvalósításához két sorra van szüksége: Moszkva és alma + Perm és alma.

Elemek halmaza– több azonos dimenziójú elem. Például alma és körte. Sorok halmaza- több sor, amelyek mindegyike ugyanabban a sorrendben azonos méretekből áll. Például egy két sorból álló készlet: Moszkva, alma és Perm, banán.

Automatikus kiegészítés a mentéshez

Térjünk vissza a CUBEVALUE függvény szintaxisához. Használjuk az automatikus kiegészítést. Kezdje el beírni a képletet egy cellába:

Az Excel felajánlja az Excel-munkafüzetben elérhető összes kapcsolatot:

Rizs. 6. A Power Pivot adatmodellhez való csatlakozást mindig ThisWorkbookDataModelnek hívják

Rizs. 7. Csatlakozások kockákhoz

Folytassuk a képlet beírását (esetünkben az adatmodellhez):

Az automatikus kiegészítés az összes elérhető táblát és adatmodell mértékét javasolja:

Rizs. 8. Az első szint elérhető elemei - táblázatok nevei és mértékegységei (kiemelve)

Válasszon egy ikont Intézkedések. Vmerre mutat:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

Az automatikus kiegészítés minden elérhető intézkedést javasol:

Rizs. 9. A második szint elérhető elemei az intézkedéscsomagban

Válasszon mértéket. Idézőjelek hozzáadása, záró zárójel, Enter.

CUBEVALUE(" ThisWorkbookDataModel " ; " . ")

Rizs. 10. CUBEVALUE képlet Excel cellában

Hasonlóképpen hozzáadhat egy harmadik argumentumot a képlethez:

VBA az Excelben Excel.PivotTable objektum és a Kimutatások és OLAP-kockák használata az Excelben

10.8 Munkavégzés kimutatástáblákkal (PivotTable objektum)

Excel.PivotTable objektum, programozottan dolgozzon a kimutatástáblákkal és az OLAP kockákkal az Excelben VBA, PivotCache objektum segítségével, hozzon létre egy kimutatástábla elrendezést

A legtöbb vállalkozás működése során a tevékenységekre vonatkozó ún. nyers adatok halmozódnak fel. Például egy kereskedelmi vállalkozás esetében az áruk értékesítésére vonatkozó adatok gyűjthetők - minden vásárláshoz külön, vállalkozások számára sejtes kommunikáció- statisztika betöltése bázisállomások stb. Nagyon gyakran egy vállalkozás vezetésének analitikus információra van szüksége, amelyet nyers információk alapján állítanak elő - például annak kiszámításához, hogy az egyes termékek milyen mértékben járulnak hozzá a vállalkozás bevételéhez vagy a szolgáltatás minőségéhez egy adott állomás zónájában. A nyers információból nagyon nehéz ilyen információt kinyerni: nagyon kell teljesíteni összetett SQL lekérdezések, amelyek végrehajtása hosszú időt vesz igénybe, és gyakran zavarja az aktuális munkát. Ezért egyre több jelenleg nyers adat kerül fel először az Adattárházba, majd az OLAP kockákba, amelyek nagyon kényelmesek az interaktív elemzéshez. Az OLAP kockákat legegyszerűbben többdimenziós tábláknak tekinthetjük, amelyekben a szokásos két dimenzió (oszlopok és sorok, mint a normál táblázatokban) helyett sok méret is előfordulhat. A "metszeti" kifejezést általában a kocka méreteinek leírására használják. Például egy marketing osztálynak szüksége lehet időre, régióra, terméktípusra, értékesítési csatornára stb. A kockák segítségével (szemben a szabványos SQL lekérdezésekkel) nagyon könnyű választ kapni olyan kérdésekre, mint „hány ilyen típusú terméket értékesítettek a tavalyi év negyedik negyedévében az északnyugati régióban regionális forgalmazókon keresztül.

Természetesen ilyen kockák nem hozhatók létre normál adatbázisokban. Az OLAP kockákhoz speciális szoftvertermékek szükségesek. Az SQL Server a Microsoft Analysis Services nevű OLAP-adatbázisával érkezik. Vannak OLAP megoldások az Oracle, IBM, Sybase stb.

Az ilyen kockákkal való munkavégzéshez egy speciális kliens van beépítve az Excelbe. Oroszul úgy hívják Pivot tábla(a grafikus képernyőn a menün keresztül érhető el Adat -> Pivot tábla), és angolul - Pivot tábla. Ennek megfelelően az ügyfél által képviselt objektumot PivotTable-nek nevezik. Megjegyzendő, hogy nem csak OLAP kockákkal, hanem Excel táblákban vagy adatbázisokban lévő szokásos adatokkal is tud működni, de sok funkció elvész.

A PivotTable és a PivotTable objektum a Panorama Software szoftvertermékei, amelyeket a Microsoft vásárolt meg és integrált az Excelbe. Ezért a kimutatás-objektummal való munka némileg eltér a többi Excel-objektum használatától. Gyakran nehéz kitalálni, mit kell tenni. Ezért ajánlatos aktívan használni a makrórögzítőt tippek fogadására. Ugyanakkor a pivot táblákkal való munka során a felhasználóknak gyakran ugyanazokat az ismétlődő műveleteket kell végrehajtaniuk, ezért sok esetben szükség van az automatizálásra.

Hogyan néz ki programozottan dolgozni egy kimutatási táblával?

Az első dolog, amit tennünk kell, hogy létrehozunk egy PivotCache objektumot, amely az OLAP-forrásból lekért rekordok készletét képviseli. Nagyon feltételesen ez a PivotCache objektum összehasonlítható a QueryTable-lel. Csak egy PivotCache objektum használható kimutatás-objektumként. A PivotCache objektum a PivotCaches gyűjtemény Add() metódusával jön létre:

Dim PC1 PivotCache-ként

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

A PivotCaches egy szabványos gyűjtemény, és a részletes átgondolást érdemlő metódusok közül csak az Add() metódus nevezhető meg benne. Ez a módszer két paramétert igényel:

• Forrás típus- kötelező, meghatározza a pivot tábla adatforrásának típusát. Dönthet úgy, hogy egy Excel-tartomány, adatbázisból származó adatok, külső adatforrás, egy másik kimutatás stb. alapján hoz létre kimutatást. A gyakorlatban általában csak akkor van értelme az OLAP-ot használni, ha sok adat van – ennek megfelelően speciális külső tárhely(például Microsoft Analysis Services). Ebben a helyzetben az xlExternal van kiválasztva.
• SourceData- minden esetben kötelező, kivéve ha az első paraméter értéke xlExternal. Szigorúan véve meghatározza azt az adattartományt, amely alapján a kimutatás létrejön. Általában egy Range objektumot vesz fel.

A következő feladat a PivotCache objektum paramétereinek konfigurálása. Mint már említettük, ez az objektum nagyon hasonlít a QueryTable-re, és tulajdonságai és metódusai nagyon hasonlóak. Néhány a legtöbb fontos tulajdonságaités módszerek:

• ADOConnection- egy ADO Connection objektum visszaküldésének képessége, amely automatikusan létrejön a külső adatforráshoz való csatlakozáshoz. A kapcsolat tulajdonságainak további konfigurálására szolgál.
• kapcsolat- pontosan ugyanúgy működik, mint az azonos nevű QueryTable objektumtulajdonság. Elfogadhat kapcsolati karakterláncot, előkészített Recordset objektumot, szöveges fájlt, webes kérést. Microsoft Query fájl. Az OLAP-pal végzett munka során leggyakrabban a kapcsolati karakterláncot közvetlenül írják (mivel nincs értelme egy Recordset objektum fogadásának, például az adatok megváltoztatásához - az OLAP adatforrások szinte mindig csak olvashatók). Például ennek a tulajdonságnak a beállítása a Foodmart adatbázishoz (Analysis Services mintaadatbázishoz) való csatlakozáshoz a LONDON szerveren a következőképpen nézhet ki:

PC1.Connection = "OLEDB; Szolgáltató=MSOLAP.2; Adatforrás=LONDON1; Kezdeti katalógus = FoodMart 2000"

• tulajdonságait CommandTypeÉs parancsszöveg ugyanúgy írja le az adatbázis-kiszolgálónak küldött parancs típusát és magának a parancsnak a szövegét. Például az Értékesítési kocka eléréséhez és annak teljes gyorsítótárba helyezéséhez az ügyfélen használhatja a hasonló kódokat

• ingatlan Helyi kapcsolat lehetővé teszi az Excel segítségével létrehozott helyi kockához (*.cub fájlhoz) való csatlakozást. Természetesen erősen ellenjavallt az ilyen fájlok használata "gyártási" adatkötetekkel való munkavégzéshez - csak elrendezések készítésére stb.
• ingatlan Használt memória visszaadja a számot véletlen hozzáférésű memória, amelyet a PivotCache használ. Ha az ezen a kimutatás-gyorsítótáron alapuló kimutatás még nincs létrehozva és megnyitva, akkor 0-t ad vissza. Használható annak ellenőrzésére, hogy az alkalmazás működik-e gyenge klienseken.
• ingatlan OLAP Igaz értéket ad vissza, ha a PivotCache csatlakozik az OLAP-kiszolgálóhoz.
• OptimizeCache- a gyorsítótár szerkezetének optimalizálásának képessége. Az adatok kezdeti betöltése tovább tart, de ezután a munka sebessége megnőhet. OLE DB források esetén nem működik.

A PivotCache objektum többi tulajdonságai megegyeznek a QueryTable objektum tulajdonságaival, ezért itt nem tárgyaljuk őket.

A PivotCache objektum fő metódusa a CreatePivotTable() metódus. Ennek a módszernek a segítségével a következő lépést hajtjuk végre - egy pivot tábla (PivotTable objektum) létrehozását. Ez a módszer négy paramétert vesz igénybe:

• TableDestination az egyetlen kötelező paraméter. Elfogad egy Range objektumot, amelynek bal felső sarkába kerül a pivot táblázat.
• táblázat neve- Pivot tábla neve. Ha nincs megadva, akkor a „PivotTable1” űrlap neve automatikusan létrejön.
• adatokat olvasni- ha True értékre van állítva, akkor a kocka összes tartalma automatikusan gyorsítótárba kerül. Nagyon óvatosnak kell lennie ezzel a paraméterrel, mert helytelen használata drámaian megnövelheti a kliens terhelését.
• DefaultVersion- Ez a tulajdonság általában nincs megadva. Lehetővé teszi a létrehozandó kimutatás verziójának megadását. Alapértelmezés szerint a legfrissebb verzió használatos.

A kimutatástábla létrehozása a munkafüzet első lapjának első cellájában így nézhet ki:

PC1.CreatePivotTable Range("A1")

A pivot tábla létrejött, de közvetlenül a létrehozás után üres. Négy területet biztosít a forrásból származó mezők elhelyezésére (a grafikus képernyőn mindezt akár az ablak segítségével is beállíthatjuk Pivot tábla mezőlista- automatikusan vagy gombbal nyílik Elrendezés a PivotTable varázsló utolsó képernyőjén):

• oszlopterület- tartalmazza azokat a dimenziókat („szakasz”, amelyben az adatok elemzésére kerül sor), amelynek tagjai kisebbek;
• vonalterület- azok a dimenziók, amelyek tagjai több;
• oldal területe- azokat a méréseket, amelyekkel csak szűrni kell (például csak ilyen és olyan régióra, vagy csak ilyen és ilyen évre vonatkozóan mutatni az adatokat);
• adatterület- valójában a táblázat központi része. Azok a számszerű adatok (például az eladások mennyisége), amelyeket elemezünk.

Nehéz arra hagyatkozni, hogy a felhasználó helyesen helyezze el az elemeket mind a négy területen. Ezenkívül eltarthat egy ideig. Ezért gyakran szükséges az adatokat programozottan elrendezni egy kimutatásban. Ezt a műveletet a CubeField objektum segítségével hajtják végre. Ennek az objektumnak a fő tulajdonsága az Orientation, ez határozza meg, hogy ez vagy az a mező hol lesz. Tegyük például az Ügyfelek dimenziót az oszlopterületre:

PT1.CubeFields(""). Orientation = xlColumnField

Ezután - az Idő dimenzió a karakterláncok területén:

PT1.CubeFields(""). Tájolás = xlRowField

Ezután - a Termék dimenziót az oldal területére:

PT1.CubeFields(""). Tájolás = xlPageField

És végül a mutató (számszerű adatok az elemzéshez) Egységértékesítés:

PT1.CubeFields("."). Orientation = xlDataField

Az első PivotTable felület, más néven Pivot Reports, 1993-ban került be az Excelbe ( Excel verziók 5.0). Annak ellenére, hogy sok hasznos funkcionalitás, a legtöbb Excel-felhasználó munkájában gyakorlatilag nem használják. Még a tapasztalt felhasználók is gyakran az "összefoglaló jelentés" kifejezés alatt olyasmit értenek, ami összetett képletekkel készült. Próbáljuk meg népszerűsíteni a pivot táblák használatát a közgazdászok mindennapi munkájában. Ez a cikk az összefoglaló jelentések készítésének elméleti alapjait tárgyalja, gyakorlati javaslatokat ad ezek használatára, valamint példát ad több táblázat alapján az adatok elérésére.

A többváltozós adatelemzés feltételei

A legtöbb közgazdász hallott már a „többdimenziós adatok”, „virtuális kocka”, „OLAP technológiák” stb. kifejezéseket. De egy részletes beszélgetés során általában kiderül, hogy szinte mindenki nem igazán tudja, mit kérdéses. Ez azt jelenti, hogy az emberek valami összetettet jelentenek, és általában nem relevánsak napi tevékenységeik szempontjából. Valójában nem.

Többdimenziós adatok, méretek

Nyugodtan kijelenthetjük, hogy a közgazdászok szinte mindig többdimenziós adatokkal foglalkoznak, de ezeket előre meghatározott módon igyekeznek bemutatni táblázatok segítségével. A többdimenziósság itt azt a képességet jelenti, hogy ugyanazt az információt változtatással be lehet vinni, megtekinteni vagy elemezni kinézet, az adatok különféle csoportosításait és rendezéseit alkalmazva. Például egy értékesítési tervet a következő kritériumok szerint lehet elemezni:

• árutípusok vagy -csoportok;
• márkák vagy termékkategóriák;
• időszakok (hónap, negyedév, év);
• vásárlók vagy vevők csoportjai;
• értékesítési régiók
• stb.

A többváltozós adatelemzés fenti kritériumainak mindegyikét "dimenziónak" nevezik. Azt mondhatjuk, hogy egy dimenzió egy bizonyos értékkészletre vonatkozó információt jellemzi. A többdimenziós információ mérésének egy speciális típusa az „adat”. Példánkban az értékesítési terv adatai a következők lehetnek:

• értékesítés volumene;
• Eladási ár;
• egyéni kedvezmény
• stb.

Elméletileg az adatok a többdimenziós információk szabványos dimenziói is lehetnek (például csoportosíthatja az adatokat eladási ár alapján), de az adatok általában egy speciális értéktípust jelentenek.

Így elmondhatjuk, hogy a gyakorlati munkában a közgazdászok kétféle információt használnak: többdimenziós adatok ( tényleges és tervezett számok sok funkcióval) és könyvtárak (adatok jellemzői vagy mérései).

OLAP

Az OLAP (online analitikai feldolgozás) rövidítés szó szerinti fordításban így hangzik: " elemző feldolgozás valós időben". A definíció nem túl konkrét, szinte bármilyen szoftvertermékről szóló jelentés összefoglalható ez alatt. Definíció szerint az OLAP olyan technológiát foglal magában, amely speciális jelentésekkel dolgozik, beleértve a többdimenziós strukturált adatok megszerzésére és elemzésére szolgáló szoftvereket is. Az OLAP technológiákat megvalósító népszerű szoftvertermékek egyike az SQL Server Analysis Server. Vannak, akik tévesen őt tartják a koncepció szoftveres megvalósításának egyetlen képviselőjének.

Virtuális adatkocka

A "virtuális kocka" (többdimenziós kocka, OLAP-kocka) egy szakkifejezés, amelyet néhány speciális gyártó használt. szoftver. Az OLAP rendszerek általában saját struktúrájukban készítik elő és tárolják az adatokat, és ezekből a virtuális kockákból speciális elemzési felületek (például Excel összefoglaló jelentések) érik el az adatokat. Ugyanakkor egy ilyen dedikált tároló használata egyáltalán nem szükséges többdimenziós információk feldolgozásához. Általában, virtuális kocka- ez egy speciálisan optimalizált többdimenziós adattömb, amelyet összefoglaló jelentések készítésére használnak. Speciális szoftvereszközökkel és adatbázistáblázatokhoz vagy bármely más forráshoz, például Excel-táblázathoz való egyszerű hozzáféréssel is beszerezhető.

Pivot tábla

"Pivot Report" (pivot table, Pivot Table) egy felhasználói felület többdimenziós adatok megjelenítésére. Ezen a felületen csoportosíthatja, rendezheti, szűrheti és módosíthatja az adatokat, hogy különböző analitikai mintákat kapjon. A riport frissítése egyszerű kezelőfelületi eszközökkel történik, az adatok a megadott szabályok szerint automatikusan összesítésre kerülnek, és nincs szükség további információk vagy újbóli bevitelére. Az Excel PivotTable felülete talán a legnépszerűbb szoftver termék többdimenziós adatokkal való munkavégzéshez. Mind a külső adatforrásokat (OLAP-kockák és relációs adatbázisok), mind a belső táblázat-tartományokat támogatja adatforrásként. A 2000-es verziótól (9.0) az Excel is támogatja grafikus forma többdimenziós adatok megjelenítése - Pivot Chart.

Az Excelben megvalósított PivotTable felület lehetővé teszi a többdimenziós adatok dimenzióinak elrendezését a munkalapterületen. Az egyszerűség kedvéért a kimutatástáblázatot úgy is felfoghatja, mint egy cellatartomány tetején elhelyezkedő jelentést (valójában a cellaformátumok bizonyos módon kötődnek a kimutatástábla mezőihez). Az Excel-kimutatás négy megjelenítési területtel rendelkezik: szűrő, oszlopok, sorok és adatok. Az adatdimenziók el vannak nevezve pivot tábla mezői. Ezeknek a mezőknek saját tulajdonságaik és megjelenítési formátumuk van.

Még egyszer szeretném felhívni a figyelmet arra, hogy az Excel pivot tábla kizárólag adatelemzésre szolgál, információszerkesztési lehetőség nélkül. Közelebbi jelentéssel bírna a „pivot jelentés” (Pivot Report) kifejezés széles körben elterjedt használata, és így hívták ezt a felületet egészen 2000-ig. De valamilyen oknál fogva a későbbi verziókban a fejlesztők elhagyták.

Pivot táblák szerkesztése

Definíciója szerint az OLAP technológia elvileg nem jelenti a forrásadatok megváltoztatásának lehetőségét a jelentésekkel végzett munka során. Azonban egy egész osztály szoftverrendszerek, megvalósítva az adatok többdimenziós táblázatokban történő elemzésének és közvetlen szerkesztésének lehetőségét. Az ilyen rendszerek alapvetően a költségvetési problémák megoldására irányulnak.

Az Excel beépített automatizálási eszközeivel számos, nem szabványos feladatot is megoldhat. A munkalapadatokon alapuló Excel pivot táblák szerkesztésének példája megtalálható weboldalunkon.

Többdimenziós adatok előkészítése

Közelítsük meg a pivot táblák gyakorlati alkalmazását. Próbáljuk meg elemezni az értékesítési adatokat különböző irányú. Fájl pivotableexample.xls több lapból áll. Lap Példa alapvető információkat tartalmaz az értékesítésről egy bizonyos időszakra vonatkozóan. A példa egyszerűsége érdekében elemezzük az egyetlen számszerű mutatót - az eladások mennyiségét kg-ban. A következő kulcsfontosságú adatdimenziók állnak rendelkezésre: termék, vevő és szállító ( szállitó cég). Ezen kívül számos további adatdimenzió is létezik, amelyek a termék attribútumai: típus, márka, kategória, szállító, valamint vevő: típus. Ezeket az adatokat a Címtár lapon gyűjtjük össze. A gyakorlatban sokkal több ilyen mérés lehet.

Lap Példa tartalmaz standard jogorvoslat adatelemzés - autofilter. A táblázat kitöltési példáját tekintve nyilvánvaló, hogy a dátum szerinti értékesítési adatok (oszlopokba rendezve) alkalmasak a normál elemzésre. Ezenkívül az automatikus szűrő használatával megpróbálhatja összegezni az adatokat egy vagy több kulcskritérium kombinációi alapján. Márkákról, kategóriákról és típusokról egyáltalán nincs információ. Nem lehetséges az adatok csoportosítása automatikus összesítéssel egy adott kulcs szerint (például ügyfelek szerint). Ezenkívül a dátumok készlete rögzített, és megtekintheti az összefoglaló információkat egy bizonyos időszakra, például 3 napra, automatikus eszközökkel nem sikerül.

Általánosságban elmondható, hogy egy előre meghatározott dátum helye van ezt a példát- az asztal fő hátránya. A dátumok oszlopok szerinti rendezésével mintegy előre meghatároztuk ennek a táblának a dimenzióját, így megfosztottuk magunkat attól a lehetőségtől, hogy pivot táblákat használjunk.

Először is meg kell szabadulnunk ettől a hiányosságtól – pl. távolítsa el a forrásadatok egyik dimenziójának előre meghatározott helyét. Érvényes táblázat például egy munkalap Értékesítés.

A táblázat információbeviteli napló formájában készült. Itt a dátum egyenlő adatdimenziót jelent. Azt is meg kell jegyezni, hogy a pivot táblákban történő későbbi elemzéshez a sorok egymáshoz viszonyított relatív helyzete (más szóval a rendezés) teljesen közömbös. A relációs adatbázisokban lévő rekordok rendelkeznek ezekkel a tulajdonságokkal. A nagy mennyiségű adatbázis elemzése elsősorban a pivot táblák felületére összpontosít. Ezért ezeket a szabályokat be kell tartania, ha cellatartományok formájában lévő adatforrással dolgozik. Ugyanakkor senki sem tiltja, hogy az Excel felület eszközeit használja a munkája során – a pivot táblák csak adatokat elemeznek, a formázás, a szűrők, a forráscellák csoportosítása és rendezése tetszőleges lehet.

Az automatikus szűrőtől az összefoglaló jelentésig

Elméletileg az Értékesítési lap adatain már három dimenzióban lehet elemezni: áruk, vásárlók és fuvarozók. A termékek és vásárlók tulajdonságaira vonatkozóan ezen a lapon nem szerepel adat, ami ennek megfelelően nem teszi lehetővé azok megjelenítését az összefoglaló táblázatban. Az eredeti pivot tábla létrehozásának normál módjában excel adatok nem teszi lehetővé több tábla adatainak összekapcsolását bizonyos mezőkben. Megkerülheti ezt a korlátozást szoftver eszközök- tekintse meg weboldalunkon a cikk kiegészítését. Annak érdekében, hogy ne folyamodjanak szoftveres módszerek információfeldolgozás (főleg, hogy nem univerzálisak), hozzá kell tenni további jellemzők közvetlenül a naplóbejegyzési űrlapra – lásd a SalesAnalysis lapot.

A VLOOKUP függvények használata megkönnyíti az eredeti adatok hiányzó jellemzőkkel való kiegészítését. Most az automatikus szűrő alkalmazásával elemezheti az adatokat különböző dimenziók. De a csoportosítás problémája továbbra is megoldatlan. Például elég problémás az összeg nyomon követése csak a márkák esetében bizonyos dátumokon. Ha korlátozott Excel képletek, akkor további mintákat kell készítenie a SUMIF függvény segítségével.

Most pedig nézzük meg, milyen funkciókat kínál a pivot table felület. A lapon CodeAnalysis több jelentést készített egy sor cella alapján lapadatokkal Értékesítési elemzés.

Az első elemző tábla az Excel 2007 felületén keresztül épül fel Szalag \ Beszúrás \ Kimutatás(Excel 2000-2003 menüben Data\PivotTable).

A második és harmadik tábla másolással és későbbi testreszabással jön létre. Az összes tábla adatforrása ugyanaz. Ezt az eredeti adatok megváltoztatásával ellenőrizheti, majd frissítenie kell az összesítő jelentések adatait.

A mi szempontunkból az információ láthatóságának előnyei nyilvánvalóak. Cserélhet szűrőket, oszlopokat és sorokat, elrejthet bizonyos értékcsoportokat bármely dimenzióból, használhat kézi húzást és automatikus rendezést.

Tulajdonságok és formázás

Az adatok közvetlen megjelenítése mellett számos lehetőség áll rendelkezésre a pivot táblák megjelenésének megjelenítésére. A további adatok elrejthetők szűrők segítségével. Egyetlen elemhez vagy mezőhöz egyszerűbb a helyi menüelem használata Töröl(2000-2003-as verzióban Elrejt).

A pivot tábla többi elemének megjelenítését is kívánatos nem cellaformázással, hanem a pivot tábla mezőjének vagy elemének beállításával beállítani. Ehhez vigye az egérmutatót a kívánt elemre, várja meg egy speciális kurzor alakzat megjelenését (nyíl formájában), majd egyetlen kattintással jelölje ki a kiválasztott elemet. A kijelölés után módosíthatja a nézetet a szalagon, a helyi menün keresztül, vagy meghívhatja a szabványos cellaformátum párbeszédpanelt:

Ezenkívül az Excel 2007 számos előre definiált kimutatási stílust vezetett be:

Figyelje meg, hogy a vezérlőszűrők és a húzási területek aktívak a diagramon.

Hozzáférés a külső adatokhoz

Amint már említettük, a pivot táblák használatából adódóan a legnagyobb hatást az adatok elérésekor lehet elérni külső források– OLAP-kockák és adatbázis-lekérdezések. Az ilyen források általában nagy mennyiségű információt tárolnak, és előre definiált relációs szerkezettel rendelkeznek, ami megkönnyíti a többdimenziós adatok dimenzióinak meghatározását (pivot tábla mezők).

Az Excel számos típusú külső adatforrást támogat:

A külső információforrások felhasználásából a legnagyobb hatást automatizálási eszközök alkalmazásával érhetjük el ( VBA programok) mind az adatgyűjtéshez, mind pedig azok pivot táblákban történő előfeldolgozásához.