錦子老師

錦子老師：

小弟在使用Excel時發生一些困擾，請版上大大幫忙解惑，以B3為例子，若B4:B19內有前後列儲存格內容差距堑分鐘內都標記同一種顏色。

參考值希望為:「日期」及「時間」

如附圖，希望B3:B19日期+時間資料可以變得像D3:D19這樣方式呈現!!。

時間轉數值輔助欄(E欄)

首先我們必須將B欄資料轉換成可以運算的數值資料。

故在E3儲存格輸入公式：

=VALUE(MID(D3,5,4)&RIGHT(D3,6))

並將E3公式複製到E4:E19儲存格中，將月日時分秒擷取出來變一串數字。

【公式解說】

MID(D3,5,4) 傳回D3儲存格從第5個字元開始的3個字元。

RIGHT(D3,6) 傳回D3儲存格右邊6個字元。

VALUE(MID(D3,5,4)&RIGHT(D3,6)) 將MID(D3,5,4)與RIGHT(D3,6)結合並轉換為數字。

分組數字輔助欄(F欄)

接下來我們要分析每筆月日時分秒轉換的數字與前一筆是否差距在2分鐘內。

故在F3儲存格輸入公式：

=IF(E3<E2+200,F2,ROW())

並將F3公式複製到F4:F19儲存格中，將資料分好組。

【公式解說】

IF(E3<E2+200,F2,ROW()) 如果E3儲存格小於E2儲存格加200的值，則填入F2的數值，否則填入F3的列號。

設定格式化的條件

這部份是較麻煩的，目前19列可以分成7組，資料愈多分的組別也愈多，要一一設定真的….。

框選D3:D19儲存格範圍。

點取「常用 > 設定格式化的條件▼鈕 > 新增規則」指令。

在【新增格式化規則】對話方塊，點取「使用公式來決定要格式化哪些儲存格」項目。

點取「格式化在此公式為TRUE的值」欄位，輸入「=$F3=3」。

點取「格式」鈕。

填取「填滿」標籤，再點取所要色彩的色塊。

點取「確定」鈕，回到【新增格式化規則】對話方塊。

點取「確定」鈕，回到工作表即可看到設定結果。

重複操作設定「格式化在此公式為TRUE的值」欄位，輸入「=$F3=5」、「=$F3=8」、「=$F3=10」、「=$F3=12」、「=$F3=14」、「=$F3=18」及相關填滿色彩的色塊。

結果如下圖所示。

若不想讓人看到E欄與F欄則可以將其隱藏起來。