Această întrebare apare o dată la fiecare deceniu: puteți face o mediană într-un tabel pivot. În mod tradițional, răspunsul a fost nu. Îmi amintesc în 2000, când am angajat MVP-ul Excel Juan Pablo Gonzalez să scrie o macro minunată care creează rapoarte care semănau cu tabele pivot, dar aveau mediane.
Deci, când Alex, în seminarul meu Houston Power Excel, a întrebat despre crearea medianelor, am fost rapid să spun „Nu”. Dar apoi … M-am întrebat dacă DAX are o funcție mediană. O căutare rapidă pe Google și Da! Există o funcție DAX pentru Median.
Ce este nevoie pentru a utiliza DAX într-un tabel pivot? Aveți nevoie de versiunea Windows pentru Excel care rulează Excel 2013 sau o versiune mai recentă.
Iată setul meu de date foarte simplu pe care îl voi folosi pentru a testa modul în care sunt calculate medianele din tabelul pivot. Puteți vedea mediana pentru Chicago ar trebui să fie 5000 și mediana pentru Cleveland ar trebui să fie 17000. În cazul Chicago, există cinci valori, deci Mediana va fi a 3-a cea mai mare valoare. Dar pentru întregul set de date, există 12 valori. Asta înseamnă că mediana este undeva între 11000 și 13000. Excel face media acestor două valori pentru a returna 12000.
Pentru început, selectați o celulă din datele dvs. și apăsați Ctrl + T pentru a formata datele ca tabel.
Apoi, alegeți Inserare, tabel pivot. În caseta de dialog Inserare tabel pivot, selectați caseta pentru Adăugați aceste date la modelul de date.
În câmpurile Tabelului pivot, alegeți Regiune și district. Dar nu alege Vânzări. În schimb, faceți clic dreapta pe antetul Tabelului și alegeți Măsură nouă. „Măsură” este un nume elegant pentru un câmp calculat. Măsurile sunt mai puternice decât câmpurile calculate în tabelele pivot obișnuite.
În dialogul Definire măsură, completați cele patru intrări prezentate mai jos:
- Nume măsură: Mediană de vânzări
- Formulă
=MEDIAN((Sales)) - Format număr: Număr
- Locuri zecimale: 0
După crearea măsurii, aceasta este adăugată la lista de câmpuri, dar trebuie să alegeți intrarea pentru a o adăuga în zona Valori din tabelul pivot. După cum puteți vedea mai jos, tabelul pivot calculează corect medianele.
Urmăriți videoclipul
Transcriere video
Aflați Excel din Podcast, Episodul 2197: Mediană într-un tabel pivot.
Trebuie să-ți spun că sunt foarte încântat de asta. când eram la Houston, făcând un seminar Power Excel acolo, iar Alex a ridicat mâna, a spus: "Hei, există vreo modalitate de a face o mediană într-un tabel pivot?" Nu, nu puteți face o mediană într-un tabel pivot. Îmi amintesc acum 25 de ani, bunul meu prieten, Juan Pablo Gonzales, a adus de fapt un macro pentru a face echivalentul medianei, nu folosind un tabel pivot - pur și simplu nu este posibil.
Dar am avut o scânteie. Am spus „Așteptați o secundă” și deschid un browser și caut „DAX median” și, cu siguranță, există o funcție MEDIAN în DAX, ceea ce înseamnă că putem rezolva această problemă.
Bine, deci, pentru a utiliza DAX, trebuie să fiți în Excel 2013 sau Excel 2016 sau în Excel 2010 și să aveți programul de completare Power Pivot; nu trebuie să aveți fila Power Pivot, ci trebuie doar să avem Modelul de date. Bine? Așadar, voi alege aceste date, le voi transforma într-un tabel cu Ctrl + T și îi dau un nume neimaginat de „Tabelul 4”. În cazul dvs., ar putea fi „Tabelul 1”. Și vom insera un tabel pivot, vom adăuga aceste date la Modelul de date, vom face clic pe OK și vom alege Regional în partea stângă, dar nu și Vânzări. În schimb, vreau să creez o nouă măsurătoare calculată. Așa că vin aici la masă și fac clic dreapta și spun, Adăugați măsură. Și această măsură va fi numită „Mediana vânzărilor”, iar formula va fi: = MEDIANĂ. Apăsați fila acolo și alegeți Vânzări,paranteză de închidere. Pot alege acest lucru ca număr cu zero zecimale, să folosesc un separator de mii și să fac clic pe OK. Și, să vedem, noul nostru câmp este adăugat aici, trebuie să-l bifăm pentru a-l adăuga și se spune că mediana pentru Midwest este de 12.000.
Acum să verificăm asta. Deci, aici, în întregul Midwest, mediana întregului set de date cuprinde între 11.000 și 13.000. Deci este în medie 11 și 13, ceea ce este exact ceea ce ar face mediana în Excel obișnuit. Acum să adăugăm districtul și scrie mediana celor 5.000 din Chicago, mediana din Cleveland este 17.000; Midwest total și mare total 12.000. Toate acestea sunt corecte. Cât de minunat este asta? În cele din urmă, mediana într-un tabel pivot, datorită unui câmp calculat, sau măsură, așa cum se numește, și modelului de date.
Acum, multe dintre sfaturile mele preferate - sfaturile pentru seminarul meu live Power Excel - din această carte LIVe, The 54 Greatest Tips of All Time. Faceți clic pe „Eu” din colțul din dreapta sus pentru a citi mai multe despre carte.
Bine. Încheiere: puteți face o mediană într-un tabel pivot? Oh, nu, da, da poți, datorită Modelului de date. Puteți crea o mediană; Ctrl + T pentru a vă transforma datele într-un tabel; Introduceți tabelul pivot; și bifați caseta respectivă, Adăugați aceste date la Modelul de date; faceți clic dreapta pe numele tabelului și alegeți noua măsură, iar măsura va fi = MEDIAN ((Vânzări)). Este minunat.
Mulțumesc lui Alex pentru că ai apărut la seminarul meu și mi-ai pus această întrebare, mulțumesc pentru că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: pivot-table-median.xlsx
