excelperfect
标签:Excel公式
有时候,有一块矩形区域的数据,我们想将其转换成单列数据,以便于进一步处理,如何使用公式实现呢?
情形1:逐行转换成单列
如下图1所示,在单元格区域A1:D7中有一系列数据,要将其转换成单列数据,规则是自左自右自上而下逐行转换,即将第1行的A、B、C、D转换为列数据,然后转换第2行的E、F、G、H,依此类推。
在单元格F1中输入公式:
=IFERROR(REPT(INDEX($A$1:$D$7,INT((ROW(A1)-1)/COLUMNS($A$1:$D$7))+1,MOD(ROW(A1)-1,COLUMNS($A$1:$D$7))+1),1),'')
然后下拉复制单元格公式。
上面的公式稍作变化,也能达到相同的效果:
=INDEX($A$1:$D$7,1+INT((ROW(A1)-1)/COLUMNS($A$1:$D$7)),MOD(ROW(A1)-1+COLUMNS($A$1:$D$7),COLUMNS($A$1:$D$7))+1)
也可以使用下面的公式:
=IF(OFFSET($A$1,ROUNDUP(ROWS($1:1)/COLUMNS($A$1:$D$100),0)-1,
MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100)))=0,'',OFFSET($A$1,ROUNDUP(ROWS($1:1)
/COLUMNS($A$1:$D$100),0)-1,MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100))))
情形2:逐列转换成单列
如果我们需要逐列转换,那该如何?即先将第1列的数据转换为列数据,再将第2列的数据添加到刚才列数据后面,如下图2所示。
可以使用下面的公式:
=IF(OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),ROUNDUP(ROWS($1:1)
/COUNTA($A:$A),0)-1)=0,'',OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),
ROUNDUP(ROWS($1:1)/COUNTA($A:$A),0)-1))
很有意思的变换,有兴趣的朋友可以多琢磨,写出自己的公式来,提升编写公式解决问题的能力。
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。
联系客服