Динамические отчеты в excel как сделать

Обновлено: 04.07.2024

Главная Статьи Сводные таблицы Динамический источник данных сводной таблицы

Динамический источник данных сводной таблицы

Вложения:

pivotsources.xlsx

[Источники данных сводных таблиц]

30 kB

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

В приложенном к статье примере показано 3 варианта построения сводного отчета на основе данных рабочего листа.

Фиксированный диапазон
"Умная таблица" Excel 2007
Динамический именованный диапазон

Фиксированный диапазон

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

В зависимости от практической задачи может потребоваться добавить новые данные в источник сводного отчета. Обычно добавляются новые строки. Это может происходить как при ручном вводе или копировании, так и при автоматизированном получении данных из внешних систем. После простого добавления данных вниз исходного диапазона Excel не включит эту новую строку в источник сводной таблицы. Обновление отчета не приведет к изменениям. Действительно, сводная таблица основана на заранее определенном фиксированном диапазоне данных, не включающих новые строки. Пример такой ситуации показан в файле-примере на листах Data и ReportData – строки 11 и 12 в отчет не попали:

Одним из вариантов решения проблемы является добавление новых строк в середину диапазона – при этом Excel корректно исправит ссылку на источник данных сводной таблицы.

На практике же обычно удобнее и проще заранее определить максимально возможный диапазон строк источника сводной таблицы, добавив про запас достаточное количество строк. В зависимости от задачи можно добавить как 10, так и 10000 строк, как ни странно, это почти не сказывается на производительности сводной таблицы. В файле-примере поменяйте источник данных сводной таблицы ReportData следующим образом:

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

Создавать запас пустых строк в источнике данных можно также для старых сводных таблиц xls-формата. Здесь, правда, имеется небольшая недоработка в интерфейсе: если столбец хранит даты, а в исходных данных присутствуют пустые значения, сводный отчет не позволит применить к этому полю стандартную группировку по годам, кварталам, месяцам и т.д. Новые сводные таблицы xlsx-формата лишены этого недостатка.

"Умная таблица"

Начиная с версии Excel 2007, в интерфейсе рабочего листа стала доступна работа с так называемой «умной таблицей» (лента Вставка \ Таблица). Такая таблица имеет расширенные возможности по дизайну, собственную нотацию формул и может быть опубликована на сервере SharePoint для совместного доступа нескольким пользователям. В нашем случае важно, что «умная таблица» Excel может быть указана в качестве источника данных сводного отчета – можно указать ее по имени или выделить мышью (листы Table, ReportTable):

Добавление строк в «умную таблицу» не влияет на источник данных сводного отчета. Ссылка определяется по имени, поэтому анализ всегда проводится на полном объеме информации. Т.е. это вариант решения можно признать удобным в случае, когда исходные данные могут быть преобразованы в «умную таблицу». При ручном заполнении источника данных сводной таблицы этот вариант работы можно даже назвать приоритетным. Недостатком является несовместимость с xls-форматом, а также сложность при автоматизации импорта из внешних систем.

Динамический именованный диапазон

«Продвинутые» пользователи Excel умеют использовать именованные диапазоны рабочего листа. При помощи таких имен можно заменять ссылки в формулах или указывать источники данных. Обычные именованные диапазоны – это просто замена длинной ссылки в координатах рабочего листа на короткое имя. Так в примере на листе Data можно выделить область A1:D12, присвоить ей имя, а затем указать его в качестве источника данных сводной таблицы ReportData.

Результат будет совершенно такой же, как и при использовании ссылки на фиксированный диапазон. Т.е. проблемы с добавлением новых строк простой именованный диапазон не решает.

Немногие пользователи Excel знают, что в качестве именованного диапазона можно использовать не только простые ссылки на область рабочего листа, но и формулы. Такие именованные диапазоны обычно называют вычисляемыми или динамическими. Создание такого диапазона возможно только в Диспетчере имен (лента Формулы \ Диспетчер имен). При помощи функций вычисляемой адресации можно динамически изменять размер диапазона в зависимости от количество строк с данными.

Пример такого использования имени с формулой на листах Name и ReportName.

Формула имени source выглядит следующим образом:

Функции COUNT() и COUNTIF() используются для подсчета непустых значений в диапазоне, COUNT() предназначена для числовых ячеек, COUNTIF() – для текстовых. В формуле вычисляется количество непустых строк для поля типа дата (столбец A) и количество столбцов по заголовкам полей (строка 1). К количеству столбцов добавляется единица, так как в диапазон входит также строка заголовков. Результирующая функция OFFSET() выдает прямоугольный диапазон с относительным смещением от левого верхнего угла рабочего листа (Name!$A$1). Если количество полей исходных данных не меняется, то вместо последнего параметра функции лучше явно указать это число:

Добавив несколько заполненных строк вниз таблицы исходных данных, можно убедиться, что сводная таблица успешно их обрабатывает (по нажатию кнопки «Обновить»).

Динамический именованный диапазон можно признать самым универсальным способом определения источника сводной таблицы, так как он не требует специальных объектов («умная таблица») и работает даже в старом xls-формате. В приведенном примере имеется требование на наличие непустого идентифицирующего поля в исходном диапазоне. На самом деле его также можно обойти, используя дополнительные вычисления (например, добавив функцию COUNTBLANK()), главное, чтобы в результате был найден конец заполненного диапазона.

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

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

Для освоения примера создания дашборда в Excel смоделируем ситуацию. Из CRM системы были выгруженные в файл Excel через экспорт данных статистические показатели взаимодействия фирмы с ее клиентами:

Уровень сервиса.
Уровень качества.
Продуктивность.

Все значения показателей относительны и поэтому измеряются в процентом соотношении к максимально возможному результату. Формировалась данная статистика в CRM системе – ежедневно на протяжении двух с половиной лет. А точнее отчетный период охватывает промежуток времени между датами: с 01.01.2019 по 31.07.2021 (2,5 года). Все статистические данные находятся в одной большой таблице аж на 3773 строки:

Необходимо с помощью средств визуализации данных в Excel преобразовать таблицу отчета в графический вид отображения информации для визуального анализа дальнейшего развития фирмы в области взаимодействия с клиентами. Идеальное решение – это построить дашборд с интерактивными возможностями изменения данных на графиках и диаграммах пользователем: сегментирование информации по месяцам или по годам, фильтрация значений показателей, масштабирование временных периодов и т.п.

Решим поставленную задачу в 3 шага:

Подготовка исходных данных.
Обработка значений для визуализации.
Динамическая визуализация данных.

Шаг первый – подготовка данных для создания дашборда в Excel

Для подготовки данных нам необходимо лишь добавить один столбец с формулами к таблице на листе «Данные». В данном столбце с заголовком «Месяц» формула преобразует исходные даты в соответственное им названия месяцев, сокращенные до 3-х символов:

С подготовкой данных мы закончили и переходим к следующему шагу – обработка значений таблицы.

Настройка таблиц для элементов управления интерактивного дашборда

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

Шаг второй – обработка исходных данных для диаграмм и графика

Создайте новый лист с именем «Обработка» и заполните его таблицами и значениями также как показано ниже на рисунке:

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

Так как наш сводный дашборд со средствами визуализации данных будет обладать интерактивным функционалом, нам потребуется связать переменными значениями обработку данных с элементами управления дашбордом. Поэтому не закончив еще второй шаг создаем новый лист с именем «Дашборд», на котором сначала создадим все интерактивные элементы управления.

Элементы управления динамическими диаграммами и графиком на дашборде

Перед тем как создавать элементы управления дашбордом сначала создадим основу из фигур на которой будем все размещать.

Основа и панель управления для дашборда

На листе «Дашборд» нарисуйте большую квадратную фигуру с размерами 17 см. на 25 см. выбрав инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Прямоугольник».

Создаем новый элемент основы с размерами 1,5x25 см., для этого выберите инструмент: «ВСТАВКА»-«Текст»-«Надпись»:

Для быстрого оформления верхней надписи основы дашборда воспользуйтесь готовым решением из ее дополнительного меню: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Стильный эффект: Черный, темный 1».

Добавляем основу для панели управления дашбордом рисуя снова прямоугольник с размерами высоты 15,45 см на 3,85 см ширины и придаем ему такой же стиль оформления, как и для надписи:

Блок кнопок для управления показателями по регионам

Далее на панели управления дашбордом создаем первый блок кнопок меню из прозрачного прямоугольника (размером 4,6 x 3) и надписью «РЕГИОН»:

