EXCEL 的應用-中學成績表步驟

2008/03/17

《課程目標》－成績表試作

Excel 中學成績表步驟一：

1、講解活頁簿、工作表及儲存格。

2、請同學開五張工作表：首頁一、總表一，三個科目工作表

n          首頁、總表、冬天到了、想睡覺、蹺課不點名。

3、使用「冬天到了」建立成績表範本。填入欄位。

n          從B3 開始填入欄位。學號、姓名，三次小考40%，期中30%、期末30%及平均。

4、清單的使用。

n          學號，49603001 開始，學生三十人。

l          使用滑鼠右鍵及左鍵，拉數列。

l          說明「自定清單」。「工具」=>「選項」=>自訂清單

n          姓名，高1，下拉三十位。同學請自行編姓名，不可用拉的。

5、填入成績部分，使用亂數填入成績。

n          成績輸入亂數

l          =RAND()*100（一個大於等於 0 但小於 100 的隨機亂數）

l          RAND，傳回一個大於等於 0 且小於 1 的隨機亂數。每當工作表重算時，便會傳回一個新的隨機亂數。可參閱 EXCEL 的說明。

l          所以大家要是覺得亂數出來的平均分數過低時，可以更改公式，比如 =RAND()*(100-20)+20 。

n          每次考試下方列，算其平均。

6、儲存格格式：

n          說明儲存格格式，並指定為數字，顯示小數點下一位。

7、說明 excel 函數格式：

n          函數的形式

l          =函數 (範圍，條件）

l          =function(range,criteria)

8、「設定格式化條件」

n          「格式」=「設定格式化條件」

9、平均：=AVERAGE(D4:F4)*0.4+G4*0.3+H4*0.3

10、                人數統計及百分比

n          函數 COUNTIF 的應用

l          計算某範圍內符合某搜尋篩選條件的儲存格個數。

l          COUNTIF(range,criteria)

n          比較：

l          若要計算一個以文字字串或範圍中數值為主的和，可以使用 SUMIF 工作表函數。若要公式傳回以條件式為主的兩個值中的其中一個，如指定銷售量的銷售金額，可以使用 IF 工作表函數。

11、                插入圖表：

n          說明各類型圖表的用途，比如圓形圖適合總數固定的百分比展現。

n          需插入一個區域圖，一個圓形圖。

l          說明數列。

l          說明圖例。

12、                三種預覽列印，設定頁首頁尾，版面。

Excel 中學成績表步驟二：

接下來是總表的進度。

1.          平均：如果直接使用 average 函數，若沒有數值時，會出現 “#DIV/0!” 的訊息，所以我們使用 if 函數。

n          含 IF 函數的公式 if(logical_test, value_if_true, value_if_false)

l          logical_test：您要檢查的條件。

l          value_if_true：條件為 true 時會傳回的值。

l          value_if_false：條件為 false 時會傳回的值。

n          例如：

l          IF(E4<60,"不及格","及格")。

l          也可以和AND 及 OR 函數併用：

1.          IF(AND(A2>84.5, A2<90),”A”,””)

2.          IF(OR(A2>A3, A2<A4),”測試”,””)

n          所以平均的部分，我們使用下列的公式：

l          IF(SUM(D4:F4),AVERAGE(D4:F4),"")

l          SUM(D4:F4)=>儲存格區域中出現任一數值。

l          True 則傳回 AVERAGE(D4:F4) 數值。

l          False 則傳回「空白」。

2.          名次：使用RANK 函數

n          RANK(number,ref,order)

l          Number：是要知道等級的數字。

l          Ref：是一個數值陣列或數值參照位址，非數值將被忽略。

l          Order：是指定的順序。

1.          如果 order 為 0 (零) 或被省略，則 Microsoft Excel 把 ref 當成從大到小排序來評定 number 的等級。

2.          如果 order 不是 0，則 Microsoft Excel 把 ref 當成從小到大排序來評定 number 的等級。

n          所以，我們使用的公式如下：

l          IF(SUM(G4),RANK(G4,G4:G33,0),"")

l           G4 儲存格的邏輯值：

1.          有數值則傳回RANK(G4,G4:G33,0) 之值

2.          無數值則傳回「空白」

n          但我們往下拉時，會出現一個狀況，就是「REF」數值參照位址會改變，這時我們必需加上 $ 。加上 $ 則不會改變。

3.          設定總表的圓形圖及分佈圖。

4.          成績使用「複製」=>「選擇性貼上」=>「貼上連結」保持與成績表同步。

接下來是首頁的製作：

5.          設定首頁的背景色彩，並使用「快取圖案」製作按鈕。

n          提醒使用複製貼上即可

6.          錄製巨集。

n          說明巨集必需為一個循環，結束時又回到首頁。

n          巨集可用錄製，也可以用編輯。

n          巨集必要給予名稱，才不會搞錯。

7.          指定快取圖案對應的巨集。

8.          巨集對應完畢後，記得群組物件。可以使設定不跑掉。

9.          中學成績表完成，請同學壓縮後，傳到我的信箱。

• EXCEL 一般公式檢索