Flatik.ru

Перейти на главную страницу

Поиск по ключевым словам:

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

Сергей Багузин

Для удобства изучения трюка используйте Excel-файл (формат 2007).

При добавлении строк в исходные данные для корректной работы сводной таблицы необходимо увеличить область источника данных. Меню Работа со сводными таблицами  Параметры  Изменить источник данных:

В открывшемся окне «Изменить источник данных сводной таблицы» в области Таблица или диапазон ввести новые данные (расширить диапазон):



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



  • возрастает объем анализируемых строк (начиная с версии 2007 на листе Excel более 1 млн. строк!), что замедляет работу и съедает ресурсы ПК… но это полбеды…

  • не всегда получается группировать данные; перейдите на лист «св1» Excel-файла, меню Работа со сводными таблицами  Параметры  Группировка по выделенному:

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



Существует прием, который позволяет:

  • группировать данные в сводных таблицах, и при этом…

  • не думать о расширении области данных при добавлении строк в исходные данные.

Для этого на основе исходных данных надо создать именованный массив, а область его определения задать формулой.

  1. В любой ячейке на листе Excel напишите параметры функции СМЕЩ:

Функция СМЕЩ позволяет задать область на листе Excel. Параметры функции:

Ссылка – $A$1 (абсолютная) – определяет верхний левый угол области;

Смещ_по_строкам – 0 – всегда ставьте ноль и не думайте, что он означет! 

Смещ_по_стролбцам – 0 – всегда ставьте ноль;

Высота – СЧЁТЗ($A$1:$A$4000) – обязательно абсолютные ссылки; начинается в левом верхнем углу области ($A$1), задайте нижний предел – 4000 – заведомо достаточный для того чтобы в область вошли все новые строки;

Ширина – СЧЁТЗ($1:$1) – обязательно абсолютные ссылки; подсчитает число столбцов с заголовками.


  1. Скопируйте получившийся текст функции, и создайте новое имя массива. Меню Формулы  Диспетчер имен  Создать:





Имя – приход – задайте короткое «говорящее» имя области данных;

Примечание – можно не заполнять, но, если используете файл редко, лучше заполнить, чтобы через месяц вспомнили, о чем речь! 

Диапазон – вставьте ранее скопированную в буфер функцию СМЕЩ

Получилось:



  1. Проверьте, что имя области работает правильно. Наберите имя области, как показано ниже (выделено красным), нажмите Enter, область с именем «приход» выделиться:



  1. Перейдите на лист «св2». Измените источник данных сводной таблицы. Меню Работа со сводными таблицами  Параметры  Изменить источник данных:

В открывшемся окне «Изменить источник данных сводной таблицы» в области Таблица или диапазон введите имя диапазона:





  1. Проверьте, что добавление новых данных позволяет автоматически «подцеплять» их при обновлении сводной: перенесите новые данные с листа «дополнительные данные» на лист «исходные данные». Перейдите на лист «св2» и обновите сводную таблицу. В сводной добавилась строка с данными за 20 апреля.

Вуаля! 

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

При добавлении строк в исходные данные для корректной работы сводной таблицы необходимо увеличить область источника данных. Меню Работа со сводными таблицами  Параметры  Изменить

25.01kb.

14 12 2014
1 стр.


Сводной таблицы «Результаты рейтинговых оценок качества пищевой продукции за период 2004-2011гг.»
321.13kb.

08 10 2014
1 стр.


Формулировка задания: ms excel. Создание таблиц. Ввод формул

Цель работы: Знакомство с основными навыками работы в электронных таблицах: ввод данных в ячейки таблицы, форматирование ячеек таблицы, ввод простых формул, создание обрамления, ре

359.96kb.

14 12 2014
4 стр.


Определение значений исходных данных для оценки устойчивости при действии поражающих факторов ядерного взрыва

Форма рэа и значение соответствующего коэффициента аэродинамического сопротивления: Параллелепипед, Сх 85

34.99kb.

24 09 2014
1 стр.


Занятие 5 Практическое занятие Экономические расчеты в ms excel

Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных

114.98kb.

25 12 2014
1 стр.


Внимание рекомендуемая версия Диспетчера pos устройств: 0 31101 или более поздняя

При создании или обновлении базы данных необходимо включить режим «Использовать версионность строк для читающих запросов»

324.46kb.

11 09 2014
1 стр.


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

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

329.03kb.

14 12 2014
1 стр.


Таблицы таблицы Растения и окружающая среда 2 таблицы

«Организмы: что это такое?»,6 кл. «Клеточное строение организма», 9 кл. «Клеточный уровень»

288.93kb.

12 10 2014
3 стр.