Функция если содержит в excel примеры. Логические функции Excel. Функции счётесли и суммесли
В Excel существует множество различных функций, работа которых построена на проверке логических условий. Например, это функции ЕСЛИ, СЧЕТЕСЛИ, СУММЕСЛИ и т.д. Также логические условия можно задавать в обычных формулах, если необходимо получить утвердительный ответ: Да или Нет . К примеру, задавая простые логические условия, Вы можете ответить на такие вопросы:
- 5 больше 8?
- Содержимое ячейки A5 меньше 8?
- А может равно 8?
Операторы сравнения в Excel
В Excel имеется ряд стандартных операторов, которые используются для задания простых логических условий. Все шесть возможных операторов сравнения приведены в таблице ниже:
Как задать условие в Excel
Операторы сравнения позволяют задавать условия, которые возвращают логические значения ИСТИНА или ЛОЖЬ. Примеры использования логических условий представлены ниже:
=A1=B1 – Данное условие вернет ИСТИНА, если значения в ячейках A1 и B1 равны, или ЛОЖЬ в противном случае. Задавая такое условие, можно сравнивать текстовые строки без учета регистра. К примеру, сравнивая “ЯНВАРЬ” и “январь” формула возвратит ИСТИНА.
=A1>B1 – Следующая формула возвратит ИСТИНА, если значение ячейки А1 больше, чем в B1. В противном случае формула вернет ЛОЖЬ. Такие сравнения можно задавать и при работе с текстом.
Например, если в ячейке A1 хранится значение “Апельсин”, а в B1 – “Арбуз”, то формула вернет ЛОЖЬ, поскольку в алфавитном порядке “Арбуз” находится ниже, чем “Апельсин”. Чем ниже, тем больше.
=A1<=B1 – Формула вернет ИСТИНА, если значение ячейки A1 меньше или равно значению в ячейке B1. Иначе результатом будет ЛОЖЬ.
=A1<>B1 – Формула вернет ИСТИНА, если значения ячеек A1 и B1 не равны. В противном случае – ЛОЖЬ.
В Excel существуют логические функции ИСТИНА() и ЛОЖЬ() , которые не имеют аргументов. Данные функции существуют в основном для обеспечения совместимости с другими электронными таблицами. Вы можете вводить значения ИСТИНА и ЛОЖЬ прямо в ячейки или формулы, не используя форму записи функции, Excel все прекрасно поймет.
Если Вы уверены, что уже достаточно хорошо освоили эту тему, можете обратиться к статье Используем логические функции Excel для задания сложных условий , чтобы научиться задавать условия, используя различные логические функции, например И() или ИЛИ() .
Возможность проведения логических проверок в ячейках является мощным инструментом. Вы найдете бесконечное количество применений для ЕСЛИ() в ваших повседневных документах. Проблема заключается в том, что часто необходимые проверки выходят за рамки возможностей простой функции ЕСЛИ() . Это происходит, когда необходимо сделать проверку на два и более условия до принятия результата.
Для таких случаев в Excel предусмотрено несколько вариантов: использование ЕСЛИ() внутри другого ЕСЛИ() , функции И() и ИЛИ() . Далее мы познакомимся с этими способами.
Использование ЕСЛИ() внутри другой функции ЕСЛИ()
Давайте рассмотрим вариант на основе изученной ранее функции =ЕСЛИ(А1>1000;"много"; "мало") . Что если вам необходимо вывести другую строку, когда число в А1 является, например, большим, чем 10.000? Другими словами, если выражение А1>1000 верно, вы захотите запустить другую проверку и посмотреть, верно ли, что А1>10000. Такой вариант вы можете создать, применив вторую функцию ЕСЛИ() внутри первой в качестве аргумента значение _если_истина: =ЕСЛИ(А1>1000;ЕСЛИ(А1>10000;"очень много"; "много");"мало") .
Если А1>1000 является истинным, запускается другая функция ЕСЛИ() , возвращающая значение «очень много», когда А1>10000. Если же при этом А1 меньше или равно 10000, возвращается значение «много». Если же при самой первой проверке число А1 будет меньше 1000, выведется значение «мало».
Обратите внимание, что с таким же успехом вы можете запустить вторую проверку, в случае если первая будет ложной (то есть в аргументе значение_если_ложь функции еслио). Вот небольшой пример, возвращающий значение «очень мало», когда число в А1 меньше 100: =ЕСЛИ(А1>1000;"много";ЕСЛИ(А1<100;"очень мало"; "мало")) .
Расчет бонуса с продаж
Хорошим примером использования одной проверки внутри другой проверки является расчет бонуса с продаж персоналу. который работает в Клуб — отель Гелиопарк Талассо, Звенигород . В данном случае, если значение равно X, вы хотите получить один результат, если У — другой, если Z
— третий. Например, в случае вычисления бонуса за успешные продажи возможны три варианта:
- Продавец не достиг планового значения, бонус равен 0.
- Продавец превысил плановое значение менее чем на 10%, бонус равен 1 000 рублей.
- Продавец превысил плановое значение более чем на 10%, бонус равен 10 000 рублей.
Вот формула для расчета такого примера: =ЕСЛИ(Е3>0;ЕСЛИ(Е3>0.1;10000;1000);0) . Если значение в Е3 является отрицательным, то возвращается 0 (нет бонуса). В случае когда результат положительный, проверяется, больше ли он 10%, и в зависимости от этого выдается 1 000 или 10 000. Рис. 4.17 показывает пример работы формулы.
Функция И()
Часто бывает необходимо провести проверку только на верность одновременно двум условиям. Например, выплата бонуса осуществляется, только когда продажи определенного товара превысили план и также общие продажи превысили план. Если одно из данных условий не выполняется (или оба условия), премия не выплачивается. В булевой логике это называется логическим И, поскольку оба условия должны быть истинны для истинности всего выражения.
В Excel выражения логического И обрабатываются с помощью функции И() : И(логическое_значение1;логическое_значение2;…). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо.
Еще раз отметим работу функции:
- Если все выражения возвращают ИСТИНА (или любое положительное число), И() возвращает ИСТИНА.
- Если один или более аргументов возвращают ЛОЖЬ (или 0), И() возвращает ЛОЖЬ.
Чаще всего И() применяется внутри функции ЕСЛИ() . В таком случае, когда все аргументы внутри И() вернут ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение если истина. Если одно или более из выражений в И() вернет ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь.
Вот небольшой пример: =ЕСЛИ(И(С2>0;В2>0);1000;"нет бонуса") . Если значение в В2 будет больше нуля и значение в С2 будет больше нуля, формула вернет 1000, в противном случае выведется строка «нет бонуса».
Разделение значений по категориям
Полезным применением функции и () является разделение по категориям в зависимости от значения. Например, у вас имеется таблица с результатами какого-то опроса или голосования, и вы хотите разделить все голоса на категории в соответствии со следующими возрастными рамками: 18-34,35-49, 50-64,65 и более. Предполагая, что возраст респондента находится в ячейке В9, следующие аргументы функции и () проводят логическую проверку на принадлежность возраста диапазону: =И(В9>=18;В9
Если ответ человека находится в ячейке С9, следующая формула выведет результат голосования человека, если срабатывает проверка на соответствие возрастной группе 18-34: =ЕСЛИ(И(В9>=18;В9
- 35-49: =ЕСЛИ(И(В9>=35;В9
- 50-64: =ЕСЛИ(И(В9>=50;В9
- 65+: =ЕСЛИ(В9>=65;С9;"")
Функция ИЛИ()
Существуют ситуации, когда вам необходимо решение, только когда одно из условий принимает положительное значение (ИСТИНА). Например, вы можете решить выплатить бонус сотрудникам, когда общие продажи превышают план или когда продажи отдельного товара превысили прогноз. В булевой логике это называется логическим ИЛИ .
Такие условия проверяются в Excel с помощью функции ИЛИ() : ИЛИ(логическое_значение1; логическое_значение2;...). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо. Результат работы ИЛИ() зависит от следующих условий:
- Если один аргумент или более возвращает ИСТИНУ (любое положительное число), ИЛИ() возвращает ИСТИНУ.
- Если все аргументы возвращают ЛОЖЬ (нулевое значение), результатом работы ИЛИ() будет ЛОЖЬ.
Так же как и И() , чаще всего функция ИЛИ() используется внутри проверки ЕСЛИ() . В таком случае, когда один из аргументов внутри ИЛИ() вернет ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение_если_истина. Если все выражения в ИЛИ() вернут ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь . Вот небольшой пример: = ЕСЛИ(ИЛИ(С2>0;В2>0);1000;"нет бонуса") .
В случае когда в одной из ячеек (С2 или В2) будет положительное число, функция вернет 1000. Только когда оба значения будут отрицательны (или равны нулю), функция вернет строку "нет бонуса".
Однажды жизненные обстоятельства заставляют осваивать новые программные инструменты для решения повседневных или офисных задач. Вручную выполнять долго и неудобно, к тому же увеличивается вероятность совершить ошибку. Поэтому рассмотрим, как работает СУММЕСЛИ в Excel и покажем примеры использования.
Синтаксис функции и принцип работы
О возможностях сложения известно каждому пользователю, хоть раз запускавшего Excel. СУММЕСЛИ – логичное продолжение базовой СУММ, отличие которой заключается в суммировании по условию.
Синтаксис СУММЕСЛИ выглядит следующим образом:
=СУММЕСЛИ(диапазон; критерий; [диапазон суммирования]) , где:
- диапазон – список значений, к которым будет применено ограничение;
- критерий – конкретное условие;
- диапазон суммирования – список значений, которые будут суммироваться.
Итак, имеем функцию с тремя компонентами. Стоит отметить, что последний аргумент можно не использовать – СУММЕСЛИ способен работать без диапазона суммирования.
Примеры использования
Рассмотрим все возможные ситуации применения одноименной функции.
Общий вид
Разберем простой пример, призванный наглядно показать преимущества использования СУММЕСЛИ.
Есть таблица, в которой указаны порядковый номер, вид товара, наименование, цена за единицу и количество штук на складе.
Допустим, нам нужно выяснить, сколько всего единиц товара находится на складе. Тут все просто – используем СУММ и указываем нужный интервал. Но что делать, если интересует количество единиц одежды? Тут на помощь и приходит СУММЕСЛИ. Функция будет иметь следующий вид:
=СУММЕСЛИ(C3:C12;»одежда»;F3:F12) , где:
- C3:C12 – тип одежды;
- «одежда» – критерий;
- F3:F12 – интервал суммирования.
Внимание! В функции СУММЕСЛИМН аргумент диапазона суммирования стоит в начале формулы. Будьте внимательны!
С динамическим условием
Бывают ситуации, когда мы забыли внести один из товаров в таблицу и его нужно добавить. Спасает ситуацию тот факт, что функции СУММЕСЛИ и СУММЕСЛИМН автоматически подстраиваются под изменение данных в таблице и мгновенно обновляют итоговое значение.
Для вставки новой строки нужно нажать ПКМ на интересующей ячейке и выбрать «Вставить» – «Строку».
Далее просто введите новые данные или скопируйте их с другого места. Итоговое значение соответственно изменится. Аналогичные трансформации происходят и при редактировании или удалении строк.
На этом я заканчиваю. Вы увидели основные примеры использования функции СУММЕСЛИ в Excel. Если есть какие-то рекомендации или вопросы – милости прошу в комментарии.
Проверка истинности условий и логических сравнений между выражениями, которые являются общими для многих задач. Для создания условных формул можно использовать функции " и ", "," и " Если ".
Например, функция Если использует указанные ниже аргументы.
Формула, использующая функцию если
Лог_выражение: условие, которое нужно проверить.
Значение_если_истина: возвращаемое значение, если условие истинно.
Значение_если_ложь: возвращаемое значение, если условие имеет значение false.
Дополнительные сведения о том, как создавать формулы, можно найти в разделе Создание и удаление формул .
В этой статье
Создание условной формулы, которая приводит к логическому значению (истина или ложь)
Для выполнения этой задачи используйте функции и операторы and, or и Not
Пример
Копирование примера
Важно:
Формулы в группе Зависимости формул нажмите кнопку Показывать формулы .
|
|
Дополнительные сведения о том, как использовать эти функции, можно найти в статьях, функциях и функциях, а не функциях .
Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от истина или ложь
Для выполнения этой задачи используйте функции и операторы Если , и и или , как показано в следующем примере.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
Выделите пример, приведенный в этой статье.
Важно: Не выделяйте заголовки строк или столбцов.
Выделение примера в справке
Нажмите клавиши CTRL+C.
В Excel создайте пустую книгу или лист.
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы .
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
|
|
Дополнительные сведения об использовании этих функций можно найти в статьях функция и функция .
Откроется диалоговое окно функции если.
Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 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)) С помощью диалоговых окон очень просто пользоваться функцией ЕСЛИ и при необходимости не запутаться с вложенными ЕСЛИ, главное изначально подумать о последовательности проверки ЕСЛИ.