Tw-city.info

IT Новости
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Назначение функции если в excel

Использование оператора «ЕСЛИ» в Microsoft Excel

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

Общее определение и задачи

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь])» .

Пример использования «ЕСЛИ»

Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «ЕСЛИ».

  1. Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
  2. Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».

Так мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ».

Пример функции с несколькими условиями

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

  1. Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
  2. Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))» .
  3. Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».

Как и в прошлый раз, «протягиваем» формулу вниз.

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

В функции «ЕСЛИ» можно также использовать оператор «И», который позволяет считать истинной только выполнение двух или нескольких условий одновременно.

  1. Например, в нашей ситуации премия к 8 марта в размере 1000 рублей выдается только женщинам, которые являются основным персоналом, а мужчины и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной персонал»); «1000»; «0»)» . Вставляем ее в ячейку.

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

Пример использования оператора «ИЛИ»

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

    Итак, предположим, что премия к 8 марта в 1000 рублей положена только женщинам, которые входят в число основного персонала. В этом случае, если работник — мужчина или относится к вспомогательному персоналу, то величина его премии будет равна нулю, а иначе – 1000 рублей. В виде формулы это выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»; C6=»Вспомогательный персонал»); «0»; «1000»)» . Записываем ее в соответствующую ячейку таблицы.

«Протягиваем» результаты вниз.

Как видим, функция «ЕСЛИ» может оказаться для пользователя хорошим помощником при работе с данными в Microsoft Excel. Она позволяет отобразить результаты, соответствующие определенным условиям.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ШКОЛА ПРОГРАММИРОВАНИЯ

Вы здесь

Сообщение об ошибке

Функции Excel.Функция И(), ИЛИ()

На прошлом уроке «Функции Excel. Функция ЕСЛИ» была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях — вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции — И() и ИЛИ().

Функция И()

Функция И() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 И Условие 2. При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.

Функция ИЛИ()

Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 ИЛИ Условие 2. Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.

Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.

Примеры использования функции И и ИЛИ

Все вышесказанное сложно к пониманию и относится к разделу Мат. логики и Дискретной математики. Попробую это все изложить на понятном языке. Разберем несколько примеров. Скажу сразу, все примеры будут с использованием функции ЕСЛИ.

Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В — 52, 55, 88, 4, 25. Столбец С — 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.

Переходим в ячейку D1 и с помощью мастера функций вводим следующее

Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);»Все значения равны»;»Значения не равны») “

Пример 2.

Теперь пример на функцию ИЛИ. Те же столбцы, те же значения, условие следующее: с помощью функций ЕСЛИ и ИЛИ необходимо определить строки, в которых есть совпадающие значения А=В или В=С или А=С.

Мастер функций выглядит так:

Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);»Есть равные значения»;»Нет равных значений»)”

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

И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.

Пример 3.

В таблицу занесены процент выполнения заданий, необходимо проценты перевести в пятибалльную систему следующим образом:

1 – от 0% до 10%
2- от 11% до 30%
3- от 31% до 40%
4- от 41% до 75%
5-от 76% до 100%

Таблица следующего вида:

Составим формулу следующего вида:

