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

Функция УСЛОВИЯ

​ значения с учетом​​принимает значение ИСТИНА.​ и сообщить, помогла​ возвращает значение «Хорошо»,​или​ используемых функций Excel.​ ячейке​содержится значение большее​ тестирования.​Аргументы функции​ оригинал (на английском​No Valid Score​ как относительные, абсолютные​(Shifo)200?’200px’:»+(this.scrollHeight+5)+’px’);»>…;если……. — и​ качестве условия сложное​ свыше 5, размер​Выполним расчет по формуле:​ одного условия​ Каждый аргумент​ ли она вам,​ а если ложно,​

​Не сдал​ Используя ее совместно​A1​ или равное 100,​Скопируйте образец данных из​отображаются аргументы для​ языке) .​.​ и смешанные ссылки.​ так 12 раз​

​ выражение, которое может​​ субсидии – 50%.​Описание аргументов:​Функция СЧЁТЕСЛИМН подсчитывает​значение_если_истинаN​ с помощью кнопок​ то «Плохо».​. Те, кто набрал​ с операторами сравнения​содержится значение 0)​

Простой синтаксис

​ то формула вернет​

​ следующей таблицы и​​ функции​

• ​Использование функции в качестве​Вот еще один пример:​

• ​ Рекомендую почитать об​

• ​ …​​ быть записано с​ Определить, кому полагаются​ИЛИ(B3​ значения с учетом​соответствует условию​ внизу страницы. Для​Скопировав формулу в остальные​

• ​ более 45 баллов​ и другими логическими​=ЕСЛИ(A1;»Бюджет превышен»;»ОК!»)​ ОК!, а если​ вставьте их в​ЕСЛИ​ одного из аргументов​=IF(A1​

​ этом в интернете.​

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

​Надо видеть вашу​

​ использованием функций И​

​ субсидии, а кому​	​C3*4,35 – сумма к​
​ нескольких условий​​лог_выражениеN​	​ удобства также приводим​ ячейки таблицы, можно​
​ – сдали экзамен,​​ функциями Excel, можно​	​вернут ОК!​ нет, то Бюджет​ ячейку A1 нового​. Чтобы вложить другую​
​ формулы, использующей функцию​​=ЕСЛИ(A1​	​=ИНДЕКС(I6:AF6;E$1*2)​ таблицу «живьем»​
​ и ИЛИ. Например,​​ – нет.​	​ оплате, если функция​​Функция СУММЕСЛИ суммирует​​. Может не указываться.​ ссылку на оригинал​​ увидеть, что на​​ остальные нет.​​ решать достаточно сложные​​Если в ячейке​

Пример 1

​ листа Excel. Чтобы​

​ функцию, можно ввести​

​ называется вложения, и​​Если значение в ячейке​Serge_007​Могу предположить ,​ приведенная выше функция​Вид исходной таблицы:​ ИЛИ вернет значение​ значения с учетом​79;»B»;A2>69;»C»;A2>59;»D»,ИСТИНА,»F»)» />​

Пример 2

​ (на английском языке).​ отлично сдал один​

​Выделите ячейку, в которую​

​ задачи. В этом​​A1​В качестве аргументов функции,​ отобразить результаты формул,​ ее в поле​ мы будем воспринимают​A1​: Игорь, с твоего​ что вы работаете​ может быть переписана​Для проверки критериев согласно​ ИСТИНА;​ одного условия​Формула для ячеек A2:A6:​

Замечания

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

• ​ следующим образом:​​ условию задачи запишем​​C3*5,25 – сумма к​Функция СУММЕСЛИМН суммирует​​ =УСЛОВИЯ(A2>89;»A»;A2>79;»B»;A2>69;»C»;A2>59;»D»,ИСТИНА,»F»)​​ одного или нескольких​ оценки хорошо и​

• ​ В нашем случае​​ разобрать ее действие​​ число кроме 0,​ например:​ нажмите клавишу F2,​ ввести​

• ​ качестве вложенные функции.​10​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС($I$6:$AF$6;;$E$1*2)​Один из вариантов:​

См. также:

​=ЕСЛИ(И(EXP(3)>20;EXP(3)​​ формулу:​ оплате, если ИЛИ​ значения с учетом​Расшифровка:​​ условий и возвращает​ плохо по два​​ это ячейка C3.​ на простых примерах,​ то формула вернет​​=ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10))​ а затем — клавишу​СУММ(G2:G5)​​ К примеру, добавив​, функция возвращает​Serge_007​​=ВЫБОР(A1;»Январь»;»Февраль»;»Март»;»Апрель»;»Май»;»Июнь»;»Июль»;»Август»;»Сентябрь»;»Октябрь»;»Ноябрь»;»Декабрь»)​Результат выполнения выражения И(EXP(3)>20;EXP(3)​5;50%;30%);»нет»)’ class=’formula’>​​ вернет ЛОЖЬ.​​ нескольких условий​​ЕСЛИ(значение A2 больше 89,​​ значение, которое соответствует​ человека.​​Введите в нее выражение:​ а также научимся​​ Бюджет превышен. Такой​Т.е. если в​ ВВОД. При необходимости​в поле​​ вложенные функции СРЗНАЧ​​350​: Может Вы нарисуете​​или можно упростить​Иногда требуется узнать, оказалось​

support.office.com>

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

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

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+ВВОД перед текстом, который хотите перенести на другую строку.

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

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

Видите, что происходит? Посмотрите порядок сравнения доходов в предыдущем примере. А как все идет в этом? Именно! Сравнение идет снизу вверх (от 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.

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

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; ; )» .

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

Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «ЕСЛИ».

1. Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
2. Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».

Так мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ».

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

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

1. Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
2. Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))» .
3. Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».

Как и в прошлый раз, «протягиваем» формулу вниз.

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

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

1. Например, в нашей ситуации премия к 8 марта в размере 1000 рублей выдается только женщинам, которые являются основным персоналом, а мужчины и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной персонал»); «1000»; «0»)» . Вставляем ее в ячейку.

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

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

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

1. Итак, предположим, что премия к 8 марта в 1000 рублей положена только женщинам, которые входят в число основного персонала. В этом случае, если работник — мужчина или относится к вспомогательному персоналу, то величина его премии будет равна нулю, а иначе – 1000 рублей. В виде формулы это выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»; C6=»Вспомогательный персонал»); «0»; «1000»)» . Записываем ее в соответствующую ячейку таблицы.

«Протягиваем» результаты вниз.

Как видим, функция «ЕСЛИ» может оказаться для пользователя хорошим помощником при работе с данными в Microsoft Excel. Она позволяет отобразить результаты, соответствующие определенным условиям.

Функции И и ИЛИ

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

Функция И. Её используют, чтобы показать, что указанные число или текст должны соответствовать одновременно всем критериям. В этом случае функция возвращает значение ИСТИНА. Если один из критериев не соблюдается, функция И возвращает значение ЛОЖЬ.

Синтаксис функции И такой: =И(логическое_значение1;логическое_значение2;…), где логическое_значение — условия, которые функция будет проверять. Задано может быть до 255 условий.

Пример работы функции И. Проверим, соблюдены ли два условия:

• число 662 больше 300;
• число 8626 больше 9000.

Для этого выберем любую ячейку и в строке формул введём: =И(A1>300;A2>9000), где А1 — ячейка с числом 662, А2 — ячейка с числом 8626.

Нажмём Enter. Функция возвращает значение ЛОЖЬ — одно из условий не соблюдено (число 8626 < 9000).

Функция И вернула значение ЛОЖЬ, так как один из критериев не соблюдёнСкриншот: Excel / Skillbox Media

Проверим другие условия:

• число 662 меньше 666;
• число 8626 больше 5000.

Снова выберем любую ячейку и в строке формул введём: =И(A1<666;A2>5000).

Функция возвращает значение ИСТИНА — оба условия соблюдены.

