Использование функции если в excel

Функция «ЕСЛИ» в 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% от заработной платы; если задолженностей нет, вывести строку «Без кредита».

Функция IF (ЕСЛИ) в Excel. Как использовать?

Функция IF (ЕСЛИ) в Excel – это отличный инструмент для проверки условий на ИСТИНУ или ЛОЖЬ. Если значения ваших расчетов равны заданным параметрам функции как ИСТИНА, то она возвращает одно значение, если ЛОЖЬ, то другое.

Что возвращает функция

Заданное вами значение при выполнении двух условий ИСТИНА или ЛОЖЬ.

Синтаксис

=IF(logical_test, [value_if_true], [value_if_false]) – английская версия

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

Аргументы функции

• logical_test (лог_выражение) – это условие, которое вы хотите протестировать. Этот аргумент функции должен быть логичным и определяемым как ЛОЖЬ или ИСТИНА. Аргументом может быть как статичное значение, так и результат функции, вычисления;
• [value_if_true] ([значение_если_истина]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если значение которое вы тестируете соответствует условию ИСТИНА;
• [value_if_false] ([значение_если_ложь]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если условие, которое вы тестируете соответствует условию ЛОЖЬ.

Дополнительная информация

• В функции IF (ЕСЛИ) может быть протестировано 64 условий за один раз;
• Если какой-либо из аргументов функции является массивом – оценивается каждый элемент массива;
• Если вы не укажете условие аргумента FALSE (ЛОЖЬ) value_if_false (значение_если_ложь) в функции, т.е. после аргумента value_if_true (значение_если_истина) есть только запятая (точка с запятой), функция вернет значение “0”, если результат вычисления функции будет равен FALSE (ЛОЖЬ).
  На примере ниже, формула =IF(A1> 20,”Разрешить”) или =ЕСЛИ(A1>20;”Разрешить”) , где value_if_false (значение_если_ложь) не указано, однако аргумент value_if_true (значение_если_истина) по-прежнему следует через запятую. Функция вернет “0” всякий раз, когда проверяемое условие не будет соответствовать условиям TRUE (ИСТИНА).

|

Функция Если в Excel примеры с несколькими условиями

Пример 1. Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)

При использовании функции IF (ЕСЛИ) в Excel, вы можете использовать различные операторы для проверки состояния. Вот список операторов, которые вы можете использовать:

Ниже приведен простой пример использования функции при расчете оценок студентов. Если сумма баллов больше или равна “35”, то формула возвращает “Сдал”, иначе возвращается “Не сдал”.

Пример 2. Использование вложенной функции IF (ЕСЛИ) для проверки условия выражения

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

В приведенном ниже примере мы проверяем два условия.

• Первое условие проверяет, сумму баллов не меньше ли она чем 35 баллов. Если это ИСТИНА, то функция вернет “Не сдал”;
• В случае, если первое условие – ЛОЖЬ, и сумма баллов больше 35, то функция проверяет второе условие. В случае если сумма баллов больше или равна 75. Если это правда, то функция возвращает значение “Отлично”, в других случаях функция возвращает “Сдал”.

Пример 3. Вычисляем сумму комиссии с продаж с помощью функции IF (ЕСЛИ) в Excel

Функция позволяет выполнять вычисления с числами. Хороший пример использования – расчет комиссии продаж для торгового представителя.

В приведенном ниже примере, торговый представитель по продажам:

• не получает комиссионных, если объем продаж меньше 50 тыс;
• получает комиссию в размере 2%, если продажи между 50-100 тыс
• получает 4% комиссионных, если объем продаж превышает 100 тыс.

Рассчитать размер комиссионных для торгового агента можно по следующей формуле:

В формуле, использованной в примере выше, вычисление суммы комиссионных выполняется в самой функции ЕСЛИ . Если объем продаж находится между 50-100K, то формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продажи.

Пример 4. Используем логические операторы (AND/OR) (И/ИЛИ) в функции IF (ЕСЛИ) в Excel

Вы можете использовать логические операторы (AND/OR) (И/ИЛИ) внутри функции для одновременного тестирования нескольких условий.

Например, предположим, что вы должны выбрать студентов для стипендий, основываясь на оценках и посещаемости. В приведенном ниже примере учащийся имеет право на участие только в том случае, если он набрал более 80 баллов и имеет посещаемость более 80%.

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

Формула для этого расчета:

=IF(AND(B2>80,C2>80%),”Да”,”Нет”) – английская версия

=ЕСЛИ(И(B2>80;C2>80%);”Да”;”Нет”) – русская версия

Пример 5. Преобразуем ошибки в значения “0” с помощью функции IF (ЕСЛИ)

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

Формула для преобразования ошибок в ячейках следующая:

=IF(ISERROR(A1),0,A1) – английская версия

=ЕСЛИ(ЕОШИБКА(A1);0;A1) – русская версия

Формула возвращает “0”, в случае если в ячейке есть ошибка, иначе она возвращает значение ячейки.

ПРИМЕЧАНИЕ. Если вы используете Excel 2007 или версии после него, вы также можете использовать функцию IFERROR для этого.

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

=IF(ISBLANK(A1),0,A1) – английская версия

=ЕСЛИ(ЕПУСТО(A1);0;A1) – русская версия

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

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

Коротко о синтаксисе

Функция ЕСЛИ имеет всего три аргумента:

=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)

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

Пример 1

Обратимся к примеру, приведенному на рисунках ниже. В данном примере функция ЕСЛИ в первую очередь проверят условие A1>25. Если это так, то формула возвратит текстовую строку “больше 25”, в любом другом случае – “меньше или равно 25”.

Пример 2

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

В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал или Не сдал. Те, кто набрал более 45 баллов – сдали экзамен, остальные нет.

1. Выделите ячейку, в которую необходимо ввести формулу. В нашем случае это ячейка C3.
2. Введите в нее выражение: =ЕСЛИ(B3>45; “Сдал”; “Не сдал”) и нажмите Enter.
3. Данная формула сравнивает значение в ячейке B3 с числом 45, если значение больше 45, то возвращает строку “Сдал”, иначе “Не сдал”.
4. Скопировав формулу в остальные ячейки таблицы, можно увидеть, что 2 человека из 5 не прошли переаттестацию.

Функция ЕСЛИ и несколько условий

Функции ЕСЛИ можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Плохо. Оценка Отлично ставится при количестве баллов более 60, оценка Хорошо при более 45 и оценка Плохо в остальных случаях.

Чтобы решить эту задачу, введем в ячейку C3 следующую формулу:=ЕСЛИ(B3>60;”Отлично”;ЕСЛИ(B2>45;”Хорошо”;”Плохо”)) и нажмем Enter.

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

Как видите, вместо второго и третьего аргументов функции ЕСЛИ можно подставлять новые функции ЕСЛИ, тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать.

В Excel существуют более благородные инструменты для обработки большого количества условий, например, функция ВПР или ПРОСМОТР.

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

Практическое задание на использование функции ЕСЛИ в EXCEL

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Тема Табличный процессор EXCEL -2003

Практическое занятие № 5

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

1. Логическая функция ЕСЛИ

=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) — принимает одно из двух указанных значений, в зависимости от выполнения условия.

Если логическое выражение верно, то функция принимает первое значение. Если логическое выражение не верно, то функция принимает второе значение.

Пример: Студенты сдали экзамен. Требуется сделать заключение: если оценка 5, 4 или 3, то экзамен сдан, иначе – переэкзаменовка.

Запустить Excel и создать таблицу по образцу.

В ячейку D2 ввести формулу =ЕСЛИ(С2>=3;”Экзамен сдан”;”Переэкзаменовка”)

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

2. Логическая функция И

И(логическое_значение1; логическое_значение2;. ) — принимает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; принимает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Пример: Для студентов, сдававших экзамен, вывести сообщение «Оценка получена», для студентов, проигнорировавших экзамен, вывести сообщение «Не пришел».

Скопировать таблицу на Лист2 и удалить формулы в ячейках D 2: D 11. Изменить оценки и двум студентам вместо оценок ввести «неявка».

В ячейку D2 ввести формулу =ЕСЛИ(И(С2>=2;С2 4000. В ячейку С10 ввести формулу =СУММЕСЛИ(С2:С8;»>4000″).

Из примера видно, что суммироваться будут значения только тех ячеек диапазона С2:С8, которые отвечают условию «>4000». В данном случае диапазон C 2: C 8 и проверяется и обрабатывается.

Рассмотрим использование функции с тремя аргументами.

В ячейку А11 ввести текст Сумма для НР. В ячейку С11 ввести формулу =СУММЕСЛИ(А2:А8; «НР»;С2:С8).

В данном случае производится суммирование ячеек диапазона С2:С8 только в тех случаях, если в соседней ячейке диапазона А2:А8 находится запись «НР». Здесь диапазон А2:А8 проверяется, а диапазон С2:С8 обрабатываются.

Показать результаты преподавателю, сохранить файл с именем Мат-ф-5.

6. Вложенная функция ЕСЛИ

Вставить Лист6. Разместить листы по порядку. Оформить таблицу по образцу.

Предположим, что премия начисляется при следующих условиях:

если средний балл меньше 3, то премия равна 0,

если средний балл больше 3, но меньше 4.5, то премия равна 500р,

если средний балл больше 4.5, то премия равна 800р.

Поместить курсор в ячейку D 2 и вызвать Мастера функций.

На первом шаге в левом окне выбрать категорию Логические. В правом окне выбрать функцию ЕСЛИ.

Щелкнуть по кнопке ОК. Появится окно функции ЕСЛИ.

На втором шаге Мастера функций в поле Логическое_выражение надо ввести условие, во втором поле — значение в случае Истина, а в третьем поле — значение в случае Ложь.

Значение среднего балла находится в ячейке С2. Поэтому надо щелкнуть мышью по кнопке с красной стрелочкой в верхнем поле справа. Диалоговое окно свернется в строку.

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

Теперь с помощью клавиатуры надо дополнить условие С2 1, но 2, но 3, но 4, то премия равна 300.

Получить решение, показать преподавателю и сохранить файл с именем Если-сам.

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

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

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

Коротко о синтаксисе

Функция ЕСЛИ имеет всего три аргумента:

=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)

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

Пример 1

Обратимся к примеру, приведенному на рисунках ниже. В данном примере функция ЕСЛИ в первую очередь проверят условие A1>25. Если это так, то формула возвратит текстовую строку “больше 25”, в любом другом случае – “меньше или равно 25”.

Пример 2

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

В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал или Не сдал. Те, кто набрал более 45 баллов – сдали экзамен, остальные нет.

1. Выделите ячейку, в которую необходимо ввести формулу. В нашем случае это ячейка C3.
2. Введите в нее выражение: =ЕСЛИ(B3>45; “Сдал”; “Не сдал”) и нажмите Enter.
3. Данная формула сравнивает значение в ячейке B3 с числом 45, если значение больше 45, то возвращает строку “Сдал”, иначе “Не сдал”.
4. Скопировав формулу в остальные ячейки таблицы, можно увидеть, что 2 человека из 5 не прошли переаттестацию.

Функция ЕСЛИ и несколько условий

Функции ЕСЛИ можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Плохо. Оценка Отлично ставится при количестве баллов более 60, оценка Хорошо при более 45 и оценка Плохо в остальных случаях.

Чтобы решить эту задачу, введем в ячейку C3 следующую формулу:=ЕСЛИ(B3>60;”Отлично”;ЕСЛИ(B2>45;”Хорошо”;”Плохо”)) и нажмем Enter.

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

Как видите, вместо второго и третьего аргументов функции ЕСЛИ можно подставлять новые функции ЕСЛИ, тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать.

В Excel существуют более благородные инструменты для обработки большого количества условий, например, функция ВПР или ПРОСМОТР.

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