Как объединить несколько страниц в одну в excel - Word и Excel

6 примеров — как консолидировать данные и объединить листы Excel в один

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

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

Сегодня мы займемся проблемой, с которой ежедневно сталкиваются многие пользователи Excel, — как объединить листы Excel в один без использования операций копирования и вставки. Рассмотрим два наиболее распространенных сценария: объединение числовых данных (сумма, количество, среднее и т. д.) и объединение листов ( то есть копирование данных из нескольких листов в один).

Вот что мы рассмотрим в этой статье:

Объединение при помощи стандартного инструмента консолидации.
Как копировать несколько листов Excel в один.
Как объединить листы с различным порядком столбцов.
Объединение только определённых столбцов из нескольких листов
Слияние листов в Excel с использованием VBA
Как объединить два листа в один по ключевым столбцам

Консолидация данных из нескольких листов на одном.

Самый быстрый способ консолидировать данные в Excel (в одной или нескольких книгах) — использовать встроенную функцию Excel Консолидация.

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

Как вы видите на скриншоте ниже, четыре объединяемых листа имеют схожую структуру данных, но разное количество строк и столбцов:

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

Правильно расположите исходные данные. Чтобы функция консолидации Excel работала правильно, убедитесь, что:
- Каждый диапазон (набор данных), который вы хотите объединить, находится на отдельном листе. Не помещайте данные на лист, куда вы планируете выводить консолидированные данные.
- Каждый лист имеет одинаковый макет, и каждый столбец имеет заголовок и содержит похожие данные.
- Ни в одном списке нет пустых строк или столбцов.
Запустите инструмент «Консолидация». На новом листе, где вы планируете поместить результаты, щелкните верхнюю левую ячейку, начиная с которой должны отображаться консолидированные данные, затем на ленте перейдите на вкладку «Данные» и нажмите кнопку «Консолидация».

Совет. Желательно объединить данные в пустой лист. Если на вашем основном листе уже есть данные, убедитесь, что имеется достаточно места (пустые строки и столбцы) для записи результатов.

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

Если один или несколько листов находятся в другой книге, используйте кнопку «Обзор», чтобы найти эту книгу и использовать ее.

Настройте параметры обновления. В том же диалоговом окне Консолидация выберите любой из следующих параметров:
- Установите флажки «Подписи верхней строки» и / или «Значения левого столбца» в разделе «Использовать в качестве имён», если вы хотите, чтобы заголовки строк и / или столбцов исходных диапазонов были также скопированы.
- Установите флажок «Создать связи с исходными данными», если нужно, чтобы консолидированные данные обновлялись автоматически при изменении исходных таблиц. В этом случае Excel создаст ссылки на ваши исходные листы, а также схему, как на следующем скриншоте:

Если вы развернете какую-либо группу (щелкнув значок плюса), а затем установите курсор на ячейку с определенным значением, в строке формул отобразится ссылка на исходные данные.
Если флажок не устанавливать, то вы получаете просто таблицу с итоговыми цифрами без всяких формул и ссылок:

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

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

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

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

Как скопировать несколько листов Excel в один.

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

Для начала давайте будем исходить из следующих условий:

Структура таблиц и порядок столбцов на всех листах одинаковы.
Количество строк везде разное.
Листы могут в будущем добавляться или удаляться.

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

Три простых шага — это все, что нужно, чтобы объединить выбранные листы в один.

1. Запустите мастер копирования листов.

На ленте перейдите на вкладку AblebitsData, нажмите «Копировать листы (Copy Sheets)» и выберите один из следующих вариантов:

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

Поскольку мы хотим объединить несколько листов путем копирования их данных, то выбираем последний вариант:

1. Выберите листы и, при необходимости, диапазоны для объединения.

Мастер копирования листов отображает список всех имеющихся листов во всех открытых книгах. Выберите те из них, которые хотите объединить, и нажмите « Далее».

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

В этом примере мы объединяем первые три листа нашей книги:

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

2. Выберите, каким образом произвести объединение.

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

Как вставить :

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

Как расположить :

Разместите скопированные диапазоны один под другим – то есть вертикально.
Расположить скопированные диапазоны рядом – то есть по горизонтали.

Как скопировать :

Сохранить форматирование – понятно и очень удобно.
- Разделить скопированные диапазоны пустой строкой – выберите этот вариант, если вы хотите добавить пустую строку между сведениями, скопированными из разных листов. Так вы сможете отделить их друг от друга, если это необходимо.
- Скопировать таблицы вместе с их заголовками. Установите этот флажок, если хотите, чтобы заголовки исходных таблиц были включены в итоговый лист.

На скриншоте ниже показаны настройки по умолчанию, которые нам подходят:

Нажмите кнопку «Копировать (Copy)», и у вас будет содержимое трех разных листов, объединенное в один итоговый, как показано в начале этого примера.

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

Как объединить листы Excel с различным порядком столбцов.

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

Используем инструмент объединения листов Combine Sheets.

Запускаем надстройку через меню Ablebits Data – Combine Sheets.

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

Вы должны указать те столбцы, данные из которых вы хотели бы объединить. Можете выбрать их все, можете – только самые важные.

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

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

И данные будут идеально скомпонованы по заголовкам столбцов:

Мы получили своего рода сводную таблицу с необходимой информацией.

Объединение определенных столбцов из нескольких листов.

А вот, как мне кажется, наиболее часто встречающаяся ситуация:

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

Запустите мастер объединения листов, как мы это делали в предыдущем примере, укажите нужные, а затем выберите соответствующие столбцы. Да, это так просто!

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

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

Полнофункциональная ознакомительная версия Ultimate Suite доступна для загрузки в по этой ссылке.

Слияние листов в Excel с помощью кода VBA

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

Для этого на вкладке Разработчик (Developer) нажмите кнопку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert — 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

    'Delete the sheet "RDBMergeSheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "RDBMergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"

    'Fill in the start row
    StartRow = 2

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
  sh.Range("A1:Z1").Copy DestSh.Range("A1")
End If

            'Find the last row with data on the DestSh and sh
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            'If sh is not empty and if the last row >= StartRow copy the CopyRng
            If shLast > 0 And shLast >= StartRow Then

                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

                'Test if there enough rows in the DestSh to copy all the data
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "There are not enough rows in the Destsh"
                    GoTo ExitTheSub
                End If

                'This example copies values/formats, if you only want to copy the
                'values or want to copy everything look below example 1 on this page
                CopyRng.Copy
                With 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)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            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 GoTo 0
End Function

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

В этой функции выполняется копирование данных со всех листов начиная со строки 2 и до последней строки с данными. Если шапка в ваших таблицах занимает две или более строки, то измените этот код, поставив вместо 2 цифры 3, 4 и т.д.:

    'Fill in the start row
    StartRow = 2

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

Ещё несколько интересных примеров кода VBA для объединения листов вашей рабочей книги вы можете найти по этой ссылке.

Как объединить два листа Excel в один по ключевому столбцу

Если вы ищете быстрый способ сопоставить и объединить данные из двух листов, вы можете либо использовать функцию Excel ВПР, либо воспользоваться мастером объединения таблиц Merge Two Tables.

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

Более подробно его работа рассмотрена в этой статье.

Мастер объединения двух таблиц также включен в Ultimate Suite for Excel, как и множество других полезных функций.

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

Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…

Как быстро объединить несколько файлов Excel — Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…

Как работать с мастером формул даты и времени — Работа со значениями, связанными со временем, требует глубокого понимания того, как функции ДАТА, РАЗНДАТ и ВРЕМЯ работают в Excel. Эта надстройка позволяет быстро выполнять вычисления даты и времени и без особых…

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

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

6 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.…

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

Источник

Excel для Microsoft 365 для Mac Excel 2021 для Mac Excel 2019 для Mac Excel 2016 для Mac Excel для Mac 2011 Еще…Меньше

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

Тип консолидации следует выбирать с учетом того, как выглядят объединяемые листы. Если данные на листах расположены единообразно (названия строк и столбцов могут при этом различаться), воспользуйтесь консолидацией по расположению. Если же на листах для соответствующих категорий используются одинаковые названия строк и столбцов (данные при этом могут быть расположены по-разному), используйте консолидацию по категории.

Консолидация по расположению

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

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

Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Выделите на каждом листе нужные данные.

Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

Консолидация по категории

Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.

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

Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: подписи верхней строки, значения левого столбца либо оба флажка одновременно.
Выделите на каждом листе нужные данные. Не забудьте включить в них ранее выбранные данные из верхней строки или левого столбца.

Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

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

Консолидация по расположению

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

Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Выделите на каждом листе нужные данные и нажмите кнопку Добавить.

Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

Консолидация по категории

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

Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: подписи верхней строки, значения левого столбца либо оба флажка одновременно.
Выделите на каждом листе нужные данные. Убедитесь, что вы выбрали верхнюю строку или левый столбец, а затем нажмите кнопку Добавить.

Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

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

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

Источник

Как объединить листы документа в Excel Online

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

Содержание

1 Как работает объединение листов в Эксель Онлайн
2 Как объединить все листы в одну книгу
3 Объединение данных в один лист

Как работает объединение листов в Эксель Онлайн

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

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

Откройте нужные листы Excel и зайдите в меню Главная;
Нажмите кнопку Формат и выберите пункт Переместить или скопировать текст;
В появившемся окне укажите Новая книга и нажмите ОК.

Как объединить все листы в одну книгу

Разберем довольно быстрый и легкий способ, как соединить все листы документа в одну книгу.

Для объединения листов Excel в одну книгу выполните следующие действия:

Откройте нужные для объединения листы;
Зайдите в меню Главная на вкладку Формат;
Выберите пункт Переместить или скопировать текст;
В появившемся меню выберите Новая книга, чтобы указать сводную таблицу для совмещения всех листов;
Продублируйте все действия с остальными листами, которые нужно объединить. Сохраните готовый файл.

Объединение данных в один лист

Иногда возникает необходимость несколько наборов данных объединить в сводный лист. Для этого заранее отформатируйте эти данные и приступите в объединению.

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

Создайте новый рабочий документ Excel;
Зайдите в меню Данные и нажмите кнопку Закреплять;
В появившемся окне кликните Сумма;
Нажмите пункт Рекомендации, чтобы открыть таблицу для выбора данных.

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

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

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

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

Источник

Объединение данных с нескольких листов

Смотрите также объединяет в себе файл Иван.xlsx, выделите и финальным листами варианта, первый консолидацией, код из таблицы и называем его группы; символ «ЛЕВСИМВЗдесь к ячейке (Coca SKU, но компьютер совпадений. Например, когда области, в которой пустых строк иОткройте каждый из исходных Office 2011 г.Примечание: конкретные столбцы из таблицу с данными дополнительные листы с второй формулами, но

поставщика. После этогоSKU converter—(LEFT),A2 Cola и Coca-Cola не так догадлив! уникальный идентификатор из требуется разместить консолидированные столбцов. листов. Мы стараемся как других листов той (вместе с шапкой). данными, которые также если у вас повторяем шаг 2.. Копируем весь столбец» – это разделитель;ПРАВСИМВ– это адрес

Inc.) Это не точное первой таблицы представляет данные.Откройте каждый из исходныхНа конечном листе щелкнитеДля консолидации по расположению можно оперативнее обеспечивать же самой книги. Затем нажмите кнопку станут автоматически учитываться по факту листовВ нашей главной таблицеOur.SKUB2(RIGHT), ячейки, из которойРассмотрим две таблицы. Столбцы

совпадение делает невозможным собой первые пятьПримечание:

Консолидация по расположению

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

в несколько раз есть ключевой столбециз листа– это адресПСТР
мы будем извлекать первой таблицы содержат использование обычных формул символов идентификатора из Убедитесь, что справа и

 в том, что области, в которой каждом из исходных материалами на вашем отдельных листах меняется.
(Add)Если исходные таблицы не больше чем в с точным совпадениемStore ячейки, содержащей код(MID),
символы, а номенклатурный номер (SKU), Excel для объединения второй таблицы. Все
снизу достаточно свободных данные на них

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

Консолидация по категории

НАЙТИ5 наименование пива (Beer) данных из двух предлагаемые в этой ячеек для консолидированных расположены одинаково. данные. формат списка без переведена автоматически, поэтому итогового столбца будет чтобы добавить выделенный имеют разное количество вариант, читайте тогда поиска, так что удаляем дубликаты и

в остальные строки.(FIND), чтобы извлекать
– количество символов, и его цену таблиц. статье решения протестированы данных.

На конечном листе щелкнитеПримечание: пустых строк и ее текст может меняться.
диапазон в список строк, столбцов или про консолидацию, это теперь эта задача оставляем в нёмТеперь объединить данные из любые части составного
которое будет извлечено. (Price). Во второйИ что совсем плохо мной в Excel
На вкладке верхнюю левую ячейку Убедитесь, что справа и столбцов. содержать неточности иКак это сделать? объединяемых диапазонов. повторяющиеся данные или средство Excel идеально не вызовет сложностей
только уникальные значения. наших двух таблиц индекса. Если сКопируем эту формулу во таблице записан SKU – соответствия могут 2013, 2010 и

Данные области, в которой снизу достаточно свободныхОткройте каждый из исходных
грамматические ошибки. ДляMaybachПовторите эти же действия находятся в разных подходит под вашуПри помощи функции

Рядом добавляем столбец не составит труда. этим возникли трудности все ячейки нового и количество бутылок быть вовсе нечёткими, 2007.в разделе

Консолидация по расположению

требуется разместить консолидированные ячеек для консолидированных листов и убедитесь нас важно, чтобы: Конкатенция не вариант? для файлов Риты файлах, то суммирование

структуру таблиц иВПРSupp.SKU Мы будем сопоставлять – свяжитесь с
столбца. на складе (In и «Итак, есть два листаСервис

 данные. данных. в том, что эта статья былаПример прикладывайте для
и Федора. В при помощи обычных задачу. В файле(VLOOKUP) объединяем данныеи вручную ищем столбец нами, мы сделаем
Готово! Теперь у нас stock). Вместо пиваНекоторая компания Excel, которые нужно
нажмите кнопкуПримечание:На вкладке данные на них вам полезна. Просим

более конкретного предложения итоге в списке формул придется делать в , создан
листа соответствия между значениямиFull ID всё возможное, чтобы есть ключевые столбцы может быть любой

Консолидация по категории

» в одной таблице объединить для дальнейшегоКонсолидация Убедитесь, что справа иДанные расположены одинаково. вас уделить паруStep_UA должны оказаться все для каждой ячейки с помощью консолидации.Store столбцовпервой таблицы со помочь Вам. с точным совпадением

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

 секунд и сообщить,: К сожалению конкатенция три диапазона: персонально, что ужасноDoktor1962
с данными листаOur.SKU столбцомПредположим, таблица, в которой значений – столбец столбцов в реальной «
в одной таблицеВыберите в раскрывающемся списке ячеек для консолидированныхРабота с данными
верхнюю левую ячейку помогла ли она не вариант.Обратите внимание, что в трудоемко. Лучше воспользоваться: Ув. AleksSid разWholesale Supplier 1иID производится поиск, содержит
SKU helper жизни может бытьЗАО «Некоторая Компания» содержатся цены (столбецфункцию данных.нажмите кнопку области, в которой вам, с помощьюЧтобы было все

данном случае Excel принципиально другим инструментом. уж вам нравится, используя для поиска
Supp.SKUвторой таблицы. При столбец с идентификаторами.в основной таблице гораздо больше.» в другой таблице,

 Price) и описания, которую требуется использоватьНа вкладкеКонсолидация требуется разместить консолидированные кнопок внизу страницы. понятно — добавляю запоминает, фактически, положение

support.office.com

Как объединить две таблицы Excel по частичному совпадению ячеек

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

три разных файла (он над вашим,Supp.SKU помогут описания из из столбцов столбца содержатся записиSKU символами создаём вспомогательныйНовая Компания (бывшая Некоторая которые Вы продаёте,Установите флажки в группев разделеВыберите в раскрывающемся спискеПримечание: приводим ссылку наMaybach прописывая для каждого ( если заметите сообщением),. столбцаDescription видав таблице, где столбец. Можно добавить Компания)

а во второйИспользовать в качестве именСервисфункцию Убедитесь, что справа и оригинал (на английском: Через макрос… из них полныйИван.xlsx могу кроме приведенногоВот пример обновлённых данныхDescriptionиXXXX-YYYY будет выполняться поиск. его в конец» и « отражены данные о, указывающие, где внажмите кнопку, которую требуется использовать снизу достаточно свободных языке) .Maybach путь (диск-папка-файл-лист-адреса ячеек)., вами способа формулами в столбце). Это скучная работёнка,Price, гдеТеперь при помощи функции таблицы, но лучшеСтарая Компания

наличии товаров на исходных диапазонах находятсяКонсолидация для консолидации данных. ячеек для консолидированныхЕсли данные, которые требуется: А как то Чтобы суммирование происходилоРита.xlsx посоветовать Вам посчитатьWholesale Price пусть Вас радуетвторой таблицы будутXXXXВПР всего вставить его» тоже окажутся записью складе (столбец In названия:.Установите флажки в группе

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

 с учетом заголовкови на пальцах. А: мысль о том, добавлены в первую– это кодовое(VLOOKUP) мы получим следующим справа после об одной и stock). Если Выподписи верхней строкиВыберите в раскрывающемся спискеИспользовать в качестве именНа вкладке

нескольких листах или сам мог это столбцов и строк

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

Ключевой столбец в одной из таблиц содержит дополнительные символы

необходимо включить оба.xlsx некрасиво ли? Задавайте свои выполнить только одинВот пример: Вы владелец (мобильные телефоны, телевизоры,Извлечь первые он был на Это известно Вам, составляли обе таблицызначения левого столбца, которую требуется использовать исходных диапазонах находятсяв группе

их можно объединить столбцов. На некоторых флажка) с тремя таблицами:Имеем несколько однотипных таблиц вопросы в комментариях раз :-). небольшого магазина, получаете видеокамеры, фотокамеры), аХ

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

к статье, яВ результате мы имеем товар от одногоYYYYсимволов справа: например,Ключевым в таблице в
объяснить Excel? в обеих должен одновременно.Выделите на каждом листеподписи верхней строкинажмите кнопку
с помощью команды является например третьим.(Use labels)
не одинаковы - одной книги. Например, постараюсь ответить, как вот такую таблицу: или нескольких поставщиков.– это код
6 символов справа
нашем примере является

Выход есть всегда, читайте присутствовать как минимумВыделите на каждом листе нужные данные и,Консолидация «Консолидация». Например, если В то же. Флаг
у них различные вот такие: можно скорее.

В главную таблицу (лист У каждого из товара внутри группы. из записи «DSFH-164900». столбец далее и Вы один ключевой столбец нужные данные. Не нажмите кнопкузначения левого столбца

. есть отдельный лист время таких итоговыхСоздавать связи с исходными

Другие формулы

размеры и смысловаяНеобходимо объединить их всеУрок подготовлен для Вас Store) вставляем новый них принята собственная Главная таблица состоит Формула будет выглядеть
A
узнаете решение!
с уникальными идентификаторами забудьте включить вДобавитьлибо оба флажкаВыберите в раскрывающемся списке расходов для каждого столбцов у меня данными начинка. Тем не в одну общую командой сайта office-guru.ru столбец
номенклатура, отличающаяся от
из двух столбцов:
так:с данными SKU,Замечание: товаров. Описание товара них ранее выбранные. одновременно.функцию регионального представительства, с
несколько. Они все
(Create links to source

менее их можно таблицу, просуммировав совпадающиеИсточник: https://www.ablebits.com/office-addins-blog/2013/09/20/merge-worksheets-excel-partial-match/Supp.SKU Вашей. В результате в одном содержатся=ПРАВСИМВ(A2;6) и нужно извлечьРешения, описанные в или цена могут данные из верхнейПуть к файлу вводитсяВыделите на каждом листе, которую требуется использовать помощью консолидации можно должны быть на data) собрать в единый

Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

значения по кварталамПеревел: Антон Андронов. возникают ситуации, когда коды товарных групп=RIGHT(A2,6) из него первые этой статье, универсальны. изменяться, но уникальный строки или левого в поле нужные данные. Не для консолидации данных. создать на базе итоговом листе.позволит в будущем отчет меньше, чем и наименованиям.Автор: Антон АндроновДалее при помощи функции Ваша запись «Case-Ip4S-01» (Group), во второмПропустить первые 5 символов. Добавим Вы можете адаптировать идентификатор всегда остаётся столбца. Затем нажмитеВсе ссылки забудьте включить вВыделите на каждом листе

этих данных корпоративныйЕсть идея как (при изменении данных за минуту. ЕдинственнымСамый простой способ решенияYooooВПР соответствует записи «SPK-A1403» записаны коды товаров

Х вспомогательный столбец и их для дальнейшего

неизменным.
кнопку

. них ранее выбранные нужные данные. лист расходов. Такой это реализовать обычными в исходных файлах) условием успешного объединения задачи «в лоб»: Нужно объединить таблицы(VLOOKUP) сравниваем листы в файле Excel, (ID). Мы не

символов, извлечь следующие назовём его использования с любымиТрудности начинаются, когда ВыДобавитьПосле добавления данных из данные из верхнейПуть к файлу вводится лист может содержать формулами, но это производить пересчет консолидированного (консолидации) таблиц в — ввести в со всех листовStore полученном от поставщика. можем просто отброситьY

Данные в ключевых столбцах не совпадают

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

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

После нажатия на совпадение заголовков столбцов формулу видаПроблема в том,SKU converter случайным образом и так как один извлечь «0123» изНаводим указатель мыши наВПР из других отделов :-)

