Οι Συγκεντρωτικοί Πίνακες του Excel είναι εκπληκτικοί (ξέρω ότι το αναφέρω κάθε φορά που γράφω για Συγκεντρωτικούς Πίνακες, αλλά είναι αλήθεια).
Με μια βασική κατανόηση και λίγη μεταφορά και απόθεση, μπορείτε να ολοκληρώσετε τη δουλειά σε λίγα δευτερόλεπτα.
Ενώ πολλά μπορούν να γίνουν με μερικά κλικ στους Συγκεντρωτικούς πίνακες, υπάρχουν ορισμένα πράγματα που θα χρειαστούν μερικά επιπλέον βήματα ή λίγη δουλειά.
Και ένα τέτοιο πράγμα είναι να μετρήσετε διαφορετικές τιμές σε έναν Συγκεντρωτικό πίνακα.
Σε αυτό το σεμινάριο, θα σας δείξω πώς να μετράτε ξεχωριστές τιμές καθώς και Μοναδικές τιμές σε έναν περιστρεφόμενο πίνακα Excel.
Αλλά πριν προχωρήσω στο πώς να μετρήσω διαφορετικές τιμές, είναι σημαντικό να κατανοήσουμε τη διαφορά μεταξύ του «διακριτού αριθμού» και του «μοναδικού αριθμού»
Distinct Count Vs Unique Count
Ενώ αυτά μπορεί να φαίνονται το ίδιο πράγμα, δεν είναι.
Παρακάτω είναι ένα παράδειγμα όπου υπάρχει ένα σύνολο ονομάτων και έχω απαριθμήσει μοναδικά και ξεχωριστά ονόματα ξεχωριστά.
Μοναδικές τιμές/ονόματα είναι αυτά που συμβαίνουν μόνο μία φορά. Αυτό σημαίνει ότι όλα τα ονόματα που επαναλαμβάνονται και έχουν διπλότυπα δεν είναι μοναδικά. Τα μοναδικά ονόματα παρατίθενται στη στήλη C στο παραπάνω σύνολο δεδομένων
Διακριτές τιμές/ονόματα είναι αυτά που εμφανίζονται τουλάχιστον μία φορά στο σύνολο δεδομένων. Έτσι, εάν ένα όνομα εμφανίζεται τρεις φορές, εξακολουθεί να υπολογίζεται ως ένα ξεχωριστό όνομα. Αυτό μπορεί να επιτευχθεί αφαιρώντας τις διπλές τιμές/ονόματα και διατηρώντας όλες τις ξεχωριστές. Τα ξεχωριστά ονόματα παρατίθενται στη στήλη Β στο παραπάνω σύνολο δεδομένων.
Με βάση αυτό που έχω δει, τις περισσότερες φορές όταν οι άνθρωποι λένε ότι θέλουν να πάρουν το μοναδικό μέτρημα σε έναν Συγκεντρωτικό Πίνακα, στην πραγματικότητα εννοούν ξεχωριστή καταμέτρηση, κάτι που καλύπτω σε αυτό το σεμινάριο.Μετρήστε ξεχωριστές τιμές στον περιστροφικό πίνακα του Excel
Ας υποθέσουμε ότι έχετε τα δεδομένα πωλήσεων όπως φαίνονται παρακάτω:
Κάντε κλικ εδώ για να κάνετε λήψη του παραδείγματος αρχείου και ακολουθήστε
Με το παραπάνω σύνολο δεδομένων, ας πούμε ότι θέλετε να βρείτε την απάντηση στις ακόλουθες ερωτήσεις:
- Πόσοι αντιπρόσωποι πωλήσεων υπάρχουν σε κάθε περιοχή (που δεν είναι παρά ο ξεχωριστός αριθμός πωλήσεων σε κάθε περιοχή);
- Πόσοι αντιπρόσωποι πωλήσεων πούλησαν τον εκτυπωτή το 2021-2022;
Ενώ οι Συγκεντρωτικοί Πίνακες μπορούν να συνοψίσουν αμέσως τα δεδομένα με μερικά κλικ, για να λάβετε τον αριθμό των διακριτών τιμών, θα χρειαστεί να κάνετε μερικά ακόμη βήματα.
Εάν χρησιμοποιείτε Excel 2013 ή εκδόσεις μετά από αυτό, υπάρχει μια ενσωματωμένη λειτουργικότητα στον Συγκεντρωτικό πίνακα που σας δίνει γρήγορα τον ξεχωριστό αριθμό. Και αν χρησιμοποιείτε Excel 2010 ή εκδόσεις πριν από αυτό, θα πρέπει να τροποποιήσετε τα δεδομένα προέλευσης προσθέτοντας μια στήλη βοηθού.Οι ακόλουθες δύο μέθοδοι καλύπτονται σε αυτό το σεμινάριο:
- Προσθήκη στήλης βοηθού στο αρχικό σύνολο δεδομένων για καταμέτρηση μοναδικών τιμών (λειτουργεί σε όλες τις εκδόσεις).
- Προσθήκη δεδομένων σε μοντέλο δεδομένων και χρήση της επιλογής Διακριτής μέτρησης (διατίθεται στο Excel 2013 και εκδόσεις μετά από αυτό).
Υπάρχει μια τρίτη μέθοδος την οποία ο Ρότζερ δείχνει σε αυτό το άρθρο (την οποία αποκαλεί η μέθοδος περιστροφής ο περιστροφικός πίνακας).
Ας αρχίσουμε!
Προσθήκη στήλης βοηθού στο σύνολο δεδομένων
Σημείωση: Εάν χρησιμοποιείτε Excel 2013 και νεότερες εκδόσεις, παραλείψτε αυτήν τη μέθοδο και μεταβείτε στην επόμενη (καθώς χρησιμοποιεί μια ενσωματωμένη λειτουργία Συγκεντρωτικού πίνακα - Διακριτός Κόμης).Αυτός είναι ένας εύκολος τρόπος για να μετρήσετε διαφορετικές τιμές στον Συγκεντρωτικό πίνακα καθώς χρειάζεται μόνο να προσθέσετε μια στήλη βοηθού στα δεδομένα προέλευσης. Αφού προσθέσετε μια στήλη βοηθού, μπορείτε στη συνέχεια να χρησιμοποιήσετε αυτό το νέο σύνολο δεδομένων για να υπολογίσετε τον διακριτό αριθμό.
Παρόλο που αυτός είναι ένας εύκολος τρόπος αντιμετώπισης, υπάρχουν ορισμένα μειονεκτήματα αυτής της μεθόδου (που καλύπτονται αργότερα σε αυτό το σεμινάριο).
Επιτρέψτε μου πρώτα να σας δείξω πώς να προσθέσετε μια στήλη βοηθού και να λάβετε μια ξεχωριστή καταμέτρηση.
Ας υποθέσουμε ότι έχω το σύνολο δεδομένων όπως φαίνεται παρακάτω:
Προσθέστε τον ακόλουθο τύπο στη στήλη F και εφαρμόστε τον για όλα τα κελιά που έχουν δεδομένα στις παρακείμενες στήλες.
= ΑΝ (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)
Ο παραπάνω τύπος χρησιμοποιεί τη συνάρτηση COUNTIFS για να μετρήσει πόσες φορές εμφανίζεται ένα όνομα στη δεδομένη περιοχή. Επίσης, σημειώστε ότι το εύρος κριτηρίων είναι $ C $ 2: C2 και $ B $ 2: B2. Αυτό σημαίνει ότι συνεχίζει να επεκτείνεται καθώς κατεβαίνετε τη στήλη.
Για παράδειγμα, στο κελί Ε2, τα εύρη κριτηρίων είναι $ C $ 2: C2 και $ B $ 2: B2 και στο κελί E3 αυτά τα εύρη επεκτείνονται σε $ C $ 2: C3 και $ B $ 2: B3.
Αυτό διασφαλίζει ότι η συνάρτηση COUNTIFS μετρά την πρώτη παρουσία ενός ονόματος ως 1, τη δεύτερη παρουσία του ονόματος ως 2 και ούτω καθεξής.
Δεδομένου ότι θέλουμε να λάβουμε μόνο τα διακριτά ονόματα, χρησιμοποιείται η συνάρτηση IF που επιστρέφει 1 όταν εμφανίζεται ένα όνομα για μια περιοχή την πρώτη φορά και επιστρέφει 0 όταν εμφανίζεται ξανά. Αυτό διασφαλίζει ότι μετρώνται μόνο διακριτά ονόματα και όχι οι επαναλήψεις.
Παρακάτω είναι πώς θα ήταν το σύνολο δεδομένων σας όταν προσθέσατε τη στήλη βοηθού.
Τώρα που έχουμε τροποποιήσει τα δεδομένα προέλευσης, μπορούμε να το χρησιμοποιήσουμε για να δημιουργήσουμε έναν Συγκεντρωτικό πίνακα και να χρησιμοποιήσουμε τη στήλη βοηθού για να λάβουμε τον ξεχωριστό αριθμό των αντιπροσώπων πωλήσεων σε κάθε περιοχή.
Παρακάτω είναι τα βήματα για να το κάνετε αυτό:
- Επιλέξτε οποιοδήποτε κελί στο σύνολο δεδομένων.
- Κάντε κλικ στην καρτέλα Εισαγωγή.
- Κάντε κλικ στον Συγκεντρωτικό πίνακα (ή χρησιμοποιήστε τη συντόμευση πληκτρολογίου - ALT + N + V)
- Στο παράθυρο διαλόγου Δημιουργία συγκεντρωτικού πίνακα, βεβαιωθείτε ότι ο πίνακας/εύρος είναι σωστός (και περιλαμβάνει τη στήλη βοηθού) και «Νέο φύλλο εργασίας» στο επιλεγμένο.
- Κάντε κλικ στο OK.
Τα παραπάνω βήματα θα εισάγουν ένα νέο φύλλο το οποίο έχει τον Συγκεντρωτικό πίνακα.
Σύρετε το πεδίο «Περιοχή» στην περιοχή Σειρές και το πεδίο «Αρίθμηση D» στην περιοχή Τιμές.
Θα λάβετε έναν Συγκεντρωτικό Πίνακα όπως φαίνεται παρακάτω:
Τώρα μπορείτε να αλλάξετε την κεφαλίδα στήλης από «Άθροισμα μέτρησης D» σε «Αντιπρόσωπος πωλήσεων».
Μειονεκτήματα της χρήσης στήλης βοηθού:
Ενώ αυτή η μέθοδος είναι αρκετά απλή, πρέπει να επισημάνω μερικά μειονεκτήματα που προκύπτουν από την τροποποίηση των δεδομένων προέλευσης σε έναν Συγκεντρωτικό πίνακα:
- Η προέλευση δεδομένων με τη στήλη βοηθού δεν είναι τόσο δυναμική όσο ένας Συγκεντρωτικός πίνακας. Ενώ μπορείτε να κόψετε και να κόψετε τα δεδομένα με οποιονδήποτε τρόπο με έναν Συγκεντρωτικό πίνακα, όταν χρησιμοποιείτε μια στήλη βοηθού, χάνετε ένα μέρος αυτής της ικανότητας. Ας υποθέσουμε ότι προσθέτετε μια στήλη βοηθού για να λάβετε τον αριθμό ενός ξεχωριστού εκπροσώπου πωλήσεων σε κάθε περιοχή. Τώρα, τι γίνεται αν θέλετε επίσης να έχετε τον ξεχωριστό αριθμό πωλήσεων εκτυπωτών πώλησης. Θα πρέπει να επιστρέψετε στα δεδομένα προέλευσης και να τροποποιήσετε τον τύπο στήλης βοηθού (ή να προσθέσετε μια νέα στήλη βοηθού).
- Δεδομένου ότι προσθέτετε περισσότερα δεδομένα στην πηγή Συγκεντρωτικού πίνακα (η οποία προστίθεται επίσης στην Συγκεντρωτική κρυφή μνήμη), αυτό μπορεί να οδηγήσει σε υψηλότερο μέγεθος αρχείου Excel.
- Δεδομένου ότι χρησιμοποιούμε έναν τύπο Excel, ενδέχεται να καθυστερήσει το βιβλίο εργασίας του Excel σε περίπτωση που έχετε χιλιάδες σειρές δεδομένων.
Προσθέστε δεδομένα στο μοντέλο δεδομένων και συνοψίστε τη χρήση διακριτού αριθμού
Ο Συγκεντρωτικός Πίνακας πρόσθεσε νέα λειτουργικότητα στο Excel 2013 που σας επιτρέπει να λάβετε το διακριτό πλήθος ενώ συνοψίζετε το σύνολο δεδομένων.
Σε περίπτωση που χρησιμοποιείτε προηγούμενη έκδοση, δεν θα μπορείτε να χρησιμοποιήσετε αυτήν τη μέθοδο (όπως θα πρέπει να προσπαθήσετε να προσθέσετε τη στήλη βοηθού όπως φαίνεται στη μέθοδο παραπάνω από αυτήν).
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να λάβετε τον αριθμό των μοναδικών αντιπροσώπων πωλήσεων σε κάθε περιοχή.
Παρακάτω είναι τα βήματα για να λάβετε μια ξεχωριστή τιμή καταμέτρησης στον Συγκεντρωτικό πίνακα:
- Επιλέξτε οποιοδήποτε κελί στο σύνολο δεδομένων.
- Κάντε κλικ στην καρτέλα Εισαγωγή.
- Κάντε κλικ στον Συγκεντρωτικό πίνακα (ή χρησιμοποιήστε τη συντόμευση πληκτρολογίου - ALT + N + V)
- Στο παράθυρο διαλόγου Δημιουργία Συγκεντρωτικού Πίνακα, βεβαιωθείτε ότι ο Πίνακας/Εύρος είναι σωστός και Νέο φύλλο εργασίας στο Επιλεγμένο.
- Επιλέξτε το πλαίσιο που λέει - "Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων"
- Κάντε κλικ στο OK.
Τα παραπάνω βήματα θα εισάγουν ένα νέο φύλλο το οποίο έχει τον νέο Συγκεντρωτικό πίνακα.
Σύρετε την περιοχή στην περιοχή Σειρές και Αντιπρόσωπος πωλήσεων στην περιοχή Τιμές. Θα λάβετε έναν Συγκεντρωτικό Πίνακα όπως φαίνεται παρακάτω:
Ο παραπάνω Συγκεντρωτικός Πίνακας δίνει το συνολικό αριθμό των εκπροσώπων πωλήσεων σε κάθε περιοχή (και όχι τον ξεχωριστό αριθμό).
Για να λάβετε τον ξεχωριστό αριθμό στον Συγκεντρωτικό πίνακα, ακολουθήστε τα παρακάτω βήματα:
- Κάντε δεξιό κλικ σε οποιοδήποτε κελί στη στήλη "Αριθμός αντιπροσώπων πωλήσεων".
- Κάντε κλικ στις Ρυθμίσεις πεδίου τιμής
- Στο παράθυρο διαλόγου Ρυθμίσεις πεδίου τιμής, επιλέξτε «Διακριτός αριθμός» ως τύπος υπολογισμού (ίσως χρειαστεί να μετακινηθείτε προς τα κάτω στη λίστα για να τον βρείτε).
- Κάντε κλικ στο OK.
Θα παρατηρήσετε ότι το όνομα της στήλης αλλάζει από ‘Count of Sales Rep’ σε ‘Distinct Count of Sales Rep’. Μπορείτε να το αλλάξετε σε ό, τι θέλετε.
Ορισμένα πράγματα που γνωρίζετε όταν προσθέτετε τα δεδομένα σας στο μοντέλο δεδομένων:
- Εάν αποθηκεύσετε τα δεδομένα σας στο μοντέλο δεδομένων και, στη συνέχεια, ανοίξετε σε μια παλαιότερη έκδοση του Excel, θα εμφανιστεί μια προειδοποίηση - "Ορισμένες λειτουργίες πίνακα περιστροφής δεν θα αποθηκευτούν". Ενδέχεται να μην βλέπετε τον διακριτό αριθμό (και το μοντέλο δεδομένων) όταν ανοίγετε σε παλαιότερη έκδοση που δεν το υποστηρίζει.
- Όταν προσθέτετε τα δεδομένα σας σε ένα μοντέλο δεδομένων και δημιουργείτε έναν Συγκεντρωτικό πίνακα, δεν θα εμφανίζονται οι επιλογές προσθήκης υπολογιζόμενων πεδίων και υπολογιζόμενων στηλών.
Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου
Τι γίνεται αν θέλετε να μετρήσετε μοναδικές αξίες (και όχι ξεχωριστές τιμές);
Εάν θέλετε να μετρήσετε μοναδικές τιμές, δεν έχετε ενσωματωμένη λειτουργικότητα στον Συγκεντρωτικό πίνακα και θα πρέπει να βασίζεστε μόνο σε στήλες βοηθού.
Θυμηθείτε - Οι μοναδικές τιμές και οι ξεχωριστές αξίες δεν είναι οι ίδιες. Κάντε κλικ εδώ για να μάθετε τη διαφορά.Ένα παράδειγμα θα μπορούσε να είναι όταν έχετε το παρακάτω σύνολο δεδομένων και θέλετε να μάθετε πόσες αντιπροσωπείες πωλήσεων είναι μοναδικές για κάθε περιοχή. Αυτό σημαίνει ότι λειτουργούν μόνο σε μια συγκεκριμένη περιοχή και όχι στις άλλες.
Σε τέτοιες περιπτώσεις, πρέπει να δημιουργήσετε μία από περισσότερες από μία στήλες βοηθού.
Για αυτήν την περίπτωση, ο παρακάτω τύπος κάνει το κόλπο:
= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)
Ο παραπάνω τύπος ελέγχει εάν ένα όνομα αντιπροσώπου πωλήσεων εμφανίζεται σε μία μόνο περιοχή ή σε περισσότερες από μία περιοχές. Το κάνει μετρώντας τον αριθμό εμφάνισης ενός ονόματος σε μια περιοχή και διαιρώντας το με το συνολικό αριθμό εμφανίσεων του ονόματος. Εάν η τιμή είναι μικρότερη από 1, υποδεικνύει ότι το όνομα εμφανίζεται σε δύο ή περισσότερες από δύο περιοχές.
Σε περίπτωση που το όνομα εμφανίζεται σε περισσότερες από μία περιοχές, επιστρέφει 0 άλλο επιστρέφει ένα.
Ο τύπος ελέγχει επίσης εάν το όνομα επαναλαμβάνεται στην ίδια περιοχή ή όχι. Εάν το όνομα επαναληφθεί, μόνο η πρώτη εμφάνιση του ονόματος επιστρέφει την τιμή 1 και όλες οι άλλες περιπτώσεις επιστρέφουν 0.
Αυτό μπορεί να φαίνεται λίγο περίπλοκο, αλλά εξαρτάται και πάλι από το τι προσπαθείτε να επιτύχετε.
Έτσι, εάν θέλετε να μετρήσετε μοναδικές τιμές σε έναν Συγκεντρωτικό πίνακα, χρησιμοποιήστε στήλες βοηθού και αν θέλετε να μετρήσετε ξεχωριστές τιμές, μπορείτε να χρησιμοποιήσετε την ενσωματωμένη λειτουργικότητα (στο Excel 2013 και παραπάνω) ή να χρησιμοποιήσετε μια στήλη βοηθού.
Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου
Μπορεί επίσης να σας αρέσουν τα ακόλουθα μαθήματα περιστροφικού πίνακα:
- Πώς να φιλτράρετε δεδομένα σε έναν Συγκεντρωτικό πίνακα στο Excel
- Πώς να ομαδοποιήσετε ημερομηνίες σε συγκεντρωτικούς πίνακες στο Excel
- Πώς να ομαδοποιήσετε αριθμούς στον Συγκεντρωτικό πίνακα στο Excel
- Πώς να εφαρμόσετε μορφοποίηση υπό όρους σε έναν Συγκεντρωτικό πίνακα στο Excel
- Τεμαχιστές στον περιστροφικό πίνακα του Excel
- Πώς να ανανεώσετε τον συγκεντρωτικό πίνακα στο Excel
- Διαγράψτε έναν Συγκεντρωτικό Πίνακα στο Excel
