網友想問:在 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 函數,即可找出對應的儲存格內容。

arrow
arrow
    全站熱搜

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