- Χρησιμοποιώντας το GetPivotData για να αποκτήσετε μια τιμή
- Δημιουργία Συγκεντρωτικού Πίνακα σε Φύλλο
- Δημιουργία περιστροφικού πίνακα σε νέο φύλλο
- Προσθήκη πεδίων στον Συγκεντρωτικό πίνακα
- Αλλαγή της διάταξης αναφοράς του Συγκεντρωτικού πίνακα
- Διαγραφή Συγκεντρωτικού Πίνακα
- Διαμορφώστε όλους τους Συγκεντρωτικούς πίνακες σε ένα βιβλίο εργασίας
- Αφαίρεση Πεδίων Συγκεντρωτικού Πίνακα
- Δημιουργία φίλτρου
- Ανανέωση του Συγκεντρωτικού Πίνακα σας
Αυτό το σεμινάριο θα δείξει πώς να εργάζεστε με Συγκεντρωτικούς Πίνακες χρησιμοποιώντας VBA.
Οι Συγκεντρωτικοί Πίνακες είναι εργαλεία σύνοψης δεδομένων που μπορείτε να χρησιμοποιήσετε για να αντλήσετε βασικές πληροφορίες και περιλήψεις από τα δεδομένα σας. Ας δούμε ένα παράδειγμα: έχουμε ένα σύνολο δεδομένων πηγής στα κελιά A1: D21 που περιέχει τις λεπτομέρειες των προϊόντων που πωλούνται, που φαίνονται παρακάτω:
Χρησιμοποιώντας το GetPivotData για να αποκτήσετε μια τιμή
Ας υποθέσουμε ότι έχετε έναν Συγκεντρωτικό Πίνακα που ονομάζεται Συγκεντρωτικός Πίνακας1 με Πωλήσεις στο πεδίο Τιμές/Δεδομένα, Προϊόν ως πεδίο γραμμών και Περιοχή ως πεδίο Στήλες. Μπορείτε να χρησιμοποιήσετε τη μέθοδο Συγκεντρωτικού Πίνακα.GetPivotData για να επιστρέψετε τιμές από Συγκεντρωτικούς πίνακες.
Ο ακόλουθος κωδικός θα επιστρέψει 1.130,00 $ (οι συνολικές πωλήσεις για την περιοχή της Ανατολής) από τον Συγκεντρωτικό Πίνακα:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Πωλήσεις", "Περιοχή", "Ανατολή") |
Σε αυτήν την περίπτωση, οι Πωλήσεις είναι το "DataField", το "Field1" είναι η Περιφέρεια και το "Item1" είναι Ανατολικό.
Ο ακόλουθος κωδικός θα επιστρέψει $ 980 (οι συνολικές πωλήσεις για το προϊόν ABC στη βόρεια περιοχή) από τον Συγκεντρωτικό πίνακα:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Πωλήσεις", "Προϊόν", "ABC", "Περιοχή", "Βόρεια") |
Σε αυτήν την περίπτωση, οι πωλήσεις είναι το "DataField", το "Field1" είναι προϊόν, το "Item1" είναι ABC, το "Field2" είναι η Περιφέρεια και το "Item2" είναι Βόρεια.
Μπορείτε επίσης να συμπεριλάβετε περισσότερα από 2 πεδία.
Η σύνταξη για το GetPivotData είναι:
GetPivotData (DataField, Πεδίο 1, Στοιχείο 1, Πεδίο2, Στοιχείο 2… ) όπου:
| Παράμετρος | Περιγραφή |
|---|---|
| Datafield | Πεδίο δεδομένων όπως πωλήσεις, ποσότητα κ.λπ. που περιέχει αριθμούς. |
| Πεδίο 1 | Όνομα στήλης ή πεδίου γραμμής στον πίνακα. |
| Στοιχείο 1 | Όνομα στοιχείου στο Πεδίο 1 (Προαιρετικό). |
| Πεδίο 2 | Όνομα στήλης ή πεδίου γραμμής στον πίνακα (Προαιρετικό). |
| Στοιχείο 2 | Όνομα στοιχείου στο Πεδίο 2 (Προαιρετικό). |
Δημιουργία Συγκεντρωτικού Πίνακα σε Φύλλο
Για να δημιουργήσουμε έναν Συγκεντρωτικό πίνακα με βάση το παραπάνω εύρος δεδομένων, στο κελί J2 στο Φύλλο 1 του ενεργού βιβλίου εργασίας, θα χρησιμοποιούσαμε τον ακόλουθο κώδικα:
| 1234567891011 | Φύλλα εργασίας ("Φύλλο1"). Κελιά (1, 1) .ΕπιλέξτεActiveWorkbook.PivotCaches.Create (Τύπος πηγής: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Έκδοση: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Φύλλα ("Φύλλο1"). Επιλέξτε |
Το αποτέλεσμα είναι:
Δημιουργία περιστροφικού πίνακα σε νέο φύλλο
Για να δημιουργήσουμε έναν Συγκεντρωτικό πίνακα με βάση το παραπάνω εύρος δεδομένων, σε ένα νέο φύλλο, του ενεργού βιβλίου εργασίας, θα χρησιμοποιήσουμε τον ακόλουθο κώδικα:
| 12345678910111213 | Φύλλα εργασίας ("Φύλλο1"). Κελιά (1, 1) .ΕπιλέξτεΦύλλα. ΠροσθήκηActiveWorkbook.PivotCaches.Create (Τύπος πηγής: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Έκδοση: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Φύλλα ("Φύλλο2"). Επιλέξτε |
Προσθήκη πεδίων στον Συγκεντρωτικό πίνακα
Μπορείτε να προσθέσετε πεδία στον πρόσφατα δημιουργημένο Συγκεντρωτικό πίνακα που ονομάζεται Συγκεντρωτικός Πίνακας1 με βάση το παραπάνω εύρος δεδομένων. Σημείωση: Το φύλλο που περιέχει τον Συγκεντρωτικό πίνακα πρέπει να είναι το Ενεργό φύλλο.
Για να προσθέσετε προϊόν στο πεδίο γραμμών, θα χρησιμοποιήσετε τον ακόλουθο κώδικα:
| 123 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτικά πεδία ("Προϊόν"). Προσανατολισμός = xlRowFieldActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτικά πεδία ("Προϊόν"). Θέση = 1 |
Για να προσθέσετε Περιοχή στο πεδίο στήλες, θα χρησιμοποιήσετε τον ακόλουθο κώδικα:
| 123 | ActiveSheet.PivotTables ("Συγκεντρωτικός πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). Προσανατολισμός = xlColumnFieldActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). Θέση = 1 |
Για να προσθέσετε πωλήσεις στην ενότητα τιμών με τη μορφή αριθμού νομίσματος, θα χρησιμοποιήσετε τον ακόλουθο κώδικα:
| 123456789 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). AddDataField ActiveSheet.PivotTables (_"Συγκεντρωτικός Πίνακας1"). PivotFields ("Πωλήσεις"), "Άθροισμα πωλήσεων", xlSumΜε ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). PivotFields ("Άθροισμα πωλήσεων").NumberFormat = "$#, ## 0,00"Τέλος με |
Το αποτέλεσμα είναι:
Αλλαγή της διάταξης αναφοράς του Συγκεντρωτικού πίνακα
Μπορείτε να αλλάξετε τη διάταξη αναφοράς του Συγκεντρωτικού πίνακα. Ο ακόλουθος κώδικας θα αλλάξει τη διάταξη αναφοράς του Συγκεντρωτικού πίνακα σε μορφή πίνακα:
| 1 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). TableStyle2 = "PivotStyleLight18" |
Διαγραφή Συγκεντρωτικού Πίνακα
Μπορείτε να διαγράψετε έναν Συγκεντρωτικό πίνακα χρησιμοποιώντας το VBA. Ο ακόλουθος κώδικας θα διαγράψει τον Συγκεντρωτικό πίνακα που ονομάζεται Συγκεντρωτικός Πίνακας1 στο Ενεργό Φύλλο:
| 12 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτική επιλογή "", xlDataAndLabel, TrueΕπιλογή. ClearContents |
Διαμορφώστε όλους τους Συγκεντρωτικούς πίνακες σε ένα βιβλίο εργασίας
Μπορείτε να μορφοποιήσετε όλους τους Συγκεντρωτικούς πίνακες σε ένα βιβλίο εργασίας χρησιμοποιώντας το VBA. Ο ακόλουθος κώδικας χρησιμοποιεί μια δομή βρόχου για να βολέψει όλα τα φύλλα ενός βιβλίου εργασίας και να διαγράψει όλους τους Συγκεντρωτικούς πίνακες στο βιβλίο εργασίας:
| 12345678910111213 | Sub FormattingAllThePivotTablesInAWorkbook ()Ελαφρύ ως φύλλο εργασίαςDim wb Ως βιβλίο εργασίαςΡύθμιση wb = ActiveWorkbookDim pt Ως Συγκεντρωτικός ΠίνακαςΓια κάθε εβδομάδα σε wb. ΦύλλαΓια κάθε pt Σε wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Επόμενο πόντοΕπόμενα εβδομάδεςΤέλος υπο |
Για να μάθετε περισσότερα σχετικά με τον τρόπο χρήσης του Loops στο VBA κάντε κλικ εδώ.
Αφαίρεση Πεδίων Συγκεντρωτικού Πίνακα
Μπορείτε να καταργήσετε πεδία σε έναν Συγκεντρωτικό πίνακα χρησιμοποιώντας το VBA. Ο ακόλουθος κώδικας θα καταργήσει το πεδίο Προϊόν στην ενότητα Σειρές από έναν Συγκεντρωτικό πίνακα που ονομάζεται Συγκεντρωτικός Πίνακας1 στο Ενεργό φύλλο:
| 12 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). PivotFields ("Προϊόν"). Προσανατολισμός = _xlΚρυμμένο |
Δημιουργία φίλτρου
Ένας Συγκεντρωτικός Πίνακας που ονομάζεται Συγκεντρωτικός Πίνακας1 έχει δημιουργηθεί με το Προϊόν στην ενότητα Σειρές και Πωλήσεις στην ενότητα Τιμές. Μπορείτε επίσης να δημιουργήσετε ένα φίλτρο για τον Συγκεντρωτικό σας πίνακα χρησιμοποιώντας το VBA. Ο ακόλουθος κώδικας θα δημιουργήσει ένα φίλτρο με βάση την περιοχή στην ενότητα Φίλτρα:
| 123 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). Προσανατολισμός = xlPageFieldActiveSheet.PivotTables ("Συγκεντρωτικός πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). Θέση = 1 |
Για να φιλτράρετε τον Συγκεντρωτικό πίνακα με βάση ένα μεμονωμένο στοιχείο αναφοράς σε αυτήν την περίπτωση στην περιοχή Ανατολή, θα χρησιμοποιούσατε τον ακόλουθο κώδικα:
| 12345 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). ClearAllFiltersActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). CurrentPage = _"Ανατολή" |
Ας υποθέσουμε ότι θέλετε να φιλτράρετε τον Συγκεντρωτικό σας πίνακα με βάση πολλές περιοχές, σε αυτήν την περίπτωση Ανατολικά και Βόρεια, θα χρησιμοποιούσατε τον ακόλουθο κώδικα:
| 1234567891011121314 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). Προσανατολισμός = xlPageFieldActiveSheet.PivotTables ("Συγκεντρωτικός πίνακας1"). Συγκεντρωτικά πεδία ("Περιοχή"). Θέση = 1ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). PivotFields ("Περιοχή"). _EnableMultiplePageItems = ΑλήθειαΜε ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). PivotFields ("Περιοχή").PivotItems ("Νότια"). Ορατά = Λάθος.PivotItems ("Δυτικά"). Ορατά = ΛάθοςΤέλος με |
Ανανέωση του Συγκεντρωτικού Πίνακα σας
Μπορείτε να ανανεώσετε τον Συγκεντρωτικό πίνακα σε VBA. Θα χρησιμοποιούσατε τον ακόλουθο κώδικα για να ανανεώσετε έναν συγκεκριμένο πίνακα που ονομάζεται Συγκεντρωτικός Πίνακας1 στο VBA:
| 1 | ActiveSheet.PivotTables ("Συγκεντρωτικός Πίνακας1"). PivotCache.Refresh |
