Обновить формулы в excel горячая клавиша: Как пересчитать формулы только в выбранном диапазоне Excel – инструкция

Содержание

Как пересчитать формулы только в выбранном диапазоне Excel – инструкция

При работе с большими книгами 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. Как проставить якоря в формулах

Специальная вставка в Excel

Циклические ссылки в Excel

Если копируете формулу, например, в правую ячейку, то все формулы тоже перемещаются вправо. Неудобно. Как закрепить формулы в нужной ячейки? Используйте абсолютные и относительные ссылки в Excel. Многие знают про так называемые якоря или символ $ в формулах. А знаете ли вы как быстро проставить $ якоря в нужном месте? Это горячая клавиша F4

Содержание

  • Введение. Абсолютные и относительные ссылки
  • Хитрости. R1C1 в привычный формат
  • Похожие статьи
Введение. Абсолютные и относительные ссылки

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

Ссылки в MS Excel — это как ссылки в интернете. Ссылкой называется адрес ячейки (например: А10, А — название столбца, 10 — название строки). Они делятся на два основных типа- абсолютные и относительные . В Excel формулы состоят из адресов и при перетаскивании нужные адреса сбиваются.

Для тех кто уже знает, что это за ссылки, открою маленький секрет: существует горячая клавиша F4. Нажав на нее 1 раз, когда активна формула, вы получаете абсолютную ссылку, нажав еще по разу, получите смешанные ссылки. Мне кажется, я сэкономил несколько часов своей жизни, когда узнал о ней.

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

Т.е. если вы перетаскиваете формулу влево, то нужно закрепить (поставить якорь, т.е. знак $) название столбца (буква), если перетаскиваете вниз, то перед цифрой. Особенно удобно, если вы из большого массива данных выбираете информацию по двум критериям (читайте =СУММЕСЛИМН()), например, по месяцу и городу.

Хитрости. 

R1C1 в привычный формат

Если хотите выделить в формуле достаточно большой диапазон, например А2:А10000, я советую выбирать весь столбец, т.е. в формате А:А. Соответственно, для всего столбца абсолютная ссылка будет выглядеть как $A:$A. Не забывайте про горячие клавиши. А лучше создавайте диапазоны. Какие еще диапазоны? Читайте здесь 😉

Да и еще… Если у вас и в столбцах и в строках цифры? Значит у вас включен тип ссылок R1C1, он достаточно удобен в программировании. Но его легко изменить. Жмете на круглую кнопку в верхнем левом углу, внизу меню — Параметры Excel-  Формулы — снимаете галочку Стиль ссылок R1C1.

 

Специальная вставка в Excel

Циклические ссылки в Excel

Вычисление формулы Excel с помощью режима «Редактировать» и клавиши F9

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

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

Самый быстрый способ оценить формулу в Excel — нажать CTRL + ~ (тильда). Это переключает отображение текущего рабочего листа, позволяя переключаться между значениями ячеек и формулами ячеек. Это отлично подходит для простого понимания того, как работает формула, или для определения положения #REF! ошибка — особенно в области, содержащей несколько ячеек.

Если вам требуется дополнительная информация о том, как работает ваша формула или ее подмножество, вы можете оценить формулу на более детальном уровне, используя функцию Excel «Оценить формулу» (Alt + M + V). Это позволяет оценивать формулу по ячейке за ячейкой. Однако есть два ключевых фактора, ограничивающих эту функцию: (i) вы можете оценивать формулу только слева направо и (ii) правки нельзя вносить непосредственно в диалоговом окне «Вычислить формулу».

Чтобы преодолеть эти ограничения, вы можете использовать клавишу F9. ключ для оценки вашей формулы или подмножества вашей формулы. Для этого вы должны сначала нажать клавишу F2 в нужной ячейке, чтобы активировать режим «Редактировать» (см. Наше предыдущее сочетание клавиш на двойной клавише F2).

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

Имейте в виду две ключевые вещи при оценке формулы в режиме «Редактировать» и клавише F9:

  • Обязательно выделяйте части формулы в режиме «Редактирования», прежде чем нажимать F9, иначе вся формула будет упрощена. до расчетного значения
  • Когда вы закончите оценивать свою формулу:
    • Нажатие клавиши Escape приведет к выходу из режима «Редактировать» и возврату ячейки к исходной формуле
    • Нажатие клавиши Enter заменяет любое из рассчитанных значений в виде жестких кодов в исходной формуле

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

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

  • Предположим, у вас есть простая таблица, как показано на рисунке ниже, и вы хотите получить показатель EBITDA за 2018 год на основе входных данных ячеек C3 и C4.
  • Ячейка C5 должна возвращать число 444, но вместо этого возвращает ошибку #Н/Д.
  • Чтобы выяснить, откуда возникла ошибка, нажмите F2 в ячейке C5, чтобы войти в режим «Редактировать».
  • Формула в ячейке C5 отображается и использует формулу ИНДЕКС-ПОИСКПОЗ-ПОИСКПОЗ для получения нужной метрики в течение нужного года. Требуемое значение находится в строке 2 столбца 3 массива INDEX.
  • Выделите часть формулы, которая говорит ПОИСКПОЗ(C3,C8:G8,0).
  • После выделения нажмите F9. Это должно вернуть значение 3, что и происходит.
  • Поскольку эта часть формулы не привела к ошибке, тот же процесс можно применить к следующей части формулы.
  • Выделите часть формулы ПОИСКПОЗ(C4,B9:G12,0).
  • После выделения нажмите F9. Это должно вернуть значение 2, но вместо этого возвращает ошибку #Н/Д.
  • Нажмите клавишу Escape, чтобы выйти из режима «Редактировать» и вернуть ячейке исходную формулу.
  • Теперь, когда найдена часть формулы, содержащая ошибку #Н/Д, эту часть формулы можно отредактировать.
  • Чтобы изменить формулу, нажмите F2 в ячейке C5, чтобы войти в режим «Редактировать».
  • Замените ПОИСКПОЗ(C4,B9:G12,0) на ПОИСКПОЗ(C4,B9:B12,0), так как массив ПОИСКПОЗ должен включать только столбец, в котором найдена нужная метрика.
  • Нажмите клавишу Enter, и ячейка C5 обновится, чтобы отобразить число 444, что является правильным значением.

Как исправить формулы Excel, которые не вычисляют (обновить формулы)

Перейти к содержимому Как исправить формулы Excel, которые не вычисляют (обновить формулы)

Формулы — это жизнь и кровь Microsoft Excel.

Мы используем их для сложения чисел, вычитания дат и даже извлечения текстов.

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

Но что произойдет, если это не так?

Очевидно, 2+2 = 4! Не 5!

Можем ли мы быть лучше в базовой математике, чем в Excel? Наверное, нет 🤣

Итак, как исправить формулу, которая не вычисляется автоматически?

В этом уроке вы узнаете, почему ваши формулы не обновляются и как это исправить!

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

Содержание