Πρόσφατα έλαβα μια ερώτηση από έναν αναγνώστη σχετικά με τον συνδυασμό πολλών φύλλων εργασίας στο ίδιο βιβλίο εργασίας σε ένα μόνο φύλλο εργασίας.
Του ζήτησα να χρησιμοποιήσει το Power Query για να συνδυάσει διαφορετικά φύλλα, αλλά στη συνέχεια συνειδητοποίησα ότι για κάποιον νέο στο Power Query, αυτό μπορεί να είναι δύσκολο.
Έτσι αποφάσισα να γράψω αυτό το σεμινάριο και να δείξω τα ακριβή βήματα για να συνδυάσω πολλά φύλλα σε έναν πίνακα χρησιμοποιώντας το Power Query.
Κάτω από ένα βίντεο όπου δείχνω πώς να συνδυάζετε δεδομένα από πολλά φύλλα/πίνακες χρησιμοποιώντας το Power Query:
Παρακάτω υπάρχουν γραπτές οδηγίες σχετικά με τον τρόπο συνδυασμού πολλαπλών φύλλων (σε περίπτωση που προτιμάτε γραπτό κείμενο από βίντεο).
Σημείωση: Το Power Query μπορεί να χρησιμοποιηθεί ως πρόσθετο στο Excel 2010 και 2013 και είναι μια ενσωματωμένη δυνατότητα από το Excel 2016 και μετά. Με βάση την έκδοσή σας, ορισμένες εικόνες μπορεί να έχουν διαφορετική εμφάνιση (οι λήψεις εικόνων που χρησιμοποιούνται σε αυτό το σεμινάριο είναι από το Excel 2016).
Συνδυάστε δεδομένα από πολλά φύλλα εργασίας χρησιμοποιώντας το Power Query
Όταν συνδυάζετε δεδομένα από διαφορετικά φύλλα χρησιμοποιώντας το Power Query, απαιτείται να τα έχετε σε έναν πίνακα Excel (ή τουλάχιστον σε ονομαστικές περιοχές). Εάν τα δεδομένα δεν βρίσκονται σε έναν πίνακα Excel, η μέθοδος που εμφανίζεται εδώ δεν θα λειτουργήσει.
Ας υποθέσουμε ότι έχετε τέσσερα διαφορετικά φύλλα - Ανατολικά, Δυτικά, Βόρεια και Νότια.
Κάθε ένα από αυτά τα φύλλα εργασίας έχει τα δεδομένα σε έναν πίνακα Excel και η δομή του πίνακα είναι συνεπής (δηλαδή, οι κεφαλίδες είναι ίδιες).
Κάντε κλικ εδώ για να κατεβάσετε τα δεδομένα και να ακολουθήσετε.
Αυτό το είδος δεδομένων είναι εξαιρετικά εύκολο να συνδυαστεί χρησιμοποιώντας το Power Query (το οποίο λειτουργεί πολύ καλά με δεδομένα στον πίνακα Excel).
Για να λειτουργήσει καλύτερα αυτή η τεχνική, είναι καλύτερα να έχετε ονόματα για τους Πίνακες Excel (λειτουργήστε και χωρίς αυτό, αλλά είναι ευκολότερο να χρησιμοποιείτε όταν ονομάζονται οι πίνακες).
Έχω δώσει στους πίνακες τα ακόλουθα ονόματα: East_Data, West_Data, North_Data και South_Data.
Ακολουθούν τα βήματα για τον συνδυασμό πολλών φύλλων εργασίας με πίνακες Excel χρησιμοποιώντας το Power Query:
- Μεταβείτε στην καρτέλα Δεδομένα.
- Στην ομάδα Get & Transform Data, κάντε κλικ στην επιλογή «Λήψη δεδομένων».
- Μεταβείτε στην επιλογή «Από άλλες πηγές».
- Κάντε κλικ στην επιλογή «Κενό ερώτημα». Αυτό θα ανοίξει τον επεξεργαστή Power Query.
- Στο πρόγραμμα επεξεργασίας ερωτήματος, πληκτρολογήστε τον ακόλουθο τύπο στη γραμμή τύπων: = Excel.CurrentWorkbook(). Σημειώστε ότι οι τύποι του Power Query έχουν διάκριση πεζών -κεφαλαίων, οπότε πρέπει να χρησιμοποιήσετε τον ακριβή τύπο όπως αναφέρεται (αλλιώς θα εμφανιστεί σφάλμα).
- Πατήστε το πλήκτρο Enter. Αυτό θα σας δείξει όλα τα ονόματα των πινάκων σε ολόκληρο το βιβλίο εργασίας (θα σας δείξει επίσης τα ονομαζόμενα εύρη και/ή συνδέσεις σε περίπτωση που υπάρχουν στο βιβλίο εργασίας).
- [Προαιρετικό βήμα] Σε αυτό το παράδειγμα, θέλω να συνδυάσω όλους τους πίνακες. Εάν θέλετε να συνδυάσετε μόνο συγκεκριμένους Πίνακες Excel, μπορείτε να κάνετε κλικ στο αναπτυσσόμενο εικονίδιο στην κεφαλίδα ονόματος και να επιλέξετε αυτούς που θέλετε να συνδυάσετε. Ομοίως, εάν έχετε ονομαστεί εύρος ή συνδέσεις και θέλετε να συνδυάσετε μόνο πίνακες, μπορείτε επίσης να καταργήσετε αυτές τις ονομαστικές περιοχές.
- Στο κελί κεφαλίδας περιεχομένου, κάντε κλικ στο βέλος με το διπλό σημείο.
- Επιλέξτε τις στήλες που θέλετε να συνδυάσετε. Εάν θέλετε να συνδυάσετε όλες τις στήλες, βεβαιωθείτε ότι έχει επιλεγεί (Επιλογή όλων των στηλών).
- Καταργήστε την επιλογή "Χρήση ονόματος αρχικής στήλης ως πρόθεμα".
- Κάντε κλικ στο OK.
Τα παραπάνω βήματα θα συνδυάσουν τα δεδομένα από όλα τα φύλλα εργασίας σε έναν ενιαίο πίνακα.
Αν κοιτάξετε προσεκτικά, θα βρείτε ότι η τελευταία στήλη (δεξιά) έχει το όνομα των πινάκων του Excel (East_Data, West_Data, North_Data και South_Data). Αυτό είναι ένα αναγνωριστικό που μας λέει ποια εγγραφή προήλθε από ποιον πίνακα Excel. Αυτός είναι και ο λόγος που είπα ότι είναι καλύτερο να υπάρχουν περιγραφικά ονόματα για τους πίνακες του Excel.
Ακολουθούν μερικές τροποποιήσεις που μπορείτε να κάνετε στα συνδυασμένα δεδομένα στο ίδιο το Query του Power:
- Σύρετε και τοποθετήστε τη στήλη Όνομα στην αρχή.
- Καταργήστε το "_Data" από τη στήλη ονόματος (έτσι θα μείνετε με ανατολικά, δυτικά, βόρεια και νότια στη στήλη ονόματος). Για να το κάνετε αυτό, κάντε δεξί κλικ στην κεφαλίδα Όνομα και κάντε κλικ στο Αντικατάσταση τιμών. Στο παράθυρο διαλόγου Αντικατάσταση τιμών, αντικαταστήστε το _Data με ένα κενό.
- Αλλάξτε τη στήλη Δεδομένα για να εμφανίζονται μόνο οι ημερομηνίες (και όχι η ώρα). Για να το κάνετε αυτό, κάντε κλικ στην κεφαλίδα στήλης Ημερομηνία, μεταβείτε στην καρτέλα "Μεταμόρφωση" και αλλάξτε τον τύπο δεδομένων σε Ημερομηνία.
- Μετονομάστε το ερώτημα σε ConsolidatedData.
Τώρα που έχετε τα συνδυασμένα δεδομένα από όλα τα φύλλα εργασίας στο Power Query, μπορείτε να τα φορτώσετε στο Excel - ως νέος πίνακας σε νέο φύλλο εργασίας.
Για να το κάνω αυτό. ακολουθήστε τα παρακάτω βήματα:
- Κάντε κλικ στην καρτέλα "Αρχείο".
- Κάντε κλικ στο Κλείσιμο και φόρτωση σε.
- Στο παράθυρο διαλόγου Εισαγωγή δεδομένων, επιλέξτε Επιλογές πίνακα και Νέο φύλλο εργασίας.
- Κάντε κλικ στο κουμπί Ok.
Τα παραπάνω βήματα θα συνδυάσουν δεδομένα από όλα τα φύλλα εργασίας και θα σας δώσουν αυτά τα συνδυασμένα δεδομένα σε ένα νέο φύλλο εργασίας.
Ένα ζήτημα που πρέπει να επιλύσετε όταν χρησιμοποιείτε αυτήν τη μέθοδο
Σε περίπτωση που χρησιμοποιήσατε την παραπάνω μέθοδο για να συνδυάσετε όλους τους πίνακες στο βιβλίο εργασίας, πιθανόν να αντιμετωπίσετε ένα πρόβλημα.
Δείτε τον αριθμό των σειρών των συνδυασμένων δεδομένων - 1304 (που είναι σωστό).
Τώρα, εάν ανανεώσω το ερώτημα, ο αριθμός των γραμμών αλλάζει σε 2607. Ανανεώστε ξανά και θα αλλάξει σε 3910.
Εδώ είναι το πρόβλημα.
Κάθε φορά που ανανεώνετε το ερώτημα, προσθέτει όλες τις εγγραφές στα αρχικά δεδομένα στα συνδυασμένα δεδομένα.
Σημείωση: Θα αντιμετωπίσετε αυτό το ζήτημα μόνο εάν έχετε χρησιμοποιήσει το Power Query για να συνδυάσετε ΟΛΟΙ ΟΙ ΕΞΑΙΡΕΤΙΚΟΙ ΠΙΝΑΚΕΣ στο τετράδιο εργασίας. Σε περίπτωση που επιλέξατε συγκεκριμένους πίνακες για συνδυασμό, δεν θα αντιμετωπίσετε αυτό το ζήτημα.Ας κατανοήσουμε την αιτία αυτού του προβλήματος και πώς να το διορθώσουμε.
Όταν ανανεώνετε ένα ερώτημα, επιστρέφει και ακολουθεί όλα τα βήματα που κάναμε για να συνδυάσουμε τα δεδομένα.
Στο βήμα όπου χρησιμοποιήσαμε τον τύπο = Excel.CurrentWorkbook (), μας έδωσε μια λίστα με όλους τους πίνακες. Αυτό λειτούργησε καλά την πρώτη φορά καθώς υπήρχαν μόνο τέσσερα τραπέζια.
Αλλά όταν κάνετε ανανέωση, υπάρχουν πέντε πίνακες στο βιβλίο εργασίας - συμπεριλαμβανομένου του νέου πίνακα που εισήγαγε το Power Query, όπου έχουμε τα συνδυασμένα δεδομένα.
Έτσι κάθε φορά που ανανεώνετε το ερώτημα, εκτός από τους τέσσερις πίνακες Excel που θέλουμε να συνδυάσουμε, προσθέτει επίσης τον υπάρχοντα πίνακα ερωτήματος στα δεδομένα που προκύπτουν.
Αυτό ονομάζεται αναδρομή.
Εδώ είναι πώς να λύσετε αυτό το ζήτημα.
Μόλις εισαγάγετε = Excel.CurrentWorkbook () στη γραμμή τύπων Power Query και πατήσετε enter, λαμβάνετε μια λίστα με πίνακες Excel. Για να βεβαιωθείτε ότι συνδυάζετε μόνο τους πίνακες από το φύλλο εργασίας, πρέπει με κάποιο τρόπο να φιλτράρετε μόνο αυτούς τους πίνακες που θέλετε να συνδυάσετε και να αφαιρέσετε όλα τα άλλα.
Ακολουθούν τα βήματα για να βεβαιωθείτε ότι έχετε μόνο τους απαιτούμενους πίνακες:
- Κάντε κλικ στο αναπτυσσόμενο μενού και τοποθετήστε τον δείκτη του ποντικιού στα Φίλτρα κειμένου.
- Κάντε κλικ στην επιλογή Περιέχει.
- Στο παράθυρο διαλόγου Γραμμές φίλτρου, εισαγάγετε _Δεδομένα στο πεδίο δίπλα στην επιλογή ‘περιέχει’.
- Κάντε κλικ στο OK.
Ενδέχεται να μην δείτε καμία αλλαγή στα δεδομένα, αλλά αυτό θα αποτρέψει την προσθήκη του πίνακα που προκύπτει ξανά όταν ανανεωθεί το ερώτημα.
Σημειώστε ότι στα παραπάνω βήματα χρησιμοποιήσαμε "_Δεδομένα”Για να φιλτράρετε όπως ονομάσαμε τους πίνακες με αυτόν τον τρόπο. Αλλά τι γίνεται αν τα τραπέζια σας δεν ονομάζονται με συνέπεια. Τι γίνεται αν όλα τα ονόματα των πινάκων είναι τυχαία και δεν έχουν τίποτα κοινό.
Εδώ είναι ο τρόπος επίλυσης αυτού - χρησιμοποιήστε το φίλτρο «δεν ισούται» και εισαγάγετε το όνομα του ερωτήματος (το οποίο θα ήταν ConsolidatedData στο παράδειγμά μας). Αυτό θα διασφαλίσει ότι όλα θα παραμείνουν ίδια και ο πίνακας ερωτήματος που δημιουργείται φιλτράρεται.
Εκτός από το γεγονός ότι το Power Query καθιστά όλη αυτή τη διαδικασία συνδυασμού δεδομένων από διαφορετικά φύλλα (ή ακόμα και το ίδιο φύλλο) αρκετά εύκολη, ένα άλλο πλεονέκτημα της χρήσης του είναι ότι το καθιστά δυναμικό. Εάν προσθέσετε περισσότερες εγγραφές σε οποιονδήποτε από τους πίνακες και ανανεώσετε το Power Query, αυτόματα θα σας δώσει τα συνδυασμένα δεδομένα.Σημαντική σημείωση: Στο παράδειγμα που χρησιμοποιήθηκε σε αυτό το σεμινάριο, οι κεφαλίδες ήταν ίδιες. Σε περίπτωση που οι κεφαλίδες είναι διαφορετικές, το Power Query θα συνδυάσει και θα δημιουργήσει όλες τις στήλες στον νέο πίνακα. Εάν τα δεδομένα είναι διαθέσιμα για αυτήν τη στήλη, θα εμφανιστούν, διαφορετικά θα εμφανιστούν μηδενικά.
Μπορεί επίσης να σας αρέσουν τα ακόλουθα μαθήματα Power Query:
- Συνδυάστε δεδομένα από πολλά βιβλία εργασίας στο Excel (χρησιμοποιώντας το Power Query).
- Τρόπος Unpivot δεδομένων στο Excel χρησιμοποιώντας το Power Query (γνωστός και ως Get & Transform)
- Λήψη λίστας ονομάτων αρχείων από φακέλους και υποφακέλους (χρησιμοποιώντας το Power Query)
- Συγχώνευση πινάκων στο Excel χρησιμοποιώντας το Power Query.
- Πώς να συγκρίνετε δύο φύλλα/αρχεία Excel
