费了好大力气，写了这么个公式，想求更简洁的

明天不上网

<table cellspacing="0" cellpadding="0" width="257" border="0" xstr="" style="WIDTH: 193pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="155" style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 4960;"></col><col></col><col></col><col></col><col width="102" style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264;"></col><col></col><col></col><col></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl22" width="155" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 116pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><font face="宋体">22*33*44</font></td><td class="xl22" align="right" width="102" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">31944</font></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;"><font face="宋体">1*2</font></td><td class="xl22" align="right" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">2</font></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;"><font face="宋体">11*222</font></td><td class="xl22" align="right" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">2442</font></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;"><font face="宋体">1*2*3*4*5*6*7*8*9</font></td><td class="xl22" align="right" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">362880</font></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;"><font face="宋体">1*22*333*4444</font></td><td class="xl22" align="right" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">32556744</font></td></tr></tbody></table><p>&nbsp;</p><p>=PRODUCT(--(SUBSTITUTE(0&amp;(MID(A1,SMALL(IF(RIGHT(MID("*"&amp;A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20)),SMALL(IF(RIGHT(MID(A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20))-SMALL(IF(RIGHT(MID("*"&amp;A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20))))&amp;".","0.",1)))</p><p>&nbsp;</p><p>宏表函数的不要[em05]</p><p>&nbsp;</p><p>将就一下，这样了</p><p>=PRODUCT(--(SUBSTITUTE("0000000000"&amp;(MID(A1,SMALL(IF(RIGHT(MID("*"&amp;A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20)),SMALL(IF(RIGHT(MID(A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20))-SMALL(IF(RIGHT(MID("*"&amp;A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20))))&amp;".","0000000000.",1)))</p>

[此贴子已经被作者于2008-5-27 14:55:55编辑过]

明天不上网

<p>再解释一下，例如</p><p>A1内是字符串22*33*44，B1内是22*33*44的结果，以下类推。</p>

々栤ぐ藍

<p><font face="楷体_GB2312" size="1">不明白是啥意思，能说的详细点吗</font></p>

明天不上网

<p>RODUCT函数中不能有0，为了把0变成1，费了好大的周折</p><p>不过到底解决了，0&amp;字符串&amp;. 替换0.为1 </p><p>&nbsp;</p><p>（好像这里有弊端20*30可能就会出错）[em03]</p>

[此贴子已经被作者于2008-5-27 14:39:15编辑过]

格子布圣诞鹿

<p>定义名称AA=EVALUATE($A1)</p><p>B1输入=AA</p>

zbhpass

<p>看清了,才知EVALUATE是宏表</p><p>还是HAI的TEXT用的出神入化.</p><p>用另一种思路,=PRODUCT(--MID(A1,SMALL(IF(MID("*"&amp;A1&amp;"*",ROW(1:20),1)="*",ROW(1:20),100),ROW(1:3)),SMALL(IF(MID("*"&amp;A1&amp;"*",ROW(1:20),1)="*",ROW(1:20),100),ROW(2:4))-SMALL(IF(MID("*"&amp;A1&amp;"*",ROW(1:20),1)="*",ROW(1:20),100),ROW(1:3))-1)),不知道*数量怎么算</p><p>&nbsp;</p>

[此贴子已经被作者于2008-5-27 16:57:34编辑过]

我是新来的

<p>不好意思了哈，没看清版面</p>

[此贴子已经被作者于2008-5-27 15:02:45编辑过]

明天不上网

回楼上，想讨论的是数组问题

hai

<p>用TEXT把0转为1</p><p>=PRODUCT(--TEXT(MID(A1,SMALL(IF(RIGHT(MID("*"&amp;A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20)),SMALL(IF(RIGHT(MID(A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20))-SMALL(IF(RIGHT(MID("*"&amp;A1,1,ROW($1:$20)))="*",ROW($1:$20),21),ROW($1:$20))),"0;;;!1"))</p>

明天不上网

<p>想过hai的方法，可TEXT用不好[em03]</p>