在 Excel 中使用陣列公式時，常會因為儲存格陣列的數量龐大，而造成系統效能下降，所以不得已要放棄陣列公式的使用，所以得發展不需使用陣列公式的方式。
繼前二篇文章：
Excel-列出篩選和不篩選項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)
Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)
本篇要分別以陣列公和非陣列公式來處理將項目清單中的空白予以忽略，重新列出有資料的項目。
【公式設計與解析】
先選取儲存格A1:A18，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：項目。
1. 陣列公式
儲存格C2：
{=IFERROR(OFFSET($A$1,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,0),"")}
這是陣列公式，輸入公式後要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格C2，貼至儲存格C2:D18。
(1) IF(項目<>"",ROW(項目),"")
判斷項目陣列中具有空白的儲存格，傳回列號的陣列。ROW 函數可以儲存格的列號。
(2) SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))
將第(1)式傳回的列號陣列，利用 SMALL 函數由小至大取出其列號。公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET($A$1,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,0)
將第(2)式傳回的列號代入 OFFSET 函數，傳回對應的儲存格內容。
(4) IFERROR(第(3)式,"")
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。
2. 非陣列公式
儲存格C2：
=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((項目<>"")*ROW(項目),
ROW(1:1)+COUNTIF(項目,"")))-1,0),"")
複製儲存格C2，貼至儲存格C2:D18。
(1) (項目<>"")*ROW(項目)
判斷項目陣列中具有空白的儲存格，傳回列號的陣列。ROW 函數可以儲存格的列號。
(2) SMALL((項目<>"")*ROW(項目),ROW(1:1)+COUNTIF(項目,"")
COUNTIF(項目,"")：計算在項目欄位中共有幾個空白儲存格。
將第(1)式傳回的列號陣列，利用 SMALL 函數由小至大取出其列號。公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) SUMPRODUCT(SMALL((項目<>"")*ROW(項目),ROW(1:1)+COUNTIF(項目,""))
在 SUMPRODUCT 函數中，第(1)式和第(2)式可以執行相當於陣列公式的功能。
(4) OFFSET($A$1,第(3)式-1,0)
最後，將第(3)式的列號代入 OFFSET 函數，傳回對應的儲存格內容。
(5) IFERROR(第(4)式,"")
最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。
【延伸閱讀：FILTER函數篩選應用】
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
留言列表