
Консолидация данных и объединение листов в Excel

6 примеров: как объединить данные и объединить листы Excel в один
В этой статье обсуждаются различные способы объединения листов в Excel в зависимости от того, какой результат вы хотите получить:
- объединить все данные из выбранных листов,
- объединить несколько листов с разным порядком столбцов,
- объединить определенные столбцы из нескольких листов,
- объединить две таблицы Excel в одну по ключевым столбцам.
Сегодня мы займемся проблемой, с которой многие пользователи Excel сталкиваются каждый день: как объединить листы Excel в один без использования операций копирования и вставки. Давайте рассмотрим два наиболее распространенных сценария: сочетание числовых данных (сумма, количество, среднее значение и т.д.) и сочетание листов (то есть копирование данных с нескольких листов в один).
Консолидация данных из нескольких листов на одном.
Самый быстрый способ консолидировать данные в Excel (в одну или несколько книг) — использовать встроенную функцию консолидации Excel.
Рассмотрим следующий пример. Предположим, у вас есть несколько отчетов из региональных офисов вашей компании, и вы хотите объединить эти числа в один основной рабочий лист, чтобы получить сводный отчет, показывающий итоги продаж по всем позициям.
Как вы можете видеть на снимке экрана ниже, четыре объединенных листа имеют аналогичную структуру данных, но разное количество строк и столбцов:
Чтобы объединить всю эту информацию в один лист, выполните следующие действия:
- Правильно оформляйте исходные данные. Чтобы функция консолидации Excel работала правильно, убедитесь, что:
- Каждый диапазон (набор данных), который вы хотите объединить, находится на отдельном листе. Не вводите данные на листе, на котором вы планируете распечатать сводные данные.
- Каждый лист имеет одинаковый макет, и каждый столбец имеет заголовок и содержит похожие данные.
- Ни в одном списке нет пустых строк или столбцов.
- Запустите инструмент консолидации. На новом листе, на котором вы планируете разместить результаты, щелкните верхнюю левую ячейку, в которой вы хотите просмотреть консолидированные данные, затем на ленте перейдите на вкладку «Данные» и нажмите кнопку «Консолидировать».
Совет. Рекомендуется объединить данные в чистый лист. Если основной рабочий лист уже содержит данные, убедитесь, что есть достаточно места (пустые строки и столбцы) для записи результатов.
- Задайте параметры консолидации. Появится диалоговое окно «Консолидация», в котором вы выполните следующие действия:
- В поле Функция выберите одну из функций, которую вы хотите использовать для консолидации данных (количество, среднее, максимальное, минимальное и т.д.). В этом примере мы выбираем Sum.
- В окне справки щелкните поле «Ссылка на значок», чтобы выбрать диапазон на первом листе. Затем нажмите кнопку «Добавить», чтобы добавить его в список диапазонов. Повторите этот шаг для всех листов, которые хотите объединить.
Если один или несколько листов находятся в другой книге, используйте кнопку «Обзор», чтобы найти эту книгу и использовать ее.
- Настройте параметры обновления. В том же диалоговом окне Консолидация выберите один из следующих вариантов:
- Установите флажки «Ярлыки верхних строк» и / или «Значения левого столбца» в разделе «Использовать как имена», если вы также хотите скопировать заголовки строк и / или столбцов исходных диапазонов.
- Установите флажок Создать ссылки на исходные данные, если вы хотите, чтобы консолидированные данные обновлялись автоматически при изменении исходных таблиц. В этом случае Excel создаст ссылки на исходные листы, а также схему, как на скриншоте ниже:
Если вы развернете группу (щелкнув знак плюса), а затем поместите курсор в ячейку с определенным значением, в строке формул появится ссылка на исходные данные.
Если флажок не установлен, вы просто получаете таблицу с итогами без формул и ссылок:
Как видите, функция консолидации Excel очень полезна для сбора данных. Однако у него есть несколько ограничений. В частности, он работает только для числовых значений и всегда так или иначе обрабатывает эти числа (сумма, количество, среднее и т.д.). Вы не увидите здесь исходных чисел.
Если вы хотите объединить листы в Excel путем простого копирования и объединения их содержимого, вариант консолидации не подходит. Чтобы объединить всего пару из них, создав как бы единый массив данных, ни одна из стандартных функций Excel вам не подойдет, кроме старого доброго копирования / вставки.
Но если таким образом придется обрабатывать десятки листов, ошибки будут практически неизбежны. И затраченное время очень значительное.
Поэтому для таких задач я рекомендую использовать один из следующих нестандартных методов для автоматизации слияния.
Как скопировать несколько листов Excel в один.
Как мы уже видели, встроенная функция консолидации способна суммировать данные из разных листов, но не может объединять их, копируя данные в сводный лист. Для этого вы можете использовать один из инструментов слияния и комбинирования, включенных в надстройку Ultimate Suite Excel.
Для начала будем исходить из следующих условий:
- Структура таблиц и порядок столбцов на всех листах одинаковый.
- Количество строк везде разное.
- Таблицы могут быть добавлены или удалены в будущем.
Итак, у вас есть несколько таблиц, содержащих информацию о различных продуктах, и теперь вам нужно объединить эти таблицы в сводку, например, как на изображении ниже:
Чтобы объединить выбранные листы в один, нужно всего три простых шага.
Запустите мастер копирования листов.
На ленте перейдите на вкладку AblebitsData, нажмите Копировать листы и выберите один из следующих вариантов:
- Скопируйте листы каждой книги на один лист и поместите полученные листы в книгу.
- Объедините одноименные листы в один.
- Копировать выбранное в книгу.
- Объедините данные из выбранных листов в один лист.
Поскольку мы хотим объединить несколько листов, скопировав их данные, мы выбираем последний вариант:
Выберите листы и, при необходимости, диапазоны для объединения.
Мастер копирования листов отображает список всех листов, доступных во всех открытых книгах. Выберите те, которые хотите объединить, и нажмите «Далее».
Если вы не хотите копировать все содержимое определенного рабочего листа, используйте специальный значок, чтобы выбрать желаемый диапазон, как показано на скриншоте ниже.
В этом примере мы объединяем первые три листа нашей книги:
Совет. Если листы, которые вы хотите объединить, находятся в другой книге, которая в данный момент закрыта, нажмите кнопку «Добавить файл…», чтобы найти и открыть эту книгу.
Выберите, каким образом произвести объединение.
На этом этапе вам необходимо настроить дополнительные параметры, чтобы ваша информация была объединена именно так, как вы хотите.
Как войти :
- Вставить все: копирует все данные (значения и формулы). В большинстве случаев это правильный выбор.
- Вставить только значения — выберите этот параметр, если вы не хотите, чтобы формулы переносились.
- Создать ссылки на исходные данные — добавляются формулы, связывающие полученные ячейки с исходными ячейками. Выберите этот параметр, если хотите, чтобы результат слияния автоматически обновлялся при изменении исходных файлов. Он работает аналогично параметру «Создать ссылки на исходные данные» в стандартном инструменте консолидации Excel.
Как организовать :
- Разместите скопированные диапазоны один под другим, т. Е. По вертикали.
- Расположите скопированные диапазоны рядом, т. Е. По горизонтали.
Как скопировать :
- Форматирование сохранения понятно и очень удобно.
- Разделить диапазоны, скопированные с пустой строкой — выберите этот параметр, если вы хотите добавить пустую строку между информацией, скопированной с разных листов. Таким образом, вы можете при необходимости отделить их друг от друга.
- Скопируйте таблицы вместе с их заголовками. Установите этот флажок, если хотите, чтобы заголовки исходных таблиц были включены в сводную таблицу.
На скриншоте ниже показаны подходящие нам настройки по умолчанию:
Нажмите кнопку «Копировать», и содержимое трех разных листов будет объединено в одну сводку, как показано в начале этого примера.
Возможно, вы скажете, что аналогичную операцию можно проделать простым копированием и вставкой. Но если у вас есть дюжина или более листов и хотя бы несколько сотен строк на каждом, это будет очень трудоемкая и длительная операция. И ошибки очень вероятны. Использование надстройки сэкономит вам много времени и нервов.
Как объединить листы Excel с различным порядком столбцов.
При работе с файлами, созданными разными пользователями, порядок столбцов часто отличается. Как их совместить? Будете ли вы вручную копировать или перемещать столбцы, чтобы расположить их в каждой книге? Это ни в коем случае не вариант.
Мы используем инструмент «Объединить листы.
Запустите надстройку через меню Data Ablebits — Combine Sheets.
Выбираем и устанавливаем флажки те листы, данные из которых хотим объединить. Затем программа анализирует их и предлагает нам список найденных столбцов, показывающий, сколько раз был найден столбец с похожим именем.
вам нужно указать столбцы, из которых вы хотите объединить. Вы можете выбрать их все, а можете — только самые важные.
Затем мы определяем, как вставить собранные данные: в комплекте с формулами или просто значениями, или мы создаем ссылки на источники, чтобы гарантировать, что информация постоянно обновляется и обновляется в случае изменений в исходных таблицах.
Вы также можете указать сохранение исходного форматирования, если оно уникально в каждой таблице. Так что, кстати, вам будет проще определить, откуда берется информация в общем массиве, какая таблица является их источником.
И данные будут идеально организованы в соответствии с заголовками столбцов:
У нас получилась своего рода сводная таблица с необходимой информацией.
Объединение определенных столбцов из нескольких листов.
И вот, как мне кажется, самая частая ситуация:
- у вас действительно большие листы с множеством разных столбцов,
- столбцы расположены на каждом из них по-разному, без определенного порядка,
- необходимо объединить в сводную таблицу только самые важные.
Запустите мастер объединения листов, как мы делали в предыдущем примере, выберите необходимые, а затем выберите соответствующие столбцы. Да, это так просто!
Все дальнейшие действия мы уже описали выше. В результате в итоговую таблицу будут включены данные только из выбранных столбцов:
Эти примеры продемонстрировали только несколько инструментов смешивания данных, но это еще не все! Немного поэкспериментировав, вы увидите, насколько полезны и удобны все функции, включенные в пакет.
Полнофункциональная пробная версия Ultimate Suite доступна для загрузки по этой ссылке.
Слияние листов в Excel с помощью кода VBA
Если вы опытный пользователь Excel и хорошо разбираетесь в макросах и VBA, вы можете объединить несколько листов Excel в один с помощью сценария.
Для этого на вкладке «Разработчик» нажмите кнопку Visual Basic или воспользуйтесь сочетанием клавиш Alt + F11. В открывшемся окне добавьте новый модуль через меню Ins ert — Module и скопируйте туда текст следующего макроса:
Sub CopyDataWithHeaders () Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long With Application .ScreenUpdating = False .EnableEvents = False End With ‘Удалить «RDBMergeSheet», если приложение существует. = False при ошибке Возобновить следующий ActiveWorkbook.Worksheets («RDBMergeSheet») Удалить при ошибке GoTo 0 Application.DisplayAlerts = True ‘Добавить рабочий лист с именем «RDBMergeSheet» Установить DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = «RDBMergeSheet» Заполнить начальную строку StartRow = 2 ‘выполняет итерацию всех листов и копирует данные в DestSh Для каждого sh В ActiveWorkbook.Worksheets Если sh.Name DestSh.Name Then’ Копировать строку заголовка, измените диапазон, если вы используете несколько столбцов Если WorksheetFunction.CountA (DestSh.UsedRange) = 0 Тогда sh.Range («A1: Z1»). Копировать DestSh.Range («A1») End If ‘Найти последнюю строку с данными в DestSh и sh Last = LastRow (DestSh) shLast = LastRow (sh)’ Если sh не пусто и последняя строка> = StartRow copy CopyRng If shLast> 0 And shLast> = StartRow Then ‘Установите диапазон, который вы хотите скопировать Se t CopyRng = sh.Range (sh.Rows (StartRow), sh.Rows (shLast))’ Проверьте, достаточно ли строк в DestSh для копирования всех данных If Last + CopyRng.Rows.Count> DestSh.Rows.Count Then MsgBox «В Destsh недостаточно строк» GoTo ExitTheSub End If ‘Этот пример копирует значения / форматы, если вы просто хотите скопировать значения ‘или вы хотите скопировать все, см ниже пример 1 на этой странице Копировать с помощью DestSh.Cells (Last + 1, «A») .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application .CutCopyMode = False End With End If End If Next ExitTheSub: Application.Goto DestSh.Cells (1) ‘Автоматически подогнать ширину столбца в DestSh DestSh.Columns.AutoFit With Application .ScreenUpdating = True .Enab sheet leEvents = True End With End Подфункция LastRow (sh As Worksheet) При ошибке Возобновить След. LastRow = sh.Cells.Find (What: = «*», _ After: = sh.Range («A1»), _ Lookat: = xlPart, _ LookIn: = xlForm ulas, _ SearchOrder: = xlByRows, _ SearchDirection: = xlPrevious, _ MatchCase: = False) .Row On Error GoTo 0 End Function Function LastCol (sh As Worksheet) On Error Resume Next LastCol = sh. Cells.Find (What: = «*», _ After: = sh.Range («A1»), _ Lookat: = xlPart, _ LookIn: = xlFormulas, _ SearchOrder: = xlByColumns, _ SearchDirection: = xlPrevious, _ MatchCase : = False). Column On Error Перейти к 0 Завершить функцию
Обратите внимание, что для правильной работы кода VBA все исходные таблицы должны иметь одинаковую структуру, заголовки столбцов и порядок столбцов.
Эта функция копирует данные со всех листов из строки 2 в последнюю строку данных. Если заголовок в ваших таблицах занимает две или более строк, измените этот код, заменив 2 числами 3, 4 и так далее:
‘Заполните начальную строку StartRow = 2
При запуске функция добавит рабочий лист с именем RDBMergeSheet в книгу и скопирует ячейки с каждого листа в книгу. Каждый раз, когда вы запускаете макрос, он
сначала удаляет получившийся рабочий лист с именем RDBMergeSheet, если он существует, а затем добавляет новый в книгу. Это гарантирует, что данные всегда будут актуальными после запуска кода. В этом случае также копируется формат объединяемых ячеек.
По этой ссылке вы можете найти еще несколько интересных примеров кода VBA для объединения листов в своей книге.
Как объединить два листа Excel в один по ключевому столбцу
Если вы ищете быстрый способ сопоставить и объединить данные из двух листов, вы можете использовать функцию ВПР в Excel или мастер объединения двух таблиц.
Последний представляет собой удобный визуальный инструмент, который позволяет сравнивать две таблицы Excel в одном или нескольких общих столбцах и извлекать соответствующие данные из справочной таблицы. На скриншоте ниже показан один из возможных результатов.