Συναρμολόγηση πινάκων από διαφορετικά αρχεία Excel με το Power Query

Διατύπωση του προβλήματος

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

Ας υποθέσουμε ότι έχουμε τον ακόλουθο φάκελο, ο οποίος περιέχει πολλά αρχεία με δεδομένα από πόλεις υποκαταστημάτων:

Ο αριθμός των αρχείων δεν έχει σημασία και μπορεί να αλλάξει στο μέλλον. Κάθε αρχείο έχει ένα φύλλο με όνομα ΕΚΠΤΩΣΕΙΣόπου βρίσκεται ο πίνακας δεδομένων:

Ο αριθμός των γραμμών (παραγγελιών) στους πίνακες, φυσικά, είναι διαφορετικός, αλλά το σύνολο των στηλών είναι τυπικό παντού.

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

Επιλέγουμε όπλα

Για τη λύση, χρειαζόμαστε την πιο πρόσφατη έκδοση του Excel 2016 (η απαραίτητη λειτουργικότητα είναι ήδη ενσωματωμένη σε αυτό από προεπιλογή) ή προηγούμενες εκδόσεις του Excel 2010-2013 με εγκατεστημένο το δωρεάν πρόσθετο Ερώτημα ισχύος από τη Microsoft (κατεβάστε το εδώ). Το Power Query είναι ένα εξαιρετικά ευέλικτο και εξαιρετικά ισχυρό εργαλείο για τη φόρτωση δεδομένων στο Excel από τον εξωτερικό κόσμο, και στη συνέχεια την αφαίρεση και την επεξεργασία τους. Το Power Query υποστηρίζει σχεδόν όλες τις υπάρχουσες πηγές δεδομένων – από αρχεία κειμένου έως SQL, ακόμη και Facebook 🙂

Εάν δεν έχετε Excel 2013 ή 2016, τότε δεν μπορείτε να διαβάσετε περαιτέρω (απλά αστειεύομαι). Σε παλαιότερες εκδόσεις του Excel, μια τέτοια εργασία μπορεί να επιτευχθεί μόνο με τον προγραμματισμό μιας μακροεντολής στη Visual Basic (πράγμα πολύ δύσκολο για αρχάριους) ή με μονότονη μη αυτόματη αντιγραφή (η οποία διαρκεί πολύ και δημιουργεί σφάλματα).

Βήμα 1. Εισαγάγετε ένα αρχείο ως δείγμα

Πρώτον, ας εισαγάγουμε δεδομένα από ένα βιβλίο εργασίας ως παράδειγμα, έτσι ώστε το Excel "να πάρει την ιδέα". Για να το κάνετε αυτό, δημιουργήστε ένα νέο κενό βιβλίο εργασίας και…

• εάν έχετε Excel 2016, ανοίξτε την καρτέλα ημερομηνία και στη συνέχεια Δημιουργία ερωτήματος – Από αρχείο – Από βιβλίο (Δεδομένα — Νέο ερώτημα- Από αρχείο — Από Excel)
• εάν έχετε το Excel 2010-2013 με εγκατεστημένο το πρόσθετο Power Query, ανοίξτε την καρτέλα Ερώτημα ισχύος και επιλέξτε σε αυτό Από αρχείο – Από βιβλίο (Από αρχείο — Από Excel)

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

Εάν κάνετε κλικ στο κουμπί στην κάτω δεξιά γωνία αυτού του παραθύρου Λήψη (Φορτώνω), τότε ο πίνακας θα εισαχθεί αμέσως στο φύλλο στην αρχική του μορφή. Για ένα μόνο αρχείο, αυτό είναι καλό, αλλά πρέπει να φορτώσουμε πολλά τέτοια αρχεία, οπότε θα πάμε λίγο διαφορετικά και θα κάνουμε κλικ στο κουμπί Διόρθωση (Επεξεργασία). Μετά από αυτό, το πρόγραμμα επεξεργασίας ερωτημάτων Power Query θα πρέπει να εμφανίζεται σε ξεχωριστό παράθυρο με τα δεδομένα μας από το βιβλίο:

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

• φιλτράρετε περιττά δεδομένα, κενές γραμμές, γραμμές με σφάλματα
• ταξινόμηση δεδομένων κατά μία ή περισσότερες στήλες
• απαλλαγείτε από την επανάληψη
• διαιρέστε το αυτοκόλλητο κείμενο με στήλες (κατά οριοθέτες, αριθμό χαρακτήρων κ.λπ.)
• βάλτε το κείμενο σε σειρά (αφαιρέστε επιπλέον κενά, σωστή πεζογραφία κ.λπ.)
• μετατρέψτε τους τύπους δεδομένων με κάθε δυνατό τρόπο (μετατρέψτε αριθμούς όπως το κείμενο σε κανονικούς αριθμούς και αντίστροφα)
• μεταφέρετε (περιστρέψτε) πίνακες και επεκτείνετε τους δισδιάστατους διασταυρούμενους πίνακες σε επίπεδους
• προσθέστε επιπλέον στήλες στον πίνακα και χρησιμοποιήστε τύπους και συναρτήσεις σε αυτές χρησιμοποιώντας τη γλώσσα M που είναι ενσωματωμένη στο Power Query.
• ...

Για παράδειγμα, ας προσθέσουμε μια στήλη με το όνομα κειμένου του μήνα στον πίνακά μας, έτσι ώστε αργότερα να είναι ευκολότερη η δημιουργία αναφορών συγκεντρωτικών πινάκων. Για να το κάνετε αυτό, κάντε δεξί κλικ στην επικεφαλίδα της στήλης Ραντεβού και επιλέξτε την εντολή Διπλή στήλη (Διπλότυπη στήλη)και, στη συνέχεια, κάντε δεξί κλικ στην κεφαλίδα της διπλής στήλης που εμφανίζεται και επιλέξτε Εντολές Μετασχηματισμός – Όνομα μήνα – μήνα:

Θα πρέπει να σχηματιστεί μια νέα στήλη με τα ονόματα κειμένου του μήνα για κάθε σειρά. Κάνοντας διπλό κλικ σε μια επικεφαλίδα στήλης, μπορείτε να την μετονομάσετε από Ημερομηνία αντιγραφής σε ένα πιο άνετο Μήνας, π.χ.

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

Μπορείτε να εξαιρέσετε γραμμές με σφάλματα ή κενές γραμμές, καθώς και περιττούς διαχειριστές ή πελάτες, χρησιμοποιώντας ένα απλό φίλτρο:

Επιπλέον, όλοι οι μετασχηματισμοί που εκτελούνται είναι σταθεροποιημένοι στο δεξιό πλαίσιο, όπου μπορούν πάντα να μετακινηθούν προς τα πίσω (σταυρός) ή να αλλάξουν οι παραμέτρους τους (γρανάζι):

Ελαφρύ και κομψό, έτσι δεν είναι;

Βήμα 2. Ας μετατρέψουμε το αίτημά μας σε συνάρτηση

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

Στον Επεξεργαστή ερωτημάτων, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο κουμπί Advanced Editor (Προβολή — Σύνθετη επεξεργασία). Θα πρέπει να ανοίξει ένα παράθυρο όπου όλες οι προηγούμενες ενέργειες μας θα γραφτούν με τη μορφή κώδικα στη γλώσσα M. Λάβετε υπόψη ότι η διαδρομή προς το αρχείο που εισαγάγαμε για το παράδειγμα είναι κωδικοποιημένη στον κώδικα:

Τώρα ας κάνουμε μερικές προσαρμογές:

Η σημασία τους είναι απλή: η πρώτη γραμμή (διαδρομή αρχείου)=> μετατρέπει τη διαδικασία μας σε συνάρτηση με όρισμα διαδρομή αρχείου, και παρακάτω αλλάζουμε τη σταθερή διαδρομή στην τιμή αυτής της μεταβλητής. 

Ολα. Κάντε κλικ στο φινίρισμα και πρέπει να δείτε αυτό:

Μην φοβάστε ότι τα δεδομένα έχουν εξαφανιστεί – στην πραγματικότητα, όλα είναι εντάξει, όλα πρέπει να φαίνονται έτσι 🙂 Δημιουργήσαμε με επιτυχία την προσαρμοσμένη λειτουργία μας, όπου ολόκληρος ο αλγόριθμος για την εισαγωγή και την επεξεργασία δεδομένων απομνημονεύεται χωρίς να συνδέεται με ένα συγκεκριμένο αρχείο . Απομένει να του δώσουμε ένα πιο κατανοητό όνομα (για παράδειγμα getData) στον πίνακα στα δεξιά στο πεδίο Όνομα και μπορείς να θερίσεις Αρχική σελίδα — Κλείσιμο και λήψη (Αρχική σελίδα — Κλείσιμο και φόρτωση). Λάβετε υπόψη ότι η διαδρομή προς το αρχείο που εισαγάγαμε για το παράδειγμα είναι κωδικοποιημένη στον κώδικα. Θα επιστρέψετε στο κύριο παράθυρο του Microsoft Excel, αλλά ένας πίνακας με τη δημιουργία σύνδεσης στη λειτουργία μας θα πρέπει να εμφανιστεί στα δεξιά:

Βήμα 3. Συλλογή όλων των αρχείων

Όλο το πιο δύσκολο κομμάτι είναι πίσω, το ευχάριστο και εύκολο κομμάτι παραμένει. Μεταβείτε στην καρτέλα Δεδομένα – Δημιουργία ερωτήματος – Από αρχείο – Από φάκελο (Δεδομένα — Νέο ερώτημα — Από αρχείο — Από φάκελο) ή, εάν έχετε Excel 2010-2013, παρόμοια με την καρτέλα Ερώτημα ισχύος. Στο παράθυρο που εμφανίζεται, καθορίστε το φάκελο στον οποίο βρίσκονται όλα τα αρχεία της πόλης προέλευσης και κάντε κλικ OK. Το επόμενο βήμα θα πρέπει να ανοίξει ένα παράθυρο όπου θα εμφανίζονται όλα τα αρχεία Excel που βρίσκονται σε αυτόν τον φάκελο (και οι υποφακέλους του) και οι λεπτομέρειες για καθένα από αυτά:

Πατήστε Αλλαγή (Επεξεργασία) και πάλι μπαίνουμε στο γνωστό παράθυρο του προγράμματος επεξεργασίας ερωτημάτων.

Τώρα πρέπει να προσθέσουμε μια άλλη στήλη στον πίνακα μας με τη συνάρτηση που δημιουργήσαμε, η οποία θα «τραβάει» τα δεδομένα από κάθε αρχείο. Για να το κάνετε αυτό, μεταβείτε στην καρτέλα Προσθήκη στήλης – Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσθήκη προσαρμοσμένης στήλης) και στο παράθυρο που εμφανίζεται εισάγουμε τη συνάρτηση μας getData, προσδιορίζοντας για αυτό ως όρισμα την πλήρη διαδρομή σε κάθε αρχείο:

Μετά το κλικ OK η στήλη που δημιουργήθηκε πρέπει να προστεθεί στον πίνακα μας στα δεξιά.

Τώρα ας διαγράψουμε όλες τις περιττές στήλες (όπως στο Excel, χρησιμοποιώντας το δεξί κουμπί του ποντικιού – ΑΦΑΙΡΕΣΗ), αφήνοντας μόνο την προστιθέμενη στήλη και τη στήλη με το όνομα αρχείου, γιατί αυτό το όνομα (ακριβέστερα, η πόλη) θα είναι χρήσιμο να υπάρχει στα συνολικά δεδομένα για κάθε σειρά.

Και τώρα το "wow moment" - κάντε κλικ στο εικονίδιο με τα δικά του βέλη στην επάνω δεξιά γωνία της στήλης που προστέθηκε με τη συνάρτησή μας:

… καταργήστε την επιλογή Χρησιμοποιήστε το αρχικό όνομα στήλης ως πρόθεμα (Χρησιμοποιήστε το αρχικό όνομα στήλης ως πρόθεμα)και κάντε κλικ στο κουμπί OK. Και η συνάρτησή μας θα φορτώσει και θα επεξεργαστεί τα δεδομένα από κάθε αρχείο, ακολουθώντας τον καταγεγραμμένο αλγόριθμο και συλλέγοντας τα πάντα σε έναν κοινό πίνακα:

Για απόλυτη ομορφιά, μπορείτε επίσης να αφαιρέσετε τις επεκτάσεις .xlsx από την πρώτη στήλη με ονόματα αρχείων – με τυπική αντικατάσταση με το “nothing” (κάντε δεξί κλικ στην κεφαλίδα της στήλης – Υποκατάστατο) και μετονομάστε αυτήν τη στήλη σε Πόλη. Και επίσης διορθώστε τη μορφή δεδομένων στη στήλη με την ημερομηνία.

Ολα! Κάντε κλικ στο Αρχική σελίδα – Κλείσιμο και φόρτωση (Αρχική — Κλείσιμο & Φόρτωση). Όλα τα δεδομένα που συλλέγονται από το ερώτημα για όλες τις πόλεις θα μεταφορτωθούν στο τρέχον φύλλο Excel με τη μορφή "έξυπνου πίνακα":

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

Στο μέλλον, με οποιεσδήποτε αλλαγές στο φάκελο (προσθήκη ή αφαίρεση πόλεων) ή σε αρχεία (αλλαγή του αριθμού των γραμμών), θα αρκεί να κάνετε δεξί κλικ απευθείας στον πίνακα ή στο ερώτημα στο δεξιό πλαίσιο και να επιλέξετε το εντολή Ενημέρωση & Αποθήκευση (Φρεσκάρω) – Το Power Query θα «αναδημιουργήσει» όλα τα δεδομένα ξανά σε λίγα δευτερόλεπτα.

PS

Τροπολογία. Μετά τις ενημερώσεις του Ιανουαρίου 2017, το Power Query έμαθε πώς να συλλέγει βιβλία εργασίας του Excel από μόνο του, δηλαδή δεν χρειάζεται πλέον να δημιουργεί ξεχωριστή λειτουργία – γίνεται αυτόματα. Έτσι, το δεύτερο βήμα από αυτό το άρθρο δεν χρειάζεται πλέον και η όλη διαδικασία γίνεται αισθητά πιο απλή:

1. Επιλέξτε Δημιουργία Αίτησης – Από Αρχείο – Από Φάκελο – Επιλέξτε Φάκελο – ΟΚ
2. Αφού εμφανιστεί η λίστα των αρχείων, πατήστε Αλλαγή
3. Στο παράθυρο Επεξεργαστής ερωτημάτων, αναπτύξτε τη στήλη Δυαδικό με ένα διπλό βέλος και επιλέξτε το όνομα του φύλλου που θα ληφθεί από κάθε αρχείο

Και αυτό είναι όλο! Τραγούδι!

• Επανασχεδιασμός της εγκάρσιας γλωττίδας σε επίπεδο κατάλληλο για την κατασκευή περιστρεφόμενων τραπεζιών
• Δημιουργία ενός κινούμενου γραφήματος με συννεφάκια στο Power View
• Μακροεντολή για τη συγκέντρωση φύλλων από διαφορετικά αρχεία Excel σε ένα