Забыли пароль?


Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (16)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Сводная таблица из нескольких листов

Как ни странно, но начиная с Excel 2007 пропала очевидная возможность создания сводной таблицы из нескольких листов. В Excel 2003 это можно было сделать во время создания простой сводной (Меню- Данные- Отчет сводной таблицы и диаграммы), выбрав в меню "Из нескольких диапазонов консолидации". Хоть на офф.сайте Microsoft для версии Excel 2007 и выше и предлагают сделать это при помощи клавиш: ALT+D+P у меня данное сочетание не вызвало никакой реакции со стороны самого Excel ни на русской раскладке, ни на английской. Скорее всего локализация ОС и Офиса так же имеет значение. Но я не стал далее ничего пытаться делать, т.к. знаю более короткий путь, чем изменение локализации. Чтобы создавать сводную из нескольких диапазонов для начала необходимо добавить кнопку на панель быстрого доступа:

  • Excel 2010- Файл (File)- Параметры (Options)- Панель быстрого доступа (Quick Access Toolbar)
  • Excel 2007- Кнопка офис- Параметры Excel (Excel options)- Панель быстрого доступа (Quick Access Toolbar)

или непосредственно с панели быстрого доступа:

Выбрать команды из: Все команды (All Commands). Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:

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


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

Жмем кнопочку, которую мы так старательно создали на панели быстрого доступа.
На Первом шаге необходимо указать в нескольких диапазонах консолидации (Multiple consolidation ranges)

Далее на Втором шаге Excel предложит создать одно поле страницы или создать свои поля страниц. Т.к. Excel как правило создает весьма малопонятные наименования для полей страниц по умолчанию, лучше выбрать второй пункт - Создать поля страницы (I will create the page fields).

Правда, создать можно будет не более 4-х полей страниц, но как правило для большинства задач этого вполне хватает. К тому же нам это потребуется лишь для того, чтобы задать понятные наименования для полей страницы. А уж для этого 1-го поля за глаза хватит.

Третьим шагом необходимо будет указать все диапазоны, из которых планируется создать сводную (диапазоны могут быть как на одном листе, так и на разных листах и даже в разных книгах):

Количество полей страниц указываем 1 (How mane page fields do you want?). Становится активным окно для указания имени поля (Field one). Даем имена для полей, поочередно выделяя их в списке диапазонов:
Лист3!$A$2:$N$6 - указываем 2013 год;Лист3!$A$8:$N$11 - указываем 2012 год;Лист3!$A$13:$N$17 - указываем 2011 год.

Жмем Далее (Next), выбираем место создания сводной таблицы(существующий или новый лист), Готово (Finish).
Получаем более-менее привычный вид сводной таблицы:

Я бы выделил две небольшие проблемы:

  1. Например на рисунке выше столбец Менеджер расположен в области значений и нет возможности перенести его в область строк или столбцов. Можно лишь убрать его из отображения, выключив в фильтре поля Столбец. Все дело в том, что сводные таблицы, созданные на основе нескольких диапазонов консолидации, содержат только три базовых поля: Строка, Столбец и Значение. Поле Строка всегда создается на основе первого столбца указанного диапазона и может содержать только одну область. Поле Столбец содержит все, что расположено правее первого столбца и комбинирует столбцы всех диапазонов в одно поле заголовков.
  2. Тот же столбец Менеджер содержит значения по количеству, но не суммы, т.к. это текстовые поля. Именно из-за этого значения для остальных столбцов тоже отражены не суммами, а количеством. Это легко изменить, поменяв функцию поля на Сумма. В этом случае столбец Менеджер будет содержать нули. Но главная особенность в том, что этот столбец трактуется сводной изначально как значения и не может содержать текст. А это значит, что нет никакой возможности узнать значения из таблицы исходных данных.

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

А на рисунке ниже показано для чего мы давали имена полям:

Имена элементов соответствуют тем значениям, которые мы задали на третьем шаге: 2011 год, 2012 год, 2013 год.
Если бы на втором шаге мы выбрали Создать одно поле страницы (Create a single page field for me), то получили бы малопонятные названия в фильтре Страниц (Объект1(Item1), Объект2(Item2), Объект3(Item3)):

Сами же поля страниц можно использовать для более гибкого анализа. Например, поле Страница1 (Page1) может быть перемещено в область строк для анализа данных наглядно по годам:

В подобных случаях как раз и может понадобиться добавление более одного поля в область страниц.
Сами заголовки Страница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).

Пара важных замечаний(ложек дегтя, если угодно):

  1. таблицы на листах должны иметь все перечисленные в запросе столбцы. Столбцы на листах могут располагаться в разном порядке, но иметь одинаковые заголовки. Если на одном листе заголовок столбца называется "Сумма", а на другом "Sum" - будет ошибка. Вы можете сами регулировать порядок столбцов и их кол-во, перечисляя их в запросе. За это отвечает строка:
    sCols = "[Отделение],[Статья Расходов],[Сумма]"
    Если в одном из листов будет отсутствовать какой-либо столбец - получите ошибку.
    Если указать
    sCols = "*"
    То в сводную будут включены все столбцы таблиц на листах, что избавляет от процедуры их перечисления. Но в данном случае есть свои нюансы: все таблицы на листах могут иметь различное кол-во строк, но столбцы должны быть строго одинаковые - по кол-ву и по порядку. Иными словами шапка должна быть полностью идентична на всех листах. Если на каком-то листе будет лишний столбец - получите ошибку. Если на каком-то листе будет отсутствовать столбец - получите ошибку. Если имена столбцов где-то различаются - получите ошибку.
  2. заголовки не должны содержать запятых, точек и прочих знаков препинания. Лучше использовать исключительно пробел и нижнее подчеркивание. Так же лучше давать короткие имена заголовков.
  3. лучше заголовки располагать в первой строке. Так же, как и для стандартной сводной - не должно быть объединенных ячеек.
  4. на листах не должно быть пустых строк и столбцов. Что имеется ввиду: если на листе нажать Ctrl+End, то выделяется последняя заполненная ячейка листа. По-хорошему это должна быть последняя ячейка таблицы данных. Если при нажатии на Ctrl+End выделяется пустая ячейка правее или(и) ниже таблицы данных - следует удалить эти пустые столбцы справа и строки снизу и сохранить файл.
  5. На листах не должно быть лишних таблиц. На одном листе должна быть только одна таблица.
  6. Лист для создания сводной таблицы должен находиться на первом листе. Если есть желание расположить его на каком-то другом, то надо в строке кода:
    Set rRes = ThisWorkbook.Sheets(1).Cells
    заменить 1 на номер листа для расположения сводной таблицы.

Количество строк в таблицах значения не имеет - оно может быть разным.
В прикрепленном файле я добавил процедуру обновления кэша без построения сводной заново. Может пригодиться, если подключение по каким-то причинам сбилось.
Скачать пример:

  Tips_PT_PTFromMultipleSheets.xls (54,0 KiB, 5 323 скачиваний)


Так же на данный момент есть еще одна возможность сбора данных с нескольких листов и файлов - при помощи бесплатной надстройки PowerQuery. Как это сделать можно почитать и посмотреть в статье: Собрать и просуммировать данные из разных файлов при помощи PowerQuery

Так же см.:
План-фактный анализ в Excel при помощи Power Query
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Собрать и просуммировать данные из разных файлов при помощи PowerQuery


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 46 комментариев
  1. Павел:

    По поводу Alt + D + P.

    Нажимать следует Alt + D, отпустить, затем только P.

    От локализации это не зависит.

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

  3. Вадим:

    При работе макроса выдает ошибку "Слишком мало параметров. Требуется 3" на строке "Set oPT = .CreatePivotTable(rRes(3, 1))". С чем это может быть связано? (версия офиса 2013 стандарт)

    • Видимо, в 2013 Excel сделали обязательными еще два параметра - если открыть скобку после .CreatePivotTable, то выпадет список всех параметров. Те, что без квадратных скобок - обязательные.

      • Вадим:

        В том то и дело, что они необязательные, справка по этому методу на сайте Майкрософт это подтверждает. Я всё же вставил все эти недостающие параметры и всё равно та же ошибка.

        • Вадим, значит где-то в таблицах неверно заданы имена столбцов. Или неверно они заданы в тексте запроса.

          • Ксения:

            Ребята, возникла та же проблема, что и у Вадима ("Слишком мало параметров. Требуется 3" на строке "Set oPT = .CreatePivotTable(rRes(3, 1))"). Только у меня просит 5. Скобочку открывала, нашла каких-то два названия без круглых скобок, но не знаю что и как вставлять. Подскажите, пжл. Кстати, проблем с названием столбцов нет. Ни в самих названиях, ни в запросе, т.к. их название я копировала с самой таблицы.
            Спасибо.

    • Ксения, значит все же неверно заданы столбцы. Посмотрите внимательно на то, как они записаны в коде. Каждый заголовок внутри квадратных скобок. Именно квадратных. И разделены запятыми.

      • Ксения:

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

      • Ксения:

        Дмитрий, теперь я сделала так: вместо перечисления названий столбцов поставила звездочку (*), и получилась ошибка: "В таблице или запросах, выбранных на объединение, не совпадает число столбцов". Нажимаю кнопку "Debug" на этом окне сообщений, и меня перебрасывает в модулю на строку "Set oPT = .CreatePivotTable(rRes(3, 1))". Хотя во всех таблицах по 12 столбцов.

        • Ксения:

          А, еще, после закрытия модуля, у меня удаляется таблица с первого листа.

  4. Сергей:

    Совсем не выходит. Остановилось на числах единички. Данные из таблиц не собирает. Уже целый день долблю!

  5. А если таблицы большие, кэш сможет их обработать?

    • Владимир, обработает. Хотя тут еще и параметры ПК могут сыграть роль: при большом кол-ве данных надо понимать, что оперативной памяти на ПК должно хватать для обработки таких объемов.
      Еще присмотритесь к другому варианту обработки больших объемов - Сводная из базы данных Access

  6. Кирилл:

    Вопрос немного не по теме. Скажите можно ли создать чертежную рамку А3(альбомная), что бы она не менялась в размерах в зависимости от изменения их размера. В этой рамке должна быть таблица в которой когда заполняется определенное кол-во информации, перебрасывало в новую страницу автоматически+ создание новой рамки?

  7. Ксения:

    Насколько я поняла, в статье указан макрос для создания сводной таблицы. А можете подсказать девушке, которая впервые с этим столкнулась, куда нужно скопировать этот макрос, что нужно в нем изменить и, главное, как, чтоб создать нужную именно мне сводную таблицу? Кстати, есть один нюансик: количество листов с таблицами данных для сводной может измениться в большую сторону (примерно до 50 шт). Заранее огромное спасибо, и мои извинения админам, если залезла со своим вопросом не туда.

    • Ксения, вот пара ссылок:
      Что такое макрос и где его искать?
      Что такое модуль? Какие бывают модули?

      Код надо поместить в стандартный модуль. А уж вызывать - как угодно, в одной из статей описаны способы вызова макроса. Плюс к статье приложен пример с кодом и кнопкой - там наглядно можно посмотреть и где код располагать и как вызвать.
      Если вчитаетесь в комментарии внутри кода, то найдете кусок, который отвечает за перечисление имен листов, данные которых надо включить в сводную:

      'сюда можно добавить еще листы
      avSheets = Array("План", "Факт") 'например: Array("Январь", "Февраль", "Март", "Апрель")

      А вот что и как изменить, чтобы создать нужную ИМЕННО ВАМ сводную - никто не подскажет, т.к. никто не знает что за сводная Вам нужна. Но если это стандартная сводная - то код создает именно такую и дальше делайте с ней что угодно и как нужно.

      • Ксения:

        Дмитрий, огромное спасибо за информацию, но я ни разу не работала с макросами, поэтому объясните мне на пальцах: в самом коде есть четыре цвета: красный, синий, черный и зеленый. Что они означают? Если я правильно поняла, зеленый - это Ваш комментарий, тогда возникает вопрос, строки с 1 по 8 за какое действие отвечают? Спасибо.

  8. Виталий:

    Макрос выдает ошибку в следующей строке:
    ThisWorkbook.PivotCaches(1).Connection = sCon
    Run-time error '1004'
    Application-defined or object-defined error.

  9. Катя Л:

    Спасибо за код макроса, но как быть, если сводная уже создана, а мне пришлось добавить в один из лист с исходными данными информацию,а сводная с этими данными не обновляется ?

    • Катя, к сожалению я так не могу ничего сказать. Вы создали сводную руками и надо добавить данные еще одного листа? Или кодом создавали? Хотя в любом случае, если добавился лист - надо заново формировать сводную со всех листов. При этом в коде есть строка для добавления листов - я упоминал про это в комментариях к коду:
      avSheets = Array("План", "Факт")
      здесь надо добавить в кавычках имя нового листа.

      • Катя Л:

        нет, не новый лист появился, а я внесла дополнительные строки на лист из которого формировалась изначально сводная, т.е. появились новые данные и они не отображаются в сводной. Решила эту проблему только удалением данных с листа и внесением новых, но это неудобно.

        • Катя Л:

          делала через макрос по описанию выше

        • Ясно. Здесь да, иначе вряд ли получится. При простом добавлении кэш не обновляется. Но если просто вставить и удалить строку на листе - то все обновиться. Я подумаю, как обновление при такой ситуации можно сделать отдельным кодом

          • Катя Л:

            Да,было бы круто. а еще, подскажите пожалуйста. я в макросах ни бум бум, мне нужно сделать Сумму по полям, а также вывести среднее значение. Я использовала ваш код, где я могу внести изменения,чтобы нужные мне поля отобразились в сводной?
            Я внесла корректировку в код:sCols = "[№ п/п],[Тип конструкции],[Коэф-т],[Кол-во,тн],[Сумма]" и у меня выпали в сводную все,кроме поля Коэф-т и Сумма..по ним нужно вывести сумму и среднее значение. Не получается..Я еще нашла ниже код :AddDataField .PivotFields("Сумма"), "Сумма по полю Сумма", xlSum , как его скорректировать чтобы работал и по другим полям?

          • Катя Л:

            Дмитрий, ура!!У меня что-то получилось)) буду смотреть дальше,спасибо за помощь)

          • Катя Л:

            не поняла только, как вывести среднее значение, что указывать вместо xlSum?

  10. Катя, просто запишите макрорекордером изменение типа вычислений для поля(т.е. установите среднее). Если не в курсе как записывать макросы: Что такое макрос и где его искать?

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2019 Excel для всех   Войти