Как сделать чтобы сводная таблица обновлялась автоматически

Добавил пользователь Morpheus
Обновлено: 19.09.2024

На листе Excel сводная таблица. Исходные данные на другом листе. Как сделать чтобы при изменении исходных данных автоматически обновлялась сводная таблица? Причем обновлялась также, как если бы пользователь вручную нажал кнопку "Обновить".

Если так, навскидку.
Можно "повесить" на кнопку "Обновить" макрос и запускать его при обновлении листа. Где-то так (данные на листе1):

Вот такой макрос обновляет сводную таблицу, но обновление "неполное", т.е. оно не аналогично тому, если просто нажать на кнопку "обновить".

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Лист2").PivotTables("СводнаяТаблица1").PivotCache.Refresh
End Sub

например, строчки не выстраиваются в алфавитном порядке при использовании такого макроса.

Разобрался, в чем глюк такого обновления. Дело в том, что на листе с исходными данными у меня ячейки с проверкой из списка (там фамилии и их можно выбрать только из определенного набора: Иванов, Петров, Сидоров). Когда я из списка выбираю одну фамилию вместо другой, Excel не распознает это действие как обновление информации на листе с исходными данными. В этом и была вся загвоздка.
Думаю, заставить Excel считать вышеописанное действо обновлением невозможно, хотя возможно я и ошибаюсь. Если нет - подскажите, пожалуйста, как это сделать. спасибо!

Сводные таблицы Excel - очень удобный и гибкий инструмент, позволяющий быстро сгруппировать данные нужным образом. И вот пять настроек, которые позволят вам использовать этот инструмент максимально эффективно.

Макет сводной таблицы

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

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

Через меню Файл заходим в Параметры Excel, раздел Данные и нажимаем на Изменить макет по умолчанию .

Здесь можно выбрать нужные настройки с помощью выпадающих меню и чек-боксов (1, см.рис.ниже), либо произвести импорт настроек (3), указав на листе (2) подходящую сводную таблицу.

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

Объединение ячеек

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

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

Выбираем пункт, который позволит объединить и выровнять по центру ячейки с подписями полей. Останется лишь задать для них подходящее форматирование - шрифт, его размер, начертание и т.д.

Изменение ширины столбцов

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

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

Зафиксировать ширину столбцов можно через параметры сводной таблицы. Здесь на вкладке Макет и формат снимаем флажок с параметра Автоматически изменять ширину столбцов при обновлении .

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

Автообновление

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

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

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

Фильтры

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

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

Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Можно обновлять значения в сводных таблицах, подключенных к внешним данным, таким как базы данных (SQL Server, Oracle, Access и другие), куб служб Analysis Services, веб-каналы данных и многие другие источники. Вы также можете обновлять данные из исходной таблицы в той же или другой книге. Кроме того, можно настроить автоматическое обновление данных сводной таблицы при открытии книги.

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

Обновление вручную

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.

Совет: Чтобы одновременно обновить все сводные таблицы в книге, нажмите кнопку анализ > Обновить все.

Если таблица обновляется слишком долго, на вкладке Анализ нажмите стрелку рядом с кнопкой Обновить и выберите пункт Состояние обновления. Так вы сможете проверить состояние обновления.

Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

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

На вкладке Анализ нажмите кнопку Параметры.

На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Автоматическое обновление данных при открытии книги

Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.

На вкладке Анализ нажмите кнопку Параметры.

Обновление вручную

Щелкните в любом месте сводной таблицы.

На вкладке Параметры в группе данные выполните одно из указанных ниже действий.

Чтобы обновить данные в соответствии с источником данных, нажмите кнопку Обновить или нажмите клавиши Alt + F5.

Вы также можете щелкнуть сводную таблицу правой кнопкой мыши и выбрать команду Обновить.

Если обновление занимает больше времени, чем ожидалось, нажмите кнопку параметры > Обновить > Обновить состояние , чтобы проверить состояние обновления.

Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

Автоматическое обновление данных сводной таблицы при открытии книги

Щелкните в любом месте сводной таблицы.

На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Обновление данных в сводных таблицах Excel

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

О целесообразности и возможности сводных таблиц

Оптимально формировать сводный отчет на основе исходной таблицы, если она отвечает следующим параметрам:

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

Требования к исходной таблице:

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

Нерациональная организация информации:

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

Лучше значения ввести следующим таким образом.

Как сделать сводную таблицу в Excel

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

Подразумевается, что данная таблица состоит из сотен строк. Создадим сводный отчет для выведения списка книг из конкретной категории или определенного года. Задача сформулирована – перейдем к реализации.

Сводная таблица сделана. С помощью нескольких кликов. Такой способ представления информации удобен для финансовых отчетов.

Как обновить сводную таблицу в Excel

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

Как настроить автоматическое обновление сводной таблицы в Excel:

Закрыть окно, нажав кнопку ОК.

Еще один вариант:

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

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

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

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

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

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

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

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

На новый лист (New Worksheet)
На существующий лист (Existing Worksheet)

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

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

Если же после указания видите именно диапазон — значит что-то указано неверно или таблица не является умной(возможно, форматирование от умной таблицы, но сама умная таблица была удалена).

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

убеждаемся, что макросы разрешены(Почему не работает макрос?, Что такое макрос и где его искать?)
перейти на лист исходных данных(в моем случае лист так и называется — Исходные данные)
жмем на ярлычке этого листа правой кнопкой мыши —Исходный текст (View code) :
вставляем туда следующий код:

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

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

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

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

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

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

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

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

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

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

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

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

Как обновить данные в сводной таблице excel

Возможно, но макросом.
Если им (макросом) подойдет, то нужен пример Вашего файла. Желательно с реальным (можно усеченным по вертикали) расположением (сами данные можно заменить на произвольные) данных и реальной сводной по этим данным.

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

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

[/vba]протупил, согласен. Хотя это в любом случае не будет лишним

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

2. Если сводная на этом листе. Обновление сводной в частности вызывает событие Worksheet_Change, что приведет к бесконечному циклу и аварийному выходу из Excel

Обновление данных сводной таблицы

Сводные таблицы и диаграммы не обновляются автоматически.

Если вы изменили исходные данные, сводную таблицу необходимо обновить:

1. Выделите ячейку внутри Сводной таблицы.

2. В разделе Работа со сводными таблицами на вкладке Параметры в группе Данные нажмите кнопку Обновить.

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

1. Выделите ячейку внутри Сводной таблицы.

2. В разделе Работа со сводными таблицами на вкладке Параметры в группе Данные нажмите кнопку Источник данных.

3. В окне диалога Изменить источник данных сводной таблицы в поле Таблица или диапазон укажите измененный диапазон исходных данных.

Более подробную информацию вы можете найти в книгах:

1. Иванов И.И. Microsoft Excel 2010 для квалифицированного пользователя. — СПб.: 2011. — С. 153.

2. Джелен Б., Александр М. Сводные таблицы в Microsoft Excel 2010.: Пер. с англ. – М.: 2011. – С. 58.

Оформление сводной таблицы

При необходимости Вы можете изменить оформление сводной таблицы. Для этого:

1. Выделите ячейку внутри Сводной таблицы.

2. В разделе Работа со сводными таблицами перейдите на вкладку Конструктор.

3. В группе Стили сводной таблицы выберите подходящий стиль оформления.

4. В группе Параметры стилей сводной таблицы, используя соответствующие флажки, настройте форматное выделение:

5. В группе Макет используя кнопку Общие итоги включите или отключите отображение итогов по строкам и/или столбцам используя соответствующие команды.

Более подробную информацию вы можете найти в книгах:

1. Иванов И.И. Microsoft Excel 2010 для квалифицированного пользователя. — СПб.: 2011. — С. 154.

2. Джелен Б., Александр М. Сводные таблицы в Microsoft Excel 2010.: Пер. с англ. – М.: 2011. – С. 66.

Создание Сводной диаграммы на основе Сводной таблицы

Вы можете данные сводной таблицы представить в графическом виде с помощью сводной диаграммы. Для создания сводной диаграммы выполните следующие действия:

1. Выберите отчет сводной таблицы. На ленте появится панель Работа со сводными таблицами с дополнительными вкладками Параметры и Конструктор

2. На вкладке Параметры в группе Сервис нажмите кнопку Сводная диаграмма.

3. В окне диалога Вставка диаграммы выберите нужный тип и подтип диаграммы. Можно использовать любой тип, кроме точечной, пузырьковой и биржевой диаграммы.

Более подробную информацию вы можете найти в книгах:

1. Иванов И.И. Microsoft Excel 2010 для квалифицированного пользователя. — СПб.: 2011. — С. 155.

2. Джелен Б., Александр М. Сводные таблицы в Microsoft Excel 2010.: Пер. с англ. – М.: 2011. – С. 69.

Отключите adBlock!
и обновите страницу (F5)
очень нужно

После создания сводной таблицы она не обновляется автоматически, когда вы добавляете новые или изменяете существующие данные.

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

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

В этом руководстве рассматриваются несколько способов сделать это.

Обновить сводную таблицу

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

Вот шаги, чтобы обновить сводную таблицу:

Щелкните правой кнопкой мыши любую ячейку в сводной таблице.
Выберите Обновить.

Это мгновенно обновит сводную таблицу.

Вы также можете выбрать любую ячейку в сводной таблице и использовать сочетание клавиш ALT + F5.

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

Обновить сводную таблицу, изменив источник данных

Если вы добавили новые строки / столбцы в источник данных, вам необходимо изменить источник данных, чтобы убедиться, что новые строки / столбцы являются частью набора данных.

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

Автообновление сводной таблицы с использованием макроса VBA

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

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

Расшифровка кода: Это событие изменения, которое запускается всякий раз, когда происходит изменение на листе, содержащем исходные данные. Как только происходит изменение, код обновляет сводный кэш сводной таблицы с именем PivotTable1.

Вам необходимо изменить этот код, чтобы он работал в вашей книге:

Куда поместить этот код VBA:

Нажмите Alt + F11. Откроется окно редактора VB.
В редакторе VB слева будет проводник проекта (с именами всех листов). Если его там нет, нажмите Control + R, чтобы сделать его видимым.
В проводнике проекта дважды щелкните имя листа, содержащего сводную таблицу.
В окне кода справа скопируйте и вставьте данный код.
Закройте редактор VB.

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

кликните сюда чтобы скачать файл с примером.

Примечание. Поскольку в книге есть макрос, сохраните его с расширением .xls или .xlsm.

Вам также могут понравиться следующие руководства по сводным таблицам:

Читайте также: