《計(jì)算機(jī)應(yīng)用案例教程》課件第5章

學(xué)習(xí)情境五電子表格Excel2003實(shí)訓(xùn)項(xiàng)目1表格的建立及格式化 實(shí)訓(xùn)項(xiàng)目2表格的計(jì)算及圖表的制作 實(shí)訓(xùn)項(xiàng)目3Excel的數(shù)據(jù)庫(kù)管理及統(tǒng)計(jì)功能實(shí)訓(xùn)項(xiàng)目4綜合應(yīng)用Excel處理數(shù)據(jù)

通過(guò)本章學(xué)習(xí)，應(yīng)掌握電子表格Excel2003的建立、計(jì)算、圖表和數(shù)據(jù)統(tǒng)計(jì)的操作方法，主要內(nèi)容包括：

1．Excel2003的基本操作

在Excel中有很多快速輸入數(shù)據(jù)的技巧，如自動(dòng)填充、自定義序列等，熟練掌握這些技巧可以提高數(shù)據(jù)輸入速度。同時(shí)，在輸入數(shù)據(jù)時(shí)，要注意數(shù)據(jù)單元格的數(shù)據(jù)分類，如對(duì)于編號(hào)、郵編及電話號(hào)碼等數(shù)據(jù)應(yīng)該設(shè)置為文本型。

2．工作表的格式化

工作表的格式化及打印輸出則包括字體格式、數(shù)據(jù)的對(duì)齊方式、表格的邊框及底紋、行高列寬、頁(yè)面設(shè)置和打印預(yù)覽等。

3．工作表的計(jì)算

Excel最強(qiáng)大的是其計(jì)算功能，這功能主要是通過(guò)公式和函數(shù)來(lái)實(shí)現(xiàn)的。在進(jìn)行公式和函數(shù)計(jì)算時(shí)，要熟悉公式的輸入規(guī)則、函數(shù)參數(shù)的設(shè)置方法及單元格的引用方式，常用函數(shù)的使用要熟練掌握。

4．圖表的建立及格式化

圖表可以比具體的數(shù)字更直觀地說(shuō)明問(wèn)題，因此在實(shí)際應(yīng)用中，在使用表格的同時(shí)，通常還需要輔以圖表加以說(shuō)明。使用圖表向?qū)?chuàng)建圖表，然后移動(dòng)圖表位置、調(diào)整圖表大小、設(shè)置圖表區(qū)和繪圖區(qū)格式，以及進(jìn)行其他設(shè)置等格式化操作。

5．工作表的數(shù)據(jù)管理及統(tǒng)計(jì)

用戶可以使用數(shù)據(jù)排序、篩選、分類匯總、創(chuàng)建數(shù)據(jù)透視表等對(duì)表格中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析。

數(shù)據(jù)的篩選可以通過(guò)“自動(dòng)篩選”和“高級(jí)篩選”兩種方式進(jìn)行?！白詣?dòng)篩選”可用于簡(jiǎn)單條件的篩選，“高級(jí)篩選”可用于復(fù)雜條件的篩選。篩選結(jié)果使工作表中滿足設(shè)定條件的記錄顯示出來(lái)。分類匯總實(shí)際上是一種條件求和，很多統(tǒng)計(jì)類的問(wèn)題都可以用“分類匯總”來(lái)完成。在進(jìn)行分類匯總之前一定要對(duì)工作表按照分類字段進(jìn)行排序，再進(jìn)行匯總計(jì)算。

數(shù)據(jù)透視表是一種交互式工作表，用于對(duì)現(xiàn)有工作表進(jìn)行匯總和分析。在使用數(shù)據(jù)透視表時(shí)，要注意數(shù)據(jù)區(qū)域的正確選取，根據(jù)需要設(shè)定正確的布局。任務(wù)單

【實(shí)訓(xùn)目的】

(1)掌握啟動(dòng)和退出Excel的操作方法。

(2)熟悉Excel的工作界面，理解工作簿、工作表和單元格的概念。

(3)熟練掌握Excel工作簿的建立和保存方法。

(4)熟練掌握Excel工作表的編輯操作。

(5)熟練掌握格式化工作表的操作。實(shí)訓(xùn)項(xiàng)目1表格的建立及格式化

【實(shí)訓(xùn)內(nèi)容】

本例制作三個(gè)表格，分別為學(xué)生成績(jī)表、產(chǎn)品月銷售統(tǒng)計(jì)表和員工年度績(jī)效考核表。

制作學(xué)生成績(jī)表，并對(duì)表格進(jìn)行格式化。

要求：

(1)新建一個(gè)工作簿文件，將當(dāng)前的臨時(shí)工作簿文件保存在D:\EXCEL文件夾中，命名為“學(xué)生成績(jī)表.xls”。

(2)在Sheet1工作表中輸入圖5-1所示內(nèi)容。任務(wù)一制作學(xué)生成績(jī)表

圖5-1“學(xué)生成績(jī)表”原始數(shù)據(jù)

(3)將Sheet1中的表格內(nèi)容復(fù)制到Sheet2相同的區(qū)域中，將工作表Sheet2重新命名為“學(xué)生成績(jī)表”。

①設(shè)置“學(xué)生成績(jī)表”工作表單元格的對(duì)齊方式及合并單元格。將標(biāo)題行合并居中(水平居中、垂直居中)，將第2～16行數(shù)據(jù)居中。

②設(shè)置字體格式。將標(biāo)題字體設(shè)置為“隸書(shū)、加粗、深藍(lán)色、20號(hào)且加下劃線”，其他的文字字體設(shè)置為“宋體、常規(guī)、12號(hào)”。③設(shè)置數(shù)字格式。將“數(shù)學(xué)”、“英語(yǔ)”和“計(jì)算機(jī)”列數(shù)據(jù)保留1位小數(shù)。

④設(shè)置邊框、背景圖案。為學(xué)生成績(jī)表添加邊框，邊框?yàn)椤吧罴t色，外框粗線、內(nèi)框細(xì)線”。為標(biāo)題行加背景：“淺青色”；列標(biāo)頭加背景：“象牙色”。

⑤設(shè)置單元格的行高和列寬。將標(biāo)題欄的行高設(shè)置為“30”，數(shù)據(jù)部分的行高設(shè)置為“18”，將“學(xué)號(hào)”列的列寬設(shè)置為“10”，其余列的列寬設(shè)置為“8”。⑥設(shè)置條件格式。對(duì)學(xué)生成績(jī)表中數(shù)學(xué)、英語(yǔ)、計(jì)算機(jī)成績(jī)大于或等于90分的數(shù)據(jù)用藍(lán)色、加雙下劃線來(lái)標(biāo)注，低于60元的數(shù)據(jù)用紅色、加粗傾斜來(lái)標(biāo)注。

⑦插入批注。給學(xué)生成績(jī)表中李凱的姓名后加一個(gè)批注“班長(zhǎng)”，給石慧娟的姓名后加一個(gè)批注“學(xué)習(xí)委員”。

(4)保存工作簿。

任務(wù)一學(xué)生成績(jī)表制作效果如圖5-2所示。

圖5-2“學(xué)生成績(jī)表”效果圖

制作產(chǎn)品月銷售統(tǒng)計(jì)表，并對(duì)表格進(jìn)行格式化。

要求：

(1)新建一個(gè)工作簿文件，將當(dāng)前的臨時(shí)工作簿文件保存在D:\EXCEL文件夾中，命名為“產(chǎn)品月銷售統(tǒng)計(jì)表.xls”。

(2)在Sheet1工作表中輸入圖5-3所示內(nèi)容。任務(wù)二制作產(chǎn)品月銷售統(tǒng)計(jì)表

圖5-3“產(chǎn)品月銷售統(tǒng)計(jì)表”原始數(shù)據(jù)

(3)將Sheet1中的表格內(nèi)容復(fù)制到Sheet2相同的區(qū)域中，將工作表Sheet2重新命名為“產(chǎn)品月銷售統(tǒng)計(jì)表”，并在該工作表中完成下面的操作。

①在“產(chǎn)品月銷售統(tǒng)計(jì)表”工作表第2行前插入1行，輸入當(dāng)前日期(當(dāng)前日期輸入方法為<Ctrl+;>)，并將當(dāng)前日期數(shù)據(jù)設(shè)置為“××××年××月××日”的格式。

②設(shè)置單元格的對(duì)齊方式及合并單元格。將標(biāo)題行跨列垂直和水平居中，第2行(當(dāng)前日期)右對(duì)齊，其余行數(shù)據(jù)居中。③設(shè)置字體格式。將標(biāo)題字體設(shè)置為“黑體、加粗、深紅色、18號(hào)”，其他的文字字體設(shè)置為“宋體、常規(guī)、12號(hào)”。

④設(shè)置單元格的行高和列寬。將標(biāo)題欄的行高設(shè)置為“35”，數(shù)據(jù)部分的行高設(shè)置為“20”。將“名稱”、“單價(jià)”列寬設(shè)置為“8”，其余列設(shè)置為最合適的列寬。

(4)保存工作簿。

圖5-4“產(chǎn)品月銷售統(tǒng)計(jì)表”效果圖

制作員工年度績(jī)效考核表，并對(duì)表格進(jìn)行格式化。

要求：

(1)新建一個(gè)工作簿文件，將當(dāng)前的臨時(shí)工作簿文件保存在D:\EXCEL文件夾中，命名為“員工年度績(jī)效考核表.xls”。

(2)在Sheet1工作表中輸入圖5-5所示內(nèi)容，其中：

C3單元格內(nèi)容為：工作計(jì)劃考評(píng)(50分)；任務(wù)三 制作員工年度績(jī)效考核表

D3單元格內(nèi)容為：規(guī)章制度執(zhí)行考評(píng)(30分)；

E3單元格內(nèi)容為：出勤情況考評(píng)(10分)；

F3單元格內(nèi)容為：團(tuán)隊(duì)與協(xié)作考評(píng)(10分)；

G3單元格內(nèi)容為：年度績(jī)效總分。

圖5-5“員工年度績(jī)效考核表”原始數(shù)據(jù)

(3)將Sheet1中的表格內(nèi)容復(fù)制到Sheet2相同的區(qū)域中，將工作表Sheet2重新命名為“員工年度績(jī)效考核表”，并在該工作表中完成下面的操作。

①設(shè)置單元格的對(duì)齊方式及合并單元格。參照?qǐng)D5-6所示設(shè)置單元格的對(duì)齊方式及合并單元格。

②設(shè)置字體格式。將標(biāo)題字體設(shè)置為“宋體、加粗、黑色、18號(hào)”，其他的文字字體設(shè)置為“宋體、常規(guī)、12號(hào)”。

③設(shè)置邊框、背景圖案。為員工年度績(jī)效考核表添加邊框：外框雙細(xì)線、內(nèi)框單細(xì)線。第1行標(biāo)題：茶色點(diǎn)狀底紋；第2、3行文字(班級(jí)、姓名……)，黃色底紋；其他各行文字，淺綠色底紋。

④設(shè)置單元格的行高和列寬。將數(shù)據(jù)列寬設(shè)置為“8”。將標(biāo)題欄的行高設(shè)置為“30”，數(shù)據(jù)部分的行高設(shè)置為最合適的行高。

(4)保存工作簿。

任務(wù)三員工年度績(jī)效考核表最終效果如圖5-6所示。

圖5-6“員工年度績(jī)效考核表”效果圖

【操作提示】

1.新建工作簿

進(jìn)入Excel，選擇工具欄“文件”→“新建”命令，單擊“空白工作簿”按鈕。

2.保存工作簿

選擇“文件”→“保存(另存為)”，在彈出的對(duì)話框中，設(shè)置存盤的位置及文件名。

3.插入工作表、復(fù)制表格的操作

(1)插入：選擇【插入】→【工作表】命令即可。

(2)復(fù)制表格：通過(guò)復(fù)制和粘貼操作完成。

4.工作表的編輯及格式化操作

(1)在標(biāo)題下插入一行：執(zhí)行【插入】→【行】命令。

(2)標(biāo)題格式：選中標(biāo)題行，執(zhí)行【格式】→【單元格】命令，選擇“字體”或“圖案”標(biāo)簽。

(3)合并單元格并居中：選中文本單元格，執(zhí)行【格式】→【單元格】命令，選擇“對(duì)齊”標(biāo)簽。

(4)表格內(nèi)容自動(dòng)換行：選中文本單元格，執(zhí)行【格式】→【單元格】命令，選擇“對(duì)齊”標(biāo)簽，選中“文本控制”中的“自動(dòng)換行”。。

(5)設(shè)置表格邊框線：選中表格，執(zhí)行【格式】→【單元格】命令，選擇“邊框”標(biāo)簽。

(6)設(shè)置表格底紋：選中表格，執(zhí)行【格式】→【單元格】命令，選擇“圖案”標(biāo)簽。

(7)重新命名工作表：在該工作表的快捷菜單中選“重命名”。

(8)輸入當(dāng)前日期：方法為<Ctrl+;

>。

(9)設(shè)置日期及數(shù)據(jù)格式：選中文本單元格，執(zhí)行【格式】→【單元格】命令，選擇“數(shù)字”標(biāo)簽，進(jìn)行相應(yīng)設(shè)置。

【實(shí)訓(xùn)目的】

(1)學(xué)習(xí)公式與函數(shù)的使用。

(2)掌握?qǐng)D表的制作方法和圖表的格式化操作。

【實(shí)訓(xùn)內(nèi)容】

對(duì)實(shí)訓(xùn)項(xiàng)目1中制作的三個(gè)表格：學(xué)生成績(jī)表、產(chǎn)品月銷售統(tǒng)計(jì)表和員工年度績(jī)效考核表進(jìn)行計(jì)算及制作圖表。實(shí)訓(xùn)項(xiàng)目2表格的計(jì)算及圖表的制作

打開(kāi)“學(xué)生成績(jī)表.xls”，并對(duì)表格按要求進(jìn)行計(jì)算，如圖5-7所示。

要求：

(1)添加“總分”和“總評(píng)”列，增加“平均分”、“最高分”、“最低分”、“總?cè)藬?shù)”以及“不及格人數(shù)”行，如圖5-7所示。

(2)計(jì)算“總分”、“平均分”列數(shù)據(jù)。

①總分

=

數(shù)學(xué)

+

英語(yǔ)

+

計(jì)算機(jī)。

②平均分

=

(數(shù)學(xué)

+

英語(yǔ)

+

計(jì)算機(jī))÷3。任務(wù)一學(xué)生成績(jī)表的計(jì)算

圖5-7“學(xué)生成績(jī)表”的計(jì)算結(jié)果

(3)分別計(jì)算“數(shù)學(xué)”、“英語(yǔ)”、“計(jì)算機(jī)”三門課的“平均分”、“最高分”、“最低分”、“總?cè)藬?shù)”以及“不及格人數(shù)”。

(4)計(jì)算“總評(píng)”列數(shù)據(jù)。

總評(píng)：平均分≥90，優(yōu)秀；平均分≥80，良好；平均分≥60，及格；否則，不及格。

(5)在“學(xué)生成績(jī)表”中，對(duì)前5個(gè)學(xué)生制作如圖5-8所示的“成績(jī)圖表”，并設(shè)置圖表標(biāo)題格式為“黑體、加粗、18號(hào)”，設(shè)置繪圖區(qū)圖案格式為雙色(橙色和淺黃色)底紋格式，移動(dòng)圖表位置和調(diào)整圖表大小到合適的位置。

圖5-8學(xué)生成績(jī)圖表

(6)保存工作簿。

任務(wù)一學(xué)生成績(jī)表的計(jì)算結(jié)果如圖5-7所示。

