ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для графиков Excel
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ наиболее часто используемая функция для разработки интерактивных дашбордов в Excel на основе сводных таблиц. Разработчикам визуализации данных важно знать возможности и особенности использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel. Рассмотрим все по порядку.
Схема структуры интерактивного дашборда в Excel
На этой схеме показан базовый принцип построения интерактивных дашбордов в Excel с использованием сводных таблиц и элементов управления:
- SOURCE DATA – Вводим исходные данные.
- PIVOT TABLE – На основе исходных данных создаем сводную таблицу.
- CHARTING DATA – Заполняем таблицу чартов. Используя функцию GETPIVOTDATA извлекаем обработанные данные для создания чартов дашборда.
- DATA VISUALIZATION – на основе данных чарта создаем графики и диаграммы.
- CONTROLS – создаем срезы данных и используем их как кнопки управления дашбордом.
Правила использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
Сразу стоит отметить, что для корректной работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ заголовки строк и столбцов всех таблиц (исходных данных, сводных и чартов) – должны быть одинаковыми значениями.
В таблице построения чартов в первой ячейке для месяца январь вводим знак равно и кликаем по первой ячейке сводной таблицы на значение на против месяца январь. В результате Excel автоматически сгенерирует функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ с уже заполненными аргументами. Чтобы функция автоматически заполняла таблицу соответственными значениями, нам необходимо настроить аргументы.
В последнем аргументе указать ссылку на заголовок строки данных чарта. Дистантно лишь указать относительную ссылку на ячейку. Но ссылка для заголовка столбца должна быть текстового типа. Иначе функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет возвращать ошибку. Для этого с помощью функции CONCATENATE к ссылке следует просто добавить пустую строку и любое значение, передаваемое такой ссылкой, будет иметь текстовый тип данных. Также в данной ситуации ссылка на заголовок столбца должна быть абсолютной.
Преимущества использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для графиков
Главное преимущество функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для извлечения значений из сводных таблиц по сравнению с обычными ссылками и формулами – это сохранять свою функциональность при любых изменениях структуры сводной таблицы как по горизонтали, так и по вертикали.
Для примера мы изменили структуру по горизонтали добавив в середину новый столбец для извлечения данных к новой таблице чарта. Работоспособность всех формул сохранилась.
Для данных диаграммы через функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ ссылаемся на итоговое значение в среднем столбце.
На основе таблиц для чартов составляем визуализацию данных в Excel.
Перейдите курсором Excel в область сводной таблицы и выберите инструмент: Insert – Filters – Slicer.
Теперь мы можем управлять сводными таблицами с помощью срезов данных, а значит мы управляем всем дашбордом.
Шаблон интерактивных графиков с использованием сводных таблиц
Освоив базовые принципы работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и схему структуры построения дашборда в Excel, мы можем прочитать любой алгоритм шаблона интерактивной визуализации с использованием сводных таблиц.
Структура шаблона интерактивного дашборда отличается от приведенной схемы только тем, что все типы таблиц и элементы организованы на разных листах Excel.
- На листе Data находятся исходные данные.
- На листе Control расположены сводные таблицы.
- На листе Processing ячейки для чартов заполняются формулами.
- На листе DASHBOARD – визуализация данных с интерактивными элементами управления сводными таблицами и чартами в целом.
Скачать шаблон с использованием функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel 
В следующих статьях мы рассмотрим на практике как создаются интерактивные дашборды в Excel со сводными таблицами и использованием функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для подключения данных к чартам. На данном этапе обучения вы можете скачать готовый шаблон с примерами, описанными в этой статье и попрактиковать усвоенный обещающий материал.
