Excel: на основе значения ячейки в столбце A (три возможных значения) и ячейки в столбце B (целое число) определите значение ячейки C.

Question

Я пытаюсь решить проблему следующим образом:

1. Столбец A: одно из трех возможных значений. {яблоки, апельсины, груши}

2. Столбец B: целое число.

3. Значение столбца C следует рассчитывать на основе предыдущих двух столбцов.

Для столбца B он должен находиться в диапазоне чисел. Например, (1-19, 20-99). Так, для яблок, если число находится в диапазоне от 1 до 19, значение должно быть 12, если число находится в диапазоне от 20 до 99, значение должно быть 4, больше этого, значение должно быть 18.

Диапазоны и значения, которые должны быть выведены, различны для каждого из фруктов.

На данный момент лучшее, что я могу сделать, — это длинная цепочка вложенных операторов if, что, по моему мнению, не является оптимальным.

=IF(I74=Reference!$A$2,(IF(AND(H74>=1,H74<=19),Reference!$B$4,IF(AND(H74>=20,H74<=149), Reference!$C$4,IF(H74>=150,Reference!$D$4,"NaN")))),(IF(AND(H74>=1,H74<=19),Reference!$B$8,IF(AND(H74>=19,H74<=99), Reference!$C$8, IF(H74>=100, Reference!$D$8, "NaN")))))

Я был бы очень признателен за любую помощь. Спасибо.

Answer 1

У вас должна быть справочная таблица, описывающая предельное количество каждого товара, и отсортируйте количество DESCENDING.

Ну вот:

=INDEX($C$2:$C$4, MATCH(1, (F2 = $A$2:$A$4) * (G2 > $B$2:$B$4), 0))

и используйте CTRL+ Shift+ Enterвместо , Enterчтобы вставить формулу массива.

Объяснение

$C$2:$C$4это диапазон справочной цены.

Поскольку у нас есть два критерия для поиска, используйте MATCH(1, (criteria1) * (criteria2) * (...), 0). Формула аналогична MATCH(TRUE, criteria1 AND criteria2 AND ...).

По сути, это MATCHпоиск наименования товара в таблице, а ограничение по количеству меньше его значения.

В моем примере я только указал applesсправочную таблицу. Вы должны добавить свой orangesи pearsсебя, очевидно.