单元格数字提取循环相加

hqhl2008 · 发表于 2008-6-1 23:03

题目要求：将单元格中的数字提取出来，然后循环相加，最后得到一位数。例如A1单元格:A568ee4567K89→5+6+8+4+5+6+7+8+9=58→5+8=13→1+3=4请用回复可见答题，前6名回答正确，各奖励5个BB<table cellspacing="0" cellpadding="0" width="237" border="0" style="WIDTH: 178pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="165" style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 5280;"></col><col width="72" style="WIDTH: 54pt;"></col></colgroup><tbody><tr height="29" style="HEIGHT: 21.75pt; mso-height-source: userset;"><td class="xl65" width="165" height="29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 124pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: yellow;"><font face="

MingLiU">数据</td><td class="xl65" 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: yellow;"><font face="

MingLiU">结果</td></tr><tr height="29" style="HEIGHT: 21.75pt; mso-height-source: userset;"><td class="xl67" height="29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent;">A568ee4567K89</td><td class="xl66" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1;">4</td></tr><tr height="29" style="HEIGHT: 21.75pt; mso-height-source: userset;"><td class="xl67" height="29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent;">3098a36567ks8</td><td class="xl66" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1;">1</td></tr><tr height="29" style="HEIGHT: 21.75pt; mso-height-source: userset;"><td class="xl67" height="29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent;">yt78995BK23999D8999</td><td class="xl66" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1;">6</td></tr><tr height="29" style="HEIGHT: 21.75pt; mso-height-source: userset;"><td class="xl67" height="29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent;">5639899699998978</td><td class="xl66" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1;">6</td></tr><tr height="29" style="HEIGHT: 21.75pt; mso-height-source: userset;"><td class="xl67" height="29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent;">563华3856中809</td><td class="xl66" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1;">8</td></tr><tr height="29" style="HEIGHT: 21.75pt; mso-height-source: userset;"><td class="xl67" height="29" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent;">中国223AB56c56</td><td class="xl66" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1;">2</td></tr></tbody></table>

liuhao_goody · 发表于 2008-6-2 01:29

游客，如果您要查看本帖隐藏内容请回复

[此贴子已经被作者于2008-6-2 1:29:33编辑过]

genoussi · 发表于 2008-6-2 09:11

看看。学些学习。。

hqhl2008 · 发表于 2008-6-2 11:10

关于2楼用辅助列，如果D列得到一个3位数，将出错。本题用一个公式解决。

7dong502 · 发表于 2008-6-2 14:53

对币币流口水，但，水平啊水平。[em03]

7dong502 · 发表于 2008-6-2 14:53

帮你移到自习室去哈。[em01][em01]

格子布圣诞鹿 · 发表于 2008-6-2 17:21

游客，如果您要查看本帖隐藏内容请回复

有趣啊

zhaoxiaofeng · 发表于 2008-6-2 17:43

游客，如果您要查看本帖隐藏内容请回复

zhaoxiaofeng · 发表于 2008-6-2 17:48

[em04]改为MID(A2,ROW($1:$99),1)

liuhao_goody · 发表于 2008-6-2 18:29

虽还没研读通两位(格子布,zhaoxiaofen)的公式,但是佩服![em17]

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