Функции и Формулы

Функции Google таблиц IF (ЕСЛИ) и IFS (ЕСЛИМН)

Функции Google таблиц IF (ЕСЛИ) и IFS (ЕСЛИМН)

Функция IF (ЕСЛИ) в Google таблицах — на практике одна из самых востребованных. В зависимости от условий она возвращает указанный результат (значение или другую формулу со своими параметрами).

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

Функция IF (ЕСЛИ) в Google таблицах

Логическая функция имеет следующий синтаксис:

 =IF(логическое_выражение; значение_если_истина; [значение_если_ложь])
  • Логическое_выражение – это искомое проверяемое условие. Например: B2<100. Если значение в ячейке B2 действительно меньше 100, то в памяти гугл таблиц генерируется ответ «ИСТИНА» и функция возвращает то, что указано следующим параметром. Если это не так, в памяти генерируется ответ «ЛОЖЬ» и возвращается значение из последнего указанного нами параметра.
  • Значение_если_истина – значение или любая другая формула, которая отображается при наступлении указанного в первом параметре события.
  • [Значение_если_ложь] – это альтернативное значение или любая другая формула, которая отображается при невыполнении нашего условия (первый проверяемый параметр). Данный параметр не обязателен к заполнению. В этом случае при наступлении альтернативного события функция вернет значение «ЛОЖЬ». Для удобства все необязательные к заполнению параметры в формулах google таблиц помечаются в квадратными скобками «[…]».

Примеры функции IF (ЕСЛИ) в Google таблицах

Функция IF (ЕСЛИ) практически всегда используется в связке с другими функциями. Чаще всего она является составным элементом в сложной аналитической структуре. Но ее можно использовать и в простейших вариантах.

Простой пример функции IF (ЕСЛИ)

Рассмотрим очень простой пример проверки продаж отдельных продуктов. По условию, нам нужно пометить, какие продукты хорошо продаются, а какие нет. Критерий определения — больше 30 кг в день. Если в день мы продаем больше 30 кг — Хорошо, если меньше — Плохо.

В таком случае синтаксис формулы будет выглядеть так:

 =IF(логическое_выражение; значение_если_истина; [значение_если_ложь])
 =IF(B2>30;"Хорошо";"Плохо")

Все текстовые выражения помещаются в кавычки (слова, числа в виде текста и т.д.).

Пошагово: IF (ЕСЛИ) мы продали моркови больше 30 кг за 1 день, то выводится значение_если_истина — «Хорошо» (да, мы продали 35 кг > 30 кг), если меньше — «Плохо» ([значение_если_ложь] — альтернативное значение, если условие не соблюдается).

Более сложный пример функции IF (ЕСЛИ)

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

 =C2-B2*7

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

 =IF(логическое_выражение; значение_если_истина; [значение_если_ложь])
 =IF(C2-B2*7<0;0;C2-B2*7)

Таким образом мы получили корректный результат и в прогнозе нет больше отрицательных значений.

Функция IF (ЕСЛИ) в Google таблицах - несколько условий

Чаще всего на практике требуется применять формулу где условий более чем 2 — 3, 4, 5… В таком случае можно использовать функцию, но уже вкладывая одну в одну, как матрешку, указывая все условия по очереди.

Рассмотрим пример многомерной функции IF (ЕСЛИ) на живом примере начисления премии по KPI продаж. Система начислений следующая:

  1. Если план выполнен менее чем на 90% — премия не начисляется.
  2. 90% — 95% — премия 10%.
  3. 95% — 100% — премия 20%.
  4. Если план перевыполнен — премия 30%.

Фактически у нас 4 конкретных условия, которые могут быть указаны по следующей логической структуре:

  • если выполняется первое условие, то выводится первый вариант, в противном случае переход ко второму условию;
  • если выполняется второе условие, то выводится второй вариант, в противном случае переход к третьему условию;
  • если выполняется третье условие, то выводится третий вариант, в противном случае переход к четвертому условию;
  • если выполняется четвертое условие, то выводится четвертый вариант, а в качестве завершения логической структуры указывается [Значение_если_ложь].

Максимальное количество таких условий = 256. В конце формулы указывается [Значение_если_ложь], для которого не выполняется ни одно из перечисленных ранее условий. В итоге формула имеет следующий синтаксический вид:

 =IF(логическое_выражение; значение_если_истина; IF(логическое_выражение; значение_если_истина; IF(логическое_выражение; значение_если_истина; [Значение_если_ложь])))
 =IF(B2<0,9;0;IF(B2<0,95;0,1;IF(B2<1;0,2;0,3)))

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

При создании формульной логической цепочки очень легко запутаться. Новичкам рекомендуется либо абстрактно принимать переменные (условия) за «Х», «Y», «Z», …, либо смотреть на всплывающие подсказки. Плюсом, система сама подсвечивает разные переменные и условия, а так же, доставляет закрывающие скобки формулы функций.

Функция IFS (ЕСЛИМН) в Google таблицах

В более поздних версиях Excel и Google таблицах (с 2016 года) появилась функция IFS (ЕСЛИМН). Она фиксирует сразу множество условий, что значительно оптимизирует серверную нагрузку (формулы считаются быстрее и наши таблицы не тормозят так, как это было бы, если бы сервер просчитывал каждое вложенное условие IF (ЕСЛИ)) и разгружает наше абстрактное мышление, упраздняя повторяющиеся простые условия IF (ЕСЛИ). По сути, это та же функция ЕСЛИ, только в разы упрощенная, к тому же, максимальное число условий увеличилось до 512.

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

Первый вариант

 =IFS(B2<0,9;0;B2<0,95;0,1;B2<1;0,2;B2>=1;0,3)

Второй вариант

 =IFS(B2<0,9;0;B2<0,95;0,1;B2<1;0,2;TRUE;0,3)

Формулы функции IFS (ЕСЛИМН) выполняют одно и то же действие, отличаясь завершающим последним обязательным (в отличии от обычной функции IF (ЕСЛИ)) аргументом. В первом варианте мы конкретно указали какое значение у аргумента должно выводиться, если выполняется 4 условие функции. Можно избежать конкретного действия, прописав значение «TRUE» (ИСТИНА), тогда будет выводиться последнее альтернативное значение 4 условия.

P.S.: первый вариант мне нравится больше.

Вы познакомились с функциями IF (ЕСЛИ) и IFS (ЕСЛИМН), увидели где их можно применить на практике, и, я уверен, сможете применить их в своей работе. Поделитесь, пожалуйста в комментариях своими идеями или случаями применения рассматриваемых функций.

Статьи по теме

Комментарии к статье: “Функции Google таблиц IF (ЕСЛИ) и IFS (ЕСЛИМН)

  1. Ирина:

    Скажите, долгое время уже бьюсь на формулой, которая кажется простой.
    Дано — строка с 6 ячейками. в первой ячейке нужна формула, которая будет считать сумму ячеек справа. Но не так все просто)
    у каждой ячейки свое значение по числу. Оно может быть от 1 до 5. Все зависит от того, заполнена она или нет текстом. Если есть текст, то ячейка будет считаться как 1 или 3 или 5. Но в первой ячейке нужна сумма этих ячеек.

    1. Если по-простому:
      1. Создать вспомогательные столбцы, которые можно потом скрыть, с привязкой ячейка к ячейке, т.е., у Вас 5 столбцов, где:
      1я ячейка из столбца = 1; 2я = 2; … 5я = 5. Я так понял, это какой-то рейтинг или отметки.
      2. Во вспомогательных столбцах прописать формулу, ссылающуюся на свою ячейку: =IF(A2<>«»;1;»»). Где А2 — это первая ячейка, в которой будет проверяться условие на заполнение этой ячейки, а единица в формуле — это значение ячейки (от 1 до 5). И по такому аналогу сделать все ячейки во вспомогательном столбце.
      Ячейка F2( =IF(A2<>«»;1;»») ); Ячейка G2( =IF(B2<>«»;2;»») ); Ячейка H2( =IF(C2<>«»;3;»») ); Ячейка I2( =IF(D2<>«»;4;»») ); Ячейка J2( =IF(E2<>«»;5;»») )
      3. В первую ячейку вывести сумму формулой: Ячейка A1( =SUM(F2:J2) )

      По-сложному:
      В ячейку A1 прописываем комбинацию формул:
      =SUM(IF(ISBLANK(B1)=FALSE;1;0);IF(ISBLANK(C1)=FALSE;2;0);IF(ISBLANK(D1)=FALSE;3;0);IF(ISBLANK(E1)=FALSE;4;0);IF(ISBLANK(F1)=FALSE;5;0))

      Где B1;C1;D1;E1;F1 — ячейки в которых будет текст, и, соот-но: B1 = 1; C1 = 2; D1 = 3; E1 = 4; F1 = 5

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *