Δημιουργήστε πίνακες με διαφορετικές κεφαλίδες από πολλά βιβλία

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

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

Στο εσωτερικό, αυτά τα αρχεία μοιάζουν με αυτό:

Εν:

• Το φύλλο δεδομένων που χρειαζόμαστε καλείται πάντα Φωτογραφίες, αλλά μπορεί να βρίσκεται οπουδήποτε στο βιβλίο εργασίας.
  
• Πέρα από το σεντόνι Φωτογραφίες Κάθε βιβλίο μπορεί να έχει άλλα φύλλα.
• Οι πίνακες με δεδομένα έχουν διαφορετικό αριθμό σειρών και μπορεί να ξεκινούν με διαφορετική σειρά στο φύλλο εργασίας.
• Τα ονόματα των ίδιων στηλών σε διαφορετικούς πίνακες μπορεί να διαφέρουν (για παράδειγμα, Ποσότητα = Ποσότητα = Ποσότητα).
• Οι στήλες στους πίνακες μπορούν να ταξινομηθούν με διαφορετική σειρά.

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

Βήμα 1. Προετοιμασία ενός καταλόγου ονομάτων στηλών

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

Μετατρέπουμε αυτήν τη λίστα σε έναν δυναμικό «έξυπνο» πίνακα χρησιμοποιώντας το κουμπί Μορφοποίηση ως πίνακα στην καρτέλα Αρχική (Αρχική σελίδα — Μορφοποίηση ως πίνακα) ή συντόμευση πληκτρολογίου Ctrl+T και φορτώστε το στο Power Query με την εντολή Δεδομένα – Από πίνακα/Εύρος (Δεδομένα — Από πίνακα/εύρος). Σε πρόσφατες εκδόσεις του Excel, έχει μετονομαστεί σε Με φύλλα (Από φύλλο).

Στο παράθυρο του προγράμματος επεξεργασίας ερωτημάτων Power Query, παραδοσιακά διαγράφουμε το βήμα Άλλαξε τύπο και προσθέστε ένα νέο βήμα αντί για αυτό κάνοντας κλικ στο κουμπί f_xστη γραμμή τύπων (αν δεν είναι ορατή, τότε μπορείτε να την ενεργοποιήσετε στην καρτέλα Βαθμολογία Κριτικής) και εισαγάγετε τον τύπο εκεί στην ενσωματωμένη γλώσσα Power Query M:

=Table.ToRows(Πηγή)

Αυτή η εντολή θα μετατρέψει αυτήν που φορτώθηκε στο προηγούμενο βήμα Πηγή πίνακας αναφοράς σε μια λίστα που αποτελείται από ένθετες λίστες (List), καθεμία από τις οποίες, με τη σειρά της, είναι ένα ζεύγος τιμών Ήταν-έγινε από μια γραμμή:

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

Αφού ολοκληρώσετε τη μετατροπή, επιλέξτε τις εντολές Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση σε… και είδος εισαγωγής Απλώς δημιουργήστε μια σύνδεση (Αρχική — Κλείσιμο&Φόρτωση — Κλείσιμο&Φόρτωση σε… — Δημιουργία μόνο σύνδεσης) και επιστρέψτε στο Excel.

Βήμα 2. Φορτώνουμε τα πάντα από όλα τα αρχεία ως έχουν

Τώρα ας φορτώσουμε τα περιεχόμενα όλων των αρχείων μας από το φάκελο – προς το παρόν, όπως είναι. Επιλογή ομάδων Δεδομένα – Λήψη δεδομένων – Από αρχείο – Από φάκελο (Δεδομένα — Λήψη δεδομένων — Από αρχείο — Από φάκελο) και μετά ο φάκελος όπου βρίσκονται τα βιβλία πηγής μας.

Στο παράθυρο προεπισκόπησης, κάντε κλικ Μετατρέπω (Μεταμορφώνω) or Αλλαγή (Επεξεργασία):

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

Power Query στο παράδειγμα του πρώτου αρχείου (Vostok.xlsx) θα μας ρωτήσει το όνομα του φύλλου που θέλουμε να πάρουμε από κάθε βιβλίο εργασίας – επιλέξτε Φωτογραφίες και πατήστε ΟΚ:

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

1. Το Power Query θα πάρει το πρώτο αρχείο από το φάκελο (θα το έχουμε Vostok.xlsx — δείτε Παράδειγμα αρχείου) ως παράδειγμα και εισάγει το περιεχόμενό του δημιουργώντας ένα ερώτημα Μετατροπή δείγματος αρχείου. Αυτό το ερώτημα θα έχει μερικά απλά βήματα όπως Πηγή (πρόσβαση σε αρχείο) Πλοήγηση (επιλογή φύλλου) και ενδεχομένως ανεβάζοντας τους τίτλους. Αυτό το αίτημα μπορεί να φορτώσει δεδομένα μόνο από ένα συγκεκριμένο αρχείο Vostok.xlsx.
   
2. Με βάση αυτό το αίτημα, θα δημιουργηθεί η συνάρτηση που σχετίζεται με αυτό Μετατροπή αρχείου (υποδεικνύεται από ένα χαρακτηριστικό εικονίδιο f_x), όπου το αρχείο προέλευσης δεν θα είναι πλέον μια σταθερά, αλλά μια μεταβλητή τιμή - μια παράμετρος. Έτσι, αυτή η συνάρτηση μπορεί να εξαγάγει δεδομένα από οποιοδήποτε βιβλίο που εισάγουμε σε αυτήν ως όρισμα.
   
3. Η συνάρτηση θα εφαρμοστεί με τη σειρά της σε κάθε αρχείο (Δυαδικό) από τη στήλη Περιεχόμενο – το step είναι υπεύθυνο για αυτό Κλήση προσαρμοσμένης λειτουργίας στο ερώτημά μας που προσθέτει μια στήλη στη λίστα των αρχείων Μετατροπή αρχείου με αποτελέσματα εισαγωγής από κάθε βιβλίο εργασίας:

   

4. Οι επιπλέον στήλες αφαιρούνται.
5. Τα περιεχόμενα των ένθετων πινάκων επεκτείνονται (βήμα Εκτεταμένη στήλη πίνακα) – και βλέπουμε τα τελικά αποτελέσματα της συλλογής δεδομένων από όλα τα βιβλία:

   
   

Βήμα 3. Τρίψιμο

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

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

Μπορείτε να διορθώσετε όλα αυτά τα προβλήματα πολύ εύκολα – απλώς τροποποιήστε το ερώτημα Convert Sample File. Όλες οι προσαρμογές που κάνουμε σε αυτό θα εμπίπτουν αυτόματα στη σχετική συνάρτηση Μετατροπής αρχείου, πράγμα που σημαίνει ότι θα χρησιμοποιηθούν αργότερα κατά την εισαγωγή δεδομένων από κάθε αρχείο.

Ανοίγοντας ένα αίτημα Μετατροπή δείγματος αρχείου, προσθέστε βήματα για να φιλτράρετε περιττές σειρές (για παράδειγμα, ανά στήλη Column2) και ανεβάζοντας τις επικεφαλίδες με το κουμπί Χρησιμοποιήστε την πρώτη γραμμή ως κεφαλίδες (Χρησιμοποιήστε την πρώτη σειρά ως κεφαλίδες). Το τραπέζι θα φαίνεται πολύ καλύτερο.

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

= Table.RenameColumns(#"Elevated Headers", Headers, MissingField.Ignore)

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

Στην πραγματικότητα, αυτό είναι όλο.

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

• Τι είναι το Power Query, το Power Pivot, το Power BI και γιατί τα χρειάζεται ένας χρήστης του Excel
  
• Συλλογή δεδομένων από όλα τα αρχεία σε έναν δεδομένο φάκελο
• Συλλογή δεδομένων από όλα τα φύλλα του βιβλίου σε έναν πίνακα