Объединить листы в один excel: Как объединить листы в Еxcel в один

Как объеденить кучу Excel файлов в один? — Хабр Q&A

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

Открываем книгу, куда хотим собрать листы из других файлов, входим в редактор Visual Basic (ALT+F11), добавляем новый пустой модуль (в меню Insert — Module) и копируем туда текст вот такого макроса:

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer

Application.ScreenUpdating = False ‘отключаем обновление экрана для скорости

‘вызываем диалог выбора файлов для импорта
FilesToOpen = Application.GetOpenFilename _
(FileFilter:=»All files (*.*), *.*», _
MultiSelect:=True, Title:=»Files to Merge»)

If TypeName(FilesToOpen) = «Boolean» Then
MsgBox «Не выбрано ни одного файла!»
Exit Sub
End If

‘проходим по всем выбранным файлам
x = 1
While x <= UBound(FilesToOpen)
Set importWB = Workbooks.

Open(Filename:=FilesToOpen(x))
Sheets().Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
importWB.Close savechanges:=False
x = x + 1
Wend

Application.ScreenUpdating = True
End Sub

После этого можно вернуться в Excel и запустить созданный макрос через меню Сервис — Макрос — Макросы (Tools — Macro — Macros) или нажав ALT+F8. Отобразится диалоговое окно открытия файла, где необходимо указать один или несколько (удерживая CTRL или SHIFT) файлов, листы из которых надо добавить к текущей книге.
PE

Ответ написан более трёх лет назад

Как насчет pandas?
Допустим, все файлы лежат в одной папке, и необходимые данные лежат на первом листе, тогда это можно сделать вот так:

import pandas as pd
import os
folder = r'D:\Files\excel' #папка с файлами
files = [os. path.join(folder,f) for f in folder] #формируем список путей к файлам
all_file_frames = [] #сюда будем добавлять прочитанную таблицу 
for f in files:
    print('Reading %s'%f)
    tab = pd.read_excel(f)
    all_file_frames.append(tab)
all_frame = pd.concat(all_file_frames,axis=0) #  axis=0 если нужно добавить таблицу снизу и axis=1 если нужно слева
all_frame.to_excel('final_file.xlsx') #получим файл final_file.xlsx в os.getcwd()

Ответ написан более трёх лет назад

Еще предложу посмотреть на Apache POI poi.apache.org/spreadsheet/index.html , который используют для чтения и генерации excel. Написан на java, есть куча примеров по чтению и сознанию xsl-файлов — poi.apache.org/spreadsheet/examples.html

Проект зрелый, активно развивается и используется в куче программного обеспечения —
poi. apache.org/spreadsheet/how-to.html — вопросы/ответы с кодом и ссылками на примеры.
В свое время, 8 лет назад, использовали его для генерации счетов/формуляров.

PS. А вот и пример как это сделать — www.coderanch.com/t/420958/open-source/Copying-she…
Читать всю ветку.

Ответ написан более трёх лет назад

Комментировать

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  При запуске функция добавит в вашу книгу рабочий лист с именем 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 без потери данных? Примеры и советы. — Объединение строк в Excel — одна из самых распространенных задач, которые нам нужно выполнять время от времени. Проблема в том, что Microsoft Excel не предоставляет для этого надежного инструмента. Кроме…
  Как правильно объединить столбцы в Excel без потери данных. — Объединение данных в Excel — это достаточно распространённая задача. Объединяют столбцы, строки, отдельные ячейки и даже целые таблицы. Из этой статьи вы узнаете, как объединить несколько столбцов Excel и не…
  Как сравнить два столбца на совпадения и различия — На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные…

  Объединить таблицы в Excel

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

  Объединить таблицы в Excel

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

  Как объединить 2 таблицы в Excel?

  Мы предоставили данные клиентов по городам в двух таблицах. Мы взяли для этого 20 записей.

  Лист 1: Таблица 1: Информация о клиенте

  Лист 2: Таблица 2: Сведения о продукте

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

  Ниже приведены шаги для объединения этих двух таблиц:

  • Щелкните любую ячейку в таблице сведений о клиенте. Перейдите на вкладку ВСТАВИТЬ и выберите параметр Таблица в разделе Таблицы. См. Снимок экрана ниже.
  • Затем появится диалоговое окно «Создание таблицы». В нашей таблице «CustomerInfo» есть заголовки столбцов; следовательно, следует установить флажок «Моя таблица содержит заголовки». См. Снимок экрана ниже.
  • Он преобразует наши данные в формат таблицы. Теперь щелкните поле «Имя таблицы» в разделе «Свойства» и укажите имя этой таблицы как «Customer_info. »
  • Выполните те же действия для другой таблицы, «Информация о продукте.» Мы дали название «Продукты» другой таблице. См. Снимок экрана ниже.
  • Щелкните где-нибудь в таблице Customer_Info, затем перейдите на вкладку «Вставка» и выберите параметр «Сводная таблица» в разделе «Таблицы».
  • Появится диалоговое окно для создания сводной таблицы. Установите флажок «Добавьте эти данные в модель данных», как показано на скриншоте ниже.
  • Затем нажмите ОК; он откроет новый лист с новым разделом полей сводной таблицы с правой стороны, как показано на скриншоте ниже.
  • Щелкните вкладку ВСЕ в разделе Поле сводной таблицы, и она отобразит все таблицы, созданные нами. См. Снимок экрана ниже.
  • Теперь нажмите на опцию Relationships в разделе Calculations, как показано на скриншоте ниже.
  • Откроется диалоговое окно для создания связи между этими таблицами. Щелкните по кнопке New. См. Снимок экрана ниже.
  • Он снова откроет диалоговое окно, как показано ниже, и здесь перечислены созданные таблицы.
  • Поскольку есть одно поле «Номер заказа». является общим в обеих таблицах, поэтому мы создадим связь между этими таблицами, используя это общее поле / столбец.
  • Выбрать Customer_Info в разделе «Таблицы» и № заказа. в разделе столбцов. См. Снимок экрана ниже.
  • Выбрать другую таблицу Продукты в разделе «Связанная таблица» и выберите № заказа. в разделе Связанный столбец. См. Снимок экрана ниже.
  • Первичный ключ — это уникальные значения, которые появляются в таблице один раз, затем нажмите ОК. Он отобразит отношения, как показано на скриншоте ниже.
  • Теперь мы можем перетащить поле соответствующим образом, чтобы увидеть результат. Щелкните таблицу Customer_Info, как показано на снимке экрана ниже.
  • Перетащите поля Номер заказа, Имя клиента и город под поле строки.
  • Перетащите поле Возраст под полем Фильтр.
  • Перетащите поле «Продукт» в поле «Столбец» и «Значения» для подсчета продуктов.

  Окончательный результат ниже:

  Соответственно, в соответствии с вашими требованиями вы можете перетаскивать поля.

Как вставить данные из нескольких таблиц Excel в одну таблицу Excel | Small Business

Автор Randall Blackburn Обновлено 12 февраля 2019 г.

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

Создать главную электронную таблицу

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

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

Использовать мастер консолидации данных Excel

1. Откройте первую электронную таблицу

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

3. Создать главную электронную таблицу

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

5. Щелкните первую ячейку

6. Щелкните верхнюю левую ячейку в основной электронной таблице, где будут начинаться данные из первой электронной таблицы.

7. Запуск мастера консолидации данных

8. Перейдите на вкладку «Данные» в меню ленты Excel, а затем нажмите «Консолидировать» в разделе «Инструменты данных». Запустится мастер консолидации данных.

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

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

11. Перейти к первой электронной таблице

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

13. Выберите способ объединения

14. Установите флажок «Верхняя строка», чтобы объединить категории, перечисленные в верхней строке. Щелкните параметр «Левый столбец», если категории перечислены в левой части электронной таблицы в заголовках строк. Чтобы объединить по положению, а не по категории, оставьте флажки «Верхний ряд» и «Левый столбец» пустыми.

15. Выберите желаемое Данные

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

17. Нажмите кнопку «Добавить»

18. Нажмите кнопку «Добавить». Ссылки добавляются в мастер.

19. Добавить больше таблиц

20. Нажмите «Обзор» и перейдите ко второй таблице, если она находится в другой книге. Щелкните лист, а затем нажмите «ОК».

21. Выбрать дополнительные данные

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

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

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

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

Ссылки

• Microsoft: объединение данных из нескольких рабочих листов на одном листе
• AbleBits: объединение данных в Excel и объединение нескольких листов в один рабочий лист

Советы

• Диалоговое окно «Консолидация данных» включает в себя «Создать ссылки на Исходные данные». Если выбран этот параметр, объединенные данные включают ссылки на данные в исходной электронной таблице. Когда исходная электронная таблица обновляется, объединенная электронная таблица автоматически обновляется, подобно сводной таблице Excel.

Предупреждения

• Информация, представленная в этой статье, относится к Microsoft Excel 2010 и 2013. Инструкции могут незначительно или существенно отличаться для других версий Excel.

Writer Bio

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

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

В примерах будет добавлен рабочий лист с именем RDBMergeSheet в вашу книгу и скопирует ячейки

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

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

Вы также можете найти информацию с этой страницы в этой статье на MSDN: Консолидация данных из нескольких рабочих листов в итоговый рабочий лист в Эксель
http://msdn.microsoft.com/en-us/library/cc793964.aspx

 

Скачать пример рабочей книги

Загрузите ZIP-файл с рабочей книгой с кодом с этой страницы для тестирования примеры.
Скачать MergeWorksheetsCode.zip

 

Копировать диапазон каждого листа

Примечание : В этом примере используется функция LastRow
Это Например, скопируйте диапазон A1: G1 с каждого рабочего листа.

Изменить диапазон в эта строка кода

‘Заполните диапазон, который вы хотите скопировать
Установить CopyRng = sh.Range(«A1:G1»)

 Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh как рабочий лист
    Тусклый последний до тех пор, пока
    Dim CopyRng как диапазон
    С приложением
        .
  ScreenUpdating = Ложь
        .EnableEvents = Ложь
    Конец с
    'Удалить лист "RDBMergeSheet", если он существует
    Application.DisplayAlerts = Ложь
    При ошибке Возобновить Далее
    ActiveWorkbook.Worksheets("RDBMergeSheet").Удалить
    При ошибке Перейти к 0
    Application.DisplayAlerts = Истина
    'Добавить рабочий лист с именем "RDBMergeSheet"
    Установите DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"
    'перебрать все рабочие листы и скопировать данные в DestSh
    Для каждого sh в ActiveWorkbook.Worksheets
        Если sh.Name <> DestSh.Name Тогда
            'Находим последнюю строку с данными на DestSh
            Последняя = ПоследняяСтрока(DestSh)
            'Заполните диапазон, который вы хотите скопировать
            Установите CopyRng = sh.Range("A1:G1")
            'Проверить, достаточно ли строк в DestSh для копирования всех данных
            Если Last + CopyRng.Rows.Count > DestSh.Rows.Count, то
                MsgBox "Недостаточно строк в Destsh"
                GoTo ExitTheSub
            Конец, если
            'В этом примере копируются значения/форматы, если вы хотите скопировать только
            'значения или хотите скопировать все, посмотрите на пример ниже этого макроса
            CopyRng.
 
  Copy
            С DestSh.Cells(Last + 1, "A")
                .PasteSpecialxlPasteValues
                .PasteSpecialxlPasteFormats
                Application.CutCopyMode = Ложь
            Конец с
            'Необязательно: имя листа будет скопировано в столбец H.
            DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name
        Конец, если
    Следующий
ExitTheSub:
    Application.Goto DestSh.Cells(1)
    'Автоматическая подгонка ширины столбца на листе DestSh
    DestSh.Columns.AutoFit
    С приложением
        .ScreenUpdating = Истина
        .EnableEvents = Истина
    Конец с
Конец суб 

 

Советы по изменению примеров

В приведенном выше примере я копирую диапазон A1:G1, но вы также можете использовать

Set CopyRng = sh.UsedRange
Чтобы скопировать все ячейки с данными на листе

