Εάν διαβάζετε αυτό το σεμινάριο, υπάρχει μια μεγάλη πιθανότητα να έχετε ακούσει (ή ακόμα και να χρησιμοποιήσετε) τον Συγκεντρωτικό πίνακα του Excel. Είναι μια από τις πιο ισχυρές δυνατότητες στο Excel (χωρίς πλάκα).
Το καλύτερο μέρος για τη χρήση ενός Συγκεντρωτικού Πίνακα είναι ότι ακόμη και αν δεν γνωρίζετε τίποτα στο Excel, μπορείτε ακόμα να κάνετε αρκετά φοβερά πράγματα με αυτό, με μια πολύ βασική κατανόηση.
Ας αρχίσουμε.
Κάντε κλικ ΕΔΩ για λήψη των δειγμάτων δεδομένων και συνέχεια.
Τι είναι ένας περιστρεφόμενος πίνακας και γιατί πρέπει να τον φροντίζετε;
Ένας Συγκεντρωτικός Πίνακας είναι ένα εργαλείο στο Microsoft Excel που σας επιτρέπει να συνοψίσετε γρήγορα τεράστια σύνολα δεδομένων (με λίγα κλικ).
Ακόμα κι αν είστε εντελώς νέοι στον κόσμο του Excel, μπορείτε εύκολα να χρησιμοποιήσετε έναν Συγκεντρωτικό πίνακα. Είναι τόσο εύκολο όσο το σύρσιμο και η απόθεση κεφαλίδων γραμμών/στηλών για τη δημιουργία αναφορών.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:
Πρόκειται για δεδομένα πωλήσεων που αποτελούνται από ~ 1000 σειρές.
Διαθέτει τα δεδομένα πωλήσεων ανά περιοχή, τύπο λιανοπωλητή και πελάτη.
Τώρα το αφεντικό σας μπορεί να θέλει να μάθει μερικά πράγματα από αυτά τα δεδομένα:
- Ποιες ήταν οι συνολικές πωλήσεις στη νότια περιοχή το 2016;
- Ποιοι είναι οι πέντε κορυφαίοι λιανοπωλητές κατά πωλήσεις;
- Πώς συγκρίθηκε η απόδοση του The Home Depot έναντι άλλων λιανοπωλητών στο Νότο;
Μπορείτε να προχωρήσετε και να χρησιμοποιήσετε τις λειτουργίες του Excel για να σας δώσουν τις απαντήσεις σε αυτές τις ερωτήσεις, αλλά τι γίνεται αν ξαφνικά το αφεντικό σας καταλήξει σε μια λίστα με πέντε ακόμη ερωτήσεις.
Θα πρέπει να επιστρέφετε στα δεδομένα και να δημιουργείτε νέους τύπους κάθε φορά που υπάρχει αλλαγή.
Εδώ είναι που είναι πολύ βολικό το Excel Pivot Tables.
Μέσα σε δευτερόλεπτα, ένας Συγκεντρωτικός Πίνακας θα απαντήσει σε όλες αυτές τις ερωτήσεις (όπως θα μάθετε παρακάτω).
Αλλά το πραγματικό όφελος είναι ότι μπορεί να φιλοξενήσει τον επικίνδυνο προϊστάμενό σας με δεδομένα, απαντώντας αμέσως στις ερωτήσεις του.
Είναι τόσο απλό, μπορείτε επίσης να αφιερώσετε λίγα λεπτά και να δείξετε στο αφεντικό σας πώς να το κάνει μόνος του.
Ας ελπίσουμε ότι τώρα έχετε μια ιδέα για το γιατί οι Συγκεντρωτικοί Πίνακες είναι τόσο υπέροχοι. Ας προχωρήσουμε και δημιουργήσουμε έναν Συγκεντρωτικό Πίνακα χρησιμοποιώντας το σύνολο δεδομένων (φαίνεται παραπάνω).
Εισαγωγή περιστροφικού πίνακα στο Excel
Ακολουθούν τα βήματα για τη δημιουργία ενός περιστροφικού πίνακα χρησιμοποιώντας τα δεδομένα που εμφανίζονται παραπάνω:
- Κάντε κλικ οπουδήποτε στο σύνολο δεδομένων.
- Μεταβείτε στην επιλογή Εισαγωγή -> Πίνακες -> Συγκεντρωτικός πίνακας.
- Στο παράθυρο διαλόγου Δημιουργία συγκεντρωτικού πίνακα, οι προεπιλεγμένες επιλογές λειτουργούν καλά στις περισσότερες περιπτώσεις. Εδώ είναι μερικά πράγματα που πρέπει να ελέγξετε:
- Πίνακας/Εύρος: Συμπληρώνεται από προεπιλογή με βάση το σύνολο δεδομένων σας. Εάν τα δεδομένα σας δεν έχουν κενές γραμμές/στήλες, το Excel θα προσδιορίσει αυτόματα το σωστό εύρος. Μπορείτε να το αλλάξετε χειροκίνητα, εάν χρειάζεται.
- Εάν θέλετε να δημιουργήσετε τον Συγκεντρωτικό πίνακα σε μια συγκεκριμένη τοποθεσία, στην επιλογή «Επιλέξτε πού θέλετε να τοποθετηθεί η αναφορά Συγκεντρωτικού Πίνακα», καθορίστε την τοποθεσία. Διαφορετικά, δημιουργείται ένα νέο φύλλο εργασίας με τον Συγκεντρωτικό πίνακα.
- Κάντε κλικ στο OK.
Μόλις κάνετε κλικ στο OK, δημιουργείται ένα νέο φύλλο εργασίας με τον Συγκεντρωτικό πίνακα.
Ενώ έχει δημιουργηθεί ο Συγκεντρωτικός Πίνακας, δεν θα βλέπετε δεδομένα σε αυτόν. Το μόνο που βλέπετε είναι το όνομα του Συγκεντρωτικού πίνακα και μια εντολή μιας γραμμής στα αριστερά και Πεδία Συγκεντρωτικού πίνακα στα δεξιά.
Τώρα, προτού προχωρήσουμε στην ανάλυση δεδομένων χρησιμοποιώντας αυτόν τον Συγκεντρωτικό Πίνακα, ας καταλάβουμε ποια είναι τα παξιμάδια και τα μπουλόνια που κάνουν έναν Συγκεντρωτικό Πίνακα του Excel.
Τα παξιμάδια και τα μπουλόνια ενός περιστρεφόμενου πίνακα Excel
Για να χρησιμοποιήσετε αποτελεσματικά έναν Συγκεντρωτικό πίνακα, είναι σημαντικό να γνωρίζετε τα στοιχεία που δημιουργούν έναν περιστροφικό πίνακα.
Σε αυτήν την ενότητα, θα μάθετε για:
- Κρυφή μνήμη περιστροφής
- Τιμή Περιοχή
- Περιοχή σειρών
- Περιοχή στηλών
- Περιοχή φίλτρων
Κρυφή μνήμη περιστροφής
Μόλις δημιουργήσετε έναν Συγκεντρωτικό Πίνακα χρησιμοποιώντας τα δεδομένα, κάτι συμβαίνει στο πίσω μέρος. Το Excel λαμβάνει ένα στιγμιότυπο των δεδομένων και τα αποθηκεύει στη μνήμη του. Αυτό το στιγμιότυπο ονομάζεται Pivot Cache.
Όταν δημιουργείτε διαφορετικές προβολές χρησιμοποιώντας έναν Συγκεντρωτικό πίνακα, το Excel δεν επιστρέφει στην προέλευση δεδομένων, αλλά χρησιμοποιεί την Συγκεντρωτική προσωρινή μνήμη για να αναλύσει γρήγορα τα δεδομένα και να σας δώσει τη σύνοψη/αποτελέσματα.
Ο λόγος για τον οποίο δημιουργείται μια κεντρική μνήμη περιστροφής είναι η βελτιστοποίηση της λειτουργίας του περιστροφικού πίνακα. Ακόμα και όταν έχετε χιλιάδες σειρές δεδομένων, ένας περιστροφικός πίνακας είναι εξαιρετικά γρήγορος στη σύνοψη των δεδομένων. Μπορείτε να μεταφέρετε και να αποθέτετε στοιχεία στα πλαίσια γραμμών/στηλών/τιμών/φίλτρων και θα ενημερώσει άμεσα τα αποτελέσματα.
Σημείωση: Ένα μειονέκτημα της προσωρινής μνήμης περιστροφής είναι ότι αυξάνει το μέγεθος του βιβλίου εργασίας σας. Δεδομένου ότι είναι ένα αντίγραφο των δεδομένων προέλευσης, όταν δημιουργείτε έναν περιστρεφόμενο πίνακα, ένα αντίγραφο αυτών των δεδομένων αποθηκεύεται στο Pivot Cache.
Διαβάστε περισσότερα: Τι είναι το Pivot Cache και πώς να το χρησιμοποιήσετε καλύτερα.
Τιμή Περιοχή
Η περιοχή τιμών είναι αυτή που κρατά τους υπολογισμούς/τιμές.
Με βάση το σύνολο δεδομένων που εμφανίζεται στην αρχή του σεμιναρίου, εάν θέλετε γρήγορα να υπολογίσετε τις συνολικές πωλήσεις ανά περιοχή κάθε μήνα, μπορείτε να λάβετε έναν συγκεντρωτικό πίνακα όπως φαίνεται παρακάτω (θα δούμε πώς θα το δημιουργήσουμε αργότερα στο σεμινάριο) .
Η περιοχή που επισημαίνεται με πορτοκαλί είναι η περιοχή τιμών.
Σε αυτό το παράδειγμα, έχει τις συνολικές πωλήσεις κάθε μήνα για τις τέσσερις περιοχές.
Περιοχή σειρών
Οι επικεφαλίδες στα αριστερά της περιοχής Αξίες κάνουν την περιοχή Σειρές.
Στο παρακάτω παράδειγμα, η περιοχή Rows περιέχει τις περιοχές (επισημαίνονται με κόκκινο χρώμα):
Περιοχή στηλών
Οι επικεφαλίδες στην κορυφή της περιοχής Αξίες κάνουν την περιοχή Στήλες.
Στο παρακάτω παράδειγμα, η περιοχή Στήλες περιέχει τους μήνες (επισημαίνονται με κόκκινο χρώμα):
Περιοχή φίλτρων
Η περιοχή Φίλτρα είναι ένα προαιρετικό φίλτρο που μπορείτε να χρησιμοποιήσετε για περαιτέρω επεξεργασία στο σύνολο δεδομένων.
Για παράδειγμα, εάν θέλετε να δείτε τις πωλήσεις μόνο για λιανοπωλητές Multiline, μπορείτε να επιλέξετε αυτήν την επιλογή από το αναπτυσσόμενο μενού (επισημαίνεται στην παρακάτω εικόνα) και ο Συγκεντρωτικός πίνακας θα ενημερώνεται με τα δεδομένα μόνο για λιανοπωλητές Multiline.
Ανάλυση δεδομένων χρησιμοποιώντας τον Συγκεντρωτικό πίνακα
Τώρα, ας προσπαθήσουμε να απαντήσουμε στις ερωτήσεις χρησιμοποιώντας τον Συγκεντρωτικό πίνακα που έχουμε δημιουργήσει.
Κάντε κλικ ΕΔΩ για λήψη των δειγμάτων δεδομένων και συνέχεια.
Για να αναλύσετε δεδομένα χρησιμοποιώντας έναν Συγκεντρωτικό πίνακα, πρέπει να αποφασίσετε πώς θέλετε να φαίνεται η σύνοψη δεδομένων στο τελικό αποτέλεσμα. Για παράδειγμα, μπορεί να θέλετε όλες οι περιοχές στα αριστερά και οι συνολικές πωλήσεις ακριβώς δίπλα. Μόλις έχετε αυτήν τη σαφήνεια στο μυαλό σας, μπορείτε απλά να σύρετε και να αποθέσετε τα σχετικά πεδία στον Συγκεντρωτικό πίνακα.
Στην ενότητα Πεδίων Συγκεντρωτικού πίνακα, έχετε τα πεδία και τις περιοχές (όπως επισημαίνεται παρακάτω):
Τα πεδία δημιουργούνται με βάση τα δεδομένα υποστρώματος που χρησιμοποιούνται για τον Συγκεντρωτικό πίνακα. Η ενότητα Περιοχές είναι το σημείο όπου τοποθετείτε τα πεδία και σύμφωνα με το πού πηγαίνει ένα πεδίο, τα δεδομένα σας ενημερώνονται στον Συγκεντρωτικό πίνακα.
Είναι ένας απλός μηχανισμός μεταφοράς και απόθεσης, όπου μπορείτε απλά να σύρετε ένα πεδίο και να το τοποθετήσετε σε μία από τις τέσσερις περιοχές. Μόλις το κάνετε αυτό, θα εμφανιστεί στον Συγκεντρωτικό πίνακα στο φύλλο εργασίας.
Τώρα ας προσπαθήσουμε να απαντήσουμε στις ερωτήσεις που είχε ο διαχειριστής σας χρησιμοποιώντας αυτόν τον Συγκεντρωτικό πίνακα.
Ε1: Ποιες ήταν οι συνολικές πωλήσεις στη νότια περιοχή;
Σύρετε το πεδίο Περιοχή στην περιοχή Σειρές και το πεδίο Έσοδα στην περιοχή Τιμές. Θα ενημερώνει αυτόματα τον Συγκεντρωτικό πίνακα στο φύλλο εργασίας.
Λάβετε υπόψη ότι μόλις εγκαταλείψετε το πεδίο Έσοδα στην περιοχή Τιμές, γίνεται άθροισμα εσόδων. Από προεπιλογή, το Excel αθροίζει όλες τις τιμές για μια δεδομένη περιοχή και εμφανίζει το σύνολο. Εάν θέλετε, μπορείτε να το αλλάξετε σε μετρήσεις μέτρησης, μέσου όρου ή άλλων στατιστικών στοιχείων. Σε αυτή την περίπτωση, το άθροισμα είναι αυτό που χρειαζόμασταν.
Η απάντηση σε αυτήν την ερώτηση θα ήταν 21225800.
Ε2 Ποιοι είναι οι πέντε κορυφαίοι λιανοπωλητές κατά πωλήσεις;
Σύρετε το πεδίο Πελάτης στην περιοχή Σειρά και το πεδίο Έσοδα στην περιοχή τιμών. Σε περίπτωση που υπάρχουν άλλα πεδία στην ενότητα περιοχή και θέλετε να το καταργήσετε, απλά επιλέξτε το και σύρετέ το έξω από αυτό.
Θα λάβετε έναν Συγκεντρωτικό Πίνακα όπως φαίνεται παρακάτω:
Σημειώστε ότι από προεπιλογή, τα στοιχεία (στην περίπτωση αυτή οι πελάτες) ταξινομούνται με αλφαβητική σειρά.
Για να αποκτήσετε τους πέντε κορυφαίους λιανοπωλητές, μπορείτε απλά να ταξινομήσετε αυτήν τη λίστα και να χρησιμοποιήσετε τα πέντε πρώτα ονόματα πελατών. Για να το κάνω αυτό:
- Κάντε δεξί κλικ σε οποιοδήποτε κελί στην περιοχή Τιμές.
- Μεταβείτε στην επιλογή Ταξινόμηση -> Ταξινόμηση μεγαλύτερου σε μικρότερου.
Αυτό θα σας δώσει μια ταξινομημένη λίστα με βάση τις συνολικές πωλήσεις.
Ε3: Πώς συγκρίθηκε η απόδοση του The Home Depot έναντι άλλων λιανοπωλητών στο Νότο;
Μπορείτε να κάνετε πολλές αναλύσεις για αυτήν την ερώτηση, αλλά εδώ ας προσπαθήσουμε να συγκρίνουμε τις πωλήσεις.
Σύρετε το Πεδίο Περιοχής στην περιοχή Σειρές. Τώρα σύρετε το πεδίο Πελάτης στην περιοχή Σειρές κάτω από το πεδίο Περιοχή. Όταν το κάνετε αυτό, το Excel καταλαβαίνει ότι θέλετε να κατηγοριοποιήσετε τα δεδομένα σας πρώτα ανά περιοχή και στη συνέχεια κατά πελάτες εντός των περιοχών. Θα έχετε κάτι όπως φαίνεται παρακάτω:
Τώρα σύρετε το πεδίο Έσοδα στην περιοχή Αξίες και θα έχετε τις πωλήσεις για κάθε πελάτη (καθώς και τη συνολική περιοχή).
Μπορείτε να ταξινομήσετε τους λιανοπωλητές με βάση τα στοιχεία πωλήσεων ακολουθώντας τα παρακάτω βήματα:
- Κάντε δεξί κλικ σε ένα κελί που έχει την τιμή πώλησης για οποιονδήποτε λιανοπωλητή.
- Μεταβείτε στην επιλογή Ταξινόμηση -> Ταξινόμηση μεγαλύτερου σε μικρότερου.
Αυτό θα ταξινομήσει αμέσως όλους τους λιανοπωλητές κατά την αξία πωλήσεων.
Τώρα μπορείτε να σαρώσετε γρήγορα στην περιοχή του Νότου και να προσδιορίσετε ότι οι πωλήσεις του The Home Depot ήταν 3004600 και τα πήγε καλύτερα από τέσσερις λιανοπωλητές στη νότια περιοχή.
Τώρα υπάρχουν περισσότεροι από ένας τρόποι για το δέρμα της γάτας. Μπορείτε επίσης να βάλετε την περιοχή στην περιοχή Φίλτρο και, στη συνέχεια, να επιλέξετε μόνο τη Νότια περιοχή.
Κάντε κλικ ΕΔΩ για λήψη των δειγμάτων δεδομένων.
Ελπίζω ότι αυτό το σεμινάριο θα σας δώσει μια βασική επισκόπηση των περιστροφικών πινάκων του Excel και θα σας βοηθήσει να ξεκινήσετε με αυτό.
Ακολουθούν μερικά ακόμη μαθήματα Pivot Table που μπορεί να σας αρέσουν:
- Προετοιμασία δεδομένων προέλευσης για συγκεντρωτικό πίνακα.
- Πώς να εφαρμόσετε μορφοποίηση υπό όρους σε έναν Συγκεντρωτικό πίνακα στο Excel.
- Πώς να ομαδοποιήσετε ημερομηνίες σε συγκεντρωτικούς πίνακες στο Excel.
- Πώς να ομαδοποιήσετε αριθμούς στον περιστροφικό πίνακα στο Excel.
- Πώς να φιλτράρετε δεδομένα σε έναν Συγκεντρωτικό πίνακα στο Excel.
- Χρήση τεμαχιστών στον περιστροφικό πίνακα του Excel.
- Πώς να αντικαταστήσετε τα κενά κελιά με μηδενικά στους περιστρεφόμενους πίνακες του Excel.
- Πώς να προσθέσετε και να χρησιμοποιήσετε υπολογισμένα πεδία περιστροφικού πίνακα Excel.
- Πώς να ανανεώσετε τον συγκεντρωτικό πίνακα στο Excel.
