Συνδυάστε δεδομένα από πολλά βιβλία εργασίας στο Excel (χρησιμοποιώντας Power Query)

Το Power Query μπορεί να βοηθήσει πολύ όταν θέλετε να συνδυάσετε πολλά βιβλία εργασίας σε ένα μόνο βιβλίο εργασίας.

Για παράδειγμα, ας υποθέσουμε ότι έχετε τα δεδομένα πωλήσεων για διαφορετικές περιοχές (Ανατολικά, Δυτικά, Βόρεια και Νότια). Μπορείτε να συνδυάσετε αυτά τα δεδομένα από διαφορετικά βιβλία εργασίας σε ένα φύλλο εργασίας χρησιμοποιώντας το Power Query.

Εάν έχετε αυτά τα βιβλία εργασίας σε διαφορετικές τοποθεσίες/φακέλους, είναι καλή ιδέα να τα μεταφέρετε σε έναν μόνο φάκελο (ή να δημιουργήσετε ένα αντίγραφο και να το βάλετε αυτό το αντίγραφο του βιβλίου εργασίας στον ίδιο φάκελο).

Αρχικά, έχω τέσσερα βιβλία εργασίας σε ένα φάκελο (όπως φαίνεται παρακάτω).

Τώρα, σε αυτό το σεμινάριο, καλύπτω τρία σενάρια όπου μπορείτε να συνδυάσετε τα δεδομένα από διαφορετικά βιβλία εργασίας χρησιμοποιώντας το Power Query:

• Κάθε βιβλίο εργασίας περιέχει τα δεδομένα σε έναν πίνακα Excel και όλα τα ονόματα των πινάκων είναι ίδια.
• Κάθε βιβλίο εργασίας έχει τα δεδομένα με το ίδιο όνομα φύλλου εργασίας. Αυτό μπορεί να συμβαίνει όταν υπάρχει φύλλο με την ονομασία "σύνοψη" ή "δεδομένα" σε όλα τα βιβλία εργασίας και θέλετε να τα συνδυάσετε όλα αυτά.
• Κάθε βιβλίο εργασίας έχει πολλά φύλλα και πίνακες και θέλετε να συνδυάσετε συγκεκριμένους πίνακες/φύλλα. Αυτή η μέθοδος μπορεί επίσης να είναι χρήσιμη όταν θέλετε να συνδυάσετε πίνακα/φύλλα που δεν έχουν σταθερό όνομα.

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

Κάθε βιβλίο εργασίας περιέχει τα δεδομένα σε έναν πίνακα Excel με την ίδια δομή

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

Ο αριθμός των σειρών σε κάθε πίνακα μπορεί να ποικίλει.

Μην ανησυχείτε εάν ορισμένοι από τους Πίνακες του Excel έχουν επιπλέον στήλες. Μπορείτε να επιλέξετε έναν από τους Πίνακες ως πρότυπο (ή όπως το «κλειδί» όπως το ονομάζει το Power Query) και το Power Query θα το χρησιμοποιούσε για να συνδυάσει όλους τους άλλους Πίνακες Excel με αυτό.

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

Τώρα έχω τέσσερα βιβλία εργασίας σε ένα φάκελο που θέλω να συνδυάσω.

Παρακάτω είναι ένα στιγμιότυπο του πίνακα που έχω σε ένα από τα βιβλία εργασίας.

Ακολουθούν τα βήματα για να συνδυάσετε τα δεδομένα από αυτά τα βιβλία εργασίας σε ένα ενιαίο βιβλίο εργασίας (ως ενιαίος πίνακας).

1. Μεταβείτε στην καρτέλα Δεδομένα.
2. Στην ομάδα Get & Transform, κάντε κλικ στο αναπτυσσόμενο μενού New Query.
3. Τοποθετήστε τον δείκτη του ποντικιού στο «Από αρχείο» και κάντε κλικ στο «Από φάκελο».
4. Στο παράθυρο διαλόγου Φάκελος, εισαγάγετε τη διαδρομή αρχείου του φακέλου που περιέχει τα αρχεία ή κάντε κλικ στην επιλογή Αναζήτηση και εντοπίστε το φάκελο.
5. Κάντε κλικ στο OK.
6. Στο παράθυρο διαλόγου που ανοίγει, κάντε κλικ στο κουμπί συνδυασμού.
7. Κάντε κλικ στο «Συνδυάστε και φορτώστε».
8. Στο παράθυρο διαλόγου "Συνδυασμός αρχείων" που ανοίγει, επιλέξτε τον πίνακα στο αριστερό παράθυρο. Σημειώστε ότι το Power Query σας εμφανίζει τον Πίνακα από το πρώτο αρχείο. Αυτό το αρχείο θα λειτουργούσε ως πρότυπο (ή το κλειδί) για να συνδυάσει άλλα αρχεία. Το Power Query θα αναζητούσε τώρα τον «Πίνακα 1» σε άλλα βιβλία εργασίας και θα το συνδυάζει με αυτό.
9. Κάντε κλικ στο OK.

Αυτό θα φορτώσει το τελικό αποτέλεσμα (συνδυασμένα δεδομένα) στο ενεργό φύλλο εργασίας σας.

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

Σε περίπτωση που θέλετε πρώτα να επεξεργαστείτε τα δεδομένα πριν τα φορτώσετε στο Excel, στο βήμα 6, επιλέξτε «Συνδυασμός και επεξεργασία». Αυτό θα ανοίξει το τελικό αποτέλεσμα στον επεξεργαστή Power Query όπου μπορείτε να επεξεργαστείτε τα δεδομένα.

Μερικά πράγματα που πρέπει να γνωρίζετε:

• Εάν επιλέξετε έναν πίνακα Excel ως πρότυπο (στο βήμα 7), το Power Query θα χρησιμοποιήσει τα ονόματα στηλών σε αυτόν τον πίνακα για να συνδυάσει τα δεδομένα από άλλους πίνακες. Εάν άλλοι πίνακες έχουν επιπλέον στήλες, αυτές θα αγνοηθούν. Σε περίπτωση που αυτοί οι άλλοι Πίνακες δεν έχουν στήλη, η οποία βρίσκεται στον Πίνακα προτύπων, το Power Query θα θέσει απλώς «μηδενικό» για αυτό.
• Οι στήλες δεν χρειάζεται να είναι στην ίδια σειρά με το Power Query που χρησιμοποιεί κεφαλίδες στηλών για τη χαρτογράφηση στηλών.
• Αφού έχετε επιλέξει τον πίνακα 1 ως κλειδί, το Power Query θα αναζητήσει τον πίνακα 1 σε όλα τα βιβλία εργασίας και θα τα συνδυάσει όλα αυτά. Σε περίπτωση που δεν βρείτε έναν πίνακα Excel με το ίδιο όνομα (Πίνακας 1 σε αυτό το παράδειγμα), το Power Query θα σας δώσει ένα σφάλμα.

Προσθήκη νέων αρχείων στο φάκελο

Τώρα ας πάρουμε ένα λεπτό και καταλάβουμε τι κάναμε με τα παραπάνω βήματα (που μας πήραν μόνο λίγα δευτερόλεπτα).

Συνδυάσαμε τα δεδομένα από τέσσερα διαφορετικά βιβλία εργασίας σε έναν πίνακα σε λίγα δευτερόλεπτα χωρίς καν να ανοίξουμε κανένα από τα βιβλία εργασίας.

Αλλά δεν είναι μόνο αυτό.

Η πραγματική ΔΥΝΑΜΗ του Power Query είναι ότι τώρα, όταν προσθέτετε περισσότερα αρχεία στο φάκελο, δεν χρειάζεται να επαναλάβετε κανένα από αυτά τα βήματα.

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

Για παράδειγμα, στο παραπάνω παράδειγμα, αν προσθέσω ένα νέο βιβλίο εργασίας - «Mid-West.xlsx» στο φάκελο και ανανεώστε το ερώτημα, θα μου δώσει αμέσως το νέο συνδυασμένο σύνολο δεδομένων.

Δείτε πώς μπορείτε να ανανεώσετε ένα ερώτημα:

• Κάντε δεξί κλικ στον πίνακα Excel που έχετε φορτώσει στο φύλλο εργασίας και κάντε κλικ στην επιλογή Ανανέωση.
• Κάντε δεξί κλικ στο ερώτημα στο παράθυρο «Ερώτημα βιβλίου εργασίας» και κάντε κλικ στην επιλογή Ανανέωση
• Μεταβείτε στην καρτέλα Δεδομένα και κάντε κλικ στο Ανανέωση.

Κάθε βιβλίο εργασίας έχει τα δεδομένα με το ίδιο όνομα φύλλου εργασίας

Σε περίπτωση που δεν έχετε τα δεδομένα σε έναν πίνακα Excel, αλλά όλα τα ονόματα φύλλων (από τα οποία θέλετε να συνδυάσετε τα δεδομένα) είναι τα ίδια, τότε μπορείτε να χρησιμοποιήσετε τη μέθοδο που εμφανίζεται σε αυτήν την ενότητα.

Υπάρχουν μερικά πράγματα που πρέπει να είστε προσεκτικοί όταν πρόκειται μόνο για πίνακες δεδομένων και όχι για πίνακα Excel.

• Τα ονόματα του φύλλου εργασίας πρέπει να είναι τα ίδια. Αυτό θα βοηθήσει το Power Query να περάσει από τα βιβλία εργασίας σας και να συνδυάσει τα δεδομένα από τα φύλλα εργασίας που έχουν το ίδιο όνομα σε κάθε βιβλίο εργασίας.
• Το Power Query κάνει διάκριση πεζών -κεφαλαίων. Αυτό σημαίνει ότι ένα φύλλο εργασίας που ονομάζεται "δεδομένα" και "Δεδομένα" θεωρούνται διαφορετικά. Ομοίως, μια στήλη με την κεφαλίδα "Store" και μια με "store" θεωρούνται διαφορετικές.
• Αν και είναι σημαντικό να έχετε τις ίδιες κεφαλίδες στηλών, δεν είναι σημαντικό να έχετε την ίδια σειρά. Εάν η στήλη 2 στο «East.xlsx» είναι η στήλη 4 στο «West.xlsx», το Power Query θα ταιριάξει σωστά με την αντιστοίχιση των κεφαλίδων.

Τώρα ας δούμε πώς να συνδυάσουμε γρήγορα δεδομένα από διαφορετικά βιβλία εργασίας όπου το όνομα του φύλλου εργασίας είναι το ίδιο.

Σε αυτό το παράδειγμα, έχω έναν φάκελο με τέσσερα αρχεία.

Σε κάθε βιβλίο εργασίας, έχω ένα φύλλο εργασίας με το όνομα «Δεδομένα» που περιέχει τα δεδομένα στην ακόλουθη μορφή (σημειώστε ότι δεν πρόκειται για πίνακα Excel).

Ακολουθούν τα βήματα για τον συνδυασμό δεδομένων από πολλά βιβλία εργασίας σε ένα μόνο φύλλο εργασίας:

1. Μεταβείτε στην καρτέλα Δεδομένα.
2. Στην ομάδα Get & Transform, κάντε κλικ στο αναπτυσσόμενο μενού New Query.
3. Τοποθετήστε τον δείκτη του ποντικιού στο «Από αρχείο» και κάντε κλικ στο «Από φάκελο».
4. Στο παράθυρο διαλόγου Φάκελος, εισαγάγετε τη διαδρομή αρχείου του φακέλου που περιέχει τα αρχεία ή κάντε κλικ στην επιλογή Αναζήτηση και εντοπίστε το φάκελο.
5. Κάντε κλικ στο OK.
6. Στο παράθυρο διαλόγου που ανοίγει, κάντε κλικ στο κουμπί συνδυασμού.
7. Κάντε κλικ στο «Συνδυάστε και φορτώστε».
8. Στο παράθυρο διαλόγου "Συνδυάστε αρχεία" που ανοίγει, επιλέξτε "Δεδομένα" στο αριστερό παράθυρο. Σημειώστε ότι το Power Query σας εμφανίζει το όνομα του φύλλου εργασίας από το πρώτο αρχείο. Αυτό το αρχείο θα λειτουργούσε ως το κλειδί/πρότυπο για να συνδυάσει άλλα αρχεία. Το Power Query θα περάσει από κάθε βιβλίο εργασίας, θα βρει το φύλλο με το όνομα «Δεδομένα» και θα τα συνδυάσει όλα αυτά.
9. Κάντε κλικ στο OK. Τώρα το Power Query θα περάσει από κάθε βιβλίο εργασίας, θα αναζητήσει το φύλλο εργασίας που ονομάζεται «Δεδομένα» σε αυτό και, στη συνέχεια, θα συνδυάσει όλα αυτά τα σύνολα δεδομένων.

Αυτό θα φορτώσει το τελικό αποτέλεσμα (συνδυασμένα δεδομένα) στο ενεργό φύλλο εργασίας σας.

Σε περίπτωση που θέλετε πρώτα να επεξεργαστείτε τα δεδομένα πριν τα φορτώσετε στο Excel, στο βήμα 6, επιλέξτε «Συνδυασμός και επεξεργασία». Αυτό θα ανοίξει το τελικό αποτέλεσμα στον επεξεργαστή Power Query όπου μπορείτε να επεξεργαστείτε τα δεδομένα.

Κάθε βιβλίο εργασίας περιέχει τα δεδομένα με διαφορετικά ονόματα πίνακα ή ονόματα φύλλων

Μερικές φορές, ενδέχεται να μην λάβετε δομημένα και συνεπή δεδομένα (όπως Πίνακες με το ίδιο όνομα ή φύλλο εργασίας με το ίδιο όνομα).

Για παράδειγμα, ας υποθέσουμε ότι λαμβάνετε τα δεδομένα από κάποιον που δημιούργησε αυτά τα σύνολα δεδομένων αλλά ονόμασε τα φύλλα εργασίας ως East Data, West Data, North Data και South Data.

Or, το άτομο μπορεί να έχει δημιουργήσει πίνακες Excel, αλλά με διαφορετικά ονόματα.

Σε τέτοιες περιπτώσεις, μπορείτε ακόμα να χρησιμοποιήσετε το Power Query, αλλά πρέπει να το κάνετε με μερικά επιπλέον βήματα.

1. Μεταβείτε στην καρτέλα Δεδομένα.
2. Στην ομάδα Get & Transform, κάντε κλικ στο αναπτυσσόμενο μενού New Query.
3. Τοποθετήστε τον δείκτη του ποντικιού στο "Από αρχείο" και κάντε κλικ στο "Από φάκελο".
4. Στο παράθυρο διαλόγου Φάκελος, εισαγάγετε τη διαδρομή αρχείου του φακέλου που περιέχει τα αρχεία ή κάντε κλικ στην επιλογή Αναζήτηση και εντοπίστε το φάκελο.
5. Κάντε κλικ στο OK.
6. Στο παράθυρο διαλόγου που ανοίγει, κάντε κλικ στο κουμπί Επεξεργασία. Αυτό θα ανοίξει τον επεξεργαστή Power Query όπου θα δείτε τις λεπτομέρειες όλων των αρχείων στο φάκελο.
7. Κρατήστε πατημένο το πλήκτρο ελέγχου και επιλέξτε τις στήλες "Περιεχόμενο" και "Όνομα", κάντε δεξί κλικ και επιλέξτε "Κατάργηση άλλων στηλών". Αυτό θα καταργήσει όλες τις άλλες στήλες εκτός από τις επιλεγμένες στήλες.
8. Στην κορδέλα επεξεργαστή ερωτήματος, κάντε κλικ στην επιλογή "Προσθήκη στήλης" και, στη συνέχεια, κάντε κλικ στην επιλογή "Προσαρμοσμένη στήλη".
9. Στο παράθυρο διαλόγου Προσθήκη προσαρμοσμένης στήλης, ονομάστε τη νέα στήλη ως "Εισαγωγή δεδομένων" και χρησιμοποιήστε τον ακόλουθο τύπο = Excel. Βιβλίο εργασίας ([CONTENT]). Σημειώστε ότι αυτός ο τύπος έχει διάκριση πεζών -κεφαλαίων και πρέπει να τον εισαγάγετε ακριβώς με τον τρόπο που έχω δείξει εδώ.
10. Τώρα θα δείτε μια νέα στήλη που έχει γραμμένο τον πίνακα. Τώρα επιτρέψτε μου να εξηγήσω τι συνέβη εδώ. Παρέχετε το Power Query τα ονόματα των βιβλίων εργασίας και το Power Query έχει ανακτήσει τα αντικείμενα όπως φύλλα εργασίας, πίνακες και εύρος ονομάτων από κάθε βιβλίο εργασίας (το οποίο βρίσκεται στο κελί του πίνακα μέχρι τώρα). Μπορείτε να κάνετε κλικ στο λευκό διάστημα δίπλα στον πίνακα κειμένου και θα δείτε τις πληροφορίες στο κάτω μέρος. Σε αυτήν την περίπτωση, δεδομένου ότι έχουμε μόνο έναν πίνακα και ένα φύλλο εργασίας σε κάθε βιβλίο εργασίας, μπορείτε να δείτε μόνο δύο σειρές.
11. Κάντε κλικ στο εικονίδιο διπλού βέλους στο επάνω μέρος της στήλης "Εισαγωγή δεδομένων".
12. Στο πλαίσιο δεδομένων της στήλης που ανοίγει, καταργήστε την επιλογή «Χρήση της αρχικής στήλης ως πρόθεμα» και, στη συνέχεια, κάντε κλικ στο κουμπί OK.
13. Τώρα θα δείτε έναν διευρυμένο πίνακα όπου βλέπετε μία γραμμή για κάθε αντικείμενο στον πίνακα. Σε αυτήν την περίπτωση, για κάθε βιβλίο εργασίας, το αντικείμενο φύλλου και το αντικείμενο πίνακα παρατίθενται ξεχωριστά.
14. Στη στήλη Kind, φιλτράρετε τη λίστα για να εμφανιστεί μόνο ο πίνακας.
15. Κρατήστε πατημένο το πλήκτρο ελέγχου και επιλέξτε τη στήλη Όνομα και δεδομένα. Τώρα, κάντε δεξί κλικ και αφαιρέστε όλες τις άλλες στήλες.
16. Στη στήλη Δεδομένα, κάντε κλικ στο εικονίδιο διπλού βέλους στην επάνω δεξιά γωνία της κεφαλίδας δεδομένων.
17. Στο πλαίσιο δεδομένων στήλης που ανοίγει, κάντε κλικ στο κουμπί OK. Αυτό θα συνδυάσει τα δεδομένα σε όλους τους πίνακες και θα εμφανιστεί στο Power Query.
18. Τώρα μπορείτε να κάνετε οποιαδήποτε μετατροπή που χρειάζεστε και, στη συνέχεια, μεταβείτε στην καρτέλα Αρχική σελίδα και κάντε κλικ στο Κλείσιμο & φόρτωση.

Τώρα επιτρέψτε μου να προσπαθήσω και να εξηγήσω γρήγορα τι κάναμε εδώ. Δεδομένου ότι δεν υπήρχε συνέπεια στα ονόματα των φύλλων ή στα ονόματα των πινάκων, χρησιμοποιήσαμε τον τύπο = Excel.Wbookbook για την ανάκτηση όλων των αντικειμένων των βιβλίων εργασίας στο Power Query. Αυτά τα αντικείμενα μπορούν να περιλαμβάνουν φύλλα, πίνακες και ονόματα εύρους. Μόλις είχαμε όλα τα αντικείμενα από όλα τα αρχεία, τα φιλτράραμε για να λάβουμε υπόψη μόνο τους Πίνακες Excel. Στη συνέχεια, επεκτείναμε τα δεδομένα στους πίνακες και τα συνδυάσαμε όλα αυτά.

Σε αυτό το παράδειγμα, φιλτράραμε τα δεδομένα για να χρησιμοποιήσουμε μόνο Πίνακες Excel (στο Βήμα 13). Σε περίπτωση που θέλετε να συνδυάσετε φύλλα και όχι πίνακες, μπορείτε να φιλτράρετε φύλλα.

Σημείωση - αυτή η τεχνική θα σας δώσει τα συνδυασμένα δεδομένα ακόμη και όταν υπάρχει αναντιστοιχία στα ονόματα στηλών. Για παράδειγμα, εάν στο East.xlsx, έχετε μια στήλη που έχει γραφτεί λάθος, θα καταλήξετε σε 5 στήλες. Το Power Query θα γεμίσει δεδομένα σε στήλες αν τα βρει και αν δεν μπορεί να βρει μια στήλη, θα αναφέρει την τιμή ως "μηδενική".

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

Τώρα αν έχετε περισσότερα βιβλία εργασίας από τα οποία πρέπει να συνδυάσετε δεδομένα, απλώς αντιγράψτε και επικολλήστε τα στο φάκελο και ανανεώστε το Power Query