Как вводятся формулы в excel. Математические функции и графики. Как использовать формулы в Exel

Здравствуйте!

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

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

Возможно, что прочти подобную статью лет 15-17 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения "простых" (прим.: как я сейчас понимаю) задач)...

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel - задают один странный вопрос: "ну и где тут таблица?". Между тем, все клеточки, что вы видите после запуска программы - это и есть одна большая таблица!

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

• слева : в ячейке (A1) написано простое число "6". Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число "6".
• справа : в ячейке (C1) с виду тоже простое число "6", но если выбрать эту ячейку, то вы увидите формулу "=3+3" - это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например "=3+5+8" (без кавычек). Результат вам писать не нужно - Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму - можно написать формулу двумя способами:

• первый: "=5+6" (не совсем удобно, представьте, что в ячейке A1 - у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
• второй: "=A1+B1" - а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!)

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

1. в строке 2 написать формулу "=A2+B2" , в строке 3 - "=A3+B3" и т.д. (это долго и утомительно, этот вариант никогда не используют);
2. выбрать ячейку D1 (в которой уже есть формула), затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже). Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! (Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)).

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 - то увидите формулу "=A2+B2" (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

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

Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже - это G2.

Далее в ячейке E2 пишется формула "=D2*G2" и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу "D3*G3", в 4-ю строку: "D4*G4" и т.д. Надо же, чтобы G2 везде оставалась G2...

Чтобы это сделать - просто измените ячейку E2 - формула будет иметь вид "=D2*$G$2". Т.е. значок доллара $ - позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже)...

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая "=A1+B1+C1" и т.п. Но в Excel есть более быстрые и удобные инструменты.

Один из самых простых способов сложить все выделенные ячейки - это использовать опцию автосуммы (Excel сам напишет формулу и вставить ее в ячейку).

1. сначала выделяем ячейки (см. скрин ниже);
2. далее открываем раздел "Формулы" ;
3. следующий шаг жмем кнопку "Автосумма" . Под выделенными вами ячейками появиться результат из сложения;
4. если выделить ячейку с результатом (в моем случае - это ячейка E8 ) - то вы увидите формулу "=СУММ(E2:E7)" .
5. таким образом, написав формулу "=СУММ(xx)" , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк...

Довольно часто при работе требуется не просто сумма всего столбца, а сумма определенных строк (т.е. выборочно). Предположим простую задачу: нужно получить сумму прибыли от какого-нибудь рабочего (утрировано, конечно, но пример более чем реальный).

Я в своей таблицы буду использовать всего 7 строк (для наглядности), реальная же таблица может быть намного больше. Предположим, нам нужно посчитать всю прибыль, которую сделал "Саша". Как будет выглядеть формула:

1. "=СУММЕСЛИМН(F2:F7 ;A2:A7 ;"Саша") " - (прим .: обратите внимание на кавычки для условия - они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру "СУММ..."), сам подсказывает и подставляет возможные варианты - а формул в Excel"e сотни!;
2. F2:F7 - это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
3. A2:A7 - это столбик, по которому будет проверяться наше условие;
4. "Саша" - это условие, те строки, в которых в столбце A будет "Саша" будут сложены (обратите внимание на показательный скриншот ниже).

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

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию. Ну, например, сколько раз имя "Саша" встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой? Формула:

"=СЧЁТЕСЛИ(A2:A7 ;A2 ) " - где:

• A2:A7 - диапазон, в котором будут проверяться и считаться строки;
• A2 - задается условие (обратите внимание, что можно было написать условие вида "Саша", а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Теперь представьте более расширенную задачу: нужно посчитать строки где встречается имя "Саша", и где в столбце И - будет стоять цифра "6". Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже).

Формула будет иметь вид:

=СЧЁТЕСЛИМН(A2:A7 ;A2 ;B2:B7 ;"6") (прим.: обратите внимание на кавычки - они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ;A2 - первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;"6" - второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Как посчитать процент от суммы

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

Самый простой способ, в котором просто невозможно запутаться - это использовать правило "квадрата", или пропорции. Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 - ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал "Саша", нужно найти какую...).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число). В принципе, используя это правило, запутаться в процентах практически невозможно .

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь "пяток" формул) - Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel), и не знаешь как быстрее это сделать...

Excel, в отличие от Word, позволяет создавать практически безграничные по объему таблицы, но все же это не самое большое преимущество электронных таблиц. Главное - это возможность автоматизации вычислений, особенно многократных. Это могут быть расчеты ежемесячные или ежеквартальные, или расчет, в которых нужно "поиграть" исходными данными. Чтобы автоматизировать вычисления, в таблице должны быть формулы. О приемах создания формул в электронных таблицах вы можете узнать из этой статьи.

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

Чтобы вставить формулу для сложения, проделайте несколько действий:

• щелкните по ячейке , где должен будет отображаться результат (на рисунке это ячейка D2);
• введите знак = (это обязательное условие, иначе программа не отличит формулу от всего остального);
• щелкните по первому числу (на рисунке это ячейка А2);
• введите знак + ;
• щелкните по второму числу (на рисунке это ячейка В2);
• нажмите клавишу ENTER (завершение ввода).

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

Адреса ячеек в формулах можно записывать вручную с клавиатуры, но тогда нужно не забывать отслеживать, чтобы был включен английский язык, иначе вместо результата вы рискуете увидеть сообщение #ИМЯ? .

Логичный вопрос, почему бы не составить формулу из чисел, ведь результат одинаковый?

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

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

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

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

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

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

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

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

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

В этом уроке мы рассмотрим «Как сделать формулу в Excel».

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

Формула складывается из трех основных частей:

• Знака все равенства (=);
• Одна или несколько ссылок;
• Знаки математических действий (складывание, умножение и т.д.).

Ссылка на ячейку – это уникальная запись, однозначно идентифицирующая одну ячейку, например A4 или D9. К основным математическим действиям, которые используются в формулах, относят складывание (+), вычитание (-), умножение (*) и деление (/). В таблице приведены все математические операторы, которыми можно пользоваться в формулах.

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

Эта формула содержит три части.

• Знак =. Показывает то, что написанный текст является формулой. Если написать в ячейку A4+C7, то MC Excel будет считать эту запись как обычный тест.

• Ссылки на две ячейки. В нашем случайном примере это A4 и C
• Математический оператор складывания (+).

Чтобы сделать формулу в ячейке Excel, совершите ряд некоторых действий.

1. Щелкните на ячейке, в которой нужно сделать формулу.

Для выделения ячейки также очень часто пользуются кнопками клавиатуры со стрелками.

MC Excel выделит нужную вам ячейку.

1. Введите знак равенства (=).

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

Если вам нужно просуммировать числа, написанные в ячейках A4 и E8, то напишите =A4+E8.

1. Нажмите «Enter ».

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

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

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

MC Excel выделит нужную вам ячейку.

1. Напишите знак равенства (=)

1. Нажимайте на ячейках с нужными данными (к примеру, A4 или E8) и напишите необходимые математические действия.

Чтобы сделать формулу в Excel =A4+E8, выполните нижеперечисленные операции.

а) Введите =.

Тем самым вы показываете MC Excel , что собираетесь создать формулу.

б) Щелкните на ячейке A4.

Microsoft Excel автоматом вставит в формулу ссылку на ячейку A4.

в) Напишите +.

г) Щелкните на ячейке E8.

Microsoft Excel автоматом вставит в формулу ссылку на ячейку E8.

1. Нажимайте клавишу «Enter ».

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

Теперь вы знаете как сделать формулу в Excel. Желаем удачи.

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

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

Рассмотрим работу формул на самом простом примере - сумме двух чисел. Пусть в одной ячейке Excel введено число 2, а в другой 3. Нужно, чтобы в третье ячейке появилась сумма этих чисел.

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

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

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

Рассмотрим формулу для суммы чисел в примере выше:

СУММ(A2;B2)

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

Далее в примере идет функция СУММ, которая означает что необходимо произвести суммирование некоторых данных, а уже в скобках у функции, разделенные точкой с запятой, указываются некоторые аргументы, в данном случае координаты ячеек (A2 и B2), значения которых необходимо сложить и поместить результат в ту ячейку, где написана формула. Если бы Вам требовалось сложить три ячейки, то можно было бы написать три аргумента у функции СУММ, разделяя их точкой с запятой, например:

СУММ(А4;B4;C4)

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

СУММ(B2:B7)

Диапазон ячеек в Экселе указывается с помощью координат первой и последней ячеек, разделенных знаком «двоеточие». В данном примере производится сложение значений ячеек, начиная с ячейки B2 до ячейки B7.

Функции в формулах можно соединять и комбинировать как Вам необходимо для получение требуемого результата. Например, стоит задача сложить три числа и в зависимости от того, меньше ли результат числа 100 или больше, домножить сумму на коэффициент 1.2 или 1.3. Решить задачу поможет следующая формула:

ЕСЛИ(СУММ(А2:С2)

Разберем решение задачи подробнее. Использовалось две функции ЕСЛИ и СУММ. Функция ЕСЛИ всегда имеет три аргумента: первый - условие, второй - действие в случае, если условие верно, третий - действие в случае, если условие неверно. Напоминаем, что аргументы разделяются знаком «точка с запятой».

ЕСЛИ(условие; верно; неверно)

В качестве условия указано, что сумма диапазона ячеек A2:C2 меньше 100. Если при расчете, условие выполнится и сумма ячеек диапазона будет равна, например, 98, то Эксель выполнить действие указанное во втором аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,2. В случае же, если сумма превысит число 100, то выполнится уже действие в третьем аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,3.

Встроенные функции в Excel

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

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

Чтобы вставить функцию в Excel 2007 выберите в главном меню пункт «Формулы» и кликните на значок «Вставить функцию», либо нажмите на клавиатуре комбинацию клавиш Shift+F3.

В Excel 2003 функция вставляется через меню «Вставка»->«Функция». Так же работает и комбинация клавиш Shift+F3.

В ячейке на которой стоял курсор появится знак равенства, а поверх листа отобразится окно «Мастер функций».

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

Пролистывайте список и выделяйте мышью наименование, заинтересовавшей Вас функции. Чуть ниже списка появится ее форма записи, требуемые аргументы и краткое описание, которое разъяснит Вам предназначение функции. Когда найдете то, что нужно, кликните по кнопке «OK» для перехода к указанию аргументов.

В окне аргументов имеются поля с названиями «Число 1», «Число 2» и т.д. Их необходимо заполнить координатами ячеек (либо диапазонами) в которых требуется взять данные. Заполнять можно вручную, но гораздо удобнее нажать в конце поля на значок таблицы для того, чтобы указать исходную ячейку или диапазон.

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

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

Заполнив все аргументы, Вы уже можете предварительно посмотреть результат расчета полученной формулы. Чтобы он появился в ячейке на листе, нажмите кнопку «OK». В рассмотренном примере в ячейку D2 помещено произведение чисел в ячейках B2 и C2.

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

Нравится

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

Ввод формулы начинается всегда со знака «равно» =

Затем Вы пишете свою формулу, с использованием:

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

Вы говорите Экселю, что например, нужно сложить цифры в ячейке А1 и С1, и затем из этой суммы отнять число из ячейки Н1. Для этого Вы пишете в ячейке, в которой Вам нужен результат =A1+C1-H1 и нажимаете enter.

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

Т.е., если говорить о примере выше — поставили знак «=», щелкнули ячейку А1, поставили знак «+», щелкнули ячейку С1, поставили знак «-«, щелкнули ячейку Н1, нажали enter.

Если Вы внесете изменения в таблицу, например, измените число, участвующее в расчетах — формула будет пересчитана!

Частые ошибки при написании формул в Экселе:

• неправильный ввод чисел с дробной частью (в зависимости от версии Эксель разделитель целой и дробной частей может быть либо запятая либо точка! Как правило, в большинстве случаев используется запятая, проверяйте с помощью установки числовых или денежных форматов со знаками после запятой)
• ввод адресов ячеек — ТОЛЬКО латинскими буквами, переключайте язык и набирайте латинские символы — либо просто щелкайте по ячейкам и Эксель проставит правильные адреса сам
• незакрытые скобки
• не видите результат, а только символы «решеточек» — # — результат банально не поместился в ячейку) Просто расширьте столбец и будет Вам счастье:)
• Если появились вопросы или не знаете, откуда взялась ошибка — пишите в комменты, я всегда помогу!

Самый простой пример формулы: сумма двух ячеек (A2 и B2)

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

Формулы в Эксель — Примеры

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

И небольшой видеоурок о том, как просто и быстро создать формулу в Excel