Как обновить формулы в Excel

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

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

Чтобы пересчитать всю книгу:

• Нажмите F9 или
• Нажмите кнопку «Рассчитать сейчас» на вкладке Формулы в >группе «Расчет».

Чтобы пересчитать активный лист:

• Нажмите Shift + F9 или
• Нажмите «Вычислить лист» на вкладке Формулы >группа «Расчет».

Чтобы пересчитать все листы во всех открытых книгах, нажмите Ctrl + Alt + F9.

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

Формулы Excel не вычисляют

Проблема: в ячейке отображается формула, а не результат.

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

1. Режим "Показать формулы" включен

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

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

• Нажатие сочетания клавиш Ctrl + `или
• Нажав кнопку "Показать формулы" на вкладке Формулы в группе Аудит формул.

2. Формула вводится как текст

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

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

3. Ячейка с формулой имеет начальный пробел или апостроф перед знаком равенства

Если вы случайно ввели пробел или апостроф (') перед знаком равенства, Excel обрабатывает содержимое ячейки как текст и, следовательно, не оценивает никакую формулу в этой ячейке (при копировании формулы из паутина). Чтобы это исправить, просто удалите начальный пробел или одинарную кавычку.

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

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

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

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

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

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

Оглавление

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Уровень навыков: начинающий

Посмотреть обучающее видео

Загрузить файл Excel

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

График погашения кредита – Пример расчета вручную.xlsx

Почему мои формулы не работают?

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

Это случилось с моим другом Бреттом. Он говорил мне, что работает с файлом, и он не пересчитывает формулы, а вводит данные. Он обнаружил, что ему приходилось редактировать каждую ячейку и нажимать Enter, чтобы формула в ячейке обновлялась.

И это происходило только на его домашнем компьютере. Его рабочий компьютер работал нормально. Это сводило его с ума и отнимало много времени.

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

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

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

Настройки расчета сбивают с толку!

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

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

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

Это должно происходить только для первого файла, который вы открываете во время сеанса Excel.

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

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

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

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

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

3 варианта расчета

В Excel есть три варианта расчета.

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

Ручное вычисление означает, что Excel будет пересчитывать только тогда, когда вы принуждаете его к этому. Это может быть нажатие кнопки или сочетание клавиш. Вы также можете пересчитать одну ячейку, отредактировав ячейку и нажав Enter.

Автоматически, кроме таблиц данных, означает, что Excel будет автоматически пересчитывать все ячейки, кроме тех, которые используются в таблицах данных. Это не относится к обычным таблицам Excel, с которыми вы можете часто работать. Это относится к инструменту анализа сценариев, которым пользуются немногие. Вы найдете его на вкладке «Данные» под кнопкой «Что, если сценарии». Поэтому, если вы не работаете с этими таблицами данных, маловероятно, что вы когда-либо намеренно измените настройку на этот параметр.

Помимо параметра "Расчет" на вкладке "Данные", его также можно найти в меню "Параметры Excel". Перейдите в «Файл», затем «Параметры», затем «Формулы», чтобы увидеть те же параметры настройки в окне «Параметры Excel».

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

Нажмите, чтобы увеличить

Зачем использовать режим ручного расчета?

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

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

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

Вот список всех сочетаний клавиш для пересчета:

Макрос переходит в режим ручного расчета

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

Теперь я хочу предварить это, сказав, что проблема вызвана НЕ всеми макросами. Это определенная строка кода, которую разработчик может использовать для ускорения работы макроса.

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

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

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

Однако проблема возникает, когда макрос не завершается — возможно, из-за ошибки, сбоя программы или неожиданной системной проблемы. Макрос изменяет настройку на Вручную, и она не возвращается обратно.

Как я упоминаю в видео, это именно то, что случилось с моим другом Бреттом, и он НЕ знал об этом. Он остался в ручном режиме расчета и не знал, почему и как снова заставить Excel выполнять вычисления.

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

Я также рекомендую НЕ изменять свойство Calculation с помощью кода, если в этом нет крайней необходимости. Это поможет предотвратить разочарование и ошибки пользователей ваших макросов.

Заключение

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

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

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

Вот пример нашей электронной таблицы:

Теперь добавим число в список и посмотрим, что произойдет:

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

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

Вот обычный SUM:

Вот динамически обновляемая функция суммы:

Чтобы сделать приведенную выше формулу совместимой с версиями Excel до Excel 2007, используйте это:

Это связано с тем, что вы не можете ссылаться на весь столбец в Excel 2003 и более ранних версиях, а A:A ссылается на весь столбец, столбец A.

Теперь давайте разделим эту формулу.

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

Это та часть приведенной выше формулы, которая делает ее динамической:

Все, что вам нужно сделать, это изменить эти две части, чтобы они работали на вас:

СМЕЩЕНИЕ(A2,0,0,СЧЁТ(A:A))

Чтобы использовать функцию OFFSET() для возврата динамического диапазона, нам просто нужно поместить первую ячейку в диапазоне в качестве первого аргумента, в данном случае A2.

Затем нам нужно использовать функцию COUNT() в качестве четвертого аргумента для функции OFFSET(), чтобы выяснить, насколько большим должен быть наш диапазон. Измените A:A на любой столбец, содержащий ваш список данных.

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

Если вы используете Excel 2007 или более позднюю версию, просто используйте A:A в качестве аргумента для функции COUNT(), как показано в первом динамическом примере выше. Таким образом, будет указана ссылка на весь столбец.

Динамические диапазоны, содержащие текст и многое другое

Если вы работаете в Excel 2007 и более поздних версиях, вы можете использовать функцию COUNTA() вместо функции COUNT(). Функция COUNTA() подсчитывает все ячейки, которые НЕ пусты, тогда как функция COUNT() подсчитывает только те ячейки, в которых есть числа.

Рекомендации

Если вы сделаете формулы и функции динамическими с помощью функции OFFSET(), как указано выше, это очень быстро вызовет путаницу и беспорядок при создании больших формул.

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

В этом учебном пособии по динамическому именованному диапазону в Excel показано, как это сделать.

Функция СМЕЩ() – это ключ к созданию динамических формул и функций в Excel. Обязательно ознакомьтесь с нашим учебным пособием по функции OFFSET, если вы хотите получить более подробное объяснение того, как она работает.

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

  
• Таблица Word не работает
  
• Настройка Outlook 2010 Exchange 2010
  
• Как удалить вкладки в Google Chrome
  
• Как очистить кеш браузера на iphone
  
• Как изменить шрифт текста в Adobe Premiere Pro