贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()


留言列表 (1)

發表留言
  • 訪客
  • 你第3.4點是不是有些公式被切掉了?
  • 謝謝你的提醒!已做了一些調整。

    vincent 於 2014/09/02 11:19 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