Объединение и автоматизация таблиц Excel: пошаговая инструкция по Power Query + Сводная таблица
Проблема, знакомая каждому HR, бухгалтеру, маркетологу и др.
Представьте: у вас есть Excel-файл учета спецодежды на 50+ сотрудников. Данные разбросаны по трем вкладкам:
- Справочник — название одежды, размеры, коды
- ФИО — список сотрудников с табельными номерами
- Статистика — кому что выдали (широкая таблица, где каждый сотрудник — отдельный столбец)
Задача: Собрать всё в одну таблицу и сформировать понятную автоматическую таблицу, где все данные будут подтягиваться.
Классическое решение: 2-3 часа ручной работы с ВПР или макрос на VBA.
Правильное решение: 10 минут в Power Query.
Что такое Power Query и почему это магия
Power Query — встроенный в Excel 2016+ инструмент для преобразования данных. Он умеет:
- Распаковывать широкие таблицы в длинные (unpivot)
- Объединять данные из разных источников
- Фильтровать, группировать, очищать
- Всё это без формул и без VBA
Главное преимущество: создав запрос один раз, вы можете обновлять данные одной кнопкой при изменении исходных таблиц.
Исходные данные
Вкладка 1: "Справочник"
Вкладка 2: "ФИО1"
Вкладка 3: "Статистика" (проблемная!)
Цифры — это количество единиц одежды, выданных каждому сотруднику.Проблема: Данные в "широком" формате — ФИО в заголовках столбцов. Для отчета нужен "длинный" формат.
Итоговый результат
1. Правильно сформулированная "база данный" - основа для сводной таблицы
2. Сводная таблица - автоматическая таблица из которой можно формировать нужные нам данные в виде понятных таблиц.
Пошаговая инструкция
✅ Этап 1: Загружаем таблицу "Статистика" в Power Query
1. Откройте лист "Статистика"
2. Кликните на любую ячейку внутри таблицы
Вкладка "Данные" → "Из таблицы/диапазона"
Убедитесь, что стоит галочка "Таблица с заголовками" ✅
Нажмите ОК
✅ Откроется редактор Power Query — отдельное окно с вашими данными.
✅ Этап 2: Распаковываем данные (Unpivot)
Суть: превращаем столбцы с ФИО в строки.
1. Выделите столбец "Название одежды" (клик на заголовок)
2. Удерживая Ctrl, выделите столбец "Размер"
3. Правой кнопкой мыши → "Отменить свертывание других столбцов"
🎉 Магия! Теперь у вас 4 столбца вместо 50+
- Название одежды
- Размер
- Атрибут (это ФИО)
- Значение (это количество)
4Переименуйте столбцы
"Атрибут" → "ФИО" (правой кнопкой → Переименовать)
"Значение" → "Количество"
✅ Этап 3: Фильтруем — оставляем только количество > 0
1. Кликните на стрелку вниз в столбце "Количество"
2. "Числовые фильтры" → "Больше чем..."
3. Введите: 0
4. ОК
Теперь остались только строки, где сотрудникам что-то выдали!
✅ Этап 4: Добавляем табельный номер из "ФИО1"
Шаг 4.1: Загружаем "ФИО1" как подключение
Что такое "подключение"?
Это способ загрузить данные в Power Query, но НЕ создавать отдельный лист в Excel. Данные будут доступны для объединения, но не займут место в рабочей книге.
Пошагово:
1. Сохраните текущую работу:
- Закройте редактор Power Query: "Главная" → "Закрыть и загрузить"
- Excel создаст временный лист с вашими данными (это нормально)
2. Переключитесь на лист "ФИО1":
- Внизу Excel найдите вкладку "ФИО1"
- Кликните на неё
3. Загрузите таблицу в Power Query:
(повторяем Этап 1✅, только берем вкладку ФИО1)
- Кликните на любую ячейку внутри таблицы с ФИО и табельными номерами
- Вкладка "Данные" → "Из таблицы/диапазона"
- Появится окно "Создание таблицы"
- Убедитесь, что галочка "Таблица с заголовками" стоит ✅
- Нажмите ОК
4. Переименуйте запрос:
- Откроется редактор Power Query
- Справа найдите панель "Параметры запроса" (Query Settings)
- В самом верху есть поле "Имя" (Name)
- Кликните в это поле и замените текст на "ФИО1"
- Нажмите Enter
5. Создайте подключение БЕЗ загрузки на лист:
⚠ Важный момент! Здесь НЕ нажимаем просто "Закрыть и загрузить"
- "Главная" → "Закрыть и загрузить в..." (с троеточием!)
- Появится окно "Импорт данных"
- Выберите "Только создать подключение" (радиокнопка внизу)
- Нажмите ОК
вапрва
✅ Готово! Данные ФИО1 теперь доступны для объединения, но не создали новый лист.
Как проверить:
- Вкладка "Данные" → кнопка "Запросы и подключения"
- Справа появится панель
- Вы увидите запрос "ФИО1" с иконкой подключения (не таблицы)
Шаг 4.2: Объединяем таблицы
1. "Данные" → "Запросы и подключения" (справа появится панель)
2. Правой кнопкой на запрос "Таблица1" (или "Статистика") → "Изменить"
3. В редакторе: "Главная" → "Объединить запросы"
Шаг 4.3: Настраиваем объединение
В окне "Слияние":
- Верхняя таблица: кликните на столбец "ФИО"
- Выпадающий список: выберите "ФИО1"
- Нижняя таблица: кликните на столбец "ФИО"
- Тип соединения: "Левое внешнее (Left Outer)"
- ОК
Шаг 4.4: Разворачиваем столбец
Появился столбец "ФИО1" с надписью "Table":
1. Кликните на иконку с двумя стрелками ⬌ в заголовке
2. Снимите галочку "Использовать исходное имя столбца как префикс"
3. Оставьте только "Табельный номер"
4. ОК
🎉 Появился столбец "Табельный номер"!
✅ Этап 5: Добавляем код из "Справочник"
Шаг 5.1: Загружаем "Справочник" как подключение
Повторяем процесс для Справочника:
1. Переключитесь в Excel (Главная - Закрыть и загрузить)
2. Перейдите на лист "Справочник"
3. Кликните на любую ячейку в таблице
4. "Данные" → "Из таблицы/диапазона" → ОК
5. Переименуйте запрос:
- В правой панели в поле "Имя" введите "Справочник"
- Нажмите Enter
6. Создайте подключение:
- "Главная" → "Закрыть и загрузить в..."
- Выберите "Только создать подключение" ⚠
- ОК
✅ Теперь у вас три запроса:
- Таблица1 (или "Статистика") — основной, с данными на листе
- ФИО1 — подключение
- Справочник — подключение
Шаг 5.2: Объединяем по ДВУМ столбцам
1. Откройте основной запрос на редактирование
2. Запросы и подключения - заходим в "Таблица1"
3. "Главная" → "Объединить запросы"
В окне "Слияние":
- Верхняя таблица: выделите "Название одежды", затем с Ctrl — "Размер"
- Выберите: "Справочник"
- Нижняя таблица: выделите "Название одежды" + "Размер" (с Ctrl)
- Тип: "Левое внешнее"
- ОК
4. Разверните столбец "Справочник":
- Снимите галочку "Использовать исходное имя..."
- Оставьте только "Код"
- ОК
✅ Этап 6: Финальное оформление
Шаг 6.1: Перетаскиваем столбики как нам удобно
- ФИО
- Табельный номер
- Название одежды
- Размер
- Код
- Количество
✅ Этап 7: Загружаем результат
"Главная" → "Закрыть и загрузить"
🎉 ГОТОВО! Excel создаст новый лист с итоговой таблицей.
Бонус 1: автообновление данных
После настройки запроса:
1. Добавили новые данные в исходные таблицы
2. Правой кнопкой на итоговую таблицу → "Обновить"
3. Всё пересчиталось автоматически!
Можно настроить автообновление при открытии файла:
- "Данные" → "Запросы и подключения" → Правой кнопкой на запрос → "Свойства"
- Галочка "Обновить при открытии файла"
Для кого эта инструкция
✅ HR-менеджеры (учет спецодежды, отпусков, командировок)
✅ Бухгалтеры (сверка данных из разных источников)
✅ Менеджеры по продажам (отчеты по клиентам)
✅ Аналитики данных (подготовка данных для анализа)
✅ Все, кто работает с Excel и устал от рутины
Итого
Было: 3 часа ручной работы + риск ошибок
Стало: 10 минут + обновление одной кнопкой
Power Query — это не магия, это просто правильный инструмент для правильной задачи.
Бонус 2: Сводная таблица для анализа
После того как мы получили итоговую таблицу, можем создать сводную для быстрого анализа.
Зачем нужна сводная таблица?
Представьте: нужно узнать общее количество каждого вида одежды по каждому сотруднику. Вместо фильтров и формул — используем сводную таблицу.
Создаем сводную таблицу
1. Кликните на любую ячейку в итоговой таблице
2. "Вставка" → "Сводная таблица"
3. Убедитесь, что выбран диапазон всей таблицы
4. Выберите "Новый лист"
5. Нажмите ОК
Настраиваем поля сводной таблицы
Справа появится панель "Поля сводной таблицы". Перетащите поля в нужные области:
📋 СТРОКИ (перетащите сюда):
1. Табельный номер
2. ФИО
3. Код
🔢 ЗНАЧЕНИЯ (перетащите сюда):
- Количество (автоматически будет "Сумма по полю Количество")
Что получится?
Сводная таблица автоматически сгруппирует данные:
🎯 Готово! Данные в сводной обновились автоматически.