Определение правильной ставки комиссии - MS Excel Challenge

Question

У меня есть таблица номеров товаров, цен и соответствующих комиссионных ставок, выложенная следующим образом ...

   A     |    B    |    C    |    D    |    E    |   F   |  G   |  H   |  I
XL-9000  |  $6500  |  $5500  |  $4500  |  $3500  |  10%  |  8%  |  6%  |  5%

Если продажа XL-9000 была совершена за 6500 долларов США или более, продавец получает комиссию в размере 10%.

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

Помощь действительно ценится!

Answer 1

Я сделал одну очень длинную формулу, которая сделает это:

=IFERROR(INDEX(INDIRECT("F"&MATCH(K1,$A$1:$A$5,0)&":I"&MATCH(K1,$A$1:$A$5,0)),1,IFERROR(MATCH(L1,INDIRECT("B"&MATCH(K1,$A$1:$A$5,0)&":E"&MATCH(K1,$A$1:$A$5,0)),-1),0)+1),0)

Дело в том, что он во многом зависит от MATCH(K1,$A$1:$A$5,0) который дает строку искомого номера товара. Если я подставлю их в формулу XX в формуле, то получится: вам нужно будет изменить $A$5 на правильную ссылку, чтобы она соответствовала вашей таблице, и есть 4 экземпляра.

=IFERROR(INDEX(INDIRECT("F"&XX&":I"&XX,1,IFERROR(MATCH(L1,INDIRECT("B"&XX&":E"&XX,0)),-1),0)+1),0)

Здесь K1 - это ячейка товара товара, а L1 - объем продаж. Ниже приведен пример:

(Открыть в новой вкладке или в новом окне для увеличения версии)

Answer 2

Если предположить, что данные находятся в строке 1, и вы собираетесь поместить фактическую цену продажи в ячейку J1 тогда следующая формула может перейти в K1 чтобы определить соответствующую сумму комиссии:

=IF(J1>B1,F1,IF(J1>C1,G1,IF(J1>D1,H1,IF(J1>E1,I1,"No commission"))))

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