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

本次的练习是：如下图1所示，单元格区域A1:D6中是一系列数据，其中包含空单元格，现在要将它们放置到一列中，并删除空单元格，如图中所示的单元格区域G1:G13，如何使用公式实现？

图1

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

公式

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

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

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

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

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

=IF(ROWS($1:1)>$F$1,"",INDIRECT(TEXT(SMALL(IF(rngData<>"",10^5*ROW(rngData) COLUMN(rngData)),ROWS($1:1)),"R0C00000"),0))

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

公式解析

公式中的：

ROWS($1:1)

随着公式的下拉，其值会发生变化，在第1行为ROWS($1:1)，结果为1；第2行为ROWS($1:2)，结果为2；第3行为ROWS($1:3)，结果为3，依此类推。因此，如果结果大于单元格F1中的值，则公式结果为空，否则执行IF语句的第2部分。

IF(rngData<>"",10^5*ROW(rngData) COLUMN(rngData))

这个IF语句rngData<>””部分的值为：

IF({TRUE,TRUE,FALSE,TRUE;FALSE,TRUE,TRUE,TRUE;TRUE,FALSE,TRUE,FALSE;FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE}

10^5*ROW(rngData) COLUMN(rngData)部分的值为：

{100001,100002,100003,100004;200001,200002,200003,200004;300001,300002,300003,300004;400001,400002,400003,400004;500001,500002,500003,500004;600001,600002,600003,600004}

其结果为{100001,100002,FALSE,100004;FALSE,200002,200003,200004;300001,FALSE,300003,FALSE;FALSE,FALSE,400003,FALSE;FALSE,500002,500003,500004;FALSE,FALSE,FALSE,600004}

上述IF函数的结果作为SMALL函数的第一个参数值，其第2个参数ROWS($1:1)随行号变化，对于第1行为1，结果为100001；第2行为2，结果为100002；第3行为3，结果为100004，等等，依此类推。

SAMLL函数的结果作为TEXT函数的参数，例如第3行：

TEXT(100004,”R0C00000”)

结果为R1C00004。

这个结果传递给INDIRECT函数：

INDIRECT(“R1C00004”,0)

结果将取出第1行第4列中的值，即单元格D4中的值。

为什么选用10^5，并且使用R0C00000作为格式字符串呢？使用足够大的数值，主要是为了考虑行和列扩展后能够准确地取出相应行列所在单元格的数据。

注意到，在TEXT函数中，先填充C之后的五个零，剩下的在填充R之后的部分。

其他解

还可以使用公式：

=IFERROR(INDIRECT(TEXT(SMALL(IF(rngData<>"",10^5*ROW(rngData) COLUMN(rngData)),ROWS($1:1)),"R0C00000"),0),"")