Získejte více hodnot vyhledávání v jedné buňce (s opakováním i bez)

Můžeme vyhledat a vrátit více hodnot do jedné buňky v Excelu (oddělené čárkou nebo mezerou)?

Mnoho z mých kolegů a čtenářů mi tuto otázku položilo několikrát.

Excel má několik úžasných vyhledávacích vzorců, například VLOOKUP, INDEX/MATCH (a nyní XLOOKUP), ale žádný z nich nenabízí způsob, jak vrátit více odpovídajících hodnot. Všechny tyto funkce fungují tak, že identifikujete první shodu a vrátíte ji.

Tak jsem udělal trochu kódování VBA, abych přišel s vlastní funkcí (také nazývanou funkcí definovanou uživatelem) v aplikaci Excel.

Aktualizace: Poté, co Excel vydal dynamická pole a úžasné funkce, jako jsou UNIQUE a TEXTJOIN, je nyní možné použít jednoduchý vzorec a vrátit všechny odpovídající hodnoty do jedné buňky (popsáno v tomto tutoriálu).

V tomto tutoriálu vám ukážu, jak to udělat (pokud používáte nejnovější verzi Excelu - Microsoft 365 se všemi novými funkcemi), a také způsob, jak to udělat v případě, že používáte starší verze ( pomocí VBA).

Začněme tedy!

Vyhledat a vrátit více hodnot v jedné buňce (pomocí vzorce)

Pokud používáte Excel 2016 nebo předchozí verze, přejděte k další části, kde vám ukážu, jak to udělat pomocí VBA.

Díky předplatnému Microsoft 365 má váš Excel nyní mnohem výkonnější funkce a funkce, které v předchozích verzích nebyly (například funkce XLOOKUP, Dynamic Arrays, UNIQUE/FILTER atd.)

Pokud tedy používáte Microsoft 365 (dříve známý jako Office 365), můžete použít metody popsané v této části a vyhledat a vrátit více hodnot do jedné buňky v Excelu.

A jak uvidíte, je to opravdu jednoduchý vzorec.

Níže mám soubor dat, kde mám jména lidí ve sloupci A a školení, které absolvovali ve sloupci B.

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

U každého člověka chci zjistit, jaké školení absolvoval. Ve sloupci D mám seznam jedinečných jmen (ze sloupce A) a chci rychle vyhledat a extrahovat veškerá školení, která každý člověk absolvoval, a získat je v jedné sadě (oddělené čárkou).

Níže je uveden vzorec, který to provede:

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

Po zadání vzorce do buňky E2 jej zkopírujte pro všechny buňky, kde chcete výsledky.

Jak tento vzorec funguje?

Dovolte mi dekonstruovat tento vzorec a vysvětlit každou část toho, jak to dohromady dává výsledek.

Logický test ve vzorci IF (D2 = $ A $ 2: $ A $ 20) kontroluje, zda je buňka názvu D2 stejná jako v oblasti A2: A20.

Prochází každou buňkou v rozsahu A2: A20 a kontroluje, zda je název v buňce D2 stejný nebo ne. pokud je to stejné jméno, vrátí TRUE, jinak vrátí FALSE.

Tato část vzorce vám tedy poskytne pole, jak je uvedeno níže:

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Protože chceme získat pouze školení pro Boba (hodnota v buňce D2), potřebujeme získat všechny odpovídající tréninky pro buňky, které vracejí hodnotu TRUE ve výše uvedeném poli.

To lze snadno provést zadáním [value_if_true] části vzorce IF jako rozsahu, který má školení. Tím je zajištěno, že pokud se název v buňce D2 shoduje se jménem v rozsahu A2: A20, vzorec IF vrátí veškeré školení, které tato osoba absolvovala.

A všude tam, kde pole vrací FALSE, jsme zadali hodnotu [value_if_false] jako “” (prázdné), takže vrací prázdné místo.

IF část vzorce vrací pole, jak je uvedeno níže:

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

Tam, kde má názvy školení, si Bob vzal a prázdná místa, kde se nejmenovalo Bob.

Nyní vše, co musíme udělat, je spojit tyto názvy školení (oddělené čárkou) a vrátit je do jedné buňky.

A to lze snadno provést pomocí nového vzorce TEXTJOIN (k dispozici v Excelu2021-2022 a Excelu v Microsoft 365)

Vzorec TEXTJOIN přebírá tři argumenty:

• oddělovač - což je v našem případě „“, protože chci, aby byl trénink oddělen čárkou a mezerou
• TRUE - což říká vzorci TEXTJOIN, aby ignoroval prázdné buňky a kombinoval pouze ty, které nejsou prázdné
• Vzorec If, který vrací text, který je třeba zkombinovat

Pokud používáte Excel v Microsoft 365, který již má dynamická pole, stačí zadat výše uvedený vzorec a stisknout Enter. A pokud používáte Excel2021-2022, musíte zadat vzorec, podržet klávesu Control a Shift a poté stisknout Enter

Kliknutím sem stáhnete ukázkový soubor a budete pokračovat

Získejte více hodnot vyhledávání v jedné buňce (bez opakování)

Protože je UNIQUE vzorec k dispozici pouze pro Excel v Microsoft 365, nebudete tuto metodu moci použít v aplikaci Excel2021-2022

V případě, že ve vaší sadě dat dochází k opakování, jak je uvedeno níže, musíte vzorec trochu změnit, abyste získali seznam jedinečných hodnot pouze v jedné buňce.

Ve výše uvedené sadě dat absolvovali někteří lidé školení několikrát. Například Bob a Stan absolvovali dvakrát školení Excel a Betty dvakrát školení MS Word. Ale v našem výsledku nechceme, aby se jméno tréninku opakovalo.

K tomu můžete použít následující vzorec:

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

Výše uvedený vzorec funguje stejně, s menší změnou. použili jsme vzorec IF v rámci funkce UNIQUE, takže v případě, že ve výsledku if dojde k opakování, funkce UNIQUE ji odstraní.

Kliknutím sem stáhnete ukázkový soubor

Vyhledávání a návrat více hodnot do jedné buňky (pomocí VBA)

Pokud používáte Excel 2016 nebo předchozí verze, nebudete mít přístup ke vzorci TEXTJOIN. Nejlepší způsob, jak potom vyhledat a získat více odpovídajících hodnot v jedné buňce, je pomocí vlastního vzorce, který můžete vytvořit pomocí VBA.

Abychom získali více hodnot vyhledávání v jedné buňce, musíme ve VBA vytvořit funkci (podobnou funkci VLOOKUP), která zkontroluje každou buňku ve sloupci a pokud se vyhledávací hodnota najde, přidá ji k výsledku.

Zde je kód VBA, který to dokáže:

'Code by Sumit Bansal (https://trumpexcel.com) Funkce 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) Koncová funkce

Kam tento kód vložit?

1. Otevřete sešit a klikněte na Alt + F11 (otevře se okno editoru VBA).
2. V tomto okně editoru VBA je vlevo průzkumník projektů (kde jsou uvedeny všechny sešity a pracovní listy). Klikněte pravým tlačítkem na libovolný objekt v sešitu, kde má tento kód fungovat, a přejděte na Vložit -> Modul.
3. V okně modulu (které se zobrazí vpravo) zkopírujte a vložte výše uvedený kód.
4. Nyní jste všichni připraveni. Přejděte na libovolnou buňku v sešitu a napište = SingleCellExtract a připojte požadované vstupní argumenty (tj. LookupValue, LookupRange, ColumnNumber).

Jak tento vzorec funguje?

Tato funkce funguje podobně jako funkce VLOOKUP.

Jako vstupy jsou zapotřebí 3 argumenty:

1. Hodnota vyhledávání - Řetězec, který potřebujeme vyhledat v řadě buněk.
2. Rozsah vyhledávání - Pole buněk, odkud potřebujeme načíst data ($ B3: $ C18 v tomto případě).
3. Číslo sloupce - Jedná se o číslo sloupce tabulky/pole, ze kterého má být vrácena odpovídající hodnota (v tomto případě 2).

Když použijete tento vzorec, zkontroluje každou buňku ve sloupci úplně vlevo ve vyhledávacím rozsahu a když najde shodu, přidá se k výsledku v buňce, ve které jste vzorec použili.

Zapamatovat si: Chcete-li tento vzorec znovu použít, uložte sešit jako sešit s podporou maker (.xlsm nebo .xls). Také tato funkce by byla k dispozici pouze v tomto sešitu a ne ve všech sešitech.

Kliknutím sem stáhnete ukázkový soubor

Zjistěte, jak pomocí VBA v Excelu zautomatizovat nudné opakující se úkoly. Připojte se k Kurz Excel VBA

Získejte více hodnot vyhledávání v jedné buňce (bez opakování)

Existuje možnost, že v datech můžete mít opakování.

Pokud použijete výše použitý kód, bude se vám ve výsledku opakovat také.

Pokud chcete získat výsledek tam, kde se neopakují, musíte kód trochu upravit.

Zde je kód VBA, který vám poskytne více hodnot vyhledávání v jedné buňce bez jakýchkoli opakování.

'Code by Sumit Bansal (https://trumpexcel.com) Funkce 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) Then GoTo Skip End If End If Next J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funkce

Jakmile vložíte tento kód do editoru VB (jak je uvedeno výše v tutoriálu), budete moci používat MultipleLookupNoRept funkce.

Zde je snímek výsledku, kterého s tím dosáhnete MultipleLookupNoRept funkce.

Kliknutím sem stáhnete ukázkový soubor

V tomto kurzu jsem se zabýval tím, jak pomocí vzorců a VBA v Excelu najít a vrátit více hodnot vyhledávání v jedné buňce v Excelu.

I když to lze snadno provést pomocí jednoduchého vzorce, pokud používáte Excel v předplatném Microsoft 365, pokud používáte předchozí verze a nemáte přístup k funkcím, jako je TEXTJOIN, můžete to stále dělat pomocí VBA vytvořením vlastní vlastní funkce.