Excel中幾個常用函數的教學探討

摘要:在工作和學習中用電子表格來處理數據即迅速又方便，雖然Excel的學習很簡單，不過真正能掌握并用好Excel的人并不多，很多學生都停留在求和、求平均值等層面，該文將與你一起探討平常教學中一些非常實用的技巧，掌握這些技巧將大大提高你的學習和工作效率。

關鍵詞:常用函數;教學探討

中圖分類號:TP311 文獻標識碼:A 文章編號:1009-3044(2009)33-9292-02

Excel in the Teaching of Several Commonly Used Functions

ZHANG Lian-fang

(Dagang Vocational Education Center at Lianyungang，Lianyungang 222042，China)

Abstract: In the working and learning using spreadsheets to handle data that is quick and convenient， while Excel is very simple to learn， but to really grasp and make good use of Excel， not many people， many students are stuck in sum， an average dimensions， This article will explore with you some of the most common teaching of practical skills， to master these skills will greatly enhance your learning and working efficiency.

Key words: common functions; teaching

我們生活中經常要接觸到各式各樣的數據表，比如學生成績表、教師工資表、企業報表、商店銷售表等，那么我們如何快速地求出這些數據的總和、平均值、優分率、獎學金等等。利用Excel的公式和函數這些問題將會迎忍而解。Excel是微軟辦公套裝軟件Office的一個重要的組成部分，依靠Excel進行數據管理不僅能夠方便的處理表格和進行圖形分析，其更強大的功能體現在對數據的自動處理和計算方面。函數作為Excel處理數據的一個重要手段，功能是十分強大的，所謂函數是預先定義好的，執行計算、分析等處理數據任務的特殊公式。在生活和工作實踐中可以有多種應用，如給定一張成績表要求完成如下操作:

1) 根據幾門課成績求學生的平均分;

2) 根據平均分給學生寫出總評;

3) 根據總評計算學生的獎學金;

4) 計算出每門課的優分率等等。

從以上幾個問題可以看出，要解決本題，至少需要掌握四個函數:求平均值函數AVERAGE、邏輯函數IF、統計函數COUNT和COUNTIF。本文結合本人在教學及日常學生管理中的工作，專門對幾個常用函數進行探討。

1 AVERAGE平均值函數

格式:AVERAGE(數值1，數值2，……);其功能是返回參數的算術平均值。

說明:參數應是數值、名字、數組或包含數的單元格的引用;

注意點1:嚴格區分空白單元格和包含零的單元格。例如:若A1:A5單元格中的內容為2，0，5，0，8，則函數=AVERAGE(A1:A5)的返回值為3，若A1:A5單元格中的內容為:2，空白，5，空白，8，則函數= AVERAGE(A1:A5)的返回值為5。

注意點2:如何跨表求平均值。使用AVERAGE函數在同一工作表中求平均值比較簡單，如果需要對不同工作表的多個區域進行求平均值，可以采用以下方法:選中 “插入”→“fx函數”對話框中的函數，“確定”后打開“函數參數”對話框。切換至第一個工作表，鼠標單擊“number1”框后選中需要求平均值的區域。如果同一工作表中的其他區域需要參與計算，可以單擊“number2”框，再次選中工作表中要計算的其他區域。上述操作完成后切換至第二個工作表，重復上述操作即可完成輸入，“確定”后公式所在單元格將顯示計算結果。例如sheet1、sheet2和sheet3三張工作表存放三個班學生的成績，則要計算三個班的“計算機網絡”總平均分的計算公式為“=AVERAGE(sheet1!E2:E31，sheet2!E2:E31，sheet3!E2:E31)”(假設每個班有30人)。

2 IF邏輯函數

格式:IF(測試的邏輯值或表達式，條件為真時應返回的值，條件為假時應返回的值)。

功能:按條件測試的真或假返回不同的值。

注意:“測試的邏輯值或表達式”是任何可以計算為邏輯值(真/假)的值或表達式;可以在IF函數中嵌套IF函數(最多為七層)。所謂嵌套函數，就是指在某些情況下，您可能需要將某函數作為另一函數的參數使用。例如，下面的公式就是AVERAGE 函數和SUM函數嵌套在IF函數中，“=IF(AVERAGE(G2:G12)>60，SUM(G2:G12)，0)”。

值得注意的是當嵌套函數作為參數使用時，它返回的數值類型必須與已知參數的數值類型相同。例如，如果參數需要一個 TRUE 或 FALSE 值，那么嵌套函數也必須返回一個 TRUE 或 FALSE 值。否則，Excel將顯示 #VALUE! 錯誤值。

3 COUNTIF統計函數

格式:COUNTIF(range，criteria)，它的功能是計算某個區域中滿足給定條件的單元格數目，其中第一個參數range為統計的范圍，一般最好用絕對地址來引用;第二個參數criteria是以數字、表達式或文本形式定義的條件，甚至可以使用統配符。其中數字可以直接寫入，表達式和文本必須加引號。比如我們要求出平均分為88分的人數則=COUNTIF(G2:G12，88);如要求出平均分在80分以上的學生數，則= COUNTIF(G2:G12，\">80\");如要求出所有學生中姓“王”的學生數，則=COUNTIF(B2:B12，\"王*\")。

4 COUNT函數

格式:COUNT(value1，value2，…)，它的功能是返回參數組中數字的個數。其需要說明的是，計數時，函數把數、空白、邏輯值、日期或以文字代表的數計算進去;但忽略錯誤值以及其它無法轉換成數字的文字。若參數是一個數組或引用，則只有在數組或引用中的數字才被計數;數組或引用中的空白單元格、邏輯值、文字、錯誤值都將被忽略。

假設在工作表sheet1中已建立如右圖所示的表格:

試解決以下幾個問題:

1) 用函數計算每個學生的平均分。

2) 根據平均分為每個學生寫出總評:平均分在85分以上為“優秀”，85分至75分為“良好”，75分至60分為“及格”，60分以下為“不及格”。

3) 根據各學生的總評計算獎學金:“優秀”為500元，“良好”為200元，其余都沒有獎學金(其對應單元格為空)。

4) 計算每門課的優分率。

對于上述第1)個問題，解決的方法很簡單利用AVERAGE函數，直接在G2單元格中輸入“=AVERAGE(B2:F2)”確認即可，然后拖動填充柄至G12單元格即可完成操作。對于第2)和第3)個問題，則需使用IF(test，value1，value2)函數。其中第一個參數test為一個邏輯值或表達式，不能加引號;第二個參數value1為條件成立時的結果，如果是顯示某個值，則要加引號;第三個參數value2為條件不成立時的結果，如果是顯示某個值，同樣要加引號，但是對于第3)個問題若沒有獎學金，則其相應單元格為空，則該值應為空串(””)。另外，該函數可以嵌套，即在第二個或第三個參數處可以再寫一個IF函數。比如在求“總評”時，根據條件首先判斷其“平均分”是否大于85，如果條件成立，則返回“總評”為“優秀”，否則(即“平均分”小于或等于85)進入第二層嵌套，判斷“平均分”是否大于75，如果條件成立，則“總評”為“良好”，否則又進入第三層嵌套。依次類推，直至能根據條件表達式的值直接求出結果。

因此，為了得到“總評”列所要的結果，可以在H2單元格中輸入公式:=IF(G2>85，\"優秀\"，IF(G2>75，\"良好\"，IF(G2>=60，\"及格\"，\"不及格\")))，然后，利用自動填充柄將其復制到下方的單元格。

同理，為了得到“獎學金”列所要的結果，可以在H2單元格中輸入公式:=IF(H2=\"優秀\"，500，IF(H2=\"良好\"，200，\"\"))，然后，利用自動填充柄將其復制到下方的單元格。

從上述兩個問題可以看出，IF函數在使用時關鍵是掌握該函數的格式、功能、各參數的含義，特別是當參數中含有嵌套時，內層IF函數的格式，參數一定要正確。

對于第4)個問題，首先要理解“優分率”的概念，即85分以上人數占總人數的比例，這里就涉及到兩個函數，一個是統計85以上人數的COUNTIF函數，另一個是求總人數的COUNT函數。因此，可以在“優分率”單元格B13中輸入=COUNTIF(B2:B12，\">=85\")/COUNT(B2:B12)，確認后利用自動填充柄將其拖動至右方單元格。

通過以上的案例，我們可以看出Excel在數據處理方面，確實是獨點鰲頭，利用其提供的豐富的函數，不需掌握很深的數學計算方法，不需了解具體的求解技術細節，更不需編寫程序，而只要正確地選擇適當的參數，即可完成復雜的求解過程，得到相應的分析結果和完整的求解報告。

參考文獻:

[1] 石河成.計算機操作與使用[M].北京:電子工業出版社，2007.

[2] 馬成榮.計算機應用基礎[M].江蘇:江蘇科學技術出版社，2009.

[3] 柳青.計算機應用基礎[M].北京:高等教育出版社，2006.