Вземете множество стойности за търсене в една клетка (със и без повторение)

Можем ли да търсим и връщаме множество стойности в една клетка в Excel (разделени със запетая или интервал)?

Много пъти ми бяха задавани този въпрос от много мои колеги и читатели.

Excel има някои невероятни формули за търсене, като VLOOKUP, INDEX/MATCH (и сега XLOOKUP), но никой от тях не предлага начин за връщане на множество съвпадащи стойности. Всичко това работи, като идентифицира първото съвпадение и го връща.

Затова направих малко VBA кодиране, за да измисля персонализирана функция (наричана още потребителска функция) в Excel.

Актуализиране: След като Excel пусна динамични масиви и страхотни функции като UNIQUE и TEXTJOIN, вече е възможно да се използва проста формула и връща всички съвпадащи стойности в една клетка (обхванати в този урок).

В този урок ще ви покажа как да направите това (ако използвате най -новата версия на Excel - Microsoft 365 с всички нови функции), както и начин да направите това в случай, че използвате по -стари версии ( използвайки VBA).

Така че нека започнем!

Търсене и връщане на множество стойности в една клетка (използване на формула)

Ако използвате Excel 2016 или предишни версии, преминете към следващия раздел, където ще покажа как да направите това с помощта на VBA.

С абонамента за Microsoft 365 вашият Excel вече има много по -мощни функции и функции, които не са съществували в предишни версии (като XLOOKUP, Dynamic Arrays, UNIQUE/FILTER и др.)

Така че, ако използвате Microsoft 365 (по -рано известен като Office 365), можете да използвате методите, обхванати в този раздел, могат да търсят и връщат множество стойности в една клетка в Excel.

И както ще видите, това е наистина проста формула.

По -долу имам набор от данни, където имам имената на хората в колона А и обучението, което са преминали в колона Б.

Щракнете тук, за да изтеглите примерния файл и продължете

За всеки човек искам да разбера какво обучение са завършили. В колона D имам списък с уникални имена (от колона А) и искам бързо да потърся и извлека цялото обучение, което всеки човек е извършил, и да ги получа в един набор (разделен със запетая).

По -долу е формулата, която ще направи това:

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

След като въведете формулата в клетка E2, я копирайте за всички клетки, където искате резултатите.

Как действа тази формула?

Позволете ми да деконструирам тази формула и да обясня всяка част от това как тя се събира ни дава резултата.

Логическият тест във формулата IF (D2 = $ A $ 2: $ A $ 20) проверява дали клетката с име D2 е същата като тази в диапазон A2: A20.

Той преминава през всяка клетка в диапазона A2: A20 и проверява дали името е същото в клетка D2 или не. ако е същото име, връща TRUE, в противен случай връща FALSE.

Така че тази част от формулата ще ви даде масив, както е показано по -долу:

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

Тъй като искаме само да получим обучението за Bob (стойността в клетка D2), трябва да получим всички съответни обучения за клетките, които връщат TRUE в горния масив.

Това лесно се постига чрез посочване на [value_if_true] част от формулата IF като диапазон, който има обучение. Това гарантира, че ако името в клетка D2 съвпада с името в диапазона A2: A20, формулата IF ще върне цялото обучение, което е преминал този човек.

И където масивът връща FALSE, ние сме посочили стойността [value_if_false] като “” (празно), така че връща празно.

IF частта от формулата връща масива, както е показано по -долу:

{“Excel”; ””; ””; ”PowerPoint”; ””; ””; ””; ””; ””; ””; ””; ””; ”” ”;” ”;” ” ; ””; ””; ””}

Където има имената на обучението, което Боб е взел, и празни места, където името не беше Боб.

Сега всичко, което трябва да направим, е да комбинираме тези имена на обучение (разделени със запетая) и да го върнем в една клетка.

И това може лесно да се направи с помощта на новата формула TEXTJOIN (налична в Excel2021-2022 и Excel в Microsoft 365)

Формулата TEXTJOIN приема три аргумента:

• Разделителят - който е „,“ в нашия пример, тъй като искам обучението да бъде разделено със запетая и интервал
• TRUE - което казва на формулата TEXTJOIN да игнорира празни клетки и да комбинира само тези, които не са празни
• Формулата If, която връща текста, който трябва да се комбинира

Ако използвате Excel в Microsoft 365, който вече има динамични масиви, можете просто да въведете горната формула и да натиснете Enter. И ако използвате Excel2021-2022, трябва да въведете формулата и да задържите клавишите Control и Shift и след това да натиснете Enter

Щракнете тук, за да изтеглите примерния файл и продължете

Вземете множество стойности за търсене в една клетка (без повторение)

Тъй като формулата UNIQUE е достъпна само за Excel в Microsoft 365, няма да можете да използвате този метод в Excel2021-2022

В случай, че във вашия набор от данни има повторения, както е показано по -долу, трябва да промените малко формулата, така че да получите само списък с уникални стойности в една клетка.

В горния набор от данни някои хора са преминали обучение няколко пъти. Например Боб и Стан са преминали обучението по Excel два пъти, а Бети е преминала обучение по MS Word два пъти. Но в нашия резултат не искаме да повтаряме име на обучение.

Можете да използвате следната формула, за да направите това:

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

Горната формула работи по същия начин, с малка промяна. използвахме формулата IF в рамките на функцията UNIQUE, така че в случай, че има повторения в резултата от формулата if, функцията UNIQUE ще я премахне.

Щракнете тук, за да изтеглите примерния файл

Търсене и връщане на множество стойности в една клетка (използване на VBA)

Ако използвате Excel 2016 или предишни версии, няма да имате достъп до формулата TEXTJOIN. Така че най -добрият начин след това да потърсите и да получите множество съвпадащи стойности в една клетка е като използвате персонализирана формула, която можете да създадете с помощта на VBA.

За да получим множество стойности за търсене в една клетка, трябва да създадем функция във VBA (подобна на функцията VLOOKUP), която проверява всяка клетка в колона и ако стойността за търсене е намерена, я добавя към резултата.

Ето кода на VBA, който може да направи това:

„Код от Sumit Bansal (https://trumpexcel.com) Функция 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). .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Резултат, Len (Резултат) - 1) Крайна функция

Къде да поставите този код?

1. Отворете работна книга и щракнете върху Alt + F11 (това отваря прозореца на VBA Editor).
2. В този прозорец на VBA Editor вляво има изследовател на проекти (където са изброени всички работни книги и работни листове). Щракнете с десния бутон върху всеки обект в работната книга, където искате да работи този код, и отидете на Вмъкване -> Модул.
3. В прозореца на модула (който ще се появи вдясно) копирайте и поставете горния код.
4. Сега сте готови. Отидете до всяка клетка в работната книга и въведете = SingleCellExtract и включете необходимите входни аргументи (т.е. LookupValue, LookupRange, ColumnNumber).

Как действа тази формула?

Тази функция работи подобно на функцията VLOOKUP.

За вход са необходими 3 аргумента:

1. Търсене на стойност - Низ, който трябва да търсим в диапазон от клетки.
2. LookupRange - Масив от клетки, откъдето трябва да извлечем данните ($ B3: $ C18 в този случай).
3. ColumnNumber - Това е номерът на колоната на таблицата/масива, от който трябва да се върне съответстващата стойност (2 в този случай).

Когато използвате тази формула, тя проверява всяка клетка в най -лявата колона в диапазона за търсене и когато намери съвпадение, добавя към резултата в клетката, в която сте използвали формулата.

Помня: Запазете работната книга като работна книга с активирани макроси (.xlsm или .xls), за да използвате отново тази формула. Освен това тази функция ще бъде налична само в тази работна книга, а не във всички работни книги.

Щракнете тук, за да изтеглите примерния файл

Научете как да автоматизирате скучни повтарящи се задачи с VBA в Excel. Присъедини се към Excel VBA курс

Вземете множество стойности за търсене в една клетка (без повторение)

Възможно е да имате повторения в данните.

Ако използвате кода, използван по -горе, той ще ви даде повторения и в резултата.

Ако искате да получите резултат, където няма повторения, трябва да промените кода малко.

Ето кода на VBA, който ще ви даде множество стойности за търсене в една клетка без никакви повторения.

'Код от Sumit Bansal (https://trumpexcel.com) Функция MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Както Long Dim Result As String For i = 1 To LookupRange.Columns (1). .Count If LookupRange.Cells (i, 1) = Lookupvalue Тогава For J = 1 To i - 1 If LookupRange.Cells (J, 1) = Lookupvalue Тогава If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Тогава GoTo Skip End If End If End If Next J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Резултат, Len (Резултат) - 1) Край Функция

След като поставите този код във VB Editor (както е показано по -горе в урока), ще можете да използвате MultipleLookupNoRept функция.

Ето моментна снимка на резултата, който ще получите с това MultipleLookupNoRept функция.

Щракнете тук, за да изтеглите примерния файл

В този урок разгледах как да използвам формули и VBA в Excel за намиране и връщане на множество стойности за търсене в една клетка в Excel.

Въпреки че може лесно да се направи с проста формула, ако използвате Excel в абонамент за Microsoft 365, ако използвате предишни версии и нямате достъп до функции като TEXTJOIN, все пак можете да направите това с помощта на VBA, като създадете своя собствена персонализирана функция.