Περιεχόμενα
Διατύπωση του προβλήματος
Οι συγκεντρωτικοί πίνακες είναι ένα από τα πιο εκπληκτικά εργαλεία στο Excel. Αλλά μέχρι στιγμής, δυστυχώς, καμία από τις εκδόσεις του Excel δεν μπορεί να κάνει ένα τόσο απλό και απαραίτητο πράγμα εν κινήσει όσο να δημιουργήσει μια σύνοψη για πολλά αρχικά εύρη δεδομένων που βρίσκονται, για παράδειγμα, σε διαφορετικά φύλλα ή σε διαφορετικούς πίνακες:
Πριν ξεκινήσουμε, ας ξεκαθαρίσουμε μερικά σημεία. Εκ των προτέρων, πιστεύω ότι στα δεδομένα μας πληρούνται οι ακόλουθες προϋποθέσεις:
- Οι πίνακες μπορούν να έχουν οποιονδήποτε αριθμό σειρών με οποιαδήποτε δεδομένα, αλλά πρέπει να έχουν την ίδια κεφαλίδα.
- Δεν πρέπει να υπάρχουν επιπλέον δεδομένα στα φύλλα με πίνακες προέλευσης. Ένα φύλλο – ένα τραπέζι. Για έλεγχο, σας συμβουλεύω να χρησιμοποιήσετε μια συντόμευση πληκτρολογίου Ctrl+Τέλος, το οποίο σας μεταφέρει στο τελευταίο κελί που χρησιμοποιήσατε στο φύλλο εργασίας. Στην ιδανική περίπτωση, αυτό θα πρέπει να είναι το τελευταίο κελί στον πίνακα δεδομένων. Εάν όταν κάνετε κλικ στο Ctrl+Τέλος επισημαίνεται οποιοδήποτε κενό κελί στα δεξιά ή κάτω από τον πίνακα – διαγράψτε αυτές τις κενές στήλες στα δεξιά ή γραμμές κάτω από τον πίνακα μετά τον πίνακα και αποθηκεύστε το αρχείο.
Μέθοδος 1: Δημιουργήστε πίνακες για ένα pivot χρησιμοποιώντας το Power Query
Ξεκινώντας από την έκδοση του 2010 για το Excel, υπάρχει ένα δωρεάν πρόσθετο Power Query που μπορεί να συλλέξει και να μετατρέψει οποιαδήποτε δεδομένα και στη συνέχεια να τα δώσει ως πηγή για τη δημιουργία ενός συγκεντρωτικού πίνακα. Η επίλυση του προβλήματός μας με τη βοήθεια αυτού του πρόσθετου δεν είναι καθόλου δύσκολη.
Αρχικά, ας δημιουργήσουμε ένα νέο κενό αρχείο στο Excel – θα γίνει η συναρμολόγηση σε αυτό και στη συνέχεια θα δημιουργηθεί ένας συγκεντρωτικός πίνακας σε αυτό.
Στη συνέχεια, στην καρτέλα ημερομηνία (εάν έχετε Excel 2016 ή μεταγενέστερο) ή στην καρτέλα Ερώτημα ισχύος (αν έχετε Excel 2010-2013) επιλέξτε την εντολή Δημιουργία ερωτήματος – Από αρχείο – Excel (Λήψη δεδομένων — Από αρχείο — Excel) και καθορίστε το αρχείο προέλευσης με τους πίνακες που θα συλλεχθούν:
Στο παράθυρο που εμφανίζεται, επιλέξτε οποιοδήποτε φύλλο (δεν έχει σημασία ποιο) και πατήστε το κουμπί παρακάτω Αλλαγή (Επεξεργασία):
Το παράθυρο του Power Query Query Editor θα πρέπει να ανοίξει πάνω από το Excel. Στη δεξιά πλευρά του παραθύρου στον πίνακα Παράμετροι αιτήματος διαγράψτε όλα τα βήματα που δημιουργούνται αυτόματα εκτός από το πρώτο – Πηγή (Πηγή):
Τώρα βλέπουμε μια γενική λίστα με όλα τα φύλλα. Εάν εκτός από τα φύλλα δεδομένων υπάρχουν και άλλα πλαϊνά φύλλα στο αρχείο, τότε σε αυτό το βήμα η αποστολή μας είναι να επιλέξουμε μόνο εκείνα τα φύλλα από τα οποία πρέπει να φορτωθούν οι πληροφορίες, εξαιρουμένων όλων των άλλων που χρησιμοποιούν το φίλτρο στην κεφαλίδα του πίνακα:
Διαγραφή όλων των στηλών εκτός από τη στήλη ημερομηνίακάνοντας δεξί κλικ σε μια επικεφαλίδα στήλης και επιλέγοντας Διαγραφή άλλων στηλών (Αφαιρώ άλλες στήλες):
Στη συνέχεια, μπορείτε να επεκτείνετε τα περιεχόμενα των συλλεγόμενων πινάκων κάνοντας κλικ στο διπλό βέλος στην κορυφή της στήλης (πλαίσιο ελέγχου Χρησιμοποιήστε το αρχικό όνομα στήλης ως πρόθεμα μπορείτε να το απενεργοποιήσετε):
Εάν τα κάνατε όλα σωστά, τότε σε αυτό το σημείο θα πρέπει να δείτε τα περιεχόμενα όλων των πινάκων που συλλέγονται ο ένας κάτω από τον άλλο:
Απομένει να ανυψώσετε την πρώτη σειρά στην κεφαλίδα του πίνακα με το κουμπί Χρησιμοποιήστε την πρώτη γραμμή ως κεφαλίδες (Χρησιμοποιήστε την πρώτη σειρά ως κεφαλίδες) αυτί Αρχική (Σπίτι) και αφαιρέστε τις διπλότυπες κεφαλίδες πίνακα από τα δεδομένα χρησιμοποιώντας ένα φίλτρο:
Αποθηκεύστε όλα όσα έγιναν με την εντολή Κλείσιμο και φόρτωση – Κλείσιμο και φόρτωση σε… (Κλείσιμο & Φόρτωση — Κλείσιμο & Φόρτωση σε…) αυτί Αρχική (Σπίτι)και στο παράθυρο που ανοίγει, ορίστε την επιλογή Μόνο σύνδεση (Μόνο σύνδεση):
Τα παντα. Απομένει μόνο να φτιάξουμε μια περίληψη. Για να το κάνετε αυτό, μεταβείτε στην καρτέλα Εισαγωγή – Συγκεντρωτικός Πίνακας (Εισαγωγή — Συγκεντρωτικός πίνακας), επιλέξτε την επιλογή Χρησιμοποιήστε εξωτερική πηγή δεδομένων (Χρησιμοποιήστε εξωτερική πηγή δεδομένων)και στη συνέχεια κάνοντας κλικ στο κουμπί Επιλέξτε τη σύνδεση, το αίτημά μας. Η περαιτέρω δημιουργία και διαμόρφωση του άξονα περιστροφής γίνεται με έναν εντελώς τυπικό τρόπο, σύροντας τα πεδία που χρειαζόμαστε στις γραμμές, τις στήλες και την περιοχή τιμών:
Εάν τα δεδομένα προέλευσης αλλάξουν στο μέλλον ή προστεθούν μερικά ακόμη φύλλα καταστήματος, τότε θα αρκεί να ενημερώσουμε το ερώτημα και τη σύνοψή μας χρησιμοποιώντας την εντολή Ανανέωση όλων αυτί ημερομηνία (Δεδομένα — Ανανέωση όλων).
Μέθοδος 2. Ενώνουμε πίνακες με την εντολή UNION SQL σε μια μακροεντολή
Μια άλλη λύση στο πρόβλημά μας αντιπροσωπεύεται από αυτήν τη μακροεντολή, η οποία δημιουργεί ένα σύνολο δεδομένων (cache) για τον συγκεντρωτικό πίνακα χρησιμοποιώντας την εντολή ΕΝΟΤΗΤΑ Γλώσσα ερωτημάτων SQL. Αυτή η εντολή συνδυάζει πίνακες από όλους που καθορίζονται στον πίνακα Ονόματα Φύλλων φύλλα του βιβλίου σε έναν ενιαίο πίνακα δεδομένων. Δηλαδή, αντί να κάνουμε φυσική αντιγραφή και επικόλληση σειρών από διαφορετικά φύλλα σε ένα, κάνουμε το ίδιο στη μνήμη RAM του υπολογιστή. Στη συνέχεια, η μακροεντολή προσθέτει ένα νέο φύλλο με το συγκεκριμένο όνομα (μεταβλητή Όνομα φύλλου αποτελεσμάτων) και δημιουργεί μια πλήρη (!) σύνοψη με βάση τη συλλεγμένη κρυφή μνήμη.
Για να χρησιμοποιήσετε μια μακροεντολή, χρησιμοποιήστε το κουμπί της Visual Basic στην καρτέλα προγραμματιστή (Προγραμματιστής) ή συντόμευση πληκτρολογίου άλλος+F11. Στη συνέχεια εισάγουμε μια νέα κενή ενότητα μέσω του μενού Εισαγωγή – Ενότητα και αντιγράψτε τον παρακάτω κώδικα εκεί:
Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache Ως PivotCache Dim objRS Ως Object Dim ResultSheetName Ως String Dim SheetsName As Variant 'όνομα φύλλου όπου το προκύπτον pivot θα εμφανιστεί στο "ReultSheet" ονόματα με πίνακες προέλευσης SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'δημιουργούμε μια κρυφή μνήμη για πίνακες από φύλλα από SheetsNames With ActiveWorkbook ReDim arSQL(1 έως (UBound(SheetsNames) + 1) ) Για i = LBound (SeetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Επόμενο i Set objRS = CreateObject("ADODB.Recordset") objRS .Άνοιγμα Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Τέλος με "εκ νέου δημιουργία του φύλλου για να εμφανιστεί ο συγκεντρωτικός πίνακας που προκύπτει Στο σφάλμα Συνέχιση Επόμενης εφαρμογής.DisplayAlerts = False Worksheets(ResultSheetName).Διαγραφή συνόλου wsPivot = Φύλλα εργασίας.Προσθήκη wsPivo t. Name = ResultSheetName 'εμφάνιση της σύνοψης της κρυφής μνήμης που δημιουργήθηκε σε αυτό το φύλλο Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivotachet. objPivotCache = Nothing Range("A3").Επιλέξτε End With End Sub Η ολοκληρωμένη μακροεντολή μπορεί στη συνέχεια να εκτελεστεί με μια συντόμευση πληκτρολογίου άλλος+F8 ή το κουμπί Μακροεντολές στην καρτέλα προγραμματιστή (Προγραμματιστής — Μακροεντολές).
Μειονεκτήματα αυτής της προσέγγισης:
- Τα δεδομένα δεν ενημερώνονται επειδή η κρυφή μνήμη δεν έχει σύνδεση με τους πίνακες προέλευσης. Εάν αλλάξετε τα δεδομένα προέλευσης, πρέπει να εκτελέσετε ξανά τη μακροεντολή και να δημιουργήσετε ξανά τη σύνοψη.
- Όταν αλλάζετε τον αριθμό των φύλλων, είναι απαραίτητο να επεξεργαστείτε τον κωδικό μακροεντολής (πίνακας Ονόματα Φύλλων).
Αλλά στο τέλος παίρνουμε έναν πραγματικό πλήρη πίνακα περιστροφής, χτισμένο σε πολλές περιοχές από διαφορετικά φύλλα:
Voila!
Τεχνική σημείωση: εάν λάβετε ένα σφάλμα όπως "Ο πάροχος δεν είναι εγγεγραμμένος" κατά την εκτέλεση της μακροεντολής, τότε πιθανότατα έχετε μια έκδοση 64-bit του Excel ή μια μη ολοκληρωμένη έκδοση του Office είναι εγκατεστημένη (χωρίς Access). Για να διορθώσετε την κατάσταση, αντικαταστήστε το τμήμα στον κώδικα μακροεντολής:
Provider=Microsoft.Jet.OLEDB.4.0;
στο:
Provider=Microsoft.ACE.OLEDB.12.0;
Και κατεβάστε και εγκαταστήστε τη δωρεάν μηχανή επεξεργασίας δεδομένων από την Access από τον ιστότοπο της Microsoft – Microsoft Access Database Engine 2010 Redistributable
Μέθοδος 3: Ενοποίηση του Οδηγού Συγκεντρωτικού Πίνακα από παλιές εκδόσεις του Excel
Αυτή η μέθοδος είναι λίγο ξεπερασμένη, αλλά αξίζει να αναφερθεί. Επίσημα μιλώντας, σε όλες τις εκδόσεις μέχρι και το 2003, υπήρχε η επιλογή στον Οδηγό Συγκεντρωτικού Πίνακα για "δημιουργία ενός άξονα για πολλά εύρη ενοποίησης". Ωστόσο, μια αναφορά που έχει κατασκευαστεί με αυτόν τον τρόπο, δυστυχώς, θα είναι μόνο μια θλιβερή εμφάνιση μιας πραγματικής ολοκληρωμένης σύνοψης και δεν υποστηρίζει πολλά από τα "τσιπ" των συμβατικών συγκεντρωτικών πινάκων:
Σε ένα τέτοιο pivot, δεν υπάρχουν επικεφαλίδες στηλών στη λίστα πεδίων, δεν υπάρχει ρύθμιση ευέλικτης δομής, το σύνολο των συναρτήσεων που χρησιμοποιούνται είναι περιορισμένο και, γενικά, όλα αυτά δεν μοιάζουν πολύ με έναν πίνακα περιστροφής. Ίσως αυτός είναι ο λόγος που, ξεκινώντας το 2007, η Microsoft αφαίρεσε αυτήν τη λειτουργία από το τυπικό παράθυρο διαλόγου κατά τη δημιουργία αναφορών συγκεντρωτικού πίνακα. Τώρα αυτή η δυνατότητα είναι διαθέσιμη μόνο μέσω ενός προσαρμοσμένου κουμπιού Οδηγός Συγκεντρωτικού Πίνακα(Οδηγός συγκεντρωτικού πίνακα), το οποίο, εάν το επιθυμείτε, μπορεί να προστεθεί στη γραμμή εργαλείων γρήγορης πρόσβασης μέσω Αρχείο – Επιλογές – Προσαρμογή της γραμμής εργαλείων γρήγορης πρόσβασης – Όλες οι εντολές (Αρχείο — Επιλογές — Προσαρμογή γραμμής εργαλείων γρήγορης πρόσβασης — Όλες οι εντολές):
Αφού κάνετε κλικ στο κουμπί που προστέθηκε, πρέπει να επιλέξετε την κατάλληλη επιλογή στο πρώτο βήμα του οδηγού:
Και, στη συνέχεια, στο επόμενο παράθυρο, επιλέξτε κάθε περιοχή με τη σειρά και προσθέστε τη στη γενική λίστα:
Αλλά, και πάλι, αυτή δεν είναι μια πλήρης περίληψη, οπότε μην περιμένετε πολλά από αυτήν. Μπορώ να προτείνω αυτήν την επιλογή μόνο σε πολύ απλές περιπτώσεις.
- Δημιουργία αναφορών με Συγκεντρωτικούς Πίνακες
- Ρυθμίστε τους υπολογισμούς στους Συγκεντρωτικούς Πίνακες
- Τι είναι οι μακροεντολές, πώς να τις χρησιμοποιήσετε, πού να αντιγράψετε τον κώδικα VBA κ.λπ.
- Συλλογή δεδομένων από πολλά φύλλα σε ένα (πρόσθετο PLEX)