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

Формула Эксель ЕСЛИ ячейка содержит текст то значение равно (условия с текстом)

Автор Амина С. На чтение 5 мин Опубликовано 29.01.2021

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

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

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

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

Содержание

1. Проверка условия для полного совпадения текста (ЕСЛИ + СОВПАД)
2. Функция ЕСЛИ с частичным совпадением текста
3. ЕСЛИ + ПОИСК
4. ЕСЛИ + НАЙТИ

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

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

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

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

Не стоит забывать заключать все текстовые значения в кавычки.

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

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

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

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

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

ЕСЛИ + ПОИСК

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

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

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

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

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

ЕСЛИ + НАЙТИ

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Завершился чемпионат мира по Excel / Хабр

На спортивном канале ESPN3 и в прямом эфире на YouTube завершилась трансляция Кубка мира по финансовому моделированию в программе Microsoft Excel. Его в прямом эфире смотрели несколько сотен человек, а финалисты разделили призовой фонд $10 тыс. от компании Microsoft.

Хотя мероприятие немного уступает по размеру призового фонда чемпионатам по Dota 2 и Fortnite, но не менее зрелищное.

В борьбу за мировую корону вступили 128 мастеров электронных таблиц во всего мира. Квалификация началась 13 ноября 2021 года, и после нескольких отборочных раундов на выбывание двое лучших встретились в финале 11 декабря.

Кубок FMWC (Financial Modeling World Cup) проводится ежегодно, на официальном сайте ведётся подробная статистика, обновляется текущий мировой рейтинг и топ атлетов из каждого региона. В этом виде спорта от России зарегистрировано три спортсмена, которые в данный момент занимают 21, 55 и 104 места в мировой классификации.

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

В финальном бою аналитик из Австралии Эндрю Нгай (Andrew Ngai), 2-й номер мирового рейтинга, вынес канадского профессионала Майкла Джармана (Michael Jarman), 5-го номера мирового рейтинга.

Финальная игра называлась Knights & Warriors (аналог пошаговой стратегии Heroes of Might and Magic). Это симуляция войны между вымышленными нациями Winsia и Loblenia, которые отправляют в бой друг против друга солдат, рыцарей и лучников.

Правила. У каждого юнита набор характеристик: очки здоровья (HP), атаки и стрельбы (у лучников). В каждом раунде каждая армия одновременно бьёт по врагу, а урон вычисляется как произведение очков атаки и количества юнитов. Удар сначала идёт по солдатам, потом по рыцарям, потом по лучникам. Например, DMG в 1000 очков означает, то 33 пехотинца убиты (33×30 HP), а один ранен.

Десять вопросов первого уровня выглядят так: «Сколько HP у армии Winsia в начале битвы, если они начинают со следующим количеством бойцов?» — и десять вариантов количества бойцов (по 5 очков за каждый вариант). Нужно написать правильную формулу в итоговую ячейку.

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

Финал продолжался 30 минут. К сожалению, канадский аналитик Майкл Джарман так и не сумел понять изъян на третьем этапе своей стратегии расчёта DMG, что привело к сокрушительному итоговому поражению со счётом 734:280.

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

…тогда как Эндрю к тому моменту уже составил сложнейшую промежуточную таблицу.

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

В следующем году призовой фонд FMWC вырастет до $25 тыс., принять участие в соревновании приглашаются все желающие.

Условные функции — E 115: Введение в вычислительные среды

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

Примеры:

• A3>14 Содержимое ячейки A3 больше числа 14
• D5<=2 Содержимое ячейки D5 меньше или равно числу 2
• T47=»сыр» Содержит ли ячейка T47 слово сыр

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

ЕСЛИ

=если( условие , значение_если_истина, значение_если_ложь )

Если данное условие истинно, в ячейке будет установлено значение «значение_если_истина». Если условие ложно, в ячейке будет установлено значение «value_if_false», если «value_if_false» оставить пустым, по умолчанию в ячейке будет установлено значение «FALSE».

Пример:

=if(A2>3,32,»Слишком маленькое число»)

