第1次上機課(Excel基本操作)

1、精選優(yōu)質文檔-傾情為你奉上實驗 Excel電子表格制作【實驗目的】1. 掌握Excel文件的建立、保存與打開。2. 掌握工作表的選擇、添加、刪除、重命名、復制與移動。3. 掌握單元格的輸入、編輯、刪除、修改、插入、復制與移動。4. 掌握工作表的修飾、公式與函數(shù)的應用（重點掌握）。5. 掌握文本的版面格式設計。6. 掌握工作表的高級應用實驗1.1 工作表的基本操作【實驗內容與要求】1. 創(chuàng)建如圖1.1內容的工作表文件。圖1.1 工作簿文件內容2. 在A1單元格輸入標題“部分城市降水量”，在A2單元格輸入“城市”。3.在B2單元格輸入“第一季”后，拖動填充柄填充C2F2中的內容。其余單元格按所給內

2、容輸入。4. 將A1G1合并及居中，表格自動套用格式為“彩色2”，為數(shù)據(jù)清單加“粗外邊框、細內邊框”。5. 標題字體格式設置為：楷體_GB2312、14磅、加粗、淺青綠色。6. 工作表sheet1重命名為“降水量表”。7. 利用函數(shù)計算北京市的年總降水量和季平均降水量。（均保留小數(shù)點后兩位小數(shù)）；拖動填充柄填充F4F7和G4G7單元格中的內容。8. 按“年總量”列數(shù)值降序排序。9. 第二行表頭區(qū)設文字水平居中，字體為粗體。10. B3E7數(shù)據(jù)設條件格式，數(shù)值65的設為紅色、粗體。拖動方向圖1.2 填充柄【實驗步驟】1. 數(shù)據(jù)填充。用鼠標指針指向填充柄，光標變?yōu)榧毷郑蟿邮髽思纯?，如圖1.2所

3、示。 2. 合并及居中。鼠標拖過A1G1，然后按格式工具欄上的“合并及居中”按鈕（如圖1.3所示）；或者執(zhí)行“格式/單元格”對齊選項卡命令，按圖1.4設置。3. 表格自動套用格式。執(zhí)行“格式/自動套用格式”命令，在圖1.6對話框中單擊“彩色2”，按確定按鈕。圖1.3合并及居中操作圖1.4 合并及居中設置圖1.5 字體格式設置4. 標題格式設置。在圖1.5中字體 選項卡中進行各種選擇設置。5. 計算年總量。單擊F3單元格，執(zhí)行“插入/函數(shù)”命令，在圖1.7插入函數(shù)對話框中單擊SUM求和函數(shù)，確定后，用鼠標拖選計算范圍，如圖1.7所示，回車或按編輯欄上綠色的對號，完成F3單元格的計算。向下拖動填充

4、柄直至F7單元格。圖1.6 表格“自動套用格式”圖1.7 插入函數(shù)注：常用函數(shù)可以用“常用”工具欄上的按鈕及旁邊的小箭頭調出，也可以單擊編輯欄左側的fx按鈕調出“插入函數(shù)”對話框。圖1.8 求和函數(shù)的用法6.計算季均量。單擊G3單元格，仿照年總量的計算方法，此次函數(shù)要選AVERAGE。7.工作表中數(shù)據(jù)區(qū)加表格線。用鼠標選取A1G7區(qū)域，執(zhí)行“格式/單元格”命令調出如圖1.10邊框 選項卡；在“線條樣式”區(qū)單擊粗線，在“預置”區(qū)單擊“外邊框”；在“線條樣式”區(qū)單擊細線，在“預置”區(qū)單擊“內部”。8.排序。單擊數(shù)據(jù)區(qū)中任一單元格，執(zhí)行“數(shù)據(jù)/排序”命令，按圖1.11 排序對話框設置，主要關鍵字選“

5、年總量”，降序。圖1.9 求平均值函數(shù)的用法圖1.10 數(shù)據(jù)區(qū)加邊框圖1.11 排序對話框圖1.12 最終完成結果圖實驗1.2 公式和函數(shù)的靈活運用【實驗內容與要求】完成下表計算內容。圖1.13 計算用表及完成結果1. 用函數(shù)計算成績列的結果，四個科目的平均分數(shù)60為通過，否則為不通過。2. 用函數(shù)統(tǒng)計不及格的人數(shù)。3. 用函數(shù)計算及格率，及格率=及格的人數(shù)/總人數(shù)。4. 用百分比表示及格率，保留小數(shù)1位。5. 工作表標簽重命名為“數(shù)據(jù)1”。【實驗步驟】 建立圖1.13成績表，其中F3F9、A10E11區(qū)域空白，需計算得出。圖1.14 IF函數(shù)的參數(shù) 成績列的計算。根據(jù)所給條件：如果“四個科目

6、的平均分數(shù)60為通過，否則為不通過”，選擇函數(shù)IF，計算公式為：“=IF(AVERAGE(B3:E3)<60,"未通過","通過")”。操作方法：單擊F3單元格，直接輸入公式即可，或者采用如下步驟。 單擊F3單元格；， 執(zhí)行“插入/函數(shù)”命令，從圖1.7“插入函數(shù)”對話框中選擇IF函數(shù)；IF函數(shù)有3項參數(shù)：一是判斷條件，二是條件為真時的取值，三是條件為假時的取值， 依次按圖所圖1.14所示填寫； 在Logical_test一欄中，光標插入<60之前，再按編輯欄×號左側的箭頭，選擇AVERAGE函數(shù)，如圖1.15；圖1.15 IF函數(shù)

7、嵌套AVERAGE函數(shù)的操作方法 AVERAGE函數(shù)參數(shù)選擇可以輸入或用鼠標拖選，如圖1.16，按確定；圖1.16 AVERAGE函數(shù)的參數(shù) 拖動F3單元格的填充柄向下至F9，成績計算完畢。 統(tǒng)計不及格的人數(shù)。此為條件統(tǒng)計，需要使用統(tǒng)計類函數(shù)中的COUNTIF函數(shù)。計算公式為“=COUNTIF(B3:B9,"<60")”。操作方法： 單擊B10單元格；， 執(zhí)行“插入/函數(shù)”命令，從圖1.7“插入函數(shù)”對話框中選擇COUNTIF函數(shù)； 按下圖填寫參數(shù)，按確定按鈕。圖1.17 COUNTIF函數(shù)的參數(shù) 計算及格率。及格率=及格的人數(shù)/總人數(shù)，需要使用函數(shù)COUNTIF統(tǒng)計

8、及格總人數(shù)和函數(shù)COUNT統(tǒng)計總人數(shù)。計算公式為“=COUNTIF(B3:B9,">=60")/COUNT(B3:B9)”。操作方法： 單擊B11單元格； 執(zhí)行“插入/函數(shù)”命令，從圖1.7“插入函數(shù)”對話框中選擇COUNTIF函數(shù)； 仿照圖1.17示例，第二行參數(shù)改為“>=60”，按確定按鈕完成公式第一部分； 在編輯欄公式后輸入“/”號，再從左側函數(shù)列表中選COUNT函數(shù)；圖1.18 選擇COUNT函數(shù) 填寫參數(shù)如下圖，或用鼠標拖選要計算的區(qū)域，按確定完成整個計算。圖1.19 COUNT函數(shù)的參數(shù) 百分比樣式。執(zhí)行“格式/單元格”命令，在數(shù)字選項卡中設置。也可

9、以按格式工具欄上的“%”百分比樣式按鈕和增加 | 減少小數(shù)位數(shù)按鈕進行處理。圖1.20 設定百分比格式 拖動B11單元格的填充柄向右至E11，及格率計算完畢。 工作表改名。執(zhí)行“格式/工作表/重命名”，鍵入新的名稱即可。更簡單的方法是雙擊工作表標簽，直接更名。最終完成效果如圖1.13。實驗1.3 絕對引用和混合引用【實驗內容與要求】完成下表的計算，學習絕對引用的概念和方法。前面已經(jīng)熟悉了對單元格地址的相對引用方式，可以非常方便地完成大多數(shù)自動填充的計算任務，不過有時需要從固定的位置取數(shù)據(jù)，就需要用到絕對引用。任務：利用公式計算出各類項目的百分比，百分比=差額/各項目總差額。（格式為百分比樣式，

10、保留小數(shù)點后兩位小數(shù)）。圖1.21 計算用表【實驗步驟】第一種方法：單擊E10單元格，用SUM函數(shù)計算總差額；單擊F10單元，輸入“=”，單擊E3，輸入“/”，單擊E10，若此時結束輸入，圖1.22 輸入公式圖1.23 向下拖動填充柄出現(xiàn)相對引用錯誤（除數(shù)為零）F3單元格可以得到正確結果，但用填充柄向下拖動則回出現(xiàn)“#DIV/0!”錯誤，這是因為填充公式引用了E10以下單元格的地址，產(chǎn)生了被0除的錯誤。我們希望填充柄向下拖動時對E10單元格的引用能夠固定，解決的方法是將公式改寫為“=E3/$E$10”，$E$10這種寫法為絕對應用。在公式中相對引用的單元格地址隨填充柄的拖動而改變，而絕對引用的

11、地址則固定不變，從而得到正確的結果。圖1.24 絕對引用獲得正確結果第二種方法：單擊F10單元格，輸入“=”；單擊E3，在編輯欄公式后輸入“/”號，再從左側函數(shù)列表中選SUM函數(shù)；圖1.25 調用SUM函數(shù)按確定按鈕后，把編輯欄內公式改為“=E3/SUM($E$3:$E$9)”；圖1.26 SUM函數(shù)參數(shù)采用絕對引用 單擊格式工具欄上的“%”百分比樣式按鈕，數(shù)值變成百分比樣式。 向下拖動填充柄直至F9單元格，完成計算。注意，因為向下拖動填充柄時列不發(fā)生變化，所以SUM的計算區(qū)域也可以寫成SUM(E$3:E$9)這種混合引用的模式。圖1.27 SUM函數(shù)參數(shù)采用混合引用實驗1.4 圖表制作【實驗

12、內容與要求】按圖1.28表格內容制作成績分布圖和平均成績分布圖，圖表樣式為簇狀柱型圖。圖1.28 成績分布圖【實驗步驟】1成績分布圖。選取數(shù)據(jù)區(qū)域B2:E9，執(zhí)行“插入/圖表”命令，按圖表向導指引逐步完成圖表的制作。完成效果見圖1.28成績分布圖。2平均成績分布圖制作方法： 用鼠標選取B2:E2區(qū)域，按住Ctrl再鼠標選取B10:E10區(qū)域；圖1.29 選取數(shù)據(jù)區(qū)域 執(zhí)行“插入/圖表”命令，如圖1.30選取簇狀柱型圖，按下一步按鈕； 選擇系列產(chǎn)生在“行”，如圖1.31，按下一步按鈕； 如圖1.32填寫標題信息，按下一步按鈕； 圖1.33數(shù)據(jù)標志顯示為“值”，按下一步按鈕； 在“圖表向導- 4

13、步驟之 4 圖表位置”對話框中選“作為其中的對象插入”； 按完成按鈕，得到圖1.34平均成績分布圖。用鼠標拖拽坐標軸文字“科目”和“分數(shù)”到相應位置； Y軸坐標文字“分數(shù)”需要順時針旋轉90度，在文字“分數(shù)”上右擊鼠標，在菜單中選“圖表標題格式”，出現(xiàn)對話框，選“對齊選項卡”，調整“方向”為0度。圖1.30 簇狀柱型圖圖1.31 系列產(chǎn)生在行圖1.32 填寫標題信息圖1.33 數(shù)據(jù)標志顯示為“值”圖1.34 平均成績分布圖實驗1.5 數(shù)據(jù)的自動篩選和高級篩選【實驗內容與要求】圖1.35 自動篩選1按圖1.37所給樣表，用自動篩選方法查找出計算機優(yōu)秀和不及格的學生的記錄。2用高級篩選方法查找出計

14、算機優(yōu)秀而物理不及格的記錄?！緦嶒灢襟E】1自動篩選。 執(zhí)行“數(shù)據(jù)/篩選/自動篩選”，在列標題旁出現(xiàn)小箭頭，單擊“計算機”字段旁的小箭頭，在出現(xiàn)的下拉列表中選“自定義”； 按圖1.36填寫篩選條件，按確定按鈕，即可獲得結果。圖1.36 填寫篩選條件2高級篩選圖1.37 學生成績原始數(shù)據(jù)表 先在工作表空白處建立篩選條件區(qū)，如圖1.38，條件為“與”的關系；圖1.38 高級篩選的篩選條件區(qū)，條件“與”圖1.39 確定高級篩選的3個區(qū)域 光標放到數(shù)據(jù)清單區(qū)中任一格； 執(zhí)行“數(shù)據(jù)/篩選/高級篩選”，如圖1.39選擇“條件區(qū)域”和“復制到”的位置；（注：因為事先并不清楚篩選出來的記錄有多少，所以復制到的位

15、置并不需要用鼠標精確框選，只需要確定放置位置的左上角單元格就可以了。） 按確定按鈕完成。高級篩選可以完成比較復雜的或用自動篩選無法完成的篩選任務。圖1.40 高級篩選完成結果（計算機優(yōu)秀而物理不及格）實驗1.6 分類匯總【實驗內容與要求】根據(jù)圖1.37實驗數(shù)據(jù)樣表，分別求出各班學生各個科目的平均成績。本實驗需要采用分類匯總的方法完成。分類匯總前需要先按分類字段排序，然后才能進行數(shù)據(jù)的處理。操作步驟如下：【實驗步驟】 光標放置于欲排序列中任一單元格，執(zhí)行“數(shù)據(jù)/排序”，選擇排序字段；圖1.41 分類匯總前先按分類字段排序 排序后，再執(zhí)行“數(shù)據(jù)/分類匯總”，在對話框中選擇分類字段、匯總方式和匯總項

16、，按確定按鈕。圖1.42 分類匯總的各個選項 最終得到結果如圖1.43。分類匯總表左上角按鈕123可以折疊分類的明細項。 如果想取消分類匯總數(shù)據(jù)，可以在圖1.42的分類匯總對話框中按全部刪除按鈕，即可使數(shù)據(jù)還原。圖1.43 分類匯總結果*實驗1.7 數(shù)據(jù)透視表的制作【實驗內容與要求】根據(jù)圖1.37學生成績原始數(shù)據(jù)表的內容，求各班英語、高數(shù)、物理和化學各科目的平均成績，制作出如圖1.44和圖1.45所示的數(shù)據(jù)透視表?！緦嶒灢襟E】1數(shù)據(jù)透視表一的制作。 選取要制作數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域； 執(zhí)行“數(shù)據(jù)/數(shù)據(jù)透視表和數(shù)據(jù)透視圖”；圖1.44 數(shù)據(jù)透視表一圖1.45 數(shù)據(jù)透視表二 彈出如下數(shù)據(jù)透視表制作向

17、導 - 3步驟之1，按下一步按鈕；圖1.46 數(shù)據(jù)透視表向導 - 3步驟之1 彈出如下數(shù)據(jù)透視表制作向導 -3步驟之2，選定數(shù)據(jù)區(qū)，按下一步按鈕；圖1.47 數(shù)據(jù)透視表向導 - 3步驟之2 彈出如下數(shù)據(jù)透視表制作向導 -3步驟之3，按布局按鈕；圖1.48 數(shù)據(jù)透視表向導 - 3步驟之3 如下圖，把各個字段按鈕拖放到圖中相應位置；圖1.48 數(shù)據(jù)透視表布局 雙擊上圖中求和項：英語按鈕，彈出如下對話框，在“匯總方式”欄中選擇“平均值”，按數(shù)字按鈕可以設置數(shù)字格式，但也可以后再做調整；圖1.49 數(shù)據(jù)透視表字段匯總方式 其它求和項均依此辦理，按確定按鈕后，即得到如下數(shù)據(jù)透視表。圖1.50 完成的數(shù)據(jù)透視表2數(shù)據(jù)透視表二的制作。在上一個數(shù)據(jù)透視表的基礎上，右擊鼠標，在菜單中選“顯示數(shù)據(jù)透視表工具欄”，調出該工具欄，按“設置報告格式”按鈕；圖1.51 數(shù)據(jù)透視表工具欄 在如下“自動套用格式”對話框中選“報表1”，按確定按鈕。圖1.52 數(shù)