2017年2月27日 星期一

Excel-根據網站瀏覽計算每週各天和各月的平均值並繪統計圖(SUMPRODUCT)

今天一時興起,趁著假期取得本網站(http://isvincent.pixnet.net)的網頁流量來分析一下,想要看看最近五年(超過1800筆數據)中,各星期幾和各月份的相關數據。
Excel-根據網站瀏覽計算每週各天和各月的平均值並繪統計圖(SUMPRODUCT)
一、分析各週每天和各月份的平均值
Excel-根據網站瀏覽計算每週各天和各月的平均值並繪統計圖(SUMPRODUCT)
【公式設計與解析】
先選取所有基本資料的儲存格範圍,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、瀏覽量。
1. 求各週每天的平均數
第(1)式:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*瀏覽量)
第(2)式:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*1)
儲存格F2:=第(1)式/第(2)式
複製儲存格F2,貼至儲存格F2:F8。
2. 求各月的平均數
第(1)式:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*瀏覽量)
第(2)式:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*1)
儲存格F11:=第(1)式/第(2)式
複製儲存格F11,貼至儲存格F11:F22。

二、製作每週各天和各月份平均值的統計圖
將計算所得數據產生二個直條圖,發現有趣的現象:
(1) 星期二~星期五每天的流量都差不多;星期六、日較少(約平常的一半)。
(2) 星期一也是上班(課)日,但為何流量會較少?
(3) 1月至12月的曲線呈現笑口的弧形,有特別的意義嗎?
(4) 一年中間月份的流量明顯較少?是因為學生放暑假嗎?
Excel-根據網站瀏覽計算每週各天和各月的平均值並繪統計圖(SUMPRODUCT)

沒有留言:

張貼留言

好康東東