Какое ограничение числа вложенных функций в ms excel

Какие существуют ограничения для формул Excel

Все формулы в Excel имеют следующую базовую структуру: они начинаются со знака равно (=), следом идут операнды — значения, ссылки на ячейки, диапазоны и их имена, названия функций — и все это может быть разделено операторами — символами, которые сочетают операнды определенным образом, такими как знак плюса (+) или знак больше (>). Ничего страшного, если вы будете использовать пробелы между операторами или операндами. Напротив, это повысит читаемость документа. Вы также можете перенести часть формулы на другую строку, используя сочетание клавиш Аlt + Enter. Несмотря на то что маловероятно превышение этих ограничений, неплохо быть всегда в курсе и знать возможные пределы. Все ограничения представлены в следующей таблице.

Ограничения формул в Excel 2010

Объект	Новый максимум	Старое ограничение (до Excel 2007)
Столбцы	16 384	1024
Строки	1 048 576	65 536
Длина функции (символов)	8 192	1 024
Аргументы функции	255	30
Уровень вложения функций	64	7
Ссылки	Неограничено	65 335
Столбцы сводной таблицы	16 384	255
Строки сводной таблицы	1 048 576	65 536
Поля в сводной таблице	16 384	255
Уникальность элементов в сводной таблице	1 048 576	3 268

Ввод формулы на рабочий лист достаточно прост и прямолинеен:

1. Выберите ячейку для ввода формулы.
2. Введите знак равенства (=) для указания Excel о том, что вы будете вводить формулу.
3. Введите все операнды и операторы формулы.
4. Нажмите Enter для подтверждения формулы.

При этом в Excel имеется три механизма ввода, в соответствии с которыми программа интерпретирует ваши действия:

• Когда вы вводите знак равенства (=) для начала формулы, Excel переходит в режим ввода, который используется вами для ввода операторов и операндов формулы.
• Если вы нажмете на любую из клавиш навигации клавиатуры (это все клавиши со стрелками, а также «Page Up», «Page Down», «Ноmе» и «End») или если вы кликнете мышью на любую другую ячейку, Excel переходит в режим указания. В данном режиме вы выбираете ячейки или диапазоны как операнды. Excel возвращается в режим ввода, когда вы введете с клавиатуры любой символ.
• При нажатии на F2 Excel переходит в режим редактирования. В данном режиме вы можете производить изменения в формуле. К примеру, вы можете использовать клавиши «←» и «→» клавиатуры для перемещения по различным частям формулы для удаления или вставки символов. Также в этот режим вы можете войти, дважды кликнув на ячейке с формулой или просто внутри формулы.Нажмите F2 для возврата в режим ввода.

Вы можете увидеть, в каком режиме находитесь, просто посмотрев в левом нижнем углу программы на строку состояния.

3.2 Основные статистические и математические функции Excel

AVERAGE(<список аргументов>) (СРЗНАЧ) — возвращает среднее арифметическое из значений всех аргументов.

Например, AVERAGE(C1:C7) возвращает среднее значение чисел, записанных в ячейках С1, С2, . С8.

МАХ(<список аргументов>) (МАКС) — возвращает максимальное число из списка аргументов.

MIN(<cпиcoк аргументов>) (МИН) — возвращает минимальное число из списка аргументов.

Например, МАХ(С1:С7) возвращает максимальное из чисел, записанных в ячейках С1, С2, . С7.

Перечислим наиболее распространенные математические функции Excel.

PRODUCT(<список аргументов) (ПРОИЗВЕД) — возвращает произведение значений аргументов.

SUM(<список аргументов>) (СУММ) — возвращает сумму значений аргументов.

SQRT(<число>) (КОРЕНЬ) — возвращает корень квадратный из числа.

FАСТ(число) (ФАКТР) — возвращает факториал целого числа, заданного в качестве аргумента.

RAND() (СЛЧИС) — возвращает случайное число в интервале от 0 до 1.

АВS(число) — возвращает модуль значения аргумента.

LN(число) — возвращает натуральный логарифм числа (по основанию е = 2,71828. ).

ЕХР(число) — возвращает экспоненту числа (е х ).

SIN(число) — возвращает синус числа, заданного в радианах.

С0S(число) — возвращает косинус числа, заданного в радианах.

ТАN(число) — возвращает тангенс числа, заданного в радианах.

3.3 Логические функции

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

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

Для решения таких задач применяют условную функцию IF. Эта функция имеет формат:

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

Первый аргумент функции IF — логическое выражение (в частном случае, условное выражение), которое принимает одно из двух значений: “Истина” или “Ложь” (1 или 0). В первом случае IF принимает значение выражения1, а во втором — значение выражения2. В качестве выражения1 или выражения2 можно записать вложенную функцию IF.

Вернемся к примеру 1. Если количество задано в кг, стоимость С рассчитывается по формуле:

где Q — количество (кг), Ц — цена (руб./кг). Если количество задано в тоннах, стоимость рассчитывается по формуле:

где Q1 — количество продукции (т).

Пусть в ячейке С5 помещается код единицы измерения количества продукции, который принимает значения:

В ячейке D5 помещается количество продукции, в ячейке Е5 — цена. В ячейку F5 необходимо поместить стоимость товара. Тогда в эту ячейку мы можем записать функцию:

IF(C5=1, D5*E5, IF(C5=2, D5*1000*E5, 0)).

Здесь логическое выражение С5=1 (в данном случае — это просто условное выражение). Если С5=1, условие выполнено и значение выражения равно “Истине”. Поэтому функция IF, записанная в ячейке F5, примет значение D5*E5. Если значение логического выражения ложно (С5 не равно 1), функция IF примет значение второго аргумента, т. е. вложенной функции IF. А каково ее значение? Оно зависит от выполнения условия С5==2. Если это условие выполнено (т. е. единица измерения количества — тонна), значением функции будет D5*1000*E5, если нет — значением функции будет нуль.

Число вложенных функций IF не должно превышать семи.

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

Формат функций одинаков:

АND(<логическое выражение1>,<логическое выражение2>. ),

ОR(<логическое выражение1>,<логическое выражение2>. ).

Функция AND принимает значение “Истина”, если одновременно истинны все логические выражения, указанные в качестве аргументов этой функции. В остальных случаях значение AND — “Ложь”. В скобках можно указать до 30 логических выражений.

Функция OR принимает значение “Истина”, если истинно хотя бы одно из логических выражений, указанных в качестве аргументов этой функции. В остальных случаях значение OR — “Ложь”.

Пример 2. Изменим условие формирования таблицы из примера 1. В ней могут быть товары, количество которых измеряется в килограммах или в штуках и соответственно цена за товар может быть в рублях за 1 кг или за одну штуку. В этом случае стоимость определяется только в том случае, если выполнено одно из условий: либо заданы и количество в кг или тоннах, и цена в рублях за 1 кг, либо заданы и количество в штуках, и цена в рублях за штуку. Для решения этой задачи, кроме кода единицы измерения количества продукции, необходимо ввести код единицы измерения цены. Выделим для этого клетку G5. Примем, что код единицы измерения цены имеет значения:

Тогда в ячейку F5 можно записать функцию IF:

IF(AND(C5=1 ,G5=1 ),D5*E5,IF(AND(C5=2,G5=1 ),

Вы видите, что если одновременно С5=1 и G5==l (кг и 1 руб./кг) или одновременно С5=3 и G5=2 (штуки и грн./шт), j стоимость равна D5*E5; если одновременно С5=2 и G5=l 1 (тонны и грн./кг), стоимость равна D5*1000*E5; в остальных •; случаях она равна нулю.

Подобным же образом можно использовать и функцию OR.

В Excel предусмотрены также еще три логические функции (NOT, FALSE и TRUE), которые можно изучить самостоятельно.

19 советов по работе с вложенными функциями ЕСЛИ

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

Но использование одной функции ЕСЛИ часто приводит к использованию второй, и как только вы объединяете более пары ЕСЛИ, ваши формулы могут начать выглядеть как маленькие Франкенштейны ��

Являются ли вложенные ЕСЛИ опасными? Всегда ли они необходимы? Какие есть альтернативы?

Читайте дальше, чтобы узнать ответы на эти вопросы и многое другое …

1. Базовый ЕСЛИ

Прежде чем говорить о вложенном ЕСЛИ, давайте быстро рассмотрим базовую структуру:

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

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

Обратите внимание на квадратные скобки … это означает, что аргументы необязательны. Однако вы должны указать либо значение ИСТИНА, либо значение ЛОЖЬ.

Чтобы проиллюстрировать это, мы используем ЕСЛИ, чтобы проверить результаты и вернуть «Зачтено» для баллов не менее 65:

Базовая функция ЕСЛИ — вернуть «Зачтено» для баллов не менее 65

Ячейка D4 в примере содержит эту формулу:

Что можно прочитать так: если количество баллов в ячейке C4 составляет не менее 65, вернуть «Зачтено».

Однако обратите внимание, что если оценка меньше 65, ЕСЛИ возвращает ЛОЖЬ, так как мы не указали «значение_если_ложь». Чтобы отобразить «Не зачтено» для непроходных оценок, мы можем добавить «Не зачтено» в качестве ложного аргумента следующим образом:

Базовая функция ЕСЛИ — с добавленным значением_если_ложь

2. Что значит вложение

Вложенность означает объединение формул, одна внутри другой, так что одна формула обрабатывает результат другой. Например, вот формула, в которой функция СЕГОДНЯ (TODAY) вложена в функцию МЕСЯЦ (MONTH):

Функция СЕГОДНЯ (TODAY) возвращает текущую дату внутри функции МЕСЯЦ (MONTH). Функция МЕСЯЦ (MONTH) берет эту дату и возвращает текущий месяц. Даже в формулах средней сложности часто используются вложения, поэтому вы увидите их в более сложных формулах.

3. Простой вложенный ЕСЛИ (IF)

Вложенный ЕСЛИ — это всего лишь два оператора ЕСЛИ в формуле, где один оператор ЕСЛИ появляется внутри другого.

Чтобы проиллюстрировать это, ниже я расширил оригинальную формулу «Зачтено/Не зачтено», приведенную выше, для обработки «пустых» результатов, добавив функцию еще одну функцию ЕСЛИ:

Простой вложенный ЕСЛИ

Внешний ЕСЛИ запускается первым и проверяет, является ли ячейка C4 пустой. Если это так, внешний ЕСЛИ возвращает «Неявка», а внутренний ЕСЛИ никогда не запускается.

Если ячейка не пуста, внешний ЕСЛИ возвращает ЛОЖЬ, и запускается вторая функция ЕСЛИ.

4. Вложенный ЕСЛИ (IF) для шкал

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

Хитрость заключается в том, чтобы выбрать направление (от высокого к низкому или от низкого к высокому), а затем соответствующим образом структурировать условия. Например, чтобы присвоить оценки в порядке «от низкого до высокого», мы можем представить решение, отраженное в следующей таблице. Обратите внимание, что нет условия для «Отлично», потому что, как только мы выполним все остальные условия, мы знаем, что баллов должно быть больше 90, и, следовательно, «Отлично».

Баллы	Оценка	Условие
0 — 63	Неуд.	< 64
64 — 72	Удовл.	< 73
73 — 89	Хорошо	< 90
90 — 100	Отлично

С четко понятными условиями мы можем ввести первый оператор ЕСЛИ:

Мы позаботились о «Неуд.». Теперь, чтобы обработать «Удовл.», нам нужно добавить еще одно условие:

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

Мы обработали все оценки и дошли до последнего уровня «Отлично». Вместо добавления еще одного ЕСЛИ, просто добавьте итоговую оценку для ЛОЖЬ.

Вот последняя вложенная формула ЕСЛИ в действии:

Завершенный вложенный пример ЕСЛИ
для расчета оценок

5. Логика вложенных ЕСЛИ

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

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

6. Используйте функцию «Вычислить формулу»

В Windows вы можете использовать функцию «Вычислить формулу», чтобы шаг за шагом посмотреть, как Excel решает ваши формулы. Это отличный способ «увидеть» логический поток более сложных формул и устранить неполадки, если что-то не работает. Кнопку «Вычислить формулу» можно найти на ленте на вкладке Формулы.

Функция Вычислить формулу в ленте

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

Окно Вычисление формулы

К сожалению, версия Excel для Mac не содержит этой функции, но вы можете использовать прием, описанный ниже.

7. Используйте F9, чтобы определить результаты проверки

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

Проверка вложенного ЕСЛИ

Результат проверки с клавишей F9

Используйте Ctrl + Z (Command + Z на Mac), чтобы отменить F9. Вы также можете нажать Esc, чтобы выйти из редактора формул без каких-либо изменений.

8. Помни об ограничениях

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

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

9. Расставляй круглые скобки как профессионал

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

Во-первых, если у вас несколько наборов скобок, круглые скобки имеют цветовую кодировку, поэтому открывающие скобки соответствуют закрывающим скобкам. Эти цвета нелегко рассмотреть, но при желании — можно:

Цветные скобки

Во-вторых, когда вы закроете круглые скобки, Excel выделит соответствующую пару. Вы также можете щелкнуть в формуле и использовать клавишу со стрелкой для перемещения по скобкам, и Excel будет выделять обе скобки, когда есть совпадающая пара. Если нет совпадений, вы не увидите жирного шрифта.

Пара скобок выделена жирным

К сожалению, выделение шрифтом — это функция только для Windows. Если вы используете Excel на Mac для редактирования сложных формул, иногда имеет смысл скопировать и вставить формулу в хороший текстовый редактор, чтобы получить лучшие инструменты для сопоставления скобок. Вы можете вставить формулу обратно в Excel после того, как вы все исправите.

10. Используйте окно подсказки для навигации и выбора

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

Выберите аргументы формулы с помощью подсказки на экране

11. Будьте осторожны с текстом и цифрами

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

Является ли результат теста в А1 действительно текстом, а не числом? Нет? Тогда не используйте кавычки с числом. В противном случае логический тест вернет ЛОЖЬ, даже если значение является проходным баллом, потому что «100» не совпадает с 100. Если тестовый балл является числовым, используйте вот такую формулу:

12. Добавляйте разрывы строк, чтобы облегчить чтение вложенных ЕСЛИ

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

Например, на приведенном ниже экране показан вложенный ЕСЛИ, который рассчитывает комиссионную ставку на основе суммы продажи. Здесь вы можете увидеть типичную вложенную ЕСЛИ-структуру, которую трудно расшифровать:

Вложенные ЕСЛИ без разрывов строк трудно читать

Однако, если я добавляю разрывы строк перед каждым «значением_если_ ложь», логика формулы легко читается. Кроме того, формулу легче редактировать:

Разрывы строк облегчают чтение вложенных ЕСЛИ

Вы можете добавить разрывы строк в Windows с помощью Alt + Enter, на Mac — Control + Option + Return.

13. Уменьшите количество ЕСЛИ с И и ИЛИ

Вложенные ЕСЛИ — мощный инструмент, но формулы быстро становятся громоздкими, когда вы добавляете больше уровней. Один из способов избежать большего количества уровней — использовать ЕСЛИ в сочетании с функциями И (AND) и ИЛИ (OR). Эти функции возвращают простой результат ИСТИНА / ЛОЖЬ, который отлично работает внутри ЕСЛИ, поэтому вы можете использовать их для расширения логики одного ЕСЛИ.

Например, в приведенной ниже задаче мы хотим поставить «х» в столбце D, чтобы отметить строки, где цвет «красный», а размер «маленький».

ЕСЛИ с функцией И проще, чем два вложенных ЕСЛИ

Мы могли бы написать формулу с двумя вложенными ЕСЛИ, вот так:

Однако, заменив одну проверку на функцию И, мы можем упростить формулу:

Таким же образом, мы можем легко расширить эту формулу с помощью функции ИЛИ, чтобы проверить наличие красного ИЛИ синего И маленького:

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

14. Замените вложенные ЕСЛИ на ВПР

Когда вложенный ЕСЛИ просто присваивает значения на основе одного значения, его можно легко заменить функцией ВПР (VLOOKUP). Например, этот вложенный ЕСЛИ присваивает номера пяти различным цветам:

= ЕСЛИ (F2 = «красный»; 100; ЕСЛИ (F2 = «синий»; 200; ЕСЛИ (F2 = «зеленый»; 300; ЕСЛИ (F2 = «оранжевый»; 400;500))))

Мы можем легко заменить все ЕСЛИ одним ВПР:

Вложенный ЕСЛИ против ВПР

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

15. Выберите ВЫБОР

Функция ВЫБОР (CHOOSE) может предоставить элегантное решение, когда вам необходимо отобразить простые последовательные числа (1,2,3 и т.д.) для произвольных значений.

В приведенном ниже примере ВЫБОР (CHOOSE) используется для создания пользовательских сокращений дней недели:

Функция ВЫБОР

Конечно, вы можете использовать длинный и сложный вложенный ЕСЛИ, чтобы сделать то же самое, но, пожалуйста, не надо ��

16. Используйте ЕСЛИМН вместо вложенных ЕСЛИ

Если вы используете Excel 2016, у Office 365 есть новая функция, которую вы можете использовать вместо вложенных ЕСЛИ: функция ЕСЛИМН (IFS). Функция ЕСЛИМН (IFS) предоставляет специальную структуру для оценки нескольких условий без вложенности

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

Обратите внимание, в формуле всего одна пара скобок!

Что происходит, когда вы открываете электронную таблицу, которая использует функцию ЕСЛИМН (IFS) в более старой версии Excel? В Excel 2013 и 2010 (и я верю в Excel 2007, но не могу проверить) вы увидите «_xlfn» в ячейке. Ранее вычисленное значение все еще будет там, но если формула пересчитается, вы увидите ошибку #ИМЯ?.

17. Используйте МАКС

Иногда вы можете использовать МАКС (MAX) или МИН (MIN) очень интересным способом, избегая оператора ЕСЛИ (IF). Предположим, что у вас есть расчет, который должен привести к положительному числу или нулю. Другими словами, если вычисление возвращает отрицательное число, вы просто хотите показать ноль.

Функция МАКС дает вам способ сделать это без ЕСЛИ:

Этот метод возвращает результат вычисления, если положительный, и ноль в противном случае.

Я люблю эту конструкцию, потому что она очень проста.

18. Перехват ошибок с помощью ЕСЛИОШИБКА

Классическим использованием ЕСЛИ является перехват ошибок и предоставление другого результата при возникновении ошибки, например:

= ЕСЛИ (ЕОШИБКА (формула); значение_если_ошибка; формула)

Это уродливо и неудобно, так как одна и та же формула вводится дважды, и Excel должен вычислять одно и то же несколько раз, если ошибки нет.

В Excel 2007 была введена функция ЕСЛИОШИБКА (IFERROR), которая позволяет более элегантно отлавливать ошибки:

= ЕСЛИОШИБКА (формула; значение_если_ошибка)

Теперь, когда формула выдает ошибку, ЕСЛИОШИБКА просто возвращает указанное вами значение.

19. Используйте «логическую» логику

Вы также можете иногда избегать вложенных ЕСЛИ, используя так называемую «логическую логику». Слово логическое относится к значениям ИСТИНА / ЛОЖЬ. Хотя Excel отображает слова ИСТИНА и ЛОЖЬ в ячейках, внутренне Excel воспринимает ИСТИНА как 1, а ЛОЖЬ как ноль.

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

= ЕСЛИ (F2 = «красный»; 100; ЕСЛИ (F2 = «синий»; 200; ЕСЛИ (F2 = «зеленый»; 300; ЕСЛИ (F2 = «оранжевый»; 400;500))))

Используя логическую логику, вы можете переписать формулу следующим образом:

Каждое выражение выполняет тест, а затем умножает результат теста на «значение, если оно истинно». Поскольку тесты возвращают значение ИСТИНА или ЛОЖЬ (1 или 0), результаты ЛОЖЬ фактически отменяют формулу.

Для числовых результатов логическая логика проста и чрезвычайно быстра, так как нет разветвлений. С другой стороны, логическая логика может сбивать с толку людей, которые не привыкли к ней. Тем не менее, это отличная техника, чтобы знать о ней.

Когда вам нужен вложенный ЕСЛИ?

Со всеми этими опциями для избежания вложенных ЕСЛИ, вы можете задаться вопросом: «А когда же его использовать?»

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

Например, предположим, что вы хотите определить статус счета-фактуры «Оплачено», «Ожидание», «Просрочено» и т.д. Для этого необходимо посмотреть дату счета-фактуры и остаток задолженности:

Расчет статуса счета-фактуры с помощью вложенного ЕСЛИ

Как прописать функцию «если» с несколькими условиями в программе Excel? Функция ЕСЛИ в Excel. Примеры (с несколькими условиями)

Рекомендую перед дальнейшим прочтением ознакомиться со статьей: «Правильное написание формул».
Функция ЕСЛИ проверяет условие. Когда оно выполняется (ИСТИНА) возвращается значение. Если нет — другое.

Выглядите так: =ЕСЛИ(логическое выражение; истина; ложь).
Где:

1. Выражение задается в виде числа или фразы. Например, с текстом «без налога»;
2. Истина. Значение, которое отобразится если выражение правильно;
3. Ложь — когда неправильно.

Простой пример формулы если в Эксель (Excel). Проверка равенства чисел из двух ячеек.

Рассмотрим примеры формулы ЕСЛИ в Ексель (Excel).

Функция ЕСЛИ в Excel (общие сведения)

Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:

1. Проверить всю колонку А на предмет наличия четных чисел.
2. Если четное число обнаружено, сложить такие-то значения.
3. Если четное число не обнаружено, то выдать надпись «не обнаружено».
4. Проверить получившееся число, является ли оно четным.
5. Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

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

Синтаксис функции ЕСЛИ с одним условием

Любая функция в Ecxel выполняется с помощью формулы. Шаблон, по которому нужно передавать функции данные, называется синтаксисом. В случае с оператором ЕСЛИ, формула будет в таком формате.

Давайте рассмотрим синтаксис более подробно:

1. Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
2. Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
3. Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.

Вот пример для наглядности.

1

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

Если надо отображать текстовое значение в ячейке, необходимо его брать в кавычки.

Вот еще одна ситуация. Для получения права сдавать экзаменационную сессию учащимися должна быть пройдена зачетная. Студентам получилось одержать зачет по всем предметам, и теперь остался последний, который оказался решающим. Наша задача – определить, какие из студентов допущены к экзаменам, а какие – нет.

2

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

Синтаксис функции ЕСЛИ с несколькими условиями

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

Чтобы было более наглядно, вот синтаксис.

В этом случае функция проверит сразу два критерия. Если первое условие оказывается истинным возвращается значение, полученное в результате совершения операции в первом аргументе. Если нет – осуществляется проверка на соответствие второму критерию.

3

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

4

Как видим, здесь было добавлено еще одно условие, но принцип не изменился. Так можно сразу несколько критериев проверять.

С несколькими условиями в математических выражениях

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

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

Цель – вычесть стоимость для любого количества продуктов, введенного в указанную ячейку. Количество – ячейка B8.

Формула для решения данной задачи принимает вид =B8*ЕСЛИ(B8>=101;12;ЕСЛИ(B8>=50;14;ЕСЛИ(B8>=20;16;ЕСЛИ(B8>=11; 18;ЕСЛИ(B8>=1;22;»»))))) или =B8*ЕСЛИ(B8>=101;B6;ЕСЛИ(B8>=50;B5;ЕСЛИ(B8>=20;B4;ЕСЛИ(B8>=11;B3;ЕСЛИ(B8>=1;B2;»»))))).

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

С операторами «и», «или», «не»

Оператор «и» используется для проверки нескольких правдивых или нескольких ложных критериев, «или» – одно условие должно иметь верное или неверное значение, «не» – для убеждения, что данные не соответствуют одному условию.

Синтаксис выглядит так:

Операторы «и», «или» теоретически могут проверить до 255 отдельных критериев, но такое количество сложно создавать, тестировать и изменять, поэтому лучше использовать до 5-ти. А «нет» – только один критерий.

Для проверки ячейки на наличие символов

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

Пустые двойные кавычки в формуле означают «ничего». То есть: если в A2 нет символов, программа выводит текст «пустая», в противном случае будет «не пустая».
Для проверки ячейки ЕСЛИ часто используется в одной формуле c функцией ЕПУСТО (вместо пустых двойных кавычек).

Когда один из аргументов не вписан в формулу

Второй аргумент можно не вводить, когда интересующий критерий не выполняется, но тогда результат будет отображаться некрасиво.Как вариант – можно вставить в ячейку пустое значение в виде двойных кавычек.
И все-таки лучше использовать оба аргумента.

Сложные логические выражения

В аргументе Лог_выражение можно использовать как простые сравнения типа 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)

Несколько условий

Что делать, если нужно учесть 2-3 варианта. Например, 3 условия. Проверяется значение ячейки А5. Если оно равно нулю, возвращает текст «ноль». Если «истина». Когда значение ложь, происходит другая проверка, является ли значение меньше 0, и возвращается «Меньше нуля», ЕСЛИ результат положительный. Иначе — «больше нуля».

Проверяем условие для полного совпадения текста.

Проверку выполнения доставки организуем при помощи обычного оператора сравнения «=».

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

Если же вас интересует именно точное совпадение текстовых значений с учетом регистра, то можно рекомендовать вместо оператора «=» использовать функцию СОВПАД(). Она проверяет идентичность двух текстовых значений с учетом регистра отдельных букв.

Вот как это может выглядеть на примере.

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

ЕСЛИ + СОВПАД

В случае, если нас интересует полное совпадение текста с заданным условием, включая и регистр его символов, то оператор «=» нам не сможет помочь.

Но мы можем использовать функцию СОВПАД (английский аналог — EXACT).

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

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

Как видите, варианты «ВЫПОЛНЕНО» и «выполнено» не засчитываются как правильные. Засчитываются только полные совпадения. Будет полезно, если важно точное написание текста — например, в артикулах товаров.

Использование функции ЕСЛИ с частичным совпадением текста.

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

Первое, что приходит на ум – использовать подстановочные знаки «?» и «*» (вопросительный знак и звездочку). Однако, к сожалению, этот простой способ здесь не проходит.

Примечания

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

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

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

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

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

Примеры

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

Функция если в Excel больше или равно

Рассчитаем скидки. Условия, следующие:

1. До 100 000 — скидка 0 процентов;
2. От 100 001 до 30000 — 3;
3. 300001 до 500 000 — 5;
4. Более 500001 — 7.

В ячейке А1 объем продаж. Формула примет вид:

Функция если в Excel больше но меньше

Используйте ЕСЛИ совместно с функцией И. Если А2 больше В2 но меньше С1 возвратится значение ИСТИНА. Иначе ЛОЖЬ.

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

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

5000;10%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>15000;20%;0)))))» src=»/images/ru-ru/media/b1eeb416-b9d0-4537-9594-cb54c7f86c59.png» loading=»lazy»>

Видите, что не так? Сравните порядок сравнения доходов с предыдущим примером. Как это будет происходить? Правильно, она будет снизу вверх (от 5 000 до 15 000 рублей), а не наоборот. Но почему это так важно? Это очень важно, так как формула не может пройти первую оценку для любого значения стоимостью более 5 000 рублей. Предположим, что вы получили доход в размере 12 500 долларов США— если вы получили 10 %, так как она больше 5 000 рублей, и она остановится на этом. Это может быть чрезвычайно проблемным, так как во многих ситуациях такие типы ошибок остаются незамеченными до тех пор, пока они не оказывают отрицательного влияния. Так что же можно сделать, зная о том, что при сложных вложенных заявлениях ЕСЛИ существуют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВЛОП. С помощью ВLOOKUPсначала нужно создать таблицу для справки:

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

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

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

В этой области в этой области вложена более подробная информация,но это намного проще, чем 12-уровневая сложная вложенная если-выписка! Есть и другие, менее очевидные, преимущества:

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

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

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

Вы знали?

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как задать функции ЕСЛИ через Построитель формул

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

Давайте разберем, как это работает. Допустим, нам снова как и в первом примере нужно проставить скидку на всю женскую обувь в размере 25%.

1. Ставим курсор на нужную ячейку, заходим во вкладку “Формулы”, далее щелкаем “Вставить функцию”.
2. В открывшемся списке Построителя формул выбираем “ЕСЛИ” и нажимаем “Вставить функцию”.
3. Открывается окно настройки функции.
   В поле “логическое выражение” пишем условие, по которому будет проводиться проверка. В нашем случае это “B2=”женский”.
   В поле “Истина” пишем значение, которое должно отобразиться в ячейке, если условие выполняется.
   В поле “Ложь” – значение при невыполнении условия.
4. После того, как все поля заполнены, жмем “Готово”, чтобы получить результат.

Ошибки

В результате может отобразиться одна из ошибок:

1. #ИМЯ? – опечатка в самой формуле.
2. 0 – не указан истинный или ложный аргумент.

Вместо заключения

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

В программах OpenOffice и LibreOffice также используется ЕСЛИ (IF), а синтаксис и принцип работы функции точно такие же, как описано выше.