Συγκεντρωτικός πίνακας Excel VBA
Οι Συγκεντρωτικοί Πίνακες αποτελούν την καρδιά της σύνοψης της αναφοράς μεγάλου όγκου δεδομένων. Μπορούμε επίσης να αυτοματοποιήσουμε τη διαδικασία δημιουργίας ενός συγκεντρωτικού πίνακα μέσω κωδικοποίησης VBA. Είναι σημαντικό μέρος οποιασδήποτε αναφοράς ή ταμπλό, στο excel είναι εύκολο να δημιουργήσετε πίνακες με ένα κουμπί, αλλά στο VBA πρέπει να γράψουμε μερικούς κωδικούς για να αυτοματοποιήσουμε τον συγκεντρωτικό μας πίνακα, πριν από το Excel 2007 και τις παλαιότερες εκδόσεις του στο VBA δεν χρειαζόμαστε δημιουργήστε μια προσωρινή μνήμη για συγκεντρωτικούς πίνακες, αλλά στο Excel 2010 και απαιτούνται οι νεότερες εκδόσεις της.
Η VBA μπορεί να μας εξοικονομήσει χρόνο στο χώρο εργασίας μας. Παρόλο που το να μάθεις δεν είναι τόσο εύκολο αλλά αξίζει να ξοδέψεις χρόνο να το μάθεις. Χρειάστηκαν 6 μήνες για να κατανοήσω τη διαδικασία δημιουργίας συγκεντρωτικών πινάκων μέσω του VBA. Ξέρετε τι έκαναν θαύματα για αυτούς τους 6 μήνες γιατί έκανα τόσα πολλά λάθη προσπαθώντας να δημιουργήσω τον συγκεντρωτικό πίνακα.
Αλλά το πραγματικό πράγμα είναι ότι έχω μάθει από τα λάθη μου και τώρα γράφω αυτό το άρθρο για να σας δείξω πώς να δημιουργήσετε συγκεντρωτικούς πίνακες χρησιμοποιώντας κώδικα.
Με ένα μόνο κλικ ενός κουμπιού, μπορούμε να δημιουργήσουμε αναφορές.
Βήματα για τη δημιουργία συγκεντρωτικού πίνακα στο VBA
Για να δημιουργήσετε έναν συγκεντρωτικό πίνακα, είναι σημαντικό να έχετε δεδομένα. Για αυτό, έχω δημιουργήσει κάποια εικονικά δεδομένα. Μπορείτε να κατεβάσετε το βιβλίο εργασίας για να ακολουθήσετε μαζί μου με τα ίδια δεδομένα.
Βήμα 1: Ο Συγκεντρωτικός Πίνακας είναι ένα αντικείμενο αναφοράς του συγκεντρωτικού πίνακα που δηλώνει τη μεταβλητή ως Συγκεντρωτικοί Πίνακες.
Κώδικας:
Sub PivotTable () Dim PTable ως PivotTable End Sub
Βήμα 2: Πριν δημιουργήσουμε έναν συγκεντρωτικό πίνακα πρώτα, πρέπει να δημιουργήσουμε έναν συγκεντρωτικό cache για να καθορίσουμε την πηγή των δεδομένων.
Στα κανονικά φύλλα εργασίας, ο συγκεντρωτικός πίνακας χωρίς να μας ενοχλεί θα δημιουργήσει μια περιστροφική κρυφή μνήμη στο παρασκήνιο. Αλλά στο VBA, πρέπει να δημιουργήσουμε.
Για αυτό, ορίστε τη μεταβλητή PivotCache.
Κώδικας:
Μειωμένο PCache ως PivotCache
Βήμα 3: Για τον προσδιορισμό του εύρους συγκεντρωτικών δεδομένων ορίστε τη μεταβλητή ως εύρος.
Κώδικας:
Dim PRange ως εύρος
Βήμα 4: Για να εισαγάγετε έναν συγκεντρωτικό πίνακα, χρειαζόμαστε ένα ξεχωριστό φύλλο για να προσθέσουμε φύλλα εργασίας για τον συγκεντρωτικό πίνακα για να δηλώσουμε τη μεταβλητή ως φύλλο εργασίας.
Κώδικας:
Dim PSheet ως φύλλο εργασίας
Βήμα 5: Παρομοίως, για αναφορά στα δεδομένα που περιέχουν το φύλλο εργασίας δηλώστε μια ακόμη μεταβλητή ως φύλλο εργασίας
Κώδικας:
Dim DSheet ως φύλλο εργασίας
Βήμα 6: Τέλος, για να βρείτε την τελευταία γραμμή και στήλη που χρησιμοποιήσατε, ορίστε δύο ακόμη μεταβλητές ως Long.
Κώδικας:
Dim LR Όσο Long Dim LC Όσο καιρό
Βήμα 7: Τώρα, πρέπει να εισαγάγουμε ένα νέο φύλλο για να δημιουργήσουμε έναν συγκεντρωτικό πίνακα. Πριν από αυτό, εάν υπάρχει συγκεντρωτικό φύλλο, τότε πρέπει να το διαγράψουμε.
Βήμα 8: Τώρα, ορίστε τη μεταβλητή αντικειμένου PSheet και DSheet σε Pivot Sheet και Data Sheet, αντίστοιχα.
Βήμα 9: Βρείτε τη γραμμή που χρησιμοποιήθηκε τελευταία και τη στήλη που χρησιμοποιήθηκε τελευταία στο φύλλο δεδομένων.
Βήμα 10: Τώρα ορίστε το εύρος περιστροφής χρησιμοποιώντας την τελευταία σειρά και την τελευταία στήλη.
Αυτό θα ρυθμίσει τέλεια το εύρος δεδομένων. Θα επιλέξει αυτόματα το εύρος δεδομένων, ακόμα κι αν υπάρχει προσθήκη ή διαγραφή δεδομένων στο φύλλο δεδομένων.
Βήμα 11: Πριν δημιουργήσουμε έναν συγκεντρωτικό πίνακα, πρέπει να δημιουργήσουμε μια συγκεντρωτική κρυφή μνήμη. Ορίστε τη μεταβλητή pivot cache χρησιμοποιώντας τον παρακάτω κώδικα VBA.
Βήμα 12: Τώρα, δημιουργήστε έναν κενό συγκεντρωτικό πίνακα.
Βήμα 13: Μετά την εισαγωγή του συγκεντρωτικού πίνακα, πρέπει πρώτα να εισαγάγουμε το πεδίο σειράς. Έτσι θα εισαγάγω το πεδίο γραμμής ως στήλη Χώρα μου.
Σημείωση: Κατεβάστε το βιβλίο εργασίας για να κατανοήσετε τις στήλες δεδομένων.
Βήμα 14: Τώρα, ένα ακόμη στοιχείο θα εισαγάγω στο πεδίο της σειράς ως το δεύτερο στοιχείο θέσης. Θα εισαγάγω το προϊόν ως το δεύτερο στοιχείο γραμμής στο πεδίο γραμμής.
Βήμα 15: Μετά την εισαγωγή των στηλών στο πεδίο της γραμμής, πρέπει να εισαγάγουμε τιμές στο πεδίο της στήλης. Θα εισαγάγω το "Τμήμα" στο πεδίο της στήλης.
Βήμα 16: Τώρα, πρέπει να εισαγάγουμε αριθμούς στο πεδίο δεδομένων. Εισαγάγετε λοιπόν "Πωλήσεις" στο πεδίο δεδομένων.
Βήμα 17: Έχουμε τελειώσει με το τμήμα σύνοψης του συγκεντρωτικού πίνακα. Τώρα πρέπει να μορφοποιήσουμε τον πίνακα. Για να μορφοποιήσετε τον συγκεντρωτικό πίνακα, χρησιμοποιήστε τον παρακάτω κωδικό.
Για να εμφανίσετε τα στοιχεία τιμών που έχουν κατατεθεί στη σειρά σε μορφή πίνακα, προσθέστε τον παρακάτω κώδικα στο κάτω μέρος.
Εντάξει, τελειώσαμε εάν εκτελέσουμε αυτόν τον κώδικα χρησιμοποιώντας το πλήκτρο F5 ή χειροκίνητα τότε, θα πρέπει να έχουμε τον περιστρεφόμενο πίνακα έτσι.
Έτσι, χρησιμοποιώντας την κωδικοποίηση VBA, μπορούμε να αυτοματοποιήσουμε τη διαδικασία δημιουργίας ενός συγκεντρωτικού πίνακα.
Για αναφορά σας, έχω δώσει τον παρακάτω κώδικα.
Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
