Λάβετε πολλαπλές τιμές αναζήτησης σε ένα κελί (με & χωρίς επανάληψη)

Μπορούμε να αναζητήσουμε και να επιστρέψουμε πολλαπλές τιμές σε ένα κελί στο Excel (χωρισμένο με κόμμα ή κενό);

Μου έχουν κάνει πολλές φορές αυτή την ερώτηση από πολλούς συναδέλφους και αναγνώστες μου.

Το Excel έχει εκπληκτικούς τύπους αναζήτησης, όπως VLOOKUP, INDEX/MATCH (και τώρα XLOOKUP), αλλά κανένα από αυτά δεν προσφέρει τρόπο επιστροφής πολλαπλών τιμών αντιστοίχισης. Όλα αυτά λειτουργούν προσδιορίζοντας το πρώτο ταίριασμα και επιστρέφοντάς το.

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

Εκσυγχρονίζω: Αφού το Excel κυκλοφόρησε δυναμικούς πίνακες και φοβερές λειτουργίες όπως UNIQUE και TEXTJOIN, είναι πλέον δυνατό να χρησιμοποιήσετε έναν απλό τύπο και επιστρέψτε όλες τις τιμές που ταιριάζουν σε ένα κελί (καλύπτεται σε αυτό το σεμινάριο).

Σε αυτό το σεμινάριο, θα σας δείξω πώς να το κάνετε αυτό (εάν χρησιμοποιείτε την πιο πρόσφατη έκδοση του Excel - Microsoft 365 με όλες τις νέες λειτουργίες), καθώς και έναν τρόπο να το κάνετε αυτό σε περίπτωση που χρησιμοποιείτε παλαιότερες εκδόσεις ( χρησιμοποιώντας VBA).

Ας ξεκινήσουμε λοιπόν!

Αναζήτηση και επιστροφή πολλαπλών τιμών σε ένα κελί (χρησιμοποιώντας τον τύπο)

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

Με τη συνδρομή Microsoft 365, το Excel σας έχει τώρα πολύ πιο ισχυρές λειτουργίες και δυνατότητες που δεν υπάρχουν σε προηγούμενες εκδόσεις (όπως XLOOKUP, Dynamic Arrays, UNIQUE/FILTER, κ.λπ.)

Έτσι, εάν χρησιμοποιείτε το Microsoft 365 (παλαιότερα γνωστό ως Office 365), μπορείτε να χρησιμοποιήσετε τις μεθόδους που καλύπτονται σε αυτήν την ενότητα, για να αναζητήσετε και να επιστρέψετε πολλαπλές τιμές σε ένα κελί στο Excel.

Και όπως θα δείτε, είναι μια πολύ απλή φόρμουλα.

Παρακάτω έχω ένα σύνολο δεδομένων όπου έχω τα ονόματα των ατόμων στη στήλη Α και την εκπαίδευση που έχουν παρακολουθήσει στη στήλη Β.

Κάντε κλικ εδώ για να κάνετε λήψη του παραδείγματος αρχείου και ακολουθήστε

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

Παρακάτω είναι ο τύπος που θα το κάνει αυτό:

= TEXTJOIN (",", TRUE, IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

Αφού εισαγάγετε τον τύπο στο κελί Ε2, αντιγράψτε τον για όλα τα κελιά όπου θέλετε τα αποτελέσματα.

Πώς λειτουργεί αυτός ο τύπος;

Επιτρέψτε μου να αποδομήσω αυτόν τον τύπο και να εξηγήσω κάθε μέρος για το πώς συνδυάζεται μας δίνει το αποτέλεσμα.

Η λογική δοκιμή στον τύπο IF (D2 = $ A $ 2: $ A $ 20) ελέγχει εάν το κελί ονόματος D2 είναι το ίδιο με αυτό στην περιοχή A2: A20.

Περνάει από κάθε κελί της περιοχής A2: A20 και ελέγχει αν το όνομα είναι ίδιο στο κελί D2 ή όχι. αν είναι το ίδιο όνομα, επιστρέφει TRUE, αλλιώς επιστρέφει FALSE.

Αυτό το μέρος του τύπου θα σας δώσει έναν πίνακα όπως φαίνεται παρακάτω:

{ΑΛΗΘΕΙΑ; ΛΑΘΟΣ; ΛΑΘΟΣ; ΑΛΗΘΕΙΑ; ΛΑΘΟΣ; ΛΑΘΟΣ; ΛΑΘΟΣ; ΛΑΘΟΣ; ΛΑΘΟΣ; ALΕΥΔΙΚΟ

Δεδομένου ότι θέλουμε μόνο να λάβουμε την εκπαίδευση για τον Bob (η τιμή στο κελί D2), πρέπει να λάβουμε όλη την αντίστοιχη εκπαίδευση για τα κελιά που επιστρέφουν TRUE στον παραπάνω πίνακα.

Αυτό γίνεται εύκολα καθορίζοντας [value_if_true] μέρος του τύπου IF ως το εύρος που έχει την εκπαίδευση. Αυτό διασφαλίζει ότι εάν το όνομα στο κελί D2 ταιριάζει με το όνομα της περιοχής A2: A20, ο τύπος IF θα επιστρέψει όλη την εκπαίδευση που έχει λάβει το άτομο.

Και όπου ο πίνακας επιστρέφει FALSE, έχουμε καθορίσει την τιμή [value_if_false] ως "" (κενό), οπότε επιστρέφει ένα κενό.

Το τμήμα IF του τύπου επιστρέφει τον πίνακα όπως φαίνεται παρακάτω:

{"Excel"; ""; "" "" PowerPoint ";" ";" "" "" "" "" "" "" "" "" "" ; ”” ”” ”” ”” ”}

Όπου έχει τα ονόματα της προπόνησης ο Μπομπ έχει πάρει και κάνει κενά όπου δεν ήταν το όνομα Μπομπ.

Τώρα, το μόνο που χρειάζεται να κάνουμε είναι να συνδυάσουμε αυτό το όνομα εκπαίδευσης (χωρισμένο με κόμμα) και να το επιστρέψουμε σε ένα κελί.

Και αυτό μπορεί εύκολα να γίνει χρησιμοποιώντας τον νέο τύπο TEXTJOIN (διαθέσιμο στο Excel2021-2022 και Excel στο Microsoft 365)

Ο τύπος TEXTJOIN λαμβάνει τρία επιχειρήματα:

• ο οριοθέτης - που είναι "," στο παράδειγμά μας, καθώς θέλω η εκπαίδευση να διαχωρίζεται με ένα κόμμα και έναν κενό χαρακτήρα
• TRUE - το οποίο λέει στον τύπο TEXTJOIN να αγνοήσει τα κενά κελιά και να συνδυάσει μόνο αυτά που δεν είναι κενά
• Ο τύπος If που επιστρέφει το κείμενο που πρέπει να συνδυαστεί

Εάν χρησιμοποιείτε το Excel στο Microsoft 365 που έχει ήδη δυναμικούς πίνακες, μπορείτε απλώς να εισαγάγετε τον παραπάνω τύπο και να πατήσετε enter. Και αν χρησιμοποιείτε Excel2021-2022, πρέπει να εισαγάγετε τον τύπο και κρατήστε πατημένο το πλήκτρο Control και Shift και, στη συνέχεια, πατήστε Enter

Κάντε κλικ εδώ για να κάνετε λήψη του παραδείγματος αρχείου και ακολουθήστε

Λάβετε πολλαπλές τιμές αναζήτησης σε ένα κελί (χωρίς επανάληψη)

Δεδομένου ότι ο ΜΟΝΑΔΙΚΟΣ τύπος είναι διαθέσιμος μόνο για το Excel στο Microsoft 365, δεν θα μπορείτε να χρησιμοποιήσετε αυτήν τη μέθοδο στο Excel2021-2022

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

Στο παραπάνω σύνολο δεδομένων, ορισμένοι άνθρωποι έχουν λάβει εκπαίδευση πολλές φορές. Για παράδειγμα, ο Bob και ο Stan έχουν παρακολουθήσει την εκπαίδευση Excel δύο φορές και η Betty έχει παρακολουθήσει MS Word δύο φορές. Αλλά στο αποτέλεσμά μας, δεν θέλουμε να έχουμε επανάληψη ονόματος προπόνησης.

Μπορείτε να χρησιμοποιήσετε τον παρακάτω τύπο για να το κάνετε αυτό:

= TEXTJOIN (",", TRUE, UNIQUE (ΕΑΝ (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

Ο παραπάνω τύπος λειτουργεί με τον ίδιο τρόπο, με μια μικρή αλλαγή. χρησιμοποιήσαμε τον τύπο IF μέσα στη συνάρτηση UNIQUE, έτσι ώστε σε περίπτωση που υπάρχουν επαναλήψεις στο αποτέλεσμα του τύπου if, η συνάρτηση UNIQUE θα τον αφαιρέσει.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Αναζήτηση και επιστροφή πολλαπλών τιμών σε ένα κελί (χρησιμοποιώντας VBA)

Εάν χρησιμοποιείτε Excel 2016 ή προηγούμενες εκδόσεις, τότε δεν θα έχετε πρόσβαση στον τύπο TEXTJOIN. Ο καλύτερος τρόπος για να αναζητήσετε και να λάβετε πολλαπλές τιμές αντιστοίχισης σε ένα κελί είναι χρησιμοποιώντας έναν προσαρμοσμένο τύπο που μπορείτε να δημιουργήσετε χρησιμοποιώντας το VBA.

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

Εδώ είναι ο κώδικας VBA που μπορεί να το κάνει αυτό:

'Code by Sumit Bansal (https://trumpexcel.com) Συνάρτηση SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1). .Count If LookupRange.Cells (i, 1) = Lookupvalue then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Αριστερά (Αποτέλεσμα, Len (Αποτέλεσμα) - 1) Λειτουργία λήξης

Πού να τοποθετήσετε αυτόν τον κωδικό;

1. Ανοίξτε ένα βιβλίο εργασίας και κάντε κλικ στο Alt + F11 (αυτό ανοίγει το παράθυρο του VBA Editor).
2. Σε αυτό το παράθυρο VBA Editor, στα αριστερά, υπάρχει ένας εξερευνητής έργου (όπου παρατίθενται όλα τα βιβλία εργασίας και τα φύλλα εργασίας). Κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο στο βιβλίο εργασίας όπου θέλετε να λειτουργήσει αυτός ο κώδικας και μεταβείτε στην επιλογή Εισαγωγή -> Ενότητα.
3. Στο παράθυρο της λειτουργικής μονάδας (που θα εμφανιστεί στα δεξιά), αντιγράψτε και επικολλήστε τον παραπάνω κώδικα.
4. Τώρα είστε έτοιμοι. Μεταβείτε σε οποιοδήποτε κελί στο βιβλίο εργασίας και πληκτρολογήστε = SingleCellExtract και συνδέστε τα απαιτούμενα ορίσματα εισόδου (δηλαδή, LookupValue, LookupRange, ColumnNumber).

Πώς λειτουργεί αυτός ο τύπος;

Αυτή η λειτουργία λειτουργεί παρόμοια με τη λειτουργία VLOOKUP.

Παίρνει 3 ορίσματα ως εισόδους:

1. Lookupvalue - Μια συμβολοσειρά που πρέπει να αναζητήσουμε σε μια σειρά κελιών.
2. Εύρος αναζήτησης - Μια σειρά κελιών από όπου πρέπει να πάρουμε τα δεδομένα ($ B3: $ C18 σε αυτήν την περίπτωση).
3. ColumnNumber - Είναι ο αριθμός στήλης του πίνακα/πίνακα από τον οποίο πρέπει να επιστρέψει η τιμή αντιστοίχισης (2 σε αυτή την περίπτωση).

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

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

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Μάθετε πώς να αυτοματοποιείτε βαρετές επαναλαμβανόμενες εργασίες με το VBA στο Excel. Γίνε μελος Μάθημα Excel VBA

Λάβετε πολλαπλές τιμές αναζήτησης σε ένα κελί (χωρίς επανάληψη)

Υπάρχει πιθανότητα να έχετε επαναλήψεις στα δεδομένα.

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

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

Εδώ είναι ο κώδικας VBA που θα σας δώσει πολλαπλές τιμές αναζήτησης σε ένα κελί χωρίς καμία επανάληψη.

'Code by Sumit Bansal (https://trumpexcel.com) Λειτουργία MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Colons (1). Κελιά .Count If LookupRange.Cells (i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells (J, 1) = Lookupvalue then If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Στη συνέχεια GoTo Skip End If End If Next J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Αριστερά (Αποτέλεσμα, Len (Αποτέλεσμα) - 1) Τέλος Λειτουργία

Αφού τοποθετήσετε αυτόν τον κωδικό στο VB Editor (όπως φαίνεται παραπάνω στο σεμινάριο), θα μπορείτε να χρησιμοποιήσετε το MultipleLookupNoRept λειτουργία.

Εδώ είναι ένα στιγμιότυπο του αποτελέσματος που θα έχετε με αυτό MultipleLookupNoRept λειτουργία.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Σε αυτό το σεμινάριο, κάλυψα πώς να χρησιμοποιήσετε τύπους και VBA στο Excel για να βρείτε και να επιστρέψετε πολλαπλές τιμές αναζήτησης σε ένα κελί στο Excel.

Παρόλο που μπορεί εύκολα να γίνει με έναν απλό τύπο εάν χρησιμοποιείτε το Excel στη συνδρομή Microsoft 365, εάν χρησιμοποιείτε προηγούμενες εκδόσεις και δεν έχετε πρόσβαση σε λειτουργίες όπως το TEXTJOIN, μπορείτε ακόμα να το κάνετε χρησιμοποιώντας το VBA δημιουργώντας το δική του προσαρμοσμένη λειτουργία.