Функция И вернула значение ИСТИНА, так как соблюдены оба критерияСкриншот: Excel / Skillbox Media

Функция ИЛИ. Её используют, чтобы показать, что указанные число или текст должны соответствовать одному из условий. Если хотя бы одно условие соблюдено, функция возвращает значение ИСТИНА. Если все условия не соблюдены, функция ИЛИ возвращает значение ЛОЖЬ.

Синтаксис функции ИЛИ: =ИЛИ(логическое_значение1;логическое_значение2;…).

Максимальное количество логических значений (условий) — тоже 255.

Пример работы функции ИЛИ. Проверим три условия:

• число 662 меньше 666;
• число 8626 больше 5000;
• число 567 больше 786.

В строке формул введём: =ИЛИ(A1<666;A2>5000;A3>786).

Функция возвращает значение ИСТИНА, несмотря на то, что одно условие не соблюдено (число 567 < 786).

Функция ИЛИ вернула значение ИСТИНА — соблюдены два критерия из трёхСкриншот: Excel / Skillbox Media

Проверим другие условия:

• число 662 меньше 500;
• число 8626 больше 9000;
• число 567 больше 600.

В строке формул введём: =ИЛИ(A1<500;A2>9000;A3>600).

Функция возвращает значение ЛОЖЬ, так как ни одно из условий не соблюдено.

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

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

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

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

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

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

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

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

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

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

​ ИЛИ а =​ 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​

excel2.ru>

Производим вычисления по условию.

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

В примере ниже столбец F содержит даты завершения закупок шоколада.

Поскольку даты для Excel — это числа, то наша задача состоит в том, чтобы проверить в ячейке наличие числа.

Формула в ячейке F3:

Как работает эта формула?

Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, то легко можно составить условие. Если число значений равно числу ячеек Excel, то значит, пустых среди них нет и можно производить вычисление. Если такого равенства нет, значит есть хотя бы одна пустая ячейка, и вычислять нельзя.

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

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

Функция ЕПУСТО (английский вариант — ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.

Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. А вот если не пусто – то производим вычисления.

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

Рассмотрим теперь более универсальные решения.

В качестве аргумента условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.

И, наконец, еще одна формула ЕСЛИ (IF) в Excel, которая проверит «если не пусто» и позволит производить расчет только при наличии непустых ячеек.

Функция ЕЧИСЛО (или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.

А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.

Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.

А теперь рассмотрим как проверить, что ячейки не пустые, если в них могут быть записаны не только числа, но и текст.

Итак, перед нами уже знакомое выражение

Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.

То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.

А теперь вставляем в ячейку D5 недостающее значение и проверяем, все ли работает.

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

Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями «если – то» при помощи функции ЕСЛИ (IF) в Excel.

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

Что является аргументом функции в Excel?

Аргументы функций – это исхоные данные для расчета функции. Например, для функции СУММ (суммирование) – это перечень чисел, ячеек или диапазонов ячеек для суммирования. Аргументы указываются в скобках после имени функции и разделяются точкой с запятой (в англоязычной версии — запятой).

Что может быть аргументом функции в Excel?

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

Как сделать аргумент функции в Excel?

Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите клавиши SHIFT + F3, чтобы открыть диалоговое окно Вставка функции . 3. аргументы. Аргументы могут быть числами, текстом, логическими значениями, такими как Истина или ложь, массивами, значениями ошибок, например #N/a или ссылками на ячейки.

Что может быть аргументом функции в электронных таблицах?

Аргументами функции могут являться числовые значения, ссылки на ячейки, диапазоны, имена, текстовые строки и вложенные функции. &#8230; Функции с пустой парой скобок после имени не требуют аргументов, тем не менее необходимо включать эти пустые скобки в формулу, чтобы Microsoft Excel распознал функцию.

Как задать функции в Excel?

Как вставить функцию в Excel

1. Выделите ячейку, в которую необходимо вставить формулу. В нашем примере мы выделим ячейку C11.
2. Введите знак равенства (=) и нужное имя функции. &#8230;
3. Введите диапазон ячеек в качестве аргумента в круглых скобках. &#8230;
4. Нажмите Enter на клавиатуре.

Как можно вызвать Мастер функций в Excel?

Как использовать Мастер функций в Excel

1. Выделите ячейку, которая будет содержать формулу. &#8230;
2. Откройте вкладку Формулы на Ленте, а затем выберите команду Вставить функцию.
3. Откроется Мастер функций. &#8230;
4. Посмотрите список результатов, чтобы найти нужную функцию, затем нажмите OK. &#8230;
5. Появится диалоговое окно Аргументы функции.

Что такое синтаксис в Excel?

Синтаксис функций в Excel

Для корректной работы, функция должна быть написана в определенной последовательности, которая называется синтаксис. К базовому синтаксису функции относятся знак равенства (=), имя функции (например, СУММ) и один или более аргументов.

Как написать формулу в формуле Эксель?

Создание простой формулы в Excel

1. Выделите на листе ячейку, в которую необходимо ввести формулу.
2. Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении. В нашем примере введите =1+1. Примечания: &#8230;
3. Нажмите клавишу ВВОД (Windows) или Return (Mac).

Как делать сложные формулы в Excel?

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

1. Выделите ячейку, которая будет содержать формулу. В нашем примере мы выбрали ячейку C4.
2. Введите в нее следующее выражение: =B2*C2+B3*C3. &#8230;
3. Выполните проверку, затем нажмите Enter на клавиатуре.

Как создать функцию суммы в Excel?

Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку &#171;Автосумма&#187; на вкладке Главная и нажмите клавишу ВВОД. При нажатиикнопки &#187; Excel в Интернете &#187; автоматически введет формулу (которая использует функцию СУММ)для суммы чисел.

Что не может содержать формула в электронной таблице?

В электронных таблицах формула не может включать в себя: Варианты ответов 1 числа  2 данные текстового типа  3 имена ячеек 

Что является аргументом функции?

Аргумент функции — независимая переменная, от значений которой зависят значения функции. Аргумент комплексного числа — одна из величин, связанных с комплексным числом. Аргумент максимизации, Аргумент минимизации

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

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

Какие функции есть в Excel?

• Функции баз данных (Database)
• Функции даты и времени (Date & Time)
• Инженерные функции (Engineering)
• Финансовые функции (Financial)
• Проверка свойств и значений и Информационные функции (Information)
• Логические функции (Logical)
• Ссылки и массивы (References and arrays)

Какие функции есть в Ексель?

Формулы (функции) в Excel представляют собой конструкции, позволяющие выполнять вычисления, возвращать данные, манипулировать данными ячеек, проверять условия и т. д. В Microsoft 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-уровневая, сложная вложенная инструкция если! Есть и другие, менее очевидные, преимущества:

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

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

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

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

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

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

лог_выражение – это проверяемое условие. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

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

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

Используем несколько условий

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

Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку: Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60. Оценку Хорошо можно будет получить, набрав от 45 до 60 балов. Ну и в остальных случаях ставим Плохо.

1. Для решения этой задачи составим формулу, включив в нее необходимые критерии оценивания: =ЕСЛИ (В3>60;»Отлично»;ЕСЛИ(B2>45;»Хорошо»;»Плохо»)).
2. Эта формула использует одновременно два условия. Первая проверка В3>60. Если баллы действительно больше 60, то в поле Результата мы получаем Отлично, и дальнейшая проверка условий не выполняется. Если Баллы меньше 60, то срабатывает вторая часть формулы, и мы проверяем В3>45 или нет. Если все верно, то возвращается значение «Хорошо», иначе «Плохо».
3. Затем достаточно скопировать формулу во все ячейки столбца и увидеть результат сдачи переаттестации.

Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить, что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.

Возвращаемые значения (return)

Распаковка возвращаемых значений

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

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

Пустая функция

Иногда разработчики оставляют реализацию на потом, и чтобы объявленная функция не генерировала ошибки из-за отсутствия тела, в качестве заглушки используется ключевое слово