VLOOKUP или INDEX MATCH не обновляют столбцы при обращении к сводной таблице

Question

Я собираюсь помешаться, пытаясь понять это. Я думал, что выиграл джекпот, когда узнал об INDEX-MATCH и дополнительной гибкости по сравнению с VLOOKUP, но эта гибкость, похоже, не помогает, когда формула ссылается на сводную таблицу.

Моя проблема заключается в том, что мне нужно добавить еще один столбец в сводную таблицу, но когда я это делаю, все формулы VLOOKUP, ссылающиеся на данные справа от нового столбца, не выполняются. Я могу заставить его автоматически обновлять букву столбца в формуле только при обращении к обычной таблице.

Если VLOOKUP ссылается на данные из сводной таблицы, в которую я добавляю столбец ... аварийные сигналы ...

Answer 1

GETPIVOTDATA - это встроенная функция поиска для сводных таблиц, поэтому вы можете использовать ее вместо VLOOKUP или INDEX и MATCH . Строки в функции GETPIVOTDATA могут быть назначены вашим переменным для эффективного поиска.

Answer 2

Я понимаю, что 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 потому что она более надежна . Если вы не можете этого сделать, попробуйте более длинную формулу, приведенную выше, а затем просто следите за тем, как вы управляете сводной таблицей.