Как объединить несколько файлов Excel в один

Обновлено: 21.11.2024

Чтобы обобщить и представить результаты из отдельных рабочих листов, вы можете объединить данные из каждого листа в основной рабочий лист. Листы могут находиться в той же рабочей книге, что и основной рабочий лист, или в других рабочих книгах. Когда вы консолидируете данные, вы собираете данные, чтобы их было легче обновлять и агрегировать по мере необходимости.

Например, если у вас есть таблица расходов для каждого из ваших региональных офисов, вы можете использовать консолидацию, чтобы свести эти цифры в основную таблицу корпоративных расходов. Этот основной рабочий лист может также содержать общие и средние данные о продажах, текущие уровни запасов и самые продаваемые продукты для всего предприятия.

Совет. Если вы часто консолидируете данные, может помочь создание новых рабочих листов на основе шаблона рабочего листа с согласованным макетом. Дополнительные сведения о шаблонах см. в разделе Создание шаблона. Это также идеальное время для настройки вашего шаблона с таблицами Excel.

Существует два способа консолидации данных: по позиции или по категории.

Консолидация по положению: данные в исходных областях имеют одинаковый порядок и используют одинаковые метки. Используйте этот метод для консолидации данных из серии таблиц, например таблиц бюджета отдела, созданных на основе одного и того же шаблона.

Консолидация по категориям: когда данные в исходных областях упорядочены не в том же порядке, но используют одни и те же метки. Используйте этот метод для объединения данных из серии листов с разными макетами, но с одинаковыми метками данных.

Консолидация данных по категориям аналогична созданию сводной таблицы. Однако с помощью сводной таблицы вы можете легко реорганизовать категории. Рассмотрите возможность создания сводной таблицы, если вам нужна более гибкая консолидация по категориям.

Примечание. Примеры в этой статье были созданы в Excel 2016. Хотя ваше представление может отличаться, если вы используете другую версию Excel, шаги те же.

Выполните следующие действия, чтобы объединить несколько листов в один главный лист:

Если вы еще этого не сделали, настройте данные на каждом составном листе, выполнив следующие действия:

Убедитесь, что каждый диапазон данных имеет формат списка. Каждый столбец должен иметь метку (заголовок) в первой строке и содержать аналогичные данные. В списке не должно быть пустых строк или столбцов.

Поместите каждый диапазон на отдельный рабочий лист, но ничего не вводите в основной рабочий лист, где вы планируете консолидировать данные. Excel сделает это за вас.

Убедитесь, что все диапазоны имеют одинаковый макет.

На главном листе щелкните верхнюю левую ячейку области, в которой должны отображаться консолидированные данные.

Примечание. Чтобы избежать перезаписи существующих данных на основном листе, убедитесь, что вы оставили достаточно ячеек справа и под этой ячейкой для консолидированных данных.

Нажмите «Данные»> «Консолидировать» (в группе «Инструменты данных»).

В поле «Функция» выберите итоговую функцию, которую вы хотите использовать для консолидации данных в Excel. Функция по умолчанию — СУММ.

Вот пример, в котором выбраны три диапазона рабочих листов:

Выберите свои данные.

Затем в поле "Ссылка" нажмите кнопку "Свернуть", чтобы уменьшить панель и выбрать данные на листе.

Нажмите на лист, содержащий данные, которые вы хотите консолидировать, выберите данные, а затем нажмите кнопку "Развернуть диалоговое окно" справа, чтобы вернуться в диалоговое окно "Консолидация".

Если лист, содержащий данные, которые необходимо консолидировать, находится в другой книге, нажмите кнопку "Обзор", чтобы найти эту книгу. После обнаружения и нажатия кнопки «ОК» Excel введет путь к файлу в поле «Ссылка» и добавит к этому пути восклицательный знак. Затем вы можете продолжить выбор других данных.

Вот пример, в котором выбраны три диапазона рабочих листов:

Во всплывающем окне «Консолидация» нажмите «Добавить». Повторите это, чтобы добавить все диапазоны, которые вы объединяете.

Автоматическое или ручное обновление. Если вы хотите, чтобы Excel автоматически обновлял сводную таблицу при изменении исходных данных, просто установите флажок Создать ссылки на исходные данные. Если этот флажок не установлен, вы можете обновить консолидацию вручную.

