Что такое итеративные вычисления excel
Циклические ссылки в excel
25.10.2013 Григорий Цапко Полезные советы
Среди пользователей Excel широко распространено мнение, что циклическая ссылка в excel является разновидностью ошибки, и от нее нужно непременно избавляться.
Между тем, именно циклические ссылки в excel способны облегчить нам решение некоторых практических экономических задач и финансовом моделировании.
Эта заметка как раз и будет призвана дать ответ на вопрос: а всегда ли циклические ссылки – это плохо? И как с ними правильно работать, чтобы максимально использовать их вычислительный потенциал.
Для начала разберемся, что такое циклические ссылки в excel 2010.
Циклические ссылки возникают, когда формула, в какой либо ячейке через посредство других ячеек ссылается сама на себя.
Например, ячейка С4 = Е 7, Е7 = С11, С11 = С4. В итоге, С4 ссылается на С4.
Наглядно это выглядит так:
На практике, зачастую, не такая примитивная связь, а более сложная, когда результаты вычисления одной формулы, порою весьма замысловатые, влияют на результат вычисления другой формулы, которая в свою очередь влияет на результаты первой. Возникает циклическая ссылка.
Предупреждение о циклической ссылке
Появление циклических ссылок очень легко определить. При их возникновении или наличии в уже созданной книге excel сразу же появляется предупреждение о циклической ссылке, которое по большому счету и описывает суть явления.
При нажатии на кнопку ОК, сообщение будет закрыто, а в ячейке содержащей циклическую ссылку в большинстве случаев появиться 0.
Предупреждение, как правило, появляется при первоначальном создании циклической ссылки, или открытии книги содержащей циклические ссылки. Если предупреждение принято, то при дальнейшем возникновении циклических ссылок оно может не появляться.
Как найти циклическую ссылку
Циклические ссылки в excel могут создаваться преднамеренно, для решения тех или иных задач финансового моделирования, а могут возникать случайно, в виде технических ошибок и ошибок в логике построения модели.
В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.
Во втором случае, мы можем вообще не знать где они находятся, например, при открытии чужого файла и появлении сообщения о наличии циклических ссылок.
Найти циклическую ссылку можно несколькими способами. Например, чисто визуально формулы и ячейки участвующие в образовании циклических ссылок в excel отмечаются синими стрелками, как показано на первом рисунке.
Если циклическая ссылка одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.
Если циклические ссылки есть еще на других листах кроме активного, то будет выведено сообщение без указания ячейки.
Если или на активном листе их более одной, то будет выведено сообщение с указанием ячейки, где циклическая ссылка появляется в первый раз, после ее удаления – ячейка, содержащая следующую циклическую ссылку и т.д.
Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.
На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.
Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После ее корректировки или удаления – со второй и т.д.
Теперь, после того как мы выяснили как найти и убрать циклическую ссылку, рассмотрим ситуации, когда делать этого не нужно. То есть когда циклическая ссылка в excel приносит нам определенную пользу.
Использование циклических ссылок
Рассмотрим пример использования циклических ссылок в финансовом моделировании. Он поможет нам понять общий механизм итеративных вычислений и дать толчок для дальнейшего творчества.
В финансовом моделировании не часто, но все же возникает ситуация, когда нам необходимо рассчитать уровень затрат по статье бюджета, зависящей от финансовый результата, на который эта статья влияет. Это может быть, например статья расходов на дополнительное премирование персонала отдела продаж, зависящая от прибыли от продаж.
После введения всех формул, у нас появляется циклическая ссылка:
Однако, ситуация не безнадежная. Нам достаточно изменить некоторые параметры Excel и расчет будет осуществлен корректно.
Еще одна ситуация когда могут быть востребованы циклические ссылки – это метод взаимных или обратных распределений косвенных затрат между непроизводственными подразделениями.
Подобные распределения делаются при помощи системы линейных уравнений, и могут быть реализованы в Excel с применением циклических ссылок.
На тему методов распределения затрат в ближайшее время появиться отдельная статья.
Пока же нас интересует сама возможность таких вычислений.
Итеративные вычисления
Для того чтобы корректный расчет был возможен, мы должны включить итеративные вычисления в параметрах Excel.
Итеративные вычисления – это вычисления повторяемые множество раз, пока не будет достигнут результат соответствующий заданным условиям (условию точности или условию количества осуществленных итераций).
Включить итеративные вычисления можно через вкладку Файл → раздел Параметры → пункт Формулы. Устанавливаем флажок «Включить итеративные вычисления».
Как правило, установленных по умолчанию предельного числа итераций и относительной погрешности достаточно для наших вычислительных целей.
Следует иметь ввиду, что слишком большое количество вычислений может существенно загружать систему и снижать производительность.
Также, говоря об итеративных вычислениях, следует отметить, что возможны три варианта развития событий.
Решение сходится, что означает получение надежного конечного результата.
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
Решение колеблется между двумя значениями, например, после первой итерации получается значение 1, после второй — значение 10, после третьей — снова 1 и т. д.
Microsoft Excel
трюки • приёмы • решения
Как в Excel с помощью итерационных вычислений посчитать чистую прибыль от вклада
Одним из способов решения данной проблемы является возможность предположить правильный ответ и затем посмотреть, насколько вы близко находитесь от реального результата. Например, в данном случае можно предположить, что, если вклад составляет 10% от чистой прибыли, мы можем взять эти 10% от валовой прибыли в 100 000. При этом сумма вклада получается равной 10 000. Если мы используем это число в формуле, то получим, что чистая прибыль равна 90 000, а 10% от нее равны 9 000. Таким образом, мы ошиблись на 1 000. Можно попробовать ещё раз. Возьмем вклад, равный 9 000. При этом чистая прибыль будет равна 91 000, а сумма вклада в 10% от этой суммы будет равна 9 100. Мы ошиблись уже всего на 100 рублей.
Если мы будем продолжать данный процесс, сумма вклада все ближе и ближе будет приближаться к истинному значению. Когда мы приблизимся достаточно близко (например, с точностью до рубля), мы можем остановиться и принять ответ за верное решение. Этот процесс называется итерациями. Конечно, мы не собираемся тратить свое драгоценное время или время компании на такие расчеты. Excel делает такие сложные вычисления очень простыми, если вы проделаете следующие шаги.
Рис. 3.5. Циклические ссылки
Рис. 3.6. Ответ после итерационных вычислений
Если вы хотите посмотреть, как именно происходит вычисление итераций в вашем случае, установите в параметрах Excel вычисления на листе вручную и предельное число итераций, равное 1. Нажимая на кнопку F9, вы сможете видеть результат вычисления очередной итерации.
Изменение пересчета, итерации или точности формулы в Excel
Для эффективного использования формул необходимо ознакомиться с тремя ключевыми понятиями.
Вычисление — это процесс расчета по формулам и последующего отображения значений результатов в ячейках, содержащих формулы. Во избежание ненужных вычислений, которые забрать время и замедлить работу компьютера, Microsoft Office Excel автоматически пересчитывает формулы только при изменении влияющих на формулу ячеек. Это является стандартной процедурой при первом открытии книги и ее редактировании. Однако тем, как и когда Excel будет пересчитывать формулы, можно управлять.
Итерация — это повторный пересчет на отдельном числовом условии. Excel не может автоматически вычислить формулу, которая ссылается (прямо или косвенно) на ячейку, содержаную эту формулу. Это называется циклской ссылкой. Если формула ссылается на одну из собственных ячеек, необходимо определить, сколько раз она должна пересчитываться. Цикловые ссылки могут итерироваться бесконечно. Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Точность — это показатель степени сходимости вычислений. Excel хранит и выполняет вычисления с точностью 15 значащих цифр. Однако существует возможность изменить точность вычислений, так что Excel при пересчете формул будет использовать для вычислений не хранимое, а отображаемое значение.
Во время вычислений можно выбирать команды и выполнять ввод чисел или формул. Для выполнения команд или других действий вычисления прерываются, а затем возобновляются снова. Если книга содержит большое число формул либо листы содержат таблицы данных или функции, автоматически пересчитываемые при каждом пересчете книги, процесс вычислений может занять значительное время. Он также может быть длительным, если листы содержат связи с другими листами или книгами. Можно изменить способ выполнения вычислений, установив параметр пересчета вручную.
Важно: Изменение любого из этих параметров влияет на все открытые книги.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Формулы».
Выполните одно из указанных ниже действий.
Для пересчета всех зависимых формул при каждом изменении значения, формулы или имени в разделе Параметры вычислений в группе Вычисления в книге выберите пункт Автоматически. Это — способ вычислений по умолчанию.
Чтобы пересчитать все зависимые формулы, кроме таблиц данных, при каждом изменении значения, формулы или имени в разделе «Параметры вычислений» в разделе «Вычисления книги» выберите значение «Автоматически» за исключением таблиц данных.
Для отключения автоматического пересчета и выполнения пересчета открытых книг только при явном требовании (с помощью клавиши F9) в разделе Параметры вычислений в группе Вычисления в книге выберите параметр Вручную.
Примечание: При выборе параметра Вручную Excel автоматически устанавливает флажок Пересчитывать книгу перед сохранением. Если сохранение книги занимает много времени, для его экономии снимите флажок Пересчитывать книгу перед сохранением.
Для ручного пересчета всех открытых книг, включая таблицы данных, и обновления всех открытых листов диаграмм на вкладке Формулы в группе Вычисление нажмите кнопку Пересчет.
Для ручного пересчета активного листа, всех диаграмм и листов диаграмм, связанных с этим листом, на вкладке Формулы в группе Вычисление нажмите кнопку Произвести вычисления.
Совет: Многие из этих параметров можно изменять и вне диалогового окна Параметры Excel. Откройте вкладку Формулы и в группе Вычисления щелкните элемент Параметры вычислений, а затем — Выполнять автоматически.
Примечание: Если лист содержит формулу, связанную с непересчитанным листом, и эта связь обновляется, появится сообщение о том, что исходный лист полностью не пересчитан. Для обновления связи с текущим значением, записанном в исходном листе, даже если оно неверно, нажмите кнопку ОК. Для прекращения обновления связи и использования предыдущего значения, полученного из исходного листа, нажмите кнопку Отмена.
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, во всех открытых книгах. Если задан автоматический пересчет книги, нажимать клавишу F9 для пересчета не требуется.
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, в активном листе.
Пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета.
Проверка зависимых формул, а затем пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Формулы».
В разделе Параметры вычислений установите флажок Включить итеративные вычисления.
Чтобы установить максимальное число пересчетов, введите в поле Предельное число итераций число итераций. Чем больше число итераций, тем больше времени потребуется для пересчета листа.
Для установки максимальной разности между результатами пересчетов введите ее в поле Относительная погрешность. Чем меньше это число, тем точнее результат, и тем больше времени требуется на пересчет листа.
Примечание: «Поиск решения» и «Поиск цели» — это часть набора команд, иногда называемых инструментами анализ «что если» средств. Обе команды используют итерацию контролируемым способом для получения нужных результатов. С помощью функции «Поиск решения» можно найти оптимальное значение для определенной ячейки, настроив значения в нескольких ячейках или применив определенные ограничения к одному или нескольким значениям в вычислениях. Если вы знаете нужный результат одной формулы, но не входные значения, необходимые для его определения, можно использовать средству поиска целью.
Изменяя точность вычислений в книге, необходимо учитывать важные факторы, указанные ниже.
По умолчанию в вычислениях используются хранимые, а не отображаемые значения
Отображаемые и печатаемые значения зависят от выбора формата и отображения хранимых значений. Например, ячейка, в которой отображается дата «22.06.2008», также содержит порядковый номер, который является хранимым значением даты в этой ячейке. Можно изменить отображение даты на другой формат (например, «22-июнь-2008»), но хранимое в ячейке значение не изменится.
С осторожностью изменяйте точность вычислений
При пересчете формулы Excel обычно использует значения, хранящиеся в ячейках, на которые она ссылается. Например, если в двух ячейках содержится значение 10,005, а формат ячеек предполагает отображение значений в виде денежных единиц, в каждой из ячеек будет отображаться значение 10,01 ₽. При сложении этих значений результат будет равен 20,01 ₽, поскольку Excel складывает хранимые значения 10,005 и 10,005, а не отображаемые значения.
При изменении точности вычислений в книге путем использования отображаемых (форматированных) значений Excel безвозвратно меняет точность хранимых в ячейках значений от полной (15 цифр) до любого другого числа десятичных знаков отображаемого формата. После такого изменения возврат к исходной точности невозможен.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Дополнительно.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Дополнительные параметры».
В разделе При пересчете этой книги выберите нужную книгу и установите флажок Задать указанную точность.
Хотя Excel ограничивает точность до 15 десятичных разрядов, это не означает, что максимальное число, которое можно хранить в Excel, ограничено 15 разрядами. Для положительных чисел предельное значение равно 9,99999999999999E+307, а для отрицательных значений — –9.99999999999999E+307. Эти числа приблизительно равны 1 или –1 с 308 нулями.
Точность в Excel означает, что любое число с разрядностью более 15 хранится и отображается только с точностью в 15 десятичных разрядов. Десятичная запятая может стоять перед любым из этих разрядов. Все разряды справа от 15-го обнуляются. Например, у числа 1234567,890123456 16 разрядов (7 до запятой и 9 после). В Excel это число хранится и отображается как 1234567,89012345 (в строке формулы и ячейке). Если установить для ячейки формат, отображающий все разряды (вместо экспоненциального формата, такого как 1,23457E+06), вы увидите, что число показывается как 1234567,890123450. 6 в конце (16-й разряд) отбрасывается и заменяется на 0. Последним является 15-й разряд, а все последующие равны нулю.
Компьютер может содержать более одного процессора (несколько физических процессоров) или поддерживать многопоточность (несколько логических процессоров). На таких компьютерах можно уменьшить или контролировать время, требуемое для пересчета книг, содержащих множество формул, путем определения числа процессоров, используемых для пересчета. Во многих случаях пересчет может выполняться частями одновременно. Распределение нагрузки между несколькими процессорами может уменьшить общее время пересчета.
На вкладке Файл нажмите кнопку Параметры и выберите категорию Дополнительно.
В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Дополнительные параметры».
Для включения или отключения возможности использования для вычислений нескольких процессоров в разделе Формулы установите или снимите флажок Включить многопоточные вычисления.
Заметка Этот флажок по умолчанию установлен, и для вычислений используются все процессоры. Число процессоров на компьютере определяется автоматически и отображается рядом с параметром использовать все процессоры данного компьютера.
Дополнительно при выборе параметра Включить многопоточные вычисления можно задавать число используемых процессоров компьютера. Например, можно ограничить число процессоров, используемых для пересчета, если нужно выделить процессорное время другим выполняемым на компьютере программам.
Чтобы управлять числом процессоров, в области «Число потоковвычислений» выберите «Вручную». Введите число процессоров (максимальное число — 1024).
Чтобы гарантировать правильность пересчета старых книг, приложение Excel при первом открытии книги, сохраненной в более старой версии Excel, и книги, созданной в текущей версии, ведет себя по-разному.
При открытии книги, созданной в текущей версии, в Excel пересчитываются только формулы, зависящие от измененных ячеек.
При открытии книги, созданной в более ранней версии Excel, пересчитываются все формулы в книге. Это гарантирует полную оптимизацию книги для использования в текущей версии Excel. Исключением является ситуация, когда книга находится в другом режиме вычислений, например ручном.
Поскольку полный пересчет требует больше времени, чем частичный, открытие книги, которая не была сохранена в формате текущей версии Microsoft Excel, может потребовать больше времени. После сохранения книги в формате текущей версии Microsoft Excel книга будет открываться быстрее.
В Excel в Интернете результаты формулы автоматически пересчитываются при изменении данных в ячейках, которые используются в этой формуле. Этот автоматический пересчет можно отключить и вычислить результаты формулы вручную. Вот как это сделать:
Примечание: Изменение параметра вычисления влияет только на текущую книгу, а не на другие открытые книги в браузере.
В Excel в Интернете щелкните вкладку «Формулы».
Рядом с параметрами вычисленийвыберите один из следующих параметров в этом меню:
Чтобы пересчитать все зависимые формулы при каждом изменении значения, формулы или имени, выберите значение «Автоматически». Этот параметр установлен по умолчанию.
Чтобы пересчитать все зависимые формулы, кроме таблиц данных, при каждом изменении значения, формулы или имени нажимайте кнопку «Автоматически, кроме таблиц данных».
Чтобы отключить автоматический пересчет и пересчет открытых книг только явным образом, нажмите кнопку «Вручную».
Чтобы пересчитать книгу вручную (включая таблицы данных), нажмите кнопку «Вычислить книгу».
Примечание: В Excel в Интернете нельзя изменить количество пересчетов формулы до тех пор, пока не будет выполнены определенное числовый условие, а также изменить точность вычислений с помощью отображаемого значения, а не хранимого значения при пересчете формул. Однако это можно сделать в приложении Excel для настольных систем. Чтобы задать параметры вычислений и изменить пересчет, итерацию или точность формулы, откройте книгу с помощью кнопки «Открыть в Excel».
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Удаление или разрешение циклической ссылки
Вы ввели формулу, но она не работает. Вместо этого вы получили это сообщение о цикл такой ссылке. Миллионы людей имеют одинаковые проблемы, и это происходит потому, что формула пытается вычислить себя, а функция итеративных вычислений отключена. Вот как он выглядит:
Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить эту проблему, можно переместить формулу в другую ячейку. Нажмите CTRL+X, чтобы вырезать формулу, выберите другую ячейку и нажмите CTRL+V, чтобы в нее в нее врезаться.
Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:
Вы также можете попробовать один из описанных ниже способов.
Если вы только что ввели формулу, начните с этой ячейки и проверьте, есть ли в ней ссылка. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также приводят к ошибкам цикловой ссылки.
Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.
Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.
Проверьте формулу в ячейке. Если вам не удается определить, является ли эта ячейка причиной циклической ссылки, выберите в подменю Циклические ссылки следующую ячейку.
Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение «Циклические ссылки».
В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.
При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение «Циклические ссылки» без адресов ячеек.
Вы можете перемещаться между ячейками в цикле, дважды щелкнув стрелку зависимости. Стрелка указывает на ячейку, которая влияет на значение выбранной ячейки. Чтобы отследить стрелку трассировки, щелкните Формулыи выберите Зависимые зависимые от нее зависимые зависимые от нее зависимые.
Предупреждение о циклической ссылке
Когда Excel впервые находит циклическую ссылку, появляется предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.
При закрытии сообщения Excel отображает в ячейке нуль или последнее вычисляемого значения. А теперь, возможно, вы думаете: «Повесить, последнее вычисляемая величина?» Да. В некоторых случаях формула может выполниться успешно, прежде чем она пытается вычислить себя. Например, формула, использующая функцию ЕСЛИ, может работать до тех пор, пока пользователь не вложит в формулу аргумент (часть данных, которую нужно правильно выполнить), которая вызывает вычисления самой формулы. В этом случае Excel сохраняет значение из последнего успешного вычисления.
Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:
Щелкните формулу в строке формулы и нажмите клавишу ВВОД.
Важно Во многих случаях при создании дополнительных формул с циклическими ссылками предупреждающее сообщение в приложении Excel больше не отображается. Ниже перечислены некоторые, но не все, ситуации, в которых предупреждение появится.
Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.
Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.
Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.
Пользователь открывает книгу, содержащую циклическую ссылку.
При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.
Итеративные вычисления
Иногда может потребоваться использовать циклические ссылки, так как они приводят к итерации функций— повторяйте их до тех пор, пока не будет выполнены определенное числовые условия. Это может замедлить компьютер, поэтому итеративные вычисления обычно отключаются в Excel.
Если вы не знакомы с итеративными вычислениями, вероятно, вы не захотите оставлять активных циклических ссылок. Если же они вам нужны, необходимо решить, сколько раз может повторяться вычисление формулы. Если включить итеративные вычисления, не изменив предельное число итераций и относительную погрешность, приложение Excel прекратит вычисление после 100 итераций либо после того, как изменение всех значений в циклической ссылке с каждой итерацией составит меньше 0,001 (в зависимости от того, какое из этих условий будет выполнено раньше). Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.
Если вы используете Excel 2007, нажмите кнопку Microsoft Office 
нажмите кнопку Параметры Excel ивыберите категорию Формулы.
В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.
В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.
В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.
Итеративное вычисление может иметь три исход:
Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
Решение переключается между двумя значениями. Например, после первой итерации результат будет 1, после следующей — 10, после следующей — 1 и так далее.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.