2014年7月23日 星期三

Excel-在數值清單中找出缺少的號碼(COUNTIF,SMALL)

有老師在一個學生號碼的 Excel 資料清單中,想要自動列出 1 至 35 個號碼中,有那些號碼缺少了而未出現。例如:學生繳交作業的號碼,逐筆記錄後,想要知道有那些學生尚未繳交,該如何處理這個問題呢?(參考下圖)

【輸入公式】

儲存格B2:{=SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格B2,往下各列貼上。

COUNTIF($A$2:$A$26,ROW($1:$35):找出儲存格A2:A26中,含有 1 至 35 的個數,其中不是 1 就是 0。

IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)):在 IF 函數中,依上式結果,1 代表 TRUE,0 代表 FALSE。意思是如果未出現的數字,即傳回 ROW(1:35) 的結果,該結果就是未出現的數字。而參數 40 要比最大值 35 大。

如果不想讓儲存格中超出 35 的數字(40)顯示出來,則可以修改公式如下:(參考上圖D欄)

{=IF(SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1))>35,"",SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格B2,往下各列貼上。

沒有留言:

張貼留言

好康東東