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

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

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

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

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

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

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

Τώρα, τι γίνεται αν θέλετε επίσης να μάθετε ποια είναι τα περιθώρια κέρδους αυτών των λιανοπωλητών (το περιθώριο κέρδους διαιρείται με το "κέρδος" με τις "πωλήσεις");

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

Δείτε πώς να το κάνετε:

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

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

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

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

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

Σημειώστε ότι τα υποσύνολα και τα σύνολα είναι λανθασμένα.

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

Έτσι, για το South Total, ενώ η τιμή θα έπρεπε να είναι 22,824,000, το South Total αναφέρει λανθασμένα ως 22,287,000.Αυτό συμβαίνει επειδή χρησιμοποιεί τον τύπο 21,225,800*1.05 για να πάρει την τιμή.

Δυστυχώς, δεν μπορείτε να διορθώσετε αυτό το πρόβλημα.

Ο καλύτερος τρόπος για να το χειριστείτε αυτό είναι να αφαιρέσετε τα υποσύνολα και τα γενικά σύνολα από τον συγκεντρωτικό πίνακα.

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

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

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

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

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

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

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

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

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

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