Как создать динамическую сводную таблицу, которая будет автоматически обновляться при расширении данных в Excel?
Обычно Сводная таблица можно обновить актуальными данными из диапазона Исходные данные. Однако если вы добавляете новые данные в Исходный диапазон, например новые строки или столбцы внизу или справа от Исходный диапазон, эти расширенные данные не будут включены в Сводная таблица, даже если выполнить обновление Сводная таблица вручную. Как обновить Сводная таблица с учётом расширяющихся данных в Excel? Методы из этой статьи помогут вам решить эту задачу.
Создайте динамический Сводная таблица, преобразовав Исходный диапазон в таблицу
Создайте динамический Сводная таблица, используя формулу СМЕЩ (OFFSET)
Создайте динамический Сводная таблица, преобразовав Исходный диапазон в таблицу
Преобразование исходных данных в таблицу позволяет обновлять сводную таблицу с учётом расширения данных в Excel. Выполните следующие действия.
1. Выделите диапазон данных и одновременно нажмите клавиши Ctrl+T. В открывшемся диалоговом окне Создание таблицы нажмите кнопку OK.
2. Теперь исходные данные преобразованы в диапазон таблицы. Не снимая выделения с этого диапазона, выберите Вставка > Сводная таблица.
3. В окне Создание сводной таблицы выберите место размещения сводной таблицы и нажмите кнопку OK (в данном случае сводная таблица размещена на текущем листе).
4. На панели Поля сводной таблицы перетащите поля в соответствующие области.
5. Теперь, если вы добавите новые данные внизу или справа от исходного диапазона, перейдите к сводной таблице, щёлкните её правой кнопкой мыши и выберите команду Обновить в контекстном меню.
Теперь вы увидите, что сводная таблица обновилась с учётом расширенных данных, как показано на снимке экрана ниже.
Создайте динамическую Сводная таблица, используя функцию СМЕЩ (OFFSET)
В этом разделе я покажу, как создать динамическую сводную таблицу с помощью функции СМЕЩ (OFFSET).
1. Выделите диапазон «Исходные данные» и выберите Формулы > Менеджер имён. См. снимок экрана:
2. В окне Менеджер имен нажмите кнопку Создать, чтобы открыть диалоговое окно Редактировать имя. В этом диалоговом окне вам необходимо:
- Введите имя диапазона в поле Имя;
- Скопируйте приведённую ниже формулу в поле Ссылка на;
=OFFSET('dynamic pivot with table'!$A$1,0,0,COUNTA('dynamic pivot with table'!$A:$A),COUNTA('dynamic pivot with table'!$1:$1)) - Нажмите кнопку OK.
Примечание: В формуле «dynamic pivot with table» — это имя листа, содержащего исходный диапазон; $A$1 — первая ячейка диапазона; $A$A — первый столбец диапазона; $1$1 — первая строка диапазона. Замените их на значения, соответствующие вашему собственному диапазону исходных данных.
3. Затем вы вернётесь в окно Менеджер имен, где увидите вновь созданное имя диапазона. Закройте это окно.
4. Выберите Вставка > Сводная таблица.
5. В окне Создание сводной таблицы введите имя ячейки, указанное на шаге 2, выберите место размещения сводной таблицы и нажмите кнопку OK.
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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек