接力赛：递减型业务费计算

dengyf · 发表于 2010-8-14 08:48

严禁跟贴灌水，否则每贴扣罚20金币           1、 什么是接力赛？详见 好玩的竟赛模式：优化接力赛<a href="http://www.excelpx.com/forum.php?mod=viewthread&tid=136591" target="_blank">http://www.excelpx.com/forum.php?mod=viewthread&tid=136591</a>        2、本题目奖励基数为10，奖励系数为1.2，详细请见附表。        3、参与方式：下载附件然后对公式进行优化，如果你的公式式长度短于目前最短的公式长度，你就可以回贴提交公式（注明公式长度）        4、公式必须适用于2003版本        5、题目的具体要求见附件奖励：<table width="201" border="0" cellspacing="0" cellpadding="0" style="WIDTH: 151pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="72" style="WIDTH: 54pt;"></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col width="129" style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4128;"></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td width="72" height="19" class="xl23" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid;">基数</td><td width="129" align="right" class="xl23" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 97pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid;">10</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第1棒</td><td align="right" class="xl22" xfmla="=ROUND(B1*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">12</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第2棒</td><td align="right" class="xl22" xfmla="=ROUND(B2*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">14</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第3棒</td><td align="right" class="xl22" xfmla="=ROUND(B3*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">17</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第4棒</td><td align="right" class="xl22" xfmla="=ROUND(B4*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">20</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第5棒</td><td align="right" class="xl22" xfmla="=ROUND(B5*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">24</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第6棒</td><td align="right" class="xl22" xfmla="=ROUND(B6*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">29</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第7棒</td><td align="right" class="xl22" xfmla="=ROUND(B7*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">35</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第8棒</td><td align="right" class="xl22" xfmla="=ROUND(B8*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">42</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第9棒</td><td align="right" class="xl22" xfmla="=ROUND(B9*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">50</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第10棒</td><td align="right" class="xl22" xfmla="=ROUND(B10*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">60</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第11棒</td><td align="right" class="xl22" xfmla="=ROUND(B11*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">72</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第12棒</td><td align="right" class="xl22" xfmla="=ROUND(B12*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">86</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第13棒</td><td align="right" class="xl22" xfmla="=ROUND(B13*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">103</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第14棒</td><td align="right" class="xl22" xfmla="=ROUND(B14*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">124</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第15棒</td><td align="right" class="xl22" xfmla="=ROUND(B15*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">149</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第16棒</td><td align="right" class="xl22" xfmla="=ROUND(B16*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">179</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第17棒</td><td align="right" class="xl22" xfmla="=ROUND(B17*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">215</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第18棒</td><td align="right" class="xl22" xfmla="=ROUND(B18*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">258</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第19棒</td><td align="right" class="xl22" xfmla="=ROUND(B19*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">310</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td height="19" class="xl22" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">第20棒</td><td align="right" class="xl22" xfmla="=ROUND(B20*1.2,)" style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid;">372</td></tr></tbody></table>   原公式为=SUM(--TEXT(IF(A8>={3,5,7,10,99999}*500,{15,8,6,6,0}*1000,IF(A8<{3,5,7,10,99999}*500,{10,8,6,4,3}*(A8-{0,3,5,7,10}*500),0)),"0.00;!0"))不算等号134字符

fjmxwrs · 发表于 2010-8-14 09:18

不算等号129=IF(A8<=1500,A8*10,IF(A8<=2500,(A8-1500)*8+15000,IF(A8<=3500,(A8-2500)*6+23000,IF(A8<=5000,(A8-3500)*4+29000,(A8-5000)*3+35000))))没注意到精度，加上138字符了，晕=ROUND(IF(A8<=1500,A8*10,IF(A8<=2500,(A8-1500)*8+15000,IF(A8<=3500,(A8-2500)*6+23000,IF(A8<=5000,(A8-3500)*4+29000,(A8-5000)*3+35000)))),2)

[此贴子已经被作者于2010-8-14 9:34:07编辑过]

dengyf · 发表于 2010-8-14 09:22

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用fjmxwrs在2010-8-14 9:18:00的发言： 不算等号129=IF(A8<=1500,A8*10,IF(A8<=2500,(A8-1500)*8+15000,IF(A8<=3500,(A8-2500)*6+23000,IF(A8<=5000,(A8-3500)*4+29000,(A8-5000)*3+35000)))) </div>未作出精度为分计算。(汗,业务费分类做的太少了,应该超出七层,没想到这个问题)

chunlin1wang · 发表于 2010-8-14 10:47

不算等号92=ROUND(LOOKUP(A15,{0,3,5,7,10}*500,{10,8,6,4,3}*(A15-{0,3,5,7,10}*500)+{0,15,23,29,35}*10^3),2)

[此贴子已经被作者于2010-8-14 10:51:03编辑过]

wjh619993018 · 发表于 2010-8-14 11:44

借14楼公式，减一个字符，不算等号91个=ROUND(LOOKUP(A8%,{0,3,5,7,10}*5,{10,8,6,4,3}*(A8-{0,3,5,7,10}*500)+{0,15,23,29,35}*10^3),2)

dengyf · 发表于 2010-8-14 12:31

其实，可以参考个税公式的，大家加油哦

HGH · 发表于 2010-8-14 16:43

=A8*10-SUM(TEXT(A8-{3,5,7,10}*500,"0.00;!0")*{2,2,2,1})忘了说了 54个字符

[此贴子已经被作者于2010-8-14 16:44:36编辑过]

dengyf · 发表于 2010-8-14 17:16

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用HGH在2010-8-14 16:43:00的发言： =A8*10-SUM(TEXT(A8-{3,5,7,10}*500,"0.00;!0")*{2,2,2,1})忘了说了 54个字符 </div>分位数精度不对。

chunlin1wang · 发表于 2010-8-14 17:20

=ROUND(LOOKUP(A8%,{0,3,5,7,10}*5,{10,8,6,4,3}*A8+{0,3,8,15,20}*10^3),2)70字符

HGH · 发表于 2010-8-14 17:27

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用dengyf在2010-8-14 17:16:00的发言： 分位数精度不对。</div>=A8*10-SUM(TEXT(A8-{3,5,7,10}*500,"0.0000;!0")*{2,2,2,1})56

		自动登录	找回密码
密码			注册

接力赛：递减型业务费计算

本帖子中包含更多资源