В чем суть автоматического перерасчета в ms excel кратко

Обновлено: 04.07.2024

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

Но у Вас есть возможность управлять, будет ли расчет производиться в вручную. А это зачем. Рассказываю случай из жизни. Приходит некая мадам - сразу к директору (вообще, персонал любит сразу к директору, в ВЦ обычно - в самую последнею очередь). И эта мадам сообщает, что у нее медленно работает компьютер. Таблица большая, расчет сложный и ей приходиться ждать некоторое время после ввода цифр, пока Excel пересчитает. Вот. Значит, ей нужен новый блестящий компьютер со звуковой картой и колонками. Думаю, идея понятна. Автоматический пересчет хорош, пока таблица маленькая, а компьютер рвет и мечет. А когда наоборот, то плохо. Отключение автоматического пересчета решает эту проблему намного дешевле.

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

Теперь можно спокойно вводить, нужно посчитать - жмем F9 и все проблемы решены.

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

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

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

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

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

Создание дерева зависимостей
Создание цепочки вычислений
Пересчет ячеек

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

При структурном изменении книги, например при вводе новой формулы, Excel заново создает дерево зависимостей и цепочку вычислений. При вводе новых данных или новых формул Excel помечает все ячейки, которые зависят от новых данных, как требующие пересчета. Помеченные такие образом ячейки называются "грязными". Все прямые и косвенные зависимые ячейки помечаются как "грязные", поэтому если ячейка B1 зависит от ячейки A1, а ячейка C1 — от B1, то при изменении ячейки A1 ячейки B1 и C1 помечаются как "грязные".

Отметив ячейки как "грязные" при следующем пересчете, 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:

Интерфейсы API VBA и C поддерживают способы сообщить Excel, что пользовательскую функцию следует обрабатывать как переменную. В VBA пользовательская функция объявляется переменной следующим образом.

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

С помощью API C можно зарегистрировать функцию XLL как переменную до ее первого вызова. Он также позволяет включать и отключать переменное состояние функции листа.

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

Режимы вычисления, команды, выборочный пересчет и таблицы данных

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

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

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

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

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

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

VBA: ActiveSheet.Calculate

API C: xlcCalculateDocument

Все режимы

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

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

VBA: Worksheets( reference ).Calculate

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

Пересчитывает "грязные" ячейки и их зависимости только на указанном листе. Ссылка — это имя листа как строка или номер индекса в соответствующей книге.

Excel 2000 и более поздних версий предоставляет свойство листа Boolean (EnableCalculation). Если задать для него значение True вместо False, все ячейки на указанном листе будут помечены как "грязные". В автоматических режимах это вызывает пересчет всей книги.

В ручном режиме следующий код вызывает пересчет только активного листа.

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

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

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

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

Все режимы

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

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

VBA: Application.Calculate

API C: xlcCalculateNow

Все режимы

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

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

VBA: Application.CalculateFull

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

Все режимы

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

Зачем нужен автоматический пересчет формул в программе Эксель?

Как отключить автоматический пересчет формул?

Обычно вычисления происходят автоматически, практически мгновенно и незаметны пользователю. Но бывают таблицы в которых несколько десятков тысяч строк и сотни столбцов. Вычисления при изменении одного значения занимают уже заметное время. Вот и представьте, нужно внести изменения в такую таблицу, а после каждого внесения (изменения одной ячейки) программа зависает на 5-10 секунд (а то и более), пересчитывая цепочки формул. Для оперативной работы автоматический пересчет можно отключить и вносить данные без помех. А при необходимости достаточно нажать F9, произойдет перерасчет и на экране отобразятся актуальные итоги вычислений.

Отключить автоматический расчет можно в настройках. В блоке формулы найти параметры вычислений и установить флаг на нужном режиме.

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

Отключать пересчет формул в таблицах Microsoft Excel не требуется, так как по умолчанию таблицы свободны от всяких формул. Их наоборот вам придется вводить, для того, чтоб таблица начала их считать.

Microsoft Excel

трюки • приёмы • решения

Как контролировать вычисления на листе Excel

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

Выберите Файл → Параметры → Формулы.
Или перейдите на вкладку Формулы ленты инструментов и нажмите кнопку Параметры вычислений.

Вы видите три предложенных варианта:

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

Нажмите F9 или кнопку Пересчет панели инструментов (при нахождении на вкладке Формулы ленты инструментов) для проведения пересчета всех листов документа.
Нажмите Shift+F9 или кнопку Произвести вычисления для пересчета текущего листа.

Если вы хотите пересчитать только часть вашего листа при нахождении в ручном режиме, используйте одну из следующих возможностей:

Для пересчета одной формулы выберите ячейку с этой формулой, активируйте ввод (двойным щелчком мыши) и затем подтвердите ячейку (нажатием на клавишу Enter).
Для пересчета формул нескольких ячеек выберите их, нажмите сочетание клавиш Ctrl+Н. На экране появится окно поиска и замены данных. В поля Найти и Заменить на введите знак Далее нажмите на кнопку Заменить все. При этом Excel сделает пересчет выбранных ячеек.

Автоматический пересчет формул в Excel и вручную

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

Автоматический и ручной пересчет

Для книги, которая содержит сотни сложных формул можно настроить пересчет по востребованию пользователя. Для этого:

Внимание! Быстрая клавиша F9 – выполняет пересчет во всех формулах книги на всех листах. А Комбинация горячих клавиш SHIFT+F9 – выполняет пересчет только на текущем листе.

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

Как отображать формулу ячейке Excel

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

Чтобы наглядно отобразить пример данного урока нам потребуется лист содержащий формулы:

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

Так же можно использовать комбинацию горячих клавиш CTRL+` (над клавишей Tab). Данная комбинация работает в режиме переключателя, то есть повторное нажатие возвращает в обычный режим отображения результатов вычисления в ячейках.

Примечание. Все выше описанные действия распространяются только на режим отображения ячеек одного листа. То есть на других листах при необходимости потребуется выполнять эти действия повторно.

Не работает автоматический пересчет формул на листах

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

Автоматический пересчет функции
извините, но не нашла "поиска" по форуму. знаю точно, что где то есть подобная тема. проблема.

Пересчет формул только для 1 листа
Подскажите, пож. Ситуация: программный пересчет объемных данных на листе 1; генерируются.

Пересчет формул только на одном листе
Доброго времени суток, форумчане! Столкнулся со следующей проблемой: На нескольких листах в.

А если запринтскринить?

Мое, ИМХО, баги и еще раз баги, например, плохая оператива.

1) А если все-таки покажите нам снимок этого белого куска.

2) Если виновата память, нужно провести тест стабильности компонентов и предоставить полный отчет о параметрах Вашей системы. Готовы к этому?

Мне тоже не нравится.
Но иногда даже полного обнуления настроек бывает недостаточно. Если таки решитесь, нужно делать не просто переустановку, а со сбросом настроек (уже не помню поможет ли способ № 2, я лично использовал способ № 4 по приведенной выше ссылке). И даже в этом случае на одном и компьютеров продолжала возникать проблема.

Шаблон должен влиять на создаваемую, а не открываемую книгу.
На открываемую могут влиять макросы, записанные в личную книгу макросов, если такие имеются.

3) Вот еще попробуйте добавить новую подпрограмму в один из своих модулей:

4) Как вариант, может там установлен Excel со специфической локализацией. Т.о. формулы не распознаются.

5) Поставьте курсор на ячейку, которая у Вас якобы не вычисляется -> правая кнопка мыши -> форма ячеек -> какой у Вас показан установленый формат ячеек (общий, текстовой, числовой) ?

6) Если поставить курсор в эту ячейку, затем нажать и затем — значение в ячейке будет пересчитано?

Автоматический пересчет формул в Excel

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

Вычисления в книге — эта группа переключателей определяет режим вычислений:

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

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

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

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

В разделе Работа с формулами задаются параметры, управляющие вводом и вычислением формул.

Стиль ссылок R1C1 — переход от стандартного для Excel именования ячеек (A1, D6, E4 и т. д.) к стилю ссылок, при котором нумеруются не только строки, но и столбцы. При этом буква R (row) означает строку, а C (column) — столбец. Соответственно, запись в новом стиле R5C4 будет эквивалентна записи D5 в стандартном стиле.

Автозавершение формул — в этом режиме предлагаются возможные варианты функций во время ввода их в строке формул (рис. 2.11).

Использовать имена таблиц в формулах — вместо того, чтобы вставлять в формулы диапазоны ссылок в виде A1:G8 , вы можете выделить нужную область, задать для нее имя и затем вставить это имя в формулу. На рис. 2.12 приведен такой пример — сначала был выделен диапазон E1:I8 , этому диапазону было присвоено имя MyTable , затем в ячейке D1 была создана формула суммирования, в которую в качестве аргумента передано имя данного диапазона.

Использовать функции GetPivotData для ссылок в сводной таблице — в этом режиме данные из сводной таблицы выбираются при помощи вышеуказанной функции. Если вы вставляете в формулу ссылку на ячейку, которая расположена в сводной таблице, то вместо ссылки на ячейку будет автоматически вставлена функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ . Если вам все же нужна именно ссылка на ячейку, этот флажок нужно сбросить.

С помощью элементов управления раздела Контроль ошибок настраивается режим контроля ошибок:

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

В разделе Правила контроля ошибок расположены флажки, управляющие режимом контроля ошибок. Я опишу не все флажки, а только те, названия которых недостаточно полно характеризуют их назначение:

Автоматический и ручной пересчет

Как отображать формулу ячейке Excel

Чтобы наглядно отобразить пример данного урока нам потребуется лист содержащий формулы:

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

Читайте также: