學不完．教不停．用不盡

在 Excel 中取得一個物品領用的數量統計表，現在要利用這個資料表以公式運算方式列出(1)整年領取數為0者，(2)每個月都有被領取者。

因為需要用到一個「輔助」欄位，所以先輸入公式：

儲存格Q2：=COUNTIF(D2:O2,"<>0")，複製儲存格Q2，往下各列貼上。

將A欄有資料的部分定義名稱為「編號」；將B欄有資料的部分定義名稱為「請領物品」；將P欄有資料的部分定義名稱為「小計」；將Q欄有資料的部分定義名稱為「輔助」。

(1) 整年領取數為0者

儲存格S2：{=IFERROR(SMALL(IF(小計=0,編號,FALSE),ROW(1:1)),"")}

這是陣列公式，輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格S2，往下各列貼上。以下公式做法雷同。

(A) IF(小計=0,編號,FALSE)：得到小計為0者的編號陣列。

(B) SMALL((A),ROW(1:1)：取得(A)陣列中的最小值(最小的編號)。往下複製時ROW(1:1)=1 → ROW(2:2)=2 → ‥‥，可取得第2小、第3小‥‥的編號

(C) IFERROR((B),"")：因為當找不到編號時會傳回錯誤值，所以藉由IFERROR函數，將其顯示為空白。

儲存格T2：{=IFERROR(LOOKUP(SMALL(IF(小計=0,編號,FALSE),ROW(1:1)),編號,請領物品),"")}

原理同上，再將編號利用LOOKUP函數以查表方式找到對應的「請領物品」名稱。

(2)每個月都有被領取者

試著根據(1)的做法，藉助「輔助」欄位，練習每個月都有被領取者。

儲存格V2：{=IFERROR(SMALL(IF(輔助=12,編號,FALSE),ROW(1:1)),"")}

其先找到輔助欄位為12者(12個月都不為0)的陣列，再取出其編號。

儲存格W2：{=IFERROR(LOOKUP(SMALL(IF(輔助=12,編號,FALSE),ROW(1:1)),編號,請領物品),"")}