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

Формулы EXCEL с примерами — Инструкция по применению

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

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

Применение формул позволяет определить значение одной ячейки исходя из внесенных данных в другие.

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

Что очень удобно для проведения различных расчетов, в том числе финансовых.

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

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

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

В программе можно вычислить целый комплекс показателей, в числе которых:

• максимальные и минимальные значения;
• средние показатели;
• проценты;
• критерий Стьюдента и многое другое.

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

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

При этом необходимость вводить дополнительные данные и параметры отпадает.

Как применять простые формулы в программе?

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

Для этого необходимо ввести в одну ячейку одно число, а во вторую – другое.

Например, В Ячейку А1 – число 5, а в ячейку В1 – 3. Для того чтобы в ячейке А3 появилось суммарное значение необходимо ввести формулу:

Вычисление суммарного значения двух чисел

Определить сумму чисел 5 и 3 может каждый человек, но вводить число в ячейку С1 самостоятельно не нужно, так как в этом и замысел расчета формул.

После введения итог появляется автоматически.

При этом если выбрать ячейку С1, то в верхней строке видна формула расчета.

Если одно из значений изменить, то перерасчет происходит автоматически.

Например, при замене числа 5 в ячейке В1 на число 8, то менять формулу не нужно, программа сама просчитает окончательное значение.

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

Сумма дробных чисел

В Excel можно производить любые арифметические операции: вычитание «-», деление «/», умножение «*» или сложение «+».

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

Любая формула должна начинаться знаком «=».

Если вначале не поставить «равно», то программа не сможет выдать необходимое значение, так как данные введены неправильно.

Создание формулы в Excel

В приведенном примере формула =СУММ(A1;B1) позволяет определить сумму двух чисел в ячейках, которые расположены по горизонтали.

Формула начинается со знака «=». Далее задана функция СУММ. Она указывает, что необходимо произвести суммирование заданных значений.

В скобках числятся координаты ячеек. Выбирая ячейки, следует не забывать разделять их знаком «;».

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

Формула суммы трех заданных чисел

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

=СУММ(A1:A10). На рисунке арифметическая операция будет выглядеть следующим образом:

Определение диапазона ячеек для формулы сложения

Также можно определить произведение этих чисел. В формуле вместо функции СУММ необходимо выбрать функцию ПРОИЗВЕД и задать диапазон ячеек.

Формула произведения десяти чисел

Совет! Применяя формулу «ПРОИЗВЕД» для определения значения диапазона чисел, можно задать несколько колонок и столбцов. При выборе диапазона =ПРОИЗВЕД(А1:С10), программа выполнит умножение всех значений ячеек в выбранном прямоугольнике. Обозначение диапазона – (А1-А10, В1-В10, С1-С10).

Комбинированные формулы

Диапазон ячеек в программе указывается с помощью заданных координат первого и последнего значения. В формуле они разделяются знаком «:».

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

Если нужно найти сумму трех чисел и умножить сумму на коэффициенты 1,4 или 1,5, исходя из того, меньше ли итог числа 90 или больше.

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

Создание сложных формул в Microsoft Excel

В этом уроке мы разберем, как создать сложную формулу в 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.

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

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

1. Выделите ячейку, которая будет содержать формулу. В нашем примере мы выбрали ячейку C4.
2. Введите в нее следующее выражение: =B2*C2+B3*C3. Действия в формуле будут выполняться в соответствии с правилами порядка, следовательно, первым идет умножение: 2.29*20=45.80 и 3.49*35=122.15. Затем эти значения будут суммированы для вычисления полной стоимости: 45.80+122.15.
3. Выполните проверку, затем нажмите Enter на клавиатуре. Формула вычислит и отобразит результат. В нашем случае результат вычислений показывает, что полная стоимость заказа составляет $167.95.

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

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

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

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

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

Составление элементарных формул

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

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

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

Эти 2 способа выше дублируют друг друга и можно выбрать тот, который больше по душе.
Далее начинаем писать формулу. Допустим, мы хотим просуммировать ячейки B2-B7. Тогда в ячейке B8 (можно выбрать любую другу свободную ячейку) после знака “=” щелкаем курсором по B2 (ее контур начинает пульсировать, а координата ячейки появляется в формуле), далее ставим знак “+”, выделяем следующую ячейку, далее снова знак “+” и так далее. В конечном виде формула выглядит так: =B2+B3+B4+B5+B6+B7

Как только мы перечислим все ячейки, нажимаем клавишу “ENTER”, чтобы получить результат.

Оператор формул в Excel

Вместо знака “+” в формуле можно использовать знаки вычитания, умножения и деления, при этом в Эксель есть специальные знаки для этих математических действий, которые называются операторами формул:

• Плюс – это знак “+”
• Минус – это знак “-” (дефис).
• Умножение – это знак “*” (звездочка).
• Деление – это знак “/” (слэш или косая черта).
• Возведение в степень – это знак “^” (циркумфлекс, находится на клавише с цифрой 6, печатается вместе с зажатой клавишей Shift).

А так могут выглядеть формулы в ячейке:

Примечание: все знаки должны идти без пробелов.

Также, в формулах может быть задействовано сколько угодно ячеек из различных строк и столбцов (=А1+А2+А3+А4+В6… и т.д), а результирующей может быть любая свободная ячейка.

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

Попробуем применить полученные знания на деле. Например, у нас есть заполненная таблица. В первом столбце указано наименование товара (разновидности велосипедов), во втором столбце – количество проданных штук, в третьем — цена за 1 штуку. Мы можем посчитать, на какую общую сумму был продан каждый вид велосипеда. Сделаем это, умножив цену за 1 штуку на количество проданных штук.

В ячейке D2, которая станет результирующей, ставим знак “=”, далее выделяем ячейку B2, ставим знак “*” и выделяем ячейку C2. Так формула выглядит в конечном виде: =B2*C2

Формула готова. Жмем “ENTER” и получаем результат умножения.

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

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

Создание формул со скобками

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

Допустим, у нас есть данные по продажам за 1 и 2 квартала, при этом цена товара оставалась неизменной. Узнать нужно общую сумму реализованного товара за 1-2 кварталы.

Для начала нужно сложить количество проданного товара в 1 и 2 кварталах, и далее умножить полученную сумму на цену за 1 штуку.

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

Ставим курсор на результирующую ячейку (E2), пишем знак “=”, далее открываем скобку, в ней складываем ячейки B2 и C2, далее скобку закрываем, ставим знак умножения и, наконец, координаты ячейки D2. Так формула выглядит в конечном виде: =(B2+C2)*D2

Формула готова. Теперь жмем клавишу “ENTER”, чтобы увидеть результат вычислений.

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

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

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

Программу Excel можно использовать как обычный калькулятор. Ставим знак “=” в любой ячейке, пишем нужную формулу, а затем нажимаем ” ENTER”, чтобы получить результат.

Заключение

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

Создание простой формулы в Excel

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

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

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

Введите = (знак равенства), а затем константы и операторы (не более 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).