Для удаления заливки прямоугольника выбираем опцию из меню: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Заливка фигуры»-«Нет заливки». И здесь же изменяем цвет контура «Контур фигуры»-«Цвет белый».

Такими же инструментами настраиваем цвет заливки и контура для надписи. А в разделе инструментов: «ГЛАВНАЯ»-«Шрифт» задаем белый цвет шрифта и размер 11 пунктов.

Теперь переходим непосредственно к созданию самих кнопок. Выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель»:

Рисуем первую кнопку переключатель с размерами 0,7 x 2,75 см. Затем двойным щелчком по тексту изменяем сам текст. После кликаем правой кнопкой мышки по кнопке переключателя и выбираем из появившегося контекстного меню опцию «Формат объекта». В появившемся диалоговом окне «Форматирование объекта» на вкладке «Элемент управления» в поле ввода «Связь с ячейкой:» указываем внешнюю ссылку на лист: =Обработка!$B$1. Дальше создаем еще 3 копии этой кнопки и подписываем их текст соответственно как показано выше на рисунке.

Таким образом внешней ссылкой мы создали первое подключение данных к элементам управления «Переключатели». По этой ссылке будут передаваться переменные значения для интерактивной обработки данных.

В зависимости от того какой выбран переключатель в ячейку Обработка!$B$1 будут передаваться переменные числовые значения от 1, 2, 3 или 4. Эти числа будут использованы в формулах на том же листе «Обработка».

Блок срезов временных периодов по годам

Следующий блок кнопок будет создан по другому принципу, но он также будет связан с листом «Обработка». Поэтому сразу переходим на данный лист и выделяем диапазон ячеек A16:C19, а затем выбираем инструмент: «ВСТАВКА»-«Таблицы»-«Таблица» CTRL+T:

В появившемся окне «Создание таблицы» просто нажимаем ОК. И сразу нам доступно ее дополнительное меню где отмечаем галочкой опцию: «РАБОТА С ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Параметры стилей таблиц»-«Строка итогов» CTRL+SHIFT+T. В строке итогов созданной таблицы под вторым столбцом выбираем опцию «Минимум», а под третьим – «Максимум».

Для создания блока кнопок управления привязанных к этой таблице выберите инструмент: «РАБОТА С ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Сервис»-«Вставить срез»:

В появившемся окне «Вставка срезов» отмечаем только одну первую галочку на против опции «ГОД» и жмем ОК. Для быстрого форматирования среза задаем ему готовый стиль: «ИНСТРУМЕНТЫ ДЛЯ СРЕЗА»-«ПАРАМЕТРЫ»-«Стили срезов»-«Серый цвет другой 1». И на конец указываем в параметрах среза размеры «Высота:»-3,33 см и «Ширина:»-3 см.

Очередной блок кнопок для панели управления дашбордом – ГОТОВ! Переносим его на главный лист с именем «ДАШБОРД» и красиво размещаем под первым блоком элементов управления (переключатели).

Выпадающий список для выбора месяца

Переходим к созданию следующего блока элементов управления на листе «ДАШБОРД». Копируем прямоугольник и надпись с первого блока кнопок-переключателей и вставляем под срезом. Надпись переименовываем на «МЕСЯЦ» высоту прямоугольника уменьшаем до 2см.

Внутрь прямоугольника нового блока размещаем новый элемент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком». Рисуем поле с размерами 0,7 см на 2,8 см:

Кликаем правой кнопкой мышки по элементу поле, из контекстного меню выбираем опцию «Формат объекта». В появившемся окне «Форматирование объекта» на вкладке «Элемент управления» указываем внешние ссылки для связи с обработкой данных. Изменяем 3 параметра:

«Форматировать список по диапазону:» – здесь указываем внешнюю ссылку на диапазон ячеек с трехсимвольными названиями месяцев, из которого будут взяты значения для поля: =Обработка!$A$3:$A$14.
«Связь с ячейкой:» - в этом поле ввода указываем внешнюю ссылку на ячейку куда будет передаваться параметр переменного числового значения (от 1-го и до 12-ти) для обработки данных: =Обработка!$A$1.
«Количество срок списка:» - в этом поле ввода мы указываем сколько будет выводиться опций в элементе без полосы прокрутки. Значение 12 так как у нас 12 сокращенных названий месяцев.

Блок управления отображением показателей на графике

И наконец последний блок кнопок управления. Создаем его на основе прямоугольника и надписи из предыдущих блоков, просто скопировав их. Текст надписи изменяем на «ПОКАЗАТЕЛИ», а в прямоугольнике изменяем высоту на 3,8 см. Внутри прямоугольника 4-го блока размещаем новые кнопки воспользовавшись инструментом: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Флажок». Создаем 3 кнопки типа флажка с размерами 0,7 x 2,75 см и подписываем их так как показано на рисунке ниже:

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

Сервис: =Обработка!$H$1
Качество: =Обработка!$I$1
Производительность: =Обработка!$J$1

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

Обработка формул для динамических графиков и диаграмм в Excel

Перейдите на лист с именем «Обработка» и заполните диапазон ячеек D2:F2 ниже приведенной сложной формулой выборки значений по нескольким условиям из листа «Данные»:

После чего заполняем ячейки второй незаполненной таблицы в диапазоне H3:J14 другой сложной формулой:

Обработка данных – ГОТОВА! Сразу переходим к построению визуализации обработанных значений на дашборде.

Визуализация данных на интерактивном динамическом дашборде

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

Шаг третий – визуализация данных таблиц

На листе «ДАШБОРД» снова создайте фигуру прямоугольник с размерами 6,3 x 5 см. Преобразуйте ее в 3D фигуру выбрав инструмент: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Эффекты фигуры», здесь же «Заливка фигуры»-«Цвет»-Зеленый и «Контур фигуры»-«Нет контура»:

Переходим на лист «Обработка» выделяем диапазон ячеек D1:D3 и выбираем инструмент: «ВСТАВКА»-«Диаграммы»-«Кольцевая»:

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

Переносим диаграмму на лист «ДАШБОРД» и продолжаем ее форматировать. Кликнув правой кнопкой мышки по левой доли диаграммы вызываем контекстное меню из которого следует выбрать опцию «Формат точки данных». В появившемся дополнительном окне изменяем параметры: «Формат точи данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Сплошная заливка»-«Цвет»-белый и здесь же «Прозрачность»-50%. А также «ГРАНИЦА»-«Нет линий»:

Снова кликнув правой кнопкой мышки по самой диаграмме и контекстным меню через опцию «Формат области диаграммы» открываем дополнительное окно где в разделе меню «ЗАЛИВКА» отмечаем опцию «Нет заливки» сделав таким образом диаграмму прозрачной. И здесь же отмечаем опцию «ГРАНИЦА»-«Нет линий».

Теперь кликаем правой кнопкой мышки по правой доли ряда данных диаграммы и из появившегося контекстного меню снова выбираем опцию «Формат точки данных» где изменяем только один параметр – цвет заливки на белый:

А сейчас кликаем правой кнопкой мышки по целому ряду диаграммы и выбираем из контекстного меню опцию «Формат ряда данных» где вносим свои параметры для добавления и настройки тени в окне: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ТЕНЬ»-«Размер»–105% и здесь же «Размытие»-15 пунктов и «Расстояние»-5 пунктов:

После чего добавляем на диаграмму 2-е надписи для которых настраиваем прозрачный фон, белый цвет шрифта и размер шрифта также как мы это делали с предыдущими надписями:

Надпись с показателем 48% будет динамически изменяться поэтому для нее следует сделать внешнюю ссылку на соответствующую ячейку. Для этого делаем активной надпись выделив ее одним кликом левой кнопкой мышки, а в строке формул вводим адрес для соответственной внешней ссылки =Обработка!D2 и нажимаем клавишу Enter на клавиатуре для подтверждения.

Далее создаем еще 2 диаграммы для дашборда по такому же принципу, как была создана первая диаграмма. Только эти диаграммы будут сделаны для диапазонов ячеек E1:E3 и F1:F3. У их прямоугольников будут другие фоновые цвета заливки: фиолетовый с кодом RGB: 159; 75; 228; и голубой с кодом RGB: 0; 182; 225:

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

График для отображения динамики изменения показателей за период

Перейдите на лист «Обработка» и выделите диапазон ячеек H2:J14 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«График»:

Не снимая выделения с графика выберите инструмент из его дополнительного меню: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Стили диаграмм»-«Стиль 6» и здесь же «Тип»-«Изменить тип диаграммы»:

В появившемся окне «Изменение типа диаграммы» для рада СЕРВИС выбираем тип «График с маркерами» для КАЧЕСТВО – «Гистограмма с группировкой» и для ПРОДУКТИВНОСТЬ – «График с областями и накоплением».

Изменяем цвета соответственно уже созданным диаграммам кликая по каждому ряду правой кнопкой мышки и выбирая из контекстного меню уже знакомую опцию «Формат ряда»:

Цвета фиолетовой гистограммы и голубого графика с областями немного сливаются, поэтому разграничим их тенями. Кликаем правой кнопкой мышки по фиолетовой гистограмме и вызываем контекстным меню инструмент: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Эффекты»-«ТЕНЬ»:

Также увеличиваем ширину столбцов фиолетовой гистограммы: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Боковой зазор» - 150 процентов.

Осталось лишь настроить внешний вид маркеров зеленого графика ряд СЕРВИС. Кликаем по нему правой кнопкой мышки и вносим параметры: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«МАРКЕР»-«ПАРАМЕТРЫ МАРКЕРА»-«Размер»-9 и здесь же «ЗАЛИВКА»-«Сплошная заливка»-«Цвет белый»:

Ниже параметр «ГРАНИЦА»-«Сплошная линия»-«Цвет зеленый» и здесь же «Ширина»-2,25 пункта.

Для динамического изменения названия графика на листе обработка в ячейке B14 создадим следующую формулу:

Теперь мы будем просто ссылаться из названия графика на ячейку B14 также как мы это делали с подписями на диаграммах. Переносим график на главный лист «ДАШБОРД» и тестируем готовый результат:

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

В статье Как сделать сводную таблицу в Excel в пункте «A. Подготовка. Выгружаем данные из Facebook» я подробно расписала, как подготовить данные для отчета. Делаем все согласно данным рекомендациям.

В итоге у вас будут выгружены данные за определенный период.

Я выгрузила отчет из facebook за 3 дня «3day-Report-May-13-2020-May-15-2020.csv» Сохранила его в папке «fb cvs», которая находится в общей папке «Отчеты».

Желательно сразу создать несколько папок для отчетов. Например, общая папка «Отчеты», в ней папки: «fb cvs», «динамич отчеты» и другие в соответствии с содержимым.

B. Создаем динамический отчет в Excel

В папке «динамич отчеты» (если еще не создали ее в общей папке «Отчеты» — самое время!) создаем файл Excel. Называем, как нам удобно. Этот файл и будет нашим обновляемым отчетом.

Я создала «fb_3 day dinam report.xlsx».

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

C. Подгружаем данные в созданный отчет

Открываем созданный документ «fb_3 day dinam report.xlsx»

Нажимаем «Данные»
«Получить данные»
«Из файла»
«Из папки» (в дальнейшем новые файлы с актуальными данными будут подтягиваться из папки в отчет для обновления)

5. Выбираем папку, где сохранен источник данных

Мой отчет «3day-Report-May-13-2020-May-15-2020.csv» сохранен в папке «fb cvs»

6. Нажимаем «Преобразовать данные»

7. Открывается редактор запросов. Щелкаем на значок объединить файлы справа от «Content».

9. Редактируем таблицу, как нам надо.

Удалила лишние столбцы (Source name, Reporting Starts, Reporting Ends, Campaign Budget Type)

Сократила названия столбцов

10. Excel воспринимает наши значения, прописанные через точку, за текст.

FB по умолчанию выгружает десятичные числа с точкой, а не с запятой в качестве разделителя.

И при замене формата на десятичное число Excel выдает Error.

Чтобы отменить любое примененное действие, нужно нажать на крестик в области «Примененные шаги» справа

11. Чтобы изменить формат с текста на десятичное число, нужно заменить все точки в цифрах на запятые.

Зажимаем Ctrl
Выделяем нужные столбцы
Щелкаем на «Замена значений» и меняем «.» на «,»

12. Теперь надо заменить «null» на 0

Также через зажатие Ctrl, выделение столбцов с «null» и выбор «Замена значений» (как на скрине)

13. Когда таблица готова, нажимаем «Закрыть и загрузить» слева вверху.

14. На новом листе появляется наша таблица с данными.

Выделяем все столбцы с данными и нажимаем «Сводная таблица», ОК.

15. Наш динамический отчет создан в виде сводной таблицы.

Как работать со сводной таблицей, я подробно рассказывала в этой статье в пункте «C. Базовые принципы работы со сводной таблицей»

D. Обновляем данные в динамическом отчете

Заходим в AdsReporting и находим сохраненный отчет (см. пункты 4-6 статьи), у меня это «3day Report»:

2. Обновляем даты, сохраняем и скачиваем отчет (Export as .csv)

3. Сохраняем скачанный отчет в ту же папка, что и предыдущий (у меня это «fb cvs»).

4. Нажимаем «Данные», «Обновить все» (иногда требуется два щелчка).

5. Если вы сделали все верно, в таблицу подгрузятся актуальные данные. В моем случае — за следующие 3 дня.

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

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

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

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

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

Создать динамический диапазон с помощью таблиц Excel

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

У нас есть еще один инструмент под названием Таблицы данных, который является частью What-If-Analysis. Так что не путайся с этим.

Как создать динамические таблицы в Excel?

Существует два основных способа создания динамических таблиц в Excel - 1) Использование сводной таблицы и 2) Использование формул

Вы можете скачать этот шаблон Excel для динамических таблиц здесь - Шаблон Excel для динамических таблиц

Динамические таблицы в Excel - Использование сводной таблицы

У меня есть таблица продаж за январь. Эти данные о продажах включают дату, месяц, продавца и стоимость продаж.

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

Шаг 1: Выберите все данные.

Шаг 2. Выберите сводную таблицу на вкладке « Вставка ».

Шаг 3: После того, как стержень вставлен, перетащите Продавца в заголовок Строки и Продажи в Значения.

Шаг 4: Теперь я получил обновления продаж за февраль месяц. Я вставил в данные продаж за январь месяц.

Шаг 5: Если я обновлю сводную таблицу, она не выдаст мне обновленный отчет, поскольку диапазон данных ограничен только от A1 до D11.

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

Создать динамическую таблицу для сокращения ручной работы

Создавая таблицы, мы можем сделать данные динамическими.

Шаг 1: Поместите курсор в любое место в данных о продажах за январь.

Шаг 2: Теперь нажмите Ctrl + T, который является клавишей быстрого доступа для вставки таблиц. Это покажет вам диалоговое окно ниже. Убедитесь, что в моей таблице установлены заголовки .

Если вы заметите, как только курсор окажется в любом месте данных, он покажет вам новую вкладку на ленте в виде «Дизайн».

Шаг 4: На вкладке «Дизайн» дайте имя вашей таблице. Дайте имя, которое вам легко понять.

Шаг 5: Теперь вставьте новую сводную таблицу в эту таблицу. (Примените предыдущую технику) Одна из прелестей этой таблицы в том, что вам не нужно выбирать весь набор данных, вместо этого вы можете поместить курсор в таблицу и вставить сводную таблицу.

Шаг 6: Теперь добавьте данные о продажах за февраль за эту таблицу.

Шаг 7: Теперь перейдите к сводной таблице и обновите сводную таблицу. Вы можете нажать сочетание клавиш ALT + A + R + A.

Шаг 8: Это обновит сводную таблицу.

Теперь в сводной таблице начали отображаться новые значения, включая продажи за февраль.

Динамические таблицы в Excel - Использование формул

Мы можем не только создать сводную таблицу с динамической таблицей, но и применить ее к формулам. Теперь посмотрим на разницу между обычной формулой и формулой динамической таблицы.

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

Теперь я добавлю данные о продажах за февраль в список.

Формула по-прежнему показывает старые значения.

Теперь я буду применять формулу к динамической таблице.

Если вы соблюдаете формулу, в формуле нет диапазонов. В нем есть имена. Позвольте мне разбить формулу на кусочки.

= SUMIF (SalesTable (продавец), F2, SalesTable (продажи))

SalesTable: это имя таблицы.

Продавец: Это название столбца, на который мы ссылаемся.

F2: это ссылка на ячейку имени торгового представителя.

Продажи: это снова столбец, на который мы ссылаемся.

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

Что нужно помнить о динамических таблицах в Excel

Ctrl + T - это сочетание клавиш для создания таблиц.
Все заголовки относятся к соответствующим столбцам.
Даже отдельная ячейка в столбце указывается заголовком столбца.

Мы также можем создавать динамические именованные диапазоны.