Set CopyRng = sh.Range(«A1»).CurrentRegion
Чтобы скопировать текущую область ячейки A1
Текущая область представляет собой диапазон, ограниченный любой комбинацией пустых строк и пустой столбец

Set CopyRng = sh. Rows(«1»)
Чтобы скопировать всю строку или строки (тогда используйте «1:8»)

Только копирование значения или все

Замените это:

 CopyRng.Copy
    С DestSh.Cells(Last + 1, "A")
        .PasteSpecialxlPasteValues
        .PasteSpecialxlPasteFormats
        Application.CutCopyMode = Ложь
    Конец с 

с

 с CopyRng
        DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
            .Столбцы.Количество).Значение = .Значение
   End With 

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

Или Замените это:

 CopyRng.Copy
    С DestSh.Cells(Last + 1, "A")
        .PasteSpecialxlPasteValues
        .PasteSpecialxlPasteFormats
        Application.CutCopyMode = Ложь
    End With 

With

 CopyRng.Copy DestSh.Cells(Last + 1, "A") 

Если вы хотите скопировать все

 

Не перебирать все листы книги

Заменить эту строку :
Если sh.Name <> DestSh. Name Тогда
С:
Если LCase(Left(sh.Name, 4)) = «неделя» Тогда
Если вы хотите копировать только листы с именем, которое начать с недели.

Замените эту строку:
Если sh.Name <> DestSh.Name Затем
с:
Если sh.Name <> DestSh.Name And sh.Visible = True Then

Если вы хотите копировать только с видимых листов в вашей книге.

Заменить это строка:
Для каждого sh в ActiveWorkbook.Worksheets
С:
Для каждого ш. ActiveWorkbook.Sheets(Array(«Лист1», «Лист3»))
И удалить это две строки:
Если sh.Name <> DestSh.Name Тогда и End If before Next
Если вы хотите скопировать только из листов, находящихся в массиве.

Замените эту строку:
If sh.Name <> DestSh.Name Then
With:
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, «Total Sheet», «Menu Sheet»), 0)) Then
Если вы хотите исключить больше листов, чем DestSh.

 

Копировать со строки 2 до последней строки с данными

Примечание : В этом примере используется функция LastRow
Примечание : Вы можете использовать приведенные выше советы и в этом примере

В Пример 1 вы можете видеть, что вы можете скопировать все ячейки на листе с помощью этого строка:
Установить CopyRng = sh.

UsedRange

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

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

‘Заполнить начальную строку
StartRow = 2

Если вы хотите скопировать строку заголовка в первую строку RDBMergeSheet затем скопируйте приведенный ниже код, если каждый рабочий лист имеет одинаковые заголовки после этого line : If sh.Name <> DestSh.Name Then

 'Скопировать строку заголовка, изменить диапазон, если вы используете больше столбцов
Если WorksheetFunction.CountA(DestSh.UsedRange) = 0 Тогда
  sh.Range("A1:Z1").Копировать DestSh.Range("A1")
End If 

Пример макроса:

 Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh как рабочий лист
    Тусклый последний до тех пор, пока
    Dim shLast As Long
    Dim CopyRng как диапазон
    Dim StartRow As Long
    С приложением
        . ScreenUpdating = Ложь
        .EnableEvents = Ложь
    Конец с
    'Удалить лист "RDBMergeSheet", если он существует
    Application.DisplayAlerts = Ложь
    При ошибке Возобновить Далее
    ActiveWorkbook.Worksheets("RDBMergeSheet").Удалить
    При ошибке Перейти к 0
    Application.DisplayAlerts = Истина
    'Добавить рабочий лист с именем "RDBMergeSheet"
    Установите DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"
    'Заполнить начальную строку
    Начальная строка = 2
    'перебрать все рабочие листы и скопировать данные в DestSh
    Для каждого sh в ActiveWorkbook.Worksheets
        Если sh.Name <> DestSh.Name Тогда
            'Находим последнюю строку с данными о DestSh и sh
            Последняя = ПоследняяСтрока(DestSh)
            shLast = Последняя строка (sh)
            'Если sh не пуст и если последняя строка >= StartRow, скопируйте CopyRng
            Если shLast > 0 И shLast >= StartRow Тогда
                'Установите диапазон, который вы хотите скопировать
                Установить CopyRng = sh. Range(sh.Rows(StartRow), sh.Rows(shLast))
                'Проверить, достаточно ли строк в DestSh для копирования всех данных
                Если Last + CopyRng.Rows.Count > DestSh.Rows.Count, то
                    MsgBox "Недостаточно строк в Destsh"
                    GoTo ExitTheSub
                Конец, если
                'В этом примере копируются значения/форматы, если вы хотите скопировать только
                'значения или хотите скопировать все, смотрите ниже пример 1 на этой странице
                CopyRng.Copy
                С DestSh.Cells(Last + 1, "A")
                    .PasteSpecialxlPasteValues
                    .PasteSpecialxlPasteFormats
                    Application.CutCopyMode = Ложь
                Конец с
            Конец, если
        Конец, если
    Следующий
