2016年6月1日 星期三

Excel-在不連續的時間序列中補足分鐘數(ROUND,OFFSET,MATCH,陣列公式)

網友想要根據下圖中的 Excel 資料清單,補足連續分鐘數缺漏的部分(下圖左有底線位置即有缺漏),下圖右紅色文字即為補上的分鐘數,該如何處理這樣的問題?
在不連續的時間序列中補足分鐘數(ROUND,OFFSET,MATCH,陣列公式)

【公式設計與解析】

1. 產生連續的分鐘數
儲存格G3:=G2+1/24/60
因為在 Excel 中一天以「1」來表示,所以每一分鐘表示為「1/24/60」。
複製儲存格G3,貼至儲存格G3:G101。(假設要產生100分鐘的資料)

2. 資料查表
儲存格H2:{=IFERROR(OFFSET($C$2,MATCH(ROUND(G2,5),
ROUND($B$2:$B$101,5),0)-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel自動產生「{}」。
因為時間轉換出來的數值太精密,所以在使用 MATCH 函數時無法正確比對,因此要調整一下,只取到小數點第5位。
(1) ROUND(G2,5)
將儲存格G2的數值取小數點5位。
(2) ROUND($B$2:$B$101,5)
在陣列公式中,將儲存格B2:B101的數值全部取小數點5位。
(3) MATCH(ROUND(G2,5),ROUND($B$2:$B$101,5),0)-1,0)
在儲存格B2:B101中比對和儲存格G2相同者,傳回其列號。
(4) OFFSET($C$2,第(3)式,0)
利用 MATCH 函數傳回的列號代入 OFFSET 函數,查詢對應的儲存格內容。
(5) IFFERROR(OFFSET($C$2,第(3)式,0),"")
利用 IFFERROR 函數,將傳回錯誤訊息的儲存格轉換為空字串。(何時會出現錯誤呢?在原始資料清單中缺漏的秒數,將會查詢不到任何資料,即會傳回錯誤訊息。)
同理:
儲存格I2:{=IFERROR(OFFSET($D$2,MATCH(ROUND(G2,5),
ROUND($B$2:$B$101,5),0)-1,0),"")}

沒有留言:

張貼留言

好康東東