Формулы excel и их использование. Самостоятельное создание формул

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


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

Для самого простого примера, подсчитаем сумму двух значений. Для этого в ячейке А1 запишем цифру 5, а в В1 число 3. Чтобы сумма вычислялась автоматически, запишем следующую комбинацию символов в ячейку С1, которая и будет являться формулой: =СУММ(А1;В1).


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

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

Для того, чтобы усложнить задачу, введем в ячейки А1 и В1 дробные числа, которые подсчитать гораздо сложнее. В результате в С1 будет произведен пересчет и отобразится правильное значение.


В Excel можно делать не только сложение, но и иные арифметические действия: вычитание, умножение, деление. Запись формулы должна всегда начинаться с символа «=» и указанием так называемых координат ячеек (в рассматриваемом примере это А1 и В1).

Самостоятельное создание формул

В вышеуказанном примере формула =СУММ(А1;В1) позволяет выполнить сложение двух значений. Символ «=» понимается, как начало формулы, СУММ – служебное слово, которое и обозначает подсчет суммы, А1 и В1 – координаты статических значений, которые и выступают в качестве слагаемых, причем они должны быть обязательно разделены точкой с запятой «;».

Рассмотрим пример сложения трех ячеек А1, В1, С1. Для этого записывается практически идентичная формула, но в скобках дополнительно указывается третья ячейка. В итоге, необходимо прописать в ячейку D1 следующую комбинацию =СУММ(А1;В1;С1), чтобы выполнить подсчет.


Если слагаемых, которые идут в одной строке (столбце), довольно много, например 10, формулу можно упростить, указав диапазон координат. В итоге формула будет выглядеть следующим образом: =СУММ(А1:А10). В качестве ячейки для вывода искомого результата, выберем ячейку В6. В итоге у нас получится результат, показанный на скриншоте.


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


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

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

Электронные таблицы способны выполнять не только простые арифметические вычисления, но и делать сложные математические расчеты.

Например, произведем расчет суммы значений и умножим его на коэффициент 1,4, если при сложении значение было меньше 90. Если результат будет больше 90, тогда следует умножить на 1,5. В этом случае формула содержит уже несколько операторов и выглядит гораздо сложнее: =ЕСЛИ(СУММ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).


Здесь уже было использовано две функции вместо одной: ЕСЛИ и СУММ. Первая команда – это условный оператор, который работает следующим образом:

  • если сумма трех значений (А1:С1) больше, чем 90, выполняется умножение на 1,4, то есть на первый коэффициент, который указан после условия;
  • если же (А1:С1) меньше, чем 90 (как в нашем приведенном примере), то есть условие не выполнено, происходит перемножение этого значения на второй коэффициент.

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

Встроенные функции в электронных таблицах

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

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


Абсолютно все функции поделены на категории, чтобы упростить поиск нужной.

Функция ВПР

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

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


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

В итоге получается следующий результат.


Округление значений

В Excel можно производить точно округление для упрощения вычислений. Для этого существует специальная функция «ОКРУГЛВВЕРХ». Например, если нам нужно округлить число, указанное в ячейке А1, введем в соседней формулу = ОКРУГЛВВЕРХ(А1;0), где второй аргумент показывает до какого порядка десятичных знаков нудно произвести округление (при указании нуля, итоговое значение будет целым числом). В итоге получается следующий результат.

Как видно из скриншота, произошло округление в большую сторону. Для того, чтобы выполнить в меньшую, существует функция «ОКРУГЛВНИЗ».

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

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

Откройте программу Эксель. Перед вами будет рабочая область, удостоверьтесь, что вкладка «Главная» открыта. В пустой строке, расположенной между шапкой программы и её рабочей областью, вы увидите поле для ввода формулы, она вам понадобится после ввода аргументов, то есть, чисел, которые нужно посчитать.


Например, у вас есть столбец с определёнными числами. В данном варианте — это 15 и 20.


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


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


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


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


Кликните мышкой по первой ячейке.


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


Нажмите клавишу Enter. В ячейке появится сумма двух чисел.


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


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

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

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

В приведенном ниже примере, мы продемонстрируем, каким образом Excel вычисляет сложные формулы, опираясь на порядок выполнения операций. В данном примере мы хотим вычислить величину налога с продаж за услуги по питанию. Чтобы это осуществить, запишем следующее выражение в ячейке D4: =(D2+D3)*0,075 . Эта формула сложит стоимость всех позиций счета, а затем умножит на размер налога с продаж 7,5% (записанный как 0,075).

Excel придерживается порядка действий и сначала складывает значения в скобках: (44.85+39.90)=$84.75 . Затем умножает это число на налоговую ставку: $84.75*0.075 . Результат вычислений показывает, что налог с продаж составит $6.36 .


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


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

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



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


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

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

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

Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.

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

Части формулы Excel

Формула также может содержать один или несколько таких элементов, как функции , ссылки , операторы и константы .

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

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

Использование функций и вложенных функций в формулах Excel

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



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

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

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

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

Пример 1

Пример 2

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. Кроме того, вы можете настроить ширину столбцов в соответствии с содержащимися в них данными.

Примечание: В формулах в столбцах C и D определенное имя "Продажи" заменяется ссылкой на диапазон A9:A13, а имя "ИнформацияОПродажах" заменяется диапазоном A9:B13. Если же в книге не будет этих имен, формулы в D2:D3 вернут ошибку #ИМЯ?.

Тип примера

Пример, в котором не используются имена

Пример, в котором используются имена

Формула и результат с использованием имен

"=СУММ(A9:A13)

"=СУММ(Продажи)

СУММ(Продажи)

"=ТЕКСТ(ВПР(МАКС(A9:13),A9:B13,2,ЛОЖЬ),"dd/mm/yyyy")

"=ТЕКСТ(ВПР(МАКС(Продажи),ИнформацияОПродажах,2,ЛОЖЬ),"дд.мм.гггг")

ТЕКСТ(ВПР(МАКС(Продажи),ИнформацияОПродажах,2,ЛОЖЬ),"дд.мм.гггг")

Дата продажи

Дополнительные сведения см. в статье Определение и использование имен в формулах .

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

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

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

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

Примечание: При вводе формулы массива Excel автоматически заключает ее в фигурные скобки { и }. При попытке вручную ввести фигурные скобки Excel отобразит формулу как текст.

Использование констант массива

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

Константы массива могут содержать числа, текст, логические значения, например ИСТИНА или ЛОЖЬ, либо значения ошибок, такие как #Н/Д. В одной константе массива могут присутствовать значения различных типов, например {1,3,4;ИСТИНА,ЛОЖЬ,ИСТИНА}. Числа в константах массива могут быть целыми, десятичными или иметь экспоненциальный формат. Текст должен быть заключен в двойные кавычки, например "Вторник".

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

    Константы заключены в фигурные скобки ({ } ).

    Столбцы разделены запятыми (, ). Например, чтобы представить значения 10, 20, 30 и 40, введите {10,20,30,40}. Эта константа массива является матрицей размерности 1 на 4 и соответствует ссылке на одну строку и четыре столбца.

    Значения ячеек из разных строк разделены точками с запятой (; ). Например, чтобы представить значения 10, 20, 30, 40 и 50, 60, 70, 80, находящиеся в расположенных друг под другом ячейках, можно создать константу массива с размерностью 2 на 4: {10,20,30,40;50,60,70,80}.

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

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

С помощью двоеточия (: ) разделяются ссылки на первую и последнюю ячейки в диапазоне. Например: A1:A5 .

Указаны обязательные аргументы

У функции могут быть обязательные и необязательные аргументы (последние обозначаются в синтаксисе квадратными скобками). Необходимо ввести все обязательные аргументы. Старайтесь также не вводить слишком много аргументов.

В формуле не больше 64 уровней вложенности функций

Уровней вложенности не может быть более 64.

Имена книг и листов заключены в одинарные кавычки

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

Указан путь к внешним книгам

Числа введены без форматирования

При вводе чисел в формулу нельзя указывать знаки долларов, так как они используются для обозначения абсолютных ссылок. Например, вместо значения $1000 нужно ввести 1000 .

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM.