==== 試算表 ====

一、學習目標

由一份成績單的製作過程,學習試算表的幾項基本功能:

  1. 工作表、欄、列的插入與刪除
  2. 填滿控點的使用
  3. 公式與函數
  4. 相對參照與絕對參照
  5. 格式設定與格式化條件

 

二、基本組成

  1. 活頁簿:每一個 Excel 的檔案稱為一個 活頁簿,其副檔名為 .xls。
  2. 工作表:每個活頁簿 可以包含多個表格,每個表格稱為 工作表,可以在左下角的工作表標籤的地方進行工作表的插入、刪除、移動和重新命名等動作。
  3. :工作表中每一個縱向的切割稱為一個 ,每一欄以一到兩個英文字母區分,由左至右依序為 A、B、C、.....Y、Z、AA、AB、....、IU、IV,最大有 256欄。
  4. :工作表中每一個橫向的切割稱為一個 ,每一列以數字區分,由上到下依序為 1、2、3、.....65534、65535、65536,最大到 65536 列。
  5. 儲存格:真正儲存資料的地方,每一個儲存格依其所在的欄與列做區分,例如第 C 欄第 10 列則稱為 C10。

 

三、資料輸入

  1. 文字資料
  2. 數值資料
  3. 公式:以等號 = 開頭,後面接要計算的運算式,可使用儲存格名稱及函數。如果要顯示公式本身,請在前面加上單引號 '

 

四、常用函數

  1. SUM:計算總合
  2. AVERAGE:計算平均
  3. RANK:計算排名
  4. COUNT:計算個數
  5. COUNTIF:計算符合條件的個數
  6. MAX:最大值
  7. MIN:最小值

 

五、參照

  1. 相對參照:直接在公式或函數中寫上該儲存格的欄名與列名,例如:A1、B3。當公式或函數被複製到其他儲存格時,會自動改變對應的欄與列,也就是說它是記錄該儲存格以及公式所在儲存格的相對位置。
  2. 絕對參照:在欄名或列名前面加上錢字號 $,例如 $A1、B$3、$C$9。加上 $ 的欄或列在公式被複製時,該欄或該列則不會改變。
  3. 範圍的參照:要指定一個範圍儲存格,則是寫出左上以及右下兩格儲存格的參照,中間用冒號 : 隔開,例如:A1:B3。而如果要指令多個範圍,則是在範圍之間用逗點 , 隔開,例如:A1:A10,C1:C10,E1:E10。
  4. 跨工作表的參照:在參照前面加上工作表名稱以及驚歎號 !,即可使用跨工作表的參照,例如:Sheet2!A1、Sheet3!B3:C9。

 

六、作業規定

完成下列動作:

  1. 刪除不必要的工作表,並將該工作表更名為「成績單」。
  2. 在最左邊加入一欄座號,從 1 到 25 號。(填滿控點)
  3. 在最上面加入一列欄位名稱(座號、姓名....等等)。
  4. 在最上面再加入一列標題,並將儲存格合併。
  5. 依照 5、4、4、4、4 的比重,計算每一位同學的總分、平均。
  6. 使用 RANK 函數(絕對參照)編排名次。
  7. 在最下面增加兩列「各科平均」以及「不及格人數」(COUNTIF 函數)。
  8. 在各科成績的部分,將 90 分以上以藍色表示,不到 60 分的以紅色表示。(設定格式化條件)
  9. 設定各欄之欄寬以及各儲存格之框線、底色。

以下為額外之練習:

  1. 在各科平均下面增加一列「全距」,即最高分與最低分之差。
  2. 在不及格人數下面再增加一列「及格率」,即該科 60 分以上之人數除以全班人數,以 % 方式呈現,並取到整數位。
  3. 依照每位同學的加權平均,在表格右邊計算 90~100 分、80~90 分、70~80 分、60~70 分、0~60 分之人數(兩個 COUNTIF 相減)。

PS.原始檔中之人名、分數,均為電腦亂數產生,如有雷同,均屬巧合。