OLAP je jednoduchý! Praktická práca na technológii olap v exceli.

Analytical Analytical Processing (OLAP) je technológia, ktorá sa používa na organizáciu veľkých podnikových databáz a podporu business intelligence. Databázy OLAP sú rozdelené do jednej alebo viacerých kociek a každá kocka je usporiadaná správcom kocky tak, aby vyhovovala spôsobu získavania a analýzy údajov, aby sa uľahčilo vytváranie a používanie zostáv kontingenčných tabuliek a kontingenčných grafov, ktoré budete potrebovať.

V tomto článku

Čo je to business intelligence?

Obchodný analytik chce často získať väčší obraz o podniku, zobraziť širšie trendy založené na agregovaných údajoch a zobraziť trendy rozdelené do ľubovoľného počtu premenných. Business Intelligence je proces extrakcie údajov z databázy OLAP a analýzy týchto údajov s cieľom poskytnúť informácie, ktoré možno použiť na prijímanie informovaných obchodných rozhodnutí a prijímanie opatrení. Napríklad pomocou OLAP a business intelligence môžete odpovedať na nasledujúce otázky o obchodných údajoch.

Aký je celkový predaj všetkých produktov v roku 2007 v porovnaní s predajom od roku 2006?

Ako je to v porovnaní s dátumom a časom obdobia poskytovania dávok za posledných päť rokov?

Koľko peňazí minuli zákazníci na 35 minulý rok a ako sa toto správanie časom zmenilo?

Koľko produktov sa tento mesiac predalo v dvoch konkrétnych krajinách/regiónoch v porovnaní s rovnakým mesiacom minulého roka?

Pre každú vekovú skupinu zákazníkov Aké je rozdelenie ziskovosti (percentuálna aj celková marža) podľa kategórie produktov?

Vyhľadajte najlepších a najnižších predajcov, distribútorov, dodávateľov, zákazníkov, partnerov a zákazníkov.

Čo je online analytické spracovanie (OLAP)?

Databázy OLAP (Online Analytical Processing) zjednodušujú dotazy na business intelligence. OLAP je databázová technológia, ktorá je optimalizovaná pre dopyty a zostavovanie, nie pre spracovanie transakcií. Zdrojom údajov pre OLAP sú databázy spracovania online transakcií (OLTP), ktoré sú zvyčajne uložené v dátových skladoch. Údaje OLAP sa získavajú z týchto historických údajov a kombinujú sa do štruktúr, ktoré umožňujú komplexnú analýzu. Údaje OLAP sú tiež usporiadané hierarchicky a uložené v kockách a nie v tabuľkách. Ide o sofistikovanú technológiu, ktorá na poskytovanie využíva viacrozmerné štruktúry rýchly prístup k údajom na analýzu. V tejto organizácii môžete pre zostavu kontingenčnej tabuľky alebo zostavu kontingenčného grafu jednoducho zobraziť súhrnné údaje na vysokej úrovni, ako sú súhrnné údaje o predaji pre celú krajinu alebo región, a tiež zobraziť podrobnosti o lokalitách, kde je predaj obzvlášť silný alebo slabý.

OLAP databázy sú navrhnuté tak, aby urýchlili načítanie dát. Vzhľadom k tomu, OLAP server, nie Microsoft Office Excel vypočítava agregované hodnoty, pri vytváraní alebo úprave zostavy je potrebné do Excelu odosielať menej údajov. Tento prístup vám umožňuje pracovať s väčším množstvom zdrojových údajov, ako keby boli údaje usporiadané v tradičnej databáze, kde Excel získava všetky jednotlivé záznamy a vypočítava agregované hodnoty.

Databázy OLAP obsahujú dva hlavné typy údajov: miery, čo sú číselné údaje, množstvá a priemery, ktoré sa používajú na prijímanie informovaných obchodných rozhodnutí, a dimenzie, čo sú kategórie používané na organizáciu týchto meraní. Databázy OLAP vám pomôžu usporiadať údaje s mnohými úrovňami podrobností pomocou rovnakých kategórií, aké poznáte pre analýzu údajov.

Nasledujúce časti podrobne popisujú každý komponent nižšie.

KubickýŠtruktúra údajov, ktorá kombinuje miery naprieč úrovňami a hierarchiami každej dimenzie, ktorú chcete analyzovať. Kocky kombinujú viaceré dimenzie, ako je čas, geografia a produktové rady, so súhrnnými údajmi, ako sú tržby a zásoby. Kocky nie sú „kocky“ v prísnom matematickom zmysle, pretože nemusia mať nevyhnutne rovnaké strany. Predstavujú však trefnú metaforu zložitého konceptu.

merania Množina hodnôt v kocke, ktoré sú založené na stĺpci v tabuľke faktov kocky a ktorá je zvyčajne číselnou hodnotou. Miery sú centrálne hodnoty v kocke, ktoré sú vopred spracované, spracované a analyzované. Najbežnejšími príkladmi sú tržby, príjmy, príjmy a výdavky.

člen Prvok v hierarchii, ktorý predstavuje jeden alebo viac výskytov údajov. Prvok môže byť jedinečný alebo nejedinečný. Napríklad roky 2007 a 2008 predstavujú jedinečných členov na úrovni roka časovej dimenzie, zatiaľ čo január predstavuje nejedinečných členov na úrovni mesiaca, pretože v časovej dimenzii je viac ako jeden január, pretože obsahuje údaje za viac ako jeden rok.

Vypočítaný členČlen dimenzie, ktorej hodnota sa vyhodnocuje v čase spustenia pomocou výrazu. Hodnoty vypočítaných členov možno odvodiť z hodnôt ostatných členov. Napríklad vypočítanú položku „zisk“ možno určiť odpočítaním hodnoty položky, ako aj nákladov, od hodnoty položky, tržieb.

meranie Sada jednej alebo viacerých usporiadaných hierarchií na úrovni kocky, ktorým používateľ rozumie a používa ich ako základ pre analýzu údajov. Napríklad geografická dimenzia môže zahŕňať úroveň krajiny/regiónu, štátu/regiónu a mesta. Okrem toho môže časová dimenzia zahŕňať hierarchiu s úrovňami roka, štvrťroka, mesiaca a dňa. V zostave kontingenčnej tabuľky alebo zostave kontingenčného grafu sa každá hierarchia stáva množinou polí, ktoré možno rozbaliť a zbaliť, aby sa zobrazovali nižšie alebo vyššie. vysoké úrovne.

Hierarchia Logická stromová štruktúra, ktorá usporiada členov dimenzie tak, že každý člen má jedného rodiča a nula alebo viac potomkov. Podradený prvok je členom predchádzajúcej skupiny v hierarchii, ktorá priamo súvisí s aktuálnym členom. Napríklad v časovej hierarchii obsahujúcej úrovne štvrťroka, mesiaca a dňa je január potomkom Qtr1. Rodičovský prvok je člen nižšej úrovne v hierarchii, ktorý priamo súvisí s aktuálnym členom. Nadradená hodnota je zvyčajne konsolidáciou hodnôt všetkých podradených prvkov. Napríklad v časovej hierarchii obsahujúcej úrovne štvrťroka, mesiaca a dňa je Qtr1 rodičom januára.

úroveň V hierarchii môžu byť údaje usporiadané do nižších a vyšších úrovní podrobností, ako sú roky, štvrťroky, mesiace a dni v časovej hierarchii.

Funkcie OLAP v Exceli

Získavanie údajov OLAP K zdrojom údajov OLAP sa môžete pripojiť rovnakým spôsobom, ako sa môžete pripojiť k iným externým zdrojom údajov. Môžete pracovať s databázami vytvorenými pomocou služieb spoločnosti Microsoft SQL Server OLAP verzia 7.0, Microsoft SQL Server Analysis Services verzia 2000 a Microsoft SQL Server Analysis Services verzia 2005, serverové produkty Microsoft OLAP. Excel môže pracovať aj s produktmi OLAP tretích strán, ktoré sú kompatibilné s OLE-DB pre OLAP.

Údaje OLAP možno zobraziť iba ako zostavu kontingenčnej tabuľky alebo kontingenčného grafu alebo vo funkcii hárka konvertovanej zo zostavy kontingenčnej tabuľky, nie ako externý rozsah údajov. Zostavy kontingenčných tabuliek OLAP a kontingenčných grafov môžete ukladať do šablón zostáv a môžete vytvárať súbory Office Data Connection (ODC) na pripojenie k databázam OLAP pre dotazy OLAP. Keď otvoríte súbor .odc v Exceli, zobrazí sa prázdna zostava kontingenčnej tabuľky a je pripravená na umiestnenie.

Vytvorte súbory kocky na použitie offline Môžete vytvoriť samostatný súbor kocky (.cub) s podmnožinou údajov z databázy servera OLAP. Súbory kocky offline sa používajú na prácu s údajmi OLAP, keď nie ste pripojení k sieti. S kockou môžete pracovať s väčším množstvom údajov v zostave kontingenčnej tabuľky alebo zostave kontingenčného grafu, než by ste inak pracovali, a získať údaje rýchlejšie. Súbory kocky môžete vytvárať iba vtedy, ak používate poskytovateľa OLAP, ako je napríklad Microsoft SQL Analysis Services verzia 2005, ktorý podporuje túto funkciu.

Akcie servera Akcia servera je voliteľná funkcia, ktorú môže definovať správca kocky OLAP na serveri, ktorý používa prvok kocky alebo mieru ako parameter v dotaze na získanie informácií v kocke alebo na spustenie inej aplikácie, napríklad prehliadača. Excel podporuje adresy URL, zostavy, sady riadkov, hĺbkovú analýzu a podrobné akcie servera, ale nepodporuje vlastného natívneho operátora a množinu údajov.

KPI KPI je špecifická, vypočítaná miera definovaná na serveri, ktorá vám umožňuje sledovať „Kľúčové ukazovatele výkonnosti“ vrátane stavu (aktuálna hodnota zodpovedá určitý počet). a trend (hodnoty v čase). Keď sa zobrazia, server môže odoslať príslušné ikony, podobné novej ikone Excelu, na zoradenie nad alebo pod úrovne stavu (napríklad pre ikonu zastavenia), ako aj posúvať hodnotu nahor alebo nadol (napr. napríklad ikona smerovej šípky).

Formátovanie na serveri Správcovia kociek môžu vytvárať miery a vypočítané členy pomocou formátovania farieb, formátovania písma a pravidiel podmieneného formátovania, ktoré možno priradiť ako podnikové štandardné obchodné pravidlo. Napríklad formátom príjmu na strane servera môže byť číselný formát meny, farba bunky je zelená, ak je hodnota väčšia alebo rovná 30 000 a červená, ak je hodnota menšia ako 30 000, a štýl písma je tučný, ak je hodnota je menej ako 30 000, a ak je hodnota kladná - obyčajná. väčšie alebo rovné 30 000. Ďalšie informácie môže byť najdený.

Jazyk rozhrania Office Správca kocky môže definovať preklady údajov a chýb na serveri pre používateľov, ktorí potrebujú zobraziť informácie o kontingenčnej tabuľke v inom jazyku. Táto funkcia je definovaná ako vlastnosť pripojenia súboru a miestne nastavenie a krajina počítača používateľa sa musia zhodovať s jazykom rozhrania.

Softvérové ​​komponenty potrebné na prístup k zdrojom údajov OLAP

Poskytovateľ OLAP Ak chcete nastaviť zdroje údajov OLAP pre Excel, potrebujete jedného z nasledujúcich poskytovateľov OLAP.

Poskytovateľ Microsoft OLAP Excel obsahuje ovládač zdroja údajov a klienta softvér na prístup k databázam vytvoreným pomocou Microsoft SQL Server olap verzie 7.0, Microsoft SQL Server olap verzie 2000 (8.0) a Microsoft SQL Server Analysis Services verzie 2005 (9.0).

Poskytovatelia OLAP tretích strán Ostatné produkty OLAP vyžadujú inštaláciu dodatočných ovládačov a klientskeho softvéru. Ak chcete používať možnosti manipulácie s údajmi OLAP programu Excel, produkt tretej strany musí spĺňať štandard OLE-DB pre OLAP a musí byť kompatibilný s balíkom Microsoft Office. Informácie o inštalácii a používaní poskytovateľa OLAP tretej strany nájdete v časti systémový administrátor alebo predajcu produktu OLAP.

Serverové databázy a súbory kocky Klientsky softvér Excel OLAP podporuje pripojenia k dvom typom databáz OLAP. Ak je databáza na serveri OLAP dostupná v sieti, môžete z nej získať zdrojové údaje priamo z nej. Ak máte samostatný súbor kocky, ktorý obsahuje údaje OLAP alebo súbor definície kocky, môžete sa k tomuto súboru pripojiť a získať z neho zdrojové údaje.

Zdroje dát Zdroj údajov poskytuje prístup ku všetkým údajom v databáze OLAP alebo offline súbore kocky. Keď vytvoríte zdroj údajov OLAP, môžete na ňom založiť zostavy a vrátiť údaje OLAP do Excelu ako zostavu kontingenčnej tabuľky alebo kontingenčného grafu alebo ako funkciu hárka konvertovanú zo zostavy kontingenčnej tabuľky.

Microsoft Query Pomocou služby Query môžete získať údaje z vonkajšia základňaúdaje, ako napríklad Microsoft SQL alebo Microsoft Access. Na získanie údajov z kontingenčnej tabuľky OLAP priradenej k súboru kocky nemusíte použiť dotaz. Ďalšie informácie .

Rozdiely vo funkciách OLAP a zdrojových údajoch iných ako OLAP

Ak pracujete so zostavami kontingenčných tabuliek a kontingenčnými grafmi zo zdrojových údajov OLAP a iných typov zdrojových údajov, uvidíte určité rozdiely vo funkciách.

Extrakcia dát Server OLAP vráti nové údaje do Excelu vždy, keď sa zmení rozloženie zostavy. Pri iných typoch externých zdrojov údajov dopytujete všetky zdrojové údaje naraz alebo môžete nastaviť parametre dotazu len pri zobrazení rôznych položiek poľa filtra zostavy. Okrem toho máte niekoľko ďalších možností aktualizácie prehľadu.

V zostavách založených na zdrojových údajoch OLAP nie sú dostupné možnosti poľa filtra zostavy, dotaz na pozadí nie je dostupný a možnosť optimalizácie pamäte nie je dostupná.

Poznámka: Možnosť optimalizácie pamäte tiež nie je k dispozícii pre zdroje údajov OLEDB a zostavy kontingenčných tabuliek založené na rozsahu buniek.

Typy polí Zdrojové údaje OLAP. polia dimenzií možno použiť iba ako riadky (riadky), stĺpce (kategória) alebo polia stránok. Polia merania možno použiť iba ako polia hodnôt. V prípade iných typov zdrojových údajov možno všetky polia použiť v ktorejkoľvek časti zostavy.

Prístup k podrobným údajom Pre zdrojové údaje OLAP server určí dostupné úrovne podrobností a vypočíta súhrnné hodnoty, takže podrobné záznamy, ktoré tvoria súhrnné hodnoty, nemusia byť dostupné. Server však môže poskytnúť polia vlastností, ktoré môžete zobraziť. Iné typy zdrojových údajov nemajú polia vlastností, ale môžete zobraziť základné informácie o hodnotách údajových polí a prvkov a zobraziť prvky bez údajov.

Polia filtra zostavy OLAP nemusia mať Všetky prvky a príkaz Zobraziť stránky filtrovania prehľadov nie je k dispozícií.

Počiatočné poradie zoradenia V prípade zdrojových údajov OLAP sa položky najprv zobrazia v poradí, v akom ich vráti server OLAP. Prvky môžete zoradiť alebo manuálne zmeniť poradie. Pre ostatné typy zdrojových údajov sa prvky novej zostavy najskôr zoradia podľa názvu prvku vo vzostupnom poradí.

Nimi Servery OLAP poskytujú súhrnné hodnoty priamo do zostavy, takže nie je možné zmeniť súhrnné funkcie pre polia hodnôt. Pre iné typy zdrojových údajov môžete zmeniť súhrnnú funkciu pre pole hodnoty a použiť viacero súhrnných funkcií pre rovnaké pole hodnoty. V zostavách so zdrojovými údajmi OLAP nemôžete vytvárať vypočítané polia a vypočítané členy.

Medzisúčty V zostavách so zdrojovými údajmi OLAP nemôžete zmeniť súhrnnú funkciu pre medzisúčty. Pri iných typoch zdrojových údajov môžete zmeniť súhrnné funkcie pre medzisúčty a zobraziť alebo skryť medzisúčty pre všetky polia riadkov a stĺpcov.

Pre zdrojové údaje OLAP môžete pri výpočte medzisúčtov a celkových súčtov zahrnúť alebo vylúčiť skryté položky. V prípade iných typov zdrojových údajov môžete zahrnúť skryté položky do polí filtra zostavy v medzisúčtoch, ale skryté položky v iných poliach budú predvolene vylúčené.

Problémy analytiky, OLAP, dátových skladov sú čoraz viac predmetom záujmu ruských IT špecialistov. K dnešnému dňu naša počítačová tlač a internet zverejnili veľa dobrých, akademických materiálov na túto tému, vrátane úvodných. Dávame do pozornosti článok, v ktorom sa zámerne snažíme vysvetliť OLAP „na prstoch“ na konkrétnom príklade. Prax ukazuje, že takéto vysvetlenie je pre niektorých IT odborníkov a najmä pre koncových používateľov nevyhnutné.

Takže OLAP *1 ako prvé priblíženie, „na prstoch“, možno definovať ako špeciálny spôsob analýzy údajov a generovania správ. Jeho podstatou je poskytnúť používateľovi viacrozmernú tabuľku, ktorá automaticky sumarizuje údaje v rôznych sekciách a umožňuje interaktívne spravovať výpočty a formu zostavy. Tento článok bude hovoriť o technológii a základných operáciách OLAP na príklade analýzy faktúr pre podnik zaoberajúci sa veľkoobchodom s potravinami.

*1. OLAP - On-Line Analytical Processing, prevádzková analýza dát.

Ako nástroj bude považovaný OLAP systém najjednoduchšej a najlacnejšej triedy, OLAP klient *1. Napríklad sa vyberie najjednoduchší produkt z počtu klientov OLAP - „Contour Standard“ od Intersoft Lab. (Pre prehľadnosť budú ďalej v článku všeobecne akceptované podmienky OLAP označené tučným písmom a budú sprevádzané anglickými náprotivkami.)

*1. Ďalšie informácie o klasifikácii systémov OLAP nájdete v článku „OLAP Made in Russia“ v PC Week/RE, č. 3/2001.

Začnime teda systémom. Najprv je potrebné opísať zdroj údajov (zdroj údajov) – cestu k tabuľke a jej polia. To je úlohou používateľa, ktorý pozná fyzickú implementáciu databázy. Pre koncových používateľov prekladá názov tabuľky a jej polí do doménových výrazov. Za „zdrojom údajov“ sa nachádza lokálna tabuľka, tabuľka alebo zobrazenie servera SQL alebo uložená procedúra.

S najväčšou pravdepodobnosťou v konkrétnej databáze nie sú faktúry uložené v jednej, ale v niekoľkých tabuľkách. Okrem toho sa niektoré polia alebo záznamy nemusia použiť na analýzu. Preto sa potom vytvorí Ukážka (výsledková sada alebo dotaz), v ktorej sú nakonfigurované nasledovné: algoritmus na kombinovanie tabuliek podľa kľúčových polí, podmienky filtrovania a množina vrátených polí. Náš výber nazveme „Faktúry“ a dáme doň všetky polia zdroja údajov „Faktúry“. IT špecialista tak vytvorením sémantickej vrstvy skryje pred koncovým používateľom fyzickú implementáciu databázy.

Potom sa nakonfiguruje zostava OLAP. Môže to urobiť odborník na danú problematiku. Po prvé, polia plochej vzorky údajov sú rozdelené do dvoch skupín – fakty (fakty alebo miery) a merania (rozmery). Fakty sú čísla a merania sú „strihy“, v ktorých budú zhrnuté fakty. V našom príklade budú rozmery: „Región“, „Mesto“, „Zákazník“, „Produkt“, „Dátum“ a skutočnosť bude jedna – pole „Suma“ faktúry. V skutočnosti musí byť vybraný jeden alebo viac agregačných algoritmov. OLAP je schopný nielen sumarizovať súčty, ale vykonávať aj zložitejšie výpočty, až Štatistická analýza. Výberom viacerých agregačných algoritmov sa vytvoria virtuálne vypočítané fakty. V príklade je vybratý jeden agregačný algoritmus - „Sum“.

Špeciálnou vlastnosťou OLAP systémov je generovanie dimenzií a údajov za staršie časové obdobia z dátumu a automatický výpočet súčtov za tieto obdobia. Vyberme obdobia „Rok“, „Štvrťrok“ a „Mesiac“, pričom pre každý deň v prehľade nebudú žiadne údaje, ale zobrazia sa vygenerované dimenzie „Rok“, „Štvrťrok“ a „Mesiac“. Zostavu pomenujeme „Analýza predaja“ a uložíme. Práce na vytvorení rozhrania analytickej aplikácie sú ukončené.

Teraz, keď toto rozhranie spúšťate denne alebo mesačne, používateľ uvidí tabuľku a graf, v ktorom sú zhrnuté faktúry podľa položky, zákazníka a obdobia.

Aby bola manipulácia s údajmi intuitívna, nástroje na správu dynamická tabuľka sú prvky samotnej tabuľky – jej stĺpce a riadky. Používateľ môže presúvať, mazať, filtrovať a vykonávať ďalšie operácie OLAP. Tabuľka automaticky vypočíta nové medzisúčty a konečné súčty.

Napríklad potiahnutím (operácia „presunúť“) stĺpec „Produkt“ na prvé miesto získame porovnávaciu zostavu – „Porovnanie objemov predaja produktov za rok“. Ak chcete agregovať údaje za rok, stačí presunúť stĺpce „Štvrťrok“ a „Mesiac“ do hornej časti tabuľky – „oblasť neaktívnych meraní“. Dimenzie „Štvrťrok“ a „Mesiac“ prenesené do tejto oblasti budú uzavreté (operácia „zatvorená dimenzia“), t. j. vylúčené z prehľadu. fakty budú zhrnuté za rok. Napriek tomu, že dimenzie sú uzavreté, môžete nastaviť konkrétne roky, štvrťroky a mesiace pre filtrovanie údajov (operácia „filtra“).

Pre lepšiu prehľadnosť zmeňme typ grafu znázorňujúceho tabuľku OLAP a jej umiestnenie na obrazovke.

Prehĺbenie údajov (operácia „drill down“) vám umožňuje získať podrobnejšie informácie o predaji produktu, ktorý nás zaujíma. Kliknutím na znamienko „+“ oproti produktu „Káva“ sa nám zobrazia jeho objemy predaja podľa regiónu. Po otvorení regiónu Ural získame objemy predaja v kontexte miest regiónu Ural, keď sa ponoríme do údajov o Jekaterinburgu, budeme môcť zobraziť údaje o veľkoobchodných nákupcoch tohto mesta.

Na nastavenie filtrov môžete použiť aj otvorené merania. Aby sme porovnali dynamiku predaja cukroviniek v Moskve a Jekaterinburgu, nastavme si filtre pre dimenzie „Produkt“ a „Mesto“.

Zatvorte nepotrebné merania a vyberte typ grafu "Line". Na výslednom grafe môžete sledovať dynamiku predaja, vyhodnocovať sezónne výkyvy a vzťah medzi prepadmi a rastom predaja tovaru v rôznych mestách.

Presvedčili sme sa teda, že technológia OLAP umožňuje užívateľovi uvoľniť desiatky typov rôznych reportov z jedného rozhrania a spravovať dynamickú OLAP tabuľku pomocou myši. Úlohou programátora, ktorý takýto nástroj vlastní, nie je rutinné kódovanie reportovacích formulárov, ale nastavenie OLAP klienta pre databázy. Spôsoby spravovania reportu sú zároveň pre koncového užívateľa intuitívne.

V skutočnosti je OLAP prirodzeným rozšírením a rozvojom myšlienky tabuliek. Vizuálne rozhranie OLAP je v podstate tiež tabuľkový procesor, ale vybavený výkonným výpočtovým motorom a špeciálnym štandardom na prezentáciu a správu údajov. Okrem toho sú niektorí klienti OLAP implementovaní ako doplnky do MS Excel. Preto aj miliónová armáda „bielych golierov“, ktorí sú si istí tabuľkami, rýchlo ovládajú nástroje OLAP. Pre nich je to „zamatová revolúcia“, ktorá poskytuje nové príležitosti, ale nevyžaduje rekvalifikáciu.

Ak čitateľ po prečítaní tohto článku nestratil záujem o OLAP, môže sa odvolať na materiály uvedené na začiatku. Zbierky takýchto materiálov sú zverejnené na mnohých stránkach na internete, vrátane stránky Intersoft lab - www.iso.ru. Z neho si môžete stiahnuť aj demo verziu systému Kontur Standard s príkladom opísaným v článku.

Štandardné kontingenčnej tabuľky pôvodné údaje sú 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- Toto používateľské rozhranie na zobrazenie 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
• prezentáciu údajov

Ryža. 1. Analýza údajov v Exceli: a) nespracované údaje, b) meranie c Power Pivot, c) prístrojová doska; Ak chcete obrázok zväčšiť, kliknite naň pravým tlačidlom myši 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().

Toto 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 predstavujúci 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, ako 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 - preto 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 Typ príkazu 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

Vyberte dokument z archívu, ktorý chcete zobraziť:

18,5 kB autá.xls

14 kB krajiny.xls

Excel pr.r. 1.docx

Knižnica
materiálov

Praktická práca 1

"Účel a rozhranie programu MS Excel"

Vyplnením tejto témy získate:

1. Naučte sa spúšťať tabuľky;

2. Opravte základné pojmy: bunka, riadok, stĺpec, adresa bunky;

3. Zistite, ako zadávať údaje do bunky a upravovať riadok vzorcov;

5. Ako vybrať celé riadky, stĺpec, niekoľko buniek umiestnených vedľa seba a celú tabuľku.

Cvičenie: Oboznámte sa so základnými prvkami okna MS Excel.

Spustite program Microsoft Excel. Pozrite sa pozorne na okno programu.

Dokumenty vytvorené pomocouEXCEL , sa volajúpracovné zošity a mať rozšírenie. XLS. Nový zošit má tri pracovné hárky s názvom LIST1, LIST2 a LIST3. Tieto názvy sú uvedené na kartách hárkov v spodnej časti obrazovky. Ak chcete prepnúť na iný hárok, kliknite na názov tohto hárka.

Akcie pracovného listu:

Premenovanie pracovného hárka. Umiestnite ukazovateľ myši na chrbát pracovného hárka a dvakrát kliknite na ľavé tlačidlo alebo zavolajte obsahové menu a vyberte Premenovať.Pomenujte hárok „WORKOUT“

Vložte pracovný hárok . Vyberte záložku listu "Hárok 2", pred ktorú chcete vložiť nový list, a pomocou kontextového menuvložte nový hárok a pomenujte ho „Ukážka“ .

Odstránenie pracovného hárka. Vyberte kartu listu "Hárok 2" a pomocou kontextového menuvymazať .

Bunky a rozsahy buniek.

Pracovný priestor pozostáva z riadkov a stĺpcov. Riadky sú očíslované od 1 do 65536. Stĺpce sú označené latinskými písmenami: A, B, C, ..., AA, AB, ..., IV, spolu - 256. Na priesečníku riadku je bunka a stĺpec. Každá bunka má svoju adresu: názov stĺpca a číslo riadku, na ktorého priesečníku sa nachádza. Napríklad A1, CB234, P55.

Ak chcete pracovať s niekoľkými bunkami, je vhodné ich kombinovať do "rozsahov".

Rozsah sú bunky usporiadané do obdĺžnika. Napríklad A3, A4, A5, B3, B4, B5. Ak chcete napísať rozsah, použite ": »: A3:B5

8:20 - všetky bunky v riadkoch 8 až 20.

A:A - všetky bunky v stĺpci A.

N:R - všetky bunky v stĺpcoch od H po R.

Adresa bunky môže obsahovať názov pracovného hárka: Hárok8!A3:B6.

2. Vyberte bunky v Exceli

Čo zdôrazňujeme

Akcie

jedna bunka

Kliknite naň alebo presuňte výber pomocou klávesov so šípkami.

reťazec

Kliknutím na číslo riadku.

Stĺpec

Kliknutím na názov stĺpca.

Rozsah buniek

Potiahnite ukazovateľ myši z ľavého horného rohu rozsahu do pravého dolného rohu.

Viacnásobné rozsahy

Vyberte prvý, stlačte SCHIFT + F 8, vyberte ďalší.

Celý stôl

Kliknutím na tlačidlo „Vybrať všetko“ (prázdne tlačidlo naľavo od názvov stĺpcov)

Potiahnutím okrajov medzi nimi môžete zmeniť šírku stĺpca a výšku riadka.

Pomocou posúvačov určte, koľko riadkov má tabuľka a aký je názov posledného stĺpca.
Pozor!!! Ak chcete rýchlo dosiahnuť koniec tabuľky vodorovne alebo zvisle, musíte stlačiť kombináciu klávesov: Ctrl+→ - koniec stĺpcov alebo Ctrl+↓ - koniec riadkov. Rýchly návrat na začiatok tabuľky - Ctrl+Home.

Do bunky A3 zadajte adresu posledného stĺpca tabuľky.

Koľko riadkov je v tabuľke? Zadajte adresu posledného riadku do bunky B3.

3. V programe EXCEL môžete zadať nasledujúce typy údajov:

čísla.

Text (napríklad nadpisy a vysvetľujúci materiál).

Funkcie (napríklad súčet, sínus, odmocnina).

Vzorce.

Údaje sa vkladajú do buniek. Ak chcete zadať údaje, musíte vybrať požadovanú bunku. Existujú dva spôsoby zadávania údajov:

Stačí kliknúť do bunky a zadať požadované údaje.

Kliknite do bunky a do riadka vzorcov a zadajte údaje do riadka vzorcov.

Stlačte Enter.

Zadajte svoje meno do bunky N35, vycentrujte ho v bunke a použite tučné písmo.
Pomocou riadka vzorcov zadajte aktuálny rok do bunky C5.

4. Zmeňte údaje.

Vyberte bunku a stlačte F 2 a zmeňte údaje.

Vyberte bunku e kliknite na riadok vzorcov a zmeňte tam údaje.

Ak chcete zmeniť vzorce, môžete použiť iba druhú metódu.

Zmeňte údaje v bunke N35, pridajte svoje priezvisko. pomocou ktorejkoľvek z metód.

5. Zadávanie vzorcov.

Vzorec je aritmetický alebo logický výraz, pomocou ktorého sa vykonávajú výpočty v tabuľke. Vzorce pozostávajú z odkazov na bunky, operačných znakov a funkcií. Pani EXCEL má veľmi veľký súbor vstavaných funkcií. S ich pomocou môžete vypočítať súčet alebo aritmetický priemer hodnôt z určitého rozsahu buniek, vypočítať úroky z vkladov atď.

Vzorce vždy začínajú znakom rovnosti. Po zadaní vzorca do príslušnej bunky sa zobrazí výsledok výpočtu a samotný vzorec je možné vidieť na riadku vzorcov.

Akcia

Príklady

+

Doplnenie

A1+B1

-

Odčítanie

A1 - B2

*

Násobenie

B3*C12

/

divízie

A1 / B5

Umocňovanie

A4 ^3

=, <,>,<=,>=,<>

znaky vzťahu

A2

Pomocou zátvoriek vo vzorcoch môžete zmeniť poradie akcií.

Automatické dopĺňanie.

Veľmi pohodlným nástrojom, ktorý sa používa iba v MS EXCEL, je automatické dopĺňanie susedných buniek. Napríklad musíte zadať názvy mesiacov v roku do stĺpca alebo riadka. Toto je možné vykonať ručne. Existuje však oveľa pohodlnejší spôsob:

Do prvej bunky zadajte požadovaný mesiac, napríklad január.

Vyberte túto bunku. V pravom dolnom rohu rámčeka výberu je malý štvorec - rukoväť výplne.

Podržte stlačený a presuňte ukazovateľ myši nad rukoväť výplne (zmení sa na kríž). ľavé tlačidlo myšou potiahnite značku v požadovanom smere. V tomto prípade bude aktuálna hodnota bunky viditeľná vedľa rámčeka.

Ak potrebujete vyplniť nejaký číselný rad, musíte zadať prvé dve čísla do dvoch susedných buniek (napríklad zadajte 1 do A4 a 2 do B4), vyberte tieto dve bunky a roztiahnite oblasť výberu o fix na požadovanú veľkosť.

Vybratý dokument na zobrazenie Excel pr.r. 2.docx

Knižnica
materiálov

Praktická práca 2

"Zadávanie údajov a vzorcov do buniek tabuľky MS Excel"

· Zadajte údaje do buniek iný typ: textové, číselné, vzorce.

Cvičenie: Vykonajte požadované zadávanie údajov a jednoduché výpočty v tabuľke.

Technológia vykonávania úloh:

1. Spustite programMicrosoft Excel.

2. Do bunkyA1 list 2 uveďte text: „Rok založenia školy“. Opravte údaje v bunke akýmkoľvek spôsobom, ktorý poznáte.

3. Do bunkyV 1 uveďte číslo - rok založenia školy (1971).

4. Do bunkyC1 zadajte číslo - aktuálny rok (2016).

Pozor! Upozorňujeme, že v čs Excel textúdaje sú zarovnané doľava a čísla a dátumy sú zarovnané doprava.

5. Zvýraznite bunkuD1 , pomocou klávesnice zadajte vzorec na výpočet veku školy:=C1-B1

Pozor! Vzorce vždy začínajú znakom rovnosti«=». Adresy buniek musia byť zadané latinkou bez medzier. Adresy buniek je možné zadávať do vzorcov bez použitia klávesnice, ale jednoducho kliknutím na príslušné bunky myšou.

6. Odstráňte obsah bunkyD1 a znova zadajte vzorec pomocou myši. V bunkeD1 nainštalovať znamenie«=» a potom kliknite na bunkuC1, všimnite si adresu tejto bunky, v ktorej sa objavilaD1, postaviť znamenie«–» a kliknite na bunkuB1 , kliknite(Vstúpte).

7. Do bunkyA2 zadajte text"Môj vek".

8. Do bunkyB2 zadajte rok narodenia.

9. Do bunkyC2 zadajte aktuálny rok.

10. Napíšte do bunkyD2 vzorec na výpočet vášho veku v aktuálnom roku(=C2-B2).

11. Zvýraznite bunkuC2. Zadajte číslo budúceho roka. Všimnite si prepočet v bunkeD2 stalo automaticky.

12. Určite svoj vek v roku 2025. Ak to chcete urobiť, nahraďte rok v bunkeC2 na2025.

Samostatná práca

Cvičenie: Vypočítajte si pomocou ET, bude vám stačiť 130 rubľov na nákup všetkých produktov, ktoré vám objednala vaša matka, a bude stačiť kúpiť čipy za 25 rubľov?

Technológia cvičenia:
o Do bunky A1 zadajte „Nie“.
o Do buniek A2, A3 zadajte „1“, „2“, vyberte bunky A2, A3, ukážte do pravého dolného rohu (mal by sa objaviť čierny krížik), roztiahnite na bunku A6
o Do bunky B1 zadajte „Názov“
o Do bunky C1 zadajte „Cena v rubľoch“
o Do bunky D1 zadajte „Množstvo“
o Do bunky E1 zadajte „Náklady“ atď.
o V stĺpci „Cena“ sú zapísané všetky vzorce anglický jazyk!
o Vo vzorcoch sa namiesto premenných píšu názvy buniek.
o Po stlačení Enter namiesto vzorca sa ihneď objaví číslo - výsledok výpočtu

o Celkovú sumu si vypočítajte sami.

Ukážte výsledok učiteľovi!

Vybratý dokument na zobrazenie Excel pr.r. 3.docx

Knižnica
materiálov

Praktická práca 3

MS Excel. Vytvárajte a upravujte tabuľkový dokument»

Vyplnením tejto témy sa naučíte:

 Vytvorte a naplňte tabuľku údajmi;

 Formátovať a upravovať údaje v bunke;

 Použite jednoduché vzorce v tabuľke;

 Skopírujte vzorce.

Cvičenie:

1. Vytvorte tabuľku obsahujúcu cestovný poriadok zo stanice Saratov do stanice Samara. Všeobecná forma tabuľka "Rozvrh" je zobrazená na obrázku.

2. Vyberte bunkuA3 , zmeňte slovo „Golden“ na „Great“ a stlačte klávesZadajte .

3. Vyberte bunkuA6 , kliknite naň dvakrát ľavým tlačidlom myši a nahraďte "Ponurý" za "Veselkovo"

4. Vyberte bunkuA5 prejdite na riadok vzorcov a nahraďte „Sennaya“ za „Sennaya 1“.

5. Doplňte tabuľku „História“ o výpočty času zastávok vlakov v každom sídlisku. (vložte stĺpce) Vypočítajte celkový čas zastávky, celkový čas cesty, čas, ktorý vlak potrebuje na cestu z jedného lokalite inému.

Technológia vykonávania úloh:

1. Presuňte stĺpec Čas odchodu zo stĺpca C do stĺpca D. Ak to chcete urobiť, postupujte takto:

Vyberte blok C1:C7; vyberte tímVystrihnúť .
Umiestnite kurzor do bunky D1;
Vykonajte príkazVložiť ;
Zarovnajte šírku stĺpca tak, aby zodpovedala veľkosti hlavičky.;

2. Do bunky C1 zadajte text „Parkovanie“. Zarovnajte šírku stĺpca tak, aby zodpovedala veľkosti hlavičky.

3. Vytvorte vzorec, ktorý vypočíta čas parkovania v lokalite.

4. Musíte skopírovať vzorec do bloku C4:C7 pomocou rukoväte výplne. Ak to chcete urobiť, postupujte takto:
Okolo aktívnej bunky je rám, v rohu ktorého je malý obdĺžnik, ktorý ho chytí a predĺži vzorec až do bunky C7.

5. Do bunky E1 zadajte text „Čas cesty“. Zarovnajte šírku stĺpca tak, aby zodpovedala veľkosti hlavičky.

6. Vytvorte vzorec, ktorý vypočíta čas, ktorý vlak potrebuje na cestu z jedného miesta na druhé.

7. Zmeňte formát čísla pre bloky C2:C9 a E2:E9. Ak to chcete urobiť, postupujte takto:

Vyberte blok buniek C2:C9;
Domov - Formát - Iné formáty čísel - Čas a nastavené parametre (hodiny:minúty) .

Stlačte klávesOK .

8. Vypočítajte celkový čas parkovania.
Vyberte bunku C9;
Kliknite na tlačidloAutoSum na paneli nástrojov;
Potvrďte výber bunkového bloku C3:C8 a stlačte klávesZadajte .

9. Zadajte text do bunky B9. Ak to chcete urobiť, postupujte takto:

Vyberte bunku B9;
Zadajte text „Celkový čas parkovania“. Zarovnajte šírku stĺpca tak, aby zodpovedala veľkosti hlavičky.

10. Odstráňte obsah bunky C3.

Vyberte bunku C3;
Vykonajte príkaz hlavného menu Upraviť - Vymazať alebo klikniteOdstrániť na klávesnici;
Pozor! Počítač automaticky prepočíta množstvo v bunke C9!!!

Vykonajte príkaz Zrušiť alebo kliknite na príslušné tlačidlo na paneli s nástrojmi.

11. Do bunky D9 zadajte text „Celkový čas cesty“.

12. Vypočítajte celkový čas cesty.

13. Vyfarbite tabuľku a zvýraznite okraje tabuľky.

Samostatná práca

Vypočítajte pomocou tabuľkyExcelvýdavky školákov, ktorí sa chystajú na exkurziu do iného mesta.

Vybratý dokument na zobrazenie Excel pr.r. 4.docx

Knižnica
materiálov

Praktická práca 4

"Odkazy. Vstavané funkcie MS Excel".

Vyplnením tejto témy sa naučíte:

Vykonávajte operácie s kopírovaním, presúvaním a automatickým vypĺňaním jednotlivých buniek a rozsahov.

Rozlišujte medzi typmi odkazov (absolútne, relatívne, zmiešané)

Použite vstavané matematické a štatistické Excel funkcie.

MS Excel obsahuje 320 vstavaných funkcií. Najjednoduchší spôsob prijímanie úplné informácie o ktoromkoľvek z nich je použiť menuOdkaz . Pre pohodlie sú funkcie v Exceli rozdelené do kategórií (matematické, finančné, štatistické atď.).
Volanie každej funkcie pozostáva z dvoch častí: názvu funkcie a argumentov v zátvorkách.

Tabuľka. Vstavané funkcie v Exceli

* Napísané bez argumentov.

Tabuľka . Typy odkazov

Cvičenie.

1. Stanovené sú náklady na 1 kWh. stavy elektriny a elektromerov za predchádzajúci a aktuálny mesiac. Je potrebné vypočítať spotrebu elektriny za uplynulé obdobie a náklady na spotrebovanú elektrinu.

Technológia práce:

1. Zarovnajte text v bunkách. Vyberte bunky A3:E3. Domov - Formát - Formát bunky - Zarovnanie: horizontálne - na stred, vertikálne - na stred, zobrazenie - zalamovanie slov.

2. Do bunky A4 zadajte: Sq. 1, do bunky A5 zadajte: Sq. 2. Označte bunky A4:A5 a pomocou značky automatického dopĺňania vyplňte číslovanie bytov do 7 vrátane.

5. Vyplňte bunky B4:C10 podľa obrázku.

6. Do bunky D4 zadajte vzorec na zistenie spotreby elektriny / energie. A vyplňte riadky nižšie pomocou tokenu automatického dopĺňania.

7. Do bunky E4 zadajte vzorec na zistenie ceny elektriny=D4*$B$1. A vyplňte riadky nižšie pomocou tokenu automatického dopĺňania.

Poznámka!
Pri automatickom dopĺňaní sa adresa bunky B1 nemení,
pretože je nastavená absolútna referencia.

8. Do bunky A11 zadajte text "Štatistické údaje", vyberte bunky A11:B11 a kliknite na tlačidlo "Zlúčiť a vycentrovať" na paneli nástrojov.

9. Do buniek A12:A15 zadajte text zobrazený na obrázku.

10. Kliknite na bunku B12 a zadajte matematickú funkciuSUM , ak to chcete urobiť, kliknite na riadok vzorcovznakomfx a vyberte funkciu a potvrďte rozsah buniek.

11. Podobne sú funkcie nastavené v bunkách B13:B15.

12. Vykonali ste výpočty na hárku 1, premenujte ho na Elektrina.

Samostatná práca

Cvičenie 1:

Vypočítajte si svoj vek od aktuálneho roku do roku 2030 pomocou tokenu automatického dopĺňania. Rok vášho narodenia je absolútnou referenciou. Vykonajte výpočty na Háre 2. Premenujte Hárok 2 na Vek.

Cvičenie 2: Vytvorte tabuľku podľa príkladu.V bunkáchja5: L12 aD13: L14 by mali byť vzorce: AVERAGE, COUNTIF, MAX, MIN. bunkyB3: H12 sú vyplnené informáciami.

Vybratý dokument na zobrazenie Excel pr.r. 5.docx

Knižnica
materiálov

Praktická práca 5

Vyplnením tejto témy sa naučíte:

 Technológie na vytváranie tabuľkových dokumentov;

 Priraďte typ použitým údajom;

 Vytváranie vzorcov a pravidiel na zmenu odkazov v nich;

 Na výpočty použite vstavané štatistické funkcie Excelu.

Cvičenie 1. Vypočítajte počet dní života.

Technológia práce:

1. Spustite aplikáciu Excel.

2. Do bunky A1 zadajte svoj dátum narodenia (deň, mesiac, rok - 20.12.97). Opravte zadávanie údajov.

3. Zobrazenie rôznych formátov dátumu(Domov - Formát bunky - Iné formáty čísel - Dátum) . Previesť dátum na typHH.MM.RRRR. Príklad, 03/14/2001

4. Zvážte niekoľko typov formátov dátumu v bunke A1.

5. Do bunky A2 zadajte dnešný dátum.

6. V bunke A3 vypočítajte počet dní života pomocou vzorca. Výsledok môže byť vyjadrený ako dátum, v takom prípade by sa mal previesť na číselný typ.

Úloha 2. Vek študentov. Podľa zadaného zoznamu študentov a ich dátumu narodenia. Určiť, kto sa narodil skôr (neskôr), určiť, kto je najstarší (najmladší).

Technológia práce:

1. Získajte súbor Age. Cez LAN: Otvorte priečinok My Network Places -šéf-Všeobecné dokumenty - 9. ročník, nájdite súbor Vek. Skopírujte ho akýmkoľvek spôsobom, ktorý poznáte, alebo si ho stiahnite z tejto stránky v spodnej časti aplikácie.

2. Vypočítajte vek študentov. Ak chcete vypočítať vek, musíte použiť funkciuDNES vyberte dnešný aktuálny dátum, odčíta sa od neho dátum narodenia študenta, následne z výsledného dátumu pomocou funkcie ROK sa z dátumu vytiahne iba rok. Od výsledného čísla odpočítajte 1900 – storočie a získajte vek študenta. Do bunky D3 napíšte vzorec=ROK(DNES()-S3)-1900 . Výsledok môže byť prezentovaný ako dátum, v takom prípade by sa mal previesť načíselný typ.

3. Určite najskoršie narodeniny. Do bunky C22 napíšte vzorec=MIN(C3:C21) ;

4. Určte najmladšieho študenta. Do bunky D22 napíšte vzorec=MIN(D3:D21) ;

5. Určite najneskoršie narodeniny. Do bunky C23 napíšte vzorec=MAX(C3:C21) ;

6. Určite najstaršieho žiaka. Do bunky D23 napíšte vzorec=MAX(D3:D21) .

Samostatná práca:
Úloha. Vykonajte potrebné výpočty rastu študentov v rôznych jednotkách merania.

Vybratý dokument na zobrazenie Excel pr.r. 6.docx

Knižnica
materiálov

Praktická práca 6

MS Excel. Štatistické funkcie“ Časť II.

Úloha 3. Pomocou tabuľkového procesora spracujte údaje pomocou štatistických funkcií. Uvádzajú sa informácie o žiakoch triedy vrátane priemerného skóre za štvrťrok, vek (rok narodenia) a pohlavie. Zistite priemerné skóre chlapcov, podiel výborných žiakov medzi dievčatami a rozdiel v priemernom skóre žiakov rôzneho veku.

Riešenie:
Vyplňte tabuľku počiatočnými údajmi a vykonajte potrebné výpočty.Venujte pozornosť formátu hodnôt v bunkách „GPA“ (číselné) a „Dátum narodenia“ (dátum)

Tabuľka používa ďalšie stĺpce, ktoré sú potrebné na zodpovedanie otázok položených v úlohe -vek študenta a je študentvýborná študentka a dievča súčasne.
Na výpočet veku sa použil nasledujúci vzorec (s použitím príkladu bunky G4):

=INTEGER((TODAY()-E4)/365,25)

Poďme sa k tomu vyjadriť. Od dnešného dátumu sa odpočíta dátum narodenia študenta. Dostaneme tak celkový počet dní, ktoré uplynuli od narodenia študenta. Vydelením tohto čísla číslom 365,25 (skutočný počet dní v roku, 0,25 dňa pre bežný rok je kompenzovaný priestupným rokom), dostaneme celkový počet rokov študenta; nakoniec zvýraznenie celej časti, - vek študenta.

To, či je dievča výborným študentom, určuje vzorec (na príklade bunky H4):

=IF(AND(D4=5,F4="w");1,0)

Začnime základnými výpočtami.
V prvom rade je potrebné určiť priemerné skóre dievčat. Podľa definície je potrebné vydeliť celkové skóre dievčat ich počtom. Na tieto účely môžete použiť zodpovedajúce funkcie tabuľkového procesora.

=SUMIF(F4:F15,"W",D4:D15)/COUNTIF(F4:F15,"W")

Funkcia SUMIF vám umožňuje sčítať hodnoty iba v tých bunkách rozsahu, ktoré spĺňajú zadané kritérium (v našom prípade je dieťa chlapec). Funkcia COUNTIF počíta počet hodnôt, ktoré spĺňajú zadané kritériá. Tak dostaneme to, čo potrebujeme.
Na výpočet podielu vynikajúcich študentov medzi všetkými dievčatami priradíme počet vynikajúcich dievčat k celkovému počtu dievčat (tu použijeme množinu hodnôt z jedného z pomocných stĺpcov):

=SUM(H4:H15)/COUNTIF(F4:F15,"W")

Nakoniec určíme rozdiel v priemernom skóre detí rôzneho veku (pri výpočtoch použijeme pomocný stĺpecVek ):

=ABS(SUMIF(G4:G15,15,D4:D15)/COUNTIF(G4:G15,15)-
SUMIF(G4:G15;16,D4:D15)/COUNTIF(G4:G15,16))

Upozorňujeme, že formát údajov v bunkách G18:G20 je číselný, na dve desatinné miesta. Tým je problém úplne vyriešený. Na obrázku sú znázornené výsledky riešenia pre daný súbor údajov.

Vybratý dokument na zobrazenie Excel pr.r. 7.docx

Knižnica
materiálov

Praktická práca 7

"Vytváranie grafov pomocou MS Excel"

Vyplnením tejto témy sa naučíte:

 Vykonajte operácie na vytvorenie grafov na základe údajov zadaných v tabuľke;

 Upravte údaje grafu, jeho typ a vzhľad.

Čo je graf. Graf je určený na grafické znázornenie údajov. Čiary, pruhy, stĺpce, sektory a iné vizuálne prvky sa používajú na zobrazenie číselných údajov zadaných do buniek tabuľky. Vzhľad grafu závisí od jeho typu. Všetky grafy, s výnimkou koláčového, majú dve osi: horizontálna je os kategórie a vertikálna je os hodnôt. Pri vytváraní 3D grafov sa pridáva tretia os – os série. Graf často obsahuje prvky, ako je mriežka, názvy a legenda. Mriežkové čiary sú rozšírenia rozdelenia na osiach, názvy sa používajú na vysvetlenie jednotlivých prvkov grafu a povahy údajov na ňom prezentovaných, legenda pomáha identifikovať rad údajov prezentovaných na grafe. Existujú dva spôsoby pridania grafov: vložiť ich do aktuálneho pracovného hárka a pridať samostatný hárok s grafom. V prípade, že je samotný diagram zaujímavý, umiestni sa na samostatný list. Ak potrebujete súčasne zobraziť graf a údaje, na základe ktorých bol zostavený, vytvorí sa vložený graf.

Graf sa uloží a vytlačí spolu so zošitom.

Po vygenerovaní diagramu bude možné v ňom vykonávať zmeny. Pred vykonaním akýchkoľvek akcií s prvkami grafu ich vyberte kliknutím ľavým tlačidlom myši. Potom vyvolajte kontextové menu pravým tlačidlom myši alebo použite príslušné tlačidláPanel nástrojov grafu .

Úloha: Použite tabuľku na vykreslenie funkcie Y=3,5x–5. Kde X nadobúda hodnoty od -6 do 6 v krokoch po 1.

Technológia práce:

1. Spustite tabuľku programu Excel.

2. Do bunky A1 zadajte „X“, do bunky B1 zadajte „Y“.

3. Vyberte rozsah buniek A1:B1, zarovnajte text v bunkách na stred.

4. Do bunky A2 zadajte -6 a do bunky A3 zadajte -5. Vyplňte bunky nižšie značkou automatického dopĺňania až po parameter 6.

5. Do bunky B2 zadajte vzorec: =3,5*A2–5. Použite token automatického dopĺňania na rozšírenie tohto vzorca na koniec údajových parametrov.

6. Vyberte celú tabuľku, ktorú ste vytvorili, a nastavte jej vonkajšie a vnútorné hranice.

7. Vyberte hlavičku tabuľky a vyplňte vnútornú oblasť.

8. Vyberte zvyšok buniek tabuľky a vyplňte vnútornú oblasť inou farbou.

9. Vyberte celú tabuľku. Vyberte z panela ponuky Vložiť -Diagram , Typ: Bodové, Zobrazenie: Bodové s hladkými krivkami.

10. Presuňte graf pod tabuľku.

Samostatná práca:

Nakreslite funkciu y=hriech(X)/ Xna segmente [-10;10] s krokom 0,5.

Zobrazte graf funkcie: a) y=x; b) y=x 3 ; c) y=-x na segmente [-15;15] s krokom 1.

Otvorte súbor "Cities" (prejdite do sieťového priečinka - 9. ročník-Cities).

Vypočítajte náklady na rozhovor bez zľavy (stĺpec D) a náklady na rozhovor so zľavou (stĺpec F).

Pre vizuálnu reprezentáciu vytvorte dva koláčové grafy. (1- diagram nákladov na rozhovor bez zľavy; 2- diagram nákladov na rozhovor so zľavou).

Vybratý dokument na zobrazenie Excel pr.r. 8.docx

Knižnica
materiálov

Praktická práca 8

STAVEBNÉ GRAFY A VÝKRESY PROSTREDNÍCTVOM MS EXCEL

1. Vytvorenie výkresu"DÁŽDNIK"

Tu sú funkcie, ktorých grafy sú zahrnuté v tomto obrázku:

y1= -1/18x 2 + 12, xО[-12;12]

r2 = -1/8x 2 +6, xО[-4;4]

r3= -1/8(X+8) 2 + 6, xО[-12; -4]

r4= -1/8(X-8) 2 + 6, хО

r5= 2(X+3) 2 – 9, хн[-4;0]

r6=1.5(X+3) 2 – 10, xО[-4;0]

- Spustite MS EXCEL

- V celeA1 zadajte označenie premennejX

· - Doplňte rozsah buniek A2:A26 číslami od -12 do 12.

Pre každý graf funkcie zavedieme vzorce postupne. Pre y1= -1/8x 2 + 12, xО[-12;12], pre
r2 = -1/8x 2 +6, xО[-4;4] atď.

Poradie akcií:

Nastavte kurzor na bunkuV 1 a zadajtey1

Do bunkyAT 2 zadajte vzorec=(-1/18)*A2^2 +12

Kliknite Zadajte na klávesnici

Hodnota funkcie sa vypočíta automaticky.

Rozbaľte vzorec do bunky A26

Rovnako aj v celeC10 (keďže hodnotu funkcie nájdeme len na intervale x od [-4; 4]) zadáme vzorec pre graf funkcier2 = -1/8x 2 +6. ATĎ.

Výsledkom by mal byť nasledujúci ET

Po vypočítaní všetkých hodnôt funkcií môžetevytvárať grafy títofunkcie

Vyberte rozsah buniek A1:G26

Na paneli s nástrojmi vyberte položkuVložiť menu → Diagram

V okne Sprievodca grafom vyberteBodové → Vybrať požadovaný pohľad→ Stlačte Dobre .

Výsledkom by mal byť nasledujúci obrázok:

Úloha na samostatnú prácu:

Nakreslite grafy funkcií v jednom súradnicovom systéme.x od -9 do 9 v krokoch po 1 . Získajte kresbu.

1. "Body"

2. "Mačka" Filtrovanie (výber) údajov v tabuľke umožňuje zobraziť len tie riadky, ktorých obsah buniek spĺňa zadanú podmienku alebo viacero podmienok. Na rozdiel od triedenia sa pri filtrovaní nezmení poradie údajov, ale skryjú sa iba záznamy, ktoré nespĺňajú zadané kritériá výberu.

Filtrovanie údajov je možné vykonať dvoma spôsobmi:pomocou automatického filtra alebo pokročilého filtra.

Ak chcete použiť automatický filter, potrebujete:

o nastavte kurzor do tabuľky;

o vybrať tímÚdaje - Filter - Automatický filter;

o rozbaľte zoznam stĺpca, podľa ktorého sa uskutoční výber;

o vyberte hodnotu alebo podmienku a nastavte kritériá výberu v dialógovom okneVlastný automatický filter.

Ak chcete obnoviť všetky riadky zdrojovej tabuľky, vyberte riadok all v rozbaľovacom zozname filtra alebo vyberte príkazÚdaje - Filter - Zobraziť všetko.

Ak chcete zrušiť režim filtrovania, umiestnite kurzor do tabuľky a znova vyberte príkaz ponukyÚdaje - Filter - Automatický filter (zrušte začiarknutie).

Pokročilý filter vám umožňuje vytvárať viaceré výberové kritériá a vykonávať komplexnejšie filtrovanie údajov tabuľky zadaním množiny kritérií pre výber v niekoľkých stĺpcoch. Filtrovanie záznamov pomocou rozšíreného filtra sa vykonáva pomocou príkazu ponukyÚdaje - Filter - Rozšírený filter.

Cvičenie.

Vytvorte tabuľku podľa príkladu znázorneného na obrázku. Uložte ho ako Sort.xls.

Technológia vykonávania úloh:

1. Otvorte dokument Sort.xls

2.

3. Vykonajte príkaz ponukyÚdaje - Triedenie.

4. Vyberte prvý kľúč triedenia "Vzostupne" (všetky oddelenia v tabuľke budú zoradené abecedne).

Pripomeňme, že si potrebujeme každý deň vytlačiť zoznam položiek, ktoré zostali v predajni (s nenulovým zostatkom), no k tomu si musíme najskôr takýto zoznam zaobstarať, t.j. filtrovať údaje.

5. Nastavte kurzor rámca vo vnútri tabuľky údajov.

6. Vykonajte príkaz ponukyÚdaje - Filter

7. Zrušte výber tabuliek.

8. Každá bunka hlavičky tabuľky má teraz tlačidlo „Šípka nadol“, nie je vytlačené, čo vám umožňuje nastaviť kritériá filtrovania. Všetky položky chceme ponechať s nenulovým zvyškom.

9. Kliknite na tlačidlo so šípkou, ktoré sa zobrazí v stĺpciZostatok . Otvorí sa zoznam, z ktorého si môžete vybrať. Vyberte riadokPodmienka. Nastavte podmienku: > 0. KlikniteOK . Údaje v tabuľke budú filtrované.

10. Namiesto úplný zoznam tovar, dostaneme zoznam doteraz predaného tovaru.

11. Filter je možné vylepšiť. Ak si dodatočne vyberiete oddelenie, môžete získať zoznam nedodaného tovaru podľa oddelení.

12. Ak chcete znova vidieť zoznam všetkého nepredaného tovaru pre všetky oddelenia, musíte v zozname "Oddelenie" vybrať kritérium "Všetko".

13. Aby ste sa vo svojich prehľadoch neplietli, vložte dátum, ktorý sa bude automaticky meniť podľa systémový čas počítačVzorce - Funkcia vkladania - Dátum a čas - Dnes .

Samostatná práca

MS Excel. Štatistické funkcie »

1 úloha (všeobecná) (2 body).

Pomocou tabuľkového procesora spracujte údaje pomocou štatistických funkcií.
1. Uvádzajú sa informácie o žiakoch v triede (10 osôb) vrátane známok za jeden mesiac z matematiky. Spočítajte päť, štvorku, dvojku a trojku, zistite priemerné skóre každého žiaka a priemerné skóre celej skupiny. Vytvorte tabuľku znázorňujúcu percento známok v skupine.

2.1 úloha (2 body).

Štyria priatelia cestujú tromi spôsobmi dopravy: vlakom, lietadlom a loďou. Nikolai preplával 150 km na parníku, 140 km precestoval vlakom a 1100 km preletel lietadlom. Vasilij preplával 200 km na parníku, 220 km precestoval vlakom a preletel 1 160 km lietadlom. Anatolij preletel 1200 km v lietadle, 110 km precestoval vlakom a 125 km preplával na parníku. Maria precestovala 130 km vlakom, letela 1500 km lietadlom a 160 km preplávala loďou.
Vytvorte tabuľku na základe údajov uvedených vyššie.

Pridajte do tabuľky stĺpec, ktorý bude zobrazovať celkový počet kilometrov, ktoré každý z chalanov prejde.

Vypočítajte celkový počet kilometrov, ktoré chalani precestovali vo vlaku, nalietali v lietadle a plavili sa na lodi (každý spôsob dopravy zvlášť).

Vypočítajte celkový počet kilometrov všetkých priateľov.

Určte maximálny a minimálny počet kilometrov prejdených priateľmi pre všetky druhy dopravy.

Určte priemerný počet kilometrov pre všetky druhy dopravy.

2.2 úloha (2 body).

Vytvorte tabuľku „Jazerá Európy“ s použitím nasledujúcich údajov pre oblasť (km2) a najhlbšiu hĺbku (m): Ladoga 17 700 a 225; Onega 9510 a 110; Kaspické more 371 000 a 995; Venern 5550 a 100; Chudskoye s Pskov 3560 a 14; Balaton 591 a 11; Ženeva 581 a 310; Vättern 1900 a 119; Kostnica 538 a 252; Mälaren 1140 a 64. Určte najväčšie a najmenšie jazero z hľadiska plochy, najhlbšie a najplytšie jazero.

2.3 úloha (2 body).

Vytvorte tabuľku „Rieky Európy“ s použitím nasledujúcich údajov o dĺžke (km) a oblasti povodia (tisíc km štvorcových): Volga 3688 a 1350; Dunaj 2850 a 817; Rýn 1330 a 224; Elba 1150 a 148; Visla 1090 a 198; Loire 1020 a 120; Ural 2530 a 220; Don 1870 a 422; Seina 780 a 79; Temža 340 a 15. Určte najdlhšie a najkratšie rieky, vypočítajte celkovú plochu povodí, priemernú dĺžku riek v európskej časti Ruska.

3 úloha (2 body).

Banka eviduje včasnosť platieb poskytnutých úverov viacerým organizáciám. Výška pôžičky a suma, ktorú organizácia už zaplatila, sú známe. Pre dlžníkov sú stanovené sankcie: ak firma splatí úver viac ako 70 percent, tak pokuta bude 10 percent z dlhu, inak bude pokuta 15 percent. Vypočítajte pokutu pre každú organizáciu, priemernú pokutu, celkovú sumu peňazí, ktorú banka dostane dodatočne. Určiť priemernú výšku pokuty pre rozpočtové organizácie.

Nájdite materiál na akúkoľvek lekciu,