Cd-m.ru

ПК Журнал СД-М
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

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

Браузер не поддерживает видео.

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

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

Имя аргумента

лог_выражение (обязательно)

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

значение_если_истина (обязательно)

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

значение_если_ложь (необязательно)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

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

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

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

Например:
=ЕСЛИ(A1<>””;1;0)
<> – не равно,
“” – пустая.
То есть, если ячейка А1 не пустая, возвращается 1, если пустая, то 0.
При этом не важно будет ли в ячейке А1 число, текст или любые другие знаки.

Допустим нам нужно, за заказ свыше 15000 руб применить скидку 10%.
В поле Лог_выражение напишем условие: сумма заказа(В2) больше 15000. Пропишем в Значениe_если_истина В2*0,9, а в Значениe_если_ложь вернем сумму заказа (В2).

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

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

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

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

Часто бывает, что необходимо применить более 1 условия.
Допустим нам надо при сумме заказа свыше 15000 руб, сделать скидку 10%, а свыше 20000 руб 15%.

Excel воспринимает до 7 уровней вложения функций и проверяет условия начиная с первого.

Например, мы напишем формулу неправильно:
=ЕСЛИ(B2>15000;B20,9;ЕСЛИ(B2>20000;B20,85;B2))

То есть если мы напишем первым условием В2>15000, то для суммы свыше 20000, скидка 15% применяться не будет.

Например, для ячейки В4 проверится первое Лог_выражение (B4>15000), Excel увидит, что это выражение верно и применит скидку 10%.

Ошибка в построении функции ЕСЛИ

Поэтому начинать будем с суммы заказа больше 20%.

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

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

Лог_выражение напишем В2>20000
Значение_если_истина – В2*0,85
В пустое поле Значение_если_ложь необходимо встать курсором и в поле имени (слева от строки формул) выбрать функцию ЕСЛИ.

Читать еще:  Почему с карты памяти не удаляются файлы

Построение вложенных функций ЕСЛИ

При этом откроется новое окно, но начало формулы не пропадет.

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

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

Построение функции ЕСЛИ с 2-мя условиями с помощью мастера функций

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

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

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

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

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

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

Появятся аргументы второго условия ЕСЛИ.
Стереть поле Лог_выражение (В2>15000)

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию ИЛИ.

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

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

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

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

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

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

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

Скидки за заказ оставим на прежнем уровне:
Заказ более 20000 – 15%
Заказ более 15000 – 10%

Но если заказ более 15000 и оплачен более чем на 50% – скидка 20%

Для наглядности занесем все наши условия в таблицу

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

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

В появившемся окне стираем Лог_выражение: B2>15000.

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию И.

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

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

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

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

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

При нажатии на ОК окно закроется.

Мы прописали только первую строчку (Лог_выражение) в нашем втором условии ЕСЛИ. Снова встаем на 2-ую ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

В первой строке (Лог_выражение) уже прописаны 2 наших условия.
Значение_если_истина исправляем 0,9 на 0,8, чтобы при соблюдении 2-х условий Excel считал скидку 20%
Значение_если_ложь стираем B2 (будем прописывать в нее 3-е условие).

Встаем курсором на Значение_если_ложь и в поле имени выбираем функцию ЕСЛИ.

Появится окно в которое мы будем прописывать 3-е условие.

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

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

Если мы пропишем 2-м условием Заказ более 15000 – 10%, а 3-м выполнение 2-х условий, Excel определит 2-е условие как истина и 3-е условие проверять не будет.

Оператор “ЕСЛИ” в программе Microsoft Excel: применение и примеры

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

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

Содержание: Функция “ЕСЛИ” в Excel

Определение функции “ЕСЛИ” и ее назначение

Оператор «ЕСЛИ» – это инструмент программы Эксель для проверки определенного условия (логического выражения) на выполнение.

То есть, представим, что у нас есть какое-то условие. Задача “ЕСЛИ” – проверить соблюдение данного условия и вывести в ячейку с функцией значение, основанное на результате проверки.

  1. Если логического выражение (условие) выполняется, то значение соответствует истине.
  2. Если логического выражение (условие) не выполняется, значение является ложным.

Сама формула функции в программе представляет собой следующее выражение:

=ЕСЛИ(условие; [значение если условие выполнено]; [значение если условие не выполнено])

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

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

Читать еще:  Ошибка «Невозможно выполнить запрос, произошел программный сбой» в Photoshop

У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования.

Использование функции

Наша задача — в столбце “Скидка” вывести значение «25%» для всех строк с наименованиями для женского пола. И соответственно, значение «0», если в столбце «Пол» стоит значение «мужской»

Использование функции

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

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

=ЕСЛИ(B2=”женский”;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%.

  1. Ставим курсор на нужную ячейку, заходим во вкладку “Формулы”, далее щелкаем “Вставить функцию”.Как задать функции ЕСЛИ через Построитель формул
  2. В открывшемся списке Построителя формул выбираем “ЕСЛИ” и нажимаем “Вставить функцию”.Как задать функции ЕСЛИ через Построитель формул
  3. Открывается окно настройки функции. Как задать функции ЕСЛИ через Построитель формулВ поле “логическое выражение” пишем условие, по которому будет проводиться проверка. В нашем случае это “B2=”женский”.
    В поле “Истина” пишем значение, которое должно отобразиться в ячейке, если условие выполняется.
    В поле “Ложь” – значение при невыполнении условия.
  4. После того, как все поля заполнены, жмем “Готово”, чтобы получить результат.Как задать функции ЕСЛИ через Построитель формул
Читать еще:  Как узнать модель материнской платы на ноутбуке: все способы

Заключение

Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ, которая проверяет данные на совпадение заданным нами условиям и выдает результат в автоматическом режиме, что исключает возможность ошибок из-за человеческого фактора. Поэтому, знание и умение применять этот инструмент позволит сэкономить время не только на выполнение многих задач, но и на поиски возможных ошибок из-за “ручного” режима работы.

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

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

Раздел функцийЛогические
Название на английскомIF
ВолатильностьНе волатильная
Похожие функцииЕСЛИОШИБКА, ЕОШ, ЕОШИБКА, ЕНД

Что делает эта функция?

Функция проверят истинность первого аргумента — вычисления или значения ячейки. В зависимости от результата будут выполнены вычисления или возвращены другие значения.

Синтаксис

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

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

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

Другие особенности функции ЕСЛИ:

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

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

Пример 1

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

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

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

Для проверки вхождения цифр в строку мы воспользуемся функцией ПОИСК.

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

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

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

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

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

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

В версях Excel до 2016 отсутствуют функции МАКСЕСЛИ и МИНЕСЛИ. Но нам ничто не мешает создать их аналог с помощью формул массива.

Допустим, мы хотим посчитать наибольшее и наименьшее четное число в диапазоне A1:A10.

Используем на массиве функцию ЕЧЁТН (Появилась в Excel 2007) и будем возвращать само число, если оно чётное, и пустую строку, если нечётное. Итоговый массив обработаем функциями МАКС и МИН:

А если у нас Excel 2003 и функция ЕЧЁТН для нас тоже недоступна, используем функцию ОСТАТ, проверяя остаток от деления на 2:

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector