《常用辦公軟件（第四版）》 課件 項目四 Excel 2021 的使用

04Excel2021的使用192任務1制作“2022年臺歷”——Excel2021界面組成及模板應用任務2創(chuàng)建“員工信息登記表”——Excel2021的文件操作及數(shù)據(jù)錄入任務3美化“員工信息登記表”——表格的格式設置任務4對“員工信息登記表”進行驗證設置——表格的數(shù)據(jù)驗證設置193任務5利用“員工信息登記表”創(chuàng)建相似工作表——工作表的插入、刪除、重命名、移動和復制任務6編輯“畢業(yè)生抽樣調(diào)查表”——數(shù)據(jù)的編輯和修改任務7制作“學生成績統(tǒng)計表”——公式、函數(shù)、自動填充功能任務8制作“技能競賽成績表”——數(shù)據(jù)表格的排序194任務9突顯“校園歌手大獎賽歌手得分統(tǒng)計表”中不同分數(shù)段成績——條件格式的應用任務10打印“校園歌手大獎賽歌手得分統(tǒng)計表”——頁面設置及打印任務11利用圖表分析校園歌手大獎賽成績——圖表的創(chuàng)建、編輯與打印任務12篩選分析學生考試成績——自動篩選和高級篩選195任務13匯總分析學生考試成績——數(shù)據(jù)的分類匯總?cè)蝿?4制作“電器銷售情況統(tǒng)計表”數(shù)據(jù)匯總表——合并計算及保護工作表任務15制作校園歌手大賽獲獎證書——郵件合并任務16分析技能競賽成績數(shù)據(jù)——數(shù)據(jù)透視表的使用任務17“產(chǎn)品銷售情況”模擬分析——單變量求解、模擬運算表、方案管理器196制作“2022年臺歷”——Excel2021界面組成及模板應用任務1197學習目標知識目標：1.了解Excel2021界面組成及其具有的功能。2.了解Excel2021模板應用。3.了解Excel的常用術(shù)語。技能目標：1.能完成Excel2021的啟動與保存操作。2.能完成Excel2021模板的創(chuàng)建與修改操作。198任務描述本任務在學習Excel2021模板應用方法的基礎上，完成以下案例的制作。某公司在2021年末需要為每位員工制作一本2022年臺歷，要求利用Excel2021模板快速完成2022年臺歷的制作，2022年1月份臺歷的完成效果如圖所示。1992002022年1月份臺歷的完成效果相關知識一、Excel2021操作界面的組成啟動Excel2021后，自動創(chuàng)建一個名為“工作簿1”的空白表格文件，其操作界面如圖所示，由快速訪問工具欄、選項卡、標題欄、功能區(qū)、編輯欄、工作表編輯區(qū)、工作表標簽、行號、列號、狀態(tài)欄等部分組成。201202Excel2021的操作界面1.快速訪問工具欄該工具欄位于工作界面的左上角，包含一組用戶使用頻率較高的工具，如“保存”“撤銷”和“恢復”。用戶可單擊“快速訪問工具欄”右側(cè)的下三角形按鈕，在展開的列表中選擇要在其中顯示或隱藏的工具按鈕。2.選項卡與功能區(qū)選項卡與功能區(qū)位于標題欄的下方，Excel2021將用于處理數(shù)據(jù)的所有命令組織在不同的選項卡中。單擊不同的選項卡標簽可切換功能區(qū)中顯示的工具命令。在每個選項卡中，命令又被分類放置在不同的組中。組的右下角通常會有一個對話框啟動器按鈕，用于打開與該組命令相關的對話框，以便用戶對要進行的操作做進一步的設置。2033.編輯欄編輯欄主要用于輸入和修改活動單元格中的數(shù)據(jù)。在工作表的某個單元格中輸入數(shù)據(jù)時，編輯欄會同步顯示輸入的內(nèi)容。4.工作表編輯區(qū)工作表編輯區(qū)用于顯示或編輯工作表中的數(shù)據(jù)。5.工作表標簽工作表標簽位于工作簿窗口的左下角，默認名稱為Sheet1，單擊Sheet1右側(cè)的按鈕

可創(chuàng)建新的工作表，默認名稱為Sheet2，以此類推，可創(chuàng)建多個工作表，單擊不同的工作表標簽可在工作表之間進行切換。204二、Excel常用術(shù)語1.單元格單元格是Excel工作簿的最小組成單位，所有數(shù)據(jù)都存儲在單元格中。工作表編輯區(qū)中每一個長方形的小格就是一個單元格，每一個單元格都可以用其所在的行號和列號進行標識，如A1單元格表示位于A列第1行的單元格。2052.工作表工作表是顯示在工作簿窗口中由行和列構(gòu)成的表格。它主要由單元格、行號、列號和工作表標簽等組成。行號顯示在工作簿窗口的左側(cè)，依次用數(shù)字1、2、…、1048576表示；列號顯示在工作簿窗口的上方，依次用字母A、B、…、XFD表示。3.工作簿一個Excel文件就是一個工作簿，工作簿由若干工作表組成。默認情況下包含一個工作表，用戶可以根據(jù)需要添加或刪除相應的工作表。2064.活動單元格活動單元格指當前正在操作的單元格。5.單元格區(qū)域單元格區(qū)域是指由若干單元格組合而成的一個范圍。在數(shù)據(jù)運算中，一個單元格區(qū)域通過它左上角單元格的坐標與右下角單元格的坐標來表示，中間用冒號作為分隔符。例如，A4：C8表示以A4單元格為左上角，以C8單元格為右下角的矩形所包圍的單元格區(qū)域。207三、Excel2021模板在Excel表格制作過程中，有時候需要套用模板來簡化操作流程，提高工作效率，還可以讓表格更有高級感。下面來了解一下Excel2021的模板應用。1.搜索模板（1）打開Excel2021中的“新建”窗口，如圖所示，在搜索欄中輸入想要的模板類型并單擊回車鍵，在下方會顯示搜索的模板樣式，如圖所示。208209Excel2021“新建”窗口Excel2021“搜索”模板效果（2）單擊所選擇的模板，出現(xiàn)“創(chuàng)建”模板窗口，如圖所示，然后單擊“創(chuàng)建”按鈕，就可以直接在Excel2021中使用這個模板了。210“創(chuàng)建”模板窗口2.插入模板單擊“插入”選項卡下的“文本”按鈕，在下拉列表中選擇“對象”命令，如圖所示，打開“對象”對話框，如圖所示，單擊“由文件創(chuàng)建”選項卡，單擊“瀏覽”按鈕，打開“瀏覽”對話框，如圖所示，選擇模板文件，單擊“確定”按鈕即可插入模板。211插入“對象”操作212“對象”對話框“瀏覽”對話框3.制作模板新建一個Excel2021工作簿，在一個工作表中編輯模板內(nèi)容，可以添加表格樣式、單元格格式以及編輯公式等，然后單擊“保存”按鈕，選擇保存類型為“Excel模板”即可。這樣就制作好一個Excel2021模板了，需要使用它時，直接打開該模板后編輯即可。213創(chuàng)建“員工信息登記表”——Excel2021的文件操作及數(shù)據(jù)錄入任務2214學習目標知識目標：1.了解Excel2021操作界面的組成及其功能。2.了解Excel2021常用名詞術(shù)語及數(shù)字的格式。技能目標：1.能在Excel中完成新建、保存、關閉、退出、打開等操作。2.能對Excel文件進行加密。215任務描述本任務在學習Excel2021文件操作及數(shù)據(jù)錄入方法的基礎上，完成以下案例的制作。某公司人力資源部門需要設計制作一個表格，對公司員工信息進行登記，以便于人員管理，要求按照表格項目錄入公司員工的各項信息，并將文件加密，設置密碼為“147258”，“員工信息登記表”效果如圖所示。216217“員工信息登記表”效果相關知識一、文件操作在使用Excel2021時，經(jīng)常會對文件進行基本操作，如文件的啟動、打開、新建、保存、關閉、退出等，下面介紹Excel文件的操作方法。1.文件的啟動在桌面上雙擊Excel2021圖標，或者單擊“開始”按鈕，在搜索欄中輸入“Excel”，打開Excel2021操作界面。此時會自動新建一個名為“工作簿1”的工作簿。2182192.文件的新建在Excel2021窗口中，單擊“文件”選項卡下的“新建”按鈕，在右側(cè)“新建”欄下選擇“空白工作簿”即可，也可使用快捷鍵Ctrl+N來完成文件的新建。3.文件的打開在Excel2021窗口中，單擊“文件”選項卡下的“打開”按鈕，在右側(cè)的“打開”欄下單擊“瀏覽”按鈕，彈出“打開”對話框，在對話框中選擇要打開文件的位置和文件名即可，也可使用快捷鍵Ctrl+O來完成。對于已有的Excel文件，直接雙擊文件圖標即可將其打開。2204.文件的保存在Excel2021窗口中，單擊“文件”選項卡下的“保存”命令或使用快捷鍵Ctrl+S來完成。如果要更改文件保存位置可單擊“文件”選項卡下的“另存為”命令，在右側(cè)選項中單擊“瀏覽”項，打開“另存為”對話框，在對話框中設置文件保存位置及文件名，單擊“保存”按鈕即可。5.文件的關閉在Excel2021窗口中，單擊“文件”選項卡下的“關閉”命令即可關閉當前編輯的文件，也可使用快捷鍵Ctrl+F4來完成。6.文件的退出在Excel2021窗口中，單擊窗口右上角上的

按鈕即可退出文件，也可使用快捷鍵Alt+F4來完成。二、數(shù)字格式數(shù)字格式是指同一數(shù)字的不同表達方式。例如，對于數(shù)字“159687”，如果用帶千位分隔符的形式表示則為“159，687”，如果保留兩位小數(shù)則變成“159，687.00”。雖然都是同一個數(shù)字，但可以用多種方式來表示，適用于不同場合的需要。Excel2021提供了多種數(shù)字格式。設置數(shù)字格式的步驟：選定數(shù)據(jù)區(qū)域，在“開始”選項卡下“數(shù)字”組中單擊右下角的對話框啟動器按鈕，打開“設置單元格格式”對話框，在對話框的“數(shù)字”選項卡中可以設置數(shù)字格式。該選項卡中提供了以下幾種數(shù)字格式。2212221.常規(guī)常規(guī)格式是Excel所應用的默認數(shù)字格式。該格式下，如因小數(shù)部分位數(shù)過多使單元格的寬度不夠顯示整個數(shù)字時，會對數(shù)字進行四舍五入，而對整數(shù)部分位數(shù)過多的，則使用科學記數(shù)法表示。2.數(shù)值數(shù)值格式用于數(shù)字的一般表示。用戶可以指定要使用的小數(shù)位數(shù)、是否使用千位分隔符以及如何顯示負數(shù)。3.貨幣貨幣格式用于表示貨幣值，并顯示貨幣符號。用戶可以指定要使用的小數(shù)位數(shù)、貨幣符號類型以及如何顯示負數(shù)。2234.會計專用會計專用格式也用于表示貨幣值，但是它會在一列中對齊貨幣符號和數(shù)字的小數(shù)點。5.時間和日期在時間和日期兩種格式下，可根據(jù)指定的類型和區(qū)域設置（國家/地區(qū)），將時間和日期序列號顯示為時間和日期值。以星號（*）開頭的時間格式受“控制面板”中指定的區(qū)域日期和時間設置的更改的影響。不帶星號的格式不受“控制面板”設置的影響。2246.百分比在百分比格式下，將數(shù)字以百分數(shù)形式顯示，可以指定要使用的小數(shù)位數(shù)。7.分數(shù)在分數(shù)格式下，根據(jù)所指定的分數(shù)類型以分數(shù)形式顯示數(shù)字。8.科學記數(shù)科學記數(shù)格式是以科學記數(shù)法顯示數(shù)字，在Excel2021中，“a×10b”表示為“aE+b”。例如，在2位小數(shù)的科學記數(shù)格式下，12345678901顯示為1.23E+10，即1.23×1010。用戶可以指定要使用的小數(shù)位數(shù)。2259.文本設為文本格式后，無論輸入的是數(shù)字還是其他字符，Excel2021都會將其視為文本。10.特殊在特殊格式中，根據(jù)區(qū)域設置的不同，可將數(shù)字顯示為各種特殊形式，如郵政編碼、中文小寫數(shù)字或中文大寫數(shù)字等。11.自定義使用自定義格式，可以在現(xiàn)有格式基礎上創(chuàng)建自定義數(shù)字格式，并將其添加到數(shù)字格式代碼列表中。美化“員工信息登記表”——表格的格式設置任務3226學習目標知識目標：了解Excel2021字符格式的設置方法。技能目標：1.能設置單元格內(nèi)容的顯示格式。2.能對表格完成對齊、更換字體、加邊框等基本設置。227任務描述本任務在學習Excel2021表格設置基礎上，完成以下案例的制作。在上一任務創(chuàng)建完成的員工信息登記表基礎上，進一步進行美化處理，要求將表格標題的單元格進行合并，并對表格內(nèi)的數(shù)據(jù)進行更改字體、字號及加邊框等設置，使表格看起來更加美觀、規(guī)范，“某公司員工信息登記表”效果如圖所示。228229“某公司員工信息登記表”效果相關知識在Excel2021中對表格格式進行設置，需要用到“設置單元格格式”對話框，在該對話框中可以設置數(shù)字、對齊、字體、邊框、填充、保護等。選擇需要設置格式的單元格或單元格區(qū)域，單擊右鍵打開快捷菜單，在彈出的快捷菜單中選擇“設置單元格格式”，如圖所示，打開“設置單元格格式”對話框，如圖所示。230231“設置單元格格式”對話框快捷菜單中的“設置單元格格式”“設置單元格格式”對話框包含六個選項卡。一、“數(shù)字”選項卡在“數(shù)字”選項卡中，分類列表中列出了常規(guī)、數(shù)值、貨幣、會計專用等12種數(shù)字格式，根據(jù)需要選擇一種數(shù)字格式，在右側(cè)區(qū)域內(nèi)設置其參數(shù)即可。232二、“對齊”選項卡“對齊”選項卡如圖所示，用于設置文本的對齊方式、文本控制、從右到左，還可以調(diào)整文字方向。233“設置單元格格式”對話框中的“對齊”選項卡三、“字體”選項卡“字體”選項卡如圖所示，用于設置字體、字形、字號、顏色、特殊效果等。234“設置單元格格式”對話框中的“字體”選項卡四、“邊框”選項卡“邊框”選項卡如圖所示，用于設置邊框線條樣式、顏色、選擇預置邊框線等。235“設置單元格格式”對話框中的“邊框”選項卡五、“填充”選項卡“填充”選項卡如圖所示，用于設置背景色、填充效果、圖案顏色、圖案樣式等。236“設置單元格格式”對話框中的“填充”選項卡六、“保護”選項卡“保護”選項卡如圖所示，用于設置工作表的鎖定、隱藏等。237“設置單元格格式”對話框中的“保護”選項卡對“員工信息登記表”進行驗證設置——表格的數(shù)據(jù)驗證設置任務4238學習目標知識目標：了解Excel2021中數(shù)據(jù)驗證的用途。技能目標：1.能設置單元格的數(shù)據(jù)驗證條件。2.能設置數(shù)據(jù)驗證的提示信息。239任務描述在Excel工作表中錄入數(shù)據(jù)時，難免會出現(xiàn)錄入錯誤，從而給后續(xù)工作帶來很多麻煩，甚至造成損失，利用“數(shù)據(jù)驗證”功能可以有效解決這一問題。本任務利用“數(shù)據(jù)驗證”功能，完成以下案例的制作。在前面完成的員工信息登記表的基礎上，為避免后續(xù)錄入信息時出現(xiàn)數(shù)據(jù)不規(guī)范或錯誤，利用“數(shù)據(jù)驗證”功能進行限制，如“性別”列只能輸入“男”或“女”而不能輸入其他信息，“薪資（元）”列數(shù)據(jù)值設置在“2000～6000”之間，并要求對錯誤輸入彈出提示信息，設置后的表格效果如圖所示。240241設置后的表格效果相關知識選擇要設置驗證數(shù)據(jù)的單元格或單元格區(qū)域，單擊“數(shù)據(jù)”選項卡下“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗證”按鈕，在下拉列表中選擇“數(shù)據(jù)驗證”命令，如圖所示，即可打開圖所示的“數(shù)據(jù)驗證”對話框。242“數(shù)據(jù)驗證”按鈕的位置243“數(shù)據(jù)驗證”對話框一、“設置”選項卡“設置”選項卡用于設置驗證條件，包括“任何值”“整數(shù)”“小數(shù)”“序列”“日期”“時間”“文本長度”“自定義”等類型，根據(jù)所選類型，可在“數(shù)據(jù)”下拉列表中進一步設置取值范圍。244二、“輸入信息”選項卡“輸入信息”選項卡如圖所示，用于設置選定單元格時是否顯示提示信息及信息內(nèi)容。245“輸入信息”選項卡三、“出錯警告”選項卡“出錯警告”選項卡如圖所示，用于設置輸入無效數(shù)據(jù)時是否顯示出錯警告及警告信息的圖標樣式和文字內(nèi)容。246“出錯警告”選項卡四、“輸入法模式”選項卡“輸入法模式”選項卡如圖所示，用于設置輸入法模式，包括“隨意”“打開”“關閉（英文模式）”，如果選擇“關閉（英文模式）”，那么將無法輸入漢字及全角符號。247“輸入法模式”選項卡利用“員工信息登記表”創(chuàng)建相似工作表——工作表的插入、刪除、重命名、移動和復制任務5248學習目標知識目標：了解創(chuàng)建多張相似工作表的方法。技能目標：能完成工作表的插入、刪除、重命名、移動和復制等基本操作。249任務描述本任務在學習工作表基本操作的基礎上，完成以下案例的制作。在完成前面員工信息登記表任務的基礎上，進一步細化制作該公司設計部、財務部、人事部三個部門的員工信息表，為了減少工作量，要求用已有的工作表通過復制、數(shù)據(jù)修改等操作實現(xiàn)多張工作表的創(chuàng)建，以便提高工作效率。三張表格在工作簿中按照設計部、財務部、人事部的順序排列。250相關知識工作表的基本操作主要有工作表的重命名、移動、復制、插入、刪除等。這些命令在功能區(qū)中均位于“開始”選項卡下“單元格”組中。單擊其中的“格式”按鈕，在下拉菜單的“組織工作表”小類中，可進行重命名、移動或復制等操作。單擊“插入”按鈕，可進行插入新工作表的操作。單擊“刪除”按鈕，可進行刪除當前工作表的操作。251252“插入工作表”命令新插入的“Sheet2”工作表若要刪除工作表，首先選中要刪除工作表的標簽，如“Sheet2”，然后單擊“開始”選項卡下“單元格”組中的“刪除”按鈕，在彈出的下拉列表中單擊“刪除工作表”命令，如圖所示，在彈出的確認對話框中單擊“刪除”按鈕即可完成操作。253“刪除工作表”命令編輯“畢業(yè)生抽樣調(diào)查表”——數(shù)據(jù)的編輯和修改任務6254學習目標知識目標：1.了解Excel2021中行或列的隱藏、凍結(jié)功能。2.掌握數(shù)據(jù)編輯修改的基本操作方法。技能目標：1.能完成數(shù)據(jù)的查找與替換。2.能完成表格行或列的插入與刪除。3.能完成數(shù)據(jù)的移動、復制和刪除。255任務描述本任務在學習Excel2021中編輯和修改數(shù)據(jù)方法的基礎上，完成以下案例的制作。某學校要對信息工程系畢業(yè)生做抽樣統(tǒng)計工作，現(xiàn)需對已建立的“畢業(yè)生抽樣調(diào)查表”進行數(shù)據(jù)的編輯與修改，為后續(xù)統(tǒng)計工作做準備。首先利用“查找與替換”功能將所有的“計算機網(wǎng)絡應用”替換成“計網(wǎng)”；然后在“性別”列和“年齡”列之間插入“民族”列并填寫信息；最后將“20140003仇曉杰”行內(nèi)容移到“20140017高亮亮”行內(nèi)容的上方，并刪除“20140002鄧玉林”和“20140010劉殿臣”兩行，原始文件和修改后文件的效果分別如圖所示。256257“畢業(yè)生抽樣調(diào)查表”原始文件編輯修改后的“畢業(yè)生抽樣調(diào)查表”相關知識一、行、列、工作表的隱藏與取消隱藏表格中有很多行或列時，不便于查詢信息，在打印表格時，有時不想打印某些不必要的行或列，此時，就可以將多余的行或列隱藏。1.隱藏行、列或工作表在Excel2021中，隱藏行、列或工作表有以下兩種方法。（1）選中需要隱藏的行、列或工作表標簽，單擊鼠標右鍵，在彈出的快捷菜單中單擊“隱藏”命令。258（2）選中需要隱藏的行、列或工作表標簽，單擊“開始”選項卡下“單元格”組中的“格式”按鈕，在下拉列表中選擇“隱藏和取消隱藏”級聯(lián)菜單中需要的命令，如圖所示。259隱藏行、列或工作表命令2.取消行、列或工作表的隱藏取消隱藏的方法是：對于已被隱藏的行（列），選中其上下兩行或多行（上下兩列或多列），單擊鼠標右鍵，在彈出的快捷菜單中單擊“取消隱藏”命令；對于已被隱藏的工作表，在任意其他工作表上單擊鼠標右鍵，在彈出的快捷菜單中單擊“取消隱藏”命令；此外，也可在前述“隱藏和取消隱藏”級聯(lián)菜單中單擊“取消隱藏行”“取消隱藏列”“取消隱藏工作表”等命令。如果操作對象是行或列，將直接顯示隱藏的行或列，如果操作對象是工作表，則出現(xiàn)圖所示的“取消隱藏”對話框，選中需要取消隱藏的工作表，單擊“確定”按鈕即可。260261“取消隱藏”對話框二、行、列的凍結(jié)和取消凍結(jié)如果需要在工作表滾動時保持一部分行和列始終可見，可使用凍結(jié)窗格功能。其使用方法是：選中不需凍結(jié)區(qū)域左上角的單元格，單擊“視圖”選項卡下“窗口”組中的“凍結(jié)窗格”按鈕，在下拉菜單中單擊“凍結(jié)窗格”命令，如圖所示。262“凍結(jié)窗格”下拉列表如需取消凍結(jié)窗格，單擊“視圖”選項卡下“窗口”組中的“凍結(jié)窗格”按鈕，在下拉菜單中單擊“取消凍結(jié)窗格”命令即可。在“凍結(jié)窗格”下拉菜單中，還提供了“凍結(jié)首行”和“凍結(jié)首列”兩個選項，可實現(xiàn)快速凍結(jié)首行和首列操作。263制作“學生成績統(tǒng)計表”——公式、函數(shù)、自動填充功能任務7264學習目標知識目標：1.了解公式、函數(shù)的功能。2.了解自動填充功能的用途。技能目標：1.能應用公式、函數(shù)完成相關運算。2.能使用自動填充功能快速輸入有規(guī)律的一組數(shù)據(jù)。265任務描述本任務在學習公式、函數(shù)、自動填充等功能的基礎上，完成以下案例的制作。某技師學院為方便快捷地統(tǒng)計學生成績，要制作一個成績統(tǒng)計表，要求用Excel軟件中的公式、函數(shù)、自動填充功能，快速求出總分、平均分、最高分、最低分、人數(shù)等數(shù)值，學生成績統(tǒng)計表的效果如圖所示。266267學生成績統(tǒng)計表的效果相關知識一、公式公式是對工作表中的數(shù)值執(zhí)行計算的一串表達式，以等號（=）開頭。公式中可以包括函數(shù)、引用、運算符和常量等。函數(shù)是軟件中預先編好的、用于運算的一小段程序，按照函數(shù)格式給定參數(shù)即可得到相應的運算結(jié)果。例如，求和函數(shù)用于求給定值的和，求最小值函數(shù)用于找出一組數(shù)據(jù)中的最小值等。單元格的引用是指在一處單元格的公式中使用另一處單元格的值，例如在單元格A3中輸入公式“=B5*6+9”，即引用了單元格B5的值。268運算符是一個標記或符號，用于指定表達式內(nèi)執(zhí)行的計算類型，包括數(shù)學運算符、比較運算符、邏輯運算符和引用運算符等。各種運算符的符號和使用格式可通過Excel2021的幫助文檔查閱。其中算術(shù)運算符與數(shù)學中的符號大多相同，但也有少數(shù)不同，如乘方運算的運算符為“∧”，相乘運算的運算符為“*”等。常量是指在運算過程中不發(fā)生變化的量，也就是不用計算的值，如數(shù)字“20”及文本“收入”等都是常量。269二、單元格引用Excel2021中不但可以引用當前工作簿中任何一個工作表的任何單元格或單元格區(qū)域中的數(shù)據(jù)，還可以引用其他工作簿中的數(shù)據(jù)。引用單元格數(shù)據(jù)后，公式的運算值將隨著被引用單元格數(shù)據(jù)的變化而變化。當被引用的單元格數(shù)據(jù)被修改后，公式的運算結(jié)果也將自動修改。Excel2021提供了相對引用、絕對引用和混合引用三種引用方式。1.相對引用在相對引用方式下，Excel2021實際記錄的是被引用單元格和公式所在單元格之間的相對位置，當公式所在單元格移動或復制到其他位置時，公式中所引用的單元格也隨之移動。2702.絕對引用在絕對引用方式下，Excel2021實際記錄的是被引用單元格的絕對位置，當公式所在單元格移動或復制到其他位置時，公式中所引用的單元格不隨之移動。在Excel2021中，絕對引用的標記是在行號和列號前分別添加一個符號“$”。3.混合引用若僅需行號或列號中的一個使用絕對位置，另一個使用相對位置，可僅在需要使用絕對位置的行號或列號前添加“$”，這就是混合引用。271三、常用函數(shù)1.SUM（求和函數(shù)）格式：SUM（參數(shù)1，參數(shù)2，參數(shù)3，…）。功能：求各參數(shù)之和。示例：SUM（3，5，6，8，2）的運算結(jié)果是“24”。2.AVERAGE（求平均值函數(shù)）格式：AVERAGE（參數(shù)1，參數(shù)2，參數(shù)3，…）。功能：求各參數(shù)的平均值。示例：AVERAGE（3，5，6，8，2）的運算結(jié)果是“4.8”。2723.ABS（求絕對值函數(shù)）格式：ABS（參數(shù)）。功能：求參數(shù)的絕對值。示例：ABS（-5）的運算結(jié)果是“5”。4.MAX（求最大值函數(shù)）格式：MAX（參數(shù)1，參數(shù)2，參數(shù)3，…）。功能：求各參數(shù)的最大值。示例：MAX（2，3，5，6，9，4）的運算結(jié)果是“9”。2735.MIN（求最小值函數(shù)）格式：MIN（參數(shù)1，參數(shù)2，參數(shù)3，…）。功能：求各參數(shù)的最小值。示例：MAX（2，3，5，6，9，4）的運算結(jié)果是“2”。6.RANK（排名函數(shù)）格式：RANK（參數(shù)1，參數(shù)2，參數(shù)3）。功能：求參數(shù)1在參數(shù)2這組數(shù)字中的排名數(shù)。說明：參數(shù)1為指定的一個數(shù)或一個單元格，參數(shù)2為一組數(shù)或一組單元格（參數(shù)1應是這一組中的一員）。若參數(shù)3為0或沒有，則按降序排列；若參數(shù)3為非零值，則按升序排列。2747.IF（條件函數(shù)）格式：IF（參數(shù)1，參數(shù)2，參數(shù)3）。功能：若參數(shù)1成立則轉(zhuǎn)到參數(shù)2；若參數(shù)1不成立則轉(zhuǎn)到參數(shù)3。說明：參數(shù)1為任意一個可判斷TRUE或FALSE的數(shù)值或表達式。8.COUNT（計數(shù)函數(shù)）格式：COUNT（參數(shù)1，參數(shù)2，參數(shù)3，…）。功能：求全部參數(shù)中包含數(shù)字參數(shù)的數(shù)目。說明：參數(shù)也可以是單元格區(qū)域，此時將統(tǒng)計該區(qū)域內(nèi)包含數(shù)字的單元格數(shù)目。2759.COUNTIF（條件統(tǒng)計函數(shù)）格式：COUNTIF（參數(shù)1，參數(shù)2）。功能：求參數(shù)1區(qū)域中滿足參數(shù)2條件的單元格數(shù)目。說明：統(tǒng)計的對象可以是數(shù)值，也可以是文本。10.SUMIF（按條件求和函數(shù)）格式：SUMIF（參數(shù)1，參數(shù)2，參數(shù)3）。功能：對滿足條件的單元格求和。說明：參數(shù)1為要進行條件比較的區(qū)域；參數(shù)2為條件，一般為一個表達式，將這個條件與參數(shù)1中的值進行比較；參數(shù)3為實際參與計算的單元格區(qū)域，若參數(shù)1的某值符合參數(shù)2的條件，則其對應位置上參數(shù)3的值將參與計算，如果參數(shù)3省略，則使用參數(shù)1中的值參與計算。27611.ROUND（四舍五入函數(shù)）格式：ROUND（參數(shù)1，參數(shù)2）。功能：按指定的位數(shù)對數(shù)值進行四舍五入。說明：參數(shù)1為原數(shù)值，參數(shù)2為一個數(shù)字，用于指定需要保留的小數(shù)位數(shù)。12.MID（截取字符串函數(shù)）格式：MID（參數(shù)1，參數(shù)2，參數(shù)3）。功能：從一個字符串中截取出指定位置、指定數(shù)量的字符。說明：參數(shù)1為原字符串，參數(shù)2為要截取的第一個字符的位置，參數(shù)3為截取的長度。277四、自動填充功能如果要輸入具有規(guī)律性的數(shù)據(jù)或文本，可采用Excel2021的自動填充功能。如輸入一年中的十二個月“一月、二月、三月、四月、……”，輸入“1、3、5、7、9、……”（即有相等的步長），輸入“甲、乙、丙、丁、……”，輸入“星期一、星期二、星期三、……”等，均可用自動填充功能快速輸入。2781.單擊“文件”選項卡下的“選項”命令，打開“Excel選項”對話框，如圖所示，單擊“高級”→“編輯自定義列表”按鈕，打開“自定義序列”對話框，如圖所示。279“Excel選項”對話框“自定義序列”對話框2.在“輸入序列”窗口中輸入序列內(nèi)容（如公司工人姓名），然后單擊“添加”按鈕，即可將所需自動填充的內(nèi)容添加到序列中，如圖所示，最后單擊“確定”按鈕。280添加填充序列制作“技能競賽成績表”——數(shù)據(jù)表格的排序任務8281學習目標知識目標：了解Excel2021表格的排序方式。技能目標：能根據(jù)需求，按照不同的排序規(guī)則進行排序。282任務描述本任務利用Excel2021的排序功能，完成以下案例的制作。某院校組織一次平面設計專業(yè)學生技能競賽，在競賽結(jié)束后需要統(tǒng)計各參賽班級小組的成績，并制作成表格，要求表格內(nèi)容包括考號、班級、小組、姓名、競賽成績和備注，并對同一班、同一組的學生成績按降序排序，技能競賽成績表排序效果如圖所示。283284技能競賽成績表排序效果相關知識在用Excel2021處理數(shù)據(jù)時，經(jīng)常要對數(shù)據(jù)進行排序，最常用、最快捷的方法是使用工具欄上的排序按鈕。除此之外，對于其他更復雜的排序需要，還可以單擊“開始”選項卡下“編輯”組中的“排序和篩選”按鈕，在下拉列表中單擊“自定義排序”命令，如圖所示，或單擊“數(shù)據(jù)”選項卡下“排序和篩選”組中的“排序”按鈕，如圖所示，打開“排序”對話框，如圖所示，自行設置參數(shù)即可進行排序。285286“自定義排序”命令“排序”按鈕287“排序”對話框Excel2021數(shù)據(jù)排序可分為單條件排序和多條件排序。單條件排序是指按單一條件進行排序，如按計算機科目的成績排序。多條件排序是指按多個條件進行排序，如先按計算機科目成績排序，成績相同者再按高等數(shù)學科目成績排序。突顯“校園歌手大獎賽歌手得分統(tǒng)計表”中不同分數(shù)段成績——條件格式的應用任務9288學習目標知識目標：了解條件格式的作用。技能目標：能使用條件格式完成特定單元格的突出顯示。289任務描述本任務利用Excel2021的條件格式功能，完成以下案例的制作。校園歌手大獎賽賽事委員會要對歌手比賽成績進行統(tǒng)計分析，需要在統(tǒng)計表中突顯出某一分數(shù)段內(nèi)的成績。具體要求是設置6分以下的成績格式，使其文字顏色為藍色，字形為加粗，底紋顏色為紅色；設置高于9分的成績格式，使其文字顏色為紅色，字形為加粗，底紋顏色為黃色，突顯成績效果如圖所示。290291突顯成績效果相關知識Excel2021的條件格式功能可以迅速為某些滿足條件的單元格或單元格區(qū)域設定某項格式。在使用條件格式功能時，用戶可以根據(jù)條件使用數(shù)據(jù)條、色階和圖標集等功能來突出顯示相關單元格，強調(diào)異常值，實現(xiàn)數(shù)據(jù)的可視化效果。其操作方法是：選擇需設置條件格式的單元格，單擊“開始”選項卡下的“條件格式”按鈕，在“突出顯示單元格規(guī)則”中選擇需要的條件（如“大于”），步驟如圖所示，彈出圖所示的“大于”對話框，在其中設置比較數(shù)據(jù)及單元格格式。292293“大于”對話框條件格式設置打印“校園歌手大獎賽歌手得分統(tǒng)計表”——頁面設置及打印任務10294學習目標知識目標：了解打印表格之前的相關準備工作。技能目標：1.能完成表格的頁面設置及頁眉、頁腳設置。2.能進行打印預覽并打印工作表。295任務描述本任務在學習頁面設置和打印操作的基礎上，完成以下案例的制作。校園歌手大獎賽賽事委員會要將紙質(zhì)的成績表存檔，需要將歌手得分統(tǒng)計表打印輸出，在表格制作、美化、排名次的基礎上，設置頁面格式后打印。具體要求為：打印在B5紙上，橫向，上下邊距為3.0cm，左右邊距為3.0cm；頁眉設置為“校園歌手大獎賽賽事委員會”，其格式設置為“字體：楷體，字號：12，字形：加粗，對齊方式：左對齊”；頁腳設置為“第一頁”，其格式設置為“字體：宋體，字號：14，對齊方式：居中”，如圖所示。296297校園歌手大獎賽歌手得分統(tǒng)計表頁面設置效果相關知識在“頁面布局”選項卡下“頁面設置”組中單擊按鈕

，彈出“頁面設置”對話框，如圖所示。對話框中共包含四個選項卡，分別是“頁面”“頁邊距”“頁眉/頁腳”及“工作表”。各選項卡的各項參數(shù)為默認值，用戶可根據(jù)需要進行修改。298“頁面設置”對話框1.單擊“頁面”選項卡，可以設置打印方向、縮放比例、紙張大小等參數(shù)。2.單擊“頁邊距”選項卡，可以設置上、下、左、右等頁面邊距的數(shù)據(jù)值，頁眉、頁腳距離數(shù)值及居中方式等參數(shù)，如圖所示。299“頁邊距”選項卡3.單擊“頁眉/頁腳”選項卡，可以自定義頁眉和頁腳，奇偶頁不同和首頁不同等參數(shù)，如圖所示，單擊“自定義頁眉”或“自定義頁腳”按鈕時，彈出“頁眉”和“頁腳”對話框，如圖所示，在此對話框中可以設置頁眉、頁腳的內(nèi)容和位置，對話框中各按鈕的含義如圖所示。300“頁眉/頁腳”選項卡301“頁眉”和“頁腳”對話框a）“頁眉”對話框b）“頁腳”對話框302“頁眉”和“頁腳”對話框中各按鈕含義利用圖表分析校園歌手大獎賽成績——圖表的創(chuàng)建、編輯與打印任務11303學習目標知識目標：了解圖表的作用。技能目標：1.能在Excel2021中創(chuàng)建圖表。2.能對圖表進行編輯與修改。304任務描述本任務在學習Excel2021圖表功能的基礎上，完成以下案例的制作。校園歌手大獎賽賽事委員會要對歌手比賽成績進行統(tǒng)計分析，需要將比賽成績制作成圖表，要求創(chuàng)建一種直觀、形象、易懂的圖表來展示成績表格的內(nèi)容，“校園歌手大獎賽歌手得分統(tǒng)計圖”效果如圖所示。305306“校園歌手大獎賽歌手得分統(tǒng)計圖”效果相關知識Excel2021是最主要的數(shù)據(jù)分析工具之一，它可以將抽象的數(shù)據(jù)圖表化，有助于用戶分析數(shù)據(jù)、查看數(shù)據(jù)差異、預測發(fā)展趨勢等。常用的圖表類型有柱形圖、折線圖、餅圖、條形圖、面積圖、曲面圖、圓環(huán)圖、氣泡圖和雷達圖等。分別單擊“插入”選項卡下“圖表”組中的各類圖表按鈕，在展開的菜單中即可選擇要插入的圖表類型。圖表的組成部分如圖所示。307308圖表的組成部分篩選分析學生考試成績——自動篩選和高級篩選任務12309學習目標知識目標：了解Excel2021篩選功能。技能目標：能熟練運用自動篩選、高級篩選功能。310任務描述本任務在學習Excel2021篩選功能的基礎上，完成以下案例的制作。某技師學院信息工程系要對“室內(nèi)設計專業(yè)學生成績表”進行分析，計算各科優(yōu)秀生、及格生所占的比例，要求通過Excel2021提供的篩選功能來實現(xiàn)，具體要求為：篩選出“PHOTOSHOP”成績大于90分的學生成績，篩選出“3DMAX”成績大于60分且“AUTOCAD”成績大于等于85分的學生成績。311相關知識在Excel2021中可以通過數(shù)據(jù)篩選實現(xiàn)只顯示滿足指定條件的記錄，從而快速找到所需數(shù)據(jù)。Excel2021中的篩選方式可分為自動篩選和高級篩選兩種。一、自動篩選單擊“數(shù)據(jù)”選項卡下的“篩選”按鈕，在數(shù)據(jù)表格區(qū)域中的各列標題單元格右側(cè)均會出現(xiàn)一個下拉按鈕，單擊該按鈕可以設置數(shù)據(jù)篩選條件。表格中不同列可以同時設置不同的篩選條件。312二、高級篩選利用高級篩選可以在當前或其他工作表的任何位置建立條件區(qū)域，根據(jù)該條件進行篩選。條件區(qū)域至少應有兩行，且首行應與數(shù)據(jù)表格相應的列標題一致。在條件區(qū)域中，在同一行的條件關系為邏輯與，不在同一行的條件關系為邏輯或。篩選的結(jié)果可以在原數(shù)據(jù)表格位置顯示，也可以在其他位置顯示。313匯總分析學生考試成績——數(shù)據(jù)的分類匯總?cè)蝿?3314學習目標知識目標：了解Excel2021中分類匯總的功能。技能目標：能運用分類匯總功能進行數(shù)據(jù)分析。315任務描述本任務在學習Excel2021分類匯總功能的基礎上，完成以下案例的制作。某技師學院現(xiàn)需根據(jù)“室內(nèi)設計專業(yè)學生成績表”分別計算男生和女生的平均成績，要求使用Excel2021的分類匯總功能解決這個問題。室內(nèi)設計專業(yè)學生成績表如圖所示，分類匯總后的結(jié)果如圖所示。316317室內(nèi)設計專業(yè)學生成績表318分類匯總后的結(jié)果相關知識分類匯總就是對數(shù)據(jù)按種類進行快速匯總，利用分類匯總功能能夠極大提高數(shù)據(jù)處理效率。數(shù)據(jù)的分類匯總分為兩個步驟進行，第一是用戶利用排序功能將需要匯總的內(nèi)容排列在一起；第二是系統(tǒng)利用預設的函數(shù)，根據(jù)用戶設定的條件進行計算。319制作“電器銷售情況統(tǒng)計表”數(shù)據(jù)匯總表——合并計算及保護工作表任務14320學習目標知識目標：1.了解Excel2021合并計算功能。2.了解鎖定單元格和保護工作表功能。技能目標：能根據(jù)需求，完成工作表的合并計算和保護。321任務描述本任務利用Excel2021的合并計算和保護工作表功能，完成以下案例的制作。某商場想要對4、5、6月的電器銷售情況進行匯總，需要制作一份第二季度電器銷售情況統(tǒng)計表。要求：在同一個工作簿中，創(chuàng)建“四月”“五月”“六月”“二季度匯總表”工作表，在“二季度匯總表”中將4、5、6月的相同電器銷量情況進行匯總求和，為每個工作表標簽設置不同顏色，并對“二季度匯總表”進行工作表保護，如圖所示。322323“二季度電器銷售匯總表”效果相關知識一、合并計算在Excel2021中，合并計算是指通過合并計算的方法來匯總一個或多個源區(qū)中的數(shù)據(jù)。在日常工作中，經(jīng)常會遇到一些相關數(shù)據(jù)需要合并到一起，形成一個匯總表格的情況，利用Excel2021中的“合并計算”功能，對解決這類匯總多個格式一致的數(shù)據(jù)是非常有用的，且非常簡便。Excel2021合并計算功能具有求和、求平均值、計數(shù)、求最大值、求最小值等一系列合并功能。合并計算的數(shù)據(jù)源區(qū)域可以是一個工作表、多個工作表及多個工作簿中的數(shù)據(jù)。324二、鎖定單元格和保護工作表對于一些數(shù)據(jù)表和工作表，用戶有時不希望對它的某部分或全部數(shù)據(jù)進行修改，通過Excel2021中的鎖定和保護功能可以實現(xiàn)對工作表的部分或整個工作表的保護。保護工作表需要兩步，第一步是鎖定需要保護的單元格，第二步是保護工作表。選擇工作表中的所有單元格，單擊“開始”選項卡下“單元格”組中“格式”下拉列表的“鎖定單元格”命令，可以將所有單元格解除鎖定，如圖所示。325326鎖定單元格再選擇鎖定的單元格區(qū)域，單擊“開始”選項卡下“單元格”組中的“格式”按鈕，單擊下拉列表中“鎖定單元格”命令，將選擇的單元格區(qū)域鎖定。在“格式”下拉列表中單擊“保護工作表”命令，打開“保護工作表”對話框，如圖所示，設置“取消工作表保護時使用的密碼”和“允許此工作表的所有用戶進行”項目，此時再進入工作表時，將無法更改“允許此工作表的所有用戶進行”項目設置項以外的操作。如需取消保護，可在“格式”下拉列表中“撤銷工作表保護”，輸入正確密碼即可。327328“保護工作表”對話框三、工作表標簽顏色工作表標簽的顏色是可以更改的。單擊“開始”選項卡下“單元格”組的“格式”按鈕，在下拉列表中選擇“工作表標簽顏色”命令，在彈出的級聯(lián)菜單中選擇需要更換的顏色，如圖所示。若取消標簽顏色或更換顏色，可在級聯(lián)菜單中選擇“無顏色”或其他顏色。329330更改工作表標簽顏色制作校園歌手大賽獲獎證書——郵件合并任務15331學習目標知識目標：了解郵件合并的應用領域。技能目標：能利用郵件合并功能，根據(jù)模板批量制作文檔。332任務描述本任務在學習Excel和Word聯(lián)合使用郵件合并功能基礎上，完成以下案例的制作。某技師學院組織第二屆校園歌手大獎賽，在比賽結(jié)束后為獲獎選手頒發(fā)獲獎證書，現(xiàn)需要在大賽前利用郵件合并功能，制作獲獎證書模板，方便在大賽成績公布后，以最快的速度打印獲獎證書。獲獎證書的效果如圖所示。333334獲獎證書的效果相關知識在日常工作中，常需要處理一些十分相似的文檔，它們具有相同的布局、相同的格式設置、相同的文本和圖形，僅在某些特定部分有所不同，具有個性化的內(nèi)容，如信封、準考證、獲獎證書、通知書、格式合同、成績單等。在Word中逐一制作這些文檔，操作起來較為煩瑣，尤其是當數(shù)量較大時，更加費時費力。這時使用郵件合并功能，可一次性批量創(chuàng)建這些文檔。郵件合并功能需要同時用到Word和Excel兩個軟件，使用Word軟件制作主文檔，使用Excel軟件制作數(shù)據(jù)源。主文檔就是一個模板，體現(xiàn)這類文檔的共性部分，而數(shù)據(jù)源是由個性化部分匯總成的表格。運行郵件合并功能，可自動將數(shù)據(jù)源中的每條個性化內(nèi)容分別填入主文檔中，生成若干個文檔頁面。335分析技能競賽成績數(shù)據(jù)——數(shù)據(jù)透視表的使用任務16336學習目標知識目標：了解數(shù)據(jù)透視表的功能。技能目標：能使用數(shù)據(jù)透視表對數(shù)據(jù)進行分析。337任務描述任務利用Excel數(shù)據(jù)透視表的分析功能，完成以下案例的制作。某院校組織了一次平面設計專業(yè)學生技能競賽，已將競賽成績錄入到Excel表格中，現(xiàn)要分析每個班級、每個小組的總分排名情況，需要制作一個數(shù)據(jù)透視表來清晰地查看每個班級各小組的總分，數(shù)據(jù)透視表效果如圖所示。338339數(shù)據(jù)透視表效果相關知識數(shù)據(jù)透視表是一種可以快速匯總大量數(shù)據(jù)的交互式、交叉制表的Excel報表，用于對多種來源的數(shù)據(jù)（包括Excel表格中的數(shù)據(jù)和數(shù)據(jù)庫記錄等外部數(shù)據(jù)）進行匯總和分析，尤其是對于記錄多、結(jié)構(gòu)復雜的工作表更加方便。使用數(shù)據(jù)透視表可以深入分析數(shù)值數(shù)據(jù)，并且可以發(fā)現(xiàn)一些預計不到的數(shù)據(jù)問題，其主要有以下用途。1.以多種方式交叉查詢大量數(shù)據(jù)。3402.對數(shù)值數(shù)據(jù)進行分類匯總和聚合，按分類和子分類對數(shù)據(jù)進行匯總，創(chuàng)建自定義計算和公式。3.展開或折疊要關注結(jié)果的數(shù)據(jù)級別，查看感興趣區(qū)域摘要數(shù)據(jù)的明細。4.將行移動到列或?qū)⒘幸苿拥叫校ɑ颉巴敢暋保?，以查看源?shù)據(jù)的不同匯總結(jié)果。5.對最有用和最關注的數(shù)據(jù)子集進行篩選、排序、分組和有條件地設置格式，以便關注所需信息。6.可以提供簡明、有吸引力且?guī)в信⒌穆?lián)機報表或打印報表。341“產(chǎn)品銷售情況”模擬分析——單變量求解、模擬運算表、方案管理器任務17342學習目標知識目標：1.了解模擬分析的功能。2.了解模擬分析三種工具的作用。技能目標：1.能完成單變量求解操作。2.能完成模擬運算表操作。3.能完成方案管理器操作。343任務描述本任務在學習Excel2021模擬分析三種工具操作的基礎上，完成以下案例的制作。某公司生產(chǎn)了一批不同型號的產(chǎn)品，為了能提前掌握產(chǎn)品銷售動態(tài)信息情況，需要利用Excel2021模擬分析功能完成不同情況下的數(shù)據(jù)分析。要求利用Excel2021模擬分析的三種工具，分析不同情況下的數(shù)據(jù)動態(tài)變化情況。344利用單變量求解功能計算某型號產(chǎn)品為達到某一利潤值時銷售加價百分比的數(shù)值；利用模擬運算表功能計算某型號產(chǎn)品當銷售數(shù)量和加價百分比發(fā)生變化時的產(chǎn)品銷售利潤值；利用方案管理器功能制作出三種方案，預測四種型號產(chǎn)品當達到某一銷售利潤時的加價百分比數(shù)值，并完成方案摘要和方案數(shù)據(jù)透視表的制作。效果分別如圖所示。345采用單變量求解計算加價百分比效果346采用模擬運算表計算利潤值效果347采用方案管理器顯示方案2效果348方案2的方案摘要效果349方案2的數(shù)據(jù)透視表效果相關知識模擬分析就是模仿真實的場景做假設性分析（預測分析）。在Excel2021中，如果單元格中的數(shù)值發(fā)生了改變，那么工作表中公式的結(jié)果也會隨之發(fā)生變化。模擬分析有單變量求解、模擬運算表和方案管理器三種工具。一、單變量求解單變量求解是根據(jù)一個已知的目標值和已有的公式，計算出另一個變量值（形式如X+50=200），它是一種逆向的假設性推斷。350例如，某產(chǎn)品要想讓利潤達到15000元，在其他條件不變的情況下，需要銷售多少商品。具體操作步驟如下。1.在Excel2021工作表中輸入數(shù)據(jù)信息（產(chǎn)品進價、產(chǎn)品運費、產(chǎn)品銷量、產(chǎn)品售價），如圖所示。2.在B5單元格內(nèi)輸入公式“=（B4-B1-B2）*B3”，如圖所示。351產(chǎn)品銷售表數(shù)據(jù)信息輸入利潤