Функция УСЛОВИЯ. Функция эксель если
Функция УСЛОВИЯ - Служба поддержки Office
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Функция IFS проверки выполняются одного или нескольких условий и возвращает значение, которое соответствует первому ИСТИННОМУ условию. IFS могут выполняться нескольких вложенных операторов Если и гораздо легче читать с несколькими условиями.
Простой синтаксис
Примечания:
-
Функция ЕСЛИМН позволяет проверить до 127 различных условий.
-
Например:
-
Расшифровка: если(значение A1 равно 1, вывести 1, если значение A1 равно 2, вывести 2, иначе если значение A1 равно 3, вывести 3).
-
Однако обычно не рекомендуется использовать слишком много условий, так как условия нужно вводить в правильном порядке и их может быть сложно добавлять, проверять и обновлять.
Синтаксис
-
УСЛОВИЯ(лог_выражение1;значение_если_истина1,[лог_выражение2;значение_если_истина2],[лог_выражение3;значение_если_истина3],...)
|
Аргумент |
Описание |
|
лог_выражение1 (обязательно) |
Условие, принимающее значение ИСТИНА или ЛОЖЬ. |
|
значение_если_истина1 (обязательно) |
Результат, возвращаемый, если условие лог_выражение1 принимает значение ИСТИНА. Может не указываться. |
|
лог_выражение2…лог_выражение127 (необязательно) |
Условие, принимающее значение ИСТИНА или ЛОЖЬ. |
|
значение_если_истина2...значение_если_истина127 (необязательно) |
Результат, возвращаемый, если условие лог_выражениеN принимает значение ИСТИНА. Каждый аргумент значение_если_истинаN соответствует условию лог_выражениеN. Может не указываться. |
Пример 1
Формула для ячеек A2:A6:
Расшифровка: ЕСЛИ(значение A2 больше 89, возвращается "A", если значение A2 больше 79, возвращается "B", и т. д. Для всех значений меньше 59 возвращается "F").
Пример 2
В ячейке G7 находится следующая формула:
Замечания
-
Чтобы задать результат по умолчанию, введите значение ИСТИНА для последнего аргумента лог_выражение. Если ни одно из других условий не выполняется, возвращается соответствующее значение. Это показано в строках 6 и 7 примера 1 (с 58 баллами).
-
Если условие лог_выражение указывается без соответствующего аргумента значение_если_истина, появляется сообщение "Указано недостаточно аргументов".
-
Если аргумент лог_выражение принимает значение, отличное от ИСТИНА или ЛОЖЬ, функция возвращает ошибку #ЗНАЧ!.
-
Если условия со значением ИСТИНА не найдены, возвращается ошибка #Н/Д!
Дополнительные сведения
См. также:
Функция ЕСЛИ Усложненные функции ЕСЛИ: как работать с вложенными формулами и избежать ошибок Обучающие видео: усложненные функции ЕСЛИ Функция СЧЁТЕСЛИ подсчитывает значения с учетом одного условия Функция СЧЁТЕСЛИМН подсчитывает значения с учетом нескольких условий Функция СУММЕСЛИ суммирует значения с учетом одного условия Функция СУММЕСЛИМН суммирует значения с учетом нескольких условий Функция И Функция ИЛИ Функция ВПР Полные сведения о формулах в Excel Рекомендации, позволяющие избежать появления неработающих формул Исправление распространенных ошибок в формулах с помощью функции проверки ошибок Логические функции Функции Excel (по алфавиту) Функции Excel (по категориям)
Как в Excel применять функцию ЕСЛИ() — Трюки и приемы в Microsoft Excel
Функция ЕСЛИ() является одним из самых мощных инструментов в мире функций. Если вы сумеете освоить ее в работе, вы перейдете на новый уровень создания формул.Давайте начнем с простейшей возможности использования формулы. Синтаксис: ЕСЛИ (лог_выражение; значение_если истина), где лог_выражение — логическое выражение, то есть выражение возвращающее значение ИСТИНА или ЛОЖЬ (или эквивалентные численные значения: 0, если ЛОЖЬ, и любое положительное значение — ИСТИНА, и где значение_если истина — значение, возвращаемое функцией, в случае если лог_выражение имеет значение ИСТИНА.
Например, разберем следующую формулу: =ЕСЛИ(A1>1000;"много"). Логическое выражение А1>1000 используется для проверки. Допустим, вы добавите данную формулу в ячейку В1. Если логическое выражение является истинным (оно является таковым, когда число в A1 больше 1000), функция возвращает значение «много», и именно это вы увидите в ячейке В1 (в случае если А1 меньше 1000, вы увидите значение ЛОЖЬ).
Другое простое применение функции — проверка для вывода предупреждений. Например, если у вас имеется книга с результатами увеличения продаж различных товаров в процентах. Будет полезно отметить определенным образом товары с уменьшившимися продажами. Базовая формула для такой отметки представляет собой следующее: =ЕСЛИ(ячейка .
Здесь ячейка - адрес ячейки для проверки, а отметка - определенного рода текст для вывода на экран для указания на отрицательное значение. Вот пример: =ЕСЛИ(В1 .
Немного более улучшенная версия отметки, в которой она будет зависеть от величины отрицательного значения, выглядит так: =ПОВТОР(". Это выражение умножает процентное значение на 100 (минус указан, для того чтобы сделать значение положительным) и затем использует результат для указания количества знаков для вывода. Вот итоговая формула: =ЕСЛИ(В1. Рис. 4.15 показывает, как это выглядит на практике.
Рис. 4.15. Пример использования функции ЕСЛИ()
Обработка ложного результата
Как вы можете видеть на рис. 4.15, если в функции ЕСЛИ() логическое выражение принимает отрицательное значение, функция возвращает ЛОЖЬ в качестве результата. Это не является критическим недостатком, однако делает рабочие листы как бы «тяжелее», чем если бы в результате возвращалась, например, пустая строка.
Для того чтобы сделать это, вам необходимо использовать расширенный синтаксис функции: ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь). Первые два аргумента мы с вами уже знаем, а последний аргумент значение_если_ложь задает значение для возврата функции ЕСЛИ(), когда результат вычисления лог_выражения является ложным.
Например, рассмотрим следующую формулу: =ЕСЛИ(A1>1000;"много";"мало"). Если на этот раз ячейка A1 содержит число, меньшее или равное 1000, формула выведет строку «мало». Для примера с отметкой отрицательных объемов продаж (см. выше рис. 4.15) необходимо использовать следующую формулу: =ЕСЛИ(B1.
Рис. 4.16. Обработка ложного результата
Как вы можете судить по рис. 4.16, рабочий лист теперь выглядит гораздо лучше.
Предотвращение деления на ноль
Как мы уже указывали ранее, функция возвращает ошибку #ДЕЛ/0!, если формула пытается поделить какое-то значение на 0. Для устранения даже возможности совершения такой ошибки вы можете воспользоваться функцией ЕСЛИ() для проверки деления.
Например, простейшее вычисление процента валовой прибыли по формуле (Доходы - Расходы)/Доходы. Для уверенности в том, что значение Доходы не равно нулю, используйте следующую формулу (предполагается, что вместо слов Доходы и Расходы будут подставлены необходимые значения): =ЕСЛИ(Доходы 0; (Доходы - Расходы)/Доходы; "Доходы равны 0!"). Если логическое выражение Доходы 0 верно, значит, произойдет вычисление по формуле. В противном случае функция вернет предупреждающее сообщение вместо ошибки.
Важными задачами учебного курса в школе являются развитие у учащихся основных мыслительных операций (анализ, синтез, сравнение, обобщение), нормализация взаимосвязи их деятельности с речью, формирование приемов умственной работы: анализ исходных данных, планирование деятельности, осуществление поэтапного и итогового самоконтроля, для чего используется поурочное планирование процесса обучения. Большое значение придается умению рассказывать о выполненной работе с правильным употреблением соответствующей терминологии и установлением логических связей в излагаемом материале.
excelexpert.ru
Несколько условий ЕСЛИ в Excel
Функция ЕСЛИ в Excel позволяет оценивать ситуацию с двух точек зрения, например, значение больше 0 или меньше, и в зависимости от ответа на этот вопрос, произведи дальнейшие расчеты по той или иной формуле. Однако, не редки ситуации, когда вам приходится работать более, чем с двумя условиями. В сегодняшней статье мы рассмотрим примеры создания формул в Excel с несколькими условиями ЕСЛИ.
Прежде, чем начать изучать данный урок, рекомендую прочитать статью про функцию ЕСЛИ, где описаны основные приемы работы.
Принцип создания формул с несколькими условиями ЕСЛИ заключается в том, что в одном из аргументов формулы (значение_если_ИСТИНА или значение_если_ЛОЖЬ) находится еще одна формула ЕСЛИ.
Например: =ЕСЛИ(A5=0;»НОЛЬ»;ЕСЛИ(A5<0;»МЕНЬШЕ НОЛЯ»;»БОЛЬШЕ НОЛЯ»)), где функция оценивает значение ячейки A5 два раза, первый, проверяет, равняется ли значение нулю, и возвращает текст – НОЛЬ, если ИСТИНА. Если результат оценки вернул значение ЛОЖЬ, происходит вторая оценка, функция проверяет, является ли значение ячейки A5 меньше ноля, и возвращает текст МЕНЬШЕ НОЛЯ, если результат ИСТИНА, в противном случае возвращает текст БОЛЬШЕ НОЛЯ.
Таким образом, в примере выше, формула вернет значение МЕНЬШЕ НОЛЯ, так как при первой оценке, результат оказался ЛОЖЬ, а при второй оценке ИСТИНА.
Давайте рассмотрим пример посложнее. Предположим, вам необходимо рассчитать размер комиссии каждого продавца в зависимости от объема его продаж.
Ваш план таков:
- Если продажи меньше или равны 500$, комиссия составляет 7%
- Если продажи больше 500$, но меньше или равны 750%, комиссия составляет 10%
- Если продажи больше 750$, но меньше или равны 1000%, комиссия составляет 12,5%
- Если продажи больше 1000$, комиссия составляет 16%
Вместо того, чтобы рассчитывать размер комиссии для каждого работника, можно создать формулу с несколькими условиями ЕСЛИ. Логика формулы будет следующая:
- Продажи меньше или равны 500$. Если ИСТИНА, рассчитываем комиссию.
- Если ЛОЖЬ, то продажи меньше или равны 750$. Если ИСТИНА, рассчитываем комиссию.
- Если ЛОЖЬ, то продажи меньше или равны 1000$. Если ИСТИНА, рассчитываем комиссию.
- Если ЛОЖЬ, рассчитываем комиссию, так как это будет означать, что продажи больше 1000$ и больше логических тестов проводить не нужно.
Давайте создадим формулу следуя данной логике для продавца Сергея. (Я выделил жирным проверку логики для лучшего понимания).
=ЕСЛИ(B4<400;B4*7%;ЕСЛИ(B4<750;B4*10%;ЕСЛИ(B4<1000;B4*12.5%;B4*16%)))
На первый взгляд может показаться, что это ужасная формула, но давайте попробуем разобраться:
Логическое выражение в первой формуле ЕСЛИ проверяет, является ли значение в ячейке B4 меньше 400, если ИСТИНА, формула умножает значение ячейки B4 на 7% и останавливает дальнейшие вычисления. Если значение ячейки B4 больше 400, мы переходим к следующей функции ЕСЛИ. Так будет продолжаться, пока мы не достигнем последнего значения, где значение ячейки умножается на 16%. Это значит, что ни одно из условий не удовлетворило требованиям, т.е. продажи составляют более 1000$.
Ниже вы видите, как будет выглядеть колонка Комиссия, когда все формулы будут введены. Также в колонке Формула отображены формулы для каждого продавца.
Можно проверить на примере Натальи правильность работы формулы. Продажи Натальи составили 844$, т.е. больше, чем 750$, но меньше чем 1000$. Соответственно, коэффициент комиссии будет равняться 12,5%, а сама комиссия составит 105,5$. Также важно отметить, о работе формулы с пограничными значениями. Предположим, что сумма продаж Натальи составила 750$, какой коэффициент должна применить формула? Коэффициент будет 12,5%, так как для коэффициента 10% сумма продаж должна равняться меньше 750. Это важное замечание, поэтому будьте аккуратны при составлении логики формулы.
Итак, как вы увидели, формула ЕСЛИ очень мощный инструмент при составлении логических выражений с несколькими условиями и позволяет экономить время на просчет каждой ячейки таблицы.
Вам также могут быть интересны следующие статьи
exceltip.ru
Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали
Excel имеет различные функции, включая функции расчета арккосинуса заданного значения, умножение двух матриц, оценки внутренней нормы доходности. Но большинство из нас (ну, и я в том числе) использует не более 5-6 формул для работы. И формула ЕСЛИ находится в этом списке. Так что, не повредит узнать еще несколько интересных вещей, которые можно сделать только с одной функцией ЕСЛИ.
1. Сумма альтернативных столбцов/строк
Случается такое, когда нам необходимо суммировать каждую вторую строку столбца (не спрашивайте меня зачем, такое случается и не редко). Конечно, мы могли бы суммировать каждую ячейку по отдельности, но это не наш вариант. Вместо этого мы устроим себе кофе-брейк, в то время как Excelсделает за нас всю грязную работу. Для этого нам понадобится альтернативный столбец и функция СУММЕСЛИ, которая суммирует ячейки, заданные определенным условием. В нашем случае критерием будет «если элемент находится в нечетных строках».
Все что нам нужно сделать, это добавить дополнительный столбец и заполнить его единицами и нулями (введите 1 и 0 в две строки, выделите обе ячейки и перетащите до конца таблицы). Теперь мы можем использовать наш столбец для определения условия с помощью функции =СУММЕСЛИ(диапазон условия; 1; диапазон суммирования).
2. Подсчет количества повторений в листе А элементов из листа Б
Часто, когда вы работаете с несколькими листами, необходимо подсчитать количество повторений какого-либо элемента на другом листе. Это можно реализовать с помощью старой доброй СЧЕТЕСЛИ, которая считает количество ячеек, удовлетворяющих заданному условию.
В примере выше я использовал функцию СЧЁТЕСЛИ, чтобы посчитать, сколько клиентов находиться в определенном городе (где список с клиентами находится на Листе 2, список с городами на Листе 1). Формула выглядит следующим образом =СЧЁТЕСЛИ(диапазон условий; условие). К примеру, формула СЧЁТЕСЛИ($E$6:$E$16; “Волгоград”) скажет мне, сколько клиентов находиться в Волгограде.
3. Быстрый подсчет данных с помощью СЧЁТЕСЛИ и СУММЕСЛИ
Теперь, когда мы выяснили, как пользоваться формулами СЧЁТЕСЛИ и СУММЕСЛИ, может использовать их вместе.
Например, мы можем выяснить средний объем дохода от клиента, приходящегося на каждый город путем деления суммы дохода города на количество клиентов. Это быстрый сводный анализ без использования инструмента построения сводных таблиц.
4. Поиск второго, третьего … n-ного вхождения элемента в списке EXCEL
Часто случается так, что мы работаем со списком данных, имеющих много дубликатов, например, в список телефонов клиента со временем могут добавляться новые данные. Получить второе, третье … десятое вхождение может быть не простым. С помощью комбинаций СЧЁТЕСЛИ и ВПР мы можем найти любое появление в списке.
Для начала добавим дополнительную колонку в таблицу с данными о клиентах и пропишем в ячейки формулу, которая к имени клиента добавляет порядковый номер вхождения имени в списке. Таким образом, при первом появлении в списке, формула выдаст Имя1, при втором Имя2 и так далее.
Далее, при поиске информации о Кристине Агилере, мы будем пользоваться измененными данными клиентов. К примеру, формула ВПР(“Кристина Агилера3”, диапазон поиска, 2, ЛОЖЬ) выдаст нам третий номер телефона Кристины Агилеры. Обратите внимание, что последний аргумент формулы ВПР будет ЛОЖЬ, так как наш список не отсортирован по алфавиту и нам требуется точное совпадение значений поиска.
5. Сокращаем количество вложений функции ЕСЛИ
В более ранних версиях Excel, допускалось всего 7 уровней вхождения функции ЕСЛИ. Начиная с Excel 2007 это ограничение снято. К счастью, большинство из нас никогда не опускалось ниже 3-го или 4-го уровня. Но зачем писать такие многоуровневые формулы, если можно воспользоваться функцией ВЫБОР, которая выбирает значение или действие из списка значений по номеру индекса. Синтаксис функции выглядит следующим образом ВЫБОР(номер_индекса; значение_при_индексе_равном_1; значение_при_индексе_равном_2; значение_при_индексе_равном_2…). Например, следующая функция, ВЫБОР(3; «Отлично»; «Хорошо»; «Плохо»), вернет «Плохо», если ее вбить в ячейку. Есть одно ограничение, значение номера индекса должно быть задано в числовом формате, поэтому потребуется немного творчества (как в следующем примере), когда значение индекса нельзя задать явно:
Как я перевел значение буквы в число в функции ВЫБОР(), ну пускай это будет вашим домашним заданием)
Вам также могут быть интересны следующие статьи
exceltip.ru
Использование функции ЕСЛИ без функции ЕСЛИ
Использование функции ЕСЛИ мы уже неоднократно рассматривали в наших статьях (Статья 1, Статья 2, Статья 3), казалось бы, ну что еще можно придумать? Можно! В Этой статье мы рассмотрим как используя элементарные операции Excel (умножение и сравнение) можно быстро записать условие формулой Excel не прибегая к использованию функции ЕСЛИ.
Собственно говоря, это не статья про функцию ЕСЛИ, а то, как можно без нее обойтись. Для этого нам потребуется только операция умножения и запись условия равенства двух ячеек (используем знак "="). Напомним, что при сравнении двух ячеек, например, =А1=А2 (как для чисел так и для текста) значением такой функции будет "ИСТИНА" при верном исходе и "ЛОЖЬ«, если равенство окажется ошибочным,
Истина_Ложь
так как „ИСТИНА“ представляет собой единицу(1), а »ЛОЖЬ" — ноль(0), то, как вы уже догадались, через умножение в формуле можно будет указать операцию для вычисления. Например, формула =(А1=А2)*(6-1) в нашем примере даст результат 0, а =(А1=А3)*A2 даст результат 15. Другими словами: если равенство выполняется, то происходит дальнейшее вычисление, если нет, то выводится 0.
Понятно, что если мы имеем несколько условий, то записывать эти условия можно до бесконечности (перемножая их), и при выполнении только всех условий (т.е. когда все условия будут истинными) будет выполняться необходимое выражение, например:
=(A1=B1)*(A2=B2)*(A3=B3)*(D19/28,7+E19)
Интереснее и удобнее всего использовать такой вариант записи условия для обработки больших объемов данных, массивов, сочетание такого варианта задания условия (без функции ЕСЛИ) в формуле с использованием функции СУММПРОИЗВ().
Эти и другие сложные функции мы рассмотрим в нашем Профессиональном Курсе EXCEL (готовится к выходу).
Благодарим за внимание.
SirExcel.ru – Безграничные возможности Excel
sirexcel.ru
