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

Инструкция

Устанавливаем себе надстройку ЁXCEL (PRO). Читаем справку.

Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку "Таблицы", в выпавшем списке выбираем команду "Объединить таблицы":

В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем "ОК":

Программа сформирует запрос - объединит таблицы и выведет информационное сообщение:

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

В открывшемся диалоговом окне выбираем "Подключения в этой книге" - "Запрос из Excel Files" и нажимаем "Открыть":

В открывшемся диалоговом окне устанавливаем переключатели в положения "Таблица" и "Имеющийся лист", нажимаем "ОК":

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

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

kak-ob-edinit-dve-tablitsy-i-bolee-v-odnu_2.gif

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

kak-ob-edinit-dve-tablitsy-i-bolee-v-odnu_3.gif

В итоговой таблице появятся строчки, добавленные в выбранный вами лист.

Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.RefreshAll
End Sub

Важно:

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

Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль "ЭтаКнига" следующий код:

Private Sub Workbook_Open()
    Dim q As String
    On Error Resume Next
    q = Application.ThisWorkbook.Path & "\" & Application.ThisWorkbook.Name 'Определяем текущий путь к файлу
    With ActiveWorkbook.Connections("Запрос из Excel Files").ODBCConnection 'Имя запроса
        .Connection = "ODBC;DSN=Excel Files;DBQ=" & q & _
        ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"  'Меняем строку подключения
    End With
End Sub

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

Файлы для скачивания:
ФайлОписаниеРазмер файла:Скачивания
Скачать этот файл (P_Hitrosti.zip)Пример 21 Кб632

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

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

  • В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: "В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов." Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
  • Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
  • В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение: преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос - текстовые данные появятся.

Комментарии  

# света 03.12.2016 16:44
Добрый день.
имеются две таблицы в разных файлах (или на разных листах) . Необходимо слить в одну обе эти табл. В обеих табл есть уникальный код по которому их можно сцепить. Но проблема, что в одной из таблиц таких одинаковых кодов может быть несколько и надо, чтобы при объединении таблиц например в таблицу с одни кодом подтянулись несколько аналогичных кодов со второй таблицей не суммируя их, а просто путем добавления строк вниз.
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 03.12.2016 18:33
Светлана, из Вашего описания мало что понятно. Киньте мне пример на почту. Почту можно найти в контактах.
Ответить | Ответить с цитатой | Цитировать
# Ольга2016 21.09.2016 20:43
Добрый день.Не могу слить два файла выдает ошибку,не совпадает число столбцов.Оба файла заканчиваются столбцом Y.
Ответить | Ответить с цитатой | Цитировать
# Сергей Хвостов 22.09.2016 10:15
В меню сайта "Контакты". Кликните внизу по имени.
Ответить | Ответить с цитатой | Цитировать
# Ольга2016 22.09.2016 10:35
отправила
Ответить | Ответить с цитатой | Цитировать
# Ольга2016 22.09.2016 10:05
Сделала, все согласно рекомендаций,но ошибка осталась.Точное описание ошибки: Mikrosoft(Драйв ер ODBC Excel)В таблицах или запросах,выбран ных в запросе на объединение, не совпадает число столбцов.
На какой адрес могу выслать файл?
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 21.09.2016 22:48
Ольга, на каком-то из листов есть пустые столбцы за пределами таблиц. Или есть данные, возможно написанные белым цветом. Попробуйте воспользоваться командами из вкладки листы - Удалить все пустые столбцы. После повторите операцию. Если не получиться скиньте мне файл по почте, я посмотрю что там не так.
Ответить | Ответить с цитатой | Цитировать
# Эмилия 30.07.2016 18:36
Здравствуйте! А как суммировать данные из 2-х и более таблиц (на разных листах) в одной, сводной?
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 30.07.2016 18:41
Почитайте вот эту статью: https://e-xcel.ru/index.php/svodnye-tablitsy/kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam
Ответить | Ответить с цитатой | Цитировать
# Маришка 06.07.2016 12:00
Что делать, если строчка "объединить таблицы" не активна.... или почему она не активна?
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 06.07.2016 14:08
Данная команда доступна только в версии PRO.
Ответить | Ответить с цитатой | Цитировать
# Маришка 06.07.2016 14:14
спасибо
Ответить | Ответить с цитатой | Цитировать
# gus010 02.05.2016 19:08
И снова здравствуйте!
В дополнение к предыдущему вопросу хочется присовокупить ещё один-возможно ли объединение таблиц из разных книг (несколько филиалов заполняют одинаковые таблицы с несколькими листами)? Которые впоследствии необходимо объединить в одну таблицу.
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 04.05.2016 08:57
Да это возможно, если предварительно воспользоваться командой: https://e-xcel.ru/index.php/komandy/listy/vstavit-listy-iz-drugoj-knigi
и "перетащить" все листы в одну книгу.
Ответить | Ответить с цитатой | Цитировать
# Cogito 16.03.2016 18:33
Добрый день.
Поле "объединить таблицы" не активно.
С чем это может быть связано?
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 16.03.2016 19:32
Данная команда доступна только в версии PRO.
Ответить | Ответить с цитатой | Цитировать

Добавить комментарий

Защитный код
Обновить