Περιεχόμενα
Διατύπωση του προβλήματος
Ως δεδομένα εισόδου, έχουμε ένα αρχείο Excel, όπου ένα από τα φύλλα περιέχει πολλούς πίνακες με δεδομένα πωλήσεων της ακόλουθης μορφής:
Σημειώστε ότι:
- Πίνακες διαφορετικών μεγεθών και με διαφορετικά σύνολα προϊόντων και περιοχών σε σειρές και στήλες χωρίς καμία ταξινόμηση.
- Μεταξύ των πινάκων μπορούν να εισαχθούν κενές γραμμές.
- Ο αριθμός των τραπεζιών μπορεί να είναι οποιοσδήποτε.
Δύο σημαντικές υποθέσεις. Θεωρείται ότι:
- Πάνω από κάθε πίνακα, στην πρώτη στήλη, υπάρχει το όνομα του διαχειριστή του οποίου οι πωλήσεις απεικονίζει ο πίνακας (Ivanov, Petrov, Sidorov, κ.λπ.)
- Τα ονόματα των εμπορευμάτων και των περιοχών σε όλους τους πίνακες γράφονται με τον ίδιο τρόπο – με ακρίβεια πεζών-κεφαλαίων.
Ο απώτερος στόχος είναι η συλλογή δεδομένων από όλους τους πίνακες σε έναν επίπεδο κανονικοποιημένο πίνακα, βολικό για μεταγενέστερη ανάλυση και δημιουργία μιας σύνοψης, δηλαδή σε αυτόν:
Βήμα 1. Συνδεθείτε στο αρχείο
Ας δημιουργήσουμε ένα νέο κενό αρχείο Excel και ας το επιλέξουμε στην καρτέλα ημερομηνία εντολή Λήψη δεδομένων – Από αρχείο – Από βιβλίο (Δεδομένα — Από αρχείο — Από βιβλίο εργασίας). Καθορίστε τη θέση του αρχείου προέλευσης με δεδομένα πωλήσεων και, στη συνέχεια, στο παράθυρο πλοήγησης επιλέξτε το φύλλο που χρειαζόμαστε και κάντε κλικ στο κουμπί Μετατροπή Δεδομένων (Μετατροπή δεδομένων):
Ως αποτέλεσμα, όλα τα δεδομένα από αυτό θα πρέπει να φορτωθούν στο πρόγραμμα επεξεργασίας Power Query:
Βήμα 2. Καθαρίστε τα σκουπίδια
Διαγραφή βημάτων που δημιουργούνται αυτόματα τροποποιημένου τύπου (Αλλαγμένος τύπος) и Ανυψωμένες κεφαλίδες (Κεφαλίδες που προωθούνται) και απαλλαγείτε από κενές γραμμές και γραμμές με σύνολα χρησιμοποιώντας ένα φίλτρο μηδέν и ΣΥΝΟΛΟ από την πρώτη στήλη. Ως αποτέλεσμα, έχουμε την ακόλουθη εικόνα:
Βήμα 3. Προσθήκη διαχειριστών
Για να καταλάβουμε αργότερα πού είναι οι πωλήσεις, είναι απαραίτητο να προσθέσουμε μια στήλη στον πίνακα μας, όπου σε κάθε σειρά θα υπάρχει ένα αντίστοιχο επώνυμο. Για αυτό:
1. Ας προσθέσουμε μια βοηθητική στήλη με αριθμούς γραμμών χρησιμοποιώντας την εντολή Προσθήκη στήλης – Στήλη ευρετηρίου – Από 0 (Προσθήκη στήλης — Στήλη ευρετηρίου — Από 0).
2. Προσθέστε μια στήλη με έναν τύπο με την εντολή Προσθήκη στήλης – Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσαρμοσμένη στήλη) και εισάγετε εκεί την ακόλουθη κατασκευή:
Η λογική αυτού του τύπου είναι απλή - εάν η τιμή του επόμενου κελιού στην πρώτη στήλη είναι "Προϊόν", τότε αυτό σημαίνει ότι έχουμε πέσει στην αρχή ενός νέου πίνακα, οπότε εμφανίζουμε την τιμή του προηγούμενου κελιού με το όνομα του διαχειριστή. Διαφορετικά, δεν εμφανίζουμε τίποτα, δηλαδή null.
Για να λάβουμε το γονικό κελί με το επώνυμο, πρώτα αναφερόμαστε στον πίνακα από το προηγούμενο βήμα #"Προστέθηκε ευρετήριο"και, στη συνέχεια, καθορίστε το όνομα της στήλης που χρειαζόμαστε [Στήλη 1] σε αγκύλες και τον αριθμό κελιού σε αυτή τη στήλη σε σγουρές αγκύλες. Ο αριθμός κελιού θα είναι ένα μικρότερος από τον τρέχοντα, τον οποίο παίρνουμε από τη στήλη Περιεχόμενα, Αντίστοιχα.
3. Μένει να συμπληρώσουμε τα κενά κελιά με μηδέν ονόματα από ανώτερα κελιά με την εντολή Μετασχηματισμός – Συμπλήρωση – Κάτω (Μεταμόρφωση — Συμπλήρωση — Κάτω) και διαγράψτε τη στήλη που δεν χρειάζεται πλέον με δείκτες και σειρές με επώνυμα στην πρώτη στήλη. Ως αποτέλεσμα, παίρνουμε:
Βήμα 4. Ομαδοποίηση σε ξεχωριστούς πίνακες κατά διαχειριστές
Το επόμενο βήμα είναι να ομαδοποιήσετε τις σειρές για κάθε διαχειριστή σε ξεχωριστούς πίνακες. Για να το κάνετε αυτό, στην καρτέλα Μετασχηματισμός, χρησιμοποιήστε την εντολή Group by (Transform – Group By) και στο παράθυρο που ανοίγει, επιλέξτε τη στήλη Manager και τη λειτουργία Όλες οι γραμμές (Όλες οι γραμμές) για να συλλέξετε απλά δεδομένα χωρίς να εφαρμόσετε καμία συνάρτηση συγκέντρωσης τους (άθροισμα, μέσος όρος κ.λπ.). Π.):
Ως αποτέλεσμα, λαμβάνουμε ξεχωριστούς πίνακες για κάθε διαχειριστή:
Βήμα 5: Μεταμόρφωση ένθετων πινάκων
Τώρα δίνουμε τους πίνακες που βρίσκονται σε κάθε κελί της στήλης που προκύπτει Ολα τα δεδομένα σε αξιοπρεπή μορφή.
Αρχικά, διαγράψτε μια στήλη που δεν χρειάζεται πλέον σε κάθε πίνακα Manager. Χρησιμοποιούμε ξανά Προσαρμοσμένη στήλη αυτί Μεταμόρφωση (Μετατροπή — Προσαρμοσμένη στήλη) και τον ακόλουθο τύπο:
Στη συνέχεια, με μια άλλη υπολογισμένη στήλη, ανεβάζουμε την πρώτη σειρά σε κάθε πίνακα στις επικεφαλίδες:
Και τέλος, εκτελούμε τον κύριο μετασχηματισμό – ξεδιπλώνοντας κάθε πίνακα χρησιμοποιώντας τη συνάρτηση M Table.UnpivotOtherColumns:
Τα ονόματα των περιοχών από την κεφαλίδα θα μεταβούν σε μια νέα στήλη και θα λάβουμε έναν πιο στενό, αλλά ταυτόχρονα, έναν πιο κανονικό πίνακα. Κενά κελιά με μηδέν αγνοούνται.
Για να απαλλαγούμε από τις περιττές ενδιάμεσες στήλες, έχουμε:
Βήμα 6 Αναπτύξτε τους ένθετους πίνακες
Απομένει να επεκταθούν όλοι οι κανονικοποιημένοι ένθετοι πίνακες σε μια ενιαία λίστα χρησιμοποιώντας το κουμπί με τα διπλά βέλη στην κεφαλίδα της στήλης:
… και επιτέλους παίρνουμε αυτό που θέλαμε:
Μπορείτε να εξαγάγετε τον πίνακα που προκύπτει πίσω στο Excel χρησιμοποιώντας την εντολή Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση σε… (Αρχική — Κλείσιμο&Φόρτωση — Κλείσιμο&Φόρτωση σε…).
- Δημιουργήστε πίνακες με διαφορετικές κεφαλίδες από πολλά βιβλία
- Συλλογή δεδομένων από όλα τα αρχεία σε έναν δεδομένο φάκελο
- Συλλογή δεδομένων από όλα τα φύλλα του βιβλίου σε έναν πίνακα