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

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

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

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

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

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

• 
  возрастает объем анализируемых строк (начиная с версии 2007 на листе Excel более 1 млн. строк!), что замедляет работу и съедает ресурсы ПК… но это полбеды…
  
• 
  не всегда получается группировать данные; перейдите на лист «св1» Excel-файла, меню Работа со сводными таблицами  Параметры  Группировка по выделенному:
  

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Получилось:

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

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

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

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

Вуаля! 