Συγκεντρωτικός πίνακας ανανέωσης του Excel VBA
Όταν εισάγουμε έναν συγκεντρωτικό πίνακα στο φύλλο, όταν τα δεδομένα αλλάξουν τα δεδομένα του συγκεντρωτικού πίνακα δεν αλλάζουν, πρέπει να το κάνουμε χειροκίνητα, αλλά στο VBA υπάρχει μια δήλωση για την ανανέωση του συγκεντρωτικού πίνακα που είναι η έκφραση . συγκεντρωτικός πίνακας αναφερόμενος στο φύλλο εργασίας που αποτελείται ή μπορούμε να αναφερθούμε σε ολόκληρους τους συγκεντρωτικούς πίνακες στα φύλλα εργασίας και να τους ανανεώσουμε ταυτόχρονα.
Ο συγκεντρωτικός πίνακας είναι ζωτικής σημασίας για την ανάλυση του τεράστιου όγκου δεδομένων. Βοηθά σωστά από την ανάλυση, τη σύνοψη, καθώς και την πραγματοποίηση χρήσιμης ερμηνείας δεδομένων. Ωστόσο, ένα από τα προβλήματα με αυτόν τον συγκεντρωτικό πίνακα είναι ότι δεν θα ανανεωθεί αυτόματα εάν υπάρχει αλλαγή στα δεδομένα προέλευσης. Ο χρήστης πρέπει να ανανεώσει τον συγκεντρωτικό πίνακα μεταβαίνοντας στον συγκεκριμένο συγκεντρωτικό πίνακα κάθε φορά που υπάρχει αλλαγή. Αλλά πείτε αντίο στη μη αυτόματη διαδικασία, επειδή εδώ έχουμε τη μέθοδο για να ανανεώσετε τον συγκεντρωτικό πίνακα μόλις κάνετε οποιαδήποτε αλλαγή στον συγκεντρωτικό πίνακα.
Πώς να ανανεώσετε αυτόματα τα δεδομένα πίνακα περιστρεφόμενου πίνακα VBA;
Η μόνη φορά που πρέπει να ενημερωθεί ο συγκεντρωτικός πίνακας είναι όποτε υπάρχει αλλαγή στα δεδομένα προέλευσης του συγκεντρωτικού πίνακα στον οποίο αναφερόμαστε.
Για παράδειγμα, δείτε τα παρακάτω δεδομένα και τον συγκεντρωτικό πίνακα.
Τώρα θα αλλάξω τους αριθμούς στα πηγαία δεδομένα, δηλαδή από A1 σε B17.
Στο κελί B9, πρέπει να αλλάξω την τιμή από 499 σε 1499, δηλαδή, 1000 αύξηση στα δεδομένα, αλλά αν κοιτάξετε τον περιστρεφόμενο δείκτη εξακολουθεί να δείχνει το αποτέλεσμα ως 4295 αντί για 5295. Πρέπει να ανανεώσω μη αυτόματα τον πίνακα περιστροφής μου για να ενημερώσω το συγκεντρωτικός πίνακας.
Για να ξεπεράσουμε αυτό το ζήτημα, πρέπει να γράψουμε έναν απλό κώδικα μακροεντολής excel για να ανανεώσουμε τον συγκεντρωτικό πίνακα όποτε υπάρχει οποιαδήποτε αλλαγή στα δεδομένα προέλευσης.
# 1 - Απλή μακροεντολή για ανανέωση όλου του πίνακα
Βήμα 1: Αλλαγή συμβάντος του φύλλου δεδομένων
Πρέπει να ενεργοποιήσουμε το συμβάν αλλαγής του φύλλου δεδομένων. Στο οπτικό βασικό πρόγραμμα επεξεργασίας, κάντε διπλό κλικ στο φύλλο δεδομένων.
Μόλις κάνετε διπλό κλικ στο φύλλο, επιλέξτε "Φύλλο εργασίας" και επιλέξτε το συμβάν ως "Αλλαγή".
Θα δείτε μια αυτόματη δευτερεύουσα διαδικασία ανοιχτή ως Worksheet_Change (ByVal Target As Range)
Βήμα 2: Χρησιμοποιήστε το αντικείμενο φύλλου εργασίας
Ανατρέξτε στο φύλλο δεδομένων χρησιμοποιώντας το αντικείμενο των φύλλων εργασίας.
Βήμα 3: Ανατρέξτε στον συγκεντρωτικό πίνακα ανά όνομα
Ανατρέξτε στο όνομα του συγκεντρωτικού πίνακα με το όνομα του συγκεντρωτικού πίνακα.
Βήμα 4: Χρησιμοποιήστε τη μέθοδο Refresh Table
Επιλέξτε τη μέθοδο ως "Ανανέωση πίνακα".
Τώρα, αυτός ο κώδικας θα ανανεώσει τον συγκεντρωτικό πίνακα "Συγκεντρωτικός πίνακας1" κάθε φορά που υπάρχει οποιαδήποτε αλλαγή στο φύλλο δεδομένων προέλευσης. Μπορείτε να χρησιμοποιήσετε τον παρακάτω κωδικό. Απλά πρέπει να αλλάξετε το όνομα του συγκεντρωτικού πίνακα.
Κώδικας:
Φύλλο εργασίας Private Sub Works_Change (ByVal Target As Range) ("Sheet Data"). PivotTable ("PivotTable1"). RefreshTable End Sub
# 2 - Ανανέωση όλων των συγκεντρωτικών πινάκων του ίδιου φύλλου εργασίας
Εάν έχετε πολλούς συγκεντρωτικούς πίνακες στο ίδιο φύλλο εργασίας, μπορείτε να ανανεώσετε όλους τους συγκεντρωτικούς πίνακες με ένα μόνο κλικ. Χρησιμοποιήστε τον παρακάτω κώδικα για να ανανεώσετε όλους τους συγκεντρωτικούς πίνακες στο φύλλο.
Κώδικας:
Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Επιλέξτε With ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable. RefreshTable .PivotTables ("Table5"). RefreshTable End με End Sub
Πρέπει να αλλάξετε το όνομα του φύλλου εργασίας και τα ονόματα του συγκεντρωτικού πίνακα σύμφωνα με τις λεπτομέρειες του φύλλου εργασίας σας.
# 3 - Ανανέωση όλων των πινάκων στο βιβλίο εργασίας
Είναι πολύ απίθανο να έχουμε όλους τους συγκεντρωτικούς πίνακες στο ίδιο φύλλο εργασίας. Συνήθως, για κάθε αναφορά, προσπαθούμε να προσθέσουμε ξεχωριστούς συγκεντρωτικούς πίνακες σε ξεχωριστά φύλλα. Σε αυτές τις περιπτώσεις, δεν μπορούμε να συνεχίσουμε να γράφουμε τον κωδικό για κάθε συγκεντρωτικό πίνακα που θα ανανεωθεί.
Έτσι, αυτό που μπορούμε να κάνουμε είναι με έναν κωδικό χρησιμοποιώντας βρόχους, μπορούμε να βρούμε όλους τους συγκεντρωτικούς πίνακες στο βιβλίο εργασίας και να τους ανανεώσουμε με ένα μόνο κλικ του κουμπιού.
Ο παρακάτω κώδικας θα περάσει σε κάθε συγκεντρωτικό πίνακα και θα τους ανανεώσει.
Κωδικός 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT ως συγκεντρωτικός πίνακας για κάθε PT στο ActiveWorkbook.PivotTables PT.RefreshTable Επόμενο PT End Sub
Κωδικός 2:
Υπο Refresh_Pivot_Tables_Example3 () Dim PC ως PivotCache για κάθε υπολογιστή στο ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub
Και οι δύο κωδικοί θα κάνουν την ανανέωση των συγκεντρωτικών πινάκων.
Εάν θέλετε ο συγκεντρωτικός πίνακας να ανανεωθεί μόλις υπάρξει αλλαγή στο φύλλο δεδομένων του συγκεντρωτικού φύλλου, πρέπει να αντιγράψετε και να επικολλήσετε τους παραπάνω κωδικούς στο συμβάν Αλλαγή φύλλου εργασίας σε αυτό το βιβλίο εργασίας.
# 4 - Αποφύγετε τη φόρτωση χρόνου χρησιμοποιώντας το φύλλο εργασίας Απενεργοποίηση συμβάντος
Όταν χρησιμοποιούμε το συμβάν "Αλλαγή φύλλου εργασίας", συνεχίζει να ανανεώνεται ακόμη και όταν δεν υπάρχει καμία αλλαγή στην πηγή δεδομένων, αλλά εάν συμβεί κάποια αλλαγή στο φύλλο εργασίας.
Ακόμα κι αν εισαγάγετε μια μεμονωμένη κουκκίδα στο φύλλο εργασίας, προσπαθεί να ανανεώσει τον συγκεντρωτικό πίνακα. Έτσι, για να το αποφύγουμε αυτό, μπορούμε να χρησιμοποιήσουμε τη μέθοδο "Worksheet Deactivate" αντί της μεθόδου "Change Sheet Change"
Απενεργοποιήστε τις ενημερώσεις συμβάντων στον συγκεντρωτικό πίνακα κατά τη μετακίνηση από ένα φύλλο σε άλλο φύλλο.
