☰

从非空白单元格获取引用

tag-icon • tag-icon microsoft-excel worksheet-function
从非空白单元格获取引用

我需要有关函数的帮助。在 AI 列中有一个名称列表，但它们之间用空白单元格隔开。有没有办法在 B 列中显示这些名称，但不使用空白单元格？

结果应如下所示：

在此处输入图片描述

答案1

以下数组公式即使任何名称相同也会起作用。使用 + + 输入它们B2，Ctrl然后Shift向下Enter填充。

这个使用基于样本数据的固定范围：

{=IFERROR(INDEX(A:A,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10),""),ROW()-ROW(A$1)+1)),"")}

这个使用动态范围，当您向列添加更多名称时会自动调整A:A：

=IFERROR(INDEX(A:A,SMALL(IF(A$1:INDEX(A:A,MATCH("*",A:A,-1))<>"",ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1))),""),ROW()-ROW(A$1)+1)),"")

与上面相同的动态公式，但以展开形式表示：

=
IFERROR(
  INDEX(
    A:A
  , SMALL(
      IF(
        A$1:INDEX(A:A,MATCH("*",A:A,-1))<>""
      , ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1)))
      , ""
      )
    , ROW()-ROW(A$1)+1
    )
  )
, ""
)

如您所见，第二个公式只是将第一个公式中的 全部A$10替换为INDEX(A:A,MATCH("*",A:A,-1))。

解释：

该IF()函数相当于：

IF(
  {"";"Camilo Georgi";"";"Carla Suarez Navarro";"";"Belinda Bencic";"";"Grace Min";"";"Johanna Larsson"}<>""
, {1;2;3;4;5;6;7;8;9;10}
, ""
)

由于 Excel 会自动将常量扩展为匹配长度的常量数组，因此变为：

IF(
  {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
, {1;2;3;4;5;6;7;8;9;10}
, {"";"";"";"";"";"";"";"";"";""}
)

其计算结果为：

{"";2;"";4;"";6;"";8;"";10}

该SMALL()函数变为：

SMALL({"";2;"";4;"";6;"";8;"";10},ROW()-1+1)

并且由于SMALL()忽略字符串，因此这相当于：

SMALL({2;4;6;8;10},ROW())

请注意，数字是非空白名称的索引。对于单元B1 SMALL()格返回2，对于B2它返回 4，等等。对于B6及以下它返回#NUM!错误。（这就是为什么有一个IFERROR()函数。它将这些错误转换为空白。）

最后，该INDEX()函数使用索引提取名称。

答案2

以下是列出值并排除空白单元格的一种方法。请参阅此帮助页面更多细节。

执行此操作的公式需要在第一个数据行上方添加一行。下表中的标题就是为此目的。

在此处输入图片描述

此数组公式在 B2 中输入：

=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$10&"")+IF($A$2:$A$10=",1,0),0)),"")

作为数组公式，必须使用CtrlShiftEnter, 而不是 来输入Enter。如果输入正确，Excel 将在公式栏中使用花括号 {} 将公式括起来。

公式输入完成后，点击B2单元格，将公式向下填充。

如果有重复的名称需要列出，此数组公式将仅删除空白而不会删除重复项：

=IFERROR(INDEX(A:A,SMALL(INDEX(NOT(ISBLANK($A$2:$A$10))*ROW($A$2:$A$10),0),COUNTBLANK($A$2:$A$10)+ROW(C1))),"")

相关内容