Excel: круговые вычисления не будут сходиться, если во входных данных в определенном пространственном расположении

Question

Я обнаружил странное поведение с круговыми вычислениями в Excel. Когда включено (через Опции), циклические ссылки могут использоваться для итерации. В моем примере я использую метод Ньютона. Проблема, которую я вижу, состоит в том, что, когда я ввожу формулы своих ячеек в определенном пространственном расположении, итерация не будет сходиться. Если я использую другой макет, он будет сходиться. Это ошибка или я что-то упустил? Вот как это воспроизвести (протестировано в Excel 2010, 2011):

f (x) = x * ln (x)

1. Новая книга Excel.

2. Создайте "горизонтальный" макет:

   2.1 Делать этикетки. В ячейках A1-F1 напишите x_start, x_old, x_new, f (x), f '(x), f-goal

   2.2 Установите формулы ячеек (в следующем порядке):

   • A2 = 4
   • B2 = ИСКЛЮЧЕНИЕ (C2, A2)
   • C2 = B2-D2 / E2
   • D2 = B2 * LN (B2) -F2
   • E2 = LN (B2) +1
   • F2 = 10

3. Создайте "вертикальный" макет:

   3.1 Сделать этикетки. В ячейках A4-A9 напишите x_start, x_old, x_new, f (x), f '(x), f-goal

   3.2 Установите формулы ячеек (в следующем порядке):

   • B4 = 4
   • B5 = ИГРУШКА (B6, B4)
   • B6 = B5-B7 / B8
   • B7 = B5 * LN (B5) -B9
   • B8 = LN (B5) +1
   • B9 = 10

4. Создайте макет с двумя столбцами:

   4.1 Делать этикетки. В ячейках E4-E6 напишите x_start, x_old, x_new. В ячейках H4-H6 напишите f-цель, f (x), f '(x).

   4.2 Задайте формулы ячеек (в следующем порядке):

   • F4 = 4
   • F5 = ИГРУШКА (F6, F4)
   • F6 = F5-G5 / G6
   • G5 = F5 * LN (F5) -G4
   • G6 = LN (F5) +1
   • G4 = 10

Решение должно сходиться для x_old и x_new примерно при 5,728926, причем f (x) почти равен нулю. Теперь нажмите F9 или Shift-F9, чтобы пересчитать значения ячеек. Он сходится только для макета с двумя столбцами, что означает, что значения не меняются (вряд ли, если вообще). Горизонтальные и вертикальные макеты постоянно меняются числами и не приближаются к ответу.

Вы можете проверить, что приведенные выше формулы точно такие же для предполагаемых переменных (проверьте, назначив ячейке x_old фиксированное значение для каждого макета. Числа будут идеально совпадать.) Я сумасшедший или это ошибка?

Answer 1

Excel использует другой процесс расчета для циклических ссылок.

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

Так что, вероятно, именно поэтому конвергенция зависит от пространственного расположения (но я не рассмотрел ваш пример подробно).

для получения более подробной информации см. http://www.decisionmodels.com/calcsecretsc.htm