数组兴趣小组练习题-021期

laoyebin

第一题  =SUM(SUMIF($A$2:$A$6,MID(SUBSTITUTE(D2,",",),ROW($1:$3)*3-2,3),$B$2:$B$6))

兰色幻想

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>laoyebin</i>在2007-10-23 15:25:22的发言：</b><br/>第一题&nbsp; =SUM(SUMIF($A$2:$A$6,MID(SUBSTITUTE(D2,",",),ROW($1:$3)*3-2,3),$B$2:$B$6))</div><p>那个逗号是不存在的[em07],可以省略替换 </p><p>=SUM(SUMIF($A$2:$A$6,MID(D2,ROW($1:$3)*3-2,3),$B$2:$B$6))</p>

laoyebin

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>兰色幻想</i>在2007-10-23 15:34:04的发言：</b><br/><div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>laoyebin</i>在2007-10-23 15:25:22的发言：</b><br/>第一题&nbsp; =SUM(SUMIF($A$2:$A$6,MID(SUBSTITUTE(D2,",",),ROW($1:$3)*3-2,3),$B$2:$B$6))</div><p>那个逗号是不存在的[em07],可以省略替换 </p><p>=SUM(SUMIF($A$2:$A$6,MID(D2,ROW($1:$3)*3-2,3),$B$2:$B$6))</p></div><p>呵呵,没注意,我还当是逗号呢,谁知道原来是假的</p>

willin2000

<p>第一题:</p><p>=SUM(SUMIF(A:A,MID(D2,ROW($1:$3)*3-2,3),B$1))</p><p>第二题:<br/>=N(OFFSET(INDEX($18:$18,MATCH($A$1,$17:$17,)),ROW(OFFSET($A$1,MATCH($B$1,$A$19:$A$22,)-1,,COUNT($D$19:$D$22)-MATCH($B$1,$A$19:$A$22,)+1)),COLUMN($A:$C)-1))</p>

Luckyguy2008

第二题<br/>=OFFSET(C18,MATCH(B1,A19:A22,),MATCH(A1,D17:L17,),ABS(IF(B1=A19,1,SUM(ROW(A23)-18))-MATCH("々",A19:A22)),3)

gvntw

<p>题一：</p><p>=SUM(ISNUMBER(FIND(A$2:A$6,D2))*B$2:B$6)</p><p></p>

gvntw

<p>建议：</p><p>编号最好使用文本格式：101,103,104，或用、号分隔，一般不会有人设置为带分隔号的货币格式，再输入数据，而一般编号中也不一定就固定3位，并且一个单元格中输入最多的编号数也不固定是几个，有可能1个，也有可能4、5个。</p>

willin2000

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>gvntw</i>在2007-10-24 14:20:02的发言：</b><br/><p>题一：</p><p>=SUM(ISNUMBER(FIND(A$2:A$6,D2))*B$2:B$6)</p><p></p></div><p></p><table cellspacing="0" cellpadding="0" width="343" border="0" style="WIDTH: 258pt; BORDER-COLLAPSE: collapse;"><colgroup><col span="2" width="72" style="WIDTH: 54pt;"></col><col width="25" style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 800;"></col><col width="102" style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264;"></col><col width="72" style="WIDTH: 54pt;"></col></colgroup><tbody><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl63" width="72" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><font face="宋体">编号</font></td><td class="xl63" width="72" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">金额</font></td><td width="25" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 19pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl63" width="102" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">编号</font></td><td class="xl63" width="72" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">金额</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" align="right" 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="宋体">102</font></td><td class="xl64" align="right" 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><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl64" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">102</font></td><td class="xl66" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow;"><font face="宋体">2</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" align="right" 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="宋体">112</font></td><td class="xl64" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">6</font></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl65" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">121,210,102</font></td><td class="xl66" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow;"><font face="宋体">20</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" align="right" 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="宋体">121</font></td><td class="xl64" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">3</font></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl64" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">212</font></td><td class="xl66" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow;"><font face="宋体">8</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" align="right" 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="宋体">210</font></td><td class="xl64" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">7</font></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td class="xl67" colspan="2" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">结果</font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl64" align="right" 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="宋体">212</font></td><td class="xl64" align="right" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体">8</font></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体"></font></td></tr><tr height="19" style="HEIGHT: 14.25pt;"><td class="xl67" colspan="2" height="19" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent;"><font face="宋体">数据</font></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td></tr></tbody></table>[em07]

gvntw

<p>TO <strong><font face="Verdana" color="#61b713">willin2000</font></strong>:</p><p>如果121,210,102中逗号不是单元格内容，而是单元格数字格式增加的，是有这种情况，就是212。</p><p>可以把D2的数字格式带入到公式中，用Text(D2,数字格式）代替D2。</p><p>此题最好是把编号文本化，逗号手动输入，公式中再前后加逗号去除重复（如编号为12与121）</p><p>=SUM(ISNUMBER(FIND(","&amp;A$2:A$6&amp;",",","&amp;D2&amp;","))*B$2:B$6)</p>

MXQCHINA

<p>第一题：</p><p>E2=SUM(((--MID(D2,COLUMN(INDIRECT("r1c1:r1c"&amp;LEN(D2)/3,))*3-2,3))=$A$2:$A$6)*$B$2:$B$6)</p><p>不过还是SUM+SUMIF简单。</p>