Я понимаю, что OP уже нашел свой ответ в GETPIVOTDATA
но для других пользователей, для которых это не работает, есть еще один вариант.
Если по какой-либо причине использование GETPIVOTDATA не работает для ваших нужд - я сталкивался с этой проблемой несколько раз - тогда вы все равно можете использовать вашу комбинацию INDEX / MATCH с небольшой модификацией, но она сделает ее намного дольше. Общий формат
=INDEX(ResultArray, MATCH(Lookup_Value, Lookup_Array, Match_Type))
Вам нужно изменить ResultArray
и Lookup_Array
чтобы они были динамическими, не полагаясь на автоматическое обновление формулы. Вы можете сделать это с помощью функции OFFSET
. Например, допустим, у вас есть сводная таблица, в которой заголовки в строке 4. Используйте OFFSET
и MATCH
чтобы сначала найти нужные заголовки.
=INDEX(OFFSET(FirstColumnOfPivotTable, 0, MATCH("Result Header", "4:4", 0)), MATCH(Lookup_Value, OFFSET(FirstColumnOfPivotTable, 0, MATCH("Lookup Header", "4:4", 0)), Match_Type))
Есть несколько проблем с этим решением:
- Выбор диапазона для использования с
FirstColumnOfPivotTable
имеет решающее значение. Если вы выберете некоторый статический диапазон, сделайте его больше, чем вам когда-либо понадобится. Было бы лучше, чтобы он также был динамическим в зависимости от размера сводной таблицы.
- Если вы добавите / удалите фильтры в сводной таблице, строка 4 может больше не иметь заголовков, и это нарушит формулу
- Все, что зависит от большего количества вещей, находящихся в выравнивании, с большей вероятностью сломается
GETPIVOTDATA
может возвращать только число, которое уже отображается в сводной таблице. Если вам нужна общая сумма для некоторого подзаголовка, но общая сумма не отображается в сводной таблице, функция не может дать ее вам. Если возможно, измените вашу сводную таблицу, чтобы использовать GETPIVOTDATA
потому что она более надежна . Если вы не можете этого сделать, попробуйте более длинную формулу, приведенную выше, а затем просто следите за тем, как вы управляете сводной таблицей.