1、實(shí)驗(yàn)二 Excel 2010的高級(jí)操作一、 實(shí)驗(yàn)?zāi)康耐ㄟ^(guò)一系列的操作實(shí)驗(yàn)，要求學(xué)生熟練掌握電子表格的常用操作。1 熟練掌握Excel 2010中圖表的制作、更改、修飾等操作。2 熟練掌握Excel 2010中數(shù)據(jù)的排序、篩選、分類匯總等數(shù)據(jù)管理和分析。3 熟練掌握Excel 2010的綜合應(yīng)用。二、實(shí)驗(yàn)內(nèi)容1. 圖表的創(chuàng)建和修飾操作。2. 數(shù)據(jù)的排序操作。3. 數(shù)據(jù)的自動(dòng)篩選和高級(jí)篩選操作。4. 數(shù)據(jù)的分類匯總操作。5. 綜合實(shí)例操作。三、實(shí)驗(yàn)步驟(一) 圖表的創(chuàng)建和修飾操作1. 創(chuàng)建簇狀柱形圖工作表打開工作簿“cunkuan.xls”，利用“家庭存款帳本”工作表中的數(shù)據(jù)建立一個(gè)“簇狀柱形圖”

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

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

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

5、入 “（元）”；單擊“圖例”按鈕，選擇“在頂部顯示圖例”，即在圖表上方創(chuàng)建圖例標(biāo)簽。（如果不需要圖例，單擊“圖例”按鈕，選擇“無(wú)”即可。）（7） 在“圖表工具”“設(shè)計(jì)”功能區(qū)中單擊“移動(dòng)圖表”命令按鈕，彈出“移動(dòng)圖表”對(duì)話框，選擇“新工作表”單選按鈕，并在對(duì)應(yīng)的文本框中輸入“金額、本息對(duì)照?qǐng)D”，如圖2.8所示。（8） 單擊“確定”按鈕，完成圖表工作表的創(chuàng)建，結(jié)果如圖2.1所示。（9） 單擊“文件”“保存”按鈕，或者單擊“快速訪問(wèn)工具欄”中的“保存”按鈕，將圖表工作表保存在當(dāng)前工作薄中。圖2.7創(chuàng)建圖表標(biāo)簽示例圖2.8移動(dòng)圖表對(duì)話框2. 設(shè)置圖表工作表格式對(duì)“金額、本息對(duì)照?qǐng)D”工作表進(jìn)行格式設(shè)置

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

7、號(hào)字、藍(lán)色、加粗； 單擊圖例，在“開始”|“字體”功能組中設(shè)置字符格式為楷體、16號(hào)字；雙擊圖例，打開“設(shè)置圖例項(xiàng)格式”對(duì)話框，單擊“邊框顏色”選項(xiàng)卡，設(shè)置邊框顏色為“無(wú)線條”； 單擊分類軸標(biāo)題“存款日期”，在“開始”|“字體”功能組中設(shè)置字符格式為楷體、16號(hào)字、淺藍(lán)、加粗； 單擊Y坐標(biāo)軸標(biāo)題“（元）”，在“開始”|“字體”功能組中設(shè)置字符格式為楷體、16號(hào)字、淺藍(lán)、加粗；雙擊Y坐標(biāo)軸標(biāo)題“（元）”，打開“設(shè)置坐標(biāo)軸標(biāo)題格式”對(duì)話框，選擇“對(duì)齊方式”選項(xiàng)卡，設(shè)置文字方向?yàn)椋骸皺M排”，垂直對(duì)齊方式為：“中部居中”，并將標(biāo)題“（元）”拖到Y(jié)坐標(biāo)軸上方的合適位置。（2） 分類軸格式設(shè)置 單擊X軸分

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

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

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

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

12、從“次要關(guān)鍵字”區(qū)域下拉列表框中選擇“存款日期”，從“排序依據(jù)”區(qū)域下拉列表框中選擇“數(shù)值”，從“次序”區(qū)域下拉列表框中選擇“降序”；勾選“數(shù)據(jù)包含標(biāo)題”復(fù)選框（如不勾選該復(fù)選框，系統(tǒng)會(huì)把字段名混同數(shù)據(jù)一起排序），如圖2.15所示。圖2.15 “排序”對(duì)話框（4） 單擊“確定”按鈕，進(jìn)行排序，排序結(jié)果如圖2.16所示。圖2.16排序結(jié)果示例提示：當(dāng)對(duì)選中數(shù)據(jù)區(qū)域中的任意單元格進(jìn)行排序時(shí)，系統(tǒng)默認(rèn)選中所有數(shù)據(jù)作為排序?qū)ο蟆Ｈ绻粚?duì)部分?jǐn)?shù)據(jù)排序，應(yīng)預(yù)先選擇待排序的數(shù)據(jù)區(qū)域，然后再執(zhí)行排序命令。3. 自動(dòng)篩選和高級(jí)篩選操作1) 自動(dòng)篩選（1） 操作要求將Sheet2工作表重命名為“自動(dòng)篩選”，然后自

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

14、 設(shè)置“金額”自動(dòng)篩選條件 單擊“存款銀行”右側(cè)的下拉箭頭按鈕，在下拉列表框中選擇“文本篩選”|“自定義篩選”項(xiàng)，打開“自定義自動(dòng)篩選方式”對(duì)話框，按圖2.19所示進(jìn)行設(shè)置，單擊“確定”按鈕。此時(shí)，在工作表中顯示的是“金額”大于20030且存款銀行為“中國(guó)銀行”或“工商銀行”的記錄，篩選結(jié)果如圖2.20所示。圖2.19設(shè)置“存款銀行”自動(dòng)篩選條件圖2.20自動(dòng)篩選結(jié)果示例篩選結(jié)束后，“金額”和“存款銀行”字段名后的下拉箭頭按鈕變?yōu)槁┒沸螤睿@兩條記錄的行號(hào)也變?yōu)樗{(lán)色，如圖2.20所示，說(shuō)明這些字段及記錄是經(jīng)過(guò)自動(dòng)篩選的。思考：在設(shè)置“存款銀行”為“工商銀行”和“中國(guó)銀行”這兩個(gè)條件時(shí)，為什么選

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

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

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

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