close
昨天寫了一篇「不會 EXCEL 排名函數，是不是很可恥呢？」之後，發現去考試那天還有一個欄位沒有做，那就是「及格人數有多少個？」，讓我又繼續深入研究。找到一篇教導的文章 -- [Excel-成績單常用公式一覽表]，裡面講了許多深入的公式，也讓我了解好幾個有用的函數與相關概念，不過若沒有仔細做過一遍，根本就不可能明白其中有何玄機，而作者也沒有把關鍵處畫龍點睛講清楚，於是我就按圖索驥之後，把自己的領悟整理一下。
首先，要怎麼算及格人數呢（先把引發我研究的動機來源揭密）？就是利用 COUNTIF 這個函數。它有兩個欄位（範圍,判定），範圍很簡單就不多說，至於「判定」的話，譬如說及格分數是六十分，所以填入（">= 60"）即可，EASY 吧（越是容易，就令我越懊悔
）！
再來講一些使用 Excel 的重要基本觀念。在儲存格裡面有兩種參照模式，一種叫做「相對參照」；另一種叫做「絕對參照」。平常我們使用時都是相對參照，也就是說隔壁的欄位會模仿前面的欄位語法，然後將儲存格自動遞增或遞減。不曉得大家有沒有遇過不想要讓儲存格跟著移動的情況呢？譬如說，我們有一堆美金要換算台幣，而匯率是固定的，這時候如果我們不想傻傻的浪費太多儲存格，那麼就是使用「絕對參照」才是聰明的方法。如上圖，左邊是使用「絕對參照」，所參照的是上面 B1 那格。右邊則是使用 F 欄位的「相對參照」（Copy 完成的）。為了更清楚說明，請看下圖：
招式：（在 EXCEL 裡，按住 CTRL + ` 鍵，就會顯示出所有的公式，好用！
）請看上圖 B 欄，台幣的被乘項目都是 $B$1 ； F 欄則的台幣被乘項目依序為 F4、F5、F6、F7、...。所以右邊浪費了許多欄位，而左邊則是參照絕對欄位 B1 而已。語法的差別是「金錢號 $」。這個 $ 會把欄位固定，因為試算表有排(Row)英文與列(Column)數字，所以要記得如果只有單一絕對欄位，則必須加入 $ 在英文與數字的欄位代號前面。若只想固定「排(Row)英文」，請鍵入顯示如 $B1 ；若只想固定「列(Column)數字」，請鍵入顯示如 B$1。
招式：（指定欄位後，請按 F4 ，便會顯示「相對參照」和「絕對參照」供選擇，省去鍵入需要挑選好位置的煩人金錢符號）
接下來就是進入 [Excel-成績單常用公式一覽表]中的難題解說。這個表格運用到的函數如下表：
結果 使用函數
加權平均 SUMPRODUCT + SUM
名次 Rank
及格 IF
等級 VLOOKUP
各科最高分 MAX
各科最低分 MIN
參加考試人數 COUNT
缺考人數 COUNTIF
各科第三高之分數 LARGE
各科倒數第三之分數 SMALL
甲班各科分數合計 SUMPRODUCT + LEFT
乙班各科分數合計 SUMPRODUCT + LEFT
丙班各科分數合計 SUMPRODUCT + LEFT
八十分以上分數合計 SUMIF
六十分以上分數平均 AVERAGE + IF
頂標（前25%）分數平均 AVERAGE + IF + QUARTIlE
高標＼前標（前50%）分數平均 AVERAGE + IF + QUARTIlE
均標（所有分數平均） AVERAGE
後標（後50%分數平均） AVERAGE + IF + QUARTIlE
低標（後25%分數平均） AVERAGE + IF + QUARTIlE
前三高分數平均 AVERAGE + LARGE + ROW
眾數 ISERROR + MODE
標準差 STDEV
中間值 MEDIAN
變異數 VAR
各科及格人數 COUNTIF
前三名學生姓名 INDEX + MATCH + LARGE
前三名學生成績 INDEX + MATCH + LARGE
加權平均 SUMPRODUCT + SUM
名次 Rank
及格 IF
等級 VLOOKUP
各科最高分 MAX
各科最低分 MIN
參加考試人數 COUNT
缺考人數 COUNTIF
各科第三高之分數 LARGE
各科倒數第三之分數 SMALL
甲班各科分數合計 SUMPRODUCT + LEFT
乙班各科分數合計 SUMPRODUCT + LEFT
丙班各科分數合計 SUMPRODUCT + LEFT
八十分以上分數合計 SUMIF
六十分以上分數平均 AVERAGE + IF
頂標（前25%）分數平均 AVERAGE + IF + QUARTIlE
高標＼前標（前50%）分數平均 AVERAGE + IF + QUARTIlE
均標（所有分數平均） AVERAGE
後標（後50%分數平均） AVERAGE + IF + QUARTIlE
低標（後25%分數平均） AVERAGE + IF + QUARTIlE
前三高分數平均 AVERAGE + LARGE + ROW
眾數 ISERROR + MODE
標準差 STDEV
中間值 MEDIAN
變異數 VAR
各科及格人數 COUNTIF
前三名學生姓名 INDEX + MATCH + LARGE
前三名學生成績 INDEX + MATCH + LARGE
以上就是用運算上面結果所需要的函數列表。若單靠 EXCEL 的說明，我想很多人都無法參透、摸索出函數的應用方式，更不用說怎麼變化。我的建議是，把[Excel-成績單常用公式一覽表]所用的的結果運算都操作一遍，不會的地方，可以直接貼他列表中的函數算，這樣慢慢地就可以搞熟公式的使用方法，我這邊負責把困難點寫出來即可。
SUMPRODUCT 是乘積和，形式就像這樣： (D5*D16) + (E5*E16) + (F5*F16) + (G5*G16) 。所以說加權平均，就是把各科目成績乘上權重加總起來再除以總和 ==> SUMPRODUCT / SUM。這邊要小心的當然是 SUMPRODUCT ，參數是（排列 1、排列 2、排列 3、....），而每個排列的個數都要一致。
各科最高、低分與參加人數就沒什麼好說。但是缺考人數就需要判斷，所以是使用 COUNTIF，若發現是「空集合」，則計數才會算入缺席者人數。
LARGE 與 SMALL 函數不難，就是選取範圍之後，然後指定要第幾個。如果要找第二高分，那就使用 LARGE（範圍, 2）。
各班各科成績平均用到 SUMPRODUCT + LEFT。這個 LEFT 是為了要找尋班級字元，也就是說要先找出是甲班的那群資料、乙班的資料...。透過 LEFT 的搜尋，才能夠把運算集中在各班級的各科成績上，利用 SUMPRODUCT 是將符合班級的資料當為「真」處理。也就是說符合甲班的成績都乘上 1 再加總（換句話說，不是甲班的成績都乘上 0 來消滅！）。「甲班各科分數合計」之 LEFT 所選的是前面欄位「甲班各科分數合計」的「甲」這個字，在 SUMPRODUCT 運算中有關於「甲」的資料才會當成 1 做運算。
SUMIF 應該沒什麼好講，就只要列出條件，符合條件的就加起來。不過「六十分以上分數平均」這個運算使用到 AVERAGE + IF 就必須有陣列運算的概念才行。「六十分以上分數平均」的意思是有達到六十分以上的分數要加起來取平均，技巧就在於先找到六十分以上的分數欄位，然後要看有幾個欄位數，這樣才可以取平均。然而，IF 這個函數一般只能判斷單一欄位，要處理多重欄位的話，就需要運用陣列公式，如果不使用陣列公式，那麼算出來的東西就會顯示錯誤。所以這邊的陣列公式意思是先將所有資料做判斷後，做成陣列，然後用陣列形式取平均數。下面好幾項運算都會用到『陣列公式』。
招式：（陣列公式鍵入後，請按 Cmd + Shift + Enter，則公式會在外面加上大括號 { } 來標示）
QUARTIlE 這是一個固定好運算模式的函數，它是用來算資料的四分位數，可以求得前 25%、50％、75％...等資料，簡單而好用。因此結合這個函數與AVERAGE + IF 就可以把高、低標這類平均分數找出來。
續篇：「再次挑戰更高一級的運算（下）」
全站熱搜
留言列表
