網友想問：在 Excel 中如果有兩個固定間隔時間的清單，該如何找出兩者之間所有時間重疊者？
在下圖中：
條件A：在 9:00～13:00 中每間隔 3 分鐘的時間清單。
條件B：在 9:00～13:00 中每間隔 5 分鐘的時間清單。
想要找出條件A和條件B時間重疊者，如下圖右(D欄)。
【公式設計與解析】
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 函數，即可找出對應的儲存格內容。
留言列表