Как изменить диапазон сводной таблицы в Microsoft Excel

Способ 1: Изменение диапазона через вкладку «Анализ»

Стандартный инструмент для изменения диапазона сводной таблицы в Microsoft Excel находится на вкладке «Анализ», которая становится доступной после выделения любой ячейки сводной таблицы. Данный метод подходит для расширения или сокращения исходного диапазона данных, когда в таблицу добавились новые строки или, наоборот, часть информации стала неактуальной. Вы можете воспользоваться этой инструкцией независимо от того, находятся ли исходные данные на том же листе, что и сводная таблица, или на другом листе книги.

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

2. На ленте появится дополнительная группа вкладок, где перейдите на вкладку «Анализ сводной таблицы».

3. В группе «Данные» найдите кнопку «Источник данных» и нажмите на нее.

4. Откроется диалоговое окно «Изменение источника данных сводной таблицы». В поле «Таблица или диапазон» отобразится текущий диапазон исходных данных.
5. Можете вручную отредактировать диапазон, изменив адреса ячеек в поле ввода. Например, если текущий диапазон A1:E10, а вам нужно включить данные до 20-й строки, измените его на A1:E20.

6. Альтернативный вариант — нажмите на кнопку со стрелкой справа от поля ввода, чтобы свернуть диалоговое окно, затем выделите мышью нужный диапазон на листе. Диапазон автоматически отобразится в поле.

7. После указания нового диапазона нажмите «OK». Сводная таблица мгновенно обновится с учетом новых данных.

Обратите внимание, что при использовании этого метода не забудьте после изменения диапазона обновить сводную таблицу, если данные в исходном диапазоне уже были изменены. Для этого щелкните правой кнопкой мыши по сводной таблице и выберите «Обновить», или нажмите комбинацию клавиш Alt + F5.

Способ 2: Создание сводной таблицы на основе форматированной таблицы

Форматированные таблицы (также известные как «умные таблицы») автоматически расширяют свой диапазон при добавлении новых данных в смежные ячейки, что делает работу со сводными таблицами значительно удобнее. Если сводная таблица создана на основе форматированной таблицы, вам не придется каждый раз вручную корректировать диапазон — достаточно будет просто обновить сводную таблицу. Рекомендуем применять данную технику при работе с регулярно пополняемыми данными, такими как ежедневные отчеты по продажам или учет расходов.

1. Если у вас еще нет сводной таблицы, сначала преобразуйте исходный диапазон данных в форматированную таблицу. Выделите любую ячейку внутри диапазона данных.

2. Перейдите на вкладку «Вставка» и нажмите кнопку «Таблица» в группе «Таблицы».

3. В появившемся диалоговом окне «Создание таблицы» проверьте автоматически определенный диапазон и убедитесь, что установлен флажок «Таблица с заголовками».

4. Нажмите «OK». Диапазон будет преобразован в форматированную таблицу с характерным дизайном и автоматически присвоенным именем (например, «Таблица1»).

5. Теперь создайте сводную таблицу обычным способом: перейдите на вкладку «Вставка» — «Сводная таблица».

6. В диалоговом окне создания сводной таблицы в поле «Таблица или диапазон» вместо адреса диапазона вы увидите имя форматированной таблицы (например, «Таблица1»).

7. При добавлении новых строк в форматированную таблицу диапазон будет расширяться автоматически. Чтобы новые данные отобразились в сводной таблице, просто обновите ее через правый клик мыши — «Обновить».

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

Способ 3: Использование динамического именованного диапазона

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

1. Перейдите на вкладку «Формулы» и нажмите кнопку «Диспетчер имен» в группе «Определенные имена».

2. В открывшемся окне диспетчера имен нажмите кнопку «Создать».

3. В поле «Имя» введите понятное название для вашего диапазона, например, «ДанныеОтчета» или «ИсходныеДанные».

4. В поле «Диапазон» введите формулу, которая будет динамически определять размер диапазона. Для таблицы, начинающейся с ячейки A1, используйте формулу: =СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A:$A);СЧЁТЗ(Лист1!$1:$1)).
5. Разберем компоненты формулы: функция СМЕЩ создает диапазон от начальной ячейки A1, параметры 0;0 означают отсутствие смещения, а функции СЧЁТЗ автоматически подсчитывают количество заполненных ячеек в столбце A (определяет количество строк) и в первой строке (определяет количество столбцов).

6. Нажмите «OK» для сохранения именованного диапазона.

7. При создании сводной таблицы в поле «Таблица или диапазон» введите имя созданного динамического диапазона (например, «ДанныеОтчета») вместо обычной ссылки на ячейки.

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

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

Способ 4: Автоматическое обновление сводной таблицы при открытии файла

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

1. Щелкните левой кнопкой мыши по любой ячейке сводной таблицы, чтобы активировать инструменты работы с ней.

2. Перейдите на вкладку «Анализ сводной таблицы» и найдите группу «Сводная таблица».

3. Нажмите на кнопку «Параметры» в левой части ленты — откроется дополнительное окно с настройками.

4. Перейдите на вкладку «Данные» в этом окне.

5. Установите флажок «Обновить при открытии файла». Эта настройка обеспечит автоматическое обновление сводной таблицы каждый раз при открытии книги Excel.

6. Нажмите «OK» для сохранения настроек, затем проверьте действие данной опции, повторно открыв ту же самую книгу.

Стоит отметить, что автоматическое обновление при открытии файла может замедлить процесс открытия больших книг Excel с объемными сводными таблицами. Если файл содержит несколько сводных таблиц на основе больших массивов данных, время загрузки может увеличиться на несколько секунд. В таких случаях рассмотрите возможность отключения автоматического обновления и используйте команду «Обновить все» на вкладке «Данные» по мере необходимости.

Способ 5: Расширение диапазона путем выделения всего столбца

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

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

2. В диалоговом окне «Изменение источника данных сводной таблицы» вместо обычного диапазона введите ссылку на целые столбцы. Например, если ваши данные находятся в столбцах от A до E, введите Лист1!$A:$E.

3. Нажмите «OK». Теперь сводная таблица будет охватывать все ячейки в указанных столбцах, включая те, которые будут заполнены в будущем.
4. После добавления новых данных в таблицу просто обновите сводную таблицу, и новая информация автоматически отобразится в отчете.

При использовании целых столбцов убедитесь, что в этих столбцах нет лишних данных ниже основной таблицы, иначе они тоже попадут в сводную таблицу и исказят результаты. Также первая строка диапазона обязательно должна содержать заголовки столбцов — Excel использует их для определения полей сводной таблицы.

Способ 6: Использование Power Query для динамического источника данных

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

1. Выделите любую ячейку в исходной таблице с данными. Перейдите на вкладку «Данные» и в группе «Получить и преобразовать данные» нажмите «Из таблицы/диапазона».
2. Если диапазон еще не преобразован в форматированную таблицу, появится диалоговое окно «Создание таблицы». Проверьте диапазон и убедитесь, что установлен флажок «Таблица с заголовками», затем нажмите «OK».

3. Откроется окно редактора Power Query. Здесь можете при необходимости выполнить дополнительные преобразования данных: фильтрацию, удаление дубликатов, изменение типов данных.

4. На вкладке «Главная» в редакторе Power Query нажмите «Закрыть и загрузить в».

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

6. Нажмите «OK». Сводная таблица будет создана на основе запроса Power Query.
7. При добавлении новых строк в исходную таблицу для обновления сводной таблицы перейдите на вкладку «Данные» и нажмите «Обновить все». Power Query автоматически загрузит обновленные данные из источника.

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