Как Excel пересчитывает книгу и почему надо избегать волатильных функций

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

Как Excel пересчитывает книгу

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

Когда мы с вами создаем таблицу с формулами, при записи каждой формулы каждой ячейке также присваивается несколько дополнительных атрибутов, кроме прочего это: (1) значение, (2) связи — то есть от каких ячеек она зависит. Эти связи можно посмотреть с помощью функциональности Trace precendents.

Для чего нужны эти связи?

Предположим, у нас есть формула =B2*C2 в ячейке D3 (я поместил ее в отдельную строку только для того, чтобы было лучше видно связи).

Значение этой формулы зависит от значений ячеек B2 и C2. Поэтому пересчитывать ее имеет смысл только тогда, когда эти значения меняются. До тех пор, пока из значения неизменны и формула в ячейке D3 не меняется, нет необходимости ее пересчитывать.

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

Что такое волатильные и неволатильные функции

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

Microsoft приводит следующий список функций (он менялся до 1997 года, но мы проигнорируем старые версии Excel):

NOW (ТДАТА) TODAY (СЕГОДНЯ) RAND (СЛЧИС) RANDBETWEEN (СЛУЧМЕЖДУ) (Miscrosoft ее не упоминает, но она волатильная) OFFSET (СМЕЩ) INDIRECT (ДВССЫЛ) CELL (ЯЧЕЙКА) INFO (ИНФОРМ)

Большинство этих функций волатильны по собственной природе. Пожалуй, единственное, что удручает — это функция OFFSET, которая на самом деле вполне предсказуемо зависима от нескольких значений — то есть ее вполне можно было сделать и неволатильной. Надеемся, что однажды она станет нормальной функцией, которой можно будет полноценно пользоваться.

Почему надо избегать волатильных функций

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

Если Вы часто работаете с большими файлами, вам могут быть интересна статья о работе с ними.

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