Как писать формулы в Excel

Формулы в Microsoft Excel

Расчет формул в Excel – это то, что делает программу «живой». Благодаря этой возможности, Майкрософт Эксель получил широкое распространение и почти бесконечное поле применения. Давайтеразберемся как грамотно писать формулы, и наслаждаться работой высокого уровня!

В ячейках с формулами первый символ всегда знак равенства «=». Так Microsoft Excel понимает, что дальше будет записана формула. Если этот знак не поставить, или он будет не в начале строки, программа посчитает, что в ячейку внесен обычный текст. Когда вы ввели формулу – нажимайте Enter, программа рассчитает результат и отобразит в ячейке его значение (хотя фактически в ячейке будет формула). Чтобы увидеть и откорректировать формулу, выделите нужную ячейку и произведите все манипуляции в строке формул.

Частями формул могут быть:

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

Какие операторы применяются в формулах Эксель

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

Приоритет Оператор Описание
1 ^ Возведение в степень
2 *

/

Умножение

Деление

3 +

Сложение

Вычитание

4 & Конкатенация (объединение текстовых строк)
5  

=

>

<

Логические операторы:

Равно

Больше

Меньше

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

Применение функций мы рассмотрим в отдельном посте о функциях, здесь я уделю теме лишь несколько предложений. Функции Эксель – это особые операторы, выполняющие сложные вычисления. Например, функция СРЗНАЧ подсчитывает среднее значение в выбранном диапазоне.

Ввод формул в ячейку

Пора приступать к действиям, давайте учиться писать формулы. Например, в ячейке А4 нужно просуммировать значения из диапазона А1:А3. Это можно сделать двумя способами:

  1. Вручную. Установите курсор в ячейку А4 и введите с помощью клавиатуры: =A1+A2+A3. Нажмите Ввод, программа просчитает формулу и отобразит результат в ячейке
  2. Указанием. Вместо того, чтобы вручную писать адреса ячеек, можно их указать. Напишите в ячейке А4 «=», после этого кликните мышкой на ячейку А1 (либо выберите стрелками клавиатуры). В строке формул отобразится «=А1». После этого нажмите на клавиатуре «+» и укажите на ячейку А2. Аналогично прибавьте клетку А3. Нажмите Enter для выполнения расчета.

Вставка имён в формулы и использование

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

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

Автозаполнение при указании имени ячейки

  1. Установите курсор в нужное место формулы и нажмите F3 Откроется диалоговое окно «Вставка имени», выберите нужное и сделайте на нем двойной клик для вставки. Если в книге нет заданных имён, нажатие F3 ни к чему не приведет

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

Так, можно присвоить имя константе. Для этого, выполните такие действия:

  1. Кликните на ленточной команде Формулы – Определенные имена – Присвоить Имя. Откроется окно «Создание имени»
  2. В поле «Имя» запишите имя будущей константы
  3. В поле «Область» выберите область видимости константы – вся книга или какой-то конкретный лист
  4. В «Примечании» можете оставить свой комментарий
  5. В поле «Диапазон» запишите числовое значение, которое будете именовать и нажмите «ОК»

Именованная константа готова. Это лучшее решение, чем записывать в формулах значение цифрами. Во первых, по названию легче понять что это за значение, во вторых, это именованное значение легче изменить.

По аналогичной схеме можно присвоить имя формуле. Прописывая формулу в поле «Диапазон», не забудьте поставить вначале знак «=». Учитывайте в этих формулах виды ссылок, ведь это может повлиять на результат вычислений.

Редактирование формул

Если вы хотите внести изменения в формулу (а такое случается часто), это делается очень легко. Как всегда MS Excel предлагает несколько вариантов. Сначала установите курсор в ячейку с формулой, после этого выполните одно из действий:

  • Измените формулу в строке формул. Можете выделять участки формулы, заменять их другими, вставлять и удалять куски или отдельные символы. Когда закончите – жмите Enter
  • Дважды кликните левой кнопкой мыши на активной ячейке. Формула отобразится в ячейке вместо результата вычислений. Теперь внесите изменения на своё усмотрение и нажмите «Ввод»
  • Нажмите клавишу F2, она так же отобразит формулу в ячейке. Редактируем и нажимаем Enter.

Режимы вычислений

В Microsoft Excel есть 3 режима вычислений, умелое использование которых позволит вам хорошо управляться с формулами и экономить своё рабочее время. Чтобы выбрать режим вычислений, выполните на ленте: Формулы – Вычисления – Параметры вычислений. Откроется список для выбора одного из параметров вычислений:

Параметры вычислений в Excel

Параметры вычислений в Эксель

  1. Автоматически. Этот режим используется в программе по умолчанию. Формулы просчитываются сразу, при изменении влияющей ячейки, зависимые формулы будут моментально пересчитаны. Расчёт выполняется в естественной последовательности: сначала влияющие ячейки, затем зависимые. Этот режим подходит для небольших файлов, когда Эксель не оказывает большой нагрузки на процессор.
  2. Автоматически, кроме таблиц данных. Автоматически вычисляются все ячейки, кроме диапазонов, связанных в таблицу. Табличные формулы просчитываются вручную (после вашей команды на пересчёт). Режим используют, когда в рабочей книге есть большие таблицы с формулами, пересчёт которых длится значительное время. Тогда, вы сначала откорректируете влияющие ячейки, а потом скомандуете пересчитать таблицу (как это сделать – читайте в следующем пункте).
  3. Вручную. Формулы не пересчитываются, пока вы не дадите команду. Чтобы вычислить формулы, можно использовать такие комбинации клавиш:
    • F9 – пересчитать формулы всех открытых документов Excel
    • Shift+F9 – пересчитать формулы активного рабочего листа
    • Ctrl+Shift+F9 – пересчитать все формулы.

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

Как видите, продуманное переключение режимов позволит вам экономить ресурсы компьютера и своё личное время.

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

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

Ваш e-mail не будет опубликован. Обязательные поля помечены *