Applies ToExcel pentru Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

MEDIE

Returnează media (media aritmetică) a tuturor numerelor dintr-o coloană.

AVERAGEA

Returnează media (media aritmetică) a tuturor valorilor dintr-o coloană. Gestionează valorile text și non-numerice.

CONTA

Contorizează valorile numerice dintr-o coloană.

COUNTA

Contorizează valorile dintr-o coloană care nu sunt goale.

MAX

Returnează cea mai mare valoare numerică dintr-o coloană.

MAXX

Returnează cea mai mare valoare dintr-un set de expresii evaluate peste un tabel.

MIN

Returnează cea mai mică valoare numerică dintr-o coloană.

COCHETĂ

Returnează cea mai mică valoare dintr-un set de expresii evaluate peste un tabel.

SUMA

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

AVERAGEX

Face media unui set de expresii evaluate într-un tabel.

COUNTAX

Contorizează un set de expresii evaluate într-un tabel.

NUMĂRBLANK

Contorizează valorile necompletate dintr-o coloană.

COUNTX

Contorizează numărul total de rânduri dintr-un tabel.

NUMĂRĂTOARE

Contorizează rândurile returnate de o funcție de tabel imbricat, cum ar fi funcția de filtrare.

SUMX

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

ÎNCHIDEREBALANCEMONTH

CUARTA DE ÎNCHIDERE

ÎNCHIDEREBALANCEYEAR

Calculează o valoare la sfârșitul calendarului din perioada dată.

DESCHIDEREBALANCEMONTH

CUARTA DE DESCHIDERE

DESCHIDEREBALANCEYEAR

Calculează o valoare la sfârșitul calendarului perioadei anterioare perioadei date.

TOTALMTD

TOTALYTD

TOTALQTD

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.

Aveți nevoie de ajutor suplimentar?

Doriți mai multe opțiuni?

Explorați avantajele abonamentului, navigați prin cursurile de instruire, aflați cum să vă securizați dispozitivul și multe altele.

Comunitățile vă ajută să adresați întrebări și să răspundeți la întrebări, să oferiți feedback și să primiți feedback de la experți cu cunoștințe bogate.