Есть ли в excel

Обновлено: 07.07.2024

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

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

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

Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.

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

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

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

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

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

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

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

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

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

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

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

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Примеры

Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.

Ниже приведены формулы с расшифровкой их логики.

Если A2 (25) больше нуля и B2 (75) меньше 100, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба условия имеют значение ИСТИНА, поэтому функция возвращает значение ИСТИНА.

Если A3 ("синий") = "красный" и B3 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только одно условие имеет значение ИСТИНА, поэтому возвращается значение ЛОЖЬ.

Если A4 (25) больше нуля или B4 (75) меньше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только первое условие имеет значение ИСТИНА, но поскольку для ИЛИ требуется, чтобы только один аргумент был истинным, формула возвращает значение ИСТИНА.

Если значение A5 ("синий") равно "красный" или значение B5 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае второй аргумент имеет значение ИСТИНА, поэтому формула возвращает значение ИСТИНА.

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 ("синий") НЕ равно "красный", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

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

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

Если A3 больше B2 И меньше C2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба аргумента истинны, поэтому формула возвращает значение ИСТИНА.

Если A4 больше B2 ИЛИ меньше B2+60, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае первый аргумент равен ИСТИНА, а второй — ЛОЖЬ. Так как для оператора ИЛИ требуется, чтобы один из аргументов был истинным, формула возвращает значение ИСТИНА. Если вы используете мастер вычисления формул на вкладке "Формулы", вы увидите, как Excel вычисляет формулу.

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Использование операторов И, ИЛИ и НЕ с условным форматированием

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

На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.

Вот как будут выглядеть формулы для примеров с датами:

Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A3 больше B2 И меньше C2, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A4 больше B2 ИЛИ меньше B2 + 60, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.

Примечание: Распространенная ошибка — ввод формулы в условное форматирование без знака "равно" (=). При этом вы увидите, что в диалоговом оке Условное форматирование в формулу будут добавлены знаки "равно" и кавычка: ="ИЛИ(A4>B2;A4<B2+60)",поэтому вам потребуется удалить кавычка, чтобы формула правильно отреагирует.

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

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

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

Поисковая функция в Excel

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

Способ 1: простой поиск

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

Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.

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

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

Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».

Способ 2: поиск по указанному интервалу ячеек

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

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

«Найти и заменить»

«Параметры»

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

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

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

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

Ещё более точно поиск можно задать, нажав на кнопку «Формат».

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

Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

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

После того, как формат поиска настроен, жмем на кнопку «OK».

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Найдите, если значение находится в списке с помощью Kutools for Excel

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

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

Если есть сопоставленные значения, он перечислит соответствующие значения в списке ниже, см. Снимок экрана:

Но если значение не найдено, появится диалоговое окно с напоминанием. Смотрите скриншот:

Выберите ячейки / строки / столбцы с одним или двумя критериями в Excel

Найдите, есть ли значение в списке с формулой

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

Выберите пустую ячейку, вот C2, и введите эту формулу = ЕСЛИ (ЕЧИСЛО (ПОИСКПОЗ (B2; A: A; 0)); 1,0) в него и нажмите Enter ключ для получения результата, и если он отображает 1, указывает, что значение находится в списке, а если 0, то его не существует.

Наконечник:

2. В приведенных выше формулах A: A - это список столбцов, в котором вы регистрируете значение, а B2 - это значение, которое вы хотите найти, если оно есть в списке.

Найдите, если значение находится в списке с помощью Kutools for Excel

Если у вас есть Kutools for Excel, С Выбрать определенные ячейки утилита, вы можете быстро проверить, есть ли значение в списке, и в то же время найти значение.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите список, из которого вы хотите проверить определенное значение, и нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:

2. в Выбрать определенные ячейки диалоговое окно, выберите Равно из первого раскрывающегося списка в Конкретный тип раздел, а затем введите значение, которое вы хотите проверить, и найдите в следующем текстовом поле. Смотрите скриншот:

3. Нажмите Ok, и появится диалоговое окно, в котором будет указано количество совпавших ячеек. И нажмите OK, вы увидите, что значение, которое вы проверяете, было выбрано. Смотрите скриншот:

Наконечник: Если проверяемого значения нет в списке, после нажатия Ok, появится диалоговое окно, в котором сообщается, что математики значений не существует. Смотрите скриншот:

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

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

Функцией ЕСЛИ в Excel, первый пример.

Рассмотрим простой пример применения функции ЕСЛИ в MS Excel. У нас есть столбец №1, в котором содержаться числа от 1 до 10.

Необходимо проверить, соответствует ли числа находящиеся в столбце №1 нашему критерию. Критерий: является ли числа находящиеся в столбце №1 числами, значения которых больше числа 4.

Взываем функцию ЕСЛИ в ячейку С3.

В диалоговом окне Аргументы функции видим три поля для заполнения:

Нажимаем ОК в диалоговом окне Аргументы функции.

Как видим, число содержащиеся в ячейки В3 не является числом, которое больше числа 4. Функция ЕСЛИ вернула значение_если_ложь (неправда). Протянем функцию ЕСЛИ вниз по столбцу, чтобы проверить значения находящиеся в остальных ячейках столбца №1, на соответствие нашему критерию (логическое_выражение).

Как видно из результата, ячейки в диапазоне В7:В12 содержат в себе числа, которые больше числа 4. Функция ЕСЛИ вернула значение_если_истина (правда).

Функция ЕСЛИ в Excel, второй пример.

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

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

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

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

Видим, что в зависимости от результатов сдачи зачета, студент допущен к экзамену, либо не допущен.

Обращаю внимание, что при работе с функцией ЕСЛИ, можно использовать разные варианты равенств: больше (>), меньше(<), больше или равно (>=), меньше или равно (<=), равно (=), не равно (<>).

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

Предположим, что у нас есть таблица с результатами сдачи контрольной работы студентами ВУЗа. Результаты контрольной работы оцениваются в баллах. От 0 до 100. Где все, что выше 90 баллов, отлично. Выше 80 баллов, это хорошо. Выше 70 баллов, это удовлетворительно. Ниже 70 баллов, это плохо.

Формула функции ЕСЛИ будет выглядеть вот так:

Где ячейка С3, первая из проверяемых ячеек в столбце Оценка. Далее функция протянута вниз по столбцу Результат, до конца таблицы.

Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.

Таблица выглядит вот так:

В столбце Результат, в зависимости от количества баллов, функция ЕСЛИ вернула значение, согласно заданному критерию в формуле функции.

Использование операторов «И» и «ИЛИ» в функции ЕСЛИ в MS Excel.

Формула функции ЕСЛИ выглядит вот так:

Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.

Формула функции ЕСЛИ выглядит вот так:

Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.

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