有时候,在一列数据中有许多空单元格,导致数据不连续,我们需要剔除这些空单元格,让数据区域连起来。
如下图1所示,在单元格区域A1:A15中输入了一些数据,但其间有许多空白单元格。
图1
在单元格D1中,输入数组公式:
=IFERROR(INDIRECT("A" & SMALL(IF(LEN(A15)=0,"",ROW(1:15)),ROW(A1))),"")
然后,向下复制直至出现空白单元格为止,如下图2所示。
图2
注意,数组公式要求按下Ctrl+Shift+Enter组合键才算完成输入,Excel将自动在公式两侧加上花括号({})。
公式的核心部分是:
SMALL(IF(LEN(A1:A15)=0,"",ROW(1:15)),ROW(A1))
首先,获取单元格区域A1:A15中每个单元格内数据的长度:LEN(A1:A15),看其是否为0:LEN(A1:A15)=0,如果为0,则表明该单元格为空,返回一个空值,否则返回该单元格所在的行号:IF(LEN(A1:A15)=0,"",ROW(1:15)),对于本例来说,返回数组:{1;””;3;””;””;6;””;””;””;10;11;””;””;””;15}。接着,对于第一个公式来说,ROW(A1)返回1,即获取该数组中最小的值,即1。
然后,使用INDIRECT函数获取第1个非空单元格的值:
INDIRECT(“A” & 1)
即单元格A1中的值。