Пересчет в ExcelExcel Recalculation

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

Пользователь может вызывать пересчет в Microsoft Excel несколькими способами, например:The user can trigger recalculation in Microsoft Excel in several ways, for example:

  • путем ввода новых данных (если Excel находится в режиме автоматического пересчета, описанном далее в этой статье);Entering new data (if Excel is in Automatic recalculation mode, described later in this topic).

  • явным указанием Excel пересчитать всю книгу или ее часть;Explicitly instructing Excel to recalculate all or part of a workbook.

  • путем удаления или вставки строки или столбца;Deleting or inserting a row or column.

  • путем сохранения книги при заданном параметре Пересчет перед сохранением;Saving a workbook while the Recalculate before save option is set.

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

  • двойным щелчком по разделителю строк или столбцов (в режиме автоматического вычисления);Double-clicking a row or column divider (in Automatic calculation mode).

  • путем добавления, редактирования или удаления заданного имени;Adding, editing, or deleting a defined name.

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

  • путем изменения позиции листа относительно других листов;Changing the position of a worksheet in relation to other worksheets.

  • путем скрытия или отображения строк (не столбцов).Hiding or unhiding rows, but not columns.

Примечание

В этой статье не делается различий между непосредственным нажатием клавиши или кнопки мыши пользователем и выполнением этих задач командой или макросом. Пользователь запускает команду или делает что-либо, чтобы она запустилась, поэтому это также считается действием пользователя. Таким образом, слово "пользователь" также означает "пользователь либо команда или процесс, запущенные пользователем".This topic does not distinguish between the user directly pressing a key or clicking the mouse, and those tasks being done by a command or macro. The user runs the command, or does something to cause the command to run so that it is still considered a user action. Therefore the phrase "the user" also means "the user, or a command or process started by the user."

Зависимость, "грязные" ячейки и пересчитанные ячейкиDependence, Dirty Cells, and Recalculated Cells

Вычисление листов в Excel можно рассматривать как процесс из трех этапов:The calculation of worksheets in Excel can be viewed as a three-stage process:

  1. Создание дерева зависимостейConstruction of a dependency tree

  2. Создание цепочки вычисленийConstruction of a calculation chain

  3. Пересчет ячеекRecalculation of cells

Дерево зависимостей сообщает Excel, какие ячейки зависят от других ячеек или, аналогично, какие ячейки являются прецедентами для других. Из этого дерева Excel составляет цепочку вычислений. В ней перечисляются все ячейки, которые содержат формулы, в том порядке, в котором их необходимо вычислять. Во время пересчета Excel изменяет эту цепочку, если обнаруживается формула, которая зависит от еще не вычисленной ячейки. В этом случае вычисляемая ячейка и зависящие от нее ячейки перемещаются вниз по цепочке. По этой причине время вычисления в первых нескольких циклах часто сокращается на листах, которые были только что открыты.The dependency tree informs Excel about which cells depend on which others, or equivalently, which cells are precedents for which others. From this tree, Excel constructs a calculation chain. The calculation chain lists all the cells that contain formulas in the order in which they should be calculated. During recalculation, Excel revises this chain if it comes across a formula that depends on a cell that has not yet been calculated. In this case, the cell that is being calculated and its dependents are moved down the chain. For this reason, calculation times can often improve in a worksheet that has just been opened in the first few calculation cycles.

При структурном изменении книги, например при вводе новой формулы, Excel заново создает дерево зависимостей и цепочку вычислений. При вводе новых данных или новых формул Excel помечает все ячейки, которые зависят от новых данных, как требующие пересчета. Помеченные таким образом ячейки называются "грязными". Все прямые и косвенные зависимые ячейки помечаются как "грязные", поэтому если ячейка B1 зависит от ячейки A1, а ячейка C1 — от B1, то при изменении ячейки A1 ячейки B1 и C1 помечаются как "грязные".When a structural change is made to a workbook, for example, when a new formula is entered, Excel reconstructs the dependency tree and calculation chain. When new data or new formulas are entered, Excel marks all the cells that depend on that new data as needing recalculation. Cells that are marked in this way are known as dirty . All direct and indirect dependents are marked as dirty so that if B1 depends on A1, and C1 depends on B1, when A1 is changed, both B1 and C1 are marked as dirty.

Если ячейка зависит, прямо или косвенно, от себя, то Excel обнаруживает циклическую ссылку и предупреждает пользователя. Как правило, это приводит к ошибке, которую пользователь должен исправить, а Excel предоставляет полезные графические и навигационные средства, которые помогут пользователю найти источник циклической зависимости. В некоторых случаях это условие создается намеренно. К примеру, вам нужно выполнить итеративное вычисление, где начальной точкой для следующей итерации является результат предыдущей. Excel поддерживает управление итеративными вычислениями с помощью диалогового окна параметров вычислений.If a cell depends, directly or indirectly, on itself, Excel detects the circular reference and warns the user. This is usually an error condition that the user must fix, and Excel provides very helpful graphical and navigational tools to help the user to find the source of the circular dependency. In some cases, you might deliberately want this condition to exist. For example, you might want to run an iterative calculation where the starting point for the next iteration is the result of the previous iteration. Excel supports control of iterative calculations through the calculation options dialog box.

Отметив ячейки как "грязные" при следующем пересчете, Excel повторно оценивает содержимое каждой "грязной" ячейки в порядке, определяемом цепочкой вычислений. В приведенном выше примере это означает, что сначала оценивается ячейка B1, а затем — C1. Пересчет происходит сразу после того, как Excel закончит отмечать ячейки как "грязные", если выбран автоматический режим пересчета. В противном случае это происходит позже.After marking cells as dirty, when a recalculation is next done, Excel reevaluates the contents of each dirty cell in the order dictated by the calculation chain. In the example given earlier, this means B1 is first, and then C1. This recalculation occurs immediately after Excel finishes marking cells as dirty if the recalculation mode is automatic; otherwise, it occurs later.

Начиная с Microsoft Excel 2002, объект Range в Microsoft Visual Basic для приложений (VBA) поддерживает метод Range.Dirty, который отмечает ячейки как требующие подсчета. Когда он используется совместно с методом Range.Calculate (см. следующий раздел), он включает принудительный пересчет ячеек в заданном диапазоне. Это удобно при выполнении ограниченного вычисления в макросе, где установлен ручной режим подсчета (для избежания избытка вычисляемых ячеек, не относящихся к функции макроса). Методы подсчета диапазонов недоступны через API C.Starting in Microsoft Excel 2002, the Range object in Microsoft Visual Basic for Applications (VBA) supports a method, Range.Dirty, which marks cells as needing calculation. When it is used together with the Range.Calculate method (see next section), it enables forced recalculation of cells in a given range. This is useful when you are performing a limited calculation during a macro, where the calculation mode is set to manual, to avoid the overhead of calculating cells unrelated to the macro function. Range calculation methods are not available through the C API.

В Excel 2002 и более ранних версиях Excel составлял цепочку вычислений для каждого листа в каждой открытой книге. Это несколько усложняло обработку ссылок между листами и требовало осторожности для обеспечения эффективного пересчета. В частности, в Excel 2000 необходимо сводить к минимуму зависимости между листами и присваивать листам имена в алфавитном порядке, чтобы листы, зависящие от других листов, следовали по алфавиту за листами, от которых они зависят.In Excel 2002 and earlier versions, Excel built a calculation chain for each worksheet in each open workbook. This resulted in some complexity in the way links between worksheets were handled, and required some care to ensure efficient recalculation. In particular, in Excel 2000, you should minimize cross-worksheet dependencies and name worksheets in alphabetical order so that sheets that depend on other sheets come alphabetically after the sheets they depend on.

В Excel 2007 логика была улучшена для поддержки пересчета в нескольких потоках, чтобы разделы цепочки вычислений не зависели друг от друга и для них можно было проводить подсчеты одновременно. Вы можете настроить Excel для использования нескольких потоков на компьютере с одним процессором или одного потока на многопроцессорном или многоядерном компьютере.In Excel 2007, the logic was improved to enable recalculation on multiple threads so that sections of the calculation chain are not interdependent and can be calculated at the same time. You can configure Excel to use multiple threads on a single processor computer, or a single thread on a multi-processor or multi-core computer.

Асинхронные пользовательские функцииAsynchronous User Defined Functions (UDFs)

Когда вычисление обнаруживает асинхронную пользовательскую функцию, оно сохраняет состояние текущей формулы, запускает пользовательскую функцию и продолжает оценивать остальные ячейки. Когда вычисление завершает оценку ячеек, Excel ждет завершения асинхронных функций, если они еще выполняются. По мере того как каждая асинхронная функция сообщает о результатах, Excel завершает формулу, а затем запускает новую передачу вычисления, чтобы пересчитать ячейки, которые используют ячейку со ссылкой на асинхронную функцию.When a calculation encounters an asynchronous UDF, it saves the state of the current formula, starts the UDF and continues evaluating the rest of the cells. When the calculation finishes evaluating the cells Excel waits for the asynchronous functions to complete if there are still asynchronous functions running. As each asynchronous function reports results, Excel finishes the formula, and then runs a new calculation pass to re-compute cells that use the cell with the reference to the asynchronous function.

Переменные и постоянные функцииVolatile and Non-Volatile Functions

Excel поддерживает переменные функции, то есть функции, значения которых в разные моменты могут отличаться, даже если ни один из аргументов (если они принимаются) не изменился. Excel повторно оценивает ячейки, которые содержат переменные функции, вместе со всеми зависимыми функциями при каждом пересчете. По этой причине чрезмерное использование переменных функций может замедлить пересчет. Используйте их экономно.Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.

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

  • NOWNOW

  • TODAYTODAY

  • RANDBETWEENRANDBETWEEN function

  • OFFSETOFFSET

  • INDIRECTINDIRECT

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

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

  • SUMIF (в зависимости от аргументов)CELL (depending on its arguments)

Интерфейсы API VBA и C поддерживают способы сообщить Excel, что пользовательскую функцию следует обрабатывать как переменную. В VBA пользовательская функция объявляется переменной следующим образом.Both the VBA and C API support ways to inform Excel that a user-defined function (UDF) should be handled as volatile. By using VBA, the UDF is declared as volatile as follows.

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 узнает, что пользовательская функция является переменной, только при ее первом вызове. Переменную пользовательскую функцию можно сделать постоянной, как в следующем примере.By default, Excel assumes that VBA UDFs are not volatile. Excel only learns that a UDF is volatile when it first calls it. A volatile UDF can be changed back to non-volatile as in this example.

С помощью API C можно зарегистрировать функцию XLL как переменную до ее первого вызова. Он также позволяет включать и отключать переменное состояние функции листа.Using the C API, you can register an XLL function as volatile before its first call. It also enables you to switch on and off the volatile status of a worksheet function.

По умолчанию Excel обрабатывает пользовательские функции XLL, которые принимают диапазоны в качестве аргументов и объявлены как эквиваленты листа макросов (изменчивые). Вы можете отключить это состояние по умолчанию с помощью функции xlfVolatile при первом вызове пользовательской функции.By default, Excel handles XLL UDFs that take range arguments and that are declared as macro-sheet equivalents as volatile. You can turn this default state off using the xlfVolatile function when the UDF is first called.

Режимы вычисления, команды, выборочный пересчет и таблицы данныхCalculation Modes, Commands, Selective Recalculation, and Data Tables

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

  • AutomaticAutomatic

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

  • ManualManual

В автоматическом режиме вычисления пересчет происходит только после каждого ввода данных и после определенных событий, таких как примеры в предыдущем разделе. В очень больших книгах пересчет может занимать так много времени, что пользователям необходимо ограничивать эти условия, чтобы пересчет происходил только при необходимости. Для этого Excel поддерживает ручной режим. Пользователь может выбрать режим в системе меню Excel или программным способом с помощью API VBA, COM или C.When calculation is set to automatic, recalculation occurs after every data input and after certain events such as the examples given in the previous section. For very large workbooks, recalculation time might be so long that users must limit when this happens, that is, only recalculating when they need to. To enable this, Excel supports the manual mode. The user can select the mode through the Excel menu system, or programmatically using VBA, COM, or the C API.

Таблицы данных — это специальные структуры на листе. Сначала пользователь настраивает вычисление результата на листе. Это зависит от одного или двух изменяемых наборов данных, введенных с клавиатуры, и других параметров. Затем пользователь может создать таблицу результатов для значений одного или обоих вводов с клавиатуры. Таблица создается с помощью мастера таблиц данных. После настройки таблицы Excel по одному отправляет наборы введенных данных в вычисление и копирует полученное значение в таблицу. Так как можно использовать один или два набора введенных данных, таблицы данных могут быть одномерными или двумерными.Data tables are special structures in a worksheet. First, the user sets up the calculation of a result on a worksheet. This depends on one or two key changeable inputs and other parameters. The user can then create a table of results for a set of values for one or both of the key inputs. The table is created by using the Data Table Wizard. After the table is set up, Excel plugs the inputs one-by-one into the calculation and copies the resulting value into the table. As one or two inputs can be used, data tables can be one- or two-dimensional.

Пересчет таблиц данных обрабатывается немного по-другому:Recalculation of data tables is handled slightly differently:

  • Пересчет обрабатывается асинхронно в отличие от обычного пересчета книг, поэтому пересчет больших таблиц может занимать больше времени, чем пересчет остальных элементов книги.Recalculation is handled asynchronously to regular workbook recalculation so that large tables might take longer to recalculate than the rest of the workbook.

  • Циклические ссылки допускаются. Если вычисление, используемое для получения результата, зависит от одного или нескольких значений из таблицы данных, то Excel не возвращает ошибку циклической зависимости.Circular references are tolerated. If the calculation that is used to get the result depends on one or more values from the data table, Excel does not return an error for the circular dependency.

Учитывая, что Excel по-другому обрабатывает пересчет таблиц данных, а вычисление больших таблиц, зависящих от сложных или длинных вычислений, может занимать много времени, Excel позволяет отключить автоматическое вычисление таблиц данных. Для этого выберите режим вычисления "Автоматический, кроме таблиц". В этом режиме пользователь может пересчитывать данные, нажав клавишу F9 или выполнив эквивалентную программную операцию.Given the different way that Excel handles recalculation of data tables, and the fact that large tables that depend on complex or lengthy calculations can take a long time to calculate, Excel lets you disable the automatic calculation of data tables. To do this, set the calculation mode to Automatic except Data Tables. When calculation is in this mode, the user recalculates the data tables by pressing F9 or some equivalent programmatic operation.

Excel предоставляет методы, с помощью которых можно изменять режим пересчета и управлять им. Эти методы улучшались от версии к версии, чтобы обеспечить возможность более точного управления. Возможности API C в этом отношении отражают возможности, доступные в Excel версии 5, поэтому не предоставляют такого управления, как при использовании VBA в более поздних версиях.Excel exposes methods through which you can alter the recalculation mode and control recalculation. These methods have been improved from version to version to allow for finer control. The capabilities of the C API in this regard reflect those that were available in Excel version 5, and so do not give you the same control that you have using VBA in more recent versions.

Эти методы чаще всего используются, когда Excel находится в ручном режиме вычисления, и позволяют выборочно вычислять книги, листы и диапазоны, полностью пересчитывать все открытые книги и даже полностью перестраивать дерево зависимостей и цепочку вычислений.Most frequently used when Excel is in manual calculation mode, these methods allow selective calculation of workbooks, worksheets, and ranges, complete recalculation of all open workbooks, and even complete rebuild of the dependency tree and calculation chain.

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

Клавиша: нетKeystroke: None

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

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

  • Ручной режимManual mode

    Пересчитывает только ячейки в заданном диапазоне независимо от того, "грязные" ли они. Поведение метода Range.Calculate изменилось в Excel 2007. Но предыдущее поведение по-прежнему поддерживается методом Range.CalculateRowMajorOrder.Recalculates just the cells in the given range regardless of whether they are dirty or not. Behavior of the Range.Calculate method changed in Excel 2007; however, the old behavior is still supported by the Range.CalculateRowMajorOrder method.

  • Режим "Автоматически" или "Автоматически, кроме таблиц"Automatic or Automatic Except Tables modes

    Пересчитывает книгу, но не выполняет принудительный пересчет диапазона или каких-либо ячеек в нем.Recalculates the workbook but does not force recalculation of the range or any cells in the range.

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

Клавиши: SHIFT+F9Keystroke: SHIFT+F9

VBA: ActiveSheet.CalculateVBA: ActiveSheet.Calculate

API C: xlcCalculateDocumentC API: xlcCalculateDocument

  • Все режимыAll modes

    Пересчитывает ячейки, отмеченные для вычисления, только на активном листе.Recalculates the cells marked for calculation in the active worksheet only.

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

Клавиша: нетKeystroke: None

VBA: Worksheets( reference ).CalculateVBA: Worksheets( reference ).Calculate

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

  • Все режимыAll modes

    Пересчитывает "грязные" ячейки и их зависимости только на указанном листе. Ссылка — это имя листа как строка или номер индекса в соответствующей книге.Recalculates the dirty cells and their dependents within the specified worksheet only. Reference is the name of the worksheet as a string or the index number in the relevant workbook.

    Excel 2000 и более поздних версий предоставляет свойство листа Boolean (EnableCalculation). Если задать для него значение True вместо False, все ячейки на указанном листе будут помечены как "грязные". В автоматических режимах это вызывает пересчет всей книги.Excel 2000 and later versions expose a Boolean worksheet property, the EnableCalculation property. Setting this to True from False dirties all cells in the specified worksheet. In automatic modes, this also triggers a recalculation of the whole workbook.

    В ручном режиме следующий код вызывает пересчет только активного листа.In manual mode, the following code causes recalculation of the active sheet only.

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

Повторное создание и принудительный пересчет дерева книгиWorkbook Tree Rebuild and Forced Recalculation

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

VBA: Workbooks( reference ).ForceFullCalculation (представлен в Excel 2007)VBA: Workbooks( reference ).ForceFullCalculation (introduced in Excel 2007)

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

  • Все режимыAll modes

    Указывает Excel заново создать дерево зависимостей и цепочку вычислений для определенной книги и вызывает пересчет всех ячеек, содержащих формулы.Causes Excel to rebuild the dependency tree and the calculation chain for a given workbook and forces a recalculation of all cells that contain formulas.

Все открытые книгиAll Open Workbooks

Клавиша: F9Keystroke: F9

VBA: Application.CalculateVBA: Application.Calculate

API C: xlcCalculateNowC API: xlcCalculateNow

  • Все режимыAll modes

    Пересчитывает все ячейки, которые Excel отметил как "грязные", то есть зависящие от переменных или измененных данных, и ячейки, программно отмеченные как "грязные". Если выбран режим вычисления "Автоматический, кроме таблиц", этот метод вычисляет таблицы, которые требуют обновления, а также все переменные функции и их зависимости.Recalculates all cells that Excel has marked as dirty, that is, dependents of volatile or changed data, and cells programmatically marked as dirty. If the calculation mode is Automatic Except Tables, this calculates those tables that require updating and also all volatile functions and their dependents.

Повторное создание и принудительное вычисление дерева всех открытых книгAll Open Workbooks Tree Rebuild and Forced Calculation

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

VBA: Application.CalculateFullVBA: Application.CalculateFull

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

  • Все режимыAll modes

    Пересчитывает все ячейки во всех открытых книгах. Если выбран режим вычисления "Автоматический, кроме таблиц", выполняется принудительный пересчет таблиц.Recalculates all cells in all open workbooks. If the calculation mode is Automatic Except Tables, it forces the tables to be recalculated.

См. такжеSee also

Многопоточный пересчет в ExcelMultithreaded Recalculation in Excel

Типы данных, используемые в ExcelData Types Used by Excel

Управление памятью в ExcelMemory Management in Excel

Понятия, связанные с программированием, для ExcelExcel Programming Concepts