Отримання кількох значень пошуку в одній клітинці (з & без повторення)

Чи можна шукати та повертати декілька значень в одній клітинці 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.

І як ви побачите, це дуже проста формула.

Нижче у мене є набір даних, де я маю імена людей у ​​колонці А та навчання, яке вони пройшли у колонці В.

Натисніть тут, щоб завантажити файл прикладу та продовжити

Для кожної людини я хочу дізнатися, яке навчання вони пройшли. У стовпці 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}

Оскільки ми хочемо лише пройти навчання для Боба (значення у комірці D2), нам потрібно пройти все відповідне навчання для клітин, які повертають TRUE у вищезгаданому масиві.

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

І де б масив не повертав 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

Натисніть тут, щоб завантажити файл прикладу та продовжити

Отримати декілька значень пошуку в одній клітинці (без повторення)

Оскільки формула 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, який може це зробити:

'Code by Sumit Bansal (https://trumpexcel.com) Функція SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Як довгий результат Dim 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).
2. У цьому вікні редактора VBA ліворуч є провідник проектів (де перераховані всі робочі книги та аркуші). Клацніть правою кнопкою миші будь -який об’єкт у книзі, де потрібно працювати з цим кодом, і перейдіть до пункту Вставити -> Модуль.
3. У вікні модуля (яке з'явиться праворуч) скопіюйте та вставте вищевказаний код.
4. Тепер усе готово. Перейдіть до будь -якої клітинки у книзі та введіть текст = SingleCellExtract і вставте необхідні вхідні аргументи (тобто LookupValue, LookupRange, ColumnNumber).

Як працює ця формула?

Ця функція працює аналогічно функції VLOOKUP.

В якості вхідних даних потрібно 3 аргументи:

1. Пошук значення - Рядок, який нам потрібно шукати в діапазоні клітинок.
2. LookupRange - Масив клітинок, звідки нам потрібно отримати дані (у цьому випадку $ B3: $ C18).
3. Номер стовпця - Це номер стовпця таблиці/масиву, з якого потрібно повернути відповідне значення (2 у цьому випадку).

Коли ви використовуєте цю формулу, вона перевіряє кожну клітинку в крайньому лівому стовпці діапазону пошуку і коли він знаходить відповідність, він додає результат у клітинку, у якій ви використовували формулу.

Пам’ятайте: Збережіть книгу як книгу з підтримкою макросів (.xlsm або .xls), щоб знову використати цю формулу. Крім того, ця функція буде доступна лише у цій книзі, а не у всіх книгах.

Натисніть тут, щоб завантажити файл прикладу

Дізнайтесь, як автоматизувати нудні повторювані завдання за допомогою VBA в Excel. Приєднуйтесь до Курс Excel VBA

Отримати декілька значень пошуку в одній клітинці (без повторення)

Існує ймовірність того, що дані можуть повторюватися.

Якщо ви використовуєте код, використаний вище, він також дасть вам повторення в результаті.

Якщо ви хочете отримати результат, де немає повторень, вам потрібно трохи змінити код.

Ось код VBA, який дасть вам кілька значень пошуку в одній клітинці без повторів.

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

Після того, як ви розмістите цей код у редакторі VB (як показано вище у навчальному посібнику), ви зможете використовувати MultipleLookupNoRept функція.

Ось короткий огляд результату, який ви отримаєте з цим MultipleLookupNoRept функція.

Натисніть тут, щоб завантажити файл прикладу

У цьому посібнику я розглянув, як за допомогою формул та VBA в Excel знаходити та повертати кілька значень пошуку в одній клітинці Excel.

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