2016年5月4日 星期三

Excel-由多個工作表中摘要出時間最接近的資料(TIMEVALUE,VLOOKUP)

有網友問到:在多個工作表中有一個日期時間欄位和二個資料欄位,如何指定一個時間後,在一個工作表中摘要各個工作表中最接近該時間的二個資料。
在下圖中,有資料A、資料B、...、資料G共7個工作表,每個工作表中有『日期時間、資料1、資料2』三個欄位。要在一個工作表中摘要這7個工作表中最接近指定時間所對應的資料1和資料2,該如何處理?
Excel-由多個工作表中摘要出時間最接近的資料(TIMEVALUE,VLOOKUP)
首先,觀察『日期時間』欄位,其中的日期和時間並非標準的格式,也就是該欄位內容其實是文字而非數值,所以無法直接取出時間來使用。所以,必須先做轉換工作。
在『資料A』工作表中(參考下圖):
儲存格B2:=TIMEVALUE(RIGHT(A2,8))
複製儲存格B2,貼至儲存格B2:B6,並在每工作表重覆上述步驟。(結果參考下圖)
Excel-由多個工作表中摘要出時間最接近的資料(TIMEVALUE,VLOOKUP)

在『整合』工作表中(參考下圖):
儲存格B2:{=VLOOKUP($E$2,INDIRECT(A2&"!$B2:$D5"),2,TRUE)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
(1) INDIRECT(A2&"!$B2:$D5")
將儲存格A2的內容串接儲存格B2:D5,由 INDIRECT 函數轉換為儲存格位置,例如:『資料A!B2:D5』。
(2) VLOOKUP($E$2,INDIRECT(A2&"!$B2:$D5"),2,TRUE):
在陣列公式中,將 INDIRECT 函數傳回的儲存格位置和儲存格E2(本例為03:00:00)比對,利用 VLOOKUP 函數傳回對應的C欄資料。其中參數『TRUE』,可以在陣列中取得和儲存格A2內容最近者。
同理,儲存格C2:{=VLOOKUP($E$2,INDIRECT(A2&"!$B2:$D5"),3,TRUE)}
複製儲存格B2:C2,貼至儲存格B2:C8。
Excel-由多個工作表中摘要出時間最接近的資料(TIMEVALUE,VLOOKUP)

沒有留言:

張貼留言

好康東東