Операторы excel: Типы операторов в формулах Excel — MS Excel

Какие бывают операторы в Эксель (Excel), как ими пользоваться

Опубликовано: 24.09.2019Автор: Дмитрий Просветов

Вы писали формулы в Excel. Как задать тип вычисления, которое нужно выполнить для элементов в формуле. Используйте операторы. Рассмотрим какие бывают операторы в Эксель (Excel), и как ими пользоваться.

Содержание

1. Виды операторов в Excel
2. Логические операторы в Excel
3. Логические операторы в формулах
4. Логический оператор «И»
5. Условные операторы в Excel
6. Операторы формул в Excel
7. Арифметические операторы
8. Арифметические операторы и их приоритет
9. Вывод

Виды операторов в Excel

В графическом редакторе существует четыре типа оператора:

1. Простые математические вычисления;
2. Сравнение. Сравниваются две величины, результат — выражение «ЛОЖЬ» или «ИСТИНА»;
3. Текстовый. Объединяет части контента в строку;
4. Ссылки. Перенаправление на другие ячейки.

Логические операторы в Excel

Сравнивается два значения. Результатом будет «ЛОЖЬ» или «ИСТИНА». Существует такие операторы:

1. Равно «=». Например: =А1=В1. Возвратит «ИСТИНА», когда А1=В1. Иначе — «ЛОЖЬ»;
2. Не равно «<>». =А1<>B1. Если А1 не равно B1 — «ИСТИНА»;
3. Больше «>». =А1>B1. Если А1>B1 «ИСТИНА»;
4. Меньше «<». =А1<B1 Если А1<B1 «ИСТИНА»;
5. Больше или равно. «>=». =А1>=B1 Если значение в А1 больше или равно числу ячейки B1 «ИСТИНА»;
6. Меньше или равно. «<=». =А1<=B1. Если число в А1 меньше или равно значению в B1 «ИСТИНА».

Логические операторы в формулах

Рассмотрим на примере:

Логический оператор «И»

Проверяет несколько условий на предмет их выполнения. Например, формула =И(А2=”Яблоки”;В2>С2). Если в А2 находятся «Яблоки», а В2>С2 возвратится значение «ИСТИНА». Иначе — «ЛОЖЬ».

Условные операторы в Excel

Функция ЕСЛИ проверяет условие на ЛОЖЬ или ИСТИНУ. »;

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

Вывод

Не забывайте, формула начинается со знака «=». Если не получается написать формулу, возможно есть опечатка в выражении. Не спешите. Прописывайте формулу внимательно.

Понравилась статья? Поделиться с друзьями:

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

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

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

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

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

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

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



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

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

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

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

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

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

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

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скачать все примеры функции ЕСЛИ в Excel

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

Функция ЕСЛИ – вложенные формулы и избежание ловушек

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

Таким образом, оператор IF может иметь два результата. Первый результат — если ваше сравнение истинно, второй — если ваше сравнение ложно.

IF невероятно надежны и составляют основу многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться к минимальным условиям, таким как Мужчина/Женщина, Да/Нет/Возможно, и это лишь некоторые из них, но иногда вам может понадобиться оценить более сложные сценарии, требующие вложения* более 3 функций ЕСЛИ вместе.

* «Вложенность» относится к практике объединения нескольких функций в одной формуле.

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

Синтаксис

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

Например:

Примечания

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

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

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

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

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

Примеры

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

• org/ListItem»>

  =ЕСЛИ(D2>

  89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))

  Этот сложный вложенный оператор IF следует простой логике:

1. Если результат теста (в ячейке D2) больше 89, учащийся получает A

2. Если результат теста выше 79, учащийся получает B

3. Если результат теста выше 69, учащийся получает C

4. Если результат теста больше 59, то студент получает D

5. org/ListItem»>

   В противном случае учащийся получает F

Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно сегментировать оценки между A+, A и A- (и так далее)? Теперь нужно переписать оператор IF с четырьмя условиями, чтобы он содержал 12 условий! Вот как теперь будет выглядеть ваша формула:

• =ЕСЛИ(В2>97,»А+»,ЕСЛИ(В2>93,»А»,ЕСЛИ(В2>89,»А-«,ЕСЛИ(В2>87,»В+»,ЕСЛИ(В2>83,» В», ЕСЛИ(В2>79,»В-«, ЕСЛИ(В2>77,»С+»,ЕСЛИ(В2>73,»С»,ЕСЛИ(В2>69,»С-«,ЕСЛИ(В2>57) ,»D+»,ЕСЛИ(B2>

  53,»D»,ЕСЛИ(B2>49,»D-«,»F»))))))))))))

