Chapter 6. 函式 (一)：邏輯與運算 › 6.5 統計值與資料大小排序
6.5 統計值與資料大小排序
本節會介紹多個與統計資料相關的函式，將先介紹各函式的用法，再使用範例說明：
- 基本統計資料：
語法 | COUNT / SUM / MIN / MAX / AVERAGE / MEDIAN / MODE / STDEV / STDEVP / VAR / VARP (值_1, [值_2,…]) |
輸入 | 值：要計算的範圍，可以是多個儲存格，或是單一值都可以，例如 =MIN(A1:A4) 或 =MIN(1, 2, 3, 4, 5)。 |
輸出 |
若上述的 值 包含非數字資料時，將會自動忽略。 |
Google Sheets 提供另一系列的函式，若遇到 值 包含非數字資料時會視為 0，只要將函式名稱加上 A 即可，即 COUNTA / MINA / MAXA / AVERAGEA / STDEVA / STDEVPA / VARA / VARPA，其函式輸入與輸出完全相同。
- 四分位數與百分位數的值：
語法 | QUARTILE / PERCENTILE (資料, 四分位數 / 百分位數) |
輸入 |
|
輸出 | 資料 中的最接第 四分位數 / 百分位數 個四分位數 / 百分位數的值，若介於兩筆資料之間會自動計算與兩者的間距。 |
- 取得第 n 小 / 大的值：
語法 | |
輸入 |
|
輸出 | 資料 中第 n 小 / 大的值。 |
- 取得某個值的排序是第幾大 / 小：
語法 | RANK (值, 資料, [遞增]) |
輸入 |
|
輸出 | 值 在 資料 中排第幾大 / 小，非數字資料會自動忽略。 |
- 取得某個值的排序是第幾百分位：
語法 | PERCENTRANK (資料, 值, [有效位數]) |
輸入 |
|
輸出 | 值 在 資料 中位於多少百分位，在 0~1 之間，若在兩筆資料之間會計算與兩者的間距。若小於等於 MIN(資料) 或大於 MAX(資料) 則輸出 #N/A。 |
以下使用四個基本的範例說明各函式的用法，完成結果如下：
- 在 E2:E9 分別計算每一位學生的總分
公式
E2 的公式為 =SUM(B2:D2)。
解說
直接將 B:D 欄的值加總，再複製到第 3~9 列即可。
- 數學分數的各項指標，包括 (1) 平均、(2) 標準差、(3) 最低分、(4) 最高分、(5) 第二高分、(6) Q1~Q3
公式
(1) =AVERAGE(C2:C9)
(2) =STDEV(C2:C9)
(3) =MIN(C2:C9)
(4) =MAX(C2:C9)
(5) =LARGE(C2:C9,2)
(6) =QUARTILE(C2:C9,1/2/3)
解說
(6) 的 Q2 即中位數，可使用 =MEDIAN(C2:C9) 代替。
- 計算數學 60 分 (1) 是所有學生的第幾高分、(2) PR 值 (0~99 的整數)
公式
(1) =RANK(60,C2:C9,0)
(2) =PERCENTRANK(C2:C9,60,2)*100
解說
(2) 因為 PR 值為 0~99 的整數，因此要將百分位數取到小數點後兩位再乘上 100。
上述三個範例都相當簡單直觀，接下來將再示範兩個之前介紹過的範例，但使用不同的方法完成：
- 將三科平均 > 80 分的學生整列 (A:E 欄) 填滿顏色改為「淺綠色 3」
結果
公式
套用範圍「A1:E9」，自訂公式「=AVERAGE($B1:$D1)>80」。
解說
在第 6.1 節範例 A. 使用三個科目加起來計算，但在這節介紹 AVERAGE 後，可以用 AVERAGE 取代之，公式更直觀簡單。
- 將「有兩科以上 (含) 未達 60 分的學生」整列 (A:E 欄) 填滿顏色設定為「淺紅色 3」
結果
如範例 D. 所示。
公式
套用範圍「A1:E9」，自訂公式「=SMALL($B1:$D1,2)<60」。
解說
在第 6.3 節範例 F. 計算有幾個科目小於 60 分，然後再判斷是否大於等於 2 科，但在此可以直接判斷第二低分的科目是否小於 60 分，如果小於 60 分代表至少有兩個科目未達 60 分。
了解上述內容後，可以嘗試使用 =LARGE(...) 替換上述公式，確保自己是否理解。