Как пересчитать выбранные ячейки в Excel

Обновлено: 21.11.2024

Пользователь может инициировать перерасчет в Microsoft Excel несколькими способами, например:

  • Ввод новых данных (если Excel находится в режиме автоматического пересчета, описанном далее в этом разделе).
  • Явное указание Excel пересчитать всю книгу или ее часть.
  • Удаление или вставка строки или столбца.
  • Сохранение книги, когда установлен параметр "Пересчитывать перед сохранением".
  • Выполнение определенных действий автофильтра.
  • Дважды щелкните разделитель строк или столбцов (в режиме автоматического расчета).
  • Добавление, редактирование или удаление определенного имени.
  • Переименование листа.
  • Изменение положения листа по отношению к другим листам.
  • Скрытие или отображение строк, но не столбцов.

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

Зависимость, грязные ячейки и пересчитанные ячейки

Вычисление рабочих листов в Excel можно рассматривать как трехэтапный процесс:

  1. Построение дерева зависимостей
  2. Построение цепочки расчетов
  3. Пересчет ячеек

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

Когда в рабочую книгу вносятся структурные изменения, например при вводе новой формулы, Excel восстанавливает дерево зависимостей и цепочку вычислений. При вводе новых данных или новых формул Excel помечает все ячейки, зависящие от этих новых данных, как требующие пересчета. Ячейки, помеченные таким образом, называются грязными. Все прямые и косвенные зависимости помечаются как грязные, поэтому, если B1 зависит от A1, а C1 зависит от B1, при изменении A1 и B1, и C1 помечаются как грязные.

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

После пометки ячеек как грязных при последующем выполнении пересчета Excel повторно оценивает содержимое каждой грязной ячейки в порядке, определяемом цепочкой вычислений. В приведенном ранее примере это означает, что сначала идет B1, а затем C1. Этот пересчет происходит сразу после того, как Excel закончит помечать ячейки как грязные, если режим пересчета является автоматическим; в противном случае это происходит позже.

Начиная с Microsoft Excel 2002, объект Range в Microsoft Visual Basic для приложений (VBA) поддерживает метод Range.Dirty, который помечает ячейки как требующие вычисления. Когда он используется вместе с методом Range.Calculate (см. следующий раздел), он включает принудительный пересчет ячеек в заданном диапазоне. Это полезно, когда вы выполняете ограниченные вычисления во время макроса, когда режим вычисления установлен на ручной, чтобы избежать накладных расходов на вычисление ячеек, не связанных с функцией макроса. Методы расчета диапазона недоступны через C API.

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

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

Асинхронные определяемые пользователем функции (UDF)

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

Изменяемые и неизменяемые функции

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

Следующие функции Excel являются энергозависимыми:

И VBA, и C API поддерживают способы информирования Excel о том, что определяемая пользователем функция (UDF) должна обрабатываться как изменчивая. С помощью VBA определяемая пользователем функция объявляется изменчивой следующим образом.

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

С помощью C API вы можете зарегистрировать функцию XLL как энергозависимую перед ее первым вызовом. Это также позволяет вам включать и выключать изменчивый статус функции рабочего листа.

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

Режимы расчета, команды, выборочный пересчет и таблицы данных

Excel поддерживает три режима расчета:

  • Автоматически
  • Автоматические таблицы исключений
  • Вручную

Если для расчета задан автоматический режим, пересчет происходит после каждого ввода данных и после определенных событий, таких как примеры, приведенные в предыдущем разделе. Для очень больших книг время пересчета может быть настолько большим, что пользователи должны ограничивать его, то есть пересчитывать только тогда, когда это необходимо. Для этого Excel поддерживает ручной режим. Пользователь может выбрать режим через систему меню Excel или программно, используя VBA, COM или C API.

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

Пересчет таблиц данных выполняется несколько иначе:

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

Учитывая другой способ, которым Excel обрабатывает пересчет таблиц данных, а также тот факт, что расчет больших таблиц, зависящих от сложных или длительных вычислений, может занять много времени, Excel позволяет отключить автоматический расчет таблиц данных. Для этого установите режим расчета «Автоматически, кроме таблиц данных». Когда расчет находится в этом режиме, пользователь пересчитывает таблицы данных, нажимая F9 или аналогичную программную операцию.

Excel предоставляет методы, с помощью которых можно изменить режим пересчета и управлять пересчетом. Эти методы улучшались от версии к версии, чтобы обеспечить более точный контроль. Возможности C API в этом отношении отражают те, которые были доступны в Excel версии 5, и поэтому не дают вам такого же контроля, как при использовании VBA в более поздних версиях.

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

Вычисление диапазона

VBA: Диапазон.Вычисление (представлено в Excel 2000, изменено в Excel 2007) и Range.CalculateRowMajorOrder (представлено в Excel 2007)

C API: не поддерживается

Ручной режим

Пересчитывает только ячейки в заданном диапазоне независимо от того, грязные они или нет. Поведение метода Range.Calculate изменено в Excel 2007; однако метод Range.CalculateRowMajorOrder по-прежнему поддерживает старое поведение.

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

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

Активное вычисление рабочего листа

VBA: ActiveSheet.Calculate

C API: xlcCalculateDocument

Все режимы

Пересчитывает ячейки, помеченные для расчета, только на активном листе.

Указанный расчет рабочего листа

VBA: **Рабочие листы (**ссылка).Рассчитать

C API: не поддерживается

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

Excel 2000 и более поздние версии предоставляют логическое свойство рабочего листа, свойство EnableCalculation. Установка для этого параметра значения True вместо False загрязняет все ячейки на указанном листе. В автоматических режимах это также вызывает пересчет всей книги.

В ручном режиме следующий код вызывает пересчет только активного листа.

Перестроение дерева рабочей книги и принудительный перерасчет

Нажатие клавиши: CTRL+ALT+SHIFT+F9 (появилось в Excel 2002)

VBA: **Workbooks(**reference).ForceFullCalculation (появилось в Excel 2007)

C API: не поддерживается

Все режимы

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

Все открытые книги

VBA: Application.Calculate

C API: xlcCalculateNow

Все режимы

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

Перестроение дерева всех открытых книг и принудительный расчет

VBA: Application.CalculateFull

C API: не поддерживается

Все режимы

Пересчитывает все ячейки во всех открытых книгах. Если выбран режим расчета «Автоматически, кроме таблиц», он принудительно пересчитывает таблицы.

Сегодня у меня есть для вас совет, если вы работаете с очень большими моделями Excel. Те, расчет которых занимает минуты или часы. Вы можете вычислять только выбранные ячейки.

Немного теории вычислений в Excel

Проще говоря, Excel постоянно вычисляет (за некоторыми исключениями) все измененные ячейки и все зависимые ячейки. Вы можете переключить режим расчета, чтобы остановить расчет в промежутке между ними. Это называется «Ручной расчет». Сделав это, вы сможете

  • Пересчитайте все измененные ячейки, нажав клавишу F9 на клавиатуре.
  • принудительно выполните полный пересчет всех формул и функций, нажав Ctrl + Alt + F9 на клавиатуре или
  • вычислять только текущую таблицу, нажимая Shift + F9.

Хотите узнать больше о теории? В этой статье вы можете узнать больше о режимах расчета.

Итак, что, если вы хотите пересчитать только небольшое количество ячеек?

Сначала переключитесь в режим ручного расчета

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

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

Имейте в виду: теперь Excel не выполняет постоянный перерасчет. Это означает, что результат может не обновляться.

Вычислять только одну ячейку

Когда вы находитесь в режиме ручного расчета, достаточно просто вычислить только одну ячейку:

  1. Введите ячейку, которую нужно пересчитать. Выберите только ячейку и нажмите F2 на клавиатуре.
  2. Подтвердите, нажав Enter на клавиатуре.

Вычислять только выбранные ячейки

Но что, если вы не хотите пересчитывать только одну ячейку, а хотите вычислить ячейки, которые вы выбрали в данный момент? По сути, у вас есть два варианта: использовать VBA (это просто, не нужно вставлять модуль) или использовать надстройку Excel.

Вычислить выбранные ячейки с помощью VBA

В редакторе VBA есть функция "Немедленно". По умолчанию он ниже вашего кода, и мы можем использовать его здесь.

  1. Выберите ячейки, которые хотите вычислить.
  2. Откройте окно редактора VBA, нажав Alt + F11 на клавиатуре.
  3. Введите в поле «Непосредственное выполнение» «Selection.Calculate» и нажмите Enter на клавиатуре.

Если вы хотите повторить это, просто поместите курсор мыши в конец «Selection.Calculate» и снова нажмите Enter.

Расчет выбранных ячеек с помощью надстройки Excel

Наша надстройка Excel «Professor Excel Tools» включает более 120 функций. Один из них — «Вычислить выделение», который может реально сэкономить вам время.

Просто выберите ячейки, которые вы хотите рассчитать, и нажмите «Рассчитать выбор» на ленте Professor Excel.

Эта функция включена в нашу надстройку Excel «Professor Excel Tools»

(без регистрации, загрузка начинается сразу)

Более 35 000 пользователей не могут ошибаться.

Другие советы для таких больших моделей Excel

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

  • Можете ли вы использовать другое программное обеспечение (например, PowerBI или любой другой более подходящий инструмент/язык, такой как Python, R и т. д.)?
  • Возможно, на данном этапе вам придется остаться с Excel, верно? Тогда вы решили использовать PowerQuery? Может ли он взять на себя некоторые из более сложных вычислительных задач?
  • Если нет, почему бы вам не прочитать эту статью о том, как ускорить работу Excel, или мою книгу «Ускорение Microsoft Excel»?

Также интересно:

Хенрик Шиффнер — независимый бизнес-консультант и разработчик программного обеспечения. Живет и работает в Гамбурге, Германия. Помимо увлечения Excel, он увлекается фотографией и спортом.

Оставить комментарий Отменить ответ

Лучшие публикации и страницы

  • Объединение файлов Excel: как объединить книги в один файл
  • Возврат пустых ячеек вместо нулей в формулах Excel: легко!
  • Тысячи или миллионы в Excel: как изменить единицу измерения
  • Не удается выполнить прокрутку в Excel? Вот причины и простые решения!
  • Как отобразить сразу все строки или столбцы в Excel
  • Кнопки для вставки изображений или диаграмм в Excel выделены серым цветом?
  • ДВССЫЛ: как использовать текст в качестве ссылки на ячейку в Excel
  • Оглавление в Excel: 4 простых способа создания каталогов
  • Вставить название листа в ячейку: легко! 3 способа вернуть имя рабочего листа
  • Именованные диапазоны в Excel: просмотр всех определенных имен (включая скрытые имена)

Не пропустите главное

«Professor Excel Tools»: добавьте в Excel более 120 замечательных функций!

Перед уходом: улучшите свои навыки работы с Excel! Присоединяйтесь к тысячам пользователей Excel и подпишитесь на информационный бюллетень!

  • Лучшие советы, рекомендации и учебные пособия по Excel.
  • 1 раз в месяц.
  • Никакого спама. Обещано.

Обзор конфиденциальности

Нажмите здесь, чтобы прочитать нашу Политику конфиденциальности.

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

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

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

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

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

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

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

В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Формулы".

Выполните одно из следующих действий:

Чтобы пересчитывать все зависимые формулы каждый раз, когда вы вносите изменения в значение, формулу или имя, в разделе "Параметры расчета" в разделе "Расчет рабочей книги" нажмите "Автоматически". Это настройка расчета по умолчанию.

Чтобы пересчитывать все зависимые формулы, кроме таблиц данных, каждый раз, когда вы вносите изменения в значение, формулу или имя, в разделе "Параметры расчета" в разделе "Расчет рабочей книги" выберите "Автоматически, кроме таблиц данных".

Чтобы отключить автоматический перерасчет и пересчитывать открытые книги только в том случае, если вы делаете это явным образом (нажав клавишу F9), в разделе "Параметры расчета" в разделе "Расчет рабочей книги" нажмите "Вручную".

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

Чтобы вручную пересчитать все открытые рабочие листы, включая таблицы данных, и обновить все открытые листы диаграмм, на вкладке "Формулы" в группе "Расчет" нажмите кнопку "Рассчитать сейчас".

Чтобы вручную пересчитать активный лист и любые диаграммы и листы диаграмм, связанные с этим листом, на вкладке "Формулы" в группе "Расчет" нажмите кнопку "Рассчитать лист".

Совет. Кроме того, многие из этих параметров можно изменить вне диалогового окна «Параметры Excel». На вкладке "Формулы" в группе "Расчет" нажмите "Параметры расчета" и выберите "Автоматически".

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

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

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

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

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

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

В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Формулы".

В разделе "Параметры расчета" установите флажок "Включить итеративный расчет".

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

Чтобы установить максимальную сумму сдачи между результатами пересчета, введите ее в поле "Максимальная сдача". Чем меньше число, тем точнее результат и тем больше времени требуется Excel для пересчета рабочего листа.

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

Прежде чем изменить точность вычислений, имейте в виду следующие важные моменты:

По умолчанию 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 цифр, это не означает, что 15 цифр — это предел размера числа, которое вы можете хранить в Excel. Предел составляет 9,99999999999999E+307 для положительных чисел и -9,999999999999999E+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. Исключение составляют случаи, когда рабочая книга находится в другом режиме вычислений, например в ручном.

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

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

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

В электронной таблице Excel для Интернета перейдите на вкладку "Формулы".

В раскрывающемся списке рядом с параметрами расчета выберите один из следующих параметров:

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

Чтобы пересчитывать все зависимые формулы, кроме таблиц данных, каждый раз, когда вы вносите изменения в значение, формулу или имя, нажмите «Автоматически, кроме таблиц данных».

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

Чтобы вручную пересчитать рабочую книгу (включая таблицы данных), нажмите «Рассчитать рабочую книгу».

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

Нужна дополнительная помощь?

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

В этом руководстве мы рассмотрим, как пересчитывать и обновлять формулы в Microsoft Excel.

Мы сделаем это с помощью функций "Рассчитать сейчас" и "Рассчитать лист" в приложении.

Мы также рассмотрим использование сочетаний клавиш!

*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы все равно можете выполнить те же шаги.

Каспер Лангманн, соучредитель Spreadsheeto

Оглавление

Зачем нужно пересчитывать и обновлять?

Excel автоматически вычисляет все формулы на листе.

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

Вы можете потратить больше времени на ожидание завершения работы Excel, чем на обновление или ввод информации.

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

Два способа установить расчет вручную

Для первого метода перейдите на вкладку "Файл" на ленте и выберите "Параметры".

Когда откроется диалоговое окно "Параметры Excel", выберите "Формулы" в списке параметров слева.

Выберите «Вручную» в параметрах расчета.

Для второго метода перейдите в группу "Расчет" на вкладке "Формулы".

Каспер Лангманн, соучредитель Spreadsheeto

Нажмите стрелку вниз для параметра "Параметры расчета" и убедитесь, что вы выбрали "Вручную".

Получите БЕСПЛАТНЫЙ файл с упражнениями

Прежде чем начать:

В этом руководстве вам понадобится набор данных для практики.

Я включил один для вас (бесплатно).

Загрузите прямо ниже!

БОНУС: загрузите файл рабочей тетради Recalculate and Refresh Formulas, чтобы сопровождать этот пост.

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

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

Первым шагом для пересчета является переход в группу "Расчет" на вкладке "Формулы".

Затем вы нажимаете на один из вариантов расчета, где вы можете выбрать один из двух вариантов.

Первый вариант — "Рассчитать сейчас". Этот вариант позволяет рассчитать всю книгу.

Второй вариант — «Вычислить лист» — этот параметр будет вычислять активный рабочий лист.

Есть также несколько сочетаний клавиш для повышения эффективности при обновлении вычислений.

Читайте также: