Excel公式练习33： 将包含空单元格的多行多列单元格区域转换成单独的列并去掉空单元格（续）

本次的练习是：这个练习题与本系列上篇文章的练习题相同，如下图1所示，不同的是，上篇文章中将单元格区域A1:D6中的数据（其中包含空单元格）转换到单独的列（如图中所示的单元格区域G1:G13）中时，是以行的方式进行的，即先放置第1行中的数据、再放置第2行……依此类推。这里，需要以列的方式进行，即先放置第1列中的数据、再放置第2列中的数据……依此类推，最终结果如图中所示的单元格区域H1:H13，如何使用公式实现？

图1

先不看答案，自已动手试一试。

公式

1.先将单元格区域A1:D6命名为“rngData”。

2.在单元格F1中输入公式：

=SUM(COUNTIF(rngData,{">0","?*"}))

统计单元格区域A1:D6中非空单元格的数量。并将该单元格作为辅助单元格。

3.在单元格H1中输入数组公式：

=IF(ROWS($1:1)>$F$1,"",INDIRECT(TEXT(SUM(10^{5,0}*MID(TEXT(SMALL(IF(rngData<>"",10^5*COLUMN(rngData)+ROW(rngData)),ROWS($1:1)),"R00000C00000"),{8,2},5)),"R00000C00000"),0))

向下拖拉至出现空单元格为止。

公式解析

公式中的主要部分与上篇文章相同，不同的是将：

TEXT(SMALL(IF(rngData<>"",10^5*ROW(rngData)+COLUMN(rngData)),ROWS($1:1)),”R0C00000”)

换成了：

TEXT(SMALL(IF(rngData<>"",10^5*COLUMN(rngData)+ROW(rngData)),ROWS($1:1)),”R00000C00000”)

随机选一个单元格获取的值来解析公式，例如单元格H6，其中的部分公式为：

MID(TEXT(SMALL(IF(rngData<>"",10^5*COLUMN(rngData)+ROW(rngData)),ROWS($1:6)),"R00000C00000"),{8,2},5)

应该获取单元格C2中的值，即数据区域的第2行第3列。公式中间结果为：

MID(“R00003C00002”,{8,2},5)

返回数组：

{“00002”,”00003”}

使数值反转，正好与行列相对应。

此时，公式变为：

INDIRECT(TEXT(SUM(10^{5,0}*{“00002”,”00003”}),”R00000C00000”),0)

变为：

INDIRECT(TEXT(SUM({200000,3}),”R00000C00000”),0)

变为：

INDIRECT(TEXT(200003,”R00000C00000”),0)

变为：

INDIRECT(“R00002C00003”,0)

即获取单元格C2中的值。

相关参考

Excel公式练习32：将包含空单元格的多行多列单元格区域转换成单独的列并去掉空单元格

Excel公式练习4：将矩形数据区域转换成一行或者一列