Agregările sunt o modalitate de a restrânge, a rezuma sau a grupa datele. Atunci când începeți cu date brute din tabele sau din alte surse de date, datele sunt adesea plate, ceea ce înseamnă că există multe detalii, dar nu au fost organizate sau grupate în niciun fel. Această lipsă de rezumate sau de structură poate îngreuna descoperirea modelelor din date. O parte importantă a modelării datelor este definirea agregărilor care simplifică, abstract sau rezumă modele ca răspuns la o anumită întrebare de afaceri.
Majoritatea agregărilor comune, cum ar fi cele care utilizează AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN sau SUM pot fi create automat într-o măsură , utilizând Însumare automată. Alte tipuri de agregări, cum ar fi AVERAGEX, COUNTX, COUNTROWS sau SUMXreturnează un tabel și necesită o formulă creată utilizând DAX (Data Analysis Expressions) .
Înțelegerea agregărilor din Power Pivot
Alegerea Grupuri pentru agregare
Atunci când agregați date, grupați datele după atribute, cum ar fi produsul, prețul, regiunea sau data, apoi definiți o formulă care funcționează pe toate datele din grup. De exemplu, atunci când creați un total pentru un an, creați o agregare. Dacă apoi creați o proporție din acest an față de anul precedent și le prezentați ca procente, este un alt tip de agregare.
Decizia de a grupa datele este determinată de întrebarea de afaceri. De exemplu, agregările pot răspunde la următoarele întrebări:
Contează Câte tranzacții au fost acolo într-o lună?
Medii Care au fost vânzările medii din această lună ale vânzătorului?
Valori minime și maxime Care districte de vânzări au fost primele cinci în ceea ce privește unitățile vândute?
Pentru a crea un calcul care răspunde la aceste întrebări, trebuie să aveți date detaliate care conțin numerele de contor sau de însumat, iar datele numerice trebuie să fie corelate într-un fel cu grupurile pe care le veți utiliza pentru a organiza rezultatele.
Dacă datele nu conțin deja valori pe care le puteți utiliza pentru grupare, cum ar fi o categorie de produse sau numele regiunii geografice în care se află depozitul, se recomandă să introduceți grupuri la datele dvs. adăugând categorii. Când construiți grupuri în Excel, trebuie să tastați sau să selectați manual grupurile pe care doriți să le utilizați din coloanele din foaia de lucru. Cu toate acestea, într-un sistem relațional, ierarhiile, cum ar fi categoriile pentru produse, sunt stocate adesea într-un tabel diferit de tabelul de date sau valori. De obicei, tabelul categorie este legat la datele de fapt printr-un fel de cheie. De exemplu, să presupunem că descoperiți că datele conțin ID-uri de produs, dar nu și numele produselor sau categoriile lor. Pentru a adăuga categoria la o foaie de lucru Excel plată, va trebui să copiați în coloana care conține numele categoriilor. Cu Power Pivot, puteți să importați tabelul categoriei de produse în modelul dvs. de date, să creați o relație între tabel cu datele numerice și lista de categorii de produse, apoi să utilizați categoriile pentru a grupa datele. Pentru mai multe informații, consultați Create unei relații între tabele.
Alegerea unei funcții pentru agregare
După ce ați identificat și ați adăugat grupările de utilizat, trebuie să decideți ce funcții matematice să utilizați pentru agregare. Adesea, agregarea cuvintelor este utilizată ca sinonim pentru operațiunile matematice sau statistice care sunt utilizate în agregări, cum ar fi sume, medii, minime sau contorizări. Cu toate acestea, Power Pivot vă permite să creați formule particularizate pentru agregare, în plus față de agregările standard prezente atât în Power Pivot, cât și în Excel.
De exemplu, având în vedere același set de valori și grupări care au fost utilizate în exemplele anterioare, puteți crea agregări particularizate care răspund la următoarele întrebări:
Contoare filtrate Câte tranzacții au existat într-o lună, excluzând fereastra de întreținere de la sfârșitul lunii?
Rapoarte care utilizează medii în timp Care a fost creșterea procentuală sau scăderea vânzărilor comparativ cu aceeași perioadă a anului trecut?
Valori minime și maxime grupate Ce districte de vânzări au fost clasificate în top pentru fiecare categorie de produse sau pentru fiecare promoție de vânzări?
Adăugarea agregărilor la formule și rapoarte PivotTable
Atunci când aveți o idee generală despre cum ar trebui grupate datele pentru a fi semnificative și valorile cu care doriți să lucrați, puteți decide dacă să construiți un raport PivotTable sau să creați calcule într-un tabel. Power Pivot extinde și îmbunătățește capacitatea nativă a Excel de a crea agregări, cum ar fi sume, contorizări sau medii. Puteți crea agregări particularizate în Power Pivot în fereastra Power Pivot sau în zona PivotTable Excel.
-
Într-o coloană calculată, puteți crea agregări care să ia în considerare contextul rândului curent pentru a regăsi rândurile asociate din alt tabel, apoi să însumați, să contorizați sau să mediați acele valori din rândurile asociate.
-
Într-o măsură, puteți crea agregări dinamice care utilizează atât filtre definite în formulă, cât și filtre impuse de proiectarea raportului PivotTable și selectarea slicerelor, titlurilor de coloană și titlurilor de rând. Măsurile care utilizează agregări standard pot fi create în Power Pivot utilizând Însumare automată sau creând o formulă. De asemenea, puteți crea măsuri implicite utilizând agregări standard într-un raport PivotTable din Excel.
Adăugarea grupărilor la un raport PivotTable
Când proiectați un raport PivotTable, glisați câmpurile care reprezintă grupări, categorii sau ierarhii în secțiunea de coloane și rânduri din PivotTable pentru a grupa datele. Apoi glisați câmpurile care conțin valori numerice în zona de valori, astfel încât să poată fi contorizate, mediate sau însumate.
Dacă adăugați categorii la un raport PivotTable, dar datele categoriei nu sunt legate de datele de fapt, este posibil să primiți o eroare sau rezultate ciudate. De obicei, Power Pivot va încerca să corecteze problema, detectând și sugerând automat relații. Pentru mai multe informații, consultați Lucrul cu relațiile în rapoartele PivotTable.
De asemenea, puteți glisa câmpurile în slicere, pentru a selecta anumite grupuri de date pentru vizualizare. Slicerele vă permit să grupați, să sortați și să filtrați interactiv rezultatele dintr-un raport PivotTable.
Lucrul cu grupările într-o formulă
De asemenea, puteți utiliza grupări și categorii pentru a agrega datele stocate în tabele creând relații între tabele, apoi creând formule care utilizează acele relații pentru a căuta valori asociate.
Cu alte cuvinte, dacă doriți să creați o formulă care grupează valorile după o categorie, utilizați mai întâi o relație pentru a conecta tabelul care conține datele detaliate și tabelele care conțin categoriile, apoi construiți formula.
Pentru mai multe informații despre cum să creați formule care utilizează căutări, consultați Căutări în formule Power Pivot.
Utilizarea filtrelor în agregări
O caracteristică nouă din Power Pivot este capacitatea de a aplica filtre la coloane și tabele de date, nu doar în interfața utilizator și într-un PivotTable sau o diagramă, ci și în formulele pe care le utilizați pentru a calcula agregări. Filtrele pot fi utilizate în formule atât în coloane calculate, cât și în s.
De exemplu, în noile funcții de agregare DAX, în loc să specificați valorile peste care să însumați sau să contorizați, puteți specifica un tabel întreg ca argument. Dacă nu ați aplicat filtre la acel tabel, funcția de agregare ar funcționa față de toate valorile din coloana specificată a tabelului. Cu toate acestea, în DAX puteți crea un filtru dinamic sau static în tabel, astfel încât agregarea să funcționeze cu un subset diferit de date, în funcție de condiția de filtrare și de contextul curent.
Combinând condiții și filtre în formule, puteți crea agregări care se modifică în funcție de valorile furnizate în formule sau care se modifică în funcție de selecția titlurilor de rânduri și a titlurilor de coloană dintr-un Raport PivotTable.
Pentru mai multe informații, consultați Filtrarea datelor în formule.
Compararea funcțiilor de agregare Excel și a funcțiilor de agregare DAX
Următorul tabel listează unele dintre funcțiile de agregare standard furnizate de Excel și furnizează linkuri la implementarea acestor funcții în Power Pivot. Versiunea DAX a acestor funcții se comportă cam la fel ca versiunea Excel, cu unele diferențe minore de sintaxă și gestionare a anumitor tipuri de date.
Funcții de agregare standard
Funcție |
Utilizați |
Returnează media (media aritmetică) a tuturor numerelor dintr-o coloană. |
|
Returnează media (media aritmetică) a tuturor valorilor dintr-o coloană. Gestionează valorile text și non-numerice. |
|
Contorizează valorile numerice dintr-o coloană. |
|
Contorizează valorile dintr-o coloană care nu sunt goale. |
|
Returnează cea mai mare valoare numerică dintr-o coloană. |
|
Returnează cea mai mare valoare dintr-un set de expresii evaluate peste un tabel. |
|
Returnează cea mai mică valoare numerică dintr-o coloană. |
|
Returnează cea mai mică valoare dintr-un set de expresii evaluate peste un tabel. |
|
Adună toate numerele dintr-o coloană. |
Funcțiile de agregare DAX
DAX include funcții de agregare care vă permit să specificați un tabel peste care se va efectua agregarea. Prin urmare, în loc să adauge sau să facă o medie a valorilor dintr-o coloană, aceste funcții vă permit să creați o expresie care definește dinamic datele de agregat.
Următorul tabel listează funcțiile de agregare disponibile în DAX.
Funcție |
Utilizați |
Face media unui set de expresii evaluate într-un tabel. |
|
Contorizează un set de expresii evaluate într-un tabel. |
|
Contorizează valorile necompletate dintr-o coloană. |
|
Contorizează numărul total de rânduri dintr-un tabel. |
|
Contorizează rândurile returnate de o funcție de tabel imbricat, cum ar fi funcția de filtrare. |
|
Returnează suma unui set de expresii evaluate într-un tabel. |
Diferențele dintre funcțiile DEX și Agregare Excel
Deși aceste funcții au aceleași nume ca omologii lor din Excel, ele utilizează motorul de analiză a memoriei Power Pivot și au fost rescrise pentru a lucra cu tabele și coloane. Nu puteți utiliza o formulă DAX într-un registru de lucru Excel și invers. Acestea se pot utiliza doar în fereastra Power Pivot și în rapoartele PivotTable bazate pe date Power Pivot. De asemenea, deși funcțiile au nume identice, comportamentul poate fi ușor diferit. Pentru mai multe informații, consultați subiectele individuale despre referința funcției.
Modul în care coloanele sunt evaluate într-o agregare este, de asemenea, diferit de modul în care Excel gestionează agregările. Un exemplu vă poate ajuta să ilustrați.
Să presupunem că doriți să obțineți o sumă a valorilor din coloana Volum din tabelul Vânzări, astfel încât să creați următoarea formulă:
=SUM('Sales'[Amount])
În cel mai simplu caz, funcția obține valorile dintr-o singură coloană nefiltrată, iar rezultatul este același ca în Excel, care adună întotdeauna doar valorile din coloană, Volum. Cu toate acestea, în Power Pivot, formula este interpretată ca "Obțineți valoarea în Volum pentru fiecare rând al tabelului Vânzări, apoi adunați acele valori individuale. Power Pivot evaluează fiecare rând peste care se efectuează agregarea și calculează o singură valoare scalară pentru fiecare rând, apoi efectuează o agregare pentru acele valori. Prin urmare, rezultatul unei formule poate fi diferit dacă s-au aplicat filtre unui tabel sau dacă valorile sunt calculate pe baza altor agregări care pot fi filtrate. Pentru mai multe informații, consultați Contextul în formulele DAX.
Funcțiile DAX Time Intelligence
În plus față de funcțiile de agregare a tabelelor descrise în secțiunea anterioară, DAX are funcții de agregare care funcționează cu datele și orele pe care le specificați, pentru a furniza date inteligente predefinite. Aceste funcții utilizează zone de date pentru a obține valori asociate și a agrega valorile. De asemenea, puteți compara valorile între intervalele de date.
Următorul tabel listează funcțiile time intelligence care pot fi utilizate pentru agregare.
Funcție |
Utilizați |
Calculează o valoare la sfârșitul calendarului din perioada dată. |
|
Calculează o valoare la sfârșitul calendarului perioadei anterioare perioadei date. |
|
Calculează o valoare în intervalul care începe din prima zi a perioadei și se termină la cea mai recentă dată din coloana de date specificată. |
Celelalte funcții din secțiunea funcției Time Intelligence (Funcțiile Time Intelligence) sunt funcții care pot fi utilizate pentru a regăsi date sau intervale particularizate de date de utilizat în agregare. De exemplu, aveți posibilitatea să utilizați funcția DATESINPERIOD pentru a returna un interval de date și să utilizați acel set de date ca argument pentru altă funcție pentru a calcula o agregare particularizată doar pentru acele date.