Функция если в экселе пошаговая инструкция для чайников

Идите в бухгалтерию!
Вы с нуля научитесь вести бухучёт по российским стандартам и работать в 1С, готовить налоговую отчётность и рассчитывать зарплату. Трудоустройство гарантировано договором.
Узнать больше

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

Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

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

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.



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

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

Синтаксис будет выглядеть следующим образом:

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

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функции И и ИЛИ могут проверить до 30 условий.

Пример использования оператора И:

Пример использования функции ИЛИ:

Как сравнить данные в двух таблицах

Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

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

Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.

Исходные данные (таблицы, с которыми будем работать):

Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

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

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

Функция ЕСЛИ в Excel – это своеобразный логический аналог транзистора. Иными словами, это оператор, который определяет, что, если выполняется некоторое условие, то имеют место последствия одного рода, а если же это условие не выполняется, то последствия другого (второго) рода. То есть, аргумент в данном операторе должен задаваться в бинарном формате: на него должны быть возможны только 2 варианта – «истина» или «ложь».

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

Вот как выглядит функция: =ЕСЛИ(логическое выражение, значение если «да», значение если «нет»)
Теперь рассмотрим синтаксис данного оператора:

• на первом месте неизменное «=»;
• далее сам оператор ЕСЛИ;
• затем пара скобок (), внутри которых пишется;
• обязательное условие;
• вариант его выполнения;
• вариант его неисполнения

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

Вариант выполнения – это то значение функции, которое она принимает, если выполняется условие, заданное для аргумента (так называемое значение «да»).

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

Аргумент всегда бинарен. То есть, даже если вы запишите, к примеру, что ячейка А2=7, то оператор воспримет это, как условие, при котором в первом случае результат выводится, если значение в ячейке А2 действительно равно 7, а во втором подразумевает вывод того результата, при котором имеют место вообще все остальные значения аргумента. (Аргументационное условие можно задавать с помощью математических знаков “=”, “>”, “<”, “>=”, “<=”, “<>”).

Примеры использования функции ЕСЛИ

Представляется полезным рассмотреть некоторые примеры применения функции ЕСЛИ в 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, то пусть оператор выведет слово «МНОГО».

Функция ИЛИ

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

Например, отсутствие товара на складе отмечается в электронной таблице либо как 0, либо как прочерк (дефис или минус). Тогда для того, чтобы теперь оператор «ЕСЛИ» вывел нам фразу «в наличии отсутствует», нам необходимо, чтобы он воспринял в качестве аргумента один из символов: 0 ИЛИ «-». (Только не нужно забывать, что знак «-» текстовый, поэтому в соответствии с правилами синтаксиса Excel он должен быть заключен в кавычки). Оператор «ИЛИ» здесь будет вложенной функцией оператора «ЕСЛИ».

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

Пользоваться оператором «ЕСЛИ» можно не только вручную, но и через так называемый «построитель формул», что существенно облегчает верстку формулы. Разберем, как это работает. Пусть нам требуется установить скидку в 25 % на всю женскую обувь. Тогда:

• Щелкаем мышкой на ячейку и заходим во вкладку «Формулы». Затем выбираем «Вставить функцию».
• Открывается «Построитель формул», выбираем «ЕСЛИ», а далее жмем на «Вставить функцию».
• Появляется окно настройки – в поле «логическое выражение» необходимо написать условие (сформировать аргумент). В приведенном примере пишем «В2=«женский».
• Далее заполняем поле «Истина» – сюда нужно записать значение, которое будет выводиться в ячейке, если условие аргумента выполняется.
• Поле «Ложь» заполняется, соответственно, тем значением, которое должно выводиться, если условие аргумента не выполнено.

Когда все поля заполнены, нажимаем кнопку «Готово».

Особый оператор функции ЕСЛИ

В 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.