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

Общее определение и задачи

«SE» — это стандартная функция Microsoft Excel. В его обязанности входит контроль соблюдения определенного условия. Когда условие выполняется (истина), значение возвращается в ячейку, в которой используется эта функция, а если оно не выполняется (ложь), возвращается другое значение.

Синтаксис этой функции следующий: «ЕСЛИ (логическое выражение; функция, если истина; функция, если ложь)».

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

Теперь рассмотрим конкретные примеры использования формулы с оператором ЕСЛИ».

1. У нас есть таблица выплат. К 8 марта все женщины имеют право на бонус в размере 1000 рублей. В таблице есть столбец с указанием пола сотрудников. Следовательно, мы должны вычислить женщин из предоставленного списка и в соответствующие строки столбца «Бонус до 8 марта» ввести «1000». При этом, если пол не совпадает с женским, значение таких строк должно соответствовать «0». Функция будет выглядеть так: «ЕСЛИ (B6 =« женский »;« 1000 »;« 0 »)». То есть при результате проверки «истина» (если выясняется, что строка данных занята женщиной с параметром «женский») будет выполнено первое условие — «1000», а если «ложь»), то, соответственно, последний равен «0».
2. Мы вводим это выражение в верхнюю ячейку, где должен отображаться результат. Поставьте знак «=» перед выражением «=».



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



4. Итак, мы получили таблицу с одним столбцом, заполненным функцией «ЕСЛИ».

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

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

1. Например, возьмем тот же стол с выплатами премий до 8 марта. Но на этот раз по условиям размер бонуса зависит от категории сотрудника. Женщины со статусом ключевого персонала получают премию в размере 1000 рублей, вспомогательный персонал — только 500 рублей. Конечно, мужчины абсолютно не имеют права на этот вид оплаты, независимо от категории.
2. Первое условие — если наемный работник — мужчина, размер полученного бонуса равен нулю. Если это значение ложно и сотрудник не мужчина (т.е женщина), проверяется второе условие. Если женщина принадлежит к основному персоналу, в ячейке будет отображаться значение «1000», а в противном случае — «500». В виде формулы это будет выглядеть так: «= SE (B6 =« мужской »;« 0 »; SE (C6 =« Главный штаб »;« 1000 »;« 500″))».
3. Вставьте это выражение в верхнюю ячейку столбца «Бонус к 8 марта».



4. Как и в прошлый раз, «растягиваем» формулу вниз.

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

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

1. Например, в нашей ситуации премия до 8 марта в размере 1000 рублей присуждается только женщинам, которые являются основным штатом, а мужчины и представительницы женского пола, числящиеся во вспомогательном составе, ничего не получают. Следовательно, чтобы значение в ячейках столбца «Бонус до 8 марта» было равно 1000, необходимо выполнение двух условий: пол — женщина, категория персонала — основной персонал. Во всех остальных случаях значение в этих ячейках заранее будет равно нулю. Это записывается по следующей формуле: «= SE (AND (B6 =« женский »; C6 =« Главный штаб »);« 1000 »;« 0 »)». Ставим в камеру.



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

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

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

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

Предполагая, что количество находится в 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; «»)))))

Функция ИЛИ (результат изменяется при выполнении одного из нескольких условий)

Добавим в наш пример еще одно условие:
При оплате заказа более 50% скидка также составляет 10%.
Для наглядности вынесем все наши условия в таблицу

Первая функция ЕСЛИ остается без изменений.

Установите курсор на вторую функцию ЕСЛИ и нажмите кнопку «Вставить функцию» (слева от строки формул).

Отображаются аргументы для второго условия ЕСЛИ.
Очистить поле Log_Expression (B2> 15000)

Поместите курсор в пустое поле Log_expression и выберите функцию OR в поле Name.

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

В появившемся окне прописываем 2 наших условия:

Логическое_значение 1: B2> 15000

Логическое_значение 2: C2 * 100 / B2> 50

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

Продолжим формулу для остальных заказов.

Примеры использования функции ЕСЛИ в Excel

Давайте посмотрим на несколько простых примеров использования логической функции ЕСЛИ. Вы можете скачать результаты в виде готового файла Excel (* .xlsx) после статьи и попробовать сделать что-то свое.

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

Сравнение чисел с константой

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

