贊助廠商

///本部落格所有文章列表///

同仁問到:常會看到下圖這種表示法,在 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-由分數清單計算加權平均並在儲存格中繪製橫條圖

vincent 發表在 痞客邦 留言(0) 人氣()

網友根據這篇:

Excel-取含有文字的儲存格中計算數字總和(SUMPRODUCT,SUBSTITUTE)

想要將資料格以下圖格式呈現,該如何將『核』字消除,再計算小計。

Excel-取含有文字的儲存格中計算數字總和(SUMPRODUCT,SUBSTITUTE)

 

【公式設計與解析】

儲存格K2:=SUMPRODUCT(1*(SUBSTITUTE(B2:J2,"核","")))

SUMPRODUCT 函數中,儲存格B2:J2被視為陣列儲存格。

SUBSTITUTE(B2:J2,"核",""):將儲存格範圍的每個儲存格中的『核』字消除,留下數字,傳回數字的陣列。不過,這個數字其實是文字格式。

其中的『*1』,可以將上式中的文字格式轉換為數值格式。

再透過 SUMPRODUCT 函數予以數字加總

複製儲存格K2,貼至儲存格K2:K11。

vincent 發表在 痞客邦 留言(1) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