Olap kocky v exceli. Publikácie Praktická práca o technológii olap v exceli

Prvé rozhranie kontingenčnej tabuľky, nazývané aj kontingenčné zostavy, bolo súčasťou Excelu už v roku 1993 (Excel verzia 5.0). Napriek mnohým užitočným funkčnosť, v práci väčšiny používateľov Excelu sa prakticky nepoužíva. Dokonca aj skúsení používatelia často myslia pod pojmom „pivot report“ niečo vytvorené pomocou zložité vzorce. Skúsme spopularizovať používanie kontingenčných tabuliek v každodennej práci ekonómov. Článok pojednáva teoretický základ vytváranie súhrnných správ praktické rady o ich použití a tiež uvádza príklad prístupu k údajom na základe niekoľkých tabuliek.

Podmienky viacrozmernej analýzy údajov

Väčšina ekonómov už počula pojmy „viacrozmerné dáta“, „virtuálna kocka“, „technológie OLAP“ atď. Ale pri podrobnom rozhovore sa zvyčajne ukáže, že takmer každý nerozumie tomu, čo je v stávke. To znamená, že ľudia znamenajú niečo zložité a zvyčajne to nie je relevantné pre ich každodenné činnosti. V skutočnosti nie je.

Viacrozmerné údaje, rozmery

Dá sa s istotou povedať, že ekonómovia sa takmer neustále zaoberajú multidimenzionálnymi údajmi, no snažia sa ich prezentovať vopred definovaným spôsobom pomocou tabuliek. Multidimenzionálnosť tu znamená schopnosť zadávať, prezerať alebo analyzovať rovnaké informácie so zmenou vzhľad aplikovaním rôznych zoskupení a triedení údajov. Napríklad plán predaja možno analyzovať podľa nasledujúcich kritérií:

druhy alebo skupiny tovaru;
značky alebo kategórie produktov;
obdobia (mesiac, štvrťrok, rok);
kupujúci alebo skupiny kupujúcich;
predajné regióny
a tak ďalej.

Každé z vyššie uvedených kritérií z hľadiska analýzy viacrozmerných údajov sa nazýva "dimenzia". Dá sa povedať, že dimenzia charakterizuje informáciu o určitom súbore hodnôt. Špeciálnym typom merania viacrozmernej informácie sú „údaje“. V našom príklade môžu byť údaje plánu predaja:

objem predaja;
Predajná cena;
individuálna zľava
a tak ďalej.

Teoreticky môžu byť údaje aj štandardnou dimenziou viacrozmerných informácií (dáta môžete napríklad zoskupiť podľa predajnej ceny), ale údaje sú zvyčajne stále špeciálny typ hodnoty.

Môžeme teda povedať, že v praktickej práci ekonómovia používajú dva typy informácií: viacrozmerné údaje ( skutočné a plánované čísla s mnohými funkciami) a adresáre (charakteristiky alebo merania údajov).

OLAP

Skratka OLAP (online analytické spracovanie) v doslovnom preklade znie ako „analytické spracovanie v reálnom čase“. Definícia nie je veľmi konkrétna, dá sa pod ňu zhrnúť takmer každá správa akéhokoľvek softvérového produktu. Podľa definície OLAP znamená technológiu na prácu so špeciálnymi správami vrátane softvéru na získavanie a analýzu viacrozmerných štruktúrovaných údajov. Jedným z populárnych softvérových produktov, ktoré implementujú technológie OLAP, je SQL Server Analytický server. Niektorí ho dokonca mylne považujú za jediného predstaviteľa softvérovej implementácie tohto konceptu.

Virtuálna dátová kocka

„Virtuálna kocka“ (multidimenzionálna kocka, kocka OLAP) je technický termín, ktorý vytvorili niektorí predajcovia špecializovaných softvér. Systémy OLAP zvyčajne pripravujú a ukladajú údaje vo svojich vlastných štruktúrach a špeciálnych analytických rozhraniach (napríklad súhrnné Prehľady Excel) získať prístup k údajom týchto virtuálnych kociek. Zároveň použitie takéhoto vyhradeného úložiska nie je vôbec potrebné na spracovanie viacrozmerných informácií. Všeobecne, virtuálna kocka- ide o pole špeciálne optimalizovaných viacrozmerných údajov, ktoré sa používajú na vytváranie súhrnných správ. Dá sa získať prostredníctvom špecializovaných softvérových nástrojov, ako aj jednoduchým prístupom k databázovým tabuľkám alebo akémukoľvek inému zdroju, ako je napríklad tabuľkový procesor Excel.

kontingenčnej tabuľky

"Pivot Report" (kontingenčná tabuľka, kontingenčná tabuľka) je používateľské rozhranie na zobrazenie viacrozmerných údajov. Pomocou tohto rozhrania môžete zoskupovať, triediť, filtrovať a meniť umiestnenie údajov, aby ste získali rôzne analytické vzorky. Prehľad sa aktualizuje pomocou jednoduchých nástrojov používateľského rozhrania, údaje sa automaticky agregujú podľa špecifikovaných pravidiel a nie sú potrebné žiadne ďalšie alebo opätovné zadávanie akýchkoľvek informácií. Rozhranie kontingenčnej tabuľky Excel je snáď najobľúbenejším softvérovým produktom na prácu s viacrozmernými údajmi. Podporuje externé zdroje údajov (kocky OLAP a relačné databázy) a interné rozsahy tabuliek ako zdroj údajov. Počnúc verziou 2000 (9.0) Excel podporuje aj grafickú formu zobrazenia viacrozmerných údajov – kontingenčný graf.

Rozhranie kontingenčnej tabuľky implementované v Exceli umožňuje usporiadať rozmery viacrozmerných údajov v oblasti pracovného hárka. Pre jednoduchosť si kontingenčnú tabuľku môžete predstaviť ako zostavu, ktorá leží nad rozsahom buniek (v skutočnosti existuje určitá väzba formátov buniek na polia kontingenčnej tabuľky). Excelová kontingenčná tabuľka má štyri oblasti zobrazenia: filter, stĺpce, riadky a údaje. Dimenzie údajov sú pomenované polia kontingenčnej tabuľky. Tieto polia majú svoje vlastné vlastnosti a formát zobrazenia.

Ešte raz by som chcel upozorniť na skutočnosť, že kontingenčná tabuľka Excel je určená výlučne na analýzu údajov bez možnosti úpravy informácií. Významovo bližšie by bolo rozšírené používanie výrazu „pivot report“ (Pivot Report), a tak sa toto rozhranie až do roku 2000 nazývalo. Z nejakého dôvodu to však vývojári v nasledujúcich verziách opustili.

Úprava kontingenčných tabuliek

Technológia OLAP vo svojej definícii v zásade neznamená možnosť zmeny zdrojových údajov pri práci so zostavami. Avšak, celá trieda softvérové systémy, uvedomujúc si možnosti analýzy aj priamej editácie údajov vo viacrozmerných tabuľkách. V zásade sú takéto systémy zamerané na riešenie problémov s rozpočtovaním.

Pomocou vstavaných nástrojov Automatizácia Excelu, môžete vyriešiť veľa neštandardných problémov. Príklad implementácie úprav pre kontingenčné tabuľky programu Excel na základe údajov pracovného hárka nájdete na našej webovej stránke.

Príprava viacrozmerných údajov

Poďme do praktické uplatnenie kontingenčné tabuľky. Pokúsme sa analyzovať údaje o predaji rôznymi smermi. Súbor príklad kontingenčnej tabuľky.xls pozostáva z niekoľkých listov. List Príklad obsahuje základné informácie o predaji za určité obdobie. Pre jednoduchosť príkladu rozoberieme jediný číselný ukazovateľ - objem predaja v kg. K dispozícii sú nasledujúce kľúčové dimenzie údajov: produkt, kupujúci a prepravca (dopravná spoločnosť). Okrem toho existuje niekoľko ďalších dimenzií údajov, ktoré sú atribútmi produktu: typ, značka, kategória, dodávateľ, ako aj kupujúci: typ. Tieto údaje sa zhromažďujú na hárku Adresáre. V praxi môže byť takýchto meraní oveľa viac.

List Príklad obsahuje štandardná náprava analýza dát - automatický filter. Pri pohľade na príklad vypĺňania tabuľky je zrejmé, že údaje o predaji podľa dátumov (sú usporiadané v stĺpcoch) sa hodia na normálnu analýzu. Okrem toho sa pomocou automatického filtra môžete pokúsiť zhrnúť údaje podľa kombinácií jedného alebo viacerých kľúčových kritérií. Neexistujú absolútne žiadne informácie o značkách, kategóriách a typoch. Nie je možné zoskupovať údaje s automatickou sumarizáciou podľa konkrétneho kľúča (napríklad podľa zákazníkov). Okrem toho je súbor dátumov pevný a nebude možné automaticky zobraziť súhrnné informácie za určité obdobie, napríklad 3 dni.

Vo všeobecnosti je prítomnosť preddefinovaného dátumu v tomto príklade hlavnou nevýhodou tabuľky. Usporiadaním dátumov do stĺpcov sme tak trochu predurčili rozmer tejto tabuľky, čím sme sa pripravili o možnosť použiť analýzu pomocou kontingenčných tabuliek.

Najprv sa musíme zbaviť tohto nedostatku – t.j. odstráňte preddefinované umiestnenie jednej z dimenzií zdrojových údajov. Príkladom platnej tabuľky je hárok Predaj.

Tabuľka má formu denníka zadávania informácií. Tu je dátum rovnocennou dátovou dimenziou. Treba tiež poznamenať, že pre následnú analýzu v kontingenčných tabuľkách je vzájomná relatívna pozícia riadkov (inými slovami triedenie) úplne indiferentná. Záznamy v relačných databázach majú tieto vlastnosti. Práve analýza veľkých objemov databáz je primárne zameraná na rozhranie kontingenčných tabuliek. Preto pri práci so zdrojom údajov vo forme rozsahov buniek musíte dodržiavať tieto pravidlá. Zároveň nikto nezakazuje používať nástroje rozhrania Excel v práci - kontingenčné tabuľky analyzovať iba údaje, pričom formátovanie, filtre, zoskupovanie a triedenie zdrojových buniek môže byť ľubovoľné.

Od automatického filtra po súhrnný prehľad

Teoreticky je už na údajoch Predajného listu možné analyzovať v troch dimenziách: tovar, kupujúci a dopravcovia. Na tomto hárku nie sú žiadne údaje o vlastnostiach produktov a kupujúcich, a preto ich nemožno zobraziť v súhrnnej tabuľke. V normálnom režime vytvárania kontingenčnej tabuľky pre zdrojové údaje Excel neumožňuje prepojiť údaje z viacerých tabuliek podľa určitých polí. Toto obmedzenie môžete obísť softvérové nástroje- pozrite si príklad - dodatok k tomuto článku na našej webovej stránke. Aby ste sa neuchýlili k programovým metódam spracovania informácií (najmä preto, že nie sú univerzálne), mali by ste pridať ďalšie charakteristiky priamo do formulára na zadanie denníka - pozri list Analýza predaja.

Použitie funkcií VLOOKUP uľahčuje doplnenie pôvodných údajov o chýbajúce charakteristiky. Teraz pomocou automatického filtra môžete analyzovať údaje v rôzne rozmery. Ale problém zoskupení zostáva nevyriešený. Napríklad sledovanie sumy len pri značkách v určitých dátumoch je dosť problematické. Ak ste obmedzený na vzorce programu Excel, musíte vytvoriť ďalšie vzorky pomocou funkcie SUMIF.

Teraz sa pozrime, aké funkcie poskytuje rozhranie kontingenčnej tabuľky. Na liste CodeAnalysis vytvoril niekoľko správ založených na rozsahu buniek s údajmi hárka Analýza predaja.

Prvá analytická tabuľka je vytvorená pomocou rozhrania Excel 2007 Pás s nástrojmi \ Vložiť \ Kontingenčná tabuľka(v ponuke Excel 2000-2003 Údaje\Kontingenčná tabuľka).

Druhá a tretia tabuľka sú vytvorené kopírovaním a následným prispôsobením. Zdroj údajov pre všetky tabuľky je rovnaký. Môžete to skontrolovať zmenou pôvodných údajov, potom je potrebné aktualizovať údaje súhrnných výkazov.

Výhody vo viditeľnosti informácií sú z nášho pohľadu zrejmé. Môžete zamieňať filtre, stĺpce a riadky, skryť určité skupiny hodnôt z ľubovoľnej dimenzie, používať manuálne presúvanie a automatické triedenie.

Vlastnosti a formátovanie

Okrem priameho zobrazenia údajov existuje veľká sada možností zobrazenia vzhľadu kontingenčných tabuliek. Ďalšie údaje je možné skryť pomocou filtrov. Pre jeden prvok alebo pole je jednoduchšie použiť položku kontextového menu Odstrániť(vo verzii 2000-2003 Skryť).

Je tiež žiaduce nastaviť zobrazenie ostatných prvkov kontingenčnej tabuľky nie prostredníctvom formátovania buniek, ale nastavením poľa alebo prvku kontingenčnej tabuľky. Ak to chcete urobiť, presuňte ukazovateľ myši na požadovaný prvok, počkajte, kým sa objaví špeciálny tvar kurzora (vo forme šípky), a potom vyberte vybraný prvok jediným kliknutím. Po výbere môžete zmeniť zobrazenie cez pás s nástrojmi, kontextovú ponuku alebo vyvolať dialógové okno štandardného formátu bunky:

Okrem toho Excel 2007 zaviedol mnoho preddefinovaných štýlov zobrazenia kontingenčnej tabuľky:

Všimnite si, že ovládacie filtre a oblasti ťahania sú v grafe aktívne.

Prístup k externým údajom

Ako už bolo uvedené, možno najväčší efekt z použitia kontingenčných tabuliek možno dosiahnuť pri prístupe k údajom z externých zdrojov – OLAP kociek a databázových dotazov. Takéto zdroje zvyčajne uchovávajú veľké množstvo informácií a majú tiež preddefinovanú relačná štruktúru, ktorá uľahčuje definovanie rozmerov viacrozmerných údajov (polia kontingenčnej tabuľky).

Excel podporuje mnoho typov externých zdrojov údajov:

Najväčší efekt z využívania externých zdrojov informácií je možné dosiahnuť použitím automatizačných nástrojov (VBA programov) ako na získavanie údajov, tak aj na ich predbežné spracovanie v kontingenčných tabuľkách.

V štandardnej kontingenčnej tabuľke sú zdrojové údaje uložené na lokálnom pevnom disku. Týmto spôsobom ich môžete vždy spravovať a reorganizovať, aj keď nemáte prístup k sieti. To však v žiadnom prípade neplatí pre kontingenčné tabuľky OLAP. V kontingenčných tabuľkách OLAP sa vyrovnávacia pamäť nikdy neukladá na lokálny pevný disk. Preto ihneď po odpojení od lokálna sieť vaša kontingenčná tabuľka zlyhá. Žiadne z polí v ňom nebudete môcť presunúť.

Ak po prechode do režimu offline stále potrebujete analyzovať údaje OLAP, vytvorte kocku údajov offline. Offline dátová kocka je samostatný súbor, čo je vyrovnávacia pamäť kontingenčnej tabuľky, ktorá ukladá údaje OLAP, ktoré sa zobrazujú po odpojení od lokálnej siete. Údaje OLAP skopírované do kontingenčnej tabuľky je možné vytlačiť, podrobne to popisuje stránka http://everest.ua.

Ak chcete vytvoriť samostatnú dátovú kocku, najprv vytvorte kontingenčnú tabuľku OLAP. Umiestnite kurzor do kontingenčnej tabuľky a kliknite na tlačidlo Nástroje OLAP na kontextovej karte Nástroje, ktorá je súčasťou skupiny kontextových kariet Nástroje kontingenčnej tabuľky. Vyberte príkaz Offline OLAP (obr. 9.8).

Ryža. 9.8. Vytvorte offline dátovú kocku

Zobrazí sa dialógové okno Offline nastavenia dátovej kocky OLAP. Kliknite na tlačidlo Vytvoriť súbor údajov offline. Spustili ste Sprievodcu vytvorením súboru dátovej kocky. Ak chcete pokračovať v postupe, kliknite na tlačidlo Ďalej.

Najprv musíte určiť rozmery a úrovne, ktoré budú zahrnuté v dátovej kocke. V dialógovom okne musíte vybrať údaje, ktoré budú importované z databázy OLAP. Cieľom je špecifikovať len tie rozmery, ktoré budú potrebné po odpojení počítača od lokálnej siete. Čím viac rozmerov zadáte, tým väčšia bude kocka offline údajov.

Kliknutím na tlačidlo Ďalej prejdete na ďalšie dialógové okno sprievodcu. Poskytuje vám možnosť špecifikovať členy alebo dátové prvky, ktoré nebudú zahrnuté v kocke. Konkrétne nebudete potrebovať mieru rozšírenej sumy internetového predaja, takže bude v zozname odškrtnutá. Zrušené začiarkavacie políčko znamená, že zadaná položka nebude importovaná a zaberie ďalšie miesto na lokálnom pevnom disku.

V poslednom kroku zadajte umiestnenie a názov dátovej kocky. V našom prípade bude mať súbor kocky názov MyOfflineCube.cub a bude sa nachádzať v priečinku Práca.

Súbory dátových kociek majú príponu .mláďa

Po chvíli Excel uloží offline dátovú kocku do určeného priečinka. Ak to chcete otestovať, dvakrát kliknite na súbor, čím sa automaticky vygeneruje excelový zošit, ktorý obsahuje kontingenčnú tabuľku priradenú k vybranej dátovej kocke. Po vytvorení môžete offline dátovú kocku distribuovať všetkým zainteresovaným používateľom, ktorí pracujú v režime offline LAN.

Po pripojení k lokálnej sieti môžete otvoriť súbor offline dátovej kocky a aktualizovať ho, ako aj príslušnú dátovú tabuľku. Hlavným princípom je, že offline dátová kocka slúži len na prácu pri odpojení lokálnej siete, no po obnovení spojenia je povinné ju aktualizovať. Pokus o aktualizáciu offline dátovej kocky po prerušení pripojenia zlyhá.

Práca s offline súbormi kocky

Offline súbor kocky (.cub) ukladá údaje vo forme kocky OLAP (Online Analytical Processing). Tieto údaje môžu byť súčasťou databázy OLAP na serveri OLAP alebo môžu byť generované nezávisle od databázy OLAP. Pomocou offline súboru kocky môžete pokračovať v práci so zostavami kontingenčných tabuliek a kontingenčných grafov, keď je server nedostupný alebo keď ste offline.

Bezpečnostná poznámka: Buďte opatrní pri používaní alebo distribúcii offline súboru kocky, ktorý obsahuje citlivé alebo osobné informácie. Namiesto súboru kocky sa odporúča uložiť údaje do zošita, aby ste mohli riadiť prístup k údajom pomocou správy práv. Ďalšie informácie nájdete v téme Správa informačných práv v Office.

Pri práci s kontingenčnou tabuľkou alebo zostavou kontingenčného grafu, ktorá je založená na zdrojových údajoch servera OLAP, môžete použiť Sprievodcu offline kockou na skopírovanie zdrojových údajov do samostatného súboru offline kocky v počítači. Na vytvorenie týchto offline súborov je v počítači nainštalovaný poskytovateľ údajov OLAP, ktorý podporuje túto funkciu, napríklad MSOLAP od Microsoft SQL Server Analysis Services.

Poznámka: Vytváranie a používanie súborov kocky offline zo služby Microsoft SQL Server Analysis Services podlieha zmluvným podmienkam a licencovaniu inštalácie spoločnosti Microsoft SQL Server. Prečítajte si príslušné informácie o licenciách na vydanie SQL Server.

Práca so sprievodcom Offline Cube Wizard

Ak chcete vytvoriť súbor offline kocky, môžete vybrať podmnožinu údajov v databáze OLAP pomocou sprievodcu offline kockou a potom túto podmnožinu uložiť. Správa nemusí obsahovať všetky polia zahrnuté v súbore, ani nemusí vybrať žiadne z nich a dátové polia dostupné v databáze OLAP. Ak chcete, aby bol súbor minimálny, môžete zahrnúť iba údaje, ktoré sa majú zobraziť v prehľade. Môžete vynechať všetky dimenzie a pre väčšinu typov dimenzií môžete vylúčiť aj podrobnosti a členov nižšej úrovne. špičková úroveň ktoré nie je potrebné zobrazovať. Pre všetky prvky, ktoré zahrniete, sú polia vlastností dostupné v databáze pre tieto prvky uložené aj v offline súbore.

Prepnutie údajov do režimu offline a ich opätovné pripojenie

Ak to chcete urobiť, musíte najprv vytvoriť zostavu kontingenčnej tabuľky alebo kontingenčného grafu založenú na databáze servera a potom zo zostavy vytvoriť samostatný súbor kocky. Potom môžete kedykoľvek prepnúť zostavu medzi databázou servera a súborom offline. Napríklad, ak používate laptop pre domáce a video cestovanie a potom znova pripojte počítač k sieti.

Nižšie sú uvedené hlavné kroky, ktoré treba dodržať životnosť batérie s údajmi a potom ich preneste späť na internet.

Vytvorte alebo otvorte zostavu kontingenčnej tabuľky alebo kontingenčného grafu na základe údajov OLAP, ku ktorým chcete pristupovať offline.

Vytvorte offline súbor kocky v počítači. V kapitole Vytvorte offline súbor kocky z databázy servera OLAP(nižšie v tomto článku).

Odpojenie od siete a práca s offline súborom kocky.

Pripojte sa online a znova pripojte súbor kocky offline. Pozrite si sekciu Opätovné pripojenie súboru offline kocky k databáze servera OLAP(nižšie v tomto článku).

Aktualizujte súbor offline kocky o nové údaje a znova vytvorte súbor offline kocky. Pozrite si sekciu aktualizujte a znova vytvorte súbor offline kocky(nižšie v tomto článku).

BLOG

Len kvalitné príspevky

Čo sú kontingenčné tabuľky programu Excel a kocky OLAP

Pozrite si video k článku:

OLAP- toto je angličtina. online analytické spracovanie, analytická technológia spracovanie údajov v reálnom čase. v jednoduchom jazyku- úložisko s viacrozmernými údajmi (kocka), ešte jednoduchšie - len databáza, z ktorej môžete získať údaje v Exceli a analyzovať ich pomocou nástroja Excel - kontingenčné tabuľky.

Kontingenčné tabuľky je používateľské rozhranie na zobrazovanie viacrozmerných údajov. Inými slovami - špeciálny druh tabuliek, s ktorými môžete urobiť takmer akýkoľvek prehľad.

Aby to bolo jasné, porovnajme „Bežnú tabuľku“ s „Kontingenčnou tabuľkou“

Bežný stôl:

Kontingenčná tabuľka:

Hlavný rozdiel Kontingenčné tabuľky je prítomnosť okna Zoznam polí kontingenčnej tabuľky“, z ktorých si môžete vybrať požadované polia a automaticky získať akúkoľvek tabuľku!

Ako použiť

OTVORENÉ excelový súbor, ktorý je pripojený ku kocke OLAP, napríklad „BIWEB“:

Čo to teraz znamená a ako to používať?

Presuňte požadované polia, aby ste získali napríklad nasledujúcu tabuľku:

« Plusy» vám umožní prejsť do prehľadu. V tomto príklade je „Značka“ rozbalená na „Skrátené názvy“ a „Štvrťrok“ na „Mesiac“, t.j. Takže:

Analytické funkcie v Exceli (funkcie kocky)

Microsoft neustále pridáva do Excelu nové funkcie, pokiaľ ide o analýzu a vizualizáciu údajov. Práca s informáciami v Exceli môže byť reprezentovaná ako tri relatívne nezávislé vrstvy:

„správne“ usporiadané zdrojové údaje
matematika (logika) spracovania dát
reprezentácia údajov

Ryža. 1. Analýza údajov v Exceli: a) nespracované údaje, b) meranie c Power Pivot, c) prístrojová doska; pre zväčšenie obrázku kliknite naň kliknite pravým tlačidlom myši myšou a vyberte Otvoriť obrázok na novej karte

Stiahnite si poznámku vo formáte Word alebo pdf, príklady vo formáte Excel

Funkcie kocky a kontingenčné tabuľky

Najjednoduchším a zároveň veľmi výkonným prostriedkom na prezentáciu údajov sú kontingenčné tabuľky. Môžu byť zostavené z údajov obsiahnutých v: a) pracovnom hárku programu Excel, b) kocke OLAP alebo c) údajovom modeli Power Pivot. V posledných dvoch prípadoch môžete okrem kontingenčnej tabuľky použiť analytické funkcie (funkcie kocky) na vytvorenie zostavy na hárku Excel. Kontingenčné tabuľky sú jednoduchšie. Funkcie kocky sú zložitejšie, ale poskytujú väčšiu flexibilitu, najmä pri vytváraní prehľadov, takže sú široko používané v dashboardoch.

Nasledujúca diskusia sa týka vzorcov kocky a kontingenčných tabuliek založených na modeli Power Pivot a v niekoľkých prípadoch na kockách OLAP.

Jednoduchý spôsob, ako získať funkcie kociek

