Όπως όλοι γνωρίζουμε, κάθε φορά που κάνουμε αλλαγές σε δεδομένα προέλευσης ενός περιστρεφόμενου πίνακα, δεν αντικατοπτρίζονται αμέσως στον περιστροφικό πίνακα. Πρέπει να ανανεώσουμε τους συγκεντρωτικούς πίνακες για να δούμε τις αλλαγές. Και αν στείλετε ένα ενημερωμένο αρχείο χωρίς να ανανεώσετε τους περιστρεφόμενους πίνακες, μπορεί να αισθανθείτε αμηχανία.
Έτσι σε αυτό το άρθρο, θα μάθουμε πώς να ανανεώνουμε αυτόματα έναν περιστρεφόμενο πίνακα χρησιμοποιώντας το VBA. Αυτός ο τρόπος είναι πιο εύκολος από ό, τι φανταζόσασταν.
Αυτή είναι η απλή σύνταξη για την αυτόματη ανανέωση των περιστρεφόμενων πινάκων στο βιβλίο εργασίας.
'Κωδικός σε φύλλο δεδομένων φύλλου αντικειμένου Ιδιωτικό φύλλο εργασίας_Αποενεργοποίηση () sheetname_of_pivot_table.PivotTables ("pivot_table_name"). PivotCache.Refresh End Sub
Τι είναι τα 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 ("PivotTable1"). PivotCache.Refresh End Sub
Και γίνεται.
Τώρα κάθε φορά που θα αλλάζετε από τα δεδομένα προέλευσης, αυτός ο κώδικας vba θα εκτελείται για να ανανεώσετε τον συγκεντρωτικό πίνακα1. Όπως μπορείτε να δείτε στο gif παρακάτω.
Πώς να ανανεώσετε όλους τους συγκεντρωτικούς πίνακες στο βιβλίο εργασίας;
Στο παραπάνω παράδειγμα, θέλαμε να ανανεώσουμε μόνο έναν συγκεκριμένο περιστροφικό πίνακα. Αλλά αν θέλετε να ανανεώσετε όλους τους περιστροφικούς πίνακες σε ένα βιβλίο εργασίας, πρέπει απλώς να κάνετε μικρές αλλαγές στον κώδικά σας.
Private Sub Worksheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Ανανέωση για κάθε υπολογιστή στο 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 ("PivotTable1"). PivotCache.Refresh End Sub
Πώς να ανανεώσετε τα πάντα στα βιβλία εργασίας όταν πραγματοποιείται μια αλλαγή στα δεδομένα πηγής;
Εάν θέλετε να ανανεώσετε τα πάντα σε ένα βιβλίο εργασίας (γραφήματα, περιστρεφόμενοι πίνακες, τύποι κ.λπ.) μπορείτε να χρησιμοποιήσετε την εντολή ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change (ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Λάβετε υπόψη ότι αυτός ο κωδικός δεν αλλάζει την προέλευση δεδομένων. Έτσι, εάν προσθέσετε δεδομένα κάτω από τα δεδομένα προέλευσης, αυτός ο κώδικας δεν θα περιλαμβάνει αυτά τα δεδομένα αυτόματα. Μπορείτε να χρησιμοποιήσετε τους Πίνακες Excel για να αποθηκεύσετε δεδομένα πηγής. Εάν δεν θέλετε να χρησιμοποιήσετε πίνακες, μπορούμε να χρησιμοποιήσουμε το VBA για να συμπεριλάβουμε και νέα δεδομένα. Θα το μάθουμε στο επόμενο σεμινάριο.
Έτσι, ναι φίλε, με αυτόν τον τρόπο μπορείτε να ανανεώσετε αυτόματα τους περιστροφικούς πίνακες στο Excel. Ελπίζω να ήμουν αρκετά επεξηγηματικός και αυτό το άρθρο σας βοήθησε καλά. Εάν έχετε οποιεσδήποτε ερωτήσεις σχετικά με αυτό το θέμα, μπορείτε να με ρωτήσετε στην παρακάτω ενότητα σχολίων.
Πώς να ενημερώσετε δυναμικά το εύρος πηγών δεδομένων περιστροφικού πίνακα στο Excel: Για να αλλάξουμε δυναμικά το εύρος δεδομένων προέλευσης των περιστρεφόμενων πινάκων, χρησιμοποιούμε κεντρικές μνήμες περιστροφής. Αυτές οι λίγες γραμμές μπορούν να ενημερώσουν δυναμικά οποιονδήποτε περιστροφικό πίνακα αλλάζοντας το εύρος δεδομένων προέλευσης. Στο VBA χρησιμοποιήστε αντικείμενα περιστρεφόμενων πινάκων όπως φαίνεται παρακάτω…
Εκτελέστε μακροεντολή εάν πραγματοποιηθεί οποιαδήποτε αλλαγή στο φύλλο σε καθορισμένο εύρος: Στις πρακτικές σας VBA, θα χρειαστεί να εκτελέσετε μακροεντολές όταν αλλάζει ένα συγκεκριμένο εύρος ή κελί. Σε αυτήν την περίπτωση, για την εκτέλεση μακροεντολών όταν πραγματοποιείται αλλαγή σε εύρος στόχου, χρησιμοποιούμε το συμβάν αλλαγής.
Εκτέλεση μακροεντολής όταν πραγματοποιείται οποιαδήποτε αλλαγή στο φύλλο | Έτσι, για να εκτελέσετε τη μακροεντολή σας κάθε φορά που ενημερώνεται το φύλλο, χρησιμοποιούμε τα Συμβάντα φύλλου εργασίας του VBA.
Απλούστερος κώδικας VBA για να επισημάνετε τη χρήση της τρέχουσας γραμμής και στήλης | Χρησιμοποιήστε αυτό το μικρό απόσπασμα VBA για να επισημάνετε την τρέχουσα γραμμή και στήλη του φύλλου.
Οι εκδηλώσεις φύλλου εργασίας στο Excel VBA | Το συμβάν φύλλου εργασίας είναι πραγματικά χρήσιμο όταν θέλετε οι μακροεντολές σας να εκτελούνται όταν εμφανίζεται ένα συγκεκριμένο συμβάν στο φύλλο.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Η συνάρτηση VLOOKUP στο Excel | Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
COUNTIF στο Excel 2016 | Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένη τιμή. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.