Συνδυάστε με βάση την κοινή στήλη - Συμβουλές Excel

Ο David από τη Φλόριντα θέτει τη σημερινή ερώτηση:

Έχω δύο βιβλία εργασίας. Και οι δύο έχουν τα ίδια δεδομένα στη στήλη Α, αλλά οι υπόλοιπες στήλες είναι διαφορετικές. Πώς μπορώ να συγχωνεύσω αυτά τα δύο βιβλία εργασίας;

Ρώτησα τον David αν είναι πιθανό το ένα βιβλίο εργασίας να έχει περισσότερους δίσκους από το άλλο. Και η απάντηση είναι ναι. Ρώτησα τον David αν το πεδίο κλειδιού εμφανίζεται μόνο μία φορά σε κάθε αρχείο. Η απάντηση είναι επίσης ναι. Σήμερα, θα το λύσω με το Power Query. Τα εργαλεία Power Query βρίσκονται στις εκδόσεις του Excel 2016+ των Windows στην ενότητα Get & Transform της καρτέλας Δεδομένα. Εάν έχετε εκδόσεις Windows του Excel 2010 ή Excel 2013, μπορείτε να πραγματοποιήσετε λήψη του πρόσθετου Power Query για αυτές τις εκδόσεις.

Εδώ είναι το βιβλίο εργασίας του David 1. Έχει Product και στη συνέχεια τρεις στήλες δεδομένων.

Το πρώτο βιβλίο εργασίας

Εδώ είναι το βιβλίο εργασίας του David 2. Έχει τον Κωδικό Προϊόντος και μετά άλλες στήλες. Σε αυτό το παράδειγμα, υπάρχουν επιπλέον προϊόντα στο βιβλίο εργασίας2, αλλά οι λύσεις θα λειτουργήσουν εάν οποιοδήποτε βιβλίο εργασίας έχει επιπλέον στήλες.

Το δεύτερο βιβλίο εργασίας

Εδώ είναι τα βήματα:

  1. Επιλέξτε Δεδομένα, Λήψη δεδομένων, Από αρχείο, από Βιβλίο εργασίας:

    Φόρτωση δεδομένων από ένα αρχείο
  2. Περιηγηθείτε στο πρώτο βιβλίο εργασίας και κάντε κλικ στο OK
  3. Στο παράθυρο διαλόγου Navigator, επιλέξτε το φύλλο εργασίας στα αριστερά. (Ακόμα και αν υπάρχει μόνο ένα φύλλο εργασίας, πρέπει να το επιλέξετε.) Θα δείτε τα δεδομένα στα δεξιά.
  4. Στο παράθυρο διαλόγου Navigator, ανοίξτε το αναπτυσσόμενο μενού Φόρτωση και επιλέξτε Φόρτωση σε…
  5. Επιλέξτε Μόνο Δημιουργία σύνδεσης και πατήστε OK.

  6. Επαναλάβετε τα βήματα 1-5 για το δεύτερο βιβλίο εργασίας.

    Δημιουργήστε μια σύνδεση με το βιβλίο εργασίας

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

    Συνδέσεις και στα δύο βιβλία εργασίας

    Συνεχίστε με τα βήματα για τη συγχώνευση των βιβλίων εργασίας:

  7. Δεδομένα, Λήψη δεδομένων, Συνδυασμός ερωτημάτων, Συγχώνευση.

    Συγχώνευση δύο ερωτημάτων με διαφορετικές στήλες
  8. Από το επάνω αναπτυσσόμενο μενού στο παράθυρο διαλόγου Συγχώνευση, επιλέξτε το πρώτο ερώτημα.
  9. Από το δεύτερο αναπτυσσόμενο μενού στο παράθυρο διαλόγου Συγχώνευση, επιλέξτε το δεύτερο ερώτημα.
  10. Κάντε κλικ στην επικεφαλίδα προϊόντος στην επάνω προεπισκόπηση (αυτό είναι το βασικό πεδίο. Σημειώστε ότι μπορείτε να επιλέξετε πολλαπλά δύο ή περισσότερα βασικά πεδία με Ctrl + κλικ)
  11. Κάντε κλικ στην επικεφαλίδα Κωδικός προϊόντος στη δεύτερη προεπισκόπηση.

  12. Ανοίξτε τον τύπο συμμετοχής και επιλέξτε Full Outer (Όλες οι σειρές και από τα δύο)

    Τα βήματα 8 - 12 απεικονίζονται εδώ

  13. Κάντε κλικ στο OK. Η προεπισκόπηση δεδομένων δεν εμφανίζει τις επιπλέον σειρές και εμφανίζει μόνο "Πίνακας" επανειλημμένα στην τελευταία στήλη.

    Αυτό δεν φαίνεται πολλά υποσχόμενο
  14. Παρατηρήστε ότι υπάρχει ένα εικονίδιο "Ανάπτυξη" στην επικεφαλίδα του DavidTwo. Κάντε κλικ σε αυτό το εικονίδιο.

  15. Προαιρετικό, αλλά πάντα αποεπιλέγω "Χρήση αρχικού ονόματος στήλης ως πρόθεμα". Κάντε κλικ στο OK.

    Αναπτύξτε τα πεδία από το βιβλίο εργασίας 2

    Τα αποτελέσματα εμφανίζονται σε αυτήν την προεπισκόπηση:

    Όλες οι εγγραφές και από τα δύο βιβλία εργασίας
  16. Στο Power Query, χρησιμοποιήστε το Home, Close & Load.

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

Κάντε ξανά τα βήματα 1-16 κάνοντας κλικ σε αυτό το εικονίδιο ανανέωσης.

Σημείωση

Το εικονίδιο για Ανανέωση είναι συνήθως κρυφό. Σύρετε την αριστερή άκρη του παραθύρου ερωτημάτων και συνδέσεων προς τα αριστερά για να εμφανιστεί το εικονίδιο.

Δες το βίντεο

Μεταγραφή βίντεο

Μάθετε το Excel από το Podcast, επεισόδιο 2216: Συνδυάστε δύο βιβλία εργασίας με βάση μια κοινή στήλη.

Γεια σας, καλώς ήρθατε στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση του David, ο οποίος ήταν στο σεμινάριό μου στη Μελβούρνη της Φλόριντα, για το Κεφάλαιο της Διαστημικής Ακτής του IIA.

Ο David έχει δύο διαφορετικά βιβλία εργασίας, όπου η στήλη Α είναι κοινό μεταξύ των δύο. Λοιπόν, εδώ είναι το Βιβλίο Εργασίας 1, εδώ το Βιβλίο Εργασίας 2 - και τα δύο έχουν κωδικό προϊόντος. Αυτό έχει στοιχεία που δεν έχει το πρώτο ή το αντίστροφο και ο David θέλει να συνδυάσει όλες τις στήλες. Έχουμε λοιπόν τρεις στήλες εδώ και τέσσερις στήλες εδώ. Τα έβαλα και τα δύο στο ίδιο βιβλίο εργασίας, σε περίπτωση που κατεβάζετε το βιβλίο εργασίας για να δουλέψετε μαζί. Πάρτε κάθε ένα από αυτά, μετακινήστε το στο δικό του βιβλίο εργασίας και αποθηκεύστε το.

Εντάξει, για να συνδυάσουμε αυτά τα αρχεία, θα χρησιμοποιήσουμε το Power Query. Το Power Query είναι ενσωματωμένο στο Excel 2016. Εάν χρησιμοποιείτε την έκδοση των Windows 10 ή 13, μπορείτε να μεταβείτε στη Microsoft και να κατεβάσετε το Power Query. Μπορείτε να ξεκινήσετε από ένα νέο κενό βιβλίο εργασίας με ένα κενό φύλλο εργασίας. Θα αποθηκεύσετε αυτό το αρχείο - Αποθηκεύστε, όπως γνωρίζετε, ίσως το Βιβλίο εργασίας, για να εμφανίσετε τα αποτελέσματα των συνδυασμένων αρχείων .xlsx. Καλώς? Και αυτό που πρόκειται να κάνουμε είναι, θα κάνουμε δύο ερωτήσεις. Θα μεταβούμε στα δεδομένα, λήψη δεδομένων, από αρχείο, από το βιβλίο εργασίας και στη συνέχεια θα επιλέξουμε το πρώτο αρχείο. Σε μια προεπισκόπηση, επιλέξτε το φύλλο που περιέχει τα δεδομένα σας και δεν χρειάζεται να κάνουμε τίποτα σε αυτά τα δεδομένα. Απλώς ανοίξτε το πλαίσιο φόρτωσης και επιλέξτε Φόρτωση σε, Δημιουργία σύνδεσης μόνο, κάντε κλικ στο OK. Τέλειος. Τώρα, θα το επαναλάβουμε για το δεύτερο στοιχείο - Δεδομένα, Από Αρχείο,Από ένα βιβλίο εργασίας, επιλέξτε DavidTwo, επιλέξτε το όνομα του φύλλου και, στη συνέχεια, ανοίξτε το φορτίο, Load To, Only Create a Connection. Θα δείτε εδώ σε αυτό το πλαίσιο, έχουμε και τις δύο συνδέσεις. Καλώς.

Τώρα η πραγματική εργασία - Δεδομένα, Λήψη δεδομένων, Συνδυασμός ερωτημάτων, Συγχώνευση και, στη συνέχεια, στο παράθυρο διαλόγου Συγχώνευση, επιλέξτε DavidOne, DavidTwo και αυτό το επόμενο βήμα είναι εντελώς διαισθητικό. Πρέπει να το κάνετε αυτό. Επιλέξτε τη στήλη ή τις κοινές στήλες - έτσι Προϊόν και Προϊόν. Καλώς. Και τότε, να είστε πολύ προσεκτικοί εδώ με τον τύπο σύνδεσης. Θέλω όλες τις σειρές και από τα δύο επειδή μπορεί να έχει μια επιπλέον σειρά και πρέπει να το δω και μετά κάνουμε κλικ στο OK. Καλώς. Και εδώ είναι το αρχικό αποτέλεσμα. Δεν μοιάζει να λειτούργησε. δεν φαίνεται να πρόσθεσε τα επιπλέον στοιχεία που ήταν στο αρχείο 2. Και έχουμε αυτήν τη στήλη 5 - είναι άκυρη τώρα. Θα κάνω δεξί κλικ στη στήλη 5 και θα πω, Κατάργηση αυτής της στήλης. Ανοίξτε λοιπόν αυτό το εικονίδιο επέκτασης και αποεπιλέξτε αυτό το πλαίσιο για Χρήση αρχικού ονόματος στήλης ως πρόθεμα και BAM! δουλεύει. Έτσι, τα επιπλέον στοιχεία που βρίσκονταν στο Αρχείο 2, που δεν ήταν στο αρχείο 1,εμφανίζονται.

Καλώς. Τώρα στο σημερινό αρχείο, φαίνεται ότι αυτή η στήλη Κωδικός προϊόντος είναι καλύτερη από αυτήν τη στήλη Προϊόντος, επειδή έχει επιπλέον σειρές. Αλλά μπορεί να υπάρξει μια μέρα στο μέλλον όπου το Workbook 1 έχει πράγματα που το Workbook 2 δεν έχει. Άρα θα αφήσω και τους δύο εκεί, και δεν πρόκειται να απαλλαγούμε από μηδέν γιατί, παρόλο που, παρόλο που αυτή η σειρά στο κάτω μέρος φαίνεται να είναι εντελώς άκυρη, μπορεί να υπάρξει στο μέλλον μια κατάσταση όπου έχουμε μερικά κενά εδώ γιατί κάτι λείπει. Καλώς? Λοιπόν, τέλος, Κλείσιμο & φόρτωση, και έχουμε τις δεκαέξι σειρές μας.

Τώρα, στο μέλλον, ας πούμε ότι κάτι αλλάζει. Εντάξει, οπότε θα επιστρέψουμε σε ένα από αυτά τα δύο αρχεία και θα αλλάξω την τάξη για την Apple σε 99, και ας εισάγουμε ακόμη και κάτι νέο και να αποθηκεύσουμε αυτό το βιβλίο εργασίας Καλώς. Και τότε, εάν θέλουμε να ενημερώσουμε το αρχείο συγχώνευσης, ελάτε εδώ - τώρα, προσέξτε, όταν το κάνετε αυτό την πρώτη φορά, δεν μπορείτε να δείτε το εικονίδιο Ανανέωσης - πρέπει να αρπάξετε αυτήν τη γραμμή και να το σύρετε . Και θα κάνουμε Refresh, και φορτώθηκαν 17 σειρές, εμφανίζεται το καρπούζι, η Apple αλλάζει σε 99 - είναι ένα όμορφο πράγμα. Τώρα, hey, θέλετε να μάθετε για το Power Query; Αγοράστε αυτό το βιβλίο των Ken Puls και Miguel Escobar, το M είναι για (DATA) MONKEY. Θα σας φτάσω στην ταχύτητα.

Συμπλήρωση σήμερα: Ο David από τη Φλόριντα έχει δύο βιβλία εργασίας που θέλει να συνδυάσει. Και οι δύο έχουν τα ίδια πεδία στη στήλη Α, αλλά οι άλλες στήλες είναι όλες διαφορετικές. ένα βιβλίο εργασίας μπορεί να έχει επιπλέον στοιχεία που δεν είναι στο άλλο και ο David το θέλει. δεν υπάρχει κανένα αντίγραφο σε κανένα αρχείο. θα χρησιμοποιήσουμε το ερώτημα ενέργειας για να το λύσουμε, οπότε ξεκινήστε σε ένα νέο κενό βιβλίο εργασίας σε ένα κενό φύλλο εργασίας. πρόκειται να κάνετε τρία ερωτήματα, το πρώτο - Δεδομένα, Από Αρχείο, Βιβλίο εργασίας και, στη συνέχεια, Φόρτωση σε Δημιουργία σύνδεσης. το ίδιο πράγμα για το δεύτερο βιβλίο εργασίας και, στη συνέχεια, Δεδομένα, Λήψη δεδομένων, Συγχώνευση, επιλέξτε τις δύο συνδέσεις, επιλέξτε τη στήλη που είναι κοινή και στα δύο - στην περίπτωσή μου, Προϊόν - και στη συνέχεια από τον τύπο συμμετοχής, θέλετε να συμμετάσχετε πλήρως όλα από το αρχείο 1, όλα από το αρχείο 2. Και τότε το όμορφο είναι εάν αλλάξουν τα υποκείμενα δεδομένα,μπορείτε απλώς να ανανεώσετε το ερώτημα.

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

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

Λήψη αρχείου Excel

Για να πραγματοποιήσετε λήψη του αρχείου excel: συνδυάστε-based-on-common-column.xlsx

Το Power Query είναι ένα καταπληκτικό εργαλείο στο Excel.

Excel Thought Of the Day

Ζήτησα από τους φίλους μου στο Excel Master τις συμβουλές τους σχετικά με το Excel. Η σημερινή σκέψη να σκεφτούμε:

"Πάντα πατήστε το F4 όταν διαβάζετε εύρος ή μήτρα σε μια συνάρτηση"

Τάντζα Κουν

ενδιαφέροντα άρθρα...