Με το Power Query, η εργασία με δεδομένα που διασκορπίζονται σε φύλλα εργασίας ή ακόμη και σε βιβλία εργασίας έχει γίνει ευκολότερη.
Ένα από τα πράγματα στα οποία το Power Query μπορεί να σας εξοικονομήσει πολύ χρόνο είναι όταν πρέπει να συγχωνεύσετε πίνακες με διαφορετικά μεγέθη και στήλες με βάση μια αντίστοιχη στήλη.
Παρακάτω είναι ένα βίντεο όπου δείχνω ακριβώς πώς να συγχωνεύσετε πίνακες στο Excel χρησιμοποιώντας το Power Query.
Σε περίπτωση που προτιμάτε να διαβάζετε το κείμενο παρά να παρακολουθείτε ένα βίντεο, ακολουθούν οι γραπτές οδηγίες.
Ας υποθέσουμε ότι έχετε έναν πίνακα όπως φαίνεται παρακάτω:
Αυτός ο πίνακας έχει τα δεδομένα που θέλω να χρησιμοποιήσω, αλλά εξακολουθούν να λείπουν δύο σημαντικές στήλες - το «Αναγνωριστικό προϊόντος» και το «Περιοχή» όπου λειτουργεί ο εκπρόσωπος πωλήσεων.
Αυτές οι πληροφορίες παρέχονται ως ξεχωριστοί πίνακες, όπως φαίνεται παρακάτω:
Για να συγκεντρώσετε όλες αυτές τις πληροφορίες σε έναν πίνακα, θα πρέπει να συγχωνεύσετε αυτούς τους τρεις πίνακες, ώστε στη συνέχεια να δημιουργήσετε έναν Συγκεντρωτικό πίνακα και να τον αναλύσετε ή να τον χρησιμοποιήσετε για άλλους σκοπούς αναφοράς/πίνακα ελέγχου.
Και με τη συγχώνευση, δεν εννοώ μια απλή επικόλληση αντιγράφου.
Θα πρέπει να αντιστοιχίσετε τις σχετικές εγγραφές από τον Πίνακα 1 με δεδομένα από τους Πίνακες 2 και 3.
Τώρα μπορείτε να βασιστείτε σε VLOOKUP ή INDEX/MATCH για να το κάνετε αυτό.
Or αν είστε Viz whiz, μπορείτε να γράψετε έναν κωδικό για να το κάνετε αυτό.
Αλλά αυτές οι επιλογές είναι χρονοβόρες και περίπλοκες σε σύγκριση με το Power Query.
Σε αυτό το σεμινάριο, θα σας δείξω πώς να συγχωνεύσετε αυτούς τους τρεις πίνακες Excel σε έναν.
Για να λειτουργήσει αυτή η τεχνική, πρέπει να έχετε στήλες σύνδεσης. Για παράδειγμα, στον πίνακα 1 και στον πίνακα 2, η κοινή στήλη είναι «Στοιχείο» και στον πίνακα 1 και στον πίνακα 3, η κοινή στήλη είναι «Αντιπρόσωπος πωλήσεων». Επίσης, σημειώστε ότι δεν πρέπει να υπάρχει επανάληψη σε αυτές τις στήλες σύνδεσης.Σημείωση: Το Power Query μπορεί να χρησιμοποιηθεί ως πρόσθετο στο Excel 2010 και 2013 και είναι μια ενσωματωμένη δυνατότητα από το Excel 2016 και μετά. Με βάση την έκδοσή σας, ορισμένες εικόνες μπορεί να έχουν διαφορετική εμφάνιση (οι λήψεις εικόνων που χρησιμοποιούνται σε αυτό το σεμινάριο είναι από το Excel 2016).
Συγχώνευση πινάκων με χρήση ερωτήματος ισχύος
Έχω ονομάσει αυτούς τους πίνακες όπως φαίνεται παρακάτω:
- Πίνακας 1 - Δεδομένα πωλήσεων
- Πίνακας 2 - Pdt_Id
- Πίνακας 3 - Περιοχή
Δεν είναι υποχρεωτικό να μετονομάσετε αυτούς τους πίνακες, αλλά είναι καλύτερο να δώσετε ονόματα που περιγράφουν τον πίνακα.
Με τη μία, μπορείτε να συγχωνεύσετε μόνο δύο πίνακες στο Power Query.
Έτσι, θα πρέπει πρώτα να συγχωνεύσουμε τον Πίνακα 1 και τον Πίνακα 2 και στη συνέχεια να συγχωνεύσουμε τον Πίνακα 3 σε αυτόν στο επόμενο βήμα.
Συγχώνευση του πίνακα 1 και του πίνακα 2
Για να συγχωνεύσετε πίνακες, πρέπει πρώτα να μετατρέψετε αυτούς τους πίνακες σε συνδέσεις στο Power Query. Μόλις έχετε τις συνδέσεις, μπορείτε εύκολα να τις συγχωνεύσετε.
Ακολουθούν τα βήματα για να αποθηκεύσετε έναν πίνακα Excel ως σύνδεση στο Power Query:
- Επιλέξτε οποιοδήποτε κελί στον πίνακα Sales_Data.
- Κάντε κλικ στην καρτέλα Δεδομένα.
- Στην ομάδα Get & Transform, κάντε κλικ στο «From Table/Range». Αυτό θα ανοίξει το πρόγραμμα επεξεργασίας ερωτήματος.
- Στο πρόγραμμα επεξεργασίας ερωτήματος, κάντε κλικ στην καρτέλα "Αρχείο".
- Κάντε κλικ στην επιλογή «Κλείσιμο και φόρτωση σε».
- Στο παράθυρο διαλόγου «Εισαγωγή δεδομένων», επιλέξτε «Μόνο δημιουργία σύνδεσης».
- Κάντε κλικ στο OK.
Τα παραπάνω βήματα θα δημιουργήσουν μια σύνδεση με το όνομα Sales_Data (ή οποιοδήποτε όνομα που έχετε δώσει στον πίνακα του Excel).
Επαναλάβετε τα παραπάνω βήματα για τον πίνακα 2 και τον πίνακα 3.
Όταν τελειώσετε, θα έχετε τρεις συνδέσεις (με το όνομα Sales_Data, Pdt_Id και Region).
Τώρα ας δούμε πώς να συγχωνεύσουμε τον πίνακα Sales_Data και Pdt_Id.
- Κάντε κλικ στην καρτέλα Δεδομένα.
- Στην ομάδα Get & Transform Data, κάντε κλικ στο Get Data.
- Στο αναπτυσσόμενο μενού, κάντε κλικ στο Συνδυασμός ερωτημάτων.
- Κάντε κλικ στο Συγχώνευση. Αυτό θα ανοίξει το παράθυρο διαλόγου Συγχώνευση.
- Στο παράθυρο διαλόγου Συγχώνευση, επιλέξτε ‘Sales_Data’ από το πρώτο αναπτυσσόμενο μενού.
- Επιλέξτε "Pdt_Id" από το δεύτερο αναπτυσσόμενο μενού.
- Στην προεπισκόπηση ‘Sales_Data’, κάντε κλικ στη στήλη ‘Item’. Με αυτόν τον τρόπο θα επιλέξετε ολόκληρη τη στήλη.
- Στην προεπισκόπηση ‘Pdt_Id’, κάντε κλικ στη στήλη ‘Στοιχείο’. Με αυτόν τον τρόπο θα επιλέξετε ολόκληρη τη στήλη.
- Στο αναπτυσσόμενο μενού «Join Kind», επιλέξτε «Αριστερά εξωτερικά (όλα από το πρώτο, ταιριάζουν από το δεύτερο)».
- Κάντε κλικ στο OK.
Τα παραπάνω βήματα θα ανοίξουν το πρόγραμμα επεξεργασίας ερωτήματος και θα σας δείξουν τα δεδομένα από τα Sales_Data με μία επιπλέον στήλη (του Pdt_Id).
Συγχώνευση των πινάκων Excel (Πίνακας 1 & 2)
Τώρα η διαδικασία συγχώνευσης των πινάκων θα πραγματοποιηθεί εντός του προγράμματος επεξεργασίας ερωτήματος με τα ακόλουθα βήματα:
- Στην πρόσθετη στήλη (Pdt_Id), κάντε κλικ στο διπλό βέλος στην κεφαλίδα.
- Από το πλαίσιο επιλογών που ανοίγει, καταργήστε την επιλογή όλων των ονομάτων στηλών και επιλέξτε μόνο Στοιχείο. Αυτό συμβαίνει επειδή έχουμε ήδη τη στήλη όνομα προϊόντος στον υπάρχοντα πίνακα και θέλουμε μόνο το αναγνωριστικό προϊόντος για κάθε προϊόν.
- Καταργήστε την επιλογή «Χρήση αρχικού ονόματος στήλης ως πρόθεμα».
- Κάντε κλικ στο κουμπί Ok.
Αυτό θα σας δώσει τον πίνακα που προκύπτει και έχει κάθε εγγραφή από τον πίνακα Sales_Data και μια επιπλέον στήλη που έχει επίσης αναγνωριστικά προϊόντος (από τον πίνακα Pdt_Id).
Τώρα αν θέλετε να συνδυάσετε μόνο δύο πίνακες, μπορείτε να φορτώσετε αυτό το Excel έχετε τελειώσει.
Έχουμε όμως τρεις πίνακες για συγχώνευση, οπότε υπάρχει περισσότερη δουλειά που πρέπει να γίνει.
Πρέπει να αποθηκεύσετε αυτόν τον πίνακα που προκύπτει ως σύνδεση (έτσι ώστε να τον χρησιμοποιήσουμε για να τον συγχωνεύσουμε με τον Πίνακα 3).
Ακολουθούν τα βήματα για την αποθήκευση αυτού του συγχωνευμένου πίνακα (με δεδομένα από τον πίνακα Sales_Data και Pdt_Id) ως σύνδεση:
- Κάντε κλικ στην καρτέλα Αρχείο
- Κάντε κλικ στην επιλογή «Κλείσιμο και φόρτωση σε».
- Στο παράθυρο διαλόγου «Εισαγωγή δεδομένων», επιλέξτε «Μόνο δημιουργία σύνδεσης».
- Κάντε κλικ στο OK.
Αυτό θα αποθηκεύσει τα νέα συγχωνευμένα δεδομένα ως σύνδεση. Μπορείτε να μετονομάσετε αυτήν τη σύνδεση εάν θέλετε.
Συγχώνευση του πίνακα 3 με τον πίνακα που προκύπτει
Η διαδικασία συγχώνευσης του τρίτου πίνακα με τον πίνακα που προκύπτει (που πήραμε με τη συγχώνευση του πίνακα 1 και του πίνακα 2) είναι ακριβώς η ίδια.
Ακολουθούν τα βήματα για τη συγχώνευση αυτών των πινάκων:
- Κάντε κλικ στην καρτέλα Δεδομένα.
- Στην ομάδα Λήψη & μετασχηματισμός δεδομένων, κάντε κλικ στο «Λήψη δεδομένων».
- Στο αναπτυσσόμενο μενού, κάντε κλικ στο «Συνδυασμός ερωτημάτων.
- Κάντε κλικ στο «Συγχώνευση». Αυτό θα ανοίξει το παράθυρο διαλόγου Συγχώνευση.
- Στο παράθυρο διαλόγου Συγχώνευση, επιλέξτε «Συγχώνευση1» από το πρώτο αναπτυσσόμενο μενού.
- Επιλέξτε «Περιοχή» από το δεύτερο αναπτυσσόμενο μενού.
- Στην προεπισκόπηση ‘Merge1’, κάντε κλικ στη στήλη ‘Sales Rep’. Με αυτόν τον τρόπο θα επιλέξετε ολόκληρη τη στήλη.
- Στην προεπισκόπηση περιοχής, κάντε κλικ στη στήλη "Αντιπρόσωπος πωλήσεων". Με αυτόν τον τρόπο θα επιλέξετε ολόκληρη τη στήλη.
- Στο αναπτυσσόμενο μενού «Join Kind», επιλέξτε αριστερά εξωτερικά (όλα από το πρώτο, ταιριάζουν από το δεύτερο).
- Κάντε κλικ στο OK.
Τα παραπάνω βήματα θα ανοίξουν το πρόγραμμα επεξεργασίας ερωτήματος και θα σας δείξουν τα δεδομένα από το Merge1 με μία επιπλέον στήλη (Περιοχή).
Τώρα η διαδικασία συγχώνευσης των πινάκων θα πραγματοποιηθεί εντός του προγράμματος επεξεργασίας ερωτήματος με τα ακόλουθα βήματα:
- Στην πρόσθετη στήλη (Περιοχή), κάντε κλικ στο διπλό βέλος στην κεφαλίδα.
- Από το πλαίσιο επιλογών που ανοίγει, καταργήστε την επιλογή όλων των ονομάτων στηλών και επιλέξτε μόνο Περιοχή.
- Καταργήστε την επιλογή «Χρήση αρχικού ονόματος στήλης ως πρόθεμα».
- Κάντε κλικ στο κουμπί Ok.
Τα παραπάνω βήματα θα σας δώσουν έναν πίνακα που έχει συγχωνεύσει και τους τρεις πίνακες (πίνακας Sales_Data με μία στήλη για Pdt_Id και μία για Region).
Ακολουθούν τα βήματα για τη φόρτωση αυτού του πίνακα στο Excel:
- Κάντε κλικ στην καρτέλα Αρχείο.
- Κάντε κλικ στο «Κλείσιμο και φόρτωση σε».
- Στο παράθυρο διαλόγου "Εισαγωγή δεδομένων", επιλέξτε Επιλογές πίνακα και νέα φύλλα εργασίας.
- Κάντε κλικ στο OK.
Αυτό θα σας δώσει τον προκύπτον συγχωνευμένο πίνακα σε ένα νέο φύλλο εργασίας.
Ένα από τα καλύτερα πράγματα για το Power Query είναι ότι μπορείτε εύκολα να προσαρμόσετε τυχόν αλλαγές στα υποκείμενα δεδομένα (Πίνακας 1, 2 και 3) απλώς ανανεώνοντάς τα.
Για παράδειγμα, ας υποθέσουμε ότι η Laura μεταφέρεται στην Ασία και λαμβάνετε νέα δεδομένα για τον επόμενο μήνα. Τώρα δεν χρειάζεται να επαναλάβετε ξανά τα παραπάνω βήματα. Το μόνο που χρειάζεται να κάνετε είναι να ανανεώσετε το τραπέζι και θα κάνει τα πάντα ξανά για εσάς.
Μέσα σε δευτερόλεπτα θα έχετε τον νέο συγχωνευμένο πίνακα.
Μπορεί επίσης να σας αρέσουν τα ακόλουθα μαθήματα Power Query:
- Συνδυάστε δεδομένα από πολλά βιβλία εργασίας στο Excel (χρησιμοποιώντας το Power Query).
- Συνδυάστε δεδομένα από πολλά φύλλα εργασίας σε ένα φύλλο εργασίας στο Excel.
- Τρόπος Unpivot δεδομένων στο Excel χρησιμοποιώντας το Power Query (γνωστός και ως Get & Transform)
- Λήψη λίστας ονομάτων αρχείων από φακέλους και υποφακέλους (χρησιμοποιώντας το Power Query)
