Вопрос:
Интересно, смогу ли я получить помощь, чтобы получить правильную формулу, чтобы выяснить comm% на основе цены, на которую она была продана.
В принципе, есть две таблицы.
Первая – наша ценовая сетка, чтобы показать, какой будет комиссионный pct:
Product — 20% — 15% — 10% Widget 1 — 3990 — 3490 — 3090 Widget 2 — 4990 — 4490 — 4090 Widget 3 — 5990 — 5490 — 5090
Вторая таблица сообщит о проданных Продуктах и по какой цене она была продана по адресу:
Widget 2 — $4690 Widget 3 — $5100 Widget 1 — $4000
Я думал INDEX/MATCH. Я могу правильно сделать MATCH, чтобы найти правильную строку, но я не уверен, как включить этот диапазон цен в формулу.
В приведенном выше примере комиссия должна составлять 15%, 10% и 20% соответственно.
Необходимо создать формулу, которая соответствует Продукту, а затем берет проданную цену и сопоставляет ее с тем, в какой столбец она будет подпадать, и дать мне% для этого столбца.
Я провел недели в сети, и я могу добраться только на полпути. MATCH, чтобы выяснить, в какую строку находится Продукт, просто, это сопоставимость цен, которая заставила меня остановиться.
Спасибо.
Лучший ответ:
Вы можете решить эту проблему, используя редко используемую функцию Hlookup, используя параметр range_lookup. Единственным требованием для этого является то, что вы переупорядочиваете свои данные комиссии с низкого% до высокого%. например:
Product 10% 15% 20% Widget 1 3090 3490 3990 Widget 2 4090 4490 4990 Widget 3 5090 5490 5990
Если вы это сделали, вы можете использовать эту формулу, чтобы получить правильную комиссию
=OFFSET(Sheet1!$A$1;0;MATCH(HLOOKUP(INT(SUBSTITUTE(B1; «$»; «»));INDIRECT(«Sheet1!»&MATCH(A1;Sheet1!$A:$A ) & «:» & MATCH(A1;Sheet1!$A:$A ));1; TRUE);INDIRECT(«Sheet1!»&MATCH(A1;Sheet1!$A:$A ) & «:» & MATCH(A1;Sheet1!$A:$A)) )-1)
Это пробой:
Формат ввода как целого (данные комиссии – int, значения – $)
SUBSTITUTE(B1; «$»; «»)
Используйте ввод, чтобы собрать ссылочную строку для Hlookup. Мы используем косвенное значение для добавления имени листа и “:” в ссылку.
INDIRECT(«Sheet1!»&MATCH(A1;Sheet1!$A:$A ) & «:» & MATCH(A1;Sheet1!$A:$A ))
С помощью Hlookup найдите подходящее значение:
HLOOKUP(INT(SUBSTITUTE(B1; «$»; «»));INDIRECT(«Sheet1!»&MATCH(A1;Sheet1!$A:$A ) & «:» & MATCH(A1;Sheet1!$A:$A ));1; TRUE)
Используйте найденное значение в функции горизонтального сопоставления для поиска используемого столбца и вычитайте 1:
MATCH(<hlookup>; INDIRECT(«Sheet1!»&MATCH(A1;Sheet1!$A:$A ) & «:» & MATCH(A1;Sheet1!$A:$A))) — 1
Используйте смещение, чтобы найти подходящее значение
Offset(Sheet1!$A$1;0; <match>)
