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

Функция ЕСЛИ

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

• Откройте Excel, найдя программу в меню «Пуск» или на рабочем столе.

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

• Добавляем столбец «Бонус» — в нем мы показываем результаты функции ЕСЛИ. Поместите курсор в ячейку G4.

• Щелкните значок функции, расположенный слева от строки ввода оператора над рабочим полем. Кроме того, вы можете вставить формулу в Excel, открыв пункт меню «Формулы» и выбрав «Логический».

• В «Категории» находим «Логика» и в появившемся списке — функция SE. Щелкните «ОК».

• Появится окно, в котором нужно указать аргументы функции, то есть условия.

• Допустим, премия менеджеров составляет 30%. Заполняем аргументы, начиная с логического выражения: записываем туда адрес ячейки и желаемое значение. В нашем случае это выглядит так: Log_expression = D4 = «manager». Затем указываем размер премии (30), если выражение истинно, и 0, если оно ложно. Щелкните «ОК».

• В результате мы видим, что премия для первого сотрудника составляет 0%, так как он не является руководителем. Условие выполнено!

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

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

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

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

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда необходимо проверить несколько истинных условий, используется функция И. Суть заключается в следующем: ЕСЛИ a = 1 И a = 2, ТО значение в ELSE является значением c.

Функция OR проверяет условие 1 или условие 2. Как только хотя бы одно условие выполняется, результат будет истинным. Нижняя строка: ЕСЛИ a = 1 ИЛИ a = 2, ТО значение в ELSE значение c.

Функции И и ИЛИ позволяют проверить до 30 условий.

Примеры

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

1. Сделайте ячейку «A1» активной».
2. Повторите предыдущие шаги, чтобы вызвать окно ввода «Темы».
3. Введите следующий код в поле условия.

C1=””

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

1. В поле истины введите следующее.

«Ячейка C1 пуста”
Этот текст будет отображаться, если это условие выполнено.

1. В поле «если ложь» введите следующее.

«Ячейка C1 не пуста”

Читайте также: Браузер сам открывает страницы с рекламой, как убрать

Этот текст мы увидим в случае ложного выполнения условия.

1. Чтобы ввести нашу формулу, нажмите кнопку «ОК».

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

1. Введите любой текст в ячейку C.

1. После нажатия кнопки Enter вы увидите следующий результат.

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

Дополнительная информация

• В функции ЕСЛИ можно проверить 64 условия одновременно;
• Если один из аргументов функции является массивом, оценивается каждый элемент массива;
• Если вы не укажете условие аргумента FALSE if_value (value_if_false) в функции, например, после аргумента value_if_true (value_if_true) есть только запятая (точка с запятой), функция вернет значение «0», если результат оценки функции ЛОЖЬ).
  В следующем примере формула = ЕСЛИ (A1> 20, «Разрешить») или = ЕСЛИ (A1> 20; «Разрешить»), где значение_если_fe_ (значение_если_false) не указано, но аргумент_значение_если_ истинное значение (значение_если_ истинное значение) по-прежнему является запятой. -отдельно. Функция вернет «0» всякий раз, когда проверяемое условие не соответствует условиям ИСТИНА).
  
  |
• Если вы не укажете условие аргумента ИСТИНА (значение_если_ истинное (значение_если_ истинное)) в функции, например, условие указано только для аргумента значение_если_ ложь (значение_если_ ложь), то формула вернет значение «0», если результат расчет функции ИСТИНА);
  В приведенном ниже примере формула = ЕСЛИ (A1> 20; «Отклонить») или = ЕСЛИ (A1> 20; «Отклонить»), где значение_если_ истинное значение (значение_если_ истинное значение) не указано, формула вернет «0» всякий раз, когда состояние выполнено ИСТИНА).

Как сравнить данные в двух таблицах

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

Чтобы сравнить 2 таблицы в Excel, вы можете использовать оператор COUNTIF. Давайте посмотрим на порядок применения функции.

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

Исходные данные (таблицы, с которыми мы будем работать):

Выберите первую таблицу. Условное форматирование — Создать правило — Используйте формулу для определения форматируемых ячеек:

В строке формул мы пишем: = СЧЁТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы) = 0. Сравниваемый диапазон — вторая таблица.

Чтобы включить диапазон в формулу, просто выберите его первую и последнюю ячейку. «= 0» обозначает команду для поиска точных (не приблизительных) значений.

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

Выберите вторую таблицу. Условное форматирование — Создать правило — Использовать формулу. Применяем тот же оператор (СЧЁТЕСЛИ).

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

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

Обратите внимание на этот пример, где у нас есть два условия: требуется сумма продаж мяса (из столбца C) в южной части (из столбца a).

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

= СУММ (D2: D11; A2: A11; Юг; C2: C11; мясо»)

В результате получается значение 14 719.

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

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

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

Затем вам нужно найти данные, удовлетворяющие двум условиям, и ввести первое условие, указав для функции расположение данных (A2: A11), а также то, что это за условие — «Юг». Обратите внимание на запятые между отдельными аргументами.

Кавычки вокруг слова «Юг» означают, что это текстовые данные.

Наконец, введите аргументы для второго условия: диапазон ячеек (C2: C11), содержащих слово «Мясо», а также само слово (заключенное в кавычки), чтобы Excel мог сопоставить их. Завершите формулу, закрыв закрывающую скобку), затем нажмите Enter. Результат снова составляет 14 719.

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

Взгляните на формулу автозаполнения и изображение списка аргументов в нашем примере диапазон_суммирования: D2: D11, столбец чисел, который вы хотите суммировать диапазон_ критериев1 — A2. A11 — столбец с данными, содержащий условие1 «Юг».

Когда вы вводите формулу, остальные аргументы появятся в автозавершении формулы (здесь не показано); диапазон_критерия2 — это диапазон C2: C11, который является столбцом данных, содержащим условие2 — «Мясо”.

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

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

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

• Выделите мышкой первую ячейку (G4) столбца с бонусами. Щелкните значок Fx слева от строки ввода формулы.

• Появится окно с уже заполненными аргументами функции.

• Мы модифицируем логическое выражение, добавляя туда еще одно условие и комбинируя их с помощью оператора AND (мы берем условия в скобках). В нашем случае это будет: Log_expression = AND (D4 = «manager»; E4 = «South»). Щелкните «ОК».

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

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

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

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

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

Для этого воспользуемся следующей формулой:

= ЕСЛИ (И (B2 = «женский»; C2 = «ход»); 30%; 0)

Нажмите клавишу Enter, чтобы просмотреть результат в ячейке.

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

Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”

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

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

Функция ЕСЛИ с условием «И»

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

Это работает так: если a равно единице, а a равно 2, значение будет c.

Функция ЕСЛИ с условием «ИЛИ»

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

Примечания

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

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

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

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

Давайте посмотрим, как правильно создавать мульти-вложенные операторы IF и как понять, когда пора переходить на другие инструменты из арсенала Excel.

Объединяем несколько условий.

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

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

Пример 1

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

= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)

Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.

Пример 2.

Давайте использовать несколько более сложных условий в функции ЕСЛИ.

Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.

= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)

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

1. Регион — Запад или Юг
2. Число больше 100.

Первый из них реализован так же, как и первый пример: ИЛИ (C2 = «Запад», C2 = «Юг»)

Во-вторых, здесь все очень просто: E2> 100

В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.

В строке 4 ни один из них не был выполнен. А в строке 6,7,8 выполняется только первая, но ее слишком мало. Следовательно, скидка будет нулевая.

Пример 3.

Конечно, эти несколько условий могут быть более сложными. Ведь логические функции можно «вкладывать» друг в друга.

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

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

1. Регион — Запад или Юг и число больше 100 (рассмотрено в примере 2)
2. В названии шоколада присутствует слово «черный».

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

= ЕСЛИ (И (ЕЧИСЛО (НАЙТИ («Черный»; D2)),
И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

Функция НАЙТИ ищет точное совпадение. Если для нас не важен регистр символов в тексте, то вместо НАЙТИ можно использовать аналогичную функцию ТОЧНЫЙ.

= ЕСЛИ (И (ЕЧИСЛО (МАТЧ («черный»; D2)),
И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

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

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

Например, подумайте, как получить 40% бонус в Excel для всех сотрудников, которые являются бухгалтерами или администраторами. То есть выбор мы будем делать исходя из двух условий:

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

• Изменение аргументов функции. Логическим выражением будет: ИЛИ (D4 = «бухгалтер»; D4 = «менеджер»). В «Value_if_true» пишем 40, а в «Value_if_false» — 0. Нажимаем «ОК».

• Скопируйте формулу, растянув ее на остальные ячейки. Посмотрим на результат: премию в размере 40% получили директор и два бухгалтера.

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

Обратите внимание на «Формулы» в разделе — «Неправильно» (Wrong). Или значение в, а также операторы. Как видите, вместо второй таблицы ниже представлены

вспомогательный персонал, затем из категории сотрудников, которая одновременно представляет собой комбинацию ключей подсчета — я не понимаю, как размер комиссионных за вопросительный знак собственности (o = СУММЕСЛИ (B2: B25; «> 5») количество ячеек в третьем условии «Библиотека функций» — = ЕСЛИ (НЕ (ячейка A2 A6 равна (см пример ниже) функции «ЕСЛИ») и

Число было вставлено вместо ИСТИНА или ЛОЖЬ в первом аргументе

Поскольку значение FALSE равно 0, формулы = IF (0; «Бюджет превышен»; «ОК!») Или (если ячейка A1 содержит значение 0) = IF (A1; «Бюджет превышен»; «ОК! «) вернется ОК!

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

Примечание. Чтобы гарантировать, что логическое значение FALSE равно 0, введите формулу = —A1 = 0. Введите FALSE в A1. Формула вернет ИСТИНА. Обратите внимание, что логическое значение FALSE точно соответствует 0, но не 0, поскольку формула = A1 = 0 вернет FALSE, поэтому логическое значение FALSE не равно 0. Железная логика!

Примечание. Двойное отрицание «-» — это просто математическая операция, которая преобразует логическое выражение в числовое, но не изменяет само значение. Двойное отрицание можно заменить сложением с 0 или возвышением до первой степени: = (A1 + 0) = 0.

Сравнение данных в двух таблицах

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

Функция СЧЁТЕСЛИ используется для сравнения двух таблиц. Давайте рассмотрим подробнее.

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

У нас такая таблица.

 

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

Затем вы должны щелкнуть следующие меню: Условное форматирование — создать правило — использовать формулу для определения форматируемых ячеек.

В виде формулы форматирования запишем функцию = СЧЁТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы) = 0. В качестве поля сравнения используется таблица с характеристиками второго кухонного комбайна.

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

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

 

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

Функция СУММЕСЛИ

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

• Добавьте строку с общей зарплатой продавцов и нажмите на ячейку, в которой будет отображаться результат.

• Щелкните значок Fx, расположенный слева от строки ввода функции. В открывшемся окне ищем нужную формулу через поиск: в соответствующем окне вводим «суммировано», выбираем оператор в списке, нажимаем «ОК».

• Появится окно, в котором нужно ввести аргументы функции.

• Вставим аргументы: первое поле «Диапазон» определяет, какие ячейки проверять. В этом случае должности сотрудников. Щелкните мышью в поле «Диапазон» и укажите там D4: D18. Вы можете сделать это еще проще — просто выделите нужные ячейки.

• В поле «Критерий» введите «продавец». В «Диапазон_суммирования» пишем ячейки с зарплатой сотрудников (вручную или выделив их мышью). Также — «Ок».

• Посмотрим на результат: подсчитана итоговая зарплата всех продавцов.

Совет: Создать диаграмму в Excel быстро и легко — вам просто нужно найти соответствующую кнопку на вкладке «Вставка» в меню.

Формула в Эксель ЕСЛИ, когда несколько условий

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

Синтаксис

= ЕСЛИ (критерий_дат; критерий_значение_если_результат_miths_criteria; значение_если_результат_не_но_соответствие_критерию)

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

Второй аргумент — какой результат появится в ячейке (число или текст), когда значение удовлетворяет указанному условию (да — ИСТИНА).

Третий аргумент — какой результат появится в ячейке (число или текст), если значение не удовлетворяет указанному условию (нет — ЛОЖЬ).

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

В русской версии Excel аргументы необходимо разделять точкой с запятой («;»), в английской версии — через запятую.

Функция СУММЕСЛИМН

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

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

• Щелкните значок функции. В появившемся окне в поле поиска введите «summeslimn», нажмите «Найти» и выберите из списка нужный оператор.

• В открывшемся окне нужно ввести аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Criteria_range1» — ячейки с должностями сотрудников. «Condition1» = «менеджер», как мы суммируем зарплаты менеджеров. Теперь нужно учесть второе условие: взять руководителей южного филиала. В «Range_condition2» введите ячейки с ветвями «Condition2» = «Юг». Все аргументы определены, нажмите «ОК».

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

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

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

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

• Нажмите кнопку «Вставить функцию», расположенную на вкладке «Формулы» в меню. В открывшемся окне в поле «Категория» выберите «Полный алфавитный список». В списке формул найдите СЧЁТЕСЛИ в алфавитном порядке, нажмите «ОК».

• Заполняем аргументы функции: в поле «Интервал» указываем ячейки с позициями, в «Критерии» пишем «продавец». Также — «Ок».

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

Ошибки

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

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

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

Задача 1

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

Изначально вам нужно создать такую ​​таблицу.

Для этого вам нужно использовать функцию ЕСЛИ

В нашем примере эта формула будет выглядеть так:

= ЕСЛИ (C2> = 8; B2 / 2; B2)

Логическое выражение, содержащееся в первом аргументе функции, создается с помощью операторов> и =. Проще говоря, изначально критерий таков: когда значение ячейки больше или равно 8, выполняется формула, указанная во втором аргументе. Терминологически, если первое условие оказывается верным, выполняется второй аргумент. Если ложь — третья.

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

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

= ЕСЛИ (И (C2> = 8), B2 / 2, ЕСЛИ (И (C2> = 5), B2 / 1,5, B2))

Функция SE

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

Задача 2

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

а также
СЕБЯ
… В результате вы получите следующую строку.

= ЕСЛИ (ИЛИ (D2 <300; C2> = 10); «отменено”;””)

Если при написании условия использовался логический оператор ИЛИ, поэтому его нужно расшифровать следующим образом. Если в ячейке C2 стоит число 10 и более, или если в ячейке D2 стоит значение меньше 300, то необходимо вывести «удаленное» значение в соответствующую ячейку».

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

Другие функции разрешены в качестве аргументов. Например, допустимо использование математических формул.

Задача 3

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

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

Наша задача — сравнить сумму всех оценок с положительной оценкой и при этом убедиться, что оценка по математике меньше 4. А в графе с результатом необходимо указать «принято» или «нет».

Нам нужно ввести следующую формулу.

= ЕСЛИ (И (B3> = 4; СУММ (B3: D3)> = $ B $ 1), «принято»; «нет”)

Использование логического оператора И

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

Затем с помощью функции ЕСЛИ вы можете решить множество разных задач, поэтому это одна из самых распространенных.

Задача 4

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

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

Чтобы решить эту проблему, вам нужно использовать эту формулу.

= ЕСЛИ (D2 <среднее (d2:=»» d7);=»»>

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

Задача 5

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

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

Это можно сделать с помощью только одной функции.

= СРЕДНИЙ SE ($ B $ 2: $ B $ 7; B9; $ C $ 2: $ C $ 7)

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

СРЕДНИЙ ЕСЛИ функция

сравнивает значение в ячейке B9 со значениями в диапазоне B2: B7, в котором перечислены номера магазинов. Если данные совпадают, формула вычисляет среднее арифметическое диапазона C2: C7.

Вложенные условия с математическими выражениями.

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

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

Предполагая, что количество находится в B8, формула будет выглядеть так:

= L8 * SE (La8> = 101; 12; SE (La8> = 50; 14; SE (La8> = 20; 16; SE (La8> = 11; 18; SE (La8> = 1; 22; “”)))))

И вот результат:

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

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

= L8 * SE (L8> = 101; L6; SE (L8> = 50; L5; SE (L8> = 20; L4; SE (L8> = 11; L3; SE (L8> = 1; L2; “”)))))

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

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

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

• Нажмите кнопку «Вставить функцию» на вкладке «Формулы». Найдите нужную функцию в алфавитном списке и нажмите «ОК».

• Введите аргументы функции: «Диапазон_условия1» — это ячейки с позициями «Условие1» = «продавец». В «Range_condition2» пишем ячейки с ветвями «Condition2» = «North».

• Соответственно, будет определено количество продавцов в северном филиале.