ЕСЛИ (функция ЕСЛИ)
Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Например, функция =ЕСЛИ(C2=»Да»;1;2) означает следующее: ЕСЛИ(С2=»Да», то вернуть 1, в противном случае вернуть 2).
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Условие, которое нужно проверить.
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Простые примеры функции ЕСЛИ
В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)
В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст «Да», в противном случае вернуть текст «Нет»). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.
B2;»Превышение бюджета»,»В пределах бюджета»)» loading=»lazy»>
=ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)
В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст «Превышение бюджета», в противном случае вернуть текст «В пределах бюджета»)
B2;C2-B2;»»)» loading=»lazy»>
На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение «Фактические» больше значения «Плановые», то вычесть сумму «Плановые» из суммы «Фактические», в противном случае ничего не возвращать).
В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = «Да», то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)
Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: «Текст»). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.
Распространенные неполадки
Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.
Как правило, это указывает на ошибку в формуле.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция УСЛОВИЯ
Функция ЕСЛИМН проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия, принимающего значение ИСТИНА. Функцию ЕСЛИМН можно использовать вместо нескольких вложенных операторов ЕСЛИ. Ее гораздо проще читать при наличии нескольких условий.
Простой синтаксис
Как правило, синтаксис функции ЕСЛИМН выглядит следующим образом:
=ЕСЛИМН(условие1; значение1; [условие2; значение2]; [условиe3; значение3])
Обратите внимание, что функция ЕСЛИМН позволяет проверить до 127 различных условий. Однако не рекомендуется вкладывать слишком много условий с операторами ЕСЛИ или ЕСЛИМН. Это связано с тем, что несколько условий нужно вводить в правильном порядке и их может быть сложно добавлять, проверять и обновлять.
Условие, принимающее значение ИСТИНА или ЛОЖЬ.
Результат, возвращаемый, если условие лог_выражение1 принимает значение ИСТИНА. Может не указываться.
Условие, принимающее значение ИСТИНА или ЛОЖЬ.
значение_если_истина2. значение_если_истина127 (необязательно)
Результат, возвращаемый, если условие лог_выражениеN принимает значение ИСТИНА. Каждый аргумент значение_если_истинаN соответствует условию лог_выражениеN. Может не указываться.
Пример 1
Формула для ячеек A2:A6:
Расшифровка: ЕСЛИ(значение A2 больше 89, возвращается «A», если значение A2 больше 79, возвращается «B», и т. д. Для всех значений меньше 59 возвращается «F»).
Пример 2
В ячейке G7 находится следующая формула:
Расшифровка: ЕСЛИ(значение в ячейке F2 равно 1, возвращаемое значение в ячейке D2, если значение в ячейке F2 равно 2, возвращается значение в ячейке D3, и т. д. Если ни одно из других условий не выполняется, возвращается значение в ячейке D8.
Замечания
Чтобы задать результат по умолчанию, введите значение ИСТИНА для последнего аргумента лог_выражение. Если ни одно из других условий не выполняется, возвращается соответствующее значение. Это показано в строках 6 и 7 примера 1 (с 58 баллами).
Если условие лог_выражение указывается без соответствующего аргумента значение_если_истина, появляется сообщение «Указано недостаточно аргументов».
Если аргумент лог_выражение принимает значение, отличное от ИСТИНА или ЛОЖЬ, функция возвращает ошибку #ЗНАЧ!.
Если условия со значением ИСТИНА не найдены, возвращается ошибка #Н/Д!
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Примеры формулы ЕСЛИ с несколькими условиями в Excel
Одиночные условия функции ЕСЛИ, которые проверяют в первом аргументе выполняется ли условие, можно объединять с последующим условием во втором и/или в третьем аргументе (в зависимости от поставленной задачи). Данное решение в Excel называется циклование функций или функция ЕСЛИ с несколькими условиями. Второй и третий аргументы: Значение_если_ИСТНИА и Значение_если_ЛОЖЬ в функции также могут содержать простые условия, как и первый аргумент. Таким образом можно проверять несколько условий, при чем проверка очередного условия зависит от результата проверки предыдущего.
Формула с несколькими функциями ЕСЛИ в Excel
Ниже на рисунке изображен рабочий лист с двумя выпадающими списками, в которых пользователь может указать тип автомобиля и его характеристики.
В ячейках A2 и C2 находятся выпадающие списки. Свойства характеристик автомобиля записаны в двух диапазонах ячеек ниже под этими таблицами D2:D3 и D6:D7. Создайте выпадающие списки выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных», в окне «Проверка вводимых значений» укажите «Тип данных: Список». Заполните их параметры так как показано ниже на рисунке:
В данном примере используется формула в ячейке C7, которая в зависимости от типа и характеристик автомобиля возвращает его категорию кузова: Седан, Купе, Пикап или Внедорожник:
В данной формуле результат вычисления первого логического выражения по первому условию влияет на результат, возвращаемый вторым логическим выражением по второму условию. Рисунок:
В данном примере если проверяемая ячейка содержит значение «Легковой», тогда во втором условии проверяется имеет ли другая ячейка значение 2-хдверный или 4-хдверный. А если же проверяемая ячейка в первом условии имеет значение «Грузовик», тогда проверяется второе условие. Теперь оно проверяет содержит ли другая ячейка значение «с задним сидением» или «без заднего сидения».
Базовым инструментом для выполнения условных анализов данных в Excel является функция ЕСЛИ. Если необходимо выполнить анализ данных по нескольким условиям эту функцию можно зациклить, для этого в ее аргументах снова использовать еще одну функцию ЕСЛИ. В выше приведенном примере первая функция ЕСЛИ проверяет содержимое ячейки A2. Функция вместо того чтобы вернуть результат из второго аргумента вызывает вторую функцию ЕСЛИ, которая уже проверяет значение ячейки C2 по второму условию. Аналогично в третьем аргументе первой функции не содержится обычное значение, а третья функция ЕСЛИ, которая также проверяет значения ячейки C2, но уже по другому своему условию.
На рисунке примера мы видим, что пользователь выбрал из выпадающего списка тип автомобиля «Грузовик». Логическое выражение в первой функции ЕСЛИ вернуло значение ЛОЖЬ, так как содержимое ячейки A2 не равно значению «Легковой», поэтому возвращается второй аргумент первой функции. А в том аргументе формула проверяла: содержит ли ячейка C2 значение «с задним сидением» и вернула значение первого аргумента «Внедорожник». Если бы пользователь во втором выпадающем списке указал на опцию «без заднего сидения», тогда было бы возвращено содержимое третьего аргумента третьей функции ЕСЛИ – «Пикап».
Проверка по нескольким условиям без использования функции ЕСЛИ
В старых версиях Excel до 2007-й версии можно было создавать не больше 7-ми уровней циклирования функций ЕСЛИ. Начиная с 2007-й версии Excel, количество циклов увеличено до 64-х уровней. Как не сложно догадаться формулы с зацикленными функциями ЕСЛИ по нескольким условиям уже на 5-ом уровне весьма нечитабельны и сложно применимы, не говоря уже о 7-ми или 64-х уровнях. Если требуется формула больше чем для 3-х – 4-х уровней циклирования, стоит поискать альтернативные методы.
Альтернативная формула с одной функцией ЕСЛИ и ВПР
Когда формула содержит слишком много зацикленных логических функций ЕСЛИ, ее сложно использовать и прочитать. Ниже на рисунке приведен немного другой пример выборки типа кузова автомобиля. Вместо ввода постоянных значений в аргументах функций ЕСЛИ, можно указать ссылки на ячейки, которые содержат соответствующие значения. Например, возле ячейки с тестом «4-хдерный» находится текст «Седан». На эти значения можно ссылаться прямо из формулы.
Новая альтернативная формула будет выглядеть следующим образом:
Мы получили аналогичный результат. Условия первой функции ЕСЛИ остается неизменным. Однако во втором ее аргументе уже содержится функция ВПР, которая умеет искать значения по условию в диапазоне ячеек D2:E3. А в третьем аргументе функции ЕСЛИ формула ВПР ищет значения по условию в диапазоне D6:E7.
Проверка ввода данных по условию динамических выпадающих списков
Выпадающие списки для ввода данных пользователем показаны на рисунке примера этой статьи. Это стандартный инструмент проверки вода данных Excel. Пользователь не вводит значение в ячейку, а лишь выбирает его из выпадающего списка.
Создадим зависимый выпадающий список с динамическим изменением значений. Вам необходимо изменить правила проверки ввода данных выпадающим списком в ячейке C2 использовать интересную технику с функцией =ДВССЫЛ(). Благодаря ей содержимое выпадающего списка будет изменятся в зависимости от значения в ячейке A2.
Выше на рисунке изображено окно «Проверки вводимых значений» с формулой для списка в ячейке C2.
Еще определим на листе 2 имена диапазонов для ячеек. Диапазон с именем «Легковой» охватывает ячейки D2:D3, а диапазон имени «Грузовик» относится к ячейкам D6:D7. Можно просто выделить диапазон и в поле «Имя» ввести название. Или создать имена по инструкции: «ФОРМУЛЫ»-«Диспетчер имен»-«Создать» как изображено ниже на рисунке:
Эти имена диапазонов идентично совпадают со значениями позиций первого выпадающего списка для ячейки A2. А как вы помните поле ввода «Источник:» содержит функцию ДВССЫЛ со ссылкой на ячейку A2 в ее аргументе.
Аргумент функции ДВССЫЛ получает текст с помощью ссылки на ячейку A2. Например, в данном случае формула имеет отладочный вид: =ДВССЫЛ(«Грузовик»), так как на данный момент ячейка A2 содержит этот текст. Это же и есть имя диапазона. В результате вычислений функция ДВССЫЛ возвращает ссылку с адресом диапазона ячеек D6:D7. А значения этих ячеек используются для второго выпадающего списка в ячейке C2. Когда ячейка A2 будет содержать слово «Легковой», тогда функция ДВССЫЛ вернет уже ссылку на другой диапазон ячеек D2:D3, который бы использовался во втором выпадающем списке. Таким образом мы получаем динамический выпадающий список в ячейке C2, который меняет свои значения при условии выбора по первому выпадающему списку.
Теперь связанный выпадающий список не содержит лишних опций, как ему и полагается.
Единственная проблема осталась этом примере это то, что в момент изменения значения пользователем в ячейке A2, автоматически не меняется значение в C2. Перечень значений – обновляются, а значения в ячейке будут обновлены только после использования списком. Поэтому пользователь должен обязательно использовать 2 списка для обновления значений в обеих ячейках. Иначе будут ошибочные результаты при вычислении формулы.
Макрос для связанного динамического выпадающего списка
Но можно для нашего листа просто прописать в пару строчек кода элементарный макрос для очистки ячейки B2 при каждом использовании выпадающего списка в A2. Для этого откройте окно редактора VBA-кода макросов: «РАЗРАБОТЧИК»-«VisualBasic» или нажмите ALT+F11. После чего вставите это код в лист где находится таблица:
А в ячейке C7 где была старая формула вводим новую формулу с функцией ВПР:
В результате нам не нужна функция ЕСЛИ для проверки значений по нескольким условиям.
Все должно быть просто и красиво ведь в этом и заключается магия!
Оператор “ЕСЛИ” в программе Microsoft Excel: применение и примеры
Программа Excel, безусловно, обладает очень богатым функционалом. И среди множества разнообразных инструментов особое место занимает оператор «ЕСЛИ». Он помогает в решении совершенно различных задач, и к этой функции пользователи обращаются намного чаще, чем к остальным.
В этой статье мы поговорим о том, что же такое оператор «ЕСЛИ», а также рассмотрим область применения и принципы работы с ним.
Содержание: Функция “ЕСЛИ” в Excel
Определение функции “ЕСЛИ” и ее назначение
Оператор «ЕСЛИ» – это инструмент программы Эксель для проверки определенного условия (логического выражения) на выполнение.
То есть, представим, что у нас есть какое-то условие. Задача “ЕСЛИ” – проверить соблюдение данного условия и вывести в ячейку с функцией значение, основанное на результате проверки.
Сама формула функции в программе представляет собой следующее выражение:
=ЕСЛИ(условие; [значение если условие выполнено]; [значение если условие не выполнено])
Использование функции “ЕСЛИ” на примере
Вероятно, вышеизложенная информация может показаться не такой понятной. Но, на самом деле ничего сложного здесь нет. И для того, чтобы лучше понять назначение функции и ее работу рассмотрим пример ниже.
У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования.
Наша задача — в столбце “Скидка” вывести значение «25%» для всех строк с наименованиями для женского пола. И соответственно, значение «0», если в столбце «Пол» стоит значение «мужской»
Заполнение данных вручную займет немало времени, и есть высокая доля вероятности где-то ошибиться, особенно, если список длинный. Гораздо проще в этом случае будет автоматизировать процесс при помощи оператора «ЕСЛИ».
Для выполнения этой задачи потребуется составить следующую формулу ниже:
=ЕСЛИ(B2=”женский”;25%;0)
Данную формулу пишем в самой верхней ячейке столбца “Скидка” и нажимаем Enter. Не забываем ставить знак равно (=) перед формулой.
После этого для данной ячейки будет отображен результат согласно нашему логическому условию (на забываем настроить формат ячеек – процентный). Если проверка выявит, что пол «женский» – будет выведено значение 25%. В противном случае значение ячейки будет равно 0. Собственно говоря, что нам и требовалось.
Теперь осталось только скопировать данное выражение на все строки. Для этого подведите курсор мыши к правому нижнему краю ячейки с формулой. Указатель мышки при этом должен превратиться в крестик. Зажмите левую кнопку мыши и растяните формулу на все строки, которые нужно проверить согласно заданным условиям.
Вот и все, теперь мы применили условие ко всем строкам и получили результат для каждой из них.
Применение «ЕСЛИ» с несколькими условиями
Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму.
Рассмотрим наглядно на примере все той же таблицы. Но на этот раз усложним задачу. Теперь нужно проставить скидку на женскую обувь в зависимости от вида спорта.
Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%.
Пропишем формулу для этих условий в нужной нам ячейке.
=ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))
Щелкаем Enter и получаем результат согласно заданным условиям.
Далее растягиваем формулу на все оставшиеся строки таблицы.
Одновременное выполнение двух условий
Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И».
Рассмотрим на примере нашей таблицы. Теперь скидка 30% будет проставлена только, если это женская обувь и предназначена для бега. При соблюдении этих условий одновременно значение ячейки будет равно 30%, в противном случае – 0.
Для этого используем следующую формулу:
=ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)
Нажимаем клавишу Enter, чтобы отобразить результат в ячейке.
Аналогично примерам выше, растягиваем формулу на остальные строки.
Оператор «ИЛИ»
В этом случае значение логического выражения считается истинным при выполнении одного из условий. Второе условие при этом может не выполняться.
Поставим задачу следующим образом. Скидка 35% предоставляется только на мужскую обувь для тенниса. Если это мужская обувь для бега или любая женская обувь, скидка равна 0.
В данном случае нужна следующая формула:
=ЕСЛИ(ИЛИ(B2=”женский”; C2=”бег”);0;35%)
После нажатия Enter мы получим требуемое значение.
Растягиваем формулу вниз и скидки для всего ассортимента готовы.
Как задать функции ЕСЛИ через Построитель формул
Использовать функцию ЕСЛИ можно не только прописывая ее вручную в ячейке или строке формул, но также и через Построитель формул.
Давайте разберем, как это работает. Допустим, нам снова как и в первом примере нужно проставить скидку на всю женскую обувь в размере 25%.
Заключение
Одним из самых популярных и полезных инструментов в 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» означает команду поиска точных (а не приблизительных) значений.
Выбираем формат и устанавливаем, как изменятся ячейки при соблюдении формулы. Лучше сделать заливку цветом.
Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).
Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще.