Что такое итерационные вычисления в Excel
Обновлено: 20.11.2024
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Вы ввели формулу, но она не работает. Вместо этого вы получили это сообщение о «циклической ссылке». У миллионов людей такая же проблема, и это происходит из-за того, что ваша формула пытается вычислить сама себя, а у вас отключена функция, называемая итеративным вычислением. Вот как это выглядит:
Формула =D1+D2+D3 не работает, поскольку находится в ячейке D3 и пытается вычислить себя. Чтобы решить эту проблему, вы можете переместить формулу в другую ячейку. Нажмите Ctrl+X, чтобы вырезать формулу, выберите другую ячейку и нажмите Ctrl+V, чтобы вставить ее.
Другой распространенной ошибкой является использование функции, которая содержит ссылку на саму себя; например, ячейка F3 содержит =СУММ(A3:F3). Вот пример:
Вы также можете попробовать один из следующих методов:
Если вы только что ввели формулу, начните с этой ячейки и проверьте, ссылаетесь ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также вызывают ошибки циклической ссылки.
Пока вы ищете, проверьте наличие косвенных ссылок. Они возникают, когда вы помещаете формулу в ячейку A1, и она использует другую формулу в ячейке B1, которая, в свою очередь, ссылается на ячейку A1. Если вас это смущает, представьте, что это делает с Excel.
Если вы не можете найти ошибку, перейдите на вкладку "Формулы", щелкните стрелку рядом с пунктом "Проверка ошибок", выберите "Циклические ссылки" и щелкните первую ячейку в подменю.
Проверьте формулу в ячейке. Если вы не можете определить, является ли ячейка причиной циклической ссылки, щелкните следующую ячейку в подменю Циклические ссылки.
Продолжайте просматривать и исправлять циклические ссылки в книге, повторяя любые или все шаги с 1 по 3, пока в строке состояния больше не будет отображаться "Циклические ссылки".
Если вы новичок в работе с формулами, см. курс обучения Excel 2016 Essential на LinkedIn Learning.
В строке состояния в левом нижнем углу отображаются циклические ссылки и адрес ячейки одной циклической ссылки.
Если у вас есть циклические ссылки на других листах, но не на активном листе, в строке состояния отображается только «Циклические ссылки» без адресов ячеек.
Вы можете перемещаться между ячейками в циклической ссылке, дважды щелкнув стрелку трассировки. Стрелка указывает на ячейку, которая влияет на значение текущей выбранной ячейки. Чтобы отобразить стрелку трассировщика, нажмите "Формулы", а затем выберите "Прецеденты трассировки" или "Зависимые трассировки".
Подробнее о предупреждающем сообщении о циклической ссылке
Когда Excel впервые находит циклическую ссылку, отображается предупреждающее сообщение. Нажмите OK или закройте окно сообщения.
Когда вы закрываете сообщение, Excel отображает либо нулевое значение, либо последнее вычисленное значение в ячейке. И теперь вы, вероятно, скажете: «Подождите, последнее вычисленное значение?» да. В некоторых случаях формула может успешно работать до того, как попытается вычислить себя. Например, формула, в которой используется функция ЕСЛИ, может работать до тех пор, пока пользователь не введет аргумент (фрагмент данных, необходимый для правильной работы формулы), который заставит формулу вычислить себя. В этом случае Excel сохраняет значение последнего успешного вычисления.
Если вы подозреваете, что у вас есть циклическая ссылка в ячейке, в которой не отображается ноль, попробуйте следующее:
Выберите формулу в строке формул и нажмите клавишу ВВОД.
Важно! Во многих случаях при создании дополнительных формул, содержащих циклические ссылки, Excel больше не будет отображать предупреждающее сообщение. В следующем списке показаны некоторые, но не все сценарии, в которых появляется предупреждающее сообщение:
Вы создаете первый экземпляр циклической ссылки в любой открытой книге
Вы удаляете все циклические ссылки во всех открытых книгах, а затем создаете новую циклическую ссылку
Вы закрываете все книги, создаете новую книгу и вводите формулу, содержащую циклическую ссылку
Вы открываете книгу, содержащую циклическую ссылку
Если другие рабочие книги не открыты, вы открываете рабочую книгу, а затем создаете циклическую ссылку
Подробнее об итеративных вычислениях
Иногда вам может понадобиться использовать циклические ссылки, потому что они заставляют ваши функции выполнять итерацию — повторять до тех пор, пока не будет выполнено определенное числовое условие. Это может замедлить работу компьютера, поэтому итерационные вычисления в Excel обычно отключены.
Если вы не знакомы с итеративными вычислениями, вы, вероятно, не захотите оставлять циклические ссылки нетронутыми. Если вы это сделаете, вы можете включить итерационные вычисления, но вам нужно определить, сколько раз формула должна пересчитываться. Когда вы включаете итерационные вычисления без изменения значений максимального количества итераций или максимального изменения, Excel прекращает вычисления после 100 итераций или после того, как все значения в циклической ссылке изменятся менее чем на 0,001 между итерациями, в зависимости от того, что наступит раньше. Однако вы можете контролировать максимальное количество итераций и количество допустимых изменений.
Если вы используете Excel 2010 или более позднюю версию, нажмите «Файл» > «Параметры» > «Формулы». Если вы используете Excel для Mac, откройте меню Excel и выберите «Настройки» > «Вычисление».
Если вы используете Excel 2007, нажмите кнопку Microsoft Office , выберите "Параметры Excel" и выберите категорию "Формулы".
В разделе "Параметры расчета" установите флажок "Включить итеративный расчет". На Mac нажмите Использовать итеративный расчет.
Чтобы задать максимальное количество повторных вычислений Excel, введите количество итераций в поле Максимальное количество итераций. Чем больше число итераций, тем больше времени требуется Excel для вычисления рабочего листа.
В поле Максимальное изменение введите наименьшее значение, необходимое для продолжения итерации. Это наименьшее изменение любого расчетного значения. Чем меньше число, тем точнее результат и тем больше времени требуется Excel для вычисления рабочего листа.
Итеративный расчет может иметь три результата:
Решение сходится, что означает достижение стабильного конечного результата. Это желательное условие.
Решение расходится, что означает, что от итерации к итерации разница между текущим и предыдущим результатом увеличивается.
Решение переключается между двумя значениями. Например, после первой итерации результат равен 1, после следующей итерации результат равен 10, после следующей итерации результат равен 1 и так далее.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Итерационные расчеты могут помочь найти решение математических задач, выполняя расчеты снова и снова, используя предыдущие результаты. Это стало возможным благодаря компьютерам, которые могут многократно выполнять вычисления, чтобы найти вероятность возможных ответов, приближаясь к результатам с разных точек зрения.
В 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 выдаст предупреждение о циклической ссылке, если итерационные вычисления не включены. См. предыдущий раздел Включение итерационных вычислений, чтобы включить эту функцию. На этот раз значения «Максимум итераций» или «Максимальное изменение» не имеют большого значения, потому что нам нужна только одна итерация, поэтому вы можете оставить эти два входа со значениями по умолчанию.
Теперь каждый раз, когда мы вводим новый заказ и создаем новую строку, временная метка будет автоматически печататься в столбце "Временная метка".
В какой-то момент вычислений нам может понадобиться использовать счетчик вручную или автоматически, чтобы получить нужные нам значения. Счетчики работают с повторяющимся действием или повторяющейся командой для получения желаемого результата.
Итерация – это действие или процесс выполнения определенных действий несколько раз, пока не будет получен желаемый результат. Итерация иногда называется циклом в компьютерном программировании.
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. Базовый механизм для каждой итерации показан ниже.
| Итерация | Значение A1 | Значение A2< /td> |
| 1 | 1 | 2 |
| 2 | < td>34 | |
| 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.
Читайте также: