Формулы в экселе если: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Содержание

Если текст содержит слово формула excel

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

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

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

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

У нас много линий реализации и возвратных документов .

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

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

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

Для этого вам необходимо:

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

В открывшемся окне аргументов в поле Log_expression введите COUNTIF (), выберите его и нажмите fx 2 раза.

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

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

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

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

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

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

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

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

Читайте также: Как найти радиус кольца Ньютона

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

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

= ЕСЛИ (G2 = «Готово»; ИСТИНА; ЛОЖЬ)

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

Если вас интересует точное соответствие текстовых значений с учетом регистра, мы рекомендуем использовать функцию EXACT () вместо оператора «=». Убедитесь, что два текстовых значения идентичны, учитывая заглавные буквы отдельных букв.

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

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

ЕСЛИ + СОВПАД

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

Но мы можем использовать ТОЧНУЮ функцию).

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

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

Как видите, варианты «ГОТОВО» и «ГОТОВО» не считаются правильными. Учитываются только завершенные игры. Будет полезно, если важно точное написание текста, например в артикулах товара.

Использование функции ЕСЛИ с частичным совпадением текста.

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

Первое, что приходит в голову, — это использовать подстановочный знак «?» и «*» (вопросительный знак и звездочка). Однако, к сожалению, этот простой метод здесь не работает.

ЕСЛИ + ПОИСК

В этом нам поможет функция ПОИСК. Определяет, где в тексте находятся нужные символы. Его синтаксис следующий:

= ПОИСК (what_search, where_search, start_with_which_search_character)

Если третий аргумент не указан, поиск начинается с начала, с первого символа.

Функция ПОИСК возвращает либо номер позиции, с которой искомые символы появляются в тексте, либо ошибку.

Но нам нужны логические значения для использования в функции ЕСЛИ.

Здесь на помощь приходит еще одна функция EXCEL: ISNUMBER. Если его аргумент — число, он вернет логическое значение ИСТИНА. Во всех остальных случаях, в том числе, если его аргумент возвращает ошибку, ISNUMBER вернет FALSE.

Читайте также: Как получить баллы на aliexpress

В результате наше выражение в ячейке G2 будет выглядеть так:

Еще одно важное уточнение. Функция ПОИСК не чувствительна к регистру.

ЕСЛИ + НАЙТИ

Если для нас важны строчные и прописные буквы, то вместо этого нам придется использовать функцию НАЙТИ (в английской версии — НАЙТИ).

Его синтаксис очень похож на функцию ПОИСК: что мы ищем, где мы ищем, начиная с какой позиции.

Давайте изменим нашу формулу в ячейке G2

То есть, если вам важен случай, просто замените ПОИСК на НАЙТИ.

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

Примеры использования функции SE:

Как использовать функцию ЕСЛИ в Microsoft Excel [шаг за шагом]

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

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

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

Мы рассмотрим:

Что такое функция ЕСЛИ в Microsoft Excel?
Для чего используется функция ЕСЛИ?
Как использовать ЕСЛИ в Excel: пошаговое руководство
Определение результатов с ЕСЛИ

1. Что такое функция ЕСЛИ в Microsoft Excel?

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

=ЕСЛИ(Логическая_проверка,Значение_если_истина,Значение_если_ложь)

Другими словами, значение Логическая_проверка проверяет, выполняется ли условие, а затем возвращает предварительно определенное значение истина или ложь.

Итак, в этом примере у нас есть:

= ЕСЛИ (D9 = «1000 долларов», «Да», «Нет»)

На человеческом языке это означает, что если ячейка D9 равна 1000 долларов, затем ответьте «Да». Если нет, верните «Нет».

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

2. Для чего используется функция ЕСЛИ?

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

Бизнес

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

В этом сценарии мы будем использовать следующую формулу:

=ЕСЛИ(E3<20,E3*200,E3*180)

Итак, если количество глав в ячейке E3 меньше 20, умножьте номер в E3 на 200 долларов. В противном случае умножьте число на 180 долларов.

Образование

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

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

= ЕСЛИ (E3> 69, «Зачет», «Не зачет»)

Таким образом, если итоговый балл учащегося в ячейке E3 больше 69 , верните «Пройти». Если это 69 или ниже, верните «Fail».

Личный

Вы собираетесь в отпуск и установили бюджет на 7-дневную поездку в размере 8000 долларов. Вы создаете электронную таблицу для расчета всех своих расходов, таких как авиабилеты, проживание, питание и т. д. Вы можете использовать функцию ЕСЛИ, чтобы определить, выходите за рамки бюджета или нет.

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

=ЕСЛИ(J2<8000,«В рамках бюджета»,«Выше бюджета»)

Это означает, что если общая стоимость поездки в ячейке J2 меньше более 8000 долларов США, верните «В рамках бюджета». В противном случае верните «Превышение бюджета».

3. Как использовать ЕСЛИ в Excel: пошаговое руководство

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

Если продукт может принести чистую годовую прибыль более 200 000 долларов США, он будет оценен как продукт «А». В противном случае ему будет присвоен рейтинг «В».

Откройте лист Excel, содержащий ваши данные. Вот как выглядит наш в этом примере.

Вот что означают наши данные:

Стоимость : Общая стоимость производства каждого продукта.
Q1-Q4 : Квартальная чистая прибыль по каждому продукту.
Годовая чистая прибыль : Общая сумма прибыли по каждому продукту за год.
Ранг : Ранг каждого продукта.
- A : Годовая чистая прибыль составляет 200 000 долларов США или более.
- B : Годовая чистая прибыль составляет менее 200 000 долларов США.

В поле Годовая чистая прибыль мы используем функцию =СУММ() для расчета суммы чистой прибыли продукта за каждый квартал, а затем вычитаем себестоимость производства из этой суммы, чтобы получить чистую прибыль на год. Итак, в ячейку G2 мы ввели =СУММ(C2:F2)-B2 , а затем скопировал эту формулу для каждой строки в столбце G, щелкнув и перетащив правый нижний угол ячейки G2 вниз до G7.

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

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

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

Теперь появится окно «Аргументы функции». Здесь нам нужно ввести три значения.

Logical_test: Любое значение, которое можно оценить как истинное или ложное.
Value_if_true: значение, которое возвращается, если Logical_test определяется как истинное.
Value_if_false: значение, которое возвращается, если Logical_test определяется как ложное.

Вот значения, которые мы будем вводить в нашем примере.

Logical_test: G2>200000
Value_if_true: «A»
Value_if_false: «B»

Введите необходимую информацию и нажмите OK. Excel выполнит логический тест и вернет соответствующее значение.

Теперь вы можете видеть, что на основе наших данных было определено, что наш первый продукт имеет ранг А, так как он принес чистую прибыль более 200 000 долларов. Чтобы быстро определить рейтинг других продуктов, вы можете щелкнуть и перетащить правый нижний угол поля h3 вниз до нужной строки (H7 в нашем примере).

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

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

4. Определение результатов с помощью IF

Все дело в названии. ЕСЛИ (х), то сделать (у). В противном случае выполните (z). Выполнение этого логического теста для определения результата на основе истинного или ложного сценария может сэкономить огромное количество времени, особенно когда вы сталкиваетесь с длинным списком элементов, которые необходимо проработать.

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

Вас также может заинтересовать следующее видео, в котором наш старший научный сотрудник Том рассказывает о некоторых наиболее важных функциях Excel для аналитиков данных:

изучить больше наших руководств по Excel. Ознакомьтесь со следующими статьями:

Как использовать функцию поиска цели в Excel
Как использовать функцию ЕСЛИОШИБКА в Excel
Как использовать функцию СЦЕПИТЬ в Excel

Что вам следует делать сейчас

Получите практическое введение в аналитику данных и проведите свой первый анализ с помощью нашего бесплатного краткого курса по аналитике данных для самостоятельного обучения.
Примите участие в одном из наших БЕСПЛАТНЫХ онлайн-мероприятий по анализу данных с отраслевыми экспертами и узнайте о пути Азаде от школьного учителя до аналитика данных.
Станьте квалифицированным аналитиком данных всего за 4–8 месяцев с гарантией трудоустройства.
В этом месяце мы предлагаем 50 частичных стипендий для смены карьеры на сумму до 1385 долларов США в рамках наших программ смены карьеры 🚀 Чтобы занять место, закажите заявку сегодня!

Эта статья является частью:

Аналитика данных

Все статьи

Маршалл — профессиональный писатель с опытом работы в отрасли хранения данных, начавший свою карьеру в Synology. Оттуда он продолжил работу в сфере корпоративного хранения данных, а до основания ITEnterpriser он был директором по маркетингу и писателем технического персонала в StorageReview. Его работы также можно найти в Интернете на сайтах How-To Geek, Zapier и некоторых других онлайн-изданиях. В настоящее время он является техническим писателем API/программного обеспечения в корпорации LINE в Токио, Япония. Когда он не пишет, он создает ITEnterpriser, создает калькулятор RAID (его первый проект разработки), изучает японский язык и путешествует по красивым горам, разбросанным по Японии.

Как легко анализировать данные на основе критериев

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

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

Как работает функция ЕСЛИ

Сначала давайте рассмотрим структуру (синтаксис) функции ЕСЛИ . Как и в случае с другими функциями Excel, мы начинаем с =(равно). Затем мы добавляем имя функции, за которым следуют открывающие круглые скобки, например =IF( . Официальная логика и структура функции ЕСЛИ:

= ЕСЛИ (что вы тестируете/оцениваете, что делать, если верно, что делать, если ложно)

Условия могут быть формулами, значениями или текстом

Оцениваемая ячейка или запись может быть формулой , значение или текст; отображаемый результат также может быть формулой, значением или текстовым ответом. Например, если сумма превышает бюджетную сумму более чем на 5 %, вы можете отобразить «ПРЕВЫШЕНИЕ», в противном случае отобразить «ОК».

Например, что, если мы хотим рассчитать анализ, в котором, если общая сумма больше или равна 1000 долларов США, то в ячейку формулы вводится бонус в размере 100 долларов США; в противном случае бонус не предоставляется.

Формула будет выглядеть следующим образом: =ЕСЛИ(B2>=1000, 100, 0) , где B2 — оцениваемое значение. Как и в случае с другими формулами, результаты будут обновляться при изменении значений. И, как и в случае с другими формулами, этот расчет можно скопировать вниз или поперек, чтобы вычислить дополнительные значения.

Результатом функции ЕСЛИ также может быть текстовая запись, которую вы можете отфильтровать для проверки данных, например =ЕСЛИ(B14=E14, «ОК», «ТРЕБУЕТСЯ ПРОВЕРКА») .

В этом образце функции, если значения в двух ячейках совпадают, результатом будет «ОК». В противном случае ответ «ТРЕБУЕТСЯ АУДИТ». Обратите внимание, что текстовые записи заключены в кавычки (для создания строки символов) с запятыми в формуле вне кавычек. Обратите внимание, что не имеет значения, добавляете ли вы пробелы после каждой запятой.

Но подождите… это еще не все! Вложенные функции

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

Например, следующая функция ЕСЛИ работает с этими параметрами:

Если значение меньше 25 000 долларов, умножьте на 10%
Если значение меньше 50 000 долларов, но не менее 25 000 долларов, умножить на 20%
В противном случае (значение больше 50 000 долларов США) умножить на 30%

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

ПРИМЕЧАНИЕ. При вложении с любой функцией рабочего листа знак равенства требуется только в начальном операторе функции, т. е. 2-й ЕСЛИ в нашей вложенной формуле не нуждается в знаке равенства. В формуле можно использовать до 64 уровней вложенности, но это очень много!

Сложные функции не должны быть сложными

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

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

Если ячейка B2 находится между 750 и меньше 1000 (И), введите 75 в ячейку
Если ячейка B2 больше или равна 1000, введите 100 в ячейку

В противном случае введите 0 в ячейку (неправильная/ложная часть)

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

Узнайте больше о ярлыках, советах и рекомендациях Excel здесь.

© Дон Бьорк, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional

Dawn Bjorkhttps://www.