=ЕСЛИ(ИЛИ(И(B1>0;B1 0,11;B1 0,31;B1 0,41;B1 0,76;B1

  • Добавить комментарий
  • 139370 просмотров

Функция «ЕСЛИ» в Excel с примерами

Офисный пакет приложений помогает выполнять множество задач в разных областях. Так, MS Excel, являющийся табличным процессором, способен решить разнообразные задачи. Достаточно ввести функцию, и вот он – результат. Как правило, это приложение используют в бухгалтерском учете, но и не только. А во всем многообразии функция «ЕСЛИ» занимает далеко не последнее место.

Общие сведения об электронной таблице MS Excel

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

Документ Excel называют рабочей книгой, которая состоит из листов. Их количество может быть сколько угодно большим. Кстати, таблицы в этом приложении не имеют конца и края, в буквальном смысле. Хотя разработчики утверждают, что таблица состоит из столбцов, поименованных латинским алфавитом (А–IV), и строк, нумерованных арабскими цифрами (1–65536).

Изначально (мы сейчас говорим о MS Excel 2003) в рабочей области представлены 2 панели инструментов: «Стандартная» и «Форматирование». Конечно, пользователь может добавить и другие команды, которые он использует чаще всего. Так, можно организовать панель, из которой будет вызываться функция «ЕСЛИ» посредством диалогового окна.

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

Разновидности функций

В пакетном приложении Excel имеется порядка 400 функций. Их разделили на соответствующие категории (указаны несколько примеров для ознакомления):

  • финансовые: ПРОЦПЛАТ, ПУО, ПС, СТАВКА, ЧПС и т.д.;
  • даты и времени – ВРЕМЯ, ДЕНЬНЕД, ГОД и т.д.;
  • математические – ABS, ATAN, LOG, LN, SIN и т.д.;
  • статистические – ДИСП, ГИПЕРГЕОМЕТ, КВАРТИЛЬ и т.д.;
  • ссылок и массивов – АДРЕС, ВЫБОР, ОБЛАСТИ, ПОИСКПОЗ, ЧСТРОК и т.д.;
  • баз данных – БДДИСП, БСЧЁТА, БДСУММ, ДСРЗНАЧ и т.д.;
  • текстовые – ЗАМЕНИТЬ, НАЙТИ, ПОВТОР, ПОДСТАВИТЬ, ФИКСИРОВАННЫЙ и т.д.;
  • логические – ЕСЛИ, ИСТИНА, НЕ, ЛОЖЬ и т.д.;
  • проверки свойств и значений – ЕОШИБКА, ИНФОРМ, ЕЧИСЛО, ЯЧЕЙКА и т.д.

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

Подробнее о логических функциях

Формулы из данной категории необходимы для сравнения любых типов данных, а именно математических, текстовых, логических. В любой записи условий должен содержаться, как минимум, один знак сравнения: =, >, =, . Именно эти символы определяют отношение между элементами.

Результатом заданного логического уравнения может быть ИСТИНА или ЛОЖЬ. Первый параметр выражается как (1), второй – как (0).

Чаще всего функция «ЕСЛИ» в Excel работает в паре с такими формулами, как «И» и «ИЛИ». Первая используется для проверки нескольких условий и их одновременного выполнения. Если хотя бы один из параметров будет ложным, тогда и все условие «И» будет принимать значение (0). «ИЛИ» принимает истину, когда хотя бы одно из условий таковым является.

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

Формула «ЕСЛИ». Тонкости ввода нескольких условий

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

Первый способ: функция «ЕСЛИ» (формула может быть введена строчными или заглавными буквами) пишется вручную пользователем, скобка открывается, и табличный процессор выводит подсказку, по которой можно ориентироваться.

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

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

Типичные ошибки при вводе формул

При вводе различных формул в ячейку (функция «ЕСЛИ» в Excel не исключение) могут появиться различные ошибки.

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

Несколько примеров

Пример функции «ЕСЛИ» может содержать одно или несколько условий. Сюда могут включаться дополнительные формулы: «И», «ИЛИ», «ЕСЛИ» и т.д.

Задача

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

  • Удержать тем работникам 20%, у которых имеется два вида кредита.

  • Удержать в счет погашения задолженности 10% у тех работников, у которых имеется хотя бы один кредит.

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

Функция ЕСЛИ() в EXCEL

Функция ЕСЛИ() относится к наиболее часто используемым функциям.

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

ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])

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

В качестве аргументов функции, могут участвовать формулы, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше, то по столбцу С .

Вложенные ЕСЛИ

В EXCEL 2007 в качестве значений аргументов значение_если_истина и значение_если_ложь можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ() . =ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний проект»;»Малый проект «)))

Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы:

Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:

Если требуется вывести разный текст в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0, то можно записать следующую формулу:

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

=ПРОСМОТР(A24;<-1E+307;0;1E-307>;A27:A29) (см. файл примера )

Опущен третий аргумент [значение_если_ложь]

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

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;»Бюджет превышен»;»ОК!») или (если в ячейке A1 содержится значение 0) =ЕСЛИ(A1;»Бюджет превышен»;»ОК!») вернут ОК!

Если в ячейке A1 находится любое другое число кроме 0, то формула вернет Бюджет превышен . Такой подход удобен, когда проверяется равенство значения нулю.

Связь функции ЕСЛИ() с другими функциями использующие условия

EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .

Функция ЕСЛИ() , как альтернативный вариант, может быть также использована для подсчета и сложения значений с использованием условий. Ниже приведены иллюстрирующие примеры.

Пусть данные находятся в диапазоне A6:A11 (см. файл примера)

Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;»>10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )

Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;»>10″) . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))

Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))

Логические функции Excel

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

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

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

Будут рассмотрены следующие функции:

Функция ИСТИНА

Не принимает никаких аргументов и просто возвращает логическое значение «ИСТИНА».

Синтаксис: =ИСТИНА()

Функция ЛОЖЬ

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

Синтаксис: =ЛОЖЬ()

Функция И

Возвращает логическое значение ИСТИНА, если все аргументы функции вернули истинное значение. Если хотя бы один аргумент возвращает значение ЛОЖЬ, то вся функция вернет данное значение.

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

Рассмотрим таблицу истинности данной функции:

Синтаксис: =И(Логическое_значение1; [Логическое_значение1];…)

Пример использования:

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

Во втором примере функция никогда не вернет значение ИСТИНА, т.к. условие ее второго аргумента заранее неравно.

Функция ИЛИ

Возвращает логическое значение ИСТИНА, если хотя бы один аргумент функции вернет истинное значение.

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

Таблица истинности функции ИЛИ:

Синтаксис: =ИЛИ(Логическое_значение1; [Логическое_значение2];…)

В качестве примера, рассмотрите примеры функции И, все они вернут результат ИСТИНА, т.к. первый аргумент является истинным.

Функция НЕ

Принимает в виде аргумента всего одно логическое значение и меняет его на противоположное, т.е. значение ИСТИНА она изменит на ЛОЖЬ и наоборот.

Таблица истинности функции И с применением функции НЕ:

Таблица истинности функции ИЛИ с применением функции НЕ:

Синтаксис: =НЕ(логическое_значение)

Функция ЕСЛИ

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

Синтаксис:

Примеры использования функции:

Рассмотрим первый простой пример, чтобы понять, как функция работает.

Умышлено в первый аргумент функции вставить функцию ИСТИНА. В результате проверки, будет возвращен 2 аргумент (значение_если_истина), 3 аргумент будет опущен.

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

Имеются банковские карточки с номерами, начинающимися с первых четырех цифр, которые являются идентификатором вида карты:

Используем нашу функцию для определения типа карты.

Функция, применяемая в данном примере, выглядит так:

=ЕСЛИ( ЛЕВСИМВ(A2;4)=»1111″ ; «Visa» ;ЕСЛИ( ЛЕВСИМВ(A2;4)=»2222″ ; «Master Card» ;»карта не определена»))

Помимо самой рассматриваем функции, в примере используется текстовая функция ЛЕВСИМВ, которая возвращает часть текста из строки, начиная с левого края, в количестве символов, заданном вторым ее аргументом. С ее помощью мы проверяем, являются ли они равными строке «1111», если да, возвращаем результат «Visa», если нет, то выполняем вложенную функцию ЕСЛИ.

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

Функция ЕСЛИОШИБКА

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

Функция принимает 2 аргумента, все они являются обязательными.

Синтаксис: =ЕСЛИОШИБКА(значение;значение_если_ошибка)

Пример использования функции:

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

Читать еще:  Где лежат файлы автосохранения excel
Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector