[已解决]求助，多条件查找及返回相邻单元格数据

chentaoct

求助，
    有提供附件表单，请高手帮助解答，非常感谢。

参数表如下：

客户名称

合同开始日期

合同结束日期

返点计算方式

固定比例

消耗阶梯①-最小值

消耗阶梯①-最大值

消耗阶梯①-返点比例

消耗阶梯②-最小值

消耗阶梯②-最大值

消耗阶梯②-返点比例

消耗阶梯③-最小值

消耗阶梯③-最大值

消耗阶梯③-返点比例

客户A

2021/1/1

2021/12/31

固定比例

5%

客户B

2021/1/1

2021/3/31

消耗阶梯

1

49999

1%

50000

149999

3%

150000

∞

5%

客户B

2021/4/1

2021/6/30

消耗阶梯

1

49999

1%

50000

149999

2%

150000

∞

3%

问题：如何在合同参数表中自动查找符合“客户名称+合同对应有效期间+销售金额对应消耗阶梯范围”的返点比例，其中有固定返点和消耗阶梯返点。

客户名称	销售期间	销售金额	返点比例	返点金额
客户A	2021年1月	100,000		0
客户B	2021年1月	60,000		0
客户B	2021年4月	200,000		0

最佳答案

月排行榜 / 总排行榜

cutecpu

2021-6-21 15:08

chentaoct 发表于 2021-6-21 15:06
我也尝试了另一种登记的方式，用WPS和EXCEL都能计算准确。

就是登记的消耗阶梯，严格按照0至最大值来 ...

=IFNA(VLOOKUP(C2,1/N(OFFSET(合同参数表!F$1,MATCH(1,(A2=合同参数表!A$2:A$17)*(B2>=合同参数表!B$2:B$17)*(B2<=合同参数表!C$2:C$17),),{0,0;1,3;4,6;7,9;10,12}))^-1,2),)

您用這個試試 WPS 能不能過


祝順心，南無阿彌陀佛！

跳转到最佳答案楼层

baksy

=IF(A2=A$2,5,INDEX(IF(B2=B$3,{1,3,5},{1,2,3}),MATCH(C2,{0,5,15}*10^4)))%

chentaoct

baksy 发表于 2021-6-20 16:15
=IF(A2=A$2,5,INDEX(IF(B2=B$3,{1,3,5},{1,2,3}),MATCH(C2,{0,5,15}*10^4)))%

谢谢回复，不过工作中的实际数据，每个客户各种消耗阶梯的比例是一样的，没办法直接在公式中用数组的形式体现对应的比例，需要从合同参数表中去对应调用相应的比例，才能得到准确的结果。

客户名称

合同开始日期

合同结束日期

返点计算方式

固定比例

消耗阶梯①-最小值

消耗阶梯①-最大值

消耗阶梯①-返点比例

消耗阶梯②-最小值

消耗阶梯②-最大值

消耗阶梯②-返点比例

消耗阶梯③-最小值

消耗阶梯③-最大值

消耗阶梯③-返点比例

客户A

2021/1/1

2021/12/31

固定比例

5%

客户B

2021/1/1

2021/3/31

消耗阶梯

1

49999

1%

50000

149999

3%

150000

∞

5%

客户B

2021/4/1

2021/6/30

消耗阶梯

1

49999

1%

50000

149999

2%

150000

∞

4%

chentaoct

baksy 发表于 2021-6-20 16:15
=IF(A2=A$2,5,INDEX(IF(B2=B$3,{1,3,5},{1,2,3}),MATCH(C2,{0,5,15}*10^4)))%

   谢谢回复！
   不过工作中的实际数据，不同客户各种消耗阶梯的比例是不一样的，没办法直接在公式中用数组的形式体现对应的比例，需要从合同参数表中去对应调用相应的比例，才能得到准确的结果。

baksy

chentaoct 发表于 2021-6-20 18:34
谢谢回复！
   不过工作中的实际数据，不同客户各种消耗阶梯的比例是不一样的，没办法直接在公式中用 ...

楼主提供的就是这些,还能有什么情况？

chentaoct

baksy 发表于 2021-6-20 20:43
楼主提供的就是这些,还能有什么情况？

因为客户数量很多，适用不同的激励政策，比如增加以下内容，实际客户和消耗阶梯以及对应的比例有很多不同，分客户、分季度而定。请帮忙看下，多谢！

客户名称	合同开始日期	合同结束日期	返点计算方式	固定比例	消耗阶梯①-最小值	消耗阶梯①-最大值	消耗阶梯①-返点比例	消耗阶梯②-最小值	消耗阶梯②-最大值	消耗阶梯②-返点比例	消耗阶梯③-最小值	消耗阶梯③-最大值	消耗阶梯③-返点比例
客户A	2021/1/1	2021/12/31	固定比例	5%
客户B	2021/1/1	2021/3/31	消耗阶梯		1	49,999	1%	50,000	149,999	3%	150,000	∞	5.0%
客户B	2021/4/1	2021/6/30	消耗阶梯		1	49,999	1%	50,000	149,999	2%	150,000	∞	4.0%
客户C	2021/1/1	2021/3/31	消耗阶梯		30,000	50,000	2%	50,001	200,000	4%	200,001	∞	5.0%
客户C	2021/4/1	2021/6/30	消耗阶梯		50,000	100,000	2%	100,001	300,000	4%	300,001	∞	5.0%
客户C	2021/7/1	2021/9/30	消耗阶梯		80,000	130,000	2%	130,001	400,000	4%	400,001	∞	5.5%
客户C	2021/10/1	2021/12/31	消耗阶梯		100,000	200,000	2%	200,001	500,000	3%	500,001	∞	6.0%
客户D	2021/1/1	2021/12/31	消耗阶梯		18,000	55,000	3%	55,001	150,000	4%	150,001	∞	4.5%

baksy

chentaoct 发表于 2021-6-20 21:01
因为客户数量很多，适用不同的激励政策，比如增加以下内容，实际客户和消耗阶梯以及对应的比例有很多不同 ...

请上传附件。

chentaoct

baksy 发表于 2021-6-20 21:03
请上传附件。

已更新附件，需要计算的我只列出了客户A和客户B，没有把所有的客户加到数据表中。

baksy

chentaoct 发表于 2021-6-20 21:07
已更新附件，需要计算的我只列出了客户A和客户B，没有把所有的客户加到数据表中。

请把实际需要的都列出来，免得到时候再有问题。

chentaoct

baksy 发表于 2021-6-20 21:09
请把实际需要的都列出来，免得到时候再有问题。

您好！
     我已更新附件，增加了更多的客户数据，感谢提供的帮助。