《計(jì)算機(jī)科學(xué)與技術(shù)、計(jì)算機(jī)技術(shù)、軟件工程》

教材版本高等教育出版社（2020年1（）月第1版）課題名稱統(tǒng)計(jì)分析學(xué)生成績(jī)

高等職業(yè)教育計(jì)算機(jī)類課程新形態(tài)一體化教材計(jì)算機(jī)應(yīng)用基礎(chǔ)項(xiàng)目化教程

課題來源

（Windows10+Office2016）項(xiàng)目10（節(jié)選）（頁(yè)碼:P186-190）

?^目10統(tǒng)計(jì)分析學(xué)生成績(jī)

（PP1項(xiàng)目10

項(xiàng)目分析

.統(tǒng)計(jì)分析學(xué)生成績(jī)

1.項(xiàng)目情景

學(xué)期結(jié)束，王老師要在學(xué)生成績(jī)表中進(jìn)行成績(jī)統(tǒng)計(jì)分析。學(xué)生成績(jī)表的部分學(xué)生

成績(jī)?nèi)鐖D10-1所示（索材文件：項(xiàng)目10\統(tǒng)計(jì)分析學(xué)生成績(jī).xlsx）,要計(jì)算各位同

r~0學(xué)的總分、平均分、名次和獎(jiǎng)學(xué)金，統(tǒng)計(jì)如圖10-2所示的成績(jī)分析表。

素材文件

Ii

W10-1學(xué)生成績(jī)表

2.項(xiàng)目要求

根據(jù)統(tǒng)計(jì)表要求，需要進(jìn)行如下操作：

①計(jì)算每位同學(xué)的總分和平均分。

②根據(jù)總分計(jì)算學(xué)生的名次。

③根據(jù)名次計(jì)算學(xué)生的獎(jiǎng)學(xué)金，一等獎(jiǎng)1名，二等獎(jiǎng)2名，三等獎(jiǎng)3名。

④計(jì)算各門課程的最高分、最低分、平均分。

⑤統(tǒng)計(jì)各門課程各分?jǐn)?shù)段的人數(shù)。

⑥將表中的公式轉(zhuǎn)換為值。

⑦打印成績(jī)表,要求每頁(yè)顯示列標(biāo)題和頁(yè)碼。

3.解決方案

Excel提供了強(qiáng)大的公式和函數(shù)功能，用于對(duì)表格中的數(shù)據(jù)進(jìn)行計(jì)算和處理。在

186

項(xiàng)目10統(tǒng)計(jì)分析學(xué)生成績(jī)

使用公式計(jì)算表格數(shù)據(jù)時(shí)，可以靈活使用單元格引用方式和自動(dòng)填充功能快速填充一

組相似的公式，也可以將多個(gè)函數(shù)嵌套使用，以達(dá)到最佳效果。打印工作表時(shí)可以用

Excel提供的打印設(shè)置功能進(jìn)行個(gè)性化的打印設(shè)置，包含打印紙張大小、紙張方向、

頁(yè)邊距、打印區(qū)域、打印標(biāo)題、頁(yè)周和頁(yè)腳等。

預(yù)備知識(shí),

要更好地使用Excel提供的公式和函數(shù)功能，首先應(yīng)該認(rèn)識(shí)公式和函數(shù)，了解公

式和函數(shù)的使用方法，掌握單元格引用等預(yù)備知識(shí)。

'一、認(rèn)識(shí)公式和函數(shù)

1.公式的組成

[Excel中的公式以等號(hào)（=）開頭，后面是參與計(jì)算的元素（運(yùn)算數(shù)）和運(yùn)算檢課10-1

認(rèn)識(shí)公式和函數(shù)

符，運(yùn)算數(shù)可以是常盤、單元格或區(qū)域的引用、名稱、函數(shù)等。

如圖10-3所示公式的意義是：求以A2單元格

運(yùn)算符.和入

的值為半徑的削的面積，即用巴函數(shù)求出阿周率乘

-PI（rA2*2--------常，

以A2的2次方?！狪I一引用

函數(shù)

2.函數(shù)的組成

圖10?3公式的組成

IExcel函數(shù)是一些預(yù)定義的命名公式，使用時(shí)必f筆記

須被包含在公式中。它使用一些稱為參數(shù)的特定數(shù)

據(jù)，按特定順序或結(jié)構(gòu)來執(zhí)行計(jì)算和分析數(shù)據(jù)。Excel函數(shù)通常由函數(shù)名稱、左括號(hào)、

參數(shù)列表和右括號(hào)構(gòu)成。

函數(shù)名稱不區(qū)分大小寫，用于確定函數(shù)的功能和用途，如求和函數(shù)SUM。

參數(shù)可以是數(shù)字、文本、邏輯值、數(shù)組、錯(cuò)誤值或單元格引用，也可以是公式或

其他函數(shù)。多個(gè)參數(shù)之間用英文設(shè)號(hào)（,）分隔。

3.運(yùn)算符及優(yōu)先順序

■運(yùn)算符是對(duì)公式中的元素進(jìn)行特定類型運(yùn)算的符號(hào)。Excel中包含引用運(yùn)算符、

算術(shù)運(yùn)算符、比較運(yùn)算符和文本運(yùn)算符4類運(yùn)算符。

①引用運(yùn)算符：用于對(duì)單元格區(qū)域進(jìn)行合并計(jì)算。

②算術(shù)運(yùn)算符：用來完成算術(shù)運(yùn)算，如加、城、乘、除、乘塞等。

③比較運(yùn)算符：用于比較數(shù)據(jù)大小，包括數(shù)值和文本的比較。運(yùn)算結(jié)果為邏軾

依“TRUE（X）M或“FALSE（假）”。

；④文本運(yùn)算符：用于連接多個(gè)文本。例如，Excel2016"&"應(yīng)用技術(shù)"”的

結(jié)果為“Excel2016應(yīng)用技術(shù)”。

■如果公式中同時(shí)用到多個(gè)運(yùn)算符，Excel將按運(yùn)亢符的優(yōu)先順序進(jìn)行運(yùn)算，相同

:優(yōu)先級(jí)的運(yùn)算符從左到右進(jìn)行運(yùn)算。運(yùn)算符及優(yōu)先順序見表10T。

187

計(jì)算機(jī)應(yīng)用基礎(chǔ)項(xiàng)目化教程（Windows10+Office2016）

表10-1運(yùn)算符及優(yōu)先順序

優(yōu)先級(jí)

運(yùn)算符_____________說明1示例

:和，引用運(yùn)算符1=SUM(AI：A5,A8)

-算術(shù)運(yùn)算符：負(fù)號(hào)2=3?-5

%算術(shù)運(yùn)算符：百分比3=80*5%

A算術(shù)運(yùn)算符：乘耗4=3找

*和/算術(shù)運(yùn)算符：乘和除5=3*10/5

+和-算術(shù)運(yùn)算符：加和減6=3+2-5

&文本運(yùn)算符：文本連接符7="Excel"&"2016"

=、＜＞、＜、＞比較運(yùn)算符：等于、不等于、小于、大于、=A1=A2

8

＜=、＞=小于或等于、大于或等于=B2on9J"

............O提示：

............:若要更改公式的計(jì)算順序，可以膈公式中需要先計(jì)算的部分包含在括號(hào)中。例

如，公式“=5+2*3”將2與3相乘，然后再加上5,結(jié)果為11。如果用括號(hào)將該

公式更改為“=（5+2）*3"，則先求出5加2之和，再用結(jié)果乘以3得21。

4.Excel中的常用函數(shù)

Excel針對(duì)不同的數(shù)據(jù)類型提供了不同的函數(shù)，表10-2列出了常用的函數(shù)類型和

使用范例。

表10-2常用的函數(shù)類型和使用范例

］函數(shù)類型

______函數(shù).使用范例

SUM（求和）、AVERAGE（求平均

=AVERAGE（E2：I2）

常用值）、MAX（求最大值）、MIN（求最

計(jì)算F2：F7單元格區(qū)域中數(shù)字的平均值

小值）、COUNT（數(shù)值計(jì)數(shù)）等

DB（求資產(chǎn)的折舊值）、1RR（求=PMT（0.45%,120,100000）

財(cái)務(wù)現(xiàn)金流的內(nèi)部報(bào)酬率）、PMT（求周計(jì)算月利率為0.45%時(shí)，100000元貸款

定利率下貸款的分期償還額）等分120個(gè)月還清，每個(gè)月的還款額

YEAR（求年份）、MONTH（求月

份）、DAY（求天數(shù)）、TODAY（返回=YEAR(2O19-12-3I)

日期與時(shí)間

當(dāng)前13期）、NO%（返回當(dāng)前時(shí)計(jì)算2019年12月31日的年份為2019

間）等

ABS（求絕對(duì)值）、INT（求整數(shù)）、

=ROUND(1234.567,2)

數(shù)學(xué)與ROUND（求四舍五人）、SQRT（求平

把1234.567保留兩位小數(shù)，結(jié)果

三角方根）、RANDBETWKEN（求隨機(jī)

為

數(shù)）等1234.57

RANK（求大小排名）、COUNTIR=COUNTJFS（H3：HB,"＞=9O",C3：C13,

（統(tǒng)計(jì)單元格區(qū)域中符合多個(gè)條件的"男"）

統(tǒng)計(jì)

單元格數(shù)）、COUNTBUNK（求空單求H3：H13中數(shù)據(jù)大于等于90,且C3：

元格數(shù)）、SUMIFS（多條件求和）等CI3中為“男”的行數(shù)

188

項(xiàng)目10統(tǒng)計(jì)分析學(xué)生成績(jī)

續(xù)表

由數(shù)類。函數(shù)1例JII柩例

=IF（A3>=50,"及格"J不及格”）

AND（與）、OR（或）、NOT（非）、

邏輯判斷A3是有大于等于60,咫就返回

FALSE（ft）.TRUE?）.IF（如果）

“及格”，否則返問“不及格”

LEFT（求左子中）、RIGHT（求右

81）、MID（求產(chǎn)甲）、LEN（求字=LEN（"計(jì)算機(jī)Ml便礎(chǔ)”）

文本

符申長(zhǎng)度）EXACT（求兩個(gè)字符申是計(jì)算文本長(zhǎng)度，結(jié)果為7

否相問）等

ISBIANK（判斷是否為空單元格）、

=ISEVEN（G4）

信息ISKVEN（判斷處否為刎?cái)?shù)）、

判斷G4小元格的值是否為儡數(shù)

ISERROR（棄斷是否為胞值）等

ROW（求行序號(hào)）、COLUMN（求

杳我與列序號(hào)）、YLOOKUP（在表X城首列=R（）W（）

引用搜索滿足條件的小元格，返回指定列求當(dāng)曲單元格的行序號(hào)

的版）等

?二、認(rèn)識(shí)單元格引用

單元格引用用來指明公式中所使用的數(shù)據(jù)的位置，它可以是一個(gè)單元格地址，也

可以是單元格區(qū)域。通過朱元格引用，可以在一個(gè)公式中引用工作表中的不同數(shù)據(jù),微課10-2

認(rèn)識(shí)單元格

或不在多個(gè)公式中引用一個(gè)單元格中的數(shù)據(jù)，還可以引用同一個(gè)工作簿不同工作表中

引用

的數(shù)據(jù)。當(dāng)公式中引用的單元格數(shù)依發(fā)生變化時(shí)，公式的計(jì)算結(jié)果也會(huì)自動(dòng)更新。

1.單元格和區(qū)域地址

Excel使用字母標(biāo)識(shí)列，使用數(shù)字標(biāo)識(shí)行，這些字母稱為列標(biāo)，數(shù)字稱為行號(hào)。

引用單元格時(shí)使用列標(biāo)加行號(hào),如A3；引用單元格區(qū)域時(shí)，使用引用運(yùn)算符連接單

元格區(qū)域的起始單.無格地址和結(jié)束小元格地址。各種引用示例及含義見表10-3。

*10-3單元格和區(qū)域引用示例

中川示例01用位置川川示例川川仔1

A列中第10行的單元格5：5第5行巾的金那小元格

八列中第10行~第20行的小第5行~第10行中的全部單

AI0：A205：10

元格區(qū)域元格

?列~卜：列中笊”行的革元格

1）15：EI5H：HH列中的全部單元格

區(qū)域

八列~卜：列巾第10行~第20

AI0：E20H：J1列”列中的全部單元格

行的小元格區(qū)城

如果要引用不同的T.作簿或T.作表巾的數(shù)據(jù)，需要在單元格或區(qū)域引用前面加上工

滿名稱和工作表名稱,引用格式為“〔「?作簿名］T.作表標(biāo)簽！單元格或區(qū)域引用稱

189

計(jì)算機(jī)應(yīng)用基礎(chǔ)項(xiàng)目化教程(Windows10+Office2016)

2?相對(duì)引用和絕對(duì)引用

(..Excel提供了相對(duì)引用，絕對(duì)引用和混合引用3種引用方式，使用時(shí)要注意這3

筆記種引用方式的聯(lián)系和區(qū)別，合理應(yīng)用相對(duì)引用和絕對(duì)引用可以提高編輯公式的效率和

.............準(zhǔn)確率。

(1)相對(duì)引用

相對(duì)單元格引用是指包含公式的單元格與被引用單元格之間的相對(duì)位置。相對(duì)引用

.............宜接用列標(biāo)和行號(hào)表示單元格地址，如A1。在移動(dòng)或復(fù)制公式時(shí)，系統(tǒng)