Eurotehnik.ru

Бытовая Техника "Евротехник"
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция Эксель ЕСЛИ: учимся ставить условия

Вы часто спрашиваете меня, как выглядит формула Эксель «Если то», сегодня отвечаю на ваши вопросы. Действия с условием выполняются функцией ЕСЛИ:

=ЕСЛИ(условие ; значение если истина ; значение если ложь)

Разбираемся с аргументами:

  • Условие – логическое выражение, которое будет проверять функция. Здесь можно использовать операторы:
    • = — равно
    • <> — не равно
    • < — меньше
    • > — больше
    • <= — меньше или равно
    • >= — больше или равно

    Кроме того, можете применять функции И() и ИЛИ().

    • Значение если истина – значение, которое вернет формула, если выполнится условие
    • Значение если ложь – значение, которое вернет формула, если условие не выполнится

    Например, формула =ЕСЛИ(A1=100;«Готово»;«В процессе») выведет в ячейку слово «Готово», если в А1 содержится число 100. Или выведет «В процессе», когда в А1 не 100.

    Функция И() нужна для проверки двух условий одновременно, перечисленных через точку с запятой. Например, формула =И(A1>10;B1<30) вернет значение ИСТИНА только тогда, когда выполнятся оба условия: значение в ячейке А1 будет больше 10, а в ячейке B1 – меньше 30.
    Функция ИЛИ() вернет истину, когда выполнится хотя бы одно из условий. То есть, формула =ИЛИ(A1>10;B1<30) даст истину, когда выполнится или первое условие, или второе, или оба.

    Теперь практикуемся. На основе одной таблицы рассмотрим несколько примеров. Пусть у нас есть перечень студентов и их оценки за экзамен.

    Функция IF

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

    Условие записывается в виде логического выражения, состоящего из правой и левой части, связанного логическими операторами сравнения: больше (>), меньше (<), равно (=), неравно (<>), а также >= и <=. Результат сравнения показывает, выполняется или нет это условие. В случае выполнения условия отрабатывает алгоритм ИСТИНА, в противном случае – ЛОЖЬ.

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

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

    Используем несколько условий

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

    Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку: Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60. Оценку Хорошо можно будет получить, набрав от 45 до 60 балов. Ну и в остальных случаях ставим Плохо.

    Измененное использования условия

    1. Для решения этой задачи составим формулу, включив в нее необходимые критерии оценивания: =ЕСЛИ (В3>60;»Отлично»;ЕСЛИ(B2>45;»Хорошо»;»Плохо»)).
    2. Эта формула использует одновременно два условия. Первая проверка В3>60. Если баллы действительно больше 60, то в поле Результата мы получаем Отлично, и дальнейшая проверка условий не выполняется. Если Баллы меньше 60, то срабатывает вторая часть формулы, и мы проверяем В3>45 или нет. Если все верно, то возвращается значение «Хорошо», иначе «Плохо».
    3. Затем достаточно скопировать формулу во все ячейки столбца и увидеть результат сдачи переаттестации.

    Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить, что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.

    Синтаксис

    Первый аргумент – заданное условие (числовая или текстовая ячейка), благодаря которому принимается решение. Обязательно должно быть в виде вопроса, на который возможны только два варианта ответа (да или нет).

    Второй аргумент – какой результат появится в ячейке (число или текст), когда значение отвечает указанному условию (да – ИСТИНА).

    Третий аргумент – какой результат появится в ячейке (число или текст), когда значение не отвечает указанному условию (нет – ЛОЖЬ).

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

    Цель этого руководства — показать, как объединить функции IF и AND в Excel, чтобы проверить несколько условий в одной формуле. Некоторые предметы в природе конечны. Другие, кажется, обладают бесконечным потенциалом, и, что интересно, функция IF, похоже, обладает такими свойствами.

    Наша цель сегодня — увидеть, как и если мы можем объединить IF и функцию AND для одновременной оценки нескольких условий. Итак, во-первых, нам нужно будет построить функцию IF AND, и для этого нам потребуется объединить две отдельные функции — функции IF и AND в одну формулу. Давайте посмотрим, как этого можно достичь:

    ЕСЛИ (И (логическое1, логическое2, …), val_if_true, val_if_false)

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

    Как использовать функцию IF и в Excel?

    Давайте разберемся, как использовать функцию IF AND в Excel, используя несколько примеров.

    Вы можете скачать этот шаблон Excel с функцией IF AND Function здесь.

    Функция Excel IF и AND — Пример № 1

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

    • Если и условие 1, и условие 2 оцениваются как ИСТИНА, то заказ должен быть помечен как «Закрыто».
    • Если какое-либо из условий оценивается как FALSE или оба оценивают как FALSE, то должна быть возвращена пустая строка («»).

    = ЕСЛИ (И (В2 = «доставлено», C2 »»), «Закрыто», «»)

    Функция ЕСЛИ И показана на скриншоте ниже:

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

    Функция Excel IF и AND — Пример № 2

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

    Теперь давайте предположим, что у нас есть номера продаж для определенных заказов в столбце B, и наша задача — пометить суммы, которые меньше, чем 100 долларов США, но больше, чем 50 долларов США. Итак, чтобы решить эту проблему, сначала мы вставим следующую формулу в ячейку C2, а затем перейдем к ее копированию по столбцам:

    = ЕСЛИ (И (В2> 50, В2 <100), «Х», «»)

    Функция Excel IF и AND — Пример № 3

    Если требуется, чтобы мы включили периферийные значения (100 и 50), то нам нужно будет использовать оператор больше или равно (> =) и оператор меньше или равно (<=):

    = IF (AND (B2> = 50, B2 <= 100), «X», «»)

    Теперь, чтобы обработать другие виды периферийных значений без изменения формулы, мы введем максимальное и минимальное числа в двух разных ячейках и предоставим ссылку на эти ячейки в нашей формуле. Чтобы формула функционировала надлежащим образом для всех строк, обязательно используйте абсолютные ссылки для периферийных ячеек (в данном случае $ F $ 1 и $ F $ 2):

    Теперь мы будем применять формулу в ячейке C2.

    = IF (AND (B2> = $ F $ 1, B2 <= $ F $ 2), «X», «»)

    Функция Excel IF и AND — Пример № 4

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

    Например, мы будем отмечать все даты между 30 сентября 2018 года и 10 сентября 2018 года, включая периферийные даты. Итак, здесь мы видим, что мы столкнулись с уникальной проблемой, заключающейся в том, что даты не могут быть напрямую предоставлены для логических тестов. Чтобы сделать даты понятными для Excel, нам нужно будет заключить их в функцию DATEVALUE, как показано ниже:

    = IF (AND (B2> = DATEVALUE («10-Sep-2018»), B2 <= DATEVALUE («30-Sep-2018»)), «X», «»)

    Также можно просто ввести даты «Кому» и «От» в ячейки (в нашем примере $ F $ 2 и $ F $ 1), а затем извлечь их из исходных ячеек, используя формулу IF AND:

    = IF (AND (B2> = $ F $ 1, B2 <= $ F $ 2), «X», «»)

    Функция Excel IF и AND — Пример № 5

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

    У нас есть список студентов и их результаты тестов в процентах. Теперь нам нужно оценить их. Таблица процента к оценке приведена ниже.


    Как видно из приведенной выше таблицы, если процент выше 90%, то соответствующая оценка для этой оценки — A +. Если набранный процент составляет от 85% до 90%, тогда оценка составляет A. Если процент составляет от 80% до 85%, тогда оценка составляет B +. Если процент составляет от 75% до 80%, тогда оценка составляет B. Если процент составляет от 70% до 75%, тогда оценка составляет C +. Если процент составляет от 60% до 70%, тогда оценка составляет C. Если процент составляет от 50% до 60%, тогда оценка составляет D +. Если процент составляет от 40% до 50%, тогда оценка равна D. И, наконец, если процент ниже 40%, тогда оценка равна F.

    Теперь список студентов и их процент показан ниже:

    Формула, которую мы будем использовать:

    Мы использовали Nested IF, а также использовали несколько AND вместе с ними.

    Функция IF оценивает условие, предоставленное в своем аргументе и на основе результата (ИСТИНА или ЛОЖЬ), возвращает один вывод или другой.

    Формула IF выглядит следующим образом:

    Здесь Logical_Test — это условие, которое будет оценено. В зависимости от результата будет возвращено либо Value_If_True, либо Value_If_False.

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

    Точно так же для Студента 2 мы видим, что процент составляет 76%, таким образом, результат, возвращаемый согласно таблице процентного соотношения к классу, показанной выше, будет B, так как процент между> 75% и 80% получает оценку B.

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

    Что нужно помнить об IF и функции в Excel

    • Функция IF AND имеет предел и может проверять до 255 условий. Это, конечно, применимо только к Excel 2007 и выше. Для предыдущих версий Excel функция AND была ограничена только 30 условиями.
    • Функция IF AND может обрабатывать логические аргументы и работает даже с числами. Но он не может обрабатывать строки. Если предоставленные аргументы являются символами, тогда AND возвращает # ЗНАЧЕНИЕ! ошибка.

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

    ЕСЛИ (И (…), выход1 , ЕСЛИ (И (…), выход2 , ЕСЛИ (И (…), выход 3 , ЕСЛИ (И (…) выход4 )))

    Рекомендуемые статьи

    Это руководство по IF и функции в Excel. Здесь мы обсудим, как использовать функцию IF AND в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

    Учебное пособие по использованию Nest IF

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

    = ЕСЛИ (Д7 <30000, $ D $ 3 * D7, ЕСЛИ (Д7> = 50000, $ D $ 5 * D7, $ D $ 4 * Д7))

    Различные части формулы разделяются запятыми и выполняют следующие задачи:

    1. Первая часть, D7 <30000, проверяет, составляет ли зарплата сотрудника менее 30 000 долларов.
    2. Если зарплата составляет менее 30 000 долларов США, средняя часть, $ D $ 3 * D7, умножает зарплату по ставке вычета 6%.
    3. Если зарплата превышает 30 000 долларов США, вторая функция IF IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) проверяет еще два условия.
    4. D7> = 50000 проверяет, превышает ли зарплата сотрудника больше или равна 50 000 долларов США.
    5. Если заработная плата равна или превышает 50 000 долларов США, то D $ 5 * D7 умножает зарплату на 10%.
    6. Если зарплата составляет менее 50 000 долларов США, но превышает 30 000 долларов США, D D $ 4 * D7 умножает зарплату на 8%.

    Если для нескольких условий excel

    Всегда казалось странным, что Excel не поддерживает регулярные выражения (кои в среде программистов зовут регулярками или регэкспами). С их помощью делают сложную сортировку и фильтрацию. Инструмент SeoTools plugin for Excel поддерживает регулярки, но, как и большинство дополнительных ресурсов для Excel, работает только на платформе ПК. Эта статья предназначена, в основном, для пользователей Mac (мы не рекламируем SeoTools).

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

    Немного теории

    Всегда казалось странным, что Excel не поддерживает регулярные выражения (кои в среде программистов зовут регулярками или регэкспами). С их помощью делают сложную сортировку и фильтрацию. Инструмент SeoTools plugin for Excel поддерживает регулярки, но, как и большинство дополнительных ресурсов для Excel, работает только на платформе ПК. Эта статья предназначена, в основном, для пользователей Mac (мы не рекламируем SeoTools).

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

    Подготовка к работе с расширенным фильтром

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

    Расширенный фильтр находится:

    Для ПК: Данные > Сортировка и фильтр >Дополнительно.

    Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр

    Пользователи Mac могут щёлкнуть ПКМ по таблице и выбрать нужный пункт из контекстного меню. На ПК такой возможности нет.

    Небольшой совет

    Базовые операторы

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

    • = равенство, т.е. левая и правая часть должна совпасть: =билет [включит все ячейки, содержащие билет]
    • <> не равно, левая и правая часть не должны совпасть: <> https://www.aviasales.ru/mag* [исключаем данный адрес (блог)]
    • ‘ апостроф преобразует формулу в текст, если поставить его первым в ячейке: ‘=купить авиабилет
    • > больше чем: >500
    • >= больше или равно: B4-C4>=3
    • < меньше чем: C6<D6
    • <= меньше или равно: <=3

    Заголовки

    Набор условий

    Вот три базовых конструкции для множественных условий:

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

    Фильтр отберёт строки, для которых выполняется хотя бы одно из условий, т.е. либо Позиция меньше 11, либо Количество больше 1000. Оператор ИЛИ можно использовать и для одного столбца. В таком случае, все критерии располагаются друг под другом в целевом столбце.

    И: Если необходимо одновременное соблюдение всех критериев, применяется логический оператор И. Для этого критерии располагают на одной строке.

    Данный пример отбирает строки, где в столбце Ключевые слова встречается слово «купить» и нет слова «самолёт», также значение в Позиция больше 7, а значение в Среднемесячное количество больше 1000. Помним, что звёздочка обозначает любое количество знаков. В нашем примере будут отобраны строки, в которых встречается «купить» на любой позиции внутри ячейки. Обратите внимание, что отсев идёт по слову «самолёт», «самолет» вполне проходит.

    ОБА ОПЕРАТОРА: Можно блеснуть своим умением, применив оба оператора для установки критериев.

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

    Примечание: Чтобы получить текст вместо формул, достаточно поставить апостроф перед операторами сравнения: ‘=купить или ‘<>самолёт. Майкрософт на своём сайте советует излишне сложный способ, предлагая заключить в двойные кавычки всё выражение, а перед ним ставить знак равенства: .

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

    Промежуток: Если необходимо получить данные, лежащие в некотором промежутке, можно применить следующий приём:

    Эти условия отберут ключевые слова, для которых Среднемесячное количество находится в промежутке от 3700 до 10000.

    Формулы: Это действительно мощный инструмент. Вместо таблицы критериев можно использовать формулы.

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

    Пара моментов, на которые необходимо обращать внимание при использовании формул:

    • Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА;
    • Заголовки над формулами должны отличаться от заголовков базы данных, можно вообще использовать пустую строку;
    • Формула должна ссылаться на ячейки первой строки под заголовками базы данных;
    • Ссылки на проверяемые ячейки базы должны быть относительными: вида С4, при абсолютных, таких как $C$4 — значение формул будет статичным, т.е. всегда ЛОЖЬ или всегда ИСТИНА.

    Общий алгоритм

    Пошаговый алгоритм работы с расширенным фильтром (полагаем, что данные и табличка условий уже подготовлена):

    Шаг 1: Как упоминалось ранее, щёлкните любую ячейку внутри подготовленного набора данных и перейдите:

    Данные > Сортировка и фильтр >Дополнительно для ПК. Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

    Откроется меню. Здесь приведён скриншот Excel 2013, но все пункты совпадают с Mac.

    Шаг 2: Выберите, где будут размещаться отобранные данные. В большинстве случаев разумнее определить «другое место», это оставит неизменным исходный массив данных. Но выбор за вами.

    Шаг 3: Для исходного диапазона Excel по умолчанию отбирает всю «таблицу». Это можно изменить, исключив ненужные столбцы. Но Excel позволит выбрать только прямоугольный диапазон. Чтобы отобрать столбцы с пропусками, введите названия целевых столбцов в свободных ячейках и укажите их адрес для диапазона вывода. В Дополнительных примерах приводится подробное пояснение.

    Шаг 4: Для установки диапазона условий щёлкните внутри поля и выделите соответствующий диапазон на листе. Если нужно свернуть меню, нажмите на значок выбора диапазона справа от поля.

    Шаг 5: Если выбран пункт Cкопировать результат в другое место, укажите адрес в поле Поместить результат в диапазон. Можно указать единичную ячейку, тогда она станет верхней левой выводимого диапазона, или выбрать ячейки с введёнными названиями нужных столбцов.

    Шаг 6: Если нужны только уникальные записи поставьте флажок. К сожалению, большинство людей используют исключительно эту функцию.

    Шаг 7: Жмём OK, смотрим на результат.

    Шаг 8: Если список фильтровался на месте, можно сбросить фильтр, нажав Очистить, но это верно для ПК. Данный пункт расположен выше пункта Дополнительно. На Маке присутствует аналог — Очистить фильтр, но он недоступен. Ещё один довод в пользу фильтрации на новое место. Чтобы сбросить фильтр нужно отменить действие — нажать Command-Z, или подключить стандартный фильтр.

    голоса
    Рейтинг статьи
    Читайте так же:
    Можно ли использовать фейри в посудомоечной машине
Ссылка на основную публикацию
Adblock
detector