Использование диапазона значений в функции если. Функция если в excel с несколькими условиями

Сегодня мы рассмотрим функцию ЕСЛИ .

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

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

Синтаксис функции ЕСЛИ очень простой:

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

лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.

В этой части необходимо проверить на соответствие выражения.

Например:

ЕСЛИ(А1=10 ; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ

Другой пример

ЕСЛИ(А1>30 ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ

Еще пример

ЕСЛИ(С1=”Да” ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ

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

значение_если_истина , значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения : ИСТИНА и ЛОЖЬ

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

Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)

Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ

ЕСЛИ(A3="Ведро";D3;"-")

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

Итак, первый аргумент (лог выражения ) A3="Ведро" проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина ), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь ), в нашем случае это «-» (т.е будет написано тире).

Таким образом, в ячейки E3 появится значение D3, т.е число 240.

Сегодня мы расскажем о функции табличного редактора Excel «Если». Она имеет отношение к логическим возможностям приложения. Ее можно отнести к наиболее востребованным функциям во время работы.

Ключевые возможности

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

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

Функция выглядит таким образом: =ЕСЛИ (задача; истина; ложь). Первая составная часть — логическое выражение. Оно может быть фразой или числом. К примеру, «10» или «без НДС» — это логические выражения. Данный параметр обязательно необходимо заполнить. Истина — это значение, которое отобразится как результат, если выражение будет верным. Ложь — данные, которые будут выданы, когда задача не будет верной.

Равенство параметров двух ячеек

Для понимания возможностей функции «Если» в Excel, примеры просто необходимы, и далее мы перейдем к их рассмотрению. Вводим в клетку C1 показатель 8. Далее в поле по адресу D1 вписываем следующую формулу: =ЕСЛИ(C1<10; 1; 2). Таким образом, программа начнет из клетки C1 с параметром 10. Когда оно будет меньше десяти, в поле по адресу D1 появится единица. В обратном случае редактор укажет число 2. Рассмотрим еще одну задачу. Есть перечень студентов и их оценки, полученные по экзамену. Данные таковы: 5, 4, а также 3 и 2. Условие задачи предполагает создание для каждого из студентов текстового комментария «сдал» либо «не сдал». Другими словами, если учащийся получил оценку три и выше, он с экзаменом справился. Если его бал ниже 3 - студент не сдал. Чтобы решить эту задачу пишем такую формулу: =ЕСЛИ(C1<3; «не справился»; «сдал»). Excel начнет сравнивать результаты каждого из студентов. Когда показатель будет меньше трех (то есть двойка), в соответствующей клетке будет указан комментарий «не справился». Если оценка - 3 и выше, в необходимой графе появится отметка о том, что студент экзамен сдал. Текстовые комментарии необходимо всегда указывать в кавычках. Согласно подобному несложному принципу функционирует в Excel формула «Если». Во время ее использования, можно применять такие операторы сравнения: < >, =, >, <, >=, <=.

Примеры с применением условий «ИЛИ», «И»

Продолжаем практиковаться и осваивать логические возможности приложения. Функцию табличного редактора Excel «Если» можно соединить с операторами сравнения. К ним относятся параметры: «ИЛИ», «И». Укажем необходимое условие в Excel: если оценка учащегося равна или меньше 5, но больше 3. Должен быть отображен комментарий: «проходит». В противном случае - «нет». Итак, проходят лишь те учащиеся, которые получили пятерки и четверки. Записать подобную задачу в табличном редакторе можно используя специальную формулу. Она будет иметь вид: =ЕСЛИ(И(A1<=5; A1>3); «проходит», «нет»). К более сложному примеру можно отнести использованием «ИЛИ» либо «И». Итак посмотрим, как применять формулу в Excel, если несколько условий в задаче. Пример такого выражения: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). Из этого следует, что если показатель в клетке А1 равен 5 либо 10, программа отобразит результат 100, в обратном случае — 0. Можно использовать эти операторы и чтобы решить более сложные задачи. К примеру, в базе необходимо вычислить должников, которым необходимо заплатить более 10000 рублей. При этом они не погашали заем более шести месяцев. Функция табличного редактора Excel «Если» позволяет в автоматическом режиме получить напротив соответствующих имен пометку «проблемный клиент». Предположим, в клетке A1 расположены данные, указывающие на срок задолженности (месяцы). Поле B1 отображает сумму. В этом случае формула будет иметь следующий вид: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный клиент»; «»). Отсюда следует, что если будет обнаружен человек, который соответствует указанным условиям, программа укажет напротив его имени требуемый комментарий. Для всех прочих участников перечня аналогичная клетка останется пустой. Рассмотрим пример для случая, когда ситуация является критической. Введем соответствующий комментарий. В результате формула будет иметь следующий вид: =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая ситуация»; «»). В таком случае если программа обнаружит совпадения как минимум по одному из параметров (срок, сумма задолженности), пользователь увидит соответствующее примечание. В первой ситуации сообщение «проблемный клиент» выдавалось лишь тогда, когда были выполнены оба заданных условия.

Задачи высокого уровня сложности

Функция табличного редактора Excel «Если» используется, чтобы обойти встроенные ошибки при а также еще в нескольких случаях. Первая ситуация обозначается редактором, как «ДЕЛ/0» и встречается достаточно часто. Как правило, она возникает в тех случаях, когда подлежит копированию формула «A/B», при этом показатель B в отдельных ячейках равен нулю. Избежать этого можно посредством возможностей рассматриваемого нами оператора. Итак, необходимая формула будет иметь следующий вид: =ЕСЛИ(B1=0; 0; A1/B1). Отсюда следует, что если клетка B1 будет заполнена параметром «ноль», редактор выдаст «0», в обратном случае Excel поделит показатель A1 на данные B1 и отобразит результат.

Скидка

На практике часто встречается и ситуация, которая будет рассмотрена далее. Речь идет о расчете скидки, исходя из общей суммы средств, потраченных на приобретение определенного товара. Используемая в этом случае матрица может иметь следующий вид: менее 1000 — 0%; 1001-3000 — 3%; 3001-5000 — 5%; более 5001 — 7%. Рассмотрим ситуацию, когда в Excel присутствует база данных посетителей, а также информация о сумме потраченных ими на покупки средств. Теперь необходимо рассчитать скидку для каждого клиента. С этой целью используем следующее выражение: =ЕСЛИ(A1>=5001; B1*0,93; ЕСЛИ(А1>=3001; B1*0,95;..). Система проверяет общую сумму покупок. Когда она превышает показатель в 5001 рублей, происходит умножение на 93 процента стоимости товара. В случае преодоления отметки в 3001 единицу, имеет место аналогичное действие, но уже с учетом 95%. Приведенную формулу с легкостью можно применять на практике. Объем продаж и показатели скидок задаются по усмотрению пользователя.

Применение описанных возможностей возможно для решения различного рода задач. Основной этап — правильное составление формулы, чтобы не получить ошибочного результата. Теперь вы знаете, как использовать оператор в 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» означает команду поиска точных (а не приблизительных) значений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как видим, функция «ЕСЛИ» может оказаться для пользователя хорошим помощником при работе с данными в Microsoft Excel. Она позволяет отобразить результаты, соответствующие определенным условиям.

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

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

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

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

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

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

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

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

Очевидно, что при таком значении функция имеет два результата.

Первый – получаемый в случае, когда сравнение верное, второй – когда сравнение неверное.

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

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

Имеется и третья составляющая – ложное значение. Оно возвращается всегда, изначальное условие расценено логическими алгоритмами как ложное.

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

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

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

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

А будет выполнено, что будет, если его не выполнять» построить достаточно просто.

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

Пример 1

Это простой пример с вводом только одного простого условия для данной функции.

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

В ходе выполнения операции функция сравнивает значение, указанное в графе А1 с 30.

Для выполнения проверки действуйте следующим образом:

• Пропечатайте исходное значение А1 в любой удобной ячейке (у нас это А1);
• Нажмите на ячейку, в которой вы хотите, чтобы отображался результат работы функции (у нас это В1);
• Кликните по ячейке В1 дважды левой клавишей и как только в ней появится курсор, введите =Е;
• Откроется список доступных функций с название, начинающимся на букву Е – выберите в нем ЕСЛИ, кликнув по ней в списке дважды;

• Ячейка заполнится и после слова ЕСЛИ откроется скобка – теперь вам нужно ввести условия ;
• Нажмите левой клавишей однократно на ячейку А1 – она отобразится рядом со скобкой ;
• Далее введите текстом без пробелов A1>30;»больше 30″;»»» меньше или равно 30″;

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

• При вводе цифры 20 в ячейку А1 результат будет «меньше или равно 30», так как это тоже соответствует условию;
• При вводе цифры 40 в ячейку А1 результат будет, соответственно, «больше 30».

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

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

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

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

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

Пример 2

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

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

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

Алгоритм проверки таков:

• В первом столбце перечислены порядковые номера моделей;
• Во втором столбце – возможные цвета, в которых они выполнены;

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

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

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

Сложности

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

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

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

• Появление цифры ноль в ячейке с результатом, при использовании ЕСЛИ, говорит неполадка об ошибке пользователя, так как он не указал изначальное истинное значение (если ноль появляется при подтверждении истинности условий) или ложное значение (когда ноль появляется при невыполнении условий). Для того, чтобы истинное значение могло возвращаться, укажите значение для значения Истина/Ложь;
• Появление символов #ИМЯ? в ячейке с результатом – свидетельство того, что в логической формуле, задающей условие, допущена ошибка. Потому программа не может выполнить никакие ее условия и проверить их на истинность.

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

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

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

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

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

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

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

Вывод

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

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

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