Формулы в экселе если: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Содержание

Функция если с процентами — Dudom

В этом курсе:

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

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

Например, функция =ЕСЛИ(C2=»Да»;1;2) означает следующее: ЕСЛИ(С2=»Да», то вернуть 1, в противном случае вернуть 2).

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

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

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

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

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

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

      B2;»Превышение бюджета»,»В пределах бюджета»)» xmlns:AntiXSS=»urn:AntiXSSExtensions» />

        =ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)

        В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст «Превышение бюджета», в противном случае вернуть текст «В пределах бюджета»)

        B2;C2-B2;»»)» xmlns:AntiXSS=»urn:AntiXSSExtensions» />

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

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

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

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

            0 (ноль) в ячейке

            Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.

            Как правило, это указывает на ошибку в формуле.

            Дополнительные сведения

            Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

            Функция ЕСЛИ

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

            • Открываем Эксель, найдя программу в меню «Пуск» или на рабочем столе.
            • Вводим данные, с которыми предстоит работать. Допустим, есть информация о сотрудниках компании — ее и будем использовать в качестве материала.
            • Добавляем столбец «Премия» — в него выводим результаты функции ЕСЛИ. Курсор ставим в ячейку G4.
            • Кликаем по значку функции, расположенному слева от строки ввода оператора, которая находится над рабочим полем. Также формулу в Эксель можно вставить, обратившись к пункту меню «Формулы» и выбрав там «Логические».
            • В «Категории» находим «Логические», а в появившемся списке — функцию ЕСЛИ. Кликаем по «Ок».
            • Появится окно, где необходимо указать аргументы функции, то есть условия.
            • Предположим, премия менеджеров составляет 30%. Заполняем аргументы, начиная с логического выражения — пишем там адрес ячейки и нужное значение. В нашем случае это выглядит следующим образом: Лог_выражение = D4=«менеджер». Затем указываем размер премии (30), если выражение истинно, и 0, если оно ложно. Нажимаем «Ок».
            • В результате видим, что премия первого сотрудника составляет 0%, так как он не является менеджером. Условие выполнено!
            • Таблица содержит множество строк, необходимо заполнить их все. Конечно, не требуется каждый раз вбивать функцию заново, ее надо просто скопировать. Сделать это очень просто — кликаем мышкой по ячейке G4 (там уже получен результат), подводим курсор к левому нижнему углу и смотрим, когда он превратится в плюс.
            • Растягиваем формулу на остальные ячейки, держа левую кнопку мыши нажатой и опускаясь вниз.

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

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

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

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

            • Выделяем мышкой первую ячейку (G4) в столбце с премиями. Кликаем по значку Fx, находящемуся слева от строки ввода формул.
            • Появится окно с уже заполненными аргументами функции.
            • Изменяем логическое выражение, добавив туда еще одно условие и объединив их с помощью оператора И (условия берем в скобки). В нашем случае получится: Лог_выражение = И(D4=«менеджер»;E4=«Южный»). Нажимаем «Ок».
            • Растягиваем формулу на все ячейки, выделив первую и потянув мышкой вниз при нажатой левой клавише.

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

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

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

            • Кликаем мышью по первой ячейке в столбце премий (G4), затем нажимаем на иконку слева от строки ввода формул.
            • Редактируем аргументы функции. Логическое выражение будет представлять собой: ИЛИ(D4=«бухгалтер»;D4=«директор»). В «Значение_если_истина» пишем 40, а в «Значение_если_ложь» — 0. Кликаем «Ок».
            • Копируем формулу, растягивая ее на остальные ячейки. Смотрим результат — премия 40% начислена директору и двум бухгалтерам.

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

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

            • Добавляем строку с общей зарплатой продавцов и кликаем по ячейке, куда будет выводится результат.
            • Нажимаем на иконку Fx, которая находится слева от строки ввода функций. В открывшемся окне ищем нужную формулу через поиск — вводим в соответствующее окно «суммесли», выбираем оператор в списке, кликаем «Ок».
            • Появится окно, где необходимо заполнить аргументы функции.
            • Вводим аргументы — первое поле «Диапазон» определяет, какие ячейки нужно проверить. В данном случае — должности работников. Кликаем мышкой в поле «Диапазон» и указываем там D4:D18. Можно поступить еще проще — просто выделить нужные ячейки.
            • В поле «Критерий» вводим «продавец». В «Диапазоне_суммирования» пишем ячейки с зарплатой сотрудников (вручную либо выделив их мышкой). Далее — «Ок».
            • Смотрим на результат — общая заработная плата всех продавцов посчитана.

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

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

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

            • Добавляем строку с общей зарплатой менеджеров Южного филиала. Выделим ячейку, где будет показываться итог вычислений.
            • Кликаем по значку функции. В появившемся окне в поле поиска вводим «суммеслимн», нажимаем «Найти» и выбираем нужный оператор из списка.
            • В открывшемся окне необходимо заполнить аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Диапазон_условия1» — ячейки с должностями сотрудников. «Условие1» = «менеджер», так как мы суммируем зарплату менеджеров. Теперь нужно учесть второе условие — взять менеджеров из Южного филиала. В «Диапазон_условия2» вводим ячейки с филиалами, «Условие2» = «Южный». Все аргументы определены, нажимаем «Ок».
            • В результате будет рассчитана общая зарплата всех менеджеров, работающих в Южном филиале.

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

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

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

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

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

            • Добавляем строку с количеством продавцов Северного филиала и выделяем ячейку с будущим результатом.
            • Кликаем по кнопке «Вставить функцию» во вкладке «Формулы». Через алфавитный перечень находим нужную функцию и нажимаем «Ок».
            • Вводим аргументы функции: «Диапазон_условия1» — это ячейки с должностями, «Условие1» = «продавец». В «Диапазон_условия2» пишем ячейки с филиалами, «Условие2» = «Северный».
            • В итоге будет определено количество продавцов Северного филиала.

            Подводим итоги

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

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

            1. Условие. Может выступать арифметическим знаком или логической функцией. В любом случае, главное требование к условию – оно должно возвращать значение ИСТИНА или ЛОЖЬ.
            2. Значение, если ИСТИНА. Это текст, который выводится или функция, которая будет выполняться в случае истинности значения. Допустимо использование нескольких вложенных функций ЕСЛИ для того, чтобы выполнять многоуровневые проверки.
            3. Значение, если ЛОЖЬ. Это текст, функция или значение любого другого типа, которое будет показываться или выполняться в случае, если описанный в первом аргументе критерий не соответствует действительности.

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

            Проверка условия для полного совпадения текста (ЕСЛИ + СОВПАД)

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

            В целом, для проверки соответствия переменной текстовой строке необходимо использовать логический оператор =. Общая функция выглядит следующим образом: =ЕСЛИ(G2=”выполнено”,ИСТИНА,ЛОЖЬ).

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

            Сама функция работает следующим образом: она смотрит на текстовую строку 1, текстовую строку 2. Если они полностью совпадают, то она передает в ячейку или в аргумент функции значение ИСТИНА. Если же в результате анализа строк обнаруживается хотя бы одно различие, функция возвращает значение ЛОЖЬ.

            Чтобы передать функцию СОВПАД функции ЕСЛИ, необходимо её использовать в качестве первого аргумента. Например, формула может иметь следующий вид: =ЕСЛИ(СОВПАД(G2,”Выполнено”),”Да”,”Нет”). Как мы видим в этом примере, эта формула понимает, что строки “ВЫПОЛНЕНО” и “выполнено” не являются идентичными.

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

            Функция ЕСЛИ с частичным совпадением текста

            Ранее мы разобрались, как сравниваются строки на предмет полного соответствия. Но в некоторых ситуациях достаточно лишь частичного совпадения строки с эталоном. Например, нам нужен город, но какой именно – не столь важно. Самый простой метод, как это сделать – использовать операторы ? и *. Но это хорошая идея далеко не во всех ситуациях.

            ЕСЛИ + ПОИСК

            Один из самых надежных способов добиться поставленной задачи – использование функции ПОИСК в качестве аргумента функции ЕСЛИ. С её помощью пользователь может определить порядковый номер знака, который начинает отсчёт строки. В общем виде эта функция выглядит следующим образом: =ПОИСК(что_ищем, где_ищем, начиная_с_какого_символа_ищем).

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

            Эта функция должна использоваться совместно с ещё одним оператором. Называется он ЕЧИСЛО. Его задача – проверка, является ли определённая переменная числом. Если да, ячейке или функции передаётся значение ИСТИНА. Она может использоваться, чтобы проверить, является ли конкретная переменная текстовой. Вот, как ее использовать для наших целей: =ЕСЛИ(ЕЧИСЛО(ПОИСК(“город”,B2)),”Город”,””).

            Эта функция работает по следующему алгоритму: она проверяет, является ли значение, возвращенное функцией поиска, числовым. Если да, то возвращаемое значение – «Город». Логика очень простая. Дело в том, что эта функция также фильтрует любые другие типы данных, кроме текстовых. Поскольку функция «Поиск» может возвращать и ошибку в случае ненахождения соответствующего числа, то функция ЕЧИСЛО дает возможность его отфильтровать. Если оказывается, что функция ЕЧИСЛО говорит, что была выдана ошибка, то в качестве аргумента передается пустое значение.

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

            ЕСЛИ + НАЙТИ

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

            1. Что ищем.
            2. Где осуществляется поиск.
            3. С какой позиции ищется нужный текст.

            Следовательно, и использование формулы будет абсолютно аналогичным. =ЕСЛИ(ЕЧИСЛО(ПОИСК(“город”,B2)),”Город”,””). Как видим, функция ЕСЛИ дает возможность осуществлять самые разнообразные операции. Это один из главных операторов Excel, который позволяет автоматизировать почти все, что только можно.

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

            Как использовать функцию ЕСЛИ в Excel (Простое руководство)

            Простые примеры если | И/или Критерии | Вложенный, если | Подробнее о If

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

            Примеры Simple If

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

            1а. Например, взгляните на функцию ЕСЛИ в ячейке B2 ниже.

            Объяснение: если цена больше 500, функция ЕСЛИ возвращает значение High, в противном случае — значение Low.

            1б. Следующая функция ЕСЛИ дает точно такой же результат.

            Примечание: вы можете использовать следующие операторы сравнения: = (равно), > (больше), = (больше или равно), (не равно).

            2. Всегда заключайте текст в двойные кавычки.

            3а. Приведенная ниже формула вычисляет прогресс между двумя моментами времени.

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

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

            Критерии И/ИЛИ

            Используйте функцию ЕСЛИ в сочетании с функциями И и ИЛИ и станьте экспертом в Excel.

            1. Например, взгляните на функцию ЕСЛИ в ячейке D2 ниже.

            Объяснение: функция И возвращает ИСТИНА, если первая оценка больше или равна 60, а вторая оценка больше или равна 90, в противном случае она возвращает ЛОЖЬ. Если TRUE, функция ЕСЛИ возвращает значение Pass, если FALSE, функция IF возвращает значение Fail.

            2. Например, взгляните на функцию ЕСЛИ в ячейке D2 ниже.

            Объяснение: функция ИЛИ возвращает ИСТИНА, если хотя бы одна оценка больше или равна 60, в противном случае она возвращает ЛОЖЬ. Если TRUE, функция ЕСЛИ возвращает значение Pass, если FALSE, функция IF возвращает значение Fail.

            3. Например, взгляните на функцию ЕСЛИ в ячейке D2 ниже.

            Объяснение: приведенная выше функция И имеет два аргумента, разделенных запятой (Таблица, Зеленый или Синий). Функция AND возвращает TRUE, если значение Product равно «Table», а Color равно «Green» или «Blue». Если TRUE, то функция ЕСЛИ снижает цену на 50%, если FALSE, то ЕСЛИ функция снижает цену на 10%.

            Вложенные, если

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

            1. Например, взгляните на вложенную формулу ЕСЛИ в ячейке C2 ниже.

            Объяснение: если оценка равна 1, вложенная формула ЕСЛИ возвращает значение Плохо, если оценка равна 2, вложенная формула ЕСЛИ возвращает значение Хорошо, если оценка равна 3, вложенная формула ЕСЛИ возвращает значение Отлично, в противном случае возвращается значение Недействительно . Если у вас Excel 2016 или более поздней версии, просто используйте функцию IFS.

            2. Например, взгляните на вложенную формулу ЕСЛИ в ячейке C2 ниже.

            Объяснение: если оценка меньше 60, вложенная формула ЕСЛИ возвращает F, если оценка больше или равна 60 и меньше 70, формула возвращает D, если оценка больше или равна 70 и меньше 80, формула возвращает C, если оценка больше или равна 80 и меньше 90 формула возвращает B, иначе она возвращает A.

            Подробнее о If

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

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

            Объяснение: функция И возвращает ИСТИНА, если человек старше 12 и моложе 20 лет, иначе возвращает ЛОЖЬ. Если ИСТИНА, функция ЕСЛИ возвращает Да, если ЛОЖЬ, функция ЕСЛИ возвращает Нет.

            2. Вы можете комбинировать ЕСЛИ со СРЗНАЧ, СУММ и другими функциями Excel. Небо это предел!

            Объяснение: функция И возвращает ИСТИНА, если входное значение больше 100 и среднее значение значений в именованном диапазоне Данные1 больше 100, в противном случае она возвращает ЛОЖЬ. Если TRUE, функция ЕСЛИ возвращает сумму данных2, если FALSE, функция ЕСЛИ возвращает 0.

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

            3. Например, выберите ячейку G3 выше.

            4. На вкладке «Формулы» в группе «Аудит формул» нажмите «Оценить формулу».

            5. Нажмите Оценить несколько раз.

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

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

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

            Уровень мастерства: Новичок

            Посмотреть учебник

            Посмотреть на YouTube и подписаться на наш канал

            Скачать файл Excel

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

            Объяснение функции IF.xlsx

            Освоение функции IF

            Функция IF — важная функция, которую необходимо знать. Это может помочь при сравнении данных, выполнении поиска по групповым данным и создании интерактивных отчетов 9.0006 . Если вы не знакомы с IF или хотели бы освежить знания, этот пост поможет.

            Вот сигнатура функции ЕСЛИ:

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

            Функция ЕСЛИ имеет 3 аргумента :

            1. 900. Здесь мы можем сравнить данные или посмотреть, выполняется ли условие.
            2. Значение, если оно истинно. Определение этого аргумента сообщает Excel о необходимости возврата определенного значения, если выполняется условие в логической проверке.
            3. Значение, если ложь . Если условие не выполняется, этот аргумент определяет, каким будет возвращаемое значение.

            Вот пример. Я сравниваю сумм дохода с суммами цели . Если доход на больше или равен цели, я хочу, чтобы в столбце D было написано Да , а если нет, то Нет .

            Давайте поговорим о самой важной части функции ЕСЛИ: логическом тесте.

            Логические тесты

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

            99

            94

            .0194
            Оператор Описание
            =, равное
            <>
            <>
            <>
            < Менее
            > = ПРЕДОСТАВЛЯЕТ, чем или равны
            <=. Это важно, потому что логическому тестовому аргументу в IF требуется возвращаемое ему значение TRUE или FALSE (логическое). Это также важно, потому что вам может понадобиться , а не , даже функция ЕСЛИ, если значения ИСТИНА/ЛОЖЬ могут быть использованы для получения ответа, который вы ищете.

            Также важно отметить, что регистр не имеет значения при сравнении текста. Следующая формула вернет TRUE.

            = «Excel Campus» = «excel campus»

            Возврат других значений

            Хотя для моего примера, вероятно, было бы достаточно «ИСТИНА» и «ЛОЖЬ», я предпочитаю, чтобы они говорили «Да» и «Нет» в ответ на вопрос в заголовке столбца: «Попал в цель?».

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

            Аргументы value_if_true и value_if_false могут содержать что угодно:

            • Число
            • Текст в двойных кавычках — «hello»
            • Ссылка на другую ячейку — A2
            • Формула с другой функцией ЕСЛИ.
            • Формула с любой другой комбинацией функций.

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

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

            Например, следующая формула вернет ЛОЖЬ, если A2 НЕ равно B2, потому что аргумент значение_если_ложь не указан.

            =ЕСЛИ(A2=B2, "Сопоставление")

            Группировка данных

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

            На этом листе я хочу классифицировать транзакции как Крупные или Малые в зависимости от суммы, которую я ввел в ячейку D4. Сделка менее $60 000 будет помечен как small . Транзакции больше или равные $60,000 будут большими .

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

            символов доллара ($) в формуле делают ссылки абсолютными , поэтому они не изменяются при копировании формулы в ячейки под ней. Когда выбрана ссылка на ячейку в формуле, вы можете нажать F4 на клавиатуре (для Fn + F4 для большинства клавиатур ноутбуков), чтобы вставить символ доллара перед номером строки и буквой столбца . (Повторное нажатие переключает его на одно или другое и обратно на отсутствие или на оба.)

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

            Множественные логические тесты

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

            Функция И

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

            В приведенном ниже примере наша формула ищет транзакции, которые являются одновременно «Крупными» и «Продуктом 6».

            Функция ИЛИ

            Возможно, вы хотите показать положительный результат, если только ОДИН из логических тестов в вашей формуле верен, а не ОБА . В этом случае вы можете использовать функцию ИЛИ .

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

            Используя тот же лист, что и выше, формула в нашем примере ниже ищет записи с размером «Большой» ИЛИ , если название продукта «Товар 5».

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

            Вложенные формулы ЕСЛИ

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

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

            Например, допустим, у нас есть три категории счетов под названием Small , Большой и Ключ . Малый счет меньше 10000 долларов . Крупный счет — от 10 000 до 70 000 долларов . И ключевой счет больше, чем 70000 долларов .

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

            Логика формулы такова.

            1. Если сумма больше или равна 70 000 долларов США, верните значение «Ключ».
            2. Но если это НЕ так, оцените следующую функцию ЕСЛИ.
            3. Если сумма больше или равна 10 000 долларов США, верните значение «Большой».
            4. Но если это НЕ, верните значение «Мелкий».

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

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

            Альтернатива 1: функция IFS

            Это относительно новая функция , представленная в Excel 2019. Несмотря на то, что она по-прежнему требует большого выбора и логических тестов, ее несколько легче читать и писать формулу IFS по сравнению с вложенные формулы ЕСЛИ.

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

            Альтернатива 2: Формулы поиска

            Вы можете использовать VLOOKUP, XLOOKUP или Index Match для возврата тех же результатов, что и вложенные ЕСЛИ. Их намного проще написать. Чтобы узнать, как написать формулу ВПР, подобную этой, перейдите к этому руководству:

            Как использовать ВПР для поиска ближайшего совпадения — последний аргумент ИСТИНА

            Распространенные ошибки с функцией ЕСЛИ

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

            Читайте также: