Составить формулу в excel: Как сделать формулу в Эксель

Как сделать формулу в Эксель

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

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

Содержание

• Составление элементарных формул
• Оператор формул в Excel
• Примеры вычислений
• Создание формул со скобками
• Использование Excel в качестве калькулятора
• Заключение

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

Смотрите также: “Как конвертировать ПДФ в Эксель”

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

При этом данные для вычислений должны находиться в разных ячейках/столбцах/строках.

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

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

   Эти 2 способа выше  дублируют друг друга и можно выбрать тот, который больше по душе.

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

3. Как только мы перечислим все ячейки, нажимаем клавишу “ENTER”, чтобы получить результат.
   ” (циркумфлекс, находится на клавише с цифрой 6, печатается вместе с зажатой клавишей Shift).

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

• =А1-А2
• =А1/А2
• =А1*А2

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

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

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

Смотрите также: “Промежуточные итоги в Excel: функция и применение”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Смотрите также: “Как автоматически пронумеровать строки в таблице Эксель”

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

С момента своего появления в составе пакета Office Эксель стал постоянным рабочим инструментом для тех, кто занимается анализом и структурированием данных. С годами благодаря постоянной разработке и улучшениям этого продукта пользоваться им смогли все — от «продвинутых» до самых обычных людей. Курс на упрощение работы с данными от Microsoft выражается в постоянном упрощении методов работы с сохранением функционала, так что вполне объяснимо, что в руках пользователя Excel становится мощнейшим инструментом.

Для проведения расчётов используют готовые функции

Тем не менее, многие функции пока что не получается упростить. Это не значит, что они потребуют от вас навыков программирования или знания высшей математики. Хотя написание макросов и может предъявить вам такие условия, но у них в принципе высокий порог вхождения. Большую часть функционала Office вы в состоянии использовать с помощью кликов мышью и коротких команд с клавиатуры.




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

Для обработки значений и их диапазонов — от банальной суммы до методов математической статистики вроде медианы или моды — используются формулы. Это очень гибкий и при этом достаточно простой инструмент, который широко используется наряду с условным форматированием при создании таблиц с данными. В самом простом случае для записи формулы в ячейку используется обычный текстовый ввод. Чтобы провести, например, сложение значений из нескольких полей, можно прямо в ячейке написать «=СУММ()» и в скобках указать адреса значений через точку с запятой. Или, если они расположены рядом друг с другом, указать начальную и конечную через двоеточие. А чтобы закрепить в этой формуле ячейку, например, чтобы не менялись столбец или строка при копировании формулы в другую, поставьте перед нужным символом знак «$». Тогда Эксель не будет менять этот экранированный символ, при этом меняя другие.

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

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

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

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

Удаление формул

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

Заключение

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

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

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

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

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

• Создание формул в Excel с использованием констант и операторов
• Написание формул в Excel с использованием ссылок на ячейки
• Создание формулы Excel с использованием определенных имен
• Создание формулы в Excel с помощью функций

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

В формулах Microsoft Excel константы — это числа, даты или текстовые значения, которые вы вводите непосредственно в формулу. Чтобы создать простую формулу Excel с использованием констант, просто сделайте следующее:

• Выберите ячейку, в которую вы хотите вывести результат.
• Введите символ равенства (=), а затем введите уравнение, которое вы хотите рассчитать.
• Нажмите клавишу Enter, чтобы завершить формулу. Сделанный!

Вот пример простой формулы вычитания в Excel:
=100-50

Как писать формулы в Excel, используя ссылки на ячейки

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

Например, если вы хотите вычесть значение в ячейке B2 из значения в ячейке A2, введите следующую формулу вычитания: =A2-B2


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


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

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

Как создать формулу Excel, используя определенные имена

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

Самый быстрый способ создать имя в Excel — выбрать ячейку (ячейки) и ввести имя непосредственно в поле 9.0003 Имя ящика . Например, вот как вы создаете имя для ячейки A2:


Профессиональный способ определить имя — через вкладку Формулы > группу Определенные имена или сочетание клавиш Ctrl+F3. Подробные инструкции см. в разделе Создание определенного имени в Excel.

В этом примере я создал следующие 2 имени:

• доход для ячейки A2
• расходы по ячейке Б2

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


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

Например, если вы создадите имя 2015_продажи для ячеек A2:A100, вы можете найти общее количество этих ячеек с помощью следующей формулы СУММ: =СУММ(2015_продажи)

Конечно, вы можете получить того же результата, указав диапазон функции СУММ: =СУММ(A2:A100)

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

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

Как создавать формулы Excel с помощью функций

Функции Excel — это не что иное, как предопределенные формулы, выполняющие требуемые вычисления за кулисами.

Каждая формула начинается со знака равенства (=), за которым следует имя функции и аргументы функции, указанные в скобках. Каждая функция имеет определенные аргументы и синтаксис (особый порядок аргументов).

Для получения дополнительной информации см. список наиболее популярных функций Excel с примерами формул и снимками экрана.

В электронных таблицах Excel вы можете создать формулу на основе функции двумя способами:

• С помощью мастера функций
• Написав формулу в ячейку или строку формул

Создание формулы в Excel с помощью Мастера функций

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

1. Запустите мастер функций.

Чтобы запустить мастер, нажмите кнопку Кнопка Вставить функцию на вкладке Формулы > группа Библиотека функций или выберите функцию из одной из категорий:


Кроме того, вы можете нажать кнопку Вставить функцию слева от строки формул.

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


2. Найдите функцию, которую хотите использовать.

Когда появится мастер Вставить функцию , выполните следующие действия:

• Если вы знаете имя функции, введите его в поле Поиск функции и нажмите Перейти .
• Если вы не уверены, какую именно функцию вам нужно использовать, введите очень краткое описание задачи, которую вы хотите решить, в поле Поиск функции и нажмите Перейти . Например, вы можете ввести что-то вроде этого: « сумма ячеек» или « количество пустых ячеек» .
• Если вы знаете, к какой категории относится функция, щелкните маленькую черную стрелку рядом с Выберите категорию и выберите одну из 13 перечисленных категорий. Функции, принадлежащие к выбранной категории, появятся в списке Выберите функцию

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

Найдя нужную функцию, выберите ее и нажмите OK.


3. Укажите аргументы функции.

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

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

Краткое объяснение текущего выбранного аргумента отображается прямо под описанием функции. Для получения более подробной информации нажмите кнопку Справка по этой функции ссылка внизу.


Функции Excel позволяют выполнять вычисления с ячейками, расположенными на одном рабочем листе, разных листах и ​​даже в разных рабочих книгах. В этом примере мы рассчитываем среднее значение продаж за 2014 и 2015 годы, расположенное в двух разных электронных таблицах, поэтому ссылки на диапазоны на приведенном выше снимке экрана включают имена листов. Узнайте больше о том, как ссылаться на другой лист или книгу в Excel.

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

4. Заполните формулу.

Когда вы указали все аргументы, нажмите кнопку OK (или просто нажмите клавишу Enter), и заполненная формула будет введена в ячейку.


Напишите формулу непосредственно в ячейке или строке формул

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

Как обычно, вы начинаете с ввода знака равенства (=), за которым следует имя функции. При этом Excel выполнит своего рода добавочный поиск и отобразит список функций, которые соответствуют той части имени функции, которую вы уже набрали:


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


После ввода последнего аргумента введите закрывающую скобку и нажмите Enter, чтобы завершить формулу.

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


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

Вас также может заинтересовать

Как сделать функцию в Excel: Пользовательские ярлыки

Тепинг Крокер Категории: Excel®, Функции Теги: пользовательская функция, определяемая пользователем функция

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

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

Подготовьте рабочую тетрадь

Поскольку пользовательские функции используют VBA, ваш файл должен быть сохранен как книга .xlsm с поддержкой макросов.

Выполните описанные ниже действия в новой чистой рабочей книге с поддержкой макросов или загрузите файл 06-Custom Functions. xlsm , чтобы просмотреть завершенную пользовательскую функцию.

Определите свои переменные

Функция создает расчет на основе переменных, введенных во время использования функции. Чтобы создать пользовательскую функцию, вам сначала нужно определить аргументы, необходимые для вашей функции. Мы собираемся создать функцию для быстрого вычисления площади треугольника: площадь = основание, умноженное на высоту, деленное на 2, или A=BH/2. Нашими аргументами будут основание и высота.

Открыть редактор Visual Basic

Нажмите Alt + F11 или нажмите кнопку Visual Basic на вкладке «Разработчик».

Выберите Вставка > Модуль в редакторе VBA:

Типовой код

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

.

Функция имя_функции (аргументы) возвращаемый тип
Myfunctionname = расчет
Завершить функцию

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

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

Общая функция TRIAREA(число1, число2)
TRIAREA = (число1 * число2) / 2
Завершить функцию

Использование пользовательской функции

Вернувшись в книгу Excel, поместите курсор в то место, куда вы хотите вставить пользовательскую функцию:

1. Щелкните Вставить функцию на вкладке Формулы .
2. В диалоговом окне Вставить функцию выберите Определяется пользователем из раскрывающегося списка «выберите категорию». Вы должны увидеть имя, которое вы дали своей функции. В этом примере мы видим TRIAREA.
3. Выберите функцию, затем нажмите ОК.
4. Заполните диалоговое окно Аргументы функции и нажмите OK.

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