Если хотя бы раз собирали данные вручную из разных Excel, то наверняка помните, сколько с этим мороки: легко запутаться, допустить ошибку, а при каждом обновлении начинается новая рутина. Но есть лайфхак — Power Query в Excel справляется со всем этим за пару кликов!
В этой инструкции покажу, как объединять не только XLSX-файлы, но и TXT, CSV, XML — способ подойдёт практически для любых данных.
Шаг 1. Настройте файлы Excel перед объединением
Перед тем как собрать свои Excel-файлы в одну таблицу, уделите подготовке пару минут.
Главное правило: структура у всех файлов должна быть одинаковой. Проверьте, чтобы совпадали количество и названия столбцов, а имена нужных вкладок были одинаковыми. Совет: похожие имена файлов сделают работу ещё удобнее.
Порядок столбцов значения не имеет, а разные количества строк — не проблема.
Теперь просто переместите все нужные для объединения файлы в отдельную папку.
Идеальный вариант — в папке только нужные файлы, так ничего лишнего случайно не попадет в итоговую таблицу. Если же порядок идеальный навести не получится, ниже расскажу, как убрать всё лишнее на этапе объединения.
Шаг 2. Откройте Excel и подключите папку с файлами
Когда всё собрано в одной папке — пора за дело!
Запустите новый Excel-файл, создайте пустой лист и сохраните его отдельно — не смешивайте с теми документами, которые будете объединять.
Лучшие горячие клавиши Excel, которые я использую каждый день
С этими сочетаниями ваша работа полетит!
Далее перейдите во вкладку «Данные» (Data), кликните «Получить данные» (Get Data), подведите мышку к пункту «Из файла» (From File) и выберите «Из папки» (From Folder).
В открывшемся окне укажите вашу папку с файлами для объединения и нажмите «Открыть».
Шаг 3. Отбираем файлы для объединения
Теперь всё зависит от того, что у вас в папке: если там только нужные файлы, переходите к пункту 3А. Если затесалось что-то лишнее — смотрите 3Б.
3А: В папке только нужные файлы
Если папка чиста, всё просто: никаких фильтров! Кликайте «Объединить» (Combine), затем выбирайте «Объединить и преобразовать данные» (Combine And Transform Data).
Excel проанализирует содержимое и после этого сразу переходит к окну объединения — время для Шага 4!
3Б: В папке есть лишние файлы
Если в папке лежит что-то ненужное, после выбора папки появится весь список файлов. Кликните «Преобразовать данные» (Transform Data) — и сможете вручную выбрать только те документы, которые хотите объединить.
Откроется Power Query Editor — как раз там и можно спокойно убрать всё, что не подходит.
Например, файл Contact Details — не нужен. Просто откройте фильтр в столбце Name, снимите галочку с ненужного документа и сохраните выбор.
В списке остались только те файлы, что нужны. Щёлкните на двойную стрелку вниз в столбце Content — и переходите к следующему шагу.
Шаг 4. Объединяем файлы через Power Query
В окне «Объединение файлов» на что обратить внимание?
Первым делом — в качестве примера будет выбран первый из ваших файлов. Его структура и станет основой для итоговой таблицы. Если все файлы одинаковые — смело продолжайте дальше.
Если в книгах есть несколько листов, слева появится их список. Вот почему важно, чтобы названия вкладок были одинаковыми во всех файлах! Если есть имена Excel-таблиц, их увидите в этом же окне.
В моем примере у всех по одному листу под названием «Scores», выбираю его. Посмотрите на превью справа и нажмите «ОК», чтобы открыть Power Query Editor.
Шаг 5. Быстрая настройка и обработка данных в Power Query
Если вы первый раз открываете Power Query Editor, поначалу можете запутаться — на самом деле всё просто, и разберётесь за пять минут!
Как быстро подготовить данные к анализу через Power Query в Excel
Power Query — не прощелкайте этот супер-инструмент!
В левой панели список всех запросов. Обычно интересуют два: Transform Sample File (где готовятся структуры столбцов) и итоговый объединённый запрос (appended query). Менять что-то нужно как раз в них, если требуется настроить структуру итоговой таблицы.
Начните с настройки форматов столбцов в Transform Sample File — нажимайте иконку с цифрами в названиях: например, для первого, третьего и четвертого столбцов ставим тип «Целое число», второй — «Дата».
В панели справа — все шаги, которые уже были применены: по ходу дела можете откатывать лишние.
Во втором запросе (итоговом) часто не нужен столбец Source.Name, но иногда оттуда удобно взять, например, год — так вы точно не запутаетесь в данных.
Оставить только год просто: выделите столбец, перейдите на вкладку Transform и выберите «Извлечь» — «Текст до разделителя».
В появившемся окне введите разделитель — пробел, раскройте «Дополнительные параметры» и выберите «С начала строки». Жмите «ОК»!
Потом дважды кликните название этого столбца, переименуйте его в Год и поставьте тип данных «Целое число».
Прокрутите итоговую таблицу вниз и убедитесь, что всё на месте: нет ошибок, ничего не потерялось.
4 скрытых команды Power Query, которыми я реально пользуюсь
Будьте профи в работе с таблицами!
Шаг 6. Переносим объединённые данные на итоговый лист Excel
Собрали и обработали данные? Самое время увидеть итог прямо в Excel. На вкладке Home в Power Query Editor жмите верхнюю часть кнопки «Закрыть и загрузить» (Close and Load).
Если нужно — загрузите результат на уже существующий лист или как сводную таблицу, воспользовавшись нижней частью этой кнопки.
Ваша объединённая таблица появится на новом листе уже в фирменном стиле Excel-таблицы. Можно сразу менять дизайн и параметры через вкладку «Дизайн таблицы».
Что обязательно знать об Excel-таблицах (и почему без них никак)
Этот подход точно поменяет ваши привычки в Excel!
Если потребуется что-то поменять, откройте панель «Запросы и подключения» на вкладке «Данные», кликните по запросу правой кнопкой мыши и выберите «Изменить». После всех изменений снова жмите «Close and Load», чтобы обновить таблицу на листе.
Каждый раз после изменений не забывайте нажимать «Close and Load», чтобы видеть свежие данные.
Шаг 7. Добавляем новые данные в объединённую таблицу за секунду
Допустим, у вас появился новый файл за 2024 год и вы хотите вписать его в уже готовую таблицу. Power Query обновит всё автоматически — даже не придётся ничего копировать!
Просто скопируйте новый файл в ту же папку, где лежат остальные годы. Названия и структура должны совпадать.
Откройте ваш файл, где уже есть объединённая таблица. Через панель «Запросы и подключения» найдите основной запрос и нажмите иконку обновления.
Через пару секунд внизу таблицы появится свежий файл с новым годом. Всё — никаких ручных обновлений!
Если хотите сохранить своё оформление после обновлений, во вкладке «Дизайн таблицы» нажмите «Свойства» и отметьте пункт «Сохранять форматирование ячеек». Автоматическое изменение ширины столбцов там же можно отключить.
И напоследок: Power Query позволяет не только объединять разные файлы из одной папки. Вы легко соберёте данные с разных листов одного файла и даже вытащите таблицы прямо из интернета!
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru