Προετοιμασία δεδομένων προέλευσης για συγκεντρωτικό πίνακα

Η κατοχή των δεδομένων στη σωστή μορφή είναι ένα κρίσιμο βήμα για τη δημιουργία ενός ισχυρού και χωρίς σφάλματα Συγκεντρωτικού Πίνακα. Εάν δεν γίνει με τον σωστό τρόπο, μπορεί να καταλήξετε να αντιμετωπίζετε πολλά προβλήματα με τον περιστρεφόμενο πίνακα.

Τι είναι ένας καλός σχεδιασμός για τα δεδομένα πηγής για τον Συγκεντρωτικό πίνακα;

Ας ρίξουμε μια ματιά σε ένα παράδειγμα καλών δεδομένων προέλευσης για έναν Συγκεντρωτικό Πίνακα.

Εδώ είναι αυτό που το καθιστά καλό σχεδιασμό δεδομένων πηγής:

• Η πρώτη γραμμή περιέχει κεφαλίδες που περιγράφουν τα δεδομένα στις στήλες.
• Κάθε στήλη αντιπροσωπεύει μια μοναδική κατηγορία δεδομένων. Για παράδειγμα, η στήλη C περιέχει μόνο δεδομένα προϊόντος και στήλη D και δεδομένα μήνα μόνο.
• Κάθε σειρά είναι μια εγγραφή που θα αντιπροσωπεύει μία παρουσία της συναλλαγής ή της πώλησης.
• Οι κεφαλίδες δεδομένων είναι μοναδικές και δεν επαναλαμβάνονται πουθενά στο σύνολο δεδομένων. Για παράδειγμα, εάν έχετε αριθμούς πωλήσεων για τέσσερα τρίμηνα σε ένα έτος, ΔΕΝ πρέπει να τα ονομάσετε όλα ως πωλήσεις. Αντ 'αυτού, δώστε σε αυτές τις κεφαλίδες στηλών μοναδικά ονόματα, όπως πωλήσεις Q1, πωλήσεις Q2 και ούτω καθεξής…
  • Εάν δεν έχετε μοναδικούς τίτλους, μπορείτε ακόμα να προχωρήσετε και να δημιουργήσετε έναν Συγκεντρωτικό Πίνακα και το Excel θα τα καθιστούσε αυτόματα μοναδικά προσθέτοντας ένα επίθημα (όπως Πωλήσεις, Πωλήσεις2, Πωλήσεις3). Ωστόσο, αυτός θα ήταν ένας απαίσιος τρόπος προετοιμασίας και χρήσης ενός Συγκεντρωτικού Πίνακα.

Κοινές παγίδες που πρέπει να αποφεύγετε κατά την προετοιμασία των δεδομένων προέλευσης

• Δεν πρέπει να υπάρχουν κενές στήλες στα δεδομένα προέλευσης. Αυτό είναι εύκολο να εντοπιστεί. Εάν έχετε μια κενή στήλη στα δεδομένα προέλευσης, δεν θα μπορείτε να δημιουργήσετε έναν Συγκεντρωτικό πίνακα. Θα εμφανίσει ένα σφάλμα όπως φαίνεται παρακάτω.
• Δεν πρέπει να υπάρχουν κενά κελιά/σειρές στα δεδομένα προέλευσης. Ενώ μπορείτε να δημιουργήσετε επιτυχώς έναν Συγκεντρωτικό πίνακα παρά τα κενά κελιά ή σειρές, υπάρχουν πολλές παρενέργειες που μπορεί να σας δαγκώσουν αργότερα μέσα στην ημέρα.
  • Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα κενό κελί στη στήλη πωλήσεων. Εάν δημιουργήσετε έναν Συγκεντρωτικό πίνακα χρησιμοποιώντας αυτά τα δεδομένα και τοποθετήσετε το πεδίο πωλήσεων στην περιοχή των στηλών, θα σας εμφανίσει το COUNT και όχι το SUM. Αυτό συμβαίνει επειδή το Excel ερμηνεύει ολόκληρη τη στήλη ως δεδομένα κειμένου (μόνο λόγω ενός κενού κελιού).
• Εφαρμόστε σχετική μορφή σε κελιά στα δεδομένα προέλευσης. Για παράδειγμα, εάν έχετε ημερομηνίες (οι οποίες αποθηκεύονται ως σειριακοί αριθμοί στο backend στο Excel), εφαρμόστε μία από τις αποδεκτές μορφές ημερομηνίας. Αυτό θα σας βοηθήσει να δημιουργήσετε τον Συγκεντρωτικό πίνακα και να χρησιμοποιήσετε την Ημερομηνία ως ένα από τα κριτήρια για σύνοψη, ομαδοποίηση και ταξινόμηση των δεδομένων.
  • Εάν έχετε μερικά δευτερόλεπτα, δοκιμάστε αυτό. Διαμορφώστε τις ημερομηνίες στον Συγκεντρωτικό πίνακα ως αριθμούς και, στη συνέχεια, δημιουργήστε έναν Συγκεντρωτικό πίνακα χρησιμοποιώντας αυτά τα δεδομένα. Τώρα στον Συγκεντρωτικό πίνακα, επιλέξτε το πεδίο ημερομηνίας και δείτε τι συμβαίνει. Θα το τοποθετήσει αυτόματα στην περιοχή τιμών. Αυτό συμβαίνει επειδή ο Συγκεντρωτικός σας πίνακας δεν γνωρίζει ότι αυτές είναι ημερομηνίες. Τα ερμηνεύει ως αριθμούς.
• Μην συμπεριλαμβάνετε σύνολα στηλών, σύνολα γραμμών, μέσους όρους κ.λπ., ως μέρος των δεδομένων προέλευσης. Μόλις έχετε τον Συγκεντρωτικό πίνακα, μπορείτε εύκολα να τα αποκτήσετε αργότερα.
• Δημιουργείτε πάντα έναν πίνακα Excel και, στη συνέχεια, χρησιμοποιήστε τον ως πηγή για έναν Συγκεντρωτικό πίνακα. Αυτή είναι περισσότερο μια καλή πρακτική και όχι μια παγίδα. Ο Συγκεντρωτικός Πίνακας θα λειτουργούσε μια χαρά με δεδομένα προέλευσης που δεν είναι επίσης Πίνακας Excel. Το πλεονέκτημα του Excel Table είναι ότι μπορεί να προσαρμόσει τα επεκτεινόμενα δεδομένα. Εάν προσθέσετε περισσότερες σειρές στο σύνολο δεδομένων, δεν χρειάζεται να προσαρμόσετε τα δεδομένα προέλευσης ξανά και ξανά. Μπορείτε απλώς να ανανεώσετε τον Συγκεντρωτικό πίνακα και αυτόματα θα λαμβάνει υπόψη τις νέες γραμμές που προστίθενται στα δεδομένα προέλευσης.

Παραδείγματα σχεδίων δεδομένων κακής πηγής

Ας ρίξουμε μια ματιά σε μερικά κακά παραδείγματα σχεδίων δεδομένων πηγής.

Σχεδιασμός δεδομένων κακής πηγής - Παράδειγμα 1

Αυτός είναι ένας κοινός τρόπος για τη διατήρηση δεδομένων καθώς είναι εύκολο να ακολουθηθούν και να κατανοηθούν. Υπάρχουν δύο προβλήματα με αυτήν τη διάταξη δεδομένων:

• Δεν έχετε την πλήρη εικόνα. Για παράδειγμα, μπορείτε να δείτε ότι οι πωλήσεις για το Mid West στο Quarter 1 είναι 2924300. Είναι όμως μια μεμονωμένη πώληση ή μια σειρά πωλήσεων. Εάν έχετε κάθε εγγραφή διαθέσιμη σε ξεχωριστή σειρά, μπορείτε να κάνετε μια καλύτερη ανάλυση.
• Εάν προχωρήσετε και δημιουργήσετε έναν Συγκεντρωτικό πίνακα χρησιμοποιώντας αυτό (το οποίο μπορείτε), θα λάβετε διαφορετικά πεδία για διαφορετικά τρίμηνα. Κάτι όπως φαίνεται παρακάτω:

Σχεδιασμός δεδομένων κακής πηγής - Παράδειγμα 2

Αυτή η αναπαράσταση δεδομένων μπορεί να δεχθεί καλά από τη διοίκηση και το κοινό των παρουσιάσεων του PowerPoint, αλλά δεν είναι κατάλληλη για τη δημιουργία ενός Συγκεντρωτικού Πίνακα.

Και πάλι, αυτό είναι το είδος της περίληψης που μπορείτε εύκολα να δημιουργήσετε χρησιμοποιώντας έναν Συγκεντρωτικό πίνακα. Έτσι, ακόμη και αν τελικά θέλετε μια τέτοια αναζήτηση για τα δεδομένα σας, διατηρήστε τα δεδομένα προέλευσης σε μορφή Pivot έτοιμη και δημιουργήστε αυτήν την προβολή χρησιμοποιώντας τον Συγκεντρωτικό πίνακα.

Σχεδιασμός δεδομένων κακής πηγής - Παράδειγμα 3

Αυτή είναι και πάλι μια έξοδος που μπορεί εύκολα να ληφθεί χρησιμοποιώντας έναν Συγκεντρωτικό Πίνακα. Αλλά δεν μπορεί να χρησιμοποιηθεί για τη δημιουργία ενός Συγκεντρωτικού Πίνακα.

Υπάρχουν κενά κελιά στο σύνολο δεδομένων και τα τέταρτα κατανέμονται ως κεφαλίδες στήλης.

Επίσης, η περιοχή καθορίζεται στην κορυφή, ενώ θα πρέπει να αποτελεί μέρος κάθε εγγραφής.

[ΜΕΛΕΤΗ ΠΕΡΙΠΤΩΣΗΣ] Μετατροπή κακώς μορφοποιημένων δεδομένων σε δεδομένα έτοιμων πηγών περιστροφικού πίνακα

Μερικές φορές, ενδέχεται να λάβετε ένα σύνολο δεδομένων που είναι ακατάλληλο για χρήση ως δεδομένα προέλευσης για τον Συγκεντρωτικό πίνακα. Σε μια τέτοια περίπτωση, μπορεί να μην έχετε άλλη επιλογή παρά να μετατρέψετε τα δεδομένα σε μια μορφή δεδομένων φιλική προς το Pivot.

Ακολουθεί ένα παράδειγμα κακού σχεδιασμού δεδομένων:

Τώρα μπορείτε να χρησιμοποιήσετε Λειτουργίες Excel ή Συγκεντρωτικό ερώτημα για να μετατρέψετε αυτά τα δεδομένα σε μορφή που μπορεί να χρησιμοποιηθεί ως δεδομένα προέλευσης για Συγκεντρωτικό πίνακα.

Ας δούμε πώς λειτουργούν και οι δύο αυτές μέθοδοι.

Μέθοδος 1: Χρήση τύπων Excel

Ας δούμε πώς να χρησιμοποιούμε τις Λειτουργίες Excel για τη μετατροπή αυτών των δεδομένων σε μορφή έτοιμου Συγκεντρωτικού Πίνακα.

• Δημιουργήστε μια μοναδική κεφαλίδα στήλης για όλες τις κατηγορίες στο αρχικό σύνολο δεδομένων. Σε αυτό το παράδειγμα, θα ήταν Περιφέρεια, Περιοχή και Πωλήσεις.
• Στο κελί κάτω από την κεφαλίδα περιοχής, χρησιμοποιήστε τον ακόλουθο τύπο: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
  • Σύρετε τον τύπο προς τα κάτω και θα επαναλάβει όλες τις περιοχές.
• Στο κελί κάτω από την κεφαλίδα Quarter, χρησιμοποιήστε τον ακόλουθο τύπο: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
  • Σύρετε τον τύπο προς τα κάτω και θα επαναλάβει όλα τα τέταρτα.
• Στην κεφαλίδα κάτω από τις Πωλήσεις, χρησιμοποιήστε τον ακόλουθο τύπο: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
  • Σύρετε το για να λάβετε όλες τις τιμές. Αυτός ο τύπος χρησιμοποιεί τα δεδομένα περιοχής και Quarter ως τιμές αναζήτησης και επιστρέφει την τιμή πωλήσεων από το αρχικό σύνολο δεδομένων.

Τώρα μπορείτε να χρησιμοποιήσετε αυτά τα δεδομένα που προκύπτουν ως δεδομένα προέλευσης για τον Συγκεντρωτικό πίνακα.

Κάντε κλικ εδώ για να κατεβάσετε το Παράδειγμα αρχείου.

Μέθοδος 2: Χρήση του Power Query

Το Power Query διαθέτει μια δυνατότητα που μπορεί εύκολα να μετατρέψει αυτού του είδους τα δεδομένα σε μορφή δεδομένων έτοιμων περιστροφών.

Εάν χρησιμοποιείτε το Excel 2016, οι δυνατότητες Power Query θα είναι διαθέσιμες στην καρτέλα Δεδομένα στην ομάδα Λήψη & μετασχηματισμός. Εάν χρησιμοποιείτε το Excel 2013 ή προηγούμενες εκδόσεις, μπορείτε να το χρησιμοποιήσετε ως πρόσθετο.

Ακολουθεί ένας εξαιρετικός οδηγός σχετικά με την εγκατάσταση του Power Query από τον Jon από το Excel Campus.

Και πάλι, λαμβάνοντας υπόψη ότι έχετε τα δεδομένα μορφοποιημένα όπως φαίνεται παρακάτω:

Ακολουθούν τα βήματα για τη μετατροπή των δεδομένων προέλευσης σε μορφή έτοιμου Συγκεντρωτικού πίνακα:

• Μετατρέψτε τα δεδομένα σε έναν πίνακα Excel. Επιλέξτε το σύνολο δεδομένων και μεταβείτε στην επιλογή Εισαγωγή -> Πίνακες -> Πίνακας.
• Στο παράθυρο διαλόγου Εισαγωγή πίνακα, βεβαιωθείτε ότι έχει επιλεγεί το σωστό εύρος και κάντε κλικ στο OK. Αυτό θα μετατρέψει τα δεδομένα πίνακα σε πίνακα Excel.
• Στο Excel 2016, μεταβείτε στο στοιχείο Δεδομένα -> Λήψη & Μετασχηματισμός -> Από τον πίνακα.
  • Εάν χρησιμοποιείτε το Πρόσθετο ερωτήματος ισχύος σε προηγούμενη έκδοση, μεταβείτε στο Power Query -> Εξωτερικά δεδομένα -> Από τον πίνακα.
• Στο πρόγραμμα επεξεργασίας ερωτήματος, επιλέξτε τις στήλες που θέλετε να καταργήσετε την περιστροφή. Σε αυτήν την περίπτωση, αυτά είναι αυτά για τα τέσσερα τέταρτα. Για να επιλέξετε όλες τις στήλες, κρατήστε πατημένο το πλήκτρο Shift και, στη συνέχεια, επιλέξτε την πρώτη στήλη και στη συνέχεια την τελευταία στήλη.
• Μέσα στο πρόγραμμα επεξεργασίας ερωτήματος, μεταβείτε στη σελίδα Μετασχηματισμός -> Οποιαδήποτε στήλη -> Στήλες χωρίς περιστροφή. Αυτό θα μετατρέψει τα δεδομένα της στήλης σε μορφή φιλική προς τον Συγκεντρωτικό πίνακα.
• Το Power Query δίνει γενικά ονόματα στις στήλες. Αλλάξτε αυτά τα ονόματα σε αυτά που θέλετε. Σε αυτήν την περίπτωση, αλλάξτε το Attribute to Quarter και το Value to Sales.
• Στο πρόγραμμα επεξεργασίας ερωτήματος, μεταβείτε στο Αρχείο -> Κλείσιμο και φόρτωση. Αυτό θα κλείσει το παράθυρο διαλόγου Power Query Editor και θα δημιουργήσει ένα ξεχωριστό φύλλο εργασίας που θα περιέχει τα δεδομένα με μη περιστρεφόμενες στήλες.

Τώρα που ξέρετε πώς να προετοιμάσετε τα δεδομένα προέλευσης για τον Συγκεντρωτικό πίνακα, είστε έτοιμοι να κάνετε Excel στον κόσμο των Συγκεντρωτικών Πινάκων.

Ακολουθούν μερικά άλλα μαθήματα περιστροφικού πίνακα που μπορεί να σας φανούν χρήσιμα:

• Πώς να ανανεώσετε τον συγκεντρωτικό πίνακα στο Excel.
• Χρήση τεμαχιστών στον περιστροφικό πίνακα του Excel - Οδηγός για αρχάριους.
• Πώς να ομαδοποιήσετε ημερομηνίες σε συγκεντρωτικούς πίνακες στο Excel.
• Πώς να ομαδοποιήσετε αριθμούς στον περιστροφικό πίνακα στο Excel.
• Pivot Cache στο Excel - Τι είναι και πώς να το χρησιμοποιήσετε καλύτερα.
• Πώς να φιλτράρετε δεδομένα σε έναν Συγκεντρωτικό πίνακα στο Excel.
• Πώς να προσθέσετε και να χρησιμοποιήσετε ένα υπολογισμένο πεδίο Συγκεντρωτικού πίνακα Excel.
• Πώς να εφαρμόσετε μορφοποίηση υπό όρους σε έναν Συγκεντρωτικό πίνακα στο Excel.
• Πώς να αντικαταστήσετε τα κενά κελιά με μηδενικά στους περιστρεφόμενους πίνακες του Excel.