學不完．教不停．用不盡

在解答網友問題的過程中，常會發現網友對於陣列公式的接受度低，因為不易理解。而且一個儲存格中包含過多的函數，也讓人在理解之路困難重重。

例如，以下的範例要在姓名的清單中，依指定的文字找出姓名中含有該文字的清單。

如果以陣列公式來處理，可以在一個儲存格中即可求得解答。

儲存格C3公式：

{=IFERROR(OFFSET(A$2,SMALL(IF(SUBSTITUTE(姓名,$D$1,"")<>姓名,
ROW(姓名),""),ROW(A1))-2,0),"")}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

複製儲存格C3，貼至儲存格C3:D32。

如此以陣列公式處理，的確對很多人的負擔很大，公式不易理解，也容易出錯。

不要執著要把那麼多的函數放在同一個儲存格，再藉由輔助欄位來協助，將公式分在多個欄位中處理。例如：

(1) 找出姓名裡含有指定字者

儲存格C3：=IF(SUBSTITUTE(B3,$F$1,"")<>B3,"V","")

利用 SUBSTITUTE 函數將姓名以指定字置換為空字串(刪掉該字的概念)，如果置換結果和原姓名不相等，表示其姓名中含有該指定字。

(2) 找出含有指定字者的列號

儲存格D3：=IF(C3="V",ROW(D3),"")

ROW 函數會傳回儲存格的列號。

(3) 列出含有指定字者的編號

儲存格E3：=IFERROR(SMALL($D$3:$D$32,ROW(1:1))-2,"")

因為D欄中已列有「列號」，所以利用 SMALL 函數由小到大列出「編號」。其中編號和列號的差為「2」。

其中 ROW 函數可以傳回列號，所以公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

其中的 IFERROR 函數是用在當傳回錯誤訊息時，將其顯示為空字串（空白儲存格）。

(4) 列出含有指定字者的姓名

儲存格F3：=IFERROR(OFFSET($B$2,E3,0),"")

因為E欄中已列有「編號」，所以利用 OFFSET 函數找出對應的儲存格內容。

複製儲存格C3:F3，貼至儲存格C3:F32。(必要時隱藏C欄和D欄)