Обновить формулы в 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 здесь.
Содержание
-
Параметры расчета установлены на «Ручной расчет»
-
Формат ячейки установлен на текст
-
«Показать формулы» включено
-
Циклические ссылки
Давайте перейдем непосредственно к самой распространенной причине того, что формулы не обновляются:
Параметры расчета установлены в режим «Ручной расчет»
Что? 😲
Неужели все так просто?
В Excel действительно можно изменить настройку расчета.
Вы можете проверить и установить текущий режим расчета следующим образом:
1. Перейдите на вкладку Формулы.
2. Нажмите «Параметры расчета».
3. Убедитесь, что для параметра расчета установлено значение «Автоматически».
4. Формулы не будут пересчитываться автоматически, если Excel установлен в ручной режим расчета.
В рабочей тетради Excel формула в ячейке C2 представляет собой простую формулу сложения:
=A2 + B2
Вы можете изменить значения A2 и B2 по своему усмотрению…
…но результат формулы не изменится, пока настройка находится в режиме ручного расчета.
5. Чтобы получить правильный результат, установите для параметров расчета значение Автоматический режим расчета.
Вуаля!
Теперь вы можете вернуться к работе с Excel как обычно!
В качестве альтернативы…
Вы также можете изменить режим расчета, выбрав «Файл» > «Дополнительно…» > «Параметры» > вкладка «Формулы».
Можно выбрать один из четырех режимов расчета:
- Автоматический — Все зависимые формулы во всей книге пересчитываются при изменении значений ячеек.
- Автоматически За исключением таблиц данных — То же, что и Автоматически. Но таблицы данных пересчитываются только в том случае, если ячейка внутри таблицы изменилась. Или если вы нажмете Рассчитать сейчас или Рассчитать лист в ленте Формулы
- Вручную / Пересчитать перед сохранением — То же, что и Вручную режим расчета. Но формулы также пересчитываются каждый раз, когда вы сохраняете файл.
Но почему мой режим расчета изменился? 🤔
Имейте в виду, что настройка расчета является настройкой уровня приложения.
Если вы измените режим расчета, это будет применяться ко всей книге и ко всем другим открытым книгам.
Кроме того, Excel использует последний сохраненный режим вычислений первой открытой книги. Все рабочие книги, открытые в одном сеансе, будут использовать один и тот же режим.
Попробуйте открыть учебный файл Excel без каких-либо других открытых рабочих книг.
Вы заметите, что он открывается в ручном режиме расчета. Это была настройка, с которой он был сохранен в последний раз.Вы можете узнать больше о поведении Calculation Mode на официальном сайте Microsoft.
Каспер Лангманн , специалист Microsoft Office
Запуск макроса также может изменить режим расчета
Это особенно верно, если разработчик макроса или VBA использовал любую из следующих строк:
- Application.Calculation = xlCalculationAutomatic
- Application.Calculation = xlCalculationManual
Если вы не работаете с рабочей книгой с поддержкой макросов , то вы определенно можете это исключить!
Зачем использовать режим «Ручной расчет»?
Ну, если вы работаете с большим объемом данных, вы можете заметить небольшое отставание или задержку в Excel. 🐌
Обычно это происходит из-за того, что Excel автоматически пересчитывает формулы при каждом изменении.
Это может сильно замедлить вашу работу.
💡 Таким образом, вы можете перейти на режим расчета Ручной при вводе или изменении данных и вернуться к Автоматический позже.
Ярлыки для ручного расчета
В ручном режиме формулы можно обновить, нажав F9 .
Вы также можете нажать кнопку Рассчитать сейчас или Рассчитать лист на ленте Формулы .
Это поможет вам сэкономить время и избежать стресса, связанного с ожиданием завершения обновления формул в Excel!
Ячейка отформатирована как текст
Неправильный формат ячейки также может помешать автоматическому вычислению формулы.
Взгляните на рабочий лист «Example-Text Format» учебного файла Excel.
Формула Excel в ячейке C2 точно такая же, как формула в ячейке C2 рабочего листа «Пример-режим расчета».
Но на листе «Example-Text Format» отображается только формула, а не значение.
Даже если для рабочей книги Excel не установлен ручной режим расчета, значение ячейки не будет обновляться.
Чтобы исправить это, вы можете изменить формат ячейки:
1. На ленте «Главная» щелкните раскрывающийся список «Числовой формат».
2. Выберите нужный числовой формат.
Для вычислений обычно используются форматы General и Number.
3. Дважды щелкните ячейку или щелкните строку формул.
Ссылки на ячейки теперь должны быть выделены, как обычно в формуле Excel.
4. Нажмите Enter, чтобы получить результат!
Excel настроен на отображение формул вместо результатов
Еще одна вещь, которую следует учитывать, — это функция «Показать формулы».
Если это ON , в ячейках вместо значений будут отображаться формулы. 9+ `
Функция Показать Формулы изменяет отображение между формулой и значением ячейки.
Это позволяет проверять ошибки и несоответствия во всей книге.
Попробуйте сами!
Где-то в вашей книге есть циклическая ссылка
У вас все еще есть проблемы даже после вышеуказанных исправлений?
Если это так, возможно, в вашей книге есть циклическая ссылка.
Это когда формула прямо или косвенно ссылается на свою ячейку.
В рабочем листе «Example-Circ Ref 1» рабочей тетради у нас есть простая формула СУММА в ячейке B6.
Формула включает себя в расчет «=СУММ(B2:B6)».
Таким образом, формула не будет считаться правильно.
Вы можете идентифицировать и исправлять формулы с циклическими ссылками следующим образом:
1. На ленте Формулы щелкните Проверка ошибок .
2. Откроется раскрывающийся список. Выберите Циклические ссылки.
Затем он покажет вам список ячеек с циклическими ссылками, которые необходимо исправить.
Альтернативный метод проверки циклических ссылок
Вы можете проверить нижний левый угол окна Excel.
При наличии циклических ссылок будет отображаться сообщение, подобное приведенному ниже.
Вы также можете увидеть синих линий , которые показывают формулы, зависящие друг от друга.
Откройте рабочий лист «Example-Circ Ref 2» для следующего примера.
Здесь у вас немного более сложные формулы, связанные с электронной таблицей бюджета.
В приведенном выше примере показаны два способа расчета непредвиденных расходов и общей стоимости проекта.
- В Расчет A : Формула для общей стоимости проекта в ячейке B9 — «=СУММ(B4:B8)», а формула для 5% непредвиденных расходов в ячейке B7 — «=B9*0,05».
Таким образом, в ячейках B7 и B9 формулы зависят друг от друга. Это циклическая ссылка, и формулы Excel по умолчанию равны нулю. - В Расчет B : Эта круговая ссылка фиксируется наличием промежуточного итога в ячейке F7.
Таким образом, непредвиденные расходы могут быть рассчитаны в ячейке F8, а общая стоимость проекта теперь равна «=F7+F8».
Здесь нет циклических ссылок, поэтому значения вычисляются правильно!
Вы можете узнать больше об этом и других примерах в нашем руководстве по циклическим ссылкам здесь.
Вот и все – Что теперь?
Теперь вы знакомы с четырьмя наиболее распространенными причинами, по которым ваши формулы Excel не вычисляются автоматически.
Причина номер 1, настройка режима расчета, почти всегда является виновником ⚙️
Это особенно актуально, когда вы открываете рабочие книги, загруженные из Интернета. Или если вы открываете книги с другого компьютера.
Вы столкнетесь с неправильной ячейкой Text форматированием, Show Formulas toggle и Circular References реже.
Но все это не имеет значения, если вы не знаете, как на самом деле строить формулы и функции, облегчающие вашу работу.
Такие функции, как ЕСЛИ, СУММЕСЛИ и ВПР.
Все это (и многое другое!) вы узнаете на моем бесплатном 30-минутном тренинге.