Изменить пересчет формулы, итерацию или точность в Excel

Windows Web

Чтобы эффективно использовать формулы, вам необходимо понять три важных момента:

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

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

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

Изменить, когда лист или книга пересчитываются

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

Важно: изменение любого из параметров влияет на все открытые книги.

  1. Щелкните вкладку Файл , выберите Параметры и затем щелкните категорию Формулы .

    В Excel 2007 нажмите кнопку Microsoft Office , нажмите Excel Параметры , а затем щелкните категорию Формулы ..

  2. Выполните одно из следующих действий:

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

    • Чтобы пересчитывать все зависимые формулы, кроме таблиц данных, каждый раз, когда вы вносите изменения в значение, формулу или имя, в разделе Параметры расчета в разделе Расчет книги нажмите Автоматически, за исключением таблиц данных .

    • Чтобы отключить автоматический пересчет и пересчитать открытые книги только в том случае, если вы явно сделаете это (нажав F9), в разделе Параметры расчета в разделе Расчет книги , нажмите Вручную .

      Примечание. Когда вы щелкните Вручную , Excel автоматически установит флажок Пересчитать книгу перед сохранением . Если сохранение книги занимает много времени, снятие флажка Пересчитать книгу перед сохранением может сократить время сохранения.

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

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

Совет. Кроме того, вы можете изменить многие из этих параметров вне диалогового окна Параметры Excel . На вкладке Формулы в группе Расчет нажмите Параметры расчета , а затем нажмите Автоматически .

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

Пересчитайте рабочий лист или книгу вручную с помощью сочетаний клавиш

К

Нажмите

Пересчитать формулы, которые изменились с момента последнего вычисления, и формулы, зависящие от них, во всех открытых книгах. Если рабочая книга настроена на автоматический пересчет, вам не нужно нажимать F9 для пересчета.

F9

Пересчитайте формулы, которые изменились с момента последнего вычисления, и формулы, зависящие от них, на активном листе.

Shift + F9

Пересчитать все формулы во всех открытых книгах, независимо от того, изменились ли они с момента последний пересчет.

Ctrl + Alt + F9

Проверьте зависимые формулы, а затем пересчитайте все формулы во всех открытых книгах, независимо от того, изменились ли они с момента последнего пересчета.

Ctrl + Shift + Alt + F9

Изменение количества повторений формулы в Excel

  1. Откройте вкладку Файл , нажмите Параметры , а затем щелкните категорию Формулы .

    В Excel 2007 нажмите кнопку Microsoft Office , нажмите Параметры Excel , а затем щелкните категорию Формулы .

  2. В В разделе Параметры расчета установите флажок Включить итеративный расчет .

  3. Чтобы установите максимальное количество повторных вычислений Excel, введите количество итераций в поле Максимальное количество итераций . Чем больше количество итераций, тем больше времени потребуется Excel для пересчета рабочего листа.

  4. Чтобы установить максимальное количество изменений, вам потребуется принять между результатами пересчета, введите сумму в поле Максимальное изменение . Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для пересчета рабочего листа.

Примечание. Решатель и поиск цели являются частью набора команд, иногда называемых инструментами анализа «что, если». Обе команды используют итерацию контролируемым образом для получения желаемых результатов. Вы можете использовать Solver, когда вам нужно найти оптимальное значение для конкретной ячейки, изменив значения нескольких ячеек, или когда вы хотите применить определенные ограничения к одному или нескольким значениям в вычислении. Вы можете использовать Goal Seek, когда знаете желаемый результат одной формулы, но не знаете входное значение, необходимое формуле для определения результата.

Изменение точности вычислений в книге

Прежде чем изменять точность вычислений, имейте в виду следующие важные моменты:

По умолчанию Excel вычисляет сохраненные, а не отображаемые, значения

Отображаемое и печатаемое значение зависит от того, как вы выбираете форматирование и отображение сохраненного значения. Например, ячейка, которая отображает дату как «22.06.2008», также содержит серийный номер, который является сохраненным значением даты в ячейке. Вы можете изменить отображение даты на другой формат (например, на «22 июня 2008 г.»), но изменение отображения значения на листе не меняет сохраненное значение.

Будьте осторожны при изменении точности вычислений

Когда формула выполняет вычисления, Excel обычно использует значения, хранящиеся в ячейках, на которые ссылается формула. Например, если две ячейки содержат значение 10,005, а ячейки отформатированы для отображения значений в денежном формате, в каждой ячейке отображается значение 10,01 доллара США. Если вы сложите две ячейки вместе, результатом будет 20,01 доллара США, поскольку Excel добавляет сохраненные значения 10,005 и 10,005, а не отображаемые значения.

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

  1. Щелкните вкладку Файл , щелкните Параметры , а затем щелкните категорию Дополнительно .

    В Excel 2007 щелкните Microsoft Кнопка Office , щелкните Параметры Excel , а затем щелкните категорию Дополнительно

  2. В разделе При вычислении этой книги выберите нужную книгу, а затем установите флажок Установить точность, как показано .

Подробнее о точности в Excel

Хотя Excel ограничивает точность 15 цифрами, не означает, что 15 цифр — это предел размера числа, которое вы можете сохранить в Excel. Предел составляет 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-й цифре, поэтому все следующие цифры являются нулями..

Изменить количество процессоров, используемых для вычисления формул

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

  1. Щелкните вкладку Файл , щелкните Параметры , а затем щелкните категорию Дополнительно .

    В Excel 2007 щелкните Microsoft Office Кнопка , нажмите Параметры Excel , а затем выберите категорию Дополнительно .

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

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

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

  4. Чтобы контролировать количество процессоров, в разделе Количество потоков вычислений нажмите Вручную . Введите количество используемых процессоров (максимальное количество — 1024).

Узнайте о вычислении книг, созданных в более ранней версии Excel

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

  • Когда вы открываете книгу, созданную в текущей версии, Excel пересчитывает только формулы, которые зависят от измененных ячеек.

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

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

В Excel в Интернете результат формулы автоматически пересчитывается при изменении данных в ячейках, которые используются в этой формуле. Вы можете отключить этот автоматический пересчет и рассчитывать результаты формулы вручную. Вот как это сделать:

Примечание. Изменение параметра расчета в книге повлияет только на текущую книгу, а не на другие открытые книги в браузере.

  1. В электронной таблице Excel в Интернете щелкните вкладку Формулы .

  2. Рядом с Параметры расчета выберите в раскрывающемся списке один из следующих вариантов:

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

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

    • Чтобы отключить автоматический пересчет и пересчитывать открытые книги только в том случае, если вы это делаете явно, нажмите Вручную .

    • Чтобы вручную пересчитать книгу (включая таблицы данных), нажмите Рассчитать рабочую книгу .

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

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе ответов или предложить новую функцию или улучшение в Excel User Voice.

Оцените статью
Botgadget.ru
Добавить комментарий