Få flere opslagsværdier i en enkelt celle (uden og uden gentagelse)

Kan vi slå op og returnere flere værdier i en celle i Excel (adskilt med komma eller mellemrum)?

Jeg er blevet stillet dette spørgsmål flere gange af mange af mine kolleger og læsere.

Excel har nogle fantastiske opslagsformler, såsom VLOOKUP, INDEX/MATCH (og nu XLOOKUP), men ingen af ​​disse tilbyder en måde at returnere flere matchende værdier. Alle disse virker ved at identificere det første match og returnere det.

Så jeg lavede lidt VBA -kodning for at komme med en brugerdefineret funktion (også kaldet en brugerdefineret funktion) i Excel.

Opdatering: Efter at Excel har frigivet dynamiske arrays og fantastiske funktioner som UNIKT og TEXTJOIN, er det nu muligt at bruge en simpel formel og returnere alle matchende værdier i en celle (dækket i denne vejledning).

I denne vejledning viser jeg dig, hvordan du gør dette (hvis du bruger den nyeste version af Excel - Microsoft 365 med alle de nye funktioner), samt en måde at gøre dette på, hvis du bruger ældre versioner ( ved hjælp af VBA).

Så lad os komme i gang!

Opslag og returnering af flere værdier i en celle (ved hjælp af formel)

Hvis du bruger Excel 2016 eller tidligere versioner, skal du gå til det næste afsnit, hvor jeg viser, hvordan du gør dette ved hjælp af VBA.

Med Microsoft 365 -abonnement har din Excel nu mange mere kraftfulde funktioner og funktioner, der ikke er der i tidligere versioner (f.eks. XLOOKUP, dynamiske arrays, UNIQUE/FILTER -funktioner osv.)

Så hvis du bruger Microsoft 365 (tidligere kendt som Office 365), kan du bruge metoderne i dette afsnit til at slå op og returnere flere værdier i en enkelt celle i Excel.

Og som du vil se, er det en virkelig enkel formel.

Nedenfor har jeg et datasæt, hvor jeg har navnene på personerne i kolonne A og den træning, de har taget i kolonne B.

Klik her for at downloade eksempelfilen og følge med

For hver person vil jeg finde ud af, hvilken uddannelse de har gennemført. I kolonne D har jeg listen over unikke navne (fra kolonne A), og jeg vil hurtigt slå op og udtrække al den træning, som hver person har gennemført og få disse i et enkelt sæt (adskilt med et komma).

Nedenfor er formlen, der gør dette:

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

Når du har indtastet formlen i celle E2, skal du kopiere den for alle de celler, hvor du vil have resultaterne.

Hvordan fungerer denne formel?

Lad mig dekonstruere denne formel og forklare hver del i, hvordan den kommer sammen, giver os resultatet.

Den logiske test i IF -formlen (D2 = $ A $ 2: $ A $ 20) kontrollerer, om navnecellen D2 er den samme som i området A2: A20.

Den går gennem hver celle i området A2: A20 og kontrollerer, om navnet er det samme i celle D2 eller ej. hvis det er det samme navn, returnerer det SANDT, ellers returnerer det FALSKT.

Så denne del af formlen giver dig en matrix som vist nedenfor:

