Формулы в 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.

Нравится

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

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

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

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

Простые вычисления

• Выделите любую ячейку, например А2;
• В строчке со знаком Fx (расположена она вверху над таблицей) введите «=1+3» и нажмите «Enter»;
• В выделенной ячейке вы увидите результат (4). В строчке формул останется введенное значение.

Также в excel можно использовать и другие арифметические символы: «-» – для вычитания, «*» – для умножения, «/» – для деления. Попробуйте вводить разные формулы в строчке Fx.

Основные правила ввода:

1. Сначала выполняются умножение и деление, потом сложение и вычитание;
2. Если у нескольких операторов одинаковый приоритет, вычисление начинается слева направо;
3. Значения, указанные в скобках, вычисляются в первую очередь. В формулах они сделаны для удобства: чтобы не было случайной ошибки и было проще читать формулу. Если количество открывающихся скобок не будет совпадать с количеством закрывающихся скобок, появится окно ошибки. Интересная особенность: после вставки закрывающей скобки эксель выделяет ее жирным, чтобы при вводе новой скобки было сразу понятно, какую вставлять.

Формулы с ячейками

В excel вы можете применять формулы с указанием ячеек. Допустим, в ячейке В2 введите число 7, в ячейке В3 – число 20. В ячейке В4 укажите формулу «=В2+В3». В ячейке В4 вы увидите итог сложения в ячейках В2 и В3 – 27. Можно поменять значения в них, тогда автоматически изменится и результат в ячейке В4.

Применение мыши

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

1. Кликните по ячейке В4 левой клавишей мышки и вставьте знак «=»;
2. Выделите ячейку В2 и вставьте знак «+» на клавиатуре;
3. Кликните по ячейке В3 левой клавишей мыши два раза и нажмите «Enter».

Типы ссылок

Всего бывает 3 их разновидности:

• Относительная показывает на положение ячейки по отношению к ячейке с формулой. Обозначается она, как В2, к примеру;
• Абсолютная показывает на ячейку, положение которой не меняется. Обозначается она, как $В$2;
• В смешанной ссылке присутствует сочетание первых двух ссылок. Обозначается она, как $В2, В$2.

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

Относительные ссылки

Укажите в полях таблицы:

• В2 - 10 С2 – 100
• В3 - 70 С3 – 700

В ячейке В4 укажите «=В2+В3», потом «Enter».

Далее установите курсор в правом нижнем углу В4, удержите правую клавишу мыши, протяните на ячейку С4, и отпустите клавишу манипулятора. В открывшемся контекстном меню выберите «Копировать ячейки». Значение из поля В4 скопируется в ячейку С4. Активируйте ячейку С4, вы увидите в ней формулу – «С2+С3». То есть, вроде вы скопировали формулу из поля В4, но значение получилось другое. Этим и отличается данный тип ссылок. Например, если вы возьмете ячейку В4 и скопируете в ячейку А54, например, формула будет получаться сложением ячеек над ней, то есть «А52+А53».

Абсолютные ссылки

Укажите в ячейках:

• В2 - 10 С2 – 100
• В3 - 70 С3 – 700
• Введите в поле D2 значение 10.

В поле В4 укажите формулу «=В2+В3+$D$2». Скопируйте формулу из В4 в С4. Что в итоге получилось? Обычные ссылки изменились, а вот абсолютные не поменялись.

Допустим, чтобы ввести в поле В2 (Лист 1) ссылку на поле В7 (Лист2), выполните следующие шаги:

• Выделите В2 и вставьте знак«=»;
• Кликните по ярлыку «Лист 2»;
• Выделите В7 и нажмите клавишу ввода;
• Снова откроется Лист 1, а в поле В2 появится формула «=Лист2!В7».

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

Текст в формулах

Текстовые значения использовать для формул, если в них есть следующие символы: «0 – 9», «+», «-», «е», «Е», «/». Также можно использовать следующие символы форматирования: «$», «%», «(», «)», «пробел», «";;».

Например, если в ячейке В1 значение "Иван", а в ячейке В2 – "Иванов", а в ячейке В4 будет формула «=В2&В3», то результат получится "ИванИванов".

Знак «&;» – это текстовый оператор, амперсанд, который объединяет текст.

Чтобы вставить пробел, используется «&";; "&», то есть формула будет такой – «=В2&" "&В3».

Амперсанд используется и для объединения чисел с текстом. Например, если в первой ячейке число5, а во второй ячейке – ведер, то в результате получится «5ведер».

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

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

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

Например, на рисунке ниже ячейка А3 содержит формулу, которая складывает значения ячеек А2 и A1 .

Ещё один пример. Ячейка A3 содержит функцию SUM (СУММ), которая вычисляет сумму диапазона A1:A2 .

SUM(A1:A2)
=СУММ(A1:A2)

Ввод формулы

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

Совет: Вместо того, чтобы вручную набирать А1 и А2 , просто кликните по ячейкам A1 и A2 .

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

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

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

Приоритет операций

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

Сперва Excel умножает (A1*A2 ), затем добавляет значение ячейки A3 к этому результату.

Другой пример:

Сначала Excel вычисляет значение в круглых скобках (A2+A3 ), потом умножает полученный результат на величину ячейки A1 .

Копировать/вставить формулу

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

Вставка функции

Все функции имеют одинаковую структуру. Например:

SUM(A1:A4)
СУММ(A1:A4)

Название этой функции – SUM (СУММ). Выражение между скобками (аргументы) означает, что мы задали диапазон A1:A4 в качестве входных данных. Эта функция складывает значения в ячейках A1 , A2 , A3 и A4 . Запомнить, какие функции и аргументы использовать для каждой конкретной задачи не просто. К счастью, в Excel есть команда Insert Function (Вставить функцию).

Чтобы вставить функцию, сделайте следующее:

Примечание: Вместо того, чтобы использовать инструмент «Вставить функцию », просто наберите =СЧЕТЕСЛИ(A1:C2,”>5″). Когда напечатаете ” =СЧЁТЕСЛИ(“, вместо ввода “A1:C2” вручную выделите мышью этот диапазон.

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

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

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

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

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

Трактовка ошибок в программе Excel

• Если результат расчетов не помещается в ячейке или число даты и времени отрицательное, появится символ #####.
• Если используется аргумент формулы, который недопустим, высветится #ЗНАЧ!
• Когда пользователь пытается делить на ноль, программа оповестит его символом #ДЕЛ/0!
• В том случае, если имя некорректно и Excel не может его прочитать, он выдает ошибку #ИМЯ?
• Неопределенные данные обозначаются #Н/Д.
• Если в состав формулы входит ссылка на недопустимую ячейку, символ #ССЫЛКА! оповестит об этом.
• MS Excel имеет определенный диапазон чисел, и в том случае, когда результат вычислений выпадает из этого диапазона, программа выдаст символ #ПУСТО!

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

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

Влияющими называются те ячейки, на которые ссылаются формулы, а зависимые ячейки - это те, которые содержат формулы, ссылающиеся на координаты клеток.

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

Адреса ячеек: абсолютные и относительные

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

• Изменять ссылку на столбец, при этом сохраняя на строки.
• Сохранять ссылки на столбец, но изменять ссылки на строки.
• Не менять ссылки как на строки, так и на столбцы.

Как преобразовать относительную ссылку в абсолютную?

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

Формулы в Excel

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

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

Перед тем как сделать формулу в «Экселе», нужно поставить знак равенства. После этого необходимо указать имя функции. Оно представлено в виде аббревиатуры из заглавных букв, которые определяют значение функции. Далее в скобочках фиксируются аргументы функции, которые используются для получения результата. Аргументом может быть как конкретное число, так и ссылка на клеточку, серия ссылок или диапазон ячеек. Некоторые формулы в «Экселе» в качестве аргумента имеют текст или числа, другие - время и дату.

Мастер функций

В «Эксель» формула суммы имеет простое определение, но не все функции так просты. Некоторые требуют сложного синтаксического написания и аргументов определенных типов. Без ошибок составить такую формулу зачастую очень нелегко. Разработчики программы учли этот нюанс, поэтому в Ecxel есть помощник по составлению - «Мастер функций».

Чтобы с его помощью вводить формулы в «Экселе», необходимо выбрать значок «Вставить функцию». Он расположен с левой стороны от «Строки формул». Затем пользователю нужно подобрать подходящую категорию формулы.

Чуть ниже перечня категорий отображается краткое описание каждой функции, которая будет выделена. Там же можно узнать информацию и по аргументам. Сделав выбор, стоит подтвердить его, нажав ОК. После этого появится окошко «Аргументы функции».

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

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

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

Для удобства пользователей разработана формула «Эксель», проценты по которой не составит особого труда подсчитать.

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

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

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

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