Οι Συγκεντρωτικοί Πίνακες είναι ένα από τα πιο ισχυρά χαρακτηριστικά του excel. Ακόμα κι αν είστε αρχάριος, μπορείτε να τρυπώσετε μεγάλες ποσότητες δεδομένων σε χρήσιμες πληροφορίες. Ο Συγκεντρωτικός Πίνακας μπορεί να σας βοηθήσει να κάνετε αναφορές σε λίγα λεπτά. Αναλύστε εύκολα τα καθαρά δεδομένα σας και εάν τα δεδομένα δεν είναι καθαρά, μπορεί να σας βοηθήσει να καθαρίσετε τα δεδομένα σας. Δεν θέλω να σας κουράσω, οπότε ας μπούμε σε αυτό και να εξερευνήσουμε.
Πώς να δημιουργήσετε έναν Συγκεντρωτικό Πίνακα
Είναι απλό. Απλώς επιλέξτε τα δεδομένα σας. Μεταβείτε στην επιλογή Εισαγωγή. Κάντε κλικ στον Συγκεντρωτικό πίνακα και τελείωσε.
Αλλά περίμενε. Πριν δημιουργήσετε έναν περιστροφικό πίνακα, βεβαιωθείτε ότι όλες οι στήλες έχουν επικεφαλίδα.
Εάν οποιαδήποτε επικεφαλίδα στήλης παραμείνει κενή, ο συγκεντρωτικός πίνακας δεν θα δημιουργηθεί και θα περάσει από ένα μήνυμα σφάλματος.
Απαίτηση 1: Όλες οι στήλες πρέπει να έχουν μια επικεφαλίδα για να ξεκινήσετε με τους Συγκεντρωτικούς Πίνακες στο Excel
Θα πρέπει να έχετε οργανώσει τα δεδομένα σας με σωστή επικεφαλίδα. Μόλις το έχετε, μπορείτε να εισαγάγετε τον συγκεντρωτικό πίνακα.
Εισαγάγετε τον Συγκεντρωτικό πίνακα από κορδέλα
Για να εισαγάγετε έναν συγκεντρωτικό πίνακα από το μενού, ακολουθήστε τα εξής βήματα:
1. Επιλέξτε το εύρος δεδομένων σας
2. Μεταβείτε στην καρτέλα εισαγωγής
3. Κάντε κλικ στο εικονίδιο Συγκεντρωτικού πίνακα
4. Θα εμφανιστεί το πλαίσιο επιλογής Δημιουργία Συγκεντρωτικού Πίνακα
5. Εδώ, μπορείτε να δείτε το εύρος δεδομένων που επιλέξατε. Εάν πιστεύετε ότι αυτό δεν είναι το εύρος που θέλετε να επιλέξετε, αλλάξτε απευθείας το μέγεθος της περιοχής σας από εδώ αντί να επιστρέψετε και να επιλέξετε ξανά δεδομένα.
6. Στη συνέχεια, μπορείτε να επιλέξετε πού θέλετε τον κεντρικό πίνακα. Σας συνιστώ να χρησιμοποιήσετε το νέο φύλλο εργασίας, αλλά μπορείτε επίσης να χρησιμοποιήσετε το τρέχον φύλλο εργασίας. Απλώς καθορίστε τη θέση στο πλαίσιο τοποθεσίας.
7. Τώρα αν τελειώσετε με τις ρυθμίσεις, πατήστε το κουμπί OK. Θα έχετε τον περιστροφικό πίνακα σε ένα νέο φύλλο. Απλώς επιλέξτε τα πεδία σας για περιλήψεις. Θα δούμε πώς δημιουργούμε μια σύνοψη δεδομένων χρησιμοποιώντας τον περιστροφικό πίνακα, αλλά πρώτα ας ξεκαθαρίσουμε τα βασικά. Σε αυτό το σεμινάριο Excel pivot table θα μάθετε περισσότερα από ό, τι περιμένετε.
Εισαγωγή συντόμευσης περιστροφικού πίνακα (Alt> N> V)
Αυτή είναι μια διαδοχική συντόμευση πληκτρολογίου για να ανοίξετε τη Δημιουργία Συγκεντρωτικός πίνακας κουτί επιλογών.
Πατήστε το πλήκτρο Alt και αφήστε το. Κτύπημα Ν και απελευθερώστε το. Κτύπημα V και απελευθερώστε το. Θα ανοίξει το πλαίσιο επιλογής Δημιουργία Συγκεντρωτικού Πίνακα.
Τώρα απλώς ακολουθήστε την παραπάνω διαδικασία για να δημιουργήσετε έναν περιστροφικό πίνακα στο excel.
Εισαγωγή συντόμευσης περιστροφικού πίνακα χρησιμοποιώντας την παλιά συντόμευση Excel (Alt> D> P)
Ένα πράγμα που μου αρέσει περισσότερο στο Microsoft Excel είναι ότι σε κάθε νέα έκδοση του Excel εισάγουν νέες δυνατότητες και αλλά δεν απορρίπτουν τα παλιά χαρακτηριστικά (όπως έκανε ο MS με τη νίκη 8. patταν αξιολύπητο). Αυτό επιτρέπει στον παλαιότερο χρήστη να εργάζεται κανονικά σε νέες εκδόσεις όπως παλαιότερα για παλαιότερες εκδόσεις.
Εάν πιέσετε διαδοχικά ALT, D και Ρ στο πληκτρολόγιο, το Excel θα ανοίξει για να δημιουργήσει έναν οδηγό περιστρεφόμενου πίνακα.
Επιλέξτε την κατάλληλη επιλογή. Η επιλεγμένη επιλογή στο παραπάνω στιγμιότυπο οθόνης θα μας οδηγήσει στη δημιουργία ενός περιστροφικού πίνακα όπως δημιουργήσαμε πριν.
Πατήστε Enter ή κάντε κλικ στο κουμπί Επόμενο εάν θέλετε να ελέγξετε το επιλεγμένο εύρος.
Πατήστε ξανά Enter.
Επιλέξτε ένα νέο φύλλο εργασίας ή όπου θέλετε ο Συγκεντρωτικός σας πίνακας να πατήσει Enter. Και έγινε.
Δημιουργήστε αναφορές χρησιμοποιώντας Συγκεντρωτικούς Πίνακες.
Τώρα ξέρετε πώς να εισαγάγετε έναν συγκεντρωτικό πίνακα. Ας προετοιμαστούμε για να κάνουμε αναφορές χρησιμοποιώντας περιστροφικούς πίνακες σε λίγα λεπτά.
Έχουμε τα δεδομένα για τη στατική παραγγελία.
Τα πεδία της στήλης είναι:
Ημερομηνία παραγγελίας: Ημερομηνία παραγγελίας (Προφανώς)
Περιοχή: Η περιοχή της τάξης στη χώρα
Πελάτης: Όνομα πελάτη (τι αλλο μπορει να ειναι)
Είδος: Παραγγελία αντικειμένου
Μονάδα: Αριθμός μονάδων ενός προϊόντος που παραγγέλθηκε
Κόστος μονάδας: Ανά μονάδα κόστους
Σύνολο: Συνολικό κόστος της παραγγελίας (μονάδα*κόστος μονάδας).
Περιοχές περιστροφής: Αυτές οι 4 περιοχές χρησιμοποιούνται για την εμφάνιση των δεδομένων σας με τρόπο μελετημένο.
ΦΙΛΤΡΑ: Βάλτε εδώ πεδία από τα οποία θα θέλατε να εφαρμόσετε φίλτρα στην αναφορά σας.
ΣΤΗΛΕΣ: Τοποθετήστε τα πεδία εδώ που θέλετε στις στήλες σας στην αναφορά: (Είναι καλύτερα να εμφανίζονται παρά να εξηγούνται)
ΣΕΙΡΕΣ: Σύρετε τα πεδία που θέλετε να εμφανίζονται ακατέργαστα όπως στην παραπάνω εικόνα, έχω δείξει την περιοχή σε γραμμές.
ΑΞΙΕΣ: Επιλέξτε ένα πεδίο για να λάβετε Αριθμός, Άθροισμα, Μέσος όρος, Ποσοστό (και πολλά άλλα) κ.λπ. που θέλετε να δείτε.
Χρησιμοποιώντας τώρα τις παραπάνω πληροφορίες, ετοιμάσαμε αυτήν την αναφορά γρήγορης περιστροφής που δείχνει από ποια περιοχή πόσες παραγγελίες τοποθετούνται για κάθε είδος.
Τώρα που καταλαβαίνετε τα δεδομένα σας και τα πεδία περιστροφής (άλλωστε είστε έξυπνοι), ας απαντήσουμε γρήγορα σε μερικές ερωτήσεις που σχετίζονται με αυτά τα δεδομένα χρησιμοποιώντας τον περιστροφικό πίνακα.
Q1. Πόσες παραγγελίες υπάρχουν;
Ο συγκεντρωτικός πίνακας είναι αρχικά κενός, όπως φαίνεται στην παρακάτω εικόνα.
Πρέπει να επιλέξετε πεδία (ονόματα στηλών) στις κατάλληλες περιοχές για να δείτε τη σύνοψη ή τις λεπτομέρειες αυτού του πεδίου.
Τώρα, για να απαντήσω στην παραπάνω ερώτηση, θα επιλέξω Είδος (επιλέξτε οποιαδήποτε στήλη, απλά βεβαιωθείτε ότι δεν έχει κενό κελί μεταξύ) στα πεδία τιμών.
Επιλέγω Είδος από τη λίστα πεδίων και σύρετέ το στο πεδίο Τιμή.
Έχουμε την απάντησή μας. Οι πίνακες περιστροφής μου λένε ότι υπάρχουν συνολικά 43 παραγγελίες. Αυτό είναι σωστό.
Pro Συμβουλή: Θα πρέπει να ελέγξετε τα δεδομένα σας εάν είναι σωστά ή όχι. Εάν αυτός ο αριθμός δεν ταιριάζει με τα δεδομένα, τότε σημαίνει ότι έχετε επιλέξει κάποιο λανθασμένο εύρος ή ότι το πεδίο έχει κενά κελιά.
Πληροφορίες: Το πεδίο τιμών από προεπιλογή μετρά τον αριθμό των καταχωρήσεων σε μια στήλη εάν περιέχει κείμενο και αθροίζει εάν το πεδίο περιέχει μόνο τιμές. Μπορείτε να το αλλάξετε στις ρυθμίσεις του πεδίου τιμών. Πως? Θα σας δούμε αργότερα σε αυτό το Σεμινάριο Συγκεντρωτικού Πίνακα.
Τώρα που επέλεξα Ημερομηνία παραγγελίας στο Valuesarea, δείχνει 42. Αυτό που σημαίνει Ημερομηνία παραγγελίας έχει κενό κελί Από γνωρίζουμε ότι ο συνολικός αριθμός παραγγελιών είναι 43.
Προσδιορίστε τα ανώμαλα δεδομένα χρησιμοποιώντας τους Συγκεντρωτικούς Πίνακες και Καθαρίστε τα.
Ο Συγκεντρωτικός Πίνακας μπορεί να σας βοηθήσει να βρείτε εσφαλμένες πληροφορίες στα δεδομένα.
Τις περισσότερες φορές τα δεδομένα μας προετοιμάζονται από χειριστές εισαγωγής δεδομένων ή από χρήστες που είναι συνήθως παράτυποι και χρειάζονται καθαρισμό για την προετοιμασία ακριβούς αναφοράς και ανάλυσης.
Θα πρέπει πάντα να καθαρίζετε και να προετοιμάζετε τα δεδομένα σας με τρόπο μελετημένο, πριν προετοιμάσετε οποιεσδήποτε αναφορές. Αλλά μερικές φορές μόνο μετά την προετοιμασία της έκθεσης γνωρίζουμε ότι τα δεδομένα μας έχουν κάποια παρατυπία. Έλα, θα σου δείξω πώς…
Ε2: Πείτε τον αριθμό των παραγγελιών από κάθε περιοχή
Τώρα για να απαντήσω σε αυτήν την ερώτηση:
Επιλέγω Περιοχή και σύρετέ το στο Σειρές Περιοχή και Είδος προς το Αξίες Περιοχή.
Θα λάβουμε δεδομένα χωρισμένα σε περιοχή-σοφή. Μπορούμε να απαντήσουμε από ποια περιοχή πόσες παραγγελίες έχουν έρθει.
Υπάρχουν συνολικά 4 περιοχές στα δεδομένα μας σύμφωνα με τον συγκεντρωτικό πίνακα. Αλλά περιμένετε, παρατηρήστε το Κεντρική και Centrle περιοχή. Γνωρίζουμε ότι το Centrle πρέπει να είναι ΚεντρικόςΤο Υπάρχει μια παρατυπία. Πρέπει να πάμε στα δεδομένα μας και να κάνουμε καθαρισμό δεδομένων.
Για να καθαρίσουμε τα δεδομένα στο πεδίο περιοχής φιλτράρουμε το λάθος όνομα περιοχής (Centrle) και το διορθώνουμε (Κεντρικό).
Επιστρέψτε τώρα στα δεδομένα περιστροφής σας.
Κάντε δεξί κλικ οπουδήποτε στον περιστρεφόμενο πίνακα και κάντε κλικ στο Ανανέωση.
Η αναφορά σας έχει πλέον ενημερωθεί.
ΠΛΗΡΟΦΟΡΙΕΣ: Ανεξάρτητα από τις αλλαγές που κάνετε στα δεδομένα προέλευσης, ο συγκεντρωτικός πίνακας θα συνεχίσει να λειτουργεί στα παλιά δεδομένα μέχρι να το ανανεώσεις. Το Excel δημιουργεί μια προσωρινή μνήμη περιστροφής και ένας συγκεντρωτικός πίνακας εκτελείται σε αυτήν την προσωρινή μνήμη. Όταν ανανεώνεται, η παλιά προσωρινή μνήμη αλλάζει με νέα δεδομένα.
Τώρα, μπορείτε να δείτε ότι υπάρχουν μόνο 3 περιοχές στην πραγματικότητα.
Διαμόρφωση περιστροφικής αναφοράς με κατηγοριοποιημένες σειρές.
Μερικές φορές θα χρειαστείτε αναφορές όπως η παραπάνω εικόνα. Αυτό διευκολύνει την προβολή των δεδομένων σας με δομημένο τρόπο. Μπορείτε εύκολα να διακρίνετε από ποια περιοχή πόσα είδη παραγγέλνονται. Ας δούμε πώς μπορείτε να το κάνετε αυτό.
Κίνηση Περιοχή και Είδος προς το ΣΕΙΡΑ περιοχή. Βεβαιωθείτε ότι η περιοχή βρίσκεται στο επάνω μέρος και τα στοιχεία στο κάτω μέρος όπως φαίνεται στην εικόνα.
Σέρνω Είδος Για αξία Περιοχή.
Ως αποτέλεσμα, θα λάβετε αυτήν την αναφορά.
Θα κάνει. Αλλά μερικές φορές το αφεντικό σας θέλει να αναφέρει σε μορφή πίνακα χωρίς υποσύνολα. Για να γίνει αυτό πρέπει να μορφοποιήσουμε τον Συγκεντρωτικό πίνακα.
Αφαίρεση υποσυνολικών από τον Συγκεντρωτικό πίνακα
Ακολουθήστε αυτά τα βήματα:
1. Κάντε κλικ οπουδήποτε στον περιστροφικό πίνακα.
2. Πηγαίνετε στο Σχέδιο Αυτί.
3. Κάντε κλικ στο μενού Υποσύνολα.
4. Κάντε κλικ στο Μην εμφανίζετε υποσύνολα.
Μπορείτε να δείτε ότι τώρα δεν υπάρχουν υποσύνολα.
Πρόστιμο. Αλλά δεν είναι ακόμα σε μορφή πίνακα. Οι περιοχές και τα στοιχεία εμφανίζονται σε μία μόνο στήλη. Δείξτε τα ξεχωριστά.
Δημιουργήστε έναν Πίνακα Συγκεντρωτικού Πίνακα
Τώρα, για να εμφανίσετε περιοχές και στοιχεία σε διαφορετικές στήλες, ακολουθήστε αυτά τα βήματα:
1. Κάντε κλικ οπουδήποτε στον Συγκεντρωτικό πίνακα
2. Μεταβείτε στην καρτέλα Σχεδίαση
3. Κάντε κλικ στο Αναφορά διάταξης.
4. Κάντε κλικ στην Εμφάνιση για τη φόρμα πίνακα επιλογή. Τέλος, θα έχετε αυτήν την εκλεπτυσμένη άποψη της έκθεσής σας.
Τώρα γνωρίζουμε τον συνολικό αριθμό παραγγελιών που έχουν τοποθετηθεί για κάθε είδος από κάθε περιοχή. Εμφανίζεται στην Καταμέτρηση των Ειδών στήλες.
Αλλάξτε το όνομα της στήλης σε Παραγγελίες.
Τώρα φαίνεται καλύτερα.
Ε3: Πόσες μονάδες από κάθε αντικείμενο παραγγέλλονται;
Για να απαντήσουμε σε αυτήν την ερώτηση χρειαζόμαστε ένα σύνολο Μονάδων. Για να το κάνετε αυτό, απλώς μετακινήστε το πεδίο Μονάδες σε τιμές. Θα συνοψίσει αυτόματα τον αριθμό των μονάδων για κάθε στοιχείο. Εάν μια στήλη στον Συγκεντρωτικό πίνακα περιέχει μόνο τιμές, ο Συγκεντρωτικός πίνακας από προεπιλογή εμφανίζει το άθροισμα αυτών των τιμών. Αλλά μπορεί να αλλάξει από μια ρύθμιση πεδίου τιμών. Πως? Θα σας δείξω το τελευταίο.
Ρυθμίσεις πεδίου τιμής συγκεντρωτικού πίνακα
Ε4: Η μέση τιμή κάθε είδους;
Στα δείγματα δεδομένων μας, η τιμή ενός προϊόντος είναι διαφορετική για διαφορετικές παραγγελίες. Για παράδειγμα, δείτε Binders.
Θέλω να μάθω το μέσο κόστος κάθε στοιχείου στον Συγκεντρωτικό πίνακα. Για να το καταλάβετε, σύρετε το κόστος μονάδας στο πεδίο τιμής. Θα εμφανίσει άθροισμα κόστους μονάδας.
Δεν θέλουμε Άθροισμα κόστους μονάδας, θέλουμε Μέσο κόστος μονάδας. Να το πράξουν…
- Κάντε δεξί κλικ οπουδήποτε στο άθροισμα της στήλης Κόστος μονάδας στον Συγκεντρωτικό πίνακα
- Κάντε κλικ Ρυθμίσεις πεδίου τιμής
- Με βάση τις διαθέσιμες επιλογές, επιλέγω Μέση τιμή και πατήστε OK.
Τέλος, θα έχετε αυτήν την Συγκεντρωτική Αναφορά:
Συγκεντρωτικός πίνακας Υπολογισμένα πεδία
Μία από τις πιο χρήσιμες λειτουργίες του Συγκεντρωτικού Πίνακα είναι τα Υπολογισμένα Πεδία. Τα υπολογιζόμενα πεδία είναι πεδία που λαμβάνονται με ορισμένες λειτουργίες σε διαθέσιμες στήλες.
Ας καταλάβουμε πώς να εισαγάγετε Υπολογισμένα Πεδία στον Συγκεντρωτικό Πίνακα με ένα παράδειγμα:
Με βάση τα δεδομένα μας, ετοιμάσαμε αυτήν την έκθεση.
Εδώ, έχουμε Άθροισμα Μονάδων και Συνολικό κόστοςΤο Μόλις μετακίνησα τη συνολική στήλη στο πεδίο Τιμές και στη συνέχεια μετονόμασα σε Συνολικό κόστοςΤο Τώρα θέλω να μάθω τη μέση τιμή μιας μονάδας κάθε είδους για κάθε περιοχή. Και αυτό θα ήταν:
Μέση Τιμή = Συνολικό Κόστος / Συνολικές Μονάδες
Ας εισάγουμε ένα πεδίο στον περιστροφικό πίνακα που δείχνει τη μέση τιμή κάθε περιοχής:
Ακολουθήστε αυτά τα βήματα για να εισαγάγετε ένα υπολογιζόμενο πεδίο στον Συγκεντρωτικό πίνακα
1. Κάντε κλικ οπουδήποτε στον Συγκεντρωτικό πίνακα και μεταβείτε στο Αναλυτική καρτέλα
2. Κάντε κλικ στο Πεδία, αντικείμενα και σύνολα στην ομάδα υπολογισμών.
3. Κάντε κλικ στο Υπολογιζόμενα Πεδία.
Θα δείτε αυτό το πλαίσιο εισαγωγής για το πεδίο υπολογισμού:
4. Στο πλαίσιο εισαγωγής ονόματος γράψτε το μέσο όρο Κόστος ή οτιδήποτε σας αρέσει, το excel δεν θα πειράξει. Στο τύπος πλαίσιο εισαγωγής, γράψτε και πατήστε Εντάξει.
= Σύνολο / Μονάδες
Μπορείτε να το γράψετε χειροκίνητα από το πληκτρολόγιο ή μπορείτε να κάνετε διπλό κλικ στα ονόματα πεδίων που αναφέρονται στην περιοχή Πεδία για να εκτελέσετε λειτουργίες.