Excel vba отключить пересчет формул
добрый день,
есть задача: на листе "отчет" автоматически пересчитываются итоги работы вносимые на др.листах.
т.к. вычисления долгие, при вводе в ячейки на других листах Эксель немнго подисает, т.к. пересчитывает часть итогов на листе "отчет".
написал макрос, который добавил на лист итогов :
но почему-то этот макрос отключает также и формулы в др. ячейках.
вобщем смысл такой: пользователь вносит данные без зависания, т.к. автомат.вычисления в определленом диапозоне отключены. После чего переходит на лист "отчет", нажимает кнопку - данные вносятся (и с этим все ок), но другие ячейки (в которых формула типа если А=Б - ок; <> - проверь данные) не пересчитыватся .
__________________Помощь в написании контрольных, курсовых и дипломных работ здесь
Как отключить пересчет формул
Добрый день. Excel 2010 при закрытии файла, предварительно сохраненного в старой версии, выдает.
Как в Excel отключить пересчет формул?
Для более быстрого расчета, мне нужно в Excel отключить вычесление формул. Через объект.
Выбрать последнее совпавшее знаяение из диапазона с посредством формул
Доброе время суток!! Часто использую для выборки искомых данных из диапазона комбинацию формул.
Application.Calculation - Эта команда уровня приложения (т.е. Excel я) - а не выделенного диапозона.
Поэтому выключаются все вычисления внутри открытого экземляра Excel'я.
Добавлено через 8 минут
Есть метод Range.Calculate.
На сколько я понимаю, после массового отключения вычислений, и ввода данных можно запускать пересчеты для нужных диапазонов. Отталкивайтесь от этого.
Range("A1:B2").Сalculate или ActiveSheet.Range("A1:B2").Сalculate.
не поддерживает свойство или метод
Добавлено через 6 минут
не так выразился.. не поддерживает потом false или true
Сначала Application.Calculation = xlCalculationmanual вы выключаете отработку событий.
А потом методом Range("нужный диапозон").Calculate вы калькулируете нужные диапазоны.
интересная идея, но..
если я отключу обработку полностью, то 90% формул на листе перестанут работать. Поэтому каждый рэндж мне придется прописывать в макросе на включение.. а так как таблицы отчета со сводкой расширяются примерно раз в 3 дня - придется каждый раз апдейтить рэндж в макросе. И на сколько я понимаю, это повлечет за собой полность отключение в формул в книге, поэтому диапозонов придется вписывать очень много.
Неужели в экселе нет обратной функции на "включить все, отключить диапозон"?
п.с. перечитал 7 страниц поиска на яндексе по данной теме. ничего путного
п.с. перечитал 7 страниц поиска на яндексе по данной теме. ничего путногоТакого нет. Как правило калькуляции выключают для ускорения обработки макроса, а в конце работы макросов их включают для возвращения нормальной работы Excel.
Как вариант можно отлавливать изменения в нужном диапазоне на листе, но сомневаюсь что, это вам чего то даст.
Что если каждый пользователь будет заполнять свою книгу/шаблон. А вы потом будет консолидировать все результаты в одну книгу?
Или нарисовать формочку и данные будут забиваться в неё, а потом перетягивать их в книгу?
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из "калькулятора-переростка" в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
- Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
- Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
- Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
- Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать(Copy) .
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :
либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.
В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl + C
- Тут же вставляем обратно сочетанием Ctrl + V
- Жмём Ctrl , чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
- Если в коде есть много всяких Activate и Select , тем более в циклах - следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate - зачем нужны и нужны ли?
- Обязательно на время выполнения кода отключить:
- автоматический пересчет формул . Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода - это может дико тормозить код, если формул много:
Если во время кода все же нужно пересчитывать какие-то диапазоны, то можно пересчитывать только их:
Главное, что следует помнить - все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри Excel. Например, если забыть включить автопересчет формул - большинство формул будут пересчитывать исключительно принудительным методом - после нажатия сочетания клавиш Shift + F9 . А если забыть отключить обновление экрана - то есть шанс заблокировать себе возможность работы на листах и книгах. Хотя по умолчанию свойство ScreenUpdating и должно возвращаться в True, если было отключено внутри процедуры - лучше не надеяться на это и привыкать возвращать все свойства на свои места принудительно. По сути все это сведется к нескольким строкам:
'Возвращаем обновление экрана Application.ScreenUpdating = True 'Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic 'Включаем отслеживание событий Application.EnableEvents = True
Как такой код выглядит на практике. Предположим, надо записать в цикле в 10 000 строк значения:
Sub TestOptimize() 'отключаем обновление экрана Application.ScreenUpdating = False 'Отключаем автопересчет формул Application.Calculation = xlCalculationManual 'Отключаем отслеживание событий Application.EnableEvents = False 'Отключаем разбиение на печатные страницы ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False 'Непосредственно код заполнения ячеек Dim lr As Long For lr = 1 To 10000 Cells(lr, 1).Value = lr 'для примера просто пронумеруем строки Next 'Возвращаем обновление экрана Application.ScreenUpdating = True 'Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic 'Включаем отслеживание событий Application.EnableEvents = True End Sub
Для более опытных пользователей VBA я приведу несколько решений по оптимизации кодов в различных ситуациях:
-
Самая хорошая оптимизация кода, если приходится работать с ячейками листа напрямую, обрабатывать их и, возможно, изменять значения, то быстрее все обработки делать в массиве и разом выгружать на листе. Например, код выше по заполнению ячеек номерами будет в этом случае выглядеть так:
В таблице Excel, изображенной ниже, я хотел бы, чтобы перерасчет прекратился для верхней части из строк 1-5. Под перерасчетом я имею в виду, что, например, в ячейках M2, H4, S4 есть формула today (), и каждый раз, когда я открываю ее, она пересчитывается. Я бы хотел, чтобы он рассчитывался один раз и один раз сохраняю, чтобы не пересчитывать еще раз, снова открывал. Какие-либо предложения? я пытался
3 ответа
Что ж, установка Application.Calculation = xlCalculationManual во время Workbook_Open не всегда может гарантировать вам желаемый результат, поскольку существует вероятность того, что Excel может быть в xlCalculationAutomatic при открытии файла.
Итак, обходной путь: откройте Excel и установите вычисление вручную либо в VBE, либо через меню, а затем откройте свой файл.
В VBE введите Application.Calculation = xlCalculationManual в окне, затем нажмите Enter и откройте файл.
В Excel для версии 2007 нажмите Formula Menu , перейдите к Calculation Group , в Calculation Options выберите Automatic/Manual . Для более старых версий Tools > Options > Calculation .
Я бы посоветовал оценить функцию в VBA на нужных вам событиях.
Если вы хотите, чтобы функция СЕГОДНЯ выполнялась по вашему запросу, я бы создал кнопку с макросом, который переоценивает СЕГОДНЯ.
Как я бы это реализовал, зависит от того, хотите ли вы / нужно ли иметь ячейку в виде формулы. Если рассчитанного значения достаточно, вы можете рассчитать его в VBA и установить значение в нужные ячейки. Если вы хотите иметь формулу и сохранить последнее вычисленное значение СЕГОДНЯ, вы можете установить формулу в ячейку. Range("M2").Formula = Date() & "other formula part"
Самое простое решение - установить сегодняшнее значение в какой-либо ячейке в VBA.
У меня нет репутации, чтобы оставлять комментарий, чтобы задать уточняющий вопрос, поэтому я опубликую ответ вместо него (сделав предположение о том, каким будет ваш ответ).
Если вы никогда не хотите, чтобы эти ячейки пересчитывались (и не возражаете против потери формулы), самым простым решением было бы преобразовать их в значения.
Читайте также: