項目4電子表格軟件Excel

1、項目4 電子表格軟件Excel 2019目錄4.1 制作學(xué)生成績表4.2 制作職工工資分析表4.3 制作職工職稱工資分析表4.4 制作銷售情況分析圖4.5 制作學(xué)生成績分析表4.6 對工作表中的數(shù)據(jù)進行高級篩選4.7 創(chuàng)建職工學(xué)歷信息數(shù)據(jù)透視表和數(shù)據(jù)透視圖電子表格軟件EXCEL 2019教學(xué)目標(biāo)掌握新建空白工作簿、數(shù)據(jù)填充、合并單元格、設(shè)置單元格格式的方法;掌握各種常用函數(shù)的使用方法。熟悉各種重要函數(shù)的用法；掌握設(shè)置各種單元格格式的方法。掌握自動篩選、排序、分類匯總的操作方法；掌握高級篩選的操作方法。掌握數(shù)據(jù)透視表和數(shù)據(jù)透視圖的作用及操作方法。4.1制作學(xué)生成績表項目要求l新建一個空白工作簿，

2、并以“學(xué)生成績表”為名稱進行保存。l在A1單元格中輸入“計算機應(yīng)用1班成績表”，然后在A2：G2單元格中輸入相關(guān)數(shù)據(jù)。l在A3單元格中輸入“1”，然后拖動單元格“填充柄”進行序列填充。l使用上述相同的方法輸入學(xué)號列的數(shù)據(jù)，然后依次輸入姓名以及各科成績。l合并A1：G1單元格區(qū)域，設(shè)置單元格格式為“黑體、18號”。l選擇A2：G2單元格區(qū)域，設(shè)置單元格格式“宋體、12號、居中對齊”，設(shè)置底紋為“茶色、背景2、深色25%”。l自動設(shè)置各列列寬，手動調(diào)整各行行高。本任務(wù)要求了解計算機網(wǎng)絡(luò)的發(fā)展，掌握計算機網(wǎng)絡(luò)的概念、作用和分類。4.1制作學(xué)生成績表相關(guān)知識lExcel 2019工作界面與Word 2

3、019工作界面基本相似，由快速訪問工具欄、標(biāo)題欄、功能區(qū)、編輯欄和工作表編輯區(qū)等部分組成，如圖所示，下面介紹編輯欄、名稱欄等術(shù)語及其作用。l新建工作簿新建工作簿 在Excel 2019中，工作簿是存儲數(shù)據(jù)的文件，其默認擴展名為“.xlsx”。Excel在啟動后會自動創(chuàng)建一個名為“工作簿1”的空白工作簿，在未關(guān)閉“工作簿1”之前，再新建工作簿時，系統(tǒng)會自動命名為“工作簿2”、“工作簿3”。l保存工作表保存工作表 當(dāng)完成一個工作簿的創(chuàng)建、編輯后，就需將工作簿文件保存起來，Excel 2019提供了“保存”和“另存為”兩種方法用于保存工作簿文件，其操作步驟如下。 （1）選擇“文件”“保存”命令，此時

4、如果要保存的文件是第一次存盤，將彈出“另存為”對話框，在該對話框中可選擇“保存位置”設(shè)置，輸入文件名；如果該文件已經(jīng)被保存過，則不彈出“另存為”對話框，同時也不執(zhí)行后面的操作。 （2）選擇“文件”“另存為”命令，將為已保存過的文件再保存一個副本。l工作表的插入、刪除與重命名工作表的插入、刪除與重命名右擊“工作表標(biāo)簽”，在彈出的快捷菜單中選擇“插入”命令，即可在所選工作表之前插入一個新的工作表；選擇“刪除”命令，即可刪除所選工作表；選擇“重命名”命令，即可給所選“工作表”重新命名。l工作表區(qū)域工作表區(qū)域 工作表區(qū)域即用來記錄數(shù)據(jù)的區(qū)域。l單元格單元格每個單元格的位置由交叉的“行標(biāo)簽”和“列標(biāo)簽”

5、表示，如“A5”、“B6”。每個單元格中可以存放多達3200個字符的信息，單元格是表格的最小單位。l活動單元格活動單元格 活動單元格是當(dāng)前正在操作的單元格，被文本框框住。l7工作表標(biāo)簽工作表標(biāo)簽工作表標(biāo)簽?zāi)J為“Sheet1”，用于顯示工作表的名稱，當(dāng)前活動的工作表是白色的，其余為灰色，利用標(biāo)簽可切換顯示工作表。l狀態(tài)欄狀態(tài)欄 狀態(tài)欄位于窗口的底部，可顯示操作信息。l數(shù)據(jù)輸入數(shù)據(jù)輸入 單擊要輸入數(shù)據(jù)的單元格，可輸入所需數(shù)據(jù)。Excel工作表的單元格中，可以輸入“數(shù)值型”、“字符型”、“日期時間型”等不同類型的數(shù)據(jù)。下面分別對不同類型數(shù)據(jù)的輸入方法進行介紹。 1輸入數(shù)值型數(shù)據(jù)輸入數(shù)值型數(shù)據(jù) 數(shù)值

6、型數(shù)據(jù)是類似于“100”、“3.14”、“-2.418”等形式的數(shù)據(jù)，它表示一個數(shù)量的概念。其中的正號“+”會被忽略，當(dāng)用戶需要輸入普通的實數(shù)類型的數(shù)據(jù)時，只需直接在單元格中輸入，其默認對齊方式是“右對齊”。輸入的數(shù)據(jù)長度超過單元格寬度時（多于11位的數(shù)字，其中包括小數(shù)點和類似“E”和“+”這樣的字符），Excel會自動以科學(xué)計數(shù)法表示。 2輸入字符型數(shù)據(jù)輸入字符型數(shù)據(jù) 字符型數(shù)據(jù)是指字母、數(shù)字和其他特殊字符的任意組合，如“ABC”、“漢字”、“￥%”、等形式的數(shù)據(jù)。 當(dāng)用戶輸入的字符型數(shù)據(jù)超過單元格的寬度時，如果右側(cè)的單元格中沒有數(shù)據(jù)，則字符型數(shù)據(jù)會跨越單元格顯示

7、；如果右側(cè)的單元格中有數(shù)據(jù)，則只會顯示未超出部分?jǐn)?shù)據(jù)。 如果用戶需要在單元格中輸入多行文字，那么可以在一行輸入結(jié)束后，按“Alt + Enter”組合鍵實現(xiàn)換行，然后輸入后續(xù)的文字，字符型數(shù)據(jù)的默認對齊方式是“左對齊”。 3輸入日期時間型數(shù)據(jù)輸入日期時間型數(shù)據(jù) 對于日期時間型數(shù)據(jù)，按日期和時間的表示方法輸入即可。輸入日期時，用連字符“-”或斜杠“/”分隔日期的年、月、日；輸入時間時用“：”分隔。例如，“2004-1-1”、“2004/1/1”、“8:30:20 AM”等均為正確的日期型數(shù)據(jù)。當(dāng)日期時間型數(shù)據(jù)太長而超過列寬時，會顯示“#”，表示當(dāng)前列寬太窄，用戶只要適當(dāng)調(diào)整列寬就可以正常顯示數(shù)據(jù)

8、。l數(shù)據(jù)自動填充數(shù)據(jù)自動填充 輸入一個工作表時，經(jīng)常會遇到有規(guī)律的數(shù)據(jù)。例如，需要在相鄰的單元格中填入序號1、3、5、7等序列，這時就可以使用Excel的自動填充功能。自動填充是指將數(shù)據(jù)填寫到相鄰的單元格中，是一種快速填寫數(shù)據(jù)的方法。1使用鼠標(biāo)左鍵填充使用鼠標(biāo)左鍵填充 使用鼠標(biāo)自動填充時，需要用到填充柄，“填充柄”位于選定單元格區(qū)域的右下角，如圖4-6所示。 填充的具體操作方法是：選擇含有數(shù)據(jù)的起始單元格，移動鼠標(biāo)指針到填充柄，當(dāng)鼠標(biāo)指針變成實心十字形“ ”時，按住鼠標(biāo)左鍵拖動鼠標(biāo)到目標(biāo)單元格。2使用鼠標(biāo)右鍵填充使用鼠標(biāo)右鍵填充 按住鼠標(biāo)右鍵拖動填充的方式，提供了更為強大的填充功能，其操作步驟

9、如下。 （1）選定待填充區(qū)域的起始單元格，然后輸入序列的初始值并確認。 （2）移動鼠標(biāo)指針到初始值的單元格右下角的填充柄（指針變?yōu)閷嵭氖中巍?”）。 （3）按住鼠標(biāo)右鍵拖動填充柄，經(jīng)過待填充的區(qū)域，在彈出的快捷菜單中選擇要填充的方式。l合并單元格合并單元格 選中要合并的單元格，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開 “設(shè)置單元格格式”對話框，單擊“對齊”選項卡，選擇“文本控制”組中的“合并單元格”復(fù)選框，再單擊“確定”按鈕。l行高和列寬的調(diào)整行高和列寬的調(diào)整 工作表中的行高和列寬是Excel默認設(shè)定的，如果需要調(diào)整，可以手動完成。 1調(diào)整行高調(diào)整行高 （1）第一種方法 把鼠

10、標(biāo)指針移動到行與上下行邊界處，當(dāng)鼠標(biāo)指針變成“ ”形狀時，拖動鼠標(biāo)調(diào)整行高，這時Excel會自動顯示行的高度值。 （2）第二種方法 選擇需要調(diào)整的行或行所在的單元格，單擊“開始”功能區(qū)，在“單元格”組中單擊“格式”下拉菜單，選擇“行高”命令，在彈出的“行高”對話框中輸入行的高度值。 2調(diào)整列寬調(diào)整列寬 （1）第一種方法 把鼠標(biāo)指針移動到列與左右列的邊界處，當(dāng)鼠標(biāo)指針變成“ ”形狀時，拖動鼠標(biāo)調(diào)整列寬，這時Excel會自動顯示列的寬度值。 （2）第二種方法 選擇需要調(diào)整的列或列所在的單元格，單擊“開始”功能區(qū)，在“單元格”組中單擊“格式”下拉菜單，選擇 “列寬”命令，在彈出的“列寬”對話框中輸入

11、列寬的寬度值。4.1制作學(xué)生成績表項目實施返返回回l步驟1：新建并保存工作簿l步驟2：輸入工作表數(shù)據(jù)l步驟3：設(shè)置單元格格式l步驟4：調(diào)整行高與列寬4.2制作職工工資分析表項目要求l使用公式在E2：E4單元格區(qū)域求出職工工資的總工資。l使用求和函數(shù)SUM在C5：D5單元格區(qū)域計算職工工資的總計。l使用平均值函數(shù)AVERAGE在C6：D6單元格區(qū)域計算原來工資和浮動工資的平均值，并保留兩位小數(shù)。l使用最大值函數(shù)MAX在C7：D7單元格區(qū)域計算原來工資和浮動工資的最大值。l使用最小值函數(shù)MIN在C8：D8單元格區(qū)域計算原來工資和浮動工資的最小值。l給表格添加外邊框和內(nèi)邊框。4.2制作職工工資分析表

12、相關(guān)知識l公式的輸入 Excel通過引進公式，增強了對數(shù)據(jù)的運算分析能力。在Excel中，公式在形式上由等號“=”開始，其語法可表示為“=表達式”。 當(dāng)用戶按“Enter”鍵確認公式輸入完成后，單元格顯示的是公式的計算結(jié)果。如果用戶需要查看或者修改公式，則可以雙擊單元格，在單元格中查看或修改公式。l使用函數(shù) 函數(shù)是Excel中預(yù)先定義好、經(jīng)常使用的一種公式。Excel提供了200多個內(nèi)部函數(shù)，當(dāng)需要使用時，可按照函數(shù)的格式直接引用，函數(shù)的輸入有“手工輸入”和“使用粘貼函數(shù)輸入”兩種方式。l手工輸入 對于一些比較簡單的函數(shù)，用戶可以用輸入公式的方法直接在單元格中輸入函數(shù)。例如，可在對應(yīng)單元格中直

13、接輸入“=SUM(E4:G4)”，然后按“Enter”鍵確認，即可得到學(xué)生的總成績。l使用粘貼函數(shù)輸入 對于參數(shù)較多或比較復(fù)雜的函數(shù)，一般采用“粘貼函數(shù)”按鈕來輸入，常用的函數(shù)有求和（SUM）、求平均值（AVERAGE）、計算“指定字符串”的個數(shù)（COUNT）、求參數(shù)的最大值（MAX）、求參數(shù)的最小值（MIN）等。4.2制作職工工資分析表項目實施返返回回l步驟1：新建工作表并利用公式計算出職工工資總工資l步驟2：使用函數(shù)求和、求平均值、求最大值和最小值l步驟3：設(shè)置平均值單元格格式，并對工作表進行命名和添加邊框4.3制作職工職稱工資分析表項目要求l使用公式在E2：E9單元格區(qū)域求出職稱工資占總

14、工資的百分比，用百分比類型，保留兩位小數(shù)。l使用RANK函數(shù)在F2：F9單元格區(qū)域按降序計算“工資”列的內(nèi)容。l使用IF函數(shù)在G2：G9單元格區(qū)域?qū)べY級別進行分類，工資大于等于8000的設(shè)置為“高工資”，其他設(shè)置為“一般工資”。l使用COUNTIF函數(shù)在J4：J6單元格區(qū)域分別求出助教、講師及教授的人數(shù)。l使用SUMIF函數(shù)在K4：K6單元格區(qū)域求出各職稱職工的平均工資。4.3制作職工職稱工資分析表相關(guān)知識l單元格引用用于標(biāo)識工作表中單元格或單元格區(qū)域，它在公式中指明了公式所使用數(shù)據(jù)的位置。在Excel中有相對引用、絕對引用及混合引用，它們分別適用于不同的場合。 1相對引用 Excel默認的

15、單元格引用為相對引用。相對引用是指某一單元格的地址是相對于當(dāng)前單元格的相對位置，是由單元格的行號和列號組成，如A1、B2、E5等。在相對引用中，當(dāng)復(fù)制或移動公式時，Excel會根據(jù)移動的位置自動調(diào)節(jié)公式中引用單元格的地址。例如，E5單元格中的公式“=C5*D5/10”，在被復(fù)制到E6單元格時會自動變?yōu)椤?C6*D6/10”，從而使得E6單元格中也能得到正確的計算結(jié)果。 2絕對引用 絕對引用是指某一單元格的地址是其在工作表中的絕對位置，其構(gòu)成形式是在行號和列號前面各加一個“$”符號。例如，$A$2、$B$4、$H$5都是對單元格的絕對引用。其特點在于，當(dāng)把一個含有絕對引用的單元格中的公式移動或復(fù)

16、制到一個新的位置時，公式中的單元格地址不會發(fā)生變化。例如，若在E5單元格中有公式“=$B$3+$C$3”，如果將其復(fù)制到E6單元格中，則E6單元格中的公式還是“=$B$3+$C$3”，可以用于分?jǐn)?shù)運算時，使分母的值固定不變。 3混合引用 在公式中同時使用相對引用和絕對引用，稱為混合引用。l幾個常用函數(shù)幾個常用函數(shù) 1RANK函數(shù)函數(shù) 返回數(shù)字在一列數(shù)字中相對于其他數(shù)值的大小排名，可按降序排列，也可按升序排列。 1RANK函數(shù)函數(shù) 返回數(shù)字在一列數(shù)字中相對于其他數(shù)值的大小排名，可按降序排列，也可按升序排列。 2IF函數(shù)函數(shù) 判斷是否滿足某個條件，如果滿足則返回一個值，如果不滿足則返回另一個值。

17、3COUNTIF函數(shù)函數(shù) 計算某個區(qū)域中滿足給定條件單元格的數(shù)目。 4SUMIF函數(shù)函數(shù) 對滿足條件的單元格中的數(shù)據(jù)求和。4.3制作職工職稱工資分析表項目實施l步驟1：利用公式計算職工工資占總工資的百分比l步驟2：使用RANK函數(shù)按工資降序計算“工資”列的內(nèi)容l步驟3：使用If函數(shù)對工資級別進行分類l步驟4：使用COUNTIF函數(shù)統(tǒng)計職稱人數(shù)l步驟5：使用SUMIF函數(shù)求各職稱職工平均工資4.4制作銷售情況分析圖項目要求l將表格中所有數(shù)據(jù)居中。l將各種設(shè)備銷售額單元格的格式設(shè)置為“貨幣”，貨幣符號為“￥”，小數(shù)位數(shù)為0。l選取“設(shè)備銷售情況分析表”的“設(shè)備名稱”和“銷售額”兩列內(nèi)容建立“簇狀柱

18、形圖”。lX軸為“設(shè)備名稱”，圖表標(biāo)題為“設(shè)備銷售情況分析圖”，不顯示圖例。l將圖插入到工作表的A9：E22單元格區(qū)域內(nèi)。4.4制作銷售情況分析圖相關(guān)知識l圖表的組成圖表的組成 創(chuàng)建好圖表后，可以看到圖表包括繪圖區(qū)、圖例、分類名稱、坐標(biāo)值、圖表標(biāo)題、分類軸標(biāo)題、數(shù)值軸標(biāo)題，如圖所示。l圖表的編輯圖表的編輯 1選取圖表選取圖表 在對圖表進行編輯時，首先要選取圖表。如果是嵌入式圖表，則單擊圖表；如果是圖表工作表，則需單擊此工作表標(biāo)簽。 2移動圖表和改變圖表大小移動圖表和改變圖表大小 選中圖表后，圖表四周會出現(xiàn)8個黑色的小方塊，這時可以對圖表進行移動和改變大小的操作，與Word相同。 3刪除圖表刪除

19、圖表 選中圖表后，按“Delete”鍵即可刪除圖表。 4改變圖表類型改變圖表類型 選中圖表，單擊“功能區(qū)/更改圖表類型”按鈕，選擇合適的圖表類型。 5更改圖表中的標(biāo)題更改圖表中的標(biāo)題 選中“標(biāo)題”文本框，重新輸入標(biāo)題即可。4.4制作銷售情況分析圖項目實施l步驟1：設(shè)置文字居中、貨幣符號及小數(shù)位數(shù)l步驟2：插入圖表l步驟3：移動圖表位置4.5制作學(xué)生成績分析表制作學(xué)生成績分析表項目要求l對“計算機應(yīng)用學(xué)生成績單”的內(nèi)容進行自動篩選，條件為“數(shù)據(jù)庫原理”、“操作系統(tǒng)”、“體系結(jié)構(gòu)”3門課程成績均大于或等于70分。l對篩選后的數(shù)據(jù)按主要關(guān)鍵字“班級”的升序和次要關(guān)鍵字“操作系統(tǒng)”的降序進行排序。l對

20、排序后的數(shù)據(jù)，把“班級”作為分類字段，平均值作為“匯總方式”，“數(shù)據(jù)庫原理”和“體系結(jié)構(gòu)”作為選定匯總項，并將匯總結(jié)果顯示在數(shù)據(jù)下方。4.5制作學(xué)生成績分析表相關(guān)知識l 數(shù)據(jù)排序數(shù)據(jù)排序 Excel中我們經(jīng)常需要對工作表中的某列數(shù)據(jù)進行排序，以方便分析使用。Excel對數(shù)據(jù)的排序依據(jù)是：如果字段是數(shù)值型或日期時間型數(shù)據(jù)，則按照數(shù)據(jù)大小進行排序；如果字段是字符型數(shù)據(jù)，則英文字符按照ASCII碼排序，漢字按照漢字機內(nèi)碼或者筆畫排序。 1單列數(shù)據(jù)的排序 將光標(biāo)放在工作表區(qū)域中需要排序的列中的任一單元格，在“數(shù)據(jù)”功能區(qū)中單擊“排序和篩選”組中的按鈕，可按“升序”或“降序”對表中數(shù)據(jù)重新排列。 2多列

21、數(shù)據(jù)排序 當(dāng)需要對工作表中的多列數(shù)據(jù)進行排序，如按單列數(shù)據(jù)排序時，會出現(xiàn)值相同的情況，如果以此“單列數(shù)據(jù)”為主關(guān)鍵字，則“值相同”的情況只能隨機排序，這種情況還可把“另一字段”作為次要關(guān)鍵字進行排序。l數(shù)據(jù)篩選數(shù)據(jù)篩選 數(shù)據(jù)篩選的含義是只顯示符合條件的記錄，隱藏不符合條件的記錄。 1數(shù)據(jù)篩選的具體操作方法數(shù)據(jù)篩選的具體操作方法 選中數(shù)據(jù)清單中含有數(shù)據(jù)的任一單元格。 在“數(shù)據(jù)”功能區(qū)中，單擊“排序和篩選”組中的“篩選”按鈕，這時工作表標(biāo)題行上增加了下三角按鈕。 2單擊選定數(shù)據(jù)列的下三角按鈕，設(shè)置篩選條件單擊選定數(shù)據(jù)列的下三角按鈕，設(shè)置篩選條件 這時，Excel就會根據(jù)設(shè)置的篩選條件隱藏不滿足條件

22、的記錄。如果對所列記錄還有其他篩選要求，則可以重復(fù)上述步驟繼續(xù)篩選，重復(fù)步驟1可以取消自動篩選。l分類匯總分類匯總分類匯總的含義是首先對記錄按照某一字段的內(nèi)容進行分類，然后計算每類記錄指定字段的匯總值，如總和、平均值等。在進行分類匯總前，應(yīng)先對數(shù)據(jù)清單中的數(shù)據(jù)按某一規(guī)則進行排序，數(shù)據(jù)清單的第一行必須有字段名。4.5制作學(xué)生成績分析表制作學(xué)生成績分析表項目實施l步驟1：對“計算機應(yīng)用學(xué)生成績單”內(nèi)的數(shù)據(jù)進行自動篩選l步驟2：對“計算機應(yīng)用學(xué)生成績單”篩選后的數(shù)據(jù)進行排序l步驟3：對“計算機應(yīng)用學(xué)生成績單”內(nèi)排序后的數(shù)據(jù)進行分類匯總4.6對工作表中的數(shù)據(jù)進行高級篩選項目要求l“課程成績單”內(nèi)的數(shù)據(jù)進行高級篩選。l“系別”為“計算機”，“課程名稱”為“人工智能”。l在數(shù)據(jù)表前插入3行，前2行作為條件區(qū)域，篩選后的結(jié)果顯示在原有區(qū)域，篩選后的工作表名不變。4.6對工作表中的數(shù)據(jù)進行高級篩選相關(guān)知識l篩選規(guī)則主要包括比較運算符和邏輯運算符兩部分。 1比較運算符 當(dāng)使用比較運算符比較兩個值時，結(jié)果為邏輯值。比較運算符主要有以下幾個：大于（）、小于（=）、小于等于（=）、等于（=）、不