2014年4月5日 星期六

Excel-依日期篩選資料(MATCH,OFFSET)

回答讀者提問:有一個資料表(參考下圖上半部),其中每個月份所列的是價格資訊,現在要根據編號所對應的日期篩選出價格,其規則為:

如果日期在01日~10日搜尋前月資料;如果日期在11日~31日搜尋當月資料。

篩選結果如下圖的下半部,公式如何處理?

【準備工作】

選取含有編號資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。

 

【輸入公式】

儲存格D13:=OFFSET($D$1,MATCH(C13,編號,0),MONTH(A13)+(DAY(A13)>10)-2,,)

複製儲存格D13,往下各列貼上。

MATCH(C13,編號,0):查詢儲存格C13的內容符合「編號」陣列中的那一列。(傳回數值)

MONTH(A13)+(DAY(A13)>10):MONTH(A13)取得儲存格A13的月份,DAY(A13)>10如果儲存格A13的日期大於 10,則會傳回 TRUE,反之傳回 FALSE,因為在運算時,TRUE=1、FALSE=0,所以可以讓大於 10 日者會加 1。

最後,透過 OFFSET 函數在整個月份資料表中找到對應的價格。

 

【補充說明】

比較一下,以下兩個式子的結果是相同的:

儲存格D13:=OFFSET($D$1,MATCH(C13,編號,0),MONTH(A13)+(DAY(A13)>10)-2,,)

儲存格D13:=OFFSET($B$1,MATCH(C13,編號,0),MONTH(A13)+(DAY(A13)>10),,)

沒有留言:

張貼留言

檢視其他文章

好康東東