Συγχώνευση δύο λιστών χωρίς διπλότυπα

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

Ας δούμε παραδοσιακά διάφορους τρόπους για να λύσουμε ένα τόσο κοινό πρόβλημα – από πρωτόγονο «στο μέτωπο» έως πιο περίπλοκο, αλλά κομψό.

Μέθοδος 1: Κατάργηση διπλότυπων

Μπορείτε να λύσετε το πρόβλημα με τον απλούστερο τρόπο – αντιγράψτε χειροκίνητα τα στοιχεία και των δύο λιστών σε μία και, στη συνέχεια, εφαρμόστε το εργαλείο στο σύνολο που προκύπτει. Κατάργηση διπλότυπων από την καρτέλα ημερομηνία (Δεδομένα — Κατάργηση διπλότυπων):

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

Μέθοδος 1α. συγκεντρωτικός πίνακας

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

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

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

Μέθοδος 2: Τύπος πίνακα

Μπορείτε να λύσετε το πρόβλημα με τύπους. Στην περίπτωση αυτή, ο επανυπολογισμός και η ενημέρωση των αποτελεσμάτων θα γίνει αυτόματα και άμεσα, αμέσως μετά τις αλλαγές στις αρχικές λίστες. Για ευκολία και συντομία, ας δώσουμε ονόματα στις λίστες μας. Λίστα 1 и Λίστα 2χρησιμοποιώντας Όνομα Διευθυντής αυτί τύπος (Τύποι — Διαχείριση ονομάτων — Δημιουργία):

Μετά την ονομασία, ο τύπος που χρειαζόμαστε θα μοιάζει με αυτό:

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

Η λογική εδώ είναι η εξής:

• Ο τύπος INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) επιλέγει όλα τα μοναδικά στοιχεία από την πρώτη λίστα. Μόλις εξαντληθούν, αρχίζει να δίνει ένα σφάλμα #N/A:

  

• Ο τύπος INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) εξάγει τα μοναδικά στοιχεία από τη δεύτερη λίστα με τον ίδιο τρόπο.
• Ένθετες μεταξύ τους δύο συναρτήσεις IFERROR υλοποιούν την έξοδο πρώτα από τις μοναδικές από τη λίστα-1 και μετά από τη λίστα-2 τη μία μετά την άλλη.

Σημειώστε ότι πρόκειται για τύπο πίνακα, δηλαδή μετά την πληκτρολόγηση, πρέπει να εισαχθεί σε ένα κελί που δεν είναι συνηθισμένο εισάγετε, αλλά με συντόμευση πληκτρολογίου Ctrl+αλλαγή+εισάγετε και μετά αντιγράψτε (σύρετε) προς τα κάτω στα θυγατρικά κελιά με ένα περιθώριο.

Στην αγγλική έκδοση του Excel, αυτός ο τύπος μοιάζει με:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), "") 

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

Μέθοδος 3. Power Query

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

Ο αλγόριθμος ενεργειών έχει ως εξής:

1. Ανοίξτε μια ξεχωριστή καρτέλα του εγκατεστημένου πρόσθετου Ερώτημα ισχύος (αν έχετε Excel 2010-2013) ή απλώς μεταβείτε στην καρτέλα ημερομηνία (αν έχετε Excel 2016).
2. Επιλέξτε την πρώτη λίστα και πατήστε το κουμπί Από Πίνακας/Εύρος (Από Εύρος/Πίνακα). Όταν ρωτήθηκε για τη δημιουργία ενός "έξυπνου πίνακα" από τη λίστα μας, συμφωνούμε:

   

3. Ανοίγει το παράθυρο επεξεργασίας ερωτημάτων, όπου μπορείτε να δείτε τα φορτωμένα δεδομένα και το όνομα του ερωτήματος Πίνακας 1 (μπορείτε να το αλλάξετε στο δικό σας αν θέλετε).
4. Κάντε διπλό κλικ στην κεφαλίδα του πίνακα (word Λίστα 1) και μετονομάστε το σε οποιοδήποτε άλλο (για παράδειγμα People). Τι ακριβώς να ονομάσετε δεν είναι σημαντικό, αλλά το όνομα που επινοήθηκε πρέπει να θυμόμαστε, γιατί. θα πρέπει να χρησιμοποιηθεί ξανά αργότερα κατά την εισαγωγή του δεύτερου πίνακα. Η συγχώνευση δύο πινάκων στο μέλλον θα λειτουργήσει μόνο εάν οι επικεφαλίδες στηλών τους ταιριάζουν.
5. Αναπτύξτε την αναπτυσσόμενη λίστα στην επάνω αριστερή γωνία κλείστε και κατεβάστε Και επιλέξτε Κλείσιμο και φόρτωση σε… (Κλείσιμο & Φόρτωση σε…):

   

6. Στο επόμενο παράθυρο διαλόγου (μπορεί να φαίνεται λίγο διαφορετικό – μην ανησυχείτε), επιλέξτε Απλώς δημιουργήστε μια σύνδεση (Δημιουργία μόνο σύνδεσης):

   

7. Επαναλαμβάνουμε όλη τη διαδικασία (σημεία 2-6) για τη δεύτερη λίστα. Όταν μετονομάζετε μια επικεφαλίδα στήλης, είναι σημαντικό να χρησιμοποιείτε το ίδιο όνομα (Άτομα) όπως στο προηγούμενο ερώτημα.
8. Στο παράθυρο του Excel στην καρτέλα ημερομηνία ή στην καρτέλα Ερώτημα ισχύος Επιλέξτε Λήψη δεδομένων – Συνδυασμός αιτημάτων – Προσθήκη (Λήψη δεδομένων — Ερωτήματα συγχώνευσης — Προσάρτηση):

   

9. Στο παράθυρο διαλόγου που εμφανίζεται, επιλέξτε τα αιτήματά μας από τις αναπτυσσόμενες λίστες:

   

10. Ως αποτέλεσμα, θα λάβουμε ένα νέο ερώτημα, όπου δύο λίστες θα συνδέονται μεταξύ τους. Απομένει να αφαιρέσετε τα διπλότυπα με το κουμπί Διαγραφή σειρών – Κατάργηση διπλότυπων (Διαγραφή σειρών — Διαγραφή διπλότυπων):

    

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

    

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

• Πώς να συλλέξετε πολλούς πίνακες από διαφορετικά αρχεία χρησιμοποιώντας το Power Query
• Εξαγωγή μοναδικών αντικειμένων από μια λίστα
• Πώς να συγκρίνετε δύο λίστες μεταξύ τους για αντιστοιχίες και διαφορές