Note: The other languages of the website are Google-translated. Back to English
English English

Як здійснити пошук та об’єднання кількох відповідних значень у Excel?

Як ми всі знали, Vlookup Функція в Excel може допомогти нам шукати значення і повертати відповідні дані в іншому стовпці, але загалом вона може отримати перше відносне значення лише за наявності декількох відповідних даних. У цій статті я розповім про те, як переглядати та об’єднувати кілька відповідних значень лише в одній комірці або вертикальному списку.

Перегляд і повернення декількох відповідних значень по вертикалі за допомогою формули

Перегляд і об'єднання кількох відповідних значень у комірці з користувацькою функцією

Перегляньте та об’єднайте кілька значень, що збігаються, у комірці за допомогою Kutools для Excel


Припустимо, у мене є такий діапазон даних, щоб отримати всі відповідні значення на основі конкретного значення по вертикалі, як показано на наступному скріншоті, ви можете застосувати формулу масиву.

doc vlookup об'єднує 1

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 клавіші разом, щоб отримати відносну базу значень за певним критерієм, див. знімок екрана:

doc vlookup об'єднує 2

примітки: У наведеній вище формулі:

A1: A16 - діапазон стовпців, який містить конкретне значення, яке потрібно шукати;

D2 вказує конкретне значення, яке потрібно переглянути;

B1: B16 - діапазон стовпців, з якого потрібно повернути відповідні дані;

$ 1: $ 16 вказує посилання на рядки в межах діапазону.

2. Потім виділіть комірку E2 і перетягніть маркер заповнення вниз до комірок, поки не отримаєте порожні комірки, і всі відповідні значення будуть перераховані в стовпці, як показано на наступному знімку екрана:

doc vlookup об'єднує 3


Замість того, щоб отримувати відносні значення по вертикалі, іноді потрібно зіставити значення в одну клітинку та об’єднати їх за допомогою певного роздільника. У цьому випадку наступна визначена користувачем функція може зробити вам послугу.

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) у порожню комірку, куди потрібно помістити результат, і натисніть вводити ключ, всі відповідні значення на основі конкретних даних були повернуті в одну комірку з роздільником, див. знімок екрана:

doc vlookup об'єднує 4

примітки: У наведеній вище формулі: D2 вказує значення комірок, які потрібно шукати, А1: В16 - діапазон даних, який потрібно отримати, номер 2 - це номер стовпця, з якого слід повернути відповідне значення, ви можете змінити посилання на тези до своїх потреб.


Якщо у вас є Kutools для Excel, З його Розширені комбіновані ряди функцію, ви можете швидко закінчити цю роботу з легкістю. Ця функція може допомогти вам поєднати всі співпадаючі значення з певними роздільниками на основі тих самих даних в іншому стовпці.

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів.

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть діапазон даних, для якого потрібно отримати відповідні значення на основі конкретних даних.

2. Потім натисніть Кутулс > Злиття та розділення > Розширені комбіновані ряди, див. скріншот:

3, в Розширені комбіновані ряди у діалоговому вікні клацніть ім’я стовпця, на основі якого потрібно об’єднати, а потім натисніть Первинний ключ , див. знімок екрана:

doc vlookup об'єднує 6

4. Потім клацніть інше ім’я стовпця, для якого потрібно повернути відповідні значення, і натисніть Поєднувати щоб вибрати один роздільник для розділення комбінованих значень, див. скріншот

doc vlookup об'єднує 7

5. А потім клацніть Ok кнопку, всі відповідні значення на основі тих самих значень були об'єднані разом із певним роздільником, див. скріншоти:

doc vlookup об'єднує 8 2 doc vlookup об'єднує 9

 Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (16)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Як отримати результат. Будь ласка, допоможіть. дані data1 результат a 1 a1 b 2 a2 c b1 b2 c1 c2
Цей коментар був мінімізований модератором на сайті
Під час використання cusvlookup чи є спосіб додати прізвище з комою між ними, які можуть з’явитися в стовпці C
Цей коментар був мінімізований модератором на сайті
Сподобалась функція для Excel 2013, але трохи змінив її, щоб змінити розділовий символ на ";" замість " ", а потім видалити префікс ";" із об'єднаних значень Результати, що відповідають значенням у моєму прикладі, будуть мати ;result01 або ;result01;result02 . Додано додатковий If Left(xResult, 1) = ";" щоб видалити зайвий ";" на початку рядка, якщо це 1-й символ. Я впевнений, що є більш акуратний спосіб зробити це, але він спрацював для мене. :) Функція CusVlookup(pValue як рядок, pWorkRng як діапазон, pIndex як довго) Dim rng як діапазон Dim xResult як рядок xResult = "" Для кожного rng ​​в pWorkRng Якщо rng ​​= pValue Тоді xResult = xResult & ";" & rng.Offset(0, pIndex - 1) Якщо Left(xResult, 1) = ";" Тоді xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
Цей коментар був мінімізований модератором на сайті
Зробіть умову if для результату, якщо порожнє.

Функція CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Смутний результат Як рядок
результат = ""
Для кожного x в діапазоні пошуку
Якщо x = lookupval Тоді
Якщо не результат = "" Тоді
результат = результат & " " & x.Offset(0, indexcol - 1)
Ще
результат = x.Offset(0, indexcol - 1)
End If
Далі х
CusVlookup = результат
End Function
Цей коментар був мінімізований модератором на сайті
Це дивовижно, але я шукаю щось інше, у мене є таблиця з RollNo StudentName sub1, sub2, sub3 ... Загальний результат, коли я введу Rollnumber, він повинен дати такий результат, як "SNName Sub1 64, sub2 78,... Всього 389, результат пройшов», чи можливо
Цей коментар був мінімізований модератором на сайті
Чи є спосіб видалити повторювані значення в конкатенату?
Цей коментар був мінімізований модератором на сайті
Привіт, Джейкобе,
Можливо, наступна стаття допоможе вам вирішити вашу проблему.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Будь ласка, спробуйте, сподіваюся, це допоможе вам!
Цей коментар був мінімізований модератором на сайті
Чи є спосіб перерахувати повторювані значення лише один раз, використовуючи код vba і формулу вище? Я не знаю, куди розмістити оператор countif>1 у рядку формул чи в самому vba. Будь ласка, допоможіть
Цей коментар був мінімізований модератором на сайті
ви можете додати дві додаткові умови, щоб пропустити порожні клітинки та пропустити дублікати: для i = 1 до CriteriaRange.Count
Якщо CriteriaRange.Cells(i).Value = Умова Тоді
Якщо ConcatenateRange.Cells(i).Value <> "" Тоді "ПРОПУСТИ БАНКИ
Якщо InStr(xResult, ConcatenateRange.Cells(i).Value) = 0, то "ПРПУСТИТИ, ЯКЩО ЗНАЙДЕНО ДУПЛІКАТ
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Далі я
Цей коментар був мінімізований модератором на сайті
Я повинен сказати, що я намагаюся отримати формулу для об’єднання кількох значень і повернення їх в одну клітинку вже 2 дні. Мене врятувало це "Як робити"!! Дуже дякую! Я б ніколи не отримав його без вашого модуля!
Хоча у мене є 2 питання. У мене є роздільник як кома замість пробілу, і через це він починається з коми. Чи є спосіб запобігти початковій комі, але залишити решту?
Моє друге запитання: Коли я використовую маркер заповнення, він змінює значення діапазону, а також значення комірки, яке я хочу знайти. Я хочу, щоб він продовжував змінювати номер клітинки, який я хочу шукати, але зберігав ті самі значення діапазону. Як я можу це зробити?

Щиро дякую за допомогу!!
Цей коментар був мінімізований модератором на сайті
cusVlookup чудово спрацював для мене. Інший спосіб мати інший роздільник — обернути дві функції заміни. Перший (зсередини назовні) замінює перший пробіл без пробілу, другий замінює всі інші пробіли на «/» у моєму. Можна використовувати ",", якщо потрібно коми.
=ЗАМІНИТИ(ЗАМІНИТИ(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Крім того, якщо ваше значення пошуку не є першим стовпцем, ви можете використовувати 0 або від’ємні числа, щоб перейти до стовпця ліворуч.
=ЗАМІНИТИ(ЗАМІНИТИ(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
Цей коментар був мінімізований модератором на сайті
Привіт, Джеффе,
Дякую, що поділилися, ви, мабуть, щира людина.
Цей коментар був мінімізований модератором на сайті
Це чудово працює для мене - чи можна все-таки змінити це, щоб він перевіряв, чи містить клітинка, а не повну відповідність? В основному у мене є список завдань, де:
Стовпець A: залежності (наприклад, 10003 10004 10008)
Колонка B: Довідка про завдання (наприклад, 10001)
Стовпець C: Залежні завдання (стовпець для результату формули) – де він шукає посилання на завдання, щоб побачити, які рядки містять його в стовпці A, а потім перелічує довідку про завдання для цих завдань.

Наприклад:

Рядок | Колонка А | Колонка B | Колонка C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
Цей коментар був мінімізований модератором на сайті
Ви б хотіли використовувати функцію Instr(), яка перевірятиме щось у рядку тексту в комірці. Ви також можете використовувати Left() і Right(), якщо ви шукаєте деталі початку або кінця.
Цей коментар був мінімізований модератором на сайті
Чи є спосіб отримати унікальне "ім'я" для "class1"
Цей коментар був мінімізований модератором на сайті
Привіт, сим-Джон,
Можливо, наведена нижче стаття може вирішити вашу проблему, перегляньте її:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця