Для эффективного использования формул необходимо ознакомиться с тремя ключевыми понятиями.
Вычисление — это процесс расчета по формулам и последующего отображения значений результатов в ячейках, содержащих формулы. Во избежание ненужных вычислений, которые забрать время и замедлить работу компьютера, Microsoft Office Excel автоматически пересчитывает формулы только при изменении влияющих на формулу ячеек. Это является стандартной процедурой при первом открытии книги и ее редактировании. Однако тем, как и когда Excel будет пересчитывать формулы, можно управлять.
Итерации — это повторяющийся пересчет листа до тех пор, пока не будет выполнено определенное числовое условие. Excel не может автоматически вычислить формулу, которая прямо или косвенно ссылается на ячейку, содержащую формулу. Это называется циклической ссылкой. Если формула ссылается на одну из собственных ячеек, необходимо определить, сколько раз она должна пересчитываться. Циклические ссылки могут выполняться бесконечно. Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Точность — это показатель степени сходимости вычислений. Excel хранит и выполняет вычисления с точностью 15 значащих цифр. Однако существует возможность изменить точность вычислений, так что Excel при пересчете формул будет использовать для вычислений не хранимое, а отображаемое значение.
Во время вычислений можно выбирать команды и выполнять ввод чисел или формул. Для выполнения команд или других действий вычисления прерываются, а затем возобновляются снова. Если книга содержит большое число формул либо листы содержат таблицы данных или функции, автоматически пересчитываемые при каждом пересчете книги, процесс вычислений может занять значительное время. Он также может быть длительным, если листы содержат связи с другими листами или книгами. Можно изменить способ выполнения вычислений, установив параметр пересчета вручную.
Важно: Изменение любого из этих параметров влияет на все открытые книги.
-
На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.
В Excel 2007 нажмите кнопку Microsoft Office, выберите пункт Параметры Excel, а затем выберите категорию Формулы .
-
Выполните одно из указанных ниже действий.
-
Для пересчета всех зависимых формул при каждом изменении значения, формулы или имени в разделе Параметры вычислений в группе Вычисления в книге выберите пункт Автоматически. Это — способ вычислений по умолчанию.
-
Чтобы пересчитать все зависимые формулы, кроме таблиц данных , каждый раз, когда вы вносите изменения в значение, формулу или имя, в разделе Параметры вычисления в разделе Вычисление книги щелкните Автоматически, кроме таблиц данных.
-
Для отключения автоматического пересчета и выполнения пересчета открытых книг только при явном требовании (с помощью клавиши F9) в разделе Параметры вычислений в группе Вычисления в книге выберите параметр Вручную.
Примечание: При выборе параметра Вручную Excel автоматически устанавливает флажок Пересчитывать книгу перед сохранением. Если сохранение книги занимает много времени, для его экономии снимите флажок Пересчитывать книгу перед сохранением.
-
Для ручного пересчета всех открытых книг, включая таблицы данных, и обновления всех открытых листов диаграмм на вкладке Формулы в группе Вычисление нажмите кнопку Пересчет.
-
Для ручного пересчета активного листа, всех диаграмм и листов диаграмм, связанных с этим листом, на вкладке Формулы в группе Вычисление нажмите кнопку Произвести вычисления.
-
Совет: Многие из этих параметров можно изменять и вне диалогового окна Параметры Excel. Откройте вкладку Формулы и в группе Вычисления щелкните элемент Параметры вычислений, а затем — Выполнять автоматически.
Примечание: Если лист содержит формулу, связанную с непересчитанным листом, и эта связь обновляется, появится сообщение о том, что исходный лист полностью не пересчитан. Для обновления связи с текущим значением, записанном в исходном листе, даже если оно неверно, нажмите кнопку ОК. Для прекращения обновления связи и использования предыдущего значения, полученного из исходного листа, нажмите кнопку Отмена.
|
Действие |
Клавиши |
|---|---|
|
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, во всех открытых книгах. Если задан автоматический пересчет книги, нажимать клавишу F9 для пересчета не требуется. |
F9 |
|
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, в активном листе. |
SHIFT+F9 |
|
Пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета. |
CTRL+ALT+F9 |
|
Проверка зависимых формул, а затем пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета. |
CTRL+SHIFT+ALT+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 или получить поддержку в сообществах.
