函数法(多列汇总)--原创

欢欢

函数法(多列汇总)



提示输入:

tmtony

欢欢,公式怎么写啊,没看到公式哦

tanhong

多列汇总不错,只是没看到函数哦

pureshadow

这是个3*3矩阵转一列的内存数组公式.=IF((T(INDIRECT(TEXT(SMALL(ROW(A1:C3)*100+COLUMN(A1:C3),ROW(1:9)),"!r0c00"),))="")-COUNTBLANK(INDIRECT(TEXT(SMALL(ROW(A1:C3)*100+COLUMN(A1:C3),ROW(1:9)),"!r0c00"),)),N(INDIRECT(TEXT(SMALL(ROW(A1:C3)*100+COLUMN(A1:C3),ROW(1:9)),"!r0c00"),)),T(INDIRECT(TEXT(SMALL(ROW(A1:C3)*100+COLUMN(A1:C3),ROW(1:9)),"!r0c00"),)))
长了点,不过内存数组有它的好处,可以在外面再进行计算,非内存的要做到这点,只能加辅助列.

pureshadow

欢欢做的那种,可以自由增加的,之前也做过,只是函数一时找不到了(再让我做一次.......头会破 )
等找到了,一定拿出来

pureshadow

或者爪MM自己拿出来

业余爱好者

不用那么复杂吧

我也做一个

=IF(ROW()>COUNTA($AA),"",A1)&(IF(ROW()<=COUNTA($AA),"",IF(ROW()<=COUNTA($AB),INDIRECT("B"&ROW()-COUNTA($A:$A)),"")))&(IF(ROW()<=COUNTA($A:$B),"",IF(ROW()<=COUNTA($A:$C),INDIRECT("C"&ROW()-COUNTA($A:$B)),"")))

业余爱好者

原帖由 pureshadow 于 2008-6-17 20:26 发表
这是个3*3矩阵转一列的内存数组公式.=IF((T(INDIRECT(TEXT(SMALL(ROW(A1:C3)*100+COLUMN(A1:C3),ROW(1:9)),"!r0c00"),))="")-COUNTBLANK(INDIRECT(TEXT(SMALL(ROW(A1:C3)*100+COLUMN(A1:C3),ROW(1:9)),"!r0c00"),)), ...

=IF(ROW()>COUNTA($AA),"",A1)&(IF(ROW()<=COUNTA($AA),"",IF(ROW()<=COUNTA($AB),INDIRECT("B"&ROW()-COUNTA($A:$A)),"")))&(IF(ROW()<=COUNTA($A:$B),"",IF(ROW()<=COUNTA($A:$C),INDIRECT("C"&ROW()-COUNTA($A:$B)),"")))

公式里怎么数据变成了表情了?

再来一次

还是不行,只好把文件也传上来了


[ 本帖最后由 业余爱好者 于 2008-6-17 21:46 编辑 ]

pureshadow

禁用smiles就行了

pureshadow

相当不错

[ 本帖最后由 pureshadow 于 2008-6-17 22:09 编辑 ]