Метод итерации в Excel

Обновлено: 05.07.2024

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

В 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 долл. США, и мы хотим вложить эти деньги в депозитный счет. Предположим, ежемесячная процентная ставка составляет 1,25%. Вы можете скачать образец книги для этого варианта использования. Чтобы рассчитать общую стоимость на конец 21-го месяца, мы собираемся вычислить основную сумму за каждый месяц и добавить проценты к предыдущему месяцу.

Начните с ввода начальных денежных средств, процентов и функции общей стоимости, как показано ниже.

=значение * ( 1 + процентная ставка)

Затем выберите ячейку с начальным денежным значением и добавьте ссылку на функцию общей стоимости.

Это выдаст предупреждение о круговой ссылке, если итерационные вычисления не включены. Если вы еще этого не сделали, включите этот параметр и установите Максимальное число итераций на 20, чтобы найти проценты за 21-й месяц. См. предыдущий раздел Включение итерационных вычислений, чтобы включить эту функцию.

Автоматическая отметка времени

Циклические ссылки также можно использовать для добавления меток времени в ячейки. Вы можете скачать образец книги для этого варианта использования. Предположим, мы хотим добавить метки времени к заказам, введенным в таблице ниже.

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

Эта формула проверяет наличие данных в номере заказа (ячейка A2). Если оно не пустое и ячейка временной метки пуста, формула вернет функцию СЕЙЧАС().

Обратите внимание, что нажатие клавиши Enter выдаст предупреждение о циклической ссылке, если итерационные вычисления не включены. См. предыдущий раздел Включение итерационных вычислений, чтобы включить эту функцию. На этот раз значения «Максимум итераций» или «Максимальное изменение» не имеют большого значения, потому что нам нужна только одна итерация, поэтому вы можете оставить эти два входа со значениями по умолчанию.

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

Excel предоставляет нам быстрый и простой способ выполнения простой числовой итерации в Excel. Этот инструмент называется Goal Seek, и на первый взгляд он может показаться простым, но его правильное применение может позволить вам делать в Excel несколько полезных вещей.

Вы можете найти инструмент "Поиск цели" в разделе "Анализ возможных вариантов" на вкладке "Данные":

Итерация в Excel с поиском цели

Самый очевидный способ использования Goal Seek — это именно то, что задумала Microsoft: найти входное значение, которое дает целевой результат. Это позволяет нам «поиграть» с нашими моделями и попробовать разные варианты без необходимости переделывать уравнения или рабочий лист.

Открыть поиск цели
Выберите ячейку, с помощью которой вы хотите достичь определенной цели, в поле ввода «Установить ячейку».
Введите целевое значение, которого вы хотите достичь ("стоимость").
Укажите ячейку, которую вы хотите изменить для достижения результата, или «изменив ячейку».
Нажмите "ОК".

Поиск цели повторяет входные данные для достижения цели.

Поиск корня уравнения с помощью итерации Excel

Одно из конкретных применений поиска целевого результата — использование поиска цели для поиска корня (или корней) уравнения. В этом случае целевое значение равно 0.

Итеративные вычисления в Excel

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

Решение неявных уравнений в Excel

Конкретным применением Goal Seek для итерационных расчетов в инженерии является использование инструмента для решения неявных уравнений. Неявное уравнение — это уравнение, в котором переменная появляется по обе стороны от знака равенства, и нет возможности ее дальнейшего упрощения. Типичным примером в технике является уравнение Коулбрука-Уайта, которое описывает коэффициент трения Дарси-Вейсбаха, f, в трубе на основе высоты шероховатости, гидравлического диаметра и числа Рейнольдса.

Мы можем найти f, создав ячейку для этого входного значения и создав формулы для левой и правой частей уравнения в отдельных ячейках. Правильное решение для f — это значение, при котором левая и правая части равны. Таким образом, мы можем вычесть левую часть уравнения из правой части в другой ячейке и нацелить значение 0 (или корень уравнения) с помощью поиска цели. Просто чтобы Excel был доволен, мы будем возводить результат в квадрат, чтобы целевая ячейка всегда возвращала положительное значение.

Поиск цели и итеративный расчет

Вы также можете выбрать «включить итерационные вычисления» в параметрах Excel, как показано на изображении ниже. Это позволяет Excel обрабатывать циклические ссылки или формулы ячеек, которые ссылаются на собственный результат в качестве входных данных для формулы ячейки. При выборе этого метода необходимо правильно установить максимальное количество итераций и максимальное изменение (точность), чтобы избежать очень длинного итеративного цикла.

Поиск цели лучше, чем этот вариант, поскольку он выполняет итерацию более контролируемым образом.

Предупреждение

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

В какой-то момент вычислений нам может понадобиться использовать счетчик вручную или автоматически, чтобы получить нужные нам значения. Счетчики работают с повторяющимся действием или повторяющейся командой для получения желаемого результата.

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

Microsoft Excel также может позволить пользователям циклически повторять значения. Максимальное значение, которое вы можете зациклить, обычно равно 1000.

Ниже приведены шаги, которые вы можете использовать или выполнять для выполнения циклических или итерационных вычислений.

Шаг 1

Откройте новый лист Excel на своем компьютере и вставьте в него эти символы. Дайте данным заголовок, простой счетчик. В ячейке A2 введите начало, в ячейке A3 введите конец, а в ячейке A4 введите счетчик. Назначьте значения следующим образом: ячейка A2 – 0, ячейка A3 – 100, а ячейка A4 – пустая.

Шаг 2

На этом шаге мы собираемся запустить наш счетчик. Чтобы запустить счетчик, мы используем следующую формулу = ЕСЛИ (B4> B4, B4+1, B3). Вставляем формулу в строку формул или в ячейку B4. После нажатия кнопки ввода результат в ячейке B4 будет равен 0.

Чтобы наш счетчик работал, максимальное значение должно быть 1. Чтобы изменить это, нажмите на файл, прокрутите вниз и выберите параметры. В опциях нажмите на формулы, и появится окно. Измените параметры вычислений на ручной и установите максимальное количество итераций равным 1.

Нажмите "ОК", чтобы внести изменения. После этого значение вашего счетчика изменится на единицу на листе Excel.

Шаг 3

Теперь пришло время выполнить итеративный расчет на нашем листе Excel. Используйте кнопку F9 для ручного подсчета. Счетчик должен пройти весь путь до 100, потому что в данном случае это наше конечное значение. Вы также можете установить другое значение, если оно будет меньше 1000. Вот как будет работать счетчик.

Чтобы понять, что такое итеративный расчет в Excel, сначала нужно понять, что такое циклическая ссылка.

Циклическая ссылка возникает, когда вы вводите формулу в одной ячейке, которая ссылается на другую ячейку, но эта ячейка также ссылается на ячейку.

Ячейка A1: = 1+A2
Ячейка A2: = 1+A1

Приведенные выше формулы ссылаются друг на друга, это называется круговой ссылкой (ссылаются друг на друга, образуя круг). Формула завершится ошибкой, и появится окно с ошибкой ниже.

Итеративный расчет в Excel

Начиная с Excel 2007, появилась новая функция, позволяющая использовать циклические ссылки.

Ниже приведен снимок экрана Excel 2013, в других версиях также есть флажок «Включить итеративный расчет», установите его, чтобы включить.

"Максимальное количество итераций" определяет, сколько раз вы хотите зациклить вычисление.

"Максимальное изменение" определяет максимально допустимую величину изменения между результатами пересчета, чем меньше, тем медленнее, но точнее.

Чтобы проиллюстрировать, как работает итеративный расчет, выполните следующие шаги.

1) Отключить «итеративный расчет»

2) Введите следующую формулу

Ячейка A1: = 1+A2
Ячейка A2: = 1+A1

3) Установите Максимальное количество итераций на 10, оставьте Максимальное изменение по умолчанию, установите флажок, чтобы включить итеративный расчет

Результат

A1 сразу покажет 19, а A2 покажет 20. Базовый механизм для каждой итерации показан ниже.

< td>3

Итерация	Значение A1	Значение A2< /td>
1	1	2
2	4
3	5	6
4	7	8
5	9	10
6	11	12
7	13	14
8	15	16
9	17	18
10	19	20

Обратите внимание, что по умолчанию для расчета рабочей книги задано значение «Автоматически».

Каждый раз, когда вы редактируете какую-либо ячейку (инициируется событие Worksheet_Change), ячейки A1 и A2 будут пересчитываться еще раз на основе предыдущего результата.

Это означает, что A1 становится равным 39, а A2 становится равным 40 при первом пересчете.

Чтобы избежать повторного расчета, следует изменить расчет рабочей книги с «Автоматический» на «Вручную». A1 и A2 будут обновляться, только если вы их отредактируете.

Цель итеративного расчета Excel

Вышеприведенный пример демонстрирует механизм, лежащий в основе процесса расчета итераций, но зачем он нам нужен?

Целью этого является моделирование цикла VBA, но вам не нужно знать, как писать VBA.

В качестве примера возьмем For Loop, приведенный ниже код имитирует описанные выше действия.

Public Sub floop()
Для i = от 1 до 10
Диапазон ("A1"). Значение = Диапазон ("B1"). Значение + 1
Диапазон ("B1" ).Value = Range("A1").Value + 1
Следующий я
Конец подпункта

Вы даже можете вставить условие ЕСЛИ в итеративный расчет, чтобы указать, когда остановиться, точно так же, как в цикле VBA.

Читайте также: