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

Question

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

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

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 самостоятельно, очевидно.