Σε προηγούμενο άρθρο, μάθαμε πώς μπορείτε δυναμικά να αλλάξετε και να ενημερώσετε μεμονωμένους περιστροφικούς πίνακες με συρρίκνωση ή επέκταση των πηγών δεδομένων.
Σε αυτό το άρθρο, θα μάθουμε πώς μπορούμε να κάνουμε όλους τους περιστροφικούς πίνακες σε ένα βιβλίο εργασίας να αλλάζουν αυτόματα την προέλευση δεδομένων. Με άλλα λόγια, αντί να αλλάζουμε έναν περιστροφικό πίνακα κάθε φορά, θα προσπαθήσουμε να αλλάξουμε την προέλευση δεδομένων όλων των περιστρεφόμενων πινάκων στο βιβλίο εργασίας ώστε να συμπεριλάβουμε δυναμικά νέες γραμμές και στήλες που προστίθενται στους πίνακες προέλευσης και να αντικατοπτρίζουν αμέσως την αλλαγή στους περιστροφικούς πίνακες.
Γράψτε κώδικα στο φύλλο δεδομένων πηγής
Δεδομένου ότι θέλουμε αυτό να είναι εντελώς αυτόματο, θα χρησιμοποιήσουμε ενότητες φύλλων για να γράψουμε κώδικα αντί για βασική μονάδα. Αυτό θα μας επιτρέψει να χρησιμοποιήσουμε εκδηλώσεις φύλλου εργασίας.
Εάν τα δεδομένα προέλευσης και οι συγκεντρωτικοί πίνακες βρίσκονται σε διαφορετικά φύλλα, θα γράψουμε τον κωδικό VBA για να αλλάξουμε την προέλευση δεδομένων περιστρεφόμενου πίνακα στο αντικείμενο φύλλου που περιέχει τα δεδομένα προέλευσης (όχι αυτό που περιέχει συγκεντρωτικούς πίνακες).
Πατήστε CTRL+F11 για να ανοίξετε τον επεξεργαστή VB. Τώρα μεταβείτε στο πρόγραμμα εξερεύνησης έργου και βρείτε το φύλλο που περιέχει τα δεδομένα προέλευσης. Κάντε διπλό κλικ σε αυτό.
Θα ανοίξει μια νέα περιοχή κωδικοποίησης. Μπορεί να μην βλέπετε καμία αλλαγή, αλλά τώρα έχετε πρόσβαση στα συμβάντα του φύλλου εργασίας.
Κάντε κλικ στο αριστερό αναπτυσσόμενο μενού και επιλέξτε το φύλλο εργασίας. Από το αριστερό αναπτυσσόμενο μενού, επιλέξτε απενεργοποίηση. Θα δείτε ένα κενό δευτερεύον γραμμένο στο όνομα της περιοχής κωδικού worksheet_deativate. Ο κωδικός μας για τη δυναμική αλλαγή των δεδομένων προέλευσης και τον ανανεωτικό πίνακα περιστροφής θα μπουν σε αυτό το μπλοκ κώδικα. Αυτός ο κωδικός θα εκτελείται κάθε φορά που αλλάζετε από το φύλλο δεδομένων σε οποιοδήποτε άλλο φύλλο. Μπορείτε να διαβάσετε για όλες τις εκδηλώσεις φύλλου εργασίας εδώ.
Τώρα είμαστε έτοιμοι να εφαρμόσουμε τον κώδικα.
Πηγαίος κώδικας για δυναμική ενημέρωση όλων των περιστροφικών πινάκων στο βιβλίο εργασίας με νέο εύρος
Για να εξηγήσω πώς λειτουργεί, έχω ένα βιβλίο εργασίας. Αυτό το βιβλίο εργασίας περιέχει τρία φύλλα. Το Sheet1 περιέχει τα δεδομένα προέλευσης που μπορούν να αλλάξουν. Το Sheet2 και το Sheet3 περιέχουν περιστροφικούς πίνακες που εξαρτώνται από τα δεδομένα προέλευσης του φύλλου2.
Τώρα έχω γράψει αυτόν τον κωδικό στην περιοχή κωδικοποίησης του φύλλου 1. Χρησιμοποιώ το συμβάν Worksheet_Deactivate, έτσι ώστε αυτός ο κώδικας να τρέχει για να ενημερώνει τον συγκεντρωτικό πίνακα κάθε φορά που αλλάζουμε από το φύλλο δεδομένων προέλευσης.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Καθορισμός αριθμού τελευταίας σειράς και στήλης lstrow = Κελιά (Rows.Count, 1). End (xlUp). Row lstcol = Cells (1, Columns.Count). End (xlToLeft). 'Ρύθμιση του νέου εύρους Ορισμός πηγής_δεδομένων = Εύρος (Κελιά (1, 1), Κελιά (lstrow, lstcol))' Κωδικοποιήστε σε κάθε φύλλο και περιστροφικό πίνακα για κάθε ws In ThisWorkbook.Worksheets For Every pt In ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_TypeType: = xlDatabase, _ SourceData: = source_data) Επόμενο pt Επόμενο ws Τέλος υπο
Εάν έχετε παρόμοιο βιβλίο εργασίας, μπορείτε να αντιγράψετε άμεσα αυτά τα δεδομένα. Σας εξήγησα ότι αυτός ο κώδικας λειτουργεί παρακάτω, ώστε να μπορείτε να τον τροποποιήσετε σύμφωνα με τις ανάγκες σας.
Μπορείτε να δείτε την επίδραση αυτού του κώδικα στο gif παρακάτω.
Πώς αυτός ο κώδικας αλλάζει αυτόματα τα δεδομένα προέλευσης και ενημερώνει τους συγκεντρωτικούς πίνακες;
Πρώτα απ 'όλα χρησιμοποιήσαμε ένα worksheet_deactivate event. Αυτό το συμβάν ενεργοποιείται μόνο όταν το φύλλο που περιέχει τον κωδικό είναι ενεργοποιημένο ή απενεργοποιημένο. Έτσι λοιπόν λειτουργεί αυτόματα ο κώδικας.
Για δυναμική λήψη ολόκληρου του πίνακα ως εύρους δεδομένων, καθορίζουμε την τελευταία σειρά και την τελευταία στήλη.
lstrow = Κελιά (Rows.Count, 1). End (xlUp). Row
lstcol = Κελιά (1, Στήλες. Αριθμός). Τέλος (xlToLeft). Στήλη
Χρησιμοποιώντας αυτούς τους δύο αριθμούς ορίζουμε τα πηγαία_ δεδομένα. Είμαστε σίγουροι ότι το εύρος δεδομένων πηγής θα ξεκινά πάντα από το Α1. Μπορείτε να ορίσετε τη δική σας αναφορά αρχικού κελιού.
Ορισμός πηγής_δεδομένων = Εύρος (Κελιά (1, 1), Κελιά (lstrow, lstcol))
Τώρα έχουμε τα δεδομένα πηγής τα οποία είναι δυναμικά. Απλώς πρέπει να το χρησιμοποιήσουμε στον περιστρεφόμενο πίνακα.
Δεδομένου ότι δεν γνωρίζουμε πόσους περιστρεφόμενους πίνακες θα περιέχει ένα βιβλίο εργασίας κάθε φορά, θα περιηγηθούμε σε κάθε φύλλο και σε περιστροφικούς πίνακες κάθε φύλλου. Για να μην μείνει περιστρεφόμενος πίνακας. Για αυτό χρησιμοποιούμε ένθετο για βρόχους.
Για κάθε ws Στο ThisWorkbook.Worksheets
Για κάθε pt Σε ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Τύπος πηγής: = xlDatabase, _
SourceData: = source_data)
Επόμενο σημ
Επόμενο ws
Ο πρώτος βρόχος περνά μέσα από κάθε φύλλο. Ο δεύτερος βρόχος επαναλαμβάνεται σε κάθε περιστρεφόμενο πίνακα σε ένα φύλλο.
Οι περιστρεφόμενοι πίνακες εκχωρούνται στη μεταβλητή pt Χρησιμοποιούμε τη μέθοδο ChangePivotCache του αντικειμένου pt. Δημιουργούμε δυναμικά μια κρυφή μνήμη περιστροφής χρησιμοποιώντας το ThisWorkbook.PivotCaches.Create
Μέθοδος. Αυτή η μέθοδος λαμβάνει δύο μεταβλητές SourceType και SourceData. Ως τύπος πηγής δηλώνουμε xlDatabase και ως SourceData περνάμε το εύρος source_data που έχουμε υπολογίσει νωρίτερα.
Και αυτό είναι. Έχουμε αυτοματοποιημένους περιστροφικούς πίνακες. Αυτό θα ενημερώσει αυτόματα όλους τους περιστροφικούς πίνακες στο βιβλίο εργασίας.
Λοιπόν, ναι, έτσι μπορείτε να αλλάξετε δυναμικά το εύρος πηγών δεδομένων όλων των περιστροφικών πινάκων σε ένα βιβλίο εργασίας στο Excel. Ελπίζω να ήμουν αρκετά επεξηγηματικός. Εάν έχετε οποιεσδήποτε ερωτήσεις σχετικά με αυτό το άρθρο, ενημερώστε με στην παρακάτω ενότητα σχολίων.
Πώς να ενημερώσετε δυναμικά το εύρος πηγών δεδομένων περιστροφικού πίνακα στο Excel: Για να αλλάξουμε δυναμικά το εύρος δεδομένων προέλευσης των περιστρεφόμενων πινάκων, χρησιμοποιούμε κεντρικές μνήμες περιστροφής. Αυτές οι λίγες γραμμές μπορούν να ενημερώσουν δυναμικά οποιονδήποτε περιστροφικό πίνακα αλλάζοντας το εύρος δεδομένων προέλευσης.
Πώς να ανανεώσετε αυτόματα τους συγκεντρωτικούς πίνακες χρησιμοποιώντας το VBA: Για να ανανεώσετε αυτόματα τους περιστροφικούς πίνακες, μπορείτε να χρησιμοποιήσετε συμβάντα VBA. Χρησιμοποιήστε αυτήν την απλή γραμμή κώδικα για να ενημερώσετε αυτόματα τον περιστρεφόμενο πίνακα. Μπορείτε να χρησιμοποιήσετε μία από τις 3 μεθόδους αυτόματης ανανέωσης περιστρεφόμενων πινάκων.
Εκτελέστε μακροεντολή εάν πραγματοποιηθεί οποιαδήποτε αλλαγή στο φύλλο σε καθορισμένο εύρος: Στις πρακτικές σας VBA, θα χρειαστεί να εκτελέσετε μακροεντολές όταν αλλάζει ένα συγκεκριμένο εύρος ή κελί. Σε αυτήν την περίπτωση, για την εκτέλεση μακροεντολών όταν πραγματοποιείται αλλαγή σε εύρος στόχου, χρησιμοποιούμε το συμβάν αλλαγής.
Εκτέλεση μακροεντολής όταν πραγματοποιείται οποιαδήποτε αλλαγή στο φύλλο | Έτσι, για να εκτελέσετε τη μακροεντολή σας κάθε φορά που ενημερώνεται το φύλλο, χρησιμοποιούμε τα Συμβάντα φύλλου εργασίας του VBA.
Απλούστερος κώδικας VBA για να επισημάνετε τη χρήση της τρέχουσας γραμμής και στήλης | Χρησιμοποιήστε αυτό το μικρό απόσπασμα VBA για να επισημάνετε την τρέχουσα γραμμή και στήλη του φύλλου.
Οι εκδηλώσεις φύλλου εργασίας στο Excel VBA | Το συμβάν φύλλου εργασίας είναι πραγματικά χρήσιμο όταν θέλετε οι μακροεντολές σας να εκτελούνται όταν εμφανίζεται ένα συγκεκριμένο συμβάν στο φύλλο.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.Η συνάρτηση VLOOKUP στο Excel | Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
COUNTIF στο Excel 2016 | Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένη τιμή. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.