Как подтянуть к одному значению сразу несколько значений из другой таблицы в Excel
Представьте задачу: у вас есть список сотрудников и отдельная таблица с пройденными ими тренингами. Один сотрудник мог посетить пять, десять или двадцать разных курсов. Вам нужно собрать все эти тренинги в одной ячейке напротив фамилии сотрудника или, как минимум, связать данные так, чтобы каждому сотруднику соответствовали все его записи.
Обычный ВПР здесь бессилен — он находит только первое совпадение. Копировать и вставлять вручную? Слишком долго. Power Query решает эту задачу за минуту, создавая связь «один ко многим» между таблицами.
Шаг 1: Загружаем обе таблицы в Power Query
Выделите любую ячейку в первой таблице (например, со списком тренингов). Перейдите на вкладку «Данные» → «Из таблицы/диапазона». Если появится диалоговое окно, убедитесь, что диапазон указан верно и есть заголовки. Нажмите «ОК». Таблица откроется в редакторе Power Query.
2. На вкладке «Главная» нажмите «Закрыть и загрузить в».
3. В диалоговом окне выберите «Только создать подключение» и нажмите «ОК».
Шаг 2: Объединяем запросы по ключевому полю
Снова откройте редактор Power Query и загрузите вторую таблицу с сотрудниками, к которым нужно соотнести тренинги. Самый быстрый способ — перейти на вкладку «Данные»→ «Из таблицы/диапазона».
2. На вкладке «Главная» нажмите «Объединить» → «Объединить запросы».
3. Откроется окно «Объединение»:
В верхнем выпадающем списке уже выбрана текущая таблица.
В нижнем выпадающем списке выберите вторую таблицу (с тренингами).
В обеих таблицах выделите столбцы, по которым нужно связать данные (например, ID сотрудника). Они должны подсветиться.
В поле «Тип соединения» выберите «Внешнее соединение слева», чтобы у каждого сотрудника осталась строка, даже если тренингов нет.
4. Нажмите «ОК». В основной таблице появится новый столбец с названием типа Table, содержащий все связанные строки из таблицы тренингов.
Шаг 3: Разворачиваем подтянутую таблицу
Нажмите на двойную стрелочку (значок «Развернуть») в заголовке нового столбца.
2. В открывшемся окне вы увидите список всех полей из таблицы тренингов. Выберите только те, которые нужно подтянуть (например, «Название тренинга»).
3. Обязательно снимите галочку с пункта «Использовать исходное имя столбца как префикс», чтобы названия полей не стали слишком длинными.
4. Нажмите «ОК». Power Query развернёт данные: теперь каждому сотруднику будет соответствовать столько строк, сколько тренингов он прошел. Если у одного сотрудника 5 тренингов — будет 5 строк с его данными.
Шаг 4: Загружаем результат в Excel
Когда таблица приобрела нужный вид, нажмите «Закрыть и загрузить» на вкладке «Главная».
Power Query создаст новый лист и выгрузит на него итоговую таблицу, где каждому сотруднику соответствует несколько строк с названиями тренингов.
Если вам нужно собрать все тренинги в одну ячейку через запятую, после шага 3 можно применить группировку: на вкладке «Преобразование» → «Группировать по», выбрав поля сотрудника и агрегацию «Все строки», а затем вручную объединить тексты.
Хотите увидеть этот процесс вживую и узнать ещё больше лайфхаков? Смотрите наглядный пример в нашем Telegram-канале: https://t.me/Natalia_ProExcel/1479
Заключение
Объединение таблиц по принципу «один ко многим» через Power Query — это универсальный навык для работы с данными в Excel. Он заменяет громоздкие формулы массива, исключает ручное копирование и даёт идеальную структуру для дальнейшего анализа в сводных таблицах.
Это один из ключевых приёмов, которые превращают Excel в полноценный инструмент для обработки сложных данных. Чтобы освоить его и многие другие техники трансформации, очистки и объединения информации, приглашаем на курс «Турбо Excel». Вы научитесь строить автоматизированные отчеты с данными любой сложности.