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