Как создать динамическую сводную таблицу для автоматического обновления расширяющихся данных в Excel?
Обычно сводная таблица может быть обновлена с учетом измененных данных в исходном диапазоне. Однако, если вы добавите новые данные в исходный диапазон, например, новые строки или столбцы внизу или справа от исходного диапазона, эти расширяющиеся данные не будут добавлены в сводную таблицу даже при ручном обновлении. Как обновить сводную таблицу с учетом расширяющихся данных в Excel? Методы, описанные в этой статье, помогут вам.
Создание динамической сводной таблицы путем преобразования исходного диапазона в табличный диапазон
Создание динамической сводной таблицы с использованием формулы СМЕЩ
Создание динамической сводной таблицы путем преобразования исходного диапазона в табличный диапазон
Преобразование исходных данных в таблицу поможет обновить сводную таблицу с учетом расширяющихся данных в Excel. Пожалуйста, следуйте инструкциям ниже.
1. Выберите диапазон данных и одновременно нажмите клавиши Ctrl + T. В открывшемся диалоговом окне Создать таблицу нажмите кнопку ОК.
2. Затем исходные данные будут преобразованы в табличный диапазон. Продолжая выделение табличного диапазона, нажмите Вставка > Сводная таблица.
3. В окне Создание сводной таблицы выберите место размещения сводной таблицы и нажмите ОК (в данном случае я размещаю сводную таблицу на текущем листе).
4. В панели Полей сводной таблицы перетащите поля в соответствующие области.
5. Теперь, если вы добавите новые данные внизу или справа от исходного диапазона, перейдите к сводной таблице, щелкните её правой кнопкой мыши и выберите Обновить из контекстного меню.
Вы увидите, что сводная таблица обновилась с учетом расширяющихся данных, как показано на скриншоте ниже.
Создание динамической сводной таблицы с использованием функции СМЕЩ
В этом разделе я покажу вам, как создать динамическую сводную таблицу с помощью функции СМЕЩ.
1. Выберите исходный диапазон данных, нажмите Формулы > Диспетчер имен. Смотрите скриншот:
2. В окне Диспетчер имен нажмите кнопку Создать, чтобы открыть диалоговое окно Изменить имя. В этом диалоговом окне вам нужно:
- Введите имя для диапазона в поле Имя;
- Скопируйте приведенную ниже формулу в Ссылается на поле;
=СМЕЩ('dynamic pivot with table'!$A$1,0,0,СЧЁТЗ('dynamic pivot with table'!$A:$A),СЧЁТЗ('dynamic pivot with table'!$1:$1)) - Нажмите кнопку ОК.
Примечание: В формуле 'dynamic pivot with table' — это имя рабочего листа, содержащего исходный диапазон; $A$1 — первая ячейка диапазона; $A:$A — первый столбец диапазона; $1:$1 — первая строка диапазона. Пожалуйста, измените их в соответствии с вашим собственным исходным диапазоном данных.
3. Затем вернитесь в окно Диспетчер имен, вы увидите новое созданное имя диапазона, отображаемое в окне, пожалуйста, закройте его.
4. Нажмите Вставка > Сводная таблица.
5. В окне Создание сводной таблицы введите имя диапазона, которое вы указали на шаге 2, выберите место размещения сводной таблицы и нажмите кнопку ОК.
6. В панели Полей сводной таблицы перетащите поля в соответствующие области.
7. После добавления новых данных в исходный диапазон данные в сводной таблице будут обновлены при нажатии на опцию Обновить.
Связанные статьи
Подсчет уникальных значений в сводной таблице
По умолчанию, когда мы создаем сводную таблицу на основе диапазона данных, который содержит некоторые повторяющиеся значения, все записи также будут подсчитаны, но иногда нам нужно подсчитать только уникальные значения на основе одного столбца, чтобы получить правильный результат. Эта статья предоставляет несколько методов, которые покажут вам, как подсчитывать уникальные значения в сводной таблице.
Разместить метки строк на одной линии в сводной таблице
После создания сводной таблицы в Excel вы увидите, что метки строк перечислены только в одном столбце. Но если вам нужно разместить метки строк на одной линии для более интуитивного и четкого просмотра данных, как можно настроить макет сводной таблицы в Excel? Методы, описанные в этой статье, помогут вам.
Скрыть строки с нулевыми значениями в сводной таблице
Скрытие строк, содержащих нулевые значения в сводной таблице, может быть распространенной потребностью пользователей. После скрытия строк с нулевыми значениями ваша сводная таблица будет выглядеть аккуратно, а данные также будут сжаты. Пожалуйста, прочтите руководство для получения методов.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек