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

Изменение пересчета, итерации или точности формулы в Excel

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

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

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

Точность — это показатель степени сходимости вычислений. Excel хранит и выполняет вычисления с точностью 15 значащих цифр. Однако существует возможность изменить точность вычислений, так что Excel при пересчете формул будет использовать для вычислений не хранимое, а отображаемое значение.

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

Важно: Изменение любого из этих параметров влияет на все открытые книги.

На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.

В Excel 2007 нажмите кнопку "Microsoft Office",выберите "Параметры Excel"и щелкните категорию "Формулы".
Выполните одно из указанных ниже действий.
- Для пересчета всех зависимых формул при каждом изменении значения, формулы или имени в разделе Параметры вычислений в группе Вычисления в книге выберите пункт Автоматически. Это — способ вычислений по умолчанию.
- Чтобы пересчитать все зависимые формулы, кроме таблиц данных, при каждом изменении значения, формулы или имени в разделе "Параметры вычислений" в разделе "Вычисления книги" выберите значение "Автоматически" за исключением таблиц данных.
- Для отключения автоматического пересчета и выполнения пересчета открытых книг только при явном требовании (с помощью клавиши F9) в разделе Параметры вычислений в группе Вычисления в книге выберите параметр Вручную.
  
  Примечание: При выборе параметра Вручную Excel автоматически устанавливает флажок Пересчитывать книгу перед сохранением. Если сохранение книги занимает много времени, для его экономии снимите флажок Пересчитывать книгу перед сохранением.
- Для ручного пересчета всех открытых книг, включая таблицы данных, и обновления всех открытых листов диаграмм на вкладке Формулы в группе Вычисление нажмите кнопку Пересчет.
- Для ручного пересчета активного листа, всех диаграмм и листов диаграмм, связанных с этим листом, на вкладке Формулы в группе Вычисление нажмите кнопку Произвести вычисления.

Совет: Многие из этих параметров можно изменять и вне диалогового окна Параметры Excel. Откройте вкладку Формулы и в группе Вычисления щелкните элемент Параметры вычислений, а затем — Выполнять автоматически.

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

Действие	Клавиши
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, во всех открытых книгах. Если задан автоматический пересчет книги, нажимать клавишу F9 для пересчета не требуется.	F9
Пересчет измененных после последнего вычисления формул и формул, зависящих от них, в активном листе.	SHIFT+F9
Пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета.	CTRL+ALT+F9
Проверка зависимых формул, а затем пересчет всех формул во всех открытых книгах независимо от того, были ли они изменены после прошлого пересчета.	CTRL+SHIFT+ALT+F9

На вкладке Файл нажмите кнопку Параметры и выберите категорию Формулы.

В Excel 2007 нажмите кнопку "Microsoft Office",выберите "Параметры Excel"и щелкните категорию "Формулы".
В разделе Параметры вычислений установите флажок Включить итеративные вычисления.
Чтобы установить максимальное число пересчетов, введите в поле Предельное число итераций число итераций. Чем больше число итераций, тем больше времени потребуется для пересчета листа.
Для установки максимальной разности между результатами пересчетов введите ее в поле Относительная погрешность. Чем меньше это число, тем точнее результат, и тем больше времени требуется на пересчет листа.

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

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

По умолчанию в вычислениях используются хранимые, а не отображаемые значения

Отображаемые и печатаемые значения зависят от выбора формата и отображения хранимых значений. Например, ячейка, в которой отображается дата "22.06.2008", также содержит порядковый номер, который является хранимым значением даты в этой ячейке. Можно изменить отображение даты на другой формат (например, "22-июнь-2008"), но хранимое в ячейке значение не изменится.

С осторожностью изменяйте точность вычислений

При пересчете формулы Excel обычно использует значения, хранящиеся в ячейках, на которые она ссылается. Например, если в двух ячейках содержится значение 10,005, а формат ячеек предполагает отображение значений в виде денежных единиц, в каждой из ячеек будет отображаться значение 10,01 ₽. При сложении этих значений результат будет равен 20,01 ₽, поскольку Excel складывает хранимые значения 10,005 и 10,005, а не отображаемые значения.

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

На вкладке Файл нажмите кнопку Параметры и выберите категорию Дополнительно.

В Excel 2007 нажмите кнопку "Microsoft Office",выберите "Параметры Excel"и щелкните категорию "Дополнительные параметры".
В разделе При пересчете этой книги выберите нужную книгу и установите флажок Задать указанную точность.

Хотя Excel ограничивает точность до 15 десятичных разрядов, это не означает, что максимальное число, которое можно хранить в Excel, ограничено 15 разрядами. Для положительных чисел предельное значение равно 9,99999999999999E+307, а для отрицательных значений — –9.99999999999999E+307. Эти числа приблизительно равны 1 или –1 с 308 нулями.

Точность в Excel означает, что любое число с разрядностью более 15 хранится и отображается только с точностью в 15 десятичных разрядов. Десятичная запятая может стоять перед любым из этих разрядов. Все разряды справа от 15-го обнуляются. Например, у числа 1234567,890123456 16 разрядов (7 до запятой и 9 после). В Excel это число хранится и отображается как 1234567,89012345 (в строке формулы и ячейке). Если установить для ячейки формат, отображающий все разряды (вместо экспоненциального формата, такого как 1,23457E+06), вы увидите, что число показывается как 1234567,890123450. 6 в конце (16-й разряд) отбрасывается и заменяется на 0. Последним является 15-й разряд, а все последующие равны нулю.

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

На вкладке Файл нажмите кнопку Параметры и выберите категорию Дополнительно.

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

Заметка Этот флажок по умолчанию установлен, и для вычислений используются все процессоры. Число процессоров на компьютере определяется автоматически и отображается рядом с параметром использовать все процессоры данного компьютера.
Дополнительно при выборе параметра Включить многопоточные вычисления можно задавать число используемых процессоров компьютера. Например, можно ограничить число процессоров, используемых для пересчета, если нужно выделить процессорное время другим выполняемым на компьютере программам.
Чтобы управлять числом процессоров, в области "Число потоковвычислений" выберите "Вручную". Введите число процессоров (максимальное число — 1024).

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

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

В Excel в Интернете результаты формулы автоматически пересчитываются при изменении данных в ячейках, которые используются в этой формуле. Этот автоматический пересчет можно отключить и вычислить результаты формулы вручную. Вот как это сделать:

Примечание: Изменение параметра вычисления влияет только на текущую книгу, а не на другие открытые книги в браузере.

В Excel в Интернете щелкните вкладку "Формулы".
Рядом с параметрами вычисленийвыберите один из следующих параметров в этом меню:
- Чтобы пересчитать все зависимые формулы при каждом изменении значения, формулы или имени, выберите значение "Автоматически". Этот параметр установлен по умолчанию.
- Чтобы пересчитать все зависимые формулы, кроме таблиц данных, при каждом изменении значения, формулы или имени нажимайте кнопку "Автоматически, кроме таблиц данных".
- Чтобы отключить автоматический пересчет и пересчет открытых книг только явным образом, нажмите кнопку "Вручную".
- Чтобы пересчитать книгу вручную (включая таблицы данных), нажмите кнопку "Вычислить книгу".

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

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

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

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

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

– пробный период дает 14 дней полного доступа ко всем инструментам.

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

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

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

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

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

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

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

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

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

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

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

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

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

Заменить формулу ее результатом

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно... Меньше

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

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

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

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

Заменить формулы их расчетными значениями

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

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

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

Как выбрать диапазон, содержащий формулу массива
1. Щелкните ячейку в формуле массива.
2. На вкладке Главная в группе Редактирование щелкните Найти и выбрать , а затем щелкните Перейти к .
3. Нажмите Специальный .
4. Нажмите Текущий массив .
Щелкните Копировать .
Щелкните Вставьте .
Щелкните стрелку рядом с Параметры вставки , а затем щелкните Только значения .

В следующем примере показана формула в ячейке D2, которая умножает ячейки A2, B2 и скидку, полученную из C2, для расчета суммы счета-фактуры для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или книгу, вы можете преобразовать формулу в своей ячейке в ее значение, выполнив следующие действия:

Нажмите F2, чтобы отредактировать ячейку.
Нажмите F9, а затем нажмите клавишу ВВОД.

После преобразования ячейки из формулы в значение значение отображается как 1932,322 в строке формул. Обратите внимание, что 1932,322 — это фактическое расчетное значение, а 1932,32 — это значение, отображаемое в ячейке в денежном формате.

Совет: При редактировании ячейки, содержащей формулу, можно нажать F9, чтобы навсегда заменить формулу ее вычисленным значением.

Заменить часть формулы ее расчетным значением

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

При замене части формулы ее значением эта часть формулы не может быть восстановлена.

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

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

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

Как быстро преобразовать ФОРМУЛУ в ЗНАЧЕНИЕ в Excel

Зачем преобразовывать формулу в значение

1. Вставка значений с помощью простой комбинации клавиш

2. Использование специальной вставки для преобразования в значения Щелкните Меню

4. Преобразование формул в значения с помощью кода VBA 9Заключение :

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

Позвольте мне рассказать вам о некоторых интересных способах сделать это, но перед этим.

Почему следует преобразовывать формулу в значение

Вот несколько причин, по которым необходимо заменить формулу ее результирующим значением.

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

Краткое примечание: Это один из тех советов по работе с Excel, который может помочь улучшить базовые навыки работы с Excel

1. Вставка значений с помощью простого сочетания клавиш

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

Все, что вам нужно сделать:

Выберите диапазон ячеек, в которых у вас есть формулы.
Нажмите Ctrl + C, чтобы скопировать ячейки.

Затем нажмите Alt + E S V.

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

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

2. Использование специальной вставки для преобразования в значения

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

Прежде всего, выберите диапазон дат или весь столбец.
Затем щелкните правой кнопкой мыши и скопируйте его.
Перейти на вкладку «Главная» ➜ Буфер обмена ➜ Вставить ➜ Специальная вставка.

Или вы также можете нажать правую кнопку мыши и перейти к Специальная вставка -> Значения.

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

3. Использование контекстного меню

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

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

Это мгновенно преобразует все формулы из этих ячеек в статические значения результата.

4. Преобразование формул в значения с помощью кода VBA

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

 Sub ConvertToValues() Затемнить MyRange как диапазон Dim MyCell как диапазон Выберите дело MsgBox("Это действие нельзя отменить." & _ «Сначала сохранить книгу?», _ vbYesNoCancel, "Предупреждение") Дело = vbДа ThisWorkbook.Сохранить Случай = vbCancel Выйти из подпрограммы Конец выбора Установите MyRange = Выбор Для каждой ячейки MyCell в MyRange Если MyCell. HasFormula Тогда МояЯчейка.Формула = МояЯчейка.Значение Конец, если Следующая MyCell Конец суб

Связанный: Учебник по Excel VBA

5. Преобразование в значения внутри формулы

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

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

Отредактируйте формулу, нажав F2.
Выберите только ту часть формулы, которую необходимо преобразовать в статическое значение.
Нажмите F9.

Теперь у вас есть только текстовое значение, которое нужно преобразовать в нужный регистр.

6. Получить значение результата формулы с помощью Power Query

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

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

Выберите таблицу и перейдите на вкладку «Данные» ➜ «Получить и преобразовать» ➜ «Из таблицы».

Это откроет вашу таблицу в редакторе запросов Power.

Здесь просто нажмите «Закрыть и загрузить».

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

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

Связано: Учебное пособие по Excel Power Query

Заключение

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

Blender уроки

Категории