Excel佣金公式

Question

想知道我是否可以得到帮助，获得正确的公式，以根据销售价格计算出comm%。

基本上，有两个电子表格。

第一个是我们的价格表，用于显示佣金百分比:

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 很简单，但价格匹配让我感到困惑。

谢谢。

Answer 1

您可以通过使用很少使用的 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查找匹配值

Offset(Sheet1!$A$1;0; <match>)