Keď (ak) ste sa začali učiť kód VBA, zistili ste, že najjednoduchší spôsob, ako získať kód, je zaznamenať makro. Ďalej je možné kód upravovať, pridávať slučky, kontroly atď.. Podobne najjednoduchší spôsob, ako získať sadu funkcií kocky, je konvertovať kontingenčnú tabuľku (obr. 2). Postavte sa na ľubovoľnú bunku kontingenčnej tabuľky a prejdite na kartu Analýza, kliknite na tlačidlo Vybavenie OLAP a stlačte Previesť na vzorce.

Ryža. 2. Konverzia kontingenčnej tabuľky na množinu funkcií kocky

Čísla sa uložia, pričom nepôjde o hodnoty, ale o vzorce, ktoré extrahujú údaje z dátového modelu Power Pivot (obr. 3). Výslednú tabuľku môžete naformátovať. V tabuľke môžete najmä vymazať a vložiť riadky a stĺpce. Výrez zostáva a ovplyvňuje údaje v tabuľke. Po aktualizácii pôvodných údajov sa aktualizujú aj čísla v tabuľke.

Ryža. 3. Tabuľka založená na vzorcoch kocky

Funkcia CUBEVALUE().

To je možno hlavná funkcia kociek. Je ekvivalentná oblasti hodnoty kontingenčnej tabuľky. CUBEVALUE načíta údaje z kocky alebo modelu Power Pivot a zobrazí ich mimo kontingenčnej tabuľky. To znamená, že nie ste limitovaní hranicami kontingenčnej tabuľky a môžete vytvárať zostavy s nespočetnými možnosťami.

Písanie vzorca od začiatku

Hotovú kontingenčnú tabuľku nemusíte konvertovať. Môžete napísať akýkoľvek vzorec kocky od začiatku. Do bunky C10 sa zapíše napríklad nasledujúci vzorec (obr. 4):

Ryža. 4. Funkcia CUBEVALUE() v bunke C10 vráti predaj bicyklov za všetky roky, ako v kontingenčnej tabuľke

Malý trik. Na uľahčenie čítania vzorcov kocky je žiaduce, aby bol na každý riadok umiestnený iba jeden argument. Okno programu Excel môžete zmenšiť. Ak to chcete urobiť, kliknite na ikonu Vráťte sa do okna nachádza v pravom hornom rohu obrazovky. A potom upravte veľkosť okna horizontálne. Alternatívna možnosť– vynútiť zalomenie textu vzorca Nový riadok. Ak to chcete urobiť, na riadku vzorcov umiestnite kurzor na miesto, kde chcete vykonať prevod, a stlačte Alt + Enter.

Ryža. 5. Minimalizujte okno

Syntax funkcie CUBEVALUE().

Pomocník Excelu je úplne presný a pre začiatočníkov úplne zbytočný:

CUBEVALUE(spojenie; [výraz_prvku1]; [výraz_prvku2]; ...)

Pripojenie je povinný argument; textový reťazec A, ktoré predstavuje názov spojenia s kockou.

element_expression– voliteľný argument; textový reťazec predstavujúci MDX, ktorý vracia prvok alebo n-ticu v kocke. Okrem toho "element_expression" môže byť množina definovaná pomocou funkcie CUBESET. Použite "member_expression" ako výsek na určenie časti kocky, pre ktorú chcete vrátiť súhrnnú hodnotu. Ak v element_expression nie je zadaná žiadna miera, použije sa predvolená miera pre danú kocku.

Predtým, ako pristúpime k vysvetleniu syntaxe funkcie CUBEVALUE, pár slov o kockách, dátových modeloch a kryptickom násobný.

Niektoré informácie o kockách OLAP a dátových modeloch Power Pivot

dátové kocky OLAP ( O n l ine A nalytický P processing – prevádzková analýza dát) boli vyvinuté špeciálne pre analytické spracovanie a rýchla extrakcia z nich údaje. Predstavte si trojrozmerný priestor, kde osami sú časové úseky, mestá a tovar (obr. 5a). Uzly takejto súradnicovej siete obsahujú hodnoty rôznych mier: objem predaja, zisk, náklady, počet predaných jednotiek atď. Teraz si predstavte, že existujú desiatky alebo dokonca stovky meraní ... a tiež veľa opatrení. Toto bude viacrozmerná kocka OLAP. Vytváranie, konfigurácia a udržiavanie aktuálnych informácií s kockami OLAP je záležitosťou IT profesionálov.

Ryža. 5a. 3D kocka OLAP

Analytické vzorce Excel (vzorce kocky) extrahujú názvy osí (napr. Čas), názvy prvkov na týchto osiach (august, september), hodnoty mier v priesečníku súradníc. Je to práve táto štruktúra, ktorá umožňuje kontingenčným tabuľkám a vzorcom kociek, aby boli také flexibilné a prispôsobili sa potrebám používateľov. Kontingenčné tabuľky založené na hárkoch programu Excel nepoužívajú miery, takže nie sú také flexibilné na účely analýzy údajov.

Power Pivot je relatívne nová funkcia spoločnosti Microsoft. Toto je vstavaný Excel a trochu nezávislé prostredie so známym rozhraním. Power Pivot je oveľa lepší ako štandardné kontingenčné tabuľky. Vývoj kociek v Power Pivot je zároveň pomerne jednoduchý a hlavne si nevyžaduje účasť IT špecialistu. Microsoft realizuje svoj slogan: „Obchodná analytika – pre masy!“. Hoci modely Power Pivot nie sú 100% kocky, možno ich tiež označiť ako kocky (ďalšie podrobnosti nájdete v úvodnom kurze Power Pivot od Marka Moora a v dlhšom vydaní Roba Colleyho, Vzorce Power Pivot DAX).

Hlavnými komponentmi kocky sú dimenzie, hierarchie, úrovne, prvky (alebo členy; v angličtine členy) a miery (miery). Meranie - hlavnou charakteristikou analyzovaných údajov. Napríklad kategória produktu, časové obdobie, geografia predaja. Dimenzia je niečo, čo môžeme umiestniť na jednu z osí kontingenčnej tabuľky. Každá dimenzia okrem jedinečných hodnôt obsahuje prvok, ktorý agreguje všetky prvky tejto dimenzie.

Merania sú založené na hierarchia. Napríklad kategóriu produktov možno rozdeliť na podkategórie, potom na modely a nakoniec na názvy produktov (obr. 5b) Hierarchia vám umožňuje vytvárať súhrnné údaje a analyzovať ich na rôznych úrovniach štruktúry. V našom príklade ide o hierarchiu Kategória zahŕňa 4 úrovni.

Prvky(jednotliví členovia) sú prítomní na všetkých úrovniach. Úroveň kategórie má napríklad štyri prvky: Príslušenstvo, Bicykle, Oblečenie, Komponenty. Ostatné úrovne majú svoje vlastné prvky.

Opatrenia sú vypočítané hodnoty, napríklad objem predaja. Miery v kockách sú uložené v ich vlastnej dimenzii, ktorá sa nazýva (pozri obrázok 9 nižšie). Opatrenia nemajú hierarchiu. Každá miera vypočíta a uloží hodnotu pre všetky dimenzie a všetky členy a rozdelí na základe toho, ktoré členy dimenzie umiestnime na os. Tiež hovoria, ktoré súradnice nastavíme, alebo aký kontext filtra nastavíme. Napríklad na obr. 5a v každej malej kocke sa vypočíta rovnaká miera - Zisk. A hodnota vrátená mierou závisí od súradníc. Vpravo na obrázku 5a je znázornené, že zisk (v troch súradniciach) pre Moskvu v októbri na jablkách = 63 000 rubľov. Mieru možno interpretovať ako jedno z meraní. Napríklad na obr. 5a namiesto osi Tovar, os miesta Opatrenia s prvkami Objem predaja, Zisk, Predané jednotky. Potom každá bunka bude mať nejakú hodnotu, napríklad Moskva, september, objem predaja.

Násobný- niekoľko prvkov rôznych rozmerov, špecifikujúcich súradnice pozdĺž osí kocky, v ktorých počítame mieru. Napríklad na obr. 5a Násobný= Moskva, október, jablká. Tiež platná n-tica je Perm, jablká. Ďalším sú jablká, august. Dimenzie, ktoré nie sú zahrnuté v n-tici, sú implicitne prítomné v n-tici a sú reprezentované predvoleným členom . Bunka vo viacrozmernom priestore je teda vždy definovaná úplným súborom súradníc, aj keď niektoré z nich sú z n-tice vynechané. Do n-tice nemôžete zahrnúť dva prvky rovnakej dimenzie, syntax to neumožňuje. Napríklad neplatná tuple Moskva a Perm, jablká. Na implementáciu takéhoto viacrozmerného výrazu potrebujete sadu dvoch n-tic: Moskva a jablká + Perm a jablká.

Sada prvkov– niekoľko prvkov rovnakého rozmeru. Napríklad jablká a hrušky. Sada n-tic- niekoľko n-tic, z ktorých každá pozostáva z rovnakých rozmerov v rovnakom poradí. Napríklad súbor dvoch n-tic: Moskva, jablká a Perm, banány.

Automatické dopĺňanie na záchranu

Vráťme sa k syntaxi funkcie CUBEVALUE. Použime automatické dopĺňanie. Začnite zadávať vzorec do bunky:

Excel navrhne všetky pripojenia dostupné v excelovom zošite:

Ryža. 6. Pripojenie k dátovému modelu Power Pivot sa vždy nazýva ThisWorkbookDataModel

Ryža. 7. Spoje na kocky

Pokračujme v zadávaní vzorca (v našom prípade pre dátový model):

Automatické dopĺňanie navrhne všetky dostupné tabuľky a miery dátového modelu:

Ryža. 8. Dostupné prvky prvej úrovne - názvy tabuliek a súbor opatrení (zvýraznené)

Vyberte ikonu Opatrenia. Ukázať na:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

Automatické dopĺňanie navrhne všetky dostupné opatrenia:

Ryža. 9. Dostupné prvky druhej úrovne v súbore opatrení

Vyberte mieru. Pridajte úvodzovky, zatvorte zátvorku a stlačte Enter.

CUBEVALUE(" ThisWorkbookDataModel " ; " . ")

Ryža. 10. Vzorec CUBEVALUE v bunke Excelu

Podobne môžete do vzorca pridať tretí argument:

VBA v Exceli Excel.Objekt kontingenčnej tabuľky a práca s kontingenčnými tabuľkami a kockami OLAP v Exceli

10.8 Práca s kontingenčnými tabuľkami (objekt kontingenčnej tabuľky)

objekt Excel.PivotTable, programová práca s kontingenčnými tabuľkami a kockami OLAP v Exceli pomocou VBA, objektu PivotCache, čím sa vytvorí rozloženie kontingenčnej tabuľky

Počas fungovania väčšiny podnikov sa hromadia takzvané nespracované údaje o činnostiach. Napríklad pre obchodný podnik je možné zhromažďovať údaje o predaji tovaru - pre každý nákup samostatne, pre podniky celulárna komunikácia- načítať štatistiky na základňové stanice a tak ďalej. Manažment podniku veľmi často potrebuje analytické informácie, ktoré sa generujú na základe nespracovaných informácií – napríklad na výpočet príspevku každého typu produktu k príjmu podniku alebo kvalite služieb v danej oblasti. stanica. Je veľmi ťažké extrahovať takéto informácie z nespracovaných informácií: musíte vykonávať veľmi zložité SQL dotazy, ktoré trvajú dlho a často narúšajú prebiehajúcu prácu. Preto sa v súčasnosti stále viac nespracovaných údajov zhromažďuje najskôr v dátovom sklade a potom v kockách OLAP, ktoré sú veľmi vhodné na interaktívnu analýzu. Najjednoduchší spôsob, ako si predstaviť kocky OLAP, sú viacrozmerné tabuľky, v ktorých namiesto štandardných dvoch rozmerov (stĺpcov a riadkov, ako v bežných tabuľkách) môže byť veľa rozmerov. Termín "sekčný" sa bežne používa na opis rozmerov v kocke. Napríklad marketingové oddelenie môže potrebovať informácie podľa času, regiónu, typu produktu, predajného kanála atď. Pomocou kociek (na rozdiel od štandardných SQL dotazov) je veľmi jednoduché získať odpovede na otázky typu „koľko produktov tohto typu sa predalo v štvrtom štvrťroku minulého roka v regióne Severozápad prostredníctvom regionálnych distribútorov.

Samozrejme, nemôžete vytvárať takéto kocky v bežných databázach. Kocky OLAP vyžadujú špecializované softvérové produkty. SQL Server sa dodáva s databázou OLAP od spoločnosti Microsoft s názvom Analysis Services. Existujú riešenia OLAP od spoločností Oracle, IBM, Sybase atď.

Na prácu s takýmito kockami je v Exceli zabudovaný špeciálny klient. V ruštine je to tzv kontingenčnej tabuľky(zapnuté grafická obrazovka je dostupná cez menu Údaje -> kontingenčnej tabuľky) a v angličtine - kontingenčnej tabuľky. Podľa toho sa objekt, ktorý tento klient predstavuje, nazýva kontingenčná tabuľka. Treba si uvedomiť, že dokáže pracovať nielen s OLAP kockami, ale aj s bežnými dátami v excelovských tabuľkách či databázach, no mnohé funkcie sa strácajú.

Kontingenčná tabuľka a objekt kontingenčnej tabuľky sú softvérové produkty od spoločnosti Panorama Software, ktoré získala spoločnosť Microsoft a sú integrované do Excelu. Preto sa práca s objektom kontingenčnej tabuľky trochu líši od práce s inými objektmi programu Excel. Zistiť, čo robiť, je často ťažké. Preto sa odporúča aktívne používať záznamník makier na prijímanie rád. Zároveň pri práci s kontingenčnými tabuľkami musia používatelia často vykonávať tie isté opakujúce sa operácie, takže automatizácia je v mnohých situáciách nevyhnutná.

Ako vyzerá programová práca s kontingenčnou tabuľkou?

Prvá vec, ktorú musíme urobiť, je vytvoriť objekt PivotCache, ktorý bude reprezentovať množinu záznamov získaných zo zdroja OLAP. Tento objekt PivotCache možno veľmi podmienečne porovnať s tabuľkou QueryTable. Na jeden objekt kontingenčnej tabuľky možno použiť iba jeden objekt kontingenčnej vyrovnávacej pamäte. Objekt PivotCache sa vytvára pomocou metódy Add() kolekcie PivotCache:

Dim PC1 ako PivotCache

Nastaviť PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches je štandardná kolekcia a z metód, ktoré si zaslúžia podrobné zváženie, v nej možno pomenovať iba metódu Add(). Táto metóda má dva parametre:

Typ zdroja- povinné, definuje typ zdroja údajov pre kontingenčnú tabuľku. Môžete si vybrať vytvorenie kontingenčnej tabuľky na základe rozsahu v Exceli, údajov z databázy, externého zdroja údajov, inej kontingenčnej tabuľky atď. V praxi má zvyčajne zmysel používať OLAP iba vtedy, keď existuje veľa údajov - podľa toho je potrebné špecializované externé úložisko (napríklad analytické služby Microsoft). V tejto situácii je vybratá možnosť xlExternal.
Zdrojové údaje- vyžaduje sa vo všetkých prípadoch okrem prípadov, keď je hodnota prvého parametra xlExternal. Presnejšie povedané, definuje rozsah údajov, na základe ktorých bude kontingenčná tabuľka vytvorená. Zvyčajne berie objekt Range.

Ďalšou úlohou je nakonfigurovať parametre objektu PivotCache. Ako už bolo spomenuté, tento objekt je veľmi podobný QueryTable a jeho súbor vlastností a metód je veľmi podobný. Niektoré z najdôležitejších vlastností a metód sú:

ADOConnection- schopnosť vrátiť objekt pripojenia ADO, ktorý sa automaticky vytvorí na pripojenie k externému zdroju údajov. Používa sa na dodatočné nastavenia vlastnosti pripojenia.
spojenie- funguje presne rovnako ako vlastnosť objektu QueryTable s rovnakým názvom. Môže akceptovať reťazec pripojenia, pripravený objekt Recordset, textový súbor, Webová žiadosť. súbor Microsoft dopyt. Najčastejšie sa pri práci s OLAP zapisuje reťazec pripojenia priamo (keďže nemá zmysel prijímať objekt Recordset, napríklad na zmenu údajov - zdroje údajov OLAP sú takmer vždy len na čítanie). Napríklad nastavenie tejto vlastnosti na pripojenie k databáze Foodmart (vzorová databáza analytických služieb) na serveri LONDÝN môže vyzerať takto:

PC1.Connection = "OLEDB;Poskytovateľ=MSOLAP.2;Zdroj údajov=LONDÝN1;Počiatočný katalóg = FoodMart 2000"

vlastnosti CommandType A text príkazu rovnakým spôsobom opíšte typ príkazu, ktorý sa odosiela na databázový server, a samotný text príkazu. Napríklad, ak chcete získať prístup ku kocke Predaj a získať ju úplne uloženú do vyrovnávacej pamäte na klientovi, môžete použiť kód ako

nehnuteľnosť LocalConnection umožňuje pripojiť sa k lokálnej kocke (súbor *.cub) vytvorenej programom Excel. Samozrejme, dôrazne sa neodporúča používať takéto súbory na prácu s „výrobnými“ objemami dát - iba na účely vytvárania rozložení atď.
nehnuteľnosť Použitá pamäť vráti číslo Náhodný vstup do pamäťe, ktorý používa PivotCache. Ak kontingenčná tabuľka založená na tejto kontingenčnej vyrovnávacej pamäti ešte nebola vytvorená a otvorená, vráti hodnotu 0. Dá sa použiť na kontrolu, či vaša aplikácia bude fungovať na slabých klientoch.
nehnuteľnosť OLAP vráti hodnotu True, ak je PivotCache pripojená k serveru OLAP.
OptimizeCache- schopnosť optimalizovať štruktúru vyrovnávacej pamäte. Počiatočné načítanie údajov bude trvať dlhšie, ale potom sa môže rýchlosť práce zvýšiť. Pre zdroje OLE DB nefunguje.

Zostávajúce vlastnosti objektu PivotCache sú rovnaké ako vlastnosti objektu QueryTable, a preto sa tu nebudeme rozoberať.

Hlavnou metódou objektu PivotCache je metóda CreatePivotTable(). Pomocou tejto metódy sa vykoná ďalšia fáza - vytvorenie kontingenčnej tabuľky (objekt kontingenčnej tabuľky). Táto metóda má štyri parametre:

TableDestination je jediný požadovaný parameter. Prijme objekt Range, v ktorého ľavom hornom rohu bude umiestnená kontingenčná tabuľka.
názov tabuľky- Názov kontingenčnej tabuľky. Ak nie je zadaný, názov formulára „Kontingenčná tabuľka1“ sa vygeneruje automaticky.
čítať dáta- ak je nastavené na hodnotu True, potom sa všetok obsah kocky automaticky uloží do vyrovnávacej pamäte. S týmto parametrom musíte byť veľmi opatrní, pretože jeho nesprávne použitie môže dramaticky zvýšiť zaťaženie klienta.
Predvolená verzia- Táto vlastnosť zvyčajne nie je špecifikovaná. Umožňuje určiť verziu kontingenčnej tabuľky, ktorá sa vytvára. Štandardne sa používa najnovšia verzia.

Vytvorenie kontingenčnej tabuľky v prvej bunke prvého hárka zošita môže vyzerať takto:

PC1.CreatePivotTable Range("A1")

Kontingenčná tabuľka bola vytvorená, ale hneď po vytvorení je prázdna. Poskytuje štyri oblasti, do ktorých môžete umiestniť polia zo zdroja (na grafickej obrazovke je možné toto všetko konfigurovať buď pomocou okna Zoznam polí kontingenčnej tabuľky- otvára sa automaticky alebo pomocou tlačidla Rozloženie na poslednej obrazovke sprievodcu kontingenčnou tabuľkou):

oblasť stĺpca- obsahuje tie dimenzie („časť“, v ktorej sa budú údaje analyzovať), ktorých členov je menej;
oblasť čiary- tie rozmery, ktorých členov je viac;
oblasť stránky- tie merania, podľa ktorých je potrebné iba filtrovať (napr. zobraziť údaje len za taký a taký región alebo len za taký a taký rok);
dátová oblasť- v skutočnosti centrálna časť stola. Tieto číselné údaje (napríklad výška predaja), ktoré analyzujeme.

Spoliehať sa na používateľa, že správne umiestni prvky do všetkých štyroch oblastí, je ťažké. Tiež to môže trvať určitý čas. Preto je často potrebné usporiadať údaje v kontingenčnej tabuľke programovo. Táto operácia sa vykonáva pomocou objektu CubeField. Hlavnou vlastnosťou tohto objektu je Orientácia, určuje, kde sa bude to alebo ono pole nachádzať. Dajme napríklad dimenziu Zákazníci do oblasti stĺpca:

PT1.CubeFields("").Orientation = xlColumnField

Potom - časová dimenzia do oblasti reťazcov:

PT1.CubeFields(""). Orientácia = xlRowField

Potom - dimenzia Produkt do oblasti stránky:

PT1.CubeFields(""). Orientácia = xlPageField

A nakoniec ukazovateľ (číselné údaje na analýzu) Jednotkový predaj:

PT1.CubeFields(".").Orientácia = xlDataField

Úžasná blízkosť...

V priebehu práce som často potreboval robiť zložité správy, stále som sa v nich snažil nájsť niečo spoločné, aby boli jednoduchšie a univerzálnejšie, dokonca som na túto tému napísal a publikoval článok „Osipov strom “. Kritizovali však môj článok a povedali, že všetky problémy, ktoré som uviedol, sú už dávno vyriešené v MOLAP.RU v.2.4 (www.molap.rgtu.ru) a odporučili pozrieť si kontingenčné tabuľky v EXCEL-e.
Ukázalo sa, že je to také jednoduché, že keď som k tomu pripojil svoje dômyselné malé ručičky, dostal som veľmi jednoduchý obvod na stiahnutie údajov z 1C7 alebo akejkoľvek inej databázy (ďalej 1C znamená akúkoľvek databázu) a analýzu v OLAP.
Myslím si, že mnohé schémy nahrávania OLAP sú príliš komplikované, volím jednoduchosť.

Charakteristika :

1. Na fungovanie je potrebný iba EXCEL 2000.
2. Užívateľ sám môže navrhovať zostavy bez programovania.
3. Nahrávanie z 1C7 vo formáte jednoduchého textového súboru.
4. Pre účtovné zápisy už existuje univerzálne spracovanie pre vykládku, ktoré funguje v akejkoľvek konfigurácii. Pre vyloženie iných údajov existuje vzorové spracovanie.
5. Formuláre zostáv môžete vopred navrhnúť a potom ich použiť na rôzne údaje bez toho, aby ste ich museli znova navrhovať.
6. Celkom dobrý výkon. V prvej dlhej fáze sa údaje najskôr importujú do EXCELu z textového súboru a vytvorí sa kocka OLAP a potom sa na základe tejto kocky dá pomerne rýchlo zostaviť ľubovoľná zostava. Napríklad údaje o predaji tovaru v predajni za 3 mesiace pri sortimente 6000 tovarov sa na Cel600-128M načítajú do EXCELu za 8 minút, hodnotenie podľa tovaru a skupín (OLAP report) sa prepočíta za 1 minútu.
7. Dáta sa stiahnu z 1C7 v plnom rozsahu za zadané obdobie (všetky pohyby, pre všetky sklady, firmy, účty). Pri importe do EXCELu je možné použiť filtre, ktoré načítajú len potrebné dáta na rozbor (napr. zo všetkých pohybov, len predajov).
8. V súčasnosti boli vyvinuté metódy na analýzu pohybov alebo rezíduí, nie však pohybov a rezíduí spolu, hoci je to v zásade možné.

Čo je OLAP : (www.molap.rgtu.ru)

Predpokladajme, že máte obchodnú sieť. Nechajte údaje o obchodných operáciách nahrať do textového súboru alebo tabuľky vo formulári:

Dátum – dátum transakcie
Mesiac – mesiac prevádzky
Týždeň – týždeň prevádzky
Druh - kúpa, predaj, vrátenie, odpis
Protistrana – externá organizácia podieľajúca sa na operácii
Autor - osoba, ktorá vystavila faktúru

Napríklad v 1C bude jeden riadok tejto tabuľky zodpovedať jednému riadku faktúry, niektoré polia (Dodávateľ, Dátum) sú prevzaté z hlavičky faktúry.

Dáta na analýzu sa zvyčajne nahrávajú do systému OLAP za určité časové obdobie, od ktorého sa v zásade dá pomocou filtrov zaťaženia odlíšiť ďalšie obdobie.

Táto tabuľka je zdrojom pre analýzu OLAP.

správa	merania	Údaje	Filter
Koľko tovaru a za akú sumu sa predá denne?	Dátum, produkt	Množstvo, Množstvo	View="predaj"
Ktoré protistrany dodali aký tovar za akú sumu mesačne?	Mesiac, Dodávateľ, Produkt	Sum	View="nákup"
Za akú sumu prevádzkovatelia faktúr akého typu vypisovali za celú dobu zostavy?		Sum

Používateľ sám určí, ktoré z polí tabuľky budú Dimenzie, ktoré Údaje a ktoré Filtre použiť. Systém sám vytvorí správu vo vizuálnej tabuľkovej forme. Dimenzie možno umiestniť do záhlavia riadkov alebo stĺpcov tabuľky prehľadu.
Ako vidíte, z jednej jednoduchej tabuľky môžete získať množstvo údajov vo forme rôznych prehľadov.

Ako používať samostatne :

