Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы.
Эту функцию удобно использовать для динамического получения итогов или конкретных значений из сводной таблицы в заранее подготовленные отчёты, дашборды или ячейки.
Синтаксис
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_значений; сводная_таблица; [поле1; элемент1; …])
Аргумент |
Описание |
Допустимые значения |
|---|---|---|
поле_значений |
Название поля значений, из которого извлекаются данные |
Текстовая строка в кавычках |
сводная_таблица |
Ссылка на любую ячейку, диапазон ячеек или именованный диапазон в сводной таблице |
Ссылка на ячейку |
[поле1; элемент1; поле2; элемент2; ...] |
(необязательный) Названия полей и элементов в них. Позволяют сузить запрос и извлечь из таблицы конкретное значение. Записываются в кавычках, кроме дат и чисел. Пары названий полей и элементов могут следовать друг за другом в произвольном порядке |
Текстовая строка |
Примеры использования
Представим, что сводная таблица содержит данные о продажах с измерениями: Регион, Менеджер, Квартал, и показателем Сумма_продаж.
Нужно получить сумму продаж для менеджера "Иванов" в регионе "Север" за квартал "Q3".
Решение:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма_продаж"; $A$1; "Регион"; "Север"; "Менеджер"; "Иванов"; "Квартал"; "Q3")
где:
–"Сумма_продаж" — целевое поле данных;
–$A$1 — ссылка на сводную таблицу;
– "Регион"; "Север" — первое условие фильтрации;
–"Менеджер"; "Иванов" — второе условие фильтрации;
–"Квартал"; "Q3" — третье условие фильтрации.
Функция возвращает числовое значение, соответствующее пересечению указанных критериев в сводной таблице. При отсутствии данных возвращается ошибка #ССЫЛКА!.
Создание динамического отчета, в котором параметры запроса могут изменяться через ячейки управления
Для решения задачи требуется создать формулу, которая извлекает данные на основе значений в ячейках B1 (поле данных), C1 (значение менеджера) и D1 (значение квартала).
Решение:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ($B$1; СводнаяТаблица_Продажи; "Менеджер"; $C$1; "Квартал"; $D$1; "Статус"; "Завершено")
где:
–$B$1 — поле данных из ячейки B1;
–СводнаяТаблица_Продажи — именованный диапазон сводной таблицы;
–"Менеджер"; $C$1 — значение менеджера из C1;
–"Квартал"; $D$1 — значение квартала из D1;
–"Статус"; "Завершено" — статичное дополнительное условие.
Порядок выполнения операций:
1.Функция ищет в сводной таблице "СводнаяТаблица_Продажи" значение поля, указанного в B1.
2.Применяет фильтры: Менеджер = значение из C1, Квартал = значение из D1, Статус = "Завершено".
3.Возвращает сумму, количество, среднее в зависимости от настроек сводной таблицы.
Пример с использованием элемента «["Все"]»
Таблица содержит данные о продажах с измерениями: Регион, Продукт, и показателем Объем_продаж.
Нужно рассчитать долю продаж региона "Север" от общих продаж по всем регионам.
Решение:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Объем_продаж"; $A$1; "Регион"; "Север") / ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Объем_продаж"; $A$1; "Регион"; ["Все"])
Первая функция получает продажи региона "Север", вторая — общие продажи по всем регионам с использованием элемента ["Все"].
Функция возвращает числовое значение от 0 до 1, представляющее долю региона "Север" в общем объеме продаж.
Примечания
–Функция может быть создана автоматически при клике по ячейке сводной таблицы во время ввода формулы. Эту опцию можно включить или отключить в настройках приложения.
–Функция автоматически обновляется при изменении или обновлении исходной сводной таблицы.
–Названия полей и элементов должны точно соответствовать именам в сводной таблице.
–Для отображения всех элементов поля используется специальный элемент: "[Все]".
–Функция возвращает ошибку #ССЫЛКА!, если сводная таблица была удалена или изменена.