Вы не можете создавать ссылки, когда исходная и целевая области находятся на одном листе.

Если вам нужно изменить размер диапазона или заменить диапазон, щелкните диапазон во всплывающем окне «Консолидация» и обновите его, выполнив описанные выше действия. Это создаст новую ссылку на диапазон, поэтому вам нужно будет удалить предыдущую перед повторной консолидацией. Просто выберите старую ссылку и нажмите клавишу Delete.

Нажмите «ОК», и Excel создаст для вас консолидацию. По желанию можно применить форматирование. Форматирование необходимо только один раз, если только вы не запустите консолидацию повторно.

Любые ярлыки, которые не совпадают с ярлыками в других исходных областях, приводят к отдельным строкам или столбцам в консолидации.

Убедитесь, что все категории, которые вы не хотите объединять, имеют уникальные ярлыки, которые появляются только в одном исходном диапазоне.

Если данные для консолидации находятся в разных ячейках на разных листах:

Введите формулу со ссылками на ячейки других рабочих листов, по одной на каждый отдельный рабочий лист. Например, чтобы консолидировать данные из листов с названиями "Продажи" (в ячейке B4), "Кадры" (в ячейке F5) и "Маркетинг" (в ячейке B9), в ячейке A2 основного листа введите следующее:

Совет. Чтобы ввести ссылку на ячейку, например Sales!B4, в формулу без ввода текста, введите формулу до нужного места, затем щелкните вкладку листа и щелкните ячейку. Excel дополнит имя листа и адрес ячейки за вас. ПРИМЕЧАНИЕ: формулы в таких случаях могут быть подвержены ошибкам, так как очень легко случайно выбрать не ту ячейку. Также может быть сложно обнаружить ошибку после ввода сложной формулы.

Если данные для консолидации находятся в одних и тех же ячейках на разных листах:

Введите формулу с трехмерной ссылкой, которая использует ссылку на диапазон имен рабочих листов. Например, чтобы консолидировать данные в ячейках A2 из раздела "Продажи через маркетинг" включительно, в ячейку E5 основного листа введите следующее:

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Если вы спросите людей, которые работают с данными, вы узнаете, что объединение файлов Excel или слияние книг является частью их повседневной работы.

Простой пример: допустим, вы хотите создать отчет о продажах и у вас есть данные о четырех разных зонах в четырех разных файлах.

Самое первое, что вам нужно сделать, это объединить эти файлы в одну книгу, и только после этого вы сможете создавать свой отчет дальше.

Дело в том, что у вас должен быть метод, который вы можете использовать для слияния этих файлов. Скажите «ДА» в разделе комментариев, если вы хотите узнать лучший способ для этого.

Сегодня в этом посте я поделюсь с вами лучшим способом объединения данных из нескольких файлов Excel в ОДНУ книгу.

Но вот в чем фишка.

Этот пост научит вас тому, что вам нужно научиться использовать в реальных задачах с данными, поэтому обязательно прочитайте весь пост.

Наилучший способ объединения файлов Excel путем объединения данных в ОДНУ книгу — POWER QUERY

Power Query — это лучший способ объединить данные из нескольких файлов Excel в один файл. Вам нужно хранить все файлы в одной папке, а затем использовать эту папку для загрузки данных из этих файлов в редактор запросов Power. Это также позволяет вам преобразовывать эти данные вместе с их объединением.

Это работает примерно так:

Обязательно загрузите этот образец файла отсюда, чтобы следовать инструкциям, и ознакомьтесь с этим руководством, чтобы изучить запрос мощности.

Примечание. Для объединения данных из разных файлов Excel ваши данные должны быть структурированы одинаково. Это означает, что количество столбцов и их порядок должны быть одинаковыми.

Чтобы объединить файлы, выполните следующие действия:

Теперь у вас есть объединенные данные (из всех книг) в одну книгу.

Это момент РАДОСТИ, напишите «Радость» в разделе комментариев, если вам нравится использовать «Power Query для объединения данных из нескольких файлов».

Важный момент

В приведенных выше шагах мы использовали имя таблицы, чтобы объединить данные из всех файлов и добавить их все в одну книгу.Но не всегда у вас будет одно и то же имя таблицы во всех файлах Excel, и в этот момент вы можете использовать имя рабочего листа в качестве ключа для суммирования всех этих данных.

Еще одно:

Как я уже сказал, вы можете использовать имя рабочего листа для объединения данных с запросом мощности, но есть еще несколько вещей, которыми я хочу поделиться с вами, и вам нужно позаботиться об этом. Power Query чувствителен к регистру, поэтому при объединении файлов убедитесь, что имена листов во всех книгах написаны одними и теми же буквами.

Следующее, что нужно сделать, чтобы заголовки столбцов имели одинаковое имя, но здесь есть фишка: порядок столбцов не имеет значения. Если столбец 1 в файле north.xlsx равен столбцу 2 в файле west.xlsx, Power Query сопоставит его, но у вас должны быть одинаковые имена столбцов.

Итак, теперь при объединении файлов с помощью PowerQuery вы можете использовать имя рабочего листа вместо имени таблицы, и здесь у вас есть "SalesData" в качестве имени рабочего листа во всех файлах.

Вы выбираете его и нажимаете «Объединить и редактировать» и выполняете все шаги, которые я упомянул в приведенном выше методе.

Почему Power Query — лучший способ объединить данные в один файл?

Да, я большой поклонник Power Query, и вы станете им, если узнаете следующее:

Реальное время:

После того как вы создадите запрос, объединяющий данные из нескольких книг в одну, вам не нужно делать это снова и снова.

Если вы добавите новые файлы в эту папку, вам просто нужно обновить таблицу,

или обновите запрос

или используйте кнопку обновления.

Запрос мощности обновит вашу таблицу, и вы получите все данные в таблицу из всех книг, которые у вас есть в этой папке.

Это быстро:

Если объединять файлы вручную или даже любым другим способом, вам потребуется время, чтобы скопировать и вставить данные в одну таблицу.

Но power query делает это в фоновом режиме, и вам не нужно тратить ни секунды лишнего.

Объединение данных из нескольких рабочих книг, если у вас разные имена для рабочих листов и данных в таблицах

Это суровая правда…

…что в некоторых ситуациях у вас не будет одного и того же имени для рабочих листов и не все данные в таблицах все время.

Что делать в таком случае?

…в этом случае вы должны знать, как можно объединить данные из всех файлов, и я не хочу упускать возможность поделиться с вами этой вещью.

Обязательно загрузите эти примеры файлов и сохраните их на рабочем столе.

<р>. Итак, без лишних слов, приступим.

Прежде всего откройте диалоговое окно "Из папки", чтобы найти папку, в которой находятся все файлы.
Теперь в этом диалоговом окне найдите папку и нажмите "ОК".

После этого нажмите «Изменить», чтобы отредактировать таблицу.

На этом этапе у вас будет таблица, как показано ниже, в вашем редакторе запросов.

Затем выберите первые два столбца таблицы и нажмите «Удалить другие столбцы» в контекстном меню.

Здесь нам нужно добавить настраиваемый столбец для извлечения данных из рабочих листов рабочих книг.

Для этого перейдите на вкладку "Добавить столбец" и нажмите кнопку "Пользовательский столбец". Откроется диалоговое окно «Пользовательский столбец».

В диалоговом окне введите =Excel.Workbook([Content]) и нажмите OK.

… сейчас у вас есть новый столбец в таблице, но затем вам нужно извлечь из него данные.

Теперь откройте фильтр из только что добавленного пользовательского столбца и нажмите "ОК", чтобы развернуть все данные в таблице.

Здесь у вас есть недавно расширенная таблица с некоторыми новыми столбцами.
Теперь из этой новой таблицы удалите все столбцы, кроме третьего и четвертого.

Как только вы нажмете OK, вы получите все данные из всех файлов в одну таблицу…

… вам нужно внести в него некоторые изменения, чтобы сделать его ИДЕАЛЬНЫМ.

Если вы заметили, все заголовки столбцов находятся в самих данных.

<р>. поэтому вам нужно добавить заголовки столбцов.

Для этого нужно дважды щелкнуть заголовок и добавить имя или щелкнуть правой кнопкой мыши и выбрать переименование.

Далее вам нужно исключить заголовки, которые у вас есть в таблице данных.

Теперь откройте параметр фильтра любого столбца и отмените выбор имени заголовка, который у вас есть в данных столбца, и после этого нажмите "ОК".

Теперь наши данные готовы для загрузки на лист, поэтому перейдите на вкладку "Главная" и нажмите "Закрыть и загрузить".

<р> Поздравляем! вы только что объединили данные из разных рабочих книг (с разными именами рабочих листов и без какой-либо таблицы).

Также важно:

На этом этапе вы объединили данные в одну таблицу.

Но есть одна вещь, которую вам нужно сделать…

…и для этого нужно применить к нему некоторое форматирование и убедиться, что оно не исчезнет, когда вы обновите свои данные.

Вот что вам нужно сделать…

Прежде всего выберите столбец, в котором у вас есть даты (поскольку сейчас он отформатирован как число), и отформатируйте его как даты.
После этого сделайте ширину всех столбцов в соответствии с имеющимися в них данными.
Здесь вы также можете отформатировать сумму и цену как «Валюта».

Но следующее, что нужно сделать, это исправить форматирование.

Для этого перейдите на вкладку "Дизайн" и откройте свойства.
Снимите флажок «Настроить ширину столбца» и установите флажок «Сохранить форматирование ячейки».
Да, это так.

Теперь в вашей книге есть запрос, который может объединять данные из нескольких файлов.

<р>. и объединить ее в одну книгу.

<р>. даже если имя рабочего листа не совпадает или у вас нет таблиц.

И да, вы также исправили форматирование. ?

В конце концов,

Как я уже сказал, POWER QUERY реален, и если вы часто используете для объединения данных из нескольких файлов, вы должны использовать этот метод…

…поскольку это ОДНОРАЗОВАЯ настройка.

Самое главное, что когда вы используете power query, вы даже можете очистить данные из этих файлов.

Я надеюсь, что это руководство поможет вам стать лучше в Excel. Но сейчас ты должен сказать мне одну вещь.

Какой метод вы используете для объединения данных из нескольких файлов?

Не забудьте поделиться со мной своим мнением в разделе комментариев, я буду рад услышать от вас. И, пожалуйста, не забудьте поделиться этим постом с друзьями, я уверен, что они это оценят.

Вы должны прочитать это Далее

: этот параметр может помочь вам объединить данные из нескольких рабочих листов в один. : В этой ситуации вам нужно приложить некоторые усилия и потратить свое драгоценное время, чтобы сделать его многоразовым. : В этом посте я хотел бы показать вам трехэтапный процесс создания сводной таблицы с использованием данных из нескольких источников.

Об авторе

Пунит использует Excel со времен учебы в колледже. Он помог тысячам людей понять возможности электронных таблиц и изучить Microsoft Excel. Вы можете найти его в Интернете, в твиттере об Excel, на беговой дорожке, а иногда и в горах.

У вас есть несколько файлов Excel, которые затрудняют доступ к данным. Вы постоянно переключаетесь между разными открытыми файлами, и этот процесс занимает у вас много времени. Вы прекрасно знаете, что гораздо проще обрабатывать данные в одном файле, чем переключаться между многочисленными источниками. Тогда возникает вопрос, как я могу объединить все эти файлы в один всеобъемлющий файл? Попытка объединить разные файлы Excel в один файл, учитывая количество рабочих листов, найденных в одной книге, является хлопотным и длительным процессом. Существуют различные способы объединения файлов Excel.

В этой статье мы обсудим, как объединить несколько файлов Excel в один файл. Приступим.

Способ 1. Объединение нескольких книг в одну с помощью функции «Переместить или скопировать»

Оглавление

<р>1. Если вы хотите объединить все существующие файлы в новую книгу Excel, создайте новую книгу Excel и откройте ее. Но если вы собираетесь объединить их все в текущую книгу, откройте эту книгу.

2. Откройте все файлы Excel, которые вы хотите объединить. Вам нужно открыть все файлы, чтобы иметь возможность объединить их в один. Вместо того, чтобы делать это вручную, выберите все файлы и нажмите клавишу ввода на клавиатуре. Чтобы выбрать несколько несмежных файлов, удерживайте клавишу Ctrl и щелкните файлы один за другим. Для соседних файлов удерживайте клавишу Shift и щелкните последний файл, чтобы выделить их все.

<р>3. Разверните первый файл, который хотите объединить.

<р>4. Щелкните правой кнопкой мыши лист, который хотите объединить, и выберите "Переместить" или "Копировать".

