Функция GETPIVOTDATA в Excel

Функция GETPIVOTDATA в Excel может извлекать определенные данные из сводной таблицы по имени на основе структуры, а не ссылок на ячейки.

Что делает функция GETPIVOTDATA в Excel?

Функция GETPIVOTDATA в Excel извлекает данные из сводной таблицы в формуле.

Возвращаемое значение

Запрошенные данные.

Аргументы

• data_field — имя поля значения для запроса.
• Pivot_table — ссылка на любую ячейку сводной таблицы для запроса.
• field1, item1  — [необязательно] Пара поле/элемент.

Синтаксис

=GETPIVOTDATA(data_field, Pivot_table, [field1, item1 ], …)

Примечания по использованию

Используйте функцию GETPIVOTDATA для запроса существующей сводной таблицы и получения определенных данных на основе структуры сводной таблицы. Преимущество функции перед простой ссылкой на ячейку заключается в том, что он собирает данные на основе структуры, а не местоположения ячейки. GETPIVOTDATA будет продолжать работать правильно даже при изменении сводной таблицы, пока поля, на которые ссылаются, все еще присутствуют.

Первый аргумент data_field называет поле значения для запроса. Второй аргумент, Pivot_Table, представляет собой ссылку на любую ячейку в существующей сводной таблице. Дополнительные аргументы передаются в парах поле/элемент, которые действуют как фильтры, ограничивая извлекаемые данные на основе структуры сводной таблицы. Например, вы можете указать в поле «Регион» элемент «Восток», чтобы ограничить данные о продажах продажами в восточном регионе.

Функция GETPIVOTDATA создается автоматически, когда вы ссылаетесь на ячейку значения в сводной таблице, указывая и щелкая ее. Чтобы избежать этого, вы можете просто ввести адрес нужной ячейки (вместо того, чтобы щелкнуть мышью). Если вы хотите полностью отключить эту функцию, отключите «Создать GETPIVOTDATA» в меню «Инструменты сводной таблицы» > «Параметры» > «Параметры» (крайний слева, под именем сводной таблицы).

Примеры с GETPIVOTDATA

Приведенные ниже примеры основаны на следующей сводной таблице:

Первый аргумент функции GETPIVOTDATA называет поле, из которого извлекаются данные. Второй аргумент — это ссылка на ячейку, которая является частью сводной таблицы. Чтобы получить общий объем продаж из показанной сводной таблицы:

=GETPIVOTDATA("Sales",$B$4) // возвращает 138602

Поля и пары элементов предоставляются парами, введенными в виде текстовых значений. Чтобы получить общий объем продаж продукта «Фундук»:

=GETPIVOTDATA("Sales",$B$4,"Product","Hazelnut") // возвращает 62456

Чтобы получить общий объем продаж для Западного региона:

=GETPIVOTDATA("Sales",$B$4,"Region","West") // возвращает 41518

Чтобы получить общий объем продаж Almond в восточном регионе, вы можете использовать любую из формул ниже:

=GETPIVOTDATA("Sales",$B$4,"Region","East","Product","Almond")
=GETPIVOTDATA("Sales",$B$4,"Product","Almond","Region","East")

Вы также можете использовать ссылки на ячейки для указания имен полей и элементов. В примере, показанном выше, формула в I8 имеет следующий вид:

=GETPIVOTDATA("Sales",$B$4,"Region",I6,"Product",I7)

Значения для региона и продукта берутся из ячеек I5 и I6. Данные собираются на основе региона «Средний Запад» в ячейке I6 для продукта «Фундук» в ячейке I7.

Даты и время

При использовании GETPIVOTDATA для получения информации из сводной таблицы на основе даты или времени используйте собственный формат Excel или такую ​​функцию, как функция DATE. Например, чтобы получить общий объем продаж на 1 апреля 2021 г., когда отображаются отдельные даты:

=GETPIVOTDATA("Sales",A1,"Date",DATE(2021,4,1))

Если даты сгруппированы, используйте названия групп в виде текста. Например, если поле «Дата» сгруппировано по году:

=GETPIVOTDATA("Sales",A1,"Year","2021")

Примечания

• Имя data_field и значения поля/элемента должны быть заключены в двойные кавычки («»).
• GETPIVOTDATA в эксель вернет ошибку #REF, если какие-либо поля написаны неправильно.
• Функция вернет ошибку #REF, если ссылка на pivot_table недействительна.