學不完．教不停．用不盡

在現行的會考成績有「等級標示：A++、A+、A、B++、B+、B、C，對應點數：7、6、5、4、3、2、1」的換算。當取得一個學生的會考成績報表，試著來進一步處理。

1. 計算每個人的總點數

先為所有資料定義：

名稱：班級，儲存格範圍：A4:A350

名稱：等級標示，儲存格範圍：K4:K10

名稱：點數，儲存格範圍：L4:L10

名稱：資料，儲存格範圍：A4:H350

儲存格H4：=SUMPRODUCT((D4:H4=等級標示)*點數)

複製儲存格H4，貼至儲存格H4:H350。

(1) 判斷符合條件「D4:H4=等級標示」者，傳回 TRUE／FALSE 陣列。

(2) (D4:H4=等級標示)*點數，運算時 TRUE／FALSE 陣列會轉換為 1／0 陣列。

(3) 最後加總 1／0 陣列的總和即為所求。

2. 列出所有科目各等級的數量

儲存格L18：=SUMPRODUCT((班級=$K18)*(SUBSTITUTE(資料,L$17,"")<>資料))

複製儲存格L18，貼至儲存格L18:R32。

(1) 條件一：(班級=$K18)

判斷是為儲存格K18所指的班級，傳回 TRUE／FALSE 陣列。

(2) 條件二：(SUBSTITUTE(資料,L$17,"")<>資料)

利用 SUBSTITUTE 函數將資料內容置換儲存格L17的內容為空字串，如果兩者不相等，表示資料內容有包含儲存格L17的內容，傳回 TRUE／FALSE 陣列。

(3) ((班級=$K18)*(SUBSTITUTE(資料,L$17,"")<>資料)

將二個條件執行「*」運算，相當於執行邏輯 AND 運算，並且會將 TRUE／FALSE 陣列轉換為 1／0 陣列再加總。

3. 列出各班共有幾個A

儲存格K9：=SUMPRODUCT((班級=K9)*(SUBSTITUTE(資料,"A","")<>資料))

計算原理同「2. 列出所有科目各等級的數量」。

4. 列出各班「國文A++」清單

儲存格K12：=FILTER(班級,國文="A++")

儲存格L12：=FILTER(姓名,國文="A++")

儲存格M12：=FILTER(總點數,國文="A++")

利用 FILTER 函數執行篩選工作。

5. 列出各班「英文A, A+, A++」清單

儲存格K12：=FILTER(班級,LEFT(英文,1)="A")

儲存格L12：=FILTER(姓名,LEFT(英文,1)="A")

儲存格M12：=FILTER(總點數,LEFT(英文,1)="A")

利用 FILTER 函數執行篩選工作，並以 LEFT 函數判斷英文欄位的第1個字元是否為「A」。

【參考資料】

UNIQUE 函數參考微軟提供的說明網頁：UNIQUE 函數

FILTER 函數參考微軟提供的說明網頁：FILTER 函數