Функция если в excel. примеры (с несколькими условиями)

Примеры функций И, ИЛИ

Также при использовании в Экселе функции ЕСЛИ есть возможность соединить ее с простыми операторами сравнения (вроде И, ИЛИ). Например, можно написать такое условие: =ЕСЛИ(И(A1 3); «проходит», «не проходит»). Получается следующее условие: если оценка студента меньше или равна 5 и больше 3, то будет показан комментарий «проходит», в противном случае — «не проходит» (проходят только те студенты, у которых в оценках стоят четверки и пятерки).

Более сложный пример использования функции ЕСЛИ — с использованием условий И либо ИЛИ

Следующее условие с примером оператора ИЛИ: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). Получается, что если число в ячейке А1 равняется 5 или 10, то программа выдаст 100, в противном случае — 0.

Кроме того, можно применять эти простые операторы и для решения более сложных задач. Например, в базе данных необходимо отобрать должников, которые должны более 10 тысяч рублей и уже не платили более 6 месяцев. Если программа найдет такого человека, то пусть выдаст критерий «проблемный заемщик».

Допустим, в ячейке A1 находится информация о сроке задолженности (в месяцах), а в поле B1 — сумма задолженности. Тогда формула будет выглядеть следующим образом: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный заемщик»; «»). Если будет найден человек, который подходит под указанное условие, то программа напишет напротив его фамилии комментарий «проблемный заемщик», в противном случае ячейка останется пустой.

Если один из параметров считается критическим, тогда можно составить формулу так: =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая ситуация»; «»). Если программа найдет совпадения хотя бы по одному параметру (либо срок, либо сумма задолженности), то пользователь увидит сообщение о том, что ситуация критическая. Разница с предыдущей формулой в том, что в первом случае сообщение «проблемный заемщик» выдавалось только тогда, когда выполнялись оба условия.

Пример 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».

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

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

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

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

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

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

На примере цен товаров напишем простое сравнение, для этого добавим еще один столбец для примечаний,

в ячейку F4 напишем начало формулы «=ЕСЛИ(» и нажмем кнопку Fx. В поле Лог_выражение напишем условие «E4>200», цена больше двухсот, Значение_если_истина напишем «Дорого», Значение_если_ложь «Нормально»,

нажимаем ОК, и скопируем или протянем формулу в диапазоне «F5:F7». Напротив каждой цены появился комментарий, если цена больше двухсот, то это дорого, если меньше, то нормально.

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

В этом видео подробно показано применение функции ЕСЛИ в excel:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Новые статьи

• Как разбить ячейку в эксель, как сделать нормальную таблицу в Excel — 12/06/2021 18:30
• Исправляем ошибку VBA № 5854 слишком длинный строковый параметр в шаблоне word из таблицы excel 255 символов — 21/02/2021 08:54
• База данных из JavaScript для веб страницы из Excel на VBA модуле — 30/11/2019 09:15
• Листы в Excel из списка по шаблону — 02/06/2019 15:42
• Печать верхней строки на каждой странице в Excel — 04/06/2017 17:05
• Создание диаграммы, гистограммы в Excel — 04/06/2017 15:12
• Функция СИМВОЛ в Excel или как верстать HTML в Excel — 03/06/2017 17:32
• Функция ЕСЛИОШИБКА в excel, пример использования — 20/05/2017 11:39
• Как использовать функцию МИН в excel — 20/05/2017 11:36
• Как использовать функцию МАКС в excel — 20/05/2017 11:33
• Как использовать функцию ПРОПИСН в excel — 20/05/2017 11:31
• Как использовать функцию СТРОЧН в excel — 20/05/2017 11:29
• Как использовать функцию СЧЕТЕСЛИ в excel — 20/05/2017 11:26
• Как использовать функцию Функция СЧЁТ в excel — 20/05/2017 11:09
• Как использовать функцию ПОИСК в эксель — 10/03/2017 21:28
• Как использовать функцию СЦЕПИТЬ в эксель — 10/03/2017 20:41
• Как использовать функцию ПРАВСИМВ в excel — 10/03/2017 20:35
• Как использовать функцию ЛЕВСИМВ в excel — 06/03/2017 16:04
• Как использовать функцию ЗАМЕНИТЬ в excel — 28/02/2017 18:44
• Как использовать функцию ДЛСТР в эксель — 25/02/2017 15:07

Предыдущие статьи

• Как использовать функцию СУММЕСЛИ в Excel — 22/02/2017 19:08
• Как использовать функцию СУММ в эксель — 20/02/2017 19:54
• Печать документа в Excel и настройка печати — 16/02/2017 19:15
• Условное форматирование в ячейках таблицы Excel — 16/06/2016 17:38
• Объединить строку и дату в Excel в одной ячейке — 16/06/2016 17:33
• Горячие клавиши в Microsoft Office Excel — 04/06/2016 14:57
• Как использовать эксель в качестве фотошопа — 04/06/2016 09:01
• Как разделить текст по столбцам, как разделить ячейки в Excel — 14/04/2016 16:19
• Как применить функцию ВПР в Excel для поиска данных на листе — 08/01/2016 23:40
• Как создать таблицу в Excel, оформление таблицы — 06/01/2016 20:29
• Работа в эксель, как начать пользоваться Excel — 26/12/2015 15:48

Функция объединения текста в Excel

Для объединения значений из нескольких ячеек в одну строку используется оператор амперсанд (&) или функция СЦЕПИТЬ.

Например, значения расположены в разных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова — &. И так последовательно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

Использование функции СЦЕПИТЬ:

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

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

Сложности

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

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

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

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

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

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

Однако делать этого не рекомендуется даже при хорошем владении программой.

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

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

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

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

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

Пример 2

Функции ОБЪЕДЕНИТЬ.

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

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

Пропускать_пустые. Здесь можно поставить значение аргумента функции один (1). Тогда пустые ячейки, которые находятся в диапазоне объединяемых ячеек, не будут учитываться (не отразятся в объединённых данных). Если поставить значение аргумента функции ноль (0), тогда функция будет учитывать пустые ячейки.

Текст 1, Текст 2 и т.д. Здесь необходимо указать диапазон ячеек или ссылки на ячейки, данные в которых необходимо объединить. Так же, здесь можно указывать другие данные (текст, числа и т.д.) При необходимости нужно добавлять пробелы. Максимальное количество данных полей — 252.

Рассмотрим данную функцию на примере. У нас есть данные в ячейках В2; С2 и Е2. Укажем их как диапазон в поле Текст1. В этот диапазон попадет пустая ячейка D2. В поле Пропускать_пустые, поставим «1». Функция не будет учитывать данную пустую ячейку. В поле Разделитель, вставим тире (-).

Кавычки в поле Разделитель появляются автоматически. Нажимаем ОК.

Теперь в поле Пропускать_пустые, поставим «0». Остальные аргументы оставим без изменения.

Функция учла пустую ячейку D2, и заключила ее в тире (-).

Функция ЕСЛИ СОДЕРЖИТ

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

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

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

У нас есть множество строк с документами Реализации и Возвратов .

Все документы имеют свое наименование за счет уникального номера .

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

Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .

Для того, чтобы это сделать, необходимо:

1. 1. Начинаем с ввода функции

ЕСЛИ

1. 1. (вводим

«=»

1. 1. , набираем наименование

ЕСЛИ

1. 1. , выбираем его из выпадающего списка, нажимаем

fx

1. 1. в строке формул).

В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.

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

Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»

• Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
• Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

• В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « — »
• Далее протягиваем формулу до конца таблицы и подключаем сводную.

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

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

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

Функция ЕСЛИ СОДЕРЖИТ

EXCEL«СОДЕРЖИТ»кусок словачастьнестандартном заполнении

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

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

У нас есть множество строк с документами Реализации и Возвратов .

Все документы имеют свое наименование за счет уникального номера .

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

Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .

Для того, чтобы это сделать, необходимо:

1. Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).

В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.

СЧЁТЕСЛИ«Критерий»*реализ**

Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»

Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « — »

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

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

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

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

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

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

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

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

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

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

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

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

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

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

<Рис. 1 Окно настройки>

Проверяем условие для полного совпадения текста.

Проверку выполнения
доставки организуем при помощи обычного оператора сравнения «=».

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

Если же вас интересует
именно точное совпадение текстовых значений с учетом регистра, то можно
рекомендовать вместо оператора «=» использовать функцию СОВПАД(). Она проверяет
идентичность двух текстовых значений с учетом регистра отдельных букв.

Вот как это может
выглядеть на примере.

Обратите внимание, что
если в качестве аргумента мы используем текст, то он обязательно должен быть
заключён в кавычки

ЕСЛИ + СОВПАД

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

Но мы можем использовать функцию СОВПАД (английский аналог — EXACT).

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

Формула проверки выполнения заказа в столбце Н может выглядеть следующим образом:

Как видите, варианты «ВЫПОЛНЕНО» и «выполнено» не засчитываются как правильные. Засчитываются только полные совпадения

Будет полезно, если важно точное написание текста — например, в артикулах товаров

Как могут использоваться логические функции на практике

Как использовать функцию ЕСЛИ в Эксель – примеры

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

Соответственно, условием этой задачи будет равенство пола женскому. Если в результате проверки обнаруживается, что данный критерий истинный, то в место, куда выводится эта формула, нужно написать размер скидки – 25%. Если же ложный – указать значение 0, поскольку дискаунт не предусмотрен в этом случае.

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

Чтобы достичь поставленной на первом этапе цели, необходимо выбрать ячейку, где будет осуществляться вывод получившегося значения и записать следующую формулу: =ЕСЛИ(B2=”женский”;25%;0). Давайте расшифруем эту функцию:

1. ЕСЛИ – это непосредственно оператор.
2. B2=”женский» – это критерий, соответствие которому проверяется.
3. Далее следует то значение, которое будет выводиться в случае, если кроссовки созданы для женщин и значение, показываемое если обнаружится, что кроссовки мужские, детские или любые другие, не соответствующие заданному в первом аргументе условию.

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

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

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

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

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

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

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

Рассмотрим главные аргументы более подробно:

1. Диапазон суммирования. Ячейки, которые будут суммироваться.
2. Диапазон условия 1 – диапазон, который будет проверяться на предмет соответствия определенному критерию. 
3. Условие 1 – непосредственно условие.
4. Диапазон условия 2 – второй диапазон, который будет проверяться на предмет соответствия критерию.
5. Условие 2 – второе условие.

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

13