Пересчет в Excel

Дата последнего изменения:1 июля 2011 г.

Область применения:Excel 2013 | Office 2013 | Visual Studio

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

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

  • путем ввода новых данных (если Excel находится в режиме автоматического пересчета, описанном далее в этой статье);

  • явным указанием Excel пересчитать всю книгу или ее часть;

  • путем удаления или вставки строки или столбца;

  • путем сохранения книги при включенном параметре Пересчет перед сохранением;

  • путем выполнения некоторых действий автофильтра;

  • двойным щелчком по разделителю строк или столбцов (в режиме автоматического вычисления);

  • путем добавления, редактирования или удаления заданного имени;

  • путем переименования листа;

  • путем изменения позиции листа относительно других листов;

  • путем скрытия или отображения строк (не столбцов).

Примечание Примечание

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

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

  1. Создание дерева зависимостей

  2. Создание цепочки вычислений

  3. Пересчет ячеек

Дерево зависимостей сообщает 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

  • RAND

  • OFFSET

  • INDIRECT

  • INFO (в зависимости от аргументов)

  • CELL (в зависимости от аргументов)

Интерфейсы 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 есть три режима вычисления:

  • Автоматический

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

  • Ручной

В автоматическом режиме вычисления пересчет происходит только после каждого ввода данных и после определенных событий, таких как примеры в предыдущем разделе. В очень больших книгах пересчет может занимать так много времени, что пользователям необходимо ограничивать эти условия, чтобы пересчет происходил только при необходимости. Для этого 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 method.

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

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

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

Клавиши: SHIFT+F9

VBA: ActiveSheet.Calculate

API C: xlcCalculateDocument

  • Все режимы

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

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

Клавиша: нет

VBA: Worksheets(ссылка).Calculate

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(ссылка).ForceFullCalculation (появилось в Excel 2007)

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

  • Все режимы

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

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

Клавиша: F9

VBA: Application.Calculate

API C: xlcCalculateNow

  • Все режимы

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

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

Клавиши: CTRL+ALT+F9

VBA: Application.CalculateFull

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

  • Все режимы

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

Показ: