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

Обновлено: 05.01.2025

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

Постановка задачи

Имеется таблица с данными, на основе которой построена Сводная таблица. Чтобы при добавлении новых данных в таблицу они попадали в Сводную при её обновлении, используем пару трюков.

Именованный диапазон данных в комбинации с функцией СМЕЩ

Функция СМЕЩ возвращает ссылку на диапазон заданного размера (высота и ширина), отстоящий от стартовой ячейки на заданное число строк и столбцов.

=СМЕЩ (ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

Аргументы функции:

Ссылка (обязательный аргумент) — ссылка, от которой вычисляется смещение, аргумент должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция возвращает значение ошибки ЗНАЧ! ;
Смещ_по_строкам (обязательный аргумент) — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
Смещ_по_столбцам (обязательный аргумент) — количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
Высота (не обязательный аргумент) — высота (число строк) возвращаемой ссылки, значение должно быть положительным числом.
Ширина (не обязательный аргумент) — ширина (число столбцов) возвращаемой ссылки, значение должно быть положительным числом.

Теперь, посмотрим, как происходит захват диапазона:

Из ячейки C2 опускаемся ниже на две строки (первый аргумент). Затем, двигаемся вправо по столбцам на четыре ячейки до столбца G4 (второй аргумент). Диапазон захвата таблицы с данными 3х3 , последние два аргумента. Справа построена Сводная таблица, на основе захваченного диапазона.

Перейдем к решению исходной задачи. Создадим именованный диапазон, Формулы ► Диспетчер имен ► Создать :

Группировка позволяет выделить для анализа определенное подмножество данных сводной таблицы. Например, можно сгруппировать значения даты или времени (поля даты и времени в сводной таблице) в большом и неудобном списке по кварталам и месяцам, как показано на приведенном ниже изображении.

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

Группировка данных

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

В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.

В разделе С шагом выберите период времени. Для числовых полей введите число, которое указывает интервал для каждой группы.

Группировка выделенных элементов

Удерживая нажатой клавишу CTRL, выделите несколько значений.

Щелкните правой кнопкой мыши и выберите команду Группировать.

Присвоение имени группе

На вкладке Анализ нажмите кнопку Параметры поля.

Измените значение в поле Пользовательское имя и нажмите кнопку "ОК".

Разгруппировка сгруппированных данных

Щелкните правой кнопкой мыши любой элемент в группе.

Выберите команду Разгруппировать.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Обновить диапазон сводной таблицы в Excel

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Выполните следующие действия, чтобы обновить диапазон сводной таблицы.

1. После изменения диапазона данных щелкните соответствующую сводную таблицу и щелкните Опция (в Excel 2013 щелкните АНАЛИЗ )> Изменить источник данных. Смотрите скриншот:

2. Затем во всплывающем диалоговом окне выберите новый диапазон данных, который необходимо обновить. Смотрите скриншот:

3. Нажмите OK. Теперь сводная таблица обновлена.

Внимание: Только строки добавляются в нижнюю часть исходных данных таблицы или столбцы добавляются в самый правый угол, диапазон сводной таблицы обновляется при нажатии Option (или Analyze)> Change Data Source.

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

Изменение или редактирование оси / легенд сводной диаграммы в Excel

На самом деле, очень легко изменить или отредактировать ось и легенды сводной диаграммы в списке файлов в Excel. А сделать можно так:

Шаг 1. Выберите сводную диаграмму, ось и условные обозначения которой вы хотите изменить, а затем отобразите панель «Составленный список», щелкнув значок Поданный список Кнопка на Анализировать меню.

Внимание: По умолчанию панель списка полей открывается при нажатии на сводную диаграмму.

Шаг 2: перейдите к Ось (Категории) раздел или Легенда (Серия) в области «Список файлов» перетащите все поля из области «Список полей».
Затем вы увидите, что все оси или легенды удалены из сводной диаграммы.

Шаг 3: Теперь перейдите к Выберите поля для добавления в отчет на панели Список полей перетащите поле в Ось (Категории) sдействие или Легенда (Серия) .

Внимание: Вы также можете щелкнуть поле правой кнопкой мыши и выбрать Добавить в поля оси (категории) or Добавить в поля легенды (серии) из контекстного меню.

Изменение / редактирование источника данных сводной диаграммы в Excel

Если вы хотите изменить источник данных сводной диаграммы в Excel, вам необходимо разорвать связь между этой сводной диаграммой и ее исходными данными сводной таблицы, а затем добавить для нее источник данных. А сделать можно так:

Шаг 1: Выберите сводную диаграмму, в которой вы измените источник данных, и вырежьте ее, нажав кнопку Ctrl + X ключи одновременно.

Шаг 2. Создайте новую книгу, нажав Ctrl + N одновременно, а затем вставьте вырезанную сводную диаграмму в эту новую книгу, нажав Ctrl + V одновременно.

Шаг 3: Теперь вырежьте сводную диаграмму из новой книги, а затем вставьте ее в исходную книгу.

Шаг 4. Щелкните правой кнопкой мыши вставленную сводную диаграмму в исходной книге и выберите Выберите данные из контекстного меню.

Шаг 5: В появившемся диалоговом окне Select Data Source поместите курсор в Диапазон данных диаграммы поле, а затем выберите новые исходные данные в своей книге и щелкните значок OK кнопку.

Читайте также: