Excel сложные вложенные функции

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

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

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

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

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

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

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

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

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

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

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

= ЕСЛИ (С4 > = 65; «Зачтено»)

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

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

= ЕСЛИ (С3 > = 65; «Зачтено»; «Не зачтено»)

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

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

= МЕСЯЦ (СЕГОДНЯ ())

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

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

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

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

= ЕСЛИ (С4 = «»; «Неявка»; ЕСЛИ (С4> = 65; «Зачтено»; «Не зачтено»))

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

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

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

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

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

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

= ЕСЛИ (С5 <64;»Неуд.»)

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

= ЕСЛИ (С5 <64; «Неуд.»; ЕСЛИ (С5 <73; «Удовл.»))

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

= ЕСЛИ (С5 <64; «Неуд.»; ЕСЛИ (С5 <73; «Удовл.»; ЕСЛИ (С5 <90; «Хорошо»)))

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

= ЕСЛИ (С5 <64; «Неуд.»; ЕСЛИ (С5 <73; «Удовл.»; ЕСЛИ (С5 <90; «Хорошо»; «Отлично»)))

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

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

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

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

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

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

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

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

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

Когда вы выбираете выражение в строке формул и нажимаете клавишу F9, Excel решает только выбранную часть. Это крутой способ проверить, что на самом деле делает формула. В приведенном ниже примере я использую окна с подсказками для выбора различных частей формулы. Затем нажимаю 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»; «Зачтено»; «Не зачтено»)

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

= ЕСЛИ (А1 = 100; «Зачтено»; «Не зачтено»)

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

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

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

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

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

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

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

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

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

= ЕСЛИ (В3 = «красный»; ЕСЛИ (С3 = «маленький»; «х»; «»); «»)

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

= ЕСЛИ (И (В3 = «красный»; С3 = «маленький»); «х»; «»)

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

= ЕСЛИ (И (ИЛИ (В3= «красный»; В3= «синий»);С3= «маленький»);»х»; «»)

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

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

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

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

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

= ВПР (F2; В3:C7; 2; 0)

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

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

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

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

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

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

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

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

= ЕСЛИМН (C5 <64; «Неуд.»; C5 <73; «Удовл.»; C5 <90; «Хорошо»; C5> = 90; «Отлично»)

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

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

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

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

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

= МАКС (расчет; 0)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= (F2 = «красный») * 100 + (F2 = «синий») * 200 + (F2 = «зеленый») * 300+ (F2 = «оранжевый») * 400+ (F2 = «фиолетовый») * 500

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

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

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

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

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

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

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

Создание сложных формул с помощью встроенных логических функций Excel

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

Ниже представлен список операторов сравнения Excel:

Больше или равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

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

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

Рассчитать стоимость товара с учетом того, что если количество превысило 5 шт., то магазин дает скидку 10%. Исходные данные приведены на рис. 26.

Для того чтобы рассчитать стоимость товара с учетом скидки необходимо:

Переместить курсор в ячейку D2. С помощью мастера функций выбрать из категории Логические функцию ЕСЛИ (рис. 27), а затем щелкнуть на кнопке ОК.

Диалоговое окно Аргументы функции (рис. 28) содержит три поля ввода. В поле Лог_выражение необходимо ввести условие, которое определяет, превышает ли проданное кол-во товара 5 шт., следовательно введем в это поле С2>5. В поле Значение_если_истина необходимо ввести формулу, которая вычисляет стоимость товара с учетом скидки, тогда введем в это поле B2*C2-B2*C2*0,1. В поле Значение_если_ложь необходимо ввести формулу, которая вычисляет стоимость товара без учета скидки (условие С2>5 — Ложь), в тогда введем в это поле B2*C2 (см. рис. 29). Теперь щелкнем на кнопке OK.

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

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

На рис. 31 приведена таблица с данными. Необходимо:

Определить, на какую сумму продано товаров каждого вида.

Определить, на какую сумму продано товаров каждого вида с учетом скидки (сумма с учетом скидки = сумма — сумма * скидка). Скидка начисляется по следующему принципу: если продано товара на сумму более 2500 грн., то скидка составит 5%, если продано товара на сумму менее 1100 грн., то скидка составит 0%, в остальных случаях скидка составит 2%.

Для того чтобы выполнить первый пункт задания необходимо в ячейку D2 ввести формулу =C2*B2.

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

Переместим курсор в ячейку E2 и с помощью мастера функций введем следующую формулу (рис. 33 – 34). Результаты решения приведены на рис. 35.

Рис. 35

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

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

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

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

Предположим, что нужно вывести сообщение «Светофор», если содержимое ячейки B4 или «красный», или «зеленый», или «желтый». Если же она содержит любую другую информацию, то необходимо вывести сообщение «Это не светофор. ».

Предположим, что нужно вывести на экран содержимое ячейки B4, если она содержит число строго между 1 и 100 (1 =1; B4 =4.5.

Тогда в ячейку С15 необходимо ввести формулу: =СЧЁТЕСЛИ(B11:B13;»>=4,5″). Результат приведен на рис. 40

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

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

критерий — критерий (условие) в форме числа, выражения или текста, определяющего суммируемые ячейки.

диапазон_суммирования — фактические ячейки для суммирования.

Функция СУММЕСЛИ работает следующим образом: ячейки из «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют условию (критерию). В тех случаях, когда диапазон вычисляемых ячеек (где проверяется условие) и диапазон фактических ячеек для суммирования совпадают, аргумент «диапазон_суммирования» можно не указывать.

В таблице на рис. 41 приведены объемы партий товара, полученные магазином. Необходимо просуммировать только объемы тех партий, значения которых превышают 20. Решение на рис. 42

Логические функции в excel с примерами их использования

Логические функции в Excel проверяют данные и возвращают результат «ИСТИНА», если условие выполняется, и «ЛОЖЬ», если нет.

Рассмотрим синтаксис логических функций и примеры применения их в процессе работы с программой Excel.

Использование логических функций в Excel

Логические функции в Excel и примеры решения задач

Задача 1. Необходимо переоценить товарные остатки. Если продукт хранится на складе дольше 8 месяцев, уменьшить его цену в 2 раза.

Сформируем таблицу с исходными параметрами:

Чтобы решить поставленную задачу, воспользуемся логической функцией ЕСЛИ. Формула будет выглядеть так: =ЕСЛИ(C2>=8;B2/2;B2).

Логическое выражение «С2>=8» построено с помощью операторов отношения «>» и «=». Результат его вычисления – логическая величина «ИСТИНА» или «ЛОЖЬ». В первом случае функция возвращает значение «В2/2». Во втором – «В2».

Усложним задачу – задействуем логическую функцию И. Теперь условие такое: если товар хранится дольше 8 месяцев, то его стоимость уменьшается в 2 раза. Если дольше 5 месяцев, но меньше 8 – в 1,5 раза.

Формула приобретает следующий вид: =8);B2/2;ЕСЛИ(И(C2>=5);B2/1,5;B2))’ class=’formula’>.

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

Задача 2. Если стоимость товара на складе после уценки стала меньше 300 р. или продукт хранится дольше 10 месяцев, его списывают.

Для решения используем логические функции ЕСЛИ и ИЛИ: =10);»списан»;»»)’ class=’formula’>. Условие, записанное с помощью логической операции ИЛИ, расшифровывается так: товар списывается, если число в ячейке D2 = 10.

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

В качестве аргументов можно использовать другие функции. К примеру, математические.

Задача 3. Ученики перед поступлением в гимназию сдают математику, русский и английский языки. Проходной балл – 12. По математике для поступления нужно получить не менее 4 баллов. Составить отчет о поступлении.

Составим таблицу с исходными данными:

Нужно общее количество баллов сравнить с проходным баллом. И проверить, чтобы по математике оценка была не ниже «4». В графе «Результат» поставить «принят» или «нет».

Введем формулу вида: =4;СУММ(B3:D3)>=$B$1);»принят»;»нет»)’ class=’formula’>. Логический оператор «И» заставляет функцию проверять истинность двух условий. Математическая функция «СУММ» используется для подсчета итогового балла.

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

Статистические и логические функции в Excel

Задача 1. Проанализировать стоимость товарных остатков после уценки. Если цена продукта после переоценки ниже средних значений, то списать со склада этот продукт.

Работаем с таблицей из предыдущего раздела:

Для решения задачи используем формулу вида: . В логическом выражении «D2 . Первый аргумент – $B$2:$B$7 – диапазон ячеек для проверки. Второй аргумент – В9 – условие. Третий аргумент – $C$2:$C$7 – диапазон усреднения; числовые значения, которые берутся для расчета среднего арифметического.

Функция СРЗНАЧЕСЛИ сопоставляет значение ячейки В9 (№1) со значениями в диапазоне В2:В7 (номера магазинов в таблице продаж). Для совпадающих данных считает среднее арифметическое, используя числа из диапазона С2:С7.

Задача 3. Найти средние продажи в магазине №1 г. Москва.

Видоизменим таблицу из предыдущего примера:

Нужно выполнить два условия – воспользуемся функцией вида: .

Функция СРЗНАЧЕСЛИМН позволяет применять более одного условия. Первый аргумент – $D$2:$D$7 – диапазон усреднения (откуда берутся цифры для нахождения среднего арифметического). Второй аргумент – $B$2:$B$7 – диапазон для проверки первого условия.

Третий аргумент – В9 – первое условие. Четвертый и пятый аргумент – диапазон для проверки и второе условие, соответственно.

Функция учитывает только те значения, которые соответствуют всем заданным условиям.

БЛОГ

Только качественные посты

Практический справочник функций Microsoft Excel с примерами их использования

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

Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

Могу с уверенностью, опираясь на свой 17 летний профессиональный опыт работы в Excel, сказать, что достаточно освоить всего около 100 функций…

Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

– изучив данные Excel функции, у Вас будет достаточно теоретических знаний, чтобы решать практически любую задачу в Excel

( Для перехода к примерам нажмите на название функции. Все примеры — это ссылки на лучшие статьи уважаемых специалистов по Excel и наших партнеров)

1. СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN) — [Базовые формулы Excel]
2. ВПР (VLOOKUP) — [Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце]
3. ИНДЕКС (INDEX) — [По индексу получает значение из ссылки или массива]
4. ПОИСКПОЗ (MATCH) — [Ищет значения в ссылке или массиве]
5. СУММПРОИЗВ (SUMPRODUCT) — [Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)]
6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS) — [Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров]
7. ЕСЛИ (IF) — [Выполняет проверку условия]
8. И / ИЛИ / НЕ (AND / OR / NOT) — [Логические условия, как правило для функции ЕСЛИ]
9. ЕСЛИОШИБКА (IFERROR) — [Если формула возвращает ошибку то что]
10. СУММЕСЛИМН (SUMIFS) — [Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия]
11. СРЗНАЧЕСЛИМН (AVERAGEIFS) — [Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям]
12. СЧЁТЕСЛИМН (COUNTIFS) — [Подсчитывает количество ячеек, которые соответствуют нескольким условиям]
13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS) — [Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям]
14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL) — [Возвращает k-ое наибольшее/наименьшее значение в множестве данных]
15. ДВССЫЛ (INDIRECT) — [Определяет ссылку, заданную текстовым значением]
16. ВЫБОР (CHOOSE) — [Выбирает значение из списка значений по индексу]
17. ПРОСМОТР (LOOKUP) — [Ищет значения в массиве]
18. СМЕЩ (OFFSET) — [Определяет смещение ссылки относительно заданной ссылки]
19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN) — [Возвращает номер строки/столбца, на который указывает ссылка]
20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS) — [Возвращает количество столбцов/строк в ссылке]
21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP) — [Округляет число до указанного количества десятичных разрядов]
22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK) — [Возвращает случайное число]
23. Ч (N) — [Возвращает значение, преобразованное в число]
24. ЧАСТОТА (FREQUENCY) — [Находит распределение частот в виде вертикального массива]
25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &) — [Объединения двух или нескольких текстовых строк в одну]
26. ПСТР (MID) — [Выдает определенное число знаков из строки текста, начиная с указанной позиции]
27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT) — [Возвращает заданное количество символов текстовой строки слева / права]
28. ДЛСТР (LEN) — [Определяет количество знаков в текстовой строке]
29. НАЙТИ / ПОИСК (FIND / SEARCH) — [Поиск текста в ячейке с учетом / без учета регистр]
30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE) — [Заменяет в текстовой строке старый текст новым]
31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER) — [Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную]
32. ГИПЕРССЫЛКА (HYPERLINK) — [Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете]
33. СЖПРОБЕЛЫ (TRIM) — [Удаляет из текста все пробелы, за исключением одиночных пробелов между словами]
34. ПЕЧСИМВ (CLEAN) — [Удаляет все непечатаемые знаки из текста]
35. СОВПАД (EXACT) — [Проверяет идентичность двух текстов]
36. СИМВОЛ / ПОВТОР (CHAR / REPT) — [Возвращает знак с заданным кодом/Повторяет текст заданное число раз]
37. СЕГОДНЯ / ТДАТА (TODAY / NOW) — [Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате]
38. МЕСЯЦ / ГОД (MONTH / YEAR) — [Вычисляет год / месяц от заданной даты]
39. НОМНЕДЕЛИ (WEEKNUM) — [Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата]
40. ДАТАЗНАЧ (DATEVALUE) — [Преобразует дату из текстового формата в числовой]
41. РАЗНДАТ (DATEDIF) — [Вычисляет количество дней, месяцев или лет между двумя датами]
42. РАБДЕНЬ (WORKDAY) — [Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней]
43. ЯЧЕЙКА (CELL) — [Возвращает сведения о формате, расположении или содержимом ячейки]
44. ТРАНСП (TRANSPOSE) — [Выдает транспонированный массив]
45. ПРЕОБР (CONVERT) — [Преобразует число из одной системы мер в другую]
46. ПРЕДСКАЗ (FORECAST) — [Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом]
47. ТИП.ОШИБКИ (ERROR.TYPE) — [Возвращает числовой код, соответствующий типу ошибки]
48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) — [Возвращает данные, хранящиеся в сводной таблице]
49. БДСУММ (DSUM) — [Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям]
50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel.

После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

• Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.
• Power Pivot — это технология моделирования данных, которая позволяет создавать аналитические модели данных, устанавливать отношения и добавлять аналитические вычисления.
• Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие визуализировать различную информацию.

Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

6 малоизвестных, но очень полезных функций Excel

Microsoft Excel на сегодняшний день просто незаменим, особенно когда дело касается обработки больших объемов данных. Однако у этой программы столько функций, что непросто разобраться, какие их них действительно нужные и полезные.

И поэтому сегодня AdMe.ru расскажет, какими способами можно эффективно систематизировать информацию и разложить все по полочкам.

Сводные таблицы

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

Как применять:

1. Выберите Вставка > Рекомендуемые сводные таблицы.
2. В диалоговом окне Рекомендуемые сводные таблицы щелкните любой макет сводной таблицы, чтобы увидеть его в режиме предварительного просмотра, а затем выберите тот из них, в котором данные отображаются нужным вам образом. Нажмите кнопку ОК.
3. Excel добавит сводную таблицу на новый лист и отобразит список полей, с помощью которого можно упорядочить данные в таблице.

Подбор параметра

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

Как применять:

1. Выберите Данные > Работа с данными > Анализ «что если» > Подбор параметра.
2. В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула.
3. В поле Значение введите нужный результат формулы.
4. В поле Изменяя значениеячейки введите ссылку на ячейку, в которой находится корректируемое значение, и нажмите кнопку ОК.

Условное форматирование

Условное форматирование позволяет быстро выделить на листе важные сведения.

Как применять:

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите формулу, которая вам понадобится.

Например, если вам нужно выделить все значения меньше 100, выберите Правила выделения ячеек > Меньше, а затем наберите 100. Перед тем как нажать ОК, можно выбрать формат, который будет применяться для подходящих значений.

ИНДЕКС и ПОИСКПОЗ

Если ВПР помогает находить нужные данные только в первом столбце, то, благодаря функциям ИНДЕКС и ПОИСКПОЗ, можно искать информацию внутри таблицы.

Как применять:

1. Убедитесь, что ячейки с данными образуют сетку, где есть заголовки и названия строк.
2. Используйте функцию ПОИСКПОЗ: сначала, чтобы найти столбец, в котором расположен искомый элемент, и затем еще раз, чтобы перейти к строке с ответом.
3. Вставьте ответы в ИНДЕКС, и Excel сможет указать на ячейку, где эти значения пересекаются.

Например: ИНДЕКС (array, ПОИСКПОЗ (lookup_value, lookup_array, 0), ПОИСКПОЗ (lookup_value, lookup_array, 0)).

Диаграмма «водопад»

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

Как применять:

В версии Excel 2016 необходимо выделить нужные данные и выбрать Вставка > Водопад или Диаграмма > Водопад.

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

Использование логических функций в Excel

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

Основные логические функции, используемые в Эксель

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

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

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

Операторы ИСТИНА и ЛОЖЬ

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

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

Операторы И и ИЛИ

Синтаксис оператора И выглядит следующим образом:

=И(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

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

Синтаксис оператора ИЛИ:

=ИЛИ(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

В отличие от И, функция ИЛИ будет возвращать значение ИСТИНА даже в случае, когда хотя бы один из используемых аргументов ему соответствует, а все остальные – ложные.

Оператор НЕ

Синтаксис функции НЕ: =НЕ(лог_значение).

Количество аргументов оператора НЕ – всегда один. Соответственно, результат функции (ИСТИНА/ЛОЖЬ) полностью зависит только от значения аргумента.

Операторы ЕСЛИ и ЕСЛИОШИБКА

Общий синтаксис функции ЕСЛИ представляет собой конструкцию:

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

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

Синтаксис оператора ЕСЛИОШИБКА имеет вид:

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

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

Операторы ЕОШИБКА и ЕПУСТО

Оператор ЕОШИБКА имеет следующую структуру:

=ЕОШИБКА(значение)

Он позволяет осуществить проверку корректности уже заполненных ячеек (одной или диапазона), и, если ячейка некорректно заполнена, возвращает результат ИСТИНА, в противном случае – ЛОЖЬ.

Примеры значений в некорректно заполненных ячейках:

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

Формула функции ЕПУСТО выглядит следующим образом:

=ЕПУСТО(значение)

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

Практический пример использования логических функций

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

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

Нам необходимо произвести расчет премии. Ключевые условия, от которых зависит размер премии:

• величина обычной премии, которую получат все сотрудники без исключения – 3 000 руб.;
• сотрудницам женского пола положена повышенная премия – 7 000 руб.;
• молодым сотрудникам (младше 1984 г. рождения) положена повышенная премия – 7 000 руб.;

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

1. Встаем в первую ячейку столбца, в которой хотим посчитать размеры премий и щелкаем кнопку “Вставить функцию” (слева от сроки формул).
2. В открывшемся Мастере функций выбираем категорию “Логические”, затем в предложенном перечне операторов кликаем по строке “ЕСЛИ” и жмем OK.
3. Теперь нам нужно задать аргументы функции. Так как у нас не одно, а два условия получения повышенной премии, причем нужно, чтобы выполнялось хотя бы одно из них, чтобы задать логическое выражение, воспользуемся функцией ИЛИ. Находясь в поле для ввода значения аргумента “Лог_выражение” кликаем в основной рабочей области книги на небольшую стрелку вниз, расположенную в левой верхней части окна программы, где обычно отображается адрес ячейки. В открывшемся списке функций выбираем оператор ИЛИ, если он представлен в перечне (или можно кликнуть на пункт “Другие функции” и выбрать его в новом окне Мастера функций, как мы изначально сделали для выбора оператора ЕСЛИ).
4. Мы переключимся в окно аргументов функци ИЛИ. Здесь задаем наши условия получения премии в 7000 руб.:
   • год рождения позже 1984 года;
   • пол – женский;
5. Теперь обращаем внимание на строку формул. Кликаем в ней на название первоначального оператора ЕСЛИ, чтобы переключиться в аргументы этой функции.
6. Заполняем аргументы функции и щелкаем OK:
   • в значении “Истина” пишем цифру 7000;
   • в значении “Ложь” указываем цифру 3000;
7. Результат работы логических операторов отобразится в первой ячейке столбца, которую мы выбрали. Как мы можем видеть, окончательный вид формулы выглядит следующим образом:
   =ЕСЛИ(ИЛИ(C2>1984;D2=»жен.»);7000;3000) .
   Кстати, вместо использования Мастера функций можно было вручную составить и прописать данную формулу в требуемой ячейке.
8. Чтобы рассчитать премию для всех сотрудников, воспользуемся Маркером заполнения. Наведем курсор на правый нижний угол ячейки с формулой. После того, как курсор примет форму черного крестика (это и есть Маркер заполнения), зажимаем левую кнопку мыши и протягиваем выделение вниз, до последней ячейки столбца.
9. Все готово. Благодаря логическим операторам мы получили заполненные данные для столбца с премиями.

Заключение

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

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

Аргументы функции ЕСЛИ и принцип действия в Excel

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

Теперь разберем её аргументы более подробно:

1. Логическое выражение – определённые данные, которые мы должны проверить на соответствие с некоторыми имеющимися у нас условиями.
2. Значение_если_истина – результат проверки, который мы увидим, когда логическое выражение будет справедливо.
3. Значение_если_ложь – результат при несоответствии условия и наших данных, которые мы проверяем.

Схематически это выглядит следующим образом:

Теперь рассмотрим, как нам определить результат проверки с помощью функции ЕСЛИ. Для начала определяем ЧТО нам надо проверять – число 50. Затем указываем условие – «больше 40», «меньше 40», после чего пишем результат проверки – «ИСТИНА», при условии, что 50 действительно больше 40 и «ЛОЖЬ», когда 50 меньше 40. После того как мы определили, как будут выглядеть наши аргументы, начинаем собирать по кусочкам нашу формулу:

Функция проверила выражение «50 больше 40» и определила, что результат — ИСТИНА (ячейка Е4).

Следующее условие проверяем точно также просто заменив оператор «больше» на «меньше»:

Поскольку 40 больше 50, проверка определила, что наше выражение – ЛОЖЬ.

Теперь рассмотрим более наглядные примеры применения функции ЕСЛИ. У нас есть список студентов и оценок в баллах, полученных за экзамен. Нам нужно найти студентов отличников, которые получили оценку больше или равно 90. Начинаем составлять функцию, ячейка F17. Значение в ячейке E17 должно быть больше или равно (>=) 90, тогда мы получим результат «ИСТИНА». Когда значение в ячейке Е17 будет меньше 90, возвратится значение «ЛОЖЬ»:

И копируем значение ячейки до конца столбца, таким образом мы нашли студентов, которые получили результат больше или равно 90 баллов:

Но такие значения таблицы не позволяют пользователю корректно прочитать информацию, которую мы хотели донести. Тогда нам нужно заменить слова ИСТИНА и ЛОЖЬ на более принятые и понятные. Тут нам пригодится таблица соответствий оценок по шкале ECTS оценкам в баллах B10:C13. Пусть при истинном результате у нас будет стоять оценка А, которая соответствует баллам от 90 до 100, а при ложном – ячейки будут оставаться пустыми, после чего копируем первую ячейку до конца столбца и наша таблица станет более информативной:

Формула функции ЕСЛИ и ВПР

Теперь рассмотрим пример использования вложенных формул и ситуацию, где они могут пригодиться. В предыдущем примере мы определили студентов отличников, но у нас остались ещё незаполненные поля. Нам нужно также определить какую оценку в буквах получат и другие студенты. Указываем, что студенты с баллом больше или равно 90 получат оценку А: =ЕСЛИ(E31>=90; «A»; Затем на месте аргумента «значение_если_ложь» вставляем формулу ЕСЛИ(Е31>=80; «В»; и на месте аргумента значение_если_ложь этой же формулы вставляем ещё одну формулу ЕСЛИ(Е31>=60; «С»; и на месте третьего аргумента уже этой функции пишем последнее условие, не забывая добавить скобки: ЕСЛИ(Е31<60)))):

Копируем формулу до конца столбца и таким образом мы построили вложенную функцию. Однако иногда при написании такой функции нужно учитывать один нюанс – она корректно работает, пока данные для сравнения указываться от большего значения к меньшему (1,2,3,4):

Вот что получится, когда мы укажем условия для сравнения наоборот – от меньшего к большему:

Логика в том, что функция во время проверки первой ячейки определила, что значение больше 60 и выдала соответствующий результат – «С». Дальше проверка не продолжилась. Есть ситуации, где оценка вместо А будет А+, А, А-, такое разветвление будет по каждой букве и самих буквенных оценок будет больше. Тогда процесс создания вложенной функции будет очень долгим, вложенных формул будет очень много и становиться легко запутаться. В таком случае вместо ЕСЛИ можно использовать ВПР. Для начала видоизменяем нашу меньшую таблицу. Такие изменения обусловлены особенностями работы функции ВПР:

Формула ВПР будет искать приблизительное значение ячейки Е60 в диапазоне В53:С56 во втором столбце и передавать найденные значения в основную таблицу:

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

Теперь рассмотрим примеры, когда наши данные должны соответствовать нескольким условиям. Функция ЕСЛИ вместе с функцией И имеют следующий синтаксис:

Например у нас есть список студентов и данные их оценок по трём предметам. Нам нужно проверить наличие у студента оценки 2 хотя бы по одному предмету и указать, есть ли у студента пересдача:

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

Например у нас есть список студентов и условие, что при наличии хотя бы одной оценки 3, студент не получает стипендию. Мы проверяем, равно ли содержимое ячеек по предметам цифре 3:

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

При условии, что в ячейке Е91 не находится слово «нет» (НЕ(Е91= «нет»)), наш результат – зачёт, в любом инном случае – не зачёт:

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

У нас есть отчет о чистых прибылях нескольких компаний. Мы хотим видеть только тот показатель, который является максимальным. Для этого мы используем вместе с функцией ЕСЛИ функцию МАКС: ЕСЛИ (МАКС(указываем диапазон в котором будем искать значение)=ячейка, которую функция будет пропускать через диапазон; значение если истина (проверяемая ячейка); значение если ложь (ничего не указывать)). В ячейке Е113 прописываем формулу, не забываем про абсолютные ссылки для диапазона D113:E119, иначе он сместится при копировании, копируем формулу до конца столбца:

Разнообразим наши результаты работы и создадим график, который будет так же выделять наше максимальное число: выбираем диапазон D111:E119 – Вставка — Рекомендуемые диаграммы – Выбираем первую диаграмму и ОК. Теперь у нас данные выводятся в табличном и графическом виде:

Теперь максимальное число выделяется не только в таблице, а и на диаграмме. Но сейчас этих величин у нас две – с обеих столбцов. Немного подредактируем нашу диаграмму. На диаграмме выбираем любой из Рядов, открываем меню, клацая правой кнопкой мыши по нём, выбираем Формат ряда данных, указываем Перекрытие рядов на 100% и рассмотрим результат, который у нас получился:

Данные из третьего столбца перекрыли данные из второго и мы получили подсвечивание максимального показателя. Теперь, когда нам нужно будет изменить любое число во втором столбце, наша формула заново определит максимальное число из столбца «Чистая прибыль», покажет его в столбце «Максимальный показатель», а потом мы увидим его среди остальных чисел и на диаграмме автоматически. Например, укажем новое число для автопроизводителя Nissan – 1600. Вот какие изменения произошли:

Скачать пример функции если с несколькими условиями в Excel

Формула в столбце Е изменила свои вычисления и эти изменения отобразились на графике – подсвечивается новое найденное максимальное число. Такие процессы будут происходить при любом изменении показателей в столбце «Чистая прибыль».

Создание
сложных формул с помощью встроенных
логических функций Excel

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

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

Ниже
представлен список операторов сравнения
Excel:

Результатом
логического выражения является логическое
значение ИСТИНА (1) или логическое
значение ЛОЖЬ (0).

Функция ЕСЛИ

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

Синтаксис:

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

Пример:

Рассчитать стоимость
товара с учетом того, что если количество
превысило 5 шт., то магазин дает скидку
10%. Исходные данные приведены на рис.
26.

Рис. 26

Для того чтобы
рассчитать стоимость товара с учетом
скидки необходимо:

1. Переместить курсор
   в ячейку D2.
   С помощью мастера функций выбрать из
   категории Логические функцию ЕСЛИ
   (рис. 27), а затем щелкнуть на кнопке
   ОК.

2. Диалоговое окно
   Аргументы функции (рис. 28) содержит
   три поля ввода. В поле Лог_выражение
   необходимо ввести условие, которое
   определяет, превышает ли проданное
   кол-во товара 5 шт., следовательно введем
   в это поле С2>5.
   В поле Значение_если_истина
   необходимо ввести формулу, которая
   вычисляет стоимость товара с учетом
   скидки, тогда введем в это поле
   B2*C2-B2*C2*0,1. В
   поле Значение_если_ложь
   необходимо ввести формулу, которая
   вычисляет стоимость товара без учета
   скидки (условие С2>5 —
   Ложь),
   в тогда введем в это поле B2*C2
   (см. рис. 29). Теперь щелкнем на
   кнопке OK.

3. Скопировать
   полученную формулу в смежные ячейки.
   Результаты вычислений формул приведены
   на рис. 30.

Рис. 27

Рис. 28

Рис. 29

Рис. 30

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

Пример.

На рис. 31 приведена
таблица с данными. Необходимо:

1. Определить, на
   какую сумму продано товаров каждого
   вида.

2. Определить, на
   какую сумму продано товаров каждого
   вида с учетом скидки (сумма с учетом
   скидки = сумма — сумма * скидка). Скидка
   начисляется по следующему принципу:
   если продано товара на сумму более 2500
   грн., то скидка составит 5%, если продано
   товара на сумму менее 1100 грн., то скидка
   составит 0%, в остальных случаях скидка
   составит 2%.

Рис. 31

Для того чтобы
выполнить первый пункт задания необходимо
в ячейку D2 ввести
формулу =C2*B2.

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

Рис. 32

Переместим курсор
в ячейку E2 и с
помощью мастера функций введем следующую
формулу (рис. 33 – 34). Результаты решения
приведены на рис. 35.

Щелкнуть
тут

Рис. 33

Рис. 34

Рис.
35

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

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

ИЛИ(логическое_значение1;логическое_значение2;
…)

И(логическое_значение1;
логическое_значение2; …)

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

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

Пример

Предположим, что
нужно вывести сообщение «Светофор»,
если содержимое ячейки B4
или
«красный», или «зеленый», или «желтый».
Если же она содержит любую другую
информацию, то необходимо вывести
сообщение «Это не светофор!!!».

ЕСЛИ(ИЛИ(B4=«зеленый»;B4=«красный»;B4=«желтый»);»Светофор»;
«Это не светофор!!!» )

Пример

Предположим, что
нужно вывести на экран содержимое ячейки
B4,
если она содержит число строго между 1
и 100 (1<=B4<=100),
и сообщение «Значение вне интервала»
в противном случае.

ЕСЛИ(И(B4>=1;
B4<=100); B4; «Значение вне интервала»)

Пример

Есть список дат,
необходимо определить, является ли
выходным днем введенная дата. Решения
задачи на рис. 36

Рис. 36

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

1. Переместим курсор
   в ячейку В2,
   щелкнем
   на кнопке вставка функции и в категории
   логические выберем функцию Если.

2. Щелкнем в поле
   Лог_выражение
   функции
   Если,
   а затем
   щелкнем на стрелке вниз в строке формул
   и выберем Другие
   функции (см.
   рис. 37). Теперь в категории Логические
   можно выбрать функцию ИЛИ.

3. Щелкнем в поле
   Логическое_выражение_1
   функции ИЛИ,
   а затем щелкнем на стрелке вниз в строке
   формул и выберем Другие
   функции. В
   категории Дата
   и время
   выберем функцию ДЕНЬНЕД
   и в поле Дата_в_числом_формате
   введем А2,
   а в поле Тип
   введем 2.

4. В строке формул
   щелкнем мышкой внутри слова ИЛИ
   и таким образом, сможем вернуться в
   диалоговое окно функции ИЛИ,
   для того чтобы закончить ввод условия
   в первое поле этой функции (набираем =
   6).

5. Щелкнем в поле
   Логическое_выражение_2
   функции ИЛИ
   и повторим
   действия пункта 3 и 4, чтобы ввести второе
   условие ДЕНЬНЕД(А2;2)=7.

6. В строке формул
   щелкнем мышкой внутри слова ЕСЛИ
   и таким образом, сможем вернуться в
   диалоговое окно функции ЕСЛИ,
   для того чтобы закончить ввод формулы
   (в поле Значение_если_истина
   вводим
   Выходной
   день, в поле
   Значение_если_ложь
   вводим
   пробел).

Рис. 37

Пример

Информация о
результатах первого модульного контроля
и посещаемости студентами занятий
выведена в таблице см. рис. 38. Необходимо
вывести следующую информацию: Если
средний бал студента меньше или равен
3,5 и он пропустил более 49 часов занятий
по неуважительной причине, то необходимо
вызвать родителей в деканат; если средний
бал студента больше или 4,5 и он пропустил
не более 10 часов занятий по неуважительной
причине, то необходимо отправить
родителям письмо благодарность. Решение
задачи представлено на рис. 39.

Рис. 38

Рис. 39

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

Эти функции надо
искать не в категории Логические,
а в категориях Статистические
и Математические,
соответственно (или в полном алфавитном
перечне).

СЧЁТЕСЛИ
— подсчитывает количество ячеек в
диапазоне, удовлетворяющих заданному
условию, а СУММЕСЛИ
— суммирует значения ячеек, удовлетворяющих
заданному условию. Функция СУММЕСЛИ
используется в тех случаях, когда
необходимо суммировать не весь диапазон,
а только ячейки, отвечающие некоторым
условиям (критериям).

Синтаксис:

СЧЁТЕСЛИ
(диапазон; критерий)

где,

диапазон
—
диапазон, в котором нужно подсчитать
ячейки.

критерий
— критерий
(условие) в форме числа, выражения или
текста, который определяет, какие ячейки
надо подсчитывать.

Функция СЧЁТЕСЛИ
работает следующим образом: вычисляется
количество ячеек диапазона, значение
которых удовлетворяют условию
(критерию).

Пример

В задаче, рассмотренной
в примере 10 необходимо определить
количество студентов, у которых средний
балл >=4.5.

Тогда в ячейку С15
необходимо ввести формулу:
=СЧЁТЕСЛИ(B11:B13;»>=4,5″).
Результат
приведен на рис. 40

Рис. 40

Синтаксис:

СУММЕСЛИ
(диапазон; критерий; диапазон_суммирования)

где,

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

критерий
— критерий (условие) в форме числа,
выражения или текста, определяющего
суммируемые ячейки.

диапазон_суммирования
— фактические
ячейки для суммирования.

Функция СУММЕСЛИ
работает следующим образом: ячейки из
«диапазон_суммирования»
суммируются, только если соответствующие
им ячейки в аргументе «диапазон»
удовлетворяют условию
(критерию).
В тех случаях, когда диапазон вычисляемых
ячеек (где проверяется условие) и диапазон
фактических ячеек для суммирования
совпадают, аргумент «диапазон_суммирования»
можно не указывать.

Пример

В таблице на рис.
41 приведены объемы партий товара,
полученные магазином. Необходимо
просуммировать только объемы тех партий,
значения которых превышают 20. Решение
на рис. 42

Рис. 41

Рис.42

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

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

​Смотрите также​ ИСТИНА или ЛОЖЬ,​ использованием сразу нескольких​Хорошо​Обратимся к примеру, приведенному​ больше 10 с​ превышен»})​

• ​Суммирование значений на основе​ без обоих закрывающих​<>​В примере выше функция​

​    (обязательно)​ версия Office.​ столбце D.​ хорошо видно, насколько​ если вам потребуется​ чтобы выстроить последовательность​

​Функция ЕСЛИ позволяет выполнять​ но, в отличие​ функций​при более 45​ на рисунках ниже.​ помощью функции СУММЕСЛИ(),​=ВПР(A1;A3:B6;2)​ одного условия с​ скобок, приложение Excel​) и др.), ознакомьтесь​ ЕСЛИ в ячейке​Значение, которое должно возвращаться,​Видео: расширенное применение функции​=ВПР(B9;B2:C6;2;ИСТИНА)​ сложно бывает работать​ разделить оценки на​ из множества операторов​

​ логические сравнения значений​ от И(), она​ЕСЛИ​

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

​ и оценка​ В данном примере​ записав =СУММЕСЛИ(A6:A11;»>10″). Аналогичный​Для функции ВПР() необходимо​ помощью функции СУММЕСЛИ​ попытается исправить ее.​

​ со статьей Операторы​

​ D2 означает:​

​ если​

• ​ ЕСЛИ​

• ​Эта формула ищет значение​

Примечания

​ Она проверяет условие​ если все ее​ Надеюсь, что эта​в остальных случаях.​ЕСЛИ​ получить с помощью​

• ​A3:B6​ нескольких условий с​ 64 вложенных функций​ приоритеты.​ вернуть текст «Превышение​имеет значение ИСТИНА.​ Excel 2016 и более​ диапазоне B2:B22. Если​ будете делать, если​ Теперь ваши четыре​ по каждому условию​ и в зависимости​ условия ложны. Чтобы​ информация была для​Чтобы решить эту задачу,​в первую очередь​ формулы массива​

• ​таблицу значений:​ помощью функции СУММЕСЛИМН​ ЕСЛИ, но это​Иногда требуется проверить, пуста​ бюджета», в противном​значение_если_ложь​ поздние версии)​ значение найдено, возвращается​ ваша организация решит​ условных оператора ЕСЛИ​

​ на протяжении всей​ от его истинности​ сравнить эти функции​ Вас полезной. Удачи​ введем в ячейку​ проверят условие​=СУММ(ЕСЛИ(A6:A11>10;A6:A11))​

​Если требуется вывести разный​Функция И​ вовсе не означает,​ ли ячейка. Обычно​ случае вернуть текст​    (необязательно)​Функция СЧЁТЕСЛИ (подсчитывает​ соответствующее значение из​

Примеры

​ добавить новые уровни​ нужно переписать с​ цепочки. Если при​ возвращает результат.​ составим, так называемую​ Вам и больших​

​ C3 следующую формулу:​

• ​A1>25​

  ​(для ввода формулы​ текст в случае​Функция ИЛИ​

1. ​ что так и​ это делается, чтобы​ «В пределах бюджета»)​Значение, которое должно возвращаться,​

2. ​ значения с учетом​ той же строки​ компенсаций или изменить​

3. ​ учетом 12 условий!​ вложении вы допустите​=ЕСЛИ(это истинно, то сделать​

4. ​ таблицу истинности для​ успехов в изучении​=ЕСЛИ(B3>60;»Отлично»;ЕСЛИ(B2>45;»Хорошо»;»Плохо»))​

5. ​. Если это так,​ в ячейку вместо​

​ наличия в ячейке​Функция ВПР​ надо делать. Почему?​ формула не выводила​=ЕСЛИ(C2>B2;C2-B2;0)​ если​ одного условия)​ в столбце C.​ имеющиеся суммы или​ Вот так будет​ в формуле малейшую​ это, в противном​ И() и ИЛИ().​ Microsoft Excel!​и нажмем​ то формула возвратит​ENTER​А1​

• ​Полные сведения о формулах​

​Нужно очень крепко подумать,​ результат при отсутствии​На рисунке выше мы​лог_выражение​Функция СЧЁТЕСЛИМН (подсчитывает​Примечание:​ проценты? У вас​ выглядеть ваша формула:​ неточность, она может​ случае сделать что-то​В математических вычислениях EXCEL​Автор: Антон Андронов​Enter​ текстовую строку «больше​нужно нажать​отрицательного значения, положительного​ в Excel​ чтобы выстроить последовательность​ входного значения.​ возвращаем не текст,​имеет значение ЛОЖЬ.​ значения с учетом​ В обеих функциях ВПР​ появится очень много​=ЕСЛИ(B2>97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«; ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))​

​ сработать в 75 %​​ еще)​ интерпретирует значение ЛОЖЬ​Функция И(), английский вариант​.​ 25», в любом​CTRL+SHIFT+ENTER​ значения или 0,​Рекомендации, позволяющие избежать появления​ из множества операторов​В данном случае мы​ а результат математического​=ЕСЛИ(C2=»Да»;1;2)​ нескольких условий)​ в конце формулы​ работы!​Она по-прежнему точна и​ случаев, но вернуть​Поэтому у функции ЕСЛИ​ как 0, а​ AND(), проверяет на​Данная формула обрабатывает сразу​ другом случае —​

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

​)​ то можно записать​ неработающих формул​ ЕСЛИ и обеспечить​

• ​ используем ЕСЛИ вместе​

​ вычисления. Формула в​В примере выше ячейка​Функция СУММЕСЛИ (суммирует​ используется аргумент ИСТИНА,​Совет:​ будет правильно работать,​

​ непредвиденные результаты в​ возможны два результата.​ ИСТИНА как 1.​ истинность условия и​ два условия. Сначала​ «меньше или равно​Теперь подсчитаем количество вхождений​ следующую формулу:​Обнаружение ошибок в формулах​ их правильную отработку​ с функцией ЕПУСТО:​ ячейке E2 означает:​ D2 содержит формулу:​ значения с учетом​ который означает, что​ Чтобы сложные формулы было​ но вы потратите​

​ остальных 25 %. К​​ Первый результат возвращается​ В этом легко​ возвращает ИСТИНА если​ проверяется первое условие:​ 25».​ чисел больше 10​=ПРОСМОТР(A1;{-1E+307;0;1E-307};{«<>0»})​ с помощью функции​ по каждому условию​

​=ЕСЛИ(ЕПУСТО(D2);»Пустая»;»Не пустая»)​ЕСЛИ(значение «Фактические» больше значения​ЕСЛИ(C2 = Да, то​

​ одного условия)​ мы хотим найти​ проще читать, вы​ много времени, чтобы​ сожалению, шансов отыскать​ в случае, если​ убедиться записав формулы​ все условия истинны​B3>60​Функция​ в диапазоне ячеек​или, если требуется вместо​ проверки ошибок​ на протяжении всей​Эта формула означает:​ «Плановые», то вычесть​ вернуть 1, в​Функция СУММЕСЛИМН (суммирует​ близкое совпадение. Иначе​ можете вставить разрывы​ написать ее, а​ эти 25 % немного.​ сравнение истинно, второй —​ =ИСТИНА+0 и =ЛОЖЬ+0​ или ЛОЖЬ если​. Если оно истинно,​ЕСЛИ​A6:A11​ текстовых значений вывести​Логические функции​ цепочки. Если при​ЕСЛИ(ячейка D2 пуста, вернуть​ сумму «Плановые» из​ противном случае вернуть 2)​ значения с учетом​ говоря, будут сопоставляться​ строк в строке​ потом протестировать. Еще​Работа с множественными операторами​ если сравнение ложно.​Следствием этого является возможность​ хотя бы одно​

• ​ то формула возвращает​

​является очень гибкой​=СЧЁТЕСЛИ(A6:A11;»>10″). Аналогичный результат​ формулы, можно использовать​Функции Excel (по алфавиту)​ вложении операторов ЕСЛИ​ текст «Пустая», в​ суммы «Фактические», в​=ЕСЛИ(C2=1;»Да»;»Нет»)​

• ​ нескольких условий)​

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

​ значение «Отлично», а​​ и ее можно​ (2) можно получить​ ссылки на диапазон​Функции Excel (по категориям)​ вы допустите малейшую​ противном случае вернуть​ противном случае ничего​В этом примере ячейка​Функция И​ таблице подстановки, а​ клавиши ALT+ВВОД перед​ состоит в том,​ чрезвычайно трудоемкой, особенно​ и являются неотъемлемой​ =И(A1>100;A2>100) в виде​И(логическое_значение1; [логическое_значение2]; …)​

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

• ​ D2 содержит формулу:​Функция ИЛИ​ также все значения,​

• ​ текстом, который хотите​ что вам придется​ если вы вернетесь​ частью многих моделей​ =(A1>100)*(A2>100)​

• ​логическое_значение​ обрабатываются. Если первое​ ситуациях. Рассмотрим еще​=СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))​=ПРОСМОТР(A24;{-1E+307;0;1E-307};A27:A29) (см. файл примера)​ IF(), используется при​ сработать в 75 %​

Вы знали?

​. Вы также можете​.​ЕСЛИ(C2 = 1, то​Функция ВПР​ попадающие между ними.​ перенести на другую​ вручную вводить баллы​

• ​ к ним через​

​ электронных таблиц. Но​Значение второй формулы​   — любое значение или​

• ​ условие ложно, то​

​ один пример. В​Теперь, когда принцип понятен,​Третий аргумент функции не​ проверке условий. Например,​ случаев, но вернуть​ легко использовать собственную​

​=ЕСЛИ(E7=»Да»;F5*0,0825;0)​​ вернуть текст «Да»,​Общие сведения о​ В этом случае​ строку.​ и эквивалентные буквенные​ какое-то время и​ они же часто​ будет =1 (ИСТИНА),​

См. также:

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

support.office.com

Функция ЕСЛИ

​ пытались сделать вы​ проблем с электронными​ аргумента истинны, т.е.​Например, =И(A1>100;A2>100)​переходит ко второму:​ фирмы:​ и другие формулы​ вернет значение ЛОЖЬ​

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

​Как видите, функцию ЕСЛИ​ появления неработающих формул​ от меньшего к​ с неправильной логикой:​ ошибетесь? А теперь​ или, и того​

​ таблицами. В идеале​ равны 1. Только​Т.е. если в​B2>45​В столбец C нам​ с условиями. Например,​ (если условие не​А1​

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

​ эти 25 % немного.​ функции ЕПУСТО используются​ЕСЛИ(E7 = «Да», то​ можно использовать для​Поиск ошибок в​ большему.​

​Видите, что происходит? Посмотрите​

​ представьте, как вы​

​ хуже, кто-то другой.​

• ​ оператор ЕСЛИ должен​

• ​ произведение 2-х единиц​

Простые примеры функции ЕСЛИ

• ​ячейках​

​ истинно, то формула​ экзамена, который должен​​ среди чисел больше​=ЕСЛИ(A1>100;»Бюджет превышен»)​ либо «Бюджет превышен»​

• ​ ЕСЛИ может оказаться​

​ фактически означает «ничего».​ в ячейке F5​​ и значений. А​Логические функции​ здесь, но очевидно,​ в предыдущем примере.​

​ 64 раза для​ ваш оператор ЕСЛИ​ условий (например, «Женский»/»Мужской»,​ что совпадает с​A1 A2​ возвращает значение «Хорошо»,​ содержать всего два​ 10:​Если в ячейке​ либо «ОК!».​ очень трудоемкой, особенно​=ЕСЛИ(D3=»»;»Пустая»;»Не пустая»)​ и умножить на​ еще с ее​Функции Excel (по​ что она значительно​ А как все​ более сложных условий!​ все разрастается, устремляясь​

​ «Да»/»Нет»/»Возможно»), но иногда​​ определением функции И().​содержатся значения больше​ а если ложно,​ варианта:​=МИН(ЕСЛИ(A6:A11>10;A6:A11))​A1​Функция ЕСЛИ()относится к наиболее​

Начало работы

​ если вы вернетесь​Эта формула означает:​ 8,25 %, в противном​ помощью можно оценивать​ алфавиту)​ проще, чем сложный​ идет в этом?​ Конечно, это возможно.​ в бесконечность, значит​ сценарии настолько сложны,​Эквивалентность функции И() операции​ 100 (т.е. выражение​ то «Плохо».​Сдал​Функция​содержится значение 1,​ часто используемым функциям.​ к ним через​ЕСЛИ(в ячейке D3 ничего​ случае налога с​ ошибки. Вы можете​Функции Excel (по​ 12-уровневый вложенный оператор​

Еще примеры функции ЕСЛИ

• ​ Именно! Сравнение идет​

​ Но неужели вам​ вам пора отложить​ что для их​​ умножения * часто​ A1>100 — ИСТИНА​Скопировав формулу в остальные​или​ЕСЛИ​

• ​ то вышеуказанная формула​

​ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])​ какое-то время и​ нет, вернуть текст​ продажи нет, поэтому​ не только проверять,​​ категориям)​ ЕСЛИ. Есть и​ снизу вверх (от​ хочется потратить столько​ мышь и пересмотреть​ оценки требуется использовать​​ используется в формулах​

• ​и​

​ ячейки таблицы, можно​Не сдал​одна из самых​​ вернет значение ЛОЖЬ.​Лог_выражение​ попробуете разобраться, что​ «Пустая», в противном​ вернуть 0)​ равно ли одно​Функция ЕСЛИ — одна из​ другие, менее очевидные,​

​ 5 000 до 15 000 ₽),​

​ сил без всякой​ свою стратегию.​ вместе больше 3​ с Условием И,​выражение A2>100 -​ увидеть, что на​. Те, кто набрал​ популярных и часто​Т.к. значение ЛОЖЬ эквивалентно​   — любое значение или​ пытались сделать вы​ случае вернуть текст​Рекомендации по использованию констант​ значение другому, возвращая​ самых популярных функций​ преимущества:​ а не наоборот.​ уверенности в отсутствии​Давайте посмотрим, как правильно​ вложенных* функций ЕСЛИ.​ например, для того​ ИСТИНА), то формула​ отлично сдал один​ более 45 баллов​ используемых функций Excel.​ 0, то формулы​ выражение, принимающее значения​ или, и того​ «Не пустая»)​В последнем примере текстовое​ один результат, но​ в Excel. Она​

Операторы вычислений

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

Использование функции ЕСЛИ для проверки ячейки на наличие символов

​ значение «Да» и​ и использовать математические​ позволяет выполнять логические​ открыты и их​ в этом такого?​ будет трудно обнаружить?​

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

• ​ если хотя бы​

​ оценки хорошо и​​ остальные нет.​ с операторами сравнения​или (если в​=ЕСЛИ(A1>=100;»Бюджет превышен»;»ОК!»)​​Множественные операторы ЕСЛИ содержат​ способа использования знаков​ ставка налога с​ операторы и выполнять​ сравнения значений и​ легко увидеть.​ Это важно, потому​Совет:​

• ​ ЕСЛИ и как​

​ формуле.​​ больше 5​ в одной ячейке​ плохо по два​Выделите ячейку, в которую​ и другими логическими​​ ячейке​Т.е. если в​ по несколько открывающих​ «», при котором​ продажи (0,0825) введены​ дополнительные вычисления в​

• ​ ожидаемых результатов. Самая​

  ​Значения в таблицах просто​ что формула не​ Для каждой функции в​ понять, когда пора​​Функция ЕСЛИ, одна из​

Пример вложенных функций ЕСЛИ

​И​ значение​ человека.​ необходимо ввести формулу.​ функциями Excel, можно​A1​ ячейке​ и закрывающих скобок​

• ​ формула не вычисляется,​

​ прямо в формулу.​ зависимости от условий.​ простая функция ЕСЛИ​​ обновлять, и вам​ может пройти первую​ Excel обязательно указываются​ переходить к другим​ логических функций, служит​меньше 10:​Другими словами, формула =И(ИСТИНА;ИСТИНА)​Как видите, вместо второго​​ В нашем случае​ решать достаточно сложные​содержится значение 0)​A1​ (), за которыми​ если зависимая ячейка​ Как правило, литеральные​ Для выполнения нескольких​ означает следующее:​ не потребуется трогать​ оценку для любого​

Небольшое предупреждение

​ открывающая и закрывающая​ средствам из арсенала​ для возвращения разных​=СУММПРОИЗВ((A1:A10>5)*(A1:A10​ вернет ИСТИНА,​ и третьего аргументов​

• ​ это ячейка C3.​ задачи. В этом​=ЕСЛИ(A1;»Бюджет превышен»;»ОК!»)​содержится значение большее​ может быть трудно​ пуста:​ константы (значения, которые​ сравнений можно использовать​ЕСЛИ(это истинно, то сделать​ формулу, если условия​ значения, превышающего 5 000 ₽.​ скобки (). При​ Excel.​ значений в зависимости​Предположим, что необходимо проверить​а формулы =И(ИСТИНА;ЛОЖЬ)​ функции​

• ​Введите в нее выражение:​ уроке мы попробуем​вернут ОК!​ или равное 100,​ уследить по мере​=ЕСЛИ(D3=»»;»»;ВашаФормула())​ время от времени​ несколько вложенных функций​ это, в противном​ изменятся.​

• ​ Скажем, ваш доход​ редактировании Excel попытается​Ниже приведен пример довольно​ от того, соблюдается​ все значения в​ или =И(ЛОЖЬ;ИСТИНА) или​ЕСЛИ​

Распространенные неполадки

См. также

​ =И(ЛОЖЬ;ЛОЖЬ) или =И(ЛОЖЬ;ИСТИНА;ИСТИНА)​можно подставлять новые​

​и нажмите​ на простых примерах,​A1​

​ ОК!, а если​Проблема​ нет, не возвращать​ рекомендуется вводить прямо​

​Примечание:​ еще)​

​ чтобы люди видели​ ЕСЛИ вернет 10 %,​ что куда идет,​

​ ЕСЛИ, предназначенного для​Синтаксис​A6:A9​

​ вернут ЛОЖЬ.​ функции​Enter​

​ а также научимся​находится любое другое​ нет, то Бюджет​

​Возможная причина​

​ ничего, в противном​

​ в формулу, поскольку​

​ Если вы используете текст​Поэтому у функции ЕСЛИ​

​ вашу таблицу ссылок​ потому что это​

​ окрашивая разными цветами​ преобразования тестовых баллов​ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])​

​на превышение некоторого​

​Функция воспринимает от 1​

​ЕСЛИ​

support.office.com

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

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

​ больше 5 000 ₽, и​ части формулы. Например,​

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

​ учащихся в их​

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

​ на этом остановится.​ во время редактирования​ буквенный эквивалент.​
​=ЕСЛИ(A2>B2;»Превышение бюджета»;»ОК»)​
​ 100. Можно, конечно​ условий. Понятно, что​​ число условий, которое​​ в ячейке B3​ЕСЛИ​ Бюджет превышен. Такой​​ могут участвовать формулы,​​значение_если_истина​Если у простой функции​​ найти и изменить.​​ (пример: «Текст»). Единственное​

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

​ в случае, если​ ее на другой​​ Это может быть​​ показанной выше формулы​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»))))))))))))» />​=ЕСЛИ(A2=B2;B4-A4;»»)​ записать формулу​ 1 значение использовать​
​ формула может обработать.​ с числом 45,​

​в одной формуле.​ подход удобен, когда​ например:​

​или​ ЕСЛИ есть только​

​ Гораздо удобнее помещать​

​ исключение — слова ИСТИНА​ сравнение истинно, второй —​​ лист.​​ очень проблематично, поскольку​

​ при перемещении курсора​=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))​Имя аргумента​​=И(A6>100;A7>100;A8>100;A9>100)​​ бессмысленно, для этого​ Таким образом, Вы​ если значение больше​Функция​

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

​=ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10))​значение_если_ложь​ два результата (ИСТИНА​ константы в собственные​ и ЛОЖЬ, которые​

​ если сравнение ложно.​

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

​Теперь есть функция УСЛОВИЯ,​ ошибки такого типа​ за каждую закрывающую​Этот сложный оператор с​Описание​но существует более​
​ есть функция ЕСЛИ().​
​ можете создать нужное​​ 45, то возвращает​​ЕСЛИ​ нулю.​Т.е. если в​

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

​. Чтобы возвращать правильное​ и ЛОЖЬ), то​
​ ячейки, в которых​
​ Excel распознает автоматически.​Если вы ищете информацию​​ которая может заменить​​ часто остаются незамеченными,​
​ скобку «)» тем​
​ вложенными функциями ЕСЛИ​

​лог_выражение​​ компактная формула, правда​​ Чаще всего функцией​ количество вложений. Правда​ строку «Сдал», иначе​имеет всего три​EXCEL содержит также другие​ ячейке​ значение, добавьте текст​

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

​ у вложенных функций​ они будут доступны​Прежде чем написать оператор​ о работе с​ несколько вложенных операторов​ пока не оказывают​ же цветом будет​ следует простой логике:​   ​ которую нужно ввести​ И() на истинность​ есть очевидный недостаток​

​ «Не сдал».​ аргумента:​ функции, которые можно​A1​ двух аргументов или​ ЕСЛИ может быть​ и их можно​

​ ЕСЛИ, подумайте, чего​ несколькими операторами ЕСЛИ,​​ ЕСЛИ. Так, в​​ негативного влияния. Так​

​ окрашиваться соответствующая открывающая​Если тестовых баллов (в​(обязательный)​ как формулу массива (см.​ проверяется 2-5 условий.​ такой конструкции, после​Скопировав формулу в остальные​
​=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)​
​ применять для анализа​содержится значение >100,​​ значение ИСТИНА/ЛОЖЬ.​​ от 3 до​​ будет легко найти​​ вы хотите достичь.​

​ см. статью Усложненные​ нашем первом примере​ что же вам​​ скобка. Это особенно​​ ячейке D2) больше​Условие, которое нужно проверить.​ файл примера):​
​Сама по себе функция​

​ 3-5 вложений формула​ ячейки таблицы, можно​Первый аргумент – это​ данных с использованием​ то суммирование ведется​»#ИМЯ?» в ячейке​ 64 результатов.​ и изменить. В​
​ Какое сравнение вы​

excel2.ru

Функция ЕСЛИ в Excel на простом примере

​ функции ЕСЛИ: как​​ оценок с 4​​ делать теперь, когда​ удобно в сложных​ 89, учащийся получает​значение_если_истина​=И(A6:A9>100)​ И() имеет ограниченное​ станет нечитаемой и​ увидеть, что 2​ условие, благодаря которому​ условий. Например, для​ по столбцу​Как правило, это указывает​=ЕСЛИ(D2=1;»ДА»;ЕСЛИ(D2=2;»Нет»;»Возможно»))​ нашем случае все​ пытаетесь выполнить? Написать​​ работать с вложенными​​ вложенными функциями ЕСЛИ:​

Коротко о синтаксисе

​ вы знаете, какие​​ вложенных формулах, когда​​ оценку A.​   ​

​(для ввода формулы​

​ использование, т.к. она​ громоздкой, и с​ человека из 5​ формула может принимать​ подсчета количества вхождений​B​ на ошибку в​Показанная на рисунке выше​ в порядке, так​ оператор ЕСЛИ не​ формулами и избежать​=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))​ трудности могут ожидать​

​ вы пытаетесь выяснить,​Если тестовых баллов больше​(обязательный)​ в ячейку вместо​ может вернуть только​ ней будет невозможно​ не прошли переаттестацию.​ решения. Условие проверяется​

Пример 1

​ чисел в диапазоне​, а если меньше,​ формуле.​ формула в ячейке​​ как здесь используется​​ сложнее, чем выстроить​ ошибок.​​можно сделать все гораздо​​ вас при использовании​ достаточно ли в​ 79, учащийся получает​Значение, которое должно возвращаться,​ENTER​ значения ИСТИНА или​ работать.​

Пример 2

​Функции​​ в самую первую​​ ячеек используется функция​ то по столбцу​Видео: расширенное применение функции​ E2 означает:​ только одна функция​ в уме логическую​Функция ЕСЛИ, одна из​ проще с помощью​

​ вложенных операторов ЕСЛИ?​ них парных скобок.​ оценку B.​ если​нужно нажать​​ ЛОЖЬ, чаще всего​​В Excel существуют более​​ЕСЛИ​​ очередь и способно​ СЧЁТЕСЛИ(), а для​С​ ЕСЛИ​

1. ​ЕСЛИ(D2 равно 1, то​ ЕСЛИ, а ставка​ цепочку «что должно​ логических функций, служит​
2. ​ одной функции ЕСЛИМН:​​ В большинстве случаев​​Ниже приведен распространенный пример​​Если тестовых баллов больше​​лог_выражение​
3. ​CTRL+SHIFT+ENTER​ ее используют вместе​ благородные инструменты для​можно вкладывать друг​ вернуть всего два​ сложения значений, удовлетворяющих​.​
4. ​Функция ЕСЛИМН (Office 365, Excel 2016​ вернуть текст «Да»,​ налога с продажи​ произойти, если это​ для возвращения разных​

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

​=ЕСЛИМН(D2>89;»A»;D2>79;»B»;D2>69;»C»;D2>59;»D»;ИСТИНА;»F»)​​ вместо сложной формулы​​ расчета комиссионных за​ 69, учащийся получает​имеет значение ИСТИНА.​)​ с функцией ЕСЛИ():​ обработки большого количества​ в друга, если​ значения – ИСТИНА​ определенным условиям, используется​В EXCEL 2007 в​ и более поздние​​ в противном случае​​ будет редко изменяться.​ условие выполнено, и​ значений в зависимости​​Функция ЕСЛИМН — просто находка!​​ с функциями ЕСЛИ​ продажу в зависимости​​ оценку C.​​значение_если_ложь​

1. ​В случае, если границы​=ЕСЛИ(И(A1>100;A2>100);»Бюджет превышен»;»В рамках​ условий, например, функция​​ необходимо расширить варианты​​ или ЛОЖЬ. Если​​ функция СУММЕСЛИ().​​ качестве значений аргументов​

   

2. ​ версии)​ ЕСЛИ(D2 равно 2,​ Даже если она​​ что должно произойти,​​ от того, соблюдается​ Благодаря ей вам​ можно использовать функцию​ от уровней дохода.​Если тестовых баллов больше​   ​ для каждого проверяемого​​ бюджета»)​​ВПР​​ принятия решений в​​ условие истинно, то​Функция ЕСЛИ(), как альтернативный​значение_если_истиназначение_если_ложь​Усложненные функции ЕСЛИ: как​ то вернуть текст​
3. ​ изменится, вы сможете​ если нет?» Всегда​ ли условие.​ больше не нужно​ ВПР. При использовании​=ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))​ 59, учащийся получает​(необязательный)​

