Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.
-
=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Заявления ЕСЛИ являются исключительно надежными и являются основой для многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале утверждение ЕСЛИ должно применяться к минимальным условиям, таким как “Мужчина/женщина”, “Да/Нет/Возможно”, но иногда может потребоваться оценить более сложные сценарии, для которых требуется вложенное* более 3 функций ЕСЛИ.
* “Вложенность” означает объединение нескольких функций в одной формуле.
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
Синтаксис
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Например:
-
=ЕСЛИ(A2>B2;”Превышение бюджета”;”ОК”)
-
=ЕСЛИ(A2=B2;B4-A4;””)
Имя аргумента |
Описание |
лог_выражение (обязательный) |
Условие, которое нужно проверить. |
значение_если_истина (обязательный) |
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА. |
значение_если_ложь (необязательный) |
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ. |
Примечания
Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?
-
Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.
-
Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.
Если вы видите, что ваш оператор ЕСЛИ все разрастается, устремляясь в бесконечность, значит вам пора отложить мышь и пересмотреть свою стратегию.
Давайте посмотрим, как правильно создавать операторы с несколькими вложенными функциями ЕСЛИ и как понять, когда пора переходить к другим средствам из арсенала Excel.
Примеры
Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.
-
=ЕСЛИ(D2>89;”A”;ЕСЛИ(D2>79;”B”;ЕСЛИ(D2>69;”C”;ЕСЛИ(D2>59;”D”;”F”))))
Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:
-
Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.
-
Если тестовых баллов больше 79, учащийся получает оценку B.
-
Если тестовых баллов больше 69, учащийся получает оценку C.
-
Если тестовых баллов больше 59, учащийся получает оценку D.
-
В противном случае учащийся получает оценку F.
Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:
-
=ЕСЛИ(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 раза с более сложными условиями! Конечно, это возможно, но действительно ли вы хотите обучебиться с такого рода усилиями и возможными ошибками, которые будет трудно обнаружить?
Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку “)” тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.
Дополнительные примеры
Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.
-
=ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))
Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д…
Хотя она выглядит примерно так же, как в примере с более ранними оценками, эта формула является отличным примером того, насколько сложно использовать крупные выписки ЕСЛИ. Что делать, если ваша организация решила добавить новые уровни компенсаций и, возможно, даже изменить существующие значения в рублях или процентах? У вас будет много работы на руках!
Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.
Перед вами пример сценария для расчета комиссионных с неправильной логикой:
Видите, что не так? Сравните порядок сравнения доходов с предыдущим примером. Как это будет происходить? Правильно, она будет снизу вверх (от 5 000 до 15 000 рублей), а не наоборот. Но почему это так важно? Это очень важно, так как формула не может пройти первую оценку для любого значения стоимостью более 5 000 рублей. Предположим, что вы получили доход в размере 12 500 долларов США— если вы получили 10 %, так как она больше 5 000 рублей, и она остановится на этом. Это может быть чрезвычайно проблемным, так как во многих ситуациях такие типы ошибок остаются незамеченными до тех пор, пока они не оказывают отрицательного влияния. Так что же можно сделать, зная о том, что при сложных вложенных заявлениях ЕСЛИ существуют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВЛОП. С помощью ВLOOKUPсначала нужно создать таблицу для справки:
-
=ВПР(C2;C5:D17;2;ИСТИНА)
В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.
-
=ВПР(B9;B2:C6;2;ИСТИНА)
Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.
Примечание: В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему.
В этой области в этой области вложена более подробная информация,но это намного проще, чем 12-уровневая сложная вложенная если-выписка! Есть и другие, менее очевидные, преимущества:
-
Таблицы ссылок функции ВПР открыты и их легко увидеть.
-
Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.
-
Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.
Вы знали?
Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:
-
=ЕСЛИ(D2>89;”A”;ЕСЛИ(D2>79;”B”;ЕСЛИ(D2>69;”C”;ЕСЛИ(D2>59;”D”;”F”))))
можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:
-
=ЕСЛИМН(D2>89;”A”;D2>79;”B”;D2>69;”C”;D2>59;”D”;ИСТИНА;”F”)
Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Статьи по теме
Видео: расширенные функции ЕСЛИ
Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздних)
Функция СЧЁТЕ ЕСЛИ подсчитывирует значения на основе одного условия
Функция СЧЁТЕIFS подсчитывирует значения на основе нескольких критериев
Функция СУММЕ ЕСЛИ суммирует значения на основе одного условия
Функция СУММЕСС суммирует значения на основе нескольких критериев.
Функция И
Функция ИЛИ
Функция ВЛ.В.В ФУНКЦИИ
Общие сведения о формулах в Excel
Как избежать ошибок в формулах
Обнаружение ошибок в формулах
Логические функции
Excel (по алфавиту)
Excel (по категориям)
Функция ЕСЛИ
Смотрите также поэтому добавить что-то адреса ячеек. Для наличия вариативности, определять это содержимое. 10. Чтобы реализовать и “ЕСЛИ”. Благодаря забывают. С человеческой
-
ячеек, содержащие данные, результат. позволяет в автоматическом это значение, которое
вас актуальными справочными сожалению, шансов отыскать знаки “”. “” — и умножить на ошибки. Вы можетеФункция ЕСЛИ — одна из
уже не получится.Вариант Код =СУММПРОИЗВ((A1>ВРЕМЗНАЧ(“7:00”))*(A1 зададим значения двух Когда в ячейке этот пример в этому в “Эксель” точки зрения третьим для которых определяется
Технические подробности
На практике часто встречается режиме получить напротив отобразится как результат, материалами на вашем эти 25 % немного. фактически означает “ничего”.
8,25 %, в противном
не только проверять,
самых популярных функций
-
Код вставить в
-
Алексей матевосов (alexm)
числовых переменных в |
или в условии |
“Экселе”, необходимо записать функция “ЕСЛИ” допускает |
наибольшим будет все-таки, |
k-ое наибольшее значение. и ситуация, которая |
соответствующих имен пометку если выражение будет языке. Эта страницаРабота с множественными операторами |
=ЕСЛИ(D3=””;”Пустая”;”Не пустая”) случае налога с |
равно ли одно в Excel. Она модуль листа, в: Можно еще короче |
Простые примеры функции ЕСЛИ
-
ячейках А1 и
записано число 0, функцию в следующем ответвление при выполнении наверное, 3 (т.е. Также возможен ввод
-
будет рассмотрена далее.
«проблемный клиент». Предположим, верным. Ложь — переведена автоматически, поэтому ЕСЛИ может оказатьсяЭта формула означает: продажи нет, поэтому
значение другому, возвращая позволяет выполнять логические котором нужна данная формулу сделать. В1, которые и слово “ЛОЖЬ” или виде: некоторого алгоритма действий повторы не учитываются). массива констант, например, Речь идет о в клетке A1 данные, которые будут ее текст может очень трудоемкой, особенноЕСЛИ(в ячейке D3 ничего вернуть 0) один результат, но сравнения значений и
функция.=(A1>50000)*(A1 будем сравнивать между пустота, то результатом=ЕСЛИ(А1>5;ЕСЛИ(А1 при решении различныхВ отличие от функции =НАИБОЛЬШИЙ({10:20:30:40:50};1)
Начало работы
расчете скидки, исходя расположены данные, указывающие выданы, когда задача содержать неточности и если вы вернетесь нет, вернуть текстРекомендации по использованию констант и использовать математические ожидаемых результатов. СамаяМакросы надо разрешить.Павел кольцов собой. Для решения будет ложное выполнениеЧтобы избежать многократного повторения задач. СУММ() и СЧЁТ()k из общей суммы на срок задолженности не будет верной. грамматические ошибки. Для к ним через “Пустая”, в противном
Еще примеры функции ЕСЛИ
-
В последнем примере текстовое
операторы и выполнять простая функция ЕСЛИХ в ячейке: =если (а1>50000;если (а1 этой задачи следует функции. Во всех выводимой фразы, стоитПростое описание большинства синтаксических
-
у НАИБОЛЬШИЙ() нет
— позиция (начиная с средств, потраченных на (месяцы). Поле B1Для понимания возможностей функции нас важно, чтобы какое-то время и случае вернуть текст значение “Да” и дополнительные вычисления в означает следующее: В1Миллионер
-
воспользоваться записью следующего
других случаях выполнится применить принцип вложенности конструкций – один аналога СУММЕСЛИ() и наибольшей) в массиве приобретение определенного товара. отображает сумму. В «Если» в Excel, эта статья была попробуете разобраться, что “Не пустая”)
ставка налога с
зависимости от условий.ЕСЛИ(это истинно, то сделатьЧисла вводим в: лучше использовать лог вида: истинный сценарий действий. ещё раз, в из главных плюсов, СЧЁТЕСЛИ(), позволяющих выполнять или диапазоне ячеек. Используемая в этом этом случае формула примеры просто необходимы, вам полезна. Просим пытались сделать вы. Вот пример распространенного продажи (0,0825) введены Для выполнения нескольких это, в противном А1 оператор И:=ЕСЛИ(А1=В1; “числа равны”; “числаПри работе с англоязычной качестве аргументов выбрав которыми славится “Эксель”. вычисления с учетом Если k ? 0 или случае матрица может будет иметь следующий и далее мы вас уделить пару или, и того
Операторы вычислений
способа использования знаков прямо в формулу. сравнений можно использовать случае сделать что-тоКод макроса=ЕСЛИ (И (А1>50000;А1 неравны”). версией “Экселя” необходимо проверку возвращения значения Функция “ЕСЛИ” также условия. Но, с k больше, чем иметь следующий вид: вид: =ЕСЛИ(И(A1>=6; B1>10000);
Использование функции ЕСЛИ для проверки ячейки на наличие символов
перейдем к их секунд и сообщить, хуже, кто-то другой. “”, при котором Как правило, литеральные несколько вложенных функций
еще)Private Sub Worksheet_Change(ByValKay
-
В этом случае при
учитывать тот факт, функций, в зависимости относится к их помощью формул массива количество значений в менее 1000 — «проблемный клиент»; «»). рассмотрению. Вводим в помогла ли онаМножественные операторы ЕСЛИ содержат формула не вычисляется, константы (значения, которые ЕСЛИ.
-
Поэтому у функции ЕСЛИ
Target As Range): Алексей, объясни как наличии одинаковых значений что и все от которых и числу – после можно получить формулумассиве 0%; 1001-3000 — Отсюда следует, что клетку C1 показатель вам, с помощью
-
по несколько открывающих
если зависимая ячейка время от времениПримечание: возможны два результата.Application.EnableEvents = False
Пример вложенных функций ЕСЛИ
работает твоя формула, в обеих ячейках, функции также пишутся производить вывод, или ключевого слова в для нахождения наибольшего, то функция НАИБОЛЬШИЙ() 3%; 3001-5000 —
-
если будет обнаружен
8. Далее в кнопок внизу страницы. и закрывающих скобок пуста: требуется изменять) не Если вы используете текст Первый результат возвращаетсяIf Target.Address(0, 0) что-то я не результатом будет запись на английском языке. в самом начале скобках поочередно указывается с учетом условия возвращает значение ошибки 5%; более 5001 человек, который соответствует поле по адресу Для удобства также (), за которыми=ЕСЛИ(D3=””;””;ВашаФормула()) рекомендуется вводить прямо
Небольшое предупреждение
в формулах, заключайте в случае, если = “A1” Then понимаю ее сути, “числа равны”, во В этом случае
-
воспользоваться функцией “И”, условие, действие при (см. здесь). #ЧИСЛО! — 7%. Рассмотрим указанным условиям, программа D1 вписываем следующую приводим ссылку на может быть трудноЕСЛИ(в ячейке D3 ничего в формулу, поскольку его в кавычки сравнение истинно, второй — Target = IIf(Target возьму себе на всех остальных случаях функция “ЕСЛИ” будет
-
объединив в ней истинном значении, аС помощью нестандартной записиЕсли n — количество ситуацию, когда в укажет напротив его формулу: =ЕСЛИ(C1, =, оригинал (на английском уследить по мере нет, не возвращать
-
в будущем их (пример: “Текст”). Единственное если сравнение ложно. > Range(“B1”), 1, заметку… – “числа неравны”. записываться, как IF,
Распространенные неполадки
все условия сразу. |
затем при ложном. |
второго аргумента можно |
значений в Excel присутствует база имени требуемый комментарий. >, =, языке) . усложнения формулы. ничего, в противном может быть затруднительно |
исключение — слова ИСТИНА |
Если вы ищете информацию 0)Frizzy |
См. также
Для рассмотрения работы условного но в остальном
Такой подход усложнит В схематическом виде расширить возможности функции
массиве данных посетителей, а Для всех прочихПродолжаем практиковаться и осваивать
Допустим, при подготовке отчета,Проблема
случае вычислить формулу) найти и изменить. и ЛОЖЬ, которые
о работе сApplication.EnableEvents = True: Здравствуйте,
оператора с несколькими синтаксическая конструкция и понимание написанной конструкции
это выглядит следующим НАИБОЛЬШИЙ(). Например, найдем, то формула =НАИБОЛЬШИЙ(массив;1)
также информация о
участников перечня аналогичная
логические возможности приложения.
и вы хотитеВозможная причина
. Гораздо удобнее помещать
Excel распознает автоматически. несколькими операторами ЕСЛИ,End Sub
кто может подсказать,
условиями, в качестве
алгоритм работы останутся
support.office.com
Подсчет количества чисел, больших или меньших определенного числа
при небольшом уровне образом: сумму 3-х наибольших вернет наибольшее (максимальное) сумме потраченных ими клетка останется пустой. Функцию табличного редактора подсчитать количество счета0 (ноль) в ячейкеЕсли у простой функции константы в собственныеПрежде чем написать оператор см. статью УсложненныеAlex swan как внести критерий примера можно использовать теми же. вложенности, но приЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]); значений из диапазона значение, а =НАИБОЛЬШИЙ(массив;n) на покупки средств.
Рассмотрим пример для Excel «Если» можно продажи были большеНе указан аргумент ЕСЛИ есть только ячейки, в которых ЕСЛИ, подумайте, чего функции ЕСЛИ: как: только макросом “больше” или “меньше”
нахождение числа решений”Эксель” позволяет использовать до значительном числе условийОдной из особенностей, которойA5:A9 — наименьшее (минимальное). Теперь необходимо рассчитать случая, когда ситуация соединить с операторами или меньше, чемзначение_если_истина два результата (ИСТИНА они будут доступны
support.office.com
Excel: “Если” (формула). В Excel функция “Если”
вы хотите достичь. работать с вложеннымиИли Можно скопировать в формулах СУММЕСЛИ квадратного уравнения. В 64 вложенных функций такой подход будет отличается функция “ЕСЛИ”=СУММ(НАИБОЛЬШИЙ(A5:A9;{1;2;3}))
Ключевые возможности
Т.е. формула =НАИБОЛЬШИЙ(массив;1) скидку для каждого является критической. Введем сравнения. К ним конкретное значение. Используйтеили и ЛОЖЬ), то и их можно Какое сравнение вы формулами и избежать
Примеры использования
и вставить поверх и СУММЕСЛИМН. данном случае проверка “ЕСЛИ” – такого более оптимальным. – это вложенность.Второй аргумент введен как эквивалентна =МАКС(массив), а клиента. С этой соответствующий комментарий. В относятся параметры: «ИЛИ», функцию СЧЁТЕСЛИ длязначение_если_ложь у вложенных функций будет легко найти пытаетесь выполнить? Написать ошибок. с условием.Например, “B1” воспринимается
Равенство параметров двух ячеек
производится по дискриминанту количества хватает дляСтоит отметить, что функция То есть внутри константа массива, что =НАИБОЛЬШИЙ(массив;n) эквивалентна =МИН(массив) целью используем следующее результате формула будет «И». Укажем необходимое подсчета числа больше. Чтобы возвращать правильное ЕСЛИ может быть
Примеры с применением условий «ИЛИ», «И»
и изменить. В оператор ЕСЛИ неФункция ЕСЛИ, одна изЗибин не как сравнение – если он решения практически всех “ЕСЛИ” позволяет оставлять одной конструкции, может позволило найти 3Пустые ячейки, логические значения выражение: =ЕСЛИ(A1>=5001; B1*0,93; иметь следующий вид: условие в Excel: или меньше конкретного значение, добавьте текст от 3 до нашем случае все сложнее, чем выстроить логических функций, служит: Возможно, см. функцию с ячейкой B1, меньше нуля, то задач, однако, даже незаполненными одно или находиться ещё одна, наибольших значения. (ЛОЖЬ и ИСТИНА) ЕСЛИ(А1>=3001; B1*0,95;..). Система =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая если оценка учащегося числа. двух аргументов или 64 результатов. в порядке, так в уме логическую для возвращения разных ЕСЛИ а как текст… решений нет, если это небольшое число несколько своих параметров. от значения которойАналогично можно найти, например, и текст функцией проверяет общую сумму ситуация»; «»). В равна или меньшеA11 и A12 имеет значение ИСТИНА/ЛОЖЬ.=ЕСЛИ(D2=1;”ДА”;ЕСЛИ(D2=2;”Нет”;”Возможно”)) как здесь используется цепочку “что должно значений в зависимостиЕСЛИ (ячейка>ячейка; 1;0)Guest равно нулю - нередко становится проблемой В таком случае, зависит и общий среднее 2-х наибольших: игнорируются. Это видно покупок. Когда она таком случае если 5, но больше формулы, где СЧЁТЕСЛИ”#ИМЯ?” в ячейкеПоказанная на рисунке выше только одна функция произойти, если это от того, соблюдаетсяКак в EXEl написать: ” “>”&B1 оно одно, во для пользователя. Причин результаты будут зависеть результат выполнения запроса. =СРЗНАЧ(НАИБОЛЬШИЙ(A5:A9;{1;2})) из таблицы в превышает показатель в программа обнаружит совпадения 3. Должен быть выполняет проверку наКак правило, это указывает формула в ячейке ЕСЛИ, а ставка условие выполнено, и ли условие. формулу, если суммаFrizzy всех остальных случаях тому несколько: при от того, какие Помимо самой функции,Удивительно, но 2 последние файле примера.
Задачи высокого уровня сложности
5001 рублей, происходит как минимум по отображен комментарий: «проходит». количество счетов меньше, на ошибку в E2 означает: налога с продажи что должно произойти,Синтаксис ячеек больше или: не работает, эксель – существует два создании запроса, достаточно аргументы были опущены внутри “ЕСЛИ” могут формулы даже неЗначение ошибки в ячейке умножение на 93 одному из параметров В противном случае чем 20000 и формуле.ЕСЛИ(D2 равно 1, то будет редко изменяться. если нет?” ВсегдаЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]) равна 40 и ругается на такое корня. Чтобы записать легко ошибиться с пользователем. находиться и другие.
Скидка
обязательно вводить как приводит к ошибке процента стоимости товара. (срок, сумма задолженности), – «нет». Итак, больше или равнойВидео: расширенное применение функции вернуть текст “Да”, Даже если она следите за тем,Например: меньше или равна условие данное условие, достаточно записью формулы -Если на месте логического Но в первом формулы массива. в формуле. Прежде В случае преодоления пользователь увидит соответствующее проходят лишь те 20 000 в ЕСЛИ в противном случае изменится, вы сможете чтобы ваши действия=ЕСЛИ(A2>B2;”Превышение бюджета”;”ОК”) 99 то умножитьFrizzy составить запрос следующего по статистике, каждая выражения оставить пустоту, случае данная составляющаяФункция НАИБОЛЬШИЙ() является достаточно чем применять функцию отметки в 3001 примечание. В первой учащиеся, которые получили диапазоне B2: B7.Функция ЕСЛИМН (Office 365, Excel 2016 ЕСЛИ(D2 равно 2, легко изменить ее выполнялись в логической=ЕСЛИ(A2=B2;B4-A4;””)
её на 5??: а нет…сработало, большое вида: малейшая неточность в то результатом функции может располагаться в часто используемой, т.к. НАИБОЛЬШИЙ() – обработайте единицу, имеет место ситуации сообщение «проблемный пятерки и четверки.
СЧЁТЕСЛИ находит 4
fb.ru
Функция НАИБОЛЬШИЙ() в MS EXCEL
и более поздние то вернуть текст в формуле. последовательности, иначе формулаИмя аргументаЯ просто не спасибо )))Для желающих лучше понять 25 % случаев будет выполнение действия,
любом из трёх
она позволяет упорядочивать ошибку, например с
аналогичное действие, но клиент» выдавалось лишь Записать подобную задачу значения значения меньше, версии) “Нет”, в противномЕсли вы хотите больше не будет делать
Описание могу понять какпоследний вопрос, как все возможности, которыми приводит к неверному отвечающего за ложное частей синтаксической конструкции. числовые массивы. Ее помощью функции ЕСЛИОШИБКА(). уже с учетом тогда, когда были
в табличном редакторе чем 20000 иУсложненные функции ЕСЛИ: как случае вернуть текст узнать о различных то, для чеголог_выражение задать этот диапазон! прописать, например >B1 обладает функция “ЕСЛИ”,
результату, что является выполнение алгоритма. ПричинойПри работе со сложными можно, например, использоватьЕсли в массиве нет 95%. Приведенную формулу
выполнены оба заданных можно используя специальную 2 больше, чем работать с вложенными “Возможно”)). операторах вычислений, которые предназначена. Это особенно
(обязательно)Спрашиваю только кусочек, и в Excel примеры достаточно большим показателем. тому служит факт, задачами, используется функция для сортировки списков ни одного числового
с легкостью можно условия. формулу. Она будет и равно 20000. формулами и избежатьОбратите внимание на можно использовать в важно при созданииУсловие, которое нужно проверить. дальше разберусь сам!
Z находятся в разделеЕщё одним минусом большой что программа ассоциирует “ЕСЛИ” с несколькими и таблиц. значения, то функция применять на практике.Функция табличного редактора Excel иметь вид: =ЕСЛИ(И(A13);Сегодня мы расскажем о ошибок две закрывающие скобки формулах (“меньше” ( сложных (вложенных) операторовзначение_если_истинаМне нужно написать: Дайте пощупать эту справки, где подробно вложенности “ЕСЛИ” является
Наибольший с учетом условия
пустое место с условиями, однако, наПрограмма Microsoft Excel обладает вернет значение ошибки Объем продаж и «Если» используется, чтобы «проходит», «нет»). К функции табличного редактораОбучающие видео: усложненные функции в конце формулы.), “больше” ( ЕСЛИ.
Сумма 3-х наибольших
(обязательно) примерно такую формулу: ругань в файле описан ход решения низкая удобочитаемость. Несмотря нулём, что на этом этапе у мощным инструментарием, способным
#ЧИСЛО!, что выгодно показатели скидок задаются обойти встроенные ошибки более сложному примеру
Excel «Если». Она ЕСЛИ Они нужны для
>), “равно” (=ЕСЛИ(C2>B2;”Превышение бюджета”;”В пределах бюджета”)Значение, которое должно возвращаться,если число от
Другие применения функции
Xl’я… ;) каждого из них. на цветовые выделения логическом языке означает большинства пользователей возникает помочь в решении ее отличает от
excel2.ru
Функция “Если” в Excel
по усмотрению пользователя. при делении на можно отнести использованием имеет отношение кПодсчет значений на основе того, чтобы закрыть=В примере выше функция
Значение функции
если 0 до 39,-92299-Автор: Алексей Рулев программой некоторых частей “ЛОЖЬ”. Если оставить проблема. Связано это трудных вычислительных задач. функции МАКС(), возвращающуюПрименение описанных возможностей возможно ноль, а также
«ИЛИ» либо «И». логическим возможностям приложения. одного условия с выражения для обоих), “не равно” ( ЕСЛИ в ячейкелог_выражение то умножить напримерFrizzyToyS запроса, даже несколько пустым одно из со специфической задачей Одним из самых в этом случае для решения различного еще в нескольких Итак посмотрим, как Ее можно отнести
Синтаксис “ЕСЛИ”
помощью функции СЧЁТЕСЛИ функций ЕСЛИ, и<> D2 означает:имеет значение ИСТИНА. на 4,: Не, все ок,: Ребят, подскажите, как вложенных функций, разобрать значений, отвечающих за многоусловности алгоритма. В используемых иструментов из 0! рода задач. Основной случаях. Первая ситуация
применять формулу в
Вложенность
к наиболее востребованнымПодсчет значений на основе если ввести формулу) и др.), ознакомьтесьЕСЛИ(C2 больше B2, тозначение_если_ложьесли от 40 спасибо ))) правильно записать которые очень непросто. выполнение в случае эксель функция “ЕСЛИ” этого набора являетсяЗначение числа в текстовом этап — правильное обозначается редактором, как Excel, если несколько
Несколько условий
функциям во время нескольких условий с без обоих закрывающих со статьей Операторы вернуть текст “Превышение (необязательно) до 99, тосвой второй решил=ЕСЛИ(30? Таким образом, если истины или лжи, проверяет лишь одну функция “ЕСЛИ”. формате игнорируется функцией НАИБОЛЬШИЙ() составление формулы, чтобы «ДЕЛ/0» и встречается условий в задаче. работы.
помощью функции СЧЁТЕСЛИМН скобок, приложение Excel вычислений и их бюджета”, в противномЗначение, которое должно возвращаться, на 5, с помощью суммеслимн…Serge 007: =ЕСЛИ(и(30 спустя некоторое время то при его операцию сравнения вПри работе в “Экселе” (см. столбец Е не получить ошибочного достаточно часто. Как Пример такого выражения:В Excel формула «Если»Суммирование значений на основе попытается исправить ее. приоритеты. случае вернуть текст
если
если от 100zoloto-77JustBear: Serge 007, а придётся вернуться к выборе результатом будет логическом выражении, то необходимо понимать значение на рисунке выше). результата. Теперь вы правило, она возникает =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; помогает выполнять разного одного условия сExcel позволяет использовать доИногда требуется проверить, пуста “В пределах бюджета”)лог_выражение то на 6: Помогите пожалуйста с если надо диапазон
Особые варианты функции
конструкции или начать “0”. есть, использовать конъюнкцию функции “ЕСЛИ”, чтобы Перед нахождением наибольшего знаете, как использовать в тех случаях, 0). Из этого рода задачи, когда
помощью функции СУММЕСЛИ 64 вложенных функций ли ячейка. Обычно=ЕСЛИ(C2>B2;C2-B2;0)имеет значение ЛОЖЬ.Прошу не умничать, “больше/меньше” во времени? Типа работу с чужимОтдельно стоит отметить случай, или дизъюнкцию не конструировать правильные синтаксические значения можно попытаться оператор в Excel, когда подлежит копированию следует, что если требуется сравнить определенныеСуммирование значений на основе ЕСЛИ, но это
это делается, чтобыНа рисунке выше мы=ЕСЛИ(C2=”Да”;1;2) а реально помочь!=СУММЕСЛИ(B4:B14;”>100″;D4:D14) ЕСЛИ A (10:12) запросом, на понимание когда вместо логического получится. Для проверки запросы. Благодаря её преобразовать все значения если несколько условий формула «A/B», при показатель в клетке данные и получить нескольких условий с вовсе не означает, формула не выводила возвращаем не текст,В примере выше ячейкаСПАСИБО!это работает с укладывается в промежуток записи уйдёт немало выражения введена не нескольких условий необходимо алгоритму, производится выполнение в числовой формат. в задаче. этом показатель B
А1 равен 5 результат. Данное решение помощью функции СУММЕСЛИМН что так и результат при отсутствии а результат математического D2 содержит формулу:Игорь условием/критерием больше 100, между 7:00 и времени. Кроме того, конструкция, возвращающая значение воспользоваться свойством вложенности.
На что стоит обратить внимание
некоторого логического сравнения, Это можно сделатьАвтор: Евгений Никифоров в отдельных ячейках либо 10, программа дает возможность применятьФункция И надо делать. Почему? входного значения. вычисления. Формула вЕСЛИ(C2 = Да, то: есть стандартная функция: а как написать 20:00, то выдавать каждая функция имеет “ИСТИНА” или “ЛОЖЬ”,Чтобы понять, как задать в зависимости от формулой массива =НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;””);1)
Функция НАИБОЛЬШИЙ(), английский вариант равен нулю. Избежать отобразит результат 100, ветвящиеся алгоритмы, аФункция ИЛИНужно очень крепко подумать,В данном случае мы ячейке E2 означает: вернуть 1, в ЕСЛИ (выражение; значение больше 100 и одно значение, если свою пару скобок, а некоторый набор несколько условий в результатов которого будетНеобходимо помнить особенность функции LARGE(), возвращает k-ое этого можно посредством в обратном случае также создавать деревоФункция ВПР
Примеры
чтобы выстроить последовательность используем ЕСЛИ вместеЕСЛИ(значение “Фактические” больше значения противном случае вернуть 2) если выражение истина; меньше 200 нет – другое.
и случайно поставив символов или ссылка “ЕСЛИ”, удобно воспользоваться произведено одно из НАИБОЛЬШИЙ() при работе по величине значение возможностей рассматриваемого нами — 0. Можно решений.Полные сведения о формулах из множества операторов с функцией ЕПУСТО: “Плановые”, то вычесть
=ЕСЛИ(C2=1;”Да”;”Нет”) значение если выражение
ShAMКазанский её не на на ячейку. В примером. Пусть необходимо двух действий. со списками чисел,
из массива данных. оператора. Итак, необходимая использовать эти операторыФункция выглядит таким образом: в Excel ЕСЛИ и обеспечить=ЕСЛИ(ЕПУСТО(D2);”Пустая”;”Не пустая”) сумму “Плановые” изВ этом примере ячейка ложь): Для Ексель 2007: своё место, придётся том случае, когда проверить, находится лиГоворя более простыми словами, среди которых имеются Например, формула =НАИБОЛЬШИЙ(A2:B6;1) формула будет иметь
и чтобы решить =ЕСЛИ (задача; истина;Рекомендации, позволяющие избежать появления их правильную отработкуЭта формула означает: суммы “Фактические”, в D2 содержит формулу:ЕСЛИ (А1100;А1*6;А1*5))
и моложе:
fb.ru
Как записать условие “Больше одного, но меньше второго”
JustBear долго искать ошибку. в качестве параметра число в ячейке
функция “ЕСЛИ” в
повторы. Например, если вернет максимальное значение следующий вид: =ЕСЛИ(B1=0; более сложные задачи. ложь). Первая составная неработающих формул по каждому условиюЕСЛИ(ячейка D2 пуста, вернуть противном случае ничего
ЕСЛИ(C2 = 1, тоа вообще у=СУММЕСЛИМН(D4:D14;B4:B14;”>100″;B4:B14;” Для Ексель, формула массива, вводитсяДля закрепления понимания стоит записано некоторое выражение, “А1” в заданном случае истинного значения
имеется исходный массив (первое наибольшее) из 0; A1/B1). Отсюда К примеру, в часть — логическоеОбнаружение ошибок в формулах
на протяжении всей
CyberForum.ru
Формулы в excel. Как составить формулу : если сумма больше 50000, но меньше 75000, то 35%
текст “Пустая”, в не возвращать) вернуть текст “Да”,
EXCEL очень неплохая
2003 и старше: с помощью Ctrl+Shift+Enter
на практике рассмотреть, содержащие что-то помимо промежутке – от
некоторого выражения, выполняет
{1;2;3; диапазона следует, что если базе необходимо вычислить выражение. Оно может с помощью функции цепочки. Если при
Критерий “больше-меньше” в формулах СУММЕСЛИ и СУММЕСЛИМН
противном случае вернуть.
в противном случае справочная система -=СУММПРОИЗВ((B4:B14>100)*(B4:B14 и отображается в как работает функция
числового значения или 5 до 10. одно действие, в6
A2:B6 клетка B1 будет
должников, которым необходимо быть фразой или проверки ошибок вложении операторов ЕСЛИ
текст “Не пустая”)=ЕСЛИ(E7=”Да”;F5*0,0825;0) вернуть текст “Нет”)
я сам такShAM фигурных скобках Код
“ЕСЛИ” в Excel. логических слов, то Как можно заметить, случае ложного -
;6;7}, то третьим наибольшим
. заполнена параметром «ноль», заплатить более 10000
числом. К примеру,Логические функции
вы допустите малейшую. Вы также можетеВ этом примере формула
Как видите, функцию ЕСЛИ
его выучил… Удачи!: Да, 2-я формула =ЕСЛИ(И(A10:A12>=–“7:00”;A10:A12 Примеры, приведённые ниже, это вызовет ошибку
в данном случае другое. При этом (по версии функции
Синтаксис редактор выдаст «0»,
рублей. При этом
«10» или «безФункции Excel (по алфавиту) неточность, формула может легко использовать собственную
planetaexcel.ru
Excel – если число в ячейке больше X,в той же ячейке происходит замена на 1, если меньше – на 0.возможно ли сделать?
в ячейке F7 можно использовать дляMix-fighter44 для 2007 иJustBear
демонстрируют все основные при выполнении функции. требуется провести проверку в качестве действий
НАИБОЛЬШИЙ()) будет считаться
НАИБОЛЬШИЙмассивk в обратном случае
они не погашали НДС» — это
Функции Excel (по категориям)
сработать в 75 % формулу для состояния
означает:
сравнения и текста,: ЕСЛИ (СУММ (И моложе тоже подойдет.: способы её использования.
Если указать адрес
двух условий, проверив
может быть как 6, а не
) Excel поделит показатель заем более шести
логические выражения. ДанныйПримечание: случаев, но вернуть
“Не пустая”. В
Как в EXEl написать формулу, если сумма ячеек больше или равна 40 и меньше или равна 99 то умножить её на 5
ЕСЛИ(E7 = “Да”, то и значений. А ну а дальшеАлексей матевосов (alexm)КазанскийПростейшим примером для разбора ячейки или прописать
на истинность сравнение явное значение, так 3. Все правильно
Массив A1 на данные
месяцев. Функция табличного параметр обязательно необходимо
Мы стараемся как непредвиденные результаты в следующем примере вместо вычислить общую сумму
еще с ее пищи то что: Комментарий вы не
, 10:12 – это работы функции является
некоторое число/логическое значение, с двумя величинами
и определённая функция,
и логично, но — ссылка на диапазон B1 и отобразит редактора Excel «Если» заполнить. Истина — можно оперативнее обеспечивать
остальных 25 %. К
функции ЕПУСТО используются в ячейке F5 помощью можно оценивать надо разрешили к ответам,
время, а не сравнение двух чисел. то результат будет – 5 и в том числе
иногда об этом
Одна из самых популярных и вполне простых в применении функция ЕСЛИ является логической функцией. Она позволяет проверять некоторые данные на соответствие заданных условий и показывает результат после сравнения.
Аргументы функции ЕСЛИ и принцип действия в Excel
Синтаксис функции в самом простом применении выглядит следующим образом: ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).
Теперь разберем её аргументы более подробно:
- Логическое выражение – определённые данные, которые мы должны проверить на соответствие с некоторыми имеющимися у нас условиями.
- Значение_если_истина – результат проверки, который мы увидим, когда логическое выражение будет справедливо.
- Значение_если_ложь – результат при несоответствии условия и наших данных, которые мы проверяем.
Схематически это выглядит следующим образом:
Теперь рассмотрим, как нам определить результат проверки с помощью функции ЕСЛИ. Для начала определяем ЧТО нам надо проверять – число 50. Затем указываем условие – «больше 40», «меньше 40», после чего пишем результат проверки – «ИСТИНА», при условии, что 50 действительно больше 40 и «ЛОЖЬ», когда 50 меньше 40. После того как мы определили, как будут выглядеть наши аргументы, начинаем собирать по кусочкам нашу формулу:
Функция проверила выражение «50 больше 40» и определила, что результат – ИСТИНА (ячейка Е4).
Следующее условие проверяем точно также просто заменив оператор «больше» на «меньше»:
Поскольку 40 больше 50, проверка определила, что наше выражение – ЛОЖЬ.
Теперь рассмотрим более наглядные примеры применения функции ЕСЛИ. У нас есть список студентов и оценок в баллах, полученных за экзамен. Нам нужно найти студентов отличников, которые получили оценку больше или равно 90. Начинаем составлять функцию, ячейка F17. Значение в ячейке E17 должно быть больше или равно (>=) 90, тогда мы получим результат «ИСТИНА». Когда значение в ячейке Е17 будет меньше 90, возвратится значение «ЛОЖЬ»:
И копируем значение ячейки до конца столбца, таким образом мы нашли студентов, которые получили результат больше или равно 90 баллов:
Но такие значения таблицы не позволяют пользователю корректно прочитать информацию, которую мы хотели донести. Тогда нам нужно заменить слова ИСТИНА и ЛОЖЬ на более принятые и понятные. Тут нам пригодится таблица соответствий оценок по шкале ECTS оценкам в баллах B10:C13. Пусть при истинном результате у нас будет стоять оценка А, которая соответствует баллам от 90 до 100, а при ложном – ячейки будут оставаться пустыми, после чего копируем первую ячейку до конца столбца и наша таблица станет более информативной:
Формула функции ЕСЛИ и ВПР
Теперь рассмотрим пример использования вложенных формул и ситуацию, где они могут пригодиться. В предыдущем примере мы определили студентов отличников, но у нас остались ещё незаполненные поля. Нам нужно также определить какую оценку в буквах получат и другие студенты. Указываем, что студенты с баллом больше или равно 90 получат оценку А: =ЕСЛИ(E31>=90; «A»; Затем на месте аргумента «значение_если_ложь» вставляем формулу ЕСЛИ(Е31>=80; «В»; и на месте аргумента значение_если_ложь этой же формулы вставляем ещё одну формулу ЕСЛИ(Е31>=60; «С»; и на месте третьего аргумента уже этой функции пишем последнее условие, не забывая добавить скобки: ЕСЛИ(Е31<60)))):
Копируем формулу до конца столбца и таким образом мы построили вложенную функцию. Однако иногда при написании такой функции нужно учитывать один нюанс – она корректно работает, пока данные для сравнения указываться от большего значения к меньшему (1,2,3,4):
Вот что получится, когда мы укажем условия для сравнения наоборот – от меньшего к большему:
Логика в том, что функция во время проверки первой ячейки определила, что значение больше 60 и выдала соответствующий результат – «С». Дальше проверка не продолжилась. Есть ситуации, где оценка вместо А будет А+, А, А-, такое разветвление будет по каждой букве и самих буквенных оценок будет больше. Тогда процесс создания вложенной функции будет очень долгим, вложенных формул будет очень много и становиться легко запутаться. В таком случае вместо ЕСЛИ можно использовать ВПР. Для начала видоизменяем нашу меньшую таблицу. Такие изменения обусловлены особенностями работы функции ВПР:
Формула ВПР будет искать приблизительное значение ячейки Е60 в диапазоне В53:С56 во втором столбце и передавать найденные значения в основную таблицу:
Функция ЕСЛИ несколько условий
Теперь рассмотрим примеры, когда наши данные должны соответствовать нескольким условиям. Функция ЕСЛИ вместе с функцией И имеют следующий синтаксис:
Например у нас есть список студентов и данные их оценок по трём предметам. Нам нужно проверить наличие у студента оценки 2 хотя бы по одному предмету и указать, есть ли у студента пересдача:
Кроме функции И можно использовать функцию ИЛИ. Разница между ними в том, что при использовании И одновременно должны соответствовать условию все логические выражения. При использовании функции ИЛИ достаточно, чтобы условию соответствовало хотя бы одно логическое выражение.
Например у нас есть список студентов и условие, что при наличии хотя бы одной оценки 3, студент не получает стипендию. Мы проверяем, равно ли содержимое ячеек по предметам цифре 3:
Функция ЕСЛИ в сочетании с функцией НЕ своей работой очень похожа на самый простой пример функции ЕСЛИ с одним условием и двумя результатами, только сейчас наше логическое выражение поменяет условие на противоположное. У нас есть список студентов и информация о наличии и количестве прогулов. Нам нужно указать что при полном отсутствии прогулов у студента зачёт, а в любых других случаях – не зачёт.
При условии, что в ячейке Е91 не находится слово «нет» (НЕ(Е91= «нет»)), наш результат – зачёт, в любом инном случае – не зачёт:
Может быть ситуация, когда нам нужны не только результаты обработки условий, а и их графическое изображение. Кроме функций И, ИЛИ, НЕ мы можем комбинировать ЕСЛИ + МАКС. Рассмотрим ситуацию, где нам может это пригодиться.
У нас есть отчет о чистых прибылях нескольких компаний. Мы хотим видеть только тот показатель, который является максимальным. Для этого мы используем вместе с функцией ЕСЛИ функцию МАКС: ЕСЛИ (МАКС(указываем диапазон в котором будем искать значение)=ячейка, которую функция будет пропускать через диапазон; значение если истина (проверяемая ячейка); значение если ложь (ничего не указывать)). В ячейке Е113 прописываем формулу, не забываем про абсолютные ссылки для диапазона D113:E119, иначе он сместится при копировании, копируем формулу до конца столбца:
Разнообразим наши результаты работы и создадим график, который будет так же выделять наше максимальное число: выбираем диапазон D111:E119 – Вставка – Рекомендуемые диаграммы – Выбираем первую диаграмму и ОК. Теперь у нас данные выводятся в табличном и графическом виде:
Теперь максимальное число выделяется не только в таблице, а и на диаграмме. Но сейчас этих величин у нас две – с обеих столбцов. Немного подредактируем нашу диаграмму. На диаграмме выбираем любой из Рядов, открываем меню, клацая правой кнопкой мыши по нём, выбираем Формат ряда данных, указываем Перекрытие рядов на 100% и рассмотрим результат, который у нас получился:
Данные из третьего столбца перекрыли данные из второго и мы получили подсвечивание максимального показателя. Теперь, когда нам нужно будет изменить любое число во втором столбце, наша формула заново определит максимальное число из столбца «Чистая прибыль», покажет его в столбце «Максимальный показатель», а потом мы увидим его среди остальных чисел и на диаграмме автоматически. Например, укажем новое число для автопроизводителя Nissan – 1600. Вот какие изменения произошли:
Скачать пример функции если с несколькими условиями в Excel
Формула в столбце Е изменила свои вычисления и эти изменения отобразились на графике – подсвечивается новое найденное максимальное число. Такие процессы будут происходить при любом изменении показателей в столбце «Чистая прибыль».
history 11 апреля 2013 г.
- Группы статей
- стандартные функции
Функция ЕСЛИ()
, английский вариант IF(),
используется при проверке условий. Например, =ЕСЛИ(A1>100;”Бюджет проекта превышен”;”ОК!”). В зависимости от значения в ячейке
А1
результат формулы будет либо “Бюджет превышен” либо “ОК!”.
Функция ЕСЛИ() относится к наиболее часто используемым функциям. Поэтому рассмотрим ее подробно.
Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Функция имеет 3 аргумента.
Лог_выражение
— любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, выражение A1>100. Если в ячейке А1 содержится значение 102, то выражение A1>100 вернет ИСТИНА, т.к. 102>100. Если в ячейке А1 содержится значение 88, то выражение вернет ЛОЖЬ, т.к. неравенство 88>100 неверно. Полностью формула может выглядеть так =ЕСЛИ(A1>100;”Бюджет проекта превышен”;”ОК!”) Если в ячейке
A1
содержится значение большее 100, то формула вернет текстовое значение
Бюджет проекта превышен,
а если меньше 100 или равное 100, то будет выведено
ОК!
Других значений кроме ИСТИНА или ЛОЖЬ логическое выражение вернуть не может. Любое число кроме 0 в первом аргументе будет преобразовано в ИСТИНА, ноль – в ЛОЖЬ. Если ошибочно будет указано текстовое значение, то функция вернет ошибку #ЗНАЧ!.
Примечание
: Ошибка #ЗНАЧ! может быть также из-за ошибок в выражениях указанных во втором и третье аргументе.
Второй аргумент –
Значение_если_истина
и третий аргумент
значение_если_ложь
Как было показано выше, если логическое выражение (первый аргумент функции) принимает значение ИСТИНА, то функция возвращает значение из второго аргумента
значение_если_истина.
Соответственно, если логическое выражение принимает значение ЛОЖЬ, то функция возвращает значение из третьего аргумента
значение_если_ложь
.
Второй и третий аргумент могут быть любым значением (число или текст) или являться выражением (формулой с функциями). Например формула =ЕСЛИ(A1>100;0;А1*1%) позволяет вычислять премию для проектов, в которых не было превышения бюджета (>100). Если бюджет превышен (в А1 введено значение 200 или любое другое более 100), то формула вернет 0, если в А1 введено значение менее 100, то будет вычислено значение премии А1*1% (т.е. если в А1 число 90, то формула вернет 0,9).
Если в качестве второго и третьего аргументов введено выражение, то оно может содержать и функции, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке
A1
содержится значение >100, то суммирование ведется по столбцу
B
, а если меньше или равно 100, то по столбцу
С
. Не забывайте, что при значении А1 = 100 в данном случае будет срабатывать выражение из аргумента
значение_если_ложь.
Аргумент
значение_если_ложь
можно опустить (не вводить), тогда при значении логического выражения =ЛОЖЬ будет выведено ЛОЖЬ. Например, если в А1 содержится 600, то формула =ЕСЛИ(A1>=1000;”Больше или равно 1000″) вернет просто ЛОЖЬ. Понятно, что при значении логического выражения =ИСТИНА будет выведено “Больше или равно 1000”, т.е. второй аргумент функции ЕСЛИ() –
Значение_если_истина.
Вложенные ЕСЛИ (несколько условий)
Для построения более сложных проверок в одной формуле можно использовать несколько функций ЕСЛИ(). Для этого в качестве значений аргументов
значение_если_истина
и
значение_если_ложь
введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу
=ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2))
- Если значение из ячейки А8 менее 5, то будет выведено 1
- Если значение из ячейки А8 более 10, то будет выведено 3
- Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2
Примечание
: В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.
Примечание
: Формулу =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)
или более сложный вариант =ЕСЛИ(A8<5;1;ЕСЛИ(И(5 < A8;A8<10);2;3)). Последний вариант позволяет последовательно перебирать диапазоны изменения А8, но требует знания работы логических функций.
Формула =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8<5. Если оно истинно, то выводится 1, вычисления завершены. Если оно ложно, т.е. А8>=5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.
Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:
1) Определитесь с диапазонами:
1.1) они не должны пересекаться. В примере выше – 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;ЕСЛИ(A8<3;2;4))). Области значений у выражений A8<5 и A8<3 пересекаются и крайне сложно безошибочно прописывать такие условия. Такая формула просто не верна. Очевидно, что в этом случае 4 диапазона: меньше 3; от 3 до 5; от 5 до 10 и больше 10. Как прописать такие условия написано ниже в этом разделе.
1.2.) диапазоны должны полностью охватывать нужную область значений. Очевидно, что в формуле =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.
1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.
Примечание:
В перечисленных выше случаях логические выражения вычисляются на основании одной переменной А8. Иногда нужно придумывать формулы с ЕСЛИ(), зависящие от 2-х и более ячеек: =ЕСЛИ(B8>=5;ЕСЛИ(A8>10;3;2);1). Это гораздо сложнее, но, конечно, возможно. Следуйте правилам 1.1)-1.3) и следите за диапазонами. Еще сложнее, если обе переменных являются зависимыми, т.е. например B8 вычисляется на основании А8. Написание таких формул требует предельной аккуратности и напрямую связано с корректностью формулирования самой задачи (возможно вы не продумали до конца как использовать такую сложную зависимость). Советую по возможности разбить вычисления на 2 или более формулы, чтобы проще контролировать результат вычисления.
2) Правильно записывайте логические выражения. Например, условие 5 < A8<10 в EXCEL не будет работать. В этом выражении фактически записано 2 условия. Нужно записать эти 2 условия по отдельности. Чтобы их объединить нужна функция И(): И(5 < A8;A8<10) Подробности в этой статье
https://excel2.ru/articles/funkciya-i-v-ms-excel-i
или потребуется переделать формулу с вложенными ЕСЛИ().
3) Используйте для наглядности построчное написание вложенных ЕСЛИ. Например, формулу =ЕСЛИ(A8<5;1;ЕСЛИ(A8>10;3;2)) в строке формул можно записать как
=ЕСЛИ(A8<5;1;
ЕСЛИ(A8>10;3;2))
Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.
4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения – последовательности 1; 2; 3 … Это покажет какой блок вложенных ЕСЛИ() срабатывает.
5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета
=ЕСЛИ(A1>=100;”Очень крупный проект”;
ЕСЛИ(A1>=90;”Крупный проект”;
ЕСЛИ(A1>=50;”Средний проект”;”Малый проект “)))
Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы. Ниже приведено 2 эквивалентные формулы:
=ПРОСМОТР(A1;{0;50;90;100};{“Малый проект”;”Средний проект”;”Крупный проект”;”Очень крупный проект”})
=ВПР(A1;A3:B6;2)
Для функции ВПР() необходимо создать в диапазоне
A3:B6
таблицу значений:
Формула =ПРОСМОТР(A1;{0;50;90;100};{“Малый проект”;”Средний проект”;”Крупный проект”;”Очень крупный проект”}) выглядит необычно, но по сути второй аргумент {0;50;90;100}, как и третий аргумент {“Малый проект”;”Средний проект”;”Крупный проект”;”Очень крупный проект”} это запись содержимого 4-х ячеек. Вместо {0;50;90;100} можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.
Еще пример. Пусть требуется вывести разный текст (например “<0”; “=0” или “>0”) в случае наличия в ячейке
А1
отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():
=ПРОСМОТР(A1;{-1E+307;0;1E-307};{“<0″;”=0″;”>0″})
Примечание
: -1E+307 – это самое маленькое отрицательное число в EXCEL, 1E-307 – это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).
Второй массив можно заменить ссылкой на диапазон, например для того чтобы вывести результат вычисления формул:
=ПРОСМОТР(A24;{-1E+307;0;1E-307};A27:A29)
Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).
Опущен третий аргумент
[значение_если_ложь]
Третий аргумент функции не обязателен. Если логическое условие не выполнено, т.е. ЛОЖЬ, а третий аргумент функции не указан, то функция вернет также значение ЛОЖЬ. Например:
=ЕСЛИ(A1>100;”Бюджет превышен”)
Если в ячейке
A1
содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.
Сложные логические выражения
В аргументе
Лог_выражение
можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.
Основное правило
– логические функции и их комбинации возвращают только одно из 2х логических значений: ЛОЖЬ или ИСТИНА.
Приведем несколько полезных примеров.
Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.
Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.
Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1<=0;1;2)
Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число
Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;”Бюджет превышен”;”ОК!”) или (если в ячейке
A1
содержится значение 0) =ЕСЛИ(A1;”Бюджет превышен”;”ОК!”) вернут
ОК!
Если в ячейке
A1
находится любое другое число кроме 0, то формула вернет
Бюджет превышен
. Такой подход удобен, когда проверяется равенство значения нулю значения ячейки.
Примечание
: чтобы убедиться, что логическое значение ЛОЖЬ соответствует 0 введите формулу =–А1=0. В А1 введите ЛОЖЬ. Формула вернет ИСТИНА. Отметим, что логическое значение ЛОЖЬ именно соответствует 0, но не равно 0, т.к. формула =А1=0 вернет ЛОЖЬ, следовательно логическое значение ЛОЖЬ не равно 0. Железная логика!
Примечание
: Двойное отрицание “–” – это просто математическая операция, которая приводит к преобразованию логического выражения в числовое, но не приводит к изменению самого значения. Двойное отрицание можно заменить сложением с 0 или возведением в первую степень: =(А1+0)=0.
Связь функции ЕСЛИ() с другими функциями использующие условия
EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .
Функция ЕСЛИ() , как альтернативный вариант, может быть также использована для подсчета и сложения значений с использованием условий. Ниже приведены иллюстрирующие примеры.
Пусть данные находятся в диапазоне
A6:A11
(см. файл примера)
Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;”>10″) . Аналогичный результат (23) можно получить с помощью
формулы массива
=СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо
ENTER
нужно нажать
CTRL+SHIFT+ENTER
)
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек
A6:A11
=СЧЁТЕСЛИ(A6:A11;”>10″) . Аналогичный результат (2) можно получить с помощью
формулы массива
=СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))
#Руководства
- 24 июн 2022
-
0
Как из сотни автомобилей выбрать только те, что соответствуют запросу покупателя? Разбираемся на примере функции ЕСЛИ в Excel.
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
ЕСЛИ — логическая функция Excel. Она проверяет, выполняются ли заданные условия в выбранном диапазоне таблицы. Пользователь указывает критерий, который нужно проверить, — функция сравнивает этот критерий с данными в ячейках таблицы и выдаёт результат.
В статье разберёмся:
- как работает и для чего нужна функция ЕСЛИ в Excel;
- как запустить функцию ЕСЛИ с одним условием;
- как запустить функцию с несколькими условиями.
Функцию ЕСЛИ используют, когда нужно сравнить данные таблицы с критериями пользователя. У функции есть два результата: ИСТИНА и ЛОЖЬ. Первый результат функция выдаёт, когда данные ячейки полностью совпадают с заданным условием, второй — когда данные ячейки условию не соответствуют.
Например, если нужно определить в таблице значения меньше 500, то значение 265 будет отмечено функцией как истинное, а значение 3426 — как ложное.
Можно задавать несколько условий одновременно. Например, найти значения меньше 500, но больше 300. В этом случае функция определит значение 265 как ложное, а 402 — как истинное. Так можно проверять не только числовые значения, но и текст.
Часто функцию ЕСЛИ используют при работе с другими функциями Excel для расширения их возможностей. Например, в случае с ВПР функция ЕСЛИ позволяет настроить поиск сразу по двум критериям.
Рассмотрим, как работает функция ЕСЛИ в классическом виде на примере.
Представим, что в автосалон обратился покупатель с просьбой подобрать ему автомобиль. Его запрос — автомобили чёрного или красного цвета, с объёмом двигателя больше 1,5 л, стоимостью до 2,5 млн рублей. Есть каталог автомобилей, но все характеристики и цены расположены в нём вразброс.
Скриншот: Excel / Skillbox Media
Нужно выяснить, какие из представленных в салоне авто подходят покупателю. Разберёмся пошагово, как это можно сделать с помощью функции ЕСЛИ в Excel.
Готовимся к запуску функции ЕСЛИ
Для начала рассмотрим, как функция ЕСЛИ работает в классическом виде — для проверки одного условия пользователя. Определим автомобили стоимостью до 2,5 млн рублей.
Принцип действия функции ЕСЛИ следующий. Алгоритм просматривает выбранный диапазон таблицы и проверяет, соответствуют ли данные его ячеек запросу пользователя. Затем возвращается в отдельную ячейку и оставляет там результат: ИСТИНА или ЛОЖЬ. О том, как настроить алгоритм функции под наши потребности, поговорим ниже. А сейчас на примере покажем, как подготовить таблицу к запуску функции и как вызвать окно для её построения.
Функция ЕСЛИ приносит результаты в отдельную ячейку, поэтому создадим отдельный столбец для них. Лучше сразу назвать его так, чтобы было понятно, о чём речь. В нашем случае сделаем столбец «До 2,5 млн руб.».
Скриншот: Excel / Skillbox Media
Выделим первую ячейку нового столбца и откроем окно для поиска функций. Это можно сделать двумя способами:
1. Перейти во вкладку «Формулы» и нажать «Вставить функцию».
Скриншот: Excel / Skillbox Media
2. Нажать на «fx» в строке ссылок на любой вкладке Excel.
Скриншот: Excel / Skillbox Media
На экране справа появится окно «Построитель формул». В нём через поиск находим функцию ЕСЛИ и нажимаем «Вставить функцию».
Скриншот: Excel / Skillbox Media
Появляется окно для заполнения аргументов функции: «Лог_выражение», «Значение_если_истина», «Значение_если_ложь». Ниже разберёмся, как их заполнить.
Скриншот: Excel / Skillbox Media
Функция ЕСЛИ предполагает, что пользователь создаёт запрос и указывает два варианта ответа на него. Этот запрос и варианты ответа — и есть три аргумента функции.
«Лог_выражение» (логическое выражение) — запрос пользователя, который функция будет проверять. В нашем примере это стоимость автомобилей.
Нужно, чтобы функция определила автомобили стоимостью менее или равной 2 500 000 рублей. Порядок действий следующий:
1. Ставим курсор в окно «Лог_выражение» в построителе формул.
2. Выбираем первое значение столбца «Цена, руб.» — ячейку Е2. Обозначение ячейки переносится в окно «Лог_выражение» построителя формул и одновременно с этим появляется в строке ссылок.
Скриншот: Excel / Skillbox Media
3. Дополняем значение E2 запросом пользователя: <=2500000. Одновременно с этим формула в строке ссылок принимает вид:fx=ЕСЛИ(E2<=2500000).
Скриншот: Excel / Skillbox Media
«Значение_если_истина» — результат, который функция принесёт в ячейку, если значение совпадёт с запросом пользователя. В случае с примером — что напишет функция, если проверяемая стоимость авто будет меньше либо равна 2 500 000 рублей.
Наша задача — определить, подходят автомобили каталога под запрос клиента или нет. Поэтому в окне аргумента истины введём значение «Подходит» — можно вводить его без кавычек, Excel добавит их самостоятельно.
Скриншот: Excel / Skillbox Media
«Значение_если_ложь» — результат, который функция принесёт в ячейку, если значение не совпадёт с запросом пользователя. В нашем примере — что напишет функция, если проверяемая стоимость авто будет больше 2 500 000 рублей.
Введём в качестве аргумента ЛОЖЬ значение «Не подходит».
Итоговая формула в строке ссылок примет вид:
fx=ЕСЛИ(E2<=2500000;”Подходит”;”Не подходит”).
Скриншот: Excel / Skillbox Media
Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул.
Скриншот: Excel / Skillbox Media
В выбранной ячейке появится результат работы функции: «Подходит». Это значит, что первый автомобиль каталога подходит под запрос пользователя, — его цена 1 910 000 рублей.
Скриншот: Excel / Skillbox Media
Дальше нужно протянуть результат первой ячейки вниз до конца таблицы, чтобы функция проверила стоимость всех остальных автомобилей и отметила, подходят они под запрос покупателя или нет.
Скриншот: Excel / Skillbox Media
Чтобы настроить функцию ЕСЛИ с несколькими условиями, нужно добавить к ней дополнительные операторы Excel — «И», «ИЛИ».
Они связывают несколько критериев и, в зависимости от того, совпадают они с данными таблицы или нет, выдают результат. Схематично это будет выглядеть так: «ЕСЛИ условие a = 1, И/ИЛИ условие b = 2, И/ИЛИ условие c = 3, то — ИСТИНА, иначе — ЛОЖЬ».
В нашем примере у покупателя было три условия: цвет автомобиля, цена и объём двигателя. Разберёмся, как настроить функцию ЕСЛИ в этом случае.
Создадим ещё один столбец для новых результатов работы функции. Назовём его в соответствии с запросом покупателя: «До 2,5 млн руб., чёрный или красный, больше 1,5 л».
Скриншот: Excel / Skillbox Media
Вызываем окно построителя формул, как делали выше, и заполняем аргументы функции.
Отличия от классического варианта использования функции ЕСЛИ будут при заполнении первого аргумента «Лог_выражение»:
- Ставим курсор в окно «Лог_выражение» в построителе формул, вводим дополнительный аргумент «И» и открываем скобку.
- Первым условием будем вводить два цвета автомобилей, которые выбрал покупатель, поэтому вводим второй дополнительный аргумент «ИЛИ» и открываем ещё одну скобку.
- Выбираем первое значение столбца «Цвет» — ячейку B2. Ставим знак равенства и находим в этом же столбце значение из запроса пользователя: «чёрный» — ячейку B5. Ставим знак точки с запятой.
- Снова выбираем первое значение столбца «Цвет», ставим знак равенства и находим второй цвет из запроса пользователя: «красный» — ячейку B8. Закрываем скобку и ставим знак точки с запятой.
- Закрепляем выбранные ячейки с цветами. На Windows для этого поочерёдно выбираем значения ячеек в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значения ячеек в строке ссылок и нажимаем клавиши Cmd + T. Закрепить эти ячейки нужно, чтобы в дальнейшем можно было протянуть формулу вниз и она сработала корректно для всех остальных строк.
- Выбираем первое значение столбца «Объём двигателя, л» — ячейку D2. Дополняем его запросом пользователя: >1,5. Также ставим знак точки с запятой.
- Выбираем первое значение столбца «Цена, руб.» — ячейку Е2. Дополняем его запросом пользователя: <=2500000, как делали в первом примере. Закрываем скобку.
Формула в строке ссылок принимает вид:
fx=ЕСЛИ(И(ИЛИ(B2=$B$5;B2=$B$8);D2>1,5;E2<=2500000)). Она объединит три запроса покупателя оператором «И», а в первом запросе предоставит возможность выбора с помощью оператора «ИЛИ».
Скриншот: Excel / Skillbox Media
Аргументы «Значение_если_истина» и «Значение_если_ложь» оставляем такими же, как в первом случае: «Подходит» и «Не подходит».
Итоговая формула в строке ссылок принимает вид:
fx=ЕСЛИ(И(ИЛИ(B2=$B$5;B2=$B$8);D2>1,5;E2<=2500000);Подходит”;”Не подходит”). Нажимаем кнопку «Готово».
В выбранной ячейке появляется результат: «Не подходит». Это значит, что первый автомобиль каталога не подходит под запрос покупателя — у него жёлтый цвет, а объём двигателя равен 1,5 л.
Скриншот: Excel / Skillbox Media
Протягиваем результат первой ячейки вниз до конца таблицы — функция проверяет остальные автомобили в каталоге и отмечает, какие из них подходят под все запросы покупателя.
Скриншот: Excel / Skillbox Media
Как пользоваться функцией ЕСЛИ в «Google Таблицах»? В них тоже есть функция ЕСЛИ, но нет окна построителя формул. Поэтому нужно прописывать её вручную, добавляя все пробелы и кавычки самостоятельно. Готовая формула будет выглядеть так:
fx=ЕСЛИ(E2<=”2500000″;”Подходит”;”Не подходит”).
- Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
- Инструкция: как закреплять строки и столбцы в Excel
- Руководство по созданию выпадающих списков в Excel — как упростить заполнение таблицы повторяющимися данными
- Гайд о теории ТРИЗ, которую изучают менеджеры по всему миру
- Статья с советами эксперта, как сохранить бизнес в условиях кризиса, — о рынке, рисках, зарплатах и возможностях
Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше