Рассмотрим одну из самых часто используемых функций Excel - функцию ЕСЛИ. На простых примерах разберем, как применять функцию ЕСЛИ при составлении формул, как понять, что в формулу нужно вставить несколько условий (то есть использовать вторую вложенную функцию ЕСЛИ) и как можно объединить функцию ЕСЛИ с другими логическими функциями И и ИЛИ.
Когда использовать функцию ЕСЛИ
Функция ЕСЛИ позволяет задать условие, которому должно соответствовать проверяемое значение. Далее, в зависимости от того выполняется ли условие или нет, функция пойдет по одному из заданных путей.
Когда при постановке задачи вы употребляете слово «если», то это первый звоночек, что скорее всего нужно будет применить одноименную функцию.
Например, есть перечень, в котором указывается количество различных товаров, которые хранятся на складе.
Задача следующая - если количество товара больше нуля, то нужно вывести фразу «в наличии», а если равно нулю, то, соответственно, «отсутствует».
При постановке задачи мы использовали слово «если» и уже сформулировали само условие - значение из столбца Количество должно быть больше нуля.
Если условие выполняется то функция идет по первому пути - выводится фраза «в наличии». Если же условие не выполняется, то функция пойдет по второму пути и выведет слово «отсутствует».
Синтаксис функции ЕСЛИ простой и состоит из трех аргументов.
Сначала задаем условие, в нашем случае значение из столбца Количество должно быть больше нуля. Затем задаем результат, который вернет функция в случае выполнения условия - фраза «в наличии». Не забываем, что текстовые значения должны вводиться в двойных кавычках. Третий аргумент - это результат возвращаемый функцией в случае невыполнения условия - «отсутствует».
Мы можем вводить все аргументы вручную, либо можем сослаться на заранее подготовленные данные, то есть при вводе формулы подставлять ссылки на ячейки, в которых находятся заранее введенные значения.
В таком виде формула выглядет компактней и с ней удобнее работать, ведь если понадобится изменить условие или результат, то это можно сделать быстро в соответствующей таблице, без корректировки самой формулы. Но если формулу в последующем предполагается растягивать на некоторый диапазон, то обязательно необходимо фиксировать ссылки, делая их абсолютными, например, с помощью клавиши F4.
Абсолютным и относительным ссылкам я посвятил отдельное подробное видео и рекомендую с ним обязательно ознакомиться. Тема очень важная и без понимания абсолютной и относительной адресации в Excel сложно, а порой и невозможно, работать с формулами в программе.
Несколько условий в функции ЕСЛИ
Итак, мы рассмотрели самый простой вариант использования функции ЕСЛИ, но очень часто условие может быть не одно и тогда нужно будет применять вложенную функцию ЕСЛИ.
Например, перед нами стоит следующая задача. Для значений больших или равных 10 вывести фразу «в наличии», для значений от 1 до 9 - фразу «пополнить запас», а в случае если количество равно нулю фразу - «нет в наличии». Фактически у нас есть два условия и давайте для них сконструируем формулу.
Если значение из столбца Количество больше или равно 10, то выводим «в наличии» (ссылка на ячейку G4). Далее мы должны указать результат функции, когда условие не выполняется, то есть когда значение будет меньше 10. При таком раскладе мы уже точно знаем, что значение находится в диапазоне от 0 до 9 и поэтому если оно равно нулю, то нужно вывести фразу «нет в наличии», иначе «пополнить запас».
Мы получили две функции ЕСЛИ в одной формуле. При вложении функции нужно внимательно следить за синтаксисом, так как неверно поставленная скобка или точка с запятой приведет к ошибке, которую порой не очень легко найти.
Логическая функция И
Ну а что делать, если нам нужно еще сильнее сегментировать данные. Например, дополним предыдущую задачу еще одним условием - для значений более 100 выводить слово «много», а менее 100 - «мало». При этом предыдущие условия сохраняются.
Итак, если значение больше 10, но меньше 100, то мы должны вывести слово «мало», а ведь значение должно сразу удовлетворят двум условиям, поэтому воспользуемся еще одной функцией И совместно с функцией ЕСЛИ.
Создаем формулу с функцией ЕСЛИ, но перед тем, как задать условие вводим функцию И.
У этой функции в качестве аргументов выступают условия, которые перечисляются через точку с запятой. У нас два условия и мы их указываем - значение должно быть и больше 10, и меньше 100.
Не забываем, что у функции И есть свои скобки и нужно внимательно следить за синтаксисом, чтобы не возникло ошибки.
Если условие выполняется, то указываем «мало» (ссылка на ячейку G4), а если не выполняется, то нам нужно проверить значение еще на ряд условий. То есть вложенная функция ЕСЛИ будет выполняться в том случае, если значение либо больше 100, либо меньше 10, поэтому нам нужно ввести дополнительную проверку на любое из последующих условий.
Например, воспользуемся еще одной функцией И и проверим, что значение меньше 10, но больше 0. В этом случае мы должны вывести фразу «пополнить запас» (G3). Но вот если значение не удовлетворяет и этому условию, то оно либо больше 100, либо равно нулю. Поэтому последняя функция ЕСЛИ окончательно решит задачу - если значение равно 0, то вводим «нет в наличии» (G2) иначе мы однозначно понимаем, что значение больше 100 и выводим слово «много» (G5).
Функция ИЛИ
Итак, мы разобрались с использованиям функции И и нескольких условий в ней. Но что если нужно, чтобы выполнялось одно из условий, а не все одновременно. Вполне логично для этого использовать функцию ИЛИ, которая применяется точно также, как и функци И, но при ее применении достаточно, чтобы хотя бы одно из перечисленных условий выполнилось.
Например, у нас в перечне товаров отсутствующие на складе товар помечаются либо тире, либо указывается их нулевое количество.
Соответственно, для того, чтобы вывести в ячейке фразу «нет в наличии» нам нужно, чтобы хотя бы одно из этих условий соблюдалось.
Создадим формулу с функцией ЕСЛИ и затем в функции ИЛИ перечислим условия. При этом не завываем, что тире - это символ и его нужно указывать в двойных кавычках.
В результате функция будет проверять на истинность оба условия, но срабатывать будет по любому из них.
Итак, мы рассмотрели одну из самых часто используемых функций Excel.
Файл с примерами можно скачать здесь.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
