Пересчет в Excel

Статья
04/04/2023

Относится к: Excel 2013 | Office 2013 | Visual Studio

Пользователь может вызывать пересчет в 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 (см. следующий раздел), он включает принудительный пересчет ячеек в заданном диапазоне. Это полезно при выполнении ограниченного вычисления во время макроса, в котором режим вычисления установлен в ручном режиме, чтобы избежать дополнительных затрат на вычисление ячеек, не связанных с макрос-функцией. Методы вычисления диапазона недоступны через API C.

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

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

Асинхронные пользовательские функции

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

Переменные и постоянные функции

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

Переменными являются следующие функции Excel:

NOW
TODAY
RANDBETWEEN
OFFSET
INDIRECT
INFO (в зависимости от аргументов)
CELL (в зависимости от аргументов)
SUMIF (в зависимости от аргументов)

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

Function MyUDF(MakeMeVolatile As Boolean) As Double
   ' Good practice to call this on the first line.
   Application.Volatile MakeMeVolatile
   MyUDF = Now
End Function

�� Excel ��, �� VBA �� . Excel узнает, что пользовательская функция является переменной, только при ее первом вызове. �� , �� .

� �� API C �� XLL �� . �� .

�� Excel �� XLL, �� , �� . �� xlfVolatile �� .

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

В Excel есть три режима вычисления:

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

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

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

Пересчет таблиц данных обрабатывается немного по-другому:

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

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

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

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

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

Клавиша: нет

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

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

Ручной режим

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

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

Активное вычисление листов

Клавиши: SHIFT+F9

VBA: ActiveSheet.Calculate

API C: xlcCalculateDocument

Все режимы

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

Вычисление указанных листов

Клавиша: нет

VBA: **Worksheets(**reference ). Рассчитать

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

Все режимы

�� "��" �� . �� .

Excel 2000 � �� EnableCalculation � �� Boolean. �� True �� False, �� "��". � �� .

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

With ActiveSheet
  .EnableCalculation = False
  .EnableCalculation = True
  .Calculate
End With

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

Клавиши: CTRL+ALT+SHIFT+F9 (появились в Excel 2002)

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

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

Все режимы

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

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

Клавиша: F9

VBA: Application.Calculate

API C: xlcCalculateNow

Все режимы

�� , �� Excel �� "��", �� , � ��, �� "��". Если выбран режим вычисления "Автоматический, кроме таблиц", этот метод вычисляет таблицы, которые требуют обновления, а также все переменные функции и их зависимости.