Итеративные вычисления могут помочь найти решение математических задач путем многократного выполнения вычислений с использованием предыдущих результатов. Это стало возможным благодаря компьютерам, которые могут многократно выполнять вычисления, чтобы определить вероятность возможных ответов, приближаясь к результатам с разных точек зрения.
В Excel вы можете ссылаться на ячейку, содержащую формулу, и использовать ее результат в идентичной формуле в другой ячейке. Для этого вам нужно будет скопировать формулу и ссылки столько раз, сколько вы хотите повторить процесс. Это может сработать, если ваша модель относительно проста, но выполнение этого в более сложных книгах может оказаться гораздо более сложным, если не сказать совершенно невозможным.
Альтернативным и лучшим подходом является использование функции итеративного вычисления в Excel. Вы можете создать формулу, которая ссылается на ячейку, содержащую формулу. Формула может использовать результат предыдущих вычислений, таким образом автоматически вычисляя одно и то же на протяжении нескольких итераций.
Как бы просто это ни звучало, есть несколько вещей, которые вам нужно учитывать. Прежде всего, количество итераций должно быть ограничено. Хотя большее количество итераций обычно означает более точные результаты, это также означает более длительное время вычислений – и иногда сбои. Еще следует отметить, что когда итеративные вычисления отключены, Excel выдаст предупреждение, поскольку циклические ссылки обычно считаются ошибками пользователя, если вы не знаете, что делаете.
Включение итерационных вычислений Excel
Чтобы активировать и использовать циклические ссылки, вы должны сначала активировать их, установив флажок Включить итеративные вычисления в меню Файл.
Перейдите в раздел Файл> Параметры> Формулы> Параметры вычисления в Excel 2016, Excel 2013 и Excel 2010.
В Excel 2007 перейдите к кнопке Office > Параметры Excel> Формулы > Область итерации.
В Excel 2003 и более ранних версиях перейдите в Меню> Инструменты> Параметры> Вычисление.
Включение итеративных вычислений вызовет два дополнительных ввода в том же меню:
- Максимальное количество итераций определяет, сколько раз Excel должен пересчитывать рабочую книгу,
- Максимальное изменение определяет максимальную разницу между значениями итерационных формул. Обратите внимание, что ввод меньшего числа здесь означает более точные результаты.
Итерационные вычисления прекращаются при выполнении одного из заданных условий (количество итераций или значение изменения). Например, предположим, что максимальное количество итераций установлено равным 100, а максимальное изменение — 0,001. Это означает, что Excel прекратит вычисления либо после 100 вычислений, либо когда разница между результатами составит менее 0,001.
Варианты использования
Расчет будущей стоимости инвестиций
Давайте предположим, что у нас есть 10 000 долларов и мы хотим вложить эти деньги на депозитный счет наличными (CD). Мы собираемся предположить, что ежемесячная процентная ставка составляет 1,25%. Вы можете загрузить образец рабочей книги для этого варианта использования . Чтобы рассчитать общую стоимость в конце 21-го месяца, мы собираемся рассчитать основную сумму за каждый месяц и добавить проценты к предыдущему месяцу.
Начните с ввода начальных денежных средств, процентов и общей стоимости, как показано ниже.
=значение * (1 + процентная ставка)
Затем выберите ячейку с начальной денежной стоимостью и добавьте ссылку на функцию total value.
Это выдаст предупреждение о циклической ссылке, если итеративные вычисления не включены. Если вы еще этого не сделали, включите этот параметр и установите для максимального количества итераций значение 20, чтобы найти процент за 21 месяц. Смотрите предыдущий раздел, позволяющий выполнять итерационные вычисления, чтобы включить эту функцию.
Автоматическая временная метка
Циклические ссылки также можно использовать для добавления временных меток в ячейки. Вы можете загрузить образец рабочей книги для этого варианта использования . Предположим, мы хотим добавить временные метки к заказам, введенным в таблице ниже.
Мы можем использовать циклические ссылки для добавления метки времени при вводе новой информации о заказе. Для этого начните с добавления нового столбца в таблицу, где вы хотите напечатать метки времени. Введите формулу,
=IF(A2<>””,IF(I2<>””,I2,NOW()),”»)
Эта формула проверит, есть ли данные в номере заказа (ячейка A2). Если поле не пустое и ячейка с меткой времени пуста, формула вернет функцию NOW().
Обратите внимание, что нажатие клавиши Enter выдаст предупреждение о циклической ссылке, если итеративные вычисления не включены. Смотрите предыдущий раздел, позволяющий выполнять итерационные вычисления, чтобы включить эту функцию. На этот раз максимальные итерации или числа максимальных изменений мало что значат, потому что нам нужна только одна итерация, поэтому вы можете оставить эти два входных параметра в их значениях по умолчанию.
Теперь каждый раз, когда мы вводим новый порядок и создаем новую строку, временная метка будет автоматически печататься в столбце Timestamp.
