Обработка нескольких условий в формуле ЕСЛИ. В этой статье сравниваются три разных метода.
Когда вам нужно выполнить условное вычисление, функция ЕСЛИ является ответом. Если то иначе. На следующем рисунке простое IF вычисляет бонус, если ваши продажи составили 20 000 долларов или больше.
Но что происходит, когда необходимо выполнить два условия? Большинство людей вкладывают один оператор IF в другой, например:
Но это выходит из-под контроля, если у вас есть много условий, которые необходимо выполнить. Функция И сократит и упростит формулу. = И (Тест, Тест, Тест, Тест) будет иметь значение Истина, только если все логические тесты верны. В следующем примере показана более короткая формула с теми же результатами.
Если вам нравится AND, вы можете найти применение OR и NOT. = ИЛИ (Тест, Тест, Тест, Тест) будет True, если любой из логических тестов верен. НЕ изменит ответ. =NOT(True)ложно. =NOT(False)правда. Если вам когда-либо придется делать что-то необычное, например NAND, вы можете НЕ (И (Тест, Тест, Тест, Тест)).
Осторожно
Хотя в Excel 2013 XOR был введен как исключительное ИЛИ, он работает не так, как ожидали бы бухгалтеры. =XOR(True,False,True,True)Верно по причинам, которые слишком сложно объяснять здесь. XOR действительно считает, есть ли у вас нечетное количество истинных значений. Странный. Действительно странно.
Бонусный совет
Использование логической логики
Я всегда освещаю ИФ на своих семинарах. И я всегда спрашиваю, как люди решат проблему двух условий. Результаты часто одни и те же; 70-80% людей используют вложенные IF и 20-30% используют AND. Только однажды в Вирджинии женщина из Price Waterhouse предложила такую формулу:
Оно работает. Он дает тот же ответ, что и другие формулы. Рассчитайте бонус .02 * B4. Но затем умножьте этот бонус на логические тесты в скобках. Когда вы заставляете Excel умножать число на Истину или Ложь, Истина становится 1, а Ложь становится 0. Любое число, умноженное на 1, само по себе. Любое число, умноженное на 0, равно 0. Умножение бонуса на условия гарантирует, что будут оплачены только те строки, которые удовлетворяют обоим условиям.
Это круто. Оно работает. Но это сбивает с толку, когда вы впервые это видите. Моя шутка на семинаре всегда звучит так: «Если вы уходите с работы в следующем месяце и ненавидите своих коллег, начните использовать эту формулу».
Смотреть видео
- Самая простая функция ЕСЛИ:
=IF(Logical Test,Formula if True, Formula if False) - Но что делать, если нужно проверить два условия?
- Многие люди сделают
=IF(Test 1, IF(Test 2, Formula if True, False), False) - Это становится громоздким при 3, 5, 17 условиях!
- Вместо этого используйте
=IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) - Если вам нравится И, рассмотрите ИЛИ, НЕ для других ситуаций
- NAND может быть выполнено с НЕ (И ())
- ИЛИ можно сделать с НЕ (ИЛИ ())
- Будьте осторожны при использовании XOR, поскольку результаты не такие, как вы ожидаете
Стенограмма видео
Изучите Excel из подкаста, выпуск 2025 - Множественные условия в IF!
Я буду подкастировать всю эту книгу, буква «i» в правом верхнем углу приведет вас к списку воспроизведения всех этих подкастов!
Хорошо, мы собираемся начать с самого простого в мире случая IF. К нам приходит фиктивный вице-президент по продажам и говорит: «Эй, в этом месяце любой, у кого продажи более 20000 долларов, получит 2% бонуса». Итак, функция ЕСЛИ состоит из трех частей: логический тест, который говорит: "B4> 20000 долларов?" Запятая, тогда что делать если ИСТИНА? Если это ИСТИНА, .02 * B4, запятая, что делать, если это ЛОЖЬ? Ну, если вы не заработали 20000 долларов, бонуса для вас нет, ну хорошо. Ctrl + Enter, чтобы скопировать это вниз, и вы видите, что у нас есть бонус только на строки, которые были выше 20000 долларов, этот закрытый, но все еще без бонуса, хорошо. Такого простого плана бонусов никогда не было, правда, всегда есть несколько правил, поэтому здесь мы должны проверить, не превышает ли доход 20000 долларов И если процент валовой прибыли> 50%.
Хорошо, и если вы знаете, как решить эту проблему, подумайте, как вы собираетесь решить эту проблему, хорошо, и я собираюсь предсказать, что многие из вас говорят: «Ну, мы начнем с одного IF, а затем еще один оператор IF, подобный этому, вложенные IF ». А еще есть некоторые из вас, которые говорят: «Ну, давайте сделаем оператор IF, а затем прямо здесь, в круглых скобках, мы перейдем к другой функции, называемой AND». Кроме того, есть способ сделать это вообще без IF. Хорошо, я посмотрю на это через них, так что вот первый, наиболее распространенный способ, вложенный IF, хорошо, вот как большинство людей сделают это.
= ЕСЛИ, проведите первый тест, посмотрите, не превышает ли доход в B4 20000 долларов, если да, то выполните еще одно ЕСЛИ, посмотрите, не превышает ли процент валовой прибыли 0,05? Если это ИСТИНА, то оба условия ИСТИНА, мы можем сделать 0,02 * B4, в противном случае бонуса нет. Хорошо, но мы еще не закончили, закройте внутренние круглые скобки, запятую, а затем, если первый тест не был истинным, без бонуса, Ctrl + Enter, чтобы скопировать. Вы видите, что только те строки, которые выше 20000 долларов и выше 50% валовой прибыли, получают бонус. Хорошо сейчас, когда-то был этот ужасный предел, когда вы не могли вложить более 7 операторов IF. Это был болезненный день, особенно если вы просто медленно добавляли условия в течение нескольких месяцев, и, наконец, у вас был тот, у которого было 7, и вам нужно было добавить 8-е. Хорошо, сегодня вы можете перейти на 32, я не думаю, что вам когда-либо стоит переходить на 32,но если вам просто отчаянно нужно перейти с 7 на 8, тогда это хорошо, хорошо. Итак, это подход с использованием вложенных операторов ЕСЛИ, когда я провожу живые семинары, этим занимается около половины аудитории, но есть гораздо лучший способ.
= ЕСЛИ, а затем сразу переходим к функции с именем И, поэтому внутри И мы помещаем все тесты: Выручка> 20000, запятая, это процент валовой прибыли> 0,5. Если было больше тестов, продолжайте ставить запятые с дополнительными тестами, а затем закройте конец, все в конце должно быть ИСТИНА, чтобы конец был ИСТИННЫМ. Итак, если мы дойдем до этой точки, если конец будет ИСТИНА, 0,02 * доход, в противном случае 0, это более короткая формула, ее легче ввести, вы получите те же результаты, жизнь прекрасна.
Хорошо, из всех семинаров, которые я провел за последние 15 лет, только однажды кто-то вошел и ударил меня этой сумасшедшей формулой. Она сказала: «Послушайте, мы просто собираемся сделать = 0,02 * дохода, вот так». Хорошо, посчитайте бонус, я такой: «Эй, подождите, это будет дорого, вы собираетесь дать бонус всем». она такая: «Погодите, я еще не закончила, раз, а затем в скобки мы поместим каждое условие, так что выручка> 20000 раз, в скобках процент валовой прибыли> 0,5». Хорошо, и вот что происходит: мы вычисляем бонус, а затем он оценивается как ИСТИНА или ЛОЖЬ. И когда мы заставляем Excel умножать ИСТИНУ или ЛОЖЬ на число, ИСТИНА становится 1, все, что * 1 является самим собой, ЛОЖЬ становится 0! Итак, у нас есть 2% * доход * 1 * 0, что угодно * 0 = 0, верно,так что это очищает бонус. Это логическая логика, ИСТИНА * ИСТИНА, 1 * 1 = 1, если либо они ЛОЖЬ, либо все ЛОЖЬ, это будет оцениваться как 0, и мы получим точно такой же результат. Думаю, тебе стоит перейти на эту? Нет, это сбивает с толку, если вы не уйдете с работы на следующей неделе и ненавидите своих коллег, тогда смело переходите на это, хорошо.
Если вам нравится функция И, есть другие функции или проверки, чтобы увидеть, истинно ли какое-либо из условий, поэтому то или то, или это, оно вернет ИСТИНА. НЕ будет преобразовывать ИСТИНА в ЛОЖЬ, а ЛОЖЬ в ИСТИНУ, что полезно, когда вы пытаетесь реализовать логические концепции NAND или NOR. NAND означает не-и, это ИСТИНА, когда хотя бы одно условие - ЛОЖЬ, хорошо. Так что, если ни один из них не ИСТИНА, это здорово, если некоторые из них ИСТИННЫ, это здорово, но как только все они ИСТИННЫ, мы не платим. NOR означает не - или, это означает, что ни одно из условий не является ИСТИННЫМ - если это произойдет, или это произойдет, или это произойдет, для вас не будет бонуса. А затем XOR, теперь будьте осторожны с этим, он был представлен в Excel 2013 и не делает того, что мы, бухгалтеры, думаем, он должен делать.Исключающее или означает, что только один из тестов является ИСТИННЫМ, и он работает при двух условиях. Но инженеры-электрики делают это в паре, так что это не дает результатов, о которых вы могли подумать.
Хорошо, вот тест 1, тест 2, тест 3, тест 4, три из них ИСТИНА, а XOR говорит: "Точно ли одно из них ИСТИНА?" И когда мы выполняем XOR, он говорит: «Да, именно тот, который ИСТИНА». и это потому, что функция Excel дублирует работу очень распространенного чипа, который используется в электротехнике, я знаю, это шокирует, не так ли? Вы думаете, что Excel предназначен только для бухгалтеров, но инженеры также используют Excel, и они, очевидно, добавили XOR для них, а не для бухгалтеров. Итак, как это оценивается, когда они смотрят на первые два: «Является ли одно из этих двух ИСТИННЫМ? Да!" Хорошо, мы получаем это ИСТИНА, а затем они берут ответ от XOR этого и сравнивают его с ИСТИННЫМ: «Один из этих двух ИСТИНА? Нет, 2 из них ИСТИННЫ, так что это становится ЛОЖНОЙ! » Затем они берут этот ответ и выполняют XOR с последним,так они делают это парами, верно? «Один из этих двух ИСТИННЫЙ? Да!" Хорошо, вот как мы это понимаем. Оказывается, что то, что он на самом деле делает, электротехника - это подсчет, если нечетное количество входов ИСТИНА. Не обязательно полезно для бухгалтеров, которые ожидают, что он будет делать то, что означает XOR на английском языке.
Хорошо, в этой книге много замечательных советов, полезных вещей и даже это ужасное обсуждение NAND, XOR и тому подобного. Купите книгу, и все эти советы будут у вас на ладони. Резюме с сегодняшнего дня: простейшая функция ЕСЛИ, логический тест = ЕСЛИ, что делать, если оно ИСТИНА, что делать, если оно ЛОЖНО, но если у вас есть 2 условия, многие люди вкладывают операторы ЕСЛИ, но просто представьте, если бы у вас было 3 , 5 или 17 условий для вложения. И решит это, немного сократит его, поэтому, если вам нравится И, есть также ИЛИ или НЕ, вы можете использовать NAND, вы можете использовать NOR, но будьте осторожны при использовании этого нового Excel 2013 XOR, результаты могут быть не такими, как вы ожидать.
Хорошо, эй, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2025.xlsx
