Включить автоматический пересчет формул в Excel

Обновлено: 03.07.2024

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Обзор

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

Дополнительная информация

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

< /tr>

Имя файла	Вид документа	Режим, используемый для сохранения файла
Auto1.xlsx	Workbook	Automatic
Manual1.xlsx	Рабочая книга	Вручную
Auto2.xlsx	Рабочая книга	Автоматически

Следующие утверждения относятся к режимам расчета в Excel:

Для первого открываемого документа используется режим расчета, в котором он был сохранен в последний раз. Документы, открытые позже, используют тот же режим. Например, если открыть Auto1.xlsx, а затем открыть Manual1.xlsx, в обоих документах будет использоваться автоматический расчет (режим, используемый Auto1.xlsx). Если вы откроете Manual1.xlsx, а затем Auto1.xlsx, в обоих документах будут использоваться расчеты вручную.
При изменении режима расчета одного открытого документа изменяется режим для всех открытых документов. Например, если Auto1.xlsx и Auto2.xlsx открыты, изменение режима расчета Auto2.xlsx на ручной также приводит к изменению режима Auto1.xlsx на ручной.
Все листы, содержащиеся в рабочей книге, используют один и тот же режим расчета. Например, если Auto2.xlsx содержит три листа, изменение режима расчета первого листа на ручной также приведет к изменению режима расчета на ручной на двух других листах.
Если все другие документы закрыты и вы создаете новый документ, в новом документе используется тот же режим расчета, что и в ранее закрытых документах. Однако, если вы используете шаблон, режим расчета — это режим, указанный в шаблоне.
Если режим расчета в рабочей книге изменился и файл сохранен, текущий режим расчета сохраняется. Например, если открыть Auto1.xlsx, открыть Manual1.xlsx и сразу же сохранить Manual1.xlsx, режим расчета сохраняется как автоматический.

Как управлять режимом расчета

Все открытые документы используют один и тот же режим расчета. Для работы с документами, использующими разные режимы расчета, необходимо соблюдать специальные процедуры. Например, если вы работаете с Auto1.xlsx и хотите открыть Manual1.xlsx в ручном режиме расчета, выполните одно из следующих действий:

Перед открытием файла Manual1.xlsx установите Auto1.xlsx в режим ручного расчета.
Закройте Auto1.xlsx (и все другие открытые документы) перед открытием Manual1.xlsx.

В Excel можно выбрать четыре режима расчета. Они следующие:

Пользователь может инициировать перерасчет в Microsoft Excel несколькими способами, например:

Ввод новых данных (если Excel находится в режиме автоматического пересчета, описанном далее в этом разделе).
Явное указание Excel пересчитать всю книгу или ее часть.
Удаление или вставка строки или столбца.
Сохранение книги, когда установлен параметр "Пересчитывать перед сохранением".
Выполнение определенных действий автофильтра.
Дважды щелкните разделитель строк или столбцов (в режиме автоматического расчета).
Добавление, редактирование или удаление определенного имени.
Переименование листа.
Изменение положения листа по отношению к другим листам.
Скрытие или отображение строк, но не столбцов.

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

Зависимость, грязные ячейки и пересчитанные ячейки

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

Построение дерева зависимостей
Построение цепочки расчетов
Пересчет ячеек

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

Когда в книгу вносятся структурные изменения, например при вводе новой формулы, Excel восстанавливает дерево зависимостей и цепочку вычислений. При вводе новых данных или новых формул Excel помечает все ячейки, зависящие от этих новых данных, как требующие пересчета. Ячейки, помеченные таким образом, называются грязными. Все прямые и косвенные зависимости помечаются как грязные, поэтому, если B1 зависит от A1, а C1 зависит от B1, при изменении A1 и B1, и C1 помечаются как грязные.

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

После пометки ячеек как грязных при последующем выполнении пересчета Excel повторно оценивает содержимое каждой грязной ячейки в порядке, определяемом цепочкой вычислений. В приведенном ранее примере это означает, что сначала идет B1, а затем C1. Этот пересчет происходит сразу после того, как Excel закончит помечать ячейки как грязные, если режим пересчета является автоматическим; в противном случае это происходит позже.

Начиная с Microsoft Excel 2002, объект Range в Microsoft Visual Basic для приложений (VBA) поддерживает метод Range.Dirty, который помечает ячейки как требующие вычисления. Когда он используется вместе с методом Range.Calculate (см. следующий раздел), он включает принудительный пересчет ячеек в заданном диапазоне. Это полезно, когда вы выполняете ограниченные вычисления во время макроса, когда режим вычисления установлен на ручной, чтобы избежать накладных расходов на вычисление ячеек, не связанных с функцией макроса. Методы расчета диапазона недоступны через C API.

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

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

Асинхронные определяемые пользователем функции (UDF)

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

Изменяемые и неизменяемые функции

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

Следующие функции Excel являются энергозависимыми:

И VBA, и C API поддерживают способы информирования Excel о том, что определяемая пользователем функция (UDF) должна обрабатываться как изменчивая. С помощью VBA определяемая пользователем функция объявляется изменчивой следующим образом.

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

С помощью C API вы можете зарегистрировать функцию XLL как энергозависимую перед ее первым вызовом. Это также позволяет вам включать и выключать изменчивый статус функции рабочего листа.

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

Режимы расчета, команды, выборочный пересчет и таблицы данных

Excel поддерживает три режима расчета:

Автоматически
Автоматические таблицы исключений
Вручную

Если для расчета задан автоматический режим, пересчет происходит после каждого ввода данных и после определенных событий, таких как примеры, приведенные в предыдущем разделе. Для очень больших книг время пересчета может быть настолько большим, что пользователи должны ограничивать его, то есть пересчитывать только тогда, когда это необходимо. Для этого Excel поддерживает ручной режим. Пользователь может выбрать режим через систему меню Excel или программно, используя VBA, COM или C API.

Таблицы данных представляют собой специальные структуры на листе. Сначала пользователь настраивает расчет результата на листе. Это зависит от одного или двух ключевых изменяемых входов и других параметров. Затем пользователь может создать таблицу результатов для набора значений для одного или обоих ключевых входных данных. Таблица создается с помощью Мастера таблиц данных. После того, как таблица настроена, Excel по одному вставляет входные данные в расчет и копирует полученное значение в таблицу. Поскольку можно использовать один или два входа, таблицы данных могут быть одномерными или двумерными.

Пересчет таблиц данных выполняется несколько иначе:

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

Учитывая другой способ, которым Excel обрабатывает пересчет таблиц данных, а также тот факт, что расчет больших таблиц, зависящих от сложных или длительных вычислений, может занять много времени, Excel позволяет отключить автоматический расчет таблиц данных. Для этого установите режим расчета «Автоматически, кроме таблиц данных». Когда расчет находится в этом режиме, пользователь пересчитывает таблицы данных, нажимая F9 или аналогичную программную операцию.

Excel предоставляет методы, с помощью которых можно изменить режим пересчета и управлять пересчетом. Эти методы улучшались от версии к версии, чтобы обеспечить более точный контроль. Возможности C API в этом отношении отражают те, которые были доступны в Excel версии 5, и поэтому не дают вам такого же контроля, как при использовании VBA в более поздних версиях.

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

Вычисление диапазона

VBA: Range.Calculate (представлено в Excel 2000, изменено в Excel 2007) и Range.CalculateRowMajorOrder (представлено в Excel 2007)

C API: не поддерживается

Ручной режим

Пересчитывает только ячейки в заданном диапазоне независимо от того, грязные они или нет. Поведение метода Range.Calculate изменено в Excel 2007; однако метод Range.CalculateRowMajorOrder по-прежнему поддерживает старое поведение.

Автоматический или автоматический режим кроме таблиц

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

Активное вычисление рабочего листа

VBA: ActiveSheet.Calculate

C API: xlcCalculateDocument

Все режимы

Пересчитывает ячейки, помеченные для расчета, только на активном листе.

Указанный расчет рабочего листа

VBA: **Рабочие листы (**ссылка).Рассчитать

C API: не поддерживается

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

Excel 2000 и более поздние версии предоставляют логическое свойство рабочего листа, свойство EnableCalculation. Установка для этого параметра значения True вместо False загрязняет все ячейки на указанном листе. В автоматических режимах это также вызывает пересчет всей книги.

В ручном режиме следующий код вызывает пересчет только активного листа.

Перестроение дерева рабочей книги и принудительный перерасчет

Нажатие клавиши: CTRL+ALT+SHIFT+F9 (появилось в Excel 2002)

VBA: **Workbooks(**reference).ForceFullCalculation (появилось в Excel 2007)

C API: не поддерживается

Все режимы

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

Все открытые книги

VBA: Application.Calculate

C API: xlcCalculateNow

Все режимы

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

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

VBA: Application.CalculateFull

C API: не поддерживается

Все режимы

Пересчитывает все ячейки во всех открытых книгах. Если выбран режим расчета «Автоматически, кроме таблиц», он принудительно пересчитывает таблицы.

< бр />

Лори Кауфман

Лори Кауфман
Писатель

Лори Кауфман – специалист по технологиям с 25-летним опытом работы. Она была старшим техническим писателем, работала программистом и даже управляла собственным бизнесом в нескольких местах. Подробнее.

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

Во время пересчета формул указатель мыши принимает форму песочных часов, и вы не можете вносить какие-либо изменения в рабочий лист.

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

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

Чтобы отключить функцию автоматического расчета, откройте Excel и перейдите на вкладку ФАЙЛ.

Нажмите пункт «Параметры» в меню слева.

В диалоговом окне "Параметры Excel" нажмите "Формулы" в меню слева.

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

Следующий список определяет параметры, доступные в разделе параметров расчета:

Автоматически: вычисляет все зависимые формулы и обновляет открытые или встроенные диаграммы каждый раз, когда вы вносите изменения в значение, формулу или имя. Это параметр по умолчанию для каждого нового листа.
Автоматически, кроме таблиц данных: вычисляет все зависимые формулы и обновляет открытые или встроенные диаграммы, но не вычисляет таблицы данных, созданные с помощью функции «Таблица данных». Чтобы пересчитать таблицы данных, когда выбран этот переключатель, нажмите кнопку «Рассчитать сейчас» (F9) на вкладке «Формулы» на ленте или нажмите F9 на рабочем листе.
Вручную: расчеты на открытых листах и обновление открытых или встроенных диаграмм только при нажатии кнопки «Рассчитать сейчас» (F9) на вкладке «Формулы» на ленте или при нажатии клавиш F9 или Ctrl+= на листе.
Пересчитывать рабочую книгу перед сохранением: вычисляет открытые рабочие листы и обновляет открытые или встроенные диаграммы при их сохранении, даже если выбрана кнопка выбора «Вручную». Если вы не хотите обновлять зависимые формулы и диаграммы при каждом сохранении, отключите этот параметр.
Включить итеративный расчет: задает количество итераций, то есть количество повторных вычислений листа при поиске цели или разрешении циклических ссылок, в соответствии с числом, отображаемым в текстовом поле «Максимум итераций». Дополнительные сведения о поиске целей или разрешении циклических ссылок см. в файле справки Excel.
Максимум итераций: задает максимальное количество итераций (по умолчанию 100), если установлен флажок "Включить итеративный расчет".
Максимальное изменение: задает максимальную величину изменения значений во время каждой итерации (0,001 по умолчанию), когда установлен флажок Включить итеративный расчет.

Вы также можете переключаться между тремя основными параметрами расчета, используя кнопку "Параметры расчета" в разделе "Расчет" на вкладке "Формулы" на ленте. Однако, если вы хотите установить параметры итерации, вы должны использовать страницу «Формулы» в диалоговом окне «Параметры Word».

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

Чтобы включить функцию многопоточности, перейдите на вкладку ФАЙЛ и выберите Параметры, чтобы открыть диалоговое окно Параметры Excel, как упоминалось ранее. Нажмите «Дополнительно» в меню слева.

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

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

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

› Как добавить формулы в таблицы в Microsoft Outlook
› Добавление комментариев к формулам и ячейкам в Excel 2013
› Преобразование формулы в статическое значение в Excel 2013
› Как восстановить метки панели задач в Windows 11
› Почему прозрачные чехлы для телефонов желтеют?
› Как установить Google Play Маркет в Windows 11
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
› Что означает XD и как вы его используете?

Нажмите переключатель рядом с «Автоматически» в разделе «Параметры расчета».

Нажмите "ОК", чтобы сохранить и закрыть.

Формулы

Введите свои данные на листе. Не оставляйте пустых ячеек в диапазоне.

Перейдите на вкладку «Формулы», а затем нажмите стрелку «Параметры расчета» в группе «Расчет», чтобы отобразить раскрывающийся список. Нажмите, чтобы добавить галочку рядом с «Автоматически».

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

Перейдите на вкладку «Формулы», а затем нажмите «Автосумма» или другую функцию в группе «Библиотека функций». В качестве альтернативы щелкните вкладку «Главная», а затем нажмите «Автосумма» или другую функцию в группе «Редактирование». При нажатии кнопки со стрелкой в раскрывающемся списке «Автосумма» отображаются «Среднее», «Подсчет чисел», «Макс.», «Мин.» и «Дополнительные функции». Формула отображается в выбранной ячейке, а значения окружает анимированный пунктирный контур.

Нажмите «Ввод», чтобы активировать и отобразить вычисление в ячейке. Пунктирный контур исчезнет. Когда вы редактируете эту строку или столбец новыми значениями, ваш рабочий лист обновляется с учетом нового расчета.

Читайте также: