Pivot de putere. Utilizarea PowerPivot în Microsoft Excel. Creați o coloană calculată

Excel Power Pivot este un instrument care vă permite să creați sisteme de business intelligence. În acest articol ne vom uita la metodologia de determinare a aceluiași indicator din domeniul raportării manageriale - mai întâi pe baza formulelor Excel, apoi folosind Excel Power Pivot pentru a evalua toate capacitățile și avantajele acestuia.

Ce este Power Pivot

Excel Power Pivot este un instrument care permite utilizatorilor să-și creeze propriile sisteme de business intelligence folosind Excel - bazat pe baze de date tabelare.

De regulă, tabelele pivot, integrate inițial în Excel, nu necesită cunoștințe speciale și sunt de neînlocuit dacă aveți nevoie de analize vizuale în Excel. Cărțile vă permit să stocați date într-un tabel separat, dacă doriți. În plus, Excel acceptă operatorii pentru lucrul cu matrice de date - un set de instrumente foarte util pentru analiză.

Suplimentul Excel Power Pivot funcționează numai în Office 2010 și versiuni ulterioare și are o serie de caracteristici:

• efectuează toate calculele direct în memoria RAM a computerului, ceea ce permite o performanță ridicată a sistemului, precum și capacitatea de a procesa cantități mari de date (dimensiunea tabelelor sursă poate ajunge la milioane de rânduri);
• vă permite să creați tabele pivot pe baza dvs. Valoarea aplicativă a Excel PowerPivot pentru finanțatori și analiști constă în faptul că, cu ajutorul add-in-ului, acum puteți procesa independent astfel de volume de informații care anterior necesitau utilizarea unui software specializat (DBMS) și implicarea unor specialiști IT calificați. ;
• conține un limbaj de expresie de analiză a datelor (Data Analysis eXpression, prescurtat ca DAX), conceput pentru dezvoltarea de noi reguli de logica de afaceri. Mai simplu spus, poate fi considerată o versiune avansată a formulelor matrice. Formulele limbajului DAX au fost concepute special pentru a fi cât mai aproape posibil de sintaxa Excel. Deși sunt similare cu funcțiile standard ale foii de lucru Excel, formulele DAX sunt scrise numai în shell-ul Power Pivot. Limbajul DAX are o gamă largă de capabilități, iar informații despre diferite moduri de utilizare sunt disponibile pe Internet.

După cum arată practica, popularitatea unei anumite tehnologii depinde adesea nu de nivelul și varietatea problemelor rezolvate cu ajutorul ei, ci de cât de eficient și simplu poți trece la ea folosind cunoștințele și experiența ta. Acest articol discută scheme pentru crearea aceluiași indicator din domeniul raportării manageriale - mai întâi pe baza formulelor Excel, apoi folosind comenzi DAX. Acest lucru se va asigura că lucrul cu Excel Power Pivot este la fel ca lucrul cu foi de calcul Excel obișnuite.

De exemplu, să alegem indicatorul „Nivelul venit lunar al clienților”, care ne permite să clasificăm contrapartidele companiei în funcție de gradul de profitabilitate. Cu ajutorul acestuia, devine clar ce cotă din veniturile totale provine de la clienți de diferite tipuri, distribuția lor cantitativă în grupuri, dinamica schimbărilor în baza de clienți etc. Cu toate acestea, calcularea unui astfel de indicator este plină de anumite dificultăți:

1. Într-o singură perioadă calendaristică, un client poate folosi mai multe servicii deodată (achiziționează mai multe bunuri) - prin urmare, pentru a calcula nivelul total al profitabilității, este necesar să se însumeze veniturile primite din toate serviciile prestate clientului în perioada analizată;
2. În funcție de valoarea veniturilor calculate, clientul trebuie să fie repartizat într-unul dintre grupurile prestabilite.

Ținând cont de particularitățile condițiilor de calcul, indicatorul „Nivel de profitabilitate” va fi un bun exemplu pentru studierea logicii de execuție a comenzilor care stă la baza limbajului DAX.

Referinţă

Folosind formule matrice

Să ne amintim că, pe lângă funcțiile obișnuite ale foii de lucru (o matrice de argumente de intrare - o valoare de ieșire, care este scrisă într-o celulă de tabel), există o clasă separată de funcții numite formule matrice (o matrice de argumente de intrare - o matrice a valorilor de ieşire). Astfel de operatori sunt introduși imediat pentru o serie de celule. În acest caz, valoarea unui anumit element depinde nu numai de componenta comună tuturor (o singură formulă de calcul), ci și de locul său în matrice (așa-numitul context de execuție). Contextul de execuție este unul dintre pietrele de temelie pe care se sprijină logica calculelor analitice. Prin urmare, mai jos vom explica pe scurt care este principiul funcționării sale în Excel.

Să ne uităm la expresie (vezi Fig. 1). Parametrii săi de intrare sunt două intervale de celule: „Rând”, evidențiat în galben și „Coloană” cu umplere albastră. În zona limitată de aceste intervale, se introduce o formulă matrice de următoarea formă: (=Row Column).

Această formulă este o reprezentare compactă a operației de adăugare a perechilor de elemente din tablourile originale. După cum se poate vedea din figură, are aceeași ortografie pentru toate elementele de gamă din domeniu. În acest caz, valorile din celulele tabelului rezultat diferă unele de altele, ceea ce este explicat de contextul de execuție. De exemplu, celula E4 evidențiată în figură este situată în a treia coloană și în al doilea rând al matricei, care corespunde celui de-al doilea element al matricei Row și celui de-al treilea element al matricei Coloane.

Modul descris de organizare a calculelor nu pare destul de familiar, deoarece în formulă nu există nicio indicație explicită a celulelor care trebuie selectate pentru adăugare. Prin urmare, stăpânirea acestei tehnici necesită ceva timp. Dar după ce a învățat această tehnică, utilizatorul va putea crea expresii pentru un mediu OLAP multidimensional. Astfel, capacitățile de bază ale programului Excel sunt extinse prin adăugarea de calcule analitice bazate pe sintaxa formulelor foilor de lucru.

Lucrul cu formule standard Excel

Să presupunem că avem la dispoziție un raport de venituri al companiei, detaliat de trei analiști (contrapărți, tipuri de servicii și perioade calendaristice).

În Figura 2, datele sursă sunt prezentate în coloanele „Companie”, „Servicii”, „Perioada”, „Venituri”. Dorim să creăm două noi coloane calculate „Suma_Venit”, în care se calculează venitul total pentru un client într-o anumită perioadă, precum și „Nivel_Venit”, care, în funcție de nivelul „Suma_Venit”, determină grupa de venituri (venituri). nivelurile sunt indicate în partea dreaptă a Fig. 2).

Pentru comoditatea și claritatea calculelor ulterioare, transformăm imediat datele sursă în modul „Tabele”. Pentru a face acest lucru, vom folosi comanda corespunzătoare din fila „Insert” și îi vom da propriul nume „Fact_Table”.

Scopul nostru. Pentru fiecare rând al tabelului de fapte, calculați venitul total care corespunde clientului și lunii. De exemplu, „Clientul A” în februarie 2013 a fost furnizat cu trei servicii în valoare de 15, 15 și 25 de unități. Prin urmare, în fiecare dintre rândurile care se referă la o anumită perioadă, trebuie introdusă aceeași valoare - 55 de unități (15 + 15 + 25). Această operație se realizează în două etape.

Etapa 1. Dintre toate rândurile tabelului de fapte, sunt filtrate cele ale căror valori ale atributelor „Companie” și „Perioadă” se potrivesc cu rândul curent.

Etapa 2. Valorile atributului „Venit” din setul filtrat rezultat sunt însumate.

Funcția SUMIFS este potrivită pentru această sarcină, permițându-vă să însumați intervalele în mai multe condiții simultan. Întrebarea principală este cum să introduceți această formulă într-o coloană calculată. La urma urmei, în tabele, orice formulă trebuie specificată pentru toate rândurile simultan. Trebuie să definim doar unul, dar astfel încât contextul său de execuție să se modifice în funcție de parametrii liniei active.

Să introducem expresia 1 în oricare dintre rândurile coloanei „Income_Amount” Doar apăsați Enter, după care se va încadra în toate rândurile tabelului.

