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

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

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

Итак, приступим к решению. Мы имеем на входе 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 Кб554

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

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

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

Комментарии  

# Елена123 13.07.2017 10:57
Сергей, у меня в таблице 70 столбов. некоторые из них пустые (будут заполнены в 2018 г.-ежеквартальн ые данные по годам. ).Сейчас я в таблице просто проставила нули. Может из-за этого не сводить? Функция удалить пустые строки не работает, пишет-недостато чно памяти.
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 13.07.2017 11:14
Так сложно сказать. Надо на таблицы посмотреть. Киньте мне на почту.
Ответить | Ответить с цитатой | Цитировать
# LANSERENOK 07.07.2017 05:21
Здравствуйте, Сергей.
Возможно ли с помощью Вашей надстройки преобразовывать в массив оборотные ведомости из С1.
Например если в столбце два значения.

ФИО начисления. сумма
Подразделение

Иванов оклад. 1000
Кадры
Иванов. Премия. 500
Кадры

Петров. Оклад. 2000
Продажи.
Петров. Премия. 100
Продажи

Надо получить
ФИО. Подразделение оклад. Премия
Иванов кадры. 1000. 500
Петров продажи. 2000. 100

Заранее благодарна за ответ
Светлана
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 07.07.2017 07:16
Светлана, да это возможно, для этого есть специальная команда. Посмотрите эту статью: https://e-xcel.ru/index.php/svodnye-tablitsy/kak-bystro-postroit-svodnuyu-tablitsu-iz-otcheta-1c-ili-sap
Ответить | Ответить с цитатой | Цитировать
# Хвостов Сергей 07.07.2017 07:33
Вот справка по этой команде: https://e-xcel.ru/index.php/komandy/tablitsy/transformirovat-tsvetnuyu-tablitsu-v-massiv
Ответить | Ответить с цитатой | Цитировать
# Ксения 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
Спасибо, разобрплась. Пользуюсь надстройкой в полный рост:) Спасибо огромное!
Ответить | Ответить с цитатой | Цитировать

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

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