1. Создаём вспомогательную таблицу для поиска.

в поле кнопкуПуть к файлу вводится. текущим уровням запасов в Эксель чтоОК и строк. Именно=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3 что порядок показателей, используя для поиска

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

код товара может нам нужно пропустить

2. Обновляем главную таблицу при помощи данных из таблицы для поиска.

BПОИСКПОЗ ещё усложниться, если..

Все ссылки всех исходных листов продуктам в рамках из инструментов, что работы: и левому столбцу B2 с каждого разный для каждойOur.SKU преобразовать «SPK-A1403» в повторяться в разных первые 8 символов, при этом он

(MATCH), вдруг вводится новыйПосле добавления данных изДля консолидации по категории

. и книг нажмите всей организации. поможет решить моюНаши файлы просуммировались по каждой таблицы Excel из указанных листов, таблицы + иногда, а для обновлённых «Case-Ip4S-01». группах. и извлечь следующие должен принять видГПР формат уникальных идентификаторов

3. Переносим данные из таблицы поиска в главную таблицу

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

будет искать совпадения и затем скопировать встречаются показатели, которых данных – столбецПлохая новость:Добавляем в главной таблице 4 символа. Формула стрелки, направленной вниз:(HLOOKUP) и так или самую малость и книг нажмите

каждом из исходных всех исходных листовОК с учетом того,

инструмент Консолидация, но крайнего левого столбца и суммировать наши ее на остальные нет в остальныхSupp.SKU

Данные, содержащиеся в вспомогательный столбец и
будет выглядеть так:
Кликаем по заголовку правой

далее.

office-guru.ru

Объединение таблицы со всех листов в одну

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

 этих двух таблицах называем его=ПСТР(A2;8;4)
кнопкой мыши иВыберите подходящий пример, чтобы обозначения (SKU). И

ОК формат списка без кнопкуДля консолидации по категории листы. Если данные

 анализа данных из выделенных областей вДля того, чтобы выполнить

 вправо.TimShaСтолбец Excel, придётся обрабатыватьFull ID=MID(A2,8,4) в контекстном меню сразу перейти к перед Вами стоит. пустых строк иОК диапазон данных на на листах расположены похожих таблиц. каждом файле. Причем, такую консолидацию:

Если листов очень много,: Для чего -Supp.SKU вручную, чтобы в(столбец C), подробнееИзвлечь все символы до выбираем нужному решению: задача объединить вПримечание: столбцов. Кроме того,.

CyberForum.ru

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

каждом из исходных единообразно (названия строкМожет какие-то промежуточные если развернуть группы

Заранее откройте исходные файлы то проще будет цель главная какая?!заполняется оригинальными кодами дальнейшем было возможно

о том, как разделителя, длина получившейсяВставитьКлючевой столбец в одной Excel новую и

 Любые названия, не совпадающие

категории должны бытьПримечание: листов должен иметь и столбцов могут итоги помогут решить (значками плюс слеваСоздайте новую пустую книгу

разложить их всеКак пытались сделать, производителя. объединить их. это делается рассказано

 последовательности может быть

(Insert): из таблиц содержит старую таблицы с с названиями в названы одинаково. Например, Любые названия, не совпадающие формат списка без при этом различаться), задачку? Или еще от таблицы), то (Ctrl + N) подряд и использовать что конкретно неЗамечание:

Способ 2. Если таблицы неодинаковые или в разных файлах

Хорошая новость: ранее в этой разной. Например, нужноДаём столбцу имя дополнительные символы данными. Так или других исходных областях, если один из с названиями в пустых строк и воспользуйтесь консолидацией по что то? можно увидеть из

Установите в нее активную немного другую формулу: получается?Если в столбцеЭто придётся сделать статье. извлечь «123456» иSKU helperДанные из ключевого столбца иначе, возникает ситуация,

могут привести к столбцов называется других исходных областях, столбцов. Кроме того, расположению. Если жеMaybach какого именно файла ячейку и выберите=СУММ(‘2001 год:2003 год’!B3)OLEGOFFSupp.SKU только один раз,В ячейке «0123» из записей. в первой таблице когда в ключевых появлению в консолидированныхСред. могут привести к

категории должны быть на листах для

, приложите реальный файл.
какие данные попали на вкладке (в
Фактически — это суммирование:появились пустые ячейки, и получившуюся вспомогательнуюC2  «123456-суффикс» и «0123-суффикс»Чтобы извлечь первые 5
разбиты на два столбцах имеет место  данных отдельных строк, а другой — появлению в консолидированных названы одинаково. Например, соответствующих категорий используютсяStep_UA в отчет и  меню) всех ячеек B3Yoooo то необходимо взять таблицу можно будет
запишем такую формулу: соответственно. Формула будет символов из столбца или более столбца только частичное совпадение или столбцов.

Среднее данных отдельных строк если один из одинаковые названия строк, ссылки на исходныеДанные — Консолидация на листах с, макросом в VBA все коды сохранить для дальнейшего=СЦЕПИТЬ(A2;»-«;B2) выглядеть так:SKU во второй таблице записей, например, «Из этой статьи Вы , консолидация не просуммирует или столбцов. столбцов называется и столбцов (данныеFairuza файлы:(Data — Consolidate)

2001 по 2003, можно так.SKU использования. Далее Вы

=CONCATENATE(A2,»-«,B2)=ЛЕВСИМВ(A2;НАЙТИ(«-«;A2)-1), в ячейкуДанные в ключевых столбцах12345 узнаете, как быстро эти столбцы.Для консолидации по расположениюСред. при этом могут, спасибо.Maybach. Откроется соответствующее окно: т.е. количество листов,Doktor1962

planetaexcel.ru

Как объединить несколько столбцов с разных листов в один?

, соответствующие этим пустым сможете объединять эти
Здесь=LEFT(A2,FIND(«-«,A2)-1)B2 не совпадают (123-SDX» и « объединить данные изОткройте каждый из исходных диапазон данных на, а другой — быть расположены по-разному),Решил вопрос с: Привет!
Установите курсор в строку

 по сути, может: А надстройка консолидация
ячейкам, добавить их таблицы автоматически и

A2Одним словом, Вы можетевводим такую формулу:
и HFGT-23) или12345-новый_суффикс двух таблиц Excel,

 листов. каждом из исходных

Среднее используйте консолидацию по помощью формул Эксель,Надо чтобы наСсылка быть любым. Также данных не подходит? в таблицу сэкономить таким образом– это адрес использовать такие функции=ЛЕВСИМВ(A2;5) есть частичное совпадение,«. Вам-то понятно, что
когда в ключевыхНа конечном листе щелкните листов должен иметь, консолидация не просуммирует категории. получился вот такой отдельном листе автоматически(Reference) в будущем возможноAleksSidSKU converter массу времени ячейки, содержащей код Excel, как
=LEFT(A2,5) меняющееся от ячейки это тот же столбцах нет точных

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

CyberForum.ru

Создаём новый лист Excel

Источник

Загрузить PDF

Из этой статьи вы узнаете, как в одной книге объединить данные из двух разных страниц Microsoft Excel.

Шаги

1

Дважды нажмите на книгу, чтобы открыть ее в Excel. Эта книга должна содержать как минимум 2 листа, которые вы хотите объединить.
2

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

Выделите ячейку А1. Просто нажмите на нее, чтобы выделить.
4

Нажмите на вкладку Данные вверху окна, между вкладками «Формулы» и «Рецензирование».
5

Нажмите на Консолидация. Эта опция расположена на панели инструментов в группе «Работа с данными». После этого на экране появится окно «Консолидация».
6

Нажмите на Сумма в выпадающем меню «Функция». Это первое выпадающее меню в окне «Консолидация».
7
Нажмите на указывающую вверх стрелку в поле «Ссылка», в ячейке справа. Так вы уменьшите окно «Консолидация» и измените его название на «Консолидация — Ссылка».
- В некоторых версиях Excel эта стрелка серая и черная. В других версиях она имеет вид крошечного прямоугольника, в котором находится красная стрелка.
8

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

Нажмите на стрелку в окне «Консолидация — Данные». Вы снова увидите окно «Консолидация».
10

Нажмите на кнопку Добавить справа от поля «Список диапазонов». Теперь вы готовы объединить выделенные данные с данными на другом листе.
11

Нажмите на указывающую вверх стрелку в поле «Ссылка». Как и ранее, это приведет к уменьшению окна «Консолидация» и изменит его название на «Консолидация — Ссылка».
12

Выделите данные на втором листе. Нажмите на название листа внизу книги, а затем выделите данные, которые хотите объединить.
13

Нажмите на стрелку в окне «Консолидация — Ссылка».
14
Нажмите Добавить. В поле «Список диапазонов» появится выделенные наборы данных.
- Если вы хотите объединить и другие листы, добавьте данные тем же способом, как и с первыми двумя листами.
15

Поставьте галочку рядом с опцией «подписи верхней строки» и «значения левого столбца». Это необходимо сделать в левом нижнем углу окна «Консолидация».
16

Нажмите OK. Выделенные данные будут объединены и появятся на новом листе.

Реклама

Об этой статье

Эту страницу просматривали 18 986 раз.

Была ли эта статья полезной?

Источник

Сборка данных со всех листов книги в одну таблицу

Постановка задачи

Допустим, что у нас есть книга с большим количеством листов, где на каждом листе находится таблица с данными по сделкам в этом городе:

Давайте будем исходить из следующих соображений:

Структура и столбцов на всех листах одинаковая.
Количество строк на всех листах разное.
Листы могут в будущем добавляться или удаляться.

Наша задача — собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, Power Query и макросы.

Способ 1. Сборка данных с листов с помощью Power Query

Если вы ещё не сталкивались в своей работе с Power Query, то очень советую копнуть в этом направлении. Использование этой бесплатной и уже встроенной по умолчанию в Excel надстройки, способно полностью перевернуть весь ваш процесс сбора и анализа данных, упростив всё в разы. С задачей сбора данных с листов Power Query справляется весьма успешно.

Шаг 1. Подключаемся к файлу

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

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data). Нажмите кнопку Получить данные / Создать запрос — Из файла — Книга Excel (Get Data / New Query — From file — From Excel) и укажите наш файл с исходными листами:

В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit):

Должно появиться окно редактора запросов Power Query, где отобразятся данные с выбранного листа. Поскольку нам нужен, на самом деле, не один лист, а все, то удалим в правой панели все шаги, кроме первого шага Источник (Source) используя крестик слева от названия шага:

То, что останется после удаления шагов — это список всех объектов, которые Power Query «видит» во внешних файлах, а это:

листы (Sheet)
«умные таблицы» (Table)
именованные диапазоны (Defined Name)
области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

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

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind. Например, если вам нужны только листы:

Во-вторых, если нам нужны только видимые листы, то дополнительно можно отфильтровать ещё по столбцу Hidden.

В-третьих, если вы точно знаете размер таблиц, которые вам нужны, то можно легко добавить к нашему списку вычисляемый столбец с формулой, выводящей количество столбцов или строк и использовать потом эти числа для отбора. Для этого выберем на вкладке Добавление столбца — Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):

Для подсчёта количества строк можно использовать аналогичную функцию Table.RowCount. Получившийся столбец затем можно использовать для фильтрации «мусорных» таблиц.

В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова «Товар«, то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:

=[Data][Column1]{0}

Здесь:

[Data] — имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
[Column1] — имя столбца на листе, из которого мы хотим извлечь данные
{0} — номер строки (считая с нуля), откуда мы хотим взять данные

После фильтрации «мусора» все добавленные вспомогательные столбцы можно, конечно же, спокойно удалить, оставив только колонки Name и Data.

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix):

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Останется лишь «навести блеск», а именно:

Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home).
Переименовать первый столбец в Город двойным щелчком на заголовку.
Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home — Close & Load):

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl+Alt+F5.

Плюсы такого подхода:

Не нужно уметь программировать, всё делается быстро и почти без использования клавиатуры.
Последовательность столбцов на разных листах может быть различной — это не играет роли, столбцы правильно встанут друг под друга в итоговой сборке.
Можно быстро обновлять запрос при изменении исходных данных.

Минусы этого способа:

Собираются только значения, т.е. формулы с исходных листов не сохраняются.
Названия столбцов должны на всех листах совпадать с точностью до регистра.
Нельзя выбрать какой именно диапазон берётся с каждого листа — это определяется автоматически (берётся всё, что есть).
Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более «классического» подохода — макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub CollectDataFromAllSheets()
    Dim ws As Worksheet
    
    Set wbCurrent = ActiveWorkbook
    Workbooks.Add
    Set wbReport = ActiveWorkbook
    
    'копируем на итоговый лист шапку таблицы из первого листа
    wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
    
    'проходим в цикле по всем листам исходного файла
    For Each ws In wbCurrent.Worksheets
    
        'определяем номер последней строки на текущем листе и на листе сборки
        n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        
        'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
        Set rngData = ws.Range("A1:D5")            'фиксированный диапазон или
        Set rngData = ws.UsedRange                 'всё, что есть на листе или
        Set rngData = ws.Range("F5").CurrentRegion    'область, начиная от ячейки F5 или
        Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell))    'от А2 и до конца листа
        
        'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
        rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)
        
    Next ws
End Sub

Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или с помощью сочетания клавиш Alt+F8. Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.

Плюсы такого подхода:

Формулы с исходных листов сохраняются в сборке.
Имена столбцов не играют роли.
Макрос будет работать в любой версии Excel.
Можно выбирать, что именно брать с каждого листа (конкретный фиксированный диапазон или весь лист).

Минусы этого способа:

Последовательность столбцов на всех листах должна быть одинаковой, т.к. происходит, по сути, тупое копирование таблиц друг-под-друга.
Защита от макросов должна быть отключена.
Быстрого обновления, как это было с Power Query, здесь, к сожалению, не будет. При изменении исходных данных придётся запустить макрос повторно.

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления — использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор — это всегда хорошо.

Ссылки по теме

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

Источник

Объединить содержимое
1. Объедините все листы в один лист.
- 1.1 Объединение листов в один с помощью VBA
- 1.2 Объединение таблиц или именованных рангов с помощью запроса
- 1.3 Объедините листы в один лист с помощью инструмента
- 1.4 Дополнение к объединению листов в один
2． Объедините книги в одну книгу
- 2.1 Объединение книг в одну, перемещая или копируя
- 2.2 Объединение книг (одна папка) в одну с помощью VBA
- 2.3 Объединение книг (папок) в одну книгу с помощью инструмента
3． Объединение отдельных листов из рабочих тетрадей в одну книгу
- 3.1 Объединение определенных листов из книги с VBA
- 3.2 Объединение определенных листов из книги (папок) с помощью инструмента
4. Объедините два листа по ключевому столбцу.
- 4.1 Объединение двух листов по столбцу с помощью запроса
- 4.2 Объединение двух листов по столбцу с функциями
- 4.3 Объединение двух листов столбиком с помощью инструмента
5． Объединить два листа по два столбца
6． Объединить листы с одинаковыми заголовками
- 6.1 Объединить листы с одним и тем же коллектором по VBA
- 6.2 Объединение листов с одинаковым заголовком с помощью инструмента
Консолидировать
1． Объединяйте листы и проводите расчеты
- 1.1 Комбинируйте и выполняйте вычисления с помощью googlesheetintoonesheet
- 1.2 Комбинируйте и выполняйте вычисления с помощью инструмента
2．Совместите листы в сводную таблицу
Таблица Google
1． Объедините листы Google в один лист
- 1.1 Объедините листы Google в один лист с помощью фильтра
- 1.2 Объедините листы Google в один лист с помощью IMPORTRANCGE
2． Объедините листы Google в одну книгу
Расширение
1． Объедините листы и удалите дубликаты
- 1.1 Объедините листы, затем удалите дубликаты, удалив дубликаты
- 1.2 Объедините листы и удалите дубликаты инструментом
2． Объединяйте одноименные листы в одну книгу
3． Объединение одинаковых диапазонов на листах в один лист

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

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

Навигация по этому руководству

Объединить содержимое

1. Объедините все листы в один лист.

1.1 Объединение листов в один с помощью VBA

1.2 Объединение таблиц или именованных рангов с помощью Query (Excel 2016 или более поздние версии)

1.3 Объедините листы в один с помощью удобного инструмента

1.4 Дополнение к объединению листов в один

2． Объедините книги в одну книгу

2.1 Объединение книг в одну с помощью команды «Переместить» или «Копировать»

2.2 Объединение нескольких книг (в одной папке) в одну книгу с кодом VBA

2.3 Объедините несколько книг (из нескольких папок) в одну книгу с помощью удобного инструмента

3. Объедините отдельные листы из нескольких книг в одну книгу.

3.1 Объединение отдельных листов из книг (все книги в одной папке)

3.2 Объединение определенных листов из нескольких книг (из нескольких папок) с помощью удобного инструмента

4. Объедините два листа на основе ключевого столбца.

4.1 Объединение двух таблиц на основе одного ключевого столбца с помощью запроса (Excel 2016 или более поздние версии)

4.2 Объединение двух листов на основе одного ключевого столбца с помощью функций Excel

4.3 Объединение двух листов на основе одного ключевого столбца с помощью удобного инструмента

5. Объедините два листа на основе двух столбцов.

6. Объедините листы с одинаковыми заголовками.

6.1 Объедините все листы с одинаковыми заголовками с помощью VBA

6.2 Объединение листов с одинаковыми заголовками с помощью удобного инструмента

Консолидировать

1. Объедините листы и произведите расчеты.

1.1 Объединяйте листы и выполняйте вычисления с помощью функции «Объединить»

1.2 Объединяйте листы и проводите расчеты с помощью удобного инструмента

2. Объедините несколько листов в сводную таблицу.

Таблица Google

1. Объедините листы Google в один лист.

1.1 Объедините листы Google в один лист с помощью функции фильтра

1.2 Объедините листы Google в один лист с помощью функции ИМПОРТРАНЖ

2. Объедините листы Google в одну книгу.

Расширение

1. Объедините листы и удалите дубликаты.

1.1 Объедините листы, затем удалите дубликаты, удалив дубликаты

1.2 Объедините листы и удалите дубликаты с помощью мощного инструмента

2. Объедините одноименные листы в одну книгу.

3. Объедините одинаковые диапазоны на разных листах в один лист.

Внимание

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

Объединить содержимое

1. Объедините все листы в один лист.

Вот рабочая тетрадь с 4 листами, которые необходимо объединить в один лист.

Нажмите, чтобы загрузить образец файла

1.1 Объедините все листы в один лист с кодом VBA

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

1. Нажмите F11 и другой ключи для включения Microsoft Visual Basic для приложений окно.

2. Затем во всплывающем окне нажмите Вставить > Модули чтобы вставить новый пустой модуль.

3. Скопируйте и вставьте приведенный ниже код в новый модуль.

VBA: объединить все листы в один

Sub CombineAllSheetsIntoOneSheet()
'UpdatebyExtendoffice
    Dim I As Long
    Dim xRg As Range
    On Error Resume Next
    Worksheets.Add Sheets(1)
    ActiveSheet.Name = "Combined"
   For I = 2 To Sheets.Count
        Set xRg = Sheets(1).UsedRange
        If I > 2 Then
            Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
        End If
        Sheets(I).Activate
        ActiveSheet.UsedRange.Copy xRg
    Next
End Sub

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

4. нажмите F5 Клавиша для запуска кода, лист с именем Combined был создан перед всеми листами, чтобы разместить все содержимое листов.

1.2 Объедините все таблицы или именованные диапазоны с помощью запроса (Excel 2016 или более поздние версии)

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

Нажмите, чтобы загрузить образец файла

Во-первых, убедитесь, что диапазоны были созданы как таблицы или именованные диапазоны, для создания таблицы и именованного диапазона см. Как преобразовать диапазон в таблицу или наоборот в Excel и Определение и использование имен в формулах.

1. Откройте книгу, в которой вы хотите объединить все листы, щелкните Данные > Новый запрос > Из других источников > Пустой запрос.

2. Во всплывающем Редактор запросов окна, перейдите к строке формул, введите в нее формулу ниже.

= Excel.CurrentWorkbook ()

Нажмите Enter key, все таблицы в текущей книге были перечислены.

3. Нажмите кнопку «Развернуть» рядом с Содержание и проверить Расширьте вариант и Выбрать все столбцы флажок.

4. Нажмите OK. Все таблицы перечислены одна за другой.

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

5. Затем нажмите Файл > Закрыть и загрузить в….

6. в Загрузить в диалог, проверьте Настольные вариант, затем выберите место для загрузки объединенной таблицы в Выберите, куда должны быть загружены данные разделом, щелкните нагрузка.

Теперь создается новый лист для размещения всех объединенных таблиц.

1.3 Объедините все листы в один с помощью удобного инструмента

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

1. Включите книгу, листы которой вы хотите объединить, щелкните Кутулс Плюс > Сочетать. Появится диалоговое окно, напоминающее вам о некоторых уведомлениях о пароле. Если книги, которые вы хотите использовать, не касаются пароля, просто нажмите OK для продолжения.

2. в Объедините рабочие листы — шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Download кнопку.

3. в Шаг 2 из 3 окно, по умолчанию текущая книга была указана и проверена в Список рабочих книг панели, и все листы текущей книги были перечислены и отмечены в Список рабочих листов панели, нажмите Download для продолжения.

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

4. На последнем шаге выберите нужный режим комбинирования, вы можете выбрать Объединить по строкам or Объединить по столбцу; затем укажите другие параметры по мере необходимости. Нажмите Завершить кнопку.

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

6. Теперь все листы объединены вместе, и появляется диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.

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

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

Объединить по столбцу

1.4 Дополнение к объединению листов в один лист

1.41 С помощью удобного инструмента, чтобы объединить только выбранные листы в один лист

Если вы хотите объединить только некоторые листы в книге в один лист, встроенная функция в Excel не может этого добиться. Но Сочетать особенность Kutools for Excel может сделать это.

1. Включите используемую книгу и нажмите Кутулс Плюс > Сочетать > OK для включения мастера объединения.

2. в Шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Download кнопку.

3. В окне Шаг 2 из 3 по умолчанию текущая книга была указана и отмечена на панели списка рабочих книг, а все листы текущей книги перечислены и отмечены на панели списка рабочих листов, снимите отметки с листов, которые не нужно объединять, нажмите «Далее», чтобы продолжить.

4. На последнем шаге укажите необходимые параметры. Нажмите Завершить кнопку.

6. Теперь только отмеченные листы были объединены вместе, и появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.

1.42 С помощью удобного инструмента для объединения листов из нескольких книг в один лист

Например, вы хотите объединить все листы book1, book2 и book3 в один лист, как показано на скриншоте ниже. Сочетать особенность Kutools for Excel также может вам помочь.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Включите используемые книги и нажмите Кутулс Плюс > Сочетать > OK для Сочетать Мастер.

2. в Шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Download кнопку.

3. в Шаг 2 из 3 окно, все открытые книги были перечислены и отмечены в Список рабочих книг панели, и все листы открытых книг были перечислены и отмечены в Список рабочих листов панели, нажмите Download для продолжения.

Совет: если вы хотите добавить книги для объединения, нажмите кнопку «Добавить», чтобы выбрать путь для добавления книг.

4. На последнем шаге выберите нужные вам параметры. Нажмите Завершить кнопку.

6. Теперь все листы в открытых книгах были объединены вместе, и появится диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.

Откройте объединенную книгу, которую вы сохранили, листы книг были объединены в один лист.

2. Объедините несколько книг в одну книгу.

Вот три книги, которые необходимо объединить в одну книгу.

янв.xlsx фев.xlsx мар.xlsx

2.1 Объединение книг в одну с помощью команды «Переместить» или «Копировать»

Только для объединения листов в нескольких книгах Excel Переместить или скопировать Команда может оказать вам услугу.

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

Tips

1) Щелкните правой кнопкой мыши на одной вкладке и выберите Выбрать все листы из контекстного меню сначала, если вы хотите переместить все листы, затем используйте Переместить или скопировать команда.

2) Холдинг Ctrl Клавиша выбора нескольких несмежных листов.

3) Выберите первый лист и удерживайте Shift Клавиша выбора последнего листа для выбора нескольких соседних листов.

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

Примечание:

1) Вам лучше проверить Создать копию, или исходная книга потеряет лист после перемещения.

2) Если вы хотите поместить все книги в новую книгу, выберите (новая книга) in Бронировать выпадающий список.

Как листы были перемещены в основную книгу.

Повторите шаги, описанные выше, чтобы переместить все книги в одну.

2.2 Объединение нескольких книг (в одной папке) в одну книгу с кодом VBA

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

1. Включите книгу, в которую вы хотите объединить все книги, затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Во всплывающем окне щелкните Вставить > Модули чтобы вставить новый пустой модуль.

3. Скопируйте и вставьте ниже код VBA в новый модуль.

VBA: объединить несколько книг в текущую книгу

Sub GetSheets()
'Updated by Extendoffice
Path = "C:UsersAddinTestWin10Desktopcombine sheetscombine sheets into one workbook"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

Внимание

В коде VBA сценарий «C: Users AddinTestWin10 Desktop объединить листы объединить листы в одну книгу «- это путь к папке, в которой находятся книги, которые вы хотите объединить, измените его в соответствии с вашими потребностями.

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

4. Нажмите F5 нажмите клавишу для запуска кода, тогда все книги будут скопированы в конец текущей книги.

Наконечник: все скопированные листы будут использовать свое исходное имя, если вы хотите использовать имя книги в качестве префикса, используйте приведенный ниже код:

Sub MergeWorkbooks()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:UsersAddinTestWin10Desktopcombine sheetscombine sheets into one workbook"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

2.3 Объедините несколько книг (из нескольких папок) в одну книгу с помощью удобного инструмента

Иногда вам может потребоваться объединить все книги из разных папок в одну книгу. Например, чтобы объединить все книги в папке 2020 и 2021 в одну книгу, в Excel нет прямого способа справиться с этим.

Однако Сочетать особенность Kutools for Excel предоставляет возможность объединить книги из нескольких папок в одну.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Включите Excel, щелкните Кутулс Плюс > Сочетать, Появится диалоговое окно, напоминающее вам о некоторых уведомлениях о пароле. Если книги, которые вы хотите использовать, не касаются пароля, просто нажмите OK для продолжения.

2. в Объедините рабочие листы — шаг 1 из 3 окно, проверка Объедините несколько листов из книг в одну книгу , нажмите Download кнопку.

3. в Шаг 2 из 3 в окне щелкните стрелку рядом с Добавить кнопку, чтобы отобразить раскрывающееся меню, щелкните Папка.

4. Затем в Выбор папки диалоговом окне выберите одну папку, которую вы хотите использовать, щелкните Выбор папки добавить это к Список рабочих книг .

5. Повторите описанный выше шаг, чтобы добавить все папки и перечислить все книги в Список рабочих книг, нажмите Download.

6. На последнем шаге выберите нужные вам параметры. Нажмите Завершить кнопку.

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

8. Теперь все листы объединены вместе, и появляется диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.

Появится новая книга, в которой перечислены результаты объединения, щелкните ссылку Выходной файл, чтобы проверить объединенный лист.

Объединить результат

Примечание. В объединенной книге первый лист с именем Kutools for Excel содержит некоторую информацию об исходных рабочих листах и окончательных объединенных листах, вы можете удалить ее, если она вам не нужна.

3. Объедините отдельные листы из нескольких книг в одну книгу.

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

четверть-1.xlsx четверть-2.xlsx четверть-3.xlsx

3.1 Объединение определенных листов из книг (все книги в одной папке) в одну книгу с кодом VBA

1. Откройте книгу, чтобы найти объединенные листы, затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Во всплывающем окне щелкните Вставить > Модули чтобы вставить новый пустой модуль.

3. Скопируйте и вставьте ниже код VBA в новый модуль.

VBA: объединить определенные листы нескольких книг в текущую книгу

Sub MergeSheets2()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
 
xStrPath = "C:UsersAddinTestWin10Desktopcombine sheetscombine specific sheets from multiple workbooks"
xStrName = "A,B"
 
xArr = Split(xStrName, ",")
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub

Внимание

1) В коде VBA скрипт » C: Users AddinTestWin10 Desktop объединить листы объединить определенные листы из нескольких книг «- это путь, по которому находятся книги, которые вы хотите объединить, измените его в соответствии с вашими потребностями.

2) В коде VBA скрипт «А, В«- это имена отдельных листов из книг, которые я хочу объединить, измените их по своему усмотрению, используя запятые для разделения каждого имени листа.

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

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

3.2 Объединение определенных листов из нескольких книг (из нескольких папок) с помощью удобного инструмента

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

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

5. Повторите вышеуказанный шаг, чтобы добавить все папки и перечислить все книги в Список рабочих книг.

6. Тогда оставайтесь в Шаг 2 из 3 в окне выберите одну книгу в Список рабочих книг, и проверяйте только те листы, которые хотите использовать в Список рабочих листовзатем нажмите Тот же лист кнопка. Теперь все одноименные листы рабочих тетрадей в Список рабочих книг были проверены. Нажмите Download для продолжения.

7. На последнем шаге выберите нужные вам параметры. Нажмите Завершить кнопку.

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

9. Теперь отдельные листы были объединены вместе, и появляется диалоговое окно, в котором вас спросят, сохранить ли настройки как сценарий, нажмите Да or Нет как тебе нравится.

Объединить результат

4. Объедините два листа на основе ключевого столбца.

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

Комбинированный стол

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

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

объединить две таблицы на основе ключевого столбца запроса.xlsx

1. Щелкните любую ячейку первой таблицы, щелкните Данные > Из таблицы в Получить и преобразовать группа.

2. в Редактор запросов диалоговое окно, нажмите Файл > Закрыть и загрузить к команда. Смотрите скриншот:

3. Затем во всплывающем Загрузить в диалог, проверьте Только создать соединение вариант. Нажмите нагрузка.

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

Повторите шаги, указанные выше, чтобы добавить вторую таблицу в Запросы к книге панель.

4. Затем нажмите Данные > Новый запрос > Объединить запросы > идти.

5. в идти В двух раскрывающихся списках отдельно выберите две таблицы, которые вы хотите объединить. Таблица внизу будет объединена с таблицей выше.

6. Щелкните ключевой столбец, на основе которого вы хотите объединить две таблицы, щелкните OK.

7. Merge1 — редактор запросов всплывающее окно, нажмите кнопку «Развернуть» рядом с Новая колонка, затем отметьте все столбцы, кроме ключевого, снимите флажок Использовать исходное имя столбца в качестве префикса флажок, щелкните OK.

Теперь две таблицы были объединены в одну на основе указанного ключевого столбца.

8. Нажмите Файл > Закрыть и загрузить в, В Загрузить в диалог, проверьте Настольные вариант и укажите место, которое вы хотите загрузить. Нажмите нагрузка.

Теперь две таблицы были объединены на основе ключевого столбца.

4.2 Объединение двух листов на основе одного ключевого столбца с помощью функций Excel

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

Например, переместите полные данные с листа 2 на лист 1 и на основе столбца A найдите данные.

объединить два листа на основе ключевого столбца functions.xlsx

4.21 Объединение двух листов по столбцу с помощью функции ВПР.

1. Скопируйте и вставьте формулу ниже рядом с таблицей на листе 1:

= ВПР (A2; Лист2! $ A $ 2: $ B $ 5,2; ЛОЖЬ)

Объяснение:

A2: первое значение поиска (ключевой столбец);

Sheet2! $ A $ 2: $ B $ 5: массив таблицы, таблица содержит два или более столбца, в которых находятся столбец значения поиска и столбец значения результата;

2: индекс столбца, конкретный номер столбца (это целое число) table_array, из которого вы вернете совпадающее значение.

2. Нажмите Enter ключ, чтобы получить первый результат.

3. Затем перетащите маркер автозаполнения вниз, чтобы заполнить все данные.

4. Не снимая выделения с ячеек формулы, щелкните Главная вкладка и перейдите к форматированию ячеек так, как вам нужно, в Число группа.

Для получения дополнительной информации о функции ВПР щелкните здесь.

4.22 Объединение двух листов по столбцу с помощью формулы объединение функций ПОИСКПОЗ и ИНДЕКС

1. Скопируйте и вставьте формулу ниже рядом с таблицей на листе 1:

=INDEX(Sheet2!$B$2:$B$5,MATCH(Sheet1!A2,Sheet2!$A$2:$A$5,0))

Объяснение:

Sheet2! $ B $ 2: $ B $ 5: столбец с совпадающим значением, которое вы хотите найти;

Sheet1! A2: первое значение поиска (в ключевом столбце);

Sheet2! $ A $ 2: $ A $ 5: ключевой столбец, на основе которого вы хотите объединить два листа.

2. Нажмите Enter ключ, чтобы получить первый результат.

3. Затем перетащите маркер автозаполнения вниз, чтобы заполнить все данные.

Подробнее об INDEX.

Подробнее о МАТЧЕ.

4.3 Объединение двух листов на основе одного ключевого столбца с помощью удобного инструмента

Кроме функции объединения, есть еще одна мощная функция — Слияние таблиц in Kutools for Excel, который может быстро и легко объединить два листа на основе одного ключевого столбца.

Комбинировать два листа на основе ключевого столбца удобный инструмент.xlsx

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс Плюс >Слияние таблиц.

2. В TСлияние способностей — шаг 1 из 5 в окне выберите диапазоны таблиц в Выберите основную таблицу и Выберите таблицу поиска раздел отдельно, нажмите Download.

3. в Шаг 2 из 5 в окне, отметьте ключевой столбец, на основе которого вы хотите объединить, щелкните Download.

4. в Шаг 3 из 5 в окне вы можете проверить столбцы, данные в которых вы хотите обновить, на основе таблицы поиска, если вам не нужны данные для обновления, просто щелкните непосредственно Download.

5. в Шаг 4 из 5 в окне, здесь отметьте столбцы, которые вы хотите объединить в основную таблицу, затем нажмите Download.

6. На последнем шаге укажите нужные вам параметры, нажмите Завершить.

Эта функция поддерживает объединение двух листов в разных книгах.

5. Объедините два листа на основе двух столбцов.

Предположим, есть две таблицы на листе Sheet1 и Sheet2 отдельно, теперь, чтобы переместить данные в столбце End_Dates с листа2 на лист1 на основе столбца Project и столбца Start_Date, как показано ниже:

Комбинированный лист

Нажмите, чтобы загрузить образец файла

В Excel встроенные функции не поддерживают эту операцию, но Слияние таблиц of Kutools for Excel может справиться с этим.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс Плюс > Слияние таблиц.

2. в Объединение таблиц — шаг 1 из 5 в окне выберите диапазоны таблиц в Выберите основную таблицу и Выберите вкладку поискараздел e отдельно, нажмите Download.

3. в Шаг 2 из 5 в окне отметьте два ключевых столбца, на основе которых вы хотите объединить, щелкните Download.

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

5. в Шаг 4 из 5 в окне, отметьте здесь столбцы, которые вы хотите объединить в основную таблицу, затем щелкните Download.

6. На последнем шаге укажите нужные вам параметры, нажмите Завершить.

Затем столбцы, которые вы хотите добавить, были добавлены в конец основной таблицы.

Эта функция поддерживает объединение двух листов в разных книгах.

6. Объедините листы с одинаковыми заголовками.

Чтобы объединить несколько листов с одинаковым заголовком, как показано на скриншоте ниже:

Комбинированный лист

Нажмите, чтобы загрузить образец файла

6.1 Объедините все листы с одинаковыми заголовками с помощью VBA

Существует код VBA, который может объединить все листы книги с одинаковыми заголовками.

1. Включите книгу, в которой вы хотите объединить листы с одинаковым заголовком, затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Во всплывающем окне щелкните Вставить > Модули чтобы вставить новый пустой модуль.

3. Скопируйте и вставьте ниже код VBA в новый модуль.

VBA: объединить листы с одинаковым заголовком

Sub Combine()
'Update by Extendoffice
    Dim i As Integer
    Dim xTCount As Variant
    Dim xWs As Worksheet
    On Error Resume Next
LInput:
    xTCount = Application.InputBox("The number of title rows", "", "1")
    If TypeName(xTCount) = "Boolean" Then Exit Sub
    If Not IsNumeric(xTCount) Then
        MsgBox "Only can enter number", , "Kutools for Excel"
        GoTo LInput
    End If
    Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
    xWs.Name = "Combined"
    Worksheets(2).Range("A1").EntireRow.Copy Destination:=xWs.Range("A1")
    For i = 2 To Worksheets.Count
        Worksheets(i).Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
               Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
    Next
End Sub

4. нажмите F5 нажмите клавишу для запуска кода, появится диалоговое окно с запросом количества строк заголовка, введите число в текстовое поле. Нажмите OK.

Теперь все листы в этой книге были объединены в новый лист с именем Combined.

6.2 Объединение листов с одинаковыми заголовками с помощью удобного инструмента

Наблюдения и советы этой статьи мы подготовили на основании опыта команды Сочетать особенность Kutools for Excel также может справиться с этой задачей и поддерживает объединение листов в книгах.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Включите книгу, листы которой вы хотите объединить с одинаковыми заголовками, щелкните Кутулс Плюс > Сочетать.

2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не содержат паролей, щелкните OK продолжить. В появлении Объедините рабочие листы — шаг 1 из 3 окно, проверка Объедините несколько листов из книг в один лист , нажмите Download.

3. в Шаг 2 из 3 окно, все листы перечислены и зарегистрированы Список рабочих листов разделом, щелкните Download продолжать.

Советы:

1) Если вы хотите объединить только некоторые листы, просто отметьте нужные имена листов и оставьте другие не отмеченными.

2) Если вы хотите добавить больше книг для объединения, щелкните Добавить для добавления файлов или папок в Список рабочих книг .

4. в Шаг 3 из 3, проверить Объединить по строкам вариант и введите количество строк заголовка в поле Количество строк заголовка текстовое поле, укажите другие параметры по своему усмотрению. Нажмите Завершить.

5. Выберите папку и дайте имя новой книге в Укажите имя файла и расположение для объединенной книги диалоговое окно, нажмите Сохраните.

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

Консолидировать

1. Объедините листы и произведите расчеты.

Например, есть три листа с одинаковыми заголовками строк и столбцов, теперь вы хотите объединить их с одинаковыми заголовками и суммировать данные, как показано на скриншотах ниже.

Результат

Нажмите, чтобы загрузить образец файла

1.1 Объединяйте листы и выполняйте вычисления с помощью функции «Объединить»

В Excel Консолидировать функция поддерживает объединение листов и выполнение расчетов.

1. Включите книгу, листы которой вы хотите объединить, и выберите ячейку, в которую вы хотите поместить объединенные данные, щелкните Данные > Консолидировать.

2. в Консолидировать диалоговое окно, выполните следующие настройки:

1). Функция в раскрывающемся меню выберите расчет, который нужно выполнить после объединения листов.

2) Щелкните значок выбора рядом с Приложения кнопку, чтобы выбрать диапазон для объединения, и нажмите кнопку Добавить до Все ссылки .

Повторите этот шаг, чтобы добавить все диапазоны, которые необходимо объединить в Все ссылки .

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

4) Если вы хотите, чтобы объединенное содержимое изменялось по мере изменения данных источников, установите флажок Создавать ссылки на исходные данные флажок.

3. Нажмите OK. Диапазоны объединены и суммированы по заголовкам.

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

1.2 Объединяйте листы и проводите расчеты с помощью удобного инструмента

Наблюдения и советы этой статьи мы подготовили на основании опыта команды Сочетать особенность Kutools for Excel поддерживает объединение листов в нескольких книгах и выполнение вычислений на одном листе.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Включите книги, которые вы хотите объединить, щелкните Кутулс Плюс > Сочетать.

2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не содержат паролей, щелкните OK продолжить. В появлении Объедините рабочие листы — шаг 1 из 3 окно, проверка Объединяйте и вычисляйте значения из нескольких книг на одном листе , нажмите Download.

3. в Шаг 2 из 3 окно, все листы открытых книг перечислены и проверены Список рабочих листов разделом, щелкните Download продолжать.

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

4. в Шаг 3 из 3, укажите расчет, нужные вам ярлыки. Нажмите Завершить.

2. Объедините несколько листов в сводную таблицу.

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

Нажмите, чтобы скачать образец

1. Включите рабочую книгу, которую вы будете использовать, щелкните Настройка панели быстрого доступа > Дополнительные команды.

2. Во всплывающем Параметры Excel окно, выберите Все команды из Выберите команды из раздел, затем перетащите полосу прокрутки, чтобы выбрать Мастер сводных таблиц и диаграмм.

3. Нажмите Добавить добавить Мастер сводных таблиц и диаграмм до Настройка панели быстрого доступа, нажмите OK.

4. Нажмите Мастер сводных таблиц и диаграмм на панели инструментов и в Шаг 1 из 3, проверить Несколько диапазонов консолидации и сводная таблица параметры, щелкните Download.

5. в Шаг 2а из 3, отметьте, что я создам опцию полей страницы, нажмите Download.

6. в Шаг 2b из 3, щелкните значок выбора, чтобы выбрать диапазон, который вы хотите объединить, щелкните Добавить добавить его в Все диапазоны раздел, повторите этот шаг, чтобы добавить все диапазоны, используемые для объединения. Отметьте 0 в Сколько полей страницы вы хотите. Нажмите Download.

7. Выберите место, в котором вы хотите создать сводную таблицу. Шаг 3 из 3, нажмите Завершить.

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

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

Таблица Google

1. Объедините листы Google в один лист.

Предположим, что необходимо объединить три листа Google в один, как показано на скриншоте ниже:

Нажмите, чтобы загрузить образец файла

1.1 Объедините листы Google в один лист с помощью функции фильтра

На листе, на котором вы хотите разместить комбинированные данные, выберите ячейку, введите формулу ниже:

= ({filter (A! A2: B, len (A! A2: A)); filter (B! A2: B, len (B! A2: A)); filter (‘C’! A2: B, len (‘C’! A2: A))})

Затем данные на листах A, B и C были скопированы.

В формуле:

А, В, С имена листов, A2: B диапазон данных на каждом листе, A2: A — это первый столбец каждого диапазона данных.

1.2 Объедините листы Google в один лист с помощью функции ИМПОРТРАНЖ

Эта формула часто используется для объединения двух диапазонов в таблице Google.

Выберите ячейку под первым диапазоном данных, введите формулу ниже:

=IMPORTRANGE(«https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit»,»B!A2:B4″)

Затем данные из листа B были скопированы ниже.

В формуле

https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit

это расположение листа, вы можете найти его в строке веб-адреса.

В! А2: В4 — это диапазон ячеек на листе B, который вы хотите скопировать в первый диапазон.

Примечание: если ячейка, в которой размещается формула, была отредактирована ранее, формула вернет # ССЫЛКА!

2. Объедините листы Google в одну книгу.

Если вы хотите объединить листы из нескольких книг в Google Sheet, нет быстрого способа, как Excel.

Чтобы объединить листы Google из книг в одну книгу, вы можете использовать Скопировать в команда контекстного меню.

Щелкните правой кнопкой мыши лист, который вы хотите использовать, щелкните Скопировать в > Новая таблица or Существующая таблица.

Если вы выбираете Новая таблица, появится диалоговое окно с напоминанием о том, что лист был скопирован, щелкните OK. Теперь текущий лист скопирован в новую книгу. Вы можете нажать Открыть таблицу Проверять.

Если вы выбираете Существующая таблица, Выберите электронную таблицу, в которую нужно скопировать этот лист появляется диалоговое окно.

1) Выберите, где находится существующая электронная таблица;

2) Выберите книгу, в которую вы хотите скопировать лист. Файлы;

3) Или вы можете напрямую ввести веб-адрес книги, в которую хотите скопировать, в поле Или вставить веб-адрес здесь .

4) Нажмите Выберите.

Если лист копируется в новую электронную таблицу, имя листа в новой книге совпадает с исходным именем, если лист копируется в существующую электронную таблицу, к имени листа в объединенной книге будет добавлен префикс Копия .

Расширение

1. Объедините листы и удалите дубликаты.

Предположим, есть два листа, на которых есть несколько дубликатов, нам нужно объединить данные и удалить дубликаты, как показано на скриншоте ниже:

Нажмите, чтобы скачать образец

1.1 Объедините листы, затем удалите дубликаты, удалив дубликаты

В Excel Удалить дубликаты функция поможет вам быстро удалить повторяющиеся данные.

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

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

Затем повторяющиеся строки были удалены.

1.2 Объедините листы и удалите дубликаты с помощью удобного инструмента

Если нужно объединить только два диапазона и удалить дубликаты, Слияние таблиц of Kutools for Excel может напрямую удалять дубликаты при объединении.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Включите книгу, содержащую данные, которые вы хотите объединить, щелкните Кутулс Плюс > Слияние таблиц.

2. В TСлияние способностей — шаг 1 из 5 выберите два диапазона, которые вы хотите объединить, и, если в таблицах есть два заголовка, установите флажок Основная таблица имеет заголовок и Таблица поиска имеет заголовок флажки, щелкните Download.

3. в Шаг 2 из 5 в окне, отметьте ключевой столбец, на основе которого вы хотите объединить диапазоны, щелкните Download.

4. в Шаг 3 из 5 в окне вы можете проверить столбцы, данные в которых вы хотите обновить, на основе таблицы поиска, щелкните Download перейти к последнему шагу.

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

5. в Шаг 5 из 5 окно, в Добавить параметры раздел, проверка Добавить несовпадающие строки в конец основной таблицы флажок в Варианты обновления раздел, проверка Обновляйте ячейки только при наличии данных в таблице поиска флажок. Нажмите Завершить.

Затем две таблицы объединяются в основную таблицу без дубликатов.

2. Объедините рабочие листы с одинаковыми именами в один рабочий лист.

Предположим, что существует несколько книг с листами с одинаковыми именами, такими как лист1, лист2, теперь, чтобы объединить все листы с именем лист1 в один лист, все листы с именем лист2 в один лист, как показано ниже, вы можете использовать Kutools for ExcelАвтора Сочетать функция, чтобы быстро справиться с этой работой.

2020.xlsx 2021.xlsx

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Включите книги, которые вы объедините, нажмите Kутулс плюс > Сочетать.

2. Появится диалоговое окно с напоминанием о пароле. Если книги, которые вы используете, не содержат паролей, щелкните OK продолжить. В появлении Объедините рабочие листы — шаг 1 из 3 окно, проверка Объедините все листы с одинаковыми именами в один лист , нажмите Download.

Советы:

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

Если вы хотите добавить больше книг для объединения, щелкните Добавить для добавления файлов или папок в Список рабочих книг .

4. в Шаг 3 из 3, укажите нужные вам настройки. Нажмите Завершить.

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

Объединить по столбцу

3. Объедините одинаковые диапазоны на разных листах в один лист.

Если вы хотите объединить одни и те же диапазоны на листах в один лист, например, объедините только диапазон A1: B5 рабочей книги A и рабочей книги B на один лист, функция «Объединить» Kutools for Excel будет хорошим выбором.

A.xlsx B.xlsx

1. Включите книги, которые вы будете использовать, щелкните Кутулс Плюс > Сочетать.

3. в Шаг 2 из 3 окно, все листы открытых книг перечислены и проверены Список рабочих листов раздел, щелкните значок выбора в Список рабочих листов, затем выберите диапазон, который вы хотите использовать. Затем нажмите Тот же диапазон кнопку, чтобы установить диапазон всех листов A1: B5. Нажмите Download.

Советы:

4. в Шаг 3 из 3, укажите нужные вам настройки. Нажмите Завершить.

5. Выберите папку и дайте имя новой книге в диалоговом окне «Укажите имя файла и расположение для объединенной книги», нажмите Сохраните.

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Группировка сводной таблицы по номер недели, день недели и другое … Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя…

Вкладка Office — предоставляет интерфейс с вкладками в Office и значительно упрощает вашу работу

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Используйте раскрывающийся список, чтобы выбрать (новая книга) . Это будет служить основной электронной таблицей. где мы отправляем все наши отдельные листы. Вы можете использовать поле « До листа:», чтобы указать порядок размещения листов.

Повторите этот процесс с остальными листами, которые вы хотите объединить. Затем сохраните новый мастер-документ.

Иногда вам может понадобиться взять несколько наборов данных и представить их как один лист. Это довольно легко сделать в Excel, если вы потратите время на то, чтобы ваши данные были отформатированы заблаговременно.

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

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

На этом новом листе перейдите на вкладку « Данные » и нажмите « Консолидировать» .

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

Сделайте это для всех наборов данных, которые вы хотите объединить. Вы даже можете рисовать из других книг, используя кнопку « Обзор» , также известную как « Выбрать» в версии Excel для Mac.

Установите флажок Создать ссылки на исходные данные, если вы собираетесь продолжать обновлять данные на других листах и хотите, чтобы этот лист отражал это. Вы также можете выбрать, какие ярлыки переносятся с помощью флажков, показанных выше. Я выбрал галочку в обоих полях, потому что хотел оба набора меток.

Наконец, нажмите ОК .

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

Объединение рабочих тетрадей с VBA

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

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

Перейдите на вкладку Разработчик и нажмите Visual Basic .

Нажмите Вставить> Модуль и скопируйте и вставьте следующий код, взятый из руководства ExtendOffice :

Sub GetSheets() Path = "C:UsersBrad\MergingSheets" Filename = Dir(Path & "*.xls") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub

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

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

Смотри, прежде чем прыгать

Объединение листов и файлов в Excel довольно сложное и грязное. Этот факт должен осветить один из самых важных уроков о Microsoft Excel: всегда хорошо планировать заранее.

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

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

У вас есть совет для объединения различных наборов данных? Или вы ищете помощь с методами в этом руководстве? В любом случае, почему бы не присоединиться к беседе в разделе комментариев ниже?

Источник

Объединение данных с нескольких листов

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Консолидация по расположению

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

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

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

На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.

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

Выделите на каждом листе нужные данные.

Путь к файлу вводится в поле Все ссылки.

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

Консолидация по категории

Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред., а другой — Среднее, консолидация не просуммирует эти столбцы.

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

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

На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.

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

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

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

Путь к файлу вводится в поле Все ссылки.

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

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

Консолидация по расположению

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

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

На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.

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

Выделите на каждом листе нужные данные и нажмите кнопку Добавить.

Путь к файлу вводится в поле Все ссылки.

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

Консолидация по категории

Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред., а другой — Среднее, консолидация не просуммирует эти столбцы.

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

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

На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.

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

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

Путь к файлу вводится в поле Все ссылки.

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

Как в экселе объединить страницы одну. Как объединить файлы Excel? Объединение листов в одну книгу

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

С надстройкой «Объединить листы», вы сможете собрать данные на один сводный лист в считанные секунды:

Собрать данные листов из разных книг на одном листе
Собрать данные листов с одинаковым именем и объединить по имени вкладки
Объединить данные листов с идентичной структурой под одним заголовком
Сохранить форматирование в сводном листе результата

Добавить «Объединить листы» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 — 2007, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

Как объединить данные из нескольких листов в один в один за 3 шага

С надстройкой «Объединить листы» вы можете собрать и объединить данные нескольких листов — и из разных книг — в один главный лист всего за 3 шага:

1. Нажмите кнопку «Объединить листы» на панели XLTools > Выберите тип операции:

Объединить данные листов с одинаковым именем на одном листе

2. Отметьте листы, которые нудно объединить. Дерево данных отображает все листы во всех открытых книгах.

3. Нажмите кнопку «Объединить» > Готово! Все данные скопированы на один главный сводный лист.

Как объединить данные нескольких листов на одном сводном листе

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

Нажмите кнопку «Объединить листы» > Выберите «Объединить данные нескольких листов на одном листе».
Нажмите кнопку «Объединить» > Готово, все данные из выбранных листов собраны на одном главном листе в новой книге.

Как объединить данные из листов c одинаковым именем на одном сводном листе

Предположим, у вас есть ряд книг, и каждая из них содержит отчёт о региональных продажах. Каждый отчёт разбит на листы с данными по конкретным продуктам — таким образом, вкладки в региональных отчётах имеют одинаковые названия. Надстройка поможет вам скопировать данные по всем отчётам в один сводный лист.

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

Каким образом данные копируются на сводный лист

Объединение данных по сути означает извлечение и копирование данных из нескольких исходных листов на новый лист.

Данные копируются полностью — весь диапазон до последней использованной ячейки на исходном листе.
Скопированные диапазоны добавляются последовательно, один диапазон под последней строкой предыдущего диапазона.
Надстройка XLTools «Объединить листы» сохраняет форматирование ячеек и таблиц, ссылки на ячейки, функции и формулы, объединённые ячейки, т.д.
Исходные данные не подвергаются изменениям.

Как объединить несколько листов в одну книгу

Вы можете объединить несколько листов в одну книгу с помощью надстройки XLTools Органайзер книг . Она помогает копировать и управлять множеством листов одновременно.

Появились вопросы или предложения? Оставьте комментарий ниже.

Чаще всего работать с табличными данными в офисе или дома приходится в табличном редакторе Microsoft Office Excel. Каждый файл этого приложения содержит один документ, разделенный на отдельные листы с электронными таблицами. Увы, среди команд приложения нет функции автоматического объединения нескольких листов документа в один. Тем не менее, такая задача возникает не так уж редко, и решать ее приходится либо «вручную», либо с использованием скриптов — «макросов».

Вам понадобится

Табличный редактор Microsoft Office Excel 2007 или 2010.

Инструкция

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

Одновременная работа пользователей в одной книге

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

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

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

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

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

макрос (надстройка) для объединения нескольких файлов Excel в одну книгу

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. выбирать файлы для объединения, а также редактировать список выбранных файлов;

3. объединять все листы выбранных файлов в одну рабочую книгу;

4. объединять в рабочую книгу только непустые листы выбранных файлов;

5. собирать в итоговую книгу листы с заданным именем (можно использовать маску при помощи спец. символов совпадения);

6. собирать в одну книгу листы выбранных файлов с определенным номером (индексом), либо диапазоном номеров;

7. собирать листы с определенным значением в заданном диапазоне ячеек;

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

При необходимости имена листов сформированной рабочей книги можно быстро изменить при помощи надстройки для автоматического переименования листов .

Объединение диапазонов значений из разных листов разных рабочих книг на отдельном листе

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

Объединить листы: собрать данные с нескольких листов на один сводный лист в секунды

С надстройкой «Объединить листы», вы сможете собрать данные на один сводный лист в считанные секунды:

Собрать данные листов из разных книг на одном листе
Собрать данные листов с одинаковым именем и объединить по имени вкладки
Объединить данные листов с идентичной структурой под одним заголовком
Сохранить форматирование в сводном листе результата

Добавить «Объединить листы» в Excel 2019, 2016, 2013, 2010

Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

Как объединить данные из нескольких листов в один в один за 3 шага

1. Нажмите кнопку «Объединить листы» на панели XLTools > Выберите тип операции:

2. Отметьте листы, которые нудно объединить. Дерево данных отображает все листы во всех открытых книгах.

3. Нажмите кнопку «Объединить» > Готово! Все данные скопированы на один главный сводный лист.

Как объединить данные нескольких листов на одном сводном листе

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

Внимание: не забудьте сохранить эту сводную книгу на свой компьютер.

Как объединить данные из листов c одинаковым именем на одном сводном листе

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

Внимание: не забудьте сохранить эту сводную книгу на свой компьютер.

Каким образом данные копируются на сводный лист

Данные копируются полностью — весь диапазон до последней использованной ячейки на исходном листе.
Скопированные диапазоны добавляются последовательно, один диапазон под последней строкой предыдущего диапазона.
Надстройка XLTools «Объединить листы» сохраняет форматирование ячеек и таблиц, ссылки на ячейки, функции и формулы, объединённые ячейки, т.д.
Исходные данные не подвергаются изменениям.

Как объединить несколько листов в одну книгу

Вы можете объединить несколько листов в одну книгу с помощью надстройки XLTools Органайзер книг. Она помогает копировать и управлять множеством листов одновременно.

Появились вопросы или предложения? Оставьте комментарий ниже.

8 Комментариев к Объединить листы: собрать данные с нескольких листов на один сводный лист в секунды

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

Руслан, добрый день! Спасибо, что написали. Надстройка сейчас хорошо объединяет однотипные листы. Но идеально подготовленные данные встречаются не часто, особенно, если участвует много людей. Так что мы думаем, как улучшить надстройку. Для вашего случая, думаю, нужно предварительное удаление пустых строк-столбцов. Мы поставим это в план на следующие релизы. Спасибо!

Здравствуйте, как производить объединение двух и более листов, если в каждой таблицы совпадение по наименованию идет по одному. Например у меня есть лист «Стран» и есть лист «Города» у них сопадение идет только по полю «ID страна», аналогично по товарам: в одном листе данные «категории товаров», а в другом где «продукты» у них объединение нужно сделать именно по «ID категория»

Карашаш, добрый день!
Надстройка «Объединение листов» сейчас больше рассчитана на объединение отнотипных по структуре листов. В Вашем случае лучше подойдет надстройка «SQL запросы». Она поможет объединить таблицы по ключевому полю.

При объединении нескольких листов в один удаляются ли дубликаты данных (при их наличии)?

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

Спасибо! Отличная надстройка. Сколько стоит после пробного периода?

Евгения, спасибо! Надстройка «Объединить листы» входит в любой из трех пакетов лицензий — Базовая, PRO и GURU. Выбирайте, какой пакет вам больше подойдет.

Как объединить файлы Excel? Объединение листов в одну книгу

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

Одновременная работа пользователей в одной книге

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

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. выбирать файлы для объединения, а также редактировать список выбранных файлов;

3. объединять все листы выбранных файлов в одну рабочую книгу;

4. объединять в рабочую книгу только непустые листы выбранных файлов;

6. собирать в одну книгу листы выбранных файлов с определенным номером (индексом), либо диапазоном номеров;

7. собирать листы с определенным значением в заданном диапазоне ячеек;

8. задавать дополнительные параметры для объединения, такие как:

а) присвоение листам имен объединяемых файлов;

б) удаление из книги, в которой происходит объединение данных, собственных листов, которые были в этой книге изначально;

в) замена формул значениями (результатами вычислений).

Объединение диапазонов значений из разных листов разных рабочих книг на отдельном листе

Как в excel страницы объединить в одну

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

Инструкция

Устанавливаем себе надстройку ЁXCEL . Читаем справку.

Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Объединить таблицы»:

В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:

Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:

Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку «A1«. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:

В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:

В открывшемся диалоговом окне устанавливаем переключатели в положения «Таблица» и «Имеющийся лист», нажимаем «ОК»:

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

Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку «Данные» и нажмите кнопку «Обновить все»:

В итоговой таблице появятся строчки, добавленные в выбранный вами лист.

Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):

Видео-пример

Важно:

Количество столбцов во всех таблицах должно быть одинаково;
Кроме таблиц на листах не должно быть никакой информации;
Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте — необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).

Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:

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

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

Возможные ошибки при использовании этого метода:

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

Еще, вопрос. Хочу сливать сразу много однотипных таблиц. Но не все данные а только первые 15 столбцов.
Начиная с 16-й колонки у меня расчетные данные, которые не нужно сливать.

Источник