求一个公式，谢谢

很牛的仔

huaichuan1 发表于 2024-2-27 13:27
G2=IFS(F3>=8000,E3,F3>3000,E3*(20%-20%/5000*(F3-3000))+E3,F3

更新了附件， 帮忙再看下，谢谢！

qydn

=IF(F3<500,$E$3*(1+0.5),IF(F3>=8000,$E$3,$E$3+$E$3*(LOOKUP(F3,{500,0.5;1001,0.4;2001,0.3;3001,0.2})-LOOKUP(F3,{500,0.5,0.1;1001,0.4,0.1;2001,0.3,0.1;3001,0.2,0.2})/LOOKUP(F3,{500,0.5,0.1,500;1001,0.4,0.1,1000;2001,0.3,0.1,1000;3001,0.2,0.2,5000})*(F3-LOOKUP(F3,{500,500;1001,1000;2001,2000;3001,3000})))))
你验证下，应该没问题的

miaojohn45

很牛的仔 发表于 2024-2-27 14:14
更新了附件， 帮忙再看下，谢谢！

1. =IFNA(MAX(0,LOOKUP(F3,{500,1000,2000,3000},{0.5,0.4,0.3,0.2})-(F3-LOOKUP(F3,{500,1000,2000,3000}))*IF(MATCH(F3,{500,1000,2000,3000})>3,0.2,0.1)/CHOOSE(MATCH(F3,{500,1000,2000,3000}),500,1000,1000,5000)),0.5)*E3+E3

复制代码

1. =LET(n,{500,1000,2000,3000},x,MATCH(F3,n),IFNA(MAX(0,LOOKUP(F3,n,{0.5,0.4,0.3,0.2})-(F3-LOOKUP(F3,n))*IF(x>3,0.2,0.1)/CHOOSE(x,500,1000,1000,5000)),0.5)*E3+E3)

复制代码

mykitami

=LOOKUP(F3,J2:J7,E3*(1+({0.5;0.5;0.4;0.3;0.2;0}-{0;0.1;0.1;0.1;0.2;0}/({1;5;10;10;50;1}*100)*(F3-{0;5;10;20;30;0}*100))))

很牛的仔

mykitami 发表于 2024-2-27 16:48
=LOOKUP(F3,J2:J7,E3*(1+({0.5;0.5;0.4;0.3;0.2;0}-{0;0.1;0.1;0.1;0.2;0}/({1;5;10;10;50;1}*100)*(F3-{0; ...

谢谢