Функции в ms excel примеры. Категории встроенных функций Excel

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

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;” “;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

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

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, <, =, так и поиск конкретного значения для ячейки, которое может быть текстовым. В частности, для поиска слова «Сдал» в конкретных ячейках будет использоваться формула =ИЛИ(D4= “Сдал”; E4= “Сдал”; F4= “Сдал”)

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

Поиск и использование значения

По горизонтали

Используя функцию ГПР мы можем задать поиск по конкретной строке таблицы, а на выходе получить значение из другой ячейки того же столбца (на одну или несколько строк ниже), что соответствует заданному условию. Причем поиск задается либо на точное значение (используется оператор ЛОЖЬ), либо на приблизительное (с оператором ИСТИНА), что позволяет использовать интервалы. Синтаксис =ГПР(искомое_значение; таблица; номер_строки; интервальный_просмотр)

Применение: Для расчета бонуса конкретному сотруднику можно задать интервалы, начиная от которых действует тот или иной процент от прибыли. Скажем, формула =ГПР(E5;$D$1:$G$2;2;ИСТИНА) будет искать в первой строке таблицы из интервала D1:G2 значение, приблизительно схожее со значением из ячейки E5, а результатом формулы станет вывод ячейки со второй строки соответствующего столбца.

По вертикали

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

То есть для формулы =ВПР(E4;$I$3:$J$6;2;ИСТИНА) будет сравниваться значение ячейки E4 с ячейками столбца I из таблицы интервала I3:J6, а значение будет выдаваться из соседней ячейки столбца J.

Выполнение условия ЕСЛИ

При использовании данной функции задается конкретное условие, а следом два результата – один для случаев, если условия выполнено, а другое – наоборот. Скажем для сравнения денежных средств из двух колонок может использоваться такая формула =ЕСЛИ(C2>B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

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

Формула ранжирования

Для значения чисел можно использовать формулу РАНГ, которая выдаст величину каждого числа относительно других в заданном списке. При этом ранжирование может быть как от меньшего значения в сторону увеличения, так и обратно.

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

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

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

Максимум из выбранных значений

Простая, но очень полезная формула МАКС выдает наибольшее значение из списка значений. Сам список может состоять как из ячеек и/или их диапазона, так и вручную введенных чисел. Всего максимальное значение можно искать среди списка из 255 чисел.

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

Минимум из выбранных значений

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

Среднее из выбранных значений

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

Сумма выбранных значений

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

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

Примечание: Подробную информацию об использовании каждой функции Excel можно найти

Функция И() , английский вариант AND(), проверяет на истинность условия и возвращает ИСТИНА если все условия истинны или ЛОЖЬ если хотя бы одно ложно.

Синтаксис функции

И(логическое_значение1; [логическое_значение2]; ...)

логическое_значение - любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

Например, =И(A1>100;A2>100)
Т.е. если в обеих ячейках A1 и A2 содержатся значения больше 100 (т.е. выражение A1>100 - ИСТИНА и выражение A2>100 - ИСТИНА), то формула вернет ИСТИНА, а если хотя бы в одной ячейке значение <=100, то формула вернет ЛОЖЬ .

Другими словами, формула =И(ИСТИНА;ИСТИНА) вернет ИСТИНА,
а формулы =И(ИСТИНА;ЛОЖЬ) или =И(ЛОЖЬ;ИСТИНА) или =И(ЛОЖЬ;ЛОЖЬ) или =И(ЛОЖЬ;ИСТИНА;ИСТИНА) вернут ЛОЖЬ.

Функция воспринимает от 1 до 255 проверяемых условий. Понятно, что 1 значение использовать бессмысленно, для этого есть функция ЕСЛИ() . Чаще всего функцией И() на истинность проверяется 2-5 условий.

Совместное использование с функцией ЕСЛИ()

Сама по себе функция И() имеет ограниченное использование, т.к. она может вернуть только значения ИСТИНА или ЛОЖЬ, чаще всего ее используют вместе с функцией ЕСЛИ() :
=ЕСЛИ(И(A1>100;A2>100);"Бюджет превышен";"В рамках бюджета")

Т.е. если в обеих ячейках A1 и A2 содержатся значения больше 100, то выводится Бюджет превышен , если хотя бы в одной ячейке значение <=100, то В рамках бюджета .

Сравнение с функцией ИЛИ()

Функция ИЛИ() также может вернуть только значения ИСТИНА или ЛОЖЬ, но, в отличие от И() , она возвращает ЛОЖЬ, только если все ее условия ложны. Чтобы сравнить эти функции составим, так называемую таблицу истинности для И() и ИЛИ() .

Эквивалентность функции И() операции умножения *

В математических вычислениях EXCEL интерпретирует значение ЛОЖЬ как 0, а ИСТИНА как 1. В этом легко убедиться записав формулы =ИСТИНА+0 и =ЛОЖЬ+0

Следствием этого является возможность альтернативной записи формулы =И(A1>100;A2>100) в виде =(A1>100)*(A2>100)
Значение второй формулы будет =1 (ИСТИНА), только если оба аргумента истинны, т.е. равны 1. Только произведение 2-х единиц даст 1 (ИСТИНА), что совпадает с определением функции И() .

Эквивалентность функции И() операции умножения * часто используется в формулах с Условием И, например, для того чтобы сложить только те значения, которые больше 5 И меньше 10:
=СУММПРОИЗВ((A1:A10>5)*(A1:A10<10)*(A1:A10))

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

Предположим, что необходимо проверить все значения в диапазоне A6:A9 на превышение некоторого граничного значения, например 100. Можно, конечно записать формулу
=И(A6>100;A7>100;A8>100;A9>100)
но существует более компактная формула, правда которую нужно ввести как (см. файл примера ):
=И(A6:A9>100)
(для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )

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

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

Правила синтаксиса при записи функций

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

После этого вводится имя функции и сразу за ним – список аргументов в круглых скобках. Аргументы отделяются друг от друга точкой с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов.

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

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

Например, в приведенной ниже формуле осуществляется суммирование значений в ячейках В2, В3, В4, В5 и Е7, причем часть ячеек — от В2 до В5, представлены как непрерывный диапазон.

Рассмотрим работу функции ОКРУГЛ(арг1;арг2), которая возвращает число, округленное до заданного количества знаков после запятой, и имеет два аргумента:

арг1 – адрес ячейки с числом (или само число), которое нужно округлить;

арг2 – количество цифр после запятой у числа после округления.
Чтобы округлить число 2,71828, находящееся в ячейке A1, с точностью до одного, двух или трех знаков после запятой и записать результаты вычислений соответственно в ячейки B1, C1 и D1, необходимо действовать следующим образом.

1. Ввести число 2,71828 в ячейку A1.
2. Ввести в ячейки B1, C1 и D1 формулы следующего вида:

ОКРУГЛ(A1;1)

ОКРУГЛ(A1;2)

ОКРУГЛ(A1;3)

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

Например, просуммируем значения ячеек А1 и А2, предварительно округлив эти значения до двух десятичных знаков:

СУММ(ОКРУГЛ(A1;2);ОКРУГЛ(A2;2))

Здесь функция ОКРУГЛ является вложенной аж два раза, но это не страшно, в формулах Excel можно использовать до семи уровней вложенности функций.

Стоит отметить, что в Excel существуют функции, которые не имеют аргументов. Примерами таких функций являются ПИ (возвращает значение числа π, округленное до 15 знаков) или СЕГОДНЯ (возвращает текущую дату). При использовании подобных функций следует в строке формул сразу после названия функции ставить пустые круглые скобки без аргументов . Другими словами, чтобы получить в ячейках значение числа p или текущую дату, следует ввести формулы такого вида:

СЕГОДНЯ()

Типы функций Excel

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

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

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

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

В Excel широко представлены Математические функции и некоторые я уже привел в примерах.

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

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

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

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

Максимум, минимум и средний показатель.
- Процентное соотношение чисел
- Разные критерии, включая критерий Стьюдента
- А так же еще много полезных показателей

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

Применение простых формул в Excel.

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

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

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

Как создать формулу в программе?

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

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

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

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

Комбинации формул.

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

ЕСЛИ(СУММ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

Как можно заметить, в этом примере были использованы две формулы, одна из которых ЕСЛИ, которая сравнивает указанные значения, а вторая СУММ, с которой мы уже знакомы. Формула ЕСЛИ имеет три аргумента: условие, верно, неверно.

Рассмотрим формулу поподробнее опираясь на наш пример. Формула ЕСЛИ получает три аргумента. Первым является условие, которое проверяет меньше сумма диапазона 90 или нет. Если условие верно, то выполняется второй аргумент, а если ложно, то будет выполнен третий аргумент. То есть, если мы введем значение в ячейки, сумма которых будет меньше 90, то выполнится умножение этой суммы на коэффициент 1,4, а если их сумма будет больше или равна 90, то тогда произойдет умножение на коэффициент 1,5.

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

Базовые функции Excel

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

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

Или же можно нажать комбинацию клавиш Shift+F3. Эта кнопка (или комбинация клавиш на клавиатуре) позволяет ускорить процесс написания формул. Вам необязательно вводить все вручную, ведь при нажатии на эту кнопку в выбранной в данный момент ячейке будет добавлена та формула с аргументами, которые вы выберете в списке. Можно производить поиск по этому списку, используя начало формулы, или выбрать категорию, в которой нужная вам формула будет находиться.

К примеру, функция СУММЕСЛИМН находится в категории математических функций.

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

Функция ВПР

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

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

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

Это происходит из-за того, что данная функция имеет еще и четвертый аргумент, который имеет только два значения, ИСТИНА или ЛОЖЬ, а так как он у нас не задан, то по умолчанию он стал в позицию ИСТИНА.

Округление чисел, используя стандартные функции

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

Чтобы округлить значение ячейки в большую сторону вам понадобится формула "ОКРУГЛВВЕРХ". Стоит обратить внимание на то, что формула принимает не один аргумент, что было бы логичным, а два, и второй аргумент должен быть равен нулю.

На рисунке хорошо видно, как произошло округление в большую сторону. Следовательно, информацию в ячейке А1 можно менять как и во всех других случаях, потому что она является переменной, и используется в качестве значения аргумента. Но если вам необходимо округлить число не в большую, а меньшую сторону, то эта формула не поможет вам. В данном случае вам необходима формула ОКРУГЛВНИЗ. Округление произойдет к ближайшему целому числу, которое меньше нынешнего дробного значения. То есть, если в примере на картинке задать вместо формулы ОКРУГЛВВЕРХ формулу ОКРУГЛВНИЗ, то результат будет уже не 77, а 76.

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