Excel 函數練習：成績表
1. 將工作表標籤更名為「第一次月考」。
2. 利用亂數函數與四捨五入函數產生分數
ROUND(RAND()*60+40,0)
四捨五入函數：
ROUND(原始數值, 小數位數)。
3. 計算「總分」、「名次」、「加權平均」、「等第」、「實得學分」等欄位
名次函數：
RANK(比對值, 查詢範圍, 遞增遞減 )
遞增遞減 ＝0 或省略 表示遞減方式排序；
＝1 或其他非 0 數值 表示遞增方式排序
乘積和函數：
SUMPRODUCT(數列一, 數列二)
數列一：a
1
, a
2
, …, a
n
，數列二：b
1
, b
2
, …, b
n
，計算 a
1
b
1
+a
2
b
2
+…+a
n
b
n
條件式加總函數： SUMIF(條件範圍, 比對值, 加總範圍)
SUMIF(加總範圍, 比對值)
直式查表函數：
VLOOKUP(比對值, 查表範圍, 第幾欄)
級距表建表原則： (1) 第一欄為數值且由小而大由上而下；
(2) 第一欄數值取各個級距的下界
0
丁
60
0
x
60
丙
70
60
x
70
乙
80
70
x
80
甲
90
80
x
90
優
100
90
x
橫式查表函數：
HLOOKUP(比對值, 查表範圍, 第幾列)
IF 函數：IF(條件, 成立時回應值, 不成立時回應值)
4. 依指定的分數級距分別統計各個科目成績分布情形。
頻率函數：
FREQUENCY(資料範圍, 級距範圍)
步驟：
[1] 在空白儲存格輸入各個級距(上界)
[2] 選取輸出儲存格(比級距範圍多一格)
[3] 輸入 FREQUENCY 公式
[4] 按 F2 功能鍵
[5] 按 Shift+Ctrl+Enter
級距表
級距
人數
60
0
x
59.9
70
60
x
69.9
80
70
x
79.9
90
80
x
89.9
100
90
x
5. 自訂數值顯示格式，使得不及格分數用紅色顯示並標示「*」號，及格分數用藍色顯示並
且讓個位數字對齊
[>=60][藍色]0_*;[紅色]0”*”
6. MATCH 函數與 INDEX 函數
=MATCH(查詢值，範圍，型態)：傳回數「查詢值」在「範圍」中的第幾個位置
型態 = 0 時尋找第一個與查詢值完全相同的值
型態 = 1(或省略)時尋找第一個小於或等於查詢值的值，「範圍」必須按遞增排列
=INDEX(範圍，第幾列，第幾欄)：傳回數在「範圍」中第幾列、第幾欄之值
當「第幾欄」為 0 或省略(保留逗號)時則傳回整列資料，使用 Shift + Ctrl + Enter
7. MAX 函數與 MIN 函數
=MAX(範圍)：傳回數值範圍中的最大值
=MIN(範圍)：傳回數值範圍中的最小值
MOD(被除數, 除數)：傳回餘數
=MOD(12,9) 之結果為 3，又=MOD(-12,9) 之結果為 6
8. COUNT 函數
COUNT 函數：計算選取範圍中含有數值（含日期）的儲存格個數
COUNTA 函數：計算範圍中非空的儲存格個數
COUNTBLANK：計算範圍中空白的儲存格個數
COUNTIF 函數：計算範圍中符合指定條件的儲存格個數
=COUNTIF(範圍, 準則)，準則可以是數字、表示式或文字
9. 字串運算符號”&”與文字函數
LEN(字串)：傳回字串長度
LEFT 與 RIGHT 函數：傳回字串最左邊（最右邊）指定長度之字串
=LEFT(”abcdefghijk”, 3)之結果為”abc”
=RIGHT(”abcdefghijk”, 3)之結果為”ijk”
MID 函數：傳回字串自指定位置開始，指定長度之字串
=MID(”abcdefg”, 3, 5)之結果為”cdefg”
TRIM(字串)，刪除字串字尾的空白字元
TEXT(字串, 數字格式)：以指定的數字格式將數值轉成文字，例如
=”餘額為：”& TEXT(75128, “NT$ ###,##0”) & “元”
VALUE(字串)：將數值的文字字串轉換成數字資料
10. 日期函數
TODAY()，YEAR(日期)，MONTH(日期)，YEAR(日期)
DATE(年, 月, 日)：傳回日期型態數值，其中「年」為西元記年
DATEDIF(較小日期，較大日期，年月日)：傳回兩個日期的年數, 月數, 日數的差，其
中「年月日」為”Y”, “M”, “D”
EDATE(日期, 增加月數)：傳回增加(減少)月份後的日期，例如
EDATE("2013/1/30", 1) 之結果為 2013/2/28