Αυτή η ερώτηση τίθεται μία φορά κάθε δεκαετία: Μπορείτε να κάνετε ένα διάμεσο σε έναν συγκεντρωτικό πίνακα. Παραδοσιακά, η απάντηση ήταν Όχι. Θυμάμαι το 2000 όταν προσέλαβα το Excel MVP Juan Pablo Gonzalez για να γράψω μια φοβερή μακροεντολή που δημιούργησε αναφορές που έμοιαζαν με περιστρεφόμενους πίνακες, αλλά είχαν Medians.
Έτσι, όταν ο Alex στο σεμινάριο του Χιούστον Power Excel ρώτησε για τη δημιουργία μέσων, έλεγα γρήγορα "Όχι". Αλλά τότε… αναρωτήθηκα αν το DAX είχε διάμεση λειτουργία. Μια γρήγορη αναζήτηση στο Google και ναι! Υπάρχει μια συνάρτηση DAX για το Median.
Τι χρειάζεται για να χρησιμοποιήσετε το DAX σε έναν συγκεντρωτικό πίνακα; Χρειάζεστε την έκδοση Windows του Excel με το Excel 2013 ή νεότερη έκδοση.
Εδώ είναι το πολύ απλό σύνολο δεδομένων μου που θα χρησιμοποιήσω για να ελέγξω πώς υπολογίζονται οι διάμεσοι περιστρεφόμενου πίνακα. Μπορείτε να δείτε ότι ο διάμεσος για το Σικάγο θα πρέπει να είναι 5000 και ο διάμεσος για το Κλίβελαντ πρέπει να είναι 17000. Στην περίπτωση του Σικάγου, υπάρχουν πέντε τιμές, οπότε ο διάμεσος θα είναι η 3η υψηλότερη τιμή. Αλλά για ολόκληρο το σύνολο δεδομένων, υπάρχουν 12 τιμές. Αυτό σημαίνει ότι η διάμεση τιμή είναι μεταξύ 11000 και 13000. Το Excel υπολογίζει κατά μέσο όρο αυτές τις δύο τιμές για να επιστρέψει 12000.
Για να ξεκινήσετε, επιλέξτε ένα κελί στα δεδομένα σας και πατήστε Ctrl + T για να μορφοποιήσετε τα δεδομένα ως πίνακα.
Στη συνέχεια, επιλέξτε Εισαγωγή, Συγκεντρωτικός πίνακας. Στο παράθυρο διαλόγου Εισαγωγή Συγκεντρωτικού Πίνακα, επιλέξτε το πλαίσιο για Προσθήκη δεδομένων στο μοντέλο δεδομένων.
Στα Πεδία Συγκεντρωτικού Πίνακα, επιλέξτε Περιοχή και Περιοχή. Αλλά μην επιλέξετε Πωλήσεις. Αντ 'αυτού, κάντε δεξί κλικ στην επικεφαλίδα του πίνακα και επιλέξτε Νέο μέτρο. Το "Μέτρο" είναι ένα φανταχτερό όνομα για ένα υπολογιζόμενο πεδίο. Τα μέτρα είναι πιο ισχυρά από τα υπολογισμένα πεδία σε κανονικούς συγκεντρωτικούς πίνακες.
Στο διάλογο Ορισμός μέτρησης, συμπληρώστε τις τέσσερις καταχωρήσεις που εμφανίζονται παρακάτω:
- Όνομα μέτρου: Μέσος όρος πωλήσεων
- Τύπος
=MEDIAN((Sales)) - Μορφή αριθμού: Αριθμός
- Δεκαδικά μέρη: 0
Μετά τη δημιουργία του μέτρου, προστίθεται στη λίστα των πεδίων, αλλά πρέπει να επιλέξετε την καταχώριση για να την προσθέσετε στην περιοχή Τιμές του συγκεντρωτικού πίνακα. Όπως μπορείτε να δείτε παρακάτω, ο περιστρεφόμενος πίνακας υπολογίζει σωστά τους μεσαίους.
Δες το βίντεο
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, Episode 2197: Median in a Pivot Table.
Πρέπει να σας πω, είμαι πολύ ενθουσιασμένος για αυτό. όταν ήμουν στο Χιούστον κάνοντας ένα σεμινάριο Power Excel εκεί, και ο Άλεξ σήκωσε το χέρι του, είπε, "Γεια σου, υπάρχει τρόπος να κάνεις μια μέση τιμή σε έναν περιστρεφόμενο πίνακα;" Όχι, δεν μπορείτε να κάνετε διάμεσο σε έναν περιστρεφόμενο πίνακα. Θυμάμαι πριν από 25 χρόνια, ο καλός μου φίλος, ο Juan Pablo Gonzales, έφερε πραγματικά μια μακροεντολή για να κάνει το ισοδύναμο του μέσου όρου, χωρίς να χρησιμοποιεί έναν περιστρεφόμενο πίνακα - απλά δεν είναι δυνατόν.
Αλλά είχα μια σπίθα. Είπα, "Περιμένετε ένα δευτερόλεπτο", και ανοίγω ένα πρόγραμμα περιήγησης, και ψάχνω για "DAX median", και, βέβαια, υπάρχει μια λειτουργία MEDIAN στο DAX, πράγμα που σημαίνει ότι μπορούμε να λύσουμε αυτό το πρόβλημα.
Εντάξει, έτσι, για να χρησιμοποιήσετε το DAX, πρέπει να είστε στο Excel 2013 ή στο Excel 2016 ή στο Excel 2010 και να έχετε το πρόσθετο Power Pivot. δεν χρειάζεται να έχετε την καρτέλα Power Pivot, απλώς πρέπει να έχουμε το μοντέλο δεδομένων. Καλώς? Θα επιλέξω λοιπόν αυτά τα δεδομένα, θα τα καταστήσω σε πίνακα με Ctrl + T και θα του δώσουν ένα φανταστικό όνομα του "Πίνακα 4". Στην περίπτωσή σας, μπορεί να είναι "Πίνακας 1". Και θα εισάγουμε έναν Συγκεντρωτικό Πίνακα, θα προσθέσουμε αυτά τα δεδομένα στο Μοντέλο Δεδομένων, κάντε κλικ στο OK και θα επιλέξουμε Τοπικό στην αριστερή πλευρά, αλλά όχι Πωλήσεις. Αντ 'αυτού, θέλω να δημιουργήσω μια νέα υπολογισμένη μέτρηση. Έτσι, ανεβαίνω εδώ στο τραπέζι και κάνω δεξί κλικ και λέω, Προσθήκη μέτρου. Και αυτό το μέτρο θα ονομάζεται "Μέσος όρος πωλήσεων" και ο τύπος θα είναι: = MEDIAN. Πατήστε την καρτέλα εκεί και επιλέξτε Πωλήσεις,κλείσιμο παρενθέσεων. Μπορώ να το επιλέξω ως αριθμό με μηδενικά δεκαδικά ψηφία, να χρησιμοποιήσω ένα διαχωριστικό χίλια και να κάνω κλικ στο OK. Και, ας δούμε, το νέο πεδίο προστίθεται εδώ, πρέπει να το επισημάνουμε για να το προσθέσουμε και λέει ότι η διάμεση τιμή για το Midwest είναι 12.000.
Τώρα ας το ελέγξουμε. Έτσι, εδώ, όλα τα Midwest, ο μέσος όρος των συνόλων δεδομένων μεταξύ 11.000 και 13.000. Έτσι, είναι ο μέσος όρος 11 και 13, αυτό ακριβώς θα έκανε ο διάμεσος στο κανονικό Excel. Τώρα ας προσθέσουμε την περιοχή και λέει ότι ο μέσος όρος των 5.000 του Σικάγου, ο μέσος όρος του Κλίβελαντ είναι 17.000. Σύνολο Midwest και grand 12.000. Όλα αυτά είναι σωστά. Πόσο φοβερό είναι αυτό; Τέλος, διάμεσος σε έναν συγκεντρωτικό πίνακα, χάρη σε ένα υπολογισμένο πεδίο ή μέτρο, όπως λέγεται, και το Μοντέλο δεδομένων.
Τώρα, πολλές από τις αγαπημένες μου συμβουλές - οι συμβουλές για το ζωντανό σεμινάριο Power Excel - σε αυτό το βιβλίο LIVe, The 54 Greatest Tips of All Time. Κάντε κλικ στο "I" στην επάνω δεξιά γωνία για να διαβάσετε περισσότερα για το βιβλίο.
Καλώς. Συμπλήρωση: Μπορείτε να κάνετε μια μέση τιμή σε έναν περιστρεφόμενο πίνακα; Ω όχι, ναι, ναι μπορείτε, χάρη στο μοντέλο δεδομένων. Μπορείτε να δημιουργήσετε ένα διάμεσο. Ctrl + T για να κάνετε τα δεδομένα σας σε πίνακα. Εισαγωγή συγκεντρωτικού πίνακα; και επιλέξτε αυτό το πλαίσιο, Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων. κάντε δεξί κλικ στο όνομα του πίνακα και επιλέξτε νέο μέτρο και το μέτρο θα είναι = MEDIAN ((Πωλήσεις)). Είναι φοβερό.
Ευχαριστώ τον Alex που εμφανίστηκε στο σεμινάριό μου και έθεσε αυτήν την ερώτηση, σας ευχαριστώ που σταματήσατε. Θα σας δω την επόμενη φορά για άλλο netcast από.
Λήψη αρχείου Excel
Για να κατεβάσετε το αρχείο excel: pivot-table-median.xlsx
