Как обновить сводную таблицу в excel если изменил данные

Сводные таблицы Excel – обновление данных

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

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

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

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

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

Установите флажок « Отложить обновление макета» .

Кнопка UPDATE рядом с ней будет включена. Если вы внесете какие-либо изменения в области сводной таблицы, эти изменения будут отражены только после нажатия кнопки ОБНОВЛЕНИЕ.

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

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

• Нажмите на сводную таблицу.
• Нажмите вкладку ANALYZE на ленте.
• Нажмите Обновить в группе данных.

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

Обновить – Получить последние данные из источника, подключенного к активной ячейке.

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

Свойства соединения – установка свойств обновления для соединений книги.

Обновить – Получить последние данные из источника, подключенного к активной ячейке.

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

Свойства соединения – установка свойств обновления для соединений книги.

Изменение исходных данных сводной таблицы

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

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

Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .

Нажмите на вкладку АНАЛИЗ.

Нажмите Изменить источник данных в группе данных.

Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .

Нажмите на вкладку АНАЛИЗ.

Нажмите Изменить источник данных в группе данных.

Выберите Изменить источник данных в раскрывающемся списке.

Откроется диалоговое окно «Изменить источник данных сводной таблицы», и текущий источник данных будет выделен.

Выберите таблицу или диапазон, который хотите включить в поле «Таблица / диапазон» в разделе «Выбор таблицы или диапазона». Нажмите ОК.

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

Переход на внешний источник данных

Если вы хотите изменить внешний источник данных для сводной таблицы, лучше всего создать новую сводную таблицу. Однако, если расположение внешнего источника данных изменяется, например, имя базы данных SQL Server такое же, но оно было перемещено на другой сервер, или ваша база данных Access была перемещена в другой сетевой ресурс, вы можете изменить ваше текущее подключение к данным, чтобы отражать то же самое.

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

Нажмите вкладку ANALYZE на ленте.

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

Нажмите кнопку « Выбрать соединение» .

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

Нажмите вкладку ANALYZE на ленте.

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

Нажмите кнопку « Выбрать соединение» .

Откроется диалоговое окно « Существующие подключения ».

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

Нажмите кнопку Обзор для более .

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

Нажмите кнопку Обзор для более .

Откроется окно выбора источника данных .

• Нажмите на кнопку «Новый источник».
• Пройдите шаги мастера подключения к данным.

Если ваш источник данных находится в другой книге Excel, выполните следующие действия:

• Нажмите на поле Имя файла.
• Выберите имя файла книги.

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

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

• Нажмите на сводную таблицу.
• Нажмите вкладку ANALYZE на ленте.
• Нажмите «Выбрать» в группе «Действия».

В раскрывающемся списке выберите « Вся сводная таблица». Вся сводная таблица будет выбрана.

Нажмите клавишу удаления. Сводная таблица будет удалена.

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

Щелкните правой кнопкой мыши вкладку листа и выберите « Удалить» в раскрывающемся списке.

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Активизируем любую ячейку в исходном диапазоне – щелкаем мышкой. Переходим на вкладку «Вставка» — «Таблица». Нажимаем кнопку «Сводная таблица».
2. Автоматически выделяется весь диапазон. Открывается диалоговое окно инструмента. Необходимо проверить правильность параметров для отчета (диапазон, куда выводить сводную таблицу).
3. Открывается окно для построения отчета, список полей. В правой нижней части страницы – области для размещения данных из исходного диапазона.
4. Области позволяют сформировать структуру сводного отчета.
5. Сначала заполним «Названия строк». Так как нужно вывести список книг по годам, то в этом разделе должен быть перечень названий книг. В списке полей ставим птичку напротив поля «Название». Данные столбца имеют текстовый формат – автоматически попадают в область «Названия строк». К значениям сразу применяется сортировка по алфавиту.
6. Теперь ставим птичку напротив поля «Год выпуска».

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

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

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

1. Когда данные внесены в исходную таблицу, переходим на лист со сводным отчетом и щелкаем в любом его месте правой кнопкой мыши. В открывшемся меню выбираем «Обновить».
2. Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».
3. Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.

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

1. Открыть лист со сводным отчетом. Щелкнуть в любом месте таблицы левой кнопкой мыши. Это нужно для того, чтобы активизировалась «Работа со сводными таблицами».
2. На вкладке «Параметры» находим группу «Сводная таблица». Нажимаем – открывается меню кнопки. Выбираем пункт «Параметры».
3. В открывшемся меню «Параметры сводной таблицы» нажать кнопку «Разметка и формат». Поставить галочки напротив следующих пунктов:

Как обновить сводную таблицу в excel если изменил данные

​Смотрите также​​ в сводные данные​: Получилось все. ​ B3:C4)​Еще рекомендую. Если​ при открытии книги​ данных​ или данных. Снимите​Показывать общие итоги для​ отступ для названий​в поле​или​ полей будут сохранены​Что это означает.​Что необходимо сделать.​ нажмите кнопку​ книге содержит правила​Примечание:​ своего отдела к​только эту строчку​в) столбца (например​ у сводных один​

​ Excel, содержащей сводной​ Выберите, чтобы отсортировать поля​ флажок, чтобы скрыть​ столбцов​

​ строк​​Описание​Параметры поля значений​ как включенные.​ Сводные таблицы, созданный в​​ Удалите данные Power Pivot​Найти​​ условного форматирования, которые​Мы стараемся как​ совещанию, они обсуждаются​

​ сунуть пришлось не​ B)​​ источник данных, то​ таблицы.​ в списке полей​ всплывающие подсказки, значение,​ Установите или снимите флажок,​ Чтобы задать отступ​выделите замещающий текст​и внесите необходимые​Что это означает.​ Excel 2007 или​

В этой статье

​ из книги, которая​, чтобы найти сводные​

​ применены к ячейкам​ можно оперативнее обеспечивать​

Проблемы, которые приводят к существенной потере функциональности

​ и более так​

​ после End If,​

​вопрос 2 (побочный)​ и строить их​Примечание:​ сводной таблицы, в​ строку или столбец​ чтобы отобразить или​ строк в область​ и нажмите сочетание​ изменения.​ Кнопки полей, не отображаемые​ более поздней версии,​ будет использоваться в​ таблицы, содержащей ось​ в свернутых строках​

​ вас актуальными справочными​​ сказать неактуальны. ​ а в начале​можно ли как-то​ надо одну на​ Этот параметр недоступен для​ том порядке, определяемый​ сведения отображались на​ скрыть​ метки строк при​ клавиш CTRL+C, чтобы​В этой книге существует​

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

​Общий итог​ сводной таблицы в​ скопировать его.​ стиль среза, не​ будут сохранены как​ Excel 97 –​ Excel.​ отображается один и​ эти правила не​ языке. Эта страница​ можно чуть ли​

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

​строки в нижней​​ компактный формат, выберите​Книга содержит сводную таблицу,​​ поддерживаемый в более​​ включенные при открытии​ 2003.​В этой книге содержатся​ тот же показатель​ были потеряны в​ переведена автоматически, поэтому​ не отдельный файл​ работал!​ikki​

​ кеш (а значит​ раздела источника данных​Примечание:​Показать свойства во всплывающих​ части сводной таблицы.​

​ уровень отступа от​​ к которой применен​ ранних версиях Excel.​ и сохранении книги​Что необходимо сделать.​

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

​: по первому вопросу:​ и размер файла)​Число элементов для возвращения​ Этот параметр недоступен для​ подсказках​Фильтры раздела​

​ 0 до 127.​​ замещающий текст. Такой​ Этот стиль среза​ в более ранней​ В средстве проверки совместимости​ использовать для фильтрации​

​ все повторяющиеся меры,​​ Excel, разверните эти​ содержать неточности и​ т.д и т.п,​orange3100​обратите внимание, что​​ уменьшается в разы.​​ одного поля​​ источника данных OLAP.​​ Установите или снимите флажок,​​Промежуточный итог фильтром элементам​​Отобразить поля в области​​ текст не отображается​​ не будет сохранен.​​ версии Excel.​​ нажмите кнопку​

​ сводных таблиц и​ чтобы остается только​ строки или столбцы.​ грамматические ошибки. Для​ но нужно именно​: Не обновляются значения​ у обработчика события​lex​

​ Чтобы указать количество элементов​​Печать​ чтобы отобразить или​ страницы​ фильтра отчета​ в Excel 2007.​Что это означает.​Что необходимо сделать.​Найти​ функций для работы​ одну меру.​

​Что это означает.​​ нас важно, чтобы​ так как поставлен​​ сводной таблицы при​​ Worksheet_Change есть аргумент​: спасибо,Лузер!​ для каждого поля,​Развернуть или свернуть кнопки​ скрыть всплывающие подсказки,​ Установите или снимите флажок,​ Выберите​Что это означает.​ Настраиваемый стиль среза будет​

​ При повторном открытии книги​, чтобы найти сводные​ с аналитическими кубами​Сводная таблица или подключение​ Правила условного форматирования, примененные​ эта статья была​ вопрос, ничего не​ их изменении вручную​ — ByVal Target​light​ чтобы временно кэша​

​ Печать при отображаются​​ отображать сведения о​ чтобы включить или​вниз, затем вправо​ Замещающий текст не поддерживается​ потерян при сохранении​ после сохранения ее​

​ таблицы, которая будет​​ в книге. Срезы​ к данным в​ к ячейкам в​ вам полезна. Просим​ могу к сожалению​​Доброго времени суток!​​ As Range.​​: Спасибо!​​ с книгой, выберите​​ в сводной таблице​​ свойствах для элемента.​​ исключить отфильтрованные отчета​​отображение поля в​​ в Excel 97​​ книги в формате​

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

​А можно как-нибудь​​ одно из следующих​ Установите или снимите флажок,​Примечание:​ элементы при вычислении​ области фильтра отчета​ – 2007 и​​ файла более ранней​ ранней версии Excel​​ формате. Сохранение книги​​ в более ранних​ параметры сервера, не​​ столбцах, будут потеряны​​ секунд и сообщить,​​ сводной для совещания,​

​ вопрос крайне слаб,​​ по VBA перестала​ при таком обновлении​​ действий:​​ чтобы отобразить или​ Этот параметр доступен только​ промежуточных итогов.​ сверху вниз, как​ не отображаются.​ версии Excel.​ может потребоваться включить​​ в формате Excel​​ версиях Excel.​​ существующие в более​​ в Excel 97–2003.​

​ помогла ли она​ потом удалить ееУстановите​ но почему-то никак​

​ быть бесплатной или​​ ещё и сортировку​Автоматическое​ скрыть разворачивать и​ для источника данных​Примечание:​ поля добавляются к​

​Что необходимо сделать.​​Что необходимо сделать.​ и отключить кнопки​ 97 – 2003​Что это означает.​ ранних версиях Excel.​

​Что необходимо сделать.​ вам, с помощью​ надстройку. Она появиться​ не могу найти​ другие какие капризы​ по алфавиту осуществлять?​ Количество уникальных элементов​ сворачивать кнопок при​ OLAP.​ Источник данных OLAP должен​

​ нему, перед выполнением​​ В окне средства проверки​ Так как срезы​ полей, чтобы настроить​ и повторное создание​ Срезы будут недоступны в​ Некоторые параметры сервера​ В средстве проверки совместимости​

​ кнопок внизу страницы.​​ в меню Надстройки.​ решение.​ у неё в​ А то при​

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

​ щелкните ссылку​​ Для удобства также​ Pivot—Formatting—Reset Caption​Имеем: «Умную» таблицу,​ данный момент, то​ таком автоматическом обновлении​

​ по умолчанию.​ Этот флажок недоступен​ раскрывающимся фильтра​ синтаксису выражений MDX.​ Выделите​

​Найти​​ более ранних версиях​ (​ режиме совместимости, чтобы​

​ 2007, но останутся​

Проблемы, которые приводят к небольшой потере точности

​ или подключения к​

​ приводим ссылку на​Olya.ua​ на основе которой​ я вам поясняю:​ сортировка не производится,​Нет​ в вкладке​

​ Установите или снимите флажок,​​Помечать итоги *​вправо, затем вниз​, чтобы обнаружить сводную​ Excel, нет, которые​Работа со сводными таблицами​

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

​ таблицу, содержащую замещающий​ можно сделать, чтобы​, вкладка​ 97 – 2003​ в Excel 2010​ сохранены.​ строки или столбцы,​ языке) .​ ячеек сводной таблицы,​

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

​ области фильтра отчета​​ текст. Чтобы вывести​ сохранить настраиваемый стиль​​Анализ​​ без потери функциональности.​ или более поздней​Что это означает.​ содержащие правила условного​Средство проверки совместимости обнаружило​ чтобы он не​ полей в сводной​ на ячейку или​ то сортировка по​Макс​ снят флажок​ таблицы в верхней​

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

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

​ ранних версиях Excel​​Показать или скрыть​ с полями в​​Что необходимо сделать.​​ данные сервера параметры​ разверните их, прежде​ проблем совместимости, связанных​ обновлении таблицы? (в​ значения не возвращаются​ в которой (котором)​user0​​ элементов для каждого​.​ и отфильтровать стрелок​​ «звездочка» показывает, видимые​​ нему, перед выполнением​ Excel, можно скопировать​​ можно использовать для​​, кнопка​​ сжатой форме. В​​ В более ранних версиях​​ подключения, которые недоступны​​ чем сохранять книгу​​ в сводные таблицы.​​ частности, финансовый формат​​к исходным своим​

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

​ его в пустую​​ фильтрации данных фильтры​Кнопки полей​ более ранних версиях​ Excel для фильтрации​ в Excel 97​

​ в формате файлов​​Важно:​ изменяется на числовой)​​ значениям в «Умной»​​для решения вашей​ автоматическому обновлению сводных​ указать до 1​ каждой печатной странице​

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

​ Excel этот макет​​ данных можно использовать​ – 2007, будут​ более ранней версии​ Прежде чем продолжить сохранение​Raptor​ таблице если происходит​ задачи нужно анализировать​

​ при изменении данных​​ 048 576 элементов.​ Установите или снимите флажок,​ столбцов.​ при Excel вычисляет​Для каждого столбца число​ или добавить его​Сводная таблица в этой​Сводная таблица в этой​ изменится на табличную​​ фильтры сводных таблиц.​​ потеряны.​​ Excel.​​ книги в формате​​: Снимите галочку «Автоформат»​​ обновление сводной таблицы.​​ (проверять) этот диапазон​​ на текущем листе:​

​Примечание:​ чтобы повторять подписи​Макет классический сводной таблицы​ сумму не являются​ полей фильтра отчета​ в примечание.​

​ книге содержит одно​​ книге содержит один​ форму.​В этой книге содержатся​Что необходимо сделать.​Эта книга содержит именованные​ файла более ранней​ в параметрах сводной​Как это исправить?​

​ на пересечение с​​1) обновляю 6​ Этот параметр недоступен для​​ элементов текущей области​​ Установите или снимите флажок,​ только значения, которые​ Введите или выберите​Щелкните правой кнопкой мыши​ или несколько полей​ или несколько именованных​​Что это означает.​​ срезы, которые можно​ Проверьте, совместимы ли используемые​ наборы, не связанные​ версии, необходимо устранить​​ таблицы.​​antal10​​ нужным вам диапазоном​​ сводных таблиц, источником​ источника данных OLAP.​​ метки строк на​ чтобы включить или​​ используются в вычислениях.​ количество полей для​ в любом месте​​ с повторяющимися подписями.​ наборов. Возможно, некоторые​​ Сжатая форма (отдельно или​ использовать для фильтрации​

​ параметры сервера с​ со сводной таблицей.​проблемы, которые приводят к​Olya.ua​: А источник(база) данных​ и, в зависимости​ данных для которых​lex​

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

​ сводных таблиц и​​ более ранними версиями​ Эти именованные наборы​ существенной потере функциональности​: А если не​​ где? в другом​​ от результата сравнения​​ являются 2 листа​​: На листе несколько​ сводной таблицы.​

​ на и удалить​ Этот параметр доступен только​ другого столбца или​ выберите​ таблицы эти подписи​ не будут сохранены.​

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

​ файле? или в​​ выполнять те или​ (

10k строк на​ сводных таблиц. Исходные​Печать заголовков​ сводную таблицу.​ в том случае,​ строки на основе​Параметры сводной таблицы​ будут потеряны.​Что это означает.​ формами), которая препятствует​

​ с аналитическими кубами​ необходимые изменения (​Что это означает.​ потерю данных или​Ivan M.​ этом же?​ иные действия (или​

​ лист), следующим кодом:​​ данные на другом.​ Установите или снимите флажок,​Показывать элементы без данных​ если источник данных​ параметра для​. На вкладке​Что это означает.​

​ Один или несколько именованных​​ распространению связанных данных​ в книге. Срезы​​Работа со сводными таблицами​​ Именованные наборы, не связанные​ неправильную работу функций.​: Возможно Вы установили​orange3100​​ не выполнять никакие)​​Private Sub Worksheet_Change(ByVal​​ Как сделать чтобы​​ чтобы включить или​​ в строках​​ OLAP не поддерживает​​отображения полей в области​​Замещающий текст​​ Повторяющиеся подписи не поддерживаются​​ наборов содержат параметры​

​ по горизонтали за​ не будут сохранены.​, вкладка​ со сводной таблицей​Проблемы, которые приводят к​ формат просто выделив​: По моему опыту​

​например:​​ Target As Range)​ при изменении исходных​ отключить повторение и​ Установите или снимите флажок,​ подзапросом выборки синтаксису​

​ фильтра отчета​​в поле​ в Excel 97​​ свойств, отличные от​​ пределы экрана и​ Формулы, ссылающиеся на​Анализ​ в Excel 97​ несущественной потере точности​ конкретную ячейку или​ это не зависит​а) if not​Worksheets(«pivot_sheet1»).PivotTables(«pivot1_1»).PivotCache.Refresh​ данных автоматически обновлялись​

​ заголовки столбцов поля​ чтобы отобразить или​ выражений MDX.​.​​Описание​​ – 2007 и​​ параметров по умолчанию,​​ позволяет до минимума​​ срезы, будут возвращать​​, группа​ – 2007 исчезает.​, не всегда обязательно​ их диапазон. Не​

​ от источника.​ intersect(target, me.[b3]) is​Worksheets(«pivot_sheet1»).PivotTables(«pivot1_2»).PivotCache.Refresh​ все сводные таблицы?​ заголовков строк и​

​ скрыть элементы строк,​​Разрешить несколько фильтров для​«Формат»​выделите замещающий текст​ наклейки, будут потеряны​

​ которые могут быть​​ сократить прокрутку, недоступна​ ошибку #ИМЯ?.​​Данные​​Что необходимо сделать.​ устранять перед сохранением:​ исключено, что при​В файле который​ nothing then . ​Worksheets(«pivot_sheet1»).PivotTables(«pivot1_3»).PivotCache.Refresh​lex​ столбцов элемента на​ которые содержат нет​ поля​

​Показать для значения ошибки​ и нажмите сочетание​ при обновлении сводной​ потеряны при сохранении​​ в Excel 97–2003,​​Что это означает.​​, кнопка​​ Чтобы избежать возникновения этой​​ данные и функциональность​​ этом таблица была​ приложен к примеру​б) if not​Worksheets(«pivot_sheet2»).PivotTables(«pivot2_1»).PivotCache.Refresh​

​: На листе несколько​

Настройки сводной таблицы

​ каждой печатной странице​​ значения.​ Выберите, чтобы включить все​ Установите этот флажок,​ клавиш CTRL+C, чтобы​ таблицы в более​ книги в формате​ и поля отображаются​ Среды не поддерживаются в​Источник данных​ проблемы, убедитесь в​ не теряются, но​ отфильтрована по какому-то​ — это источник​ intersect(target, me.[b3:c4]) is​Worksheets(«pivot_sheet2»).PivotTables(«pivot2_2»).PivotCache.Refresh​ сводных таблиц. Исходные​ сводной таблицы.​Примечание:​ значения, включая эти​ а затем введите​ скопировать его.​

​ ранних версиях Excel.​ Excel 97–2003.​ в табличной форме.​​ более ранних версиях​​, команда​

​ том, что сводная​​ книга при ее​ критерию​ в данной книге​ nothing then . ​

​Worksheets(«pivot_sheet2»).PivotTables(«pivot2_3»).PivotCache.Refresh​

​ данные на другом.​

​Примечание:​ Этот параметр доступен только​ скрытые фильтром, когда​​ текст, например «Недействительна»,​К началу страницы​Что необходимо сделать.​Что необходимо сделать.​Что необходимо сделать.​ Excel. Они не​Свойства подключения​ таблица создается с​ открытии в более​В сводных таблицах​orange3100​в) if not​End Sub​

​ Как сделать чтобы​ Чтобы фактически печать наклеек,​ для источника данных​​ Microsoft Office Excel​ который вы хотите​Примечание:​ В средстве проверки совместимости​ В средстве проверки совместимости​ В средстве проверки совместимости​ могут быть отображены​

​).​ использованием подключения.​​ ранней версии Microsoft​​ лучше устанавливать формат,​​: Что значит «не​ intersect(target, me.columns(2)) is​Возможно можно как-то​ при изменении исходных​ по-прежнему необходимо ввести​ OLAP.​ вычисляет промежуточных и​​ отобразить в ячейке​​Мы стараемся как​ щелкните ссылку​ нажмите кнопку​ нажмите кнопку​ и будут утеряны.​Сводная таблица в данной​Книга содержит сводную таблицу​

​ Excel может выглядеть​ когда никакой критерий​​ возвращаются к исходным​ nothing then . ​ оптимизировать и ускорить​ данных автоматически обновлялись​ значения в полях​Показывать элементы без данных​​ общих итогов. Снимите​ вместо сообщение об​​ можно оперативнее обеспечивать​

​Найти​​Найти​При обновлении подключений с​ книге содержит данные,​ с включенным анализом​ или работать несколько​ фильтра не активирован​ значениям»​ps всё-таки рекомендую​ обновление? Поиском нашел​

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

​, чтобы найти сводные​, чтобы найти сводные​​ функциями OLAP, ссылающимися​ представленные с помощью​ «что если». Неопубликованные​ иначе.​ (каждое поле имеет​Сводная таблица -​ один раз заплатить​ тему про принудительное​

​Лузер​или​​ Установите или снимите флажок,​ только элементы, отображаемые​ флажок, чтобы отобразить​ материалами на вашем​ таблицу, содержащую повторяющиеся​ таблицы, содержащий именованный​ таблицы с полями​ на срезы, вместо​ функции «Дополнительные вычисления».​ изменения «что если»​Проблемы, которые приводят к​ галочку Show All).​ это по сути​ справке, чем мучиться​ использование одного кэша​: В код листа​

​столбцы для повтора слева​

​ чтобы отобразить или​

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

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

​ скрыть элементы столбцов,​

​ общих итогов в​Показывать пустые ячейки​​ переведена автоматически, поэтому​ их повторение (​ были изменены и​ а затем измените​ значения ошибки #ИМЯ?.​

​ данные не будут​​ предыдущих версиях Excel.​Проблемы, которые приводят к​ дли форматирования нужно​

​ т.е отображение данных.​​Serge​ таблиц, но не​Private Sub Worksheet_Change(ByVal​Печатать заголовки​ которые содержат нет​ вычислениях.​ Установите этот флажок,​ ее текст может​Работа со сводными таблицами​ внесите необходимые изменения​ их формат на​

​Что необходимо сделать.​​ сохранены и будут​Что это означает.​ небольшой потере точности​ через меню сводной​orange3100​: Уверен что нельзя.​

​ смог применить это​ Target As Range)​​на вкладке «​ значения.​Примечание:​ а затем введите​ содержать неточности и​, вкладка​ в диалоговом окне​ структурную или табличную​ Вместо срезов используйте для​ заменены исходными значениями​ Изменения анализа «что если»,​

​Проблема​​ таблицы (найдёте его,​: Если вы просмотрите​Обновить сразу все​

​ к нескольким источникам​

​Sheets(«Лист2»).PivotTables(«СводнаяТаблица1»).PivotCache.Refresh​лист​​Примечание:​ Этот параметр доступен только​ текст, например «Пусто»,​ грамматические ошибки. Для​Конструктор​Параметры поля​ форму, сняв флажок​ фильтрации данных фильтры​ из источника данных.​

​ не опубликованные на​

​ открыв панель Pivot​ пример (вложение) или​​ сводные можно так:​ сводных:​End Sub​» диалогового окна​ Этот параметр доступен только​ для источника данных​ который вы хотите​ нас важно, чтобы​, группа​. Щелкните правой кнопкой​Объединить и выровнять по​ сводных таблиц.​Что это означает.​ сервере, не отображаются​Книга содержит сводную таблицу,​ Table), активизировать пункт​ отредактируете свою любую​Private Sub Worksheet_Change(ByVal​

​2) как задать​​где Лист2 -​Параметры страницы​ для источника данных​ не OLAP.​ отобразить в ячейке​ эта статья была​Макет​ мыши любой элемент​ центру ячейки с​К началу страницы​ Функция не поддерживается в​

​ в более ранних​ размеры которой выходят​​ Выбрать (Enable Selection),​ сводную таблицу (любое​ Target As Range)​ обновление только при​ лист со сводной​

​. (На вкладке​​ OLAP.​Сортировка раздела​ вместо пустую ячейку.​

​ вам полезна. Просим​, кнопка​​ именованный набор и​ подписями​Проблема​ Excel 97 –​ версиях Excel.​ за пределы, установленные​ затем что именно​ поле со значением​ActiveWorkbook.RefreshAll​

​ изменении а)определенной ячееки​​ таблицей​Разметка страницы​Отображение подписей элементов при​Использовать настраиваемые списки при​Автоматически изменять ширину столбцов​

​ вас уделить пару​Макет отчета​​ нажмите кнопку​(​Решение​ 2007 и пользовательское​Что необходимо сделать.​

​ в предыдущих версиях​​ выбрать (Label or​ которое можно исправить),​End Sub​

​ б)диапазона(выделен желтым в​СводнаяТаблица1 — имя​​в группе​ отсутствии полей в​ сортировке​ при обновлении​ секунд и сообщить,​

​, команда​​Параметры поля​сводной таблицы В группе​Книга содержит сводную таблицу,​

​ значение, которые не​ Прежде чем открывать книгу​ Excel. Эта сводная​​ Data or Label​ то после обновления​user0​ примере) в)столбца, так​ сводной.​

​Параметры страницы​​ области значений​ Установите или снимите флажок,​ Выберите, чтобы отрегулировать​ помогла ли она​

​Не повторять подписи элементов​. На вкладке​​ Работа с​ к которой применен​ отображаются данные, которые​ в более ранней​ таблица будет потеряна​ and Data), и​

​ это поле останется​​: ikki, Serge 007​ как сейчас все​Или все таблицы​

​ Установите или снимите флажок,​

​ чтобы включить или​ столбцы сводной таблицы​​ вам, с помощью​).​Разметка и печать​, «​ стиль сводной таблицы.​

​ вы ввели (например,​​ версии Excel, опубликуйте​ при сохранении в​

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

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

​убедитесь в том,​

​Анализ​ В более ранних​% от родительской строки​​ изменения анализа «что​ формате более ранней​ мышкой выбирайте поля​Необходимо сделать так​Я бы с​ любой ячейки листа,​Private Sub Worksheet_Change(ByVal​​.​​ скрыть подписи элементов,​ списки при Excel​​ размер широкой текст​​ Для удобства также​

​ к которой применен​ что флажок​​», группа «​ версиях Excel стили​ суммы​ если» (​ версии. В более​ (заголовки или данные)​

​ что можно было​​ удовольствием почитал справку​ что несколько неудобно.​ Target As Range)​Данные​ когда нет полей​ сортирует списки. Если​ или число. Снимите​ приводим ссылку на​

​ замещающий текст. Замещающий​​Отображать элементы разных уровней​Сводная таблица​ сводных таблиц не​​,​​Работа со сводными таблицами​​ ранних версиях Excel​​ на таблице. К​​ чтото написать или​​ по VBA, но​​зы: на всякий​​For Each PvTable​​Раздел данных сводной таблицы​​ в области значений.​​ снять этот флажок​​ флажок, чтобы сохранить​​ оригинал (на английском​​ текст для сводных​​ в разных полях​​», команда «​

​ поддерживаются.​

​% от родительской суммы​

​, вкладка​ будут работать только​​ выбранному таким образом​ откорректировать поле, а​ к сожалению на​ случай пример во​ In Sheets(«Лист2»).PivotTables​

​Сохранить источник данных с​​Примечание:​

​ при сортировке больших​ ширину столбцов текущей​ языке) .​

​ таблиц будет удален​не установлен (по​Параметры​

​Что это означает.​​ столбца​Анализ​ сводные таблицы, созданные​ диапазону применяйте нужный​ после обновления оно​ данном этапе я​

​ вложении​​PvTable.PivotCache.Refresh​ файлом​

​ Этот флажок только в​ объемов данных также​​ сводной таблицы.​Для управления различные параметры​ в версиях, предшествующих​ умолчанию), и установлен​», вкладка​ Стили сводных таблиц, основанные​

​или​​, группа​ в режиме ограниченной​

​ формат — обычно​ снова отображало данные​

​ мало что там​user0​​Next​ Установите или снимите флажок,​ сводных таблицах созданы​ может повысить производительность.​Сохранять форматирование ячеек при​ сводной таблицы с​

​ Excel 2007.​​ флажок​Разметка и формат​ на темах, не​

​нарастающим итогом в %​​Вычисления​ функциональности.​

​ он сохраняется и​​ из источника​ пойму, а так​: возможно описание сверху​End Sub​ чтобы сохранить или​

​ до Microsoft Office​​Отображение​ обновлении​

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

​ помощью диалогового окна​​Что это означает.​автоматически упорядочивать и удаление​).​ доступны и не​).​, кнопка​Что это означает.​

​ после обновления таблицы​​orange3100​ я только за​ несколько сумбурно, попробую​Лузер​ сохранить данные из​ Excel 2007.​Отображение раздела​

​ Выберите, чтобы сохранить макет​​Параметры сводной таблицы​ Замещающий текст не поддерживается​
​ повторений из набора​Сводная таблица в этой​
​ отображаются в Excel​
​Что необходимо сделать.​
​Средства OLAP​ В Excel 2007 и​Olya.ua​
​: Действия — Очистить,​ самобразование )​
​ изложить просьбу проще..​: Дисклеймер!​
​ внешнего источника данных​Показывать вычисленные компоненты с​
​Развернуть или свернуть кнопки​ сводной таблицы и​
​.​
​ в Excel 97​
​Выбранные (значение по​

​ книге содержит параметры,​​ 97–2003.​
​ В средстве проверки совместимости​
​,​ более поздних версий​: Иван, спасибо за​ и сделать по​Еще раз спасибо!​
​на листе data​Всмы, Ахтунг!​ в книге.​ сервера OLAP​ Показать​ отформатировать таким образом,​Имя​ – 2007 и​ умолчанию).​ не существующие в​

​Что необходимо сделать.​​ щелкните ссылку​
​Анализ «что если»​
​ сводной таблицы поддерживает​ такой полный ответ,​ новой​199MOS​ есть код который​
​при большом объеме​Примечания:​ Установите или снимите​ Выберите этот параметр для​ чтобы она используется​ Отображает имя сводной таблицы.​ не отображается в​Подключение в этой книге​ более ранних версиях​ В средстве проверки совместимости​

​ 1 048 576​​ моя таблица действительно​
​antal10​: у меня не​ при изменении данных​ данных, большом количестве​ ​ флажок, чтобы отобразить​ отображения знак плюс​ каждый раз выполнить​ Чтобы изменить имя,​ этих версиях Excel.​ содержит один или​

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

​ была отфильтрована, теперь​: Ну по сути​ получается вставить код​ на листе обновляет​ сводных таблиц можно​Этот параметр не должны​

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

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

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

​Дмитрий Д​ сводную таблицу, а​ в книге при​

​ листах pivot_sheet1 и​​ как XL обновляет​ конфиденциальность данных.​ флажок не влияет​

​ свернуть строк или​ чтобы не сохранять​Разметка и формат​ совместимости щелкните ссылку​ Возможно, некоторые свойства​Что это означает.​ таблицы, к которой​ удалите эти выходные​

​ один и тот​ – 2003 поддерживается​
​: Доброго всем дня!​
​ каждый раз это​
​ изменении данных в​
​ pivot_sheet2. Собственно сам​
​Еще рекомендую. Если​
​Кроме того этот параметр​
​ на вычисляемые показатели.​

​ названия столбцов. Снимите​
​ макет сводной таблицы​Раздел макета​Найти​
​ этих наборов или​ Некоторые параметры сводной таблицы​
​ применен стиль сводной​ значения (щелкните правой​
​ же показатель отображается​ только 32 500​

​У меня схожая​
​ делать просто невозможно.​ исходном листе: после​ код:​

​ у сводных один​​ недоступен для источника​
​Примечание:​ флажок, чтобы скрыть​ и формат и​Объединить и выровнять по​, чтобы обнаружить сводную​
​ элементов не будут​ в Excel 97​ таблицы, удалите этот​ кнопкой мыши поле,​ несколько раз. Эта​ элементов для каждого​ проблема (эксель 2010).​Необходимо чтобы сама​ ввода данных вылезает​Private Sub Worksheet_Change(ByVal​ источник данных, то​ данных OLAP.​ Этот параметр доступен только​

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

​ Снял все фильтры.​
​ структура и скелет​ вот что: Прикрепленные​ Target As Range)​
​ и строить их​Включение развернуть​ для источника данных​
​ минус кнопок, чтобы​ по умолчанию и​ подписями​

​ текст. Чтобы вывести​Что это означает.​ поддерживаются. Эти параметры​ и затем вручную​

​Дополнительные вычисления​​ будет сохранена.​

​Что необходимо сделать.​ В параметрах поля​
​ оставались неизменны, а​ файлы post_353375.GIF (66.12​
​Worksheets(«pivot_sheet1»).PivotTables(«pivot1_1»).PivotCache.Refresh​
​ надо одну на​

​ Установите или снимите флажок,​​ OLAP.​
​ развернуть или свернуть​

​ формат каждый раз,​ Выберите для объединения ячеек​ замещающий текст в​ Именованные наборы или вычисляемые​ будут потеряны.​ применить форматирование сводной​и​Что это означает.​ В средстве проверки совместимости​

​ значений установил числовой​

​ лишь данные вновь​​ КБ)​Worksheets(«pivot_sheet1»).PivotTables(«pivot1_2»).PivotCache.Refresh​ основе другой -​ чтобы включить детализация​Список полей​ строк или названия​ что при выполнении​ для внешней строки​ более ранней версии​ элементы обладают свойствами,​

​Что необходимо сделать.​​ таблицы, который поддерживается​Без вычислений​ В Excel 2010 и​

​ нажмите кнопку​​ формат. Все ок,​ подгружались.​Guest​

​Worksheets(«pivot_sheet1»).PivotTables(«pivot1_3»).PivotCache.Refresh​​ обновляются одновременно и​ подробных данных из​

​Два варианта являются взаимоисключающими.​​ столбцов. Может потребоваться​

​ операции в сводную​ и столбца элементов​ Excel, можно скопировать​ которые не поддерживаются​ В средстве проверки совместимости​ в более ранних​).​

​ более поздних версий​

Не обновляются значения сводной таблицы при их изменении вручную

​Найти​​ но стоит мне​делайте изменения в​: Потому что эта​
​Worksheets(«pivot_sheet2»).PivotTables(«pivot2_1»).PivotCache.Refresh​
​ кеш (а значит​ источника данных, а​Сортировка от А до​ скрыть знак плюс​ таблицу.​
​ таким образом, можно​ его в пустую​ в Excel 2007​
​ щелкните ссылку​ версиях Excel.​Книга содержит пользовательские внедренные​ можно воссоздать мер​
​, чтобы найти сводные​ убрать и снова​ столбце рядомНу это​ процедура уже описана​
​Worksheets(«pivot_sheet2»).PivotTables(«pivot2_2»).PivotCache.Refresh​

​ и размер файла)​​ затем отображение данных​ Я​ или минус кнопок​Итоги и фильтры​

​ выровнять по центру​​ ячейку на листе​ и Excel 97–2003.​Найти​
​Сводная таблица в этой​ данные. Эти данные​ в сводной таблице,​ таблицы, размер которых​

​ поставить это поле​​ уже меняет изначальную​ выше.​Worksheets(«pivot_sheet2»).PivotTables(«pivot2_3»).PivotCache.Refresh​
​ уменьшается в разы.​ на новый лист.​ Выберите, чтобы отсортировать поля​ при печати сводной​

​Раздел общие итоги​​ элементы по горизонтали​ или добавить его​ Возможно, эти свойства​, чтобы найти сводные​ книге не будет​ не будут сохранены.​ подключенной к источнику​ превышает бывшего ограничения.​ обатно в поле​
​ задачу. ​199MOS​End Sub​Лузер​Примечание:​ в списке полей​ таблицы или отобразить​

​Показывать общие итоги для​​ и вертикали. Снимите​ в примечание.​ не будут сохранены.​

​ таблицы с неподдерживаемыми​​ работать в версиях,​Что это означает.​ данных интерактивной аналитической​ Сохранение книги в​ вычеслений, как формат​
​Я поясню:​: но все равно​вопрос 1 (основной)​: Дисклеймер!​ Этот параметр недоступен для​

​ сводной таблицы в​ сводную таблицу только​ строк​ флажок, чтобы левому​
​Щелкните правой кнопкой мыши​
​Что необходимо сделать.​ параметрами, и внесите​ предшествующих Excel 2007.​ Внедренные пользовательских данных, например​ обработки (OLAP). Не​ формате Excel 97​ ячейки меняется на​В организации есть​ не работают варианты​сейчас как сделать​Всмы, Ахтунг!​
​ источника данных OLAP.​ алфавитном порядке по​ для просмотра.​ Установите или снимите флажок,​ краю элементов в​ в любом месте​ Щелкните правой кнопкой​ необходимые изменения.​ В более ранних​ Power Pivot данных​ удается отобразить сводную​ – 2003 и​ процентный, Параметры поля-Числовой​ файл в котором​ -:((​

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

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

​ мыши любой элемент​​Определенные кнопки полей сводной​ версиях Excel будут​ не поддерживается в​

​ таблицу в Excel​​ повторное создание сводной​ формат становится финансовым​

​ собраны данные с​​ber$erk​ изменении только:​ данных, большом количестве​ файла​Примечание:​ Выберите для отображения подсказок,​ скрыть столбце​ и столбцов в​
​ выберите​ именованных наборов и​ диаграммы в этой​ работать только сводные​ Excel 97 –​ 97 – 2007.​ таблицы в режиме​ «Евро Шведский (Финляндия)»​ нескольких отделов, далее​: . Прикрепленные файлы​а) определенной ячееки​ сводных таблиц можно​ Установите или снимите флажок,​ Этот параметр недоступен для​ значение, строку или​Общий итог​ верхней части группы​Параметры сводной таблицы​ нажмите кнопку​ книге включены или​ таблицы, созданные в​ 2007. Данные будут​Что необходимо сделать.​ совместимости.​Как с этим​ нач каждого отдела​

​ post_353384.gif (93.29 КБ)​​ (например B3)​ смотреть целый день,​ чтобы обновить или​ источника данных OLAP.​ столбец сведения отображались​

​рядом с последнего​​ элементов.​

​. На вкладке​Параметры поля​ отключены. Все кнопки​ режиме совместимости.​ потеряны.​ В средстве проверки совместимости​Сводная таблица в этой​ бороться?​ вписывает свои комментарии​199MOS​б) диапазона (например​ как XL обновляет​ не обновлять данные​Сортировка в порядке источника​ на значение поля​

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

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

Итака, приступаем к работе. В нашем файле на листе AZS имеется таблица с информацией о реализации различных видов топлива. Задача – настроить на основе этой таблицы (назовем ее таблицей АЗС) несколько сводных таблиц и настроить их автоматическое обновление.

Приступим к работе. На первом шаге мы зададим на основе нашей таблицы два именованных диапазона – АЗС_Данные и АЗС_Дата. Два диапазона потребуются в дальнейшем, чтобы рассмотреть особенности автоматического обновления сводных таблиц, основанных на динамических диапазонах.

Для создания динамического диапазона применяются разные способы. В частности, можно преобразовать исходный диапазон в умную таблицу, а затем использовать ее название. Сегодня же воспользуемся двумя вариантами создания динамического именованного диапазона формулами. Переходим на вкладку «Формулы» , нажимаем кнопку «Диспетчер имен». Кстати, открыть окно диспетчера имен можно, нажав сочетание Ctrl+F3. Для добавления имени нажимаем кнопку «Создать». В верхней строке вводим имя – АЗС_Данные, а в нижней – формулу, описывающую наш диапазон.

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

Нажимаем кнопку ОК, затем снова нажимаем кнопку «Создать». Создаем новый диапазон с именем АЗС_Дата и формулой

Нажимаем кнопку ОК, возвращаемся в диспетчер имен и нажимаем кнопку «Закрыть». Два динамических диапазона с различными именами и формулами созданы.

На следующем шаге создадим два листа, назовем их «Свод АЗС 1» и «Свод АЗС 2». Разместим на них несколько сводных таблиц. На листе «Свод АЗС 1» поместим 2 сводные таблицы на основе диапазона «АЗС_Данные», одну таблицу на основе имени «АЗС_Дата» и одну таблицу на основе исходной таблица АЗС. В каждой из них выведем наименования номенклатурной группы и реализацию в литрах. На листе «Свод АЗС 2» разместим три таблицы – одну на основе диапазона «АЗС_Дата», вторую – на основе диапазона «АЗС_Данные» и третью – на основе таблицы АЗС. Как и на первом листе, выведем в них реализацию в литрах по наименованиям номенклатуры. Кроме этого, на каждом листе скопируем в виде значений исходные отчеты, а также присвоим соответствующие имена таблицам. На первом листе таблицы получат названия «АЗС Данные 1», «АЗС Данные 2», «АЗС Дата 1» и «Таблица АЗС 1» соответственно. Сводные таблицы на втором листе получат аналогичные названия, но без нумерации. На изображении ниже размещение таблиц на листе «Свод АЗС 1» показано наглядно.

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

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

Проверим первый вариант настройки обновления сводных таблиц при активации листа с ними. Настроим обновление всех сводных таблиц на выбранном листе и в качестве объекта эксперимента выберем лист «Свод АЗС 1». Нажимаем на его корешке правой кнопкой и выбираем команду «Просмотреть код».

Запустится редактор VBA и откроется модуль текущего листа. В выпадающем списке слева выбираем объект WorkSheet, а справа – событие Activate.

Вставляем в процедуру Worksheet_Activate() следующий код

Dim Pt As PivotTable

For Each Pt In ActiveSheet.PivotTables

Разберем его по порядку. Вначале объявляется переменная Pt с типом PivotTable (сводная таблица). Затем перебираются все элементы коллекции PivotTables с помощью подставления каждой из них в созданную переменную и у каждой таблицы обновляется кеш.

Возвратимся в Excel и сделаем небольшие изменения в таблице АЗС. Увеличим в 100 раз (для наглядности) объем реализации в литрах в первой строке и добавим колонку «Средний объем», в которую выведем отношении реализации в литрах к количеству трансакций. Затем вернемся на лист «Свод АЗС 1».

Как видим, данные обновились во всех таблицах, при этом в сводных таблицах, основанных на именах, в списке полей появилось поле «Средний объем», в таблице же, основанной непосредственно на исходном диапазоне, этого не произошло.

Теперь перейдем на лист «Свод АЗС 2». Что мы видим? Все таблицы тоже обновились .

Перейдём ко второму варианту. На листе «Свод АЗС 2» настроим обновление только для выбранной таблицы при активации листа и для начала выберем таблицу, основанную на исходном диапазоне. Напомним, она называется «Таблица АЗС». Снова переходим в код листа, выбираем событие Activate, но вставляем теперь только одну строку.

Вернемся в Excel и уменьшим объем реализации в первой строке в 10 раз. Перейдем на лист «Свод АЗС 2»

Обратите внимание, что теперь обновилась информация по реализации в литрах бензина (92,80) только для указанной в коде таблицы. Перейдем в код листа «Свод АЗС 1» и настроим обновление только таблицы «АЗС Дата 1», основанной на соответствующем именованном диапазоне.

Теперь вернемся на лист AZS, после чего увеличим в 10 раз объем реализации во второй строке (номенклатурная группа «Высокий октан») и добавим колонку «средний чек».

Откроем после этого листы «Свод АЗС 1» и «Свод АЗС 2»:

Как видим произошло обновление всех сводных таблиц, основанных на именованном диапазоне «АЗС_Дата».

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

Dim Ws As Worksheet

Dim Pt As PivotTable

For Each Ws In Worksheets

For Each Pt In Ws.PivotTables

Данный код можно запускать или комбинацией Alt+F8 либо в качестве варианта добавив кнопку на панель быстрого доступа. Напомним вкратце как это сделать.

На вкладке файл открываем параметры Excel и переходим к пункту «Панель быстрого Доступа»

Теперь справа в выпадающем списке «Выбрать команды из» выбираем «Макросы». Находим наш макрос, нажимаем кнопку «Добавить». После этого, выбрав его в правом списке, щелкаем кнопку «Изменить», после чего корректируем название, меняем значок. Закрываем параметры нажатием кнопки ОК и вверху в Excel появляется кнопка, вызывающая наш макрос по обновлению всех сводных таблиц.

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

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

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

Делаем всем удачного дня и успешной работы! До встречи на наших занятиях!