Auditportal.ru

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

Как использовать функцию ЕСЛИ с И, ИЛИ и НЕ в Excel?

КАК: Как использовать функции AND, OR и IF в Excel — 2022

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

Функции O R возвращают TRUE в ячейку B2, если одно из этих условий истинно. Чтобы функция AND вернула TRUE в ячейку B3, должны быть верны ВСЕ три условия.

Вложение нескольких функций в Excel

Создание отчета Excel IF

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

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

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

  • Значение в ячейке A2 меньше 50?
  • Значение в A3 не равно 75?
  • Является ли значение в ячейке A4 больше или равно 100?

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

= IF (OR (A2 <50, A3 <> 75, A4> = 100), «Коррекция данных», «Ошибка данных»)

= ЕСЛИ (И (А2 <50, А3 <> 75, А4> = 100), СЕГОДНЯ (), 1000)

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

Во всех формулах в строках с четвертой по седьмую функции И и ИЛИ идентичны своим аналогам во второй и третьей строках, проверяя данные в ячейках A2 — A4 на соответствие требуемым условиям.

Управляя выводом формулы с помощью функции IF, можно изменять второй и третий аргументы функции. Примером такого вывода может быть текст, показанный в строке 4, число, показанное в строке 5, или пустая ячейка.

Из-за того, что значение в ячейке A4 не больше или равно 100, функция AND возвращает FALSE для формулы IF / AND в ячейке B5.

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

И наоборот, формула IF / OR в четвертой строке возвращает текстовую команду по одной из двух причин:

  1. Значение OR дало TRUE — значение в ячейке A3 не равно 75.
  2. Функция IF затем использует этот результат для возврата аргумента value_if_false: Данные верны.

Использование отчета IF в Excel

Следующие шаги описывают, как ввести формулу IF / OR, расположенную в ячейке B4 примера изображения. Те же шаги можно использовать для ввода любой формулы IF в наших конкретных примерах.

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

Для ввода формулы IF / OR в ячейку B4 необходимо выполнить следующие действия:

  1. Щелкните по ячейке B4, чтобы сделать ее активной.
  2. Перейдите на вкладку Формулы на ленте.
  3. Нажмите Логика, чтобы открыть выпадающий список функций.
  4. Щелкните IF в списке, чтобы открыть диалоговое окно функции IF.
  5. Нажмите Logic Test в диалоговом окне.
  6. Введите полную функцию И: ИЛИ (A2 <50, A3 <> 75, A4> = 100) в строке Logical_Test, при необходимости используя ссылку на ячейку.
  7. Нажмите true_if_value в диалоговом окне.
  8. Введите текстКорректные данные (без кавычек).
  9. Нажмите на false_if_assignment в диалоговом окне.
  10. Введите текст Ошибка данных.
  11. Нажмите OK, чтобы закрыть диалоговое окно и вернуться к рабочему листу.
  12. Как уже упоминалось выше, формула должна отображать аргумент value_if_true или Data.
  13. Когда вы щелкаете по ячейке B4, полная функция появляется в строке формул над рабочим листом.
Читайте так же:
Как изменить порядок рядов диаграмм в Excel?

= IF (OR (A2 <50, A3 <> 75, A4> = 100), «Коррекция данных», «Ошибка данных»)

Как использовать функции ноутбука и настроек Cortana

Как использовать функции ноутбука и настроек Cortana

Персонализируйте Cortana, чтобы превратить ее в мощного и личного цифрового помощника, настроив «Настройки» и «Ноутбук».

Как использовать функции ROW и COLUMN

Как использовать функции ROW и COLUMN

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

Функции и функции маршрутизаторов домашней сети

Функции и функции маршрутизаторов домашней сети

Широкополосные маршрутизаторы оснащены функциями для поддержки домашних сетей. Какие функции вы используете в своем домашнем маршрутизаторе?

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

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

Это — операторы сравнения в Excel:

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

Менее или равно

Результат логического выражения — логическое значение TRUE (1) или логическое значение FALSE (0).

Функция ЕСЛИ

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

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

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

Стоимость товара со скидкой может быть рассчитана по:

Переместите курсор в ячейку D2. С помощью мастера функций выберите функцию IF из категории Logic (рис. На следующей странице нажмите кнопку OK.

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

Заполните соседние ячейки полученной формулой. Как видно из рисунка 1, расчеты по формуле показали следующие результаты. 30.

Функции I F могут быть вложенными в качестве аргументов value_if_truth и value_if_false. Такие вложенные функции ЕСЛИ можно использовать для построения более сложных проверок. Рассмотрим на примерах, как использовать вложенную функцию ЕСЛИ.

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

Рассчитайте количество проданных товаров каждого типа.

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

Читайте так же:
Как найти ближайшее или ближайшее значение в Excel?

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

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

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

Рис. 35

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

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

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

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

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

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

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

IF(OR(OR(B4=»зеленый»;B4=»красный»;B4=»желтый») ; «Светофор» ; «Это не светофор. » )

Предположим, вы хотите отобразить содержимое ячейки B4, если она содержит число строго между 1 и 100 (1<=B4<=100), а в противном случае получить сообщение "Value out of range" (Значение вне диапазона).

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

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

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

Переместите курсор на B2, нажмите на кнопку вставки функции и выберите логическую категорию If (Если).

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

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

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

Используя функцию ИЛИ, щелкните поле Логическое_выражение_2 и повторите шаги 3 и 4, чтобы ввести второе условие ДНЕВНИК(A2;2)=7.

Введите Exit Day в поле Value_if_truth, введите пробел в поле Value_if_false. Диалоговое окно вернется к диалоговому окну функции IF.

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

Читайте так же:
Как легко объединить текст на основе критериев в Excel?

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

Невозможно найти эти функции в категории «Логика», а скорее в категориях «Статистика» и «Математика» (или в полном алфавитном списке).

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

СЧЕТ (интервал; критерий)

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

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

Функция CALCULATE работает следующим образом: она подсчитывает количество ячеек области, значения которых удовлетворяют заданному условию (критерию).

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

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

SUMMARY (диапазон; критерий; суммирование_диапазон)

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

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

Summing_range — реальные ячейки для суммирования.

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

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

Как использовать функцию ЕСЛИ в Excel — пошаговая инструкция (2022)

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

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

В данном материале рассматривается функция IF в Excel и приводятся примеры ее применения.

Общая информация

Что делает эта функция, для чего она нужна и каково ее значение?

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

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

Справочный центр Windows описывает работу этой функции одним предложением: если все правильно, сделайте так, иначе — так.

В этом случае функция выдает два результата.

Первый получается при правильном сравнении, второй — при неправильном.

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

  • Начальное условие, чтобы проверить, какой IF используется;
  • Правильное значение — это то, которое будет возвращаться всякий раз, когда логические алгоритмы считают начальное условие истинным.

Существует также третий компонент — ложное значение. Оно всегда возвращается; исходное условие рассматривается логическими алгоритмами как ложное.

Читайте так же:
Как найти и подсчитать повторяющиеся ячейки / значения в одной строке в Excel?

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

Рис. 1: Окно настройки>.

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

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

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

Кроме того, что должно исполниться, что будет, если оно не исполнится.

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

Пример 1

Ниже показан пример с одним условием для функции.

Мы задаем значение A1 и проверяем, что произойдет, если оно будет больше 30, или меньше или равно 30.

Функция сравнивает значение в столбце A1 со значением 30 во время операции.

Вы можете сделать это, выполнив следующие шаги:

  • Введите начальное значение A1 в любую ячейку (здесь A1);
  • Щелкните на ячейке, где должен отображаться результат функции (здесь B1);
  • Дважды щелкните на B1 и введите =E, когда появится курсор;
  • Откроется список доступных функций с именами, начинающимися с E — выберите функцию IF, дважды щелкнув на ней в списке;

  • Ячейка заполнится, а после слова IF откроется скобка — теперь нужно ввести условия;
  • Щелкните левой кнопкой мыши один раз по ячейке A1 — она появится рядом со скобкой;
  • Затем введите текст без пробелов A1>30; «больше 30″; «»меньше или равно30″ ;

  • Закройте скобку и нажмите Enter;
  • В зависимости от начального значения, указанного в A1, результат, отображаемый в ячейке B1, изменится: при значении, равном 30, результат будет «меньше или равно 30», потому что именно это условие было задано;

  • Если в ячейку A1 ввести число 20, результатом будет «меньше или равно 30», так как оно также соответствует условию;
  • Если в ячейку A1 ввести число 40, результатом будет «больше 30» соответственно.

<Рис. 6 Результат 2>

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

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

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

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

Просто согласитесь с ними, нажав OK, и условие примет правильную форму.

Пример 2

Это более сложный пример, который может использоваться на практике.

Другими словами, программа одновременно проверяет два логических параметра.

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

Алгоритм управления состоит в следующем:

  • В первой колонке указаны порядковые номера моделей;
  • Во второй колонке указаны возможные цвета, в которых они выполнены;
Читайте так же:
Как изменить форму поля комментариев в Excel?

  • Поместите курсор в ячейку C1;
  • Введите функцию ЕСЛИ в форме, использованной в предыдущем разделе;
  • Условие должно иметь следующий вид: =Если(A4=»белый»; «1800″;ЕСЛИ(A4=»зеленый»; «1500″; «1800″));
  • Теперь нажмите клавишу Enter и примите предложенные изменения, если в формуле есть ошибки;

Рис. 8 Уведомление об ошибке.

  • Примените используемую формулу ко всем ячейкам в столбце цен, щелкнув по ячейке C1 и распространив ее на всю колонку.

Результат рисунка 9.

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

Сложности

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

Однако разработчикам известно о ряде проблем с программой, которые привели к сбоям в ее работе.

Существует четыре основные неисправности:

  • Если при использовании функции IF в ячейке результата появляется ноль, это ошибка пользователя, поскольку вы не указали исходное истинное значение (когда ноль появляется, когда условия истинны) или ложное значение (когда ноль появляется, когда условия не истинны). Чтобы разрешить возврат истинного значения, вы должны указать значение true/false;
  • Если символы с #IMYA? в ячейке результата, это означает, что в логической формуле, определяющей условие, произошла ошибка. Поэтому программа не может выполнить ни одно из своих условий и проверить, истинны ли они.

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

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

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

Дело в том, что если пользователь допустит небольшую ошибку, то формула, конечно же, будет работать в 75% случаев. Однако еще в 25% случаев он дает неожиданный результат. Даже профессионалу трудно обнаружить ошибку, не говоря уже о том, чтобы найти ее в сложной, многоступенчатой логической формуле.

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

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

В большинстве случаев гораздо проще разбить эту формулу на несколько коротких логических строк.

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

Рисунок 10 Пример 2;

Вывод

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

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

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

alt=»Несколько способов: Как очистить компьютер от вирусов абсолютно бесплатно» /> « Предыдущая запись

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector