Функция 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 недействительна.