2018年1月20日 星期六

Excel-比對答案自動計算分數(多重選擇)(SUMPRODUCT,SUBSTITUTE)

如何利用 Excel 來計算多重選擇的總得分?如下圖,假設每個題目的答案由 A, B, C, D 所組成,答案可能是其中的 1 ~ 4 個所組成。
Excel-比對答案自動計算分數(多重選擇)(SUMPRODUCT,SUBSTITUTE)
其得分的標準:
●該選的選項也有選:+1分
●該選的選項沒有選:+0分
●不該選的選項沒有選:+1分
●不該選的選項卻有選:+0分

【公式設計與解析】
1. 計算每個題目題分
儲存格D2:=SUMPRODUCT(((SUBSTITUTE(B2,{"A","B","C","D"},"")=B2)=
(SUBSTITUTE(C2,{"A","B","C","D"},"")=C2))*1)
複製儲存格D2,貼至儲存格D2:D26。
在 SUMPRODUCT 函數中,公式可以陣列形式來運算。{"A","B","C","D"} 表示由 A、B、C、D 字元組成的陣列。
(1) SUBSTITUTE(B2,{"A","B","C","D"},"")=B2
利用 SUBSTITUTE 函數將儲存格B2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格B2相同。例如,置換 A 後若傳回相同,代表儲存格B2中沒有 A 字元。

(2) SUBSTITUTE(C2,{"A","B","C","D"},"")=C2
利用 SUBSTITUTE 函數將儲存格C2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格C2相同。

(3) (第(1)式)=(第(2)式))*1
在 SUMPRODUCT 函數中,判斷第(1)式和第(2)式的結果是否相等,傳回 TRUE/FALSE 陣列。因為 TURE 代表得分(+1),FALSE 代表沒有得分(+0),所以利用公式中的『*1』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列。
最後經由 SUMPRODUCT 函數予以加總,即為該題得分。

2. 計算所有題目總得分
儲存格G2:=SUMPRODUCT(((SUBSTITUTE(B2:B26,{"A","B","C","D"},"")=B2:B26)
=(SUBSTITUTE(C2:C26,{"A","B","C","D"},"")=C2:C26))*1)
有了「1. 計算每個題目題分」的運算經驗,只要將公式中的儲存格B2置換成儲存格B2:B26,和將儲存格C2置換成儲存格C2:C26,即為所求。
由本例來看,善用 SUMPRODUCT 函數來處理陣列形式的運算,可以縮短公式的總量。或許對很多人而言,其實看不懂這樣的公式,建議由其他較簡單的 SUMPRODUCT 函數運算先理解。

2018年1月14日 星期日

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

在 Excel 的工作表中如果能好好利用設定格式化的條件來處理多儲存格的格式,可以不怕儲存格新增/刪除所帶來又要重設的困擾。
例如:(參考下圖)如何在一個資料表中,自動為間隔欄、間隔列、間隔欄列交會產生不同的背景色彩。
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
這是原始表格:
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
1. 產生間隔欄不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=MOD(COLUMN(B2),2)=1
格式:設定淺綠色的背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
利用 COLUMN 函數取得儲存格的欄數,利用 MOD 函數判斷欄數是否奇數欄。

2. 產生間隔列不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=MOD(ROW(B2),2)=1
格式:設定淺綠色的背景色彩
利用 ROW 函數取得儲存格的列數,利用 MOD 函數判斷列數是否奇數列。

3. 產生間隔欄列不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=(MOD(COLUMN(B2),2)=1)+(MOD(ROW(B2),2)=1)
格式:設定淺綠色的背景色彩
公式中的『+』運算子,相當於執行邏輯 OR 運算。
4. 產生間隔欄列交會不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=(MOD(COLUMN(B2),2)=1)*(MOD(ROW(B2),2)=1)
格式:設定淺綠色的背景色彩
公式中的『*』運算子,相當於執行邏輯 AND 運算。

2018年1月13日 星期六

Excel-由大至小列出數值清單中出現次數最多者(MODE)

在 Excel 的工作表中有一個數值構成的資料清單,如何由些數值中找出出現次數最多者,並依由大至小排列?
在下圖中,出現最多的數字是 81,而其出數次數是 9。排列順序依次數由大至小排列。
Excel-由大至小列出數值清單中出現次數最多者(MODE)

【公式設計與解析】
選取儲存格A2:J18,按 Ctrl+F3 鍵,在名稱管理員中定義名稱:DATA。
儲存格L2:{=MODE(IF(COUNTIF(L$1:L1,DATA),"",DATA))}
儲存格M2:=COUNTIF(DATA,L2)
複製儲存格L2:M2,貼至儲存格L2:M18。
在公式中使用 MODE 函數,乃是因為 MODE 函數會傳回陣列或資料範圍中最常出現,或重複的值。
(1) COUNTIF(L$1:L1,DATA)
在陣列公式中計算在DATA儲存格範圍中和儲存格L1內容相符者的個數。
(2) IF(COUNTIF(L$1:L1,DATA),"",DATA)
COUNTIF(L$1:L1,DATA)如果傳回大於 0 的數,表示儲存格L1裡的數已計算過,所以傳回空字串;否則傳回 DATA 儲存格範圍的內容 .
(3) MODE(IF(COUNTIF(L$1:L1,DATA),"",DATA))
將第(2)式的結果代入 MODE 函數,就會找出個數最多者。

檢視其他文章

好康東東