Составьте краткое изложение этой истории. Вот краткое изложение содержания истории, основанное на фактах: Вот упрощенная версия содержания истории: Вот беззаботный взгляд на содержание истории: Исследуйте другую точку зрения: Покажите мне факты Объясните это так, будто мне 5 лет Дайте мне беззаботный пересказ
Ручное объединение данных из нескольких листов может быть крайне трудоёмким и легко привести к ошибкам копирования. Более того, этот процесс становится ещё более обременительным, если вам нужно регулярно обновлять набор данных. К счастью, инструмент Power Query в Excel может сделать всё это за вас.
Хотя в этом руководстве объясняется, как извлекать информацию из различных файлов XLSX, вы можете выполнить аналогичный процесс с файлами TXT, CSV и XML.
Шаг 1: Организуйте рабочие книги, которые вы собираетесь объединить
Прежде чем приступить к объединению файлов Microsoft Excel в один лист, необходимо выполнить несколько важных шагов.
Во-первых, убедитесь, что все наборы данных, которые вы собираетесь объединить, имеют одинаковую структуру. В частности, они должны содержать одинаковое количество столбцов, иметь одинаковые заголовки столбцов, а вкладки листов должны иметь одинаковые имена. Кроме того, процесс будет гораздо проще, если имена файлов будут иметь схожий синтаксис.
При этом столбцы не обязательно должны располагаться в одинаковом порядке, а количество строк на каждом листе может отличаться.
Во-вторых, убедитесь, что файлы сохранены в одной папке.
Если возможно, папка должна содержать Важно файлы, которые вы собираетесь объединить, так как это делает процесс более понятным и простым на последующих этапах. Однако, если вы Чёрный Сохраните в этой папке и другие файлы. Скоро я покажу вам, как обеспечить выбор только нужных файлов для слияния данных.
Шаг 2: Откройте Excel и создайте ссылку на соответствующую папку.
Как только файлы будут правильно организованы в папке в соответствии с вышеуказанными требованиями, вы будете готовы приступить к сбору данных.
Сначала откройте новую книгу Microsoft Excel, начните с пустого листа и нажмите F12, чтобы сохранить файл в различный папку, в которой находятся отдельные файлы, которые вы собираетесь объединить.
Затем на вкладке «Данные» на ленте нажмите «Получить данные». Затем наведите курсор на «Из файла» и выберите «Из папки».
Теперь в диалоговом окне «Обзор» найдите и выберите папку, в которой хранятся файлы, которые вы хотите объединить, и нажмите «Открыть».
Шаг 3: Выберите файлы, которые вы хотите объединить
Действия на этом этапе зависят от содержимого выбранной папки. Если она содержит только файлы, которые вы хотите объединить, см. раздел 3A ниже. Если же папка содержит также файлы, которые вы не хотите объединять, перейдите к разделу 3B.
3A: Папка содержит только те файлы, которые я хочу объединить
Ранее я говорил, что процесс объединения книг будет проще и понятнее, если выбранная папка предназначена для файлов, которые вы хотите объединить. Это связано с тем, что вам не нужно отфильтровывать ненужные файлы. Итак, поскольку вы уже готовы к объединению файлов, нажмите «Объединить» и в появившемся раскрывающемся меню выберите «Объединить и преобразовать данные».
Теперь, после того как Excel быстро оценит ваши данные, вы увидите диалоговое окно «Объединить файлы», и вы готовы перейти к шагу 4.
3B: В папке есть файлы, которые я не хочу объединять
Если выбранная папка содержит файлы, отличные от тех, которые вы хотите объединить, после того, как вы найдёте папку на шаге 2, вы увидите их список в списке. В этом случае вам нужно нажать кнопку «Преобразовать данные», так как вы хотите внести изменения в выборку файлов перед объединением данных.
При выборе этого параметра запускается редактор Power Query — отдельное окно в Microsoft Excel, в котором можно отфильтровать файлы, которые не нужно включать в процесс объединения.
В этом случае необходимо исключить файл с именем «Контактные данные», поэтому щелкните стрелку раскрывающегося списка фильтра в заголовке столбца «Имя», снимите флажок с этого файла и нажмите «ОК».
Теперь в списке отображаются только те файлы, которые вы хотите объединить. Нажмите двойную стрелку вниз в столбце «Содержимое», чтобы открыть диалоговое окно «Объединить файлы».
Шаг 4: Объедините файлы
В диалоговом окне «Объединение файлов» следует обратить внимание на несколько моментов.
Во-первых, по умолчанию в качестве файла-образца выбрана первая книга в списке. Структура данных при объединении файлов будет определяться файлом-образцом, поэтому, если все ваши файлы структурированы единообразно, можно оставить этот параметр без изменений.
Во-вторых, если в ваших книгах несколько вкладок листов, вы увидите их список на панели «Параметры отображения» слева. Поэтому важно, чтобы все вкладки, которые вы хотите объединить в выбранных книгах, имели одинаковые имена. Кроме того, если вы отформатировали данные в виде таблиц Excel и дали им имена, вы увидите список имён здесь.
Однако в этом примере каждая книга содержит только один лист (с именем «Результаты»), поэтому выберите этот вариант. После просмотра выбранного файла-примера в правой панели нажмите «ОК» для подтверждения и запуска редактора Power Query.
Шаг 5. Преобразуйте данные
Если вы впервые открываете редактор Power Query в Excel, поначалу он может показаться сложным. Однако, разобравшись с функциями различных разделов редактора, вы поймёте, что это на самом деле интуитивно понятный инструмент.
В левой панели находятся все запросы. При объединении данных из разных книг Excel вам нужно сосредоточиться только на двух из них: запросе «Преобразовать файл образца», который используется в качестве шаблона для любых изменений, которые вы хотите внести в наборы данных, и добавленном запросе внизу списка, который объединяет файлы. Однако, в зависимости от ваших задач, оба запроса могут быть полезны для преобразования данных.
Сначала в запросе «Преобразовать пример файла» щёлкните по значкам числовых форматов в заголовках каждого столбца, чтобы указать Excel, какие типы данных они содержат. В этом примере первый, третий и четвёртый столбцы содержат целые числа, а второй столбец — дату.
На панели «Параметры запроса» в правой части редактора Power Query просмотрите (и удалите при необходимости) каждый шаг, который вы предпринимаете при преобразовании данных.
Далее, в добавленном запросе нам не нужен столбец «Название источника», но было бы удобно указать год для каждого результата команды.
Вы можете преобразовать этот столбец, оставив только годы и удалив остальные имена файлов. Для этого щёлкните заголовок столбца, чтобы выбрать все его значения, и на вкладке «Преобразование» на ленте нажмите «Извлечь», а затем «Текст перед разделителем».
В данном случае мы хотим сохранить год и удалить всё после первого пробела. Поэтому в поле «Разделитель» введите один пробел. Затем нажмите «Дополнительные параметры» и выберите «С начала ввода». Затем нажмите «ОК».
Наконец, дважды щелкните заголовок столбца, чтобы переименовать его. Годи измените тип данных на «Целое число».
Уделите немного времени прокрутке списка объединенных данных, чтобы убедиться в отсутствии ошибок или дополнительных преобразований, которые вы хотели бы внести.
Шаг 6: загрузите объединенные данные на новый рабочий лист
Теперь, когда данные из рабочих книг объединены и преобразованы, пора посмотреть, как они выглядят на обычном листе Excel. На вкладке «Главная» в окне редактора Power Query нажмите верхнюю половину кнопки «Закрыть и загрузить».
Нажмите нижнюю часть кнопки «Закрыть и загрузить», чтобы увидеть дополнительные параметры загрузки. Например, вы можете загрузить данные на существующий лист или в сводную таблицу.
Теперь данные загружаются на новый лист как отформатированная таблица Excel, то есть вы можете форматировать ее внешний вид и свойства на вкладке «Конструктор таблиц» на ленте.
Чтобы внести дополнительные изменения в запрос в редакторе Power Query, после активации панели «Запросы и подключения» на вкладке «Данные» щелкните запрос правой кнопкой мыши и выберите «Изменить».
После внесения необходимых изменений в редакторе Power Query снова нажмите кнопку «Закрыть и загрузить», чтобы обновить полученный набор данных.
Шаг 7: Добавьте данные из дополнительных рабочих книг
Наконец, представим, что данные за 2024 год уже поступили, и вы хотите добавить эту новую информацию в существующий запрос. Удивительно, но, поскольку вы уже выполнили необходимые действия в редакторе Power Query, вам остаётся только убедиться, что книга соответствует всем критериям, описанным в шаге 1, и нажать одну кнопку в Excel, чтобы обновить таблицу.
Сначала переместите рабочую книгу с данными в папку, где находятся данные за предыдущие годы, убедившись, что имя рабочей книги, структура рабочего листа и имя вкладки соответствуют другим файлам.
Затем откройте книгу, содержащую ранее добавленные данные, и на панели «Запросы и подключения» (которую можно активировать, щелкнув «Запросы и подключения» на вкладке «Данные» на ленте) щелкните значок «Обновить» рядом с основным запросом.
Подождите несколько секунд, пока Excel завершит обновление, а затем прокрутите страницу вниз, чтобы увидеть дополнительные данные, добавленные в конец таблицы.
Чтобы сохранить форматирование, примененное к таблице перед обновлением Power Query, нажмите кнопку «Свойства» на вкладке «Конструктор таблиц» на ленте и убедитесь, что установлен флажок «Сохранить форматирование ячеек». Если вы изменяете ширину столбцов и не хотите, чтобы она автоматически подстраивалась под содержащиеся в них данные при каждом обновлении, снимите флажок «Настроить ширину столбцов».
Помимо добавления данных из отдельных книг, сохранённых в папке, вы можете объединять данные из нескольких листов Excel в одну книгу, создавая между ними связи и объединяя запросы. Более того, вы можете использовать редактор Power Query для импорта таблиц из Интернета.