ExitTheSub:
    Application.Goto DestSh.Cells(1)
    'Автоматическая подгонка ширины столбца на листе DestSh
    DestSh.Columns.AutoFit
    С приложением
        .ScreenUpdating = Истина
        . EnableEvents = Истина
    Конец с
Конец суб 

 

Скопируйте диапазон/столбец после последнего столбца с данными

Примечание : В этом примере используется функция LastCol

Этот Пример копирования столбца A с каждого листа после последнего столбца с данными на ДестШ. Я использую A:A для копирования всего столбца, но вы также можете использовать диапазон, например А1:А10. Используйте A:C, если вы хотите скопировать больше столбцов.

Измените его здесь

‘Заполните столбцы, которые вы хотите скопировать
Set CopyRng = sh.Range(«A:A»)

Помните, что в Excel 97-2003 есть всего 256 столбцов.
Excel 2007-2013 имеет 16384 столбца.

 Подпрограмма AppendDataAfterLastColumn()
    Dim sh As Worksheet
    Dim DestSh как рабочий лист
    Тусклый последний до тех пор, пока
    Dim CopyRng как диапазон
    С приложением
        .ScreenUpdating = Ложь
        .EnableEvents = Ложь
    Конец с
    'Удалить лист "RDBMergeSheet", если он существует
    Application. DisplayAlerts = Ложь
    При ошибке Возобновить Далее
    ActiveWorkbook.Worksheets("RDBMergeSheet").Удалить
    При ошибке Перейти к 0
    Application.DisplayAlerts = Истина
    'Добавить рабочий лист с именем "RDBMergeSheet"
    Установите DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"
    'перебрать все рабочие листы и скопировать данные в DestSh
    Для каждого sh в ActiveWorkbook.Worksheets
        Если sh.Name <> DestSh.Name Тогда
            'Найти последнюю колонку с данными на DestSh
            Последний = LastCol(DestSh)
            'Заполните столбцы, которые вы хотите скопировать
            Установите CopyRng = sh.Range("A:A")
            'Проверить, достаточно ли строк в DestSh для копирования всех данных
            Если Последний + CopyRng.Columns.Count > DestSh.Columns.Count Тогда
                MsgBox "Недостаточно столбцов в Destsh"
                GoTo ExitTheSub
            Конец, если
            'В этом примере копируются значения/форматы и ширина столбца
            CopyRng. Copy
            С DestSh.Cells(1, Last + 1)
                .PasteSpecial 8 'Ширина столбца
                .PasteSpecialxlPasteValues
                .PasteSpecialxlPasteFormats
                Application.CutCopyMode = Ложь
            Конец с
        Конец, если
    Следующий
ExitTheSub:
    Application.Goto DestSh.Cells(1)
    С приложением
        .ScreenUpdating = Истина
        .EnableEvents = Истина
    Конец с
Конец суб 

 

Общие функции, необходимые для всех подпрограмм

 Функция LastRow(sh As Worksheet)
 При ошибке Возобновить Далее
 LastRow = sh.Cells.Find(What:="*", _
 После:=sh.Range("A1"), _
 Посмотрите: = xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False).Строка
 При ошибке Перейти к 0
Конечная функция
Функция LastCol (sh как рабочий лист)
 При ошибке Возобновить Далее
 LastCol = sh.