" была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях - вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции - И() и ИЛИ().
Функция И()
Функция И()
Условие 1
И
Условие 2
. При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.
Функция ИЛИ()
Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом - Условие 1 ИЛИ Условие 2 . Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.
Примеры использования функции И и ИЛИ
Все вышесказанное сложно к пониманию и относится к разделу Мат. логики и Дискретной математики. Попробую это все изложить на понятном языке. Разберем несколько примеров. Скажу сразу, все примеры будут с использованием функции ЕСЛИ.
Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В - 52, 55, 88, 4, 25. Столбец С - 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.
Переходим в ячейку D1 и с помощью мастера функций вводим следующее
Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);"Все значения равны";"Значения не равны") “
Результат работы:
Пример 2.
Теперь пример на функцию ИЛИ. Те же столбцы, те же значения, условие следующее: с помощью функций ЕСЛИ и ИЛИ необходимо определить строки, в которых есть совпадающие значения А=В или В=С или А=С.
Мастер функций выглядит так:
Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);"Есть равные значения";"Нет равных значений")”
Результат:
Вот и все. Функции не сложны. Применяйте их там, где встречаются условия и произносится И и ИЛИ. Так же, всеми выше перечисленными функциями можно сравнивать текстовые значения.
И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.
Пример 3.
В таблицу занесены процент выполнения заданий, необходимо проценты перевести в пятибалльную систему следующим образом:
1 – от 0% до 10%
2- от 11% до 30%
3- от 31% до 40%
4- от 41% до 75%
5-от 76% до 100%
Таблица следующего вида.
Для того чтобы понять как пользоваться этой программой, необходимо рассмотреть формулы EXCEL с примерами.
Если поставить курсор мышки на любую ячейку и нажать на пункт «выбрать функцию», то появляется мастер функций.
С его помощью можно найти необходимую формулу максимально быстро. Для этого можно ввести ее название, воспользоваться категорией.
Программа Excel очень удобна и проста в использовании. Все функции разделены по категориям. Если категория необходимой функции известна, то ее отбор осуществляется по ней.
В случае если функция неизвестна пользователю, то он может установить категорию «полный алфавитный перечень».
Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.
Функция ВПР
С помощью функции ВПР можно извлечь необходимую информацию из таблиц. Сущность вертикального просмотра заключается в поиске значения в крайнем левом столбце заданного диапазона.
После чего осуществляется возврат итогового значения из ячейки, которая располагается на пересечении выбранной строчки и столбца.
Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий . Задача – по предложенному номеру найти фамилию.
Применение функции ВПР
Формула показывает, что первым аргументом функции является ячейка С1.
Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.
Третий аргумент – это порядковый номер столбца, из которого следует возвратить результат.
Вычисление заданной фамилии с помощью функции ВПР
Кроме того, выполнить поиск фамилии можно даже в том случае, если некоторые порядковые номера пропущены.
Если попробовать найти фамилию из несуществующего номера, то формула не выдаст ошибку, а даст правильный результат.
Поиск фамилии с пропущенными номерами
Объясняется такое явление тем, что функция ВПР обладает четвертым аргументом, с помощью которого можно задать интервальный просмотр.
Он имеет только два значения – «ложь» или «истина». Если аргумент не задается, то он устанавливается по умолчанию в позиции «истина».
Округление чисел с помощью функций
Функции программы позволяют произвести точное округление любого дробного числа в большую или меньшую сторону.
А полученное значение можно использовать при расчетах в других формулах.
Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.
Первый аргумент – 76,375, а второй – 0.
Округление числа с помощью формулы
В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».
Округление происходит до целого числа. В нашем случае до 77 или 76.
В программе Excel помогают упростить любые вычисления. С помощью электронной таблицы можно выполнить задания по высшей математике.
Наиболее активно программу используют проектировщики, предприниматели, а также студенты.
Вся правда о формулах программы Microsoft Excel 2007
Формулы EXCEL с примерами - Инструкция по применению
Откроется диалоговое окно функции если.
Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 — кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2
Для того, чтобы в дальнейшем применить данную формулу для других сотрудников, протянув ее вниз, нам необходимо закрепить ссылку в ячейке G2, чтобы она стало абсолютной (то есть не менялась, когда мы будет протягивать формулу). Для этого необходимо прописать знаки доллара перед буквой и цифрой или только перед цифрой, так как мы не будет протягивать формулу влево или вправо. Удобнее всего зафиксировать ячейку просто выделив в формуле нужную ячейку и нажав клавишу F4
Должно получиться вот так B3>$G$2 или так B3>G$2
Обратите внимание, Excel автоматически вычисляет выражение и отображает его справа. В нашем случае Excel вычислил, что выражение — ИСТИНА, то есть действительно Алексей сделал продаж в количестве 35 шт, что больше верхней границе плана 20 шт. Обратите внимание, что если бы у Алексея было бы недовыполнение плана, то формулу мы бы все равно писали точно так же — гипотетически рассуждая, как если бы у Алексея был бы выполнен план.
Значение_если_истина . Переведите курс в это поле. Тут нам необходимо рассчитать оплату сотруднику, если он выполнил план. Стандартная оплата 2000 (F4) плюс так как мы рассматриваем ситуацию когда план перевыполнен, нужно прибавить переработку. Для этого вычтем из всего продаж план и получим количество продаж сверх нормы (B3-G2) и умножим их на оплату за перевыполнение 25 рублей (F5). В итоге получаем следующий расчет 2000+(35-20)*25 то есть F4+(B3-G2)*F5
так же не забываем, что данные из таблицы условий нам нужно закрепить, чтобы они сдвигались при протягивании формулы. Для этого выделяем нужные значения и нажимаем F4
Получаем итоговый расчет: $F$4+(B3-$G$2)*$F$5
Переходим к следующему полю.
Значение_если_ложь. Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно — возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.
В этом поле можно написать вложенную функцию ЕСЛИ вручную, но это не очень удобно, поэтому мы снова воспользуемся диалоговыми окнами. Перейдите в ячейку Значение_если_ложь откройте раскрывающийся список последних использованных функции в правом углу и выберите ЕСЛИ
После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Не надо паниковать предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Это сделано для удобство.
Заполняем поле лог_выражение
, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3 Далее поле Значение_если_истина
. То есть если действительно B3 Получаем следующую формулу: $F$4-($F$2-B3)*$F$6 Отлично. Если план перевыполнен, то мы посчитали оплату, если не выполнен, мы проверяем сотрудника на невыполнения плана. Если он не выполнил план, то мы считаем ему оплату. Если же он и не перевыполнил план и не недовыполнил план, то значил от сделал ровно план от 18 до 20 шт. Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку - $F$4 Вот так будет выглядеть вложенное ЕСЛИ Нажимаем «Ок» — формула готова. Вот так выглядит полная формула: ЕСЛИ(B3>$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3<$F$2;$F$4+($F$2-B3)*$F$6;$F$4)) С помощью диалоговых окон очень просто пользоваться функцией ЕСЛИ и при необходимости не запутаться с вложенными ЕСЛИ, главное изначально подумать о последовательности проверки ЕСЛИ. АРГУМЕНТЫ:
КОММЕНТАРИИ:
ФОРМУЛЫ МАССИВА:
ЭТОГО НЕТ В ОФИЦИАЛЬНОЙ СПРАВКЕ EXCEL:
В КАКОЙ ВЕРСИИ EXCEL РАБОТАЕТ:
Здравствуйте, друзья! Часто ли вам приходится делать выбор? Например, захотели купить новый телефон, а полной суммы денег у вас нет. Взять кредит или копить? Так сегодня разберем как делать выбор в электронных таблицах Excel. Это позволяет сделать условная функция ЕСЛИ(). В этой функции проверяется истинность одного или нескольких логических выражений и выбирается дальнейшее действие. В логических выражениях используют знаки операций отношения между сравниваемыми величинами. Что же делает функция ЕСЛИ()? Посмотрите на схему. Здесь приведен простой пример работы функции при определении знака числа а
. Условие а>=0
определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное. Ниже схемы приведена запись формулы в Excel. После условия через точку с запятой перечисляются варианты действий. В случае истинности условия, в ячейке отобразится текст "неотрицательное", иначе - "отрицательное". То есть запись, соответствующая ветви схемы «Да», а следом – «Нет». Текстовые данные в формуле заключаются в кавычки, а формулы и числа записывают без них. Если результатом должны быть данные, полученные в результате вычислений, то смотрим следующий пример. Выполним увеличение неотрицательного числа на 10, а отрицательное оставим без изменений. На схеме видно, что при выполнении условия число увеличивается на десять, и в формуле Excel записывается расчетное выражение А1+10
(выделено зеленым цветом). В противном случае число не меняется, и здесь расчетное выражение состоит только из обозначения самого числа А1
(выделено красным цветом). Это была краткая вводная часть для начинающих, которые только начали постигать азы Excel. А теперь давайте рассмотрим более серьезный пример с использованием условной функции. Задание:
Решение:
Решение данной задачи видно на рисунке ниже. Но внесем все-таки ясность в эту иллюстрацию. Основные исходные данные для решения этой задачи находятся в столбцах А и В. В ячейке А5 указано пограничное значение дохода при котором изменяется ставка налогообложения. Соответствующие ставки указаны в ячейках В5 и В6. Доход фирм указан в диапазоне ячеек В9:В14. Формула расчета налога записывается в ячейку С9: =ЕСЛИ(B9>A$5;B9*B$6;B9*B$5)
. Эту формулу нужно скопировать в нижние ячейки (выделено желтым цветом). В расчетной формуле адреса ячеек записаны в виде A$5, B$6, B$5. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки. Составное условие состоит из простых, связанных логическими операциями И() и ИЛИ(). И()
- логическая операция, требующая одновременного выполнения всех условий, связанных ею. Например: Рассмотрим электронную таблицу "Ведомость сдачи вступительных экзаменов". Для зачисления абитуриента в ВУЗ, ему необходимо преодолеть проходной балл, и по математике отметка должна быть выше 70 баллов. В этом примере функция ЕСЛИ()
использует составное условие, связанное логической операцией И()
. Обратите внимание: абитуриент Петров не зачислен, хотя сумма его баллов равна проходному. Почему так произошло? Посмотрим внимательно на условие в нашей формуле =ЕСЛИ(И(E6>=D2;B6>70);"зачислен";"не зачислен")
. Логическая операция И()
требует выполнения всех условий, но у нас выполняется только одно. Второе условие B6>70
не выполнено, поэтому составное условие принимает значение «ложь». И на экран выводится сообщение «не зачислен» (вспоминаем схему – ветвь «нет»). Задание:
2. Определите скидку (в процентах), используя функцию ЕСЛИ(). Если дата продажи попадает в период праздничной распродажи, то назначается скидка, иначе скидка равняется нулю. При задании условий используйте логическую функцию И().
3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%).
Решение:
и скопировать по соответствующим столбцам до 16 строки включительно. Посмотрим пример с логической операцией ИЛИ()
в той же таблице. Только немного изменим условие задачи. Для зачисления абитуриента достаточно иметь по математике более 60 баллов или общую сумму баллов не меньше проходного. Рисунок ниже. Абитуриент Сидоров зачислен, хотя не набрал проходной балл. Вот формула =ЕСЛИ(ИЛИ(B7>60;E7>D2;);"зачислен";"не зачислен").
Здесь использована операция ИЛИ()
, поэтому достаточно выполнение хотя бы одного условия. Что и произошло, первое условие B7>60
истинно. Оно привело к выводу сообщения о зачислении абитуриента. Задание:
1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.
2. Определите скидку (в процентах), используя функцию ЕСЛИ(). Если дата продажи совпадает с датами распродаж, то назначается скидка, иначе скидка равняется нулю. При задании условий используйте логическую функцию ИЛИ().
3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%)
Решение:
Для проведения расчетов необходимо вписать следующие формулы: и скопировать по соответствующим столбцам до 15 строки включительно. Если для решения задачи недостаточно использования составных условий, то тогда применяют более сложную конструкцию вложенных условий. Смотрите схему. В данном примере вторая функция ЕСЛИ() является вложенной и записывается на месте действия, которое вызывается при не выполнении условия первой функции. В Excel последних версий допускается делать до 64 вложений. Задание:
1. Определите стаж работы сотрудников. Стаж = 2018- Год приема на работу.
2. Используя вложенные функции Если, рассчитайте надбавку для сотрудников. Надбавка (руб.) = Надбавка(%)* Оклад
Решение:
Для проведения расчетов необходимо вписать следующие формулы: и скопировать по соответствующим столбцам до 19 строки включительно. В этой статье я постарался максимально подробно и доходчиво объяснить применение условной функции в Excel. Конечно же, в Excel есть и другие функции с условием, но о них поговорим в следующей статье. Друзья, если вам была интересна и полезна данная информация, не забывайте делиться ею в социальных сетях. А также буду рад прочитать ваши комментарии. PS: Удивительные факты
Дорогой читатель! Вы посмотрели статью до конца.
=ЕСЛИ(лог_выражение
;значение_если_ИСТИНА
;значение_если_ЛОЖЬ
)
1. лог_выражение
- любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.
2. значение_если_ИСТИНА - значение, которое возвращается если
лог_выражение
= ИСТИНА. Оно может быть числом, текстом, формулой или ссылкой на ячейку ().
3.
значение_если_ЛОЖЬ -
значение, которое возвращается если
лог_выражение
= ЛОЖЬ. Оно может быть числом, текстом, формулой или ссылкой на ячейку (в формулах массива допустимо использовать так же ссылки на диапазон ячеек
).
Если аргумент
лог_выражение
= ИСТИНА, но аргумент значение_если_ИСТИНА
Если аргумент
лог_выражение
= ЛОЖЬ, но аргумент значение_если_ЛОЖЬ
не указан, то возвращается значение 0.
ОГРАНИЧЕНИЯ:
Допускается до 7 вложений функции ЕСЛИ друг в друга в качестве любого из аргументов в Excel
версий
до 2003 включительно, до 64 вложений в
Excel версии 2007 и
до 128 вложений в
Excel версии 2010
.
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ:
Функция работает в формулах массива
Аргумент
лог_выражение может и не быть выражением.
Это может быть просто текст, число или ссылка на ячейку содержащую их.
Если аргумент
лог_выражение является текстом, то Вы получите значение ошибки #ЗНАЧ
, если числом, то при значении 0 функция ЕСЛИ вернёт ЛОЖЬ, при любом другом значении - ИСТИНА.
Если не указывать аргументы значение_если_ИСТИНА или
значение_если_ЛОЖЬ, то при выполнении условий будут возвращаться текстовые значения ИСТИНА или ЛОЖЬ.
В любой версии.
Условная функция ЕСЛИ()
Простое условие
Блок-схема "Простое условие". Определение отрицательных и неотрицательных чисел 
Блок-схема "Простое условие". Расчет данных
Процентная ставка прогрессивного налога зависит от дохода. Если доход предприятия больше определенной суммы, то ставка налога выше. Используя функцию ЕСЛИ, рассчитайте сумму налога.
Составное условие
ИЛИ()
- логическая операция, требующая выполнения любого из перечисленных условий, связанных ею.Логическая операция И()
Посмотрите внимательно на рисунок ниже.
В торговой фирме перед Новым Годом устроена праздничная распродажа. Рассчитать сумму продаж с учетом скидки, назначаемой в период распродажи.
Логическая операция ИЛИ()
В торговой фирме установлены дни распродаж - последние числа месяца. Рассчитать сумму продаж с учетом скидки, назначаемой в дни распродажи.
Вложенные условия
С целью уменьшения текучести кадров администрация решила выплачивать надбавку за непрерывный стаж работы на своем предприятии. Рассчитайте надбавку за непрерывный стаж работы в соответствии с таблицей Надбавка.
Получили вы ответ на свой вопрос?
Напишите в комментариях пару слов.
Если ответа не нашли, укажите что искали
.
