2011年6月19日 星期日

Excel-查表練習(INDEX,陣列)

在 Excel 中有一個物品維修的記錄表(如下圖),由於報表跨越數年,如何指定只列出某年、某月的記錄呢?

首先,要先定義一些「名稱」:

(1) 選取所有有資料的儲存格。

(2) 按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,按一下[確定]按鈕。

可得「編號、班級、項目、報修日、完成日、損壞原因」等名稱。

(3) 選取所有有資料的儲存格,在[名稱管理員]中新增名稱:「資料」。

在儲存格H2和儲存格I2中,利用「資料驗證」方式,讓年和月可以使用清單方式挑選。

儲存格J2:{=IFERROR(INDEX(資料,SMALL(IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE),ROW(1:1)),2),"")}

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

(A) IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE):取得符合指定年和月的「編號」陣列。公式中的「*」為執行邏輯「AND」的運算,即兩個條件須都符合時,條件才成立。

(B) SMALL((A),ROW(1:1):取得編號陣列中第1小者,往下複製公式時,可以取得第2小者,依此類推。

(C) INDEX(資料,(B),2):根據編號陣列中的編號和第2欄(班級欄)的交點,即為班級名稱。

(D) IFERROR((C),""):如果公式結果有錯(找不到對應的值),即以空字串顯示,避免顯示錯誤訊息。

同理可以建立以下公式:

儲存格K2:{=IFERROR(INDEX(資料,SMALL(IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE),ROW(1:1)),3),"")}

儲存格L2:{=IFERROR(INDEX(資料,SMALL(IF((YEAR(報修日)=$H$2)*(MONTH(報修日)=$I$2),編號,FALSE),ROW(1:1)),6),"")}

沒有留言:

張貼留言

檢視其他文章

好康東東