Μπορεί το Excel VLOOKUP να επιστρέψει όλα τα αποτελέσματα και να τα ενώσει με κόμμα;
Δες το βίντεο
- Ο στόχος είναι να συγκεντρωθούν όλες οι απαντήσεις κειμένου από ένα VLOOKUP
- Μέθοδος Bill: Χρησιμοποιήστε μια συνάρτηση VBA που ονομάζεται GetAll
- Μοναδική λίστα χρησιμοποιώντας Κατάργηση διπλότυπων
- Η μέθοδος του Mike:
- Μοναδική λίστα χρησιμοποιώντας το Advanced Filter
- Η συνάρτηση TEXTJOIN προστέθηκε στο Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Λόγω της λειτουργίας IF, ο τύπος απαιτεί Ctrl + Shift + Enter όποτε επεξεργάζεστε τον τύπο
- Το Alt AQOR Enter θα εκτελέσει ξανά το Advanced Filter!
Μεταγραφή βίντεο
Επεισόδιο 183: Συμμετοχή σε όλους τους αγώνες VLOOKUP
Bill Jelen: Γεια σας, καλώς ήλθατε πίσω. Ήρθε η ώρα για ένα άλλο Dueling Excel Podcast. Είμαι ο Μπιλ Τζέλεν από, θα ενωθώ με τον Mike Girvin για το Excel Is Fun. Αυτό είναι το επεισόδιο 183: Συμμετοχή σε όλους τους αγώνες VLOOKUP.
(ΜΟΥΣΙΚΗ)
Εντάξει, η σημερινή ερώτηση από τον Ματ. Μπορεί το VLOOKUP να επιστρέψει όλα τα αποτελέσματα και να τα ενώσει μαζί με ένα κόμμα μεταξύ τους. Για παράδειγμα, 109876 που είναι αυτά τα δύο εδώ, μπορεί να επιστρέψει τον χώρο με κόμμα χαμηλού λαδιού. Ελεγμένος στις 12/12. Και φυσικά αν υπήρχαν περισσότερα, θα επέστρεφε περισσότερο. Εντάξει, οπότε η λύση μου εδώ θα χρησιμοποιεί κάποιο VBA. Εντάξει, οπότε βεβαιωθείτε ότι έχει αποθηκευτεί ως xlsm ή ότι δεν μπορείτε να εκτελέσετε VBA ή xlsb, αλλά όχι xlsx - xlsx είναι το μόνο αρχείο που δεν μπορεί να εκτελέσει VBA. Θα πατήσουμε το πλήκτρο Alt + F11, βεβαιωθείτε ότι βρίσκεστε στο Dual183 ή όποιο και αν είναι το όνομα του βιβλίου εργασίας σας. Εισαγάγετε το Module στο κενό module και θα επικολλήσουμε αυτόν τον κώδικα, εντάξει.
Ας ρίξουμε μια ματιά σε αυτήν τη λειτουργία GetAll, και εδώ είναι ο αριθμός ταυτότητας που ψάχνουμε και στη συνέχεια το εύρος που θέλουμε να αναζητήσουμε. Και ξεκινάμε, θα επιστρέψουμε μια μεταβλητή που ονομάζεται GetAll, οπότε ξεκινάμε με το να είναι ίσο με κενό. Για κάθε κελί στο εύρος μου, εάν η τιμή κελιού είναι αυτό που ψάχνουμε τότε θα πάρουμε το GetAll = GetAll & "" και μετά το Cell.Offset (0 σειρές, 1 στήλη), με άλλα λόγια την τιμή ακριβώς δίπλα στον συγκεκριμένο αριθμό ταυτότητας γιατί πίσω στο VBA, εδώ είναι ο αριθμός ταυτότητας. Αν βρούμε τον αντίστοιχο αριθμό ταυτότητας, θέλουμε να πάμε 1 στήλη. Τώρα, τι γίνεται αν θέλετε να περάσετε πάνω από 2 στήλες ή 3 στήλες, τότε τότε αλλάξετε αυτές τις 0 σειρές και 1 στήλη σε 2. Εντάξει, ελέγξτε επίσης αν - δεν βάζουμε κόμμα, εάν αυτό είναι η πρώτη.Αν λοιπόν η μεταβλητή GetAll είναι αυτή τη στιγμή "" τότε δεν θα βάλουμε το κόμμα, εντάξει;
Τώρα που έχουμε αυτήν τη λειτουργία εδώ, παρακολουθήστε πόσο εύκολο είναι να λύσετε το πρόβλημα του Ματ. Θα έρθουμε εδώ και ας πάρουμε τα αναγνωριστικά του, Ctrl + C και επικολλήστε το Ctrl + V έτσι. Δεδομένα, Κατάργηση διπλότυπων, κάντε κλικ στο OK. Υπάρχει λοιπόν μια μοναδική λίστα αναγνωριστικών και στη συνέχεια θέλουμε να πούμε = getall και αναζητούμε αυτήν την τιμή στο E2 κόμμα. Κοιτάζοντας μέσα από αυτό το εύρος εδώ, θα πατήσω το F4. Το F4 λειτουργεί όπως μια κανονική λειτουργία. Και πάλι απομακρύνοντας την ερώτηση του Ματ, κάντε διπλό κλικ για να το κατεβάσετε. Θα δουλέψει.
Και ας δοκιμάσουμε, ας δοκιμάσουμε κάτι τρελό εδώ. Ας κάνουμε μια φράση 1 και απλώς βάλτε μια σειρά από αυτές όπως η φράση 1 έως 10. Θα τα υπογράψουμε όλα αυτά στο 109999. Επικόλληση και μετά επικόλληση εδώ. Αντιγράψτε αυτόν τον τύπο προς τα κάτω, επεξεργαστείτε τον τύπο, ώστε φυσικά να φτάσει μέχρι το κάτω μέρος, φυσικά. Ναι. Και θα επιστρέψει όλες αυτές τις φράσεις. Εντάξει, έτσι είναι η λύση μου, VBA, μια μικρή λειτουργία εκεί. Μάικ, ας δούμε τι έχετε.
Mike Girvin: Ευχαριστώ, GetAll, αυτή είναι μια φοβερή λειτουργία VBA. Εντάξει, θα πάω στο φύλλο εδώ. Το έχω ήδη μετατρέψει σε πίνακα Excel, έτσι ώστε όταν προσθέτουμε εγγραφές παρακάτω, ελπίζουμε ότι τα πράγματα θα ενημερωθούν.
Τώρα το πρώτο πράγμα που θα το κάνω σε δύο μέρη. Θα μπορούσα να κάνω έναν τύπο εδώ για την εξαγωγή μιας μοναδικής λίστας, αλλά θέλω να δω μια άλλη επιλογή: Το σύνθετο φίλτρο έχει μια επιλογή εξαγωγής μοναδικής λίστας και μπορεί να ενημερωθεί. Θα επισημάνω μόνο τα δεδομένα στηλών ID, στο Advanced Filter ή θα χρησιμοποιήσω το πληκτρολόγιο Alt, A, Q. Τώρα, η λίστα φίλτρων στη θέση της, με κανένα τρόπο. Θέλω να το αντιγράψω σε άλλη τοποθεσία. Πήρε μόνο τη στήλη Α και επειδή είναι ένας πίνακας Excel που θα επεκταθεί αργότερα. Δεν έχω κριτήρια, θέλω να το αντιγράψω στο D1 και να ελέγξω μόνο τις μοναδικές εγγραφές. Κάντε κλικ στο OK.
Τώρα, θα έρθω εδώ, εισάγονται όλα τα σχόλια και θα χρησιμοποιήσω μια συνάρτηση που λειτουργεί μόνο στο Excel 2016 Office 365: = συνάρτηση TEXTJOIN. Αυτή η λειτουργία από μόνη της αξίζει να αποκτήσετε την τελευταία έκδοση του Excel. Αυτό είναι ένα τόσο κοινό καθήκον που οι άνθρωποι θέλουν να κάνουν, να ενώσουν πολλά πράγματα μαζί. Τώρα ο διαχωριστής μας στο ",", και το σπουδαίο πράγμα για αυτήν τη λειτουργία είναι ότι μπορούμε να το πούμε να αγνοήσουμε τα κενά κελιά. Τώρα, μπορώ να βάλω TRUE, 1 ή να το αφήσω, να το παραλείψω. Λοιπόν, θα το αφήσω, παραλείψτε το. Και εδώ χρειαζόμαστε το κείμενό μας. Θα χρησιμοποιήσουμε τη λειτουργία IF για να φιλτράρουμε και να λαμβάνουμε μόνο τα στοιχεία που θέλουμε. Πρόκειται να πω ολόκληρη τη στήλη εδώ: Όνομα πίνακα και στη συνέχεια στο () το όνομα πεδίου, είστε οποιοσδήποτε από εσάς = σε αυτήν τη σχετική αναφορά κελιού, αυτή είναι η λογική δοκιμή. Αν ήθελα να κάνω κλικ σε αυτό και να πατήσω το πλήκτρο F9 για αξιολόγηση,θα μπορούσατε να δείτε τώρα έχουμε μόνο 2 TRUES, Ctrl + Z τώρα πληκτρολογώ κόμμα και με τη σειρά Trues and Falses, τώρα μπορώ να του δώσω τα στοιχεία για να διαλέξω. Τώρα λοιπόν, θα διαλέξουμε μόνο τα στοιχεία που έχουν ΑΛΗΘΕΙΑ εδώ από αυτό το εύρος. Κόμμα και θέλουμε να βεβαιωθούμε ότι θα βάλουμε το “” - αυτό θα εμφανιστεί ως κενό κελί σε σχέση με το δεύτερο όρισμα στο TEXTJOIN.
Τώρα, πρόκειται να κλείσω παρένθεση και τώρα η συνάρτηση IF θα δημιουργήσει αυτήν τη συμβολοσειρά Trues and Falses, τα πραγματικά αντικείμενα από αυτό το εύρος θα παραληφθούν εάν το βλέπουν True και όλα τα άλλα στοιχεία θα έχουν αυτό το κενό κελί. Και μάντεψε τι? Το TEXTJOIN θα αγνοήσει εντελώς όλα αυτά τα κενά κελιά και θα επιστρέψει μόνο τα στοιχεία που ταιριάζουν με αυτό το αναγνωριστικό και, στη συνέχεια, θα το ενώσει με αυτό το οριοθέτη. Τώρα είναι σίγουρα μια φόρμουλα Array που απαιτεί την ειδική πληκτρολόγηση Ctrol + Shift + Enter. Το λογικό όρισμα δοκιμής διατηρεί τη λειτουργία Array και αυτό το όρισμα δεν μπορεί να υπολογίσει σωστά αυτήν τη λειτουργία Array, εκτός εάν χρησιμοποιήσουμε το πληκτρολόγιο Ctrl + Shift + Enter. Τώρα θα κλείσω παρενθέσεις. Στην πραγματικότητα θα μπορούσαμε να αποδείξουμε 1 εδώ στο Κείμενο 1 αν το F9 όλα αυτά, θα μπορούσαμε να δούμε ότι παίρνουμε τα 2 στοιχεία, τα υπόλοιπα κενά κελιά θα αγνοηθούν. Ctrl + Z. Τώρα, ας 's εισάγετε αυτό στο κελί με Ctrl + Shift + Enter. Κοιτάξτε αμέσως τη Formula Bar. Αυτές οι σγουρές αγκύλες είναι το Excel που σας λέει ότι το καταλάβαινε και το υπολόγισε ως φόρμουλα Array. Τώρα μπορώ να κάνω διπλό κλικ και να το στείλω. Αυτό φαίνεται καλό.
Θα πάω στο τελευταίο κελί και θα πατήσω το F2 για να επαληθεύσω ότι όλα τα εύρη φαίνονται σωστά. Τώρα αυτό που δεν θέλω να κάνω είναι ότι δεν θέλω να πατήσω το Enter, επειδή αυτός ο τύπος αφού το βάλουμε στη λειτουργία επεξεργασίας θα υπολογίσει σωστά μόνο εάν χρησιμοποιούμε Ctrl + Shift + Enter. ή, επειδή έχουμε ήδη εισαγάγει τον τύπο, μπορούμε απλά να χρησιμοποιήσουμε το πλήκτρο Esc για να επιστρέψουμε σε ό, τι υπάρχει στο κελί πριν το βάλουμε στη λειτουργία επεξεργασίας.
Τώρα, ας το δοκιμάσουμε. Πάω να κάνω κλικ στο τελευταίο κελί εδώ και να χτυπήσω το Tab και μετά να πληκτρολογήσω ένα νέο αναγνωριστικό, Tab, Tab. Ένας άλλος νέος δίσκος, ο Tab, και μπορώ ήδη να δω ότι δεν είχα αρκετή δουλειά εδώ. Είμαι, πρόκειται να βάλουμε - Τέλεια και στη συνέχεια Enter. Τώρα, αυτό δεν πρόκειται να ενημερωθεί αυτόματα, όπως εάν έχουμε μια σειρά από τύπους που μετρούν μοναδικά στοιχεία και στη συνέχεια εξάγουμε μοναδικά στοιχεία, αλλά δεν υπάρχει πρόβλημα. Παρακολουθήσουν αυτό. Μπορούμε να ενημερώσουμε αυτήν τη λίστα με μοναδικές εγγραφές επειδή χρησιμοποιήσαμε το Advanced Filter και δεν έχει σημασία από το κελί από το οποίο ξεκινάτε, είτε επειδή καλείται το Advanced Filter, απομνημονεύει το εύρος εξαγωγής και τα εύρη που αρχικά εξέταζε. Μπορείτε να κάνετε κλικ στο Advanced Filter ή να χρησιμοποιήσετε το πληκτρολόγιο Alt + A + Q. Πρέπει να επιλέξουμε Αντιγραφή σε άλλη τοποθεσία, αλλά να το δούμε.Θυμήθηκε εντελώς και επεκτάθηκε σε A13 λόγω της δυνατότητας του πίνακα Excel. Θυμήθηκε το εύρος εκχυλισμάτων. Πρέπει να ελέγξω μόνο τις μοναδικές εγγραφές αλλά κάντε κλικ στο OK.
Now, I have to come over and copy this formula down. And there you go, using Advanced Filter and the amazing TEXTJOIN function with, in Array operation to get just the items that match. Alright, throw back to.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Ω, γεια, θέλω να ευχαριστήσω όλους που σταμάτησαν Θα σας δούμε την επόμενη φορά για ένα άλλο podcast Dueling Excel και το Excel Is Fun.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Duel183.xlsm