• «Более 30»
• «Меньше или равно 30»

Для столбца из пяти чисел формулы (а их тоже будет 5) будут выглядеть так.

CellNumberFormulaResult

A51	10	= ЕСЛИ (LA5> 30; «больше 30»; «меньше или равно 30″)	«Меньше или равно 30»
A6	ветры	= ЕСЛИ (LA6> 30; «больше 30»; «меньше или равно 30″)	«Меньше или равно 30»
LA7	тридцать	= ЕСЛИ (LA7> 30; «больше 30»; «меньше или равно 30″)	«Меньше или равно 30»
A8	40	= ЕСЛИ (LA8> 30; «больше 30»; «меньше или равно 30″)	«Более 30»
A9	50	= ЕСЛИ (LA9> 30; «больше 30»; «меньше или равно 30″)	«Более 30»

• 1 — это адрес ячейки Excel согласно принятым в программе правилам адресации.

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

Для аргументов 2 и 3 мы использовали здесь текстовые выражения. Не забудьте заключить текст в кавычки, иначе вы получите ошибку в формуле!

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

Напоминание о пустой ячейке

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

Фамилия Должность Комментарий

Иванов	Директор
Петров	Менеджер
Сидоров		Местоположение не указано!

Формула в ячейках столбца «Комментарий» будет иметь вид: = IF (B13 <> «»; «»; «Позиция не указана!»). Очевидно, что адрес ячейки на вашем листе будет другим.

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

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

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

Как построить вложенные функции ЕСЛИ

Используя мастер функций, выберите функцию IF и заполните поля Log_expression и Value_if_true.

Log_expression будет записано B2> 20000
Value_if_true — B2 * 0,85
В пустом поле value_if_false поместите курсор и выберите функцию IF в поле имени (слева от строки формул.

Откроется новое окно, но начало формулы не исчезнет.

В новом окне напишем следующее условие:

Лог_выражение — B2> 15000

Value_if_true — B2 * 0,9

Value_if_false равно B2.

Продолжим формулу для остальных заказов.

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

Для описания условия в функции ЕСЛИ 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 может быть очень большим. Важно лишь строго соблюдать логическую последовательность их выполнения.

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

• = ЕСЛИ (C2 = «Да»; 1; 2)

В приведенном выше примере ячейка D2 содержит формулу: ЕСЛИ (C2 = Да, поэтому возвращается 1, в противном случае возвращается 2)

• = ЕСЛИ (C2 = 1, «Да», «Нет»)

В этом примере ячейка D2 содержит формулу: ЕСЛИ (C2 = 1, поэтому возвращается текст «Да», в противном случае возвращается текст «Нет»). Как видите, функцию ЕСЛИ можно использовать для сравнения текста и значений. Это также помогает вам оценивать ошибки. Вы можете не только проверить, равно ли одно значение другому, возвращая результат, но также можете использовать математические операторы и выполнять дополнительные вычисления на основе условий. Вы можете использовать несколько вложенных функций ЕСЛИ для выполнения нескольких сравнений.

B2; C2-B2;» src=»/images/ru-ru/media/4bbae039-b79d-4998-b8b3-a18950f4350e.png» loading=»lazy» )?=»»>

• = ЕСЛИ (C2> B2; C2-B2,0)

На изображении выше мы возвращаем не текст, а результат математического расчета. Формула в ячейке E2 означает: ЕСЛИ (Фактическое значение больше запланированного, поэтому вычтите запланированное из фактического, иначе ничего не вернет).

• = ЕСЛИ (MI7 = «Да»; F5 * 0,0825,0)

В этом примере формула в ячейке F7 означает: ЕСЛИ (E7 = Да, затем вычислите сумму в ячейке F5 и умножьте на 8,25%, в противном случае налог с продаж отсутствует, поэтому верните 0)

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

Как правильно записать?

Установите курсор в ячейку G2 и введите знак «=». Для Excel это означает, что будет введена формула. Поэтому, как только буква «е» будет нажата дальше, мы получим предложение выбрать функцию, которая начинается с этой буквы. Выбираем «СЭ”.

Кроме того, все наши действия также будут сопровождаться предложениями.

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

Второй аргумент — это значение, которое примет ячейка G2, если условие, которое мы отметили, выполнено. Это будет слово «Местный”.

Далее мы снова указываем значение третьего аргумента через запятую. Это значение будет принято ячейкой G2, если условие не выполнено: «Экспорт». Не забудьте завершить ввод формулы, закрыв круглые скобки и нажав «Enter”.

Наша функция выглядит так:

= ЕСЛИ (C2 = Запад, Местный, Экспорт»)

Наша ячейка G2 настроена на Local».

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

Синтаксис

= ЕСЛИ (Условие; Если Истина; Если Ложь)

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

В этом случае обычно используются критерии сравнения: ‘=’ (равно), ‘>’ (больше чем), ‘<‘ (меньше чем), ‘<>’ (разное), ‘> =’ (больше или равно), ‘<=’ (меньше или равно). Вы можете сравнивать логические, числовые и текстовые значения.

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

Другие особенности функции SE:

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

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

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

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

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

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

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

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

5
6

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

Вложенные ЕСЛИ в Excel. Пример 1

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

Мы уже выполнили часть этой задачи в предыдущем примере. Действительно, мы нашли сотрудников, выручка которых превышает 50 тысяч рублей, так что теперь нам остается только выделить тех из них, которые принесли меньше 70 тысяч рублей.

Также прописываем функцию ЕСЛИ, проверяем, что выручка больше 50 тысяч рублей. ($ B5> $ D $ 1), если все устраивает, добавляем еще одну функцию ЕСЛИ, в которой проверяем, что выручка уже ниже 70 тысяч рублей. ($ B $ 5 <$ D $ 2).
Если все условия соблюдены, напишите «Да», в остальных случаях оставьте пустое поле:

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

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

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

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

И (логическое1; логическое2; логическое3; …)

Проверяет, все ли аргументы ИСТИНА, и возвращает ИСТИНА, если все аргументы верны.

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

В результате мы проверяем оба условия с помощью функции ЕСЛИ и логического выражения в форме формулы И ($ B5> $ E $ 1; $ B5 <$ E $ 2) и возвращаем «Да», если истинно:

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

Вложенные ЕСЛИ в Excel. Пример 2

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

Также воспользуемся вложенным IF и проверим несколько условий по очереди, например, сначала условие для выручки меньше 50 тысяч рублей. ($ B5 <$ F $ 1), если true, мы возвращаем «Плохо», в остальных случаях добавляем еще один чек на выручку, уже превышающую 70 тысяч рублей. ($ B5> $ F $ 2) и напишите значение «Отлично»:

Но мы бы не анализировали этот пример, если бы все было так просто

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

А если один из параметров не заполнен?

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

= ЕСЛИ (MI2> 100; F2 * 0,1)

Что будет в результате?

Насколько красиво и удобно — судить вам. Я думаю, что в любом случае лучше использовать оба аргумента.

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

= ЕСЛИ (MI2> 100; F2 * 0,1,»»)

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

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

Кроме того, если вам действительно нужно проверить некоторые условия и получить «Истина» или «Ложь» («Да» или «Нет»), вы можете использовать следующую конструкцию –

= ЕСЛИ (MI2> 100; ИСТИНА; ЛОЖЬ)

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

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

Простейший пример применения.

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

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

В этом нам поможет функция SE. Добавим в таблицу данных столбец «Страна». Западный регион — это местные продажи («Местные»), а остальные регионы — зарубежные продажи («Экспорт»).

Функция ЕСЛИ: примеры с несколькими условиями.

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

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

Вот типичный пример от IF до IF. Предположим, у вас есть список студентов в столбце A таблицы Excel и их оценки за тест в столбце B. Вы хотите ранжировать их оценки на основе следующих условий:

• «Отлично»: более 249 баллов
• «Хорошо»: с 249 до 200 включительно
• «Удовлетворительно»: от 199 до 150 включительно
• «Плохо»: до 150.

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

= ЕСЛИ (B2> 249; «Отлично»; ЕСЛИ (B2> = 200; «Хорошо»; ЕСЛИ (B2> 150; «Удовлетворительно»; «Плохо»)))

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

= ЕСЛИ (B2> 249; «Отлично»;

ЕСЛИ (B2> = 200; «Хорошо»;

ЕСЛИ (B2> 150; «Удовлетворительно»; «Плохо»)))

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

ЕСЛИ (проверяет, является ли B2> = 249, если ИСТИНА — возвращает «отлично», или
ЕСЛИ (проверяет, если B2> = 200, если ИСТИНА — возвращает «хорошо», или
ЕСЛИ (проверяет, если B2> 150, если ИСТИНА — возвращает «Удовлетворительно», если ЛОЖЬ —
вернуть «Плохо»)))

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

• В функции ЕСЛИ можно проверить 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» всякий раз, когда состояние выполнено ИСТИНА).

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

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

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

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

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

 

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

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

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

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

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

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

Пример 2. Использование вложенной функции IF (ЕСЛИ) для проверки условия выражения

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

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

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

Пример 3. Вычисляем сумму комиссии с продаж с помощью функции IF (ЕСЛИ) в Excel

Функция позволяет выполнять вычисления с числами. Хороший вариант использования — рассчитать комиссию с продаж для торгового представителя.

В следующем примере торговый представитель:

• не получает комиссию при объеме продаж менее 50 тысяч;
• получает комиссию 2%, если продажи составляют от 50 до 100 тыс
• получает комиссию 4%, если продажи превышают 100к.

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

= SE (B2 <50.0, SE (B2 <100, B2 * 2%, B2 * 4%)) — английская версия

= SE (B2 <50,0; SE (B2 <100; B2 * 2%; B2 * 4%)) — русская версия

В формуле, использованной в предыдущем примере, расчет суммы комиссии выполняется в той же функции SE. Если объем продаж составляет от 50 до 100 000, формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продаж.

Пример 4. Используем логические операторы (AND/OR) (И/ИЛИ) в функции IF (ЕСЛИ) в Excel

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

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

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

Формула для этого расчета:

= ЕСЛИ (И (B2> 80, C2> 80%), «Да», «Нет») — английская версия

= ЕСЛИ (И (B2> 80; C2> 80%), «Да»; «Нет») — русская версия

Аргументы функции

• логический_тест (logical_test) — это условие, которое вы хотите проверить. Этот аргумент функции должен быть логическим и иметь значение FALSE или TRUE. Аргумент может быть статическим значением или результатом функции или вычисления;
• value_if_true (value_if_true) — (необязательно) — значение, возвращаемое функцией. Он будет отображаться, если проверяемое значение соответствует условию ИСТИНА;
• if_value (if_false_value) — (необязательно) — значение, возвращаемое функцией. Он будет отображаться, если проверяемое условие соответствует условию FALSE.

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

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

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

Например, мы предоставляем нашему покупателю скидку в зависимости от суммы покупки. Если сумма больше 100 — скидка 10%.

Мы называем столбец H «Скидка», а в ячейку H2 вводим функцию ЕСЛИ, вторым аргументом которой будет формула для расчета скидки.

= ЕСЛИ (E2> 100; F2 * 0,1,0)

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

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

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

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

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

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

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

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

Проблема

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

0 (ноль) в ячейке	Аргумент if_value или the_if_value не указан. Чтобы вернуть правильное значение, добавьте текст двух аргументов или ИСТИНА / ЛОЖЬ.
#ИМЯ? в камере	Обычно это указывает на ошибку в формуле.

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

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

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

И будет выполнено, что будет, если не будет выполнено. Строить довольно просто.

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

Примеры применения

Пример 1

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

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

= ЕСЛИ (СЧЁТ (Control1; Control2;…); IfTrue; IfFalse)

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

Чтобы проверить наличие цифр в строке, мы воспользуемся функцией ПОИСК.

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

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

Пример 2

Как ускорить ВПР в 50 000 раз? С ЕСЛИ и вторым ВПР! Нет, здесь нет никакой ошибки. Подробнее об этом увлекательном явлении вы можете прочитать в соответствующей статье о функции ВПР, а здесь будут представлены только файл и формула с участием SE.

Вот как выглядит его синтаксис:

= ЕСЛИ (ВПР (A1, $ E $ 1: $ E $ 400000,1,1) = A1, ВПР (A1, $ E $ 1: $ F $ 400000,2,1); «»)

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

Если вы не верите, что 250 000 ячеек можно сравнить с 400 000 других ячеек всего за мгновение, загрузите файл и попробуйте скопировать формулу из столбца B до конца:

Ошибки

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

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

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

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

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

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