淺談Excel函數在教學中的應用

智慧

摘 要：Excel是微軟公司出品的Office系列辦公軟件，可以用來完成許多復雜的數據運算、數據分析和數據預測。在教學和教育管理工作中，我們可以利用Excel公式和函數功能幫助我們解決教學管理和成績統計的各種問題。實現成績自動分析統計，教學計劃、考勤自動設置、自動管理，使教師避免大量復雜的數據運算和重復性勞動。

關鍵詞：Excel函數；教學管理；統計

一、準工作日的設置

在Excel表格中，日期系列是一串連續的數字，但工作日期不包括周六、周日兩天。如何顯示出實際的工作天數呢？Excel可以幫助我們計算出“準工作日”。

a.啟動Excel 2010，新建一個教師員工的工作簿。在sheet 1工作表的A2、B2單元格中分別輸入列標題為“姓名”和“日期”，然后單擊 “合并及居中”按鈕將A2和A3兩個單元格合并為1個單元格，然后輸入第一個教師的姓名，按照以上方法輸入其他教職工的姓名。

b.在單元格C1中輸入“3-1”回車，單元格內容自動轉成“3月1日”。用鼠標選中單元格C1，待出現實心“十”字的填充柄后，按住鼠標左鍵向右拖動到需要的位置，這樣在3月1日后就形成了一個日期序列。

c.單擊日期序列右下角的“自動填充選項”，在彈出來的菜單中點選“以工作日填充”項，單元格日期系列便自動刪除星期六、星期日兩天。

d.選中整個日期行，在Excel 2010版中單擊“開始”—數字欄中的“自定義”下拉菜單—“其他數字格式”—“自定義”項，在類型框中刪除“m”月留下“d”日，然后單擊“確定”按鈕。

二、宏按鈕的設置

宏，譯自英文單詞Macro。宏是微軟公司為其Office軟件包設計的一個特殊功能，軟件設計者為了讓人們在使用軟件進行工作時，避免一再地重復相同的動作而設計出來的一種工具，它利用簡單的語法，把常用的動作寫成宏，當在工作時，就可以直接利用事先編好的宏自動運行，去完成某項特定的任務，而不必再重復相同的動作，目的是讓文檔中的一些任務自動化，很多不會編程的人都可以使用。在考勤表中，只要我們錄制宏，輸入now（）函數，當單擊按鈕時，就能顯示當前的時間，實現宏按鈕對時間的自動設置。

操作方法如下：

a.單擊“視圖→宏→錄制宏”—“錄制新宏”—輸入“返回當前時間”—確定。

b.在單元格C3中輸入公式“=now（）”，按回車鍵，然后單擊“停止錄

制”。

c.（Excel 2010版制作按鈕的方法和其他版本不同）單擊“文件”—“選項”—“自定義功能區”—“開發工具”—“確定”—“開發工具”按鈕—“插入”—“表單控件”選擇按鈕。

d.在單元格B3中拖動鼠標繪制一個按鈕。Excel將自動打開“指定宏”對話框，點選其中的“返回當前時間”宏，再單擊“確定”。

e.制作和復制“上、下班”按鈕宏。

三、錄入時間的設置

錄入時間的設定包括兩個方面：

a.時間格式的設定：在單擊“上班”“下班”按鈕時在單元格中會返回“年份+時間”，其實我們只需要“8：00”這樣的時間就可以了。方法：單擊“開始”—數字窗—“常規”—“其他數字格式”—“數字”—“時間”—“13：30”—“確定”。

b.要將出勤記錄及時由公式結果轉換成具體的時間數值，否則當錄入新的時間時，公式被重新計算后，其他時間將更改。方法：選中表中已錄入的時間數據單元格，單擊右鍵選擇“復制”，點選“編輯→選擇性粘貼”菜單項，在“選擇性粘貼”對話框中選中“數值”項，然后單擊“確定”。

四、考勤結果的統計

（1）統計單元格與姓名單元格相匹配。

在日期行之后的單元格中依次輸入“遲到”“早退”“病假”“事假”等需要統計的項目。并將這幾列中的單元格上下兩兩合并，使之對應于姓名行。否則就會把“下班”和“上班”分別統計為“遲到”和“早退”。

（2）統計的時間格式與錄入考勤時間的格式相匹配。

在這里，我們使用COUNTIF函數自動統計考勤結果。COUNTIF函數是Microsoft Excel中對指定區域中符合指定條件的單元格計數的一個函數。

參數：range要計算其中非空單元格數目的區域。參數：criteria以數字、表達式或文本形式定義的條件。

例如：a.單擊“遲到”下面的單元格。

b.鍵入公式“=COUNTIF（C3：H3，>“2013/3/1 8：00”）”（因為計算機顯示自動時間格式是2013/3/1 8：00），按回車鍵，I23單元格中就會出現選中員工所有遲于8：00上班的工作日天數。

C3：H3：第一個教職工“上班”的日期所占的單元格區域；

2013/3/1“8：00”：是教職工“上班”的日期和時間。

c.在單元格中輸入公式“=COUNTIF（C4∶H4，“2013/3/1 17：00”）”，并按回車鍵，J23單元格中便會出現選中員工所有早于17：00下班的工作日天數。顯示結果，病假函數的使用：COUNTIF（C3∶H4，“病假”），事假函數的使用：COUNTIF（C3∶H4，“事假”）。

五、保存和打開宏文件

（1）保存宏文件。

Excel如何保存宏文件和2003版本稍有不同。

方法：選擇另存為—啟用宏的Excel工作簿—文件名—確定。

（2）打開宏文件。我們都知道宏是一段程序代碼，可以把一系列的操作自動連續完成。但由于宏是程序，容易被人用來寫入惡意代碼，成為有破壞性的病毒，所以excel默認是不允許打開宏的，在打開有宏的文檔時就會受到提醒。通常打開時是將安全級別降到最低。endprint

（3）Excel 2003設置方法：

a.菜單—工具—宏—宏安全性

b.菜單—工具—選項—安全性—宏安全性

（4）Excel 2007設置方法：

Office按鈕—Excel選項—信任中心—信任中心設置—宏設置—啟用所有宏。

完成設置后，啟動帶宏的Excel表格再不會有提示了，最好在使用這種方法打開前，用殺毒軟件進行檢查，確保文件沒有包含惡意的宏代碼。

六、Excel在教學統計中的應用

學生成績統計是教師經常要進行的工作，如果手工計算，不但效率低，工作量還大。如果利用Excel函數功能可以很好地解決教學工作中的各種問題。我們最常使用的函數除了SUM、SUMIF、AVERAGE、MAX、MIN之外，還有IF、COUNT、DCOUNT等。

在這里，主要介紹運用不同的函數如何根據分數來求出優、良、中、差所對應的A、B、C、D等級的方法。

（1）IF函數快速求出成績的等級。

首先，我們要了解Excel里IF函數的參數和返回值的用法，第一個參數是條件，第二個參數是條件為真的返回值，第三個參數是條件為假的返回值；這樣根據問題就可以用相應的公式來解決。例如：在成績等級評價中，成績在60分以下為“不合格”，設為D；60～80為“合格”，設為C；80～90為“良”，設為B；90～100為“優秀”，設為A。

上面的問題可以用下面的公式=IF（C2<60，“D”，IF（C2<80，“C”，IF（C2<90，“B”，“A”））），函數參數如圖1所示。

得到所有學生的考試結果。效果如圖2所示。

在IF函數運算中先判斷最外層的條件，如果判斷C2<60為假，在第二層條件繼續判斷，如果還為假再進入第三層IF語句，依次判斷，直到符合條件。

（2）CHOOSE函數快速求出成績的等級。

在Excel中，IF函數在Excel 2003版本中，最多允許7層嵌套，那么遇到嵌套過多的情況可以用Excel中的另一個函數解決，那就是CHOOSE函數。

下面我們來看看如何使用CHOOSE函數快速求出成績的等級來。

具體操作方法：選中D2單元格，在“函數參數”中輸入以下公式：

=CHOOSE（IF（D2>=90，1，IF（D2>=80，2，IF（D2>=60，3，4）））），“A”，“B”，“C”，“D”）

如果Index_num為1，函數CHOOSE返回Value1；如果Index_num為2，函數CHOOSE返回Value2；如果Index_num為3，函數CHOOSE返回Value3；依次類推。

CHOOSE函數與IF函數的區別是：

IF（）是判斷第一個參數的邏輯值（是/否，只有這兩個結果，第一個參數可以是計算式），“是”返回第二參數（或計算式的值），“否”返回第三個參數（或計算式的值）。

CHOOSE（）是按照序號（第一個參數）從一個數列（后面一串以逗號分隔的內容或單元格）中讀取出對應的那個數。endprint