2016年10月10日 星期一

Excel-找出兩個時間清單中重疊者(OFFSET,SMALL,ROW,陣列公式)

網友想問:在 Excel 中如果有兩個固定間隔時間的清單,該如何找出兩者之間所有時間重疊者?
在下圖中:
條件A:在 9:00~13:00 中每間隔 3 分鐘的時間清單。
條件B:在 9:00~13:00 中每間隔 5 分鐘的時間清單。
想要找出條件A和條件B時間重疊者,如下圖右(D欄)。
Excel-找出兩個時間清單中重疊者(OFFSET,SMALL,ROW,陣列公式)

【公式設計與解析】
1. 產生由 9:00 開始間隔 3 分鐘的時間清單
儲存格A3:=9*1/24
儲存格A4:=A3+3/(24*60)
複製儲存格A4,往下各列貼上。

2. 產生由 9:00 開始間隔 5 分鐘的時間清單
儲存格B3:=9*1/24
儲存格B4:=B3+5/(24*60)
複製儲存格B4,往下各列貼上。

3. 產生重疊時間的清單
選取儲存格A2:B83(有資料的儲存格),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:條件A、條件B。
儲存格D3:{=OFFSET($B$3,SMALL(IF(COUNTIF(條件A,條件B),ROW(條件B),
999),ROW(1:1))-3,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
(1) COUNTIF(條件A,條件B)
在陣列公式中,計算條件B中每一項在條件A清單中的數量。(如果傳回 1,表示重疊;如果傳回 0,表示沒有重疊。)
(2) IF(COUNTIF(條件A,條件B),ROW(條件B),999)
在陣列公式中,若第(1)式的傳回值為 1(表示重疊),則傳回重疊者儲存格的列號;若第(1)式的傳回值為 0(表示沒有重疊),則傳回「999」(這只是一個任意很大的數值)。
(3) SMALL(IF(COUNTIF(條件A,條件B),ROW(條件B),999),ROW(1:1))
在陣列公式中,利用 SMALL 函數利用 ROW(1:1)=1,找出傳回的列號中的最小值。若公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,即可依序找出列號中第 1, 2, 3, ... 小值的列號。
(4) OFFSET($B$3,第(3)式-3,0)
在陣列公式中,利用第 (3) 式的傳回值,代入第(4)式的 OFFSET 函數,即可找出對應的儲存格內容。

沒有留言:

張貼留言

檢視其他文章

好康東東