Kan Excel VERT.ZOEKEN alle resultaten retourneren en ze samenvoegen met een komma ertussen?
Bekijk video
- Het doel is om alle tekstantwoorden van VERT.ZOEKEN samen te voegen
- Bill's methode: gebruik een VBA-functie genaamd GetAll
- Unieke lijst met behulp van Duplicaten verwijderen
- Mike's methode:
- Unieke lijst met behulp van geavanceerde filter
- TEXTJOIN-functie toegevoegd in Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Vanwege de ALS-functie vereist de formule Ctrl + Shift + Enter telkens wanneer u de formule bewerkt
- Alt AQOR Enter zal het geavanceerde filter opnieuw uitvoeren!
Videotranscriptie
Aflevering 183: Doe mee met alle VLOOKUP-wedstrijden
Bill Jelen: Hey, welkom terug. Het is tijd voor nog een Dueling Excel Podcast. Ik ben Bill Jelen van, ik krijg gezelschap van Mike Girvin voor Excel Is Fun. Dit is onze aflevering 183: Doe mee met alle VLOOKUP-wedstrijden.
(Muziek)
Oké, de vraag van vandaag van Matt. Kan VERT.ZOEKEN alle resultaten retourneren en ze samenvoegen met een komma tussen beide. Bijvoorbeeld, 109876, wat deze twee hier zijn, kan het de komma-ruimte Laag oliepeil Gecontroleerd op 12/12 retourneren. En als er meer waren, zou het natuurlijk meer teruggeven. Oké, dus mijn oplossing hier gaat wat VBA gebruiken. Oké, dus zorg ervoor dat het is opgeslagen als xlsm, anders kun je geen VBA of xlsb uitvoeren, maar niet xlsx - xlsx is het enige bestand dat geen VBA kan uitvoeren. We drukken op Alt + F11, zorg ervoor dat u Dual183 gebruikt of wat de naam van uw werkmap ook is. Voeg module in de lege module in en we gaan deze code plakken, oké.
Laten we deze functie GetAll eens bekijken, en hier is het ID-nummer dat we zoeken en dan het bereik dat we willen zoeken. En we beginnen met het retourneren van een variabele met de naam GetAll, dus we beginnen met dat gelijk aan blanco blanco. Voor elke cel in mijn bereik, als de celwaarde is waarnaar we op zoek zijn, nemen we GetAll = GetAll & "" en vervolgens de Cell.Offset (0 rijen, 1 kolom), met andere woorden de waarde dat is net naast dat ID-nummer, want terug in VBA is hier het ID-nummer. Als we het overeenkomende ID-nummer vinden, willen we er 1 kolom overheen gaan. Nu, wat als je 2 kolommen over of 3 kolommen wilt gaan, dan verander je deze 0 rijen en 1 kolom in een 2. Oké, controleer ook of - we plaatsen geen komma spatie als dit is de eerste.Dus als de GetAll-variabele momenteel "" is, zullen we de komma-spatie niet plaatsen, oké?
Dus nu we deze functie hier hebben, kijk hoe gemakkelijk dit is om Matt's probleem op te lossen. We komen hierheen en laten we zijn ID's nemen, Ctrl + C en Ctrl + V zo plakken. Gegevens, Duplicaten verwijderen, klik op OK. Er is dus een unieke lijst met ID's en dan willen we zeggen = getall en we zoeken naar die waarde in E2 komma. Als ik hier door dit bereik kijk, druk ik op F4. F4 werkt net als een normale functie. En nogmaals Matts vraag uit de weg schuiven, dubbelklik om die neer te schieten. Het zal werken.
En laten we het gewoon proberen, laten we hier iets geks proberen. Laten we een zin 1 doen en er gewoon een aantal plaatsen, zoals zin 1 tot en met 10. We ondertekenen ze allemaal met 109999. Plakken, en dan hier plakken. Kopieer die formule naar beneden, bewerk de formule zodat deze natuurlijk helemaal naar beneden gaat. JEP. En het zal al die zinnen teruggeven. Oké, dus dat is mijn oplossing, VBA, een kleine functie daar. Mike, laten we eens kijken wat je hebt.
Mike Girvin: Bedankt ,. GetAll, dat is een geweldige VBA-functie. Oké, ik ga hier naar het blad. Ik heb het al geconverteerd naar een Excel-tabel, zodat wanneer we onderstaande records toevoegen, de dingen hopelijk worden bijgewerkt.
Nu, het eerste dat ik ga doen in twee delen. Ik zou hier een formule kunnen doen voor het extraheren van een unieke lijst, maar ik wil naar een andere optie kijken: Geavanceerd filter heeft een optie voor een unieke lijst uitpakken en deze kan worden bijgewerkt. Ik ga alleen de ID-kolomgegevens markeren, naar Geavanceerd filter of ik ga het toetsenbord gebruiken Alt, A, Q. Nu, filterlijst op zijn plaats, absoluut niet. Ik wil het naar een andere locatie kopiëren. Het kreeg alleen de A-kolom en omdat het een Excel-tabel is die later zal worden uitgebreid. Ik heb geen criteria, ik wil het kopiëren naar D1 en alleen Unieke records aanvinken. Klik OK.
Nu ga ik hierheen komen, Alle opmerkingen invoeren en ik ga een functie gebruiken die alleen werkt in Excel 2016 Office 365: = TEXTJOIN-functie. Alleen al deze functie is de moeite waard om de nieuwste versie van Excel te downloaden. Dit is zo'n veel voorkomende taak die mensen willen doen, veel dingen samen willen doen. Nu is ons scheidingsteken in ",", en het mooie van deze functie is dat we het kunnen vertellen om lege cellen te negeren. Nu kan ik WAAR, 1 plaatsen of Laat het weg, laat het weg. Dus ik ga het laten, weglaten. En hier hebben we onze tekst nodig. We gaan de ALS-functie gebruiken om eruit te filteren en alleen de items te krijgen die we willen. Ik ga zeggen: kijk hier door deze hele kolom: Tabelnaam en dan in () de Veldnaam, zijn een van jullie = naar deze relatieve celverwijzing, dat is de logische test. Als ik hierop zou klikken en op de F9-toets zou drukken om te evalueren,je kon nu zien dat we maar 2 TRUES hebben, Ctrl + Z nu typ ik een komma en met de array van Trues en Falses kan ik het nu de items geven om uit te kiezen. Dus nu zullen we alleen de items selecteren die hier een WAAR hebben uit deze reeks. Comma en ik willen er zeker van zijn dat "" - dat zal verschijnen als een lege cel met betrekking tot het tweede argument in TEXTJOIN.
Nu ga ik haakjes sluiten en nu zal de ALS-functie die reeks waar en onwaar maken, de feitelijke items uit dit bereik worden opgehaald als het ziet dat het waar is en alle andere items hebben die lege cel. En raad eens? TEXTJOIN negeert al die lege cellen volledig en retourneert alleen de items die overeenkomen met dit ID, en voegt het vervolgens samen met dat scheidingsteken. Dit is absoluut een matrixformule waarvoor de speciale toetsaanslag Ctrol + Shift + Enter vereist is. Het logische testargument bevat onze Array-bewerking en dat argument kan deze Array-bewerking niet correct berekenen, tenzij we het toetsenbord Ctrl + Shift + Enter gebruiken. Nu ga ik haakjes sluiten. Eigenlijk zouden we hier in tekst 1 1 kunnen bewijzen als ik dit alles F9, we zouden kunnen zien dat we de 2 items krijgen, de rest van die lege cellen wordt genegeerd. Ctrl + Z. Nu, laten 's voer dit in de cel in met Ctrl + Shift + Enter. Kijk onmiddellijk omhoog naar de formulebalk. Die accolades zijn Excel die u vertelt dat het dit heeft begrepen en berekend als een matrixformule. Nu kan ik dubbelklikken en het verzenden. Dat ziet er goed uit.
Ik ga naar de laatste cel en druk op F2 om te controleren of alle bereiken er correct uitzien. Wat ik nu niet wil doen, is dat ik niet op Enter wil drukken, omdat die formule nadat we deze in de bewerkingsmodus hebben geplaatst, alleen correct wordt berekend als we Ctrl + Shift + Enter gebruiken; of, omdat we de formule al hebben ingevoerd, kunnen we gewoon de Esc-toets gebruiken om terug te keren naar wat er in de cel staat voordat we deze in de bewerkingsmodus plaatsen.
Laten we dit nu testen. Ik ga in de laatste cel hier beneden klikken en op Tab drukken en vervolgens een nieuwe ID typen, Tab, Tab. Nog een nieuw record, Tab, en ik kan al zien dat ik hier niet genoeg werk had. Ik ben, we gaan zetten - Perfect en dan Enter. Dit wordt niet automatisch bijgewerkt, zoals als we een heleboel formules hebben waarin we unieke items tellen en vervolgens unieke items extraheren, maar dat is geen probleem. Kijk dit. We kunnen deze lijst met unieke records bijwerken omdat we Geavanceerd filter hebben gebruikt en het maakt ook niet uit vanuit welke cel u begint, want wanneer Geavanceerd filter wordt aangeroepen, onthoudt het het extractiebereik en de bereiken waarnaar het oorspronkelijk keek. U kunt op Geavanceerd filter klikken of het toetsenbord Alt + A + Q gebruiken. We moeten wel Kopiëren naar een andere locatie selecteren, maar kijk daar eens naar.Het werd volledig onthouden en uitgebreid naar A13 vanwege de Excel Table-functie. Het herinnerde zich het extractiebereik. Ik hoef alleen Unieke records te controleren, maar klik op OK.
Nu moet ik langskomen en deze formule kopiëren. En alsjeblieft, gebruikmakend van Advanced Filter en de verbazingwekkende TEXTJOIN-functie met, in Array-bewerking om alleen de items te krijgen die overeenkomen. Oké, gooi terug naar.
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, ik wil iedereen bedanken voor het langskomen. We zien je de volgende keer voor nog een Dueling Excel-podcast van en Excel is leuk.
Download bestand
Download het voorbeeldbestand hier: Duel183.xlsm
