2016年1月8日 星期五

Excel-由分數清單計算加權平均並在儲存格中繪製橫條圖(SUMPRODUCT)

同仁問到:常會看到下圖這種表示法,在 Excel 中如何模擬?
Excel-由分數清單計算加權平均並在儲存格中繪製橫條圖
在下圖中,假設A欄為1~5的分數清單(本例共有10027個)。
Excel-由分數清單計算加權平均並在儲存格中繪製橫條圖

【公式設計與解析】
先選取A欄中所有的分數,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:分數。
(1) 計算評分次數
儲存格C4:="評分次數:"&COUNT(分數)
(2) 計算加權平均分數
儲存格C3:=SUMPRODUCT((分數={5,4,3,2,1})*{5,4,3,2,1})/COUNT(分數)
(3) 計算各個分數的數量
儲存格E3:=SUMPRODUCT(--(分數=5))
儲存格E4:=SUMPRODUCT(--(分數=4))
儲存格E5:=SUMPRODUCT(--(分數=3))
儲存格E6:=SUMPRODUCT(--(分數=2))
儲存格E7:=SUMPRODUCT(--(分數=1))
(4) 設定樣式
接著,選取儲存格E3:E7,設定[常用/設定格式化的條件]功能表中的「資料橫條」,再選取一種樣式。
Excel-由分數清單計算加權平均並在儲存格中繪製橫條圖

沒有留言:

張貼留言

檢視其他文章

好康東東