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

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

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

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

Как построить сводную таблицу по нескольким массивам (листам)? 

Важно:

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

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

kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_2.png

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

kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_3.png

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

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

kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_4.png

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

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

kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_5.png

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

kak-postroit-svodnuyu-tablitsu-po-neskolkim-massivam-listam_6.png

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

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

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_STab_05.zip)Пример 27 Кб434

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

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

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

Комментарии  

# Ксения 16.09.2015 21:22
Здравствуйте, вставила в книгу Ваши коды макросов для обновления сводной таблицы, и у меня возникли проблемы. Первая: таблица не обновлялась (из-за чего, не знаю). Я добавила лишнюю строку для проверки в исходную таблицу, но в сводной она не отобразилась, поэтому я залезла в параметры Excel и включила надстройку "VBA для помощника по Интернету" (случайно, конечно, и не знаю, как ее отключить).
И вот тогда возникла вторая проблема. Теперь таблица обновляется при каждом действии в данной книге (фильтр по дате, удаление, вставка строк, столбцов, ячеек и т.д.) Сейчас это занимает всего 2-3 минуты, но боюсь в будущем это будет происходить дольше из-за увеличения объема данных. Можете помочь?
Ответить | Ответить с цитатой | Цитировать
# Сергей Хвостов 17.09.2015 11:11
Ксения, добрый день! Попробуйте сделать так:
1. Отключите надстройку "VBA для помощника по Интернету", как описано в статье http://e-xcel.ru/index.php/makrosy/kak-podklyuchat-nadstrojki-k-ms-excel
2. Удалите весь VBA код из всех модулей листов (если у вас нет никакого другого кода кроме моего, можно просто сохранить файл как файл без поддержки макросов в формате .xlsx)
3. Если у вас очень большой массив данных - лучше не нужно использовать макрос для обновления, обновляйтесь в ручном режиме.
Ответить | Ответить с цитатой | Цитировать
# Ксения 17.09.2015 15:51
Сергей, здравствуйте, все сделала, стало просто отлично. Огромное спасибо.
Может Вы также оперативно сможете помочь мне с еще одной проблемой: мне нужно создать сводную с нескольких таблиц из разных (неопределенное количество)лист ов книги. Мастер создания сводных таблиц не помогает, получается корявая таблица, вариант, что Вы описываете в статье, тоже не получается. У меня стоит Excel 2010 (у меня нет таких кнопок). Где-то читала, что есть специальный макрос для этого, и очень надеюсь, что у Вас в какой-нибудь статье он указан. Заранее огромное спасибо.
Ответить | Ответить с цитатой | Цитировать
# Сергей Хвостов 17.09.2015 16:04
Ксения, данная статья как раз описывает решение вашей задачи и в MS Excel 2010 отлично должна работать. Каких кнопок конкретно у вас в MS Excel 2010 нет?
Ответить | Ответить с цитатой | Цитировать
# Елена Семчук 16.10.2014 18:53
Есть ли какие-то секреты или особенности для выведения расчетных показателей в сводной таблице, построенной по нескольким листам?
не пойму почему - не получается сделать вычисления.
Ничего нестандартного -одно поле поделить на другое и отнять третье поле деленное на четвертое.
почему-то выводит нули вместо значений.
Ответить | Ответить с цитатой | Цитировать
# Сергей Хвостов 17.10.2014 11:31
Елена, добрый день!
Да нет каких-то особых секретов, я делаю все как обычно - поля вычисляются. Можете кинуть мне файл свой на электронку я посмотрю, может чем помогу.
Ответить | Ответить с цитатой | Цитировать
# Елена Семчук 18.02.2015 01:32
Спасибо, разобрплась. Пользуюсь надстройкой в полный рост:) Спасибо огромное!
Ответить | Ответить с цитатой | Цитировать

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

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