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

如果日期在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),,)

 

【延伸閱讀-FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

圖片1 Excel-根據單條件和雙條件篩選資料(FILTER)

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

圖片1 Excel-由資料清單中篩選一組

圖片1 Excel-利用FILTER函數模糊篩選

圖片1 Excel-從日期清單中區別平日和假日計算總和

圖片1 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

圖片1 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

 

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(6) 人氣()