Light-electric.com

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

Если то в excel

Функция ЕСЛИ в 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.

  • Данная формула обрабатывает сразу два условия. Сначала проверяется первое условие: B3>60. Если оно истинно, то формула возвращает значение “Отлично”, а остальные условия не обрабатываются. Если первое условие ложно, то функция ЕСЛИ переходит ко второму: B2>45. Если второе условие истинно, то формула возвращает значение “Хорошо”, а если ложно, то “Плохо”.
  • Скопировав формулу в остальные ячейки таблицы, можно увидеть, что на отлично сдал один человек, а на оценки хорошо и плохо по два человека.
  • Как видите, вместо второго и третьего аргументов функции ЕСЛИ можно подставлять новые функции ЕСЛИ, тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать.

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

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

    Функция 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 как пользоваться

    |

  • Если вы не укажете условие аргумента TRUE(ИСТИНА) (value_if_true (значение_если_истина)) в функции, т.е. условие указано только для аргумента value_if_false (значение_если_ложь), то формула вернет значение “0”, если результат вычисления функции будет равен TRUE (ИСТИНА);
    На примере ниже формула равна = IF (A1>20;«Отказать») или =ЕСЛИ(A1>20;”Отказать”) , где аргумент 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, а также какие задачи мы можем решить с ее помощью.

    Что делает функция ЕСЛИ?

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

    При этом аргумент функции должен быть вопросом, на который возможно 2 варианта ответа: «да» и «нет», «истина» или «ложь».

    Вот как может выглядеть это дерево решений.

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

    Синтаксис функции ЕСЛИ

    Вот как выглядит синтаксис этой функции и её аргументы:

    Читать еще:  Как заменить знак в excel

    =ЕСЛИ(логическое выражение, значение если «да», значение если «нет»)

    Логическое выражение — (обязательное) условие, которое возвращает значение «истина» или «ложь» («да» или «нет»);

    Значение если «да» — (обязательное) действие, которое выполняется в случае положительного ответа;

    Значение если «нет» — (обязательное) действие, которое выполняется в случае отрицательного ответа;

    Давайте вместе подробнее рассмотрим эти аргументы.

    Первый аргумент – это логический вопрос. И ответ этот может быть только «да» или «нет», «истина» или «ложь».

    Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “ =”, “ ”. Давайте попробуем задать такой вопрос вместе.

    Простейший пример применения.

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

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

    В этом нам поможет функция ЕСЛИ. Добавим в таблицу данных столбец “Страна”. Регион “Запад” – это местные продажи («Местные»), а остальные регионы – это продажи за рубеж («Экспорт»).

    Как правильно записать?

    Устанавливаем курсор в ячейку G2 и вводим знак “=”. Для Excel это означает, что сейчас будет введена формула. Поэтому как только далее будет нажата буква “е”, мы получим предложение выбрать функцию, начинающуюся этой буквы. Выбираем “ЕСЛИ”.

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

    В качестве первого аргумента записываем: С2=”Запад”. Как и в других функциях Excel, адрес ячейки можно не вводить вручную, а просто кликнуть на ней мышкой. Затем ставим “,” и указываем второй аргумент.

    Второй аргумент – это значение, которое примет ячейка G2, если записанное нами условие будет выполнено. Это будет слово “Местные”.

    После этого снова через запятую указываем значение третьего аргумента. Это значение примет ячейка G2, если условие не будет выполнено: “Экспорт”. Не забываем закончить ввод формулы, закрыв скобку и затем нажав “Enter”.

    Наша функция выглядит следующим образом:

    Наша ячейка G2 приняла значение «Местные».

    Теперь нашу функцию можно скопировать во все остальные ячейки столбца G.

    А если один из параметров не заполнен?

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

    Что будет в результате?

    Насколько это красиво и удобно – судить вам. Думаю, лучше все же использовать оба аргумента.

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

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

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

    Более того, если вам действительно нужно только проверить какое-то условие и получить «Истина» или «Ложь» («Да» или «Нет»), то вы можете использовать следующую конструкцию –

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

    Рассмотрим, как ещё можно использовать функцию ЕСЛИ.

    Использование функции ЕСЛИ с числами.

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

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

    К примеру, мы предоставляем нашему покупателю скидку в зависимости от суммы покупки. Если сумма больше 100, то он получает скидку 10%.

    Назовём столбец Н “Скидка” и в ячейку H2 введём функцию ЕСЛИ, вторым аргументом которой будет формула расчёта скидки.

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

    Функция ЕСЛИ

    В этом курсе:

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

    Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

    Например, функция =ЕСЛИ(C2=»Да»;1;2) означает следующее: ЕСЛИ(С2=»Да», то вернуть 1, в противном случае вернуть 2).

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

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

    Условие, которое нужно проверить.

    Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

    Читать еще:  Как работает подбор параметра в excel

    Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

    Простые примеры функции ЕСЛИ

      В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)

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

        B2;»Превышение бюджета»,»В пределах бюджета»)» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

          =ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)

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

          B2;C2-B2;»»)» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

            На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение «Фактические» больше значения «Плановые», то вычесть сумму «Плановые» из суммы «Фактические», в противном случае ничего не возвращать).

              В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = «Да», то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)

              Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: «Текст»). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.

              Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

              Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

              Функция ЕСЛИ в Excel

              Функция имеет следующий синтаксис.

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

              лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

              Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

              В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

              Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

              Формула ЕСЛИ в Excel – примеры нескольких условий

              Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

              Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

              Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

              Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.

              Ссылка на основную публикацию
              Adblock
              detector