打開(kāi)“產(chǎn)品月銷售統(tǒng)計(jì)表.xls”，并對(duì)表格按要求進(jìn)行計(jì)算，如圖5-9所示。任務(wù)二產(chǎn)品月銷售統(tǒng)計(jì)表的計(jì)算

圖5-9“產(chǎn)品月銷售統(tǒng)計(jì)表”的計(jì)算結(jié)果要求：

(1)計(jì)算“產(chǎn)品月銷售統(tǒng)計(jì)表”中“銷售額”、“利潤(rùn)”列數(shù)據(jù)，將計(jì)算出的單元格數(shù)據(jù)設(shè)置為“￥”貨幣格式。

①銷售額

=

單價(jià)

×

銷售數(shù)量。

②利潤(rùn)

=

銷售額

×

利潤(rùn)率(存放在E11單元格)。

(2)計(jì)算“合計(jì)”行數(shù)據(jù)。

(3)計(jì)算“占總銷售額比例”列數(shù)據(jù)，將計(jì)算出的單元格數(shù)據(jù)設(shè)置為百分比格式。

占總銷售額比例

=

銷售額

÷

合計(jì)。

(4)設(shè)置單元格格式，對(duì)該表格使用自動(dòng)套用格式中的“彩色2”。

(5)對(duì)“產(chǎn)品月銷售統(tǒng)計(jì)表”中數(shù)據(jù)制作如圖5-10所示的“產(chǎn)品銷售額占總銷售額比例”圖表，并設(shè)置圖表標(biāo)題格式為“宋體、加粗、14號(hào)”，設(shè)置圖表區(qū)圖案格式為“填充效果”中“紋理”中的“新聞紙”，移動(dòng)圖表位置和調(diào)整圖表大小到合適的位置。

圖5-10“產(chǎn)品銷售額占總銷售額比例”圖表樣式

(6)保存工作簿。

任務(wù)二產(chǎn)品月銷售統(tǒng)計(jì)表的計(jì)算結(jié)果如圖5-9所示。

打開(kāi)“員工年度績(jī)效考核表.xls”，并對(duì)表格按要求進(jìn)行計(jì)算。

要求：

(1)在“員工年度績(jī)效考核表”中，利用SUM函數(shù)計(jì)算“工作計(jì)劃”～“團(tuán)隊(duì)與協(xié)作考評(píng)”列數(shù)據(jù)。

任務(wù)三員工年度績(jī)效考核表的計(jì)算

(2)利用RANK函數(shù)，根據(jù)“年度績(jī)效總分”成績(jī)計(jì)算“本人排名”。

(3)保存工作簿。

任務(wù)三員工年度績(jī)效考核表的計(jì)算結(jié)果如圖5-11所示。

圖5-11“員工年度績(jī)效考核表”的計(jì)算結(jié)果

【操作提示】

1.學(xué)生成績(jī)表的計(jì)算

(1)插入“總分”和“總評(píng)”列：執(zhí)行【插入】→【行】命令。

(2)計(jì)算“總分”、“平均分”列數(shù)據(jù)：?jiǎn)螕簟俺Ｓ谩惫ぞ邫谥械摹白詣?dòng)求和”按鈕 完成或采用輸入公式的方式。請(qǐng)嘗試這兩種方法。公式復(fù)制可采用向下拖曳填充柄的方式進(jìn)行。

(3)“數(shù)學(xué)”、“英語(yǔ)”、“計(jì)算機(jī)”三門課的“平均分”、“最高分”、“最低分”、“總?cè)藬?shù)”以及“不及格人數(shù)”的計(jì)算可采用單擊“常用”工具欄中的“自動(dòng)求和”按鈕 完成或采用插入函數(shù)的方式。函數(shù)如下：

數(shù)學(xué)“平均分”，D19單元格中輸入“=AVERAGE(D3:D17)”

數(shù)學(xué)“最高分”，D20單元格中輸入“=MAX(D3:D17)”

數(shù)學(xué)“最低分”，D21單元格中輸入“=MIN(D3:D17)”

數(shù)學(xué)“總?cè)藬?shù)”，D22單元格中輸入“=COUNT(D3:D17)”數(shù)學(xué)“不及格人數(shù)”，D23單元格中輸入“=COUNTIF(D3:D17,"<60")”

然后采用公式復(fù)制的方法得到英語(yǔ)、計(jì)算機(jī)的平均分、最高分、最低分、不及格人數(shù)等數(shù)據(jù)。

(4)計(jì)算“總評(píng)”列數(shù)據(jù)。

I3單元格中輸入以下公式：

“=IF(H3>=90,"優(yōu)秀",IF(H3>=80,"良好",IF(H3>=60,"及格","不及格")))”

然后采用公式復(fù)制的方法得到其他學(xué)生的“總評(píng)”列數(shù)據(jù)。

(5)制作圖表，選擇前5個(gè)學(xué)生的“姓名”、“數(shù)學(xué)”、“英語(yǔ)”、“計(jì)算機(jī)”列數(shù)據(jù)，執(zhí)行【插入】→【圖表】命令或單擊工具欄中的圖表向?qū)О粹o ，按照向?qū)Р襟E指引制作族狀柱形圖表，對(duì)圖表標(biāo)題以及繪圖區(qū)可單擊鼠標(biāo)右鍵設(shè)置格式。

2.產(chǎn)品月銷售統(tǒng)計(jì)表的計(jì)算

(1)計(jì)算“產(chǎn)品月銷售統(tǒng)計(jì)表”中“銷售額”，使用相對(duì)地址引用，D4單元格中輸入“=B4*C4”，然后采用公式復(fù)制的方法得到下方各行數(shù)據(jù)。

“利潤(rùn)”列數(shù)據(jù)計(jì)算應(yīng)使用絕對(duì)地址引用，E4單元格中輸入“=D4/$D$8”，然后采用公式復(fù)制的方法得到下方各行數(shù)據(jù)。

將計(jì)算出的單元格數(shù)據(jù)設(shè)置為“￥”貨幣格式的方法：選擇數(shù)據(jù)區(qū)域，執(zhí)行【格式】→【單元格】命令，選擇“數(shù)字”標(biāo)簽，進(jìn)行相應(yīng)設(shè)置。

(2)計(jì)算“合計(jì)”行數(shù)據(jù)：?jiǎn)螕簟俺Ｓ谩惫ぞ邫谥械摹白詣?dòng)求和”按鈕 完成。

(3)計(jì)算“占總銷售額比例”列數(shù)據(jù)，使用絕對(duì)地址引用，F(xiàn)4單元格中輸入“=D4/$D$8”，然后采用公式復(fù)制的方法得到下方各行數(shù)據(jù)。

計(jì)算出的單元格數(shù)據(jù)設(shè)置為百分比格式：選擇數(shù)據(jù)區(qū)域，執(zhí)行【格式】→【單元格】命令，選擇“數(shù)字”標(biāo)簽，進(jìn)行相應(yīng)設(shè)置。

(4)設(shè)置單元格格式，對(duì)該表格使用自動(dòng)套用格式中的“彩色2”：選擇數(shù)據(jù)區(qū)域，執(zhí)行【格式】→【自動(dòng)套用格式】命令，選擇“彩色2”。

(5)制作圖表，選擇“名稱”和“占總銷售額比例”列數(shù)據(jù)，執(zhí)行【插入】→【圖表】命令，執(zhí)行【插入】→【圖表】命令或單擊工具欄中的圖表向?qū)О粹o ，按照向?qū)Р襟E指引制作分離型三維餅圖，對(duì)圖表區(qū)單擊鼠標(biāo)右鍵，在快捷菜單中單擊“圖表區(qū)格式”，在“圖案”選項(xiàng)卡中選擇“填充效果”，在“紋理”中選擇“新聞紙”。

3.員工年度績(jī)效考核表的計(jì)算

(1)在“員工年度績(jī)效考核表”中，在G4單元格中利用SUM函數(shù)計(jì)算“年度績(jī)效總分”列數(shù)據(jù)，然后采用公式復(fù)制的方法得到下方各行數(shù)據(jù)。

(2)利用RANK函數(shù)計(jì)算“本人排名”列數(shù)據(jù)的方法：使用絕對(duì)地址引用，H4單元格中輸入“=RANK(G4,$G$4:$G$12)”，然后采用公式復(fù)制的方法得到下方各行數(shù)據(jù)。

【實(shí)訓(xùn)目的】

(1)了解Excel的數(shù)據(jù)庫(kù)管理功能。

(2)掌握數(shù)據(jù)記錄的排序操作。

(3)掌握數(shù)據(jù)記錄的篩選(自動(dòng)篩選和高級(jí)篩選)操作。

(4)掌握數(shù)據(jù)表分類匯總的方法。

(5)掌握數(shù)據(jù)透視表的建立方法。

實(shí)訓(xùn)項(xiàng)目3Excel的數(shù)據(jù)庫(kù)管理及統(tǒng)計(jì)功能

【實(shí)訓(xùn)內(nèi)容】

在實(shí)訓(xùn)項(xiàng)目2完成的“學(xué)生成績(jī)表”的基礎(chǔ)上，完成排序、篩選、分類匯總和數(shù)據(jù)透視表等操作。

打開(kāi)“學(xué)生成績(jī)表.xls”，并對(duì)表格按要求進(jìn)行排序和篩選操作。

要求：

(1)將實(shí)訓(xùn)項(xiàng)目2完成的“學(xué)生成績(jī)表”復(fù)制到4個(gè)新表中，并將復(fù)制所得的新表分別重命名為“排序表”、“自動(dòng)篩選表”、“自定義篩選表”以及“高級(jí)篩選表”。

任務(wù)一學(xué)生成績(jī)表的排序和篩選

(2)在“排序表”中，以“平均分”為關(guān)鍵字按遞減方式排序，若平均分相同，則按姓名的姓氏筆畫(huà)升序排序，結(jié)果如圖5-12所示。

圖5-12“排序表”結(jié)果圖

(3)在“自動(dòng)篩選表”中，篩選出“英語(yǔ)”排在前5名的學(xué)生，結(jié)果如圖5-13所示。

(4)在“自定義篩選表”中，篩選出“計(jì)算機(jī)”分?jǐn)?shù)在80～90分之間的學(xué)生，結(jié)果如圖5-14所示。

(5)在“高級(jí)篩選表”中，篩選出至少有一門課程不及格的學(xué)生，如圖5-15所示。

圖5-13“自動(dòng)篩選表”結(jié)果圖

圖5-14“自定義篩選表”結(jié)果圖

圖5-15“高級(jí)篩選表”結(jié)果圖

打開(kāi)“學(xué)生成績(jī)表.xls”，并對(duì)表格按要求進(jìn)行分類匯總和制作數(shù)據(jù)透視表。

要求：

(1)將“學(xué)生成績(jī)表”復(fù)制到2個(gè)新表中，并將復(fù)制所得的新表分別重命名為“分類匯總表”以及“數(shù)據(jù)透視表”。

(2)在“分類匯總表”中，按照性別分類匯總各列數(shù)據(jù)的平均值，結(jié)果如圖5-16所示。任務(wù)二學(xué)生成績(jī)表的分類匯總和制作數(shù)據(jù)透視表

圖5-16“分類匯總表”結(jié)果圖

(3)在“數(shù)據(jù)透視表”中，為學(xué)生成績(jī)統(tǒng)計(jì)表建立數(shù)據(jù)透視表，如圖5-17所示。

(4)保存工作簿。

【操作提示】

1.學(xué)生成績(jī)表的排序和篩選

(1)數(shù)據(jù)排序。執(zhí)行【數(shù)據(jù)】→【排序】命令，在彈出的對(duì)話框中根據(jù)要求進(jìn)行設(shè)置，如圖5-18所示。

(2)數(shù)據(jù)篩選。執(zhí)行【數(shù)據(jù)】→【篩選】→【自動(dòng)篩選(高級(jí)篩選)】命令，在彈出的對(duì)話框中根據(jù)要求進(jìn)行設(shè)置，分別如圖5-19～圖5-21所示。

圖5-17數(shù)據(jù)透視表

圖5-18“排序”對(duì)話框

圖5-19“自動(dòng)篩選前10個(gè)”對(duì)話框

圖5-20“自定義自動(dòng)篩選方式”對(duì)話框

圖5-21“高級(jí)篩選”對(duì)話框

2.學(xué)生成績(jī)表的分類匯總和制作數(shù)據(jù)透視表

(1)數(shù)據(jù)分類匯總。先按照“性別”字段進(jìn)行排序，然后執(zhí)行【數(shù)據(jù)】→【分類匯總】命令，在彈出的對(duì)話框中根據(jù)要求進(jìn)行設(shè)置，如圖5-22所示。

(2)建立數(shù)據(jù)透視表。執(zhí)行【數(shù)據(jù)】→【數(shù)據(jù)透視表】命令，根據(jù)數(shù)據(jù)透視表向?qū)У闹敢瓿蓴?shù)據(jù)透視表的建立。

圖5-22“分類匯總”對(duì)話框

【實(shí)訓(xùn)目的】

(1)熟練掌握Excel的基本制表方式方法。

(2)熟練掌握Excel中公式和函數(shù)計(jì)算。

(3)熟練掌握Excel的圖表制作。

(4)提高綜合應(yīng)用Excel處理數(shù)據(jù)的能力。實(shí)訓(xùn)項(xiàng)目4綜合應(yīng)用Excel處理數(shù)據(jù)

【實(shí)訓(xùn)內(nèi)容】

建立“部門人員工資表.xls”，完成表格的輸入、計(jì)算、排序、篩選、分類匯總，建立數(shù)據(jù)透視表等操作。

建立“部門人員工資表”，完成表格輸入、計(jì)算以及格式化操作。

要求：

(1)新建一個(gè)工作簿文件，將當(dāng)前的臨時(shí)工作簿文件保存在D:\EXCEL文件夾中，命名為“部門人員工資表.xls”。

(2)在sheet1中輸入數(shù)據(jù)，如圖5-23所示。任務(wù)一部門人員工資表的建立以及格式化

圖5-23“部門人員工資表”原始數(shù)據(jù)

(3)在表中完成計(jì)算

①“工齡補(bǔ)貼”：按工作一年補(bǔ)貼8元計(jì)算。

②“扣公積金”：按“職務(wù)工資”的一定比例扣除公積金，該比例數(shù)存放在J16單元格中，要求使用絕對(duì)地址的概念。

③應(yīng)發(fā)工資

=

職務(wù)工資

+

津貼

+

獎(jiǎng)金

+

工齡補(bǔ)貼

-

扣水電費(fèi)

-

扣公積金

④求出每一列數(shù)據(jù)的平均值、最大值和最小值。⑤參考圖5-24，在第二行前插入一行，輸入制表日期和制表人，在“制表日期”后填寫(xiě)練習(xí)當(dāng)日的系統(tǒng)日期，在“制表人”后填寫(xiě)制表人的姓名。

⑥參考圖5-24，對(duì)表格進(jìn)行格式化：第一行標(biāo)題為“華文彩云、22號(hào)、加粗、水平方向和垂直方向居中、紅色”，填充顏色為“冰藍(lán)”。

圖5-24完成計(jì)算及格式化后的“部門人員工資表”⑦第二行為“宋體、10號(hào)、水平居中、黑色，跨列合并”。

⑧第三行列標(biāo)題文字為“宋體、12號(hào)、加粗、水平方向和垂直方向居中、黑色”，填充顏色為“淺青綠”。

⑨其余行文字為“宋體、12號(hào)、水平垂直居中、黑色”，數(shù)值保留一位小數(shù)，其中“應(yīng)發(fā)工資”列保留兩位小數(shù)，并帶貨幣符號(hào)。

⑩參考圖5-24設(shè)置表格邊框。

(4)將Sheet1工作表名更