Наверняка, работая с Сводными таблицами, вы замечали, что при добавлении новых строк или столбцов, они не попадают в сводный отчет при его обновлении. Приходится руками подтягивать новые данные. Давайте разбираться, как автоматизировать этот процесс.
Постановка задачи
Имеется таблица с данными, на основе которой построена Сводная таблица. Чтобы при добавлении новых данных в таблицу они попадали в Сводную при её обновлении, используем пару трюков.
Именованный диапазон данных в комбинации с функцией СМЕЩ
Функция СМЕЩ возвращает ссылку на диапазон заданного размера (высота и ширина), отстоящий от стартовой ячейки на заданное число строк и столбцов.
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
Аргументы функции:
- Ссылка (обязательный аргумент) — ссылка, от которой вычисляется смещение, аргумент должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция возвращает значение ошибки ЗНАЧ!;
- Смещ_по_строкам (обязательный аргумент) — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
- Смещ_по_столбцам(обязательный аргумент) — количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
- Высота (не обязательный аргумент) — высота (число строк) возвращаемой ссылки, значение должно быть положительным числом.
- Ширина(не обязательный аргумент) — ширина (число столбцов) возвращаемой ссылки, значение должно быть положительным числом.
Теперь, посмотрим, как происходит захват диапазона:
Из ячейки C2 опускаемся ниже на две строки (первый аргумент). Затем, двигаемся вправо по столбцам на четыре ячейки до столбца G4 (второй аргумент). Диапазон захвата таблицы с данными 3х3, последние два аргумента. Справа построена Сводная таблица, на основе захваченного диапазона.
Перейдем к решению исходной задачи. Создадим именованный диапазон, Формулы ► Диспетчер имен ► Создать:
Или нажмите Формулы ► Задать имя.
Присвоим диапазону имя "Данные" и введем формулу :
=СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A:$A);СЧЁТЗ(Лист1!$1:$1)), где
- Лист1 - название листа на, котором расположена таблица с данными, может отличаться, если ваш лист называется по другому;
- Лист1!$A$1 - первая ячейка с данными таблицы (шапка);
- Лист1!$A:$A - весь столбец A, по нему формула будет добавлять данные в диапазон, если он будет не заполнен, строка не добавится;
- Лист1!$1:$1 - первая строка таблицы (шапка), это позволит добавлять в диапазон для захвата новые столбцы. Если вам нужны конкретные столбцы, выделите только их.
Нажмем Ок:
Отлично! Теперь заменить ссылку в Сводной таблице на наш именованный диапазон.
Выделим любую ячейку Сводной таблицы, вкладка Анализ ► Источник данных:
В поле Таблица или диапазон, введем = Данные (наш именованный список):
Все! Теперь, если в таблице появятся новые строки или столбцы, они попадут в Сводную при её обновлении.
Источник данных Умная таблица
Проще всего решить задачу можно преобразав источник данных в Умную таблицу и к ней подключить сводную.
Преобразуем рабочую область с данными в Умную таблицу. Встаньте на любую ячейку с данными и нажмите сочетание клавиш Ctrl+T:
Умная таблица, благодаря своим свойствам, автоматически подхватывает добавленные в неё строки и столбцы, вам остается только обновить данные в Сводной таблице, Анализ ► Источник данных:
Готово!
Можно настроить источник данных при помощи Power Query, но по сути это будет Умная таблица, с дополнительными настройками.
Полезно? Лайк, пишите, что думаете в комментариях и подписывайтесь на канал.