В чем суть автоматического пересчета в ms excel
Обновлено: 05.07.2024
В электронных таблицах - режим, в котором все ячейки пересчитываются при каждом изменении значения. Автоматический перерасчет гарантирует, что данные электронной таблицы всегда актуальны, но может замедлить работу с электронной таблицей. Кроме того, вы можете указать перерасчет вручную, при этом вы должны явно указать приложению выполнить пересчет.
С 1995 года более 100 технических экспертов и исследователей обновляют определения, статьи и учебные пособия Webopedia. Для получения дополнительной информации о нынешнем составе редакции посетите нашу страницу «О нас».
Популярные статьи
Список версий операционной системы Windows и история [по порядку]
Операционная система Windows (ОС Windows) относится к семейству операционных систем, разработанных корпорацией Microsoft. Мы смотрим на историю Windows.
Как создать ярлык веб-сайта на рабочем столе
Ярлык веб-сайта на рабочем столе рассмотрен Web Webster Это руководство Webopedia покажет вам, как создать ярлык веб-сайта на рабочем столе с помощью.
Что такое пять поколений компьютеров? (с 1-го по 5-е)
Отзыв: Web Webster Каждое поколение компьютеров значительно увеличивает скорость и мощность вычислительных задач. Узнайте о каждом из них.
Учетные записи электронной почты Hotmail [Outlook]
Запущенная в 1996 году служба Hotmail была одной из первых общедоступных почтовых веб-служб, доступ к которым можно было получить из любого веб-браузера. На своем.
Статьи по теме
Log4Shell
Жнец
Безопасность Интернета вещей
Webopedia – это интернет-ресурс по информационным технологиям и информатике для ИТ-специалистов, студентов и преподавателей. Webopedia фокусируется на подключении исследователей к наиболее полезным для них ИТ-ресурсам. Ресурсы Webopedia охватывают определения технологий, образовательные руководства и обзоры программного обеспечения, которые доступны всем исследователям, независимо от технического образования.
Рекламодатели
Рекламируйте с помощью TechnologyAdvice в Webopedia и на других наших платформах, ориентированных на ИТ.
Наши бренды
Собственность TechnologyAdvice.
© 2021 TechnologyAdvice. Все права защищены
Пользователь может инициировать перерасчет в 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: не поддерживается
Все режимы
Пересчитывает все ячейки во всех открытых книгах. Если выбран режим расчета «Автоматически, кроме таблиц», он принудительно пересчитывает таблицы.
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Обзор
В Microsoft Excel все открытые в данный момент документы используют один и тот же режим вычислений, независимо от режима, в котором они были сохранены.
Дополнительная информация
Чтобы объяснить, как определяется способ расчета, в этой статье используются следующие гипотетические документы:
| Имя файла | Вид документа | Режим, используемый для сохранения файла |
|---|---|---|
| Auto1.xlsx | Workbook | Automatic |
| Manual1.xlsx | Рабочая книга | Вручную |
| Auto2.xlsx | Рабочая книга | Автоматически | < /tr>
Следующие утверждения относятся к режимам расчета в Excel:
- Для первого открываемого документа используется режим расчета, в котором он был сохранен в последний раз. Документы, открытые позже, используют тот же режим. Например, если открыть Auto1.xlsx, а затем открыть Manual1.xlsx, в обоих документах будет использоваться автоматический расчет (режим, используемый Auto1.xlsx). Если вы откроете Manual1.xlsx, а затем Auto1.xlsx, в обоих документах будут использоваться расчеты вручную.
- При изменении режима расчета одного открытого документа изменяется режим для всех открытых документов. Например, если Auto1.xlsx и Auto2.xlsx открыты, изменение режима расчета Auto2.xlsx на ручной также приводит к изменению режима Auto1.xlsx на ручной.
- Все листы, содержащиеся в рабочей книге, используют один и тот же режим расчета. Например, если Auto2.xlsx содержит три листа, изменение режима расчета первого листа на ручной также приведет к изменению режима расчета на ручной на двух других листах.
- Если все другие документы закрыты и вы создаете новый документ, в новом документе используется тот же режим расчета, что и в ранее закрытых документах. Однако, если вы используете шаблон, режим расчета — это режим, указанный в шаблоне.
- Если режим расчета в рабочей книге изменился и файл сохранен, текущий режим расчета сохраняется. Например, если открыть Auto1.xlsx, открыть Manual1.xlsx и сразу же сохранить Manual1.xlsx, режим расчета сохраняется как автоматический.
Как управлять режимом расчета
Все открытые документы используют один и тот же режим расчета. Для работы с документами, использующими разные режимы расчета, необходимо соблюдать специальные процедуры. Например, если вы работаете с Auto1.xlsx и хотите открыть Manual1.xlsx в ручном режиме расчета, выполните одно из следующих действий:
- Перед открытием файла Manual1.xlsx установите Auto1.xlsx в режим ручного расчета.
- Закройте Auto1.xlsx (и все другие открытые документы) перед открытием Manual1.xlsx.
В Excel можно выбрать четыре режима расчета. Они следующие:
Excel по умолчанию пересчитывает все формулы во всех листах всех открытых книг после каждого ввода данных. Самая шикарная возможность Excel – это автоматический пересчет по формулам. Если на листе сотни или тысячи формул, автоматический пересчет начинает заметно тормозить процесс работы с программой. Эта способность электронных таблиц увеличила производительность труда людей в десятки раз.
Ведь при ручном расчете нам нужно каждый раз вводить не только числа, но и математические действия, не путаться, постоянно помещая в память промежуточные результаты и так далее. В нашем случае мы создаем формулы, а затем просто меняем числа — все будет автоматически пересчитано заново. Ну а если изменения небольшие, то еще лучше - еще меньше данных вводить.
Давайте подумаем, как можно настроить Excel для повышения его производительности и беспрепятственной работы.
Автоматическое и ручное преобразование
Для книги, содержащей сотни составных формул, вы можете отказаться от пересчета по требованию пользователя. Для этого:
Обратите внимание! Ярлык F9 выполняет перерасчет во всех формулах книги на всех листах. Комбинация горячих клавиш SHIFT+F9 выполняет пересчет только на текущем листе.
Если на листе не так много формул, то пересчет может тормозить в Excel. Именно поэтому нет смысла использовать вышеописанный пример, но на будущее все же стоит знать вам об этой возможности: ведь со временем вам придется иметь дело со сложными таблицами с большим количеством формул.< /p>
Кроме того, эта функция может быть включена случайно, и вам нужно знать, где ее отключить для стандартного режима работы.
Как отобразить формулу в ячейке Excel
В ячейках Excel мы можем видеть только результат вычислений. Сами формулы можно увидеть в строке формул (отдельно). Но иногда нам нужно одновременно просмотреть все функции в ячейках (например, сравнить их и т. д.).
Чтобы наглядно отобразить пример этого урока, нам нужен этот лист, содержащий формулы:
Соответствующим образом меняем настройки Excel, чтобы в ячейках отображались формулы, а не результат их вычисления.
Вы также можете использовать комбинацию клавиш быстрого доступа CTRL + `(над клавишей Tab). Данная комбинация работает в режиме переключателя, то есть нужно повторно нажать на эту клавишу и вернуться к обычному режиму отображения результатов расчета в ячейках.
Обозначение. Все вышеописанные действия относятся только к режиму отображения ячеек одного листа. То есть на других листах при необходимости нужно выполнить эти действия повторно.
Читайте также: