1、實驗二 Excel 2010的高級操作一、 實驗目的通過一系列的操作實驗，要求學生熟練掌握電子表格的常用操作。1 熟練掌握Excel 2010中圖表的制作、更改、修飾等操作。2 熟練掌握Excel 2010中數據的排序、篩選、分類匯總等數據管理和分析。3 熟練掌握Excel 2010的綜合應用。二、實驗內容1. 圖表的創建和修飾操作。2. 數據的排序操作。3. 數據的自動篩選和高級篩選操作。4. 數據的分類匯總操作。5. 綜合實例操作。三、實驗步驟(一) 圖表的創建和修飾操作1. 創建簇狀柱形圖工作表打開工作簿“cunkuan.xls”，利用“家庭存款帳本”工作表中的數據建立一個“簇狀柱形圖”

2、圖表工作表，實驗的操作結果如圖2.1所示。圖2.1簇狀柱形圖工作表示例1) 操作要求（1） 類軸：“存款日期”；數值軸：“金額”和“本息”。（2） 圖表標題：金額、本息對照圖。（3） 分類軸標題：存款日期。（4） 數值軸標題：（元）。（5） 圖例位置：靠上。（6） 將圖表工作表命名為“金額、本息對照圖”。（7） 將圖表工作表保存到工作薄中。2) 操作方法（1） 打開工作簿“cunkuan.xls”，單擊“家庭存款帳本”工作表標簽，使之成為當前工作表。（2） 用鼠標拖動的方法，先選定“存款日期”列的數據，然后按住Ctrl鍵再分別選定“金額”和“本息”列的數據，如圖2.2所示。提示：在選定數據區域

3、時（不論是連續的，還是不連續的），都要一次性準確選擇有效的數據區域，否則將會造成圖表數據源引用錯誤，無法正確建立圖表。圖2.2選定數據區域示例（3） 單擊“插入”“圖表”功能區中的“柱形圖”命令按鈕，選擇“二維柱形圖”中的“簇狀柱形圖”，即可生成對應的圖表，如圖2.3和圖2.4所示。圖2.3選擇圖表類型示例圖2.4簇狀柱形圖表（4） 如果在插入圖表之前沒有選定數據或選定的數據不準確，可以單擊圖表，在“圖表工具”“設計”功能區中單擊“選擇數據”命令按鈕，彈出“選擇數據源”對話框，如圖2.5和2.6所示。在“圖表數據區域”文本框中可以重新選定數據區域（按照（2）步中的操作）。圖2.5修改數據源示例

4、圖2.6“選擇數據源”對話框（5） 在“選擇數據源”對話框中，單擊“切換行/列”按鈕，可以決定系列產生在“行”還是“列”（默認系列產生在“列”），如圖2.6所示。（6） 在“圖表工具”“布局”“標簽”功能組中可以創建及布局圖表的各種標簽，如圖2.7所示。單擊“圖表標題”按鈕，選擇“圖表上方”，即在圖表上方創建“圖表標題”標簽，在標簽中輸入“金額、本息對照圖”；單擊“坐標軸標題”按鈕，選擇“主要橫坐標軸標題”“坐標軸下方標題”，即在圖表下方創建“坐標軸標題”標簽，在標簽中輸入“存款日期”；單擊“坐標軸標題”按鈕，選擇“主要縱坐標軸標題”“豎排標題”，即在圖表右方創建“坐標軸標題”標簽，在標簽中輸

5、入 “（元）”；單擊“圖例”按鈕，選擇“在頂部顯示圖例”，即在圖表上方創建圖例標簽。（如果不需要圖例，單擊“圖例”按鈕，選擇“無”即可。）（7） 在“圖表工具”“設計”功能區中單擊“移動圖表”命令按鈕，彈出“移動圖表”對話框，選擇“新工作表”單選按鈕，并在對應的文本框中輸入“金額、本息對照圖”，如圖2.8所示。（8） 單擊“確定”按鈕，完成圖表工作表的創建，結果如圖2.1所示。（9） 單擊“文件”“保存”按鈕，或者單擊“快速訪問工具欄”中的“保存”按鈕，將圖表工作表保存在當前工作薄中。圖2.7創建圖表標簽示例圖2.8移動圖表對話框2. 設置圖表工作表格式對“金額、本息對照圖”工作表進行格式設置

6、，效果如圖2.9所示。1) 操作要求（1） 圖表標題：楷體、28號字、藍色、加粗；圖例標題：楷體、16號字、無邊框；分類軸（X坐標軸）標題：楷體、16號字、淺藍、加粗；數值軸（Y坐標軸）標題：楷體、16號字、淺藍、加粗，對齊方式為中部居中。（2） 分類軸格式：黑體、14號字、紅色；數字格式為日期型“yyyy年m月”，主要刻度單位“2月”；數值軸格式：黑體、14號字、紅色；數字格式為數值型，無小數點，刻度最小值為5000。圖2.9格式化的簇狀柱形圖圖表工作表示例2) 操作方法（1） 標題格式設置 單擊圖表標題“金額、本息對照圖”，選中所有字符，在“開始”|“字體”功能組中設置字符格式為楷體、28

7、號字、藍色、加粗； 單擊圖例，在“開始”|“字體”功能組中設置字符格式為楷體、16號字；雙擊圖例，打開“設置圖例項格式”對話框，單擊“邊框顏色”選項卡，設置邊框顏色為“無線條”； 單擊分類軸標題“存款日期”，在“開始”|“字體”功能組中設置字符格式為楷體、16號字、淺藍、加粗； 單擊Y坐標軸標題“（元）”，在“開始”|“字體”功能組中設置字符格式為楷體、16號字、淺藍、加粗；雙擊Y坐標軸標題“（元）”，打開“設置坐標軸標題格式”對話框，選擇“對齊方式”選項卡，設置文字方向為：“橫排”，垂直對齊方式為：“中部居中”，并將標題“（元）”拖到Y坐標軸上方的合適位置。（2） 分類軸格式設置 單擊X軸分

8、類軸線，在“開始”|“字體”功能組中設置字符格式為黑體、14號字、紅色； 雙擊X軸分類軸線，打開“設置坐標軸格式”對話框； 單擊“數字”選項卡，設置“類別”為“日期”，“類型”為“yyyy年m月”，如圖2.10所示；圖2.10 “數字”選項卡 圖2.11 “坐標軸選項”選項卡 單擊“坐標軸選項”選項卡，設置“主要刻度單位”為“固定：2、月”，如圖2.11所示。（3） 數值軸格式設置 單擊Y軸數值軸線，在“開始”|“字體”功能組中設置字符格式為黑體、14號字、紅色； 雙擊Y軸數值軸線，打開“設置坐標軸格式”對話框； 單擊“數字”選項卡，設置“類別”為“數字”，“小數位數”為“0”，去掉選中的“使

9、用千位分隔符（,）”復選框，如圖2.12所示；圖2.12 “數字”選項卡 圖2.13 “坐標軸選項”選項卡 單擊“坐標軸選項”選項卡，設置“最小值”為“固定：5000”，如圖2.13所示。(二) 數據的管理和分析操作1. 制作工作表副本1) 操作要求打開cunkuan.xls工作簿，插入三個工作表Sheet1、Sheet4、Sheet5，將“家庭存款帳單”工作表中的全部數據復制到工作表Sheet1、Sheet2、Sheet3、Sheet4、Sheet5中。將各工作表中的數據設為最適合列寬，并保存工作簿。2) 操作方法（1） 打開cunkuan.xls工作簿，鼠標右鍵單擊Sheet2工作表標簽，

10、打開快捷菜單，如圖2.14所示，單擊“插入”菜單，打開“插入”對話框，雙擊工作表圖標，在Sheet2之前插入一個名為Sheet1的工作表。（2） 用同樣的方法，在Sheet3之前插入工作表：Sheet4和Sheet5。（3） 單擊“家庭存款帳單”工作表標簽，使之成為當前工作表，選中整個工作表（單擊工作表全選標記行與列交叉的左上角區域），在“開始”|“剪貼板”功能組中單擊“復制”按鈕，然后在Sheet1中的A1單元格進行粘貼。在Sheet2、Sheet3、Sheet4、Sheet5中重復粘貼操作。（4） 選中整個工作表，在“開始”|“單元格”功能組中單擊“格式”按鈕，選擇“自動調整列寬”，設置各

11、工作表中的數據為最合適的列寬，并保存修改后的工作簿。圖2.14插入工作表2. 數據排序操作1) 操作要求將Sheet1工作表重命名為“排序”，并在該工作表中按“存期”升序排列，“存期”相同時再按“存款日期”降序排列。2) 操作方法（1） 雙擊Sheet1標簽，輸入“排序”，然后單擊工作表中的任意單元格，或按回車鍵，完成對工作表的重命名。（2） 選中A2:H14單元格區域，單擊“數據”|“排序”命令，打開“排序”對話框。（3） 從“主要關鍵字”區域下拉列表框中選擇“存期”，從“排序依據”區域下拉列表框中選擇“數值”，從“次序”區域下拉列表框中選擇“升序”；單擊“添加條件”按鈕，增加新的排序條件；

12、從“次要關鍵字”區域下拉列表框中選擇“存款日期”，從“排序依據”區域下拉列表框中選擇“數值”，從“次序”區域下拉列表框中選擇“降序”；勾選“數據包含標題”復選框（如不勾選該復選框，系統會把字段名混同數據一起排序），如圖2.15所示。圖2.15 “排序”對話框（4） 單擊“確定”按鈕，進行排序，排序結果如圖2.16所示。圖2.16排序結果示例提示：當對選中數據區域中的任意單元格進行排序時，系統默認選中所有數據作為排序對象。如果只對部分數據排序，應預先選擇待排序的數據區域，然后再執行排序命令。3. 自動篩選和高級篩選操作1) 自動篩選（1） 操作要求將Sheet2工作表重命名為“自動篩選”，然后自

13、動篩選出“金額”大于20030且“存款銀行”為“工商銀行”和“中國銀行”的記錄。（2） 操作方法 雙擊Sheet2標簽，輸入“自動篩選”，然后單擊工作表中的任意單元格，或按回車鍵，完成對工作表的重命名。 選中A2:H14單元格區域， 在“數據”|“排序和篩選”功能組中單擊“篩選”命令按鈕，工作表中每個字段名后將出現一個下拉箭頭按鈕，如圖2.17所示。圖2.17 自動篩選操作 單擊“金額”右側的下拉箭頭按鈕，在下拉列表框中選擇“數字篩選”|“自定義篩選”項，打開“自定義自動篩選方式”對話框，按圖2.18所示進行設置，單擊“確定”按鈕，此時，工作表中只顯示出“金額”大于20030的記錄。圖2.18

14、 設置“金額”自動篩選條件 單擊“存款銀行”右側的下拉箭頭按鈕，在下拉列表框中選擇“文本篩選”|“自定義篩選”項，打開“自定義自動篩選方式”對話框，按圖2.19所示進行設置，單擊“確定”按鈕。此時，在工作表中顯示的是“金額”大于20030且存款銀行為“中國銀行”或“工商銀行”的記錄，篩選結果如圖2.20所示。圖2.19設置“存款銀行”自動篩選條件圖2.20自動篩選結果示例篩選結束后，“金額”和“存款銀行”字段名后的下拉箭頭按鈕變為漏斗形狀，這兩條記錄的行號也變為藍色，如圖2.20所示，說明這些字段及記錄是經過自動篩選的。思考：在設置“存款銀行”為“工商銀行”和“中國銀行”這兩個條件時，為什么選

15、擇的是“或”關系，而不是“與”關系？2) 高級篩選（1） 操作要求將Sheet4工作表重命名為“高級篩選”。在工作表中對“本息”和“存期”進行高級篩選。篩選條件為：“本息=20030”并且“存期=20030”，單元格B20中輸入“3”，單元格B21中輸入“=5（參見圖2.22所示）。注意：篩選條件在同一行表示“與”的關系，在同一列不同行表示“或”的關系。 插入點定位在數據清單任一位置，在“數據”|“排序和篩選”功能組中，單擊“高級”命令，打開“高級篩選”對話框。 設置“列表區域”、“條件區域”和結果“復制到”的內容。設置“列表區域”時，可以直接輸入區域$A$2:$H$14，或直接在數據清單中選

16、擇數據區域。用同樣的方法設置“條件區域”和結果“復制到”的位置，如圖2.21所示。提示：在填寫條件區域時，條件字段名最好采用復制、粘貼的方式填寫，確保與數據清單中的字段名格式保持一致。 單擊“確定”按鈕執行高級篩選操作，篩選結果如圖2.22所示。圖2.21“高級篩選”對話框條件區域高級篩選結果圖2.22高級篩選示例4. 分類匯總操作1) 操作要求將工作表Sheet5重命名為“分類匯總”，并按不同的“存期”對“金額”和“本息”進行匯總。2) 操作方法（1） 雙擊Sheet5標簽，輸入“分類匯總”，然后單擊工作表中的任意單元格，或按回車鍵，完成對工作表的重命名。（2） 將插入點定位在“存期”列中的

17、任一單元格，在“數據”|“排序和篩選”功能組中，單擊“排序”按鈕（升序，也可單擊“降序”按鈕）。（3） 在“數據”|“分級顯示”功能組中，單擊“分類匯總”命令按鈕，打開“分類匯總”對話框。（4） 在“分類匯總”對話框中設置參數。“分類字段”：從下拉列表框中選擇“存期”（排序字段）；“匯總方式”：從下拉列表框中選擇“求和”； “選定匯總項”：從下拉列表框中選中“金額”和“本息”復選框；其他參數可取系統默認的設置，如圖2.23所示。圖2.23“分類匯總”對話框（5） 單擊“確定”按鈕，完成分類匯總操作，其結果如圖2.24所示。圖2.24“分類匯總”結果示例(三) 綜合實例操作在桌面上新建一個文件夾

18、，以你的班級、姓名和學號命名，在該文件夾中新建一個Excel文件（即工作簿），并將文件重命名為“期末成績統計表.xlsx”。按要求輸入表格中的數據，設計表格的版式，制作統計圖表，并對數據進行管理。 將綜合實例的操作步驟和操作過程的心得體會寫入實驗報告，將綜合實例的最終效果截圖粘貼到實驗報告。操作要求：1. 工作表的編輯和排版(1) 輸入表格的標題和字段名。字段名包括學號、姓名、性別、課程名6門(如高等數學、英語、大學物理等)、平均分、總分和成績排名。(2) 輸入數據，數據內容自由發揮，如“201500123456，張三，男，90,78,82,98,76,88”。注意：“平均分”、“總分”和“成績排名”不用輸入。(3) 分別使用函數計算每