接力赛：连续数值的个数统计

dengyf · 发表于 2010-8-12 09:39

严禁跟贴灌水，否则每贴扣罚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 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></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(--(FREQUENCY(SMALL(A$2:C$8,ROW($1:$21)),IF(MMULT(COLUMN(A:U)^0,N(COLUMN(A:AD)<>SMALL(A$2:C$8,ROW($1:$21))))=21,COLUMN(A:AD)))=ROW()-1))    <div class="msgheader">QUOTE:</div><div class="msgborder">获得金币请到此链接跟贴，报楼层，麻烦大家了。<a href="http://www.excelpx.com/forum.php?mod=viewthread&tid=137779">http://www.excelpx.com/forum.php?mod=viewthread&tid=137779</a></div><div class="msgheader">QUOTE:</div><div class="msgborder">获得金币请到此链接跟贴，报楼层，麻烦大家了。<a href="http://www.excelpx.com/forum.php?mod=viewthread&tid=137779">http://www.excelpx.com/forum.php?mod=viewthread&tid=137779</a></div>

[此贴子已经被作者于2010-8-13 9:48:09编辑过]

xingxingsuip · 发表于 2010-8-12 09:57

看看严禁跟贴灌水，否则每贴扣罚20金币

[此贴子已经被tkgg93于2010-8-12 10:08:36编辑过]

MM309755469 · 发表于 2010-8-12 10:04

來學習[em04]严禁跟贴灌水，否则每贴扣罚20金币

[此贴子已经被tkgg93于2010-8-12 10:08:54编辑过]

hugong115 · 发表于 2010-8-12 10:12

学习[em32]严禁跟贴灌水，否则每贴扣罚20金币，三位已各扣20金币

[此贴子已经被tkgg93于2010-8-12 10:16:09编辑过]

皇太极 · 发表于 2010-8-12 17:06

我先来第一棒，减1个字符=SUM(N(FREQUENCY(SMALL(A$2:C$8,ROW($1:$21)),IF(MMULT(COLUMN(A:U)^0,N(COLUMN(A:AD)<>SMALL(A$2:C$8,ROW($1:$21))))=21,COLUMN(A:AD)))=ROW()-1))

皇太极 · 发表于 2010-8-12 17:21

接着来第二棒，123字符=SUM(N(FREQUENCY(SMALL(A$2:C$8,ROW($1:$21)),IF(ISNA(MATCH(ROW($1:$99),SMALL(A$2:C$8,ROW($1:$21)),)),ROW($1:$99)))=ROW()-1))

皇太极 · 发表于 2010-8-12 17:26

第三棒，119=SUM(N(FREQUENCY(SMALL(A$2:C$8,ROW($1:$21)),ISNA(MATCH(ROW($1:$99),SMALL(A$2:C$8,ROW($1:$21)),))*ROW($1:$99))=ROW()-1))

皇太极 · 发表于 2010-8-12 17:28

第四棒，100字符=SUM(N(FREQUENCY(A$2:C$8,ISNA(MATCH(ROW($1:$99),SMALL(A$2:C$8,ROW($1:$21)),))*ROW($1:$99))=ROW()-1))

皇太极 · 发表于 2010-8-12 17:35

第五棒，98字符=SUM(N(FREQUENCY(A$2:C$8,ISNA(MATCH(ROW($1:99),SMALL(A$2:C$8,ROW($1:$21)),))*ROW($1:99))=ROW()-1))

wyywym · 发表于 2010-8-12 17:39

这个算不算，95字符=SUM(N(FREQUENCY(A$2:C8,ISNA(MATCH(ROW($1:99),SMALL(A$2:C8,ROW($1:21)),))*ROW($1:99))=ROW()-1))

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

接力赛：连续数值的个数统计

本帖子中包含更多资源