2013年10月31日 星期四

Excel-設定儲存格底色呈現統計圖表(SUMPRODUCT)

在 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」(或自訂其他數值),最後修改最小值、中間值、最大值的色彩,最好是同一色系由淺至深,如下圖。

如此,便可大功告成,數值較大的儲存格顯示較深的色彩,數值較小的儲存格顯示較淺的色彩。這樣的圖表遠勝過原來數字呈現的圖表,馬上可以反應那些時段是網路流量較大/較小的時候。

真是一圖(表)勝萬字!

沒有留言:

張貼留言

檢視其他文章

好康東東