Excel vba отключить перерасчет формулы
Обновлено: 08.07.2024
Чтобы эффективно использовать формулы, необходимо уяснить три важных момента:
Вычисление — это процесс вычисления формул с последующим отображением результатов в виде значений в ячейках, содержащих формулы. Чтобы избежать ненужных вычислений, которые могут привести к потере времени и замедлению работы компьютера, Microsoft Excel автоматически пересчитывает формулы только в том случае, если изменились ячейки, от которых зависит формула. Это поведение по умолчанию при первом открытии книги и при редактировании книги. Однако вы можете контролировать, когда и как Excel пересчитывает формулы.
Итерация — это повторяющийся пересчет рабочего листа до тех пор, пока не будет выполнено определенное числовое условие. Excel не может автоматически вычислить формулу, которая прямо или косвенно ссылается на ячейку, содержащую формулу. Это называется циклической ссылкой. Если формула ссылается на одну из своих собственных ячеек, необходимо определить, сколько раз формула должна пересчитываться. Циклические ссылки могут повторяться бесконечно. Однако вы можете контролировать максимальное количество итераций и количество допустимых изменений.
Точность – это мера степени точности вычислений. Excel хранит и вычисляет с точностью до 15 значащих цифр. Однако вы можете изменить точность вычислений, чтобы Excel использовал отображаемое значение вместо сохраненного значения при пересчете формул.
По мере выполнения вычислений вы можете выбирать команды или выполнять такие действия, как ввод чисел или формул. Excel временно прерывает расчет для выполнения других команд или действий, а затем возобновляет расчет. Процесс вычислений может занять больше времени, если рабочая книга содержит большое количество формул или рабочие листы содержат таблицы данных или функции, которые автоматически пересчитываются каждый раз при пересчете рабочей книги. Кроме того, процесс расчета может занять больше времени, если рабочие листы содержат ссылки на другие рабочие листы или рабочие книги. Вы можете контролировать, когда происходит расчет, изменив процесс расчета на ручной расчет.
Важно! Изменение любого параметра влияет на все открытые книги.
Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Формулы".
В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Формулы".
Выполните одно из следующих действий:
Чтобы пересчитывать все зависимые формулы каждый раз, когда вы вносите изменения в значение, формулу или имя, в разделе "Параметры расчета" в разделе "Расчет рабочей книги" нажмите "Автоматически". Это настройка расчета по умолчанию.
Чтобы пересчитывать все зависимые формулы, кроме таблиц данных, каждый раз, когда вы вносите изменения в значение, формулу или имя, в разделе "Параметры расчета" в разделе "Расчет рабочей книги" выберите "Автоматически, кроме таблиц данных".
Чтобы отключить автоматический перерасчет и пересчитывать открытые книги только в том случае, если вы делаете это явным образом (нажав клавишу F9), в разделе "Параметры расчета" в разделе "Расчет рабочей книги" нажмите "Вручную".
Примечание. Когда вы нажимаете «Вручную», Excel автоматически устанавливает флажок «Пересчитать рабочую книгу перед сохранением». Если сохранение книги занимает много времени, снятие флажка Пересчитать книгу перед сохранением может сократить время сохранения.
Чтобы вручную пересчитать все открытые рабочие листы, включая таблицы данных, и обновить все открытые листы диаграмм, на вкладке "Формулы" в группе "Расчет" нажмите кнопку "Рассчитать сейчас".
Чтобы вручную пересчитать активный лист и любые диаграммы и листы диаграмм, связанные с этим листом, на вкладке "Формулы" в группе "Расчет" нажмите кнопку "Рассчитать лист".
Совет. Кроме того, многие из этих параметров можно изменить вне диалогового окна «Параметры Excel». На вкладке "Формулы" в группе "Расчет" нажмите "Параметры расчета" и выберите "Автоматически".
Примечание. Если рабочий лист содержит формулу, связанную с рабочим листом, который не был пересчитан, и вы обновляете эту ссылку, Excel отображает сообщение о том, что исходный рабочий лист не был полностью пересчитан. Чтобы обновить ссылку текущим значением, хранящимся на исходном листе, даже если значение может быть неверным, нажмите кнопку ОК. Чтобы отменить обновление ссылки и использовать предыдущее значение, полученное из исходного листа, нажмите кнопку "Отмена".
Пересчитывать формулы, которые изменились с момента последнего вычисления, и формулы, зависящие от них, во всех открытых книгах. Если рабочая книга настроена на автоматический пересчет, вам не нужно нажимать F9 для пересчета.
Пересчитать формулы, которые изменились с момента последнего вычисления, и формулы, зависящие от них, на активном листе.
Пересчитывать все формулы во всех открытых книгах независимо от того, изменились ли они с момента последнего пересчета.
Проверить зависимые формулы, а затем пересчитать все формулы во всех открытых книгах, независимо от того, изменились ли они с момента последнего пересчета.
Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Формулы".
В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Формулы".
В разделе "Параметры расчета" установите флажок "Включить итеративный расчет".
Чтобы задать максимальное количество повторных вычислений Excel, введите количество итераций в поле Максимальное количество итераций. Чем больше число итераций, тем больше времени потребуется Excel для пересчета рабочего листа.
Чтобы установить максимальную сумму сдачи между результатами пересчета, введите ее в поле "Максимальная сдача". Чем меньше число, тем точнее результат и тем больше времени требуется Excel для пересчета рабочего листа.
Примечание. Решатель и Поиск цели являются частью набора команд, которые иногда называют инструментами анализа "что, если". Обе команды используют итерацию контролируемым образом для получения желаемых результатов. Вы можете использовать Solver, когда вам нужно найти оптимальное значение для конкретной ячейки, регулируя значения нескольких ячеек, или когда вы хотите применить определенные ограничения к одному или нескольким значениям в расчете. Вы можете использовать поиск цели, если вам известен желаемый результат одной формулы, но не входное значение, необходимое формуле для определения результата.
Прежде чем изменить точность вычислений, имейте в виду следующие важные моменты:
По умолчанию Excel вычисляет сохраненные, а не отображаемые значения
Отображаемое и распечатываемое значение зависит от того, как вы выбрали форматирование и отображение сохраненного значения. Например, ячейка, в которой отображается дата «22.06.2008», также содержит порядковый номер, который является сохраненным значением даты в ячейке. Вы можете изменить отображение даты в другом формате (например, на «22 июня 2008 г.»), но изменение отображения значения на листе не изменит сохраненное значение.
Будьте осторожны при изменении точности вычислений
Когда формула выполняет вычисления, Excel обычно использует значения, хранящиеся в ячейках, на которые ссылается формула. Например, если каждая из двух ячеек содержит значение 10,005 и ячейки отформатированы для отображения значений в денежном формате, в каждой ячейке отображается значение 10,01 доллара США. Если вы добавите две ячейки вместе, результат составит 20,01 доллара США, поскольку Excel добавляет сохраненные значения 10,005 и 10,005, а не отображаемые значения.
Когда вы меняете точность вычислений в книге с помощью отображаемых (отформатированных) значений, Excel навсегда изменяет сохраненные значения в ячейках с полной точности (15 цифр) на любой отображаемый формат, включая десятичные разряды. Если позже вы выберете вычисление с полной точностью, исходные базовые значения нельзя будет восстановить.
Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Дополнительно".
В Excel 2007 нажмите кнопку Microsoft Office, выберите «Параметры Excel» и выберите категорию «Дополнительно».
В разделе При расчете этой книги выберите нужную книгу, а затем установите флажок Установить точность как отображаемую.
Хотя Excel ограничивает точность до 15 цифр, это не означает, что 15 цифр — это предел размера числа, которое вы можете хранить в Excel. Предел составляет 9,99999999999999E+307 для положительных чисел и -9,999999999999999E+307 для отрицательных чисел. Это примерно то же самое, что 1 или -1 с последующими 308 нулями.
Точность в Excel означает, что любое число, превышающее 15 цифр, сохраняется и отображается с точностью до 15 цифр. Эти цифры могут быть в любой комбинации до или после запятой. Любые цифры справа от 15-й цифры будут нулями. Например, 1234567.890123456 имеет 16 цифр (7 цифр до и 9 цифр после запятой). В Excel он хранится и отображается как 1234567.89012345 (это отображается в строке формул и в ячейке). Если вы установите для ячейки числовой формат, чтобы отображались все цифры (вместо научного формата, такого как 1.23457E+06), вы увидите, что число отображается как 1234567.890123450. 6 в конце (16-я цифра) отбрасывается и заменяется 0. Точность останавливается на 15-й цифре, поэтому все последующие цифры равны нулю.
Компьютер может иметь более одного процессора (он содержит несколько физических процессоров) или может быть многопоточным (он содержит несколько логических процессоров). На этих компьютерах можно улучшить или контролировать время, необходимое для пересчета книг, содержащих много формул, задав количество процессоров, используемых для пересчета. Во многих случаях части рабочей нагрузки пересчета могут выполняться одновременно.Разделение этой рабочей нагрузки на несколько процессоров может сократить общее время, необходимое для выполнения пересчета.
Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Дополнительно".
В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Дополнительно".
Чтобы включить или отключить использование нескольких процессоров во время расчета, в разделе "Формулы" установите или снимите флажок "Включить многопоточный расчет".
Примечание. Этот флажок установлен по умолчанию, и во время расчета используются все процессоры. Количество процессоров на вашем компьютере определяется автоматически и отображается рядом с параметром Использовать все процессоры на этом компьютере.
При желании, если вы выберете Включить многопоточные вычисления, вы сможете управлять количеством процессоров, которые будут использоваться на вашем компьютере. Например, вы можете захотеть ограничить количество процессоров, используемых во время пересчета, если на вашем компьютере запущены другие программы, которым требуется выделенное время обработки.
Чтобы управлять количеством процессоров, в разделе Количество потоков вычислений нажмите Вручную. Введите количество используемых процессоров (максимальное количество – 1024).
Чтобы обеспечить правильное вычисление старых книг, Excel ведет себя иначе при первом открытии книги, сохраненной в более ранней версии Excel, чем при открытии книги, созданной в текущей версии.
При открытии книги, созданной в текущей версии, Excel пересчитывает только те формулы, которые зависят от измененных ячеек.
При открытии книги, созданной в более ранней версии Excel, все формулы в книге — те, которые зависят от измененных ячеек, и те, которые не изменились, — пересчитываются. Это гарантирует, что книга будет полностью оптимизирована для текущей версии Excel. Исключение составляют случаи, когда рабочая книга находится в другом режиме вычислений, например в ручном.
Поскольку полный пересчет может занять больше времени, чем частичный пересчет, открытие книги, которая ранее не была сохранена в текущей версии Excel, может занять больше времени, чем обычно. После сохранения книги в текущей версии Excel она будет открываться быстрее.
В Excel в Интернете результат формулы автоматически пересчитывается при изменении данных в ячейках, используемых в этой формуле. Вы можете отключить этот автоматический перерасчет и рассчитать результаты формулы вручную. Вот как это сделать:
Примечание. Изменение параметра расчета в книге повлияет только на текущую книгу, а не на другие книги, открытые в браузере.
В электронной таблице Excel для Интернета перейдите на вкладку "Формулы".
В раскрывающемся списке рядом с параметрами расчета выберите один из следующих параметров:
Чтобы пересчитывать все зависимые формулы каждый раз, когда вы вносите изменения в значение, формулу или имя, нажмите «Автоматически». Это настройка по умолчанию.
Чтобы пересчитывать все зависимые формулы, кроме таблиц данных, каждый раз, когда вы вносите изменения в значение, формулу или имя, нажмите «Автоматически, кроме таблиц данных».
Чтобы отключить автоматический перерасчет и пересчитывать открытые книги только в том случае, если вы это сделали явным образом, нажмите "Вручную".
Чтобы вручную пересчитать рабочую книгу (включая таблицы данных), нажмите «Рассчитать рабочую книгу».
Примечание. В Excel в Интернете вы не можете изменить количество повторных вычислений формулы, пока не будет выполнено определенное числовое условие, а также вы не можете изменить точность вычислений, используя отображаемое значение вместо сохраненного значения. при пересчете формул. Однако вы можете сделать это в настольном приложении Excel. Используйте кнопку "Открыть в Excel", чтобы открыть книгу, чтобы указать параметры расчета и изменить пересчет формулы, итерацию или точность.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе 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: не поддерживается
Все режимы
Пересчитывает все ячейки во всех открытых книгах. Если выбран режим расчета «Автоматически, кроме таблиц», он принудительно пересчитывает таблицы.
Обычно мы часто используем автоматический расчет в Excel для подсчета суммы или других формул для удобства. Но иногда мы хотим остановить это после нескольких вычислений. В приведенной ниже статье у вас будет два способа остановить автоматический расчет. Первый способ — остановить автоматический расчет по опциям формул напрямую в Excel. Другой способ — с помощью VBA.
Сначала посмотрите пример автоматического расчета ниже:
Установите два списка чисел в столбцах A и B, столбец C представляет собой сумму A+B. Введите «=A1+B1» в C1, после чего результат будет рассчитан автоматически.
Выберите C1 и перетащите его вниз к C2 и C3, после чего C2 и C3 будут автоматически рассчитаны правильно.
Давайте начнем изучать, как остановить автоматический расчет.
Метод 1: остановить автоматический расчет с помощью параметров расчета Excel
Шаг 1. В условии мы добавляем новую комбинацию чисел в A4 и B4.
Если вы ничего не сделаете и просто перетащите C3 вниз к C4, вы сразу получите 11. Это связано с тем, что автоматический расчет не останавливается. Поэтому нам нужно остановить вычисления сейчас.
Шаг 2. Выберите C3, щелкните меню «Формулы». Затем загружаются параметры, связанные с формулами.
Шаг 3. Нажмите «Параметры расчета» справа от панели.
Шаг 4. Нажмите на раскрывающийся список и выберите «Вручную».
Шаг 5: Затем перетащите C3 вниз к C4, вы получите 163, то есть просто скопируете число из C3 и не будете выполнять вычисление.
Понимаете, этот способ очень удобен для ручного пересчета, если это требуется клиенту. Теперь давайте начнем изучать другой способ остановить автоматический расчет.
Метод 2: остановить автоматический расчет с помощью VBA
Вы также можете использовать макрос Excel VBA для достижения того же результата. и просто выполните следующие действия:
Шаг 1: щелкните правой кнопкой мыши вкладку текущего рабочего листа в нижней части рабочего листа, затем щелкните ее правой кнопкой мыши, а затем выберите меню просмотра кода из списка контекстного меню.
Шаг 2: появится окно Visual Basic для приложений. и нажмите вкладку «Вставка» и выберите модуль из раскрывающегося списка меню.
Шаг 3: скопируйте приведенный ниже код vba в окно кода. сохраните и закройте окно VBA.
Шаг 4. Чтобы изменения вступили в силу, вам нужно повторно открыть книгу.
Информацию о методах расчета см. в разделе Методы расчета.
VBA позволяет управлять методами расчета, свойствами и событиями:
Методы расчета:
F9 — Пересчитать
CTRL/ALT/F9 — Полный расчет
В Excel 2000, 2002 и 2003:
В Excel 97 существует два возможных метода использования SendKeys или EnableCalculation:
Вы можете использовать SendKeys для отправки последовательности клавиш CTRL/ALT/F9. Это может быть сложно, потому что Sendkeys просто отправляет нажатия клавиш в буфер клавиатуры, и они обычно не обрабатываются до завершения VBA, и вы не можете гарантировать, что они будут обработаны правильным окном/приложением и т. д.
Для Windows 95/98/ME:
- Используйте ключи отправки, а не ключи отправки приложения.
- Аргумент True заставляет VBA ожидать непрерывного выполнения вычислений. Это необходимо, если оператор Sendkeys не является последним оператором VBA, который должен быть выполнен, кроме End.
- Аргумент True не работает с Application.SendKeys
В Windows NT/2000/XP:
Если процедура VBA, содержащая оператор Sendkeys, вызывается прямо или косвенно с помощью командной кнопки, аргумент True не работает, поэтому вам нужно использовать DoEvents, чтобы заставить Win Xp и Excel97 обработать их.
Другой метод использует свойство worksheet.enablecalculation. Когда это свойство изменяется, все формулы на листе помечаются как невычисленные, поэтому переключение свойства на false, а затем обратно на true для всех листов во всех открытых книгах приведет к тому, что следующее вычисление будет полным вычислением.
Размер oSht как рабочий лист
Application.Calculation=xlCalculationManual
для каждого oSht в рабочих листах
oSht.enablecalculation=false
osht.enablecalculation=true
следующий osht
Вы также можете использовать этот метод для одного рабочего листа, чтобы выполнить расчет всего листа.
Полный расчет с перестроением зависимостей
Только в Excel 2002/2003: Application.CalculateFullRebuild
В предыдущих версиях Excel вы могли добиться того же эффекта, переключившись на ручной режим, заменив все знаки равенства в формулах на знаки равенства, а затем либо переключившись обратно на автоматический режим, либо выполнив полный расчет вручную (Ctrl/Alt/F9).
Shift F9 — пересчет листа
Расчет диапазона: подробные сведения и ограничения см. в разделе Методы расчета
Метод оценки:
Вы можете использовать метод Evaluate объекта Application, Worksheet или Chart, чтобы вернуть результат вычисления строки, содержащей имена, диапазоны и/или формулы, в VBA без каких-либо изменений на рабочем листе. Evaluate также позволяет получить результаты формулы массива с одной или несколькими ячейками, переданной Evaluate в виде строки. Существует два разных синтаксиса для Application.Evaluate, например, как Evaluate("SUM(A1:A20") или как [SUM(A1:A20)]
Разница между использованием Application.Evaluate и Worksheet.Evaluate не разъясняется в справке Excel.
Application.Evaluate строка, как если бы она была на активном листе, но Worksheet.evaluate оценивает строку, как если бы она была на ссылочном листе:
If Sheet1!A1 содержит 'fred' и Sheet2!A1 содержит «Джо», а Лист 1 является активным листом, тогда Evaluate («A1») возвращает «fred», но Worksheets («Лист2»). Evaluate («A1») возвращает «Джо»
Оценить ограничения метода:
- Строка должна содержать менее 256 символов.
- Ссылки на стиль A1 можно оценивать как в режиме ссылок A1, так и в режиме R1C1 (Application.ReferenceStyle), но ссылки на стиль R1C1 можно оценивать только в режиме R1C1.
- Относительные ссылки в строке рассматриваются как абсолютные, если только они не содержатся в определенных именах, и в этом случае определенное имя оценивается относительно ячейки A1.
- Даты должны быть в формате США (месяц-день-год).
- Оценка всегда обрабатывает строковые формулы как формулы массива.
- Evaluate вернет значение ошибки, если строковые формулы содержат внешние ссылки на закрытые книги или функции XLM.
- Если строковая формула является ссылкой на определяемую пользователем функцию ( Evaluate("=MyUdf()") ), то она оценивается дважды, но если это выражение, содержащее определяемую пользователем функцию, и вы используете Worksheet.Evaluate, а не Application. Evaluate ( Activesheet.Evaluate("=0+MyUdf()") ), то он оценивается только один раз.
- Если строковая формула содержит ссылку и на UDF, и на имя, произойдет сбой с ошибкой 2029, если ссылка на имя появится ПОСЛЕ ссылки на UDF:
- Если fred — это именованный диапазон, а xyz() — это определяемая пользователем функция VBA, то этот оператор возвращает ошибку 2029: application.Evaluate("=xyz(b1)+fred")
- Эта инструкция возвращает правильное значение: application.Evaluate("=fred+xyz(b1)")
- Статья базы знаний Майкрософт 823604 указывает на эту проблему, но неправильно диагностирует обстоятельства, которые ее вызывают.
Многие из этих ограничений можно обойти (за счет снижения производительности), вставив строку формулы в ячейку рабочего листа, а затем прочитав полученное значение ячейки обратно в переменную VBA.
Оцените обработку ошибок:
Если Evaluate не может оценить строку, она возвращает ошибку, а не вызывает ошибку, поэтому для перехвата ошибки необходимо присвоить результат варианту, а затем проверить вариант с помощью ISERROR.
Общая функция EVAL(theInput As Variant) As Variant
'
' если UDF оценивает входную строку, как если бы она была на этом листе
' иначе оценивает для активного листа
'
Dim vEval As Variant
Application.Volatile
При ошибке GoTo funcfail
Если не IsEmpty(theInput), то
Если TypeOf Application.Caller.Parent Is Worksheet Then
Если TypeOf Application.Caller.Parent Is Worksheet Then
/>vEval = Application.Caller.Parent.Evaluate(Cstr(theInput))
Else
vEval = Application.Evaluate(cstr(theInput))
End If
If IsError( vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Выход из функции
funcfail: < br />EVAL = CVERr(xlErrNA)
Конец функцииСвойства, управляющие параметрами расчета:
Приложение. Расчет
Можно установить значение xlCalculationAutomatic, xlCalculationManual или xlCalculationSemiAutomatic (в Excel95 это было xlAutomatic и т. д.).
Сброс расчета до xlCalculationAutomatic вызовет повторный расчет.При установке этих свойств иногда возникает ошибка 1004, когда код вызывается из кода события элемента управления Control Toolbox. Вы можете обойти эту проблему, задав для свойства TakeFocusonClick кнопки элемента управления значение false или для других элементов управления, указав перед ним Activesheet.Activate.
Начальная настройка расчета Excel
FastExcel V2 позволяет управлять начальной последовательностью вычислений в Excel.
Excel устанавливает начальный режим расчета из первой открытой или созданной и рассчитанной рабочей книги без шаблона и надстройки.
Это означает, что настройка режима вычислений в последовательно открываемых книгах будет игнорироваться.
Если вам нужно переопределить способ, которым Excel изначально устанавливает режим расчета, вы можете установить его самостоятельно, создав модуль в ThisWorkbook (дважды щелкните ThisWorkbook в окне Project Explorer в VBE) и добавив этот код. В этом примере задается ручной расчет.
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End SubК сожалению, если для расчета задано значение «Автоматически», при открытии книги, содержащей этот код, Excel начнет процесс пересчета до того, как будет выполнено событие «Открыть». Единственный известный мне способ избежать этого — открыть фиктивную книгу с событием открытия книги, которое устанавливает расчет вручную, а затем открывает реальную книгу.
Application.CalculateBeforeSave
Верно или нет. Если вычисление выполняется вручную, а CalculateBeforeSave имеет значение true, когда вы сохраняете книгу, она будет пересчитана.
Application.Iteration, MaxIterations и MaxChange
Если Application.Iteration имеет значение true, а рабочая книга содержит циклические ссылки, то вычисления будут повторяться до тех пор, пока число итераций не достигнет MaxIterations или максимальное изменение значения ячейки в последней итерации не станет меньше, чем Maxchange.
Workbook.PrecisionAsDisplayed
Если значение равно True, рабочая книга будет рассчитываться с использованием количества знаков после запятой в форматировании.
Workbook.Date1904
Если расчеты истинной даты в рабочей книге будут основаны на 1904 году.
Workbook.AcceptLabelsInFormulas
Если значение true, Excel будет использовать в формулах метки "Естественный язык". Для всего, кроме простых моделей, это должно быть отключено.
Worksheet.EnableCalculation; Предотвращение вычисления определенных листов
FastExcel версии 2 предоставляет улучшенную реализацию этой возможности, называемой листами смешанного режима, которая позволяет вам контролировать, какие листы будут пересчитаны, для какого типа расчетного события, и сохраняет ваши настройки вместе с рабочей книгой.
Установка для свойства рабочего листа EnableCalculation значения False не позволит Excel включить рабочий лист в перерасчет. Это остановит пересчет листа с помощью автоматического пересчета, F9, Ctrl/Alt/F9 и с помощью Sheet.Calculate, Application.Calculate, Application.CalculateFull и Application.CalculateFullRebuild. Свойство EnableCalculation не влияет на Range.Calculate.
Если вы используете Range.Calculate для формулы на листе, для которого параметр EnableCalculation имеет значение false, то при следующем пересчете Excel пересчитает все формулы на других листах, которые зависят от этой формулы.
Установив для enablecalculation значение false, а затем вернув значение true, все формулы на листе будут помечены как невычисленные. Если режим расчета «Автоматический», будет запущен перерасчет. Все методы расчета, включая Sheet.Calculate, но исключая Range.Calculate, будут вычислять все формулы на листе. Вы можете использовать этот метод как способ имитации Calculatefull на рабочем листе, а не на уровне рабочей книги. Обратите внимание, что Sheet.Calculate не сбрасывает флаги невычисленных формул, поэтому два последовательных листа.Расчеты после переключения свойства EnableCalculation будут выполнять полный расчет листа.
Свойство сбрасывается в значение true при открытии книги.
События расчета:
Расчет листа приложения или рабочей книги
Это событие происходит после пересчета любого рабочего листа или повторного построения измененных данных диаграммы.
Рабочий лист или диаграмма Расчет
Это событие происходит после пересчета рабочего листа или повторного построения измененных данных диаграммы.
Только Excel 2002/2003
Excel 2002/2003 значительно расширяет возможности управления вычислениями с помощью VBA:
Добавление указанных ячеек в список вычислений
Вы можете использовать Range.Dirty, чтобы добавить указанные ячейки в список ячеек, требующих вычисления при следующем пересчете.
В Range.Dirty есть ошибка. Он всегда действует на текущий активный рабочий лист, а не на рабочий лист, на который фактически ссылается объект Range.Проверка статуса расчета
Свойство Application.CalculationState позволяет проверить, завершено ли вычисление ( xlDone ), находится в ожидании ( xlPending ) или находится в процессе ( xlCalculating ). Состояние Pending, по-видимому, соответствует сообщению Calculate в строке состояния: для книг с более чем 65 536 зависимостями CalculationState всегда имеет значение xlPending или xlCalculating.
Прерывание расчета
Вы можете управлять возможностью пользователей прерывать вычисления, указав, что будет прерывать вычисления.
Application.CalculationInterruptKey= XlAnyKey | XLEScКлюч | Кслнокей
Вы также можете управлять обработкой ошибок прерывания (в Excel 97 и более поздних версиях) с помощью
Application.EnableCancelKey= xlDisabled | кслеррорхандлер | кслинтеррупт
Только Excel 2007
Управление многопоточными вычислениями
Вы можете управлять новыми многопоточными вычислениями Excel 2007 из VBA с помощью Application.MultiThreadedCalculation .
Application.MultiThreadedCalculation.Enabled может иметь значение True или False для включения/отключения многопоточных вычислений.
Application.MultiThreadedCalculation.ThreadMode может иметь значение xlThreadModeAutomatic или xlThreadModeManual.
если установлено значение Manual, то Application.MultiThreadedCalculation.ThreadCount может быть задано числом используемых потоков.Расчет диапазона и RangeCalculateRowMajorOrder
Excel 2007 поддерживает два метода расчета диапазона. Для запуска этих методов расчета нет стандартного пользовательского интерфейса: вы должны вызывать их с помощью VBA или какого-либо другого языка программирования. Эти методы полезны, когда вы хотите рассчитать только небольшой блок ячеек, оставив все остальные формулы без изменений.
Читайте также: