Πώς να προσθέσετε και να χρησιμοποιήσετε ένα υπολογισμένο πεδίο περιστροφικού πίνακα Excel

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

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

Κατεβάστε το σύνολο δεδομένων και ακολουθήστε το.

Τι είναι το Υπολογιζόμενο πεδίο Συγκεντρωτικού πίνακα;

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

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

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

Τώρα, τι γίνεται αν θέλετε επίσης να μάθετε ποιο ήταν το περιθώριο κέρδους αυτών των λιανοπωλητών (όπου το περιθώριο κέρδους είναι «Κέρδος» διαιρούμενο με «Πωλήσεις»).

Υπάρχουν δύο τρόποι για να το κάνετε αυτό:

1. Επιστρέψτε στο αρχικό σύνολο δεδομένων και προσθέστε αυτό το νέο σημείο δεδομένων. Έτσι, μπορείτε να εισαγάγετε μια νέα στήλη στα δεδομένα προέλευσης και να υπολογίσετε το περιθώριο κέρδους σε αυτό. Μόλις το κάνετε αυτό, πρέπει να ενημερώσετε τα δεδομένα προέλευσης του Συγκεντρωτικού πίνακα για να λάβετε αυτήν τη νέα στήλη ως μέρος της.
   • Ενώ αυτή η μέθοδος είναι πιθανή, θα πρέπει να επιστρέψετε χειροκίνητα στο σύνολο δεδομένων και να κάνετε τους υπολογισμούς. Για παράδειγμα, μπορεί να χρειαστεί να προσθέσετε μια άλλη στήλη για να υπολογίσετε τη μέση πώληση ανά μονάδα (Πωλήσεις/Ποσότητα). Και πάλι θα πρέπει να προσθέσετε αυτήν τη στήλη στα δεδομένα προέλευσης και, στη συνέχεια, να ενημερώσετε τον συγκεντρωτικό πίνακα.
   • Αυτή η μέθοδος φουσκώνει επίσης τον Συγκεντρωτικό πίνακα καθώς προσθέτετε νέα δεδομένα σε αυτόν.
2. Προσθέστε υπολογισμούς εκτός του Συγκεντρωτικού Πίνακα. Αυτό μπορεί να είναι μια επιλογή εάν η δομή του Συγκεντρωτικού πίνακα είναι απίθανο να αλλάξει. Αλλά αν αλλάξετε τον Συγκεντρωτικό πίνακα, ο υπολογισμός ενδέχεται να μην ενημερωθεί ανάλογα και μπορεί να σας δώσει λάθος αποτελέσματα ή λάθη. Όπως φαίνεται παρακάτω, υπολόγισα το περιθώριο κέρδους όταν υπήρχαν λιανοπωλητές στη σειρά. Αλλά όταν το άλλαξα από πελάτες σε περιοχές, ο τύπος έδωσε ένα σφάλμα.
3. Χρήση Συγκεντρωτικού Πίνακα Υπολογισμένου Πεδίου. Αυτό είναι το πιο αποτελεσματικός τρόπος για να χρησιμοποιήσετε τα υπάρχοντα δεδομένα Συγκεντρωτικού πίνακα και να υπολογίσετε την επιθυμητή μέτρηση. Εξετάστε το Υπολογισμένο πεδίο ως εικονική στήλη που έχετε προσθέσει χρησιμοποιώντας τις υπάρχουσες στήλες από τον Συγκεντρωτικό πίνακα. Υπάρχουν πολλά οφέλη από τη χρήση ενός Υπολογισμένου Πεδίου Συγκεντρωτικού Πίνακα (όπως θα δούμε σε ένα λεπτό):
   • Δεν απαιτεί να χειρίζεστε τύπους ή να ενημερώνετε δεδομένα πηγής.
   • Είναι επεκτάσιμο καθώς θα λαμβάνει αυτόματα υπόψη τα νέα δεδομένα που μπορείτε να προσθέσετε στον Συγκεντρωτικό πίνακα. Μόλις προσθέσετε ένα πεδίο υπολογισμού, μπορείτε να το χρησιμοποιήσετε όπως οποιοδήποτε άλλο πεδίο στον Συγκεντρωτικό πίνακα.
   • Είναι εύκολο να ενημερώσετε και να διαχειριστείτε. Για παράδειγμα, εάν οι μετρήσεις αλλάξουν ή πρέπει να αλλάξετε τον υπολογισμό, μπορείτε εύκολα να το κάνετε από τον ίδιο τον Συγκεντρωτικό πίνακα.

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

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

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

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

• Επιλέξτε οποιοδήποτε κελί στον Συγκεντρωτικό πίνακα.
• Μεταβείτε στα Εργαλεία περιστροφικού πίνακα -> Ανάλυση -> Υπολογισμοί -> Πεδία, στοιχεία και σύνολα.
• Από το αναπτυσσόμενο μενού, επιλέξτε Υπολογισμένο πεδίο.
• Στο παράθυρο διαλόγου Εισαγωγή υπολογιζόμενου αρχείου:
  • Δώστε του ένα όνομα εισάγοντάς το στο πεδίο Όνομα.
  • Στο πεδίο Τύπος, δημιουργήστε τον τύπο που θέλετε για το υπολογιζόμενο πεδίο. Λάβετε υπόψη ότι μπορείτε να επιλέξετε από τα ονόματα πεδίων που αναφέρονται παρακάτω. Σε αυτήν την περίπτωση, ο τύπος είναι ‘= Κέρδος/ Πωλήσεις’. Μπορείτε είτε να εισαγάγετε με μη αυτόματο τρόπο τα ονόματα των πεδίων είτε να κάνετε διπλό κλικ στο όνομα του πεδίου που παρατίθεται στο πλαίσιο Πεδία.
• Κάντε κλικ στο Προσθήκη και κλείστε το παράθυρο διαλόγου.

Μόλις προσθέσετε το Υπολογισμένο πεδίο, θα εμφανιστεί ως ένα από τα πεδία στη λίστα Πεδίων Συγκεντρωτικού Πίνακα.

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

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

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

Στο παραπάνω παράδειγμα, χρησιμοποίησα έναν απλό τύπο (= Κέρδος/Πωλήσεις) για να εισαγάγω ένα υπολογιζόμενο πεδίο. Ωστόσο, μπορείτε επίσης να χρησιμοποιήσετε ορισμένους σύνθετους τύπους.

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

• ΔΕΝ ΜΠΟΡΕΙΤΕ να χρησιμοποιήσετε αναφορές ή ονόματα εύρους κατά τη δημιουργία ενός Υπολογισμένου Πεδίου Συγκεντρωτικού Πίνακα. Αυτό θα αποκλείσει πολλούς τύπους όπως VLOOKUP, INDEX, OFFSET και ούτω καθεξής. Ωστόσο, μπορείτε να χρησιμοποιήσετε τύπους που μπορούν να λειτουργήσουν χωρίς αναφορές (όπως SUM, IF, COUNT και ούτω καθεξής…).
• Μπορείτε να χρησιμοποιήσετε μια σταθερά στον τύπο. Για παράδειγμα, εάν θέλετε να μάθετε τις προβλεπόμενες πωλήσεις όπου προβλέπεται ότι θα αυξηθούν κατά 10%, μπορείτε να χρησιμοποιήσετε τον τύπο = Πωλήσεις*1.1 (όπου το 1.1 είναι σταθερό).
• Η σειρά προτεραιότητας ακολουθείται στον τύπο που κάνει το υπολογιζόμενο πεδίο. Ως βέλτιστη πρακτική, χρησιμοποιήστε παρένθεση για να βεβαιωθείτε ότι δεν χρειάζεται να θυμάστε τη σειρά προτεραιότητας.

Τώρα, ας δούμε ένα παράδειγμα χρήσης ενός σύνθετου τύπου για τη δημιουργία ενός Υπολογισμένου Πεδίου.

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

Για την προβλεπόμενη αξία, πρέπει να χρησιμοποιήσετε αύξηση πωλήσεων 5% για μεγάλους λιανοπωλητές (πωλήσεις άνω των 3 εκατομμυρίων) και αύξηση 10% πωλήσεων για μικρομεσαίους λιανοπωλητές (πωλήσεις κάτω των 3 εκατομμυρίων).

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

Εδώ είναι πώς να το κάνετε αυτό:

• Επιλέξτε οποιοδήποτε κελί στον Συγκεντρωτικό πίνακα.
• Μεταβείτε στα Εργαλεία περιστροφικού πίνακα -> Ανάλυση -> Υπολογισμοί -> Πεδία, στοιχεία και σύνολα.
• Από το αναπτυσσόμενο μενού, επιλέξτε Υπολογισμένο πεδίο.
• Στο παράθυρο διαλόγου Εισαγωγή υπολογιζόμενου αρχείου:
  • Δώστε του ένα όνομα εισάγοντάς το στο πεδίο Όνομα.
  • Στο πεδίο Formula, χρησιμοποιήστε τον ακόλουθο τύπο: = IF (Region = ”South”, Sales *1.05, Sales *1.1)
• Κάντε κλικ στο Προσθήκη και κλείστε το παράθυρο διαλόγου.

Αυτό προσθέτει μια νέα στήλη στον περιστροφικό πίνακα με την τιμή πρόβλεψης πωλήσεων.

Κάντε κλικ εδώ για λήψη του συνόλου δεδομένων.

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

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

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

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

Σημειώστε ότι το υποσύνολο και το σύνολο δεν είναι σωστά.

Ενώ αυτά θα προσθέσουν την ατομική αξία προβλέψεων πωλήσεων για κάθε λιανοπωλητή, στην πραγματικότητα, ακολουθεί τον ίδιο υπολογισμένο τύπο πεδίου που δημιουργήσαμε.

Έτσι για το South Total, ενώ η τιμή θα πρέπει να είναι 22.824.000, το South Total λανθασμένα το αναφέρει ως 22.287.000. Αυτό συμβαίνει καθώς χρησιμοποιεί τον τύπο 21.225.800*1.05 για να λάβει την τιμή.

Δυστυχώς, δεν υπάρχει τρόπος να το διορθώσετε αυτό.

Ο καλύτερος τρόπος για να αντιμετωπίσετε αυτό θα ήταν να αφαιρέσετε υποσύνολα και Grand Totals από τον Συγκεντρωτικό σας Πίνακα.

Μπορείτε επίσης να περάσετε από μερικές καινοτόμες λύσεις που έχει δείξει η Debra για να χειριστεί αυτό το ζήτημα.

Πώς να τροποποιήσετε ή να διαγράψετε ένα υπολογιζόμενο πεδίο Συγκεντρωτικού πίνακα;

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

• Επιλέξτε οποιοδήποτε κελί στον Συγκεντρωτικό πίνακα.
• Μεταβείτε στα Εργαλεία περιστροφικού πίνακα -> Ανάλυση -> Υπολογισμοί -> Πεδία, στοιχεία και σύνολα.
• Από το αναπτυσσόμενο μενού επιλέξτε Υπολογισμένο πεδίο.
• Στο πεδίο Όνομα, κάντε κλικ στο αναπτυσσόμενο βέλος (μικρό βέλος προς τα κάτω στο τέλος του πεδίου).
• Από τη λίστα, επιλέξτε το υπολογιζόμενο πεδίο που θέλετε να διαγράψετε ή να τροποποιήσετε.
• Αλλάξτε τον τύπο σε περίπτωση που θέλετε να τον τροποποιήσετε ή κάντε κλικ στο Διαγραφή σε περίπτωση που θέλετε να τον διαγράψετε.

Πώς να αποκτήσετε μια λίστα με όλους τους τύπους υπολογισμένων πεδίων;

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

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

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

• Επιλέξτε οποιοδήποτε κελί στον Συγκεντρωτικό πίνακα.
• Μεταβείτε στα Εργαλεία περιστροφικού πίνακα -> Ανάλυση -> Πεδία, στοιχεία και σύνολα -> Τύποι λίστας.

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

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

Μπορείτε επίσης να βρείτε χρήσιμα τα ακόλουθα μαθήματα Pivot Table:

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