學不完．教不停．用不盡

學校同仁想要分析一份在 Excel 試算表的簽到單中(如下圖中，其中簽名無任何次序性)，根據完整的名冊(如下圖左)，想要找出未簽到的人(如下圖右)，該如何處理？

【公式輸入】

儲存格H2：{=IFERROR(OFFSET($B$2,SMALL(IF(COUNTIF(D$2:D$25, $B$2:$B$25)=0,ROW($B$2:$B$25)-2,FALSE),ROW(1:1)),,,),"")}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，公式會自動產生「 {  } 」。

複製儲存格H2，貼至儲存格H2:J25。

公式有點長，慢慢來解析：

(1) COUNTIF(D$2:D$25,$B$2:$B$25)：

利用 COUNTIF 函數，計算在名字清冊中的名字陣列出現在1月6日的簽到名單中次數(若為 1 表示有簽到，若為 0 表示未簽到)，形成一個 1／0 的陣列集合。本例結果為：0, 0, 1, 0, 1, …。

(2) IF(COUNTIF(D$2:D$25,$B$2:$B$25)=0,ROW($B$2:$B$25)-2,FALSE)：

根據(1)的傳回值，判斷是否為0(若為 1 表示有簽到，若為 0 表示未簽到)，若是，則給予對應的一個數值：ROW($B$2:$B$25)-2，其中 ROW($B$2:$B$25) 為在名單清冊的第幾列。若否，則設定為 FALSE。參考下圖：

(3) SMALL(IF(COUNTIF(D$2:D$25,$B$2:$B$25)=0,ROW($B$2:$B$25)
-2,FALSE),ROW(1:1))

當公式往下各列複製後，可以利用 SMALL 函數，依序取出第 1, 2, 3, … 小值的數，本例為：0, 1, 3, 5, 7, 9, 11, 21。

(4) OFFSET($B$2,SMALL(IF(COUNTIF(D$2:D$25,$B$2:$B$25)=0,
ROW($B$2:$B$25)-2,"#NA"),ROW(1:1)),,,)

根據(3)的傳回值，代入 OFFSET 函數取得人員名冊上的一個對應姓名，其中如果查不到對應名字時(代表已查不到未簽到的名字了)，則會傳回錯誤訊息：#NUM!。

(5) 將(4)的傳回值透過 IFERROR 函數將傳回上述錯誤訊息的儲存格顯示為空白。