Как в экселе создать формулу с несколькими условиями: Как использовать функцию ЕСЛИ в Excel / Skillbox Media
Содержание
5 простых, но безгранично полезных функций Excel (в GIFках)
16 Фев. 2018
Подумайте, как вы можете делать быстрее, то чем занимаетесь каждый день? Большинство современных людей в течение дня очень много печатают и просматривают гигабайты информации в Интернете. Поэтому ничто не сэкономит вам так много времени, как слепой набор на клавиатуре и скорочтение. Научитесь эффективно искать информацию в поисковиках с помощью специальных операторов «and», «+», «-», «or», «*» и др. Используйте горячие клавиши. И Excel — это один из тех инструментов, который поможет вам сэкономить уйму времени.
Эта статья предназначена для тех, кто панически боится Excel. Для тех, кто использует его как простой разлинованный лист бумаги. Мы планируем заманить вас несколькими несложными функциями и инструкциями в GIF-ках, в надежде, что вы никогда больше не сможете отказаться от этого уровня комфорта!
СУММЕСЛИ
Что умеет: Функция СУММЕСЛИ суммирует данные, если они соответствуют определённому условию.
Как использовать: Например, вы льёте трафик на лендинг и хотите проанализировать сколько переходов вам принесли использованные источники. Но при этом намерены игнорировать как бесперспективные те, с которых было меньше 100 лидов. И вот у вас есть таблица, в которой первый столбик указывает источник трафика, а второй — число переходов с него.
- Вызываете диалоговое окно функции
- Диапазон — та область, из которой нужно взять данные. В нашем случае это ячейки с циферками В2:В7
- Условие: критерий того, какие именно ячейки нужно суммировать. Мы написали >100.
- Диапазон суммирования: необязательное поле. Но если условия находятся в одной ячейке, а цифровые данные в другой, то нужно указать этот диапазон.
Синтаксис: =СУММЕСЛИ(диапазон;условие;[диапазон_суммирования])
Ещё примеры использования:
- Суммировать стоимость заказов интернет-магазина, отправленных в Томск за месяц
- Посчитать, какую часть семейного бюджета съедают мелкие спонтанные покупки и ужаснуться.
После того, как вы немного освоитесь с этой функцией, вы сможете создавать формулы с несколькими условиями, применяя более сложную функцию СУММЕСЛИМН.
ДЛСТР
Что умеет:.Показывает число символов текста в ячейке (с пробелами)
Как использовать: Мы знаем, что стандартная длина заголовка для Яндекс.Директ — 33 символа. И можем воспользоваться ДЛСТР при подготовке объявлений. А в перспективе, объединив функцию ДЛСТР с определёнными условиями, вообще создать формулу для автоматической генерации заголовков! Но начнём с простого
- Вызываете диалоговое окно функции
- Указываем аргумент текст, то есть номер ячейки, содержащей текст
Синтаксис: =ДЛСТР(текст)
Ещё примеры:
- Узнать объём любого текста, не выходя из Excel
- В SEO для проверки длины title, description и keywords
- Выяснить, у кого из коллег самое длинное ФИО и троллить его всю оставшуюся жизнь
ВПР
Что умеет:.
ВПР (вертикальный просмотр) ищет искомое значение в указанном диапазоне. Когда находит, возвращает информацию из другого (указанного вами) столбца той же строки. Говоря проще, может найти информацию в одной таблице и подставить ее в другую.
Как использовать: Допустим, хозяйка планирует сварить борщ и хочет подсчитать его стоимость. У нее есть две таблицы. В сиреневой рецепт, где указано сколько каких ингредиентов будет израсходовано (в неё нужно подставить цены). В зелёной — цены на продукты.
Вызываете диалоговое окно функции
Искомое значение: что ищем? Может быть указано как конкретное значение, так и адрес ячейки. Мы будем искать «говядину»
Таблица Нужно определить диапазон поиска (в нашем случае — прайс-лист). Таблица — это группа ячеек, в которой выполняется поиск искомого значения и возвращаемого. Диапазон ячеек должен содержать искомое значение в первом столбце и возвращаемое значение – в любом месте.
Номер столбца: Номер столбца, содержащий возвращаемое значение (в нашем примере возвращаемое — цена за килограмм)
Интервальный просмотр: Нам нужно найти точное значение, поэтому пишем 0 (ложь).
Синтаксис: =ВПР(искомое_значение;таблица;номер столбца; [интервальный просмотр])
Используя ВПР, нужно соблюдать несколько условий:
- Таблицы должны располагаться в одной книге Excel.
- Искать можно только среди статических данных (не формул).
- Условие поиска должно располагаться в первом столбце используемых данных.
Ещё примеры:
- Найти цену выбранного товара.
- Преобразовать успеваемость ученика, выраженную в процентах, в буквенную систему оценок
ЕСЛИ
Что умеет:. Функция ЕСЛИ инспектирует таблицу на предмет выполнения заданного условия. Если условия выполнены, ЕСЛИ запишет в ячейку с формулой одно значение, если не выполнено — другое.
Как использовать: Например, у вас есть отчёты о деятельности отдела продаж. И вам нужно начислить бонус всем продажникам выполнившим план.
Вызываете диалоговое окно функции
Логическое выражение Здесь мы задаём условие, которое будем проверять.
В нашем случае условие — выполнение плана (продажи сотрудника за установленный период > 20000).
Значение, если истина Что нужно написать программе, если условие выполнено?
Значение, если ложь: Что нужно написать программе, если условие не выполнено? .
Синтаксис: =ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»)
Ещё примеры:
- Сегментация базы подписчиков
- Контролировать, укладываетесь ли вы в свой ежемесячный бюджет
Функция ЕСЛИ — базовая функция, освоив которую, вы сможете создавать действительно впечатляющие формулы, обрабатывающие значительные массивы данных.
СОВПАД
Что умеет:. регистрочувствительная функция, сравнивает две текстовые строки. Если они полностью совпадают, сообщает «Истина», если отличаются — «Ложь»
Как использовать: Предположим, вам нужно сравнить с эталоном список артикулов, емайлов или любой другой объёмной или нечитаемой информации.
Причём вам важно, чтобы написание было именно такое как в образце, и последовательность не нарушалась. Как это сделать?
Вызываете диалоговое окно функции
Текст1 Указываете первую ячейку для сравнения
Текст2 Указываете вторую ячейку для сравнения
Синтаксис: =СОВПАД(текст1;текст2)
Ещё примеры:
- Проверка идентичности текста, который тяжело воспринять визуально
В Excel множество функций. Невозможно знать их все, и вряд ли кто-то использует больше десяти в разных сочетаниях. Но освоив базовые (или самые необходимые), вы никогда не вернётесь к «ручному труду». «Это точно можно сделать как-то проще» — вот та мысль, которую мы хотим посеять этой публикацией. А если статья была полезной, поделитесь ею с друзьями в социальных сетях.
Рубрики: Полезное
Главная — a-cp.ru
Популярное
Полезные советы
13 действенных способов как обойти антиплагиат и 3 причины этого не делать
13 народных средств для восстановления памяти
15 необычных профессий прошлого, которым нет места в современном мире
15 приложений для изучения программирования с нуля
20 лучших дизайнеров интерьера россии и мира
20+ лучших курсов 3d-моделирования для начинающих дизайнеров и визуализаторов
20+ лучших курсов для инфопродюсеров: обучение от популярных онлайн-школ
2019-2020 учебный год
23 удивительных сайта с бесплатными курсами и ресурсами по геммологии
Лучшее
7 критериев исторического сочинения егэ от фипи
7 популярных грантов для обучения за рубежом
7 причин стать диджеем
7 причин стать поваром
7 способов получить дополнительные баллы при поступлении в 2020 году
8 современных профессий, которые вы можете бесплатно освоить онлайн
Bga пайка с нуля
Bist
Brand manager job description: top duties and qualifications
Обсуждаемое
Mosmake
Mubint-my.
sharepoint.com
№ 897-рм об утверждении положения о порядке назначения и выплаты дотаций остронуждающимся студентам вузов г.москвы
Pr-менеджер
Present tenses test
Smm-менеджер
Soft skills и hard skills: 20 ключевых навыков профессионального интернет-маркетолога
Studstat dgu ru
Top famous professional concept artists
Актуальное
Автотранспортный колледж в ростове
Агау
Аграрные вузы россии 2021
Аграрный колледж мичуринск официальный сайт
Агроном специальность
Агротехнические колледжи и техникумы в россии и москве
Агротехнический колледж псков
Агу юридический институт
Администрация техникума
Обновления
Аис «образование»
Вход в электронный журнал Владимирской области на официальном сайте школа.
образование33.рф
При первичном…
Антиплагиат вуз
Составляющие системы антиплагиат
Как и любая система, «антиплагиат ру» состоит из элементов. Основным…
Актер
«Хочу стать актрисой!»: базовые навыки для освоения
Каков бы ни был талант и обаяние, без профессионального…
Ангарский педагогический колледж
Педагогический колледж усолье сибирское
* На базе 9 классов , бюджетная основа , очная форма обучения…
Ангарский политехнический техникум
Всероссийские проверочные работы в АТРиПТ
С 20 сентября 2021 г. по 7 октября 2021г. в техникуме пройдут…
Акушерка, акушер
Особенности профессии
Эта профессия — призвание, ее выбирают только те люди, которые готовы посвятить…
Архангельский колледж культуры и искусства
Контакты колледжа
Специальности колледжа
Библиотековедение Заочно, на базе…
Апноэ и приступы удушья по ночам
Как проводится ПСГ?
Вечером вы придете к назначенному времени в Центр медицины сна (ориентируйтесь на.
..
Архивы по годам: 2019
Обучение в училище олимпийского резерва
Училище олимпийского резерва имеют два отделения: олимпийского…
Академическая разница при переводе: объемы, сроки и особенности перевода внутри вуза и в другое учебное заведение
Раздел 8. На что, прежде всего, следует обратить внимание?
При переводе, осуществляемом, внутри ВУЗа,…
Аннотация
Пример
Так как проекты могут быть совершенно разными, аннотации к ним тоже могут весьма отличаться….
Аниматор: профессия настоящего и будущего
Преимущества и недостатки профессии
Для начала работы в сфере animation недостаточно одного творческого…
Показать ещё
Использование вложенных функций в формуле
Использование функции в качестве одного из аргументов в формуле, использующей функцию, называется вложенностью, и мы будем называть эту функцию вложенной функцией. Например, при вложении функций СРЗНАЧ и СУММ в аргументы функции ЕСЛИ следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50.
В противном случае возвращается 0.
Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
В формулу можно вложить до 64 уровней функций.
Щелкните ячейку, в которую вы хотите ввести формулу.
Чтобы начать формулу с функцией, нажмите Вставить функцию в строке формул.
Excel вставит за вас знак равенства ( = ).
В поле Или выберите категорию , выберите Все .
Если вы знакомы с категориями функций, вы также можете выбрать категорию.
Если вы не уверены, какую функцию использовать, вы можете ввести вопрос, который описывает, что вы хотите сделать в Поиск поля функции (например, «добавить числа» возвращает функцию СУММ ).
Чтобы ввести другую функцию в качестве аргумента, введите нужную функцию в поле аргумента.
Части формулы, отображаемые в диалоговом окне Аргументы функции , отражают функцию, выбранную на предыдущем шаге.
Если щелкнуть IF , в диалоговом окне Аргументы функции отобразятся аргументы функции IF . Чтобы вложить другую функцию, вы можете ввести ее в поле аргумента. Например, можно ввести СУММ(G2:G5) в поле Значение_если_истина функции ЕСЛИ .
После ввода аргументов формулы нажмите OK .
org/ListItem»>Введите любые дополнительные аргументы, необходимые для завершения формулы.
Вместо того, чтобы вводить ссылки на ячейки, вы также можете выбрать ячейки, на которые хотите сослаться. Щелкните, чтобы свернуть диалоговое окно, выберите ячейки, на которые вы хотите сослаться, а затем щелкните, чтобы снова развернуть диалоговое окно.
Совет: Для получения дополнительных сведений о функции и ее аргументах нажмите Справка по этой функции .
Щелкните ячейку, в которую вы хотите ввести формулу.
Чтобы начать формулу с функцией, нажмите Вставить функцию в строке формул.
В диалоговом окне «Вставить функцию» в поле Выберите категорию выберите Все .
Если вы знакомы с категориями функций, вы также можете выбрать категорию.
Чтобы ввести другую функцию в качестве аргумента, введите функцию в поле аргумента в построителе формул или непосредственно в ячейку.
После ввода аргументов формулы нажмите клавишу ВВОД.
org/ListItem»>Введите любые дополнительные аргументы, необходимые для завершения формулы.
Примеры
Ниже показан пример использования вложенных функций ЕСЛИ для присвоения буквенной оценки числовому результату теста.
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.
Оценка | ||
|---|---|---|
45 | ||
90 | ||
78 | ||
Формула | Описание | Результат |
‘= ЕСЛИ(A2>89,»A»,ЕСЛИ(A2>79,»B», ЕСЛИ(A2>69,»C»,IF(A2>59,»D»,»F»)))) | Использует вложенные условия ЕСЛИ для присвоения буквенной оценки оценке в ячейке A2. | =ЕСЛИ(A2>89,»A»,ЕСЛИ(A2>79,»B»,ЕСЛИ(A2>69,»C»,ЕСЛИ(A2>59,»D»,»F»)))) |
‘= ЕСЛИ(A3>89,»A»,ЕСЛИ(A3>79,»B», ЕСЛИ(A3>69,»C»,IF(A3>59,»D»,»F»)))) | Использует вложенные условия ЕСЛИ для присвоения буквенной оценки оценке в ячейке A3. | =ЕСЛИ(A3>89,»A»,ЕСЛИ(A3>79,»B»,ЕСЛИ(A3>69,»C»,ЕСЛИ(A3>59,»D»,»F»)))) |
‘= ЕСЛИ(A4>89,»A»,ЕСЛИ(A4>79,»B», ЕСЛИ(A4>69,»C»,IF(A4>59,»D»,»F»)))) | Использует вложенные условия ЕСЛИ для присвоения буквенной оценки оценке в ячейке A4. | =ЕСЛИ(A4>89,»A»,ЕСЛИ(A4>79,»B»,ЕСЛИ(A4>69,»C»,ЕСЛИ(A4>59,»D»,»F»)))) |
Советы:
Список доступных функций см. в разделах Функции Excel (по алфавиту) или Функции Excel (по категориям).
org/ListItem»>Дополнительные сведения о формулах в целом см. в разделе Обзор формул.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
См. также
Видео: вложенные функции ЕСЛИ
Как вставить оператор Excel If с несколькими условиями
следующий → Хорошо известно, что для выполнения мощного или сложного анализа данных человеку необходимо проверить различные условия в одной точке в определенное время. В этом учебном пособии показано, как мы можем создать несколько операторов ЕСЛИ в Microsoft Excel с логикой И и ИЛИ. Кроме того, мы узнаем, как использовать функцию ЕСЛИ вместе с другими функциями Microsoft Excel.
В основном в Microsoft Excel существует два типа формулы ЕСЛИ с несколькими критериями, основанными на логике И/ИЛИ . Следовательно, при логической проверке нашей формулы ЕСЛИ мы должны использовать одну функцию из перечисленных ниже за раз. И ФУНКЦИЯ : Если мы выполним логический тест на основе функции И, то Microsoft Excel выдаст нам «ИСТИНА» в качестве вывода в каждом условии; в противном случае он вернет «ЛОЖЬ» в качестве вывода. ФУНКЦИЯ ИЛИ: Если мы используем условие ИЛИ для данной логической проверки, то Microsoft Excel выдаст результат «ИСТИНА» при совпадении какой-либо ситуации; в противном случае в качестве результата будет возвращено «ЛОЖЬ». А чтобы лучше проиллюстрировать вышеизложенный пункт, мы рассмотрим его на некоторых примерах формул из реальной жизни. Оператор Excel IF с несколькими условиями (логика И) Общая формула функций ЕСЛИ Microsoft Excel, имеющих два или более условия, выглядит следующим образом: ЕСЛИ (И (условие1, условие2,), значение_если_истина, значение_если_ложь) У нас есть таблица, в которой в первую очередь перечислены результаты двух тестов в столбце, и чтобы сдать выпускной экзамен, учащийся должен набрать больше 50 баллов. Итак, для логического теста мы используем оператор И: И (В2>50, С2>50 И если оба условия ИСТИННЫ, то в этом случае используемая формула вернет «Pass»; если какое-либо условие ложно, он вернет вывод как «Fail». =ЕСЛИ (И (B2>50, B2>50), «Пройдено», «Не пройдено») Кроме того, таким же образом мы можем использовать функцию ЕСЛИ Microsoft Excel с помощью условия множественного текста. И если оба столбца B2 и C2 больше 50, то мы должны получить вывод как «ХОРОШИЙ», иначе «ПЛОХОЙ», формула, которая может быть эффективно использована для достижения этого, выглядит следующим образом: =ЕСЛИ (И (B2=»пройдено», C2=»пройдено»), «Хорошо!», «Плохо») Функция Microsoft Excel ЕСЛИ с несколькими условиями (логика ИЛИ) Переходит к выполнению отдельного действия, если выполняется какое-либо условие В противном случае переходит к выполнению чего-то еще, эффективно используя комбинацию функций ЕСЛИ и ИЛИ в Excel: ЕСЛИ (ИЛИ (условие1, условие2,), значение_если_истина, значение_если_ложь) Итак, теперь мы используем функцию ИЛИ вместо И в том же примере и увидим результат: =ЕСЛИ (ИЛИ (B2>50, B2>50), «Пройдено», «Не пройдено») Если кто-либо из студентов, набравших более 50 баллов на любом из экзаменов, получит «Зачет» в столбце D. Использование оператора IF с несколькими операторами AND и OR в Microsoft Excel В случае, если наша задача требует оценки нескольких наборов множественных условий, тогда нам нужно использовать функции И и ИЛИ в определенный момент времени. В выбранной нами таблице образцов мы будем предполагать, что мы включены со следующими критериями для проверки результатов экзамена: Условие 1: экзамен1>50 и экзамен2>50 Условие 2: экзамен1>40 и экзамен2>60 И если одно из следующих условий выполнено, итоговый экзамен сдан. ИЛИ (И (В2>50, С2>50), И (В2>40, С2>60) Затем мы будем использовать функцию ИЛИ для логической проверки ЕСЛИ и предоставить желаемые значения значение_если_истина и значение_если_ложь . В результате мы получим следующую формулу ЕСЛИ с несколькими условиями И/ИЛИ, а также: =ЕСЛИ (ИЛИ (И (B2>50, C2>50), И (B2>40, C2>60), «Пройдено», «Не пройдено») Как использовать несколько вложенных операторов IF в Microsoft Excel? Предположим, что есть сценарий, в котором мы хотим оценить несколько логических тестов в заданной формуле. Они полезны, когда мы хотим вернуть разные значения в зависимости от различных результатов логического теста.
Итак, прежде чем перейти к написанию формулы, мы сначала рассмотрим порядок функций, которые мы собираемся вкладывать. И Microsoft Excel в конечном итоге оценит логические тесты в том порядке, в котором они могут появиться в соответствующей формуле. Как только условие оценивается как ИСТИНА, следующие условия не будут проверяться, а это означает, что формула остановится сразу после того, как мы встретим первый результат ИСТИНА. Как и в нашем случае, функции расположены от наиболее значимых к второстепенным соответственно: =ЕСЛИ (В2>=60, «Хорошо», ЕСЛИ (В2>40, «Удовлетворительно», «Плохо»)) Как вставить функции ЕСЛИ в формулы массива в Microsoft Excel?Другие методы, которые можно использовать для получения ЕСЛИ в Excel для проверки нескольких условий, — это использование «формулы массива». И чтобы оценить данные условия с помощью логики И, мы также можем использовать символ звездочки. ЕСЛИ (условие1) * (условие2) * …, значение_если_истина, значение_если_ложь) И далее для проверки условий по логике ИЛИ мы будем использовать знак плюс: ЕСЛИ (условие1) + (условие2) + …, значение_если_истина, значение_если_ложь) Мы будем нажимать клавиши Ctrl + Shift + Enter, чтобы правильно завершить формулу массива.
=ЕСЛИ ((B2>50) * (C2>50), «Пройдено», «Не пройдено») И чтобы оценить несколько условий с помощью логики ИЛИ, мы будем использовать приведенную ниже формулу: = ЕСЛИ ((B2>50) + (C2>50), «Пройдено», «Не пройдено») Как можно вставить функции ЕСЛИ вместе с другими функциями Microsoft Excel? В нем объясняется, как использовать функцию ЕСЛИ в сочетании с другими функциями Microsoft Excel. # Пример 1: Если #Н/Д ошибка в ВПР Когда функция ВПР не может что-то найти, она вернет # Н/Д ошибка , и чтобы наши таблицы выглядели привлекательно, мы можем вернуть ноль, пустой или конкретный текст, если #Н/Д , поэтому для этого мы будем использовать эту общую формулу: ЕСЛИ (ИСНА (ВПР(…)), значение_если_на, ВПР(…)) Например: Формула возвращает определенный текст, если искомое значение не найдено. =ЕСЛИ (ИСНА (ВПР (E1, A2:B10, 2, ЛОЖЬ)), «Не найдено», ВПР (E1, A2:B10, ЛОЖЬ)) # Пример 2: ЕСЛИ с функциями СУММ, СРЗНАЧ, МИН и МАКС в Microsoft Excel Если мы хотим суммировать значения ячеек, которые основаны на определенных критериях, Microsoft Excel предоставит функции СУММЕСЛИ и СУММЕСЛИМН соответственно: И в большинстве ситуаций наша бизнес-логика может потребовать включения функции СУММ в логическую проверку ЕСЛИ.
ФОРМУЛА: =ЕСЛИ (СУММ (B2:C2)>130, «Хорошо», ЕСЛИ (СУММ (B2:C2)>110, «Удовлетворительно», «Плохо»)) И если в случае, если данная сумма будет значительнее значения 130, то результат будет отображаться как «хорошо»; и если оно больше значения 110, то вернет результат «удовлетворительно», а если 110 и ниже — «плохо». Таким же образом мы можем легко внедрить функцию СРЗНАЧ в логическую проверку ЕСЛИ и возвращать различные метки, которые в основном основаны на среднем балле: =ЕСЛИ (СРЕДНИЙ (B2:C2)>65, «Хорошо», ЕСЛИ (СРЕДНИЙ (B2:C2)>55, «Удовлетворительно», «Плохо»)) Предположим, что общий балл присутствует в столбце D, в котором мы можем эффективно определить как самые высокие, так и самые низкие значения с помощью функций MAX и MIN. =ЕСЛИ (D2=МАКС ($D$2:$D$10), «Лучший результат», «») =ЕСЛИ (D2=МАКС ($D$2:$D$10), «Лучший результат», «») И чтобы обе метки были в одном столбце, мы можем вложить вышеуказанные функции одну в другую соответственно: =IF (D2=MAX ($D$2:$D$10), «Лучший результат», IF (D2=MIN ($D$2:$D$10), «Худший результат»,»)) #Пример 3: Использование ЕСЛИ и СЦЕПИТЬ в Microsoft Excel Если мы хотим вывести результат ЕСЛИ и часть текста в одну ячейку, мы можем использовать функцию СЦЕПИТЬ, а также функции ЕСЛИ. Читайте также: |
Анализ данных также требует логических тестов в этих многочисленных условиях. И для достижения вышеизложенного человек должен выполнить Оператор Excel if с несколькими условиями. 
В этом случае мы можем вложить несколько функций одну в другую, такие функции известны как «вложенные функции ЕСЛИ».
