2014年9月1日 星期一

Excel-找出清冊中缺漏的名字(OFFSET,CUNTIF,SMALL,IFERROR)

學校同仁想要分析一份在 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 函數將傳回上述錯誤訊息的儲存格顯示為空白。

沒有留言:

張貼留言

檢視其他文章

好康東東