Как обновить все формулы в excel

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

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

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

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

1. Введите формулу на чистый лист (чтобы можно было проверить как работает данный пример).
2. Выберите инструмент: «Формулы»-«Параметры вычислений»-«Вручную».
3. Убедитесь, что теперь после ввода данных в ячейку (например, число 7 вместо 1 в ячейке A2 как на рисунке), формула не пересчитывает результат автоматически. Пока пользователь не нажмет клавишу F9 (или SFIFT+F9).

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

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

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

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

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

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

Чтобы добиться данного результата, нужно выбрать инструмент: «Формулы»-«Показать» (в разделе «Зависимости формул»). Чтобы выйти из этого режима нужно выбрать данный инструмент повторно.

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Вычисление и повторное вычисление формул в книгах на основе браузера

Формулы в книге в браузере можно использовать точно так же, как и в Excel. Как и в Excel, автор формулы определяет способ вычисления или пересчета формул. В этой статье описаны различные параметры вычислений, которые может выбрать автор книги.

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

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

В таблице ниже кратко описаны различные параметры вычислений.

Параметр вычисления

Формулы автоматически пересчитываются при каждом ее изменений и при каждом внесении изменений в ячейку, на которые ссылается формула. Этот вариант применяется по умолчанию.

Формулы никогда не вычисляются автоматически. Для пересчета формул пользователю необходимо выбрать параметр «Вычислить книгу».

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

Формулы и ссылки на ячейки, содержащиеся в таблице данных Excel в What-If анализ не пересчитываются.

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

Пересчет перед сохранением

Формулы автоматически пересчитываются при каждом сохранении книги.

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

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

1. Откройте книгу.
2. На панели инструментов книги щелкните меню «Данные» и выберите пункт «Вычислить книгу».

можно также использовать клавишу F9 для вычисления формул в книге.

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

1. Чтобы использовать формулы в книге службы Excel, ее автор должен сначала создать в Excel книгу с формулами. Если Excel Online на сайте включена, вы можете вводить формулы непосредственно в книге так же, как это делаете в Excel.

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

При работе с большими книгами Excel, в которых множество формул, функций и ссылок, довольно утомительно ждать, пока пересчитывается вся книга, после изменения хотя бы одной ячейки. В таком случае вы, скорее всего, уже перешли с автоматических вычислений на вычисления вручную. Но даже при этом у вас по сути только два варианта: или пересчёт всей книги, или только активного рабочего листа. А что если вам нужно пересчитать только одну таблицу или лишь один столбец?

С инструментом «Пересчёт диапазона» вы сможете:

Пересчитать формулы только в выбранном диапазоне
Просто выберите диапазон – и нажмите ALT+F9
Или задайте собственные горячие клавиши

Перед началом работы добавьте «Пересчёт диапазона» в Excel

«Пересчёт диапазона» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как переключиться с автоматических вычислений в Excel на вычисления вручную

Инструмент «Пересчёт диапазона» будет работать, только когда вы отключите автоматические вычисления в рабочей книге и переключитесь на ручной режим.

Откройте вкладку «Формулы» Нажмите Параметры вычислений В выпадающем списке отметьте «Вручную».

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

Как выполнить пересчёт конкретного диапазона в Excel

Чтобы пересчитать только выбранный диапазон, используйте горячие клавиши XLTools:

Выделите диапазон Нажмите комбинацию горячих клавиш Alt + F9 Готово! Все ячейки с формулами в этом диапазоне пересчитаны.

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

Чтобы принудительно пересчитать всю книгу или рабочий лист, используйте горячие клавиши Excel:

F9 – пересчёт всех листов во всех открытых книгах
Shift+F9 – пересчёт активного листа

Ctrl+Alt+F9 – пересчёт всех листов во всех открытых книгах независимо от того, вносились ли в них изменения с момента последнего пересчёта

Ctrl+Alt+Shift+F9 – проверка зависимых формул с последующим пересчётом всех ячеек во всех открытых книгах, включая ячейки, не помеченные для вычисления

Как изменить комбинацию горячих клавиш для «Пересчёта диапазона»

Вы можете изменить комбинацию горячих клавиш для «Пересчёта диапазона» в любое время. Обратите внимание, некоторые сочетания клавиш могут быть недоступны, если они зарезервированы в Excel.

Вкладка XLTools Кнопка Настройки Вкладка Горячие клавиши Задайте своё сочетание клавиш OK.

Developing.ru

Автоматическое обновление формул, которые используют функции

Весь MS Office, программирование на Visual Basic for Applications и MS VB
8 сообщений • Страница 1 из 1
Александра Кузнецова Сообщения: 1 Зарегистрирован: 15 ноя 2006, 09:58

Уважаемые господа, помогите пожалуйста.
Написала на VBA в Excel функцию и использую ее как формулу на рабочем листе.
Как сделать чтобы формула персчитывалась автоматически, как и обычные формулы. Галочка в настройках — автоматическое обновление — стоит.
Есть один нюанс. Функция использует комментарий к ячейке. может быть в этом дело?

pashulka Сообщения: 831 Зарегистрирован: 24 ноя 2004, 03:46 Контактная информация:

Function myFunction() Application.Volatile True 'Application.Volatile . . . myFunction=. End Function

AlexanderVII Сообщения: 7 Зарегистрирован: 15 ноя 2006, 17:40

Функции пересчитываются только тогда, когда изменяется значение хотя бы одного из входящих аргументов, для примера я изменил ваш образец. А1 и Аn это входящие переменные. Если на рабочем листе написать например: =myFunction(C5;N12), то при изменении значения хотя бы в одной из ечеек C5 или N12 произойдет пересчет функции.

Function myFunction(А1, Аn)
Application.Volatile True ‘Application.Volatile
.
.
myFunction=А1 + Аn
End Function

pashulka Сообщения: 831 Зарегистрирован: 24 ноя 2004, 03:46 Контактная информация:

Функции пересчитываются только тогда, когда изменяется значение хотя бы одного из входящих аргументов © AlexanderVII 17/11/06

Это очередное заблуждение, ибо если пользовательская функция использует Application.Volatile True ‘Application.Volatile то она будет пересчитываться вместе с другими формулами (если установлен автоматический режим вычислений)
Причём, в этом случае изменение передаваемых значений не является обязательным, более того, пользовательская функция может вообще не иметь аргументов, например :

Function myFunction() 'Используется только для примера 'ибо есть стандартная функция рабочего листа =ТДАТА() Application.Volatile True 'Application.Volatile myFunction = Now End Function

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

Sokl Сообщения: 449 Зарегистрирован: 12 сен 2005, 08:52 Откуда: ОМ

pashulka, может стоит объяснить нюанс? AlexanderVII попытался это сделать, но вышло не очень. А нюанс есть. Он заключается в том, по «какой причине» функция будет или не будет пересчитываться. Если функция имеет аргументы и эти аргументы изменяются при обычном пересчете функций на листе (например, аргумент — ссылка на диапазон), то функция будет пересчитываться «по событию изменения аргументов». Если использовать Application.Volatile, то функция будет пересчитываться всякий раз, когда что-то в экселе может (потенциально) пересчитываться.
Например,

Код: Function myFunction() 'Используется только для примера 'ибо есть стандартная функция рабочего листа =ТДАТА() Application.Volatile True 'Application.Volatile myFunction = Now End Function 

будет пересчитываться при изменении значений любой ячейки, любой книги.
pashulka Сообщения: 831 Зарегистрирован: 24 ноя 2004, 03:46 Контактная информация:

Думаю, что если начинать об’яснять, что функция типа =СУММ(A1;C5;F10) будет пересчитываться при изменении значений в указанных ячейках, то ликбез нужно начинать с темы: что есть MS Excel. Что же касается метода Volatile, то всю необходимую информацию, можно прочитать в справке. А если внимательно прочитать сам вопрос, где говорится о том, что пользовательская функция возвращает текст комментария(примечания), а также о том, что она должна пересчитываться вместе с другими формулами, то станет видно, что мой предыдущий ответ целиком и полностью отвечал на поставленный вопрос и не нуждался в об’яснениях. Ибо, при изменении текста комментария(примечания) пользовательская функция, не использующая Application.Volatile True, не будет возвращать новый результат. Тогда как UDF, в которой наличествует Application.Volatile True, при вычислении формул будет возвращать текст комментария, причём с изменениями.

Sokl Сообщения: 449 Зарегистрирован: 12 сен 2005, 08:52 Откуда: ОМ
pashulka,

Тогда как UDF, в которой наличествует Application.Volatile True, при вычислении формул будет возвращать текст комментария, причём с изменениями

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

pashulka Сообщения: 831 Зарегистрирован: 24 ноя 2004, 03:46 Контактная информация:

А если убрать Application.Volatile True, то пользовательская функция будет пересчитываться либо после изменения значения ячейки, в которой наличествует комментарий, а этих изменений может и не быть, либо после нажатия клавиш CTRL+ALT+F9 (или нажатия кнопки Вычислить все, которая появилась только в MS Excel 2000) Тогда как в вопросе довольно чётко было сказано, что пересчёт UDF должен осуществляться вместе с другими формулами.

8 сообщений • Страница 1 из 1

• Системное и прикладное программирование
• ↳ Win API, Shell..
• ↳ C и C++
• ↳ Delphi и Pascal
• ↳ Программирование на Pascal и Delphi (Object Pascal)
• ↳ Все вопросы ООП
• ↳ Компоненты в Delphi
• ↳ Delphi и WinAPI, ActiveX и OLE , COM и DCOM, и т.д
• ↳ Java
• ↳ MS Office и VB(A).
• ↳ Ассемблер
• WEB программирование
• ↳ JScript, VBScript, DHTML.
• ↳ Perl, PHP, ASP .
• Некатегоризированное
• ↳ Вопрошайка
• ↳ SQL
• ↳ Алгоритмы
• ↳ Дизайн и графика
• Администрирование
• ↳ Apache, IIS.
• ↳ Операционные системы
• Работа
• ↳ Вакансии и заказы для программистов
• ↳ Поиск работы и заказов.
• ↳ Решите мне задачку
• ↳ задачи на Паскале и Delphi
• ↳ задачи на C и C++
• ↳ задачи на Basic и Visual Basic (VB)
• Прочее
• ↳ Жалобная книга
• ↳ Флейм.