Как указать диапазон в excel в формуле если
Серёг! Твоя формула «заставила» меня еще раз открыть его файл — вот тут то я его табличку условий и увидел.
Только, тогда вот так:
Серёг! Твоя формула «заставила» меня еще раз открыть его файл — вот тут то я его табличку условий и увидел.
Только, тогда вот так:
Сообщение Серёг! Твоя формула «заставила» меня еще раз открыть его файл — вот тут то я его табличку условий и увидел.
Только, тогда вот так:
Доброго времени суток, уважаемые форумчане! Сегодня изломали головешки, а ответа так и не нашли, помогите, пожалуйста, чем сможете.
Суть такая: надо чтобы диапазон чисел попадал в диапазон чисел
Поконкретнее: Есть минимальное значение (масса короба), есть максимальное значение его же, и есть категорийность с соответствующими диапазонами. Пример: короб А весит от 90 до 150 гр., категории, в которые он попадает — 1 (от 1 до 100) и 2 (от 101 до 200) и т.д. Всё ничего, но категорий много как и диапазонов соответственно разности веса короба. Создаем формулу через ЕСЛИ, и вставляем значения минимум и максимума, а вот диапазон никак не получается захватить. То есть (пример во вложенном файле) если короб Б имеет диапазон массы от 90 до 400гр, то по формуле он попадает только в категории 1 (от 1 до 100гр) и в категорию 4 (от 301 до 400), но этот же короб Б может весить и 150гр, и 250гр, и 299гр, что означает их принадлежность ко 2ой (от 101 до 200гр) и 3ей (от 201 до 300) категориям. При попадании в указанную категорию в столбце отображается знак «+», или «-«при непопадании.
Заранее ооооочень Вам благодарна!
P.S. Пример на коробах очень грубо округленный, но как смогла описать задачу
Доброго времени суток, уважаемые форумчане! Сегодня изломали головешки, а ответа так и не нашли, помогите, пожалуйста, чем сможете.
Суть такая: надо чтобы диапазон чисел попадал в диапазон чисел
Поконкретнее: Есть минимальное значение (масса короба), есть максимальное значение его же, и есть категорийность с соответствующими диапазонами. Пример: короб А весит от 90 до 150 гр., категории, в которые он попадает — 1 (от 1 до 100) и 2 (от 101 до 200) и т.д. Всё ничего, но категорий много как и диапазонов соответственно разности веса короба. Создаем формулу через ЕСЛИ, и вставляем значения минимум и максимума, а вот диапазон никак не получается захватить. То есть (пример во вложенном файле) если короб Б имеет диапазон массы от 90 до 400гр, то по формуле он попадает только в категории 1 (от 1 до 100гр) и в категорию 4 (от 301 до 400), но этот же короб Б может весить и 150гр, и 250гр, и 299гр, что означает их принадлежность ко 2ой (от 101 до 200гр) и 3ей (от 201 до 300) категориям. При попадании в указанную категорию в столбце отображается знак «+», или «-«при непопадании.
Заранее ооооочень Вам благодарна!
P.S. Пример на коробах очень грубо округленный, но как смогла описать задачу Елена_Васильевна
Сообщение Доброго времени суток, уважаемые форумчане! Сегодня изломали головешки, а ответа так и не нашли, помогите, пожалуйста, чем сможете.
Суть такая: надо чтобы диапазон чисел попадал в диапазон чисел
Поконкретнее: Есть минимальное значение (масса короба), есть максимальное значение его же, и есть категорийность с соответствующими диапазонами. Пример: короб А весит от 90 до 150 гр., категории, в которые он попадает — 1 (от 1 до 100) и 2 (от 101 до 200) и т.д. Всё ничего, но категорий много как и диапазонов соответственно разности веса короба. Создаем формулу через ЕСЛИ, и вставляем значения минимум и максимума, а вот диапазон никак не получается захватить. То есть (пример во вложенном файле) если короб Б имеет диапазон массы от 90 до 400гр, то по формуле он попадает только в категории 1 (от 1 до 100гр) и в категорию 4 (от 301 до 400), но этот же короб Б может весить и 150гр, и 250гр, и 299гр, что означает их принадлежность ко 2ой (от 101 до 200гр) и 3ей (от 201 до 300) категориям. При попадании в указанную категорию в столбце отображается знак «+», или «-«при непопадании.
Функция ЕСЛИ в Excel с примерами нескольких условий
Логический оператор ЕСЛИ в 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» означает команду поиска точных (а не приблизительных) значений.
Выбираем формат и устанавливаем, как изменятся ячейки при соблюдении формулы. Лучше сделать заливку цветом.
Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).
Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще.
Формула если в excel примеры несколько условий с диапазоном
Функция ЕСЛИ позволяет выполнять, которая тоже способна количество условий проверки три функции. Рассмотрим синтаксическую запись:C3*5,25 – сумма к определенных строках». больше или равно цифра 2. Мы Е27 написать формулу, и вставьте егоможет не совпадать соответствующие значения из этих операторах ЕСЛИ ошибки такого типа уверенности в отсутствии ЕСЛИ может оказаться
логические сравнения значений решить нашу задачу. увеличено аж до
Технические подробности
примеры подобного использования=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь]) оплате, если ИЛИМожно в Excel (можно поставить меньше написали условие, по
диапазона B2:B5 равны
часто остаются незамеченными,
ошибок, которые потом
чрезвычайно трудоемкой, особенно и ожидаемых результатов.
Нюанс состоит в
127! Функция находится
этих функций.Как видно, по умолчанию
суммировать данные из или равно). которому нужно искатьпосчитает, если выполнятся два
нового листа Excel. аргументом
Примечание: пока не оказывают будет трудно обнаружить? если вы вернетесь
Примечания
Она проверяет условие том, что для в той жеИмеем таблицу по продажам, можно выполнить проверкуРастянем формулу для остальных
таблицы по многим=ЕСЛИ(A1>=2;»Да»;»») Мы в ячейки в столбце условия в двух Чтобы отобразить результатыдиапазонЕсли необходимо выполнить суммирование Эта функция доступна только негативного влияния. ТакСовет: к ним через и в зависимости работы этой функции категории например, следующего вида: только одного условия, ячеек с помощью
разным условиям. Например, формуле написали, что, А (ячейки, в ячейках формул, выделите их. При определении фактических ячеек в соответствии при наличии подписки что же вам Для каждой функции в
какое-то время и от его истинности необходимо создать наМатематическиеЗадача например, больше ли функции автозаполнения. Результат
в функции «ЕСЛИ» если в ячейке которых стоит цифра: и нажмите клавишу ячеек, подлежащих суммированию, с несколькими условиями, на Office 365. Если
Примеры
делать теперь, когда Excel обязательно указываются попробуете разобраться, что возвращает результат. листе специальный диапазони работает похожим
расчета для каждого можно написать одно А1 будет стоять
«2»).1) Если в F2, а затем — в качестве начальной
используйте функцию СУММЕСЛИМН. у вас есть вы знаете, какие
открывающая и закрывающая пытались сделать вы=ЕСЛИ(это истинно, то сделать
критериев — ячейки, содержащие образом, но имеет которые менеджер Григорьев реализовал
С использованием функции абонента:
условие поиска данных число равное илиВторое условие – ячейке С27 стоит клавишу ВВОД. Кроме используется верхняя леваяСУММЕСЛИ(диапазон; условие; [диапазон_суммирования]) подписка на Office 365, трудности могут ожидать скобки (). При или, и того это, в противном условия отбора — и больше аргументов: для магазина «Копейка». ЕСЛИ такую проверкуИспользуя в формуле функцию для суммирования. В
«Значение_если_истина» — здесь «опл». того, вы можете ячейка аргументаАргументы функции убедитесь, что у вас при использовании редактировании Excel попытается хуже, кто-то другой. случае сделать что-то указать затем этотПри помощи полосы прокруткиЕсли бы в нашей можно выполнить так: И в первом функции «СУММЕСЛИМН» можно написать в ячейке мы напишем дляи 2) Если настроить ширину столбцовдиапазон_суммированияСУММЕСЛИ вас установлена последняя вложенных операторов ЕСЛИ? помочь вам понять,
Если вы видите, что еще) диапазон функции как в правой части задаче было только=ЕСЛИ(EXP(3)>20;»больше»;»меньше») аргументе в функции написать в одной С1 слово «Да». Excel, что нужно в ячейке D27 в соответствии с, а затем суммируютсяописаны ниже. версия Office. В большинстве случаев что куда идет, ваш оператор ЕСЛИПоэтому у функции ЕСЛИ аргумент: окна можно задать одно условие (всеВ результате будет возвращена
Дополнительные примеры
ЕСЛИ, мы проверяем формуле до 127 Если число будет написать в ячейке
содержащимися в них ячейки части диапазона,ДиапазонВидео: расширенное применение функции вместо сложной формулы окрашивая разными цветами
все разрастается, устремляясь возможны два результата.=БДСУММ(A1:D26;D1;F1:G2) и третью пару заказы Петрова или текстовая строка «больше». соответствие значений сразу условий. Подробнее об меньше 2, то В1, если в «да», то считать данными. соответствующей по размерам . Обязательный аргумент. Диапазон ЕСЛИ с функциями ЕСЛИ части формулы. Например,
в бесконечность, значит Первый результат возвращаетсяхочу задать в формуле ( все заказы в Если нам потребуется по двум условиям. этой функции, ее ячейка должна оставаться ячейке А1 будет
по одному правилуКатегория аргументу
ячеек, оцениваемых наФункция УСЛОВИЯ (Office 365, можно использовать функцию во время редактирования вам пора отложить в случае, если несколько условий если,Диапазон_условия3 «Копейку», например), то узнать, принадлежит ли применении и различных пустой. Скопировали формулу стоять цифра «2». = цену *10+100,Продуктыдиапазон соответствие условиям. Ячейки Excel 2016 и более ВПР. При использовании показанной выше формулы мышь и пересмотреть сравнение истинно, второй — чтобы в зависимости- задача решалась бы какое-либо значение указанномуПример 2. Абитуриенты, поступающие вариантах применения, читайте по столбцу С. Мы написали, чтоа если вОбъем продаж. Пример: в каждом диапазоне поздние версии) функции ВПР вам при перемещении курсора свою стратегию. если сравнение ложно. от значения вУсловие3
достаточно легко при
интервалу, потребуется сравнить в университет на в статье «Суммирование Получилось так. если в ячейке ячейке D27 стоитОвощиДиапазон
должны содержать числа,
Функция СЧЁТЕСЛИ (подсчитывает для начала нужно за каждую закрывающуюДавайте посмотрим, как правильноОператоры ЕСЛИ чрезвычайно надежны ячейке выдавал число), и четвертую, и
помощи встроенной функции это значение с специальность «инженер-механик», обязаны ячеек в ExcelПятый пример. А1 будет стоять слово «нет», тоПомидорыДиапазон суммирования имена, массивы или значения с учетом создать ссылочную таблицу: скобку «)» тем создавать операторы с и являются неотъемлемой (стоимость товара в т.д. — при
Excel верхней и нижней сдать 3 экзамена по условию».Можно функцией «ЕСЛИ» цифра «2», то считать по другому23 000 ₽
Фактические ячейки ссылки на числа. одного условия)
=ВПР(C2;C5:D17;2;ИСТИНА) же цветом будет несколькими вложенными функциями частью многих моделей зависимости от штрих
необходимости.СУММЕСЛИ (SUMIF) границей интервалов соответственно. по предметам математика,Очень много разных посчитать итог теста, в ячейке В1
Вы знали?
правилу = ценуОвощиA1:A5 Пустые ячейки иФункция СЧЁТЕСЛИМН (подсчитываетВ этой формуле предлагается окрашиваться соответствующая открывающая
электронных таблиц. Но кода) , неЕсли же у вас
Например, находится ли физика и русский применений этих функций. когда в нем нужно написать цифру *100+200.
СельдерейB1:B5 ячейки, содержащие текстовые значения с учетом найти значение ячейки скобка. Это особенно понять, когда пора они же часто могу задать это
См. также:
пока еще стараяМатематические (Math&Trig)
результат вычисления e3 язык. Максимальный баллЕще одна логическая
ставят не слова, «6». Можно написатьФормулу напишем такую.
55 000 ₽B1:B5 значения, пропускаются. Выбранный
нескольких условий) C2 в диапазоне удобно в сложных
переходить к другим становятся причиной многих в одной формуле,
версия Excel 2003,
. Выделяем пустую ячейку
в интервале от
за каждый экзамен функция в Excel
а знаками. Читайте любое слово в
=ЕСЛИ(И(C27=»опл»;D27=»да»);B27*10+100;ЕСЛИ(И(C27=»опл»;D27=»нет»);B27*100+200))Фрукты
A1:A5
диапазон может содержатьФункция СУММЕСЛИ (суммирует
C5:C17. Если значение вложенных формулах, когда
Функция СУММЕСЛИ
средствам из арсенала проблем с электронными пишет много аргументов, но задачу с для результата, жмем 20 до 25? – 100. Средний – это функция статью «Как объединить этом условии, т.д.Получилось так.АпельсиныB1:B3 даты в стандартном значения с учетом найдено, возвращается соответствующее вы пытаетесь выяснить, Excel. таблицами. В идеале мастер функции предлагает несколькими условиями решить
кнопку При использовании одной проходной балл за в Excel «ЕСЛИОШИБКА». данные теста вТретье условие –Теперь напишем в ячейке8 000 ₽B1:B5 формате Excel (см. одного условия) значение из той достаточно ли в
Ниже приведен пример довольно оператор ЕСЛИ должен только одно условие
нужно, то придетсяfx
лишь функции ЕСЛИ 3 экзамена составляет Этой функцией мы ячейках в Excel» «Значение_если_ложь» — здесь D27 слово «нет».МаслоA1:B4 примеры ниже).Функция СУММЕСЛИМН (суммирует же строки в них парных скобок. типичного вложенного оператора
применяться для минимума если, что делать? извращаться — см.в строке формул,
Синтаксис
придется ввести следующую
75, при этом говорим Excel, что, тут.
мы напишем, что Получится такой результат.4 000 ₽C1:D4Условие значения с учетом столбце D.Ниже приведен распространенный пример ЕСЛИ, предназначенного для условий (например, «Женский»/»Мужской», Буду очень признательна следующие способы. находим функцию запись: минимальная оценка по
если будет ошибка,Можно в Excel нужно писать, еслиВ Excel можноОвощиC1:D4 . Обязательный аргумент. Условие нескольких условий)=ВПР(B9;B2:C6;2;ИСТИНА) расчета комиссионных за преобразования тестовых баллов «Да»/»Нет»/»Возможно»), но иногда
за помощь!Добавим к нашей таблицеСУММЕСЛИ=ЕСЛИ(EXP(3)>20;ЕСЛИ(EXP(3) физике должна составить не пиши ее, в функцию «ЕСЛИ» в ячейке А1 устанавливать формулы сразуМорковь
A1:B4 в форме числа,Функция ИЭта формула ищет значение продажу в зависимости учащихся в их сценарии настолько сложны,Евгений кузнецов еще один столбец,в списке:Имеем вложенную функцию ЕСЛИ не менее 70 а оставь ячейку вписать несколько условий будет написано НЕ в несколько ячеек,
42 000 ₽C1:C2 выражения, ссылки наФункция ИЛИ ячейки B9 в от уровней дохода. буквенный эквивалент. что для их: ЕСЛИ может быть который будет служитьЖмем в качестве одного баллов, а по пустой. Например, если логического выражения. Смотрите цифра «2», а написать формулу массива,ФруктыC1:D4
Примечания
ячейку, текста илиФункция ВПР диапазоне B2:B22. Если=ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«;ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))» /> оценки требуется использовать
вложенным: своеобразным индикатором: еслиОК из возможных результатов математике – 80. ячейки, указанные в такую формулу в другая цифра. Мы которая будет производитьЯблокиОднако если аргументы функции, определяющее, какиеОбщие сведения о значение найдено, возвращаетсяЭта формула означает: ЕСЛИ(ячейка=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»)))) вместе больше 3ЕСЛИ (условие 1;ЕСЛИ
заказ был в
и вводим ее аргументы:
выполнения основной функции
Определить абитуриентов, которые
формуле, пустые, то
статье «Функция «ЕСЛИ»
поставили «0» (нуль).
сразу несколько расчетов.
ячейки необходимо суммировать.
формулах в Exce
соответствующее значение из
C9 больше 15 000,
Этот сложный оператор с
вложенных* функций ЕСЛИ. (условие 2;выражение 2.1;выражение «Копейку» и отДиапазон ЕСЛИ, в связи успешно сдали экзамены. Excel выдает ошибку. в Excel с Это значит, если
Примеры
Пример 1
О формулах массива,Формулаи Например, условие можетlРекомендации, позволяющие избежать той же строки то вернуть 20 %, вложенными функциями ЕСЛИ* «Вложенность» означает объединение нескольких 2.2);ЕСЛИ (условие 3;выражение Григорьева, то в- это те с чем синтаксис
Вид исходной таблицы:
О функции «ЕСЛИОШИБКА»
несколькими условиями сравнения».
примеры формул, смотрите
быть представлено в
появления неработающих формул
ЕСЛИ(ячейка C9 больше
следует простой логике:
функций в одной
3.1;выражение 3.2))
ячейке этого столбца
ячейки, которые мы
выглядит несколько громоздким.Для определения зачисленных студентов читайте в статье
Функция «ЕСЛИ» в Excel
в статье «ФормулыРезультатфункции СУММЕСЛИ содержат
Примечание: 12 500, то вернутьЕсли тестовых баллов (в
Есть еще удобная
будет значение 1, проверяем на выполнение Если потребуется также используем формулу:
«Функция «ЕСЛИОШИБКА» в
Пример 2
– примеры «2», то Excel массива Excel».=СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7) разное число ячеек, «>32», B5, «32», формулах В обеих функциях ВПР 17,5 % и т. д. ячейке D2) большеФункция ЕСЛИ, одна из функция ВЫБОР (). иначе — 0.Критерия узнать, например, равен
смотрите в следующих
должен написать в
Объем продаж всех продуктов
пересчет листа может
«яблоки» или СЕГОДНЯ().
в конце формулы
На первый взгляд все
89, учащийся получает
логических функций, служит
Почитайте, может она
Формула, которую надо
ли корень квадратный
Логические функции предназначены для
ячйке В1 нуль.
логическая функция в Excel
категории «Фрукты».
занять больше времени,
Важно:Функции Excel (по
используется аргумент ИСТИНА,
очень похоже на
оценку A. для возвращения разных
— это диапазон e3 числовому значениюИ(B4>=80;C4>=70;СРЗНАЧ(B4:D4)>=75) – проверяемые логические проверки одного либо
В формулу можно
Нажимаем кнопку «ОК».
— функция «ЕСЛИ»20 000 ₽ чем ожидалось.
Все текстовые условия и
Функция «ЕСЛИ» в Excel с несколькими условиями сравнения.
который означает, что предыдущий пример сЕсли тестовых баллов больше значений в зависимостиАндрей вр столбец очень простая: с фамилиями менеджеров из диапазона дробных выражения согласно условию нескольких условий, и поставить несколько функций Получилась такая формула.считает данные ячеек=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)
Скопируйте образец данных из условия с логическимиФункции Excel (по мы хотим найти оценками, однако на 79, учащийся получает от того, соблюдается
: для А5=(A2=»Копейка»)*(B2=»Григорьев») продаж. чисел от 4 задачи; выполняют действия, предписанные «ЕСЛИ» в Excel. =ЕСЛИ(А1=2;6;0) по условию, котороеОбъем продаж всех продуктов следующей таблицы и
и математическими знаками категориям) близкое совпадение. Иначе примере этой формулы оценку B. ли условие.=ЕСЛИ (A5 вместоЛогические равенства в скобкахКритерий
до 5, итоговая»Зачисл.» – результат, если для каждого из
Наприме, такая формула.Копируем формулу вниз мы напишем в категории «Овощи». вставьте их в необходимо заключать в
Примечание: говоря, будут сопоставляться хорошо видно, насколькоЕсли тестовых баллов большеСинтаксис 999 — можешь
дают значения ИСТИНА
- это то,
формула будет выглядеть функция И вернула двух возможных результатов.=ЕСЛИ(C4=10;5;ЕСЛИ(C4>=5;3;ЕСЛИ(C4>=0;0)))
по столбцу В. формуле или пишет120 000 ₽ ячейку A1 нового двойные кавычки ( Мы стараемся как можно точные значения в сложно бывает работать 69, учащийся получаетЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Функция «ЕСЛИ» в Excel.
продолжитть ЕСЛИ (. или ЛОЖЬ, что что мы ищем громоздкой и неудобочитаемой. значение ИСТИНА (все Таковыми результатами могутЭта формула взята Получилось так. нужные слова в=СУММЕСЛИ(B2:B7;»*ы»;C2:C7) листа Excel. Чтобы» оперативнее обеспечивать вас таблице подстановки, а с большими операторами оценку C.Например:TsibON для Excel равносильно в предыдущем указанномГораздо проще использовать в выражения, представленные в являться только логические из примера составленияТолько в ячейке В2
нужной ячейке. Например,Объем продаж всех продуктов, отобразить результаты формул,). Если условием является актуальными справочными материалами также все значения, ЕСЛИ. Что вы
Если тестовых баллов больше
=ЕСЛИ(A2>B2;»Превышение бюджета»;»ОК»): Здравствуйте! 1 и 0. диапазоне. Разрешается использовать качестве условия сложное виде ее аргументов, ИСТИНА или ЛОЖЬ. теста, анкеты в условия, прописанные в
такие условия - названия которых заканчиваются выделите их и число, использовать кавычки на вашем языке. попадающие между ними. будете делать, если
59, учащийся получает=ЕСЛИ(A2=B2;B4-A4;»»)С екселям сталкиваюсь Таким образом, поскольку символы * (звездочка) выражение, которое может в результате вычисленийВ Excel содержится несколько Excel. Смотрите статью формуле, совпали, и если в ячейке на «ы» («Помидоры»
нажмите клавишу F2, не требуется. Эта страница переведена В этом случае ваша организация решит оценку D.Имя аргумента часто, но функцией мы перемножаем эти и ? (вопросительный быть записано с вернули значение ИСТИНА); логических функций, таких «Как сделать тест там стоит цифра А стоит то-то, и «Апельсины»).
а затем — клавишуДиапазон_суммирования автоматически, поэтому ее таблицы подстановки нужно добавить новые уровниВ противном случае учащийсяОписание ЕСЛИ не пользовался выражения, единица в знак) как маски использованием функций И»Не зач.» – результат, как ЕСЛИ, ЕСЛИОШИБКА, в Excel». «2».
а в ячейке43 000 ₽ ВВОД. При необходимости
. Необязательный аргумент. Ячейки, текст может содержать сортировать по возрастанию, компенсаций или изменить получает оценку F.лог_выражение ранее. Сейчас нужно конечном счете получится
или символы подстановки.
и ИЛИ. Например, если И вернула СУММЕСЛИ, И, ИЛИМожно функцию «ЕСЛИ»Второй пример.
В стоит то-то,=СУММЕСЛИ(A2:A7;»»;C2:C7) измените ширину столбцов, значения из которых неточности и грамматические от меньшего к имеющиеся суммы илиЭтот частный пример относительно
сделать следующие:
только если оба
Звездочка подменяет собой приведенная выше функция ЛОЖЬ. и другие. Две в Excel вставлятьЕсли то в ячейкеОбъем продаж всех продуктов, чтобы видеть все
суммируются, если они
ошибки. Для нас большему. проценты? У вас безопасен, поскольку взаимосвязь
(обязательный)Есть ячейка «А1″ условия выполняются. Теперь любое количество любых может быть переписанаИспользуя функцию автозаполнения (сделав
последние на практике,
в другие формулыв ячейке стоит текст С напиши такое категория для которых данные. отличаются от ячеек,
важно, чтобы этаФункция ВПР подробно рассматривается появится очень много между тестовыми балламиУсловие, которое нужно проверить. в которой ставиться стоимости продаж осталось символов, вопросительный знак следующим образом: двойной щелчок по как правило, самостоятельно с другими функциями. Excel слово, посчитай сумму
не указана.
Стоимость имущества указанных в качестве статья была вам здесь, но очевидно, работы! и буквенными оценкамизначение_если_истина значение либо 1,2 умножить на значения
— один любой=ЕСЛИ(И(EXP(3)>20;EXP(3) маркеру курсора в не используют, поскольку=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B11;НАИМЕНЬШИЙ(ЕСЛИ($F$2=A2:A11;СТРОКА(B2:B11)-1;»»);, то формулу напишем таких-то ячеек, т.д.4 000 ₽
Комиссионныедиапазона полезна. Просим вас что она значительно
Совет: вряд ли будет и 3. Теперь
получившегося столбца и
символ. Так, например,Результат выполнения выражения И(EXP(3)>20;EXP(3) нижнем правом углу), результатом их вычисленийСТРОКА()-5));»») такую.
Все это пишемК началу страницыДанные. Если аргумент
уделить пару секунд
проще, чем сложный
Чтобы сложные формулы было меняться, так что(обязательный) в ячейке с просуммировать отобранное в чтобы найти всеИногда требуется узнать, оказалось получим остальные результаты: может являться одинЭта формула для
Нам нужно, чтобы в формуле. ФункцияВ1 000 000 ₽диапазон_суммирования и сообщить, помогла 12-уровневый вложенный оператор проще читать, вы дополнительных изменений неЗначение, которое должно возвращаться, формулой ЕСЛИ, необходимо, зеленой ячейке: продажи у менеджеров ли хотя быПример 3. Субсидии в из только двух примера взята из в ячейке B в Excel «ЕСЛИ»
Excel функция «ЕСЛИ»70 000 ₽опущен, Excel суммирует ли она вам, ЕСЛИ. Есть и можете вставить разрывы потребуется. Но что
если что бы еслиЕсли вы раньше не с фамилией из одно предположение верным. размере 30% начисляются возможных вариантов (ИСТИНА, статьи «Выбрать сразу 4 было написано пригодится в таблицах,сравнивает данные в2 500 000 ₽ ячейки, указанные в с помощью кнопок другие, менее очевидные, строк в строке если вам потребуетсялог_выражение
в ячейке А1 сталкивались с такой
пяти букв, можно В этом случае семьям со средним ЛОЖЬ). При совместном много данных из «100», если в чтобы посчитать определенные ячейке с теми2 000 000 ₽ аргументе внизу страницы. Для преимущества: формул. Просто нажмите разделить оценки наимеет значение ИСТИНА. «1»- то слово замечательной возможностью Excel
Примеры формул с использованием функций ИЛИ И ЕСЛИ в Excel
использовать критерий удобно использовать функцию уровнем дохода ниже использовании с функцией таблицы Excel» здесь. ячейке A 4 данные, в анкетах, условиями, которые мы140 000 ₽
диапазон удобства также приводимТаблицы ссылок функции ВПР клавиши ALT+ВВОД перед A+, A изначение_если_ложь «Банан» если «2» как формулы массива,. ИЛИ, которая выполняет 8000 рублей, которые ЕСЛИ, они способны Этой формулой выбираем будет стоять слово в тестах, т.д. прописали в формуле3 000 000 ₽
Примеры использования формул с функциями ЕСЛИ, И, ИЛИ в Excel
(те же ячейки, ссылку на оригинал открыты и их текстом, который хотите A– (и т. д.)?
- — то слово то советую почитать. А чтобы найти все проверку одного или являются многодетными или значительно расширить ее из таблицы товара
- «Да». В ячейкеФункция Excel «ЕСЛИ» и, в зависимости
210 000 ₽ к которым применяется (на английском языке).
перенести на другую
Теперь ваши четыре
- (необязательный)
- «Апельсин» если «3» предварительно про них продажи менеджеров, у нескольких логических выражений
- отсутствует основной кормилец. функционал. и переносим в
В4 написали такуюнаходится на закладке от результата, считает4 000 000 ₽ условие).
ФункцияЗначения в таблицах просто строку. условных оператора ЕСЛИЗначение, которое должно возвращаться, то слово «Яблоко».
много хорошего здесь.
Формула с функциями ЕСЛИ и СРЗНАЧ для отбора значений при условии
которых фамилия начинается и возвращает логическое Если число детейПример 1. При расчете лист заказа выбранные формулу. «Формулы» в разделе так, как мы280 000 ₽В аргументеСУММЕСЛИ обновлять, и вамПеред вами пример сценария нужно переписать с еслиПодскажите, пожалуйста, как Ну, а в на букву «П», ИСТИНА, если результат
стоимости количества потребленных покупателем позиции.
- написали в формуле.Формулаусловие
- используется, если необходимо не потребуется трогать для расчета комиссионных учетом 12 условий!лог_выражение это сделать. Буду нашем случае задача
- а заканчивается на вычислений хотя бы субсидии – 50%.
кВт электроэнергии дляЕщё есть функцияОбратите внимание! «Логические». Встаем на В статье «Функция
Формула с логическими функциями И ЕСЛИ ИЛИ в Excel
Описаниеможно использовать подстановочные просуммировать значения диапазона, формулу, если условия с неправильной логикой: Вот так будетимеет значение ЛОЖЬ. очень признателен. решается одной формулой: «В» — критерий одного из них Определить, кому полагаются абонентов учитывают следующие
«СЧЁТЕСЛИ» в Excel
В третьем условии ячейку В1, вызываем «ЕСЛИ» в Excel»
знаки: вопросительный знак
- соответствующие указанному условию.
- изменятся.Видите, что происходит? Посмотрите выглядеть ваша формула:Excel позволяет использовать до
- M73568=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)П*В является логическим ИСТИНА.
субсидии, а кому условия: — она считает в формуле мы функцию «ЕСЛИ».
Особенности использования логических функций ЕСЛИ, И, ИЛИ в Excel
мы рассмотрели, как=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)
Предположим, например, чтоЕсли вы не хотите, порядок сравнения доходов=ЕСЛИ(B2>97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«; ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»)))))))))))) 64 вложенных функций:После ввода этой формулы. Строчные и прописные
Например, требуется узнать,
– нет.Если в квартире проживают количество ячеек по поставили не «нуль»,Первый пример. написать такую формулу.Сумма комиссионных за имущество? в столбце с чтобы люди видели в предыдущем примере.Она по-прежнему точна и ЕСЛИ, но это200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(A1;<1;"Банан":2;"Апельсин":3;"Яблоко">;2;) необходимо нажать не буквы не различаются. является ли e3
Вид исходной таблицы:
менее 3 человек нашим условиям. Смотрите а две двойныеНам нужно выбратьЗдесь рассмотрим, стоимостью больше 1) и звездочку ( числами необходимо просуммировать вашу таблицу ссылок А как все будет правильно работать, вовсе не означает,M73568 Enter, как обычно,Диапазон_суммирования целым числом или
Для проверки критериев согласно или за месяц статью «Функция «СЧЁТЕСЛИ кавычки – это из столбца Акак написать несколько условий 600 000 ₽.* только значения, превышающие
или вмешивались в
но вы потратите что так и: Ну или если а- это те числом, которое меньше условию задачи запишем было потреблено менее в Excel». Эту значит «Пусто» (оставить числа «2» и функции «ЕСЛИ»630 000 ₽). Вопросительный знак соответствует 5. Для этого нее, просто поместите Именно! Сравнение идет много времени, чтобы надо делать. Почему? принципиально нужно ЕСЛИ
ячейки, значения которых 100? Для проверки формулу: 100 кВт электроэнергии, функция может посчитать ячейку пустой). Скопировали написать в столбцедля проверки данных=СУММЕСЛИ(A2:A5; «>160000») одному любому символу, можно использовать следующую ее на другой
снизу вверх (от написать ее, аНужно очень крепко подумать,200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(A1=1;»Банан»;ЕСЛИ(A1=2;»Апельсин»;ЕСЛИ(A1=3;»Яблоко»;»Что-то ещё»))) Enter мы хотим сложить,
Выборочные вычисления по одному или нескольким критериям
Постановка задачи
такого условия можно5;50%;30%);»нет»)’ >
ставка за 1 ячейки, в которых формулу в ячейку В то значение,
Способ 1. Функция СУММЕСЛИ, когда одно условие
в нескольких ячейках,Сумма по имуществу стоимостью а звездочка — любой формулу: лист. 5 000 до 15 000 ₽), потом протестировать. Еще чтобы выстроить последовательностьTsibON- тогда Excel т.е. нашем случае - использовать следующую формулу:Описание аргументов: кВт составляет 4,35 текст (например, «да» В5. которое мы укажем прежде, чем считать больше 1 600 000 последовательности символов. Если=СУММЕСЛИ(B2:B25;»>5″)
Теперь есть функция УСЛОВИЯ, а не наоборот. одна очевидная проблема
- из множества операторов: Спасибо. Вторая формула воспримет ее как стоимости заказов.=ЕСЛИ(ИЛИ(ОСТАТ(EXP(3);1)<>0;EXP(3)И(B3 рубля. — в анкете).Третий пример.
- в формуле. Можно по нашим условиям. ₽. требуется найти непосредственноЭто видео — часть которая может заменить Ну и что состоит в том, ЕСЛИ и обеспечить очень помогла формулу массива иЕсли условий больше одногоЗапись «<>» означает неравенство,Вложенная функция ЕСЛИ выполняетВ остальных случаях ставка Или ячейки сВ следующей формуле выбрать любые числа,Рассмотрим два условия9 000 000 ₽ вопросительный знак (или учебного курса сложение несколько вложенных операторов в этом такого? что вам придется их правильную отработкуAlexM сам добавит фигурные (например, нужно найти
- то есть, больше проверку на количество за 1кВт составляет числом или числами поставили в третьем слова, т.д.
Способ 2. Функция СУММЕСЛИМН, когда условий много
в формуле не=СУММЕСЛИ(A2:A5;300000;B2:B5) звездочку), необходимо поставить чисел в Excel. ЕСЛИ. Так, в Это важно, потому вручную вводить баллы по каждому условию: Если с ЕСЛИ скобки. Вводить скобки сумму всех заказов либо меньше некоторого детей в семье, 5,25 рубля. больше, например, 5. условии знак «Тире».Появившееся диалоговое окно для получения результатаСумма комиссионных за имущество перед ним знакСоветы: нашем первом примере что формула не и эквивалентные буквенные
на протяжении всей не принципиально. с клавиатуры не Григорьева для «Копейки»), значения. В данном которой полагаются субсидии.Рассчитать сумму к оплате Не только посчитать =ЕСЛИ(A5=»Да»;100;»-«) заполнили так. (например, если больше
стоимостью 3 000 000 «тильда» ( оценок с 4 может пройти первую оценки. Каковы шансы, цепочки. Если при200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(<"Банан":"Апельсин":"Яблоко">;A1)
Способ 3. Столбец-индикатор
надо. Легко сообразить, то функция случае оба выраженияЕсли основное условие вернуло за месяц для ячейки, но иВ ячейку В6В формуле «ЕСЛИ» нужно 5, то так, ₽.
При необходимости условия можно вложенными функциями ЕСЛИ:
оценку для любого
что вы не вложении вы допуститеили что этот способСУММЕСЛИ (SUMIF) возвращают значение ИСТИНА, результат ЛОЖЬ, главная нескольких абонентов. вычислить процент определенных написали такую формулу. написать три условия. а если меньше210 000 ₽). применить к одному=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))
Способ 4. Волшебная формула массива
значения, превышающего 5 000 ₽. ошибетесь? А теперь в формуле малейшуюКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВЫБОР(A1;»Банан»;»Апельсин»;»Яблоко») (как и предыдущий)не поможет, т.к. и результатом выполнения функция ЕСЛИ вернетВид исходной таблицы данных: ответов к общему
=ЕСЛИ(A6=»%»;1;»нет») Здесь в
В формуле эти 5, то так),=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)Функция СУММЕСЛИ возвращает неправильные диапазону, а просуммироватьможно сделать все гораздо Скажем, ваш доход представьте, как вы неточность, она можетSerge_007 легко масштабируется на не умеет проверять функции ЕСЛИ будет текстовую строку «нет».Выполним расчет по формуле: числу ответов. третьем условии написали условия напишутся через а еще и
Способ 4. Функция баз данных БДСУММ
Сумма комиссионных за имущество, результаты, если она соответствующие значения из проще с помощью составил 12 500 ₽ — оператор пытаетесь сделать это сработать в 75 %: Если вообще не три, четыре и больше одного критерия. текстовая строка «верно».Выполним расчет для первойОписание аргументов:Есть ещё функция слово «нет» в точку с запятой.
условия результатов данных
Как в Excel в формуле задать несколько условий если?
стоимость которого превышает используется для сопоставления другого диапазона. Например, одной функции ЕСЛИМН: ЕСЛИ вернет 10 %, 64 раза для случаев, но вернуть принципиально использование функций, т.д. условий без Поэтому начиная с Однако, если бы семьи и растянемИЛИ(B3 в Excel «СУММЕСЛИ». кавычках. Получилось так.Первое условие –
в двух ячейках значение в ячейке строк длиннее 255 символов
формула=ЕСЛИМН(D2>89;»A»;D2>79;»B»;D2>69;»C»;D2>59;»D»;ИСТИНА;»F») потому что это более сложных условий!
непредвиденные результаты в то: каких-либо ограничений. версии Excel 2007
выполнялась проверка ИЛИ(ОСТАТ(EXP(3);1)<>0;EXP(3)0 формулу на остальные
C3*4,35 – сумма к Про эту функциюЧетвертый пример.
Добавление в формулу ЕСЛИ несколько условий
«Лог_выражение». Мы написали (не в одной).
C2. или применяется к=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)Функция ЕСЛИМН — просто находка! больше 5 000 ₽, и
Конечно, это возможно. остальных 25 %. К200?’200px’:»+(this.scrollHeight+5)+’px’);»>=A1-2В категории в набор функций возвращает ИСТИНА. ячейки, используя функцию оплате, если функция смотрите в статьеВ ячейку С1 А1=2 – этоУ нас такая490 000 ₽
строке #ЗНАЧ!.суммирует только те Благодаря ей вам
на этом остановится. Но неужели вам сожалению, шансов отыскать
Формат ячейки:Базы данных (Database) была добавлена функция
На практике часто используют
автозаполнения. Полученные результаты: ИЛИ вернет значение «Как посчитать в
напишем формулу, где значит, если в таблица с данными.
Скопируйте образец данных из
Аргумент
значения из диапазона
больше не нужно Это может быть хочется потратить столько эти 25 % немного.
Яблоко;Банан;»Апельсин» можно найти функциюСУММЕСЛИМН (SUMIFS)
Формула ЕСЛИ в Excel — примеры с несколькими условиями
Заданное вами значение при выполнении двух условий ИСТИНА или ЛОЖЬ.
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.
Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.
Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.
При написании формулы легко запутаться, поэтому рекомендуется смотреть на всплывающую подсказку.
В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку
Синтаксис функции ЕСЛИ
Вот как выглядит синтаксис этой функции и её аргументы:
=ЕСЛИ(логическое выражение, значение если «да», значение если «нет»)
Логическое выражение – (обязательное) условие, которое возвращает значение «истина» или «ложь» («да» или «нет»);
Значение если «да» – (обязательное) действие, которое выполняется в случае положительного ответа;
Значение если «нет» – (обязательное) действие, которое выполняется в случае отрицательного ответа;
Давайте вместе подробнее рассмотрим эти аргументы.
Первый аргумент – это логический вопрос. И ответ этот может быть только «да» или «нет», «истина» или «ложь».
Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “<”, “>=”, “<=”, “<>”.
Расширение функционала с помощью операторов «И» и «ИЛИ»
Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функции И и ИЛИ могут проверить до 30 условий.
Пример использования оператора И:
Пример использования функции ИЛИ:
Простейший пример применения.
Предположим, вы работаете в компании, которая занимается продажей шоколада в нескольких регионах и работает с множеством покупателей.
Нам необходимо выделить продажи, которые произошли в нашем регионе, и те, которые были сделаны за рубежом. Для этого нужно добавить в таблицу ещё один признак для каждой продажи – страну, в которой она произошла. Мы хотим, чтобы этот признак создавался автоматически для каждой записи (то есть, строки).
В этом нам поможет функция ЕСЛИ. Добавим в таблицу данных столбец “Страна”. Регион “Запад” – это местные продажи («Местные»), а остальные регионы – это продажи за рубеж («Экспорт»).
Применение «ЕСЛИ» с несколькими условиями
Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму.
Рассмотрим наглядно на примере все той же таблицы. Но на этот раз усложним задачу. Теперь нужно проставить скидку на женскую обувь в зависимости от вида спорта.
Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%.
Пропишем формулу для этих условий в нужной нам ячейке.
=ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))
Щелкаем Enter и получаем результат согласно заданным условиям.
Далее растягиваем формулу на все оставшиеся строки таблицы.
Операторы сравнения чисел и строк
Операторы сравнения чисел и строк представлены операторами, состоящими из одного или двух математических знаков равенства и неравенства:
- < – меньше;
- <= – меньше или равно;
- > – больше;
- >= – больше или равно;
- = – равно;
- <> – не равно.
- Результат – любая числовая переменная;
- Выражение – выражение, возвращающее число или строку;
- Оператор – любой оператор сравнения чисел и строк.
Если переменная Результат будет объявлена как Boolean (или Variant), она будет возвращать значения False и True. Числовые переменные других типов будут возвращать значения 0 (False) и -1 (True).
Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом, VBA Excel сгенерирует ошибку Type Mismatch (несоответствие типов данных):
Сравнение строк начинается с их первых символов. Если они оказываются равны, сравниваются следующие символы. И так до тех пор, пока символы не окажутся разными или одна или обе строки не закончатся.
Значения буквенных символов увеличиваются в алфавитном порядке, причем сначала идут все заглавные (прописные) буквы, затем строчные. Если необходимо сравнить длины строк, используйте функцию Len.
Одновременное выполнение двух условий
Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И».
Рассмотрим на примере нашей таблицы. Теперь скидка 30% будет проставлена только, если это женская обувь и предназначена для бега. При соблюдении этих условий одновременно значение ячейки будет равно 30%, в противном случае – 0.
Для этого используем следующую формулу:
=ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)
Нажимаем клавишу Enter, чтобы отобразить результат в ячейке.
Аналогично примерам выше, растягиваем формулу на остальные строки.
Общее определение и задачи
«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.
Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь])» .
Как правильно записать?
Устанавливаем курсор в ячейку G2 и вводим знак “=”. Для Excel это означает, что сейчас будет введена формула. Поэтому как только далее будет нажата буква “е”, мы получим предложение выбрать функцию, начинающуюся этой буквы. Выбираем “ЕСЛИ”.
Далее все наши действия также будут сопровождаться подсказками.
В качестве первого аргумента записываем: С2=”Запад”. Как и в других функциях Excel, адрес ячейки можно не вводить вручную, а просто кликнуть на ней мышкой. Затем ставим “,” и указываем второй аргумент.
Второй аргумент – это значение, которое примет ячейка G2, если записанное нами условие будет выполнено. Это будет слово “Местные”.
После этого снова через запятую указываем значение третьего аргумента. Это значение примет ячейка G2, если условие не будет выполнено: “Экспорт”. Не забываем закончить ввод формулы, закрыв скобку и затем нажав “Enter”.
Наша функция выглядит следующим образом:
Наша ячейка G2 приняла значение «Местные».
Теперь нашу функцию можно скопировать во все остальные ячейки столбца G.
Дополнительная информация
- В функции IF (ЕСЛИ) может быть протестировано 64 условий за один раз;
- Если какой-либо из аргументов функции является массивом – оценивается каждый элемент массива;
- Если вы не укажете условие аргумента FALSE (ЛОЖЬ) value_if_false (значение_если_ложь) в функции, т.е. после аргумента value_if_true (значение_если_истина) есть только запятая (точка с запятой), функция вернет значение “0”, если результат вычисления функции будет равен FALSE (ЛОЖЬ).
На примере ниже, формула =IF(A1> 20,”Разрешить”) или =ЕСЛИ(A1>20;”Разрешить”) , где value_if_false (значение_если_ложь) не указано, однако аргумент value_if_true (значение_если_истина) по-прежнему следует через запятую. Функция вернет “0” всякий раз, когда проверяемое условие не будет соответствовать условиям TRUE (ИСТИНА).|
- Если вы не укажете условие аргумента TRUE(ИСТИНА) (value_if_true (значение_если_истина)) в функции, т.е. условие указано только для аргумента value_if_false (значение_если_ложь), то формула вернет значение “0”, если результат вычисления функции будет равен TRUE (ИСТИНА);
На примере ниже формула равна = IF (A1>20;«Отказать») или =ЕСЛИ(A1>20;”Отказать”) , где аргумент value_if_true (значение_если_истина) не указан, формула будет возвращать “0” всякий раз, когда условие соответствует TRUE (ИСТИНА).
Вложенные условия с математическими выражениями.
Вот еще одна типичная задача: цена за единицу товара изменяется в зависимости от его количества. Ваша цель состоит в том, чтобы написать формулу, которая вычисляет цену для любого количества товаров, введенного в определенную ячейку. Другими словами, ваша формула должна проверить несколько условий и выполнить различные вычисления в зависимости от того, в какой диапазон суммы входит указанное количество товара.
Эта задача также может быть выполнена с помощью нескольких вложенных функций ЕСЛИ. Логика та же, что и в приведенном выше примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными условиями (т.е. соответствующей ценой за единицу).
Предполагая, что количество записывается в B8, формула будет такая:
=B8*ЕСЛИ(B8>=101; 12; ЕСЛИ(B8>=50; 14; ЕСЛИ(B8>=20; 16; ЕСЛИ( B8>=11; 18; ЕСЛИ(B8>=1; 22; “”)))))
И вот результат:
Как вы понимаете, этот пример демонстрирует только общий подход, и вы можете легко настроить эту вложенную функцию в зависимости от вашей конкретной задачи.
Например, вместо «жесткого кодирования» цен в самой формуле можно ссылаться на ячейки, в которых они указаны (ячейки с B2 по B6). Это позволит редактировать исходные данные без необходимости обновления самой формулы:
=B8*ЕСЛИ(B8>=101; B6; ЕСЛИ(B8>=50; B5; ЕСЛИ(B8>=20; B4; ЕСЛИ( B8>=11; B3; ЕСЛИ(B8>=1; B2; “”)))))
Аргументы функции
- logical_test (лог_выражение) – это условие, которое вы хотите протестировать. Этот аргумент функции должен быть логичным и определяемым как ЛОЖЬ или ИСТИНА. Аргументом может быть как статичное значение, так и результат функции, вычисления;
- [value_if_true] ([значение_если_истина]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если значение которое вы тестируете соответствует условию ИСТИНА;
- [value_if_false] ([значение_если_ложь]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если условие, которое вы тестируете соответствует условию ЛОЖЬ.
А если один из параметров не заполнен?
Если вас не интересует, что будет, к примеру, если интересующее вас условие не выполняется, тогда можно не вводить второй аргумент. К примеру, мы предоставляем скидку 10% в случае, если заказано более 100 единиц товара. Не указываем никакого аргумента для случая, когда условие не выполняется.
Что будет в результате?
Насколько это красиво и удобно – судить вам. Думаю, лучше все же использовать оба аргумента.
И в случае, если второе условие не выполняется, но делать при этом ничего не нужно, вставьте в ячейку пустое значение.
Однако, такая конструкция может быть использована в том случае, если значение «Истина» или «Ложь» будут использованы другими функциями Excel в качестве логических значений.
Обратите также внимание, что полученные логические значения в ячейке всегда выравниваются по центру. Это видно и на скриншоте выше.
Более того, если вам действительно нужно только проверить какое-то условие и получить «Истина» или «Ложь» («Да» или «Нет»), то вы можете использовать следующую конструкцию –
Обратите внимание, что кавычки здесь использовать не нужно. Если вы заключите аргументы в кавычки, то в результате выполнения функции ЕСЛИ вы получите текстовые значения, а не логические.
Функция ЕПУСТО
Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:
=ЕПУСТО(значение)
Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.
Функции ИСТИНА и ЛОЖЬ
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:
=ИСТИНА()
=ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение “Проходите”, если выражение в ячейке А1 имеет значение ИСТИНА:
В противном случае формула возвратит “Стоп”.
Составное условие
Составное условие состоит из простых, связанных логическими операциями И() и ИЛИ().
И() – логическая операция, требующая одновременного выполнения всех условий, связанных ею.
ИЛИ() – логическая операция, требующая выполнения любого из перечисленных условий, связанных ею.
Простое условие
Что же делает функция ЕСЛИ()? Посмотрите на схему. Здесь приведен простой пример работы функции при определении знака числа а.
Блок-схема “Простое условие”. Определение отрицательных и неотрицательных чисел
Условие а>=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. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки.
Пример функции с несколькими условиями
В функцию «ЕСЛИ» можно также вводить несколько условий. В этой ситуации применяется вложение одного оператора «ЕСЛИ» в другой. При выполнении условия в ячейке отображается заданный результат, если же условие не выполнено, то выводимый результат зависит уже от второго оператора.
- Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
- Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))» .
- Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».
Пример использования «ЕСЛИ»
Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «ЕСЛИ».
- Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
- Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».
Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)
При использовании функции IF (ЕСЛИ) в Excel, вы можете использовать различные операторы для проверки состояния. Вот список операторов, которые вы можете использовать:
Если сумма баллов больше или равна “35”, то формула возвращает “Сдал”, иначе возвращается “Не сдал”.
Заключение
Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ, которая проверяет данные на совпадение заданным нами условиям и выдает результат в автоматическом режиме, что исключает возможность ошибок из-за человеческого фактора. Поэтому, знание и умение применять этот инструмент позволит сэкономить время не только на выполнение многих задач, но и на поиски возможных ошибок из-за “ручного” режима работы.