2014年10月5日 星期日

Excel-由清單中挑選相同日期者(OFFSET,ADDRESS,INDIRECT)

有網友問到:要如何在 Excel 中,由一個日期和項目組成的清單中,挑選相同日期者置於個別的工作表中?

參考下圖,先以資料查詢結果放在同一工作表來解說。

【方法一】

為了解說方便,先選取儲存格A1:A23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格D2:{=IFERROR(OFFSET($B$1,SMALL(IF(日期=D$1,ROW(日期)-1,FALSE),
ROW(1:1)),,,),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,自動會加上「{}」。

IF(日期=D$1,ROW(日期)-1,FALSE):判斷日期陣列中那些和儲存格D1的內容相符,傳回列號組成的陣列。

SMALL(IF(日期=D$1,ROW(日期)-1,FALSE),ROW(1:1)):依序取出上述列號陣列中的第1, 2, 3, ... 小值的列號。

透過 OFFSET 函數,將上式傳回的列號代入求得以儲存格B1為起點的一個儲存格內容。

最後再由 IFERROR 函數,將未查到資料而傳回錯誤訊的儲存格顯示空白。

複製儲存格D2,貼至儲存格D2:J10。

【方法二】

儲存格D2:{=IFERROR(INDIRECT(ADDRESS(SMALL(IF(日期=L$1,ROW(日期),
FALSE),ROW(1:1)),2)),"")

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,自動會加上「{}」。

參考【方法一】的說明,只是將 OFFSET 函數,改由 INDIRECT(ADDRESS( ... )) 來呈現。

【方法三】

有些讀者不喜歡使用上述的陣列變數表示法,可以改用陣列常數來執行:

儲存格D2:=IFERROR(OFFSET($B$1,SMALL(IF({41730;41735;41730;41732;41734;
41730;41733;41736;41735;41735;41734;41730;41736;41730;41734;41733;41731;
41732;41736;41734;41734;41733}=D$1,{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;
18;19;20;21;22;23}-1,FALSE),ROW(1:1)),,,),"")

其中「日期」陣列和 ROW(日期) 以常數來表示。

 

【放在不同工作表中】

最後,以上的公式如果是要放在不同的工作表(例如:下圖中的4月1日)中,要如何處理呢?

在「4月1日」工作表中的儲存格D2輸入公式:

{=IFERROR(OFFSET(Data!$B$1,SMALL(IF(日期=A$1,ROW(日期)-1,FALSE),
ROW(1:1)),,,),"")}

只是將原式中的:$B$1,改成:Data!$B$1即可。

沒有留言:

張貼留言

好康東東