Автоматический пересчет формул в Excel и вручную
Excel умолчанию пересчитывает все формулы во всех листах всех открытых книг после каждого введения данных. Если лист содержит сотни или тысячи формул автоматический пересчет начинает заметно замедлять процесс работы с программой. Рассмотрим, как можно настроить Excel для повышения его производительности и беспрепятственной работы.
Автоматический и ручной пересчет
Для книги, которая содержит сотни сложных формул можно настроить пересчет по востребованию пользователя. Для этого:
- Введите формулу на чистый лист (чтобы можно было проверить как работает данный пример).
- Выберите инструмент: «Формулы»-«Параметры вычислений»-«Вручную».
- Убедитесь, что теперь после ввода данных в ячейку (например, число 7 вместо 1 в ячейке A2 как на рисунке), формула не пересчитывает результат автоматически. Пока пользователь не нажмет клавишу F9 (или SFIFT+F9).
Внимание! Быстрая клавиша F9 – выполняет пересчет во всех формулах книги на всех листах. А Комбинация горячих клавиш SHIFT+F9 – выполняет пересчет только на текущем листе.
Если на листе не содержится множество формул, пересчет которых может тормозить Excel, то нет смысла использовать выше описанный пример. Но на будущее все же стоит знать о такой возможности. Ведь со временем придется сталкиваться со сложными таблицами с множеством формул. Кроме того данная функция может быть включена случайно и нужно знать где ее выключить для стандартного режима работы.
Как отображать формулу в ячейке Excel
В ячейках Excel мы видим только результат вычислений. Сами же формулы можно увидеть в строке формул (по отдельности). Но иногда нам нужно одновременно просмотреть все функции в ячейках (например, чтобы их сравнить и т.п.).
Чтобы наглядно отобразить пример данного урока нам потребуется лист содержащий формулы:
Изменим настройки Excel так, чтобы ячейки отображали формулы, а не результат их вычисления.
Чтобы добиться данного результата, нужно выбрать инструмент: «Формулы»-«Показать» (в разделе «Зависимости формул»). Чтобы выйти из этого режима нужно выбрать данный инструмент повторно.
Так же можно использовать комбинацию горячих клавиш CTRL+` (над клавишей Tab). Данная комбинация работает в режиме переключателя, то есть повторное нажатие возвращает в обычный режим отображения результатов вычисления в ячейках.
Примечание. Все выше описанные действия распространяются только на режим отображения ячеек одного листа. То есть на других листах при необходимости потребуется выполнять эти действия повторно.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Изменение пересчета, итерации или точности формулы в Excel
Для эффективного использования формул необходимо ознакомиться с тремя ключевыми понятиями.
Вычисление — это процесс расчета по формулам и последующего отображения значений результатов в ячейках, содержащих формулы. Во избежание ненужных вычислений, которые забрать время и замедлить работу компьютера, Microsoft Office Excel автоматически пересчитывает формулы только при изменении влияющих на формулу ячеек. Это является стандартной процедурой при первом открытии книги и ее редактировании. Однако тем, как и когда Excel будет пересчитывать формулы, можно управлять.
Итерация — это повторный пересчет на отдельном числовом условии. Excel не может автоматически вычислить формулу, которая ссылается (прямо или косвенно) на ячейку, содержаную эту формулу. Это называется циклской ссылкой. Если формула ссылается на одну из собственных ячеек, необходимо определить, сколько раз она должна пересчитываться. Цикловые ссылки могут итерироваться бесконечно. Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.
Точность — это показатель степени сходимости вычислений. Excel хранит и выполняет вычисления с точностью 15 значащих цифр. Однако существует возможность изменить точность вычислений, так что Excel при пересчете формул будет использовать для вычислений не хранимое, а отображаемое значение.
Изменение при пересчете листа или книги
Во время вычислений можно выбирать команды и выполнять ввод чисел или формул. Для выполнения команд или других действий вычисления прерываются, а затем возобновляются снова. Если книга содержит большое число формул либо листы содержат таблицы данных или функции, автоматически пересчитываемые при каждом пересчете книги, процесс вычислений может занять значительное время. Он также может быть длительным, если листы содержат связи с другими листами или книгами. Можно изменить способ выполнения вычислений, установив параметр пересчета вручную.
Важно: Изменение любого из этих параметров влияет на все открытые книги.
- На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы. В Excel 2007 нажмите кнопку «Microsoft Office»,выберите «Параметры Excel»и щелкните категорию «Формулы».
- Выполните одно из указанных ниже действий.
- Для пересчета всех зависимых формул при каждом изменении значения, формулы или имени в разделе Параметры вычислений в группе Вычисления в книге выберите пункт Автоматически. Это — способ вычислений по умолчанию.
- Чтобы пересчитать все зависимые формулы, кроме таблиц данных, при каждом изменении значения, формулы или имени в разделе «Параметры вычислений» в разделе «Вычисления книги» выберите значение «Автоматически» за исключением таблиц данных.
- Для отключения автоматического пересчета и выполнения пересчета открытых книг только при явном требовании (с помощью клавиши F9) в разделе Параметры вычислений в группе Вычисления в книге выберите параметр Вручную.
Примечание: При выборе параметра Вручную Excel автоматически устанавливает флажок Пересчитывать книгу перед сохранением. Если сохранение книги занимает много времени, для его экономии снимите флажок Пересчитывать книгу перед сохранением.
Совет: Многие из этих параметров можно изменять и вне диалогового окна Параметры Excel. Откройте вкладку Формулы и в группе Вычисления щелкните элемент Параметры вычислений, а затем — Выполнять автоматически.
Примечание: Если лист содержит формулу, связанную с непересчитанным листом, и эта связь обновляется, появится сообщение о том, что исходный лист полностью не пересчитан. Для обновления связи с текущим значением, записанном в исходном листе, даже если оно неверно, нажмите кнопку ОК. Для прекращения обновления связи и использования предыдущего значения, полученного из исходного листа, нажмите кнопку Отмена.
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)
- Прочее
- ↳ Жалобная книга
- ↳ Флейм.
Office Excel не перерасчитывает формулы
Проблема:
Формулы в Excel автоматически не пересчитываются, чтобы формула пересчиталась нужно ее выделить и нажать enter.
Решение:
2003 Excel:
Сервис-Параметры-Вычисления
убедиться, что стоит «автоматически».
2010-2013 Excel:
есть кружочек со знаком MS, на верху страницы (пункт меню офис в маке) нажимаете-внизу параметры Эксель (под последними документами)-закладка «формулы»-самое верхние «параметры вычислений» (у вас скорее всего вручную ) поставить автоматически