Изменчивые функции в Microsoft Excel полезны, когда вы хотите отображать данные в реальном времени, поскольку они пересчитываются при каждом повторном открытии книги или при выполнении любых других вычислений. Однако вы можете исправить возвращаемые ими результаты или временно запретить им пересчитываться.
Примерами изменчивых функций в Excel являются NOW (возвращает текущую дату и время), TODAY (возвращает текущую дату), RAND (возвращает случайное число от 0 до 1) и RANDBETWEEN (возвращает случайное число между двумя указанными вами значениями).
1. Преобразование изменчивой функции в статическое значение по мере ввода
Одним из способов остановить пересчет изменчивой функции является ее преобразование в фиксированное значение в точке входа.
В этом примере функция NOW используется для возврата текущей даты и времени:
= СЕЙЧАС ()
Однако через две минуты, когда я отредактировал другую ячейку на листе, время обновилось.
Чтобы сделать дату и время статическими с самого начала, введите формулу и, не снимая режима Enter, нажмите F9 > Enter.
Чтобы ввести текущую дату с помощью сочетания клавиш, нажмите Ctrl+; (точка с запятой), затем Enter. Для текущего времени нажмите Ctrl+Shift+;. Если вы используете Mac, используйте Cmd вместо Ctrl. Вы можете ввести и дату, и время в одну и ту же ячейку, используя оба сочетания клавиш, одно за другим, разделенные пробелом.
Сначала вы можете увидеть нечто похожее на случайное десятичное число, но на самом деле это серийный номер, представляющий дату и время, которые были на момент нажатия клавиши Enter.
Наконец, нажмите Ctrl+1 (или щелкните правой кнопкой мыши по ячейке и выберите «Формат ячеек»), чтобы открыть диалоговое окно «Формат ячеек», нажмите «Пользовательский» и в поле «Тип» укажите, как должны отображаться дата и время.
При нажатии «ОК» дата и время появятся в активной ячейке и останутся фиксированными, пока вы не измените их вручную.
Если вы повторно введете ячейку, содержащую изменчивую функцию, и нажмете F9, чтобы сделать ее статической, ячейка обновится еще раз, прежде чем превратиться в фиксированное значение. Если вы хотите ретроспективно исправить ячейку, содержащую изменчивую функцию, не влияя на значение, см. метод 2 ниже.
Во втором примере предположим, что вы хотите сгенерировать случайное число от 0 до 1. Для этого введите:
= СЛЧИС ()
в ячейку A1 и нажмите Enter.
Однако поскольку функция RAND является изменчивой, каждый раз, когда в другом месте рабочей книги выполняется вычисление, генерируется новое случайное число.
Вместо этого, прежде чем нажать Enter, нажмите F9, и функция СЛЧИС будет заменена фиксированным значением как в ячейке, так и в строке формул.
Будьте осторожны при использовании сочетания клавиш F9 в Microsoft Excel. Если вы не вводите данные в ячейку и не редактируете ее (другими словами, если Excel находится в режиме готовности), нажатие F9 заставляет все изменчивые функции пересчитываться — это полная противоположность тому, чего вы пытаетесь добиться!
2. Ретроспективное преобразование ячеек с изменчивыми функциями в статические значения.
Другой способ исправить значения, полученные с помощью volatile-функций (и, вероятно, метод, который я использую чаще всего), — это скопировать результат и вставить его как значение. Это особенно удобно, если вы хотите выполнить это действие для ячеек, уже содержащих volatile-функции, не изменяя их значения, или если вам нужно исправить много ячеек одновременно.
Представьте, что вы создали набор случайных чисел от 50 до 100 с помощью функции СЛУЧМЕЖДУ:
=randbetween(50,100)
Однако, если оставить их как есть, эти случайные числа будут продолжать генерироваться заново каждый раз, когда в другом месте рабочей книги выполняется вычисление или если вы закрываете и снова открываете файл.
Итак, чтобы исправить эти значения, сначала выделите ячейки и нажмите Ctrl+C (или Cmd+C на Mac).
Затем, не снимая выделения с этих ячеек, нажмите Ctrl+Shift+V (или Ctrl+Cmd+V на Mac), чтобы вставить значения ячеек, заставив Excel игнорировать формулу, использованную для их генерации.
Теперь ранее нефиксированные значения преобразованы в статические числа.
3 Отключите автоматические расчеты
Другой способ предотвратить автоматический пересчет изменчивых функций в Microsoft Excel — изменить настройки расчета. Внесение этого изменения не изменит работу функций, но повлияет на то, как они рассчитываются и как их результат отображается на экране.
Для этого нажмите кнопку «Параметры расчета» на вкладке «Формулы» и нажмите «Вручную».
Изменения параметров расчетов в одной рабочей книге повлияют на все остальные рабочие книги, которые уже открыты, и на все, которые вы откроете впоследствии.
Теперь, когда в рабочей книге выполняется вычисление, любые ячейки, содержащие изменчивые функции, которые не обновились автоматически из-за этого изменения (также известные как устаревшее значение), будут переформатированы с зачеркиванием. Это напомнит вам, что информация в этих ячейках неактуальна.
Теперь любые изменчивые функции будут рассчитываться только тогда, когда:
- Вы сохраняете книгу вручную или нажимаете Ctrl+S,
- Вы нажимаете «Рассчитать сейчас» или нажимаете F9 в режиме готовности (это влияет на все вычисления во всей рабочей книге и во всех открытых рабочих книгах), или
- Вы нажимаете «Рассчитать лист» или нажимаете Shift+F9 в режиме готовности (это влияет на все вычисления только на активном листе).
Поскольку изменчивые функции в Excel постоянно пересчитываются, использование многих из них в одной книге может существенно повлиять на производительность вашей электронной таблицы. Поэтому использование методов в этом руководстве может помочь ускорить ваш файл Excel. Упрощение формул, сжатие изображений и отказ от слишком большого форматирования — это другие способы обеспечить бесперебойную работу ваших книг.
