Расчеты в Excel: автоматические, ручные, итеративные

В учебном пособии объясняются основные параметры расчетов в Excel и способы их настройки для автоматического и ручного пересчета формул.

Чтобы эффективно использовать формулы Excel, необходимо понимать, как Microsoft Excel выполняет вычисления. Есть много деталей, которые вы должны знать об основных формулах Excel, функциях, порядке арифметических операций и так далее. Менее известны, но не менее важны «фоновые» настройки, которые могут ускорить, замедлить или даже остановить вычисления в Excel.

В целом, есть три основных параметра вычислений в Excel, с которыми вы должны быть знакомы:

Режим расчета — пересчитываются ли формулы Excel вручную или автоматически.

Итерация — количество раз, когда формула пересчитывается до тех пор, пока не будет выполнено определенное числовое условие.

Точность – степень точности расчета.

В этом уроке мы подробно рассмотрим, как работает каждая из вышеуказанных настроек и как их изменить.

Автоматический расчет Excel по сравнению с ручным расчетом (режим расчета)

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

Как изменить параметры расчета Excel

На ленте Excel перейдите к Формулы вкладка > Расчет нажмите кнопку Параметры расчета и выберите один из следующих параметров:

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

Automatic Except for Data Tables — автоматический пересчет всех зависимых формул, кроме таблиц данных.

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

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

Кроме того, вы можете изменить настройки вычислений Excel через Параметры Excel:

В Excel 2010, Excel 2013 и Excel 2016 перейдите к Файл > Опции > Формулы > Варианты расчета раздел > Расчет рабочей тетради.
В Excel 2007 щелкните Кнопка офиса > Параметры Excel > Формулы > Расчет рабочей тетради.
В Excel 2003 щелкните Инструменты > Опции > Расчет > Расчет.

Советы и примечания:

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

Как принудительно пересчитать в Excel

Если вы отключили автоматический расчет Excel, т.е. Руководство настройки расчета, вы можете заставить Excel выполнить перерасчет одним из следующих способов.

Чтобы вручную пересчитать все открытые рабочие листы и обновить все открытые листы диаграмм, перейдите к Формулы вкладка > Расчет группу и нажмите кнопку «Рассчитать сейчас».

Чтобы пересчитать только активный рабочий лист, а также любые диаграммы и листы диаграмм, связанные с ним, перейдите к Формулы вкладка > Расчет группу и нажмите кнопку Рассчитать лист.

Другой способ пересчета рабочих листов вручную — использование сочетаний клавиш:

F9 пересчитывает формулы во всех открытых книгах, но только те формулы, которые изменились с момента последнего вычисления, и формулы, зависящие от них.
Shift + F9 пересчитывает измененные формулы только на активном листе.
Ctrl+Alt+F9 заставляет Excel пересчитывать абсолютно все формулы во всех открытых книгах, даже те, которые не были изменены. Если вы чувствуете, что некоторые формулы показывают неправильные результаты, используйте этот ярлык, чтобы убедиться, что все было пересчитано.
Ctrl + Shift + Alt + F9 сначала проверяет формулы, зависящие от других ячеек, а затем пересчитывает все формулы во всех открытых книгах, независимо от того, изменились они с момента последнего вычисления или нет.

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

Microsoft Excel использует итерацию (повторное вычисление) для вычисления формул, которые ссылаются на свои собственные ячейки, что называется циклическими ссылками. Excel не вычисляет такие формулы по умолчанию, потому что циклическая ссылка может бесконечно повторяться, создавая бесконечный цикл. Чтобы включить циклические ссылки на ваших листах, вы должны указать, сколько раз вы хотите, чтобы формула пересчитывалась.

Как включить и контролировать итеративный расчет в Excel

Чтобы включить итеративный расчет Excel, выполните одно из следующих действий:

В Excel 2016, Excel 2013 и Excel 2010 перейдите к Файл > «Параметры» > «Формулы»и установите флажок Включить итеративный расчет под Варианты расчета
В Excel 2007 щелкните Кнопка офиса> Параметры Excel > Формулы > Область итерации.
В Excel 2003 и более ранних версиях перейдите к Меню> Инструменты > Опции > Расчет вкладка > Итеративный расчет.

Чтобы изменить количество пересчетов формул Excel, настройте следующие параметры:

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

Настройки по умолчанию: 100 для максимального количества итераций и 0,001 для максимального изменения. Это означает, что Excel перестанет пересчитывать ваши формулы либо после 100 итераций, либо после изменения менее 0,001 между итерациями, в зависимости от того, что наступит раньше.

После настройки всех параметров нажмите ХОРОШО сохранить изменения и закрыть Параметры Excel диалоговое окно.

Точность расчетов Excel

По умолчанию Microsoft Excel вычисляет формулы и сохраняет результаты с точностью до 15 значащих цифр. Однако вы можете изменить это и заставить Excel использовать отображаемое значение вместо сохраненного значения при пересчете формул. Прежде чем вносить изменения, убедитесь, что вы полностью понимаете все возможные последствия.

Во многих случаях значение, отображаемое в ячейке, и базовое значение (сохраненное значение) различаются. Например, вы можете отобразить одну и ту же дату несколькими способами: 01.01.2017, 1 января 2017 г. и даже 17 января в зависимости от того, какой формат даты вы установили для ячейки. Независимо от того, как изменяется отображаемое значение, сохраненное значение остается неизменным (в этом примере это серийный номер 42736, который представляет 1 января 2017 г. во внутренней системе Excel). И Excel будет использовать это сохраненное значение во всех формулах и вычислениях.

Иногда разница между отображаемыми и сохраненными значениями может заставить вас думать, что результат формулы неверен. Например, если вы введете число 5,002 в одну ячейку, 5,003 в другую ячейку и выберете отображение только 2 знаков после запятой в этих ячейках, Microsoft Excel отобразит 5,00 в обеих ячейках. Затем вы складываете эти числа, и Excel возвращает 10,01, поскольку он вычисляет сохраненные значения (5,002 и 5,003), а не отображаемые значения.

Выбор Точность как показано параметр приведет к тому, что Excel навсегда изменит сохраненные значения на отображаемые значения, и приведенный выше расчет вернет 10,00 (5,00 + 5,00). Если в дальнейшем вы захотите рассчитать с полной точностью, восстановить исходные значения (5,002 и 5,003) будет невозможно.

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

Например, вы можете увеличить или уменьшить количество отображаемых знаков после запятой, нажав соответствующую кнопку на Дом вкладка, в Число группа:

Как установить точность расчета, как показано

Если вы уверены, что отображаемая точность обеспечит желаемую точность ваших вычислений в Excel, вы можете включить ее следующим образом:

Нажмите на Файл вкладка > Опциии выберите Передовой категория.
Прокрутите вниз до раздела При расчете этой книги и выберите книгу, для которой вы хотите изменить точность вычислений.
Установите флажок Установить точность как показано.
Нажмите «ОК».