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

Как создать сложную формулу в Excel

В приведенном ниже примере, мы продемонстрируем, каким образом Excel вычисляет сложные формулы, опираясь на порядок выполнения операций. В данном примере мы хотим вычислить величину налога с продаж за услуги по питанию. Чтобы это осуществить, запишем следующее выражение в ячейке D4: =(D2+D3)*0,075 . Эта формула сложит стоимость всех позиций счета, а затем умножит на размер налога с продаж 7,5% (записанный как 0,075).

Excel придерживается порядка действий и сначала складывает значения в скобках: (44.85+39.90)=$84.75 . Затем умножает это число на налоговую ставку: $84.75*0.075 . Результат вычислений показывает, что налог с продаж составит $6.36 .

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

Создание сложных формул, используя порядок действий

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


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

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

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

Имеют некоторые секреты эксель формулы

Записывать формулы в Excel можно прямо в ячейке или в строке формул, а также можно в Excel записать формулу прямо в ячейке с помощью функций. С помощью ввода формул в Excel также высчитывают даты, время и проценты. Обозначаются в Excel столбцы буквами такими как A, B. C и так далее, а нумерация строк в excel произведена цифрами 1, 2, 3 и так далее. При пересечении столбца и строки получаете адрес ячейки в Excel который обозначается буквой столбца и цифрой строки например самая первая ячейка будет обозначаться A1.


Чтобы работать в эксель нужно знать названия ячеек

Любые формулы в экселе для расчета начинается со знака равенства =. Количество символов в ячейке Excel формула может содержать не более 1024. Делая какое-нибудь вычисление в excel в итоговой ячейке отобразится результат и если навести на эту ячейку с результатом курсор и нажать левую кнопку мыши то вы увидите в строке формул полученную формулу по которой производился расчёт в этой ячейке.

Сложение и вычитание

Если вам нужно в Excel сложить ячейки то нужно навести курсор на пустую ячейку в которой должен отображаться результат и нажать левую кнопку мыши. Затем на клавиатуре нажимаете клавишу =. Потом наводите курсор на первую ячейку которую будем суммировать с другими, нажимаем левую кнопку мыши. На клавиатуре нажимаете клавишу +. Наводите курсор на вторую ячейку которую будете суммировать с другими, нажимаете левую кнопку мыши. Если нужно будет ещё в Excel сложить числа из каких-нибудь ячеек то поочерёдно добавьте их в формулу нажимая на +, а затем на ячейку которую нужно прибавить. Когда будут зафиксированы все ячейки в формуле Excel нужно нажать на клавиатуре клавишу Enter и сразу в ячейке отобразится результат.


Очень легко в экселе сделать формулу сложения

Точно таким же способом создаётся формула в Excel для вычитания чисел. Только чтобы в Excel сделать вычитание нужно на клавиатуре вместо + нажимать минус -. При этом методе не важно в каком столбце и в какой строке находятся суммируемые и вычитаемые ячейки.


Пример как посчитать сумму в экселе в столбце

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

Умножение

Формула умножения в Excel составляется следующим способом. Например вам нужно перемножить в Excel ячейки A1 и A2, а результат чтобы отображался в ячейке A3.


Легко в Excel сделать формулу умножения

Сначала вам нужно нужно навести курсор на ячейку A3 и нажать левую кнопку мыши. На клавиатуре нажимаете клавишу =. Наводите курсор на ячейку A1 и нажимаете левую кнопку мыши. Затем на клавиатуре нажимаете знак умножения в Excel * который находится в левой части клавиатуры или на цифре 8. Чтобы отобразить знак умножения в экселе * находящийся на цифре 8 нужно сначала нажать на клавиатуре кнопку Shift и удерживая её нажать на * находящуюся на цифре 8. Потом наводите курсор на ячейку A2 и нажимаете левую кнопку мыши. В завершении нажимаете на клавиатуре клавишу Enter и сразу отобразится итог.

Деление

Формула деления в Excel составляется следующим образом. Например вам нужно разделить в Excel ячейку A1 на ячейку A2 и чтобы результат отображался в ячейке A3.


Легко в Excel ввести формулу деления

Сначала вам нужно нужно навести курсор на ячейку A3 и нажать левую кнопку мыши. На клавиатуре нажимаете клавишу =. Наводите курсор на ячейку A1 и нажимаете левую кнопку мыши. Затем на клавиатуре нажимаете знак деления в экселе / который находится в левой части клавиатуры или на клавише с вопросительным знаком. Чтобы поставить знак деления в Excel который находится на клавише с вопросительным знаком нужно сначала клавиатуру перевести на английскую раскладку и только потом нажать эту клавишу. Потом наводите курсор на ячейку A2 и нажимаете левую кнопку мыши. В завершении нажимаете на клавиатуре клавишу Enter и получаете результат деления.

Сложные формулы

Писать сложные формулы в Excel нужно точно такими же как и в математике. Например у вас заполнены ячейки данными и нужно чтобы в ячейке A4 отобразился результат исходя из формулы =A1+A2/(A3-A2)*A1.


Можно как писать формулы в Excel или вставлять уже готовые

Первым делом копируете эту формулу в буфер обмена чтобы потом вставить формулу в эксель. Наводите курсор на ячейку A4 и нажимаете левую кнопку мыши. Наводите курсор на строку формул в Excel и нажимаете левую кнопку мыши. Затем нужно в эту строку в Excel вставить формулу из буфера обмена, а для этого нажмите на клавиатуре клавишу Ctrl и удерживая её нажмите клавишу V. Формула пропишется в строке формул, а нажав на клавишу Enter сразу отобразится результат вычисления по этой формуле.
Чтобы удалить формулу из ячейки в excel достаточно навести курсор на ячейку, нажать левую кнопку мыши и удалить формулу в строке формул.

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

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

Для удобства восприятия я разделил формулы на несколько групп и буду показывать работу с ними от простого к сложному.

Нужно запомнить: вне зависимости от задачи все формулы начинаются со знака «=».

Сумма ячеек в экселе

Пожалуй самая востребованная формула. Пример простого написания формулы: =A1+B1

Давайте разберем ее. Вы уже наверное знаете, что таблица делится на столбцы, строки и ячейки. Столбцы маркируются с помощью букв «A, B, C» и т.д. Для строк используются цифры «1, 2, 3…».

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

Давайте проверим как Вы восприняли данную информацию. Какой индекс будет иметь ячейка, которая находится справа от ячейки «А1» и расположена с ней в одной строке? Правильно! Эта ячейка будет иметь обозначение «B1», так как находится в столбце «В» и в первой строке.

Давайте вернемся к нашей формуле

Сумма двух ячеек в Exel

Данная формура суммирует значение двух ячеек. В нашем случае это ячейки «А1» и «В1». Результат выводится в ту ячейку где Вы прописали формулу. Принцип я думаю понятен. Теперь покажу как это сделать на практике.

Деление и умножение ячеек в экселе

Так же довольно проста в написании. Общую теорию мы уже разобрали, поэтому сразу переходим к практике.

Среднее значение в экселе

Еще одна полезная функция.


Логические функции в экселе

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

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

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

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

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

  • Знаки математических и логических операций
  • Числа или текст

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

Можно создать простую формулу для сложения, вычитания, умножения и деления числовых значений на листе. Простые формулы всегда начинаются со знака равенства (= ), за которым следуют константы, т. е. числовые значения, и операторы вычисления, такие как плюс (+ ), минус (- ), звездочка (* ) и косая черта (/ ).

В качестве примера рассмотрим простую формулу.

    Выделите на листе ячейку, в которую необходимо ввести формулу.

    Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении.

    В нашем примере введите =1+1 .

    Примечания:

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

      В соответствии со стандартным порядком математических операций, умножение и деление выполняются до сложения и вычитания.

    Нажмите клавишу ВВОД (Windows) или Return (Mac).

Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return . Excel перемножит два последних числа и добавит первое число к результату умножения.

Использование автосуммирования

Для быстрого суммирования чисел в столбце или строке можно использовать кнопку "Автосумма". Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку Автосумма на вкладке Главная , а затем нажмите клавишу ВВОД (Windows) или Return (Mac).

Когда вы нажимаете кнопку Автосумма , Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT+ += (Mac), и Excel автоматически вставит функцию СУММ.

Пример: чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая находится прямо под столбцом с числами. Затем нажмите кнопку Автосумма . В ячейке В7 появляется формула, и Excel выделяет ячейки, которые суммируются.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Примечания:

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

    Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

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

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

Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем - ВВОД (Windows) или Return (Mac).

Данные

Формула

Описание

Результат

Сумма значений в ячейках A1 и A2

Разность значений в ячейках A1 и A2

Частное от деления значений в ячейках A1 и A2

Произведение значений в ячейках A1 и A2

Значение в ячейке A1 в степени, указанной в ячейке A2

Формула

Описание

Результат