接力赛：返回最大汇总列的最大明细对应姓名

兰色幻想 · 发表于 2010-8-1 11:47

严禁跟贴灌水，否则每贴扣罚20金币        1、 什么是接力赛？详见 好玩的竟赛模式：优化接力赛<a href="http://www.excelpx.com/forum.php?mod=viewthread&tid=136591">http://www.excelpx.com/forum.php?mod=viewthread&tid=136591</a>        2、本题目奖励基数为2，奖励系数为1.35 ，详细请见附表。        3、参与方式：下载附件然后对公式进行优化，如果你的公式式长度短于目前最短的公式长度，你就可以回贴提交公式（注明公式长度）和附件<hr/>        4、题目：要求返回汇总行最大值所在的列的最大明细值所对应的姓名。具体详见附件。             注：不能使用辅助单元格、定义名称和VBA    附：奖励表                <table cellspacing="0" cellpadding="0" width="160" border="0" style="WIDTH: 120pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="88" style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816;"></col><col></col><col width="72" style="WIDTH: 54pt;"></col><col></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl23" width="88" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ccffcc;">基数</td><td class="xl23" width="72" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc;">2</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第1棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B1*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">3</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第2棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B2*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">4</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第3棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B3*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">5</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第4棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B4*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">7</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第5棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B5*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">9</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第6棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B6*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">12</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第7棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B7*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">16</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第8棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B8*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">22</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第9棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B9*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">30</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第10棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B10*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">41</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第11棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B11*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">55</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第12棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B12*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">74</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第13棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B13*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">100</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第14棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B14*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">135</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第15棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B15*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">182</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第16棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B16*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">246</td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;">第17棒奖励</td><td class="xl22" align="right" xfmla="=ROUND(B17*1.35,)" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;">332</td></tr></tbody></table>

[此贴子已经被作者于2010-8-1 11:48:49编辑过]

chensir · 发表于 2010-8-1 12:33

看错题目了

[此贴子已经被作者于2010-8-1 12:38:15编辑过]

wjh619993018 · 发表于 2010-8-1 12:50

吓我一跳，还好2楼的公式不对，要好好的利用规则，先来第二棒=INDEX(B:B,MATCH(MAX(OFFSET(B4,,MATCH(MAX(C11:G11),11:11,)-2,7)),OFFSET(B1,,MATCH(MAX(C11:G11),11:11,)-2,10),))111字符

[此贴子已经被作者于2010-8-1 12:52:14编辑过]

wjh619993018 · 发表于 2010-8-1 13:01

第三棒 107字符=INDEX(B:B,MATCH(MAX(OFFSET(B4,,MATCH(MAX(11:11),11:11,)-2,7)),OFFSET(B1,,MATCH(MAX(11:11),11:11,)-2,10),))

一剑惊心 · 发表于 2010-8-1 13:03

=INDEX(B:B,MOD(MAX(C11:G11/1%%+C4:G10/1%+ROW(4:10)),100))

chunlin1wang · 发表于 2010-8-1 13:17

94一剑老师改方法也行吗？

wjh619993018 · 发表于 2010-8-1 13:25

="R"&MOD(MAX(C11:G11/1%%+C4:G10/1%+ROW(4:10)),100)-3

wjh619993018 · 发表于 2010-8-1 13:33

="R"&MOD(MAX(C11:G11/1%%+C4:G10/1%+ROW(1:7)),100)

fjmxwrs · 发表于 2010-8-1 13:41

48个="R"&MOD(MAX(C11:G11/1%%+C4:G10/1%+ROW(1:7)),10)不符合要求，该楼成绩不计入我试了怎么是符合要求的？呵呵

[此贴子已经被作者于2010-8-1 14:00:25编辑过]

fjmxwrs · 发表于 2010-8-1 13:47

48个的有点取巧，来个56个的=OFFSET(B3,MOD(MAX(C11:G11/1%%+C4:G10/1%+ROW(1:7)),10),)

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

接力赛：返回最大汇总列的最大明细对应姓名

本帖子中包含更多资源

本帖子中包含更多资源

本帖子中包含更多资源

浏览过的版块