Автоматическое обновление сводной таблицы при увеличении числа строк в исходных данных
Сергей Багузин
Для удобства изучения трюка используйте Excel-файл (формат 2007).
При добавлении строк в исходные данные для корректной работы сводной таблицы необходимо увеличить область источника данных. Меню Работа со сводными таблицами Параметры Изменить источник данных:
В открывшемся окне «Изменить источник данных сводной таблицы» в области Таблица или диапазон ввести новые данные (расширить диапазон):
Чтобы не следить за изменением области данных некоторые пользователи при построении сводных в качестве исходных данных используют не прямоугольную область, а целиком столбцы. У этого подхода есть два недостатка:
-
возрастает объем анализируемых строк (начиная с версии 2007 на листе Excel более 1 млн. строк!), что замедляет работу и съедает ресурсы ПК… но это полбеды…
-
не всегда получается группировать данные; перейдите на лист «св1» Excel-файла, меню Работа со сводными таблицами Параметры Группировка по выделенному:

Группировка дат не получается потому что сводная таблица сделана на основании столбцов, в которых содержатся, как даты, так и пустые строки.
Существует прием, который позволяет:
-
группировать данные в сводных таблицах, и при этом…
-
не думать о расширении области данных при добавлении строк в исходные данные.
Для этого на основе исходных данных надо создать именованный массив, а область его определения задать формулой.
-
В любой ячейке на листе Excel напишите параметры функции СМЕЩ:
Функция СМЕЩ позволяет задать область на листе Excel. Параметры функции:
Ссылка – $A$1 (абсолютная) – определяет верхний левый угол области;
Смещ_по_строкам – 0 – всегда ставьте ноль и не думайте, что он означет!
Смещ_по_стролбцам – 0 – всегда ставьте ноль;
Высота – СЧЁТЗ($A$1:$A$4000) – обязательно абсолютные ссылки; начинается в левом верхнем углу области ($A$1), задайте нижний предел – 4000 – заведомо достаточный для того чтобы в область вошли все новые строки;
Ширина – СЧЁТЗ($1:$1) – обязательно абсолютные ссылки; подсчитает число столбцов с заголовками.
-
Скопируйте получившийся текст функции, и создайте новое имя массива. Меню Формулы Диспетчер имен Создать:
Имя – приход – задайте короткое «говорящее» имя области данных;
Примечание – можно не заполнять, но, если используете файл редко, лучше заполнить, чтобы через месяц вспомнили, о чем речь!
Диапазон – вставьте ранее скопированную в буфер функцию СМЕЩ
Получилось:
-
Проверьте, что имя области работает правильно. Наберите имя области, как показано ниже (выделено красным), нажмите Enter, область с именем «приход» выделиться:
-
Перейдите на лист «св2». Измените источник данных сводной таблицы. Меню Работа со сводными таблицами Параметры Изменить источник данных:
В открывшемся окне «Изменить источник данных сводной таблицы» в области Таблица или диапазон введите имя диапазона:
-
Проверьте, что добавление новых данных позволяет автоматически «подцеплять» их при обновлении сводной: перенесите новые данные с листа «дополнительные данные» на лист «исходные данные». Перейдите на лист «св2» и обновите сводную таблицу. В сводной добавилась строка с данными за 20 апреля.
Вуаля!