2013年6月5日 星期三

Excel-使用表單核取方塊篩選要採計之成績(SUMPRODUCT)

在 Excel 的工作表中日積月累建立了一個成績表(如下圖),但是在最後時若是只想要篩選某些項次的成績來處理,每次都要修改公式,實在很煩人!該如何處理呢?

在下圖中的成績表如果想要篩選其中要計算的項目,可以利用表單中的「核取方塊」來進行篩選的動作,而計算總和和加權平均的公式,則會依篩選的項次來決定計算的內容。

參考以下的步驟來操作:

1. 選取[開發人員/控制項]中的「插入」,再選取「核取方塊」項目。

2. 在儲存格B2位置中新增一個核取方塊,並輸入文字「採計」。

3. 設定這個核取方塊的控制項格式,並將其中儲存格連結設定:$B$1。

4. 參考以下圖,重覆以上步驟,在儲存格B2:G2位置中建立 6 個核取方塊。

5. 設定每個核取方塊之控制項格式,其中儲存格連結指定在核取方塊上一列的儲存格。

6. 輸入以下公式:

(1) 篩選後加權總和

儲存格H3:=SUMPRODUCT(B3:G3*B1:G1)

儲存格B1:G1會產生 True/False 陣列,可以控制儲存格B3:G3是否列入加總。

(2) 篩選後總和

儲存格H5:=SUMPRODUCT(B5:F5*$B$1:$F$1)

(3) 篩選後加權平均

儲存格I5:=SUMPRODUCT(B5:G5*$B$3:$G$3*$B$1:$G$1)/$H$3

複製儲存格H5:I5,往下各列貼上。

只要你勾選想要採計的項目,即會產生 True/Fasle 的陣列,所有為 True 的項目才會被併入相關的計算。而如果你想要將未列入採計的欄位項目以灰色淡化的字呈現(參考下圖),以格式化條件來設計:

你只要選取所有的分數,並新增一個規則:

規則類型:使用公式來決定要格式化哪些儲存格,規則公式:=NOT(B$1),設定格式:文字色彩為灰色。

 

【補充資料】

詳細之函數說明,請參閱微軟網站的說明:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

檢視其他文章

好康東東