Σε αυτό το άρθρο, θα μάθουμε πώς να ομαδοποιούμε υποσύνολο κατά ημερομηνία χρησιμοποιώντας τη συνάρτηση GETPIVOTDATA στο Excel.
Σενάριο:
Γενικά, δουλεύοντας με περιστροφικούς πίνακες, πρέπει να βρούμε τις συγκεκριμένες τιμές αθροίσματος. Αυτές οι τιμές αθροίσματος που έχουν κριτήρια για ορισμένες τιμές ημερομηνίας μπορούν να εξαχθούν στο Excel. Για παράδειγμα, εάν πρέπει να βρείτε το άθροισμα οποιουδήποτε πεδίου κατηγοριοποιημένο κατά μήνες ή έτη με τιμές ημερομηνίας.
Πώς να λύσετε το πρόβλημα;
Εδώ έχουμε ένα πρόβλημα, το άθροισμα των καταχωρήσεων με κριτήρια ως τιμή ημερομηνίας. Η χρήση συναρτήσεων και ενός εργαλείου θα μας βοηθήσει να ξεπεράσουμε το πρόβλημα.
- Λήψη Συγκεντρωτικού Πίνακα
- Συνάρτηση GETPIVOTDATA
Πρώτον, θα σχηματιστεί ένας περιστροφικός πίνακας και θα κατηγοριοποιηθεί με τιμές ημερομηνίας. Στη συνέχεια, ο παρακάτω τύπος θα χρησιμοποιηθεί για να βρεθεί το άθροισμα με ομαδοποιημένα κριτήρια στον περιστροφικό πίνακα.
Γενικός τύπος:
| = GETPIVOTDATA ("Άθροισμα πεδίου", table_ref, ["field_1", "crit_1"], ["field_2", "crit_1"],…) |
Σημείωση :- Χρησιμοποιήστε την κεφαλίδα πεδίου με σωστή λέξη -κλειδί
Άθροισμα πεδίου: πεδίο, όπου απαιτείται άθροισμα
table_ref: πρώτο πεδίο αναφοράς
field_1: κριτήρια πεδίου 1
crit_1: κριτήρια 1
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Έτσι, ας δοκιμάσουμε αυτόν τον τύπο εκτελώντας τον στο παράδειγμα που φαίνεται παρακάτω. Αρχικά, θα φτιάξουμε έναν περιστροφικό πίνακα για τα δεδομένα.
Μετατρέψτε τα δεδομένα δεδομένα σε έναν συγκεντρωτικό πίνακα χρησιμοποιώντας την επιλογή συγκεντρωτικού πίνακα. Μάθετε περισσότερα σχετικά με τη λήψη Συγκεντρωτικού πίνακα δεδομένων εδώ. Μετά την απόκτηση του συγκεντρωτικού πίνακα κατηγοριοποιήθηκαν τα δεδομένα με δείκτη μηνών.
Όπως μπορείτε να δείτε, ο συγκεντρωτικός πίνακας βρίσκεται εδώ στα αριστερά και οι ρυθμίσεις πεδίου στα δεξιά. Αλλά το πρόβλημα της απόκτησης του αθροίσματος των υποσυνολικών τιμών είναι ακόμα εδώ.
Πώς να χρησιμοποιήσετε τη συνάρτηση GETPIVOTDATA με τον Συγκεντρωτικό πίνακα.
Εδώ έχουμε έναν περιστροφικό πίνακα και πρέπει να βρούμε το άθροισμα της Ποσότητας τον Μάρτιο του μήνα. Εάν κάνουμε την ίδια εργασία με τιμές δεδομένων αντί για έναν περιστρεφόμενο πίνακα, θα είναι μια δύσκολη εργασία ή θα χρειαστούμε περισσότερες από μία λειτουργίες για να το λύσουμε. Τώρα για να βρείτε το άθροισμα της ποσότητας τον Μάρτιο του μήνα, χρησιμοποιήστε τον παρακάτω τύπο.
Χρησιμοποιήστε τον τύπο
| = GETPIVOTDATA ("Άθροισμα Ποσότητας", Α3, "Μήνες", "Μαρ") |
Εξήγηση:
- Όρισμα Άθροισμα Ποσότητας για να βρείτε το πεδίο Άθροισμα Ποσότητας.
- A3: κελί αναφοράς πεδίου πίνακα
- Επιλέχθηκε το πεδίο πίνακα "Μήνες" για να λάβετε τις τιμές του αθροίσματος ανά μήνα.
- "Μαρ" επιλέχθηκε ο μήνας Μαρτίου όπου απαιτείται άθροισμα.
- Η συνάρτηση GETPIVOTDATA εξάγει το άθροισμα της ποσότητας που έχει κατηγοριοποιηθεί ως δηλωμένο όρισμα όπως εξηγήθηκε παραπάνω.
Πατήστε Enter για να δείτε το αποτέλεσμα.
Όπως μπορείτε να δείτε, ο τύπος επιστρέφει 302 ως το άθροισμα της Ποσότητας τον Μάρτιο του μήνα. Εδώ μπορείτε να πείτε ότι αυτός ο τύπος είναι πολύ χρήσιμος κατά την εργασία με έναν περιστρεφόμενο πίνακα. Τώρα για να πάρουμε το άθροισμα της τιμής τον Ιανουάριο από τον περιστρεφόμενο πίνακα, θα χρησιμοποιούμε τον ίδιο τύπο.
Χρησιμοποιήστε τον τύπο:
| = GETPIVOTDATA ("Άθροισμα τιμής", Β3, "Μήνες", "Ιαν") |
Άθροισμα τιμής: Άθροισμα τιμών τιμών
Β3: αναφορά στον αντίστοιχο πίνακα
"Μήνες": κριτήρια πεδίου
"Jan": κριτήρια
Όπως μπορείτε να δείτε, ο τύπος επιστρέφει το άθροισμα της τιμής 1705,82 όπως σημειώνεται στον πίνακα με ένα πράσινο βέλος. Τώρα καταλαβαίνετε πώς να αποκτήσετε έναν συγκεντρωτικό πίνακα από δεδομένα και μετά να μάθετε το υποσύνολο ομαδοποιημένο κατά ημερομηνία ως κριτήριο. Τα κριτήρια μπορούν να δοθούν ως όρισμα στον τύπο με εισαγωγικά.
Εδώ είναι όλες οι σημειώσεις παρατήρησης σχετικά με τη χρήση του τύπου.
Σημειώσεις:
- Ο Συγκεντρωτικός πίνακας είναι ένα εργαλείο για τη δημιουργία περιστρεφόμενων πινάκων, αλλά η συνάρτηση GETPIVOTDATA εξάγει αξία από τον συγκεντρωτικό πίνακα.
- Ο συγκεντρωτικός πίνακας λειτουργεί μόνο με τιμή αριθμών.
- Μπορείτε να λάβετε τον αριθμό, το άθροισμα, τον μέσο όρο, τη διακύμανση ή την τυπική απόκλιση των δεδομένων χρησιμοποιώντας τον περιστροφικό πίνακα.
- Πολλαπλά κριτήρια μπορούν να δοθούν στον τύπο που διαχωρίζεται με κόμματα χρησιμοποιώντας εισαγωγικά ("").
- Παρακολουθήστε το όρισμα λέξης -κλειδιού τύπου, καθώς ο τύπος επιστρέφει #REF! Σφάλμα εάν δεν χρησιμοποιείται σωστά.
Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο ομαδοποίησης υποσυνολικών κατά ημερομηνία χρησιμοποιώντας τη συνάρτηση GETPIVOTDATA στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τις συναρτήσεις πίνακα περιστροφής και πίνακα εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email
Συγκεντρωτικός πίνακας : Τρίψτε τους αριθμούς δεδομένων σας εν κινήσει χρησιμοποιώντας το εργαλείο πίνακα PIVOT στο Excel.
Δυναμικός περιστροφικός πίνακας : Τρυπήστε τα πρόσφατα προστιθέμενα δεδομένα σας με παλιούς αριθμούς δεδομένων χρησιμοποιώντας το δυναμικό εργαλείο πίνακα PIVOT στο Excel.
Εμφάνιση απόκρυψης κεφαλίδας πεδίου στον συγκεντρωτικό πίνακα : Επεξεργασία (εμφάνιση / απόκρυψη) κεφαλίδας πεδίου του εργαλείου πίνακα PIVOT στο Excel.
Τρόπος ανανέωσης περιστροφικών γραφημάτων : Ανανεώστε τα γραφήματα PIVOT στο Excel για να λάβετε το ενημερωμένο αποτέλεσμα χωρίς κανένα πρόβλημα.
Δημοφιλή άρθρα:
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας : Προχωρήστε γρηγορότερα στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε το tσυνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.