Як здійснити пошук та об’єднання кількох відповідних значень у Excel?
Як ми всі знали, Vlookup Функція в Excel може допомогти нам шукати значення і повертати відповідні дані в іншому стовпці, але загалом вона може отримати перше відносне значення лише за наявності декількох відповідних даних. У цій статті я розповім про те, як переглядати та об’єднувати кілька відповідних значень лише в одній комірці або вертикальному списку.
Перегляд і повернення декількох відповідних значень по вертикалі за допомогою формули
Перегляд і об'єднання кількох відповідних значень у комірці з користувацькою функцією
Внутрішній пошук і об’єднання кількох відповідних значень у клітинку за допомогою Kutools для Excel
Перегляд і повернення декількох відповідних значень по вертикалі за допомогою формули
Припустимо, у мене є такий діапазон даних, щоб отримати всі відповідні значення на основі конкретного значення по вертикалі, як показано на наступному скріншоті, ви можете застосувати формулу масиву.
1. Введіть цю формулу: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") у порожню комірку, куди потрібно помістити результат, наприклад, E2, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати відносну базу значень за певним критерієм, див. знімок екрана:
примітки: У наведеній вище формулі:
A1: A16 - діапазон стовпців, який містить конкретне значення, яке потрібно шукати;
D2 вказує конкретне значення, яке потрібно переглянути;
B1: B16 - діапазон стовпців, з якого потрібно повернути відповідні дані;
$ 1: $ 16 вказує посилання на рядки в межах діапазону.
2. Потім виділіть комірку E2 і перетягніть маркер заповнення вниз до комірок, поки не отримаєте порожні комірки, і всі відповідні значення будуть перераховані в стовпці, як показано на наступному знімку екрана:
Перегляд і об'єднання кількох відповідних значень у комірці з користувацькою функцією
Замість того, щоб отримувати відносні значення по вертикалі, іноді потрібно зіставити значення в одну клітинку та об’єднати їх за допомогою певного роздільника. У цьому випадку наступна визначена користувачем функція може зробити вам послугу.
1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно
Код VBA: пошук і об'єднання кількох відповідних значень у комірці
Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
result = result & " " & x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
3. Потім збережіть і закрийте цей код, поверніться до робочого аркуша та введіть цю формулу: = cusvlookup (D2, A1: B16,2) у порожню комірку, куди потрібно помістити результат, і натисніть Що натомість? Створіть віртуальну версію себе у ключ, всі відповідні значення на основі конкретних даних були повернуті в одну комірку з роздільником, див. знімок екрана:
примітки: У наведеній вище формулі: D2 вказує значення комірок, які потрібно шукати, А1: В16 - діапазон даних, який потрібно отримати, номер 2 - це номер стовпця, з якого слід повернути відповідне значення, ви можете змінити посилання на тези до своїх потреб.
Внутрішній пошук і об’єднання кількох відповідних значень у клітинку за допомогою Kutools для Excel
Якщо у вас є Kutools для Excel, З його Розширені комбіновані ряди функцію, ви можете швидко закінчити цю роботу з легкістю. Ця функція може допомогти вам поєднати всі співпадаючі значення з певними роздільниками на основі тих самих даних в іншому стовпці.
| Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів. |
після установки Kutools для Excel, будь ласка, виконайте наступне:
1. Виберіть діапазон даних, для якого потрібно отримати відповідні значення на основі конкретних даних.
2. Потім натисніть Кутулс > Злиття та розділення > Розширені комбіновані ряди, див. скріншот:
3, в Розширені комбіновані ряди у діалоговому вікні клацніть ім’я стовпця, на основі якого потрібно об’єднати, а потім натисніть Первинний ключ , див. знімок екрана:
4. Потім клацніть інше ім’я стовпця, для якого потрібно повернути відповідні значення, і натисніть Поєднувати щоб вибрати один роздільник для розділення комбінованих значень, див. скріншот
5. А потім клацніть Ok кнопку, всі відповідні значення на основі тих самих значень були об'єднані разом із певним роздільником, див. скріншоти:
 |
 |
 |
Завантажте та безкоштовну пробну версію Kutools для Excel зараз!
Демо: Vlookup і конкатенація кількох відповідних значень у комірці за допомогою Kutools для Excel
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
