在 Excel 中取得一個含有日期時間的資料表(參考下圖左)，例如：網路連線的 log 資料等，如何將這些資料依星期和時間形成矩陣統計結果，根據這個統計表設定儲存格底色呈現統計圖表(參考下圖右)？這個圖表可以用來呈現每個時段的網路流量大小。
例如在下圖中是一個月中搜集到的網路流量資訊，其中記錄了日期、時間和資料，所以要根據日期資料區隔出週日、週一、…、週六，根據時間資料區隔出 0 時至 23 時，並分別統計在這些時段中的數量，再依據這些數量，讓數據大者呈現較深的儲存格底色，數據小者呈現較淺的儲存格底色。
【準備工作】
選取A欄和B欄中含有資料的儲存格，按一下 Ctrl+Shift+F3 鍵，勾選頂端列，定義名稱：日期、時間。
【輸入公式】
儲存格F2：=SUMPRODUCT((WEEKDAY(日期,1)=COLUMN(A:A))*(HOUR(時間)=ROW(1:1)-1))
WEEKDAY(日期,1)=COLUMN(A:A)：在日期陣列中透過 WEEKDAY 函數找出星期日的陣列，其中參數 1，表示傳回數字 1 為星期日、…、數字 7 為星期六。
COLUMN(A:A)=1，當往右複製時會產生 COLUMN(B:B)=2、COLUMN(C:C)=3、…。
HOUR(時間)=ROW(1:1)-1：透過 HOUR 函數找出時間陣列中時數形成的陣列。
ROW(1:1)=1，當往下複製時會產生 ROW(2:2)=2、ROW(3:3)=3、…。
將以上二式，透過 SUMPRODUCT 函數即可取得該時段中的個數。
複製儲存格F2，貼至儲存格F2:L25。
接著，要將顯示的數字隱藏起來！
選取儲存格F2:L25，設定儲存格格式，自訂數值格式為「;;;」(三個分號)，儲存格內容即會不顯示出來，也就是被隱藏了。
接著，要來設定儲存格的底色。
選取儲存格F2:L25，選取[常用/樣式]功能表中的「設定格式化的條件」項下的「色階/其他規則」。接著設定規則：
在[格式樣式]中選取「三色色階」，在[中間點]項下的值設定為「50」(或自訂其他數值)，最後修改最小值、中間值、最大值的色彩，最好是同一色系由淺至深，如下圖。
如此，便可大功告成，數值較大的儲存格顯示較深的色彩，數值較小的儲存格顯示較淺的色彩。這樣的圖表遠勝過原來數字呈現的圖表，馬上可以反應那些時段是網路流量較大/較小的時候。
真是一圖(表)勝萬字！
留言列表