В статье речь пойдет о функции табличного редактора Excel «Если». Она связана с логическими возможностями приложения. Данную функцию можно отнести к наиболее распространенной, которая зачастую используется во время работы.
Ключевые возможности
В программе Excel формула «Если» позволяет различного рода задачи, которые требуют сравнить определенные значения и получить результат. Такое решение дает возможность использовать ветвящиеся алгоритмы, создавать дерево решений.
Примеры применения
Функция имеет вид: =ЕСЛИ (задача; истина; ложь). Первой составной частью является логическое выражение. Оно способно выступать в виде фразы или числа. Например, «10» или «без НДС». Такой параметр обязательно следует заполнить. Истина представляет собой значение, отображающееся в качестве результата, если выражение верно. Ложь представляет собой данные, которые будут выданы, когда задача неверна.
Равенство параметров двух ячеек
Чтобы лучше понять возможности функции «Если» в Excel, необходимо привести примеры. Поэтому дальше стоит приступить к их рассмотрению. Нужно ввести в клетку C1 значение 8. После этого в поле по адресу D1 необходимо вписать формулу вида: =ЕСЛИ(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.
Можно рассмотреть еще один пример. Существует несколько студентов и их отметки, которые заработаны за экзамен. Данные следующие: 5, 4, а также 3 и 2. По условию задания предусматривается создание для каждого из учащегося текстового комментария «сдал» или «не сдал». Таким образом, если студент получил отметку три и выше, считается, что он сдал экзамен. Если его оценка ниже 3, студент не справился с заланием. Для решения подобной задачи следует написать следующую формулу: =ЕСЛИ(C1<3; «не справился»; «сдал»).
Программа начинает сравнивать результаты каждого студента. Если показатель меньше трех, в соответствующей клетке появится надпись «не справился». Когда отметка 3 и выше, в требуемой графе можно увидеть комментарий о том, что студент не сдал экзамен. Стоит отметить, что текстовые комментарии следует всегда писать в кавычках. Во время использования данной функции можно применять следующие операторы сравнения: < >, =, >, <, >=, <=.
Примеры с использованием условий «ИЛИ», «И»
Необходимо продолжить рассмотрение логических возможностей данного приложения. Существует возможность соединить функцию «Если» с операторами сравнения. Это следующие параметры:
«ИЛИ»;
«И».
Необходимо указать важное условие в Excel: когда оценка студента равна или меньше 5, но больше 3. При этом следует отобразить комментарий: «проходит» или «нет». Таким образом, проходят только те студенты, которые заработали пятерки и четверки. Чтобы записать данную задачу в табличном редакторе следует применить специальную формулу. Она имеет следующий вид: =ЕСЛИ(И(A1<=5; A1>3); «проходит», «нет»).
Если рассмотреть более сложный пример, понадобится использовать «ИЛИ» либо «И». Таким образом, можно ознакомиться с применением формулы в Excel, если существует несколько условий в задании. Например: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). В данном случае можно сделать вывод, когда значение в клетке А1 равно 5 или 10, программа отображает результат 100. В противном случае он составляет 0. Существует возможность применить эти операторы, чтобы найти решение более сложным задачам.
Например, в базе требуется вычислить должников, которым требуется заплатить более 10000 рублей. Можно задать условие, что заем не погашался более полугода, то есть шести месяцев. Благодаря функции табличного редактора Excel «Если» позволяется в автоматическом режиме получить около соответствующих имен отметку «проблемный клиент». Допустим, в клетке A1 размещены данные, которые указывают на срок задолженности (месяцы). Поле B1 содержит сумму.
В данном случае формула представлена в следующем виде: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный клиент»; «»). Таким образом, если выявлен человек, соответствующий указанным условиям, программа отображает напротив его имени соответствующий комментарий. Для других участников списка эта клетка останется пустой.
Также можно ознакомиться с примером, когда ситуация является критической. Необходимо ввести соответствующий комментарий. Как результат, формула получится следующей: =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая ситуация»; «»). Однако если программа определяет совпадения по одному из параметров как минимум, отображается соответствующее примечание.
Сложные задачи
Функция Excel «Если» применяется для того, чтобы избежать встроенных ошибок при делении на ноль. Кроме того, она используется и в некоторых других ситуациях. Первый случай обозначается, как «ДЕЛ/0». Его можно встретить достаточно часто. Обычно это происходит тогда, когда формула «A/B» требует копирования. В это же время показатель B в отдельных ячейках составляет 0. Чтобы избежать подобной ситуации, стоит использовать возможности рассматриваемого оператора. Таким образом, требуемая формула имеет следующий вид: =ЕСЛИ(B1=0; 0; A1/B1). Итак, если клетка B1 заполнена значением «ноль», редактор отобразит «0». В противном случае программа поделит показатель A1 на данные B1 и выдаст требуемый результат.
Скидка
Как показывает практика, зачастую возникают ситуации, которые рассматриваются далее. Потребуется рассчитать скидки, учитывая общую сумму средств, затраченных на покупку конкретного товара. Матрица, которая используется, выглядит следующим образом: менее 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; более 5001 - 7%. Можно ознакомиться с ситуацией, когда в Excel имеется база данных посетителей и сведения о сумме, потраченной на покупку. Далее следует рассчитать скидку для каждого клиента. Для этого понадобится применить следующее выражение: =ЕСЛИ(A1>=5001; B1*0,93; ЕСЛИ(А1>=3001; B1*0,95;..).
Система выполняет проверку общей суммы покупок. Если она превышает значение в 5001 рублей, выполняется умножение на 93 процента цены товара. Если превышена отметка в 3001 единиц, происходит аналогичное действие, однако уже учитывается 95%.
Возможность проведения логических проверок в ячейках является мощным инструментом. Вы найдете бесконечное количество применений для ЕСЛИ() в ваших повседневных документах. Проблема заключается в том, что часто необходимые проверки выходят за рамки возможностей простой функции ЕСЛИ() . Это происходит, когда необходимо сделать проверку на два и более условия до принятия результата.
Для таких случаев в Excel предусмотрено несколько вариантов: использование ЕСЛИ() внутри другого ЕСЛИ() , функции И() и ИЛИ() . Далее мы познакомимся с этими способами.
Использование ЕСЛИ() внутри другой функции ЕСЛИ()
Давайте рассмотрим вариант на основе изученной ранее функции =ЕСЛИ(А1>1000;"много"; "мало") . Что если вам необходимо вывести другую строку, когда число в А1 является, например, большим, чем 10.000? Другими словами, если выражение А1>1000 верно, вы захотите запустить другую проверку и посмотреть, верно ли, что А1>10000. Такой вариант вы можете создать, применив вторую функцию ЕСЛИ() внутри первой в качестве аргумента значение _если_истина: =ЕСЛИ(А1>1000;ЕСЛИ(А1>10000;"очень много"; "много");"мало") .
Если А1>1000 является истинным, запускается другая функция ЕСЛИ() , возвращающая значение «очень много», когда А1>10000. Если же при этом А1 меньше или равно 10000, возвращается значение «много». Если же при самой первой проверке число А1 будет меньше 1000, выведется значение «мало».
Обратите внимание, что с таким же успехом вы можете запустить вторую проверку, в случае если первая будет ложной (то есть в аргументе значение_если_ложь функции еслио). Вот небольшой пример, возвращающий значение «очень мало», когда число в А1 меньше 100: =ЕСЛИ(А1>1000;"много";ЕСЛИ(А1<100;"очень мало"; "мало")) .
Расчет бонуса с продаж
Хорошим примером использования одной проверки внутри другой проверки является расчет бонуса с продаж персоналу. который работает в Клуб — отель Гелиопарк Талассо, Звенигород . В данном случае, если значение равно X, вы хотите получить один результат, если У — другой, если Z
— третий. Например, в случае вычисления бонуса за успешные продажи возможны три варианта:
- Продавец не достиг планового значения, бонус равен 0.
- Продавец превысил плановое значение менее чем на 10%, бонус равен 1 000 рублей.
- Продавец превысил плановое значение более чем на 10%, бонус равен 10 000 рублей.
Вот формула для расчета такого примера: =ЕСЛИ(Е3>0;ЕСЛИ(Е3>0.1;10000;1000);0) . Если значение в Е3 является отрицательным, то возвращается 0 (нет бонуса). В случае когда результат положительный, проверяется, больше ли он 10%, и в зависимости от этого выдается 1 000 или 10 000. Рис. 4.17 показывает пример работы формулы.
Функция И()
Часто бывает необходимо провести проверку только на верность одновременно двум условиям. Например, выплата бонуса осуществляется, только когда продажи определенного товара превысили план и также общие продажи превысили план. Если одно из данных условий не выполняется (или оба условия), премия не выплачивается. В булевой логике это называется логическим И, поскольку оба условия должны быть истинны для истинности всего выражения.
В Excel выражения логического И обрабатываются с помощью функции И() : И(логическое_значение1;логическое_значение2;…). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо.
Еще раз отметим работу функции:
- Если все выражения возвращают ИСТИНА (или любое положительное число), И() возвращает ИСТИНА.
- Если один или более аргументов возвращают ЛОЖЬ (или 0), И() возвращает ЛОЖЬ.
Чаще всего И() применяется внутри функции ЕСЛИ() . В таком случае, когда все аргументы внутри И() вернут ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение если истина. Если одно или более из выражений в И() вернет ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь.
Вот небольшой пример: =ЕСЛИ(И(С2>0;В2>0);1000;"нет бонуса") . Если значение в В2 будет больше нуля и значение в С2 будет больше нуля, формула вернет 1000, в противном случае выведется строка «нет бонуса».
Разделение значений по категориям
Полезным применением функции и () является разделение по категориям в зависимости от значения. Например, у вас имеется таблица с результатами какого-то опроса или голосования, и вы хотите разделить все голоса на категории в соответствии со следующими возрастными рамками: 18-34,35-49, 50-64,65 и более. Предполагая, что возраст респондента находится в ячейке В9, следующие аргументы функции и () проводят логическую проверку на принадлежность возраста диапазону: =И(В9>=18;В9
Если ответ человека находится в ячейке С9, следующая формула выведет результат голосования человека, если срабатывает проверка на соответствие возрастной группе 18-34: =ЕСЛИ(И(В9>=18;В9
- 35-49: =ЕСЛИ(И(В9>=35;В9
- 50-64: =ЕСЛИ(И(В9>=50;В9
- 65+: =ЕСЛИ(В9>=65;С9;"")
Функция ИЛИ()
Существуют ситуации, когда вам необходимо решение, только когда одно из условий принимает положительное значение (ИСТИНА). Например, вы можете решить выплатить бонус сотрудникам, когда общие продажи превышают план или когда продажи отдельного товара превысили прогноз. В булевой логике это называется логическим ИЛИ .
Такие условия проверяются в Excel с помощью функции ИЛИ() : ИЛИ(логическое_значение1; логическое_значение2;...). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо. Результат работы ИЛИ() зависит от следующих условий:
- Если один аргумент или более возвращает ИСТИНУ (любое положительное число), ИЛИ() возвращает ИСТИНУ.
- Если все аргументы возвращают ЛОЖЬ (нулевое значение), результатом работы ИЛИ() будет ЛОЖЬ.
Так же как и И() , чаще всего функция ИЛИ() используется внутри проверки ЕСЛИ() . В таком случае, когда один из аргументов внутри ИЛИ() вернет ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение_если_истина. Если все выражения в ИЛИ() вернут ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь . Вот небольшой пример: = ЕСЛИ(ИЛИ(С2>0;В2>0);1000;"нет бонуса") .
В случае когда в одной из ячеек (С2 или В2) будет положительное число, функция вернет 1000. Только когда оба значения будут отрицательны (или равны нулю), функция вернет строку "нет бонуса".
Функция ЕСЛИ() , английский вариант IF(), используется при проверке условий. Например, =ЕСЛИ(A1>100;"Бюджет превышен";"ОК!") . В зависимости от значения в ячейке А1 результат формулы будет либо "Бюджет превышен" либо "ОК!".
Функция ЕСЛИ() относится к наиболее часто используемым функциям.
Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Лог_выражение
- любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.
=ЕСЛИ(A1>=100;"Бюджет превышен";"ОК!")
Т.е. если в ячейке A1
содержится значение большее или равное 100, то формула вернет ОК!,
а если нет, то Бюджет превышен
.
В качестве аргументов функции, могут участвовать формулы, например:
=ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10))
Т.е. если в ячейке A1
содержится значение >100, то суммирование ведется по столбцу B
, а если меньше, то по столбцу С
.
Вложенные ЕСЛИ
В EXCEL 2007 в качестве значений аргументов значение_если_истина
и значение_если_ложь
можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ()
.
=ЕСЛИ(A1>=100;"Бюджет превышен";ЕСЛИ(A1>=90;"Крупный проект";ЕСЛИ(A1>=50;"Средний проект";"Малый проект ")))
ПРОСМОТР(A1;{0;50;90;100};{"Малый проект";"Средний проект";"Крупный проект";"Бюджет превышен"})
ВПР(A1;A3:B6;2)
Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:
Если требуется вывести разный текст в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0, то можно записать следующую формулу:
ПРОСМОТР(A1;{-1E+307;0;1E-307};{"<0";"=0";">0"})
или, если требуется вместо текстовых значений вывести формулы, можно использовать ссылки на диапазон ячеек (содержащих формулы)
ПРОСМОТР(A24;{-1E+307;0;1E-307};A27:A29) (см. файл примера )
Опущен третий аргумент [значение_если_ложь]
Третий аргумент функции не обязателен, если его опустить, то функция вернет значение ЛОЖЬ (если условие не выполнено).
=ЕСЛИ(A1>100;"Бюджет превышен")
Если в ячейке A1
содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.
Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число
Т.к. значение ЛОЖЬ эквивалентно 0, то формулы
=ЕСЛИ(0;"Бюджет превышен";"ОК!")
или (если в ячейке A1
содержится значение 0)
=ЕСЛИ(A1;"Бюджет превышен";"ОК!")
вернут ОК!
Если в ячейке A1 находится любое другое число кроме 0, то формула вернет Бюджет превышен . Такой подход удобен, когда проверяется равенство значения нулю.
Связь функции ЕСЛИ() с другими функциями использующие условия
EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .
Функция ЕСЛИ() , как альтернативный вариант, может быть также использована для подсчета и сложения значений с использованием условий. Ниже приведены иллюстрирующие примеры.
Пусть данные находятся в диапазоне A6:A11 (см. файл примера)
Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ()
, записав =СУММЕСЛИ(A6:A11;">10")
. Аналогичный результат (23) можно получить с помощью
=СУММ(ЕСЛИ(A6:A11>10;A6:A11))
(для ввода формулы в ячейку вместо ENTER
нужно нажать CTRL+SHIFT+ENTER
)
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11
=СЧЁТЕСЛИ(A6:A11;">10")
. Аналогичный результат (2) можно получить с помощью
=СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ()
можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10:
=МИН(ЕСЛИ(A6:A11>10;A6:A11))