在 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),設定格式:文字色彩為灰色。
【補充資料】
詳細之函數說明,請參閱微軟網站的說明:
SUMPRODUCT:http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。 |
語法:SUMPRODUCT(array1, [array2], [array3], ...) array1:要求對應元素乘積和的第一個陣列引數。 array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
【延伸閱讀-FILTER函數篩選應用】
Excel-下拉式清單選取月份列出該月日期
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)
留言列表