Как заставить Excel пересчитать циклические зависимости после исправления ошибки?

Question

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

Проблема: когда я случайно создаю ошибку (например, #NUM или # DIV/0), изменяя формулу ячейки, используемой в циклической зависимости, а затем исправляю ее, Excel не может пересчитать лист.

Проблему легко воспроизвести по простому сценарию, где налог = ставка налога *(доход + налог):

После исправления ошибки в C1, C2 остается с исходной ошибкой и не пересчитывает.

Использование Calculate now не помогает. Единственный способ восстановить функциональный лист, который я нашел, - это отредактировать (F2) формулу C2, а затем нажать Enter.

Но это решение не применимо к моему сложному рабочему листу: я получаю десятки ячеек по ошибке одновременно и не могу найти ту, которую я должен "обновить" (F2 затем (Enter).

Как я могу решить это? Я просто хотел бы заставить Excel действительно пересчитать все после исправления ошибки.

Answer 1

когда я случайно создаю ошибку (например, #NUM или # DIV/0), изменяя формулу ячейки, используемой в циклической зависимости, а затем исправляю ее, Excel не может пересчитать лист.

На самом деле это не так, Excel правильно пересчитывает (по крайней мере, в приведенном вами примере):

• когда вы вводите круговую ссылку, как в вашем примере:
  =B1*(C1+C2) в C2
• Excel интерпретирует его, просто принимая значения ячейки в предыдущий момент, а предыдущее значение в C2 было ошибкой, поэтому ваша формула будет:
  =B1*(C1+#ERR) , что приводит к ошибке, и всякий раз, когда вы ее пересчитываете, она все равно будет ошибкой, поэтому описанное вами поведение ПРАВИЛЬНО
• если вы нажмете F2, то вы сбросите значение в C2, так что оно будет считаться 0 , поэтому оно работает
• Вы можете утверждать, что хотите сбросить все формулы с ошибкой, однако это легко может привести к бесконечным циклам, когда Excel попытается разрешить невозможные вычисления.

У меня всего лишь несколько итеративных вычислений, однако я настоятельно рекомендую против них, кроме вашей текущей проблемы, могут быть и другие:

• потеря прослеживаемости: поскольку результаты расчетов зависят не только от формул и констант на экране, но и от предыдущего состояния, воспроизвести ситуации может быть очень трудно (если не невозможно)
• если вы случайно измените какое-либо значение, которое является прецедентом формулы циклической ссылки, оно будет немедленно изменено, и отменить его не всегда легко (особенно, если вы осознаете это несколькими шагами позже).
• Я думаю, что для финансового листа, пусть он будет в любом случае теоретическим / научным / нереалистичным, вы хотите сохранить полный контроль над листом.