{SAND; FALSK; FALSK; SAND; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FEJLIG

Da vi kun ønsker at få træningen til Bob (værdien i celle D2), har vi brug for at få al den tilsvarende træning for de celler, der returnerer SAND i ovenstående array.

Dette gøres let ved at angive [value_if_true] en del af IF -formlen som det område, der har træningen. Dette sikrer, at hvis navnet i celle D2 matcher navnet i området A2: A20, vil IF -formlen returnere al den træning, som personen har taget.

Og hvor arrayet returnerer en FALSK, har vi angivet [value_if_false] værdien som “” (blank), så den returnerer en blank.

IF -delen af ​​formlen returnerer arrayet som vist herunder:

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

Hvor det har navnene på den træning, Bob har taget og blanks, uanset hvor navnet ikke var Bob.

Nu skal vi bare kombinere dette træningsnavn (adskilt med et komma) og returnere det i en celle.

Og det kan let gøres ved hjælp af den nye TEXTJOIN-formel (tilgængelig i Excel2021-2022 og Excel i Microsoft 365)

TEXTJOIN -formlen tager tre argumenter:

• afgrænseren - som er “,” i vores eksempel, da jeg vil have træningen adskilt af et komma og et mellemrum
• TRUE - som fortæller TEXTJOIN -formlen at ignorere tomme celler og kun kombinere dem, der ikke er tomme
• If -formlen, der returnerer den tekst, der skal kombineres

Hvis du bruger Excel i Microsoft 365, der allerede har dynamiske arrays, kan du bare indtaste ovenstående formel og trykke på enter. Og hvis du bruger Excel2021-2022, skal du indtaste formlen og holde Ctrl og Shift-tasten nede og derefter trykke på Enter

Klik her for at downloade eksempelfilen og følge med

Få flere opslagsværdier i en enkelt celle (uden gentagelse)

Da den UNIKE formel kun er tilgængelig fra Excel i Microsoft 365, kan du ikke bruge denne metode i Excel2021-2022

Hvis der er gentagelser i dit datasæt, som vist nedenfor, skal du ændre formlen en lille smule, så du kun får en liste over unikke værdier i en enkelt celle.

I ovenstående datasæt har nogle mennesker taget træning flere gange. For eksempel har Bob og Stan taget Excel -træningen to gange, og Betty har taget MS Word -træning to gange. Men i vores resultat ønsker vi ikke at få et træningsnavn gentaget.

Du kan bruge nedenstående formel til at gøre dette:

= TEKSTJOIN (",", SAND, UNIK (HVIS (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

Ovenstående formel fungerer på samme måde med en mindre ændring. vi har brugt IF -formlen inden for funktionen UNIK, så hvis der er gentagelser i if -formelresultatet, ville UNIQUE -funktionen fjerne den.

Klik her for at downloade eksempelfilen

Slå op og returner flere værdier i en celle (ved hjælp af VBA)

Hvis du bruger Excel 2016 eller tidligere versioner, har du ikke adgang til TEXTJOIN -formlen. Så den bedste måde derefter at slå op og få flere matchende værdier i en enkelt celle er ved at bruge en brugerdefineret formel, som du kan oprette ved hjælp af VBA.

For at få flere opslagsværdier i en enkelt celle skal vi oprette en funktion i VBA (svarende til VLOOKUP -funktionen), der kontrollerer hver celle i en kolonne, og hvis opslagsværdien findes, føjes den til resultatet.

Her er VBA -koden, der kan gøre dette:

'Code by Sumit Bansal (https://trumpexcel.com) Function 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) .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) Afslut funktion

Hvor placeres denne kode?

1. Åbn en projektmappe, og klik på Alt + F11 (dette åbner vinduet VBA Editor).
2. I dette VBA Editor -vindue til venstre er der en projektudforsker (hvor alle projektmapper og regneark er angivet). Højreklik på et objekt i projektmappen, hvor du vil have denne kode til at fungere, og gå til Indsæt -> Modul.
3. I modulvinduet (der vises til højre), kopier og indsæt ovenstående kode.
4. Nu er du klar. Gå til en hvilken som helst celle i projektmappen, og skriv = SingleCellExtract og tilslut de nødvendige inputargumenter (dvs. LookupValue, LookupRange, ColumnNumber).

Hvordan fungerer denne formel?

Denne funktion fungerer på samme måde som VLOOKUP -funktionen.

Det tager 3 argumenter som input:

1. Opslagværdi - En streng, som vi skal slå op i en række celler.
2. OpslagRange - En række celler, hvorfra vi skal hente dataene ($ B3: $ C18 i dette tilfælde).
3. KolonneNummer - Det er kolonnenummeret i tabellen/arrayet, hvorfra matchningsværdien skal returneres (2 i dette tilfælde).

Når du bruger denne formel, kontrollerer den hver celle i kolonnen længst til venstre i opslagsområdet og når den finder et match, føjes det til resultatet i cellen, hvor du har brugt formlen.

Husk: Gem projektmappen som en makroaktiveret projektmappe (.xlsm eller .xls) for at genbruge denne formel igen. Denne funktion ville også kun være tilgængelig i denne projektmappe og ikke i alle projektmapper.

Klik her for at downloade eksempelfilen

Lær, hvordan du automatiserer kedelige gentagne opgaver med VBA i Excel. Deltag i Excel VBA kursus

Få flere opslagsværdier i en enkelt celle (uden gentagelse)

Der er en mulighed for, at du kan have gentagelser i dataene.

Hvis du bruger ovenstående kode, giver det dig også gentagelser i resultatet.

Hvis du vil have resultatet, hvor der ikke er gentagelser, skal du ændre koden lidt.

Her er VBA -koden, der giver dig flere opslagsværdier i en enkelt celle uden gentagelser.

'Code by Sumit Bansal (https://trumpexcel.com) Function MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Som 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) = LookupRevue.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Gå derefter til Spring over ende hvis ende hvis næste J resultat = resultat & "" & LookupRange.Cells (i, kolumnummer) & "," Spring: afslut hvis næste i MultipleLookupNoRept = venstre (resultat, Len (resultat) - 1) slut Fungere

Når du har placeret denne kode i VB Editor (som vist ovenfor i selvstudiet), vil du kunne bruge MultipleLookupNoRept fungere.

Her er et øjebliksbillede af resultatet, du får med dette MultipleLookupNoRept fungere.

Klik her for at downloade eksempelfilen

I denne vejledning dækkede jeg, hvordan man bruger formler og VBA i Excel til at finde og returnere flere opslagsværdier i en celle i Excel.

Selvom det let kan gøres med en simpel formel, hvis du bruger Excel i Microsoft 365 -abonnement, kan du stadig gøre dette ved hjælp af VBA ved at oprette dine egen brugerdefinerede funktion.