​ значения разные, то​Т.е. если в​или​​ Excel. Например, для​​ формула вернет второй​ вариант, может быть​​можно для построения​​ работать с вложенными​ «Нет», в противном​ легко изменить ее​ следите за тем,​Синтаксис​ переживать обо всех​ функции ВПР вам​Эта формула означает: ЕСЛИ(ячейка​ оценку D.​Значение, которое должно возвращаться,​ границы можно ввести​обеих​ПРОСМОТР​

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

​ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])​ этих операторах ЕСЛИ​ для начала нужно​​ C9 больше 15 000,​​В противном случае учащийся​ если​ в соседний столбец​ячейках​.​ переаттестации сотрудников, требуется​​ случае третий.​​ подсчета и сложения​ использовать до 64​ ошибок​ «Возможно»)).​Если вы хотите больше​ выполнялись в логической​Например:​

​ и скобках.​

office-guru.ru

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

​ создать ссылочную таблицу:​ то вернуть 20 %,​ получает оценку F.​лог_выражение​ и организовать попарное​A1 A2​Итак, в этом уроке​ проставить не результат,​

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

​О том, как задавать​

​ значений с использованием​​ вложенных друг в​Обучающие видео: усложненные функции​Обратите внимание на​

​ узнать о различных​
​ последовательности, иначе формула​​=ЕСЛИ(A2>B2;»Превышение бюджета»;»ОК»)​​Примечание:​​=ВПР(C2;C5:D17;2;ИСТИНА)​​ ЕСЛИ(ячейка C9 больше​Этот частный пример относительно​имеет значение ЛОЖЬ.​​ сравнение списков с​​содержатся значения больше​ мы рассмотрели логическую​ а оценку из​ условия в Excel,​ условий. Ниже приведены​ друга функций ЕСЛИ().​

​ ЕСЛИ​ две закрывающие скобки​
​ операторах вычислений, которые​ не будет делать​=ЕСЛИ(A2=B2;B4-A4;»»)​ Эта функция доступна только​

​В этой формуле предлагается​ 12 500, то вернуть​ безопасен, поскольку взаимосвязь​Excel позволяет использовать до​ помощью формулы массива:​ 100, то выводится​ функцию​ ряда: Отлично, Хорошо​ читайте статьи: Как​

Совместное использование с функцией ЕСЛИ()

​ иллюстрирующие примеры.​=ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний​Подсчет значений на основе​ в конце формулы.​ можно использовать в​ то, для чего​Имя аргумента​ при наличии подписки​
​ найти значение ячейки​ 17,5 % и т. д…​

​ между тестовыми баллами​​ 64 вложенных функций​​=И(A18:A21>B18:B21)​​ Бюджет превышен, если​​ЕСЛИ​ и Плохо. Оценка​ задать простое логическое​Пусть данные находятся в​ проект»;»Малый проект «)))​

Сравнение с функцией ИЛИ()

​ одного условия с​ Они нужны для​ формулах («меньше» (​ предназначена. Это особенно​Описание​ на Office 365. Если​ C2 в диапазоне​На первый взгляд все​ и буквенными оценками​ ЕСЛИ, но это​Вместо диапазона с границами​ хотя бы в​

Эквивалентность функции И() операции умножения *

​во всей ее​Отлично​ условие в Excel​ диапазоне​Вышеуказанную формулу можно усложнять​ помощью функции СЧЁТЕСЛИ​ того, чтобы закрыть​

​), «больше» (​ важно при создании​лог_выражение​ у вас есть​
​ C5:C17. Если значение​ очень похоже на​ вряд ли будет​ вовсе не означает,​ можно также использовать​ одной ячейке значение​ красе и примерах,​ставится при количестве​ и Используем логические​

​A6:A11​ и дальше, но​Подсчет значений на основе​ выражения для обоих​>), «равно» (​ сложных (вложенных) операторов​    (обязательно)​ подписка на Office 365,​​ найдено, возвращается соответствующее​​ предыдущий пример с​
​ меняться, так что​

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

​ что так и​ константу массива:​Функция ИЛИ() также может​​ а также разобрали​​ баллов более 60,​ функции Excel для​(см. файл примера)​ есть другие подходы:​
​ нескольких условий с​
​ функций ЕСЛИ, и​=​ ЕСЛИ.​Условие, которое нужно проверить.​ убедитесь, что у​
​ значение из той​
​ оценками, однако на​ дополнительных изменений не​​ надо делать. Почему?​​=И(A18:A21>{9:25:29:39})​​ вернуть только значения​​ простой пример с​

​ оценка​ задания сложных условий.​Подсчитаем сумму значений, которые​=ПРОСМОТР(A1;{0;50;90;100};{«Малый проект»;»Средний проект»;»Крупный проект»;»Бюджет​ помощью функции СЧЁТЕСЛИМН​ если ввести формулу​), «не равно» (​=ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)​
​значение_если_истина​

​ вас установлена последняя​ же строки в​ примере этой формулы​
​ потребуется. Но что​

excel2.ru

​Нужно очень крепко подумать,​

Смотрите видео после статьи

видео может дополнять статью или содержать её полностью

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

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

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

Нужны ли «комплексные» формулы?

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

Частым вопросом является такой: «можно ли вообще создавать в Excel вложенные формулы или так делать нельзя». Давайте рассмотрим это на примере простых вычислений.

Использование вложенных формул в Excel

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

Что касается использования встроенных в Excel формул (функций), то на первый взгляд использование их путём вложения друг в друга невозможно. Такую точку зрения часто обосновывают тем, что при вставке формулы в ячейку программа показывает всплывающее окно, предназначенное для ручного или автоматического (при помощи мышки) выбора диапазонов ячеек, отдельных ячеек или отдельных значений (как числовых, так и текстовых). На скриншоте ниже приведён пример для вставки формулы вычисления среднего значения («СРЗНАЧ»).

[нажмите на картинку для увеличения]

Справка: как сохранять фото с сайтов

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

Пример создания формулы в формуле в Excel

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

Что нужно сделать: вычислим сумму средних значений обоих столбцов.

Если не использовать комплексные вычисления, то нам бы потребовалось целых три ячейки:

• В первой ячейке: вычислить среднее значение для колонки 1 (столбец А) при помощи функции СРЗНАЧ;
• Во второй колонке сделать то же самое, но для колонки 2 (столбец B);
• В третьей ячейке при помощи функции СУММ сложить два промежуточных результата;

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

Чтобы понять, как это сделать, нам необходимо рассмотреть синтаксис используемых «встроенных» формул Excel:

• Функция «СУММ»
  Применяется для сложения двух или более чисел или диапазонов чисел.
  Синтаксис: СУММ(X; Y; Z;)
• Функция «СРЗНАЧ»
  Применяется для вычисления среднего значения двух или более чисел или диапазонов чисел.
  Синтаксис: СРЗНАЧ(X; Y; Z;)

Здесь X, Y, Z — это или конкретные числа, указанные вручную, или адреса ячеек, или диапазон ячеек вида «A1:A10». Обратите внимание, что при указании в качестве параметров диапазонов ячеек, аргумент у функций может быть всего один; при указании отдельных адресов ячеек или чисел — минимум два аргумента.

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

А раз так, то мы можем составить комплексную формулу сразу для вычисления конечного результата. Для этого применим принцип Excel «формула в формуле». Вот что должно получиться для нашего примера с двумя колонками чисел:

• СУММ(СРЗНАЧ(A1:A5); СРЗНАЧ(В1:В5))

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

Комплексные формулы в Excel — это не сложно

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

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

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

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

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

Также Вы можете записаться на онлайн курс по программе Excel или приобрести учебный видеокурс по комплексному изучению данной программы (курс рассматривает в том числе и применение формул).

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