Экселе если 1. Условные предложения первого типа в английском языке

Распространенный вопрос по 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)));"")

Пример можно скачать

Общая информация о ЕСЛИ (IF)

Функция ЕСЛИ - это одна из самых популярных в Excel функций. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется IF. ЕСЛИ (IF) относится к логическим функциям.

Уровень сложности по шкале BRP ADVICE - 2 из 7 . Каждая вложенная ЕСЛИ (IF) увеличивает сложность формулы вдвое.

ЕСЛИ (IF) позволяет построить дерево решений, то есть при выполнении условия выполнять одно действие, а при невыполнении - другое. При этом условие должно быть вопросом, имеющим варианты ответа «да / нет» или «верно / неверно» (в терминах Excel, Google Sheets, LibreOffice, OpenOffice это «ИСТИНА / ЛОЖЬ» («TRUE / FALSE»).

Чтобы разобраться c функцией ЕСЛИ (IF), сначала надо разобраться с тем, что такое логические функции.

Что такое логические функции

В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах работа логических функций основана на существовании логических параметров. Логических параметров два: первый - ИСТИНА (TRUE), второй - ЛОЖЬ (FALSE).

На основе использования этих логических параметров можно построить дерево решений. В простейшем варианте этого дерева будет задан вопрос, ответом на который может быть ИСТИНА (TRUE) или ЛОЖЬ (FALSE), и дано указание, что делать в каждом из этих двух случаев. Схематически такое дерево решений изображено на рисунке ниже.

Рисунок. Простейшее дерево решений

Логические функции позволяют либо построить такое дерево решений, либо задавать вопрос и получать логический параметр. К первым относятся, например, ЕСЛИ (IF), ЕСЛИОШИБКА (IFERROR). Ко вторым - ЕЧИСЛО (ISNUMBER), И (AND), ИЛИ (OR).

Excel, Google Sheets, LibreOffice, OpenOffice и большинство других программных продуктов позволяет использовать логические параметры ИСТИНА (TRUE) и ЛОЖЬ (FALSE) при выполнении математических операций. Чаще всего, ИСТИНА (TRUE) принимает значение 1, ЛОЖЬ (FALSE) принимает значение 0. Хотя иногда ИСТИНА (TRUE) и ЛОЖЬ (FALSE) принимают другие значения, например, при программировании в VBA ИСТИНА (TRUE) - это -1, а не 1.

Кстати, логические параметры еще называют булевыми в честь английского математика и логика Джорджа Буля.

Функция ЕСЛИ (IF)

Итак, функция ЕСЛИ (IF) позволяет построить дерево решений. У этого дерева решений есть один вопрос на входе и два варианта действий. Вопрос обязательно имеет два варианта ответа: да / нет, верно / неверно или в терминах логических параметров ИСТИНА (TRUE) / ЛОЖЬ (FALSE).

Вопрос и два варианта действий - это и есть три аргумента функции ЕСЛИ (IF).

Первый аргумент функции ЕСЛИ (IF) - логический вопрос. В Excel он называется «лог_выражение». Excel, Google Sheets, LibreOffice, OpenOffice автоматически находят ответ на этот вопрос, и этот ответ должен принять значение ИСТИНА (TRUE) / ЛОЖЬ (FALSE). Что же может дать такой ответ? Самые простые варианты - это классические равенства и неравенства. Например, выражение 12=12 вернет логический параметр ИСТИНА (TRUE), а неравенство 12>40 вернет логический параметр ЛОЖЬ (FALSE).

В логическом вопросе можно использовать равенства (левая и правая часть сравниваются при помощи знака «=»), неравенства (больше - «>», меньше - «<», больше или равно - «>=», меньше или равно «<=»), а также просто не равно - «<>».

Более сложные логические вопросы можно задать с помощью вложенных функций. В результате вычисления таких вложенных функций должен получиться тот самый логический параметр ИСТИНА (TRUE) или ЛОЖЬ (FALSE). К таким функциям относятся, например, ЕЧИСЛО (ISNUMBER), ЕТЕКСТ (ISTEXT), ЕНД (ISNA), И (AND), ИЛИ (OR), в сложных случаях - еще одна ЕСЛИ (IF).

Второй и третий аргумент - это функция ЕСЛИ (IF) должна сделать, когда ответ на вопрос ИСТИНА (TRUE), а когда ЛОЖЬ (FALSE). Функция ЕСЛИ (IF) вычисляет либо только второй аргумент (если ИСТИНА (TRUE)), либо только третий аргумент (если ЛОЖЬ (FALSE)).

Рассмотрим примеры применения функции ЕСЛИ (IF) с одним или несколькими условиями.

Применение ЕСЛИ (IF) с одним условием

Файл-пример №1 вы можете скачать .

Предположим, в компании установлен план по продажам: каждый менеджер должен продать не менее чем на 1 миллион рублей в месяц. Оклад менеджера по продажам составляет 20 тысяч рублей. При выполнении плана менеджер получает оклад и премию 5% от фактического объема продаж. При невыполнении плана продаж - только оклад.

В конце каждого месяца формируется таблица, содержащая информацию о продажах каждого менеджера. Эта таблица может выглядеть, например, как на рисунке ниже.

Рисунок. Продажи в разрезе менеджеров по продажам за отчетный месяц

При помощи функции ЕСЛИ (IF) эту таблицу можно быстро превратить из простого набора данных о продажах за месяц в отчет, который будет показывать, кто план выполнил, кто нет, и какая будет зарплата у каждого из менеджеров. Такой отчет может выглядеть как на рисунке ниже.

Рисунок. Отчет по результатам работы менеджеров по продажам

Для того чтобы автоматически заполнять столбец «Выполнение плана» и «Зарплата за месяц, руб.» (столбцы E и F соответственно), можно использовать функцию ЕСЛИ (IF).

Пример 1.1 - подстановка текста при помощи ЕСЛИ (IF)

.

В столбце «Выполнение плана» в ячейке E4 используем вот такую формулу:

ЕСЛИ(D4>=1000000;"Молодец!";"План не выполнен:(")

IF(D4>=1000000;"Молодец!";"План не выполнен:(") .

Кстати, в некоторых версиях Excel, вместо ";" должна использоваться ",".

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

Что значат все аргументы ЕСЛИ (IF)?

1. Лог_выражение: D4>=1000000. В нашем примере логический вопрос - это сравнение фактического результата и плана продаж. D4 - это ссылка на ячейку с фактическими продажами этого менеджера. Excel, Google Sheets, LibreOffice, OpenOffice подставляют вместо D4 значение из этой ячейки и проверяют, верно ли указанное неравенство. В результате проверки в формуле получается промежуточный результат, он используется для выбора нужной ветки в дереве решений.

2. Значение_если_истина. На наших схемах это левая ветка дерева решений. В текущем примере значение аргумента - "Молодец!". Этот аргумент показывает, что должна сделать функция ЕСЛИ (IF), когда в результате вычисления первого аргумента получается ИСТИНА (TRUE). В текущем примере необходимо просто написать текст «Молодец!».

Кстати, "Молодец!" у нас написано в кавычках, потому что любой текст внутри формулы должен быть написан в кавычках. Исключением являются только названия функций и именованных диапазонов. В остальных случаях всегда ставьте текст в кавычки.

3. Значение_если_ложь. На наших схемах это правая ветка дерева решений. В текущем примере значение аргумента - "План не выполнен:(". Этот аргумент показывает, что должна сделать функция ЕСЛИ (IF), когда в результате вычисления первого аргумента получается ЛОЖЬ (FALSE). В текущем примере необходимо просто написать текст «План не выполнен:(».

Здесь мы также указали текст в кавычках, потому что, если не заключать текст внутри формулы в кавычки, возникнет ошибка #ИМЯ? (#NAME?). Исключение - только названия функций и именованных диапазонов.

Во-первых, функция ЕСЛИ (IF) отвечает на логический вопрос (вычисляет первый аргумент). Во-вторых, идет к соответствующей ветке дерева решений. По Александрову П.Ф. получается так:

1. D4>=1000000, следовательно проверяем 1000329>

2. Идем в аргумент Значение_если_истина. Нужно просто подставить текст «Молодец!». Указываем текст в ячейке. Конец расчетов.

Рисунок. Как работает функция ЕСЛИ (IF), когда логическое выражение возвращает ИСТИНА (TRUE)

1. D5>=1000000, следовательно проверяем 848880>

2. Идем в аргумент Значение_если_ложь. Нужно просто подставить текст «План не выполнен:(». Указываем текст в ячейке. Конец расчетов.

Схематически расчеты выглядят, как на рисунке ниже.

Рисунок. Как работает функция ЕСЛИ (IF), когда логическое выражение возвращает ЛОЖЬ (FALSE)

Пример 1.2 - вычисление разных формул при помощи ЕСЛИ (IF)

Файл-пример №1 вы можете скачать .

ЕСЛИ(D4>=1000000;20000+D4*5/100;20000)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(D4>=1000000;20000+D4*5/100;20000) .

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

Что именно делает функция ЕСЛИ (IF) в этом примере?

Функция ЕСЛИ (IF) отвечает на логический вопрос (вычисляет первый аргумент) и переходит к соответствующей ветке дерева решений. По Александрову П.Ф. получается так:

1. D4>=1000000, следовательно проверяем 1000329>=1000000, выражение верно, значит логический параметр - это ИСТИНА (TRUE).

2. Идем в аргумент Значение_если_истина. Нужно вычислить 20000+D4*5/100 (то есть оклад 20 тысяч и та самая премия 5% от продаж). Получаем 70016, указываем это значение в ячейке. Конец расчетов.

Аргумент Значение_если_ложь в этом случае функция ЕСЛИ (IF) игнорирует.

По Ильину М.А. получается так:

1. D5>=1000000, следовательно проверяем 848880>=1000000, выражение не верно, значит логический параметр - ЛОЖЬ (FALSE).

2. Идем в аргумент Значение_если_ложь. Нужно просто поставить 20000. Указываем число в ячейке. Конец расчетов.

Аргумент Значение_если_истина в этом случае функция ЕСЛИ (IF) игнорирует.

Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой "Начать заново" на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.

Применение ЕСЛИ (IF) с несколькими условиями

Пример 2 - разные условия в логическом выражении

Файл-пример №2 вы можете скачать .

В прошлом примере и у менеджеров, и у старших менеджеров был одинаковый план продаж на месяц. Усложним задачу: установим повышенный план старшим менеджерам - 1 миллион 200 тысяч в месяц. Отчет тогда будет выглядеть, как на рисунке ниже.

В этом случае в столбце «Выполнение плана» в ячейке E4 используем вот такую формулу:

ЕСЛИ(ЕСЛИ(C4="Старший менеджер";D4>=1200000;D4>=1000000);"Молодец!";"План не выполнен:(")

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);"Молодец!";"План не выполнен:(") .

Что именно делает функция ЕСЛИ (IF) в этом примере?

По Александрову П.Ф. получается так:

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Александров П.Ф. - это не старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ЛОЖЬ (FALSE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_ложь и сравнивает фактические продажи с планом менеджера по продажам. 1 000 329 больше 1 000 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ИСТИНА (TRUE).

По Ильину М.А. получается так:

1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Ильин М.А. - это не старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ЛОЖЬ (FALSE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_ложь и сравнивает фактические продажи с планом менеджера по продажам. 848 880 меньше 1 000 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ЛОЖЬ (FALSE).

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

По Незенецеву А.А. получается так:

1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Незенецев А.А. - это старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ИСТИНА (TRUE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_истина и сравнивает фактические продажи с планом старшего менеджера по продажам. 1 204 346 больше 1 200 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ИСТИНА (TRUE).

4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ИСТИНА (TRUE) и переходит к своему (а не к вложенному) аргументу Значение_если_истина. Этот аргумент - просто текст «Молодец!».

Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «Молодец!».

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

По Соколовой Н.И. получается так:

1. Функция ЕСЛИ (IF) начинает расчет с логического выражения и видит там вложенную функцию ЕСЛИ (IF). Excel, Google Sheets, LibreOffice, OpenOffice сначала считает вложенную функцию.

2. Вложенная функция ЕСЛИ (IF) проверяет логическое выражение: должность менеджера старший менеджер или нет. Соколова Н.И. - это старший менеджер. Поэтому логическое выражение во вложенной ЕСЛИ (IF) возвращает ИСТИНА (TRUE).

3. Вложенная функция ЕСЛИ (IF) переходит к аргументу Значение_если_истина и сравнивает фактические продажи с планом старшего менеджера по продажам. 1 046 625 меньше 1 200 000, поэтому вложенная ЕСЛИ (IF) возвращает логический параметр ЛОЖЬ (FALSE).

4. Результат вычисления вложенной функции ЕСЛИ (IF) передается в основную функцию. Основная функция ЕСЛИ (IF) видит логический параметр ЛОЖЬ (FALSE) и переходит к своему (а не к вложенному) аргументу Значение_если_ложь. Этот аргумент - просто текст «План не выполнен:(».

Формула с несколькими условиями, то есть с вложенными функциями ЕСЛИ (IF), возвращает в ячейку текст «План не выполнен:(».

Посмотрите построенное дерево решений на схеме ниже.

Рисунок. Дерево решений для функции ЕСЛИ (IF) с несколькими условиями

Формула для расчета заработной платы в примере 3

В столбце «Зарплата за месяц, руб.» в ячейке F4 используем вот такую формулу:

ЕСЛИ(ЕСЛИ(C4="Старший менеджер";D4>=1200000;D4>=1000000);20000+D4*5/100;20000)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);20000+D4*5/100;20000) .

Не забудьте, в некоторых версиях Excel, вместо ";" должна использоваться ",".

В этом случае функция ЕСЛИ (IF) работает точно так же, как и в ячейке E4.

Пример 4 - разные условия и в логическом выражении, и в ветках дерева решений

Файл-пример №3 вы можете скачать .

Итак, у нас есть менеджеры, есть старшие менеджеры. У старших менеджеров план выше, чем у обычных менеджеров. Для того чтобы такая модель работала, часто необходимо дополнительное стимулирование для старших менеджеров. Например, премия старшего менеджера повышается до 6%. То есть у нас сразу несколько условий:

1. Премия выплачивается только если выполнен план.

2. Если должность старший менеджер, план - 1 миллион 200 тысяч, иначе - 1 миллион.

3. Если должность старший менеджер, премия - 6%, иначе - 5%.

В итоге получается отчет, как на рисунке ниже.

Рисунок. Отчет по результатам работы менеджеров и старших менеджеров

Как решить такую задачу при помощи функции ЕСЛИ (IF)?

В ячейке F4 можно написать такую формулу:

ЕСЛИ(C4="Старший менеджер";D4>=1200000;D4>=1000000);

20000+D4*ЕСЛИ(C4="Старший менеджер";6;5)/100;

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

IF(C4="Старший менеджер";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Старший менеджер";6;5)/100;

Не забывайте, в некоторых версиях Excel, вместо ";" должна использоваться ",".

На рисунке ниже схематически изображено построенное дерево решений.

Рисунок. Пример дерева решений с несколькими условиями и в логическом выражении, и в других аргументах функции ЕСЛИ (IF)

Частые ошибки при работе с функцией ЕСЛИ (IF)

1. Для функции ЕСЛИ (IF) всегда должен быть указан первый аргумент - логическое выражение и второй аргумент - значение если истина. Третий аргумент необязательный. Пользователи часто забывают указать третий аргумент особенно при работе со сложными формулами, из-за этого в некоторых случаях вместо нужного результата в ячейке появляется логический параметр ЛОЖЬ (FALSE).

2. Сложность формулы очень быстро растет при использовании вложенных ЕСЛИ (IF). Из-за этого очень часто пользователи забывают закрыть скобки вложенных вычислений, не ставят разделитель аргументов («;» или «,»). В зависимости от ошибки формулу в ячейку либо не удается записать, либо она считается неправильно.

3. В сложных формулах с ЕСЛИ (IF) очень тяжело отслеживать правильность расчетов: каждая вложенная функция ЕСЛИ (IF) добавляет в ваше дерево решений один вопрос и минимум две ветки. В среднем человек в уме держит до 7 объектов, получается, что при трех вложенных ЕСЛИ (IF) в уме нужно держать 3 вопроса и 6 веток дерева решений. Контролируемость и надежность формулы стремительно снижается.

Как избежать этих ошибок при работе с функцией ЕСЛИ (IF)? Минимизируйте использование ЕСЛИ (IF) с другими функциями и особенно с вложенными ЕСЛИ (IF). Лучше делайте промежуточные расчеты в соседних ячейках.

Совет: работа со сложными формулами

Часто нам приходится работать со сложными формулами, формулами в которых в одну функцию вложены другие. Как не ошибиться при создании такой формулы? Действуете по следующем алгоритму:

1. Определите конечную цель ваших расчетов: какой результат вы должны получить в итоге.

2. Определите функцию, которая позволяет это сделать.

3. Начинайте создание формулы с этой функции, укажите ее и переходите к работе с аргументами.

5. Если вам необходимо проделать промежуточные вычисления, то определяете конечную цель этих вычислений, функцию и так далее. Обычно, задача промежуточных вычислений - это получить аргумент для основной функции. Помните об этом, так как иногда нужно получить аргумент определенного типа (именно текст, именно число, именно логический параметр или что-то иное).

6. Всегда следите за скобками: как только вы закончили описание функции, закрывайте скобки.

И помните, если формула слишком сложная, лучше сделать промежуточный расчет в соседней ячейке.

Чем дополнить и заменить функцию ЕСЛИ (IF)

Вместо констант в формуле можно использовать именованные диапазоны.

Решение задачи с несколькими условиями можно значительно упростить с помощью использования вложенных функций И (AND), ИЛИ (OR).

Функция ЕСЛИ (IF) иногда может быть заменена на функцию ВПР (VLOOKUP) , ГПР (HLOOKUP), ПРОСМОТР(LOOKUP), ЕСЛИОШИБКА (IFERROR), СУММЕСЛИ (SUMIF) или СЧЁТЕСЛИ (COUNTIF).

Файл-пример №1 "Применение функции ЕСЛИ (IF) с одним условием" вы можете скачать .

Файл-пример №2 "Применение функции ЕСЛИ (IF) с несколькими условиями" .

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel .

Функция ЕСЛИ() , английский вариант 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 (см. файл примера)

Формат записи: =ЕСЛИ (условие; выражение В; выражение С)

Эта запись означает:

1) если условие выполняется, то происходит действие, определенное в выражении В;

2) если условие не выполняется, то происходит действие, оп­ределенное в выражении С.

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

Пример. Пусть у ряда работников имеется задолженность по потребительскому кредиту, которая отражена в диапазоне СЗ:С7 (рис. 9.2). Нужно найти в списке таких работников и удержать с них в счет погашения кредита 10 % от начисленной им сум­мы.

Рис. 9.2. Пример простой логической функции ЕСЛИ

Для нашего примера логическая функция будет иметь следующий вид:

=ЕСЛИ (С3>0; ВЗ *0,1; " ").

Данная логическая функция означает следующее: если задол­женность по потребительскому кредиту больше нуля, то необхо­димо удержать 10 % с начисленной суммы, в противном случае необходимо вывести пробелы.

По диапазону СЗ:С7 отыскиваются работники, у которых есть задолженность по потребительскому кредиту, а в диапазоне ЕЗ:Е7 указывается размер удержанной суммы. В случаях, когда задол­женности по потребительскому кредиту нет, напротив фамилий соответствующих работников в диапазоне ЕЗ:Е7 выводятся пробелы.

В результате в диапазоне ЕЗ:Е7 (рис. 9.3) получим размер удержанной суммы с работников, у которых имеются задолжен­ности по потребительскому кредиту.

Рис. 9 3. Результат выполнения простой логической функции ЕСЛИ

ЛОГИЧЕСКАЯ ФУНКЦИЯ И

Логическая функция И обеспечивает проверку одновременного выполнения связанных этой функцией условий. При выполнении всех условий функция принимает значение ИСТИНА, а если хотя бы одно из условий не выполняется, функция принимает значение ЛОЖЬ.

Формат записи: И (условие #1; условие #2)

Результат является истинным, если два условия истинны. В противном случае результат ложен:

Функция И входит в состав функции ЕСЛИ , которая в этом случае имеет следующий формат:

=ЕСЛИ(И(усл.#1;усл.#2); выражение В; выражение С)

Пример . Найти работников, у которых одновременно име­ются задолженности по потребительскому кредиту и кредиту на жилищное строительство, и удержать от начисленной им суммы 20 % (рис. 9.4).

Рис. 9.4. Логическая функция И в сочетании с функцией ЕСЛИ

Для нашего примера логическая функция будет иметь следу­ющий вид:

=ЕСЛИ (И (C3>0;D3>0); В3*0,2; " ")

Данная логическая функция означает следующее: если одно­временно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удер­жать 20 % с начисленной суммы, в противном случае необходимо вывести пробелы.

Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.

В столбцах С и D (рис. 9.5) будет найден только один работник, у которого есть задолженность по двум видам кредита. В столбце Е у него будет удержано 20 % от начисленной суммы. Напротив остальных работников в столбце Е будут выведены пробелы.

Рис.9.5. Результат выполнения логической функции И в сочетании с функцией ЕСЛИ

ЛОГИЧЕСКАЯ ФУНКЦИЯ ИЛИ

Логическая функция ИЛИ обеспечивает проверку выполнения хотя бы одного условия, из всех условий, связанных этой функцией. В этой ситуации функция принимает значение ИСТИНА, а если ни одно условие не выполняется, функция принимает значение ЛОЖЬ.

Данная функция истинна, если истинно хотя бы одно из двух входящих в нее условий. Формат записи: = ИЛИ (условие #1; условие #2)

Лишь в случае, когда оба условия ложны, функция ИЛИ также ложна:

Данная функция используется вместе с логической функцией ЕСЛИ, которая в этом случае имеет следующий формат:

=ЕСЛИ(ИЛИ(усл.#1;усл.#2); выражение В; выражение С)

Пример. Найти работников, у которых имеется задолжен­ность либо по потребительскому кредиту, либо по кредиту на жи­лищное строительство, либо по обоим видам кредита сразу, и удержать с них в счет погашения кредита 10 % от начисленной им сум­мы (рис. 9.6).

Рис. 9.6. Логическая функция ИЛИ в сочетании с функцией ЕСЛИ

Логическая функция в нашем примере будет иметь вид

=ЕСЛИ (ИЛИ (С3>0; D3>0); В3*0,1; " ")

Данная логическая функция означает следующее: если одно­временно или по отдельности задолженности по потребительско­му кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 10 % с начисленной суммы, в противном случае необходимо вывести пробелы.

Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.

В столбцах С и D (рис. 9.7) будут найдены работники, у кото­рых есть задолженность хотя бы по одному виду кредита, и в столбце Е с них будет удержано 10 % от начисленной им суммы. Один работник не имеет задолженности по кредиту, тогда в столбце Е напротив его фамилии будут выведены пробелы.

Рис.9.7 . Результат выполнения логической функции ИЛИ
в сочетании с функцией ЕСЛИ

ВЛОЖЕННЫЕ ЛОГИЧЕСКИЕ ФУНКЦИИ ЕСЛИ

Формат записи:

=ЕСЛИ(усл.#1; выражение В; ЕСЛИ(yсл.#2; выражение С; ЕСЛИ (...)))

Число вложенных функций в принципе может быть любое, но общая длина строки не должна быть слишком большой, это за­трудняет ее чтение; кроме того, необходимо следить за тем, чтобы число открытых скобок в точности равнялось числу закрытых скобок.

Пример. Найти работников, у которых имеются одновре­менно задолженности по обоим видам кредита, и удержать от на­численной им суммы 20 % в счет погашения кредитов (рис. 9.8). С остальных работников, имеющих задолженность по какому-ли­бо одному виду кредита, удержать 10 % от начисленной им суммы. Работникам, не имеющим задолженности по кредиту, проставить в графе «Удержано» – «б/к».

В нашем примере логическая функция будет иметь такой вид:

=ЕСЛИ (И (C3>0; D3 >0); В3*0,2; ЕСЛИ (И(C3=0; D3=0); "б/к"; В3*0,1))

Данная логическая функция означает следующее: если одно­временно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удер­жать 20 % с начисленной суммы, если обе задолженности одновре­менно равны нулю, то необходимо вывести «б/к», в противном случае необходимо удержать 10 % от начисленной суммы.

Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7 (рис. 9.8).

В столбцах С и D (рис. 9.9) будут найдены работники, у кото­рых есть задолженности по двум видам кредита. С них будет удер­жано 20 % от начисленных им сумм. Напротив фамилии работни­ка, у которого нет задолженности по кредиту, в столбце Е будет выведено «б/к». Наконец, с остальных работников будет удержа­но 10 % от начисленных им сумм.

Рис.9.8. Вложенная логическая функция ЕСЛИ

Рис. 9.9 . Результат выполнения вложенной логической функции ЕСЛИ

Логическая функция НЕ преобразует уже имеющееся логическое значение и принимает значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и наоборот.

Логическая функция ЕСЛИОШИБКА возвращает значение ЕСЛИ_ОШИБКА, если выражение ошибочно. В противном случае функция возвращает само выражение.

Логический оператор ЕСЛИ в 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» означает команду поиска точных (а не приблизительных) значений.

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

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

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