[讨论]提成

jacksonzeng

嗯～～～思考之中～～可否说详细点

山菊花

注意：表３数据已删除。

以H6单元格公式为例：

１、在sheet2!A:A中查找发货单位(A6)所在行号：

=Sheet1!$D6*IF(GET.CELL(24,INDEX(Sheet2!$A:$A,MATCH(Sheet1!$A6,Sheet2!$A:$A,)))=3,LOOKUP(ROUND(Sheet1!$G6,2),{0,0.2,0.3,0.4,0.5},{0.1,0.15,0.2,0.25,0.3}),0.05)

２、返回该单元格引用：

=Sheet1!$D6*IF(GET.CELL(24,INDEX(Sheet2!$A:$A,MATCH(Sheet1!$A6,Sheet2!$A:$A,)))=3,LOOKUP(ROUND(Sheet1!$G6,2),{0,0.2,0.3,0.4,0.5},{0.1,0.15,0.2,0.25,0.3}),0.05)

３、返回该单元格的（第一个字符）的颜色号：

=Sheet1!$D6*IF(GET.CELL(24,INDEX(Sheet2!$A:$A,MATCH(Sheet1!$A6,Sheet2!$A:$A,)))=3,LOOKUP(ROUND(Sheet1!$G6,2),{0,0.2,0.3,0.4,0.5},{0.1,0.15,0.2,0.25,0.3}),0.05)

４、如果颜色号等于３（红色），计算下面表达式的值：

=Sheet1!$D6*IF(GET.CELL(24,INDEX(Sheet2!$A:$A,MATCH(Sheet1!$A6,Sheet2!$A:$A,)))=3,LOOKUP(ROUND(Sheet1!$G6,2),{0,0.2,0.3,0.4,0.5},{0.1,0.15,0.2,0.25,0.3}),0.05)

如果颜色号不等于３，取后面数值0.05。

５、用sheet1!D6*第４步计算结果

shah

非常感谢山菊花不厌其烦的讲解，小女子才疏学浅，却有幸于山菊花的神笔指点，使小女子受益非浅，山菊花啊我这抛出来的砖，不知能否再次引来颜色号等于３（红色）的玉？

[此贴子已经被作者于2006-5-13 21:48:04编辑过]

whsfhwm

看的晕头晕脑的

shah

山菊花 斑竹 还可以请叫一个问题吗?