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