2011年6月18日 星期六

Excel-物品領用統計與分析(陣列應用)

在 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)),編號,請領物品),"")}

沒有留言:

張貼留言

好康東東