☰

從非空白儲存格中取得引用

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))),"")

相關內容