Получение нескольких значений поиска в одной ячейке (с повторением и без повторения)

Можем ли мы найти и вернуть несколько значений в одной ячейке Excel (через запятую или пробел)?

Мне неоднократно задавали этот вопрос многие мои коллеги и читатели.

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

Поэтому я немного написал код VBA, чтобы придумать настраиваемую функцию (также называемую функцией, определяемой пользователем) в Excel.

Обновлять: После того, как Excel выпустил динамические массивы и замечательные функции, такие как UNIQUE и TEXTJOIN, теперь можно использовать простую формулу и вернуть все совпадающие значения в одной ячейке (рассматривается в этом руководстве).

В этом руководстве я покажу вам, как это сделать (если вы используете последнюю версию Excel - Microsoft 365 со всеми новыми функциями), а также как это сделать, если вы используете более старые версии ( используя VBA).

Итак, приступим!

Поиск и возврат нескольких значений в одной ячейке (с использованием формулы)

Если вы используете Excel 2016 или предыдущие версии, перейдите к следующему разделу, где я покажу, как это сделать с помощью VBA.

С подпиской на Microsoft 365 ваш Excel теперь имеет гораздо более мощные функции и возможности, которых не было в предыдущих версиях (например, XLOOKUP, динамические массивы, функции UNIQUE / FILTER и т. Д.)

Поэтому, если вы используете Microsoft 365 (ранее известный как Office 365), вы можете использовать методы, описанные в этом разделе, для поиска и возврата нескольких значений в одной ячейке Excel.

И, как вы увидите, это действительно простая формула.

Ниже у меня есть набор данных, в котором есть имена людей в столбце A и обучение, которое они прошли в столбце B.

Щелкните здесь, чтобы загрузить файл с примером, и следуйте инструкциям.

Для каждого человека я хочу узнать, какое обучение они прошли. В столбце D у меня есть список уникальных имен (из столбца A), и я хочу быстро найти и извлечь все тренировки, которые прошел каждый человек, и получить их в одном наборе (разделенном запятой).

Ниже приведена формула, которая сделает это:

= TEXTJOIN (",", ИСТИНА, ЕСЛИ (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

После ввода формулы в ячейку E2 скопируйте ее для всех ячеек, в которых вы хотите получить результаты.

Как работает эта формула?

Позвольте мне разобрать эту формулу и объяснить каждую часть того, как она сочетается и дает нам результат.

Логическая проверка в формуле ЕСЛИ (D2 = $ A $ 2: $ A $ 20) проверяет, совпадает ли имя ячейки D2 с ячейкой в ​​диапазоне A2: A20.

Он просматривает каждую ячейку в диапазоне A2: A20 и проверяет, совпадает ли имя в ячейке D2 или нет. если это то же имя, возвращается ИСТИНА, иначе возвращается ЛОЖЬ.

Таким образом, эта часть формулы даст вам массив, как показано ниже:

{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

Поскольку мы хотим получить обучение только для Боба (значение в ячейке D2), нам нужно получить все соответствующее обучение для ячеек, которые возвращают ИСТИНА в приведенном выше массиве.

Это легко сделать, указав часть [value_if_true] формулы IF в качестве диапазона, в котором есть обучение. Это гарантирует, что если имя в ячейке D2 совпадает с именем в диапазоне A2: A20, формула ЕСЛИ вернет все обучение, которое прошел этот человек.

И везде, где массив возвращает FALSE, мы указали значение [value_if_false] как «» (пусто), поэтому оно возвращает пустое значение.

Часть формулы IF возвращает массив, как показано ниже:

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

Там, где есть названия тренировок, которые прошел Боб, и пробелы там, где имя не было Бобом.

Теперь все, что нам нужно сделать, это объединить эти обучающие имена (разделенные запятыми) и вернуть их в одну ячейку.

И это легко сделать с помощью новой формулы TEXTJOIN (доступной в Excel2021-2022 и Excel в Microsoft 365).

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

• разделитель - в нашем примере это «», поскольку я хочу, чтобы обучение было разделено запятой и пробелом.
• ИСТИНА - указывает формуле TEXTJOIN игнорировать пустые ячейки и объединять только те, которые не пусты.
• Формула If, возвращающая текст, который необходимо объединить

Если вы используете Excel в Microsoft 365, в котором уже есть динамические массивы, вы можете просто ввести приведенную выше формулу и нажать Enter. А если вы используете Excel2021-2022, вам нужно ввести формулу, удерживая клавиши Control и Shift, а затем нажать Enter.

Щелкните здесь, чтобы загрузить файл с примером, и следуйте инструкциям.

Получить несколько значений поиска в одной ячейке (без повторения)

Поскольку УНИКАЛЬНАЯ формула доступна только для Excel в Microsoft 365, вы не сможете использовать этот метод в Excel2021–2022.

Если в вашем наборе данных есть повторы, как показано ниже, вам нужно немного изменить формулу, чтобы вы получали только список уникальных значений в одной ячейке.

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

Для этого можно использовать формулу ниже:

= TEXTJOIN (",", ИСТИНА, УНИКАЛЬНО (ЕСЛИ (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) .Cells .Count Если LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Конечная функция

Куда положить этот код?

1. Откройте книгу и нажмите Alt + F11 (откроется окно редактора VBA).
2. В этом окне редактора VBA слева есть проводник проекта (в котором перечислены все книги и рабочие листы). Щелкните правой кнопкой мыши любой объект в книге, где вы хотите, чтобы этот код работал, и выберите Вставить -> Модуль.
3. В окне модуля (которое появится справа) скопируйте и вставьте приведенный выше код.
4. Теперь все готово. Перейдите в любую ячейку книги и введите = SingleCellExtract и вставьте необходимые входные аргументы (например, LookupValue, LookupRange, ColumnNumber).

Как работает эта формула?

Эта функция работает аналогично функции ВПР.

В качестве входных данных он принимает 3 аргумента:

1. Lookupvalue - Строка, которую нам нужно найти в диапазоне ячеек.
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 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 Функция

После того, как вы разместите этот код в редакторе VB (как показано выше в руководстве), вы сможете использовать MultipleLookupNoRept функция.

Вот снимок результата, который вы получите с этим MultipleLookupNoRept функция.

Щелкните здесь, чтобы загрузить файл примера

В этом руководстве я рассказал, как использовать формулы и VBA в Excel для поиска и возврата нескольких значений подстановки в одной ячейке в Excel.

Хотя это легко сделать с помощью простой формулы, если вы используете Excel в подписке Microsoft 365, но если вы используете предыдущие версии и не имеете доступа к таким функциям, как TEXTJOIN, вы все равно можете сделать это с помощью VBA, создав свой собственная пользовательская функция.