Expresia 1:
SUMIFS([Venituri]; [Companie]; Tabel_fact[[#Acest rând];[Companie]]; [Perioadă]; Tabel_fact[[#Acest rând]; [Perioadă]])

Să vedem cum funcționează formula. De fiecare dată când apelați (în fiecare rând) funcția SUMIFS, întregul tabel de fapte (coloana sa „Venituri”) este utilizat simultan. Dar la fiecare iterație, sunt însumate doar acele rânduri care corespund valorilor atributelor rândului curent. Pentru a face acest lucru, la formula a fost adăugat un argument special - [# This line], care acționează ca o legătură către linia curentă. În special, pentru rândurile aferente „Client, A” și februarie 2013, valoarea parametrului [[#Acest rând];[Companie]] va fi egală cu „Client, A” și [[#Acest rând]; [Perioada]] - „02/01/2013”.

Deci, am descris toate calculele necesare cu o singură formulă. În acest caz, argumentele de intrare ale formulei se modifică dinamic în funcție de locul în care este apelată.

Rețineți că formatul de înregistrare prezentat diferă semnificativ de modul standard Excel, unde parametrii de intrare sunt specificați în mod explicit în formule. De exemplu, putem converti un tabel de fapte înapoi într-un interval obișnuit (Convertire în interval în fila Instrumente tabel). Apoi, de exemplu, expresia 1 pentru linia 5 va lua forma expresiei 2:

Expresia 2:
=SUMIFS(Interval_regular!$D$2:$D$37; Interval_regulat!$A$2:$A$37; Interval_regular!$A5; Interval_regular!$C$2:$C$37; Interval_regular!$C5)

Pentru linia 6, expresia va arăta identică, cu singura diferență că în loc de $A5 va fi $A6 (și în loc de $C5 - $C6). În acest caz, rezultatul calculării expresiei 2 coincide cu valoarea expresiei 1 din linia corespunzătoare, ceea ce ne permite să le considerăm identice.

Acum trebuie să sortăm veniturile lunare ale clienților în grupuri. Pe baza indicatorului de profitabilitate integral calculat mai devreme, trebuie să selectăm rândul corespunzător dintr-un tabel extern (față de tabelul de fapte). Această operație este ușor de realizat în Excel, deoarece are mulți operatori pentru lucrul cu matrice și intervale.

În special, funcția INDEX ne permite să obținem cu ușurință o valoare dintr-o matrice bidimensională din orice celulă de registru de lucru, trebuie doar să cunoaștem identificatorul de obiect corespunzător; Să scriem expresia 3 în coloana „Income_Level”:

Expresia 3:
=INDEX(Niveluri[Nivel]; MATCH(Tabel_fact[[#Acest rând]; [Suma_venit]]; Niveluri[Start]))

Formula prezentată în expresia 3, ca și expresia 1, este executată în contextul curent al tabelului de fapte - folosește o referință la rândul activ. În plus, folosind funcția INDEX accesăm un obiect extern - tabelul „Niveluri”. În el, folosind funcția SEARCH, se caută un șir potrivit.

Să notăm încă o dată cum am reușit să obținem date dintr-un tabel extern: am selectat o funcție potrivită și am specificat identificatorul obiectului dorit în ea ca argument.

Lucrul cu Excel Power Pivot

Să ne uităm la modul în care Power Pivot efectuează operațiunile descrise în prima parte a articolului.

Instalarea Excel Power Pivot.În primul rând, suplimentul trebuie instalat ( îl puteți descărca gratuit de pe site-ul oficial Microsoft). O nouă filă „Power Pivot” ar trebui să apară pe panglica Excel. Dacă programul de completare nu este activat imediat, trebuie să îl activați manual: „Opțiuni Excel” - „Suplimente”, în lista „Gestionați”, selectați „Suplimente COM” - „Go”. Tot ce trebuie să faceți este să bifați caseta de lângă „Power Pivot pentru Excel”. Fereastra este activă numai în documentele în format xlsx.

Pregătirea datelor inițiale. Baza de date PowerPivot acceptă o mare varietate de surse. În special, puteți încărca date în acesta dintr-un SGBD MS Access sau SQL Server. În acest caz, volumul de informații procesate se poate ridica la milioane de rânduri. Dar pentru utilizatorii obișnuiți, capacitatea de a stoca informații sursă pe foi într-un registru de lucru Excel obișnuit este mai valoroasă.

În exemplul pe care îl luăm în considerare în articol, sunt folosite doar două tabele - un tabel de fapte, precum și un mic tabel de referință auxiliar cu niveluri de profitabilitate. Pentru a le adăuga la modelul Power Pivot, trebuie să îndepliniți doar două condiții:

1. Așezați mesele pe foi separate ale cărții. Rețineți că această cerință diferă de modul standard de operare Excel, care permite plasarea unui număr nelimitat de tabele care descriu diferite entități pe o singură foaie.

2. Stocați tabelele separat de fișierul raportului rezumat. Dacă este necesar, tabelele cu date pot fi chiar plasate în diferite registre de lucru Excel - Power Pivot poate accepta mai multe conexiuni deschise paralele la surse de date de un anumit tip.

Când copiați datele sursă pe foi noi, se recomandă să atribuiți imediat nume informative acestora din urmă. Când importați ulterior date în Power Pivot, numele obiectelor originale devin numele tabelelor corespunzătoare din model.

Apoi, creați un nou registru de lucru Excel, pe care îl vom numi „Date originale”. Să plasăm un tabel cu date inițiale pe o foaie numită „Table_Facts”, iar pe o altă foaie „Levels” - un tabel de referință cu niveluri de profitabilitate.

Acum să creăm un alt registru de lucru Excel, să apelăm fereastra PowerPivot din el și să importăm date din fișierul „Date originale” în el. Pentru a importa date, trebuie să apelați comanda „Obține date externe din alte surse” din meniul „Fișier”, apoi tipul „Fișier Excel”, rulând expertul de import de date.

În prima etapă a activității sale, indicăm locația fizică a fișierului de date. Vă recomandăm să activați imediat opțiunea „Utilizați primul rând pentru anteturile coloanelor” - apoi coloanele PowerPivot vor primi automat numele coloanelor din registrul de lucru Excel.

În a doua etapă, selectăm ce tabele vor fi folosite în model.În cazul nostru, ar trebui să le selectăm pe ambele.

Ca rezultat al operațiunilor efectuate, tabelele sursă ar trebui să apară în fereastra Power Pivot (vezi Fig. 3). Puteți comuta între ele selectând fila corespunzătoare din colțul din stânga jos al ferestrei.

În fiecare tabel, puteți crea două tipuri de obiecte: coloane calculate și câmpuri calculate.

Coloanele calculate acționează ca dimensiuni într-un tabel pivot — ele conțin valori care sunt apoi plasate în zonele de rând sau coloane ale aspectului raportului.

Câmpurile calculate - măsuri, adică indicatori agregați care sunt plasați în spațiul de lucru al raportului - sunt definite în zona de calcul în fereastra de vizualizare a datelor Power Pivot, această zonă este plasată sub nivelul datelor tabelului;

În acest articol, nu vom aborda problemele creării de câmpuri personalizate, ci vom lua în considerare doar adăugarea de noi coloane calculate la model. În Power Pivot, acţionează ca un analog complet al coloanelor calculate în tabelele Excel. În special, pentru toate elementele unei coloane, este întotdeauna definită o singură formulă în limbajul DAX, care este introdusă în fereastra de formule Model Builder.

Ca și în cazul tabelelor obișnuite, formulele DAX sunt executate în context de rând. Dar, spre deosebire de tabele, nu este nevoie să specificați în mod explicit parametrul [# Această linie]. Pur și simplu, chiar și fără a scrie parametrul [# This line] într-o formulă DAX, puteți presupune că este prezent în el și îl puteți utiliza în calcule. Deși această caracteristică face formulele DAX mai puțin vizuale, ea permite ca expresiile să fie scrise mai compact.

Luând în considerare caracteristicile enumerate de lucru cu formule DAX, vom crea o nouă coloană „Randament” cu următoarea formulă (expresia 4):

Expresia 4:
=CALCULATE(suma([Suma]); ALLEXCEPT ("Tabel_Fact"; "Tabel_Fact"[Companie]; "Tabel_Fact"[Perioada]))

Să ne uităm la principiul de funcționare al expresiei 4.

În primul rând, pentru fiecare rând al tabelului de fapte, acest operator primește ca intrare un tabel temporar care coincide cu tabelul de fapte în sine. Filtrele definite de contextul curent sunt apoi aplicate acestui tabel de staging. Conține linii ale căror atribute coincid cu valorile liniei curente: [[#This line]; [Companie]], [[#Această linie]; [Serviciul]], [[#Această linie]; [Perioada]], [[#Această linie]; [Sumă]].

Cu toate acestea, expresia ALLEXCEPT ("Fact_Table"; "Fact_Table" [Companie]; "Fact_Table" [Period]) necesită ștergerea filtrelor de context curent pentru toate coloanele, cu excepția [Company] și [Period]. Se pare că în tabelul intermediar lăsăm doar acele rânduri ale căror valori de atribut [Companie] și [Perioadă] coincid cu rândul curent. În tabelul filtrat în acest mod, este selectat atributul [Suma], ale cărui valori sunt apoi însumate.

La prima vedere, formula poate părea prea confuză, dar în realitate reproduce doar calculele expresiei 1, pe care am citat-o ​​chiar la începutul articolului. Valorile din coloana „Randament” vor fi o confirmare clară, deoarece coincid cu coloana „Venit_Suma” din Fig. 2.

Acum să adăugăm o altă coloană calculată la tabel - „Nivel_tehnic”. În ea, vizavi de fiecare sumă calculată la prima etapă, vom indica valoarea numerică a scalei din tabelul „Niveluri”. De fapt, trebuie să obținem valori dintr-un alt tabel din baza „Fact_Table”. La fel ca în cazul calculelor obișnuite în Excel, pentru a face acest lucru, trebuie doar să selectați funcția corespunzătoare și să îi transmiteți identificatorul obiectului dorit ca argument.

Să introducem următoarea formulă în coloana „Nivel_tehnic” (expresia 5):

Expresia 5:
=CALCULATE(max("Niveluri"[Start]);filter(all("Levels"[Start]);"Levels"[Start]

În expresia prezentată, funcția de agregare MAX() este aplicată tabelului extern „Niveluri”. În primul rând, este filtrat în funcție de constrângerile contextului curent - doar acele rânduri care sunt mai mici decât numărul din coloana Randament a rândului activ sunt lăsate în tabel.

În cele din urmă, să adăugăm ultima coloană calculată la tabelul nostru - „Dokh_Level”. În el vom afișa o linie cu numele nivelului din tabelul „Niveluri”. Astfel de operațiuni sunt efectuate folosind Funcțiile CĂUTARE V în Excel și GLOOKUP (CĂUTARE V și HLOOKUP). Limbajul DAX are o funcție numită LOOKUPVALUE, care este similară cu operatorul VLOOKUP.

Prin urmare, în coloana „Income_Level” este suficient să scrieți o expresie simplă de următoarea formă (expresia 6):

Expresia 6:
=LOOKUPVALUE(„Niveluri”[Nivel]; „Niveluri”[Start]; [Nivel_tehnic])

Trecem la etapa finală a generării unui raport de sinteză. Deoarece folosim tabelul de căutare numai în scopuri oficiale, coloanele acestuia nu ar trebui să fie afișate în raport. În mod similar, este posibil ca un raport rezumat să nu arate toate coloanele din tabelul de fapte care conțin calcule intermediare.

Pentru a limita setul de analize în Excel, vom folosi comanda „Ascunde din setul de instrumente client” (pentru a face acest lucru, faceți clic dreapta pe coloana de care avem nevoie și selectați elementul corespunzător din meniul contextual). Coloanele ascunse din Generatorul de modele sunt incolore. Pentru a comuta la mediul Excel și a lucra cu raportul rezumat rezultat, pur și simplu selectați comanda „Switch to Excel workbook”.

Cititorii pot verifica singuri dacă un raport rezumat creat pe baza PowerPivot, în ceea ce privește funcționalitatea sa, corespunde pe deplin capacităților unui tabel pivot obișnuit.

Ajutorul pentru utilizarea suplimentului PowerPivot și a limbajului DAX, inclusiv tutorialul „Învățați noțiunile de bază DAX în 30 de minute” de pe site-ul web oficial MS Office, poate fi găsit online.

office.microsoft

În mai puțin de o oră vei învăța cum să creezi excela Rapoarte PivotTable care combină date din mai multe tabele. Prima parte a acestui tutorial vă va ajuta să importați date și să le explorați. În partea 2, veți învăța să perfecționați modelul de date din spatele raportului dvs., să adăugați noi calcule și ierarhii la rapoartele Power View și să le optimizați folosind suplimentul Power Pivot.

Să începem cu importarea datelor.

1. Descărcați exemple de date (ContosoV2) pentru acest tutorial. Extrageți fișierele de date și salvați-le într-o locație convenabilă, cum ar fi dosarul Descărcări sau Documentele mele.
2. Deschideți un registru de lucru gol în Excel.
3. Selectați Date > Primirea datelor externe > Din Acces.
4. Navigați la folderul care conține fișierele de date eșantion și selectați ContosoSales.
5. Clic Deschis. Deoarece vă conectați la un fișier de bază de date care conține mai multe tabele, va apărea o casetă de dialog Selectarea tabelului, unde puteți selecta tabelele pe care doriți să le importați.

1. În caseta de dialog Selectare tabel, bifați caseta de validare Permite selecția mai multor mese.
2. Selectați toate tabelele și faceți clic pe butonul Bine.
3. În fila Import date, selectați Raport PivotTableși apăsați butonul Bine.

NOTĂ. Felicitări! Tocmai ați creat un model de date. Un model este un strat de integrare a datelor care este creat automat atunci când importați sau lucrați cu mai multe tabele simultan într-un singur raport PivotTable.

Modelul este practic invizibil în Excel, dar îl puteți vizualiza și edita direct folosind suplimentul Power Pivot. În Excel, prezența unui model de date poate fi văzută atunci când vedeți un set de tabele în lista de câmpuri dintr-un tabel pivot. Există mai multe moduri de a crea un model.

Vizualizarea datelor într-un tabel pivot

Pentru a vizualiza datele într-un mod clar, puteți trage câmpurile în zone Valori, ColoaneȘi Siruri de caractereîn lista de câmpuri din tabelul pivot.

1. Derulați prin lista de câmpuri și găsiți tabelul FactSales.
2. Selectați coloana SalesAmount. Deoarece conține date numerice, Excel va plasa automat SalesAmount în zona Valori.
3. Din tabelul DimDate, trageți coloana CalendarYear în zona Coloane.
4. Din tabelul DimProductSubcategory, trageți coloana ProductSubcategoryName în zona Rânduri.
5. Din tabelul DimProduct, trageți coloana BrandName în zona Rânduri, plasând-o sub subcategorie.

Tabelul Pivot ar trebui să arate ca cel de mai jos.

Cu puțin efort, ați creat un tabel pivot care conține câmpuri din patru tabele diferite. Această sarcină a fost făcută atât de ușoară datorită relațiilor pre-create între tabele. Deoarece relațiile dintre tabele existau în sursa de date și ați importat toate tabelele simultan, Excel a putut să recreeze acele relații în model.

Dar dacă datele provin din surse diferite sau nu sunt importate în același timp? În general, puteți adăuga date noi creând relații bazate pe coloanele care se potrivesc. În pasul următor, veți importa tabele suplimentare și veți afla despre cerințele pentru noile relații și pașii pentru a le crea.

Adăugarea de tabele suplimentare

Pentru a afla cum să faceți conexiuni, aveți nevoie de tabele suplimentare care nu au legătură. În acest moment, veți obține datele rămase utilizate în acest tutorial importând o bază de date suplimentară și inserând date din celelalte două registre de lucru.

Adăugarea categoriilor de produse

1. Deschideți o foaie nouă în carte. Acesta va stoca date suplimentare.
2. Selectați Date > Primirea datelor externe > Din Acces.
3. Navigați la folderul care conține fișierele de date eșantion și selectați ProductCategories. Faceți clic pe butonul Deschis.
4. În fila Import date, selectați Masași apăsați butonul Bine.

Adăugarea datelor geografice

1. Adăugați o altă frunză.
2. Din fișierele de date eșantion, deschideți Geography.xlsx, plasați cursorul în câmpul A1, apoi apăsați CTRL+SHIFT+END pentru a selecta toate datele.
3. Copiați datele în clipboard.
4. Lipiți datele în foaia de lucru goală pe care tocmai ați adăugat-o.
5. Selectați Formatați ca tabel cu orice stil. Formatarea datelor ca tabel vă permite să le dați un nume, ceea ce este foarte util atunci când definiți relații în pasul următor.
6. Asigurați-vă că caseta Format Table este bifată Tabel cu anteturi. Faceți clic pe butonul Bine.
7. Denumiți tabelului Geografie. Selectați o filă Lucrul cu tabele > Constructorși introduceți numele Geografie în câmpul Nume tabel.
8. Închideți fișierul Geography.xlsx pentru a-l elimina din zona de lucru.

Adăugarea datelor din magazin

• Repetați pașii anteriori pentru fișierul Stores.xlsx - lipiți conținutul acestuia într-o foaie goală. Denumiți tabelului Magazine.

Acum sunt patru foi. Sheet1 conține un tabel pivot, Sheet2 conține Categorii de produse, Sheet3 conține Geografie și Sheet4 conține Magazine. Deoarece ați denumit deja toate tabelele, următorul pas - crearea relațiilor - va fi mult mai ușor.

Utilizarea câmpurilor din tabele noi importate

Puteți folosi câmpurile din tabelele pe care tocmai le-ați importat chiar acum. Dacă Excel nu își poate da seama cum să adauge un câmp la un raport PivotTable, vi se va solicita să creați un link de tabel, care va potrivi noul tabel cu unul care face deja parte din model.

1. Selectați un articol Toateîn partea de sus a listei de câmpuri PivotTable pentru a vedea lista completă a tabelelor disponibile.
2. Derulați în jos lista. Există tabele noi pe care tocmai le-ați adăugat.
3. Extinde Magazinele.
4. Trageți coloana StoreName în zona Filtre.
5. Rețineți că Excel vă va solicita să creați relația. Această notificare apare deoarece ați folosit câmpuri dintr-un tabel care nu este asociat cu un model.
6. Clic Crea pentru a deschide caseta de dialog Creare legătură.
7. În zona Tabel, selectați FactSales. Tabelul FactSales din eșantionul de date conține informații detaliate despre vânzări și costuri pentru Contoso, precum și chei din alte tabele, inclusiv codurile de magazin care sunt prezente în fișierul Stores.xlsx care a fost importat la pasul anterior.
8. În zona Coloană (externă), selectați StoreKey.
9. În zona Tabel asociat, selectați Magazine.
10. În zona Coloană asociată (Primar), selectați StoreKey.
11. Faceți clic pe butonul Bine.

Conectarea ProductSubcategory la ProductCategory

1. În Excel, selectați Date > Relaţie > Crea.
2. În zona Tabel, selectați DimProductSubcategory.
3. În zona Coloană (externă), selectați ProductCategoryKey.
4. În zona Tabel înrudit, selectați Table_ProductCategory.accdb.
5. În zona Coloană asociată (Primar), selectați ProductCategoryKey.
6. Faceți clic pe butonul Bine.
7. Închideți caseta de dialog Managementul relațiilor.

Adăugarea de categorii la un tabel pivot

Deși tabele și relații suplimentare au fost adăugate la modelul de date, acestea nu sunt încă utilizate în PivotTable. Această sarcină adaugă ProductCategory la lista de câmpuri din tabelul pivot.

1. Toate pentru a afișa tabelele prezente în modelul de date.
2. Derulați în jos lista.
3. În zona Rânduri, ștergeți BrandName.
4. Extindeți Table_DimProductCategories.accdb.
5. Trageți ProductCategoryName în zona Rânduri, plasând-o deasupra ProductSubcategory.
6. În zona Câmpuri tabel pivot, selectați Activ astfel încât tabelele pe care tocmai le-ați folosit vor fi folosite în tabelul pivot.

Punct de verificare: revizuiți ceea ce ați învățat

Ați creat un tabel pivot care conține date din mai multe tabele pe care le-ați importat la pasul anterior. Adunarea datelor a necesitat crearea de legături de tabele, pe care Excel le folosește pentru a reconcilia rândurile. Ați învățat că este necesar să aveți coloane care se potrivesc pentru a găsi rânduri asociate. În datele eșantionului, toate tabelele conțin o coloană care poate fi utilizată în acest scop.

Deși PivotTable funcționează, este posibil să fi observat câteva probleme rămase. Lista de câmpuri PivotTable pare că conține tabele suplimentare (DimEntity) și coloane (ETLLoadID) care nu au legătură cu compania Contoso în niciun fel. În plus, datele din tabelul Geografie nu sunt încă integrate.

Următorul: Vizualizați și extindeți modelul dvs. cu Power Pivot

În următoarea serie de sarcini, vă veți extinde modelul utilizând programul de completare Microsoft Office Power Pivot în Microsoft Excel 2013. Veți învăța că cel mai simplu mod de a crea relații este folosirea vizualizării grafice furnizată în acest program de completare. De asemenea, veți folosi programul de completare pentru a crea calcule și ierarhii, a ascunde elementele pe care nu doriți să apară în lista de câmpuri și a optimiza datele pentru rapoarte suplimentare.

NOTĂ. Suplimentul Power Pivot din Microsoft Excel 2013 este disponibil în Office ProPlus.

Adăuga Power Pivot a înregistra excela prin activarea suplimentului Power Pivot.

1. Accesați fila Fişier > Opțiuni > Suplimente.
2. În câmp Control Selectați Suplimente COM> Merge.
3. Bifeaza casuta Microsoft Office Power Pivot în Microsoft Excel 2013, apoi faceți clic pe butonul Bine.

Fila Power Pivot apare pe panglică.

Adăugați o relație folosind o vizualizare diagramă în Power Pivot

1. În Excel, selectați Sheet3 pentru a-l activa. Sheet3 conține tabelul Geografie care a fost importat anterior.
2. Pe panglică, selectați Power Pivot > Adăugați la modelul de date. În acest moment, tabelul Geografie va fi adăugat la model. Se va deschide și programul de completare Power Pivot, pe care îl puteți folosi pentru a finaliza pașii rămași ai sarcinii.
3. Observați că fereastra Power Pivot afișează toate tabelele din model, inclusiv tabelul Geografie. Uită-te prin câteva tabele. În acest add-in, puteți vizualiza toate datele conținute în model.
4. În fereastra Power Pivot, sub Vizualizare, selectați Vizualizare diagramă.
5. Utilizați bara de defilare pentru a redimensiona diagrama, astfel încât să puteți vedea toate obiectele din diagramă. Rețineți că două tabele nu sunt conectate la restul diagramei: DimEntity și Geography.
6. Faceți clic dreapta pe DimEntity și apoi faceți clic Șterge. Acest tabel face parte din baza de date sursă și nu este necesar în model.
7. Ajustați scara tabelului Geografie astfel încât toate câmpurile sale să fie vizibile. Puteți mări diagrama tabelului folosind glisorul.
8. Rețineți că tabelul Geography conține o coloană GeographyKey. Această coloană conține valori care sunt identificatori unici pentru fiecare rând din tabelul Geografie. Să stabilim dacă alte tabele din acest model folosesc aceeași cheie. Dacă da, putem crea o relație care să conecteze tabelul cu restul modelului.
9. Selectați Găsi.
10. În câmpul Căutare metadate, introduceți GeographyKey.
11. Apăsați butonul de mai multe ori Găsește următorul. Valoarea GeographyKey va fi găsită în tabelele Geografie și Magazine.
12. Trageți tabelul Geografie în tabelul Magazine.
13. Trageți coloana GeographyKey din tabelul Stores pe coloana GeographyKey din tabelul Geography. Power Pivot va desena o linie între cele două coloane pentru a indica relația.

În această sarcină, ați învățat o nouă modalitate de a adăuga un tabel și de a crea relații. Acum aveți un model complet integrat în care toate tabelele sunt conectate și accesibile la PivotTable din Sheet1.

SFAT.În vizualizarea Schemă, unele scheme de tabel sunt complet extinse pentru a afișa coloanele ETLLoadID, LoadDate și UpdateDate. Aceste câmpuri specifice fac parte din depozitul de date Contoso original și sunt adăugate pentru a sprijini operațiunile de recuperare și încărcare a datelor. Nu sunt necesare în modelul dvs. Pentru a scăpa de ele, evidențiați și faceți clic dreapta pe câmpuri, apoi faceți clic Șterge .

Creați o coloană calculată

În programul de completare Power Pivot, puteți folosi formule DAX pentru a adăuga calcule. În această sarcină, veți calcula linia de jos și veți adăuga o coloană calculată care face referire la valorile datelor din alte tabele. Mai târziu, veți învăța cum să utilizați coloanele de referință pentru a vă simplifica modelul.

1. În fereastra Power Pivot, reveniți la vizualizarea datelor.
2. Dați tabelului Table_ProductCategories accdb un nume mai semnificativ. Vă veți referi la acest tabel în pașii ulterioare, iar un nume mai scurt va face calculele mai ușor de citit. Faceți clic dreapta pe numele tabelului și apoi faceți clic Redenumiți, introduceți numele ProductCategories, apoi apăsați Enter.
3. Selectați tabelul FactSales.
4. Selectați Constructor > Coloane > Adăuga.
5. În bara de formule de deasupra tabelului, introduceți următoarea formulă. Funcția de completare automată vă va ajuta să introduceți numele complete de coloane și tabel și vă va arăta funcțiile disponibile. De asemenea, puteți face pur și simplu clic pe o coloană și Power Pivot va adăuga numele acesteia la formulă.

= - -

Toate rândurile coloanei calculate vor fi umplute cu valori. Pe măsură ce derulați în jos tabel, veți vedea că rândurile pot conține valori diferite pentru o coloană dată, pe baza datelor conținute în fiecare rând.

1. Redenumiți coloana făcând clic dreapta pe CalculatedColumn1 și selectând Redenumiți coloana. Tastați Profit și apăsați ENTER.
2. Acum selectați tabelul DimProduct.
3. Selectați Constructor > Coloane > Adăuga.
4. În bara de formule de deasupra tabelului, introduceți următoarea formulă.

RELATED(Categorii de produse)

Funcția RELATED returnează o valoare dintr-un tabel înrudit. În cazul nostru, tabelul ProductCategories conține numele categoriilor de produse pe care va trebui să le adăugați la tabelul DimProduct atunci când construiți ierarhia care include informațiile despre categorie.

1. Când ați terminat de introdus formula, apăsați ENTER pentru a o confirma.

Toate rândurile coloanei calculate vor fi umplute cu valori. Pe măsură ce derulați în jos tabelul, veți vedea că fiecare rând conține un nume de categorie de produse.

1. Redenumiți coloana. Tastați ProductCategory și apăsați Enter.
2. Selectați Constructor > Coloane > Adăuga.
3. În bara de formule de deasupra tabelului, introduceți următoarea formulă, apoi apăsați Enter pentru a o confirma.

RELATED(DimProductSubcategory)

1. Redenumiți coloana făcând clic dreapta pe CalculatedColumn1 și selectând Redenumiți coloana. Tastați ProductSubcategory și apăsați ENTER.

Crearea unei ierarhii

Majoritatea modelelor conțin date care sunt de natură ierarhică. Exemplele comune includ date din calendar, date geografice și categorii de produse. Crearea ierarhiilor este utilă deoarece vă permite să trageți un element (ierarhie) în raport, mai degrabă decât să colectați și să aranjați aceleași câmpuri de fiecare dată.

1. În Power Pivot, accesați Vizualizare diagramă. Extindeți tabelul DimDate pentru a facilita lucrul cu câmpurile sale.
2. Apăsați și mențineți apăsată tasta CTRL și faceți clic pe coloanele CalendarYear, CalendarQuarter și CalendarMonth (va trebui să derulați în jos în tabel).
3. Cu trei coloane selectate, faceți clic dreapta pe una dintre ele și faceți clic Creați ierarhie. Un nod ierarhic părinte, Ierarhia 1, va fi creat în partea de jos a tabelului, iar coloanele selectate vor fi copiate în ierarhie ca noduri secundare.
4. Introduceți Date ca nume al noii ierarhii.
5. Adăugați o coloană FullDateLabel la ierarhie. Faceți clic dreapta pe FullDateLabel și selectați Adăugați la ierarhie. Selectați tipul de dată. Coloana FullDateLabel conține data în format complet, inclusiv anul, luna și ziua. Asigurați-vă că coloana FullDateLabel apare în partea de jos a ierarhiei. Acum aveți o ierarhie pe mai multe niveluri care include anul, trimestrul, luna și zilele calendaristice individuale.
6. În timp ce sunteți încă în vizualizarea diagramă, selectați tabelul DimProduct și faceți clic Creați ierarhieîn antetul tabelului. Un nod ierarhic părinte gol apare în partea de jos a tabelului.
7. Introduceți Categorii de produse ca nume al noii ierarhii.
8. Pentru a crea noduri secundare ale unei ierarhii, trageți coloanele ProductCategory și ProductSubcategory în ierarhie.
9. Faceți clic dreapta pe ProductName și selectați Adăugați la ierarhie. Selectați Categorii de produse.

Acum că știți mai multe moduri de a crea o ierarhie, să le folosim într-un tabel pivot.

1. Intoarce-te la excela.
2. Pe Sheet1 (foaia care conține tabelul pivot), ștergeți câmpurile din zona Rânduri.
3. Înlocuiți-le cu noua ierarhie a categoriilor de produse din DimProduct.
4. În mod similar, înlocuiți CalendarYear în zona Coloane cu ierarhia Date din DimDate.

Beneficiile ierarhiilor sunt acum ușor de văzut atunci când vă vizualizați datele. Puteți extinde și închide diferite zone ale tabelului pivot în mod independent, oferindu-vă mai mult control asupra modului în care este utilizat spațiul disponibil. În plus, adăugarea unei singure ierarhii pentru zonele Rânduri și Coloane permite o granularitate instantanee fără a fi nevoie să strângeți mai multe câmpuri pentru a obține același efect.

Ascunderea coloanelor

Odată ce ierarhia Categorii de produse este creată și plasată în DimProduct, DimProductCategory sau DimProductSubcategory din Lista de câmpuri PivotTable nu mai sunt necesare. În această sarcină, veți învăța cum să ascundeți tabelele și coloanele străine care ocupă spațiu în Lista de câmpuri PivotTable. Ascunderea tabelelor și coloanelor vă permite să vă simplificați raportarea fără a afecta modelul care oferă relații și calcule asupra datelor.

Puteți ascunde coloanele individuale, intervalul acestora sau întregul tabel. Numele coloanelor și tabelelor devin inactive, reflectând starea lor ascunsă pentru randarele client care utilizează modelul. Coloanele ascunse apar gri în model, dar rămân vizibile în vizualizarea datelor, astfel încât să puteți lucra cu ele.

1. Asigurați-vă că ați selectat Vizualizare date în Power Pivot.
2. Din filele din partea de jos a ecranului, faceți clic dreapta pe DimProductSubcategory și selectați.
3. Repetați pentru ProductCategories.
4. Deschideți mediul DimProduct.
5. Faceți clic dreapta pe următoarele coloane și selectați Ascundeți în Instrumentele client.

• Cheie de produs
• ProductLabel
• Subcategorie produs

1. Selectați mai multe coloane adiacente, începând cu ClassID și terminând cu ProductSubcategory. Faceți clic dreapta pentru a le ascunde.
2. Repetați acest pas cu alte tabele, eliminând ID-urile, cheile și alte detalii care nu aparțin raportului.

Acum înțelegeți că prin crearea câmpurilor calculate, vă puteți analiza datele în nenumărate moduri puternice. Să învățăm cum să creăm aceste câmpuri.

Crearea câmpurilor calculate în Power Pivot este ușor de utilizat Autosumă.

1. In masa FactVânzări selectați coloana Profit.
2. Selectați Calcule > Autosumă. Observați că a fost creat un nou câmp calculat numit Suma profituluiîn celula zonei de calcul direct sub coloană Profit.
3. În Excel, pe Sheet1, în lista de câmpuri, selectați în tabel FactVânzări câmp calculat Suma profitului.

Gata! După cum puteți vedea, folosind funcții de agregare standard, am creat un câmp calculat Suma profitului în Power Pivot în doar câteva minute și l-am adăugat la PivotTable. Acum vă puteți analiza rapid profiturile aplicând diverse filtre. În acest caz, vedeți o coloană Suma profitului ale cărei valori sunt filtrate în funcție de ierarhiile Categorie de produs și Date.

Dar ce se întâmplă dacă trebuie să faceți o analiză mai aprofundată, cum ar fi numărarea vânzărilor pentru un anumit canal, produs sau categorie? Pentru a face acest lucru, trebuie să creați un alt câmp calculat care calculează numărul de rânduri, câte unul pentru fiecare vânzare din tabelul FactSales, în funcție de filtrele pe care le utilizați.

1. În tabelul FactSales, selectați o coloană Cheie de vânzări.
2. În zonă Calcule faceți clic pe săgeata în jos de sub butonul Autosumă > VERIFICA.
3. Redenumiți noul câmp calculat făcând clic dreapta pe coloană Numărul SalesKeyîn zona de calcul și selectând comanda Redenumiți. introduce Numarași apăsați ENTER.
4. În Excel, pe Sheet1, în lista de câmpuri, selectați FactVânzăriși apăsați Numara.

Vă rugăm să rețineți că o nouă coloană a fost adăugată la PivotTable Numara, care afișează numărul de vânzări în funcție de filtrele aplicate. Ca și în cazul coloanei Suma profitului calculat, câmpul Număr calculat este filtrat în funcție de ierarhiile Categorie de produs și Date.

Să mai exersăm puțin. De data aceasta, veți crea un câmp calculat care calculează procentul de vânzări pe baza unui anumit context sau filtru. Cu toate acestea, spre deosebire de câmpurile calculate anterioare pe care le-ați creat folosind AutoSum, de data aceasta va trebui să introduceți formula manual.

1. În tabelul FactSales, în zona de calcul, selectați o celulă goală. Sfat: Cel mai convenabil este să plasați câmpurile calculate începând cu celula din colțul din stânga sus. Acest lucru le va face mai ușor de găsit. Puteți naviga la orice câmp calculat din zona de calcul.
2. În bara de formule, introduceți următoarea formulă folosind IntelliSense: Procent din toate produsele:=/CALCULATE(, ALL(DimProduct))
3. Apăsați ENTER pentru a confirma formula.
4. În Excel pe Sheet1 în lista de câmpuri din tabel FactVânzări Selectați Procentul tuturor produselor.
5. Într-un tabel pivot, selectați mai multe coloane Procentul tuturor produselor.
6. Pe fila Acasă Selectați Număr > Format procentual. Pentru a formata coloane noi, utilizați două zecimale după virgulă.

Acest nou câmp calculat calculează procentul de vânzări pentru contextul de filtru dat. În cazul nostru, filtrele sunt încă ierarhiile Categorie de produs și Date. Printre altele, puteți observa că procentul computerelor în vânzările de produse a crescut de-a lungul timpului.

Vă va fi ușor să creați formule pentru coloanele și câmpurile calculate dacă sunteți familiarizat cu cum să creați formule Excel. Dar indiferent dacă sunteți familiarizat cu formulele Excel sau nu, aveți o oportunitate excelentă de a învăța formulele DAX de bază, luând lecții din Ghidul de pornire rapidă a cărții electronice: Noțiuni de bază DAX în 30 de minute.

Salvarea locurilor de muncă

Salvați cartea pentru a o putea folosi cu alte manuale sau pentru studii viitoare.

Pasii urmatori

Deși puteți importa cu ușurință date din Excel, este adesea mai rapid și mai eficient să importați folosind programul de completare Power Pivot. Puteți filtra datele importate excluzând coloanele inutile. De asemenea, puteți decide dacă să preluați date utilizând generatorul de interogări sau comanda de interogare. Ca pas următor, explorați aceste alternative: Preluarea datelor dintr-un flux de date în Power Pivot și Importarea datelor din Analysis Services sau Power Pivot.

Rapoartele Power View sunt concepute pentru a funcționa cu modele de date similare cu cel pe care tocmai l-ați creat. Citiți mai multe despre vizualizarea bogată pe care o oferă Power View în Excel: Activați Power View în Excel 2013 și Power View: Explorați, vizualizați și prezentați-vă datele.

Încercați să vă îmbunătățiți modelul de date pentru a crea rapoarte Power View mai puternice în viitor, verificând următorul tutorial. Optimizarea modelului de date pentru rapoartele Power View.

Microsoft Excel 2019/2016. Nivelul 6. Business Intelligence cu PowerPivot, PowerView și PowerMap

Instruirea are loc pe cea mai recentă versiune de Excel 2019!

Te-ai săturat să analizezi date și ți se pare dificil? Veți putea analiza date din surse externe precum baze de date Access, server SQL, cub OLAP, fișiere Excel etc., încărcându-le în model PowerPivot direct sau preprocesat în PowerQuery. Dacă este necesar, creați conexiuni între toate aceste surse, efectuați diverse calcule atât folosind calcule simple, cât și folosind funcții. Nu vei mai depinde de dezvoltatori, pentru că... Puteți crea singur toate rapoartele.

Datele obținute pot fi prezentate în rapoarte combinate de tabele pivot și diagrame pivot, atât dependente, cât și independente unele de altele. Veți învăța să construiți rapoarte dinamice intuitive PowerView pentru a studia, vizualiza și prezenta datele atât în ​​formă tabelară, cât și grafică. Trebuie să construiți rapoarte legate de o hartă geografică a lumii? Apoi fără rapoarte create cu PowerViewȘi PowerMap Nu te descurci!

PowerPivot, PowerQuery, PowerView și PowerMap sunt instrumente moderne puternice de analiză a datelor în Microsoft Excel!

Cursul va fi de interes pentru utilizatorii experimentați de diverse specialități, în primul rând analiștii care trebuie să analizeze surse externe de date și să furnizeze rapid rapoarte pentru a înțelege situația.

Acest curs este predat numai de formatori certificați Microsoft!

Vă rugăm să rețineți software-ul necesar pentru fiecare versiune de Excel!

Pentru 2010 :

1. Microsoft Power Pivot pentru Excel

Pentru 2013 :

1. Microsoft Power Query pentru Excel
2. Previzualizare Power Map pentru Excel 2013
3. Microsoft Silverlight

Pentru 2016/2019

1. Microsoft Silverlight

IMPORTANT! Aceste programe de completare nu sunt acceptate în Excel pentru Mac.

Suplimentul gratuit PowerPivot a fost introdus în Excel 2010. Include șase instrumente foarte utile și a fost creat de dezvoltatori independenți. În Excel 2013, funcționalitatea PowerPivot, împreună cu modelul de date, este integrată direct în nucleul programului.

Modelul de date pe care l-am discutat oferă acces doar la unele dintre caracteristicile PowerPivot și pentru a obține acces la toate instrumentele, va trebui să instalați însuși add-in-ul PowerPivot, inclus cu Office 2013 Professional Plus. De asemenea, puteți obține programul de completare abonându-vă la Office 365 Business. Dacă utilizați Office 2013 Acasă și Student sau Standard, PowerPivot este încă disponibil, dar fereastra PowerPivot este blocată și unele funcții sunt dezactivate.

Descărcați nota în sau formatați, exemple în (conține mai multe fișiere Excel și o bază de date în format txt)

Avantajele și dezavantajele PowerPivot și ale modelului de date

Să începem prin a privi cele trei caracteristici principale ale PowerPivot și să vedem cum sunt implementate în fiecare versiune de Excel.

Creați tabele pivot bazate pe mai multe tabele sursă fără a utiliza funcția CĂUTARE V. Această caracteristică este disponibilă în toate versiunile de Excel 2013. În versiunea Standard, nu veți vedea cuvântul PowerPivot(se folosește în schimb expresia Model de date). Dacă aveți instalat programul de completare PowerPivot, crearea de relații între tabele este ușoară folosind o reprezentare grafică.

Importați 100 de milioane de rânduri într-un registru de lucru. Tabelele PowerPivot pot conține un număr nelimitat de rânduri. Am văzut tabele similare constând din 100 de milioane de rânduri. Singura limitare este dimensiunea fișierului cărții, care este de 2 GB, și cantitatea de RAM. Folosind algoritmul de compresie VertiPaq, un fișier text de 50 MB este comprimat în amprenta de 4 MB a unui fișier de tabel PowerPivot. Dacă numărul de coloane dintr-un astfel de tabel este de 10, numărul de rânduri va fi de 950 de milioane. Această caracteristică este disponibilă în toate versiunile de Excel 2013. În Office Standard, puteți importa înregistrări dintr-un tabel PowerPivot și puteți crea tabele pivot. nu puteți vizualiza înregistrările. Pentru a efectua previzualizarea, trebuie să instalați programul de completare PowerPivot. Incapacitatea de a vizualiza înregistrările PivotTable reprezintă un inconvenient serios, deoarece înainte de a crea orice raport, ar fi o idee bună să revizuiți informațiile incluse în acel raport.

Creați formule mai bune folosind limbajul de formule DAX. nu este disponibil în versiunile standard de Excel 2013. Pentru a obține acces la toate instrumentele din Excel 2013 care vă permit să creați formule noi în tabelele PowerPivot și să adăugați noi coloane calculate la tabelele Pivot, instalați programul de completare PowerPivot. Limbajul formulei DAX oferă utilizatorului o varietate de capabilități, deși nu este foarte ușor de învățat.

Beneficiați de modelul de date PowerPivot, disponibil în toate versiunile de Excel. Mai jos sunt o serie de beneficii suplimentare oferite de modelul de date:

• S-a adăugat o funcție pentru calcularea totalurilor Numărul de elemente distincte ( vezi, de exemplu,). Înainte ca această caracteristică să fie disponibilă, trebuia să determinați manual numărul de apariții ale diferitelor elemente.
• Zona de rezumat a unui tabel pivot poate include numai date filtrate sau toate datele. Pentru început, atunci când creați un tabel pivot, includeți datele în modelul de date (Figura 1). În PivotTable, selectați un filtru Primele 10...În zona totalurilor sunt rezumați indicatorii corespunzători primilor zece (Fig. 2a). Acum puteți crea un total care include valori pentru clienții cu sume mici de comenzi care au fost filtrate când ați creat raportul. Accesați fila de context Constructorși în meniul drop-down Subtotaluri selectați opțiunea Includeți articolele filtrate în totaluri(Fig. 2b). Vă rugăm să rețineți că, dacă nu creați un model de date, această opțiune de subtotal nu va fi disponibilă pentru selecție. De asemenea, rețineți că în titlu Total general apare un asterisc (*).
• Tabelele pivot din Excel 2010 au un instrument nou grozav - seturi, care vă permit să creați rapoarte asimetrice. Singura limitare a seturilor este capacitatea de a le folosi exclusiv atunci când lucrați cu tabelele pivot OLAP (pentru mai multe detalii, consultați). Cu toate acestea, dacă deschideți un tabel obișnuit folosind programul de completare PowerPivot, acesta este convertit automat într-un tabel pivot OLAP.
• Cei care sunt obișnuiți să folosească funcția pentru a extrage date din Tabelele Pivote pot face un pas mai puțin și pot converti Tabelul Pivot în formule cub. Aceste formule pot fi tăiate și lipite în orice format pe care îl alegeți.

Orez. 2. Includerea valorilor filtrate anterior în rezultate: (a) rezultatele includ venituri pentru cei mai buni 10 clienți; (b) rezultatele includ venituri pentru toți clienții, deși numai primii 10 sunt reflectați în tabelul rezumativ

Beneficiile programului de completare PowerPivot în Excel Pro Plus. Dacă utilizați o versiune de Excel Pro Plus care acceptă programul de completare PowerPivot, veți primi următoarele beneficii:

• Un tabel PowerPivot care poate conține până la 100 de milioane de rânduri.
• Puteți sorta, filtra și adăuga calcule la tabel.
• Un mod de proiectare grafică în care puteți crea relații prin tragerea câmpurilor.
• Abilitatea de a modifica proprietățile câmpului în model. Puteți selecta câmpuri care sunt sau nu afișate în Lista de câmpuri din tabel pivot.
• Posibilitatea de a sorta un element CâmpulA(numele lunii) după element Polul B(numărul lunii).
• Posibilitatea de a seta formatul numeric implicit utilizat la afișarea câmpurilor într-un tabel pivot. Această caracteristică nu este disponibilă într-un tabel pivot obișnuit.
• Abilitatea de a defini câmpuri care reprezintă un produs, o regiune geografică sau sunt link-uri către imagini.
• Un fel de indicatori cheie de performanță. Aceste măsuri sunt mai ușor de utilizat în tabelele pivot decât seturile de pictograme.
• Posibilitatea de a accesa panourile Power View și Power Map (pentru mai multe detalii, consultați).

Limitări ale modelului de date. Modelul de date vă permite să încorporați date Excel obișnuite într-un model OLAP. Această operațiune vine cu câteva avantaje și dezavantaje. Dezvoltatorii Excel 2013 au încercat să elimine limitările și deficiențele modelului de date, dar unele dintre ele au rămas în continuare:

• Set limitat de funcții pentru calcularea totalurilor. În ciuda introducerii de noi caracteristici, cum ar fi Numărul de elemente distincte, funcții precum Muncă.
• Lipsa grupării. PowerPivot nu are capacitatea de a grupa PivotTables. În special, nu veți putea grupa datele zilnice după lună, trimestru sau an. Desigur, puteți efectua gruparea adăugând calculele corespunzătoare la setul de date original, dar ar fi totuși mai ușor să utilizați o funcție specială de grupare.
• Vedere neobișnuită la masă. Pentru a vizualiza rândurile unui tabel pivot clasic, pur și simplu ați făcut dublu clic pe o celulă din acel tabel. Când lucrați cu un model de date, această acțiune va afișa primele mii de rânduri.
• Fără papi sau elemente calculate. Modelul de date nu acceptă câmpuri calculate sau membri calculati. Dacă este instalat programul de completare PowerPivot, măsurile DAX vă permit să efectuați calcule similare. Dacă programul de completare PowerPivot nu este instalat, nu veți putea utiliza câmpuri calculate sau membri calculati.
• Asistența este disponibilă numai în Excel 2013. Registrele de lucru care utilizează modelul de date nu pot fi încărcate în versiunile anterioare de Excel.
• Sortare prost implementată. Când folosiți tabele pivot obișnuite, ianuarie vine întotdeauna înainte de februarie. Sortarea corectă în Excel se bazează pe utilizarea listelor personalizate compilate pentru numele lunilor și zilelor săptămânii. Tabelele Pivote bazate pe modele de date nu utilizează în mod implicit liste personalizate. Remedierea acestei probleme necesită opt clicuri suplimentare pe fiecare câmp PivotTable.

Unirea mai multor tabele folosind un model de date în Excel 2013

Microsoft a adus tot ce este mai bun din PowerPivot în Excel 2013. Office Pro Plus este disponibil și cu modulele PowerPivot, Power View și Inquire cu funcții complete. În versiunile standard de Excel 2013, capacitățile de bază ale PowerPivot sunt implementate folosind un model de date. În fig. 1 arată caseta de dialog Crearea unui tabel pivot. Prezența unei casete de selectare în această fereastră înseamnă că aveți de-a face cu PowerPivot. Pentru mai multe informații despre combinarea mai multor tabele într-un singur tabel pivot folosind un model de date, consultați. Această caracteristică funcționează atât când aveți PowerPivot instalat, cât și când utilizați capabilitățile încorporate ale Excel.

Utilizarea Express View. După selectarea unei celule de tabel pivot personalizate, creată pe baza modelului de date, va apărea pictograma Vizualizare rapidă (Figura 3). Această pictogramă poate fi văzută numai de utilizatorii Excel 2013 și apare numai dacă PivotTable se bazează pe un model de date. Vizualizare rapidă este concepută pentru a ajuta un utilizator care este pe cale să editeze un tabel pivot. După ce faceți clic pe această pictogramă, va apărea o fereastră care vă va arăta cum să editați PivotTable.

În acest caz, sfaturile afișate în fereastra Quick View nu sunt de mare ajutor. În fig. Figura 4 prezintă rezultatul dezagregării veniturilor pe regiune.

Orez. 4. Tabelul Pivot rezultat nu este foarte util pentru manageri, dar în multe cazuri, Vizualizare rapidă va ajuta la generarea unui Tabel Pivot mai informativ

Creați un nou tabel pivot bazat pe un model de date existent. Această operație este puțin mai complicată decât atunci când lucrați cu tabele pivot obișnuite:

1. Selectați o echipă Introduce — Masă rotativă.
2. În caseta de dialog Crearea unui tabel pivot setați comutatorul Utilizați sursa externă de date(Fig. 5a). Chiar dacă datele sunt stocate într-un tabel PowerPivot situat într-un registru de lucru, rețineți că PowerPivot a fost inițial un program de completare extern.
3. Faceți clic pe butonul Selectați conexiunea Conexiuni existente.
4. Selectați o filă Mese.
5. Selectați un articol Tabelele din modelul de date din registrul de lucruși faceți clic Deschis(Fig. 5b).

Numărarea numărului de elemente diferite. O astfel de caracteristică a tabelelor pivot este numărarea numărului de înregistrări unice. Tabelele pivot Excel pot număra valorile text. Un exemplu tipic de astfel de tabel pivot este prezentat în Fig. 6. În acest tabel câmpul Sector este în zona RÂND, iar câmpurile ClientȘi Sursa de venit- în zona VALOARE. Numărul total de clienți afișați de acest tabel pivot este de 563, ceea ce nu este corect. De fapt, în acest caz vorbim de 563 de înregistrări nevide corespunzătoare clienților care pot fi repetate. Această suprasarcină se datorează limitărilor tabelelor pivot convenționale.

Dacă tabelul pivot se bazează pe un model de date, urmați acești pași:

1. Faceți clic dreapta pe orice celulă din zonă Numărul de elemente din coloana Client(interval B1:B9). Selectați un articol Opțiuni câmp valoric.
2. La fereastră Opțiuni pentru câmpul valoric pe filă Operațiune Selectați Numărul de elemente distincte(rețineți că, în comparație cu tabelele pivot obișnuite, nu există caracteristici MuncăȘi Index). Faceți clic pe OK.
3. Tabelul pivot afișează o listă de 27 de clienți unici, dintre care 11 sunt în sector Echipamente(Fig. 7).

Utilizarea programului de completare PowerPivot în Excel 2013 Pro Plus

Dacă versiunea dvs. de Excel 2013 acceptă programul de completare PowerPivot, puteți:

• Puteți încărca date în PowerPivot într-o varietate de moduri. Aveți mai multe surse de date, tabele legate, abilitatea de a copia și lipi date și abilitatea de a crea abonamente.
• Puteți vizualiza, sorta și filtra datele în tabelele PowerPivot.
• Puteți importa milioane de rânduri într-o singură foaie de lucru dintr-un tabel PowerPivot.
• Puteți crea formule DAX fie într-un tabel, fie ca un nou câmp calculat numit măsura. Abrevierea DAX reprezintă Expresii de analiză a datelor(Expresii de analiză a datelor). DAX include 117 funcții care vă permit să efectuați două tipuri de calcule. Puteți utiliza 81 de funcții standard Excel pentru a adăuga coloane calculate la un tabel într-o fereastră PowerPoint. Și cu 54 de funcții, puteți crea noi măsuri în tabele pivot. Cu aceste caracteristici, PivotTables câștigă capacități ca niciodată.
• Aveți modalități suplimentare de a crea relații, inclusiv o vizualizare diagramă pe care o puteți utiliza pentru a afișa relații.
• Puteți ascunde sau redenumi coloanele.
• Înainte de a crea un tabel pivot, puteți seta formatarea numerelor pentru o coloană.
• Acum este posibil să atribuiți categorii câmpurilor, de exemplu, geografie, URLlegăturăpeimagine sau link-ul web.
• Puteți defini indicatori cheie de performanță sau ierarhii.
• Dacă compania dumneavoastră are PowerPivot Server instalat, veți putea publica rapoarte interactive PowerPivot pe un site SharePoint.

Dacă intenționați să procesați milioane de înregistrări, instalați versiunile pe 64 de biți ale Office și PowerPivot. În acest caz, limitările asociate cu lipsa memoriei RAM vor rămâne în vigoare, dar sunt parțial compensate de faptul că PowerPivot poate comprima de aproape 10 ori datele stocate în fișierul PowerPivot. Versiunea pe 64 de biți a Office 2013 permite accesul la memorie care depășește limita de 4 GB stabilită pentru versiunea pe 32 de biți de Windows.

Activarea programului de completare PowerPivot. Dacă utilizați Office 365, Office 2013 Pro Plus, Office 2013 Enterprise sau versiunea în cutie a Excel 2013, veți putea accesa programul de completare PowerPivot. Pentru a activa acest supliment, urmați acești pași:

1. Deschideți Excel 2013. Vedeți fila panglică PowerPivot (Figura 8)? Dacă da, nu trebuie să faceți următoarele.
2. Rulați comanda Fişier — Opțiuni, selectați Suplimente. În lista derulantă Control situat în partea de jos a ferestrei, selectați Suplimente COMși faceți clic pe butonul Merge(Fig. 9a).
3. În lista de suplimente COM disponibile, găsiți programul de completare Microsoft Office PowerPivot pentru Excel 2013 Bifați caseta corespunzătoare și faceți clic pe OK (Fig. 9b). (Vă rugăm să rețineți că aveți nevoie de programul de completare PowerPivot pentru Excel 2013. Vechiul program de completare PowerPivot pentru Excel utilizat în Excel 2010 nu este acceptat în Excel 2013.)
4. Dacă nu vedeți fila PowerPivot pe panglică, părăsiți Excel 2013 și porniți-l din nou.

După instalarea programului de completare, fila PowerPivot va apărea pe panglica Excel 2013 (Figura 8).

Importați un fișier text. Tabelul sursă include 1,8 milioane de înregistrări situate în fișierul BigDatal.txt (o parte a fișierului din fereastra programului Notepad este prezentată în Fig. 10). Titlurile coloanelor se află pe prima linie a fișierului. Poate doriți să eliminați liniile non-standard din partea de sus a fișierului pentru a evita problemele atunci când PowerPivot le procesează. Din păcate, datele din dosar sunt în format american, deci nu sunt procesate corect în viitor.

Pentru a importa un fișier care conține 1,8 milioane de rânduri în PowerPivot, urmați acești pași:

1. Accesați fila PowerPivot. Faceți clic pe pictogramă Control. Fereastra aplicației PowerPivot va apărea pe ecran, afișând propria panglică (Figura 11). Piesa centrală a acestei ferestre este un tabel care vă permite să vizualizați datele din modelul dvs. PowerPivot. Fereastra PowerPivot conține următoarele trei file: Până la început, ConstructorȘi În plus.

Orez. 11. Faceți clic pe pictograma Gestionare din fila PowerPivot a panglicii Excel pentru a deschide fereastra PowerPivot Add-in, care afișează propria panglică

2. Importați tabelul din fișierul BigDatal.txt. Pentru a face acest lucru într-un grup Primirea datelor externe faceți clic pe butonul Din alte surse. Pe ecran va apărea o casetă de dialog Expert Import de tabel.

3. În fereastră Expert Import de tabel selectați elementul cel mai mic Fisier text. Faceți clic pe butonul Mai departe.

4. Introduceți în câmp Numele prietenos al conexiunii numele pentru conexiunea dvs.

5. Faceți clic pe butonul Revizuireși găsiți fișierul text. Dacă datele includ anteturi, PowerPivot le va detecta.

6. Asigurați-vă că selectați o virgulă ca delimitator. În lista derulantă Separator de coloane sunt afișați o serie de delimitatori standard, cum ar fi virgulă, punct și virgulă, bară verticală etc.

7. Dacă doriți să refuzați importarea oricărei coloane, debifați casetele corespunzătoare. Fișierul text este gata pentru a fi încărcat în RAM. Rețineți că puteți reduce semnificativ cantitatea de memorie RAM pe care o utilizați prin eliminarea coloanelor inutile, mai ales când includ valori de text lungi. Pentru a face acest lucru, debifați casetele de selectare corespunzătoare coloanelor ascunse (Fig. 12).

8. Vă rugăm să rețineți că fiecare câmp are o listă derulantă cu filtre. Folosind aceste liste, puteți sorta și filtra seturi de date care includ zeci sau sute de mii de înregistrări (de exemplu, excludeți anumite date dintr-un raport). Rețineți că, cu un număr mare de înregistrări, aceste operațiuni sunt foarte lente.

9. După ce faceți clic pe butonul Gata PowerPivot începe să încarce fișierul în memorie. Este afișat numărul de rânduri încărcate curent (Fig. 13).

10. Odată ce importul fișierului este finalizat, este afișat numărul de rânduri încărcate. Faceți clic pe butonul Închide pentru a reveni la fereastra PowerPivot.

11. Fereastra PowerPivot afișează 1,8 milioane de înregistrări. Puteți utiliza bara de defilare verticală pentru a le vizualiza. De asemenea, puteți sorta, modifica formatul numărului sau aplica un filtru (Figura 14).

Atribuiți un format de număr coloanelor. Câmpul de dată este conceput pentru a stoca data și ora. Dacă datele sursă nu includ o componentă de timp, selectați antetul dată și utilizați lista derulantă Format in grup Formatare file Până la început Panglici PowerPivot. Selectați formatul 14.03.2001. La coloană Sursa de venit aplicați formatul monedei. Dacă nu doriți ca Tabelul Pivot să afișeze zecimale, reduceți numărul de zecimale la zero.

Făcând clic dreapta pe o coloană, se afișează un meniu contextual care vă permite să redenumiți, să înghețați și să copiați coloanele. Utilizați acest meniu pentru a ascunde o coloană din Lista de câmpuri PivotTable din Excel.

Deși PowerPivot arată ca Excel, nu este de fapt Excel. Nu veți putea schimba celulele individuale. Dacă adăugați o formulă care calculează suma în celula E1, această formulă va fi copiată automat pe toate rândurile. Dacă formatați o valoare într-o celulă, toate celulele din acea coloană sunt formatate de asemenea.

Pentru a modifica lățimea coloanelor, trageți chenarul dintre titlurile coloanelor (ca în Excel).

Deci aveți 1,8 milioane de înregistrări pe care le puteți sorta, filtra și uniți în tabele pivot. Rețineți că cele 1,8 milioane de rânduri importate din fișierul text sunt stocate în registrul de lucru Excel. Puteți copia fișierul .xlsx pe un computer nou, după care toate liniile vor ajunge pe noul computer. Fișierul text original are o dimensiune de 58 MB, dar fișierul Excel comprimat are doar 4 MB.

Adăugarea datelor Excel prin copiere și lipire.În exemplul nostru de tabel PowerPivot, informațiile din magazin au fost limitate doar la câmp Cod[magazin]. Nu existau informații despre numele sau locația magazinului. Problema poate fi rezolvată folosind un fișier Excel mic care potrivește codurile magazinului cu nume și alte informații de identificare. Puteți adăuga aceste date într-o filă nouă din fereastra PowerPivot. Puteți utiliza copierea și inserarea așa cum este descris mai jos sau puteți crea un tabel legat (vezi secțiunea următoare), care este mai ușor de utilizat.

Pentru a utiliza operațiunile de copiere și inserare, urmați acești pași:

1. Deschideți registrul de lucru care conține intervalul care asociază ID-urile magazinului cu titluri (Figura 15).
2. Selectați datele folosind combinația de taste Ctrl+Shift*.
3. Copiați datele apăsând combinația de taste Ctrl+C.
4. Accesați fila PowerPivot din Excel.
5. Faceți clic pe butonul Control pentru a deschide fereastra PowerPivot. Acum veți putea vedea un set de date preimportat care conține 1,8 milioane de rânduri.
6. În partea stângă a filei Până la început Fereastra PowerPivot faceți clic pe pictogramă Introduce. Pe ecran va apărea o casetă de dialog Vedeți încorporarea.
7. Dați noului tabel un nume mai semnificativ decât numele implicit al tabelului, de exemplu: Magazinele(Fig. 16). Faceți clic pe OK.

Orez. 16. Fereastra Vedeți încorporarea

O filă nouă a apărut în fereastra PowerPivot Magazinele, care conține informații suplimentare despre magazine. Vă rugăm să rețineți că în partea de jos a ferestrei PowerPivot există comenzi rapide pentru foile de lucru (Figura 17). Datele lipite din clipboard sunt o copie statică a datelor Excel. Dacă datele Excel se modifică, copiați-le și executați comanda PowerPivot Inserare cu înlocuire.

Adăugați date Excel utilizând legătura. Un tabel a fost adăugat în secțiunea anterioară Magazinele prin efectuarea de operații de copiere și lipire. Acest lucru creează de fapt două copii ale datelor. Unul dintre ele este stocat în foaia de lucru Excel, iar al doilea este stocat în fereastra PowerPivot. Dacă foaia sursă se modifică, conținutul ferestrei PowerPivot rămâne neschimbat. Dacă această situație nu vă convine, legați datele din tabelul Excel cu datele din fereastra PowerPivot:

1. Pentru a începe, transformați datele (ca în Fig. 15) într-un tabel făcând clic pe orice celulă din interval și apăsând Ctrl+T.
2. Selectați o filă contextuală Constructor. Partea stângă a panglicii afișează numele tabelului nou creat - Tabelul 1. În acest câmp, introduceți un nume nou, de exemplu, Store_Code.
3. Accesați fila PowerPivot iar în grup Mese clic Adăugați la modelul de date. O copie a tabelului va fi creată și afișată în fereastră PowerPivot(Fig. 18).

Orez. 18. Masa noua in PowerPivot Cod_magazin

Definiţia connections.În mod obișnuit, Excel utilizează funcția CĂUTARE V pentru a lega două tabele. În PowerPivot, această sarcină este rezolvată mult mai ușor:

1. În fereastra PowerPivot, accesați Până la începutși faceți clic pe butonul Performanţă diagrame. Vor fi afișate două tabele unul lângă celălalt (Fig. 19).

2. Faceți clic în câmp Codîn tabelul principal (BigData) trageți și eliberați-l în timp ce se află deasupra câmpului Cod magazin regiune Magazinele. Vor apărea săgeți corespunzătoare conexiunii stabilite.

3. Pentru a reveni la tabelul PowerPivot, faceți clic Prezentarea datelor situat pe fila Până la început fereastră PowerPivot.

Adăugarea coloanelor calculate folosind DAX. Unul dintre dezavantajele tabelelor pivot create din datele PowerPivot este că nu pot grupa automat datele zilnice în funcție de an. Prin urmare, înainte de a crea un tabel pivot, utilizați limbajul formulei DAX pentru a adăuga o nouă coloană calculată la tabelul PowerPivot.

1. Accesați marcajul PowerPivot. Faceți clic pe butonul Control iar în fereastra care se deschide, faceți clic pe fila primei foi de lucru - Vânzări situat în partea de jos a ferestrei PowerPivot.
2. Coloana din dreapta este numită Adăugarea unei coloane. Faceți clic pe prima celulă a acestei coloane goale.
3. Faceți clic pe pictogramă fx situat în partea stângă a barei de formule. Pe ecran va apărea o casetă de dialog Funcția de inserare, care include o serie de categorii. Selectați o categorie data si ora. Vă rugăm să rețineți că caracteristicile afișate în această listă sunt diferite de caracteristicile din categorie data si oraîn Excel.
4. Derulați lista și selectați o funcție ANși apăsați Bine(Fig. 20). Faceți clic pe antetul coloanei Data comandă. Suprastructură PowerPivot sugerează formula =YEAR([Data comenzii]. Completați formula introducând paranteza de închidere și apăsând introduce. Excel populează coloana cu valorile anului asociate cu data.
5. Faceți clic dreapta pe coloană și selectați opțiunea din meniul contextual Redenumiți coloana. Introduceți un nou nume de coloană, de exemplu, An(Fig. 21).

Crearea unui tabel pivot:

1. Accesați fila Până la început benzi PowerPivot. Extinde lista de sub butonul Masă rotativăși selectați Masă rotativă(Fig. 22).
2. În fereastra care se deschide, selectați butonul radio care determină modul de inserare a tabelului pivot într-o foaie nouă. Veți reveni din nou la fereastra Excel. Lista de câmpuri PivotTable va afișa toate tabelele conținute în PowerPivot. Pentru a vizualiza câmpurile incluse în fiecare tabel, extindeți lista corespunzătoare tabelului (făcând clic pe „triunghi”).
3. În lista de câmpuri PowerPivot deschide masa Date mareși selectați câmpul Sursa de venit. Extindeți tabelul Magazinele o și selectați câmpul din acesta Regiune. Excel va genera un tabel pivot care arată vânzările pe regiune (Fig. 23). Deci, aveți la dispoziție un tabel pivot, care este construit pe baza a 1,8 milioane de rânduri de date și conține un link virtual către tabelul aferent.

Orez. 22. Crearea unui tabel pivot într-o fereastră PowerPivot

Apoi, puteți utiliza instrumentele din setul de file contextuale Lucrul cu tabelele pivot pentru a formata un tabel pivot. De exemplu, puteți aplica un format de monedă și puteți redenumi câmpul Suma pe coloana Venit, selectați un format cu linii alternative etc.

Diferențele dintre tabelele pivot PowerPivot și Excel. Dacă până acum ați creat doar tabele pivot obișnuite Excel, tabele pivot PowerPivotți se poate părea inconfortabil. Cauza multor probleme nu este legată de suplimentul în sine. PowerPivot, dar cu faptul că tabelul pivot PowerPivot este un tabel pivot OLAP și se comportă în consecință. Când lucrați cu tabele pivot PowerPivot Trebuie avut în vedere faptul că:

• Nu există o sortare automată după zile ale săptămânii (luni, marți, miercuri etc.). Pentru a efectua sortarea corectă, selectați comanda AdiţionalOpțiunitriere — Decrescând - În plus. Debifați caseta Sortare automată, extinde lista Sortați după prima cheieși selectați secvența luni, marți, miercuri, joi, vineri, sâmbătă, duminică.
• Pentru a sorta câmpurile în tabele pivot obișnuite, puteți utiliza următoarea tehnică (în loc să trageți și să plasați). Selectați o celulă care conține, de exemplu, cuvântul vineri, și introduceți cuvântul în această celulă luni. După apăsarea tastei introduce date din coloană luni va trece la o nouă coloană. Vă rugăm să rețineți că această tehnică nu se aplică tabelelor pivot. PowerPivot.
• Pe măsură ce introduceți formule folosind interfața Excel, puteți selecta celulele de inclus făcând clic cu mouse-ul sau folosind tastele cursorului. Probabil un supliment PowerPivot a fost creat de fanii mouse-ului, deci atunci când creați formule PowerPivot Puteți folosi doar mouse-ul.
• După ce faceți clic pe butonul Actualizați situat în fila de context Analiză, Excel actualizează datele din Tabelul Pivot. Gândiți-vă înainte de a face acest lucru în Excel 2013. Acest exemplu reimportează 1,8 milioane de rânduri.

Două tipuri de calcule DAX

Tocmai ne-am uitat la un exemplu de utilizare a funcției DAX (AN) pentru a declara o coloană calculată într-un tabel care este afișat într-o fereastră PowerPivot. Există 81 de funcții folosite pentru a crea aceste coloane, dintre care majoritatea sunt copiate direct din Excel. Multe dintre funcțiile DAX sunt similare cu funcțiile Excel corespunzătoare, cu câteva excepții discutate mai jos. De asemenea, puteți utiliza DAX pentru a crea noi câmpuri calculate într-un tabel pivot. Aceste funcții nu sunt concepute pentru a calcula o singură valoare a celulei, ci mai degrabă pentru a determina valorile rândurilor filtrate asociate cu celulele PivotTable (funcții de agregare). Există 54 de astfel de funcții în DAX. Puterea reală a PowerPivot constă în aceste caracteristici.

Utilizarea funcțiilor DAX pe coloanele calculate. Aceste funcții sunt foarte asemănătoare cu funcțiile obișnuite din Excel, așa că cele mai multe dintre ele nu necesită nicio explicație suplimentară. Dar unele funcții DAX diferă de funcțiile Excel:

• Funcția Excel rar menționată RAZNDAT a fost redenumită YEARFRAC și codul ei a fost rescris. De fapt, funcția RAZNDAT în sine nu se află în ajutorul Excel. Mai mult, nici nu se află în vrăjitorul de funcții. Și când tastați manual primele litere ale numelui funcției, Excel nu va afișa nici un indiciu în lista derulantă. Acest lucru este explicat destul de simplu. Această funcție nu este în mod nativ o funcție Excel. Este pur și simplu acceptat de Excel pentru compatibilitate cu alte sisteme de foi de calcul. Această funcție a venit în Excel din foile de calcul Lotus 1-2-3. Vezi mai multe detalii.
• Funcția Excel TEXT a fost redenumită FORMAT.
• Funcția SUMIFS a fost înlocuită cu funcția îmbunătățită CALCULATE.
• În locul funcției CĂUTARE V, este utilizată funcția RELATED mai simplă.
• DAX a introdus funcția BLANK(). Deoarece unele dintre funcțiile de agregare pot baza calculele pe parametrii ALLN0NBLANKR0W sau FIRSTNONBLANK, funcția BLANK() poate fi folosită ca argument pentru funcția IF() pentru a exclude anumite rânduri din calculele de măsurare.
• Funcția SELECT a fost redenumită SWITCH. În timp ce funcția SELECT preia valori numerice de la 1 la 255 ca argumente, funcția SWITCH poate fi programată să funcționeze cu alte valori.

Utilizați funcția RELATED pentru a face calcule în coloane pe baza valorilor dintr-un alt tabel.În timpul calculelor din tabel PowerPivot poate fi necesar să faceți referință la valori în alte celule PowerPivot. În versiunea standard a Excel, funcția CĂUTARE V este utilizată în astfel de cazuri. PowerPivot folosește funcția RELATED.

Acest exemplu folosește un tabel BigData care include un câmp Cod[magazin] și Sursa de venit(cantitatea vânzărilor pe zi). Rapoartele de vânzări folosesc adesea un indicator, cum ar fi vânzările pe metru pătrat de suprafață. Într-un tabel legat Magazinele sunt câmpuri Cod magazinȘi Zona de comert. Setul acestor câmpuri conține toate datele necesare pentru efectuarea calculelor.

Pentru a începe să creați o formulă nouă, mergeți la tabel PowerPivotși faceți clic într-o celulă goală a coloanei Adăugarea unei coloane. Introduceți un semn egal și faceți clic într-o celulă din coloană Sursa de venit. Începeți să introduceți formula în PowerPivot: = [Venit]. Introduceți caracterul bară oblică pentru a reprezenta diviziunea. Acum trebuie să accesați câmpul Zona de comert, situat în tabel Magazinele. Începeți să introduceți o funcție LEGATE DE(. Introduceți primele litere ale numelui tabelului - Ma. Va fi afișată o listă de câmpuri din tabel Magazinele(Fig. 24). Faceți dublu clic în câmp Zona de comert. Completați formula tastând o paranteză de închidere și apăsând Enter. Faceți clic dreapta pe coloană și selectați opțiunea din meniul contextual Redenumiți coloana. Dați un nume nou, de ex. VânzăriNaKvM.

Folosind coloane și relații calculate, puteți crea o serie de tabele pivot interesante. Coloanele calculate sunt calculate pentru fiecare rând al datelor subiacente. Ca urmare, formula Vânzări pe metru pătrat de spațiu comercial rulează de 1,8 milioane de ori pe masă PowerPivot. Folosind formulele DAX, puteți crea un câmp nou calculat, care este calculat o dată pentru fiecare celulă din tabelul pivot final.

Creați un câmp calculat într-un tabel pivot folosind formule DAX. Câmpurile calculate DAX au mai multe avantaje față de câmpurile calculate obișnuite. Un avantaj este că atunci când utilizați un câmp ca acesta, calculele sunt efectuate o dată în fiecare celulă a tabelului pivot rezultat. În fig. Figura 25 prezintă un tabel pivot în care celulele B5:C12 conțin valori numerice. Câmpul calculat DAX pe care îl creați va fi calculat numai pentru cele 16 celule numerice din tabelul Pivot. Acest lucru este mult mai rapid decât calcularea valorilor din 1,8 milioane de celule și apoi adăugarea acestora. Datorită formatului american de dată în fișierul sursă BigData.txt, un număr de date în model PowerPivot nu este afișat corect, rezultând un câmp (gol) din punct de vedere al datelor. Înainte de a începe să creați primul câmp calculat, ar trebui să înțelegeți filtrele.

Orez. 25. Tabel Pivot bazat pe model PowerPivot

Aplicarea de filtre pre-construite pe câmpurile calculate DAX. Câmpurile calculate DAX aplică automat filtre care sunt predefinite în celulele corespunzătoare. Toate filtrele sunt aplicate mai întâi și apoi calculele sunt efectuate pe câmpurile DAX. Să luăm în considerare celula B6, prezentată în Fig. 25. Să încercăm să răspundem la întrebarea câte filtre sunt specificate în celula B6. Veți spune că sunt definite două filtre. Cred că există patru filtre definite în această celulă.

Împreună cu filtrele automate obișnuite, datele din celule pot fi filtrate folosind felii. După crearea primei slice, sunt afișate înregistrările din celula B6 care corespund anului 2006. De asemenea, puteți vizualiza datele de vânzări ale magazinului Bellevue Square Managers adăugând o a doua secțiune. Aceste două felii formează primele două filtre. Antetul liniei Eyewear acționează și ca un filtru. Acesta va fi al treilea filtru. Și, în sfârșit, al patrulea filtru este statul Washington. Câmpurile calculate DAX aplică mai întâi filtrele create anterior și apoi calculează rezultatul aplicării formulei DAX.

Creați un câmp calculat DAX. Pentru a crea un câmp calculat, accesați panglica Excel, selectați fila PowerPivotși rulați comanda Câmpuri calculate — Creați un câmp calculat. Pe ecran va apărea o casetă de dialog Câmp calculat. În câmp Numele tabelului specificați numele tabelului. Dați un nume câmpului calculat, de exemplu, Numărul de magazine. În panoul de introducere a formulei, introduceți o formulă. Pentru a introduce numele funcțiilor, faceți clic pe pictogramă fx. Pe măsură ce introduceți numele câmpurilor, începeți să tastați câteva caractere din numele tabelului, apoi utilizați Completarea automată pentru a selecta câmpul dorit.

După ce ați terminat de introdus formula, faceți clic pe butonul Verificați formula pentru a testa sintaxa formulei. Observați sfatul explicativ al formulei care apare deasupra panoului care arată rezultatele testului cu formulă. Faceți clic în câmp Descriere pentru a ascunde balonul. Rezultatele verificării formulei vor apărea pe ecran. Dacă verificarea formulei reușește, mesajul Formula nu conține erori(Fig. 26). Clic Bine pentru a adăuga un câmp calculat la lista de câmpuri PivotTable. In masa Magazinele modele PowerPivot noul câmp nu a apărut, dar este în tabelul pivot (Fig. 27). La fel ca într-un tabel pivot obișnuit!

Odată creat, un câmp calculat poate fi folosit pentru a efectua calcule în viitor. Câmp calculat Vânzări în magazin(Fig. 28) folosește următoarea formulă:

SUMX(BigData;BigData[Venit])/[Număr de magazine]

Orez. 28. Câmp Vânzări în magazin calculat pe baza unui câmp creat anterior Numărul de magazine

Pentru a simplifica structura tabelului Pivot, puteți ascunde câmpurile Suma pe coloana VenitȘi Numărul de magazine, părăsind câmpul Vânzări în magazin.

Cum să anulați filtrarea standard. Acum să ne uităm la următoarea problemă. În fig. 29, celula C5 este evidențiată. Aplicarea filtrelor la această celulă afișează înregistrări care se potrivesc cu numele comerciantului Amber și cu data vânzării 06/01/2014. Aceasta și următoarele secțiuni (până la Atelier) folosesc un fișier Excel Exemplu de filtru.xlsx

După cum sa menționat mai devreme, toate filtrele pe care le setați într-o celulă PivotTable sunt aplicate automat câmpului calculat DAX. În multe cazuri, acest lucru este inacceptabil. Cell G6 afișează 165 USD de articole vândute, care nu sunt asociate cu niciun agent de vânzări. Politica în acest caz este ca suma de 165 USD să fie alocată altor persoane pe baza cotei lor (sub formă de procent) din suma vândută în acea zi, vânzătorul Hughes neparticipând la vânzări. Este posibilă implementarea acestei politici în acest tabel pivot?

Rețineți că calculele efectuate în celula C5 sunt filtrate implicit pentru a afișa doar vânzările lui Amber. Trebuie să creăm o formulă DAX care va dezfiltra datele. Această formulă va înlocui filtrul care arată postările care se potrivesc cu Chihlimbar și afișează postările care nu sunt legate de Chihlimbar. Funcția DAX CALCULATE poate elimina filtrele instalate și poate aplica alte filtre. Această funcție este similară cu funcția SUMIFS, dar oferă utilizatorilor mai multe opțiuni.

Funcția CALCULATE folosit pentru a calcula o coloană totală luând în considerare unul sau mai multe filtre. Dacă pentru câmp Vanzator setați un filtru, acesta va înlocui filtrul specificat anterior pentru acest câmp. Dacă creați un filtru Vanzator=Hughes, DAX va ignora automat filtrul implicit Vanzator=Chihlimbar, care va afișa toate intrările care se potrivesc cu Hughes. Dacă întâmpinați probleme în a învăța cum să creați formule DAX imediat, încercați abordarea pas cu pas. În loc de formule cu mai multe etaje, formați un set format din formule mici. Formula care calculează volumul vânzărilor lui Hughes (Fig. 30):

CALCULATE([Suma pe coloana Vânzări];Sls[Vânzător]= „Hughes”)

După ce definiți un câmp calculat, îl puteți utiliza în alte câmpuri calculate.

Setați următoarele câmpuri calculate:

Acum este timpul să aplicați filtrul pe coloană Vanzator pentru a ascunde partea lui Hughes de tabelul pivot. Ca rezultat, obținem o sumă de vânzări de 911 USD pentru 2 iunie (valoarea din celula H6, Fig. 31), în ciuda faptului că acțiunea lui Hughes este distribuită între alți vânzători. Comparați valorile pentru fiecare vânzător individual și totalurile pe zi din tabelele din Fig. 30 și 31. Acest rezultat nu poate fi obținut folosind câmpuri calculate create într-un tabel pivot obișnuit.

STUDIU DE CAZ: Calcularea intervalului de preț pentru mărfuri

Câmpurile calculate definite într-un tabel pivot obișnuit sunt întotdeauna calculate pe rândurile setului de date original. Unul dintre abonații site-ului MrExcel a încercat să calculeze intervalul de preț pentru mărfuri folosind formula MAX(Preț) – MIN(Preț) specificate într-un tabel pivot obișnuit. Când creați un câmp calculat într-un tabel pivot, Excel analizează fiecare rând de date sursă și calculează valoarea maximă din acel rând (Figura 32). Deoarece fiecare rând afișează doar o singură valoare de preț, valoarea maximă din fiecare rând a datelor sursă va corespunde unei singure valori de preț. Același lucru se poate spune despre valoarea minimă. Cu alte cuvinte, MAX – MIN = Pret – Pret, adică 0. Ca urmare, pentru fiecare produs afișat în tabelul pivot, diferența dintre prețul cel mai mare și cel mai mic va fi zero. Acest rezultat complet incorect a fost obținut datorită regulilor inerente câmpurilor calculate din tabelele pivot obișnuite (Fig. 33).

Profitați de puterea DAX pentru a rezolva această problemă.

1. Selectați setul de date original. Apăsați Ctrl+T pentru a converti setul de date selectat într-un tabel.
2. Utilizarea unui set de file contextuale Lucrul cu tabele (Designer) dați un nume noului tabel Vânzări.
3. Rulați comanda Introduce — Masă rotativăși bifați caseta Adăugați aceste date la modelul de date.
4. Adăugați un câmp Produs spre zona ROW.
5. Adăugați câmpul de două ori Preț la zona VALORI.
6. În partea de jos a listei de câmpuri din tabelul pivot, extindeți lista pentru primul câmp Preț și selectați Opțiuni câmp valoric. Schimbați funcția rezultată în Minim.
7. La fel și pentru al doilea câmp Preț selectați funcția finală Maxim.
8. La executarea paragrafelor. 6 și 7 creează de fapt un câmp calculat implicit. Aceste domenii sunt discutate în detaliu în secțiunea următoare.
9. Rulați comanda PowerPivot — Câmpuri calculate — Creați un câmp calculat pentru a crea un câmp calculat. Dați un nume acestui câmp Gamă. În fereastra de introducere a formulei, introduceți numele tabelului Vânzări. În lista de câmpuri, găsiți câmpul Vânzări [Maximum în coloana Preț] și apăsați tasta<Таb>. Introduceți un semn minus. Intră din nou în Vânzări. Găsiți câmpul Vânzări [Minimum în coloana prețului] și apăsați tasta<Таb>pentru a o introduce în formulă. Acum formula Interval devine =[Maximum în coloana Preț]-[Minim în coloana Preț].
10. Pentru a adăuga formula la modelul de date, faceți clic pe OK. După revenirea la fereastra Excel, găsiți noul câmp Gamă in masa Vânzări. Faceți clic pe acest câmp. Acum intervalul de preț este calculat corect (Fig. 34).

Creați câmpuri calculate DAX adăugând câmpuri în regiunea VALUES. După adăugarea unui câmp Preț la zona VALORI și de selecție a funcției Minim PowerPivot creează automat un câmp DAX calculat care afișează rezultatul. În acest fel, nu trebuie să utilizați formula =MINX('Vânzări','Vânzări'[Preț]) Puteți vizualiza câmpurile calculate implicite în fereastra PowerPivot PowerPivot — Control iar pe filă În plus faceți clic pe butonul Afișați câmpurile calculate implicite. Câmpuri noi vor apărea în liniile afișate în partea de jos a ferestrei (Figura 35).

Dacă treceți mouse-ul peste partea de jos a ferestrei, veți vedea un sfat explicativ care spune „PowerPivot a generat automat acest câmp calculat prin adăugarea unui câmp în zona Valori din Lista de câmpuri Excel. Câmpul este numai pentru citire și va fi șters automat când coloana este ștearsă."

Când creați o formulă DAX, este mai ușor să adăugați temporar un câmp în regiunea de valori pentru a genera un câmp implicit. Acest câmp poate fi folosit la crearea altor formule DAX. După ce creați o formulă nouă într-un tabel pivot, puteți elimina câmpul implicit din acesta. Aceste câmpuri rămân în fereastra PowerPivot deoarece sunt referite de noile măsuri DAX.

Pentru a calcula intervalul de preț, puteți crea următoarea formulă DAX:

Max(„Vânzări”; „Vânzări”[Preț])-Minx(„Vânzări”; „Vânzări”[Preț])

Utilizarea funcțiilor de timp

DAX are un set de funcții de gestionare a timpului. Pentru a utiliza aceste funcții, este necesar un tabel special care să conțină date (tabel calendaristic). Orice tabel poate include un câmp de dată. Data afișată în setul de date original va apărea pe un singur rând în tabelul de date. Dacă aveți nevoie de coloane suplimentare care identifică o dată ca aparținând unui anumit an, lună sau zi a săptămânii, adăugați aceste coloane la tabelul calendaristic. Există multe modalități de a crea astfel de tabele, dar utilizatorii Excel le creează de obicei în mediul Excel:

1. Navigați la setul de date original. Selectați coloana care include câmpul pentru dată și copiați-o.
2. Accesați o foaie goală și introduceți datele în coloana A.
3. Cu coloana A selectată, executați comanda Date — Eliminați duplicateleși faceți clic pe butonul Bine.
4. Dacă este necesar, adăugați coloane suplimentare, cum ar fi Anul, Ziua săptămânii, Ziua săptămânii, Luna și Luna (Figura 36). Formulele prezentate în partea de sus a figurii demonstrează modul în care este calculată fiecare coloană.
5. Convertiți o serie de date într-un tabel apăsând Ctrl+T.
6. Folosind fila contextuală Constructor legate de un set de file contextuale Lucrul cu tabele, selectați numele Calendar pentru tabelul care urmează să fie creat.

Din această secțiune până la sfârșitul notei, sunt utilizate datele din fișierul Excel Exemplu calendar.xlsx.

Adăugați date într-un tabel PowerPivot și efectuați formatarea. Acest exemplu final de capitol va explora câțiva pași suplimentari pentru a îmbunătăți raportul final. Să începem prin a adăuga tabele VânzăriȘi Calendarîn PowerPivot:

1. Selectați o celulă dintr-un tabel Vânzări.
2. Pe fila PowerPivotîn Excel, faceți clic pe pictogramă Adăugați la modelul de date.
3. Selectați o celulă din tabel Calendar.
4. Adăugați la modelul de date.
5. În fila PowerPivot din Excel, faceți clic pe Control; du-te la fereastră PowerPivot.

Acum să formatăm aceste două tabele în fereastră PowerPivot:

1. In masa Vânzări selectați coloana Data.
2. La fereastră PowerPivot selectați fila Până la început. Acordați atenție grupului Formatare. În mod implicit, formatul corect este deja selectat Data, dar formatarea datei în sine este incorectă. Extinde lista Formatși selectați valoarea *14.03.2001. (Aceasta nu este o eroare; așa arată elementul derulant.)
3. Selectați Coloană Vânzări in masa Vânzări.
4. Pe fila Până la început selectați formatul Valută. Pentru a elimina zecimale, faceți dublu clic Reduceți numărul de zecimale.
5. Selectați din nou coloana Data in masa Vânzări.
6. Pe fila Constructor faceți clic pe pictogramă Crearea unei conexiuni.
7. În caseta de dialog Crearea unei conexiuni primele două câmpuri sunt umplute cu valori VânzăriȘi Data. Extinde lista Tabel de căutare conectatși selectați Calendar. În lista derulantă Coloana de căutare asociată valoarea va apărea automat Data(Fig. 37). Clic Bine pentru a crea o conexiune.
8. Selectați foaia de lucru Calendar La fereastră PowerPivot.
9. Selectați o coloană Data.
10. Accesați fila Până la începutși schimbați formatul datei la *03/14/2001.
11. Selectarea unei coloane Data, accesați fila Constructor. Extinde lista Semnalați ca tabel de dateși selectați Semnalați ca tabel de date. Pe ecran va apărea o casetă de dialog Semnalați ca tabel de date. Câmpul corect este deja selectat în această fereastră Data. Faceți clic pe butonul Bine. Această acțiune este necesară pentru a crea filtre după dată în lista câmpurilor din tabelul pivot. Rezultatul este prezentat în Fig. 39.

Orez. 37. Crearea unei relații între tabele VânzăriȘi Calendar

PowerPivot nu acceptă sortarea automată după liste personalizate. Sunt un mare fan PowerPivotși scriu cărți pe subiecte conexe din 2009. Și în prima carte PowerPivot Am menționat de mai multe ori că în PowerPivot Sortarea automată după nume de luni nu este efectuată (în secvența „ianuarie, februarie, martie...”). PowerPivot PivotTables sortează numele lunilor în ordine alfabetică (aprilie, august, iulie...), iar această problemă persistă chiar și în PowerPivot pentru Excel 2013.

Tabelele pivot obișnuite care utilizează memoria cache standard sortează automat toate câmpurile pe baza listelor personalizate. Dar, din păcate, această sortare nu este disponibilă pentru tabelele pivot PowerPivot. Pentru a depăși acest lucru:

1. La fereastră PowerPivot selectați tabelul Calendar.
2. Selectați una dintre celulele din coloană Zi a săptămânii.
3. Pe fila Până la început La fereastră PowerPivot in grup Sortare și filtrare faceți clic pe pictogramă Sortarea după coloane.
4. În caseta de dialog Sortați după coloană selectați sortarea zilei săptămânii după numărul zilei săptămânii (Fig. 38). Clic Bine.
5. Selectați o celulă dintr-o coloană Lună. Repetați pp. 3 și 4, dar de data aceasta selectați sortarea coloanelor Număr lună de lună.

Creați un tabel pivot cu capabilități avansate. La fereastră PowerPivot selectați tabelul Vânzări. Pe fila Până la început extinde lista Masă rotativăși selectați Masă rotativă. Veți reveni la Excel și câmpurile tabelului vor apărea în Lista de câmpuri din tabel pivot Calendar și Mese Vânzări. Poziționați cursorul mouse-ului peste câmp Datași extinde lista. Selectați un articol Filtrează după dată, care vă permite să accesați toate filtrele de dată care sunt utilizate în tabelele pivot obișnuite (Fig. 39).

Orez. 39. Deoarece tabelul Calendar a fost declarat ca un tabel de date, a devenit posibil să se acceseze toate filtrele după dată

Urmați acești pași pentru a adăuga câmpuri la un tabel pivot:

1. Trageți câmpul Zi a săptămânii de la masă Calendar spre zona COLONĂ.
2. Trageți câmpul Vânzări de la masă Vânzări la zona VALORI.
3. Selectați o filă contextuală Analizăși faceți clic pe pictogramă Introduceți felia.
4. Adăugați o porțiune de câmp Zi a săptămânii.
5. Pe fila de context Opțiuni set de file contextuale Unelte de tăiere selectați pentru opțiune Coloane valoarea 7.
6. Pentru felie, selectați numai zilele lucrătoare.

Rezultatul acțiunilor tocmai efectuate este prezentat în Fig. 40. Deoarece ați sortat zilele săptămânii după număr, zilele săptămânii din fereastra slicer sunt afișate în ordinea corectă.

Dacă vezi o fereastră PowerPivot pentru prima dată, este puțin probabil să fii impresionat de capacitățile sale. Dacă ai lucrat cu PowerPivotîn versiunile anterioare de Excel, veți putea aprecia sortarea corectă a zilelor săptămânii.

Funcții de timp „inteligente”. După ce ați făcut lucrările preliminare, este timpul să vă ocupați serios de funcțiile smart time. Începeți prin a reorganiza tabelul pivot prezentat în figură. 40:

1. Pe fila de context Analiză rulați comanda clar — Curata tot.
2. Deschide masa Calendarși trageți câmpul Data spre zona ROW.
3. Deschide masa Vânzăriși trageți câmpul Vânzări la zona VALORI.
4. Adăugați o porțiune de câmp An.
5. În fereastra feliei An selectați 2015.

Rezultatul va fi un tabel rezumativ prezentat în Fig. 41. Vânzările pentru 2 iunie 2015 au fost de 937 USD.

Acum vom crea câmpuri calculate DAX care vor compara volumul vânzărilor din ziua curentă cu volumul vânzărilor din aceeași zi din anul precedent. S-a menționat mai devreme că această sarcină nu este atât de simplă, deoarece celula C9 are aplicat un filtru, afișând înregistrări cu data 2/06/2015. Prin urmare, trebuie să anulați filtrul din câmpul de dată și apoi să aplicați un nou filtru care vă va permite să afișați înregistrările din anul precedent.

Pentru a anula un filtru aplicat unui câmp de dată, trebuie să utilizați funcția CALCULATE. Dar dacă doriți să afișați înregistrările corespunzătoare anului precedent? Utilizați una dintre cele peste 30 de funcții smart time acceptate în DAX, funcția DATEADD.

Pentru a selecta exact aceeași zi cu exact un an în urmă, utilizați formula DATEADD('Calendar'[Data];–1;an) Al treilea argument poate fi zi, lună sau an. Rețineți că aceste argumente sunt enumerabile. la programare în Excel, sunt definite ca variabile globale care sunt convertite în cod numeric atunci când programul rulează), deci nu sunt citate ca argumente text.

Funcția DATEADD este folosită pentru a crea multe formule care calculează perioade de timp. De exemplu, pentru a vizualiza volumul zilnic de vânzări care a avut loc în urmă cu trei luni, utilizați formula =DATEADD('Calendar"[Data];-3;lună). Și nu uitați că funcția DATEADD este doar una dintre cele 34 inteligente » funcții concepute pentru a funcționa cu timpul De exemplu, o altă funcție din această categorie este utilizată în formula DATESMTD ('Calendar'[Data]), care afișează toate datele până în ziua curentă a lunii.

Să presupunem că în modelul dvs. de date coloana de dată este într-un tabel Vânzări iar în tabel Calendar. Funcțiile de oră „inteligente” vor funcționa întotdeauna corect dacă utilizați o referință la câmpul „Calendar” [Dată] Și vor funcționa doar în 10% din cazuri dacă vă referiți la câmpul „Vânzări” [Data]. Dacă doriți să vă puneți în cap o problemă complexă, încercați să creați o formulă DAX cu o referință la câmpul „Vânzări” [Data] După ce verificați că sintaxa este corectă, ecranul afișează un mesaj vesel că nu există erori și totuși, tabelul pivot afișează rezultate incorecte. Nu pot detecta motivul acestui comportament ciudat al formulei.

Deci, aveți la dispoziție funcția DATEADD, care vă permite să identificați o dată care este îndepărtată cu exact un an de la data specificată în rândul tabelului pivot. Pentru a afișa vânzările pentru această dată, utilizați funcția CALCULATE, care înlocuiește filtrele implicite existente. Deoarece rândul 9 al tabelului pivot utilizează un filtru implicit pentru a afișa înregistrările care se potrivesc cu data 2 iunie 2015, utilizați următoarea formulă care include funcția CALCULATE:

CALCULATE([Suma pe coloana Vânzări];DATEADD(‘Calendar”[Data];-1;an))

Urmați acești pași:

1. PowerPivotși rulați comanda Câmpuri calculate — Creați un câmp calculat. Se va deschide o fereastră Câmp calculat(Fig. 42).
2. Dați un nume câmpului VânzăriLastYear.
3. Introduceți formula
   =CALCULATE([Suma pe coloana Vânzări];DATEADD(‘Calendar”[Data];-1;an))
4. In grup Categorie selectați Moneda. Instalare Zecimale 0. Selectați Simbol -$ engleză (SUA).
5. Faceți clic pe butonul Verificați formula pentru a vă asigura că formula este corectă. Dacă indicația de pe ecran vă împiedică să vedeți rezultatele verificării formulei, faceți clic în casetă Descriere. Clic Bine pentru a termina crearea câmpului calculat.
6. Veți reveni la fereastra Excel. Faceți clic pe Lista câmpurilor din tabelul pivot in masa Calendar pe teren VânzăriLastYear. Câmpul este adăugat în zona VALORI.

Pentru a finaliza analiza, adăugați un al doilea câmp calculat care va fi utilizat pentru a calcula tendința de modificare procentuală. Pentru a face acest lucru, urmați acești pași:

1. În fereastra Excel, selectați fila PowerPivotși rulați comanda Calculabil câmpuri — Creați un câmp calculat.
2. Dați un nume câmpului Modificare procentuală.
3. Introduceți formula =[Suma pe coloană Vânzări]/[VânzăriLastYear]–1.
4. In grup Categorie selectați Număr. Instalare Zecimale 1. Selectați Format – Procent.
5. Repetați pp. 5 și 6 din instrucțiunile anterioare.

Tabelul rezumativ rezultat este prezentat în Fig. 43.

Acum puteți elimina câmpuri din tabelul Pivot VânzăriȘi VânzăriLastYear, părăsind câmpul Modificare procentualăși adăugați numele vânzătorilor (câmp Dealer) la zona COLONĂ (Fig. 44). Fiecare celulă a tabelului pivot arată dinamica vânzărilor față de anul trecut (ciudat, dar nu am văzut că s-a analizat dinamica zilnică).

Orez. 44. Tabel pivot cu câmp calculat Modificare procentuală

Pentru a obține tabelul prezentat în Fig. 44, urmați acești pași:

1. Celula G9 (și unele altele) afișează un mesaj de eroare care spune că nu au existat vânzări în anul precedent (și, prin urmare, o divizare cu 0). Faceți clic dreapta pe orice celulă din tabel pivot și selectați din meniul contextual Opțiuni pentru tabel pivot(Fig. 45). Accesați fila Aspect și formatși bifați caseta Pentru afisarea erorilorși introduceți simbolurile - - în câmpul corespunzător.
2. Evidențiați valorile numerice în tabelul pivot (interval C9:H38). Accesați fila Acasă, și rulați comanda Formatarea condițională — Seturi de pictogrameși selectați un set de două triunghiuri și un dreptunghi. Dacă doriți, editați regulile de formatare implicite ale Excel.

Pentru mai multe informații despre DAX, vizitați blogul lui Rob Colley http://www.powerpivotpro.com/ (am cumpărat cartea lui Colley, așa că aștept o lectură grozavă).

Utilizarea indicatorilor cheie de performanță

Indicatorii cheie de performanță (KPI) au apărut în versiunea actuală PowerPivot. În prezent funcționează cu erori deoarece nu sunt implementate perfect. Pentru a utiliza un KPI, trebuie să creați cel puțin un câmp calculat DAX pe care să îl utilizați ca câmp de bază. Opțional, puteți crea și un al doilea câmp calculat DAX pentru a servi drept valoare țintă.

Configurarea KPI-urilor pentru o valoare țintă absolută. Pentru a seta și afișa KPI într-un tabel pivot, urmați acești pași:

1. Pe fila PowerPivot alege echipa Indicatori de performanta - Creați un indicator cheie de performanță. Pe ecran va apărea o casetă de dialog Indicator de performanta.
2. Extinde lista Câmp de bază KPI (valoare)și selectați una dintre măsurile calculate DAX.
3. Pentru a seta valoarea țintă, selectați comutatorul Valoare absolutăși introduceți o valoare.
4. Selectați unul dintre cele patru stiluri de prag de stare (zona 1 din Figura 46).
5. Selectați stilul pictogramei (zona 2 din Fig. 46).
6. Vă rugăm să rețineți că nu puteți trage câmpurile de text, deși acest lucru poate părea posibil la prima vedere. Introduceți valori noi în câmpurile de text deasupra pragurilor. După introducerea unei noi valori și apăsarea tastei Tab, câmpul de text se va muta în locația dorită.
7. Faceți clic pe articol Descrieri situat în partea de jos a casetei de dialog pentru a schimba numele câmpurilor care vor apărea la pasul 10.
8. Clic Bine.
9. Deschideți tabelul Calendar din lista de câmpuri PivotTable. Aproape de câmp Modificare procentuală Va fi afișată pictograma semaforului.
10. Faceți clic pe semnul plus pentru a afișa trei câmpuri: Sens, ŢintăȘi Stat. Trageți câmpul Stat la zona VALORI. O pictogramă va apărea în Tabelul Pivot. Acum, indiferent de pictograma selectată în caseta de dialog, tabelul pivot afișează trei pictograme rotunde: roșu, galben și verde (Figura 47).

Nota a fost scrisă pe baza cărții lui Jelen, Alexander. . Capitolul 10.

Cei care folosesc des Tabele pivot, au putut deja să aprecieze cât de puternic sunt un instrument. Cu toate acestea, din păcate, PivotTables nu pot raporta din diferite surse de date, fie că este vorba de baze de date sau de fișiere. De asemenea, atunci când operăm cu volume mari de date, computerele noastre obișnuite încep să nu poată procesa aceste date și încep să „încetinească” sau chiar să înghețe. Acest lucru se aplică tabelelor care au mai mult de 300 de mii de rânduri. Deci, pentru a rezolva toate aceste probleme, Microsoft a dezvoltat o aplicație gratuită pentru excela, care extinde capacitățile convenționale Tabele pivot.

Prin urmare, vă prezentăm un add-on pentru MS Excel, care vă va permite să creați supertable - Power Pivot .

Deci, ce este PowerPivot?

Power Pivot- Acesta este un supliment suplimentar pentru excela, care extinde funcționalitatea Tabele pivot (Tabele pivot). Folosit numai pentru MS Office 2010. În noua versiune de Office 2013 PowerPivot este deja un element integral excela.

Cum se instalează PowerPivot?

Înainte de instalare PowerPivot, aveți nevoie (pentru Windows XP):

dezinstalați MS Office 2010

instalare SP3 Pentru Windows XP

instalați .NET Framework 4.0 și Visual Studio 2010 Tools for Office Runtime

instalați complet MS Office 2010(cu toate programele și instrumentele comune)

instalați PowerPivot

După instalarea PowerPivot în Excel, apare o filă suplimentară:

Făcând clic Fereastra PowerPivot Se va deschide o fereastră separată în care puteți încărca date din diferite surse.

După descărcarea datelor din diferite fișiere excela la filă Fereastra PowerPivot, puteți lucra cu ei pe o foaie obișnuită excela, adică adăugați coloane, efectuați calcule folosind formule. De asemenea, apare unul unic pentru amândoi excela„Pot face conexiuni între tabelele încărcate ca în Acces. Folosind tabele legate putem face Masă rotativă, care va prelua date din tabele diferite, fără a le combina mai întâi manual, fără o utilizare repetată CĂUTARE V (CĂUTARE V), simplu si rapid.

Făcând clic pe imagine Masă rotativă, avem ocazia să începem să-l modelăm în funcție de nevoile noastre. Astfel, vom obține acest aspect de tabel cu toate datele disponibile pe care le-am pregătit și încărcat anterior:

De asemenea, după cum vedem, în Masă rotativă apar noi câmpuri suplimentare, care sunt numite Filtre vizuale (Slicers).

O altă caracteristică importantă a avansat Tabele pivot este procesarea accelerată a tabelelor mari (filtrare, sortare etc.), care ocupă mai mult de 1 milion de rânduri, ceea ce este standard excela este aproape imposibil.

De asemenea, vă sugerăm să vizionați un videoclip despre instrumentele de autoanaliză a datelor în PowerPivot.