seychellesartprojects.org

Συγκεντρωτικός πίνακας Excel VBA

Οι Συγκεντρωτικοί Πίνακες αποτελούν την καρδιά της σύνοψης της αναφοράς μεγάλου όγκου δεδομένων. Μπορούμε επίσης να αυτοματοποιήσουμε τη διαδικασία δημιουργίας ενός συγκεντρωτικού πίνακα μέσω κωδικοποίησης VBA. Είναι σημαντικό μέρος οποιασδήποτε αναφοράς ή ταμπλό, στο excel είναι εύκολο να δημιουργήσετε πίνακες με ένα κουμπί, αλλά στο VBA πρέπει να γράψουμε μερικούς κωδικούς για να αυτοματοποιήσουμε τον συγκεντρωτικό μας πίνακα, πριν από το Excel 2007 και τις παλαιότερες εκδόσεις του στο VBA δεν χρειαζόμαστε δημιουργήστε μια προσωρινή μνήμη για συγκεντρωτικούς πίνακες, αλλά στο Excel 2010 και απαιτούνται οι νεότερες εκδόσεις της.

Η VBA μπορεί να μας εξοικονομήσει πολύ χρόνο στο χώρο εργασίας μας, παρόλο που δεν είναι τόσο εύκολο αλλά αξίζει να ξοδεύουμε χρόνο για να το μάθουμε. Χρειάστηκαν 6 μήνες για να κατανοήσω τη διαδικασία δημιουργίας συγκεντρωτικών πινάκων μέσω του VBA. Ξέρετε τι έκαναν θαύματα για αυτούς τους 6 μήνες γιατί έκανα τόσα πολλά λάθη προσπαθώντας να δημιουργήσω τον συγκεντρωτικό πίνακα.

Αλλά το πραγματικό πράγμα είναι ότι έχω μάθει από τα λάθη μου και τώρα γράφω αυτό το άρθρο για να σας δείξω πώς να δημιουργήσετε συγκεντρωτικούς πίνακες χρησιμοποιώντας κώδικα.

Με ένα μόνο κλικ ενός κουμπιού, μπορούμε να δημιουργήσουμε αναφορές.

Βήματα για τη δημιουργία συγκεντρωτικού πίνακα στο VBA

Μπορείτε να κατεβάσετε αυτό το Πρότυπο συγκεντρωτικού πίνακα VBA εδώ - Πρότυπο συγκεντρωτικού πίνακα VBA

Για να δημιουργήσετε έναν συγκεντρωτικό πίνακα, είναι σημαντικό να έχετε δεδομένα. Για αυτό έχω δημιουργήσει κάποια εικονικά δεδομένα, μπορείτε να κατεβάσετε το βιβλίο εργασίας για να ακολουθήσετε μαζί μου με τα ίδια δεδομένα.

Βήμα 1: Ο  Συγκεντρωτικός Πίνακας είναι ένα αντικείμενο αναφοράς του συγκεντρωτικού πίνακα που δηλώνει τη μεταβλητή ως Συγκεντρωτικοί Πίνακες.

Κώδικας:

 Sub PivotTable () Dim PTable ως PivotTable End Sub 

Βήμα 2:  Πριν δημιουργήσουμε έναν συγκεντρωτικό πίνακα πρώτα, πρέπει να δημιουργήσουμε μια συγκεντρωτική κρυφή μνήμη για να καθορίσουμε την πηγή των δεδομένων.

Στον συγκεντρωτικό πίνακα τακτικών φύλλων εργασίας χωρίς να μας ενοχλεί, θα δημιουργήσουμε μια περιστροφική κρυφή μνήμη στο παρασκήνιο. Αλλά στο 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 φύλλο εργασίας Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("Pivot Φύλλο "). Διαγραφή" Αυτό θα διαγράψει το υπάρχον φύλλο εργασίας συγκεντρωτικού πίνακα. Προσθέστε μετά: = ActiveSheet "Αυτό θα προσθέσει νέο φύλλο εργασίας ActiveSheet.Name =" Pivot Sheet "" Αυτό θα μετονομάσει το φύλλο εργασίας ως "Pivot Sheet" On Error GoTo 0 Ορισμός PSheet = Worksheets ("Pivot Sheet") Ορισμός DSheet = Worksheets ("Data Sheet") 'Εύρεση τελευταίας γραμμής και στήλης στο φύλλο δεδομένων LR = DSheet.Cells (Rows.Count, 1). End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count). End (xlToLeft). Column 'Ορισμός του εύρους δεδομένων του συγκεντρωτικού πίνακα Ορισμός PRange = DSheet.Cells (1, 1). Resize (LR, LC) «Set pivot cahe Set PCache = ActiveWorkbook. Κελιά (1, 1), Όνομα πίνακα: = "Sales_Report") 'Εισαγωγή χώρας σε σειρά που αρχειοθετείται με φύλλο PS.PivotTables ("Sales_Report"). PivotFields ("Country"). Orientation = xlRowField. Θέση = 1 Τέλος με το "Εισαγωγή προϊόντος σε 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 ("Τμήμα"). Προσανατολισμός = xlColumnField. Θέση = 1 Τέλος με 'Εισαγωγή στήλης πωλήσεων στο πεδίο δεδομένων με το φύλλο PSΣυγκεντρωτικοί πίνακες ("Sales_Report"). PivotFields ("Sales"). Orientation = xlDataField .Position = 1 End with 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Reptyable "PivotStyleMedium14" Εμφάνιση σε μορφή πίνακα PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub