試論Excel函數(shù)在學(xué)生成績(jī)表中的應(yīng)用

陳淑娟

摘要：Excel函數(shù)在生活中的用處很廣，在學(xué)校里，接觸最多的就是學(xué)生的成績(jī)，我們用Excel函數(shù)也可以輕松的統(tǒng)計(jì)學(xué)生成績(jī)，給工作帶來極大的方便。

關(guān)鍵詞：Excel函數(shù)；成績(jī)表

中圖分類號(hào)：TP37 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2016）32-0220-03

Excel函數(shù)在Excel電子表格中是很重要的一部分內(nèi)容，也是Excel使用方便的關(guān)鍵所在。Excel函數(shù)的使用滲透于生活的方方面面，本文就Excel函數(shù)在學(xué)生成績(jī)表中的應(yīng)用做簡(jiǎn)單的舉例說明。我們?cè)谑褂肊xcel函數(shù)之前，首先要把學(xué)生成績(jī)的原始數(shù)據(jù)輸入到Excel電子表格中。下面我們就對(duì)成績(jī)表中常用的幾個(gè)函數(shù)做一一說明。

1 用Sum函數(shù)統(tǒng)計(jì)每個(gè)學(xué)生的總成績(jī)

Sum函數(shù)的語法為：sum（number1，number2……） 其功能是計(jì)算所有參數(shù)數(shù)值的和。number1，number2……代表需要計(jì)算的值或引用的單元格（區(qū)域），它們可以是連續(xù)的單元格區(qū)域，也可以是不連續(xù)的。例如圖1所示，我們要在G3單元格中計(jì)算張民同學(xué)的總分，先選中G3單元格，然后再單擊fx插入sum函數(shù)，出現(xiàn)“函數(shù)參數(shù)”對(duì)話框，可以直接在數(shù)值1框中輸入D3：F3；也可以用鼠標(biāo)在表格中拖動(dòng)選擇D3：F3單元格區(qū)域，然后按Enter鍵，總分就計(jì)算出來了。然后利用Excel的自動(dòng)填充功能向下拖動(dòng)鼠標(biāo)，這列的總分就全算出來了。我們這里計(jì)算的”D3：F3”是一個(gè)連續(xù)的單元格區(qū)域，Sum函數(shù)還可以對(duì)不連續(xù)的單元格區(qū)域求和。只要在出現(xiàn)的“函數(shù)參數(shù)”對(duì)話框中的數(shù)值1輸入框中輸入一個(gè)連續(xù)的區(qū)域，數(shù)值2輸入框中再輸入另外一個(gè)連續(xù)的區(qū)域，數(shù)值1和數(shù)值2之間可以是不連續(xù)的。如果有更多的不連續(xù)區(qū)域求和，將鼠標(biāo)定位在數(shù)值2后面的輸入框中，函數(shù)會(huì)自動(dòng)出現(xiàn)數(shù)值3輸入框。用同樣的辦法可以出現(xiàn)數(shù)值4等更多的數(shù)值輸入框讓我們進(jìn)行輸入。當(dāng)然我們也可以直接在函數(shù)表達(dá)式中輸入這些不連續(xù)的區(qū)域，區(qū)域之間用逗號(hào)分隔。大部分人在用Excel函數(shù)求和的時(shí)候，只知道用∑自動(dòng)求和或用sum函數(shù)對(duì)連續(xù)的單元格區(qū)域求和，卻不知道不連續(xù)的單元格的和也是可以求的。

2 用Average函數(shù)計(jì)算學(xué)生的平均成績(jī)

Average函數(shù)的語法為：Average（number1，number2……） 其功能是計(jì)算所有參數(shù)數(shù)值的平均值。其用法和sum函數(shù)類似，也是先選中單元格，再插入Average函數(shù)，然后再選擇要計(jì)算的單元格區(qū)域，之后回車。這里不同的就是平均分算出來有時(shí)小數(shù)點(diǎn)后面的位數(shù)會(huì)很多，這時(shí)可以單擊工具欄上的增加或減少小數(shù)位數(shù)按鈕來實(shí)現(xiàn)；也可以打開“設(shè)置單元格格式”對(duì)話框在里面的”數(shù)字”選項(xiàng)卡中設(shè)置。

3 用Rank函數(shù)統(tǒng)計(jì)學(xué)生的名次

Rank函數(shù)的語法為：Rank（Number，ref，order）， 主要功能是返回某一數(shù)值在一列數(shù)值中相對(duì)于其他數(shù)值的排位。其中”Number”表示需要排序的數(shù)值（單元格地址）；”ref”表示排序數(shù)值所處的單元格區(qū)域；”order”表示排序方式參數(shù)?！監(jiān)rder”參數(shù)值如果為“0”或者省略，則按降序排名，如果為非“0”值，則按升序排名。如圖2，假定根據(jù)總分排出名次，在I3單元格中輸入=Rank（G3，$G$3：$G$11），日常生活中我們的名次一般是按總分的降序排列的，所以這里的第三個(gè)參數(shù)我們省略，當(dāng)然它也可以寫為“0”。這里的第二個(gè)參數(shù)“$G$3：$G$11”用了絕對(duì)引用符“$”，因?yàn)樵谒愠龅谝粋€(gè)學(xué)生的成績(jī)后，鼠標(biāo)在向下拖動(dòng)填充計(jì)算其他同學(xué)的名次時(shí)，如果不加這個(gè)絕對(duì)引用符，名次列就會(huì)有很多重復(fù)名次出現(xiàn)。這是因?yàn)槲覀冊(cè)谂琶螘r(shí)第二個(gè)參數(shù)“ref”的單元格地址區(qū)域在鼠標(biāo)向下拖動(dòng)時(shí)改變了，也就是到I4單元格時(shí)第二個(gè)參數(shù)”G3：G11”就變成了”G4：G12”，到下面的單元格時(shí)會(huì)依次類推，這樣就改變了排序范圍。而其實(shí)我們需要排序的單元格區(qū)域一直都是G3：G11，所以要加上絕對(duì)引用符，結(jié)果才會(huì)正確。這就是很多同學(xué)在這里出錯(cuò)的原因。

4 用If函數(shù)判斷成績(jī)等級(jí)

If函數(shù)的語法為if（Logical_test，Value_if_true，Value_if_false）。它是根據(jù)指定條件判斷真假，返回不同結(jié)果的函數(shù)。假如我們均分在60分及60分以上的同學(xué)為“及格”，60分以下的成績(jī)?yōu)椤安患案瘛薄Ｄ敲次覀兛梢栽趫D3的I3單元格中輸入：=if（H3>=60，”及格”，”不及格”）。這是最簡(jiǎn)單的if語句，if函數(shù)還可以嵌套if函數(shù)，最多可嵌套7層。我們就可以用if函數(shù)的嵌套來完成學(xué)生成績(jī)等級(jí)的判定。假如我們規(guī)定90分以上為“優(yōu)秀”；80～90為“良好”；70～80為“中等”；60～70為“及格”；60分以下為“不及格”。那么要判斷均分H3的等級(jí)，其if語句如圖3所示=IF（H3>=90，"優(yōu)秀"，IF（H3>=80，"良好"，IF（H3>=70，"中等"，IF（H3>=60，"及格"，"不及格"））））。關(guān)于這樣的if語句用如圖4的樹形結(jié)構(gòu)圖來表示其嵌套關(guān)系就更明了了。

5 用COUNTIF （）條件計(jì)數(shù)函數(shù)和FREQUENCY（）頻率分布函數(shù)統(tǒng)計(jì)各分?jǐn)?shù)段的學(xué)生人數(shù)

1）COUNTIF（）函數(shù)是統(tǒng)計(jì)某個(gè)單元格區(qū)域中符合指定條件的單元格數(shù)目；有兩個(gè)參數(shù)，第一個(gè)參數(shù)代表要統(tǒng)計(jì)的單元格區(qū)域，第二個(gè)參數(shù)表示指定的條件表達(dá)式。如圖5，如果要統(tǒng)計(jì)平均分在90～100分的人數(shù)可以用函數(shù)表達(dá)式：=COUNTIF（H3：H11，">=90"）

如果要計(jì)算80～90分之間的人數(shù)我們可以用80分以上的人數(shù)減去90分以上的人數(shù)，在編輯欄中輸入=COUNTIF（H3：H11，">=80"）-H12就可以完成。其余分?jǐn)?shù)段的人數(shù)用類似的方法得出。

圖 5

2）Frequency （data_array，bins_array）函數(shù)是一個(gè)頻率分布函數(shù)，它具有統(tǒng)計(jì)各區(qū)間的頻數(shù)的功能，它也有兩個(gè)參數(shù)，第一個(gè)參數(shù)是要進(jìn)行統(tǒng)計(jì)的數(shù)據(jù)，第二個(gè)參數(shù)是分組的依據(jù)，也就是分段的界值。該函數(shù)返回的是數(shù)組，必須以數(shù)組公式的形式進(jìn)行輸入。所以在輸入時(shí)首先要選中輸出結(jié)果的一串區(qū)域，并不是某一個(gè)單元格（這往往是同學(xué)們?nèi)菀壮鲥e(cuò)的地方），在編輯欄輸入完公式后按下組合鍵Ctrl+Shift+Enter，使之成為數(shù)組公式（公式會(huì)自動(dòng)加上花括號(hào)，不需要手動(dòng)輸入）。假如我們要求0～59，59～69，69～79，79～89，89以上這五個(gè)分?jǐn)?shù)段的人數(shù)，那么需要的4個(gè)界值分別為59，69，79，89；。如圖6，我們先把“59，69，79，89”四個(gè)界值放在H4：H7的單元格區(qū)域，四個(gè)界值代表五個(gè)分?jǐn)?shù)段，所以我們要選中五個(gè)單元格區(qū)域I4：I8，然后再插入函數(shù)，在出現(xiàn)的參數(shù)框中輸入相應(yīng)的區(qū)域，最后按下Ctrl+Shift+Enter組合鍵，使之成為數(shù)組公式，公式會(huì)自動(dòng)加上花括號(hào)，這樣函數(shù)才能用對(duì)。很多同學(xué)在完成之后直接輸入Enter鍵，函數(shù)肯定是會(huì)出錯(cuò)的。對(duì)于H4：H7我們也可直接輸入為59，69，79，89這四個(gè)數(shù)值，不過在直接輸數(shù)值的時(shí)候我們要在數(shù)值的兩邊加上花括號(hào)（這和組合鍵生成數(shù)組的花括號(hào)是兩碼事），如圖7函數(shù)語句我們可以這樣輸入=FREQUENCY（E3：E12，{59，69，79，89}），然后按下組合鍵Ctrl+Shift+Enter，公式會(huì)自動(dòng)變成{=FREQUENCY（E3：E12，{59，69，79，89}）}。

FREQUENCY （）函數(shù)比COUNTIF （）函數(shù)難理解，但是用的熟練了，F(xiàn)REQUENCY （）函數(shù)比COUNTIF（）函數(shù)計(jì)算起來更快，更方便?？赡苓@里有人說實(shí)際上我們一般算的是X<60，60<=x<70，70<=X<80，80<=X<90，X>=90這樣的等級(jí)區(qū)域，而X<=59和X<60還是有區(qū)別的。是的，但是如果寫成60，70，80，90，各個(gè)分?jǐn)?shù)段又包含了60等這幾個(gè)臨界值，如果要統(tǒng)計(jì)不及格的人數(shù)就連60分的人也包含在內(nèi)了，就更不符合實(shí)際了。所以為了更貼合實(shí)際一點(diǎn)，我們可以把臨界值設(shè)置為59.99，69.99，79.99，89.99，因?yàn)槌煽?jī)分?jǐn)?shù)一般最多都是保留到兩位小數(shù)的。

6 結(jié)語

利用上面這些函數(shù)我們可以方便的統(tǒng)計(jì)一個(gè)成績(jī)表，只要學(xué)生的原始成績(jī)要更改，這些用函數(shù)算出來的值也會(huì)相應(yīng)的變化，工作起來是很方便的，學(xué)生慢慢地掌握這些函數(shù)，在以后的生活中也是大有用處的。這里需要特別說明的是：Excel函數(shù)使用中用到的標(biāo)點(diǎn)都應(yīng)輸入英文標(biāo)點(diǎn)，要不然函數(shù)就會(huì)提示出錯(cuò)。

參考文獻(xiàn)：

[1] 黃國(guó)興，周南岳.計(jì)算機(jī)應(yīng)用基礎(chǔ)[M].高等教育出版社，2012.3

[2] 吳亞坤，王三虎.大學(xué)計(jì)算機(jī)基礎(chǔ)[M].遼寧大學(xué)出版社，2013.8

[3] 王宇川，劉繼華，郭思延.計(jì)算機(jī)應(yīng)用基礎(chǔ)（上）案例教程[M].中國(guó)商業(yè)出版社，2010.1.

[4] 雋青龍，王華容.辦公高手Excel 2007 案例導(dǎo)航[M].中國(guó)水利水電出版社，2009.4.