Údaje z distribučného balíka rozbaľte presne do adresára c:\fixin (pre obchodný systém je možné c:\reports) . Prečítajte si readme.txt a postupujte podľa všetkých pokynov v ňom.

Najprv musíte napísať spracovanie, ktoré nahrá údaje z 1C do textového súboru (tabuľky). Musíte definovať zloženie polí, ktoré sa budú odovzdávať.
Napríklad hotové univerzálne spracovanie, ktoré funguje v akejkoľvek konfigurácii a uvoľňuje transakcie na určité obdobie pre analýzu OLAP, uvoľňuje nasledujúce polia na analýzu:

Tam, kde sa pod predponami Dt (Kt) nachádzajú subkonto Debet (Kredit), Skupina je skupina tohto subkonta (ak existuje), Sekcia je skupina skupiny, Trieda je skupina sekcie.

Pre obchodný systém môžu byť polia nasledovné:

Pre analýzu údajov slúžia tabuľky "Analýza pohybov.xls" ("Analýza účtovníctva.xls"). Pri ich otváraní nezakazujte makrá, inak nebudete môcť zostavy aktualizovať (spúšťajú ich makrá v jazyku VBA). Tieto súbory preberajú svoje počiatočné údaje zo súborov C:\fixin\motions.txt (C:\fixin\buh.txt), inak sú rovnaké. Preto možno budete musieť skopírovať svoje údaje do jedného z týchto súborov.
Aby sa vaše údaje načítali do EXCELu, vyberte alebo napíšte vlastný filter a kliknite na tlačidlo "Generovať" na hárku "Podmienky".
Hárok prehľadov začína predponou „Od“. Prejdite na hárok s prehľadom, kliknite na „Obnoviť“ a údaje prehľadu sa zmenia podľa najnovších načítaných údajov.
Ak nie ste spokojní so štandardnými zostavami, je tu list OtchTemplate. Skopírujte ho do nového hárka a prispôsobte zobrazenie zostavy prácou s kontingenčnou tabuľkou na tomto hárku (viac o práci s kontingenčnými tabuľkami – v ktorejkoľvek knihe o EXCEL 2000). Odporúčam nastaviť prehľady na malom súbore údajov a potom ich spustiť na veľkom poli, pretože neexistuje spôsob, ako zakázať prekresľovanie tabuľky pri každej zmene rozloženia zostavy.

Technické poznámky :

Pri nahrávaní údajov z 1C si používateľ vyberie priečinok, do ktorého sa má súbor nahrať. Urobil som to, pretože je pravdepodobné, že v blízkej budúcnosti bude nahraných niekoľko súborov (zvyšky a pohyby). Potom kliknutím na tlačidlo "Odoslať" --> "Do analýzy OLAP v EXCEL 2000" v Prieskumníkovi sa údaje skopírujú z vybratého priečinka do priečinka C:\fixin. (aby sa tento príkaz objavil v zozname príkazu "Odoslať", musíte skopírovať súbor "Na analýzu OLAP v EXCEL 2000.bat" do adresára C:\Windows\SendTo) Preto nahrajte údaje ihneď s uvedením názvov do súborov motions.txt alebo buh.txt.

Formát textového súboru:
Prvý riadok textového súboru obsahuje nadpisy stĺpcov oddelené „|“, ostatné riadky obsahujú hodnoty týchto stĺpcov oddelené „|“.

Na import textových súborov do Excelu slúži Microsoft Query (súčasť EXCELu), pre jeho fungovanie je potrebné mať v importovacom adresári (C:\fixin) súbor shema.ini s nasledujúcimi informáciami:

ColNameHeader=Pravda
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=Pravda
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI

Vysvetlenie: motions.txt a buh.txt je názov sekcie, zodpovedá názvu importovaného súboru, popisuje, ako importovať textový súbor do Excelu. Zvyšné parametre znamenajú, že prvý riadok obsahuje názvy stĺpcov, oddeľovač stĺpcov je "|", znaková sada je Windows ANSI (pre DOS - OEM).
Typ poľa sa určí automaticky na základe údajov obsiahnutých v stĺpci (dátum, číslo, reťazec).
Zoznam polí nie je potrebné nikde popisovať - EXCEL a OLAP si sami určia, ktoré polia sú v súbore obsiahnuté podľa nadpisov v prvom riadku.

Pozor, skontrolujte svoje miestne nastavenia "Ovládací panel" --> "Regionálne nastavenia". Pri mojom spracovaní sa čísla nahrávajú s oddeľovačom čiarok a dátumy sú vo formáte „DD.MM.RRRR“.

Keď kliknete na tlačidlo „Generovať“, údaje sa načítajú do kontingenčnej tabuľky na hárku „Základná“ a všetky zostavy na hárkoch „Návrat“ preberajú údaje z tejto kontingenčnej tabuľky.

Rozumiem, že milovníci MS SQL Server a výkonných databáz budú reptať, že je pre mňa všetko príliš zjednodušené, že moje spracovanie bude orientované na ročnú vzorku, ale v prvom rade chcem poskytnúť výhody OLAP analýzy stredne veľkým organizáciám. . Umiestnil by som tento produkt ako ročný analytický nástroj pre veľkoobchodníkov, štvrťročnú analýzu pre maloobchodníkov a prevádzkovú analýzu pre akúkoľvek organizáciu.

Musel som sa popasovať s VBA, aby sa údaje brali zo súboru s ľubovoľným zoznamom polí a bolo možné vopred pripraviť formuláre výkazov.

Popis práce v EXCEL (pre používateľov):

Pokyny na používanie prehľadov:
1. Pošlite stiahnuté údaje na analýzu (informujte sa u správcu). Ak to chcete urobiť, kliknite pravým tlačidlom myši na priečinok, do ktorého ste nahrali údaje z 1C, a vyberte príkaz „Odoslať“ a potom „Do analýzy OLAP v EXCEL 2000“.
2. Otvorte súbor „Motion Analysis.xls“.
3. Vyberte hodnotu Filter, potrebné filtre môžete pridať na karte „Hodnoty“.
4. Kliknite na tlačidlo "Generovať" a stiahnuté dáta sa načítajú do EXCELu.
5. Po načítaní údajov do EXCELu si môžete prezerať rôzne zostavy. Stačí kliknúť na tlačidlo „Obnoviť“ vo vybranom prehľade. Hárok s prehľadmi sa začína na Rep.
Pozor! Po zmene hodnoty filtra musíte znova kliknúť na tlačidlo „Generovať“, aby sa údaje v EXCEL-e znova načítali z nahrávaného súboru v súlade s filtrami.

Spracovanie z ukážky:

Spracovanie motionsbuh2011.ert je najnovšia verzia vykladania transakcií z účtovníctva 7.7 na analýzu v Exceli. Má začiarkavacie políčko „Pripojiť k súboru“, ktoré vám umožňuje nahrávať údaje po častiach podľa období, pripájať ich k rovnakému súboru a znova ich neodovzdávať do rovnakého súboru:

Spracovanie motionswork.ert nahráva údaje o predaji na analýzu v Exceli.

Príklady prehľadov:

Šach uverejnením:

Pracovná náplň operátorov podľa typov faktúr:

P.S. :

Je zrejmé, že podľa podobnej schémy môžete organizovať vykladanie údajov z 1C8.
V roku 2011 ma kontaktoval užívateľ, ktorý potreboval dopracovať toto spracovanie v 1C7, aby mohol nahrať veľké množstvo dát, našiel som si outsourcera a túto prácu urobil. Vývoj je teda celkom relevantný.

Spracovanie Motionsbuh2011.ert bolo vylepšené, aby zvládlo nahrávanie veľkých dát.

Práca s kockou OLAP v MS Excel
1. Získajte povolenie na prístup ku kocke OLAP služby SQL Server Analysis Services (SSAS).
2. Na vašom počítači musí byť nainštalovaný MS Excel 2016 / 2013 / 2010 (možný je aj MS Excel 2007, ale práca v ňom nie je pohodlná a MS Excel 2003 má veľmi slabú funkčnosť)
3. Otvorte MS Excel, spustite sprievodcu nastavením pripojenia k analytickej službe:

3.1 Zadajte názov alebo IP adresu aktuálneho servera OLAP (niekedy je potrebné zadať číslo otvorený port, napríklad 192.25.25.102:80); Používa sa autentifikácia domény:

3.2 Vyberte multidimenzionálnu databázu a analytickú kocku (ak máte ku kocke prístupové práva):

3.3 Nastavenia pripojenia k analytickej službe sa uložia do súboru ODC na vašom počítači:

3.4 Vyberte typ zostavy (kontingenčná tabuľka/graf) a zadajte umiestnenie pre jej umiestnenie:

Ak už bolo spojenie v excelovom zošite vytvorené, potom ho možno znova použiť: hlavné menu "Údaje" -> "Existujúce pripojenia" -> vyberte pripojenie v tomto zošite -> vložte kontingenčnú tabuľku do zadanej bunky.
4. Po úspešnom pripojení ku kocke môžete spustiť interaktívnu analýzu údajov:

Pri spustení interaktívnej analýzy dát je potrebné určiť, ktoré z polí sa bude podieľať na tvorbe riadkov, stĺpcov a filtrov (stránok) kontingenčnej tabuľky. Vo všeobecnosti je kontingenčná tabuľka trojrozmerná a môžeme si predstaviť, že tretí rozmer je kolmý na obrazovku a vidíme sekcie, ktoré sú rovnobežné s rovinou obrazovky a určujú, ktorá "stránka" sa vyberie. na zobrazenie. Filtrovanie je možné vykonať presunutím príslušných atribútov dimenzie do oblasti filtrov prehľadu. Filtrovanie obmedzuje priestor kocky, znižuje zaťaženie OLAP servera, tzv je lepšie najskôr nainštalovať potrebné filtre. Potom umiestnite atribúty dimenzie do oblastí riadkov a stĺpcov a miery do oblasti údajov kontingenčnej tabuľky.

Zakaždým, keď sa kontingenčná tabuľka zmení, na server OLAP sa automaticky odošle príkaz MDX a po vykonaní sa vrátia údaje. Čím väčší a komplexnejší je objem spracovávaných údajov, vypočítané ukazovatele, tým dlhší je čas vykonania dotazu. Vykonanie požiadavky môžete zrušiť stlačením klávesu uniknúť. Posledné vykonané operácie je možné vrátiť späť (Ctrl+Z) alebo zopakovať (Ctrl+Y).

Pri najčastejšie používaných kombináciách atribútov dimenzií sa v kocke spravidla ukladajú vopred vypočítané agregované údaje, takže doba odozvy takýchto dopytov je niekoľko sekúnd. Nie je však možné vypočítať všetky možné kombinácie agregácií, pretože to môže vyžadovať veľa času a úložného priestoru. Vykonávanie rozsiahlych dopytov na údaje na úrovni detailov si môže vyžadovať značné výpočtové zdroje servera, takže čas ich vykonávania môže byť dlhý. Po načítaní údajov z diskových jednotiek ich server umiestni do vyrovnávacej pamäte RAM, čo umožňuje okamžité vykonanie ďalších takýchto požiadaviek, pretože údaje budú načítané z vyrovnávacej pamäte.

Ak si myslíte, že vaša požiadavka bude často využívaná a jej čas realizácie je neuspokojivý, môžete sa obrátiť na službu podpory analytického vývoja, aby optimalizovala realizáciu požiadavky.

Po umiestnení hierarchie do oblasti riadkov/stĺpcov je možné skryť jednotlivé úrovne:

Pre kľúčové atribúty(menej často – pre atribúty vyššie v hierarchii) dimenzie môžu mať vlastnosti – popisné charakteristiky, ktoré možno zobraziť v popisoch nástrojov aj ako polia:

Ak chcete zobraziť niekoľko vlastností poľa naraz, môžete použiť príslušný dialógový zoznam:

Používateľom definované sady

Excel 2010 predstavuje možnosť interaktívne vytvárať vlastné (používateľom definované) množiny z členov dimenzie:

Na rozdiel od sád vytvorených a uložených centrálne na strane kocky sú vlastné sady uložené lokálne v excelovom zošite a možno ich použiť neskôr:

Pokročilí používatelia môžu vytvárať sady pomocou konštrukcií MDX:

Nastavenie vlastností kontingenčnej tabuľky

Položka "Možnosti kontingenčnej tabuľky..." v kontextovej ponuke (kliknutie pravým tlačidlom myši v rámci kontingenčnej tabuľky) poskytuje možnosť prispôsobiť kontingenčnú tabuľku, napríklad:
- Záložka "Výstup", možnosť "Klasické rozloženie kontingenčnej tabuľky" - kontingenčná tabuľka sa stáva interaktívnou, polia je možné presúvať myšou (Drag&Drop);
- karta "Výstup", parameter "Zobraziť prvky bez údajov v riadkoch" - kontingenčná tabuľka zobrazí prázdne riadky, ktoré neobsahujú žiadne hodnoty indikátorov pre zodpovedajúce prvky dimenzie;
- karta "Rozloženie a formát", parameter "Pri aktualizácii zachovať formátovanie bunky" - v kontingenčnej tabuľke môžete pri aktualizácii údajov prepísať a uložiť formát bunky;

Vytváranie kontingenčných grafov

Pre existujúcu kontingenčnú tabuľku OLAP môžete vytvoriť kontingenčný graf – koláčový, stĺpcový, histogram, graf, bodový graf a ďalšie typy grafov:

V tomto prípade sa kontingenčný graf zosynchronizuje s kontingenčnou tabuľkou – pri zmene zloženia ukazovateľov, filtrov, dimenzií v kontingenčnej tabuľke sa aktualizuje aj kontingenčná tabuľka.

Vytváranie informačných panelov

Vyberte pôvodnú kontingenčnú tabuľku, skopírujte ju do schránky (Ctrl + C) a vložte jej kópiu (Ctrl + V), v ktorej zmeníme zloženie indikátorov:

Ak chcete spravovať viacero kontingenčných tabuliek súčasne, vložme slicer (nová funkcia dostupná od MS Excel 2010). Prepojme náš Slicer s kontingenčnými tabuľkami – kliknite pravým tlačidlom myši v rámci sliceru, vyberte v obsahové menu"Pripojenia k kontingenčnej tabuľke...". Je potrebné poznamenať, že môže existovať viacero panelov slicer, ktoré môžu súčasne zobrazovať kontingenčné tabuľky rôzne listy, ktorý umožňuje vytvárať koordinované dashboardy (Dashboard).

Panely Slicer je možné konfigurovať: vyberte panel a potom si pozrite „Veľkosť a vlastnosti...“, „Nastavenia rezu“, „Priradiť makro“ v kontextovej ponuke po kliknutí pravým tlačidlom myši alebo v položke „Možnosti“ hlavnej ponuky. Je teda možné nastaviť počet stĺpcov pre prvky (tlačidlá) krájača, veľkosti tlačidiel krájača a panelu, definovať farebnú schému a štýl dizajnu krájača z existujúcej sady (alebo si vytvoriť vlastný štýl), definujte vlastný názov panelu, priraďte programové makro, pomocou ktorého môžete rozšíriť funkčnosť panelu.

Vykonanie dotazu MDX z Excelu

V prvom rade je potrebné vykonať operáciu DRILLTHROUGH na nejakom ukazovateli, t.j. prejdite nadol na podrobné údaje (podrobné údaje sú zobrazené na samostatnom hárku) a otvorte zoznam pripojení;
Otvorte vlastnosti pripojenia, prejdite na kartu "Definícia";
Vyberte predvolený typ príkazu a do textového poľa príkazu umiestnite vopred pripravený MDXžiadosť;
Po stlačení tlačidla sa po skontrolovaní správnej syntaxe požiadavky a dostupnosti príslušných prístupových práv požiadavka na serveri vykoná a výsledok sa zobrazí v aktuálnom hárku vo forme bežnej plochej tabuľky.
Text dotazu MDX vygenerovaného Excelom môžete zobraziť nastavením bezplatný doplnok, ktorý poskytuje aj ďalšie doplnkové funkcie.

Preklad do iných jazykov

Analytická kocka podporuje lokalizáciu do ruštiny a anglický jazyk a (v prípade potreby je možná lokalizácia do iných jazykov). Preklady sa týkajú názvov dimenzií, hierarchií, atribútov, priečinkov, mier, ako aj prvkov jednotlivých hierarchií, ak pre ne existujú preklady na strane účtovných systémov / dátových úložísk. Ak chcete zmeniť jazyk, otvorte vlastnosti pripojenia a do reťazca pripojenia pridajte nasledujúcu možnosť:
Extended Properties="Locale=1033"
kde 1033 - anglická lokalizácia
1049 - ruská lokalizácia

Ďalšie rozšírenia Excel pre Microsoft OLAP

Schopnosť pracovať s kockami Microsoft OLAP sa zvýši, ak použijete dodatočné rozšírenia, napríklad OLAP PivotTable Extensions, vďaka ktorým môžete použiť rýchle vyhľadávanie podľa merania:

webovej stránky 2011-01-11 16:57:00Z Naposledy zmenené: 2017-10-15 16:33:59Z Vek publikum: 14-70