Как изменить диапазон сводной таблицы Excel
Обновлено: 21.11.2024
Обновление сводной таблицы — дело непростое, не так ли? Если вы часто используете сводные таблицы в своей работе, я уверен, вы поймете.
Суть в том, что каждый раз, когда вы добавляете новые данные в исходный лист, вам необходимо обновить исходный диапазон для сводной таблицы, прежде чем обновлять сводную таблицу. А теперь представьте, что если вы добавляете данные в свой исходный лист каждый день, вам придется каждый день обновлять исходный диапазон.
Каждый раз при изменении диапазона сводной таблицы возникает беспорядок. Да, верно, чем чаще вы добавляете данные, тем чаще вам нужно обновлять исходный диапазон.
Итак, вам нужен метод автоматического обновления исходного диапазона при добавлении новых данных.
ПРИМЕЧАНИЕ. Работа со сводными таблицами – это один из ПРОМЕЖУТОЧНЫХ НАВЫКОВ EXCEL.
1. Применить таблицу для автоматического обновления диапазона сводной таблицы
Несколько дней назад я спросил Джона Михалудиса о его совете по сводным таблицам на миллион долларов. Он говорит:
Поверьте мне, это совет на миллион долларов. Применяя таблицу к исходным данным, вам не нужно снова и снова менять исходный диапазон вашей сводной таблицы. Всякий раз, когда вы добавляете новые данные, они автоматически обновляют диапазон сводной таблицы.
Преобразование данных в таблицу перед созданием сводной таблицы
Каждый раз перед созданием сводной таблицы обязательно применяйте таблицу к исходным данным, выполнив следующие действия.
Теперь всякий раз, когда вы добавляете новые данные в свою таблицу данных, она автоматически обновляет диапазон сводной таблицы, и вам просто нужно обновить сводную таблицу.
Преобразование данных в таблицу после создания сводной таблицы
Если у вас уже есть сводная таблица на листе, вы можете выполнить следующие действия, чтобы преобразовать источник данных в таблицу.
- Выберите любую ячейку в источнике данных.
- Используйте сочетание клавиш Control + T или перейдите к → Вставить вкладку → Таблицы → Таблица.
- Появится всплывающее окно с текущим диапазоном данных.
- Нажмите "ОК".
- Теперь выберите любую ячейку в сводной таблице и выберите → Анализ → Данные → Изменить источник данных → Изменить источник данных (раскрывающееся меню).
- Появится всплывающее окно для повторного выбора источника данных или вы также можете ввести имя таблицы в поле ввода диапазона.
- Нажмите "ОК".
- Перейдите на вкладку "Формулы" → "Определенные имена" → "Диспетчер имен".
- Нажав на менеджер имен, вы увидите всплывающее окно.
- В окне диспетчера имен нажмите «Создать», чтобы создать именованный диапазон.
- В окне нового имени введите
- Название нового диапазона. Я использую имя «SourceData».
- Укажите область диапазона. Вы можете указать между текущим листом или книгой.
- Добавьте комментарий для описания именованного диапазона. Введите приведенную ниже формулу в строку ввода «Ссылка на».
- Строка 13: изменить имя исходного листа.
- Строка 14: изменить имя листа сводной таблицы.
- Строка 17: измените имя сводной таблицы.
- Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
- Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
- Выберите любую ячейку в сводной таблице.
- На ленте на вкладке "Инструменты для сводных таблиц" нажмите вкладку "Анализ" (в Excel 2010 нажмите вкладку "Параметры").
- В группе "Данные" щелкните верхнюю часть команды "Изменить источник данных".
- В окне «Изменить источник данных сводной таблицы» введите новую конечную строку в ссылке на диапазон — в этом примере измените значение с «Заказы!$A$1:$H$ 9» на «Заказы!$A$1:$H$ 10<». /li>
- Нажмите "ОК", чтобы закрыть окно.
- Выберите любую ячейку в сводной таблице.
- На ленте на вкладке "Инструменты для сводных таблиц" нажмите вкладку "Анализ" (в Excel 2010 нажмите вкладку "Параметры").
- В группе "Данные" щелкните верхнюю часть команды "Изменить источник данных".
- Откроется диалоговое окно «Изменить источник данных сводной таблицы», и вы увидите исходную таблицу или диапазон в поле «Таблица/диапазон». На этом снимке экрана ниже текущим источником данных является таблица Sales_Data, которая содержит данные из обоих регионов.
- Выберите любую ячейку в сводной таблице.
- На ленте на вкладке "Инструменты для сводных таблиц" нажмите вкладку "Анализ" (в Excel 2010 нажмите вкладку "Параметры").
- В группе "Данные" щелкните верхнюю часть команды "Изменить источник данных".
- В диалоговом окне "Изменить источник данных сводной таблицы" снимите флажок "Таблица/диапазон".
- В поле Таблица/диапазон введите имя созданной таблицы или именованного диапазона. В этом примере динамическим источником является именованная таблица — tblOrders.
- Нажмите "ОК".
- Файл будет больше
- Файл может открываться быстрее
- Заливка будет меньше
- Файл может открываться медленно во время перестройки сводного кеша
- Сводная таблица должна обновляться после открытия файла
- Щелкните правой кнопкой мыши ячейку в сводной таблице и выберите "Параметры сводной таблицы".
- На вкладке "Данные" в разделе "Данные сводной таблицы" установите или снимите флажок "Сохранить исходные данные в файле".
- Нажмите "ОК".
- "Отчет сводной таблицы был сохранен без базовых данных. Используйте команду "Обновить данные", чтобы обновить отчет."
- Убедитесь, что ни один из элементов в полях сводной таблицы не скрыт. Снимите все примененные фильтры и слайсеры.
- Сводная таблица не обязательно должна содержать все поля — просто убедитесь, что в области значений есть хотя бы одно поле.
- Показать общие итоги по строкам и столбцам. Если итоги не отображаются, выберите ячейку в сводной таблице и на ленте в разделе Инструменты сводной таблицы щелкните вкладку Анализ. В группе «Макет» нажмите «Общие итоги», затем нажмите «Вкл.» для строк и столбцов.
- Дважды щелкните ячейку общей суммы в правом нижнем углу сводной таблицы. Это должно создать новый лист со связанными записями из исходных исходных данных.
- Переименуйте таблицу, используя имя исходных исходных данных, если оно известно.
- Если исходные исходные данные содержали формулы, вам придется создать их заново, поскольку функция "Показать подробности" экспортирует только данные.
- Столбцы в извлеченных данных будут располагаться в том же порядке, что и в исходных исходных данных.
- Извлеченные данные будут отформатированы в формате таблицы по умолчанию для рабочей книги. Вы можете применить другой формат таблицы или применить собственное форматирование.
- Примечание. Если вы внесли изменения в исходные данные и не обновили сводную таблицу, эти изменения не будут отражены в извлеченных данных.
Теперь у вас есть динамический диапазон для создания сводной таблицы. Все, что вам нужно сделать, это просто создать сводную таблицу с вашими исходными данными и после этого изменить источник с именованным диапазоном, используя тот же метод, который я использовал в первом методе таблиц. Как только вы добавите новые данные в исходный лист, просто обновите сводную таблицу.
Как работает эта формула?
В приведенной выше формуле я использовал функцию смещения для создания динамического диапазона. Я упомянул ячейку A1 в качестве отправной точки, а затем, не упоминая строки и столбцы, указал высоту и ширину диапазона с помощью функции COUNTA.
COUNTA подсчитает ячейки со значениями из столбца A и строки 1 и сообщит смещению, чтобы соответственно увеличить его высоту и ширину.Единственное, что вы должны позаботиться о том, чтобы между столбцом A и строкой 1 не было пустой ячейки.
3. Обновление сводной таблицы с помощью кода VBA
Большинство людей любят использовать коды VBA. Итак, вот код, который можно использовать для обновления диапазона сводной таблицы с помощью VBA.
Что нужно изменить, прежде чем использовать его в своей книге.
Если у вас все еще есть проблемы с использованием этого кода, напишите мне в поле для комментариев. Теперь позвольте мне показать вам, как работает этот код, чтобы вы могли легко изменить его в соответствии со своими потребностями.
В приведенной выше части кода мы указали переменные для сводной таблицы и листа исходных данных. Здесь вы можете изменить имя рабочего листа.
В приведенной выше части кода введите имя сводной таблицы, для которой вы хотите использовать этот код.
В приведенной выше части кода будет создан динамический диапазон с использованием ячейки A1 из рабочего листа источника данных. Он проверит последний столбец и последнюю строку с данными, чтобы создать динамический диапазон. Каждый раз, когда вы запускаете этот макрос, он создает новый динамический диапазон.
Приведенная выше часть кода обновит сводную таблицу и покажет пользователю сообщение о том, что сводная таблица теперь обновлена.
В зависимости от типа изменения исходных данных, используемых для сводной таблицы, вам может понадобиться изменить источник данных сводной таблицы или просто изменить диапазон сводной таблицы.
Изменить источник и диапазон данных сводной таблицы
В большинстве случаев обычно добавляют дополнительные строки в исходные данные для размещения новых записей или записей.
Аналогично вам может понадобиться полностью реорганизовать исходные данные, добавив новые строки данных и новые столбцы данных.
В этом случае вам придется изменить диапазон данных сводной таблицы, чтобы включить новые добавленные столбцы и строки в исходные данные.
Однако, если исходные данные поступают на новый лист, вам потребуется изменить источник данных сводной таблицы со старого на новую электронную таблицу.
1. Изменить диапазон данных сводной таблицы
Каждый раз, когда к исходным данным добавляются новые строки или столбцы, вы можете выполнить следующие шаги, чтобы изменить диапазон данных сводной таблицы.
<р>1. Щелкните любую ячейку в сводной таблице, чтобы открыть вкладки «Анализ» и «Дизайн» в верхней строке меню. <р>2. Затем нажмите вкладку «Анализ» > «Изменить источник данных» > «Изменить источник данных…» в раскрывающемся меню.<р>3. В появившемся диалоговом окне «Изменить источник данных сводной таблицы» щелкните поле «Таблица/диапазон» и выберите весь диапазон данных (включая новые строки и столбцы), который вы хотите включить.
<р>3. Нажмите OK, чтобы сохранить изменения.
2. Лист изменения источника данных сводной таблицы
Если исходные данные для сводной таблицы появились на новом листе, вы можете выполнить следующие действия, чтобы изменить источник данных сводной таблицы.
<р>1. Щелкните любую ячейку в сводной таблице, чтобы открыть вкладки «Дизайн» и «Анализ» в верхней строке меню. <р>2. Нажмите «Анализ» > «Изменить источник данных» > «Изменить источник данных» в раскрывающемся меню.<р>3. В появившемся диалоговом окне «Источник данных сводной таблицы» щелкните поле «Таблица/диапазон» и щелкните рабочий лист, содержащий новые исходные данные.
Как вы можете видеть на изображении выше, поле «Таблица/диапазон» относится к рабочему листу «Продажи за январь», и нажатие на «Продажи за февраль» изменит источник данных для сводной таблицы на новый рабочий лист.
После изменения источника данных убедитесь, что диапазон данных включает все строки и столбцы, которые необходимо включить в сводную таблицу.
Как обновить диапазон сводной таблицы в Excel?
В Excel при удалении или добавлении строк или столбцов в диапазон данных относительная сводная таблица не обновляется одновременно. Теперь этот учебник расскажет вам, как обновить сводную таблицу при изменении строк или столбцов таблицы данных.
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!
Выполните следующие действия, чтобы обновить диапазон сводной таблицы.
<р>1. После изменения диапазона данных щелкните относительную сводную таблицу и нажмите «Параметры» (в Excel 2013 нажмите «АНАЛИЗ») > «Изменить источник данных». Смотрите скриншот:<р>2. Затем во всплывающем диалоговом окне выберите новый диапазон данных, который необходимо обновить. Смотрите скриншот:
<р>3. Нажмите «ОК». Теперь сводная таблица обновлена.
Примечание. Строки добавляются в нижней части данных исходной таблицы, а столбцы добавляются в самом правом углу. Диапазон сводной таблицы будет обновляться при нажатии кнопки «Параметры» (или «Анализ») > «Изменить источник данных».
Как найти и изменить исходные данные для сводной таблицы Excel. Для отсутствующих исходных данных, шаги, чтобы попытаться воссоздать их. Измените параметры, чтобы сохранить исходные данные с файлом сводной таблицы.
ПРИМЕЧАНИЕ. Макросы, помогающие управлять исходными данными, можно найти на странице макросов исходных данных сводной таблицы. Чтобы макрос развернул исходные данные, перейдите на страницу "Развернуть макрос данных Excel".
Найти и исправить исходные данные
В этом видео вы увидите, как найти источник данных сводной таблицы, а затем проверить источник данных, чтобы убедиться, что он содержит все нужные вам строки и столбцы. При необходимости настройте источник данных, чтобы включить новые строки или столбцы.
ПРИМЕЧАНИЕ. В качестве долгосрочного решения используйте именованную таблицу Excel в качестве источника данных. Он будет корректироваться автоматически при добавлении новых строк.
Письменные инструкции находятся под видео, и вы можете скачать образец файла, чтобы следовать ему вместе с видео.
Найдите исходные данные
После создания сводной таблицы может потребоваться добавить новые записи в исходные данные или изменить существующие записи. В большой книге может быть сложно найти точный источник сводной таблицы, если есть несколько таблиц или списков.
Выполните следующие действия, чтобы найти исходные данные для сводной таблицы:
Откроется диалоговое окно «Изменить источник данных сводной таблицы», и вы увидите исходную таблицу или диапазон в поле «Таблица/диапазон». Это может быть ссылка на лист и диапазон ячеек, например
или имя таблицы, например
За диалоговым окном вы можете видеть исходный диапазон на листе, окруженный движущейся рамкой.
На приведенном ниже снимке экрана диапазон исходных данных заканчивается в строке 9, а в строке 10 добавлена новая запись. Эта запись не появится в сводной таблице, пока не будет скорректирован источник данных.
Настройка диапазона исходных данных
Если исходные данные сводной таблицы являются статической ссылкой на конкретный лист и диапазон, они не корректируются автоматически при добавлении новых данных. На приведенном выше снимке экрана диапазон источников данных относится к Orders!$A$1:$H$9, и его можно настроить вручную, включив в него строку 10.
Чтобы вручную настроить диапазон статического источника:
При необходимости вы можете изменить исходный диапазон, чтобы он включал любые новые данные, но лучшим решением будет создание динамического источника и создание сводной таблицы на его основе. Инструкции приведены ниже.
Изменить исходные данные
Иногда вам может понадобиться изменить сводную таблицу, чтобы она использовала другой источник данных. В этом примере была создана новая таблица с данными только для Восточного региона. Сводная таблица будет изменена для использования этого источника, а затем ее можно будет отправить менеджеру в восточном регионе без раскрытия данных западного региона.
ПРИМЕЧАНИЕ. После перехода к другой исходной таблице может потребоваться переименовать некоторые поля или добавить другие поля в сводную таблицу.
Чтобы изменить исходные данные для сводной таблицы Excel, выполните следующие действия:
Динамический источник для сводной таблицы
Вместо использования статического диапазона в качестве источника данных сводной таблицы лучшим решением будет создание динамического диапазона, размер которого будет изменяться автоматически.
Динамический источник – таблица Excel
Вместо использования статического диапазона в качестве источника данных сводной таблицы лучше создать динамический диапазон на основе именованной таблицы Excel. Здесь есть письменные инструкции, или посмотрите видео ниже.
Запишите название таблицы, чтобы использовать ее в качестве источника сводной таблицы
Динамический источник — именованный диапазон
Если вы не можете использовать таблицу Excel в качестве исходных данных, вы создаете динамический именованный диапазон на основе формулы ИНДЕКС или СМЕЩ. Здесь есть письменные инструкции, или посмотрите видео ниже.
В видеоролике показано, как создать динамический диапазон с помощью функции СМЕЩ в Excel 2007, и шаги аналогичны в более поздних версиях.
ПРИМЕЧАНИЕ. Запомните название диапазона, чтобы использовать его в качестве источника сводной таблицы
Динамический источник — динамический массив
Если у вас есть версия Excel, поддерживающая новые функции, такие как СОРТИРОВКА или УНИКАЛЬНОСТЬ, вы можете использовать эти функции для создания динамических массивов. В этом методе используются только формулы, без макросов.
В разделе "Загрузка" получите файл примера отфильтрованных исходных данных. В нем показано, как настроить именованный диапазон только с видимыми строками из именованной таблицы Excel.
Вот отфильтрованные данные на другом листе, только с 2 повторениями и 3 категориями из видимых строк.
Затем вы можете создать сводную таблицу только на основе этих отфильтрованных данных.
Использовать динамический источник для сводной таблицы
После создания динамического источника, содержащего данные сводной таблицы, выполните следующие действия, чтобы использовать эти исходные данные:
Сохранить исходные данные с помощью сводной таблицы
Когда вы создаете сводную таблицу в своей книге, записи исходных данных сохраняются в специальной области памяти — сводном кеше. Затем, когда вы закроете файл, Excel сможет сохранить исходные данные в этом сводном кэше или очистить эту память.
У обоих вариантов есть свои преимущества и недостатки:
Если вы сохраните исходные данные:
Если вы не сохраните исходные данные:
Как изменить настройку
Чтобы включить или отключить параметр "Сохранить исходные данные":
Обновлять данные при открытии
Если вы решите отключить параметр «Сохранить исходные данные в файле», вам следует включить параметр «Обновлять данные при открытии файла». Этот параметр находится чуть ниже флажка "Сохранить исходные данные".
В противном случае вы увидите сообщение при попытке отфильтровать данные или внести любые другие изменения макета.
Если вы видите это сообщение, нажмите "ОК", а затем вручную обновите сводную таблицу.
Чтобы избежать раздражающего сообщения, включите параметр "Обновлять данные при открытии файла".
Восстановить исходные данные сводной таблицы
Если вы случайно удалили рабочий лист с исходными данными для сводной таблицы или получили файл без данных сводной таблицы, вы можете использовать функцию "Показать подробности" сводной таблицы, чтобы воссоздать его.
ПРИМЕЧАНИЕ. Это не будет работать для всех сводных таблиц, но стоит попробовать, чтобы восстановить исходные данные.
Исправить извлеченные данные
Если вы восстанавливаете исходные данные, вам может потребоваться внести некоторые дополнительные изменения, чтобы восстановить их до исходного состояния.
Подключение к извлеченным данным
Если вы переименуете таблицу, созданную во время процесса "Показать подробности", и используете то же имя, что и у таблицы, которая изначально содержала исходные данные, сводная таблица может автоматически подключиться к новым исходным данным.
Если нет, вы можете подключиться к воссозданным исходным данным — выполните действия, описанные в разделе выше: Использовать динамический источник для сводной таблицы — используйте имя таблицы, которое вы дали новой таблице.
Загрузить образец файла
Пример сводного источника. Чтобы следовать инструкциям, вы можете загрузить файл DateAmt.zip. Заархивированный файл имеет формат xlsx и не содержит макросов.
Пример отфильтрованных исходных данных. Этот пример относится к версиям Excel с новыми функциями, такими как СОРТИРОВКА и УНИКАЛЬНОСТЬ. Загрузите этот образец файла, чтобы увидеть, как сводная таблица создается только из видимых строк в отфильтрованной таблице Excel. Заархивированный файл имеет формат xlsx и не содержит макросов.
Читайте также:
С этого момента каждый раз, когда вы добавляете новые данные в исходный лист, диапазон сводной таблицы будет увеличиваться для автоматического обновления.
2. Создайте диапазон динамической сводной таблицы с функцией OFFSET
Другой лучший способ автоматического обновления диапазона сводной таблицы – использование динамического диапазона. Динамический диапазон может автоматически расширяться всякий раз, когда вы добавляете новые данные в исходный лист. Ниже приведены шаги для создания динамического диапазона.