OLAP ușor! Informații generale despre procesarea analitică în timp real (OLAP) Lucrări practice despre tehnologia olap în excel

1 sarcină (generală) (2 puncte).

Folosind o foaie de calcul, procesați datele folosind funcții statistice.
1. Se oferă informații despre elevii clasei (10 persoane), inclusiv notele pentru o lună la matematică. Numărați numărul de cinci, patru, doi și trei, găsiți scorul mediu al fiecărui elev și scorul mediu al întregului grup. Creați o diagramă care ilustrează procentul de note dintr-un grup.

2.1 sarcină (2 puncte).

Patru prieteni călătoresc cu trei moduri de transport: tren, avion și navă. Nikolai a navigat 150 km cu barca, a călătorit 140 km cu trenul și a zburat 1100 km cu avionul. Vasily a navigat 200 km cu barca, 220 km cu trenul și 1160 km cu avionul. Anatoly a zburat 1200 km cu avionul, 110 km cu trenul și 125 km cu barca. Maria a parcurs 130 km cu trenul, a zburat 1500 km cu avionul și a navigat 160 km cu barca.
Creați o foaie de calcul pe baza datelor de mai sus.

Adăugați o coloană la tabel care va afișa numărul total de kilometri pe care i-a parcurs fiecare dintre băieți.

Calculați numărul total de kilometri pe care copiii i-au parcurs cu trenul, au zburat cu avionul și au navigat cu barca (pe fiecare tip de transport separat).

Calculați numărul total de kilometri ai tuturor prietenilor.

Stabiliți numărul maxim și minim de kilometri parcurși de prieteni folosind toate tipurile de transport.

Determinați numărul mediu de kilometri pentru toate tipurile de transport.

2.2 sarcină (2 puncte).

Creați un tabel „Lacurile Europei” folosind următoarele date privind suprafața (km pătrați) și cea mai mare adâncime (m): Ladoga 17.700 și 225; Onega 9510 și 110; Marea Caspică 371.000 și 995; Wenern 5550 și 100; Chudskoye cu Pskovsky 3560 și 14; Balaton 591 și 11; Geneva 581 și 310; Wettern 1900 și 119; Constanța 538 și 252; Mälaren 1140 și 64. Determinați cel mai mare și cel mai mic lac din zonă, cel mai adânc și cel mai puțin adânc.

2.3 sarcină (2 puncte).

Creați un tabel „Râurile Europei” folosind următoarea lungime (km) și suprafața bazinului (mii km pătrați): Volga 3688 și 1350; Dunărea 2850 și 817; Rinul 1330 și 224; Elba 1150 și 148; Vistula 1090 și 198; Loara 1020 și 120; Ural 2530 și 220; Don 1870 și 422; Sena 780 și 79; Tamisa 340 și 15. Determinați cel mai lung și cel mai scurt râu, calculați suprafața totală a bazinelor hidrografice, lungimea medie a râurilor din partea europeană a Rusiei.

Sarcina 3 (2 puncte).

Banca înregistrează oportunitatea plăților împrumuturilor acordate mai multor organizații. Sunt cunoscute suma împrumutului și suma deja plătită de organizație. Pentru debitori se stabilesc penalități: dacă societatea a rambursat împrumutul cu mai mult de 70 la sută, amenda va fi de 10 la sută din valoarea datoriei, în caz contrar amenda va fi de 15 la sută. Calculați amenda pentru fiecare organizație, amenda medie, suma totală de bani pe care banca urmează să o primească suplimentar. Determinați amenda medie a organizațiilor bugetare.

Găsiți material pentru orice lecție,

Într-un tabel pivot standard, datele sursă sunt stocate pe hard disk-ul local. În acest fel, le puteți gestiona și reorganiza oricând, chiar și fără acces la rețea. Dar acest lucru nu se aplică în niciun caz tabelelor pivot OLAP. În tabelele pivot OLAP, memoria cache nu este niciodată stocată pe hard disk-ul local. Prin urmare, imediat după deconectarea de la rețeaua locală, tabelul pivot nu va mai funcționa. Nu veți putea muta un singur câmp în el.

Dacă mai trebuie să analizați datele OLAP după ce ați fost offline, creați un cub de date offline. Un cub de date offline este un fișier separat care este un cache de tabel pivot și stochează date OLAP care sunt vizualizate după deconectarea de la rețeaua locală. Datele OLAP copiate într-un tabel pivot pot fi tipărite, acest lucru este descris în detaliu pe site-ul http://everest.ua.

Pentru a crea un cub de date autonom, creați mai întâi un pivot tabel OLAP. Plasați cursorul în tabelul pivot și faceți clic pe butonul Instrumente OLAP din fila contextuală Instrumente, care face parte din grupul de file contextuale Instrumente PivotTable. Selectați comanda Offline OLAP (Fig. 9.8).

Orez. 9.8. Creați un cub de date offline

Pe ecran apare caseta de dialog Offline OLAP Data Cube Settings. Faceți clic pe butonul Creare fișier de date offline. Ați lansat Expertul de creare a fișierelor Data Cube. Faceți clic pe butonul Următorul pentru a continua procedura.

Mai întâi trebuie să specificați dimensiunile și nivelurile care vor fi incluse în cubul de date. În caseta de dialog, trebuie să selectați datele care vor fi importate din baza de date OLAP. Ideea este de a specifica doar acele dimensiuni care vor fi necesare după ce computerul este deconectat de la rețeaua locală. Cu cât specificați mai multe dimensiuni, cu atât va fi mai mare cubul de date autonom.

Faceți clic pe butonul Următorul pentru a trece la următoarea casetă de dialog expert. Acest lucru vă oferă posibilitatea de a specifica membri sau elemente de date care nu vor fi incluse în cub. În special, nu veți avea nevoie de măsura Vânzărilor pe Internet-Sumă extinsă, așa că caseta de selectare a acesteia va fi debifată din listă. O casetă de selectare debifată indică faptul că elementul specificat nu va fi importat și va ocupa spațiu inutil pe hard disk-ul local.

În ultimul pas, specificați locația și numele cubului de date. În cazul nostru, fișierul cub va fi numit MyOfflineCube.cub și va fi localizat în folderul Work.

Fișierele cub de date au extensia .pui

După ceva timp, Excel va salva cubul de date offline în folderul specificat. Pentru a-l testa, faceți dublu clic pe fișier, care va genera automat un registru de lucru Excel care conține un tabel pivot asociat cu cubul de date selectat. Odată creat, puteți distribui cubul de date offline tuturor utilizatorilor interesați care lucrează în modul LAN offline.

Odată conectat la rețeaua locală, puteți deschide fișierul cub de date offline și îl puteți actualiza și tabelul de date corespunzător. Principiul principal prevede că cubul de date offline este folosit doar pentru a funcționa atunci când rețeaua locală este deconectată, dar este necesar să fie actualizat după restabilirea conexiunii. Încercarea de a actualiza un cub de date offline după o eroare a conexiunii va avea ca rezultat un eșec.

Lucrul cu fișiere cub offline

Un fișier cub offline (.cub) stochează date sub forma unui cub OLAP (Online Analytical Processing). Aceste date pot reprezenta o parte a bazei de date OLAP pe serverul OLAP sau pot fi create independent de baza de date OLAP. Utilizați un fișier cub offline pentru a continua să lucrați cu rapoartele PivotTable și PivotChart atunci când serverul nu este disponibil sau când sunteți offline.

Notă de siguranță: Aveți grijă când utilizați sau distribuiți un fișier cub offline care conține date sensibile sau personale. Se recomandă să salvați datele într-un registru de lucru în loc de un fișier cub, astfel încât să puteți controla accesul la date utilizând gestionarea drepturilor. Pentru mai multe informații, consultați Gestionarea drepturilor de informare în Office.

Când lucrați cu un raport PivotTable sau PivotChart care se bazează pe datele sursă de server OLAP, puteți utiliza Offline Cube Wizard pentru a copia datele sursă într-un fișier cub offline separat de pe computer. Pentru a crea aceste fișiere offline, furnizorul dvs. de date OLAP trebuie să accepte această capacitate, cum ar fi MSOLAP de la Microsoft Services SQL Server Servicii de analiză instalate pe computer.

Notă: Crearea și utilizarea fișierelor cub offline de la Microsoft SQL Server Analysis Services se supune termenilor și licenței Instalări Microsoft SQL Server. Examinați informațiile de licențiere aplicabile pentru versiunea SQL Server.

Lucrul cu Offline Cube Wizard

Pentru a crea un fișier cub offline, puteți selecta un subset de date dintr-o bază de date OLAP utilizând Expertul cub offline și apoi salvați subsetul. Raportul nu trebuie să includă toate câmpurile incluse în fișier și nici nu trebuie să selecteze niciunul dintre ele și câmpurile de date disponibile în baza de date OLAP. Pentru a menține fișierul la minimum, puteți include numai datele pe care doriți să apară în raport. Puteți omite toate dimensiunile și, pentru majoritatea tipurilor de dimensiuni, puteți exclude și informațiile de nivel inferior și elementele de nivel superior pe care nu doriți să le afișați. Pentru toate elementele pe care le includeți, câmpurile de proprietate disponibile în baza de date pentru acele elemente sunt, de asemenea, salvate într-un fișier offline.

Preluarea datelor offline și conectarea lor înapoi

Pentru a face acest lucru, mai întâi trebuie să creați un raport PivotTable sau PivotChart care se bazează pe baza de date a serverului, apoi creați un fișier cub offline din raport. Apoi puteți comuta oricând raportul între baza de date server și fișierul offline. De exemplu, dacă utilizați un laptop pentru munca acasă și călătorii video, atunci reconectați computerul la rețea.

Mai jos sunt pașii de bază pe care trebuie să îi urmați durata de viata a bateriei cu datele și apoi transferați datele înapoi pe Internet.

Creați sau deschideți un raport PivotTable sau PivotChart bazat pe datele OLAP pe care doriți să le accesați offline.

Creați un fișier cub offline pe computer. În capitolul Crearea unui fișier cub offline dintr-o bază de date de server OLAP(mai jos în acest articol).

Deconectarea de la rețea și lucrul cu fișierul cub offline.

Conectați-vă la rețea și reconectați fișierul cub offline. Consultați secțiunea Reconectarea fișierului cub offline la baza de date a serverului OLAP(mai jos în acest articol).

Actualizați fișierul cub offline cu date noi și recreați fișierul cub offline. Consultați secțiunea actualizarea și re-crearea fișierului cub offline(mai jos în acest articol).

BLOG

Doar postări de calitate

Ce sunt tabelele pivot Excel și cuburile OLAP

Urmărește videoclipul pentru articol:

OLAP- asta e engleza. procesare analitică online, tehnologie analitică pentru prelucrarea datelor în timp real. In termeni simpli– un depozit cu date multidimensionale (Cube), și mai simplu – doar o bază de date din care poți obține date în Excel și să le analizezi folosind instrumentul Excel – Pivot Tables.

Tabele pivot este o interfață cu utilizatorul pentru afișarea datelor multidimensionale. Cu alte cuvinte, este un tip special de tabele cu care poți face aproape orice raport.

Pentru a fi clar, să comparăm un „Tabel obișnuit” cu un „Tabel Pivot”

Masa obisnuita:

Tabel rezumat:

Diferența principală Tabele pivot– aceasta este prezența unei ferestre” Lista câmpurilor din tabelul pivot", din care poți selecta câmpurile necesare și obține automat orice tabel!

Cum se utilizează

Deschideți un fișier Excel care este conectat la un cub OLAP, de exemplu „BIWEB”:

Acum, ce înseamnă asta și cum să-l folosești?

Trageți și plasați câmpurile necesare pentru a obține, de exemplu, un tabel ca acesta:

« Plusuri» vă permit să detaliați raportul. În acest exemplu, „Brand” este detaliat la „Nume abreviate”, iar „Trimest” la „Lună”, adică. Asa de:

Funcții analitice în Excel (funcții cub)

Microsoft adaugă în mod constant noi capabilități la Excel în ceea ce privește analiza și vizualizarea datelor. Lucrul cu informații în Excel poate fi reprezentat ca trei straturi relativ independente:

• date sursă organizate „corect”.
• matematica (logica) prelucrării datelor
• prezentarea datelor

Orez. 1. Analiza datelor în Excel: a) date sursă, b) măsurare în Power Pivot, c) tablou de bord; Pentru a mări imaginea, faceți clic dreapta pe ea și selectați Deschide imaginea într-o filă nouă

Descărcați nota în Format Word sau pdf, exemple în format Excel

Funcții cub și tabele pivot

Cele mai simple și în același timp foarte puternice mijloace de prezentare a datelor sunt tabelele pivot. Acestea pot fi construite din date conținute în: a) o foaie de lucru Excel, b) un cub OLAP sau c) un model de date Power Pivot. În ultimele două cazuri, pe lângă tabelul pivot, puteți folosi funcții analitice (funcții cub) pentru a genera un raport pe o foaie Excel. Tabelele pivot sunt mai simple. Funcțiile cubului sunt mai complexe, dar oferă mai multă flexibilitate, în special în proiectarea rapoartelor, motiv pentru care sunt utilizate pe scară largă în tablourile de bord.

Următoarea discuție se aplică formulelor cuburilor și tabelelor pivot bazate pe modelul Power Pivot și, în câteva cazuri, pe baza cuburilor OLAP.

O modalitate ușoară de a obține funcții cub

Când (dacă) ați început să învățați codul VBA, ați învățat că cel mai simplu mod de a ajunge la cod este să utilizați înregistrarea macro. Apoi codul poate fi editat, pot fi adăugate bucle, verificări etc. În mod similar, cel mai simplu mod de a obține un set de funcții de cub este transformarea tabelului pivot (Fig. 2). Stați pe orice celulă a tabelului pivot, accesați fila Analiză, faceți clic pe butonul Facilităţi OLAP, și apăsați Convertiți în formule.

Orez. 2. Convertiți un tabel pivot într-un set de funcții cub

Numerele vor fi salvate, iar acestea nu vor fi valori, ci formule care extrag date din modelul de date Power Pivot (Figura 3). Puteți formata tabelul rezultat. Inclusiv, puteți șterge și introduce rânduri și coloane în interiorul tabelului. Secțiunea rămâne și afectează datele din tabel. Când datele sursă sunt actualizate, se vor actualiza și numerele din tabel.

Orez. 3. Tabel bazat pe formule cub

Funcția CUBEVALUE().

Aceasta este probabil funcția principală a cuburilor. Ea este echivalentă cu zona Valori masă rotativă. CUBEVALUE preia date dintr-un cub sau model Power Pivot și le afișează în afara unui PivotTable. Aceasta înseamnă că nu sunteți limitat de PivotTable și puteți crea rapoarte cu nenumărate posibilități.

Scrierea unei formule de la zero

Nu trebuie să convertiți un tabel pivot pre-construit. Puteți scrie orice formulă de cub de la zero. De exemplu, următoarea formulă este introdusă în celula C10 (Fig. 4):

Orez. 4. Funcția CUBEVALUE() din celula C10 returnează vânzările de biciclete pentru toți anii, ca în tabelul pivot

Un mic truc. Pentru a facilita citirea formulelor cuburilor, este recomandabil ca pe fiecare linie să fie plasat un singur argument. Puteți face fereastra Excel mai mică. Pentru a face acest lucru, faceți clic pe pictogramă Rulează pe fereastră, situat în colțul din dreapta sus al ecranului. Și apoi reglați dimensiunea ferestrei pe orizontală. O opțiune alternativă este de a forța textul formulei să se mute pe o nouă linie. Pentru a face acest lucru, în bara de formule, plasați cursorul în locul în care doriți să faceți transferul și apăsați Alt+Enter.

Orez. 5. Minimizați fereastra

Sintaxa funcției CUBEVALUE().

Ajutorul Excel este complet precis și complet inutil pentru începători:

CUBEVALUE(conexiune; [expresie_element1]; [expresie_element2]; ...)

Conexiune– argument necesar; șir de text A care reprezintă numele conexiunii la cub.

Element_expression– argument optional; un șir de text reprezentând un MDX care returnează un element sau un tuplu într-un cub. În plus, „element_expression” poate fi un set definit folosind funcția CUBESET. Utilizați „element_expression” ca o felie pentru a determina partea din cub pentru care doriți să returnați o valoare agregată. Dacă nu este specificată nicio măsură în element_expression, va fi utilizată măsura implicită pentru acel cub.

Înainte de a continua să explicăm sintaxa funcției CUBEVALUE, câteva cuvinte despre cuburi, modele de date și misteriosul caravană.

Câteva informații despre cuburile OLAP și modelele de date Power Pivot

cuburi de date OLAP ( O n l ine A analitic P procesare - analiza datelor operaționale) au fost dezvoltate special pentru prelucrarea analitică și extragerea rapidă a datelor din acestea. Imaginați-vă un spațiu tridimensional în care perioadele de timp, orașele și bunurile sunt reprezentate de-a lungul axelor (Fig. 5a). Nodurile unei astfel de grile de coordonate conțin valorile diferitelor măsuri: volumul vânzărilor, profitul, costurile, numărul de unități vândute etc. Acum imaginați-vă că sunt zeci, sau chiar sute de dimensiuni... și există și un o mulțime de măsuri. Acesta va fi un cub OLAP multidimensional. Crearea, configurarea și întreținerea cuburilor OLAP este treaba specialiștilor IT.

Orez. 5a. cub 3D OLAP

Formulele analitice Excel (formule cub) extrag numele axelor (de exemplu, Timp), nume ale elementelor de pe aceste axe (august, septembrie), valori ale măsurilor la intersecția coordonatelor. Această structură permite tabelelor pivot pe bază de cuburi și formulelor cuburilor să fie atât de flexibile și să se adapteze nevoilor utilizatorilor. Tabelele pivot bazate pe foi Excel nu folosesc măsuri, deci nu sunt la fel de flexibile în scopul analizei datelor.

Power Pivot este o caracteristică Microsoft relativ nouă. Acesta este un mediu Excel încorporat și parțial independent, cu o interfață familiară. Power Pivot este mult mai puternic decât tabelele pivot standard. În același timp, dezvoltarea cuburilor în Power Pivot este relativ simplă și, cel mai important, nu necesită participarea unui specialist IT. Microsoft își implementează sloganul: „Analitice de afaceri pentru mase!” Deși modelele Power Pivot nu sunt 100% cuburi, ele pot fi considerate și cuburi (pentru mai multe informații, consultați cursul introductiv al lui Mark Moore, Power Pivot și cursul mai lung al lui Rob Colley, Formule DAX pentru Power Pivot).

Componentele principale ale unui cub sunt dimensiunile, ierarhiile, nivelurile, elementele (sau membrii; în engleză membri) și măsurile (măsurile). Măsurare - principalele caracteristici ale datelor analizate. De exemplu, categoria de produs, perioada de timp, geografia vânzărilor. O dimensiune este ceva ce putem plasa pe una dintre axele unui tabel pivot. Fiecare dimensiune, pe lângă valorile unice, include un element care realizează o agregare a tuturor elementelor acestei dimensiuni.

Măsurătorile se bazează pe ierarhie. De exemplu, o categorie de produse poate fi împărțită în subcategorii, apoi în modele și, în final, în nume de produse (Fig. 5b Ierarhia vă permite să creați date rezumative și să le analizați la diferite niveluri ale structurii). În exemplul nostru, ierarhia Categorie include 4 Nivel.

Elemente(membri individuali) sunt prezenți la toate nivelurile. De exemplu, la nivel de Categorie sunt patru elemente: Accesorii, Biciclete, Îmbrăcăminte, Componente. Alte niveluri au propriile lor elemente.

Măsuri– acestea sunt valori calculate, de exemplu, volumul vânzărilor. Măsurile în cuburi sunt stocate în propria lor dimensiune numită (vezi Figura 9 de mai jos). Măsurile nu au ierarhii. Fiecare măsură calculează și stochează o valoare pentru toate dimensiunile și toate elementele și este tăiată în funcție de elementele de dimensiune pe care le plasăm pe axă. De asemenea, ei spun ce coordonate vom seta sau ce context de filtru vom seta. De exemplu, în Fig. 5a, în fiecare cub mic se calculează aceeași măsură - Profit. Iar valoarea returnată de măsură depinde de coordonate. În dreapta, Figura 5a arată că Profitul (în trei coordonate) la Moscova în octombrie pe mere = 63.000 de ruble. O măsură poate fi interpretată și ca una dintre dimensiuni. De exemplu, în Fig. 5a în loc de o axă Bunuri, plasați axa Măsuri cu elemente Volumul vânzărilor, Profit, Unități vândute. Apoi, fiecare celulă va avea o valoare, de exemplu, Moscova, septembrie, volumul vânzărilor.

Cortegiu– mai multe elemente de dimensiuni diferite care precizează coordonatele de-a lungul axelor cubului în care calculăm măsura. De exemplu, în Fig. 5a Cortegiu= Moscova, octombrie, mere. De asemenea, un tuplu acceptabil este Perm, mere. Altul – mere, august. Dimensiunile care nu sunt incluse în tuplu sunt prezente implicit în acesta și sunt reprezentate de membrul implicit. Astfel, o celulă a unui spațiu multidimensional este întotdeauna definită de un set complet de coordonate, chiar dacă unele dintre ele sunt omise din tuplu. Nu puteți include două elemente de aceeași dimensiune într-un tuplu, sintaxa nu va permite acest lucru. De exemplu, un tuplu invalid Moscova și Perm, mere. Pentru a implementa o astfel de expresie multidimensională, veți avea nevoie de un set de două tupluri: Moscova și mere + Perm și mere.

Set de elemente– mai multe elemente de o dimensiune. De exemplu, mere și pere. Set de tupluri– mai multe tupluri, fiecare dintre ele constând din aceleași dimensiuni în aceeași succesiune. De exemplu, un set de două tuple: Moscova, mere și Perm, banane.

Completare automată pentru a ajuta

Să revenim la sintaxa funcției CUBEVALUE. Să folosim completarea automată. Începeți să introduceți o formulă într-o celulă:

Excel va oferi toate conexiunile disponibile în registrul de lucru Excel:

Orez. 6. Conexiunea la modelul de date Power Pivot se numește întotdeauna ThisWorkbookDataModel

Orez. 7. Conexiuni la cuburi

Să continuăm introducerea formulei (în cazul nostru pentru modelul de date):

Completarea automată va sugera toate tabelele și măsurile disponibile în modelul de date:

Orez. 8. Elemente disponibile ale primului nivel - nume de tabel și un set de măsuri (evidențiate)

Selectați o pictogramă Măsuri. Faceți un punct:

CUBEVALUE(" Acest model de date caiet de lucru " ; " .

Completarea automată va sugera toate măsurile disponibile:

Orez. 9. Elemente disponibile de al doilea nivel în setul de măsuri

Selectați o măsură. Adăugați ghilimele, o paranteză de închidere și apăsați Enter.

CUBEVALUE(" ThisWorkbookDataModel " ; " . " )

Orez. 10. Formula CUBE VALUE într-o celulă Excel

În mod similar, puteți adăuga un al treilea argument la formulă:

VBA în Excel Excel.Obiect PivotTable și lucrul cu tabele pivot și cuburi OLAP în Excel

10.8 Lucrul cu tabele pivot (obiect PivotTable)

Obiect Excel.PivotTable, lucru programatic cu tabele pivot și cuburi OLAP în Excel folosind VBA, obiect PivotCache, crearea unui aspect de tabel pivot

În timpul funcționării majorității întreprinderilor, se acumulează așa-numitele date brute despre activități. De exemplu, pentru o întreprindere comercială, datele privind vânzările de bunuri pot fi acumulate - pentru fiecare achiziție separat, pentru întreprinderi comunicatii celulare- statistici de încărcare stații de bazăși așa mai departe. Foarte des, managementul întreprinderii are nevoie de informații analitice care sunt generate pe baza informațiilor brute - de exemplu, pentru a calcula contribuția fiecărui tip de produs la veniturile întreprinderii sau calitatea serviciului în zona unei stație dată. Este foarte dificil să extragi astfel de informații din informații brute: trebuie să rulezi interogări SQL foarte complexe care durează mult timp pentru a se executa și adesea interferează cu munca curentă. Prin urmare, din ce în ce mai mult, datele brute sunt acum consolidate mai întâi într-un depozit de date de arhivă - Depozitul de date, iar apoi în cuburi OLAP, care sunt foarte convenabile pentru analiza interactivă. Cel mai simplu mod de a gândi cuburile OLAP este ca tabele multidimensionale, în care, în loc de cele două dimensiuni standard (coloane și rânduri, ca în tabelele obișnuite), pot exista o mulțime de dimensiuni. Termenul „secțional” este folosit în mod obișnuit pentru a descrie măsurătorile într-un cub. De exemplu, departamentul de marketing poate avea nevoie de informații în funcție de timp, regiune, tip de produs, canal de vânzare etc. Folosind cuburi (spre deosebire de interogările standard SQL), este foarte ușor să obțineți răspunsuri la întrebări precum „câte produse de acest tip au fost vândute în trimestrul IV al anului trecut în regiunea Nord-Vest prin distribuitori regionali.

Desigur, astfel de cuburi nu pot fi create în baze de date convenționale. Lucrul cu cuburi OLAP necesită produse software specializate. SQL Server vine cu o bază de date OLAP de la Microsoft numită Analysis Services. Există soluții OLAP de la Oracle, IBM, Sybase etc.

Pentru a lucra cu astfel de cuburi, Excel are un client încorporat. În rusă se numește Masă rotativă(pe ecranul grafic este accesibil prin meniu Date -> Masă rotativă), iar în engleză - Masă rotativă. În consecință, obiectul pe care îl reprezintă acest client se numește PivotTable. Trebuie remarcat faptul că poate funcționa nu numai cu cuburi OLAP, ci și cu date obișnuite din tabele sau baze de date Excel, dar multe capacități se pierd.

PivotTable și PivotTable sunt produse software de la Panorama Software care au fost achiziționate de Microsoft și integrate în Excel. Prin urmare, lucrul cu obiectul PivotTable este oarecum diferit de lucrul cu alte obiecte Excel. Să-ți dai seama ce trebuie făcut este adesea dificil. Prin urmare, este recomandat să utilizați în mod activ reportofonul macro pentru a primi indicii. În același timp, atunci când lucrează cu tabele pivot, utilizatorii trebuie adesea să efectueze aceleași operațiuni repetitive, astfel încât automatizarea este necesară în multe situații.

Cum arată programatic lucrul cu un tabel pivot?

Primul lucru pe care trebuie să-l facem este să creăm un obiect PivotCache care va reprezenta un set de înregistrări preluate din sursa OLAP. Foarte aproximativ, acest obiect PivotCache poate fi comparat cu un QueryTable. Puteți utiliza doar un obiect PivotCache pentru fiecare obiect PivotTable. Un obiect PivotCache este creat folosind metoda Add() a colecției PivotCaches:

Dim PC1 ca PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches este o colecție standard, iar printre metodele care merită o atenție detaliată, numai metoda Add() poate fi numită în ea. Această metodă ia doi parametri:

• SourceType- obligatoriu, definește tipul sursei de date pentru tabelul pivot. Puteți specifica crearea unui Tabel Pivot pe baza unui interval în Excel, a datelor dintr-o bază de date, a unei surse de date externe, a unui alt Tabel Pivot etc. În practică, de obicei are sens să utilizați OLAP numai atunci când există o mulțime de date - în consecință, aveți nevoie de stocare externă specializată (de exemplu, Microsoft Analysis Services). În această situație, este selectată valoarea xlExternal.
• Date sursă- obligatoriu în toate cazurile, cu excepția cazului în care valoarea primului parametru este xlExternal. De fapt, definește intervalul de date pe baza căruia va fi creat PivotTable. De obicei ia un obiect Range.

Următoarea sarcină este să configurați setările obiectului PivotCache. După cum sa menționat deja, acest obiect este foarte asemănător cu QueryTable, iar setul său de proprietăți și metode este foarte asemănător. Unele dintre cele mai importante proprietăți și metode:

• ADOConnection- capacitatea de a returna un obiect ADO Connection care este creat automat pentru a se conecta la o sursă de date externă. Este folosit pentru setari aditionale proprietățile conexiunii.
• Conexiune- funcționează exact la fel ca proprietatea obiectului QueryTable cu același nume. Poate accepta un șir de conexiune, un obiect Recordset gata făcut, fisier text,Interogare web. fișier Microsoft Interogare. Cel mai adesea, atunci când lucrați cu OLAP, șirul de conexiune este scris direct (din moment ce obținerea unui obiect Recordset, de exemplu, pentru a schimba date, nu are prea mult sens - sursele de date OLAP sunt aproape întotdeauna doar pentru citire). De exemplu, setarea acestei proprietăți pentru a se conecta la baza de date Foodmart (baza de date de exemplu de la Analysis Services) pe serverul LONDRA ar putea arăta astfel:

PC1.Conexiune = "OLEDB; Furnizor=MSOLAP.2; Sursa datelor=LONDRA1; Catalog inițial = FoodMart 2000"

• proprietăți CommandTypeȘi CommandText ele descriu, de asemenea, tipul de comandă care este trimisă către serverul bazei de date și textul comenzii în sine. De exemplu, pentru a accesa cubul de vânzări și a-l introduce în întregime în memoria cache a clientului, puteți utiliza cod precum

• proprietate LocalConnection vă permite să vă conectați la un cub local (fișier *.cub) creat folosind Excel. Desigur, nu se recomandă utilizarea unor astfel de fișiere pentru a lucra cu volume de date „de producție” - numai în scopul creării de machete etc.
• proprietate MemoryUsed returneaza cantitatea memorie cu acces aleator, folosit de PivotCache. Dacă un PivotTable bazat pe acest PivotCache nu a fost încă creat și deschis, acesta returnează 0. Poate fi folosit pentru a verifica dacă aplicația dvs. va rula pe clienți slabi.
• proprietate OLAP returnează True dacă PivotCache este conectat la serverul OLAP.
• OptimizeCache- capacitatea de a optimiza structura cache-ului. Descărcarea inițială a datelor va dura mai mult, dar apoi viteza poate crește. Nu funcționează pentru sursele OLE DB.

Proprietățile rămase ale obiectului PivotCache sunt aceleași cu cele ale obiectului QueryTable și, prin urmare, nu vor fi discutate aici.

Metoda principală a obiectului PivotCache este metoda CreatePivotTable(). Folosind această metodă, se realizează următoarea etapă - crearea unui tabel pivot (obiect PivotTable). Această metodă ia patru parametri:

• TableDestination- singurul parametru necesar. Acceptă un obiect Range în colțul din stânga sus al căruia va fi plasat tabelul pivot.
• TableName- numele tabelului pivot. Dacă nu este specificat, numele vizualizării „PivotTable1” va fi generat automat.
• CitițiDate- dacă este setat la True, atunci întregul conținut al cubului va fi plasat automat în cache. Trebuie să fiți foarte atenți cu acest parametru, deoarece utilizarea lui incorect poate crește dramatic sarcina asupra clientului.
• DefaultVersion- această proprietate nu este de obicei specificată. Vă permite să determinați versiunea tabelului pivot care este creat. În mod implicit, este utilizată cea mai recentă versiune.

Crearea unui tabel pivot în prima celulă a primei foi de lucru ar putea arăta astfel:

PC1.CreatePivotTable Range ("A1")

Am creat un tabel pivot, dar imediat după creare este gol. Oferă patru zone în care puteți plasa câmpuri din sursă (pe ecranul grafic toate acestea pot fi configurate fie folosind fereastra Lista câmpurilor din tabelul pivot- se deschide automat sau folosind un buton Aspect pe ultimul ecran al expertului PivotTable):

• zona coloanei- conține acele dimensiuni („secțiune” în care vor fi analizate datele) ai căror membri sunt mai mici;
• zona liniei- acele dimensiuni care au mai mulți membri;
• zona paginii- acele măsurători pentru care trebuie doar să filtrați (de exemplu, afișați date doar pentru așa și pentru o regiune sau doar pentru un anume și anume);
• zona de date- de fapt, partea centrală a mesei. Acele date numerice (de exemplu, valoarea vânzărilor) pe care le analizăm.

Este dificil să te bazezi pe utilizator pentru a plasa corect elementele în toate cele patru zone. În plus, acest lucru poate dura ceva timp. Prin urmare, este adesea necesar să aranjați datele într-un tabel pivot în mod programatic. Această operație este efectuată folosind obiectul CubeField. Proprietatea principală a acestui obiect este Orientarea, aceasta determină unde va fi amplasat acest sau acel câmp. De exemplu, să punem dimensiunea Clienți în zona coloanelor:

PT1.CubeFields("").Orientation = xlColumnField

Apoi - măsurarea timpului în zona liniei:

PT1.CubeFields("").Orientation = xlRowField

Apoi - dimensiunea Produs în zona paginii:

PT1.CubeFields("").Orientation = xlPageField

Și, în sfârșit, indicatorul (date numerice pentru analiză) Vânzări unitare:

PT1.CubeFields(“.”).Orientation = xlDataField

Instrumentele client OLAP sunt aplicații care calculează date agregate (sume, medii, valori maxime sau minime) și le afișează, în timp ce datele agregate în sine sunt conținute într-un cache în spațiul de adrese al unui astfel de instrument OLAP.

Dacă datele sursă sunt conținute într-un SGBD desktop, calculul datelor agregate este efectuat de instrumentul OLAP însuși. Dacă sursa datelor inițiale este un SGBD de server, multe dintre instrumentele OLAP client trimit interogări SQL care conțin instrucțiunea GROUP BY către server și, ca rezultat, primesc date agregate calculate pe server.

De regulă, funcționalitatea OLAP este implementată în instrumentele de prelucrare a datelor statistice (de la produse din această clasă la piata ruseasca produsele StatSoft și SPSS sunt utilizate pe scară largă) și în unele foi de calcul. În special, Microsoft Excel are instrumente de analiză multidimensională. Cu acest produs, puteți crea și salva ca fișier un mic cub OLAP multidimensional local și puteți afișa secțiuni transversale bidimensionale sau tridimensionale ale acestuia.

Complementele Microsoft Office Data Mining sunt un set de caracteristici care oferă acces la capacitățile de extragere a datelor ale aplicațiilor Microsoft Office, permițând astfel analiza predictivă în întreaga calculator local. Datorită faptului că serviciile sunt integrate în platformele Microsoft Cu algoritmii de extragere și procesare a datelor SQL Server disponibili din mediul aplicației Microsoft Office, utilizatorii de afaceri pot extrage cu ușurință informații valoroase din seturi complexe de date cu doar câteva clicuri. Suplimentele de extragere și manipulare a datelor Office permit utilizatorilor finali să efectueze analize direct în Microsoft Excel și Microsoft Visio.

Microsoft Office 2007 include trei componente OLAP separate:

1. Data Mining Client pentru Excel vă permite să creați și să gestionați proiecte de data mining bazate pe SSAS din Excel 2007;
2. Instrumentele de analiză a tabelelor pentru Excel vă permit să utilizați capacitățile de extragere și procesare a informațiilor încorporate ale SSAS pentru a analiza datele stocate în tabelele Excel;
3. Șabloanele Visio Data Mining vă permit să vizualizați arbori de decizie, arbori de regresie, diagrame de cluster și rețele de dependență în diagramele Visio.

Prima interfață pentru tabele pivot, numită și rapoarte pivot, a fost inclusă în Excel încă din 1993 (Excel versiunea 5.0). În ciuda numeroaselor utile funcţionalitate, practic nu este folosit în muncă de majoritatea utilizatorilor Excel. Chiar și utilizatorii experimentați înțeleg adesea prin termenul „raport rezumat” ceva construit folosind formule complexe. Să încercăm să popularizăm utilizarea tabelelor pivot în munca de zi cu zi a economiștilor. Articolul discută baza teoretică pentru crearea rapoartelor rezumative, oferă recomandări practice pentru utilizarea acestora și oferă, de asemenea, un exemplu de accesare a datelor bazate pe mai multe tabele.

Termenii de analiză multivariată a datelor

Majoritatea economiștilor au auzit termenii „date multidimensionale”, „cub virtual”, „tehnologii OLAP”, etc. Dar o conversație detaliată, de obicei, se dovedește că aproape toată lumea nu are idee despre ce vorbim. Adică oamenii înseamnă ceva complex și de obicei care nu are legătură cu activitățile lor zilnice. De fapt, acest lucru nu este adevărat.

Date multidimensionale, măsurători

Este sigur să spunem că economiștii întâlnesc aproape constant date multidimensionale, dar încearcă să le prezinte într-o formă predefinită folosind foi de calcul. Multidimensionalitatea înseamnă aici capacitatea de a introduce, vizualiza sau analiza aceleași informații cu modificări aspect, folosind diverse grupări și sortarea datelor. De exemplu, un plan de vânzări poate fi analizat folosind următoarele criterii:

• tipuri sau grupuri de bunuri;
• mărci sau categorii de produse;
• perioade (lună, trimestru, an);
• cumpărători sau grupuri de cumpărători;
• regiuni de vânzare
• și așa mai departe.

Fiecare dintre criteriile de mai sus se numește „dimensiune” în ceea ce privește analiza multidimensională a datelor. Putem spune că o măsurătoare caracterizează informația în funcție de un set specific de valori. Un tip special de măsurare a informațiilor multidimensionale este „date”. În exemplul nostru, datele planului de vânzări ar putea fi:

• volumul vânzărilor;
• Prețul de vânzare;
• reducere individuală
• și așa mai departe.

În teorie, datele pot fi, de asemenea, o dimensiune standard a informațiilor multidimensionale (de exemplu, puteți grupa datele după prețul de vânzare), dar datele sunt, de obicei, încă un tip special de valoare.

Astfel, putem spune că în munca practică economiștii folosesc două tipuri de informații: date multidimensionale ( numere reale și planificate care au multe caracteristici) și cărți de referință (caracteristici sau măsurători de date).

OLAP

Abrevierea OLAP (prelucrare analitică online) înseamnă literal „prelucrare analitică în timp real”. Definiția nu este foarte specifică aproape orice raport al oricărui produs software. În sensul său, OLAP presupune o tehnologie de lucru cu rapoarte speciale, inclusiv software, pentru obținerea și analiza datelor structurate multidimensionale. Unul dintre produsele software populare care implementează tehnologiile OLAP este SQL Server Analysis Server. Unii chiar îl consideră în mod eronat singurul reprezentant al implementării software a acestui concept.

Cub de date virtual

„Cub virtual” (cub multidimensional, cub OLAP) este un termen special propus de unii furnizori de specialitate software. Sistemele OLAP de obicei pregătesc și stochează datele în propriile structuri, iar interfețele speciale de analiză (cum ar fi rapoartele rezumative Excel) accesează datele din aceste cuburi virtuale. Mai mult, utilizarea unei astfel de stocări dedicate nu este deloc necesară pentru procesarea informațiilor multidimensionale. În general, cub virtual– aceasta este o serie de date multidimensionale special optimizate care sunt utilizate pentru a crea rapoarte rezumative. Poate fi obținut fie prin software specializat, fie prin simplu acces la tabelele bazei de date sau orice altă sursă, cum ar fi o foaie de calcul Excel.

Masă rotativă

Pivot Table este o interfață cu utilizatorul pentru afișarea datelor multidimensionale. Folosind această interfață, puteți grupa, sorta, filtra și rearanja datele pentru a obține diferite mostre analitice. Actualizarea raportului se face prin mijloace simple interfața cu utilizatorul, datele sunt agregate automat conform regulilor specificate, fără a necesita introducerea suplimentară sau repetată a vreunei informații. Interfața tabelului pivot din Excel este poate cea mai populară produs software pentru lucrul cu date multidimensionale. Acceptă atât surse de date externe (cuburi OLAP și baze de date relaționale), cât și intervale de foi de calcul interne ca sursă de date. Începând cu versiunea 2000 (9.0), Excel acceptă și o formă grafică de afișare a datelor multidimensionale - o diagramă pivot.

Interfața Excel PivotTable vă permite să aranjați dimensiunile datelor multidimensionale într-o zonă a foii de lucru. Pentru simplitate, vă puteți gândi la un tabel pivot ca la un raport situat deasupra unui interval de celule (de fapt, există o anumită legare a formatelor de celule de câmpurile tabelului pivot). Un tabel pivot Excel are patru zone pentru a afișa informații: filtru, coloane, rânduri și date. Dimensiunile datelor sunt numite Câmpuri din tabel pivot. Aceste câmpuri au propriile proprietăți și format de afișare.

Încă o dată, aș dori să vă atrag atenția asupra faptului că tabelul pivot Excel este destinat exclusiv analizei datelor fără posibilitatea de a edita informațiile. Un sens mai apropiat ar fi utilizarea pe scară largă a termenului „raport pivot”, și exact așa a fost numită această interfață până în 2000. Dar din anumite motive, în versiunile ulterioare dezvoltatorii l-au abandonat.

Editarea tabelelor pivot

Prin definiția sa, tehnologia OLAP, în principiu, nu implică capacitatea de a schimba datele sursă atunci când lucrezi cu rapoarte. Cu toate acestea, pe piață a apărut o întreagă clasă de sisteme software care implementează capabilitățile atât de analiză, cât și de editare directă a datelor în tabele multidimensionale. Practic, astfel de sisteme sunt axate pe rezolvarea problemelor de bugetare.

Folosind instrumentele de automatizare încorporate ale Excel, puteți rezolva multe probleme nestandard. Un exemplu de implementare de editare pentru tabelele pivot Datele din foile de lucru bazate pe Excel pot fi găsite pe site-ul nostru web.

Pregătirea datelor multidimensionale

Să ajungem la utilizarea practică a tabelelor pivot. Să încercăm să analizăm datele vânzărilor în diverse direcții. Fişier pivottableexample.xls este format din mai multe foi. Foaie Exemplu conține informații de bază despre vânzări pentru o anumită perioadă. Pentru a simplifica exemplul, vom analiza un singur indicator numeric – volumul vânzărilor în kg. Dimensiunile cheie ale datelor sunt: ​​produs, cumpărător și transportator (companie de transport). În plus, există mai multe dimensiuni de date suplimentare care sunt caracteristici ale produsului: tip, marcă, categorie, furnizor și client: tip. Aceste date sunt colectate pe foaia Directoare. În practică, pot exista mult mai multe astfel de măsurători.

Foaie Exemplu conţine remediu standard analiza datelor – autofiltru. Privind exemplul de completare a tabelului, este evident că datele vânzărilor după dată (sunt aranjate în coloane) se pretează unei analize normale. În plus, folosind un filtru automat, puteți încerca să rezumați datele pe baza combinațiilor de unul sau mai multe criterii cheie. Nu există absolut nicio informație despre mărci, categorii și tipuri. Nu există nicio modalitate de a grupa datele cu însumare automată după o anumită cheie (de exemplu, de către clienți). În plus, setul de date este fix și nu va fi posibilă vizualizarea informațiilor rezumative pentru o anumită perioadă, de exemplu, 3 zile, folosind mijloace automate.

În general, prezența unei locații de date predefinite în acest exemplu este principalul dezavantaj al tabelului. Prin aranjarea datelor pe coloane, am predeterminat dimensiunea acestui tabel, privându-ne astfel de posibilitatea de a folosi analiza folosind tabele pivot.

În primul rând, trebuie să scăpăm de acest neajuns - de exemplu. eliminați locația predefinită a unuia dintre dimensiunile datelor sursă. Exemplu de tabel corect - foaie Vânzări.

Tabelul are forma unui jurnal de introducere a informațiilor. Aici, data este o dimensiune egală a datelor. De asemenea, trebuie remarcat faptul că pentru analiza ulterioară în tabelele pivot, poziția relativă a rândurilor unul față de celălalt (cu alte cuvinte, sortarea) este complet indiferentă. Înregistrările din bazele de date relaționale au aceste proprietăți. Interfața tabelelor pivot vizează în primul rând analiza unor volume mari de baze de date. Prin urmare, trebuie să respectați aceste reguli atunci când lucrați cu o sursă de date sub formă de intervale de celule. În același timp, nimeni nu interzice utilizarea instrumentelor de interfață Excel în munca lor - tabelele pivot analizează numai datele, iar formatarea, filtrele, grupările și sortarea celulelor sursă pot fi arbitrare.

De la filtru automat la raport rezumat

Teoretic, este deja posibil să se efectueze analize în trei dimensiuni folosind datele din Fișa de vânzări: mărfuri, clienți și transportatori. Nu există date despre proprietățile produselor și ale clienților pe această fișă, ceea ce, în consecință, nu va permite ca acestea să fie afișate în tabelul rezumativ. În modul normal de creare a unui tabel pivot pentru datele sursă, Excel nu vă permite să legați datele din mai multe tabele după anumite câmpuri. Puteți ocoli această limitare folosind software - vezi. exemplu-adăugare la acest articol de pe site-ul nostru. Pentru a nu recurge la metode software de prelucrare a informațiilor (mai ales că nu sunt universale), ar trebui să adăugați caracteristici suplimentare direct în formularul de înscriere în jurnal - consultați fișa SalesAnalysis.

Utilizarea funcțiilor VLOOKUP facilitează completarea datelor originale cu caracteristici lipsă. Acum, folosind AutoFilter, puteți analiza datele în diferite dimensiuni. Dar problema grupărilor rămâne nerezolvată. De exemplu, urmărirea sumei numai după marcă pentru anumite date este destul de problematică. Dacă te limitezi formule Excel, atunci trebuie să construiți mostre suplimentare folosind funcția SUMIF.

Acum să vedem ce capabilități oferă interfața tabelului pivot. Pe o foaie RezumatAnaliză a construit mai multe rapoarte bazate pe o serie de celule cu date din foi Analiza vânzărilor.

Primul tabel de analiză a fost construit prin interfața Excel 2007 Ribbon\Insert\PivotTable(în meniul Excel 2000-2003 Date\PivotTable).

Al doilea și al treilea tabel au fost create prin copiere și configurare ulterioară. Sursa de date pentru toate tabelele este aceeași. Puteți verifica acest lucru modificând datele sursă, apoi trebuie să actualizați datele raportului rezumat.

Din punctul nostru de vedere, avantajele în vizibilitatea informațiilor sunt evidente. Puteți schimba filtre, coloane și rânduri și puteți ascunde anumite grupuri de valori de orice dimensiune, utilizați manual drag and drop și sortarea automată.

Proprietăți și formatare

Pe lângă afișarea directă a datelor, există o gamă largă de opțiuni pentru afișarea aspectului tabelelor pivot. Puteți ascunde datele inutile folosind filtre. Pentru un singur element sau câmp este mai ușor să utilizați elementul din meniul contextual Șterge(în versiunea 2000-2003 Ascunde).

De asemenea, este recomandabil să setați afișarea altor elemente ale tabelului pivot nu prin formatarea celulelor, ci prin setarea unui câmp sau element al tabelului pivot. Pentru a face acest lucru, trebuie să mutați indicatorul mouse-ului la elementul dorit, să așteptați să apară o formă specială de cursor (sub forma unei săgeți), apoi să selectați elementul selectat cu un singur clic. După selecție, puteți schimba vizualizarea prin panglică, meniul contextual sau puteți apela dialogul format standard de celulă:

În plus, Excel 2007 a introdus multe stiluri de afișare PivotTable predefinite:

Observați că filtrele de control și zonele de glisare sunt active în diagramă.

Acces la date externe

După cum sa menționat deja, poate cel mai mare efect din utilizarea tabelelor pivot poate fi obținut la accesarea datelor surse externe– Cuburi OLAP și interogări baze de date. Astfel de surse stochează de obicei cantități mari de informații și au, de asemenea, o structură relațională predefinită, care facilitează definirea dimensiunilor datelor multidimensionale (câmpuri de tabel pivot).

Excel acceptă multe tipuri de surse de date externe:

Cel mai mare efect din utilizarea surselor externe de informații poate fi obținut prin utilizarea instrumentelor de automatizare (programe VBA) atât pentru obținerea datelor, cât și pentru preprocesarea lor în tabele pivot.