Kunnen we meerdere waarden opzoeken en retourneren in één cel in Excel (gescheiden door komma of spatie)?
Deze vraag is mij meerdere keren gesteld door veel van mijn collega's en lezers.
Excel heeft een aantal fantastische opzoekformules, zoals VERT.ZOEKEN, INDEX/VERGELIJKEN (en nu XLZOEKEN), maar geen van deze biedt een manier om meerdere overeenkomende waarden te retourneren. Al deze werken door de eerste match te identificeren en deze terug te sturen.
Dus ik deed wat VBA-codering om een aangepaste functie (ook wel een door de gebruiker gedefinieerde functie genoemd) in Excel te bedenken.
Bijwerken: Nadat Excel dynamische arrays en geweldige functies zoals UNIQUE en TEXTJOIN had uitgebracht, is het nu mogelijk om een eenvoudige formule te gebruiken en retourneer alle overeenkomende waarden in één cel (besproken in deze zelfstudie).In deze tutorial laat ik je zien hoe je dit doet (als je de nieuwste versie van Excel gebruikt - Microsoft 365 met alle nieuwe functies), en een manier om dit te doen als je oudere versies gebruikt ( met behulp van VBA).
Dus laten we beginnen!
Zoek en retourneer meerdere waarden in één cel (met formule)
Als u Excel 2016 of eerdere versies gebruikt, gaat u naar het volgende gedeelte waar ik laat zien hoe u dit kunt doen met VBA.
Met een Microsoft 365-abonnement heeft uw Excel nu veel krachtigere functies en functies die er niet zijn in eerdere versies (zoals XLOOKUP, Dynamic Arrays, UNIEKE/FILTER-functies, enz.)
Dus als u Microsoft 365 (voorheen bekend als Office 365) gebruikt, kunt u de methoden gebruiken die in deze sectie worden behandeld, zodat u meerdere waarden in één enkele cel in Excel kunt opzoeken en retourneren.
En zoals je zult zien, is het een heel eenvoudige formule.
Hieronder heb ik een dataset waar ik de namen van de mensen in kolom A heb staan en de training die ze hebben gevolgd in kolom B.
Klik hier om het voorbeeldbestand te downloaden en volg mee
Voor elke persoon wil ik weten welke opleiding ze hebben gevolgd. In kolom D heb ik de lijst met unieke namen (uit kolom A), en ik wil snel alle training opzoeken en extraheren die elke persoon heeft gedaan en deze in een enkele set krijgen (gescheiden door een komma).
Hieronder is de formule die dit zal doen:
=TEXTJOIN(", ",TRUE,IF(D2=$A$2:$A$20,$B$2:$B$20,""))
Nadat u de formule in cel E2 hebt ingevoerd, kopieert u deze voor alle cellen waar u de resultaten wilt hebben.
Hoe werkt deze formule?
Laat me deze formule deconstrueren en uitleggen hoe elk deel ons het resultaat geeft.
De logische test in de IF-formule (D2=$A$2:$A$20) controleert of de naamcel D2 dezelfde is als die in het bereik A2:A20.
Het gaat door elke cel in het bereik A2:A20 en controleert of de naam hetzelfde is in cel D2 of niet. als het dezelfde naam is, wordt TRUE geretourneerd, anders wordt FALSE geretourneerd.
Dus dit deel van de formule geeft je een array zoals hieronder weergegeven:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Omdat we alleen de training voor Bob willen krijgen (de waarde in cel D2), moeten we alle bijbehorende training krijgen voor de cellen die WAAR retourneren in de bovenstaande array.
Dit is eenvoudig te doen door [value_if_true] deel van de IF-formule op te geven als het bereik dat de training heeft. Dit zorgt ervoor dat als de naam in cel D2 overeenkomt met de naam in het bereik A2:A20, de IF-formule alle training die die persoon heeft gevolgd, retourneert.
En waar de array een FALSE retourneert, hebben we de waarde [valu_if_false] gespecificeerd als "" (leeg), dus het retourneert een spatie.
Het IF-gedeelte van de formule retourneert de matrix zoals hieronder weergegeven:
{“Excel”;””;””;”PowerPoint”;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””}
Waar het de namen heeft van de training die Bob heeft gevolgd en spaties waar de naam niet Bob was.
Nu hoeven we alleen deze trainingsnaam (gescheiden door een komma) te combineren en in één cel terug te geven.
En dat kan eenvoudig met de nieuwe TEXTJOIN-formule (beschikbaar in Excel2021-2022 en Excel in Microsoft 365)
De TEXTJOIN-formule heeft drie argumenten:
- het scheidingsteken - wat in ons voorbeeld "," is, omdat ik wil dat de training wordt gescheiden door een komma en een spatie
- TRUE - die de TEXTJOIN-formule vertelt om lege cellen te negeren en alleen cellen te combineren die niet leeg zijn
- De If-formule die de tekst retourneert die moet worden gecombineerd
Als u Excel gebruikt in Microsoft 365 dat al dynamische matrices heeft, kunt u gewoon de bovenstaande formule invoeren en op Enter drukken. En als u Excel2021-2022 gebruikt, moet u de formule invoeren en de Control- en de Shift-toets ingedrukt houden en vervolgens op Enter drukken
Klik hier om het voorbeeldbestand te downloaden en volg mee
Krijg meerdere opzoekwaarden in een enkele cel (zonder herhaling)
Aangezien de UNIEKE formule alleen beschikbaar is voor Excel in Microsoft 365, kunt u deze methode niet gebruiken in Excel2021-2022Als er herhalingen in uw gegevensset zijn, zoals hieronder weergegeven, moet u de formule een klein beetje wijzigen, zodat u alleen een lijst met unieke waarden in één cel krijgt.
In de bovenstaande dataset hebben sommige mensen meerdere keren een training gevolgd. Zo hebben Bob en Stan twee keer de Excel-training gevolgd en heeft Betty twee keer de MS Word-training gedaan. Maar in ons resultaat willen we geen herhaling van een trainingsnaam hebben.
U kunt hiervoor de onderstaande formule gebruiken:
=TEXTJOIN(", ",TRUE,UNIEK(IF(D2=$A$2:$A$20,$B$2:$B$20,"")))
De bovenstaande formule werkt op dezelfde manier, met een kleine wijziging. we hebben de ALS-formule gebruikt in de UNIQUE-functie, zodat in het geval dat er herhalingen zijn in het resultaat van de if-formule, de UNIQUE-functie deze zou verwijderen.
Klik hier om het voorbeeldbestand te downloaden
Zoek en retourneer meerdere waarden in één cel (met VBA)
Als u Excel 2016 of eerdere versies gebruikt, heeft u geen toegang tot de TEXTJOIN-formule. Dus de beste manier om vervolgens meerdere overeenkomende waarden in één cel op te zoeken en te krijgen, is door een aangepaste formule te gebruiken die u kunt maken met VBA.Om meerdere opzoekwaarden in een enkele cel te krijgen, moeten we een functie in VBA maken (vergelijkbaar met de functie VERT.ZOEKEN) die elke cel in een kolom controleert en als de opzoekwaarde wordt gevonden, deze aan het resultaat toevoegt.
Hier is de VBA-code die dit kan doen:
'Code by Sumit Bansal (https://trumpexcel.com) Functie SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Resultaat As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Left(Result, Len(Result) - 1) Functie beëindigen
Waar plaats je deze code?
- Open een werkmap en klik op Alt + F11 (hiermee wordt het VBA-editorvenster geopend).
- In dit VBA-editorvenster, aan de linkerkant, is er een projectverkenner (waar alle werkmappen en werkbladen worden vermeld). Klik met de rechtermuisknop op een willekeurig object in de werkmap waar u deze code wilt laten werken en ga naar Invoegen -> Module.
- Kopieer en plak de bovenstaande code in het modulevenster (dat aan de rechterkant verschijnt).
- Nu ben je helemaal klaar. Ga naar een willekeurige cel in de werkmap en typ =Enkelcelextract en sluit de vereiste invoerargumenten aan (d.w.z. LookupValue, LookupRange, ColumnNumber).
Hoe werkt deze formule?
Deze functie werkt op dezelfde manier als de functie VERT.ZOEKEN.
Er zijn 3 argumenten als invoer nodig:
1. Opzoekwaarde - Een tekenreeks die we moeten opzoeken in een reeks cellen.
2. Zoekbereik - Een array van cellen waaruit we de gegevens moeten halen ($B3:$C18 in dit geval).
3. Kolomnummer - Het is het kolomnummer van de tabel/array waaruit de overeenkomende waarde moet worden geretourneerd (2 in dit geval).
Wanneer u deze formule gebruikt, controleert deze elke cel in de meest linkse kolom in het opzoekbereik en wanneer het een overeenkomst vindt, wordt het toegevoegd aan het resultaat in de cel waarin u de formule hebt gebruikt.
Herinneren: Sla de werkmap op als een werkmap met macro's (.xlsm of .xls) om deze formule opnieuw te gebruiken. Deze functie zou ook alleen beschikbaar zijn in deze werkmap en niet in alle werkmappen.
Klik hier om het voorbeeldbestand te downloaden
Leer hoe u saaie repetitieve taken kunt automatiseren met VBA in Excel. Sluit je aan bij de Excel VBA-cursusKrijg meerdere opzoekwaarden in een enkele cel (zonder herhaling)
Het is mogelijk dat u herhalingen in de gegevens heeft.
Als u de hierboven gebruikte code gebruikt, krijgt u ook herhalingen in het resultaat.
Als u het resultaat wilt krijgen zonder herhalingen, moet u de code een beetje aanpassen.
Hier is de VBA-code die u meerdere opzoekwaarden in één cel geeft zonder herhalingen.
'Code by Sumit Bansal (https://trumpexcel.com) Functie MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .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) Dan GoTo Skip End If End If Next J Resultaat = Resultaat & " " & LookupRange.Cells(i, ColumnNumber) & "," Overslaan: End If Next i MultipleLookupNoRept = Left(Result, Len(Result) - 1) End Functie
Zodra je deze code in de VB Editor hebt geplaatst (zoals hierboven in de tutorial getoond), kun je de MeerdereLookupNoRept functie.
Hier is een momentopname van het resultaat dat u hiermee krijgt MeerdereLookupNoRept functie.
Klik hier om het voorbeeldbestand te downloaden
In deze zelfstudie heb ik besproken hoe u formules en VBA in Excel kunt gebruiken om meerdere opzoekwaarden in één cel in Excel te vinden en terug te geven.
Hoewel het gemakkelijk kan worden gedaan met een eenvoudige formule als u Excel gebruikt in een Microsoft 365-abonnement, als u eerdere versies gebruikt en geen toegang hebt tot functies zoals TEXTJOIN, kunt u dit nog steeds doen met VBA door uw eigen aangepaste functie.