<р>5. Во всплывающем окне нажмите «Выбрать из раскрывающегося списка». Здесь будут отображаться все файлы Excel, открытые на вашем компьютере

<р>6. Выберите файл Excel, в который вы хотите объединить другие файлы, в раскрывающемся списке "Забронировать".

<р>7. Чтобы объединить файлы Excel, установите флажок Создать копию.

<р>8. В разделе «Перед листом» выберите «переместить в конец» и нажмите «ОК». Будет создана копия рабочего листа в целевом файле.

<р>9. Повторите все вышеуказанные шаги для всех оставшихся файлов и сохраните файл.

Метод 2. Объединение нескольких книг в одну с помощью VBA

<р>1. Откройте новую книгу, которая будет главной книгой.

<р>2. Нажмите Alt + F11, чтобы перейти на страницу VBA

<р>3. Нажмите на вкладку «Вставка». Затем выберите вкладку Модуль.

Если вы получаете информацию на нескольких листах или книгах, которую хотите обобщить, команда «Объединить» может помочь вам собрать данные на одном листе. Например, если у вас есть лист данных о расходах каждого из ваших региональных офисов, вы можете использовать консолидацию, чтобы свести эти цифры в корпоративный отчет о расходах. Этот лист может содержать общие и средние данные о продажах, текущие уровни запасов и самые продаваемые продукты для всего предприятия.

Чтобы решить, какой тип консолидации использовать, просмотрите листы, которые вы объединяете. Если листы содержат данные в непоследовательных позициях, даже если их метки строк и столбцов не идентичны, выполните консолидацию по позициям. Если таблицы используют одни и те же метки строк и столбцов для своих категорий, даже если данные не находятся в одинаковых позициях, консолидируйте по категориям.

Объединить по позиции

Чтобы консолидация по положению работала, диапазон данных на каждой исходной таблице должен быть в формате списка без пустых строк или пустых столбцов в списке.

Откройте каждый исходный лист и убедитесь, что ваши данные находятся в одном и том же месте на каждом листе.

На целевом листе щелкните верхнюю левую ячейку области, в которой вы хотите отобразить консолидированные данные.

Примечание. Убедитесь, что справа и снизу вы оставили достаточно ячеек для консолидированных данных.

На вкладке "Данные" в группе "Инструменты данных" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

На каждом исходном листе выберите свои данные.

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

Объединить по категориям

Чтобы консолидация по категориям работала, диапазон данных на каждом исходном листе должен быть в формате списка без пустых строк или пустых столбцов в списке. Также категории должны быть последовательно помечены. Например, если один столбец помечен как Avg. а другой помечен как «Среднее», команда «Объединить» не будет суммировать два столбца вместе.

Откройте каждый исходный лист.

Примечание. Убедитесь, что справа и снизу вы оставили достаточно ячеек для консолидированных данных.

На вкладке "Данные" в группе "Инструменты данных" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

Чтобы указать, где находятся метки в исходных диапазонах, установите флажки в разделе "Использовать метки в: верхней строке, левом столбце или в обоих".

На каждом исходном листе выберите свои данные. Не забудьте включить ранее выбранную информацию из верхней строки или левого столбца.

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

Примечание. Любые ярлыки, которые не совпадают с ярлыками в других исходных областях, приводят к разделению строк или столбцов в консолидации.

Объединить по позиции

Откройте каждый исходный лист и убедитесь, что ваши данные находятся в одном и том же месте на каждом листе.

Примечание. Убедитесь, что справа и снизу вы оставили достаточно ячеек для консолидированных данных.

На вкладке "Данные" в разделе "Инструменты" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

В каждом исходном листе выберите свои данные и нажмите "Добавить".

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

Объединить по категориям

Откройте каждый исходный лист.

Примечание. Убедитесь, что справа и снизу вы оставили достаточно ячеек для консолидированных данных.

На вкладке "Данные" в разделе "Инструменты" нажмите "Консолидировать".

В поле "Функция" выберите функцию, которую Excel должен использовать для консолидации данных.

На каждом исходном листе выберите свои данные. Убедитесь, что вы включили ранее выбранную информацию из верхней строки или левого столбца, а затем нажмите "Добавить".

Путь к файлу указан во всех ссылках.

Добавив данные из каждого исходного листа и книги, нажмите кнопку "ОК".

Читайте также: