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

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

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

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

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

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

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

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

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

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

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

11、地址則固定不變，從而得到正確的結(jié)果。圖1.24 絕對(duì)引用獲得正確結(jié)果第二種方法：?jiǎn)螕鬎10單元格，輸入“=”；單擊E3，在編輯欄公式后輸入“/”號(hào)，再?gòu)淖髠?cè)函數(shù)列表中選SUM函數(shù)；圖1.25 調(diào)用SUM函數(shù)按確定按鈕后，把編輯欄內(nèi)公式改為“=E3/SUM($E$3:$E$9)”；圖1.26 SUM函數(shù)參數(shù)采用絕對(duì)引用 單擊格式工具欄上的“%”百分比樣式按鈕，數(shù)值變成百分比樣式。 向下拖動(dòng)填充柄直至F9單元格，完成計(jì)算。注意，因?yàn)橄蛳峦蟿?dòng)填充柄時(shí)列不發(fā)生變化，所以SUM的計(jì)算區(qū)域也可以寫成SUM(E$3:E$9)這種混合引用的模式。圖1.27 SUM函數(shù)參數(shù)采用混合引用實(shí)驗(yàn)1.4 圖表制作【實(shí)驗(yàn)

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

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

14、算機(jī)優(yōu)秀而物理不及格的記錄?！緦?shí)驗(yàn)步驟】1自動(dòng)篩選。 執(zhí)行“數(shù)據(jù)/篩選/自動(dòng)篩選”，在列標(biāo)題旁出現(xiàn)小箭頭，單擊“計(jì)算機(jī)”字段旁的小箭頭，在出現(xiàn)的下拉列表中選“自定義”； 按圖1.36填寫篩選條件，按確定按鈕，即可獲得結(jié)果。圖1.36 填寫篩選條件2高級(jí)篩選圖1.37 學(xué)生成績(jī)?cè)紨?shù)據(jù)表 先在工作表空白處建立篩選條件區(qū)，如圖1.38，條件為“與”的關(guān)系；圖1.38 高級(jí)篩選的篩選條件區(qū)，條件“與”圖1.39 確定高級(jí)篩選的3個(gè)區(qū)域 光標(biāo)放到數(shù)據(jù)清單區(qū)中任一格； 執(zhí)行“數(shù)據(jù)/篩選/高級(jí)篩選”，如圖1.39選擇“條件區(qū)域”和“復(fù)制到”的位置；（注：因?yàn)槭孪炔⒉磺宄Y選出來的記錄有多少，所以復(fù)制到的位

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

16、，按確定按鈕。圖1.42 分類匯總的各個(gè)選項(xiàng) 最終得到結(jié)果如圖1.43。分類匯總表左上角按鈕123可以折疊分類的明細(xì)項(xiàng)。 如果想取消分類匯總數(shù)據(jù)，可以在圖1.42的分類匯總對(duì)話框中按全部刪除按鈕，即可使數(shù)據(jù)還原。圖1.43 分類匯總結(jié)果*實(shí)驗(yàn)1.7 數(shù)據(jù)透視表的制作【實(shí)驗(yàn)內(nèi)容與要求】根據(jù)圖1.37學(xué)生成績(jī)?cè)紨?shù)據(jù)表的內(nèi)容，求各班英語、高數(shù)、物理和化學(xué)各科目的平均成績(jī)，制作出如圖1.44和圖1.45所示的數(shù)據(jù)透視表?！緦?shí)驗(yàn)步驟】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、導(dǎo) - 3步驟之1，按下一步按鈕；圖1.46 數(shù)據(jù)透視表向?qū)?- 3步驟之1 彈出如下數(shù)據(jù)透視表制作向?qū)?-3步驟之2，選定數(shù)據(jù)區(qū)，按下一步按鈕；圖1.47 數(shù)據(jù)透視表向?qū)?- 3步驟之2 彈出如下數(shù)據(jù)透視表制作向?qū)?-3步驟之3，按布局按鈕；圖1.48 數(shù)據(jù)透視表向?qū)?- 3步驟之3 如下圖，把各個(gè)字段按鈕拖放到圖中相應(yīng)位置；圖1.48 數(shù)據(jù)透視表布局 雙擊上圖中求和項(xiàng)：英語按鈕，彈出如下對(duì)話框，在“匯總方式”欄中選擇“平均值”，按數(shù)字按鈕可以設(shè)置數(shù)字格式，但也可以后再做調(diào)整；圖1.49 數(shù)據(jù)透視表字段匯總方式 其它求和項(xiàng)均依此辦理，按確定按鈕后，即得到如下數(shù)據(jù)透視表。圖1.50 完成的數(shù)據(jù)透視表2數(shù)據(jù)透視表二的制作。在上一個(gè)數(shù)據(jù)透視表的基礎(chǔ)上，右擊鼠標(biāo)，在菜單中選“顯示數(shù)據(jù)透視表工具欄”，調(diào)出該工具欄，按“設(shè)置報(bào)告格式”按鈕；圖1.51 數(shù)據(jù)透視表工具欄 在如下“自動(dòng)套用格式”對(duì)話框中選“報(bào)表1”，按確定按鈕。圖1.52 數(shù)