Lost your password?


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

Автообновляемая сводная таблица

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

    Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить(Refresh) или вкладка Данные(Data) →Обновить все(Refresh all) →Обновить(Refresh)

Однако, если в конец исходных данных добавить строку(или несколько), то с большой долей вероятности даже обновление сводной таблицы не поможет - добавленная строка не появится в сводной. И чтобы её увидеть необходимо будет изменить источник данных для сводной таблицы, включив новую строку в диапазон. Не очень удобно, не правда ли? Чтобы добиться расширения диапазона исходных данных автоматически вместе с добавлением туда данных, лучше позаботиться об этом до создания сводной таблицы.


Недоавтообновление
Почему "недо" - жать кнопку Обновить все же придется...Но не отчаивайтесь - читайте до конца и мы научимся обновлять все автоматом.
Для счастливых обладателей Excel 2007 и старше есть простой способ без лишних телодвижений. Это встроенный инструмент Таблица(Table). Его еще иначе называют "умная таблица" и я тоже буду применять этот термин, чтобы не было путаницы.
Умная таблица - это специальный объект, который представляет собой правильную таблицу с заголовками, которая расширяется по мере добавления в неё данных. В ней много еще чего полезного, но нас интересует сейчас именно то, что она расширяется сама по мере добавления данных и что на её основе можно создать сводную таблицу. В нашем случае она будет играть роль динамического именованного диапазона(стандартный именованный диапазон не может быть источником данных для сводной таблицы, поэтому и приходится идти другими путями). Чтобы создать такую таблицу необходимо:

  • выделить таблицу исходных данных для создания сводной таблицы -перейти на вкладку Вставка(Insert) и выбрать Таблица(Table)
  • В появившемся окне согласиться с указанным диапазоном или выбрать свой. Галочку Таблица с заголовками(My table has headers) при этом надо обязательно оставить включенной:
    Создание умной таблицы

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

    Если вдруг захочется и здесь навести красоту, то это тоже делается довольно легко:
    Выделяем любую ячейку в этой таблице-переходим на вкладку Работа с таблицами(Table tools) -Конструктор(Desigh) -Стили таблиц(Table styles). Можно выбрать один из предлагаемых там вариантов и применить. Если ни один из вариантов не подходит - создаем свой. Раскрываем список стилей и выбираем Создать стиль таблицы(New table style...)

А дальше все как привыкли:

  1. Выделить любую ячейку исходной таблицы(теперь уже "умной")
  2. Вкладка Вставка(Insert) -группа Таблица(Table) -Сводная таблица(PivotTable)
  3. В диалоговом окне Создание сводной таблицы(Create PivotTable) в пункте Выбрать таблицу или диапазон(Select a table or range) в поле Таблица или диапазон(Range/Table) будет автоматически указан не адрес какого-то диапазона, а имя созданной умной таблицы:
    Создание сводной на основе умной таблицы
    Далее надо определить место размещения Сводной таблицы:

    • На новый лист (New Worksheet)
    • На существующий лист (Existing Worksheet)
  4. нажать OK

Готово. Теперь при добавлении строк в эту таблицу для их отображения в сводной достаточно будет лишь обновить сводную таблицу как привыкли. Правда, тут тоже есть нюанс - добавлять строки надо правильно. Можно вбить данные в любую ячейку первой пустой строки таблицы - таблица автоматом расшириться, добавив еще одну строку. Теперь туда можно скопировать нужные данные или добить вручную. Если надо вставить сразу несколько строк - в правом нижнем углу последней строки умной таблицы есть слегка выделяющийся уголочек, который надо ухватить мышью и растянуть на нужное кол-во строк/столбцов.
Если мы просто скопируем строки ниже таблицы, то она не расширится. Это надо учитывать.


Если необходимо настроить на авторасширение уже созданную сводную, то порядок почти такой же, только сводную таблицу создавать не надо. Преобразуем исходные данные в умную таблицу, переходим на лист со сводной таблицей. Выделяем любую ячейку в сводной таблице, переходим на динамическую вкладку Работа со сводными таблицами(PivotTable Tools) -Параметры(Options) -группа кнопок Даныне(Data) -Источник данных(Change data Source). В появившемся окне в поле Таблица или диапазон(Table/Range) указываем либо ссылку на всю умную таблицу, либо имя нашей умной таблицы(если знаете где его подсмотреть). На что здесь следует обратить внимание: если указывался диапазон, то если он указан верно - в поле вместо адреса ячеек будет отображено имя умной таблицы:
Изменить источник данных
Если же после указания видите именно диапазон - значит что-то указано неверно или таблица не является умной(возможно, форматирование от умной таблицы, но сама умная таблица была удалена).


Полное автообновление
Для полного счастья можно подключить работу макросов. Что я хочу? Я хочу, чтобы как только я изменил/добавил данные в исходные данные - сводная тут же обновилась. Для этого надо сделать следующее:

  1. убеждаемся, что макросы разрешены(Почему не работает макрос?, Что такое макрос и где его искать?)
  2. перейти на лист исходных данных(в моем случае лист так и называется - Исходные данные)
  3. жмем на ярлычке этого листа правой кнопкой мыши -Исходный текст(View code):
    Модуль листа
  4. вставляем туда следующий код:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        'проверяем - изменения внутри умной таблицы или нет
        If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then
            'если внутри таблицы, то обновляем сводную таблицу на листе "Автообновляемая сводная"
            Sheets("Автообновляемая сводная").PivotTables(1).RefreshTable
            'для всех сводных на листе
    '        Dim pt As PivotTable
    '        For Each pt In Sheets("Автообновляемая сводная").PivotTables
    '            pt.RefreshTable
    '        Next
        End If
    End Sub
  5. Сохраняем файл(это опционально :))

Все, теперь при любом изменении внутри исходных данных(будь это добавление/удаление строк или просто изменение значений внутри таблицы) сводная таблица обновиться без занудных действий вроде выделения сводной и жмахания кнопки Обновить.
Пара важных комментариев к коду:

  • Sheets("Автообновляемая сводная") - здесь Автообновляемая сводная это имя листа, на котором расположена сводная таблица. Это очень важно. Если будет указано неверное имя листа - код выдаст ошибку и никакого обновления, конечно же, не произойдет
  • код сделан таким образом, что на листе исходных данных должна быть только одна умная таблица. Нет, их может быть несколько, но код будет ориентироваться исключительно на первую. И если вы плохо знакомы с принципами создания объектов - то лучше не рисковать. Хотя и здесь можно выйти из положения. Если вы знаете имя своей умной таблицы(его можно подсмотреть на вкладке Конструктор -группа Свойства), то можно изменить код так:
    вместо строки
    If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then
    записать такую
    If Not Intersect(Target, Target.Parent.ListObjects("Таблица1").Range) Is Nothing Then
    где Таблица1 - имя вашей умной таблицы, на основании которой создана сводная.
  • тот же нюанс с листом самой сводной - код ориентирован так, что обновляет только первую сводную на листе Автообновляемая сводная. Здесь так же можно заменить цифру 1 на имя сводной(например .PivotTables("СводнаяТаблица1").RefreshTable), либо же сделать обновление всех сводных. Для этого надо раскомментировать блок 'для всех сводных на листе(убрать апострофы перед строками) и убрать строку с именем листа, чтобы получилось так:
    Private Sub Worksheet_Change(ByVal Target As Range)
        'проверяем - изменения внутри умной таблицы или нет
        If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then
            'если внутри таблицы, то обновляем все сводные на листе "Автообновляемая сводная"
            Dim pt As PivotTable
            For Each pt In Sheets("Автообновляемая сводная").PivotTables
                pt.RefreshTable
            Next
        End If
    End Sub

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

  • P.S. Так же можно использовать и иной подход - вставить в модуль листа Автообновляемая сводная такой код:

    Private Sub Worksheet_Activate()
        Me.PivotTables(1).RefreshTable
    End Sub

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

    Скачать файл с автообновляемой сводной таблицей:

      Tips_PT_AutoRefreshPT.xlsm (46,5 KiB, 3 291 скачиваний)


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

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

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

    Здравствуйте! Я в Excel мало что понимаю, прошу просто помочь.Это вроде моя тема.Книга,в ней 2 листа. Надо сделать так чтобы при изменение(обновление) листа 1, в лист 2(АВТОМАТИЧЕСКИ) копировались данные из определённой колонки(ячейки) листа 1.

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

  2. Елена:

    Добрый вечер. Попыталась использовать ваш код для автообновления сводной, но на листе исходных данных у меня уже есть иной код с именем
    Private Sub Worksheet_Change(ByVal Target As Range), и макрос выдает ошибку:
    Compile error:
    Аmbiguous name detected:Worksheet_Chenge
    Подскажите пожалуйста, как можно корректно изменить имя.

  3. Елена:

    Спасибо,разобралась сама) Все отлично работает

  4. d42212:

    Добрый день, макрос работает, однако пропадает возможность отменить внесенные изменение (стрелкой назад).

    • К сожалению да, Вы правы. Это особенность работы макросов в Excel - большинство действий макросом сбрасывает буфер обмена.

  5. День добрый! У меня сводная таблица строится на основе таблицы, которая через подключение обновляется. не получается сделать из этой таблицы (подключения) сделать "умную таблицу" - кнопка "Таблицы" не активна, если ее выделяю. Есть какой-то выход?

  6. abc128@mail.ru:

    Не могу запустить Ваш код. Ругается на Me.
    Private Sub Worksheet_Activate()
    Me.PivotTables(1).RefreshTable
    End Sub

    • прочтите статью внимательно - код вставляется в модуль ЛИСТА(правая кнопка мыши на листе -Посмотреть код(или Исходный текст)), а Вы явно поместили его в обычный модуль.

      • Сергей:

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

        • Сергей, в этом случае поможет только способ с использованием кода(ПОЛНОЕ АВТООБНОВЛЕНИЕ). Так я показал несколько вариантов - выбирайте по ситуации. Если изменяемая ячейка на листе, отличном от листа со сводной - то вполне подойдет код на активацию листа со сводной:

          Private Sub Worksheet_Activate()
              Me.PivotTables(1).RefreshTable
          End Sub
          • Людмила:

            Добрый день!
            А если таблица простая, а не умная? Будет работать макрос?

          • Людмила, код работать будет, т.к. он нацелен на обновление сводной при изменении ВНУТРИ исходных данных. Главное помнить, что при добавлении строк в источник сводной, эти новые строки не будут учтены в сводной - учитываются только данные первоначального источника данных.

  7. abc128@mail.ru:

    Справился сам. Именно в модуль Листа, а не в модуль Книги.

  8. abc128@mail.ru:

    Спасибо. Даже не знал о модуле Листа.

  9. Андрей:

    Добрый день! Замечательная статья и рабочие советы! Спасибо!
    Вопрос - если на лист, где находится "умная таблица" вставить данные путем копирования всего листа целиком, то "умная таблица" исчезает и сводная таблица на другом листе уже не обновляется...
    Подскажите - как сделать так, чтобы "Умная таблица" не исчезала...
    Спасибо

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

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2024 Excel для всех   Войти