Сегодня мы рассмотрим функцию ЕСЛИ .
Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах.
Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.
Синтаксис функции ЕСЛИ очень простой:
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.
В этой части необходимо проверить на соответствие выражения.
Например:
ЕСЛИ(А1=10 ; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ
Другой пример
ЕСЛИ(А1>30 ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ
Еще пример
ЕСЛИ(С1=”Да” ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
значение_если_истина , значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения : ИСТИНА и ЛОЖЬ
Пример использования функции ЕСЛИ в Excel
Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)
Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ
ЕСЛИ(A3="Ведро";D3;"-")
Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.
Итак, первый аргумент (лог выражения ) A3="Ведро" проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина ), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь ), в нашем случае это «-» (т.е будет написано тире).
Таким образом, в ячейки E3 появится значение D3, т.е число 240.
Одна из самых популярных функций в приложении Excel – ЕСЛИ . Это логическое сравнение значения и получаемого результата. Если говорить проще, то функция может звучать так:
ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще
Синтаксис этой функции достаточно прост:
ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА; значение в случае_ЛОЖЬ)
Разберем подробнее:
- Логическое_условие – значение, которое будем проверять
- Значение_в_случае_ИСТИНА – действие, когда требование выполняется
- Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется
Как использовать функцию
Пример №1
На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем
заданное условие А1>25
. ЕСЛИ это требование выполняется
, тогда выводим в ячейку «больше 25
», иначе «меньше или равно 25
».
Пример №2
Теперь более сложное задание. Ниже мы рассмотрим пример переаттестации сотрудников предприятия. Изначально таблица выглядит так:
Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ . Критерии у нас будут такими: кто набрал более 45 балов , тот считается сдавшим экзамен, ну а все остальные нет.
Для выполнения задачи необходимо :
Используем несколько условий
При необходимости, функцию ЕСЛИ можно вложить друг в друга. Таким образом, мы расширим варианты решений.
Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку : Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60 . Оценку Хорошо можно будет получить, набрав от 45 до 60 балов . Ну и в остальных случаях ставим Плохо .
Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить , что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.
Другие варианты использования функции
Находим сумму столбцов или ячеек
К примеру, есть необходимость просуммировать каждую вторую ячейку столбца. В этом случае поможет использование оператора СУММЕСЛИ
. Он позволит работать только с теми столбцами, которые попадают под наши требования. Мы будет рассматривать случай, когда необходимо суммировать элементы в нечетных строках.
Для решения задачи, нам потребуется вставить дополнительный столбец и пронумеровать строки 1 и 0. Именно эти данные мы будем использовать в нашей формуле. В примере видно, какую формулу мы использовали.
Синтаксис : СУММЕСЛИ(диапазон_ячеек; используемое_условие; [диапазон_суммирования])
- Диапазон – обязательный аргумент. Тут задается диапазон, который оценивается на соответствие условию.
- Используемое_условие – тут указываем, какие именно ячейки необходимо суммировать.
- Диапазон_суммирования – указываем, какой именно диапазон использовать для суммирования.
Количество повторений элементов на листе
В этом случае мы можем посчитать, сколько раз заданный элемент встречается на листе. Для этого используем функцию СЧЕТЕСЛИ
. Она считает количество ячеек, которые совпадают
с заданным значением.
В этом примере мы считаем, сколько клиентов находится в каждом городе. В формуле мы задаем первым диапазон, а вторым значения, которые следует искать.
Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данных
В примере мы постараемся определить усредненный объем дохода
от клиентов по каждому городу. Для этого поделим суммарный доход города на количество клиентов.
Поиск нескольких вхождений элемента в списке
Если у нас есть постоянно обновляемый список данных, в котором по мере роста могут появляться дубликаты , то поиск вхождений в этом списке может оказаться затруднительным. Для решения этой задачи проще всего воспользоваться функцией ВПР и СЧЕТЕСЛИ .
Для начала, добавим
дополнительный столбец, который добавить порядковый номер вхождения к имени клиента. В результате, первое упоминание элемента будет выдавать Имя1, следующее Имя2 и т.д.
Используем поиск по клиенту Кристина Агилера. Используя формулу ВПР («Кристина Агилера3», диапазон_поиска, 2, ЛОЖЬ), мы получим третий номер этого клиента. В формуле, последним значением ставим ЛОЖЬ, поскольку список не сортирован, и нам необходимо точное совпадение элементов.
Как сократить количество вложений ЕСЛИ
Версии до EXCEL 2003 включительно поддерживали до 7 уровней вложенности ЕСЛИ. Начиная с версии Excel 2007, это ограничение убрали . Но стоит заметить, что ниже 3-4 уровня вложенности мало кто опускается.
Для того чтобы уменьшить количество использования вложений ЕСЛИ, можно использовать функцию ВЫБОР . Она работает со значениями или действиями из заданного списка по номеру индекса.
Синтаксис : ВЫБОР (номер_индекса; значение_индекса1; значение_индекса2; [значение_индекса3];…)
Для примера, функция ВЫБОР (1; «Третий»; «Второй»; «Первый») , вернет нам слово «Третий », если мы ее добавим в ячейку.
Стоит отметить, что имеются некоторые ограничения. В частности, в качестве индекса могут выступать только числа
.
«развилка».
Разветвляющимся
называется такой алгоритм, в котором выбирается один из нескольких возможных вариантов вычислительного процесса. Каждый подобный путь называется ветвью алгоритма
.
Признаком разветвляющегося алгоритма является наличие операций проверки условия. Чаще всего для проверки условия используется условный оператор if .
if может использоваться в форме полной или неполной развилки.В случае неполной развилки если Условие истинно, то БлокОпераций1 выполняется, если Условие ложно, то БлокОпераций1 не выполняется.
В случае полной развилки если Условие истинно, то выполняется БлокОпераций1 , иначе выполняется БлокОпераций2 .
БлокОпераций может состоять из одной операции. В этом случае наличие фигурных скобок, ограничивающих блок, необязательно.
Пример на C
:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#include
int
main()
{
int
k; // объявляем целую переменную k
printf("k= "
); // выводим сообщение
scanf("%d"
, &k); // вводим переменную k
if
(k >= 5) // если k>5
printf("%d >= 5"
, k); // выводим "ЗНАЧЕНИЕ >= 5"
else
// иначе
printf("%d < 5"
, k); // выводим "ЗНАЧЕНИЕ < 5"
getchar(); getchar();
return
0;
}
Результат выполнения
Оператор if может быть вложенным.
Пример на C
:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#define
_CRT_SECURE_NO_WARNINGS
// для возможности использования scanf
#include
#include
int
main() {
int
key;
system("chcp 1251"
);
system("cls"
); // очищаем окно консоли
printf();
scanf("%d"
, &key);
if
(key == 1) // если key = 1
printf("\n Выбран первый пункт"
); // выводим сообщение
else
if
(key == 2) // иначе если key = 2
printf("\n Выбран второй пункт"
); // выводим сообщение
else
// иначе
printf(); // выводим сообщение
getchar(); getchar();
return
0;
}
Результат выполнения
При использовании вложенной формы оператора if
опция else
связывается с последним оператором if
. Если требуется связать опцию else
с предыдущим оператором if
, внутренний условный оператор заключается в фигурные скобки:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#define
_CRT_SECURE_NO_WARNINGS
// для возможности использования scanf
#include
#include
int
main() {
int
key; // объявляем целую переменную key
system("chcp 1251"
); // переходим в консоли на русский язык
system("cls"
); // очищаем окно консоли
printf("Введите номер пункта, 1 или 2: "
);
scanf("%d"
, &key); // вводим значение переменной key
if
(key != 1) { // если key не равен 1
if
(key == 2) // если key равен 2
printf("\n Выбран второй пункт"
); // вывод сообщения
} // если key - не 1 и не 2, то ничего не выводится
else
// иначе, если key равен 1
printf("\n Выбран первый пункт"
); // вывод сообщения
getchar(); getchar();
return
0;
}
Результат выполнения
Тернарные операции
Тернарная условная операция
имеет 3 аргумента и возвращает свой второй или третий операнд в зависимости от значения логического выражения, заданного первым операндом. Синтаксис тернарной операции в языке Си
Условие? Выражение1: Выражение2;
Если выполняется Условие , то тернарная операция возвращает Выражение1 , в противном случае - Выражение2 .
Тернарные операции, как и операции условия, могут быть вложенными. Для разделения вложенных операций используются круглые скобки.
Приведенный выше пример с использованием тернарных операций можно представить в виде
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#define
_CRT_SECURE_NO_WARNINGS
// для возможности использования scanf
#include
#include
int
main() {
int
key; // объявляем целую переменную key
system("chcp 1251"
); // переходим в консоли на русский язык
system("cls"
); // очищаем окно консоли
printf("Введите номер пункта, 1 или 2: "
);
scanf("%d"
, &key); // вводим значение переменной key
key == 1 ? printf("\n Выбран первый пункт"
) :
(key == 2 ? printf("\n Выбран второй пункт"
) :
printf("\n Первый и второй пункты не выбраны"
));
getchar(); getchar();
return
0;
}
Оператор ветвления switch (оператор множественного выбора)
Оператор if позволяет осуществить выбор только между двумя вариантами. Для того, чтобы производить выбор одного из нескольких вариантов необходимо использовать вложенный оператор if . С этой же целью можно использовать оператор ветвления switch .
Общая форма записи
switch
(ЦелоеВыражение)
{
case
Константа1: БлокОпераций1;
break
;
case
Константа2: БлокОпераций2;
break
;
. . .
case
Константаn: БлокОперацийn;
break
;
default: БлокОперацийПоУмолчанию;
break
;
}
Оператор ветвления switch выполняется следующим образом:
- вычисляется ЦелоеВыражение в скобках оператора switch ;
- полученное значение сравнивается с метками (Константами ) в опциях case , сравнение производится до тех пор, пока не будет найдена метка, соответствующая вычисленному значению целочисленного выражения;
- выполняется БлокОпераций соответствующей метки case ;
- если соответствующая метка не найдена, то выполнится БлокОперацийПоУмолчанию , описанный в опции default .
Альтернатива default
может отсутствовать, тогда не будет произведено никаких действий.
Опция break;
осуществляет выход из оператора switch
и переход к следующему за ним оператору. При отсутствии опции break
будут выполняться все операторы, начиная с помеченного данной меткой и кончая оператором в опции default
.
Константы в опциях case должны быть целого типа (могут быть символами).
Полный синтаксис (нажмите, чтобы раскрыть)
Условный оператор "Если"
Описание:
Оператор Если управляет выполнением программы, основываясь на результате одного или более логических выражений. Оператор может содержать любое количество групп операторов, возглавляемых конструкциями ИначеЕсли - Тогда .
Синтаксис:
Параметры:
Тернарный условный оператор
Описание:
Позволяет вычислить одно из двух заданных выражений в зависимости от результата вычисления логического выражения.
Синтаксис:
? (< Логическое выражение> , < Выражение 1 > , < Выражение 2 > ) |
Параметры:
Возвращаемое значение:
Результат вычисления одного из результирующих выражений.
&НаКлиенте Процедура ВыполнитьКод(Команда) /// Как составить оператор Если в 1с 8.3, 8.2 Если 1 > 0 Тогда // Истина // блок операторов Сообщить("Компьютер выполнит все команды из этого блока." ) ; Сообщить("Один больше нуля." ) ; КонецЕсли ; Если 1 < 0 Тогда // Ложь Сообщить("Один меньше нуля." ) ; Иначе Сообщить("Сработает именно эта ветка условного оператора (#А)." ) ; Сообщить("Один больше нуля." ) ; КонецЕсли ; Если 1 < 0 Тогда // Ложь Сообщить("Один меньше нуля." ) ; ИначеЕсли 1 = 0 Тогда // Ложь Сообщить("Один равен нулю." ) ; Иначе Сообщить("Сработает именно эта ветка условного оператора (#Б)." ) ; Сообщить("Один больше нуля." ) ; КонецЕсли ; /// Как составить тернарный оператор? в 1с 8.3, 8.2 Текст = ? (1 > 2 , "Один больше двух." , "Один не больше двух." ) ; Сообщить(Текст) ; // выведет "Один не больше двух." КонецПроцедуры /// Скачать и выполнить эти примеры на компьютере |
Распространенный вопрос по Excel «Как записывать несколько условий в одной формуле?». Особенно часто применяется два и более условий при использовании функции ЕСЛИ. Сделать несколько условий в формуле ЕСЛИ довольно просто, главное знать основные принципы. Их и обсуждаем ниже.
На мой взгляд, показательно рассмотреть пример с решением системы условий. Такие задания часто задают в институтах, на парах по Excel.
Например, есть вот такая, довольно нагроможденная формула:
Разберем на примере, как перенести ее в Excel
Понятно, что эта формула будет состоять из 3 частей, как минимум:
SIN(B1)^2 =COS(B1) =EXP(1/B1)
Но как записать несколько этих функций в одну, еще и по условию? Для того чтобы разобраться, подробно посмотрим на функцию ЕСЛИ.
Ее состав следующий:
ЕСЛИ(Условие;если условие = ДА (ИСТИНА);если условие = НЕТ (ЛОЖЬ))
Т.е. если мы запишем простую формулу, что мы получим в итоге в ячейке B2?
Верно — отобразиться 100. Если же в А1 будет стоять любое другое значение кроме 1, то в B2 отобразится бы 0.
Вернемся к нашей системе условий. Теперь нам надо понимать как записать сразу два условия до первой точки с запятой. У нас в B1 пусто, а значит = 0, и только при выполнении обоих условий А1=1 и B1=0 (знак *) значение формулы будет равно 100.
Особо разберем * между скобками
Оператор И он же * означает, что должно выполняться оба условия одновременно, А1=1 и B1=0.
Если между скобками поставить + (или), то достаточно будет одного из условий. Например только если А1=1, то уже будет отображаться 100.
Мы готовы к написанию формулы, будем это делать по частям
Запишем первое условие
ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2);
Если условие выполняется, то выполняется первая формула с синусом
Если нет, второе условие
ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1)
Во всех же остальных случаях будет выполнятся формула =EXP(1/B1)
Итого получается:
ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)))
Запись нескольких формул в одной
Если в ячейки B1 будет текст, то формула выдаст ошибку. Поэтому я часто применяю формулу .
Представим что вся наша формула из предыдущего пункта это один условный аргумент А
Тогда =ЕСЛИОШИБКА(А;»»)
Или для нашего примера
ЕСЛИОШИБКА(ЕСЛИ((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)));"")
Пример можно скачать