Объединение нескольких листов в один
В учебном пособии демонстрируются различные способы объединения листов в Excel в зависимости от того, к какому результату вы стремитесь: объединить данные из нескольких листов, объединить несколько листов, скопировав их данные, или объединить две таблицы Excel в одну по ключевому столбцу.
Сегодня мы займемся проблемой, с которой ежедневно борются многие пользователи Excel — как объединить несколько листов Excel в один без копирования и вставки. Учебное пособие охватывает два наиболее распространенных сценария: объединение числовых данных (сумма, количество и т. д.) и объединение листов (т. е. копирование данных из нескольких рабочих листов в один).
Консолидация данных из нескольких рабочих листов на одном рабочем листе
Самый быстрый способ консолидировать данные в Excel (находящиеся в одной книге или нескольких книгах) — использовать встроенную функцию консолидации Excel.
Рассмотрим следующий пример. Предположим, у вас есть несколько отчетов из региональных офисов вашей компании, и вы хотите объединить эти цифры в основной рабочий лист, чтобы у вас был один сводный отчет с общими продажами всех продуктов.
Как вы видите на снимке экрана ниже, три консолидируемых листа имеют одинаковую структуру данных, но разное количество строк и столбцов:
Чтобы объединить данные на одном листе, выполните следующие действия:
- Правильно оформить исходные данные. Чтобы функция консолидации Excel работала правильно, убедитесь, что:
- Каждый диапазон (набор данных), который вы хотите консолидировать, находится на отдельном листе. Не размещайте никаких данных на листе, где вы планируете выводить консолидированные данные.
- Каждый лист имеет одинаковую компоновку, а каждый столбец имеет заголовок и содержит аналогичные данные.
- В любом списке нет пустых строк или столбцов.
- Запустите консолидацию Excel. На главном листе щелкните верхнюю левую ячейку, в которой должны отображаться консолидированные данные, перейдите к Данные вкладку и нажмите Консолидировать.
Кончик. Желательно консолидировать данные на пустой лист. Если на основном листе уже есть данные, убедитесь, что на нем достаточно места (пустые строки и столбцы) для объединенных данных.
- Настройте параметры консолидации. Консолидировать появляется диалоговое окно, и вы делаете следующее:
- в Функция выберите одну из функций суммирования, которую вы хотите использовать для консолидации данных (счетчик, среднее, макс., мин. и т. д.). В этом примере мы выбираем Сумма.
- в Ссылка поле, нажав на Свернуть диалоговое окно значок
и выберите диапазон на первом листе. Затем щелкните Добавлять кнопку, чтобы добавить этот диапазон в Все ссылки Повторите этот шаг для всех диапазонов, которые вы хотите объединить.
Если один или несколько листов находятся в другой книге, нажмите кнопку Просматривать внизу, чтобы найти книгу.
- Настройте параметры обновления. В то же самое Консолидировать диалоговом окне выберите любой из следующих параметров:
- Установите флажки Верхний ряд и/или Левый столбец под Используйте ярлыки если вы хотите, чтобы метки строк и/или столбцов исходных диапазонов были скопированы в консолидацию.
- Установите флажок Создать ссылки на исходные данные, если вы хотите, чтобы консолидированные данные автоматически обновлялись при каждом изменении исходных данных. В этом случае Excel создаст ссылки на ваши исходные листы, а также схему, как показано на следующем снимке экрана.
Если развернуть какую-либо группу (нажав на символ контура плюса), а затем щелкнуть по ячейке с определенным значением, то в строке формул отобразится ссылка на исходные данные.
и выберите диапазон на первом листе. Затем щелкните Добавлять кнопку, чтобы добавить этот диапазон в Все ссылки Повторите этот шаг для всех диапазонов, которые вы хотите объединить.
Как видите, функция Excel Consolidate очень полезна для объединения данных из нескольких рабочих листов. Однако у него есть несколько ограничений. В частности, он работает только с числовыми значениями и всегда тем или иным образом суммирует эти числа (сумма, количество, среднее и т. д.).
Если вы хотите объединить листы в Excel, скопировав их данные, вариант консолидации не подходит. Чтобы объединить всего пару листов, вам может не понадобиться ничего, кроме старого доброго копирования/вставки. Но если вам предстоит объединить десятки листов, ошибки при ручном копировании/вставке неизбежны. В этом случае вы можете использовать один из следующих методов для автоматизации слияния.
Как объединить листы Excel в один
Всего существует четыре способа объединения рабочих листов Excel в один без копирования и вставки:
Как объединить электронные таблицы Excel с Ultimate Suite
встроенный Excel Консолидация Функция может суммировать данные с разных листов, но не может объединять листы путем копирования их данных. Для этого вы можете использовать один из инструментов слияния и объединения, включенных в наш Ultimate Suite for Excel.
Объедините несколько рабочих листов в один с помощью Copy Sheets
Предположим, у вас есть несколько электронных таблиц, содержащих информацию о различных продуктах, и теперь вам нужно объединить эти таблицы в один сводной рабочий лист, например:
Когда на ленту добавлены листы копирования, достаточно выполнить 3 простых шага, чтобы объединить выбранные листы в один.
- Запустите мастер копирования листов.
На ленте Excel перейдите к Способности вкладка, Объединить нажмите «Копировать листы» и выберите один из следующих вариантов:
- Скопируйте листы в каждой книге на один лист и поместите полученные листы в одну книгу.
- Объединить листы с одинаковыми именами в один.
- Скопируйте выбранные листы в одну книгу.
- Объединить данные с выбранных листов в один лист.
Поскольку мы хотим объединить несколько листов, скопировав их данные, мы выбираем последний вариант:
- Выберите рабочие листы и, при необходимости, диапазоны для объединения.
Копировать листы мастер отображает список всех листов во всех открытых книгах. Выберите рабочие листы, которые вы хотите объединить, и нажмите Следующий.
Если вы не хотите копировать все содержимое определенного рабочего листа, используйте Свернуть диалоговое окно значок, чтобы выбрать нужный диапазон, как показано на снимке экрана ниже.
В этом примере мы объединяем первые три листа:
Кончик. Если листы, которые вы хотите объединить, находятся в другой книге, которая в данный момент закрыта, нажмите кнопку Добавить файлы… кнопку для просмотра этой книги.
- Выберите способ объединения листов.
На этом шаге вы должны настроить дополнительные параметры, чтобы ваши рабочие листы были объединены именно так, как вы хотите.
Как вставить данные:
- Вставить все – скопировать все данные (значения и формулы). В большинстве случаев это возможность выбора.
- Вставить только значения – если вы не хотите, чтобы формулы из исходных листов вставлялись в сводную таблицу, выберите этот параметр.
- Создание ссылок на исходные данные – это вставит формулы, связывающие объединенные данные с исходными данными. Выберите этот параметр, если хотите, чтобы объединенные данные автоматически обновлялись при изменении любых исходных данных. Он работает аналогично Создание ссылок на исходные данные вариант консолидации Excel.
Как упорядочить данные:
- Поместите скопированные диапазоны один под другим – располагать скопированные диапазоны вертикально.
- Поместите скопированные диапазоны рядом – расположите скопированные диапазоны горизонтально.
Как скопировать данные:
- Сохранить форматирование – понятно и очень удобно.
- Разделите скопированные диапазоны пустой строкой – выберите этот параметр, если хотите добавить пустую строку между данными, скопированными с разных рабочих листов.
- Скопируйте таблицы с их заголовками. Установите этот флажок, если вы хотите, чтобы заголовки таблиц были включены в результирующий лист.
На скриншоте ниже показаны настройки по умолчанию, которые отлично подходят для нас:
Нажмите кнопку «Копировать», и информация с трех разных листов будет объединена в один итоговый рабочий лист, как показано в начале этого примера.
Другие способы объединения листов в Excel
Отдельно от Копировать листы Мастер Ultimate Suite for Excel предоставляет еще несколько инструментов слияния для работы с более специфическими сценариями.
Пример 1. Объединение листов Excel с другим порядком столбцов
Когда вы имеете дело с листами, созданными разными пользователями, порядок столбцов часто отличается. Как вы справляетесь с этим? Будете ли вы копировать листы вручную или перемещать столбцы на каждом листе? Ни один! Передайте задание нашему мастеру объединения листов:
И данные будут прекрасно объединяться по заголовкам столбцов:
Пример 2. Объединение определенных столбцов из нескольких листов
Если у вас действительно большие листы с множеством разных столбцов, вы можете объединить только самые важные из них в сводную таблицу. Запустите Объединить рабочие листы мастера и выберите соответствующие столбцы. Да, это так просто!
В результате в сводную таблицу попадают только данные из выбранных вами столбцов:
Эти примеры демонстрируют только пару наших инструментов слияния, но это гораздо больше! Немного поэкспериментировав, вы увидите, насколько полезны все функции. Полнофункциональная пробная версия Ultimate Suite доступна для скачивания в конце этого поста.
Объединить листы в Excel с помощью кода VBA
Если вы являетесь опытным пользователем Excel и чувствуете себя комфортно с макросами и VBA, вы можете объединить несколько листов Excel в один, используя, например, какой-нибудь сценарий VBA. Вот этот.
Имейте в виду, что для правильной работы кода VBA все исходные рабочие листы должны иметь одинаковую структуру, одинаковые заголовки столбцов и одинаковый порядок столбцов.
Объединение данных с нескольких листов с помощью Power Query
Power Query — очень мощная технология для объединения и уточнения данных в Excel. При этом он довольно сложный и требует длительного обучения. В следующем учебном пособии подробно объясняются общие способы использования: Объединение данных из нескольких источников данных (Power Query).
Как объединить два листа Excel в один по ключевому столбцу (столбцам)
Если вы ищете быстрый способ сопоставить и объединить данные из двух рабочих листов, вы можете либо использовать функцию Excel VLOOKUP, либо воспользоваться мастером объединения таблиц. Последний представляет собой визуальный удобный инструмент, который позволяет сравнивать две электронные таблицы Excel по общему столбцу (столбцам) и извлекать совпадающие данные из таблицы поиска. На следующем снимке экрана показан один из возможных результатов.
Мастер объединения таблиц также входит в состав Ultimate Suite for Excel.
Вот как вы консолидируете данные и объединяете листы в Excel. Я надеюсь, что вы найдете информацию в этом кратком руководстве полезной. В любом случае, я благодарю вас за чтение и с нетерпением жду встречи с вами в этом блоге на следующей неделе!
Доступные загрузки
Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)
