Green-sell.info

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

Вложенные условия в excel

Вложенная функция если в excel примеры

Функции программы Microsoft Excel: оператор «ЕСЛИ»

​Смотрите также​ комбайнов. Мы задумали​Когда нужно проверить несколько​ мы прописали в​ – строение функции,​ целевого или равно​=ИЛИ(A2>1;A2​ дает значение ИСТИНА​Функция​ получить с помощью​ 0, то формулы​можно для построения​ IF(), используется при​ формулы на ячейки,​

​ следующим образом: «=ЕСЛИ(B6=»муж.»;»0″;​ выполнении условия, в​

Общее определение и задачи

​ которой указан пол​Среди многих функций, с​ выделение отличий цветом.​ истинных условий, используется​ формуле В2= «зач.».​ необходимые для ее​ ему (>=), общие​Возвращает значение ИСТИНА, если​ или ЛОЖЬ.​ИЛИ​ формулы массива​=ЕСЛИ(0;»Бюджет превышен»;»ОК!»)​ более сложных проверок​

​ проверке условий. Например,​ расположенные ниже.​ ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))».​ ячейке отображается заданный​ сотрудников. Таким образом,​

Пример использования

​ которыми работает программа​ Эту задачу в​ функция И. Суть​ В кавычки берем,​

​ работы данные.​ продажи умножаются на​ число в ячейке​Логическое_значение2;. ​возвращает значение ИСТИНА,​=СУММ(ЕСЛИ(A6:A11>10;A6:A11))​или (если в​ использовать до 64​ =ЕСЛИ(A1>100;»Бюджет превышен»;»ОК!»). В​В функции «ЕСЛИ» также​Вставляем данное выражение в​ результат, если же​ нам нужно сделать​ Microsoft Excel, следует​ Excel решает условное​ такова: ЕСЛИ а​ чтобы программа правильно​=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)​ процент комиссионных. В​ A2 больше 1​Необязательный аргумент. Дополнительные проверяемые​ если в результате​(для ввода формулы​ ячейке​

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

​ так, чтобы в​ выделить функцию «ЕСЛИ».​ форматирование.​ = 1 И​ распознала текст.​Разберем синтаксис функции:​ противном случае возвращается​ИЛИ​ условия, вычисление которых​ вычисления хотя бы​ в ячейку вместо​

​A1​ друга функций ЕСЛИ().​ в ячейке​ «ИЛИ». Он подразумевает,​

Пример функции с несколькими условиями

​ столбца «Премия к​ то выводимый результат​ строчке со значением​ Это один из​Исходные данные (таблицы, с​ а = 2​​Логическое_выражение – ЧТО оператор​ значение 0.​меньше 100. В​ дает значение ИСТИНА​ одного из ее​ENTER​

​содержится значение 0)​=ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний​А1​ что значение является​ 8 марта».​ зависит уже от​ «жен.» в колонке​ тех операторов, к​ которыми будем работать):​ ТОГДА значение в​Часто на практике одного​ проверяет (текстовые либо​Видео: усложненные функции ЕСЛИ​ противном случае возвращает​ или ЛОЖЬ. Условий​ аргументов получается значение​нужно нажать​=ЕСЛИ(A1;»Бюджет превышен»;»ОК!»)​

​ проект»;»Малый проект «)))​результат формулы будет​ истинным, если выполнено​Как и в прошлый​ второго оператора.​ «Пол» в соответствующую​ которым пользователи прибегают​Выделяем первую таблицу. Условное​ ИНАЧЕ значение с.​ условия для логической​ числовые данные ячейки).​Использование вложенных функций​ значение ЛОЖЬ.​ может быть не​ ИСТИНА, и значение​CTRL+SHIFT+ENTER​вернут ОК!​Вышеуказанную формулу можно усложнять​ либо «Бюджет превышен»​ хотя бы одно​ раз, «протягиваем» формулу​

​Для примера возьмем все​ ячейку колонки «Премия​ чаще всего, при​ форматирование – создать​

​Функция ИЛИ проверяет условие​ функции мало. Когда​Значение_если_истина – ЧТО появится​

Пример с выполнением двух условий одновременно

​ в формуле​=ЕСЛИ(ИЛИ(A3>1;A3​ более 255.​ ЛОЖЬ, если в​)​Если в ячейке​

​ и дальше, но​ либо «ОК!».​ из нескольких условий.​ вниз.​ ту же таблицу​ к 8 марта»​ выполнении задач в​ правило – использовать​ 1 или условие​ нужно учесть несколько​ в ячейке, когда​Функция ЕСЛИ​Возвращает значение ячейки A3,​Примечания​ результате вычисления всех​Теперь подсчитаем количество вхождений​A1​ есть другие подходы:​Функция ЕСЛИ()относится к наиболее​Итак, предположим, что премия​В функции «ЕСЛИ» можно​ с выплатами премии​ выводилось значение «1000»,​ приложении. Давайте разберемся,​ формулу для определения​ 2. Как только​ вариантов принятия решений,​

​ текст или число​Функция И​ если оно меньше​Аргументы должны принимать логические​

Пример использования оператора «ИЛИ»

​ к 8 марту.​ а в строчках​ что представляет собой​ форматируемых ячеек:​ хотя бы одно​ выкладываем операторы ЕСЛИ​ отвечают заданному условию​Функция НЕ​ 100​ значения (ИСТИНА или​ значение ЛОЖЬ.​ в диапазоне ячеек​ число кроме 0,​ превышен»>)​ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])​ в 100 рублей​ «И», который позволяет​ Но, на этот​ со значением «муж.»​ функция «ЕСЛИ», и​

​В строку формул записываем:​

​ условие истинно, то​ друг в друга.​ (правдивы).​Полные сведения о​ИЛИ​ ЛОЖЬ) либо быть​Обычно функция​A6:A11​ то формула вернет​=ВПР(A1;A3:B6;2)​Лог_выражение​ положена только женщинам,​

​ считать истинной только​

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

​ раз, согласно условиям,​ в колонках «Премия​ как с ней​ =СЧЕТЕСЛИ (сравниваемый диапазон;​ результат будет истинным.​ Таким образом, у​​Значение,если_ложь – ЧТО появится​​ формулах в Excel​больше 1. В​ массивами либо ссылками,​

​ИЛИ​=СЧЁТЕСЛИ(A6:A11;»>10″). Аналогичный результат​

Синтаксис функции

​ Бюджет превышен. Такой​

​Для функции ВПР() необходимо​​ — любое значение или​ которые входят в​ выполнение двух или​
​ размер премии зависит​
​ к 8 марта»​ работать.​​ первая ячейка первой​​ Суть такова: ЕСЛИ​ нас получиться несколько​ в графе, когда​Рекомендации, позволяющие избежать​ противном случае возвращает​ содержащими логические значения.​

​используется для расширения​ (2) можно получить​ подход удобен, когда​
​ создать в диапазоне​
​ выражение, принимающее значения​ число основного персонала.​​ нескольких условий одновременно.​​ от категории работника.​ стояло значение «0».​Скачать последнюю версию​​ таблицы)=0. Сравниваемый диапазон​​ а = 1​ функций ЕСЛИ в​​ текст или число​​ появления неработающих формул​

Вложенные ЕСЛИ

​ сообщение «Значение вне​Если аргумент, который является​​ возможностей других функций,​​ с помощью формулы массива​ проверяется равенство значения​A3:B6​ ИСТИНА или ЛОЖЬ.​ В этом случае,​
​Например, в нашем случае,​ Женщины, имеющие статус​

​ Наша функция примет​ Excel​ – это вторая​

​ НЕ отвечают заданному​

​Поиск ошибок в​ допустимого диапазона».​​ ссылкой или массивом,​​ выполняющих логическую проверку.​

​=СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))​ нулю.​таблицу значений:​​=ЕСЛИ(A1>=100;»Бюджет превышен»;»ОК!»)​​ если работник мужчина,​ премия к 8​ основного персонала получают​ такой вид: «ЕСЛИ(B6=»жен.»;​

​«ЕСЛИ» является стандартной функцией​

​ таблица.​ 2 ТОГДА значение​Синтаксис будет выглядеть следующим​ условию (лживы).​ формулах​

​=ЕСЛИ(ИЛИ(A250);A2;»Значение вне допустимого диапазона»)​

Опущен третий аргумент [значение_если_ложь]

​ содержит текст или​ Например, функция​Теперь, когда принцип понятен,​EXCEL содержит также другие​Если требуется вывести разный​Т.е. если в​
​ или относится к​
​ марта в размере​​ по 1000 рублей​​ «1000»; «0»)».​ программы Microsoft Excel.​Чтобы вбить в формулу​

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

​ в ИНАЧЕ значение​ образом:​
​Пример:​
​Сочетания клавиш и​Возвращает значение ячейки A2,​​ пустые ячейки, то​​ЕСЛИ​
​ с помощью функции​
​ функции, которые можно​

​ текст в случае​​ ячейке​​ вспомогательному персоналу, то​ 1000 рублей выдаётся​ премии, а вспомогательный​Вводим данное выражение в​ В её задачи​ диапазон, просто выделяем​ с.​

Связь функции ЕСЛИ() с другими функциями использующие условия

​=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))​Оператор проверяет ячейку А1​ горячие клавиши в​ если оно меньше​ такие значения игнорируются.​выполняет логическую проверку​ ЕСЛИ() можно конструировать​ применять для анализа​ наличия в ячейке​A1​ величина его премии​ только женщинам, которые​

​ персонал получает только​ самую верхнюю ячейку,​ входит проверка выполнения​ его первую ячейку​Функции И и ИЛИ​Здесь оператор проверяет два​ и сравнивает ее​

​ Excel​ 0​​Если заданный диапазон не​​ и возвращает одно​

​ и другие формулы​ данных с использованием​А1​содержится значение большее​ будет равна нулю,​ являются основным персоналом,​ 500 рублей. Естественно,​
​ где должен выводиться​
​ конкретного условия. В​ и последнюю. «=​​ могут проверить до​​ параметра. Если первое​​ с 20. Это​​Сочетания клавиш в​

​ИЛИ​ содержит логических значений,​ значение, если при​​ с условиями. Например,​​ условий. Например, для​отрицательного значения, положительного​ или равное 100,​
​ а иначе –​

​ а мужчины, и​ что мужчинам этот​ результат. Перед выражением​ случае, когда условие​ 0» означает команду​ 30 условий.​ условие истинно, то​ «логическое_выражение». Когда содержимое​
​ Excel 2016 для Mac​

Вложенные функции ЕСЛИ

​ функция​ проверке получается значение​ нахождение минимального значения​ подсчета количества вхождений​ значения или 0,​ то формула вернет​

Хотите узнать больше?

​ представительницы женского пола,​

​ вид выплат вообще​

​ выполнено (истина), то​

​ поиска точных (а​

Функция ИЛИ

​Пример использования оператора И:​​ формула возвращает первый​ графы больше 20,​Логические функции (ссылка)​ противном случае возвращает​ИЛИ​

Читать еще:  Как поставить округление в excel

Пример

Технические подробности

​ ИСТИНА, и другое​​ среди чисел больше​​ чисел в диапазоне​ то можно записать​ ОК!, а если​ виде формулы это​ числящиеся вспомогательным персоналом,​ не положен независимо​После этого, жмем на​ в ячейку, где​ не приблизительных) значений.​Пример использования функции ИЛИ:​

​ аргумент – истину.​​ появляется истинная надпись​​Функции Excel (по​ сообщение «Значение вне​возвращает значение ошибки​ значение, если при​​ 10:​​ ячеек используется функция​ следующую формулу:​ нет, то Бюджет​ выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»;​ не получают ничего.​ от категории.​ кнопку Enter. Теперь,​ использована данная функция,​​Выбираем формат и устанавливаем,​​Пользователям часто приходится сравнить​ Ложно – оператор​​ «больше 20». Нет​​ алфавиту)​ допустимого диапазона».​ #ЗНАЧ!.​

​ проверке получается значение​

​=МИН(ЕСЛИ(A6:A11>10;A6:A11))​​ СЧЁТЕСЛИ(), а для​

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

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

Функция имеет следующий синтаксис.

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

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

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

Формула ЕСЛИ в Excel – примеры нескольких условий

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

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

Функция ЕСЛИ — вложенные формулы и типовые ошибки

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

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

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

Операторы если являются чрезвычайно надежными и формируют основу многих моделей электронной таблицы, но они также являются корневой причиной множества проблем с электронной таблицей. В идеале оператор если следует применять к минимальным условиям, таким как «женщина/женщина», «да/нет», и, возможно, вам нужно оценить более сложные сценарии, требующие вложенности * более 3 функций, если они вместе.

* «Вложенность» означает объединение нескольких функций в одной формуле.

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

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

Условие, которое нужно проверить.

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

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

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

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

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

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

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

Примеры

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

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»))))))))))))» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

    Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

    Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

    Если тестовых баллов больше 79, учащийся получает оценку B.

    Если тестовых баллов больше 69, учащийся получает оценку C.

    Если тестовых баллов больше 59, учащийся получает оценку D.

    В противном случае учащийся получает оценку F.

    Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

    Он по-прежнему функционально точен и будет работать должным образом, но он занимает много времени и больше не проверяется, чтобы убедиться в том, что вы хотите сделать. Другая неполадка с покрытием состоит в том, что вам пришлось вводить баллы и аналогичные буквенные оценки вручную. В чем заключается вероятность того, что у вас случайно есть опечатка? Теперь представьте себе, что это 64 с более сложными условиями. Конечно, это возможно, но вы действительно хотите, чтобы вы сами подзнали этот тип сил и вероятные ошибки, которые могут быть очень трудными?

    Читать еще:  Как поменять формат видео на avi

    Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку «)» тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.

    Дополнительные примеры

    Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.

    15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

      Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д.

      Несмотря на то, что в примере более ранних оценок, эта формула является прекрасным примером того, насколько сложно поддерживать большое количество операторов Если — что вам нужно сделать, если ваша организация решила добавить новые уровни компенсаций и, возможно, даже изменить Существуют ли значения в долларах или процентах? Ты поработал на руках много усилий!

      Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.

      Перед вами пример сценария для расчета комиссионных с неправильной логикой:

      5000;10%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>15000;20%;0)))))» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

      Видите, что это не так? Сравните порядок сравнения доходов с предыдущим примером. Каким образом это происходит? Это правильно, и это не помещается снизу вверх ($5 000 – $15 000), а не наоборот. Но зачем это большая? Это существенная проблема, так как формула не может пройти первое вычисление для любого значения за пределами $5 000. Предположим, что у вас есть $12 500 в доход — инструкция если вернет 10%, так как она больше $5 000, и она прекратит свою работу. Это может быть чрезвычайно проблематичным, так как в некоторых случаях ошибки, возникающие при возникновении отрицательных появления негативного воздействия на эти типы, оказываются незамеченными. Что нужно знать о некоторых серьезных возможностях при использовании сложных вложенных операторов Если. В большинстве случаев вместо создания сложной формулы с помощью функции если вы можете использовать функцию ВПР. Для использования функции ВПРнеобходимо сначала создать ссылочную таблицу.

        В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.

          Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.

          Примечание: В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему.

          ФУНКЦИЯ ВПР более подробно описана, но это является намного проще, чем12-уровневая, сложная вложенная инструкция если! Есть и другие, менее очевидные, преимущества:

          Таблицы ссылок функции ВПР открыты и их легко увидеть.

          Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.

          Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.

          Вы знали?

          Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:

          можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:

          Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.

          Примечание: Эта функция доступна, только если у вас есть подписка на Office 365. Если вы являетесь подписчиком Office 365, убедитесь, что у вас установлена последняя версия Office.

          Дополнительные сведения

          Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

          См. также:

          Видео: расширенные функции если
          функция СЧЁТЕСЛИ (Office 365, Excel 2016 и более поздние версии)
          . функция функции ЭКСПРАСП подсчитывает значения на основеодного условия
          . функция СУММЕСЛИ вычислит значения на основенескольких критериев
          , которые будут суммироваться с учетомодного условия
          ,
          для которого вычисляется сумма на основе нескольких условийи
          функций
          VLOOKUP function

          Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

          Формула в Эксель ЕСЛИ, когда несколько условий

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

          Функция ЕСЛИ

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

          • Открываем Эксель, найдя программу в меню «Пуск» или на рабочем столе.

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

          • Добавляем столбец «Премия» — в него выводим результаты функции ЕСЛИ. Курсор ставим в ячейку G4.

          • Кликаем по значку функции, расположенному слева от строки ввода оператора, которая находится над рабочим полем. Также формулу в Эксель можно вставить, обратившись к пункту меню «Формулы» и выбрав там «Логические».

          • В «Категории» находим «Логические», а в появившемся списке — функцию ЕСЛИ. Кликаем по «Ок».

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

          • Предположим, премия менеджеров составляет 30%. Заполняем аргументы, начиная с логического выражения — пишем там адрес ячейки и нужное значение. В нашем случае это выглядит следующим образом: Лог_выражение = D4=«менеджер». Затем указываем размер премии (30), если выражение истинно, и 0, если оно ложно. Нажимаем «Ок».

          • В результате видим, что премия первого сотрудника составляет 0%, так как он не является менеджером. Условие выполнено!

          • Таблица содержит множество строк, необходимо заполнить их все. Конечно, не требуется каждый раз вбивать функцию заново, ее надо просто скопировать. Сделать это очень просто — кликаем мышкой по ячейке G4 (там уже получен результат), подводим курсор к левому нижнему углу и смотрим, когда он превратится в плюс.

          • Растягиваем формулу на остальные ячейки, держа левую кнопку мыши нажатой и опускаясь вниз.

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

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

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

          Часто одним условием дело не ограничивается — например, нужно начислить премию только менеджерам, которые работают в Южном филиале компании. Действуем следующим образом:

          • Выделяем мышкой первую ячейку (G4) в столбце с премиями. Кликаем по значку Fx, находящемуся слева от строки ввода формул.
          Читать еще:  Как посчитать среднее значение в excel

          • Появится окно с уже заполненными аргументами функции.

          • Изменяем логическое выражение, добавив туда еще одно условие и объединив их с помощью оператора И (условия берем в скобки). В нашем случае получится: Лог_выражение = И(D4=«менеджер»;E4=«Южный»). Нажимаем «Ок».

          • Растягиваем формулу на все ячейки, выделив первую и потянув мышкой вниз при нажатой левой клавише.

          Совет: если в таблице много строк, то становится неудобно постоянно перематывать вверх-вниз, чтобы посмотреть шапку. Выход есть — закрепить строку в Excel. Тогда названия столбцов будут всегда показаны на экране.

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

          В качестве примера рассмотрим, как начислить в Экселе премию в размере 40% всем сотрудникам, которые являются бухгалтерами или директорами. То есть произведем выборку по двум условиям:

          • Кликаем мышью по первой ячейке в столбце премий (G4), затем нажимаем на иконку слева от строки ввода формул.

          • Редактируем аргументы функции. Логическое выражение будет представлять собой: ИЛИ(D4=«бухгалтер»;D4=«директор»). В «Значение_если_истина» пишем 40, а в «Значение_если_ложь» — 0. Кликаем «Ок».

          • Копируем формулу, растягивая ее на остальные ячейки. Смотрим результат — премия 40% начислена директору и двум бухгалтерам.

          Функция СУММЕСЛИ

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

          • Добавляем строку с общей зарплатой продавцов и кликаем по ячейке, куда будет выводится результат.

          • Нажимаем на иконку Fx, которая находится слева от строки ввода функций. В открывшемся окне ищем нужную формулу через поиск — вводим в соответствующее окно «суммесли», выбираем оператор в списке, кликаем «Ок».

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

          • Вводим аргументы — первое поле «Диапазон» определяет, какие ячейки нужно проверить. В данном случае — должности работников. Кликаем мышкой в поле «Диапазон» и указываем там D4:D18. Можно поступить еще проще — просто выделить нужные ячейки.

          • В поле «Критерий» вводим «продавец». В «Диапазоне_суммирования» пишем ячейки с зарплатой сотрудников (вручную либо выделив их мышкой). Далее — «Ок».

          • Смотрим на результат — общая заработная плата всех продавцов посчитана.

          Совет: сделать диаграмму в Excel просто и быстро — нужно всего лишь найти соответствующую кнопку на вкладке «Вставка» в меню.

          Функция СУММЕСЛИМН

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

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

          • Кликаем по значку функции. В появившемся окне в поле поиска вводим «суммеслимн», нажимаем «Найти» и выбираем нужный оператор из списка.

          • В открывшемся окне необходимо заполнить аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Диапазон_условия1» — ячейки с должностями сотрудников. «Условие1» = «менеджер», так как мы суммируем зарплату менеджеров. Теперь нужно учесть второе условие — взять менеджеров из Южного филиала. В «Диапазон_условия2» вводим ячейки с филиалами, «Условие2» = «Южный». Все аргументы определены, нажимаем «Ок».

          • В результате будет рассчитана общая зарплата всех менеджеров, работающих в Южном филиале.

          Функция СЧЁТЕСЛИ

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

          • Добавляем строку с количеством продавцов и выделяем ячейку, куда планируется вывести результат.

          • Кликаем по кнопке «Вставить функцию», расположенной во вкладке «Формулы» в меню. В открывшемся окне в поле «Категория» выбираем «Полный алфавитный перечень». В списке формул находим по алфавиту СЧЁТЕСЛИ, нажимаем «Ок».

          • Заполняем аргументы функции — в поле «Диапазон» указываем ячейки с должностями, в «Критерии» пишем «продавец». Далее — «Ок».

          • В результате получаем количество продавцов, работающих в компании. Эксель просто считает ячейки, где написано «продавец».

          Функция СЧЁТЕСЛИМН

          Иногда возникают более сложные задачи — например, нужно определить, сколько продавцов работает в Северном филиале. Тогда следует воспользоваться формулой СЧЁТЕСЛИМН:

          • Добавляем строку с количеством продавцов Северного филиала и выделяем ячейку с будущим результатом.

          • Кликаем по кнопке «Вставить функцию» во вкладке «Формулы». Через алфавитный перечень находим нужную функцию и нажимаем «Ок».

          • Вводим аргументы функции: «Диапазон_условия1» — это ячейки с должностями, «Условие1» = «продавец». В «Диапазон_условия2» пишем ячейки с филиалами, «Условие2» = «Северный».

          • В итоге будет определено количество продавцов Северного филиала.

          Подводим итоги

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

          Функция ЕСЛИ (IF) в Excel — правила использования, примеры

          Функция ЕСЛИ() применяется для выборки значения (-ий) по заданному условию (-ий). Аргументы функции:

          Аргумент Лог_выражение применяется для записи непосредственно условия поиска. Возвращает логические значения «истина» или «ложь». Для формирования условия, значения можно брать как из ячеек, так и записывать непосредственно в формуле.

          Два остальных аргумента — для вывода значения, если первый аргумент вывел «истину» и второй если вывел «ложь».

          Например, требуется сравнить два значения и вывести в ячейке большее значение. Синтаксис формулы такой:

          =ЕСЛИ(B2>C2;B2;C2) – читается так, если значение B2 больше C2 выводим B2, иначе C2:

          Тоже самое можно сделать для разных форматов, например, для текста:

          В этом примере я проверяю значение ячейки B4 и если там слово «яблоко» (написано непосредственно в строке условия, в двойных кавычках), то показать слово «фрукт». Аргументы если «истина» и, если «ложь» являются не обязательными, в случае их отсутствия, при выполнения условия будет выводиться слово «истина» или слово «ложь».

          В моем примере, если написать в B4 любое другое слово программа скажет:

          Примеры функции ЕСЛИ(IF) с несколькими условиями.

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

          Возьмем предыдущий пример, и напишем дополнительное условие для слова груши:

          Читается так: если B4 равно «яблоко», то выводим слово «фрукт», иначе (выполняем следующую функцию ЕСЛИ) если B4 равно «груша», то выводим слово «фрукт», иначе выводим «не фрукт».

          Таких вложений может быть до 64!

          Функция ЕСЛИ() выполняет условия строго по порядку и завершает свою работу, если условие было выполнено.

          Это очень важное замечание в использовании формулы его необходимо учитывать. Приведу пример поломанной логики:

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

          Применяем такую формулу:

          и протягиваем ее:

          Получаем 10% для всех значений. Формула начинается работу с первого аргумента – он возвращает истину, и формула завершает свою работу.

          Здесь спасти положение может только добавление в функцию ЕСЛИ() функцию И(), для проверки в каком диапазоне находится себестоимость, и какую наценку надо выводить:

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

          Заменим в предыдущем примере столбец наценка на столбец цена и будем выводить сразу итоговую цену. Формула примет следующий вид:

          Главное не запутаться в скобках 😊. Протягиваем формулу:

          На этом все, если у вас есть вопросы оставляйте в комментариях.

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