在 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來處理。

 

 

【延伸閱讀-FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

圖片1 Excel-根據單條件和雙條件篩選資料(FILTER)

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

圖片1 Excel-由資料清單中篩選一組

圖片1 Excel-利用FILTER函數模糊篩選

圖片1 Excel-從日期清單中區別平日和假日計算總和

圖片1 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

圖片1 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

 

學不完.教不停.用不盡文章列表

arrow
arrow
    全站熱搜

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