【原】忽略空单元格，先行后列将产品名单转换到一列显示！

Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能

Excel爱好者大本营

用1%的Excel基础搞定99%的职场问题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气

数万Excel爱好者聚集地

SUPER EXCEL MAN

    

1

职场实例

群里小伙伴咨询到了一个问题：如何忽略空单元格，先行后列将产品名单转换到一列显示。

如下图所示：

A2:D5单元格区域为4行4列产品的名称，我们发现名称区域中有部分单元格为空白单元格，为了达到领导的某种数据特殊处理要求，我们需要将A2:D5单元格区域中的名称转换到F列一列显示，并且难点是按照A2:D5单元格区域的先行后列的顺序排列转换（水平方向转换）。

2

解题思路

解决今天的这个问题，我们需要用到INDIRECT函数、TEXT函数、SMALL函数、IF函数、ROW函数与COLUMN函数并配合特定的逻辑思路，下面我们来讲解一下。

首先我们需要在F2单元格输入数组公式：

=IF($A$2:$D$5<>"",ROW($2:$5)*100+COLUMN(A:D),9999)

数组公式以Ctrl+Shift+回车键结束公式。

核心思路：

使用IF函数对A2:D5单元格区域进行条件判断。逻辑是这样的：如果A2:D5单元格区域中的值不等于空值，则将对ROW函数获取的第2行到第5行的行号放大100倍后的值与COLUMN函数获取的A列至D列的列号进行相加，否则返回一个较大的值，本例中用9999代表，最终会生成一个由行列号信息组成的二维数组。

我们选中公式部分，按下F9键可查看行列号信息组成的二维数组细节：

{201,9999,203,9999;301,302,303,304;401,402,9999,404;9999,502,9999,504}

其次我们再使用SMALL函数，第二参数用动态的ROW(A1)，可实现自小到大依次提取数组中的值，继续完善F2单元格中的函数为：

=SMALL(IF($A$2:$D$5<>"",ROW($2:$5)*100+COLUMN(A:D),9999),ROW(A1))

接着我们使用TEXT函数，用第二参数使用格式代码"R0C00"，将SMALL函数返回的位置信息转换为"R1C1引用样式的字符串，继续完善F2单元格中的函数为：

=TEXT(SMALL(IF($A$2:$D$5<>"",ROW($2:$5)*100+COLUMN(A:D),9999),ROW(A1)),"R0C00")

再使用INDIRECT函数返回对应的单元格引用，产品名称即以先行后列的排列方式显示在F列中，继续完善F2单元格中的函数为：

=INDIRECT(TEXT(SMALL(IF($A$2:$D$5<>"",ROW($2:$5)*100+COLUMN(A:D),9999),ROW(A1)),"R0C00"),)

为了屏蔽最后一个有效产品名称后面的0值，我们需要在公式的最后端用连接符&连接一个“”（空值），继续完善F2单元格中的函数为：

=INDIRECT(TEXT(SMALL(IF($A$2:$D$5<>"",ROW($2:$5)*100+COLUMN(A:D),9999),ROW(A1)),"R0C00"),)&""

回顾关键内容，善用图片表达，学会建立联系，拓展深度广度，浓缩关键概念，应用到行动中，善于归纳总结，尝试进行分享。