Типичная задача при обработке информации полученной из разных источников. Типовое решение - взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

Однако решение существует! И оно не очень сложное.

Для решения этой задачи нам понадобиться надстройка ЁXCEL.

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

Важно:

• Количество столбцов во всех таблицах должно быть одинаково;
• Кроме таблиц на листах не должно быть никакой информации.

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

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

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

Осталось совсем немного. Переходим в главном меню во вкладку "Данные" в разделе "Получение внешних данных" нажимаем кнопку "Существующие подключения":

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

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

Программа создаст в книге новый лист на который выведет макет сводной таблицы. Обратите внимание - программа создаст в сформированной таблице новый столбец с названиями листов из которых были получены данные:

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

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

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

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример		27 Кб	2209

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

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

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