Он по-прежнему функционально точен и будет работать так, как ожидалось, но его написание и тестирование занимает много времени, чтобы убедиться, что он работает так, как вы хотите. Еще одна вопиющая проблема заключается в том, что вам приходилось вводить баллы и эквивалентные буквенные оценки вручную. Какова вероятность того, что вы случайно сделаете опечатку? А теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но вы действительно хотите подвергать себя таким усилиям и вероятным ошибкам, которые будет очень трудно обнаружить?

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

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

Ниже приведен очень распространенный пример расчета комиссии с продаж на основе уровня достижения дохода.

• =ЕСЛИ(С9>15000,20%,ЕСЛИ(С9>12500,17,5%,ЕСЛИ(С9>10000,15%,ЕСЛИ(С9>7500,12,5%,ЕСЛИ(С9>5000,10%,0)) )))

Эта формула гласит: ЕСЛИ(C9 больше 15 000, то возвращается 20 %, ЕСЛИ(C9 больше 12 500, возвращается 17,5 % и т. д.)

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

Вот пример сценария комиссии с неисправной логикой:

Вы видите, что не так? Сравните порядок сравнения Revenue с предыдущим примером. В какую сторону идет этот? Правильно, она идет снизу вверх (от 5000 до 15000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это большое дело, потому что формула не может пройти первую оценку для любого значения, превышающего 5000 долларов. Допустим, у вас есть доход в размере 12 500 долларов – оператор IF вернет 10 %, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях эти типы ошибок остаются незамеченными, пока они не окажут негативного влияния. Итак, зная, что сложные вложенные операторы IF сопряжены с некоторыми серьезными ловушками, что вы можете сделать? В большинстве случаев можно использовать функцию ВПР вместо построения сложной формулы с помощью функции ЕСЛИ. Используя функцию ВПР, сначала необходимо создать справочную таблицу:

Эта формула предлагает искать значение в C2 в диапазоне C5:C17. Если значение найдено, вернуть соответствующее значение из той же строки в столбце D.

Аналогичным образом эта формула ищет значение в ячейке B9 в диапазоне B2:B22. Если значение найдено, вернуть соответствующее значение из той же строки в столбце C.

Примечание. Обе эти функции ВПР используют аргумент ИСТИНА в конце формул, что означает, что мы хотим, чтобы они искали приблизительное совпадение. Другими словами, он будет соответствовать точным значениям в таблице поиска, а также любым значениям, которые находятся между ними. В этом случае таблицы поиска должны быть отсортированы в порядке возрастания, от меньшего к большему.

VLOOKUP рассматривается здесь более подробно, но это, безусловно, намного проще, чем 12-уровневый сложный вложенный оператор IF! Есть и другие менее очевидные преимущества:

• Справочные таблицы

  VLOOKUP открыты и легко видны.

• org/ListItem»>

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

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

Знаете ли вы?

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

• =ЕСЛИ(D2>89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))

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

• org/ListItem»>

  =ЕСЛИ(D2>89,»A»,D2>79,»B»,D2>69,»C»,D2>59,»D»,ИСТИНА,»F»)

Функция IFS великолепна, потому что вам не нужно беспокоиться обо всех этих операторах IF и скобках.

Примечание. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена ​​последняя версия Office.

Купить или попробовать Microsoft 365

Нужна дополнительная помощь?

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

Связанные темы

Видео: расширенные функции ПЧ
Функция IFS (Microsoft 365, Excel 2016 и более поздние версии)
Функция СЧЁТЕСЛИ будет подсчитывать значения на основе одного критерия.
Функция СЧЁТЕСЛИМН будет подсчитывать значения на основе нескольких критериев.
Функция СУММЕСЛИ будет суммировать значения на основе одного критерия.
Функция СУММЕСЛИМН будет суммировать значения на основе нескольких критериев.
И-функция
Функция ИЛИ
Функция ВПР
Обзор формул в Excel
Как избежать некорректных формул
Обнаружение ошибок в формулах
Логические функции
функции Excel (по алфавиту)
функции Excel (по категориям)

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

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

С помощью оператора IF вы можете указать Excel выполнять различные вычисления в зависимости от того, является ли ответ на ваш вопрос истинным или ложным.

Посмотреть видео

Загрузить рабочие тетради

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

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

 =ЕСЛИ(D2>5,F2*10%,F2*5%) 

 =ЕСЛИ(D2>5,"Плата 10%","Плата 5%")