學習情境五電子表格Excel2003實訓項目1表格的建立及格式化 實訓項目2表格的計算及圖表的制作 實訓項目3Excel的數據庫管理及統計功能實訓項目4綜合應用Excel處理數據

通過本章學習，應掌握電子表格Excel2003的建立、計算、圖表和數據統計的操作方法，主要內容包括：

1．Excel2003的基本操作

在Excel中有很多快速輸入數據的技巧，如自動填充、自定義序列等，熟練掌握這些技巧可以提高數據輸入速度。同時，在輸入數據時，要注意數據單元格的數據分類，如對于編號、郵編及電話號碼等數據應該設置為文本型。

2．工作表的格式化

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

3．工作表的計算

Excel最強大的是其計算功能，這功能主要是通過公式和函數來實現的。在進行公式和函數計算時，要熟悉公式的輸入規則、函數參數的設置方法及單元格的引用方式，常用函數的使用要熟練掌握。

4．圖表的建立及格式化

圖表可以比具體的數字更直觀地說明問題，因此在實際應用中，在使用表格的同時，通常還需要輔以圖表加以說明。使用圖表向導創建圖表，然后移動圖表位置、調整圖表大小、設置圖表區和繪圖區格式，以及進行其他設置等格式化操作。

5．工作表的數據管理及統計

用戶可以使用數據排序、篩選、分類匯總、創建數據透視表等對表格中的數據進行統計分析。

數據的篩選可以通過“自動篩選”和“高級篩選”兩種方式進行。“自動篩選”可用于簡單條件的篩選，“高級篩選”可用于復雜條件的篩選。篩選結果使工作表中滿足設定條件的記錄顯示出來。分類匯總實際上是一種條件求和，很多統計類的問題都可以用“分類匯總”來完成。在進行分類匯總之前一定要對工作表按照分類字段進行排序，再進行匯總計算。

數據透視表是一種交互式工作表，用于對現有工作表進行匯總和分析。在使用數據透視表時，要注意數據區域的正確選取，根據需要設定正確的布局。任務單

【實訓目的】

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

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

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

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

(5)熟練掌握格式化工作表的操作。實訓項目1表格的建立及格式化

【實訓內容】

本例制作三個表格，分別為學生成績表、產品月銷售統計表和員工年度績效考核表。

制作學生成績表，并對表格進行格式化。

要求：

(1)新建一個工作簿文件，將當前的臨時工作簿文件保存在D:\EXCEL文件夾中，命名為“學生成績表.xls”。

(2)在Sheet1工作表中輸入圖5-1所示內容。任務一制作學生成績表

圖5-1“學生成績表”原始數據

(3)將Sheet1中的表格內容復制到Sheet2相同的區域中，將工作表Sheet2重新命名為“學生成績表”。

①設置“學生成績表”工作表單元格的對齊方式及合并單元格。將標題行合并居中(水平居中、垂直居中)，將第2～16行數據居中。

②設置字體格式。將標題字體設置為“隸書、加粗、深藍色、20號且加下劃線”，其他的文字字體設置為“宋體、常規、12號”。③設置數字格式。將“數學”、“英語”和“計算機”列數據保留1位小數。

④設置邊框、背景圖案。為學生成績表添加邊框，邊框為“深紅色，外框粗線、內框細線”。為標題行加背景：“淺青色”；列標頭加背景：“象牙色”。

⑤設置單元格的行高和列寬。將標題欄的行高設置為“30”，數據部分的行高設置為“18”，將“學號”列的列寬設置為“10”，其余列的列寬設置為“8”。⑥設置條件格式。對學生成績表中數學、英語、計算機成績大于或等于90分的數據用藍色、加雙下劃線來標注，低于60元的數據用紅色、加粗傾斜來標注。

⑦插入批注。給學生成績表中李凱的姓名后加一個批注“班長”，給石慧娟的姓名后加一個批注“學習委員”。

(4)保存工作簿。

任務一學生成績表制作效果如圖5-2所示。

圖5-2“學生成績表”效果圖

制作產品月銷售統計表，并對表格進行格式化。

要求：

(1)新建一個工作簿文件，將當前的臨時工作簿文件保存在D:\EXCEL文件夾中，命名為“產品月銷售統計表.xls”。

(2)在Sheet1工作表中輸入圖5-3所示內容。任務二制作產品月銷售統計表

圖5-3“產品月銷售統計表”原始數據

(3)將Sheet1中的表格內容復制到Sheet2相同的區域中，將工作表Sheet2重新命名為“產品月銷售統計表”，并在該工作表中完成下面的操作。

①在“產品月銷售統計表”工作表第2行前插入1行，輸入當前日期(當前日期輸入方法為<Ctrl+;>)，并將當前日期數據設置為“××××年××月××日”的格式。

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

④設置單元格的行高和列寬。將標題欄的行高設置為“35”，數據部分的行高設置為“20”。將“名稱”、“單價”列寬設置為“8”，其余列設置為最合適的列寬。

(4)保存工作簿。

圖5-4“產品月銷售統計表”效果圖

制作員工年度績效考核表，并對表格進行格式化。

要求：

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

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

C3單元格內容為：工作計劃考評(50分)；任務三 制作員工年度績效考核表

D3單元格內容為：規章制度執行考評(30分)；

E3單元格內容為：出勤情況考評(10分)；

F3單元格內容為：團隊與協作考評(10分)；

G3單元格內容為：年度績效總分。

圖5-5“員工年度績效考核表”原始數據

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

①設置單元格的對齊方式及合并單元格。參照圖5-6所示設置單元格的對齊方式及合并單元格。

②設置字體格式。將標題字體設置為“宋體、加粗、黑色、18號”，其他的文字字體設置為“宋體、常規、12號”。

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

④設置單元格的行高和列寬。將數據列寬設置為“8”。將標題欄的行高設置為“30”，數據部分的行高設置為最合適的行高。

(4)保存工作簿。

任務三員工年度績效考核表最終效果如圖5-6所示。

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

【操作提示】

1.新建工作簿

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

2.保存工作簿

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

3.插入工作表、復制表格的操作

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

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

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

(1)在標題下插入一行：執行【插入】→【行】命令。

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

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

(4)表格內容自動換行：選中文本單元格，執行【格式】→【單元格】命令，選擇“對齊”標簽，選中“文本控制”中的“自動換行”。。

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

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

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

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

>。

(9)設置日期及數據格式：選中文本單元格，執行【格式】→【單元格】命令，選擇“數字”標簽，進行相應設置。

【實訓目的】

(1)學習公式與函數的使用。

(2)掌握圖表的制作方法和圖表的格式化操作。

【實訓內容】

對實訓項目1中制作的三個表格：學生成績表、產品月銷售統計表和員工年度績效考核表進行計算及制作圖表。實訓項目2表格的計算及圖表的制作

打開“學生成績表.xls”，并對表格按要求進行計算，如圖5-7所示。

要求：

(1)添加“總分”和“總評”列，增加“平均分”、“最高分”、“最低分”、“總人數”以及“不及格人數”行，如圖5-7所示。

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

①總分

=

數學

+

英語

+

計算機。

②平均分

=

(數學

+

英語

+

計算機)÷3。任務一學生成績表的計算

圖5-7“學生成績表”的計算結果

(3)分別計算“數學”、“英語”、“計算機”三門課的“平均分”、“最高分”、“最低分”、“總人數”以及“不及格人數”。

(4)計算“總評”列數據。

總評：平均分≥90，優秀；平均分≥80，良好；平均分≥60，及格；否則，不及格。

(5)在“學生成績表”中，對前5個學生制作如圖5-8所示的“成績圖表”，并設置圖表標題格式為“黑體、加粗、18號”，設置繪圖區圖案格式為雙色(橙色和淺黃色)底紋格式，移動圖表位置和調整圖表大小到合適的位置。

圖5-8學生成績圖表

(6)保存工作簿。

任務一學生成績表的計算結果如圖5-7所示。

打開“產品月銷售統計表.xls”，并對表格按要求進行計算，如圖5-9所示。任務二產品月銷售統計表的計算

圖5-9“產品月銷售統計表”的計算結果要求：

(1)計算“產品月銷售統計表”中“銷售額”、“利潤”列數據，將計算出的單元格數據設置為“￥”貨幣格式。

①銷售額

=

單價

×

銷售數量。

②利潤

=

銷售額

×

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

(2)計算“合計”行數據。

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

占總銷售額比例

=

銷售額

÷

合計。

(4)設置單元格格式，對該表格使用自動套用格式中的“彩色2”。

(5)對“產品月銷售統計表”中數據制作如圖5-10所示的“產品銷售額占總銷售額比例”圖表，并設置圖表標題格式為“宋體、加粗、14號”，設置圖表區圖案格式為“填充效果”中“紋理”中的“新聞紙”，移動圖表位置和調整圖表大小到合適的位置。

圖5-10“產品銷售額占總銷售額比例”圖表樣式

(6)保存工作簿。

任務二產品月銷售統計表的計算結果如圖5-9所示。

打開“員工年度績效考核表.xls”，并對表格按要求進行計算。

要求：

(1)在“員工年度績效考核表”中，利用SUM函數計算“工作計劃”～“團隊與協作考評”列數據。

任務三員工年度績效考核表的計算

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

(3)保存工作簿。

任務三員工年度績效考核表的計算結果如圖5-11所示。

圖5-11“員工年度績效考核表”的計算結果

【操作提示】

1.學生成績表的計算

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

(2)計算“總分”、“平均分”列數據：單擊“常用”工具欄中的“自動求和”按鈕 完成或采用輸入公式的方式。請嘗試這兩種方法。公式復制可采用向下拖曳填充柄的方式進行。

(3)“數學”、“英語”、“計算機”三門課的“平均分”、“最高分”、“最低分”、“總人數”以及“不及格人數”的計算可采用單擊“常用”工具欄中的“自動求和”按鈕 完成或采用插入函數的方式。函數如下：

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

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

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

數學“總人數”，D22單元格中輸入“=COUNT(D3:D17)”數學“不及格人數”，D23單元格中輸入“=COUNTIF(D3:D17,"<60")”

然后采用公式復制的方法得到英語、計算機的平均分、最高分、最低分、不及格人數等數據。

(4)計算“總評”列數據。

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

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

然后采用公式復制的方法得到其他學生的“總評”列數據。

(5)制作圖表，選擇前5個學生的“姓名”、“數學”、“英語”、“計算機”列數據，執行【插入】→【圖表】命令或單擊工具欄中的圖表向導按鈕 ，按照向導步驟指引制作族狀柱形圖表，對圖表標題以及繪圖區可單擊鼠標右鍵設置格式。

2.產品月銷售統計表的計算

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

“利潤”列數據計算應使用絕對地址引用，E4單元格中輸入“=D4/$D$8”，然后采用公式復制的方法得到下方各行數據。

將計算出的單元格數據設置為“￥”貨幣格式的方法：選擇數據區域，執行【格式】→【單元格】命令，選擇“數字”標簽，進行相應設置。

(2)計算“合計”行數據：單擊“常用”工具欄中的“自動求和”按鈕 完成。

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

計算出的單元格數據設置為百分比格式：選擇數據區域，執行【格式】→【單元格】命令，選擇“數字”標簽，進行相應設置。

(4)設置單元格格式，對該表格使用自動套用格式中的“彩色2”：選擇數據區域，執行【格式】→【自動套用格式】命令，選擇“彩色2”。

(5)制作圖表，選擇“名稱”和“占總銷售額比例”列數據，執行【插入】→【圖表】命令，執行【插入】→【圖表】命令或單擊工具欄中的圖表向導按鈕 ，按照向導步驟指引制作分離型三維餅圖，對圖表區單擊鼠標右鍵，在快捷菜單中單擊“圖表區格式”，在“圖案”選項卡中選擇“填充效果”，在“紋理”中選擇“新聞紙”。

3.員工年度績效考核表的計算

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

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

【實訓目的】

(1)了解Excel的數據庫管理功能。

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

(3)掌握數據記錄的篩選(自動篩選和高級篩選)操作。

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

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

實訓項目3Excel的數據庫管理及統計功能

【實訓內容】

在實訓項目2完成的“學生成績表”的基礎上，完成排序、篩選、分類匯總和數據透視表等操作。

打開“學生成績表.xls”，并對表格按要求進行排序和篩選操作。

要求：

(1)將實訓項目2完成的“學生成績表”復制到4個新表中，并將復制所得的新表分別重命名為“排序表”、“自動篩選表”、“自定義篩選表”以及“高級篩選表”。

任務一學生成績表的排序和篩選

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

圖5-12“排序表”結果圖

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

(4)在“自定義篩選表”中，篩選出“計算機”分數在80～90分之間的學生，結果如圖5-14所示。

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

圖5-13“自動篩選表”結果圖

圖5-14“自定義篩選表”結果圖

圖5-15“高級篩選表”結果圖

打開“學生成績表.xls”，并對表格按要求進行分類匯總和制作數據透視表。

要求：

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

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

圖5-16“分類匯總表”結果圖

(3)在“數據透視表”中，為學生成績統計表建立數據透視表，如圖5-17所示。

(4)保存工作簿。

【操作提示】

1.學生成績表的排序和篩選

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

(2)數據篩選。執行【數據】→【篩選】→【自動篩選(高級篩選)】命令，在彈出的對話框中根據要求進行設置，分別如圖5-19～圖5-21所示。

圖5-17數據透視表

圖5-18“排序”對話框

圖5-19“自動篩選前10個”對話框

圖5-20“自定義自動篩選方式”對話框

圖5-21“高級篩選”對話框

2.學生成績表的分類匯總和制作數據透視表

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

(2)建立數據透視表。執行【數據】→【數據透視表】命令，根據數據透視表向導的指引完成數據透視表的建立。

圖5-22“分類匯總”對話框

【實訓目的】

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

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

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

(4)提高綜合應用Excel處理數據的能力。實訓項目4綜合應用Excel處理數據

【實訓內容】

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

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

要求：

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

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

圖5-23“部門人員工資表”原始數據

(3)在表中完成計算

①“工齡補貼”：按工作一年補貼8元計算。

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

③應發工資

=

職務工資

+

津貼

+

獎金

+

工齡補貼

-

扣水電費

-

扣公積金

④求出每一列數據的平均值、最大值和最小值。⑤參考圖5-24，在第二行前插入一行，輸入制表日期和制表人，在“制表日期”后填寫練習當日的系統日期，在“制表人”后填寫制表人的姓名。

⑥參考圖5-24，對表格進行格式化：第一行標題為“華文彩云、22號、加粗、水平方向和垂直方向居中、紅色”，填充顏色為“冰藍”。

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

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

⑨其余行文字為“宋體、12號、水平垂直居中、黑色”，數值保留一位小數，其中“應發工資”列保留兩位小數，并帶貨幣符號。

⑩參考圖5-24設置表格邊框。

(4)將Sheet1工作表名更