8 738 
Оставить комментарий
Сохранить статью:
О чем речь? Функция ЕСЛИ в Excel является одной из самых востребованных, так как в ходе расчетов нередко нужно использовать схему формата «если…, то». С ее помощью можно обрабатывать одно или несколько условий.
На что обратить внимание? Помимо непосредственной помощи в расчетах, данная функция может упростить использование таблицы Excel в тех случаях, когда применение формулы невозможно.
В статье рассказывается:
- Суть функции ЕСЛИ в Excel
- Примеры использования функции ЕСЛИ
- Расширение функции ЕСЛИ
- Создание функции ЕСЛИ через построитель формул
- Особый оператор функции ЕСЛИ
-
Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.Бесплатно от Geekbrains
Суть функции ЕСЛИ в Excel
Функция ЕСЛИ в Excel – это своеобразный логический аналог транзистора. Иными словами, это оператор, который определяет, что, если выполняется некоторое условие, то имеют место последствия одного рода, а если же это условие не выполняется, то последствия другого (второго) рода. То есть, аргумент в данном операторе должен задаваться в бинарном формате: на него должны быть возможны только 2 варианта – «истина» или «ложь».
Итак, функция ЕСЛИ позволяет выдавать только 2 варианта вычислений в зависимости от сформулированного и выбранного условия.
Вот как выглядит функция: =ЕСЛИ(логическое выражение, значение если «да», значение если «нет»)
Теперь рассмотрим синтаксис данного оператора:
- на первом месте неизменное «=»;
- далее сам оператор ЕСЛИ;
- затем пара скобок (), внутри которых пишется;
- обязательное условие;
- вариант его выполнения;
- вариант его неисполнения
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Скачивайте и используйте уже сегодня:
Топ-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка
Только проверенные нейросети с доступом из России и свободным использованием
ТОП-100 площадок для поиска работы от GeekBrains
Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽
Под обязательным условием подразумевается логическое или математическое выражение, где присутствует переменная величина, которая может менять свое значение.
Вариант выполнения – это то значение функции, которое она принимает, если выполняется условие, заданное для аргумента (так называемое значение «да»).
Вариант неисполнения – это значение функции, которое должно выводиться, если аргумент не соответствует заданному значению или же не попадает в допустимый диапазон.
Примеры использования функции ЕСЛИ
Представляется полезным рассмотреть некоторые примеры применения функции ЕСЛИ в Excel.
- Предположим, у нас есть таблица расчета зарплат. Руководство компании приняло решение выплатить каждой женщине к 8 марта премию в 1000 рублей. Но в нашей таблице мужчины и женщины указаны вразнобой (допустим, их фамилии идут в алфавитном порядке), однако присутствует колонка, в которой проставлен пол (М/Ж). Нам нужно сделать так, чтобы величина надбавки в соответствующем столбце («Премия к 8 марта») была указана только напротив женских фамилий. Допустим, колонка, в которой указан пол, это столбец В. Тогда формула будет иметь следующий вид: «=ЕСЛИ(B6=»Ж»;1000;0)»
- Строка 6 взята произвольно – такая формула должна находиться в каждой строке данной колонки.
- Вписав данную формулу в самую верхнюю ячейку данного столбца, «хватаем» ячейку курсором за правый нижний угол и «протягиваем» до самого конца столбца. Таким образом, мы копируем формулу, при этом адрес аргументационной ячейки будет изменяться в соответствии с изменением адреса ячейки-функции.
Условное заполнение выполнено.
Мы помним, что аргумент у нас бинарен, то есть, условие в формуле, по сути, может быть указано только одно. А что, если требуется указать их несколько? В этом случае применяется так называемое вложение. При выполнении обязательного условия выдается готовая функция, если же аргумент не выполняется, то тогда вписывается новый оператор ЕСЛИ с дополнительными параметрами. И функция уже будет зависеть от выполнения именно второго оператора.
- Усложним наш пример с 8-мартовскими премиями. Пусть 1000 рублей получат только женщины из числа основного персонала, а вспомогательные сотрудницы получат только 500 рублей. (Мужчинам же, как и в прошлый раз, никакие доплаты не положены).
- С помощью первого оператора ЕСЛИ из перечня реципиентов надбавки выделяются все мужчины. Второй же оператор сепарирует основной и вспомогательный персонал. Здесь формула будет выглядеть следующим образом: «=ЕСЛИ(B6=»М»;0;ЕСЛИ(С6=«Основной персонал»;1000;0))»
- Данная формула также вписывается в самую верхнюю ячейку и копируется (методом «протяжки») на весь актуальный столбец «Премия к 8 марта» таблицы.
Расширение функции ЕСЛИ
Логическая функция «И»
Иногда бывает нужно сегментировать данные еще сильнее. Для примера можно рассмотреть формулу, касающуюся запасов на складе, добавив необходимость указания в таблице слов «МНОГО» или «МАЛО» в зависимости от того, какое значение мы получаем: если больше 100 ед. товара, то «МНОГО», а если меньше 100, то мало. При этом промежуточное значение равно 10.
Скачать файлЗдесь нам понадобится воспользоваться оператором «И» совместно с оператором «ЕСЛИ». «И» используется фактически для того, чтобы задать необходимые диапазоны. (Нужно внимательно следить за синтаксисом, чтобы не перепутать скобки и точки с запятой у разных операторов).
Если первое условие выполняется, то указывается «МАЛО», а если оно не исполнено, то значение проверяется на выполнение еще ряда условий. Оператор «ЕСЛИ» в данном случае предстает вложенной функцией оператора «И».
Первый диапазон значений для оператора «И» является от 0 до 10. В данном случае нам необходимо, чтобы у нас в колонке, скажем, G, высвечивалась фраза «необходимо пополнить запас». Второй диапазон у оператора «И» – от 10 до 100. Если же значение не попадает ни в один из указанных диапазонов, то это означает, что оно либо больше 100, либо равно 0. Если 0, то тогда указываем в операторе «ЕСЛИ» «в наличии отсутствует», а если 100, то пусть оператор выведет слово «МНОГО».
Функция ИЛИ
Оператор «И» имеет возможность учесть сразу несколько условий (в том числе, вложенных). Но что же делать, если нам требуется, чтобы выполнялись не все условия сразу, а лишь одно из указанных? Тогда следует задействовать оператор «ИЛИ». Данная функция выдаст осмысленный результат в случае, если логически вычисляем (то есть, имеет смысл) хотя бы один из ее аргументов.
на курсы от GeekBrains до 12 мая
Например, отсутствие товара на складе отмечается в электронной таблице либо как 0, либо как прочерк (дефис или минус). Тогда для того, чтобы теперь оператор «ЕСЛИ» вывел нам фразу «в наличии отсутствует», нам необходимо, чтобы он воспринял в качестве аргумента один из символов: 0 ИЛИ «-». (Только не нужно забывать, что знак «-» текстовый, поэтому в соответствии с правилами синтаксиса Excel он должен быть заключен в кавычки). Оператор «ИЛИ» здесь будет вложенной функцией оператора «ЕСЛИ».
Создание функции ЕСЛИ через построитель формул
Пользоваться оператором «ЕСЛИ» можно не только вручную, но и через так называемый «построитель формул», что существенно облегчает верстку формулы. Разберем, как это работает. Пусть нам требуется установить скидку в 25 % на всю женскую обувь. Тогда:
- Щелкаем мышкой на ячейку и заходим во вкладку «Формулы». Затем выбираем «Вставить функцию».
- Открывается «Построитель формул», выбираем «ЕСЛИ», а далее жмем на «Вставить функцию».
- Появляется окно настройки – в поле «логическое выражение» необходимо написать условие (сформировать аргумент). В приведенном примере пишем «В2=«женский».
- Далее заполняем поле «Истина» – сюда нужно записать значение, которое будет выводиться в ячейке, если условие аргумента выполняется.
- Поле «Ложь» заполняется, соответственно, тем значением, которое должно выводиться, если условие аргумента не выполнено.
ТОП-100 площадок для поиска работы от GeekBrains
20 профессий 2023 года, с доходом от 150 000 рублей
Чек-лист «Как успешно пройти собеседование»
Когда все поля заполнены, нажимаем кнопку «Готово».
Особый оператор функции ЕСЛИ
В Excel предусмотрен особый оператор для работы с ошибками в формулах. Это оператор «ЕСЛИОШИБКА». Он дает возможность отслеживать широкий спектр различных ошибок, заменяя при этом аргументы и значения. Вот перечень тех ошибок, которые поддаются отслеживанию с помощью оператора «ЕСЛИОШИБКА»:
- #N/A
- #VALUE!
- #REF!
- #DIV/0!
- #NUM!
- #NAME?
- #NULL!
Для того чтобы получить вразумительный результат работы оператора «ЕСЛИОШИБКА», необходимо разместить любую имеющуюся формулу в скобки данной функции и задать тот результат, который вы хотите, чтобы функция возвращала при нахождении искомые ошибки. Пример выглядит следующим образом:
«=ЕСЛИОШИБКА([имеющаяся формула];[фраза, которой можно заменить сообщение об ошибке])»
Разберем конкретный пример с формулой ВПР. Если задействовать в расчетах данную формулу, то при попытке найти совпадения велик шанс того, что найдены они не будут, и вы получите сообщение об ошибке «N/A».
Допустим, электронная таблица содержит список фрилансеров, работающих неудаленно. Представлен список фирм, где они работают, и нужно указать, кто чем занимается. При заполнении данной информации были допущены некоторые ошибки, связанные с отсутствующими совпадениями. Представляется разумным создать функцию, которая поможет выдать информацию в привязке к названию фирмы, указанной в таблице:
«=ВПР(B2,$E$4:$F$6,2,ЛОЖЬ)»
Однако ряд компаний в справочной таблице отсутствует, отчего программа выдает классические «N/A». Попробуем заменить оператор на нечто более нас устраивающее – на функцию «ЕСЛИОШИБКА». Вот как будет выглядеть наша формула:
«=ЕСЛИОШИБКА(существующая формула, чем заменить ошибку)»
Если теперь в качестве замены мы укажем фразу, например, «связаться с HR менеджером», то результат проверки будет более утилитарен. Вот наша формула с учетом синтаксиса:
«=ЕСЛИОШИБКА(ВПР(B3,$E$4:$F$6,2,ЛОЖЬ),» связаться с HR менеджером»)»
Многие, глядя на «устрашающий» вид этой формулы, просто боятся ее использовать. Однако стоит посмотреть внимательно на ее логическую структуру, и сразу же становится понятно, что все, что нужно сделать – это просто скопировать проверяемую формулу и вставить в качестве первого аргумента в функцию «ЕСЛИОШИБКА» (ну, и произвольным образом написать, что нужно сделать при выявлении такой ошибки). А с конкретной рекомендацией работать удобнее, чем с малопонятным символьным сочетанием «N/A», которое еще надо знать как интерпретировать.
«ЕСЛИОШИБКА» – это особый эксплуатационный вариант применения функции «ЕСЛИ», специально разработанной для удобства продвинутых пользователей Excel. Если приходится постоянно работать с большими массивами данных и тех же формул, то специальный оператор, который ищет несостыковки и выдает полезные рекомендации – это как раз то, «что доктор прописал».
Грамотное использование функции «ЕСЛИ» в Excel позволяет создавать настоящие многофункциональные программные шедевры, которые способны обрабатывать большие объемы задаваемых данных и при этом выдавать результирующую информацию в максимально удобном виде. Навык работы с оператором «ЕСЛИ» является одним из наиболее полезных знаний и умений в опыте пользования Excel.
Разберем 11 самых важных жизненных вопросов
Только до 
Эксклюзивные лекции по основам программирования
