0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

Лог_выражение – любое значение, формула или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

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

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

Например:
=ЕСЛИ(A1<>””;1;0)
<> – не равно,
“” – пустая.
То есть, если ячейка А1 не пустая, возвращается 1, если пустая, то 0.
При этом не важно будет ли в ячейке А1 число, текст или любые другие знаки.

Допустим нам нужно, за заказ свыше 15000 руб применить скидку 10%.
В поле Лог_выражение напишем условие: сумма заказа(В2) больше 15000. Пропишем в Значениe_если_истина В2*0,9, а в Значениe_если_ложь вернем сумму заказа (В2).

Функция ЕСЛИ с 1 условием

Протянем формулу для остальных заказов.

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

Неправильное построение вложенных функций ЕСЛИ

Часто бывает, что необходимо применить более 1 условия.
Допустим нам надо при сумме заказа свыше 15000 руб, сделать скидку 10%, а свыше 20000 руб 15%.

Excel воспринимает до 7 уровней вложения функций и проверяет условия начиная с первого.

Например, мы напишем формулу неправильно:
=ЕСЛИ(B2>15000;B20,9;ЕСЛИ(B2>20000;B20,85;B2))

То есть если мы напишем первым условием В2>15000, то для суммы свыше 20000, скидка 15% применяться не будет.

Например, для ячейки В4 проверится первое Лог_выражение (B4>15000), Excel увидит, что это выражение верно и применит скидку 10%.

Ошибка в построении функции ЕСЛИ

Поэтому начинать будем с суммы заказа больше 20%.

Как построить вложенные функции ЕСЛИ

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

Лог_выражение напишем В2>20000
Значение_если_истина – В2*0,85
В пустое поле Значение_если_ложь необходимо встать курсором и в поле имени (слева от строки формул) выбрать функцию ЕСЛИ.

Построение вложенных функций ЕСЛИ

При этом откроется новое окно, но начало формулы не пропадет.

Функция ЕСЛИ с 2-мя условиями

В новом окне пропишем следующее условие:

Построение функции ЕСЛИ с 2-мя условиями с помощью мастера функций

Протянем формулу для остальных заказов.

Пример использования функции ЕСЛИ с 2-мя условиями

Функция ИЛИ (результат изменяется при выполнении одного из нескольких условий)

Добавим к нашему примеру еще одно условие:
Если заказ оплачен более чем на 50%, предоставляется скидка тоже 10%.
Для наглядности занесем все наши условия в таблицу

Первую функцию ЕСЛИ оставляем без изменений.

Встаем курсором на вторую функцию ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

Появятся аргументы второго условия ЕСЛИ.
Стереть поле Лог_выражение (В2>15000)

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию ИЛИ.

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

В появившемся окне прописываем наши 2 условия:

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строку автоматически появляется 3-я. Оставляем ее пустой и нажимаем ОК.

Протянем формулу для остальных заказов.

Функция И (результат изменяется при выполнении нескольких условий)

Скидки за заказ оставим на прежнем уровне:
Заказ более 20000 – 15%
Заказ более 15000 – 10%

Читайте так же:
Программы для раздачи Wi-Fi с ноутбука

Но если заказ более 15000 и оплачен более чем на 50% – скидка 20%

Для наглядности занесем все наши условия в таблицу

Первую функцию ЕСЛИ оставляем без изменений.

Встаем курсором на вторую функцию ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

В появившемся окне стираем Лог_выражение: B2>15000.

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию И.

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

В появившемся окне вписываем 2 наших условия

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строку автоматически появляется 3-я. Оставляем ее пустой и нажимаем ОК.

При нажатии на ОК окно закроется.

Мы прописали только первую строчку (Лог_выражение) в нашем втором условии ЕСЛИ. Снова встаем на 2-ую ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

В первой строке (Лог_выражение) уже прописаны 2 наших условия.
Значение_если_истина исправляем 0,9 на 0,8, чтобы при соблюдении 2-х условий Excel считал скидку 20%
Значение_если_ложь стираем B2 (будем прописывать в нее 3-е условие).

Встаем курсором на Значение_если_ложь и в поле имени выбираем функцию ЕСЛИ.

Появится окно в которое мы будем прописывать 3-е условие.

Протягиваем формулу для остальных заказов.

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

Если мы пропишем 2-м условием Заказ более 15000 – 10%, а 3-м выполнение 2-х условий, Excel определит 2-е условие как истина и 3-е условие проверять не будет.

Функция «если» в excel — примеры как применять, инструкция для начинающих

Microsoft Office Excel помогает работать с базами данных, содержащими большой объем информации. При помощи математических операций можно отсортировать сведения и получить необходимый массив. Также программа позволяет заполнять новые столбцы с использованием уже имеющейся информации по определенным условиям. Сегодня в статье разберемся, как работает функция «если» в excel.

Содержание и использование

Логическая функция «если» нужна для отбора информации. При этом, если значение ячейки отвечает условию, то возвращается одно значение, в противном случае – другое. Такая пара по-другому называется истина-ложь. Рассмотрим структуру:

  1. Первая часть функции нужна для записи выражения, которое задает условие.
  2. Далее значение истины.
  3. В последнем блоке указываете значение лжи.

На заметку! Если один из двух последних блоков не заполнен, то excelавтоматически возвращает ноль.

Microsoft Office Excel

Изучим подробнее, как использовать функцию «если»:

  1. Создаем таблицу на рабочем листе и заполняем информацией.

Как использовать функцию

  1. Задаемся условием: если значение больше 40, то выводим привет, в противном случае – пока.

Задаемся условием

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

  1. Применим операцию ко всем ячейкам при помощи специального штурвала в правом нижнем углу.

Применим операцию

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

Расширенные возможности

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

Читайте так же:
Команда tracert — что это и для чего используется?

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

Расширенные возможности

Зададимся условием: Определить количество учеников, которые будут допущены к экзаменам, при условии среднего балла выше 7,0.

Значение функции: ЕСЛИ(C13>7;»допущен»;»недопущен»)

Зададимся условием

Однако, ученик Сидоров также может быть допущен к экзаменам, поскольку у него средний балл 7,0. Тогда в функции нужно использовать оператор больше или равно >=.

Сидоров также может быть допущен к экзаменам

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

Условие: Определить студентов, которые по результатам тестирования набрали количество баллов более 75, при этом минимальным проходным баллом является 40.

В строке формулы будет записано следующее:

В строке формулы

Логические операторы

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

Рассмотрим пример с И:

Рассмотрим пример с И

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

Здесь, если выполняется одно из условий, то истина, в противном случае – ложь.

Пример с ИЛИ

Поиск пустых ячеек

Иногда нужно найти незаполненные места в большей таблице. Однако возникает вопрос, как задать условие, что ячейка не пустая. Задачу решим от обратного. Для этого в excel есть отдельная операция ЕПУСТО.

Найти незаполненные места в большей таблице

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

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

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

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

Примеры применения функции ЕСЛИ

Раздел функцийЛогические
Название на английскомIF
ВолатильностьНе волатильная
Похожие функцииЕСЛИОШИБКА, ЕОШ, ЕОШИБКА, ЕНД

Что делает эта функция?

Функция проверят истинность первого аргумента — вычисления или значения ячейки. В зависимости от результата будут выполнены вычисления или возвращены другие значения.

Синтаксис

Условие обычно является вычислением, возвращающим булевый результат (ИСТИНА или ЛОЖЬ).

Критерии сравнения, которые при этом обычно используются: ‘=’ (равно), ‘>’ (больше), ‘<‘ (меньше), ‘<>’ (не равно), ‘>=’ (больше или равно), ‘<=’ (меньше или равно). Сравнивать между собой можно и логические, и цифровые, и текстовые значения.

Функция может принимать на вход и числа. Числа, не равные нулю, считаются эквивалентны ИСТИНА, 0 — ЛОЖЬ.

Другие особенности функции ЕСЛИ:

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

Примеры применения

Пример 1

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

Читайте так же:
Hetman Partition Recovery 2.8 + код активации

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

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

Для проверки вхождения цифр в строку мы воспользуемся функцией ПОИСК.

Попробуем переложить синтаксис на пример с цифрами. Так будет выглядеть формула (не удивляйтесь переносам строки и пробелам — они не учитываются в Excel и вы можете для удобства составлять сложные формулы так же).

А ниже интерактивная таблица с приведенной формулой. Можно изменять ячейки, скопировать значения или формулу ячеек в свой Excel. Или скачать весь файл.

Как ускорить ВПР в 50.000 раз? С помощью ЕСЛИ и второго ВПР! Нет, здесь нет ошибки. Подробно об этом увлекательном феномене можно почитать в соответствующей статье про функцию ВПР, а здесь лишь будет приведен файл и формула с участием ЕСЛИ.

Так выглядит ее синтаксис:

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

Если не верите, что 250.000 ячеек можно сравнить с другими 400.000 ячеек чуть медленнее, чем за мгновение, скачайте файл и попробуйте скопировать формулу в столбце B вниз до конца:

В версях Excel до 2016 отсутствуют функции МАКСЕСЛИ и МИНЕСЛИ. Но нам ничто не мешает создать их аналог с помощью формул массива.

Допустим, мы хотим посчитать наибольшее и наименьшее четное число в диапазоне A1:A10.

Используем на массиве функцию ЕЧЁТН (Появилась в Excel 2007) и будем возвращать само число, если оно чётное, и пустую строку, если нечётное. Итоговый массив обработаем функциями МАКС и МИН:

А если у нас Excel 2003 и функция ЕЧЁТН для нас тоже недоступна, используем функцию ОСТАТ, проверяя остаток от деления на 2:

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

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

Речь пойдет про функцию ЕСЛИ в Excel.

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

Читайте так же:
Лёгкий способ разблокировать и прошить модем ZTE/Huawei от «Билайн»

Функция ЕСЛИ в Excel. Синтаксис

Как и всегда, изучение любой функции давайте начнем с самого базового — с синтаксиса функции ЕСЛИ (IF в английской версии):

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

Проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.

Функция содержит 3 аргумента: первое — логическое выражение (обозначим буквой А), второе — значение если истина (примем как букву B) и третье — значение если ложь (C).

По смыслу функция проверяет логическое выражение A (обычно сюда прописывают условие проверки, например, что одно число больше другого X>Y), если оно истинно, т.е. A выполнено, то функция возвращает одно значение (B), если же выражение ложно, т.е. A не выполнено, то возвращает уже другое (C). Если на словах не до конца понятно, то есть вот такая наглядная схема:

Принцип работы функции ЕСЛИ в Excel

Таким образом, первый аргумент функции ЕСЛИ — логическое условие, второй аргумент — значение если условие истинно, третий аргумент — если нет.

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

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

Исходная таблица с данными

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

Рассмотрим пример — определим всех сотрудников, по которым выручка составила более 50 тыс. руб. Если условие будет выполняться, то будем возвращать текстовое значение «Да», если нет, то пустое поле.

Прописываем функцию ЕСЛИ с заданными параметрами — делаем проверку на то, что выручка больше 50 тыс. руб. ($B5>$C$1), пишем «Да» если выполнено и пустое поле в противном случае:

Пример формулы ЕСЛИ с одним условием

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

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

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

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

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

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

Вложенные ЕСЛИ пример 1

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

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

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

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

И (логическое_значение1; [логическое_значение2]; [логическое_значение3]; …)

Проверяет, все ли аргументы имеют значение ИСТИНА, и возвращает значение ИСТИНА, если истинны все аргументы.

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

Читайте так же:
Выделяем все значения в Cheat Engine

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

Применение логических функций ЕСЛИ и И

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

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

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

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

Вложенные ЕСЛИ пример 2

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

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

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

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

ИЛИ (логическое_значение1; [логическое_значение2]; [логическое_значение3]; …)

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

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

Применение логических функций ЕСЛИ и ИЛИ

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

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

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

Спасибо за внимание!
Если у вас есть вопросы и мысли по теме статьи — прошу делиться в комментариях.

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector