Πώς να ανανεώσετε αυτόματα τα δεδομένα περιστρεφόμενου πίνακα στο Excel

Πίνακας περιεχομένων:

Anonim

Σε αυτό το άρθρο, θα μάθουμε πώς να ανανεώνετε αυτόματα τα δεδομένα περιστρεφόμενου πίνακα στο Excel.
Σενάριο:

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

Ανανέωση δεδομένων κατά το άνοιγμα ενός αρχείου στο Excel

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

Μεταβείτε στις επιλογές Συγκεντρωτικού πίνακα> Καρτέλα Δεδομένα> Επιλέξτε το πλαίσιο που λέει Ανανέωση δεδομένων κατά το άνοιγμα ενός αρχείου

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

Παράδειγμα:

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

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

Επιλέξτε τις επιλογές Συγκεντρωτικού πίνακα και αυτό θα ανοίξει ένα παράθυρο διαλόγου Επιλογές πίνακα PIvot.


Επιλέξτε την καρτέλα δεδομένα και, στη συνέχεια, σημειώστε το πλαίσιο που αναφέρει Ανανέωση δεδομένων κατά το άνοιγμα του αρχείου. Μπορείτε να το εκτελέσετε χωρίς να ανοίξετε και να κλείσετε το αρχείο χρησιμοποιώντας VBA.

Χρήση VBA

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

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

Private Sub Worksheet_Deactivate ()

όνομα φύλλου_συγκεντρωτικού πίνακα.PivotTables ("pivot_table_name") .PivotCache.Ανανέωση

Τέλος υπο

Τι είναι τα Pivot Cache;

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

Πού να κωδικοποιήσετε για αυτόματη ανανέωση περιστροφικών πινάκων;

Εάν τα δεδομένα προέλευσης και οι συγκεντρωτικοί πίνακες βρίσκονται σε διαφορετικά φύλλα, τότε ο κώδικας VBA θα πρέπει να μπει στο φύλλο δεδομένων προέλευσης.
Εδώ θα χρησιμοποιήσουμε το Worksheet_SelectionChange Event. Αυτό θα κάνει τον κώδικα να τρέχει κάθε φορά που μεταβαίνουμε από το φύλλο δεδομένων προέλευσης σε άλλο φύλλο. Θα εξηγήσω αργότερα γιατί χρησιμοποίησα αυτό το συμβάν.

Εδώ, έχω δεδομένα προέλευσης στο φύλλο2 και περιστροφικούς πίνακες στο φύλλο 1.

Ανοίξτε το VBE χρησιμοποιώντας το πλήκτρο CTRL+F11. Στο πρόγραμμα εξερεύνησης έργου, μπορείτε να δείτε τρία αντικείμενα, το Sheet1, το Sheet2 και το Workbook.

Δεδομένου ότι το Sheet2 περιέχει τα δεδομένα προέλευσης, κάντε διπλό κλικ στο αντικείμενο φύλλου2.

Τώρα μπορείτε να δείτε δύο αναπτυσσόμενα μενού στο επάνω μέρος της περιοχής κώδικα. Από το πρώτο αναπτυσσόμενο μενού, επιλέξτε το φύλλο εργασίας. Και από το δεύτερο αναπτυσσόμενο μενού επιλέξτε Απενεργοποίηση. Αυτό θα εισαγάγει ένα κενό υπο -όνομα Worksheet_Deactivate. Ο κωδικός μας θα γραφτεί σε αυτό το τμήμα. Οποιεσδήποτε γραμμές είναι γραμμένες σε αυτό το δευτερεύον, εκτελούνται μόλις ο χρήστης αλλάξει από αυτό το φύλλο σε οποιοδήποτε άλλο φύλλο.

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


Τώρα γνωρίζουμε το όνομα του περιστροφικού πίνακα, μπορούμε να γράψουμε μια απλή γραμμή για να ανανεώσουμε τον περιστροφικό πίνακα.

Private Sub Worksheet_Deactivate ()

Sheet1.PivotTables ("Συγκεντρωτικός Πίνακας1"). PivotCache.Refresh

Τέλος υπο

Και γίνεται.

Τώρα κάθε φορά που θα αλλάζετε από τα δεδομένα προέλευσης, αυτός ο κώδικας vba θα εκτελείται για να ανανεώσετε τον συγκεντρωτικό πίνακα1. Όπως μπορείτε να δείτε στο gif παρακάτω.

Πώς να ανανεώσετε όλους τους συγκεντρωτικούς πίνακες στο βιβλίο εργασίας;

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

Private Sub Worksheet_Deactivate ()

'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh

Για κάθε υπολογιστή στο ThisWorkbook.PivotCaches

pc. Ανανέωση

Επόμενο τεμ

Τέλος υπο

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

Γιατί να χρησιμοποιήσετε το Worksheet_Deactivate Event;

Εάν θέλετε να ανανεώσετε τον συγκεντρωτικό πίνακα μόλις γίνει οποιαδήποτε αλλαγή στα δεδομένα προέλευσης, θα πρέπει να χρησιμοποιήσετε το συμβάν Worksheet_Change. Αλλά δεν το προτείνω. Θα κάνει το βιβλίο εργασίας σας να εκτελεί τον κώδικα κάθε φορά που κάνετε οποιαδήποτε αλλαγή στο φύλλο. Mayσως χρειαστεί να κάνετε εκατοντάδες αλλαγές πριν θέλετε να δείτε το αποτέλεσμα. Αλλά το excel θα ανανεώνει τον περιστρεφόμενο πίνακα σε κάθε αλλαγή. Αυτό θα οδηγήσει σε σπατάλη χρόνου και πόρων επεξεργασίας. Έτσι, εάν έχετε περιστρεφόμενους πίνακες και δεδομένα σε διαφορετικά φύλλα, είναι προτιμότερο να χρησιμοποιήσετε το φύλλο εργασίας Απενεργοποίηση συμβάντος. Σας επιτρέπει να φινλανδικοποιήσετε τη δουλειά σας. Μόλις μεταβείτε σε φύλλα περιστροφικού πίνακα για να δείτε τις αλλαγές, τροποποιεί τις αλλαγές.

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

Private Sub Worksheet_Change (ByVal Target As Range)

Sheet1.PivotTables ("Συγκεντρωτικός Πίνακας1"). PivotCache.Refresh

Τέλος υπο

Πώς να ανανεώσετε τα πάντα στα βιβλία εργασίας όταν πραγματοποιείται μια αλλαγή στα δεδομένα πηγής;

Εάν θέλετε να ανανεώσετε τα πάντα σε ένα βιβλίο εργασίας (γραφήματα, περιστρεφόμενοι πίνακες, τύποι κ.λπ.) μπορείτε να χρησιμοποιήσετε την εντολή ThisWorkbook.RefreshAll.

Private Sub Worksheet_Change (ByVal Target As Range)

ThisWorkbook.RefreshAll

Τέλος υπο

Σημείωση : Ο κώδικας δεν αλλάζει την προέλευση δεδομένων. Έτσι, εάν προσθέσετε δεδομένα κάτω από τα δεδομένα προέλευσης, αυτός ο κώδικας δεν θα περιλαμβάνει αυτά τα δεδομένα αυτόματα. Μπορείτε να χρησιμοποιήσετε τους Πίνακες Excel για να αποθηκεύσετε δεδομένα πηγής. Εάν δεν θέλετε να χρησιμοποιήσετε πίνακες, μπορούμε να χρησιμοποιήσουμε το VBA για να συμπεριλάβουμε και νέα δεδομένα. Θα το μάθουμε στο επόμενο σεμινάριο.

Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο αυτόματης ανανέωσης των δεδομένων περιστροφικού πίνακα στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τον υπολογισμό των τιμών και τους σχετικούς τύπους Excel εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email.

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

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

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

Απλούστερος κώδικας VBA για να επισημάνετε τη χρήση της τρέχουσας γραμμής και στήλης : Χρησιμοποιήστε αυτό το μικρό απόσπασμα VBA για να επισημάνετε την τρέχουσα γραμμή και στήλη του φύλλου.

Οι εκδηλώσεις φύλλου εργασίας στο Excel VBA : Το συμβάν φύλλου εργασίας είναι πραγματικά χρήσιμο όταν θέλετε οι μακροεντολές σας να εκτελούνται όταν εμφανίζεται ένα συγκεκριμένο συμβάν στο φύλλο.

Δημοφιλή άρθρα:

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

Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.

Πώς να χρησιμοποιήσετε τη συνάρτηση IF στο Excel : Η δήλωση IF στο Excel ελέγχει την κατάσταση και επιστρέφει μια συγκεκριμένη τιμή εάν η συνθήκη είναι TRUE ή επιστρέφει μια άλλη συγκεκριμένη τιμή εάν είναι FALSE.

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

Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.