Как ни странно, но начиная с Excel 2007 пропала очевидная возможность создания сводной таблицы из нескольких листов. В Excel 2003 это можно было сделать во время создания простой сводной (Меню- Данные- Отчет сводной таблицы и диаграммы), выбрав в меню "Из нескольких диапазонов консолидации". Хоть на офф.сайте Microsoft для версии Excel 2007 и выше и предлагают сделать это при помощи клавиш:
- Excel 2010-2021- Файл
(File) - Параметры(Options) - Панель быстрого доступа(Quick Access Toolbar) - Excel 2007- Кнопка офис- Параметры Excel
(Excel options) - Панель быстрого доступа(Quick Access Toolbar)
или непосредственно с панели быстрого доступа:
В списке "Выбрать команды из:" выбираем
Забегая вперед скажу, что это совсем не та сводная, что на основе одного листа: нет толковых и понятных заголовков столбцов; набор функций ограничен; да и вообще функционал скуден по сравнению с обычной сводной(например, нет группировки дат и значений). Может быть поэтому в Microsoft посчитали правильным убрать данный пункт с глаз, чтобы не вводить в заблуждение.
Но тем не менее, несмотря на все недочеты, для кого-то возможность создания подобных таблиц может оказаться очень даже полезной. Например, если необходимо проанализировать данные по продажам за несколько лет и каждый год на отдельном листе или в отдельном диапазоне, как на рисунке ниже:
Жмем кнопку
На
Далее на
Далее мы поймем зачем нам это и как использовать.
Количество полей страниц указываем 1
Лист3!$A$2:$N$6 - указываем 2013 годЛист3!$A$8:$N$11 - указываем 2012 годЛист3!$A$13:$N$17 - указываем 2011 год
Именно для этого мы и выбирали на втором шаге пункт с созданием своих полей страниц - чтобы можно было задать понятное имя каждому диапазону. Далее мы увидим зачем понятные имена и что будет, если на втором шаге выбрать другой пункт. И хоть создать можно будет не более 4-х полей страниц - как правило для большинства задач и одного хватает "за глаза", т.к. нужно это обычно лишь для того, чтобы задать понятные наименования для полей страницы.
Жмем
Получаем более-менее привычный вид сводной таблицы:
С одной стороны мы вроде как работаем теперь со сводной таблицей, а с другой - есть нюансы, на которые стоит обратить внимание:
- На рисунке выше столбец
Менеджер расположен в области значений и нет возможности перенести его в область строк или столбцов. Можно лишь убрать его из отображения, выключив в фильтре поля Столбец. Все дело в том, что сводные таблицы, созданные на основе нескольких диапазонов консолидации, содержат только три базовых области: Строка, Столбец и Значение. ПолеСтрока всегда создается на основе первого столбца указанного диапазона. ПолеСтолбец содержит все, что расположено правее первого столбца и комбинирует столбцы всех диапазонов в одно поле заголовков. Все, что правее области строк и ниже области столбцов - область значений. - Т.к. столбец
Менеджер состоит из текстовых данных - все поля значений нашей сводной по умолчанию будут отображать данные по количеству строк, а не суммы. Это легко изменить, поменяв функцию поля на Сумма(правая кнопка мыши в любой ячейке поля значений -Итоги по -Сумма). Однако после этого столбецМенеджер будет содержать нули и отобразить там конкретные фамилии менеджеров не получится никак потому, что этот столбец трактуется сводной изначально как значения и не может содержать текст. Это уже куда более печально, чем функция по умолчанию.
Эти моменты следует учитывать при подготовке данных для построения сводной таблицы на основе нескольких диапазонов консолидации.
Так для чего же мы давали понятные имена полям?
Скрины ниже продемонстрируют это более чем наглядно. Если перейти в поле фильтра и раскрыть его - там мы увидим как раз эти самые поля:
Имена элементов соответствуют тем значениям, которые мы задали на третьем шаге:
Если бы на втором шаге мы выбрали
Согласитесь, не очень информативно в нашем случае.
Кстати, сами поля страниц можно использовать для более гибкого анализа. Например, переместить их в область строк для анализа данных наглядно по годам:
В подобных случаях как раз и может понадобиться добавление более одного поля в область страниц.
Сами заголовки Страница1, Строка и Столбец можно переименовать в более удобные и понятные названия. Для этого необходимо лишь выделить ячейку с названием элемента и вписать нужный текст через строку формул.
Подводя итог я бы сказал, что совершенно не напрасно этот пункт был убран из основного меню создания сводных. Т.к. полноценной сводной это назвать нельзя даже с натяжкой. И полноценный анализ при помощи данной таблицы сделать вряд ли удастся. Но тем не менее возможность создания такой сводной все же лучше, чем ничего. Ведь сводную можно простым кликом мыши обновлять при внесении изменений в исходные данные - а это уже куда лучше, чем при каждом изменении собирать все заново.
Хотя я сам обычно советую сначала применить некий код (вроде того, что описан в статье Как собрать данные с нескольких листов или книг?), после чего на основе собранных данных создать нормальную сводную таблицу со всеми её возможностями и полным функционалом. Или действовать через базы данных(особенно, если данных очень много): Сводная из базы данных Access
Но есть и более экзотический метод создания сводной из нескольких листов - через подключения. Создается подключение на языке запросов SQL, запрос подсовываем в кэш сводной - и готово, у нас есть сводная из нескольких листов со всем её функционалом.
'--------------------------------------------------------------------------------------- ' Module : mPTFromMultipleSheets ' DateTime : 07.08.2014 21:43 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Процедура создания сводной таблицы из нескольких листов ' http://www.excel-vba.ru/chto-umeet-excel/svodnaya-tablica-iz-neskolkix-listov/ '--------------------------------------------------------------------------------------- Option Explicit Sub PTFromMultipleSheets() Dim oPTCache As PivotCache, oPT As PivotTable Dim sPath As String, sWbFulName As String, sTmpFileName As String Dim avSheets Dim sCols As String, sQuery As String, sCon As String Dim rRes As Range Dim li As Long sPath = ThisWorkbook.Path sWbFulName = ThisWorkbook.FullName If Right(sPath, 1) <> "\" Then sPath = sPath & "\" sTmpFileName = sPath & "TempWbForDB_" & Format(Now, "yyyymmddhhmmss") & ".xls" 'сюда можно добавить еще листы avSheets = Array("План", "Факт") 'например: Array("Январь", "Февраль", "Март", "Апрель") 'ниже перечисляются заголовки столбцов, на основе которых строится сводная 'столбцы могут быть в разном порядке, но иметь одинаковые заголовки sCols = "[Отделение],[Статья Расходов],[Сумма]" 'sCols = "*" ' - если необходимо включить все столбцы 'при этом шапка на всех листах должна быть полностью одинаковая, кол-во столбцов одинаковое 'данные будут в том порядке, в котором расположены столбцы Application.ScreenUpdating = False If Val(Application.Version) > 11 Then DelCon Set rRes = ThisWorkbook.Sheets(1).Cells rRes.Clear ThisWorkbook.Worksheets(avSheets).Copy With ActiveWorkbook .SaveAs sTmpFileName .Close End With 'создаем строку запроса For li = LBound(avSheets) To UBound(avSheets) If li > 0 Then sQuery = sQuery & " UNION SELECT " & sCols & " FROM [" & avSheets(li) & "$]" Else sQuery = "SELECT " & sCols & " FROM [" & avSheets(li) & "$]" End If Next li 'сначала создаем подключение к временному файлу 'это поможет избежать ошибок подключения к открытому файлу sCon = _ "ODBC;DSN=Excel Files;DBQ=" & sTmpFileName & ";" & _ "DefaultDir=" & sPath & ";DriverId=790;" & _ "MaxBufferSize=2048;PageTimeout=5" Set oPTCache = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal) With oPTCache .Connection = sCon .CommandType = xlCmdSql .CommandText = sQuery Set oPT = .CreatePivotTable(rRes(3, 1)) End With 'теперь изменяем в запросе сводной путь к файлу на текущий sCon = _ "ODBC;DSN=Excel Files;DBQ=" & sWbFulName & ";" & _ "DefaultDir=" & sPath & ";DriverId=790;" & _ "MaxBufferSize=2048;PageTimeout=5" ThisWorkbook.PivotCaches(1).Connection = sCon With oPT 'выставляем первоначальные настройки для сводной With .PivotFields(1) .Orientation = xlRowField .Position = 1 End With With .PivotFields(2) .Orientation = xlRowField .Position = 2 End With .AddDataField .PivotFields("Сумма"), "Сумма по полю Сумма", xlSum End With 'удаляем временный файл Kill sTmpFileName Set oPT = Nothing: Set oPTCache = Nothing Application.ScreenUpdating = True End Sub '--------------------------------------------------------------------------------------- ' Procedure : DelCon ' Purpose : Процедура удаляет подключения ' Требуется только для версий, выше 2003 '--------------------------------------------------------------------------------------- Private Sub DelCon() On Error Resume Next: ThisWorkbook.Connections(1).Delete: On Error GoTo 0 End Sub |
Я специально привел пример именно с ODBC. Если строить на основе ADO(в сводную можно подставить результат запроса через ADO), то сводная хоть и будет полноценной, но при любом изменении в источнике данных придется перестраивать сводную заново. Что не очень удобно, если данных много и уже свою структуру сводной построили.
А такая сводная(через ODBC) имеет прямую связь с источником данных, т.к. подключение хранится в кэше сводной. Поэтому если данные на листах изменятся - надо будет лишь стандартно обновить сводную:
- выделить любую ячейку сводной таблицы →Правая кнопка мыши →
Обновить (Refresh) - или вкладка
Данные →(Data) Обновить все →(Refresh all) Обновить .(Refresh)
Пара важных замечаний(ложек дегтя, если угодно):
- таблицы на листах должны иметь все перечисленные в запросе столбцы. Столбцы на листах могут располагаться в разном порядке, но иметь одинаковые заголовки. Если на одном листе заголовок столбца называется "Сумма", а на другом "Sum" - будет ошибка. Вы можете сами регулировать порядок столбцов и их кол-во, перечисляя их в запросе. За это отвечает строка:
sCols = "[Отделение],[Статья Расходов],[Сумма]"
Если в одном из листов будет отсутствовать какой-либо столбец - получите ошибку.
Если указать
sCols = "*"
То в сводную будут включены все столбцы таблиц на листах, что избавляет от процедуры их перечисления. Но в данном случае есть свои нюансы: все таблицы на листах могут иметь различное кол-во строк, но столбцы должны быть строго одинаковые - по кол-ву и по порядку. Иными словами шапка должна быть полностью идентична на всех листах. Если на каком-то листе будет лишний столбец - получите ошибку. Если на каком-то листе будет отсутствовать столбец - получите ошибку. Если имена столбцов где-то различаются - получите ошибку. - заголовки не должны содержать запятых, точек и прочих знаков препинания. Лучше использовать исключительно пробел и нижнее подчеркивание. Так же лучше давать короткие имена заголовков.
- лучше заголовки располагать в первой строке. Так же, как и для стандартной сводной - не должно быть объединенных ячеек.
- на листах не должно быть пустых строк и столбцов. Что имеется ввиду: если на листе нажать
Ctrl +End , то выделяется последняя заполненная ячейка листа. По-хорошему это должна быть последняя ячейка таблицы данных. Если при нажатии наCtrl +End выделяется пустая ячейка правее или(и) ниже таблицы данных - следует удалить эти пустые столбцы справа и строки снизу и сохранить файл. - На листах не должно быть лишних таблиц. На одном листе должна быть только одна таблица.
- Лист для создания сводной таблицы должен находиться на первом листе. Если есть желание расположить его на каком-то другом, то надо в строке кода:
Set rRes = ThisWorkbook.Sheets(1).Cells
заменить 1 на номер листа для расположения сводной таблицы.
Количество строк в таблицах значения не имеет - оно может быть разным.
В прикрепленном файле я добавил процедуру обновления кэша без построения сводной заново. Может пригодиться, если подключение по каким-то причинам сбилось.
Tips_PT_PTFromMultipleSheets.xls (54,0 КиБ, 6 793 скачиваний)
На данный момент для пользователей Excel 2010 и новее есть еще одна возможность сбора данных с нескольких листов и файлов - при помощи бесплатной надстройки PowerQuery. Как это сделать можно почитать и посмотреть в статье: Собрать и просуммировать данные из разных файлов при помощи PowerQuery
Так же см.:
План-фактный анализ в Excel при помощи Power Query
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Собрать и просуммировать данные из разных файлов при помощи PowerQuery
Да, вспомнила,что так можно,спасибо. Сейчас не могу добавить два поля и сделать подсуммирование по полям Сумма и Кол-во,тн.
'выставляем первоначальные настройки для сводной
With .PivotFields(1)
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields(2)
.Orientation = xlRowField
.Position = 2
End With
.AddDataField .PivotFields("Коэф-т"), "Среднее по полю Коэф-т", xlAverage
End With
Что такое подсуммирование не знаю. Но судя по коду Вы только один раз добавляете поле: .AddDataField
А как тогда нужно поправить/добавить код,чтобы дополнительно вывести поля?
Есть проблема в том, что если есть полностью одинаковая строка, то она не подтянется в сводную, так как такое свойство Select. Но можно создать доп столбец с цифрами от 1 до бесконечности, тогда каждая строка будет уникальная. Автор, можно диапазон определять Диспечером имен - это бы избавило от проблемы в определении диапазона когда есть пустые значения или строки.
Коллеги, помогите изменить код так, что бы можно было работать с Диспетчером имен, так как у меня часть столбцов могут быть пустыми и диапазон определяется не правильно
Добрый день!
Очень нужная обработка, но столкнулся с проблемой.
"Поле исходных данных содержит больше уникальных элементов, чем может быть использовано в отчете сводной таблицы"
Прошу помочь!
P.s При сохранения всех листов в один массив, сводная Excel справляется без проблем!
Здравствуйте, выдает ошибку тут
ThisWorkbook.Worksheets(avSheets).Copy
все имена правильно ввела, даже скопировала специально. в чем проблема?
Поняла проблему, нужно первый лист создать, теперь к сожалению выдает все правильно только вот сумму считает везде нулями, вся талица из формул из других книг проблема в этом?
Катерина, я на вскидку могу только одну причину назвать: числа не являются числами и записаны как текст. В этом случае сводная не сможет подвести сумму таких "чисел" - может только подсчитать их кол-во.
Добрый день.
Помогите, подскажите, пожалуйста, чайнику в VBA: добавил модуль, скопировал макрос, заменил в Array (...) свои названия листов. шапку проверил и даже закопипастил, чтобы не ошибиться в символах, пустые ячейки (вправои вниз) удалил. Не срабатывает макрос все равно. При запуске выделяет синим DelCon (в этой If Val(Application.Version) > 11 Then DelCon строке). и вверху строка с названием макроса закрашивается желтым. Что у меня не так? Что я делаю неправильно?
Алексей, скорее всего код скопировали не полностью. Не скопировали процедуру DelCon. Сравните код статьи(ВЕСЬ) и свой код.
А если бы Вы привели текст ошибки - я мог бы ответить точнее. А так только догадки...
Точно (знаю же что должно заканчиваться End Sub...).
Добавил остаток кода, запустил. Тоже самое. Ошибка-Compile Error: Sub or Function not defined.
Это означает, что функция DelCon НЕ НАЙДЕНА. Посмотрите внимательно на весь код. Не просто End Sub - у Вас целой функции не хватает. Последние три строки кода.
Спасибо большое. Получилось.
Добрый день. Все получилось. Спасибо. Можете подсказать, как сделать такой же пример с исходными данными в другой книге?
Добрый день! Пробую подогнать ваш макрос под свои нужды, на что обратила внимание:
Если на листах есть идентичные строки (например, в этом макросе Лист "Факт" отделение3: пиво = 100,водка = 200,сигареты = 300, Лист "План" отделение3: пиво = 100,водка = 200,сигареты = 300), то макрос их не суммирует, а считает только один раз (например, в этом макросе получается Итого отделение3: пиво = 100,водка = 200,сигареты = 300, а должно быть пиво = 200,водка = 400, сигареты = 600)
Подскажите пожалуйста с чем это может быть связано? (У меня Excel 2016)
Как оказалось, дублированные строки не считаются и если находятся на одном листе
Добрый день!
1.А можно ли на основе этого кода брать данные из сводных таблиц и нужно ли чтобы заголовок был обязательно в первой строке?
2. Выдает ошибку 1004, не возможно получить PivotFields класса PivotTable при этом подствечивает:.AddDataField .PivotFields("Cust_Recursive_Stocks amount"), "Сумма по Cust_Recursive_Stocks amount", xlSum
Как ее устранить ?