Вернуть значение, если другая ячейка имеет одно из многих значений

Question

Я не уверен, что функция IF хотя бы правильная для запуска, но вопрос в следующем:
У меня есть ячейка Y8 и она отображает разные цифры:

0,5,10,15,20,35,50

Мне нужно ввести формулу, которая возвращает текстовое значение, но у меня есть 4 аргумента.

Мне нужно, чтобы вернуть следующее:
Если это...

• ... меньше 10 = Уровень 1.
• ... между 10-19 = Уровень 2.
• ... 20:29 = Уровень 3
• ... 30+ = Уровень 4.

Используя функцию IF мне удалось получить возвращаемое значение либо уровня 1, либо уровня 2 в зависимости от числа, которое постоянно меняется.

Я использовал следующую формулу:

=IF(Y8<=9, "Level 1", IF(Y8<="10:19", "Level 2")) 

Любые идеи о том, какую формулу я могу использовать, чтобы получить возвращаемое значение для всех 4 аргументов?

Answer 1

Вы можете использовать ВЫБРАТЬ с МАТЧ

=CHOOSE(MATCH(Y8,{0,10,20,30}),"Level 1","Level 2","Level 3","Level 4")

---

Если вы действительно хотите Level и число, которое мы можем сделать:

="Level " & MATCH(Y8,{0,10,20,30})

Answer 2

Создайте отсортированную таблицу поиска с Level соответствующим каждому порогу:

Value    Level
 0       1
10       2
20       3
30       4

Теперь вы можете использовать простой VLOOKUP чтобы получить Level любым Value , и если пороги когда-либо нужно будет изменить, или если нужно добавить новые уровни, формулу VLOOKUP менять не нужно - просто ведите таблицу порогов и сделанный.

="Level " & VLOOKUP(theValue, theLookupTable, 2, TRUE)

Обратите внимание на аргумент TRUE для последнего параметра, заставляя VLOOKUP использовать приблизительное совпадение, а не типичный режим точного сопоставления . Пока пороги сортируются по возрастанию, VLOOKUP будет возвращать значение Level для наибольшего Value , которое все еще меньше значения поиска.

Answer 3

Мне нравится ответ Скотта Крейнера. Однако я подумал, что укажу на некоторые другие варианты.

Если у вас Excel 2016 или более поздняя версия, вы можете использовать функцию IFS чтобы упростить структуру вложенных функций IF .

=IFS(Y8<10,"Level 1",Y8<20,"Level 2",Y8<30,"Level 3",TRUE,"Level 4")

Как это работает:

• Если Y8 меньше 10, он вернет "Уровень 1", в противном случае ...
• Если Y8 меньше 20, он вернет "Уровень 2", в противном случае ...
• Если Y8 меньше 30, он вернет "Уровень 3", в противном случае ...
• Вернется "Уровень 4"

В примере, приведенном в вопросе, число "уровень" увеличивается на единицу каждый раз, когда значение в Y8 увеличивается на 10. В этом особом случае можно рассчитать номер уровня без использования IF .

="Level "&INT(MIN(30,MAX(0,Y8))/10)+1

Обратите внимание, что в вопросе говорится, что любое значение меньше 10 - это уровень 1, а любое значение больше 30 - это уровень 4. Чтобы заставить отрицательные числа быть "уровнем 1", а числа больше 39 - уровнем 4, формула использует MIN(30,MAX(0,Y8)) вместо Y8 .

Answer 4

Другое решение - создать таблицу, содержащую нижние границы ваших значений и использующую VLOOKUP.

таблица со значениями уровня

=VLOOKUP(lookup value, table array, col_index_num,[range lookup])

Последняя переменная в VLOOKUP - "Приблизительное совпадение" ИСТИНА / ЛОЖЬ

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

этот метод также позволяет увеличивать ваши параметры и уровни на лету.

Answer 5

Есть два способа сделать это в зависимости от того, насколько тщательно вы хотите быть.

Лично я предпочитаю первый пример:

=IF(Y8<=9, "Level 1", IF(Y8<=19, "Level 2", IF(Y8<=29 "Level 3", "Level 4")))

Вы не беспокоитесь об определении уровня 2 как минимум 10, так как подразумевается, что Y8 должен быть больше 9, если не сопоставлять и не возвращать "уровень 1".

Если вы хотите действительно тщательно определить верхнюю и нижнюю границы, вы можете сделать что-то вроде этого, это также включает в себя возврат предупреждения / ошибки в случае, если значение в Y8 не является числом:

=IF(Y8<=9, "Level 1", IF(AND(Y8>=10, Y8<=19), "Level 2", IF(AND(Y8>=20, Y8<=29), "Level 3", IF(Y8>=30, "Level 4", "Not a number or some other error"))))

Answer 6

В качестве альтернативы использованию вложенных IFs , вы можете использовать CHOOSE:

=IF(Y8>30,"Level 4",CHOOSE(MAX(1,ROUNDUP((Y8-1)/10,0)),"Level 1","Level 2","Level 3"))

Там, где есть только 4 различных условия, я обычно использовал бы ряд вложенных IFs , но если бы вы сказали, что 100 условий, гораздо проще использовать CHOOSE .