Как прописать функцию «если» с несколькими условиями в программе excel? функция если в excel. примеры (с несколькими условиями)

Как работает функция?

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

С одним условием

Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
3. Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
4. Нажать «Enter» – и появится результат поиска.

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

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

С несколькими условиями

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

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

1. В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».
2. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

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

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.
2. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.
3. При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Для создания раскрывающегося списка:

1. Поставить курсор в ячейку, где он будет располагаться.
2. Перейти в раздел книги «Данные» – «Проверка данных».
3. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).
4. Нажать «Ок». Отобразится список.
5. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)
2. Протянуть формулу вниз, чтобы заполнить все ячейки.

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

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И), только одно условие должно иметь такое значение (ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.

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

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; ))

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Общие сведения об использовании этих функций по отдельности см. в следующих статьях: И, ИЛИ, НЕ. При сочетании с оператором ЕСЛИ они расшифровываются следующим образом:

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.

Ниже приведены формулы с расшифровкой их логики.

=ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах

Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

=ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Сравнение данных в двух таблицах

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

Для сравнения двух таблиц используется функция СЧЕТЕСЛИ. Давайте рассмотрим ее более подробно.

Допустим, у нас есть две таблицы, содержащие технические характеристики двух кухонных комбайнов. И нам нужно их сравнить, а отличия выделять цветом. Это можно сделать с помощью условного форматирования и функции СЧЕТЕСЛИ. 

Наша таблица выглядит так.

7

Выделяем диапазон, соответствующий техническим характеристикам первого кухонного комбайна.

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

8

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

9

Нужно убедиться, что адреса абсолютные (со значком доллара перед названием строки и колонки). После формулы нужно добавить =0, чтобы Excel искал точные значения.

После этого нужно задать форматирование ячеек. Для этого возле образца нужно нажать на кнопку «Формат». В нашем случае мы используем заливку, поскольку она наиболее удобна для этих целей. Но вы можете выбрать любое форматирование, которое хотите.

10

Мы в качестве диапазона назначили имя столбца. Это значительно удобнее, чем прописывать диапазон вручную.

Как сравнить две ячейки в Excel.

Объединяем несколько условий.

Заполняем аргументы функции

Оформление и примеры использования

Алгоритм написания логических формул в Эксель следующий:

1. Нужно выделить пустую ячейку, в которую будет записываться формула и выводиться результат действия.Вписывать можно и в строке формул, после выделения ячейки.
2. Перед формулами в программе ставится знак «=». Поставить его.
3. Напечатать название оператора.
4. После этого вписываются аргументы, если они есть. Начинается запись со знака «открывающаяся круглая скобка “(“».
5. Аргументы вводятся последовательно через знак ”;”. Также, если после ввода названия функции нажать клавиши Ctrl + A, то откроется меню аргументов и вписать их можно здесь.
6. В конце ставится символ «закрывающаяся круглая скобка “)”». Контролировать написание можно в строке формул.
7. После завершения нажать кнопку ENTER. Результат появится в ячейке.

Работа с ПЕРЕКЛЮЧ

Сравнивает указанную величину в ячейке или формулу со списком данных и вписывает в ячейку первое совпавшее значение. Если совпадений не будет, и не проставлена величина по умолчанию, оператор выдаст ошибку «#Н/Д». Функция схожа с ЕСЛИМН, но в отличие от нее условие ставится точно, без сравнительных знаков.

Работа оператора иллюстрируется на рисунке.

Здесь вместо чисел 1, 2, 7 — нужно проставить прописью дни недели им соответствующие. Если будут другие цифры, то возвратится значение по умолчанию «Нет совпадений (No match)».

Использование ЕСЛИОШИБКА

Оператор используется для нахождения ошибки в таблице. Найдя ее, функция не пишет в ячейке какую-либо из ошибок, а возвращает указанный ответ, который может быть текстом, пустой строкой: =ЕСЛИОШИБКА(Что_проверять;Что_выводить_вместо_ошибки).

Например, нужно поделить значения в столбце А на величины в столбце В. Если по ошибке в строках стоят 0, то получится деление на 0.

Применение оператора «=ЕСЛИОШИБКА(A2/B2;»»)» скрывает ошибки.

Здесь сравнивается выражение A2/B2. В случае обнаружения ошибки в ячейку ставится пустая строка, указанная пробелом в кавычках ““.

ЕСЛИОШИБКА появилась в Excel 2007. До этого использовалась функция ЕОШИБКА, которая самостоятельно не могла обработать ошибку, так как имела только один аргумент, проверяющий указанную ячейку. Для ввода ответа в случае обнаружения ошибки, нужно было использовать оператор ЕСЛИ: «ЕСЛИ(ЕОШИБКА(А2/В2);”“;А2/В2)».

И/ИЛИ

Простые операторы, редко применяются без связки с другими функциями.

На рисунке показан принцип действия функции И.

Пример использования: «=И(A1>B1; A2<>25)». Здесь созданы два условия:

1. Значение в ячейке А1 должно быть больше числа в В1.
2. Число в А2 должно быть не равно 25.

При исполнении обоих получается ИСТИНА.

Если одно из заданий нарушено, получается ЛОЖЬ. В данном случае число в А1 меньше чем в В1.

Ниже представлен алгоритм функционирования оператора ИЛИ.

Пусть даны 3 выражения: A1>B1; A2>B2; A3>B3. Требуется применить к ним действие ИЛИ: «=ИЛИ(A1>B1; A2>B2; A3>B3)». Возможные варианты показаны на рисунках:

Здесь конечный результат ИСТИНА, так как из трех выражений одно верно: A3>B3. На следующем изображении функция выдала ответ «ЛОЖЬ», так как на все вопросы получены аналогичные ответы.

Общее определение и задачи

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; ; )» .

Пример использования «ЕСЛИ»

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

1. Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
2. Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».

Так мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ».

Пример функции с несколькими условиями

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

1. Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
2. Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))» .
3. Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».

Как и в прошлый раз, «протягиваем» формулу вниз.

Пример с выполнением двух условий одновременно

В функции «ЕСЛИ» можно также использовать оператор «И», который позволяет считать истинной только выполнение двух или нескольких условий одновременно.

1. Например, в нашей ситуации премия к 8 марта в размере 1000 рублей выдается только женщинам, которые являются основным персоналом, а мужчины и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной персонал»); «1000»; «0»)» . Вставляем ее в ячейку.

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

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

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

1. Итак, предположим, что премия к 8 марта в 1000 рублей положена только женщинам, которые входят в число основного персонала. В этом случае, если работник — мужчина или относится к вспомогательному персоналу, то величина его премии будет равна нулю, а иначе – 1000 рублей. В виде формулы это выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»; C6=»Вспомогательный персонал»); «0»; «1000»)» . Записываем ее в соответствующую ячейку таблицы.

«Протягиваем» результаты вниз.

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

Как перевернуть текст в excel

Excel значение ячейки несколько условий • Вэб-шпаргалка для интернет предпринимателей!

​Определяет, выполняется ли следующее​,​ человек, а на​ и ее можно​ число основного персонала.​ к 8 марту.​ которым пользователи прибегают​ такое количество ячеек,​ непечатаемых знаков.​ должны быть в​ больше 1 600 000​ одному любому символу,​ только значения, превышающие​ покупателем позиции.​ В4 написали такую​ в тестах, т.д.​ A2 не равно​ условие: значение в​ИЛИ​ оценки хорошо и​ применять в различных​

Баллы	Оценка	Условие
0 — 63	Неуд.	< 64
64 — 72	Удовл.	< 73
73 — 89	Хорошо	< 90
90 — 100	Отлично

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

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

Для удобства вынесем все условия в отдельную таблицу, чтобы при необходимости мы могли быстро их менять если, например, поменяется оплата или план

Нам требуется заполнить столбец «Оплата». У нас несколько условий, поэтому одним если обойтись не получится. Вот как мы будет рассматривать эти условия

• если продаж больше 20, то считаем оплату за перевыполнение, иначе проверяем
• если продаж меньше 18, то считаем оплату за невыполнение, иначе было от 18 до 20 продаж и сумма выплаты составляет 2000 рублей

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

Итак, вставьте курсор в ячейку C3 и нажмите на значок выбора функций, перейдите в категорию «Логические» и выберите функцию ЕСЛИ и нажмите «ОК» (см. на рисунок)

Поехали дальше, переходим ко второму аргументу функции.

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

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

После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Не надо паниковать предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Это сделано для удобство.

Заполняем поле лог_выражение, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3

Отлично. Если план перевыполнен, то мы посчитали оплату, если не выполнен, мы проверяем сотрудника на невыполнения плана. Если он не выполнил план, то мы считаем ему оплату. Если же он и не перевыполнил план и не недовыполнил план, то значил от сделал ровно план от 18 до 20 шт.

Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку — $F$4

Нажимаем «Ок» — формула готова. Вот так выглядит полная формула:

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

Как поставить ноль перед числом в Экселе советы от Excelpedia
Во-первых, если у вас несколько наборов скобок, круглые скобки имеют цветовую кодировку, поэтому открывающие скобки соответствуют закрывающим скобкам. Эти цвета нелегко рассмотреть, но при желании — можно:

после статьи можно прочитать PDF файл со стандартной справкой Excel по функции ЕСЛИ.

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

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И), только одно условие должно иметь такое значение (ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.

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

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; ))

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Общие сведения об использовании этих функций по отдельности см. в следующих статьях: И, ИЛИ, НЕ. При сочетании с оператором ЕСЛИ они расшифровываются следующим образом:

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.

Ниже приведены формулы с расшифровкой их логики.

=ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ

Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

=ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

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

Вложенные ЕСЛИ в Excel. Пример 1

Усложним задачу, теперь найдем таких сотрудников, по которым выручка больше 50 тыс. руб. и меньше 70 тыс. руб.

Частично эту задачу мы уже сделали в предыдущем примере. Действительно, мы нашли сотрудников у которых выручка больше 50 тыс. руб., поэтому сейчас нам достаточно определить только тех из них, кто принес меньше 70 тыс. руб.

Также прописываем функцию ЕСЛИ, делаем проверку что выручка больше 50 тыс. руб. ($B5>$D$1), если выполнено, то добавляем еще одну функцию ЕСЛИ где делаем проверку что выручка уже меньше 70 тыс. руб. ($B$5<$D$2).В случае выполнения всех условий пишем «Да», в иных случаях оставляем пустое поле:

Как видим количество «Да» у нас сократилось, что логично так как мы несколько сузили наше исходное условие по проверке.

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

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

Логическая функция И в Excel

Для альтернативного решения этого примера познакомимся с логической функцией И (AND в английской версии), которая позволяет проверить одновременную истинность сразу нескольких выражений:

Возвращаясь к нашему примеру нам как раз нужно проверить одновременное выполнение двух условий: выручка больше 50 тыс. руб. (условие 1) и выручка меньше 70 тыс. руб. (условие 2).

В итоге оба условия проверяем с помощью функции ЕСЛИ и логического выражения в виде формулы И($B5>$E$1;$B5<$E$2), и возвращаем «Да» в случае истины:

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

Вложенные ЕСЛИ в Excel. Пример 2

Предлагаю закрепить материал и решить прямо противоположную предыдущему примеру задачу — найдем сотрудников по которым выручка меньше 50 тыс. руб. (при этом давайте в этом случае вернем значение «Плохо) или больше 70 тыс. руб. (а тут уже значение «Отлично»).

Также воспользуемся вложенным ЕСЛИ и поочередно проверим несколько условий, к примеру, сначала условие на выручку меньше 50 тыс. руб. ($B5<$F$1), в случае истинности возвращаем «Плохо», в иных случаях добавляем еще одну проверку на выручку уже больше 70 тыс. руб. ($B5>$F$2) и пишем значение «Отлично»:

Но мы бы не разбирали этот пример если бы все было так просто

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

Логическая функция ИЛИ в Excel

Как не трудно догадаться, если мы разобрали функцию И, то где-то рядом обязательно будет и другая очень полезная функция — логическая функция ИЛИ (OR в английской версии), которая позволяет проверить истинность хотя бы одного из выражений (в отличии от И, где проверяется истинность всех значений):

Возвращаемся к задаче, в нашем примере должно выполняться одно из двух условий: либо выручка меньше 50 тыс. руб. (условие 1), либо больше 70 тыс. руб. (условие 2). Соответственно с помощью функции ЕСЛИ и логического выражения в виде формулы ИЛИ($B5<$G$1;$B5>$G$2) проверяем выполнение условий, и возвращаем «Да» в случае истины:

Результат в целом совпал с предыдущим вариантом, однако такой же вариативности возвращаемых значений, к сожалению, у нас достичь уже не получится из-за особенностей функции ИЛИ, т.е. вместо «Плохо» и «Отлично» мы получили только 1 вариант ответа «Да».

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

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

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

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

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Функции программы Microsoft Excel: подбор параметра

Как используется функция ЕСЛИ в Excel с несколькими условиями

       В этой статье, я хотел бы поговорить, о самой любимой функции любого экономиста — функция ЕСЛИ в Excel. Эта функция является представителем логических функций во встроенном редакторе функций и там же её можно найти или заглянув в раздел “10 самых используемых функций”. Для более опытных пользователей эту формулу можно вызвать начав вычисления в ячейке, при вводе знака “=” и начале ввода названия функции, Excel моментально предложит все допустимые варианты с которых вы сможете выбрать нужную вам функцию.

       Синтаксис рассматриваемой функции достаточно прост, но саму ее суть нужно уловить так как это позволит ее еффективно использовать в различных ситуациях:

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

• лог_выражение — это любое выражение, значение, формула или любое условие результат которого будет значение ИСТИНА или ЛОЖЬ, необходимые для дальнейшего выполнения условий;
• значение_если_истина — указываются какие условия будут выполнятся если результат «логического выражения» будут соответствовать значению ИСТИНА;
• значение_если_ложь — критерий который будет выполнятся при условии получения результата ЛОЖЬ если условия не будут выполнены.

       Итак, чем же так хороша эта логическая формула, а тем что в неё можно вложить восем аналогичных логических цепочек, то есть несколько формул ЕСЛИ, вот примеры:

1. Простой вариант:

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

2. Сложный вариант:

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

       Вы можете вместо расчёта баллов использовать любые условия и вычисления, которые требуют логического сравнения и вычисления. Например, вычисление ставки заработной платы по категориям, при нормировке штатной численности, вычисление групп амортизации основных средств, да и много другого, где можно встретить градиент значений или условий используя несколько ЕСЛИ в формуле Excel.     Программа также предоставляет разные вариации функции ЕСЛИ и походные от неё совмещение функций, это: СУММЕСЛИ, СУММЕСЛИМН, СЧЕТЕСЛИ, но о них поговорим в других статьях. И в общем можно утверждать что логические функции в Excel одни из самых полезных для использования в бухгалтерском и экономическом расчётах. Также вам пригодится статья «3 главные функции Excel для экономистов и бухгалтеров» или «Секреты Excel, 10 лучших и полезных!».   

     Не забывайте что функция ЕСЛИ, должна быть у каждого экономиста на вооружении и ваши вычисления приобретут быстроту, точность, элегантность и большую вариативность! С другими полезными функциями вы можете ознакомится в «Справочнике функций».      Успехов вам!

Тот, кто живет по средствам, страдает от недостатка воображенияАндре Стендер, писатель

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Выводы

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

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

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

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

Оцените качество статьи

Нам важно ваше мнение: