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

liyh67

<p>出一个常见的条件求和问题，不用辅助列：</p><p> xbt6bsmN.rar (3.85 KB, 下载次数: 53)

2007-11-26 19:09 上传

点击文件名下载附件
数组兴趣小组练习题-32期 <br/></p>

Luckyguy2008

<br/>G13=SUM(ISNUMBER(FIND(G$12,$B$4:$B$100))*(TEXT($C$4:$C$100,"ym")=TEXT($F13,"ym"))*$D$4:$D$100)

箫风

<p>看这样可以吗?</p><p>公式:&nbsp;&nbsp; {=SUM((TEXT($C$4:$C$100,"YM")=TEXT($F13,"YM"))*IF(ISERROR(FIND(G$12,$B$4:$B$100)),0,1)*($D$4:$D$100))}</p>

[此贴子已经被作者于2007-11-26 20:45:50编辑过]

不看不看

<p>=SUM(IF(ISERROR(FIND(G$12,T(OFFSET($B$3,ROW($1:$97),,)))),0,1)*(TEXT($C$4:$C$100,"ym")=TEXT($F13,"ym"))*$D$4:$D$100)</p><p>有意用了下OFFSET（画蛇添足）</p>

[此贴子已经被作者于2007-11-26 22:47:53编辑过]

gouweicao78

=SUMPRODUCT((TEXT($C$4:$C$100,"em")=TEXT($F13,"em"))*ISNUMBER(FIND(G$12,$B$4:$B$100))*$D$4:$D$100)

封州大少

<p>好办法被楼上用完了，我只好用个G12:I30的多单元格数组公式:</p><p>=MMULT(--(TEXT(F13:F30,"ym")=TEXT(TRANSPOSE(C4:C100),"Ym")),ISNUMBER(FIND(G12:I12,B4:B100))*D4:D100)</p><p>"一次成形",呵呵</p>

蓝色记忆

<p>想不出别的方法了，受<strong><font face="Verdana" color="#000000">不看不看版主的最近的两点收获来个比较复杂的：</font></strong></p><p>=SUM(IF(ISERROR(N(INDIRECT(TEXT((ISNUMBER(FIND(G$12,$B$4:$B$100))*(TEXT($C$4:$C$100,"YM")=TEXT($F13,"YM"))*ROW($D$4:$D$100))*1000+4,"R0C000"),))),,N(INDIRECT(TEXT((ISNUMBER(FIND(G$12,$B$4:$B$100))*(TEXT($C$4:$C$100,"YM")=TEXT($F13,"YM"))*ROW($D$4:$D$100))*1000+4,"R0C000"),))))</p>

szyygb

路过，学习一下