Ошибка вычисления при изменении таблицы ВПР - Советы по Excel

Содержание

Существует странная ошибка, которая может вызвать ошибки вычислений в Excel при внесении изменений в таблицу поиска. Учитывая, что девиз команды Excel - «Recalc or Die», я не уверен, почему они не исправят эту ошибку.

На рисунке ниже показана формула ВПР в столбце C. Он ищет элемент в B, возвращая 4-й столбец из оранжевой таблицы поиска. На данный момент все в порядке.

Типичная функция ВПР. Excel работает быстро благодаря интеллектуальному алгоритму пересчета. В этом случае алгоритм предпочитает не пересчитывать ячейки, которые необходимо вычислить.

Если кто-то случайно удаляет столбец или вставляет столбец в таблицу поиска, происходит странная вещь.

Вставьте столбец H, и рабочий лист будет пересчитан частично.

Что здесь происходит? Это выглядит как:

Формула в C2 зависит от столбцов F: K, поэтому она пересчитывается. Мы напортачили, потому что ВПР все еще возвращает 4-й столбец таблицы. Это дает нам цвет вместо цены и приводит к сбою формулы итога в D2.
Теперь, если бы я был механизмом пересчета Excel, и если бы я был разумным, и если бы у меня была личность, я мог бы сказать себе: «Хммм. Значение в C2 изменилось. Возможно, мне следует пересчитать любую другую идентичную формулу в этом столбце». Эта мысль заставила меня пересчитать C3, C4 и C5. Но Excel не пересчитывает эти ячейки. Это не имеет ничего общего с ошибкой в D2. Даже без формулы в D2 формулы в C3, C4 и C5 на этом этапе не вычисляются.
Ячейки C3, C4 и C5 остаются ошибочными, пока вы не нажмете Ctrl + alt = "" + Shift + F9 для полного пересчета.

Не поймите меня неправильно. Я люблю ВПР. Но люди, которые жалуются на ВПР, предложили бы использовать ПОИСКПОЗ в качестве третьего аргумента в ВПР для разрешения этой ситуации.