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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формулы в Excel

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

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

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

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

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

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

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

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

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

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

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

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

Выделим произвольную ячейку, например А1. В строке формул введем =2+3 и нажмем Enter. В ячейке появится результат (5). А в строке формул останется сама формула.

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

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

Мой вам совет - ИСПОЛЬЗУЙТЕ СКОБКИ. В этом случае вы обезопасите себя от случайной ошибки в вычислениях с одной стороны, а с другой - скобки значительно облегчают чтение и анализ формул. Если в формуле количество закрывающих и открывающих скобок не совпадает, Excel выдаст сообщение об ошибке и предложит вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок.

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

Введите в ячейку А1 число 10, а в ячейку А2 - число 15. В ячейке А3 введите формулу =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 - 25. Поменяйте значения ячеек А1 и А2 (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3 (согласно формулы).

Дабы не ошибиться при вводе адресов ячеек, можно использовать при вводе ссылок мышь. В нашем случае надо проделать следующее:

Выделить ячейку А3 и ввести в строке формул знак равенства.
Щелкнуть на ячейке А1 и ввести знак плюс.
Щелкнуть на ячейке А2 и нажать Enter.

Результат будет аналогичным.

Относительные, абсолютные и смешанные ссылки

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

А1 - 20 В1 - 200
А2 - 30 В2 - 300

В ячейку А3 введите формулу =А1+А2 и нажмите Enter.

Теперь установите курсор на правый нижний угол ячейки А3, нажмите правую кнопку мыши и протяните на ячейку В3 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".

После этого значение формулы из ячейки А3 будет скопировано в ячейку В3. Активизируйте ячейку В3 и посмотрите какая формула получилась - В1+В2. Почему так получилось? Когда мы записали в ячейке А3 формулу А1+А2, Excel интерпретировал эту запись так: "Взять значения из ячейки, расположенной в текущем столбце на две строки выше и сложить со значением ячейки, расположенной в текущем столбце на одну строку выше". Т.е. скопировав формулу из ячейки А3, например, в ячейку С43, мы получим - С41+С42. Вот в этом прелесть относительных ссылок, формула как бы сама подстраивается под наши задачи.

Введите в ячейки следующие значения:

А1 - 20 В1 - 200
А2 - 30 В2 - 300
Введите в ячейку С1 число 5.

В ячейку А3 введите следующую формулу =А1+А2+$C$1. Аналогичным образом скопируйте формулу из А3 в В3. Посмотрите что получилось. Относительные ссылки "подстроились" под новые значения, а вот абсолютная - осталась неизменной.

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

Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А5 (Лист2), надо проделать следующее:

выделить ячейку А1 и ввести знак равенства;
щелкните на ярлыке "Лист 2";
щелкните на ячейке А5 и нажмите клавишу ввода;
после этого будет активизирован опять Лист 1 и в ячейке А1 появится следующая формула =Лист2!А5.

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

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

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

Цифры от 0 до 9 , + - е Е /

Еще можно использовать пять символов числового форматирования:

$ % () пробел

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

Неправильно: =$55+$33

Правильно: ="$55"+$"33"

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

Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение "Иван", а ячейка А2 - "Петров", то введя в ячейку А3 следующую формулу =А1&А2 , получим "ИванПетров".

Для вставки пробела между именем и фамилией надо написать так =А1&" "&А2 .

Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст "мешков", то в результате действия формулы =А1&А2 , мы получим "10мешков". Причем результатом такого объединения будет текстовое значение.

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

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

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

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

Ввод формулы

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

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

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

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

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” вручную выделите мышью этот диапазон.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:

Обозначение Значение

Сложение
- Вычитание
/ Деление
* Умножение

Если вам необходимо указать число, а не адрес ячейки – вводите его с клавиатуры. Чтобы указать отрицательный знак в формуле Excel, нажмите «-».

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

Ввод их всегда осуществляется после нажатия на «=». Но что делать, если однотипных расчетов много? В таком случае можно указать одну, а затем ее просто скопировать. Для этого следует ввести формулу, а затем «растянуть» ее в нужном направлении, чтобы размножить.
Установите указатель на копируемую ячейку и наведите указатель мыши на правый нижний угол (на квадратик). Он должен принять вид простого крестика с равными сторонами.

Нажмите левую кнопку и тяните.

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

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

При копировании таким образом номера строки увеличиваются, если сдвиг происходит вниз, или увеличиваются номера столбцов – если вправо. Это называется относительная адресация.
Давайте введем в таблицу значение НДС и посчитаем цену с налогом.

Цена с НДС высчитывается как цена*(1+НДС). Введем последовательность в первую ячейку.

Попробуем скопировать запись.

Результат получился странный.

Проверим содержимое во второй ячейке.

Как видим, при копировании сместилась не только цена, но и НДС. А нам необходимо, чтобы эта ячейка оставалась фиксированной. Закрепим ее с помощью абсолютной ссылки. Для этого переведите указатель на первую ячейку и щелкните в строке формул на адрес B2.

Нажмите F4. Адрес будет разбавлен знаком «$». Это и есть признак абсолютно ячейки.

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

Использование функций для вычислений

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

Расскажем о некоторых функциях.

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

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

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

Как и при вставке любой функции, откроется окно для заполнения аргументов.

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

Во второй строке напишем «Закупить». Эта надпись будет появляться для тех товаров, которые были распроданы. Последнюю строку можно оставить пустой, так как у нас нет действий, если условие ложно.

Нажмите ОК и скопируйте запись для всего столбца.

Чтобы в ячейке не выводилось «ЛОЖЬ» снова откроем функцию и исправим ее. Поставьте указатель на первую ячейку и нажмите Fx около строки формул. Вставьте курсор на третью строку и поставьте пробел в кавычках.

Затем ОК и снова скопируйте.

Теперь мы видим, какой товар следует закупить.

Формула текст в Excel

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

В первую ячейку введем функцию (кнопка «Текстовые» в разделе «Формулы»).

В окне аргументов укажем ссылку на ячейку итоговой суммы и установим формат «#руб.».

Нажмем ОК и скопируем.

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

«ЗНАЧ» обозначает, что вычисления не могут быть произведены.
Примеры форматов вы можете видеть на скриншоте.

Формула даты в Excel

Excel предоставляет много возможностей по работе с датами. Одна из них, ДАТА, позволяет построить дату из трех чисел. Это удобно, если вы имеете три разных столбца – день, месяц, год.

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

Расставьте адреса ячеек соответствующим образом и нажмите ОК.

Скопируйте запись.

Автосумма в Excel

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

Перейдите на панель «Формулы» и нажмите «Автосумма».

Excel автоматически выделит ближайший числовой диапазон.

Вы можете выделить другой диапазон. В данном примере Excel все сделал правильно. Нажмите ОК. Обратите внимание на содержимое ячейки. Функция СУММ подставилась автоматически.

При вставке диапазона указывается адрес первой ячейки, двоеточие и адрес последней ячейки. «:» означает «Взять все ячейки между первой и последней. Если вам надо перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5;F8;F11)

Работа в Excel с формулами: пример

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

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

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

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