Логические формулы в excel

Логические функции в Excel. Часть №1

Доброго времени суток уважаемый читатель!

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

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

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

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

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

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

Функция ЕСЛИ возвращает один результат в случаях, если аргумент «логическое выражение» при вычислении получает ответ «ИСТИНА» и совсем другой, если ответ «ЛОЖЬ». Функция может содержать до семи логических условий внутри себя для аргумента «_значение если истина_», а для аргумент «_значение если ложь_» умеет выполнять функции или команды дальнейшего действия (к примеру, использование функции ПЕРЕЙТИ).

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

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

  • Логическое выражение – указывается практически любое выражение или значение, которое в процессе вычисления получает значение «ИСТИНА» или «ЛОЖЬ»;
  • Значение если истина – это значение, которое будет возвращено, если аргумент «Логическое выражение» получит результат «ИСТИНА». В случае, когда этот аргумент не указан в формуле, возвращается результат «ИСТИНА»;
  • Значение если ложь — это значение, которое будет возвращено, если аргумент «Логическое выражение» получит результат «ЛОЖЬ». В случае, когда этот аргумент опущен в формуле, возвращается результат «ЛОЖЬ».

Пример №1:

Рассмотрим усложнённый пример со многими условиями, вложенными в функцию ЕСЛИ. У нас есть персонал, которому в зависимости от стажа работы, производится доплата к ставке заработной платы. Всего будет три доплаты, стаж работы 3 года – 10%, 10 лет – 20% и 20 лет – 30%. Для получения результата и автоматического расчёта доплаты в зависимости от стажа нам нужна формула такого вида:

=ЕСЛИ(C2>=20;»30%»;ЕСЛИ(C2>=10;»20%»;ЕСЛИ(C2>=3;»10%»;»0%»))).

Пример №2:

Рассмотрим еще один сложный пример, в котором мы будем определять, и идентифицировать виды банковских карточек по их номерам. Для первого аргумента используем текстовую функцию ЛЕВСИМВ для захвата первых 4 цифр, которые и будут условием определения. Для карты «Visa Electron» установим идентификатор 1234, для карты «Maestro» — 5678 и все остальные карты будут по умолчанию «MasterCard Standard». Теперь собственно формула, которая поможет нам определить результат будет такова:

=ЕСЛИ( ЛЕВСИМВ (B14;4)= «1234»; «Visa Electron»; ЕСЛИ ( ЛЕВСИМВ (B14;4)=»5678»; «Maestro»; «MasterCard Standard»)).

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

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

=ИСТИНА().

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

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

=ЛОЖЬ().

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

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

= НЕ(_логическое_значение_), где:

  • Логическое значение – это выражение, ссылка на ячейку или значение, которое как результат вычислений дает логическое значение «ИСТИНА» или «ЛОЖЬ». Функция НЕ при получении такого результата меняет его: «ЛОЖЬ» на «ИСТИНА» и «ИСТИНА» на «ЛОЖЬ».

Пример:

Обращаю внимание! Все логические функции в своих выражениях используют знаки сравнения «=», « », « =» или «<>», в результате чего получаются значения «ИСТИНА» и «ЛОЖЬ».

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

Не забудьте поблагодарить автора!

Богатство приносит не большое состояние, а скромные потребности.
Эпиктет (Epictetus)

Источник: topexcel.ru

Создание условных формул

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

Например, функция Если использует указанные ниже аргументы.

Формула, использующая функцию если

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

value_if_true: возвращаемое значение, если условие имеет значение true.

value_if_false: возвращаемое значение, если условие имеет значение false.

Читайте также:  Формула эксель если много условий

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

Что необходимо сделать

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

Для выполнения этой задачи используйте функции и операторы and, orи Not , как показано в следующем примере.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов.

Выделение примера в справке

Нажмите клавиши CTRL+C.

В Excel создайте пустую книгу или лист.

Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Источник: support.office.com

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

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

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

Логические функции Excel – обзор

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

В следующей таблице приведено краткое описание того, что делает каждая логическая функция.

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

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА

=И(A2>=10; B2 =10; B2 =10; B2 =10)

Формула возвращает ЛОЖЬ, если значение в ячейке A1 больше или равно 10; ИСТИНА в противном случае.

В дополнение к четырем логическим функциям, описанным выше, Microsoft Excel предоставляет 3 условные функции: ЕСЛИ, ЕСЛИОШИБКА и ЕСНД.

Логическая функция И в Excel

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

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

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

Логическая функция возвращает ИСТИНА, если A2 содержит «Яблоки», а B2 больше C2, ЛОЖЬ в противном случае.

Логическая функция возвращает ИСТИНА, если B2 больше 50, а B2 равно C2, ЛОЖЬ в противном случае.

=И(A2=”Яблоки”; B2>=120; B2>C2)

Логическая функция возвращает ИСТИНА, если A2 содержит «Яблоки», B2 больше или равно 120, а B2 больше C2, ЛОЖЬ в противном случае.

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

Логическая функция ИЛИ в Excel

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

Синтаксис логической функции Excel ИЛИ очень похож на функцию И:

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

Логическая функция возвращает ИСТИНУ, если A2 содержит «Яблоки» или «Бананы», в противном случае ЛОЖЬ.

Логическая функция возвращает ИСТИНУ, если B2 больше или равен 135 или C2 больше или равно 55, ЛОЖЬ в противном случае.

Логическая функция возвращает ИСТИНУ, если ячейки B2 и/или C2 пустые, ЛОЖЬ в противном случае.

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

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

Логическая функция ИСКЛИЛИ в Excel

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

Синтаксис логической функции ИСКЛИЛИ идентичен синтаксису ИЛИ:

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

=ИСКЛИЛИ(1>0; 2 0; 2>1)

Логическая функция возвращает ЛОЖЬ, потому что оба аргумента ИСТИНА.

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

  • ИСТИНА, если нечетное число аргументов оценивается как ИСТИНА;
  • ЛОЖЬ, если общее число ИСТИННЫХ утверждений четно, или если все операторы ЛОЖЬ.

Представленное ниже изображение наглядно это иллюстрирует:

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

Логическая функция НЕ в Excel

Функция НЕ является одной из простейших логических функций Excel с точки зрения синтаксиса:

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

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

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

Источник: naprimerax.org

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

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

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

Читайте также:  В эксель пропала строка формул сверху

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

Будут рассмотрены следующие функции:

Функция ИСТИНА

Не принимает никаких аргументов и просто возвращает логическое значение «ИСТИНА».

Синтаксис: =ИСТИНА()

Функция ЛОЖЬ

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

Синтаксис: =ЛОЖЬ()

Функция И

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

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

Рассмотрим таблицу истинности данной функции:

И ИСТИНА ЛОЖЬ
ИСТИНА ИСТИНА ЛОЖЬ
ЛОЖЬ ЛОЖЬ ЛОЖЬ

Синтаксис: =И(Логическое_значение1; [Логическое_значение1];…)

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

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

Во втором примере функция никогда не вернет значение ИСТИНА, т.к. условие ее второго аргумента заранее неравно.

Функция ИЛИ

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

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

Таблица истинности функции ИЛИ:

ИЛИ ИСТИНА ЛОЖЬ
ИСТИНА ИСТИНА ИСТИНА
ЛОЖЬ ИСТИНА ЛОЖЬ

Синтаксис: =ИЛИ(Логическое_значение1; [Логическое_значение2];…)

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

Функция НЕ

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

Таблица истинности функции И с применением функции НЕ:

НЕ(И()) ИСТИНА ЛОЖЬ
ИСТИНА ЛОЖЬ ИСТИНА
ЛОЖЬ ИСТИНА ИСТИНА

Таблица истинности функции ИЛИ с применением функции НЕ:

НЕ(ИЛИ()) ИСТИНА ЛОЖЬ
ИСТИНА ЛОЖЬ ЛОЖЬ
ЛОЖЬ ЛОЖЬ ИСТИНА

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

Функция ЕСЛИ

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

Синтаксис:

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

Рассмотрим первый простой пример, чтобы понять, как функция работает.

Умышлено в первый аргумент функции вставить функцию ИСТИНА. В результате проверки, будет возвращен 2 аргумент (значение_если_истина), 3 аргумент будет опущен.

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

Имеются банковские карточки с номерами, начинающимися с первых четырех цифр, которые являются идентификатором вида карты:

Используем нашу функцию для определения типа карты.

Функция, применяемая в данном примере, выглядит так:

=ЕСЛИ( ЛЕВСИМВ(A2;4)=”1111″ ; “Visa” ;ЕСЛИ( ЛЕВСИМВ(A2;4)=”2222″ ; “Master Card” ;”карта не определена”))

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

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

Функция ЕСЛИОШИБКА

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

Функция принимает 2 аргумента, все они являются обязательными.

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

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

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

Источник: office-menu.ru

Запись логических формул в Excel

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

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

Основные операции

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

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

Ведение простейших подсчётов

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

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

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

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

Функция ЕСЛИ состоит из трёх аргументов:

  • Логического (основного) выражения (требование, которое пользователь желает проверить в таблице);
  • значения, отображающегося, если условие будет полностью соблюдаться;
  • значения, если заданное требование не выдерживается.

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

В частности, в ячейку А1 ввести числовой показатель «5», а в ячейку А2 показатель «8». Если пользователю необходимо сравнить два показателя и выдать автоматический итог произведённых действий, составляется выражение, которое, безусловно, начинается со знака «равно»: =ЕСЛИ(А1>А2; «Больше»; «Меньше»). После введения этой записи в любую свободную ячейку, нажатия Enter, в ней сразу будет демонстрироваться ответ. Если пользователь заменит значения в А1 или А2, то ответ также автоматически перестроится.

Читайте также:  Как закрепить число в формуле excel

В тех случаях, когда важно выявить несовпадение двух показателей, введённых в соседние столбцы, первоначально нужно вникнуть, как в Excel правильно записать условие неравно, после чего создать запрос, используя логические функции. Знак «неравно» в Excel обозначается двумя операторами — «<>». Соответственно, нужно записать так: =ЕСЛИ(А1<>А2; «Значения разные»; «Значения одинаковые»).

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

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

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

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

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

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

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

Например, пользователю важно, чтобы А4 приобрела зелёный фон при условии, что сумма значений, введённых в А1 и А2 будет равна 5, а в А3 значение будет равно 8, в противном случае А4 должна закраситься красным цветом.

Если бы нужно было просто задать такую формулу с несколькими требованиями, то она выглядела бы так: =ЕСЛИ(И(СУММ(A1:A2)=5;А3=8);ИСТИНА;ЛОЖЬ). Вводя такую формулу в А4, нажав Enter, мгновенно отобразится либо «истина», если условие выполняется, либо «ложь».

Когда же нужно вывести не текстовый ответ, а закрасить ячейку в нужный цвет, необходимо кликнуть по А4, сделав её активной, после чего зайти в «Формат», перейти на строку «Условное форматирование», в открывшемся окне выбрать из раскрывающегося списка строку «формула», ввести в открывшуюся чистую строку созданную ранее формулу в таком виде: =И(СУММ(A1:A2)=5;А3=8). Теперь следует кликнуть по кнопке «Формат», откроется новое окно, где можно выбирать, как должна измениться А4, если будет выполняться требование. На вкладке «Вид» выбирается цвет заливки ячейки при выполнении требования.

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

Источник: nastroyvse.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Источник: statanaliz.info