Excel公式：将矩形数据转换成单列数据

excelperfect

标签：Excel公式

有时候，有一块矩形区域的数据，我们想将其转换成单列数据，以便于进一步处理，如何使用公式实现呢？

情形1：逐行转换成单列

如下图1所示，在单元格区域A1:D7中有一系列数据，要将其转换成单列数据，规则是自左自右自上而下逐行转换，即将第1行的A、B、C、D转换为列数据，然后转换第2行的E、F、G、H，依此类推。

图1

在单元格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所示。

图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))

很有意思的变换，有兴趣的朋友可以多琢磨，写出自己的公式来，提升编写公式解决问题的能力。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。