Читает: если A2>3 верно, установите ячейку на 32. В противном случае установите для ячейки значение «Слишком маленькое число»

Вложенные функции ЕСЛИ

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

Пример:
Проверяет, находится ли содержимое ячейки A2 в пределах от 1 до 3.

=if (A2>3, «Слишком большое число», if (A2<1, "Слишком маленькое число", "В самый раз!"))

Завершенная функция проверит, больше ли значение A2, чем 3. Если это так, активная ячейка будет установлена ​​на «Слишком большое число». Если A2 меньше 3, будет проверено, не меньше ли значение A2 1. Если да, то для активной ячейки установлено значение «Слишком маленькое число». В противном случае мы знаем, что значение A2 должно быть между 1 и 3, поэтому для активной ячейки установлено значение «В самый раз!»

СЧЁТЕСЛИ

=счетчик(диапазон,критерий)

Функция «countif» будет увеличивать активную ячейку на единицу каждый раз, когда критерий верен для ячейки в заданном диапазоне.

Пример:
Вы хотите узнать, в скольких университетах одним из школьных цветов является «красный», и составили базу данных школ и их цветов в Excel.

=счетчик (F1:F230, «=красный»)

Если ячейка в F1:F230 удовлетворяет «=red», то увеличивается активная ячейка на единицу.

СУММФ

=sumif(диапазон,критерий,сумма_диапазон)

Функция «sumif» использует диапазон и соответствующий диапазон_суммирования одинакового размера. Каждый раз, когда критерий истинен, активная ячейка будет увеличиваться на значение в соответствующей ячейке в диапазоне суммы. Если «диапазон_суммы» не указан, Excel также будет рассматривать «диапазон» как «диапазон_суммы».

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

=sumif (D1:D230″,=телевидение», h2:h330)

Если ячейка в D1:D230 удовлетворяет «=телевидение», тогда активная ячейка увеличивается на соответствующую ячейку от h2:h330. Если ячейка D7 содержит слово «телевидение», активная ячейка будет увеличена по сравнению с соответствующей ячейкой из h2:h330 или ячейкой H7.

И и ИЛИ

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

=И(условие, условие)
=ИЛИ(условие, условие)

Критерии и условия

В Excel есть разница между критерием и условием. Условие — это когда есть оператор (например, =, <, >= и т. д.), который имеет значение с обеих сторон (например, A2 <= 3). Критерий — это когда есть оператор и только одно значение (справа от оператора) со всеми критериями в кавычках (например: «=15»).

Условное форматирование: операторы If в Excel

Тепинг Крокер

Категории: Условное форматирование, Excel®
Метки: If/Then Условное форматирование

*Действия в этой статье относятся к Excel 2007–2016. Изображения были сделаны с использованием Excel 2016.

Если вы являетесь поклонником функции условного форматирования Excel, вы, вероятно, найдете все больше и больше способов выделить полезную информацию в ваших данных. Среди этих «зависимых от условного форматирования» часто возникает вопрос: Могу ли я использовать формулу If/Then для форматирования ячейки?

Ответ и да и нет. Любой аргумент условного форматирования должен генерировать результат TRUE, а это означает, что на буквальном уровне ваше правило условного форматирования представляет собой оператор If/Then типа «Если это условие TRUE, THEN отформатируйте ячейку таким образом».

То, что условное форматирование не может сделать в одном правиле , — это условие IF/THEN/ELSE, такое как «Если # больше 10, форматировать красным, иначе форматировать зеленым». Вместо этого для этого потребуются ДВА правила: одно для «больше 10» и одно для «меньше 10».

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

Чтобы следовать нашим примерам, загрузите 04-If-Then Conditional Formatting . xlsx

Сценарий 1 (вкладка «Дни рождения») : Вы хотите выделить всех сотрудников вашего отдела, у которых день рождения в этом месяце, красным цветом, а все остальные отделы — синим.

Решение: Создайте два правила — одно для вашего отдела, одно для всех остальных

Шаг 1. Выделите дни рождения в вашем отделе

Формула для определения дней рождения в текущем месяце будет следующей (дополнительные сведения об использовании дат в условном форматировании см. в этой статье):