Kann Excel VLOOKUP alle Ergebnisse zurückgeben und dazwischen ein Komma einfügen?
Schau Video
- Ziel ist es, alle Textantworten eines VLOOKUP zu verketten
- Bills Methode: Verwenden Sie eine VBA-Funktion namens GetAll
- Eindeutige Liste mit Duplikate entfernen
- Mikes Methode:
- Eindeutige Liste mit erweitertem Filter
- TEXTJOIN-Funktion in Office 365 hinzugefügt
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Aufgrund der IF-Funktion erfordert die Formel bei jeder Bearbeitung der Formel Strg + Umschalt + Eingabetaste
- Alt AQOR Enter führt den erweiterten Filter erneut aus!
Video-Transkript
Folge 183: Nimm an allen VLOOKUP-Spielen teil
Bill Jelen: Hey, willkommen zurück. Es ist Zeit für einen weiteren Duell-Excel-Podcast. Ich bin Bill Jelen von, ich werde von Mike Girvin für Excel Is Fun begleitet. Dies ist unsere Episode 183: An allen VLOOKUP-Spielen teilnehmen.
(Musik)
Okay, die heutige Frage von Matt. Kann VLOOKUP alle Ergebnisse zurückgeben und sie mit einem Komma dazwischen verbinden? Zum Beispiel, 109876, die diese beiden hier sind, kann es Komma-Leerzeichen zurückgeben. Überprüft am 12/12. Und wenn es mehr gäbe, würde es natürlich mehr zurückkehren. Okay, meine Lösung hier wird also VBA verwenden. Okay, stellen Sie also sicher, dass es als xlsm gespeichert ist oder Sie VBA oder xlsb nicht ausführen können, aber nicht xlsx - xlsx ist die einzige Datei, die VBA nicht ausführen kann. Wir drücken Alt + F11 und stellen sicher, dass Sie Dual183 verwenden oder wie auch immer der Name Ihrer Arbeitsmappe lautet. Fügen Sie das Modul in das leere Modul ein und wir werden diesen Code einfügen, in Ordnung.
Werfen wir einen Blick auf diese Funktion GetAll. Hier ist die ID-Nummer, nach der wir suchen, und dann der Bereich, den wir suchen möchten. Und wir fangen an, wir werden eine Variable namens GetAll zurückgeben, also beginnen wir damit, dass diese gleich leer ist. Wenn für jede Zelle in meinem Bereich der Zellenwert das ist, wonach wir suchen, nehmen wir GetAll = GetAll & "" und dann das Cell.Offset (0 Zeilen, 1 Spalte), mit anderen Worten den Wert Das ist direkt neben dieser ID-Nummer, denn hier in VBA ist hier die ID-Nummer. Wenn wir die passende ID-Nummer finden, wollen wir 1 Spalte darüber gehen. Was ist, wenn Sie 2 Spalten oder 3 Spalten überschreiten möchten? Dann ändern Sie diese 0 Zeilen und 1 Spalte in eine 2. Okay, überprüfen Sie auch, ob - wir setzen kein Komma, wenn dies der Fall ist der erste.Wenn die GetAll-Variable derzeit "" ist, werden wir das Komma-Leerzeichen nicht einfügen, okay?
Nachdem wir diese Funktion hier haben, beobachten Sie, wie einfach es ist, Matts Problem zu lösen. Wir werden hierher kommen und seine IDs Strg + C nehmen und Strg + V so einfügen. Daten, Duplikate entfernen, klicken Sie auf OK. Es gibt also eine eindeutige Liste von IDs, und dann möchten wir = getall sagen und suchen diesen Wert in E2-Komma. Wenn ich hier durch diesen Bereich schaue, drücke ich F4. F4 funktioniert wie eine reguläre Funktion. Und wenn Sie Matts Frage wieder aus dem Weg räumen, doppelklicken Sie, um sie abzuschießen. Es wird klappen.
Und versuchen wir es einfach, versuchen wir hier etwas Verrücktes. Lassen Sie uns eine Phrase 1 machen und einfach ein paar davon wie Phrase 1 bis 10 einfügen. Wir werden alle diese bis 109999 unterschreiben. Einfügen und dann hier einfügen. Kopieren Sie diese Formel nach unten und bearbeiten Sie sie so, dass sie natürlich ganz nach unten geht. Jawohl. Und es wird all diese Sätze zurückgeben. Okay, das ist meine Lösung, VBA, eine kleine Funktion dort. Mike, mal sehen, was du hast.
Mike Girvin: Danke. GetAll, das ist eine großartige VBA-Funktion. Okay, ich gehe gleich hier zum Blatt. Ich habe es bereits in eine Excel-Tabelle konvertiert, sodass die Dinge hoffentlich aktualisiert werden, wenn wir unten Datensätze hinzufügen.
Jetzt mache ich das als erstes in zwei Teilen. Ich könnte hier eine Formel zum Extrahieren einer eindeutigen Liste erstellen, möchte jedoch eine andere Option betrachten: Der erweiterte Filter verfügt über eine Option zum Extrahieren einer eindeutigen Liste und kann aktualisiert werden. Ich werde nur die ID-Spaltendaten hervorheben, bis hin zum erweiterten Filter, oder ich werde die Tastatur Alt, A, Q verwenden. Jetzt ist die Filterliste auf keinen Fall vorhanden. Ich möchte es an einen anderen Ort kopieren. Es hat nur die A-Spalte und weil es eine Excel-Tabelle ist, die später erweitert wird. Ich habe keine Kriterien, ich möchte es nach D1 kopieren und nur eindeutige Datensätze überprüfen. OK klicken.
Jetzt komme ich hierher, Alle Kommentare werden eingegeben und ich werde eine Funktion verwenden, die nur in Excel 2016 Office 365 funktioniert: = TEXTJOIN-Funktion. Allein diese Funktion ist es wert, die neueste Version von Excel zu erhalten. Dies ist eine so häufige Aufgabe, dass Menschen viele Dinge zusammenfügen wollen. Jetzt ist unser Trennzeichen in ",", und das Tolle an dieser Funktion ist, dass wir sie anweisen können, leere Zellen zu ignorieren. Jetzt kann ich TRUE setzen, 1 oder Leave it, Omit it. Also werde ich es verlassen, es weglassen. Und hier brauchen wir unseren Text. Wir werden die IF-Funktion verwenden, um genau die gewünschten Elemente herauszufiltern und abzurufen. Ich werde hier die gesamte Spalte durchsehen: Tabellenname und dann in () den Feldnamen, sind alle von Ihnen = auf diese relative Zellreferenz, das ist der logische Test. Wenn ich darauf klicken und die F9-Taste drücken würde, um zu bewerten,Sie konnten jetzt sehen, dass wir nur 2 WAHRHEITEN haben, Strg + Z, jetzt tippe ich ein Komma und mit der Reihe von Wahrheiten und Falschheiten kann ich ihm jetzt die Gegenstände zum Heraussuchen geben. Jetzt werden wir nur die Gegenstände aus diesem Bereich auswählen, die hier eine WAHRHEIT haben. Komma und ich möchten sicherstellen, dass "" gesetzt wird - dies wird als leere Zelle in Bezug auf das zweite Argument in TEXTJOIN angezeigt.
Now, I'm going to close parenthesis and now the IF function will create that string of Trues and Falses, the actual items from this range will be picked up if it sees it True and all the other items will have that empty cell. And guess what? TEXTJOIN will totally ignore all of those empty cells and return just the items that match this ID, and then join it with that delimiter. Now this is definitely an Array formula that requires the special keystroke Ctrol+Shift+Enter. The logical test argument holds our Array operation and that argument cannot calculate this Array operation correctly unless we use the keyboard Ctrl+Shift+Enter. Now I'm going to close parentheses. Actually we could prove 1 right here in Text 1 if I F9 all this, we could see we get the 2 items, the rest of those empty cells will be ignored. Ctrl+Z. Now, let's enter this into the cell with Ctrl+Shift+Enter. Immediately look up to the Formula Bar. Those curly brackets are Excel telling you it understood and calculated this as an Array formula. Now I can double-click and send it down. That is looking good.
I'm going to go to the last cell and hit F2 to verify that all the ranges are looking correctly. Now what I don't want to do is I don't want to hit Enter because that formula after we put it in Edit Mode will only calculate correctly if we use Ctrl+Shift+Enter; or, because we already entered the formula, we can just use the Esc key to revert back to whatever's in the cell before we put it in Edit Mode.
Lassen Sie uns dies jetzt testen. Ich werde in die letzte Zelle hier unten klicken und Tab drücken und dann eine neue ID, Tab, Tab eingeben. Ein weiterer neuer Rekord, Tab, und ich kann bereits sehen, dass ich hier nicht genug Arbeit hatte. Ich bin, wir werden setzen - Perfekt und dann eintreten. Dies wird nicht automatisch aktualisiert, wenn wir eine Reihe von Formeln haben, mit denen wir eindeutige Elemente zählen und dann eindeutige Elemente extrahieren, aber kein Problem. Schau dir das an. Wir können diese Liste eindeutiger Datensätze aktualisieren, da wir den erweiterten Filter verwendet haben und es keine Rolle spielt, von welcher Zelle Sie ausgehen, da beim Aufrufen des erweiterten Filters der Extraktbereich und die Bereiche gespeichert werden, die ursprünglich betrachtet wurden. Sie können auf Erweiterter Filter klicken oder die Tastatur Alt + A + Q verwenden. Wir müssen Kopieren an einen anderen Ort auswählen, aber sehen Sie sich das an.Aufgrund der Excel-Tabellenfunktion wurde es vollständig gespeichert und auf A13 erweitert. Es erinnerte sich an den Extraktbereich. Ich muss nur eindeutige Datensätze überprüfen, aber auf OK klicken.
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.
Oh hey, ich möchte allen dafür danken, dass sie vorbeigekommen sind. Wir sehen uns beim nächsten Mal für einen weiteren Duell-Excel-Podcast von und Excel macht Spaß.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Duel183.xlsm
