信息技術(shù)基礎(chǔ)（Windows10+WPSOffice2019）項(xiàng)目4 WPS電子表格應(yīng)用操作

濟(jì)源職業(yè)技術(shù)學(xué)院項(xiàng)目4WPS電子表格應(yīng)用操作信息技術(shù)基礎(chǔ)(Windows10+WPSOffice2019)學(xué)習(xí)目標(biāo)（1）掌握WPS2019表格工作簿與工作表的基本操作。（2）掌握工作表的編輯和格式化操作。（3）熟悉WPS2019表格常用的公式和函數(shù)。（4）掌握WPS2019表格數(shù)據(jù)的排序、篩選、分類匯總等操作。（5）掌握WPS2019表格圖表的創(chuàng)建與編輯。（1）能根據(jù)實(shí)際需求創(chuàng)建、編輯WPS2019表格工作簿與工作表。（2）能將工作表中的數(shù)據(jù)以指定的格式顯示。（3）會(huì)使用WPS2019表格的公式和函數(shù)完成數(shù)據(jù)的統(tǒng)計(jì)。（4）能通過WPS2019表格的排序、篩選、分類匯總對(duì)數(shù)據(jù)進(jìn)行處理與分析。（5）能將工作表中的數(shù)據(jù)以圖表的形式顯示出來。知識(shí)目標(biāo)能力目標(biāo)素質(zhì)目標(biāo)（1）通過對(duì)實(shí)際問題的解決，培養(yǎng)學(xué)生的信息素養(yǎng)，把已掌握的知識(shí)和技能當(dāng)作工具，幫助自己學(xué)習(xí)或者工作。（2）通過項(xiàng)目小組的活動(dòng)，培養(yǎng)學(xué)生的團(tuán)隊(duì)協(xié)作、友愛互助精神。（3）通過項(xiàng)目實(shí)踐，培養(yǎng)學(xué)生規(guī)范操作、謹(jǐn)慎細(xì)致的職業(yè)習(xí)慣，務(wù)實(shí)高效的職業(yè)態(tài)度。（4）通過實(shí)訓(xùn)項(xiàng)目，培養(yǎng)學(xué)生自主學(xué)習(xí)和終身學(xué)習(xí)的意識(shí)，具備不斷學(xué)習(xí)和適應(yīng)發(fā)展的能力。???目錄任務(wù)4.1制作學(xué)生信息登記表制作學(xué)生成績(jī)統(tǒng)計(jì)表任務(wù)4.2圖書銷售數(shù)據(jù)管理任務(wù)4.3制作職稱結(jié)構(gòu)統(tǒng)計(jì)圖任務(wù)4.4銷售數(shù)據(jù)分析任務(wù)4.5目錄任務(wù)4.1

制作學(xué)生信息登記表制作學(xué)生成績(jī)統(tǒng)計(jì)表任務(wù)4.2圖書銷售數(shù)據(jù)管理任務(wù)4.3制作職稱結(jié)構(gòu)統(tǒng)計(jì)圖任務(wù)4.4銷售數(shù)據(jù)分析任務(wù)4.5任務(wù)4.1制作學(xué)生信息登記表任務(wù)4.1制作學(xué)生信息登記表4.1.1認(rèn)識(shí)WPS2019表格1.認(rèn)識(shí)WPS2019表格工作界面任務(wù)4.1制作學(xué)生信息登記表4.1.1認(rèn)識(shí)WPS2019表格2.認(rèn)識(shí)工作簿、工作表、單元格和區(qū)域（1）工作簿在WPS2019表格中，一個(gè)表格文檔就是一個(gè)工作簿。（2）工作表工作表是工作簿文件的一個(gè)組成部分，它是一個(gè)包含了行和列的二維表格。（3）單元格單元格是WPS2019表格工作簿的最小組成單位，也是用于存儲(chǔ)數(shù)據(jù)的基本單位。（4）區(qū)域區(qū)域的引用常用左上角和右下角單元格的地址來標(biāo)志，中間用“：”間隔。任務(wù)4.1制作學(xué)生信息登記表4.1.2工作簿與工作表的基本操作1.工作簿的建立與保存（1）WPS2019表格文件的建立單擊“新建標(biāo)簽”按鈕打開工作臺(tái)界面選擇“新建表格”→“新建空白表格”進(jìn)行創(chuàng)建。（2）保存工作簿使用Ctrl+S快捷鍵，或按F12鍵，彈出“另存文件”對(duì)話框，選擇保存的位置，輸入文件名“學(xué)生信息登記表”，文件類型默認(rèn)為“*.xlsx”，單擊“保存”按鈕即可完成工作簿保存。（3）關(guān)閉WPS2019表格工作簿單擊要關(guān)閉的工作簿標(biāo)簽右側(cè)的“關(guān)閉”按鈕，可關(guān)閉當(dāng)前工作簿。單擊WPS2019窗口右側(cè)的“關(guān)閉”按鈕會(huì)關(guān)閉整個(gè)WPSOffice2019軟件。任務(wù)4.1制作學(xué)生信息登記表4.1.2工作簿與工作表的基本操作2.管理工作表（1）插入新工作表單擊工作表標(biāo)簽“Sheet1”右側(cè)的“新建工作表”按鈕，就可以在工作表“Sheet1”之后插入一張新的工作表。（2）選擇多個(gè)工作表單擊第一個(gè)工作表標(biāo)簽，按住“Ctrl”鍵，分別單擊要選定的工作表標(biāo)簽。（3）重命名工作表雙擊工作表標(biāo)簽名“Sheet1”，輸入“學(xué)生基本信息登記表”，按“Enter”鍵確認(rèn)。（4）移動(dòng)或復(fù)制工作表使用鼠標(biāo)拖動(dòng)工作表到某個(gè)工作表之前（或之后），可實(shí)現(xiàn)工作表的移動(dòng)；如果在拖動(dòng)的同時(shí)按住“Ctrl”鍵，可實(shí)現(xiàn)工作表的復(fù)制。（5）刪除工作表將鼠標(biāo)指針指向要?jiǎng)h除的工作表，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“刪除工作表”命令。（6）隱藏和取消隱藏工作表將鼠標(biāo)指針指向要隱藏的工作表，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“隱藏工作表”命令，可將選定的工作表隱藏。（7）設(shè)置工作表標(biāo)簽顏色在工作表標(biāo)簽上單擊鼠標(biāo)右鍵，在快捷菜單中選擇“工作表標(biāo)簽顏色”命令，然后在子菜單中選擇所需顏色。任務(wù)4.1制作學(xué)生信息登記表4.1.2工作簿與工作表的基本操作3.編輯工作表（1）單元格與單元格區(qū)域的選擇①單擊某個(gè)單元格即可選中它。②按下鼠標(biāo)左鍵不放，從第一個(gè)單元格拖曳到最后一個(gè)單元格，可以選中多個(gè)相鄰的單元格區(qū)域；或單擊要選擇區(qū)域的第一個(gè)單元格，然后按住“Shift”鍵單擊最后一個(gè)單元格，即可選中它們之間的所有單元格。③按“Ctrl”鍵可以選中多個(gè)不相鄰的單元格區(qū)域。④按下“Ctrl+A”組合鍵，可以將工作表中的所有單元格選定。（2）選擇、插入與刪除行或列①用鼠標(biāo)單擊“行號(hào)”或“列號(hào)”，選擇單行或單列。②按住鼠標(biāo)左鍵不放，對(duì)行或列進(jìn)行拖曳，可以選中多個(gè)相鄰的行或列。③在行號(hào)或列號(hào)上單擊鼠標(biāo)左鍵的同時(shí)，按下“Ctrl”鍵，再單擊其他行號(hào)或列號(hào)，可以選中多個(gè)不相鄰的行或列。④在行號(hào)或列號(hào)上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“插入”命令，輸入或選擇行數(shù)/列數(shù)，可在選定的行或列前插入行或列。⑤選擇要?jiǎng)h除的行或列，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“刪除”命令，刪除選定的行或列。任務(wù)4.1制作學(xué)生信息登記表4.1.2工作簿與工作表的基本操作3.編輯工作表（4）選擇性粘貼操作方法：選擇需要復(fù)制的單元格，在選區(qū)中單擊右鍵，在彈出的快捷菜單中選擇“復(fù)制”命令。選定目標(biāo)單元格，單擊鼠標(biāo)右鍵，在快捷菜單中單擊“選擇性粘貼”菜單命令，彈出“選擇性粘貼”對(duì)話框，如圖4-4所示。在“選擇性粘貼”對(duì)話框中，選擇所需粘貼的選項(xiàng)，單擊“確定”按鈕，退出對(duì)話框。（5）凍結(jié)窗格凍結(jié)首行首列：?jiǎn)螕艄ぷ鞅碇械娜我鈫卧瘢x擇“視圖”選項(xiàng)卡，在功能區(qū)中單擊“凍結(jié)窗格”按鈕，在下拉菜單中選擇“凍結(jié)首行”命令，此時(shí)縱向滾動(dòng)工作表頁(yè)面，第一行數(shù)據(jù)會(huì)始終保持在頁(yè)面中顯示。如選擇“凍結(jié)首列”命令，當(dāng)工作表橫向滾動(dòng)時(shí)，第一列數(shù)據(jù)會(huì)始終保持在頁(yè)面中顯示。凍結(jié)多行多列或指定區(qū)域：可選中某個(gè)單元格，此時(shí)在“凍結(jié)窗格”下拉菜單中就可以看到“凍結(jié)至第某行某列”的選項(xiàng)，可按需求進(jìn)行設(shè)置。取消凍結(jié)：?jiǎn)螕簟皟鼋Y(jié)窗格”按鈕，在下拉菜單中選擇“取消凍結(jié)窗格”命令。圖4-4

“選擇性粘貼”對(duì)話框任務(wù)4.1制作學(xué)生信息登記表4.1.3編輯數(shù)據(jù)1.輸入數(shù)據(jù)①

打開“學(xué)生基本信息登記表.xlsx”，在當(dāng)前工作表“Sheet1”中，選中A1單元格，輸入標(biāo)題“學(xué)生基本信息登記表”。②在A2單元格中輸入“學(xué)號(hào)”，并按“Tab”鍵，將B2單元格作為當(dāng)前活動(dòng)單元格，輸入“姓名”，使用同樣的方法依次輸入表格標(biāo)題行的內(nèi)容，如圖所示。③在A3單元格中輸入學(xué)號(hào)“10220301”，按“Enter”鍵后，單元格中的內(nèi)容默認(rèn)為右對(duì)齊顯示，默認(rèn)格式為數(shù)字格式。因?yàn)閷W(xué)生學(xué)號(hào)不需要參與數(shù)學(xué)運(yùn)算，需將其設(shè)置為文本類型。操作方法為：在“10220301”前輸入西文單引號(hào)“’”；或先輸入西文單引號(hào)“’”，然后輸入學(xué)號(hào)“10220301”。④

將鼠標(biāo)指針指向A3單元格的“填充柄”（位于單元格右下角的小方塊），此時(shí)鼠標(biāo)指針變?yōu)楹谑郑醋∈髽?biāo)左鍵向下拖動(dòng)填充柄，拖至A22單元格時(shí)釋放鼠標(biāo)即可，此時(shí)A3:A22單元格區(qū)域中依次顯示10220301～10220320的學(xué)生學(xué)號(hào)。1.輸入數(shù)據(jù)⑤

選擇B3單元格，在B3單元格中輸入姓名“趙孟軻”，按“Enter”鍵。在B4單元格中輸入姓名“郭晨旭”，按“Enter”鍵。用同樣的方法依次輸入其他學(xué)生的姓名。⑥選擇C3單元格，在C3單元格中輸入“男”，將鼠標(biāo)指針指向C3單元格的右下角，當(dāng)鼠標(biāo)指針變?yōu)楹谑謺r(shí)，雙擊填充柄，將“性別”列的內(nèi)容全部填充為“男”。⑦按住“Ctrl”鍵，依次單擊性別需修改為“女”的單元格，選擇完成后輸入“女”，按“Ctrl+Enter”組合鍵，可將選中的單元格內(nèi)容修改為“女”。⑧選中D3單元格，在D3單元格中輸入出生日期“1991/5/21”，按“Enter”鍵。在D4單元格中輸入出生日期“1990/9/16”，按“Enter”鍵。用同樣的方法依次輸入其他學(xué)生的出生日期。⑨依次輸入學(xué)生的籍貫、聯(lián)系電話和電子郵箱信息，完成表格數(shù)據(jù)的輸入，將文檔保存為“學(xué)生基本信息登記表.xlsx”，如圖所示。任務(wù)4.1制作學(xué)生信息登記表4.1.3編輯數(shù)據(jù)1.輸入數(shù)據(jù)任務(wù)4.1制作學(xué)生信息登記表4.1.3編輯數(shù)據(jù)2.設(shè)置單元格數(shù)據(jù)類型（1）數(shù)值型數(shù)據(jù)輸入在WPS2019表格中，單元格中輸入的數(shù)值自動(dòng)向右對(duì)齊。如果單元格中以“#”顯示，表示該單元格所在列的寬度不能足夠顯示數(shù)值，需調(diào)整所在列寬度或改變數(shù)字顯示格式。（2）文本型數(shù)據(jù)輸入文本型數(shù)據(jù)默認(rèn)為左對(duì)齊。字符串文本可在選定的單元格中直接輸入；對(duì)于學(xué)號(hào)、郵政編碼、身份證號(hào)等不需要參與數(shù)學(xué)運(yùn)算的數(shù)字信息，可將其設(shè)置為文本類型。在輸入時(shí)需要先輸入一個(gè)西文單引號(hào)“’”，然后再輸入相關(guān)數(shù)字（3）時(shí)間和日期型數(shù)據(jù)輸入日期型數(shù)據(jù)用形式“yy/mm/dd”表示，時(shí)間型數(shù)據(jù)用形式“hh:mm”表示，WPS2019表格會(huì)自動(dòng)將輸入的日期和時(shí)間型數(shù)據(jù)采用向右對(duì)齊顯示。任務(wù)4.1制作學(xué)生信息登記表4.1.3編輯數(shù)據(jù)1.設(shè)置單元格對(duì)齊方式①在“學(xué)生基本信息登記表”中選中A1:G1單元格區(qū)域。②選擇“開始”選項(xiàng)卡，在功能區(qū)中單擊“對(duì)齊方式”選項(xiàng)組右下角的“對(duì)話框啟動(dòng)器”按鈕，打開“單元格格式”對(duì)話框，在“對(duì)齊”選項(xiàng)卡中，將“水平對(duì)齊”方式設(shè)置為“居中”，“垂直對(duì)齊”方式設(shè)置為“居中”，在“文本控制”選項(xiàng)區(qū)域中選中“合并單元格”復(fù)選框，單擊“確定”按鈕，完成標(biāo)題行設(shè)置。2．設(shè)置單元格字體格式①選中A1:G1區(qū)域，選擇“開始”選項(xiàng)卡，在功能區(qū)中單擊“字體”選項(xiàng)組右下角的“對(duì)話框啟動(dòng)器”按鈕，打開“單元格格式”對(duì)話框，在“字體”選項(xiàng)卡中，將“字體(F):”設(shè)置為“微軟雅黑”；“字形(O):”設(shè)置為“粗體”；“字號(hào)(S):”設(shè)置為“27”；顏色設(shè)置為“紅色”，單擊“確定”按鈕即可。②選中區(qū)域A2:G22，將字體設(shè)置為“楷體”，字號(hào)設(shè)置為“12”，對(duì)齊方式設(shè)置為“水平居中”。任務(wù)4.1制作學(xué)生信息登記表4.1.4設(shè)置單元格格式3．設(shè)置單元格邊框選中所有數(shù)據(jù)區(qū)域A2:G22，在選中的數(shù)據(jù)區(qū)域中單擊鼠標(biāo)右鍵，在彈出的快捷菜單中，選擇“設(shè)置單元格格式”命令，打開“單元格格式”對(duì)話框。在“單元格格式”對(duì)話框中，選擇“邊框”選項(xiàng)卡，在線條樣式中選擇“雙細(xì)線”，在“預(yù)置”欄內(nèi)單擊“外邊框”按鈕；然后在線條樣式中選擇“虛線”，在“顏色(C):”下拉列表中選擇“紅色”，在“預(yù)置”欄內(nèi)單擊“內(nèi)部”按鈕，如圖所示，單擊“確定”按鈕完成邊框設(shè)置。任務(wù)4.1制作學(xué)生信息登記表4.1.4設(shè)置單元格格式4．設(shè)置單元格圖案選中A2:G2區(qū)域，在選區(qū)中單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開“單元格格式”對(duì)話框，選擇“圖案”選項(xiàng)卡，如圖4-14所示；單擊“填充效果”按鈕，在打開的“填充效果”對(duì)話框中，單擊“顏色2”，選擇主題顏色第一列第三行的“白色，背景1，深色15%”，“底紋樣式”選擇“水平”，變形效果1，如圖4-15所示。單擊“確定”按鈕，為A2:G2區(qū)域設(shè)置漸變背景效果。任務(wù)4.1制作學(xué)生信息登記表4.1.4設(shè)置單元格格式圖4-14

“單元格格式”對(duì)話框“圖案”選項(xiàng)卡

圖4-15

填充效果5．設(shè)置數(shù)字格式單擊“數(shù)字”選項(xiàng)組右下角的“對(duì)話框啟動(dòng)器”按鈕，在“設(shè)置單元格格式”對(duì)話框中，將數(shù)字分類設(shè)置為“數(shù)值”，在“小數(shù)位數(shù)”后選擇“2”，即保留兩位小數(shù)。小數(shù)位數(shù)也可通過“數(shù)字”選項(xiàng)組來快速設(shè)置。6．設(shè)置行高和列寬①選中標(biāo)題行，選擇“開始”選項(xiàng)卡，單擊“行和列”按鈕，在下拉菜單中選擇“行高”項(xiàng)，打開“行高”對(duì)話框，在“行高”文本框中，輸入“40”，單位“磅”，單擊“確定”按鈕，完成行高設(shè)置。②使用同樣的方法，將其他數(shù)據(jù)信息行高設(shè)置為16磅。③選中G列，選擇“開始”選項(xiàng)卡，單擊“行和列”按鈕，在下拉菜單中選擇“最適合的列寬”項(xiàng)，對(duì)“電子郵箱”所在列的寬度進(jìn)行自動(dòng)調(diào)整。④選中A:F列，在選區(qū)中單擊鼠標(biāo)右鍵，在彈出的快捷菜單中，選擇“列寬”命令。打開“列寬”對(duì)話框，在“列寬”文本框中，輸入“13”，單位“字符”，單擊“確定”按鈕，完成列寬設(shè)置。任務(wù)4.1制作學(xué)生信息登記表4.1.4設(shè)置單元格格式7．設(shè)置工作表背景設(shè)置方法：選擇“頁(yè)面布局”選項(xiàng)卡，單擊“背景圖片”按鈕，在打開的“工作表背景”對(duì)話框中，選擇素材文件夾中名為“背景”的圖片，單擊“打開”按鈕，完成工作表背景設(shè)置。8.設(shè)置單元格條件格式①

選中區(qū)域E3:E22，在“開始”選項(xiàng)卡中單擊“條件格式”按鈕，在彈出的下拉菜單中，選擇“突出顯示單元格規(guī)則”菜單下的“等于”命令。②在“等于”對(duì)話框中，左側(cè)文本框內(nèi)輸入“河南省”，單擊“設(shè)置為”右側(cè)下拉列表按鈕，選擇“自定義格式”菜單，在彈出的“設(shè)置單元格格式”對(duì)話框中，將字體格式設(shè)置為“加粗、雙下劃線”。單擊“確定”按鈕，完成籍貫格式設(shè)置。任務(wù)4.1制作學(xué)生信息登記表4.1.4設(shè)置單元格格式完成條件格式的設(shè)置任務(wù)后，學(xué)生基本信息登記表效果如圖所示。圖4-22“條件格式”設(shè)置后效果任務(wù)4.1制作學(xué)生信息登記表4.1.4設(shè)置單元格格式1.頁(yè)面設(shè)置①選擇“頁(yè)面布局”選項(xiàng)卡，在“頁(yè)面設(shè)置”選項(xiàng)組中，單擊右下角的“對(duì)話框啟動(dòng)器”按鈕，打開“頁(yè)面設(shè)置”對(duì)話框，選擇“頁(yè)面”選項(xiàng)卡，在“方向”選項(xiàng)區(qū)域選擇“橫向”單選按鈕，紙張大小設(shè)置為“A4”，其他參數(shù)采用默認(rèn)。②設(shè)置頁(yè)邊距，參數(shù)如圖4-24所示。③

選擇“頁(yè)眉/頁(yè)腳”選項(xiàng)卡，單擊“自定義頁(yè)腳”按鈕，打開“頁(yè)腳”對(duì)話框。在頁(yè)腳“中部”文本框中插入頁(yè)碼、插入頁(yè)數(shù)。④單擊“確定”按鈕，返回“頁(yè)面設(shè)置”對(duì)話框，在“頁(yè)眉/頁(yè)腳”選項(xiàng)卡中設(shè)置頁(yè)腳類型為“第1頁(yè)，共？頁(yè)”，單擊“確定”按鈕完成頁(yè)腳設(shè)置。任務(wù)4.1制作學(xué)生信息登記表4.1.5頁(yè)面設(shè)置

圖4-24

設(shè)置“頁(yè)邊距”新增“自定義序列”操作方法：?jiǎn)螕簟拔募辈藛?，選擇“選項(xiàng)”命令，打開“選項(xiàng)”對(duì)話框，在左側(cè)列表中選擇“自定義序列”，如圖4-27所示。在“自定義序列”列表框中選擇“新序列”，然后在“輸入序列”列表框中依次輸入26個(gè)英文字母序列，每輸入一個(gè)字母后必須按“Enter”鍵結(jié)束，或者每一項(xiàng)之間用半角狀態(tài)下的“,”分隔。輸入完成后單擊“添加”按鈕，新定義的字母序列就會(huì)出現(xiàn)在“自定義序列”列表中。此時(shí)，在工作表單元格中任意輸入一個(gè)英文字母，再用鼠標(biāo)拖動(dòng)填充柄填充，就可以實(shí)現(xiàn)自動(dòng)填充英文字母序列了。任務(wù)4.1制作學(xué)生信息登記表4.1.6自定義填充序列

圖4-27“自定義序列”對(duì)話框【能力拓展】實(shí)訓(xùn)1制作銷貨憑單【能力拓展】實(shí)訓(xùn)2制作員工信息登記表目錄任務(wù)4.1

制作學(xué)生信息登記表制作學(xué)生成績(jī)統(tǒng)計(jì)表任務(wù)4.2圖書銷售數(shù)據(jù)管理任務(wù)4.3制作職稱結(jié)構(gòu)統(tǒng)計(jì)圖任務(wù)4.4銷售數(shù)據(jù)分析任務(wù)4.5任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.1成績(jī)匯總①打開“各科成績(jī)表”工作簿，在“語文成績(jī)”工作表標(biāo)簽上單擊右鍵，在彈出的快捷菜單中，選擇“移動(dòng)或復(fù)制工作表”，打開“移動(dòng)或復(fù)制工作表”對(duì)話框，在該對(duì)話框中的工作簿下拉列表中選擇“新工作簿”，并建立副本，將“各科成績(jī)表”復(fù)制到一個(gè)新的工作簿1中，在新建工作簿1中，選擇“文件”選項(xiàng)卡中的“另存為”命令，將該工作簿保存并命名為“學(xué)生成績(jī)統(tǒng)計(jì)表.xlsx”。②將“各科成績(jī)表”工作簿中“數(shù)學(xué)成績(jī)”工作表中的數(shù)學(xué)成績(jī)和“英語成績(jī)”工作表中的英語成績(jī)復(fù)制到“學(xué)生成績(jī)統(tǒng)計(jì)表”工作簿中，并按照右圖所示，增加其余行、列內(nèi)容，完善學(xué)生成績(jī)統(tǒng)計(jì)表的信息；將“語文成績(jī)”工作表重命名為“學(xué)生成績(jī)統(tǒng)計(jì)表”。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.1成績(jī)匯總③將A1:I1合并為一個(gè)單元格，文字居中對(duì)齊，字體為“華文中宋”，加粗，字號(hào)20；將A23:C23合并為一個(gè)單元格，文字居中對(duì)齊，字體加粗。④選中區(qū)域A2:I23，選擇“開始”選項(xiàng)卡，在“對(duì)齊方式”選項(xiàng)組中，單擊“垂直居中”、“水平居中”按鈕，設(shè)置文字居中對(duì)齊；單擊“表格樣式”按鈕，選擇“表樣式淺色9”，單擊“確定”按鈕。選中區(qū)域K3:M7，設(shè)置表格樣式為“表樣式淺色10”，內(nèi)容水平居中；選中區(qū)域K12:M15，設(shè)置表格樣式為“表樣式淺色10”，內(nèi)容水平居中；選中區(qū)域K20:M22，設(shè)置表格樣式為“表樣式淺色10”，內(nèi)容水平居中。⑤將第一行的行高設(shè)置為40磅，內(nèi)容垂直居中；第2至23行行高設(shè)置為20磅；A:M列寬為9字符。⑥在頁(yè)面布局中，將頁(yè)邊距上下左右均設(shè)置為1.5厘米，將紙張方向設(shè)置為橫向。完成效果如圖4-34所示。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.1成績(jī)匯總圖4-34

學(xué)生成績(jī)統(tǒng)計(jì)表1．使用公式計(jì)算總成績(jī)使用系統(tǒng)提供的函數(shù)或在單元格中輸入公式可以實(shí)現(xiàn)許多復(fù)雜的運(yùn)算，從而避免手動(dòng)計(jì)算的復(fù)雜和易錯(cuò)問題，數(shù)據(jù)修改后公式的計(jì)算結(jié)果還可以自動(dòng)更新。學(xué)生總成績(jī)是各科成績(jī)相加之和，下面利用公式來計(jì)算學(xué)生的總成績(jī)，操作步驟如下。①選中G3單元格，輸入“=D3+E3+F3”，按“Enter”鍵，計(jì)算出第一個(gè)學(xué)生的總成績(jī)。②選中G3單元格，將鼠標(biāo)指針移動(dòng)到單元格的右下方，當(dāng)鼠標(biāo)指針的形狀變?yōu)楹谑謺r(shí)，按住鼠標(biāo)左鍵拖動(dòng)至G22單元格，使用填充柄對(duì)公式進(jìn)行復(fù)制，計(jì)算出所有學(xué)生的總成績(jī)。③

設(shè)置單元格格式為“數(shù)值”，保留一位小數(shù)。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.2創(chuàng)建公式2．公式中的運(yùn)算符（1）算術(shù)運(yùn)算符：算術(shù)運(yùn)算符連接數(shù)字并計(jì)算結(jié)果，包括加（+）、減（-）、乘（*）、除（/）、冪（^）、百分號(hào)（%）、負(fù)號(hào)（-）等。（2）比較運(yùn)算符：比較兩個(gè)數(shù)據(jù)的大小并返回邏輯值真（True）或假（False），包括等于（=）、大于（>）、大于等于（>=）、小于（<）、小于等于（<=）和不等于（<>）。（3）文本運(yùn)算符（&）：將多個(gè)字符連接成一個(gè)新的字符。（4）引用運(yùn)算符：將運(yùn)算區(qū)域合并運(yùn)算，包括冒號(hào)（：）、逗號(hào)（，）和空格。在公式中如果同時(shí)使用多個(gè)不同類型運(yùn)算符，將按照運(yùn)算符的優(yōu)先級(jí)順序進(jìn)行計(jì)算。冒號(hào)（：）→逗號(hào)（，）→負(fù)號(hào)（-）→百分號(hào)（%）→乘方（^）→乘法和除法運(yùn)算（*和/）→加法和減法運(yùn)算（+和-）→文本連接運(yùn)算（&）→比較運(yùn)算符（=、<、>、<=、>=、<>）。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.2創(chuàng)建公式3．公式中的引用在編輯公式時(shí)，有時(shí)會(huì)引用單元格地址。在WPS2019表格中，根據(jù)引用的單元格與被引用的單元格之間的位置關(guān)系，將引用分為：相對(duì)引用、絕對(duì)引用、混和引用和跨工作表格引用。（1）相對(duì)引用。如計(jì)算總成績(jī)時(shí)輸入的公式“=D3+E3+F3”，其中“D3”“E3”“F3”均為相對(duì)引用的目標(biāo)地址。（2）絕對(duì)引用。單元格中的絕對(duì)引用是指在行號(hào)和列標(biāo)前寫上“$”，如“$H$2”，它總是在指定位置引用單元格H2。（3）混合引用。混合引用是指單元格地址中既有相對(duì)引用，也有絕對(duì)引用。可以是絕對(duì)列和相對(duì)行（如$B6），也可以是絕對(duì)行和相對(duì)列（如B$6）。（4）跨工作表格引用。其形式為：“工作表名!單元格或單元格區(qū)域地址”。例如，“Sheet2!A1:D5”表示引用Sheet2工作表中A1:D5單元格區(qū)域中的數(shù)據(jù)。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.2創(chuàng)建公式1．函數(shù)說明（1）求和函數(shù)：SUM()。（2）平均值函數(shù)：AVERAGE()。（3）最小值函數(shù)：MIN(

)（4）平均值函數(shù)：AVERAGE(

)（5）條件平均值函數(shù)：AVERAGEIF(

)（6）計(jì)數(shù)函數(shù)：COUNT(

)（7）條件統(tǒng)計(jì)函數(shù)：COUNTIF(

)任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)（8）多區(qū)域條件統(tǒng)計(jì)函數(shù)：COUNTIFS(

)（9）條件求和函數(shù)：SUMIF(

)（10）判斷函數(shù)：IF(

)（11）多條件判斷函數(shù)：IFS(

)（12）RANK函數(shù)（13）TODAY函數(shù)（14）DATE函數(shù)2．使用函數(shù)（1）計(jì)算學(xué)生成績(jī)。選中D23單元格，單擊編輯欄中的“插入函數(shù)”按鈕，打開“插入函數(shù)”對(duì)話框，在“選擇函數(shù)”列表中選擇“AVERAGE”函數(shù)，單擊“確定”按鈕。

彈出“函數(shù)參數(shù)”對(duì)話框，單擊“折疊”按鈕折疊“函數(shù)參數(shù)”對(duì)話框，拖動(dòng)鼠標(biāo)選擇單元格D3:D22，按回車鍵或單擊“還原”按鈕返回“函數(shù)參數(shù)”對(duì)話框，單擊“確定”按鈕，計(jì)算出語文科目的平均成績(jī)。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)

選中D23單元格，將鼠標(biāo)移動(dòng)到單元格的右下方，當(dāng)鼠標(biāo)變成實(shí)心的“+”形狀時(shí)，按下鼠標(biāo)左鍵并向右拖動(dòng)，計(jì)算出數(shù)學(xué)和英語科目的平均成績(jī)。設(shè)置數(shù)字格式為“數(shù)值”，保留一位小數(shù)。

選中L4單元格，單擊編輯欄左側(cè)的插入函數(shù)按鈕，打開“插入函數(shù)”對(duì)話框，在查找函數(shù)文本框中輸入“MAX”，然后在選擇函數(shù)列表中選擇“MAX”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，將數(shù)值1文本框中的數(shù)據(jù)地址設(shè)置為“D3:D22”，單擊“確定”按鈕，統(tǒng)計(jì)語文成績(jī)最高分；使用相同的方法，分別在L5、L6、L7中統(tǒng)計(jì)出數(shù)學(xué)、英語、總成績(jī)的最高分。

選中M4單元格，單擊編輯欄左側(cè)的插入函數(shù)按鈕，打開“插入函數(shù)”對(duì)話框，在查找函數(shù)文本框中輸入“MIN”，然后在選擇函數(shù)列表中選擇“MIN”函數(shù)；單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，將數(shù)值1文本框中的數(shù)據(jù)地址設(shè)置為“D3:D22”，單擊“確定”按鈕，統(tǒng)計(jì)語文成績(jī)最低分；使用相同的方法，分別在M5、M6、M7中統(tǒng)計(jì)出數(shù)學(xué)、英語、總成績(jī)的最低分。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)

選中L15單元格，單擊編輯欄左側(cè)的插入函數(shù)按鈕，打開“插入函數(shù)”對(duì)話框，在“選擇函數(shù)”列表中選擇“COUNT”函數(shù)，單擊“確定”按鈕。彈出“函數(shù)參數(shù)”對(duì)話框，將值1文本框中的數(shù)據(jù)地址設(shè)置為“D3:D22”，單擊“確定”按鈕，計(jì)算全班總?cè)藬?shù)。

選中L13單元格，單擊編輯欄左側(cè)的插入函數(shù)按鈕，打開“插入函數(shù)”對(duì)話框，將選擇類別切換到“統(tǒng)計(jì)”，在“選擇函數(shù)”列表中選擇“COUNTIF”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，如圖4-37所示。設(shè)置區(qū)域文本框中的數(shù)據(jù)范圍為絕對(duì)引用C3:C22，即“$C$3:$C$22”，設(shè)置條件文本框中的內(nèi)容為“C3”或輸入“男”，單擊“確定”按鈕，統(tǒng)計(jì)班中男生人數(shù)。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)

將鼠標(biāo)移動(dòng)到L13單元格的右下角，拖動(dòng)“填充柄”填充L14單元格，選中L14單元格，將編輯欄中的“男”改為“女”，完成女生人數(shù)的統(tǒng)計(jì)。

選中M21單元格，單擊編輯欄左側(cè)的插入函數(shù)按鈕，打開“插入函數(shù)”對(duì)話框，將選擇類別切換到“統(tǒng)計(jì)”，在“選擇函數(shù)”列表中選擇“COUNTIFS”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，如圖4-38所示。設(shè)置區(qū)域1文本框中的數(shù)據(jù)范圍為“$G$3:$G$22”，在條件1文本框中輸入“K21”，設(shè)置區(qū)域2的數(shù)據(jù)范圍為“$C$3:$C$22”，在條件2文本框中輸入“男”，單擊“確定”按鈕，統(tǒng)計(jì)班中男生總成績(jī)大于等于270的人數(shù)。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)圖4-38

“COUNTIFS函數(shù)參數(shù)”對(duì)話框⑩

將鼠標(biāo)移動(dòng)到M21單元格的右下角，拖動(dòng)“填充柄”填充M22單元格，選中M22單元格，將編輯欄中的“男”改為“女”，統(tǒng)計(jì)班中女生總成績(jī)大于等于270的人數(shù)。?選中M13單元格，單擊編輯欄左側(cè)的插入函數(shù)按鈕，打開“插入函數(shù)”對(duì)話框，將選擇類別切換到“統(tǒng)計(jì)”，在“選擇函數(shù)”列表中選擇“AVERAGEIF”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框。設(shè)置區(qū)域文本框中的數(shù)據(jù)范圍為“$C$3:$C$22”，設(shè)置條件文本框中的內(nèi)容為“男”，設(shè)置求平均值區(qū)域文本框中的內(nèi)容為“$G$3:$G$22”，單擊“確定”按鈕，計(jì)算班中所有男生總成績(jī)的平均分。?將鼠標(biāo)移動(dòng)到M4單元格的右下角，拖動(dòng)“填充柄”填充M5單元格，選中M5單元格，將編輯欄中的“男”改為“女”，計(jì)算班中所有女生總成績(jī)的平均分。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)（2）學(xué)生成績(jī)等級(jí)劃分。根據(jù)總成績(jī)進(jìn)行等級(jí)總評(píng)，將總成績(jī)大于等于240的劃分為“優(yōu)秀”，總成績(jī)小于240且大于等于180的劃分為“合格”，總成績(jī)小于180的劃分為“不合格”。等級(jí)劃分需用到IF函數(shù)來解決。

選中H3單元格，單擊編輯欄左側(cè)的插入函數(shù)按鈕，彈出“插入函數(shù)”對(duì)話框，選擇“IF”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框，在測(cè)試條件文本框中輸入條件“G3>=240”，真值文本框中輸入“優(yōu)秀”，如圖4-40所示。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)圖4-40

“IF函數(shù)參數(shù)”對(duì)話框

當(dāng)G3單元格的數(shù)值小于240時(shí)，總成績(jī)有“合格”、“不及格”兩種情況，因此，需要在假值文本框中輸入IF函數(shù)嵌套。將光標(biāo)定位在假值文本框中，單擊名稱框中的IF函數(shù)，如圖4-40框選位置，彈出嵌套函數(shù)參數(shù)的對(duì)話框，在函數(shù)參數(shù)對(duì)話框中輸入?yún)?shù)，如圖4-41所示。如果G3單元格的值大于等于180，則H3單元格的值為“合格”，否則H3單元格的值為“不合格”。

選中H3單元格，拖動(dòng)“填充柄”向下填充，完成所有學(xué)生總成績(jī)等級(jí)的評(píng)定劃分。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)圖4-41

“IF函數(shù)嵌套參數(shù)”對(duì)話框（3）計(jì)算學(xué)生排名為方便了解班中學(xué)生個(gè)人學(xué)習(xí)情況，可根據(jù)學(xué)生總成績(jī)按從高到低進(jìn)行排名，為評(píng)優(yōu)評(píng)先做綜合考慮。

選中I3單元格，選擇“公式”選項(xiàng)卡，單擊“插入函數(shù)”按鈕，打開“插入函數(shù)”對(duì)話框，將“選擇類別”切換到“全部”，在“選擇函數(shù)”列表中選擇“RANK”函數(shù)，單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對(duì)話框。

在“函數(shù)參數(shù)”對(duì)話框中的Number文本框中輸入“G3”，即指定參與排名的是G3單元格中的學(xué)生總成績(jī)；在Ref文本框中輸入“$G$3:$G$22”，即絕對(duì)引用G3到G22單元格區(qū)域的數(shù)據(jù)；Order文本框不輸入任何數(shù)據(jù)，按默認(rèn)的降序進(jìn)行排名，如圖4-42所示。單擊“確定”按鈕后，在I3單元格中可以顯示出第一個(gè)學(xué)生的排名為“2”。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)圖4-42

“函數(shù)參數(shù)”對(duì)話框

選中I3單元格，按住鼠標(biāo)左鍵向下拖動(dòng)填充柄到數(shù)據(jù)的最后一行，計(jì)算出所有學(xué)生的排名情況，效果如圖4-43所示。選擇“文件”選項(xiàng)卡中“保存”命令，保存“學(xué)生成績(jī)統(tǒng)計(jì)表.xlsx”工作簿。圖4-43“學(xué)生成績(jī)統(tǒng)計(jì)表”效果圖任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)3．函數(shù)出錯(cuò)信息解決技巧（1）####錯(cuò)誤原因：?jiǎn)卧裰械臄?shù)據(jù)太長(zhǎng)，單元格公式所產(chǎn)生的結(jié)果太大，或是日期和時(shí)間格式的單元格做減法，出現(xiàn)了負(fù)值。解決方法：調(diào)整列寬，使結(jié)果能夠完全顯示。（2）#VALUE!錯(cuò)誤原因：使用錯(cuò)誤的參數(shù)或運(yùn)算對(duì)象類型，或者當(dāng)公式自動(dòng)更正功能不能更正公式時(shí)，產(chǎn)生錯(cuò)誤值。解決方法：確認(rèn)公式或函數(shù)所需的運(yùn)算符或參數(shù)是否正確，并且公式引用的單元格中包含有效的數(shù)值；確認(rèn)數(shù)組常量不是單元格引用、公式或函數(shù)；將數(shù)值區(qū)域改為單一數(shù)值。修改數(shù)值區(qū)域，使其包含公式所在的數(shù)據(jù)行或列。（3）#DIV/0!錯(cuò)誤原因：在公式中，除數(shù)使用了空單元格或是包含零值單元格的單元格引用。解決方法：修改單元格引用，或?qū)卧窆街谐龜?shù)設(shè)為非零的數(shù)值。（4）#NUM!錯(cuò)誤原因：提供了無效的參數(shù)給工作表函數(shù)；公式的結(jié)果太大或太小而無法在工作表中顯示。解決方法：確認(rèn)函數(shù)中使用的參數(shù)類型正確；如果是公式結(jié)果太大或太小，就要修改公式。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)（5）#REF!錯(cuò)誤原因：刪除了由其他公式引用的單元格，或?qū)⒁苿?dòng)單元格粘貼到了由其他公式引用的單元格中。解決方法：更改公式或者在刪除或粘貼單元格之后，立即單擊“撤消”按鈕，以恢復(fù)工作表中的單元格。（6）#NULL!錯(cuò)誤原因：在公式中的兩個(gè)范圍之間插入了一個(gè)空格以表示交叉點(diǎn)，但這兩個(gè)范圍沒有公共單元格。解決方法：取消兩個(gè)范圍之間的空格。（7）#N/A錯(cuò)誤原因：在函數(shù)或公式中沒有可用數(shù)值，從而產(chǎn)生了錯(cuò)誤值。解決方法：如果工作表中某些單元格暫時(shí)沒有數(shù)值，在這些單元格中輸入“"#N/A"”，這樣一來公式在引用這些單元格時(shí)將不進(jìn)行數(shù)值計(jì)算，而是返回#N/A。任務(wù)4.2制作學(xué)生成績(jī)統(tǒng)計(jì)表4.2.3使用函數(shù)【能力拓展】實(shí)訓(xùn)1

職工工資統(tǒng)計(jì)表【能力拓展】實(shí)訓(xùn)2學(xué)生競(jìng)賽成績(jī)統(tǒng)計(jì)目錄任務(wù)4.1

制作學(xué)生信息登記表制作學(xué)生成績(jī)統(tǒng)計(jì)表任務(wù)4.2圖書銷售數(shù)據(jù)管理任務(wù)4.3制作職稱結(jié)構(gòu)統(tǒng)計(jì)圖任務(wù)4.4銷售數(shù)據(jù)分析任務(wù)4.5任務(wù)4.3

圖書銷售數(shù)據(jù)管理任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.1記錄單的使用WPS2019表格中的記錄單使用對(duì)話框的形式，將表格中的記錄一條一條地顯示，用戶可通過記錄單對(duì)表格中的記錄進(jìn)行添加、刪除、查看或修改等操作。①打開“圖書銷售情況表.xlsx”工作簿，選中A1:F1單元格合并后居中，將“圖書銷售情況表”字體設(shè)置為“宋體、加粗、20磅”。②單擊“快速訪問”工具欄擴(kuò)展按鈕，在彈出的下拉菜單中選擇“其他命令”，打開“選項(xiàng)”對(duì)話框；或單擊“文件”菜單，選擇“選項(xiàng)”命令，打開“選項(xiàng)”對(duì)話框，在左側(cè)列表中選擇“快速訪問工具欄”。③在搜索框中輸入“記錄單”，選中列表中的記錄單，單擊添加按鈕，將“記錄單”命令添加到快速訪問工具欄中，如圖4-47所示；單擊確定按鈕完成添加。④單擊“快速訪問”工具欄中“記錄單”按鈕，打開“圖書銷售情況表”新建記錄對(duì)話框。在該對(duì)話框中，用戶可以對(duì)工作表中的數(shù)據(jù)記錄進(jìn)行添加、刪除、查詢等操作。單擊“新建”按鈕，在“新建記錄”對(duì)話框中輸入記錄值“第2分店，圖像處理，1，279，45.9”，將添加一條新記錄。①選中F3單元格，在編緝欄中輸入“=D3*E3”，按下“Enter”鍵，計(jì)算出第1分店銷售圖像處理圖書的銷售額。②選中F3單元格，將鼠標(biāo)移動(dòng)到單元格的右下方，拖動(dòng)填充柄，計(jì)算出所有圖書的銷售額。③選中區(qū)域F3:F22，選擇“開始”選項(xiàng)卡，將數(shù)字格式設(shè)置為“貨幣”，保留一位小數(shù)。④將鼠標(biāo)移動(dòng)至“圖書銷售情況表”工作表標(biāo)簽，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“創(chuàng)建副本”菜單命令，得到一張新表“圖書銷售情況表（2）”；將該工作表重命名為“分類匯總”。⑤重復(fù)步驟4的操作，在“圖書銷售情況表.xlsx”工作簿中，復(fù)制兩次“圖書銷售情況表”工作表，分別將工作表重命名為“自動(dòng)篩選”、“高級(jí)篩選”、“篩選滿足條件的前幾項(xiàng)”、“多級(jí)分類匯總”。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.2計(jì)算銷售額①選中“圖書銷售情況表”工作表中的數(shù)據(jù)區(qū)域A2:F22，選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“排序”按鈕的擴(kuò)展按鈕，選擇“自定義排序”命令，打開“排序”對(duì)話框。在“主要關(guān)鍵字”下拉列表中選擇“季度”，在“次序”下拉列表中選擇“升序”。②單擊“添加條件”按鈕，在“次要關(guān)鍵字”下拉列表中選擇“圖書名稱”，在“次序”下拉列表中選擇“降序”。③單擊“選項(xiàng)”按鈕，打開“排序選項(xiàng)”對(duì)話框，選擇“筆劃排序”單選鈕。單擊“確定”按鈕，返回“排序”對(duì)話框。④在“排序”對(duì)話框中，單擊“確定”按鈕，完成數(shù)據(jù)排序操作，排序結(jié)果如右圖所示。

任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.3數(shù)據(jù)排序①

選擇“分類匯總”工作表，按主要關(guān)鍵字“圖書名稱”進(jìn)行排序。②選中數(shù)據(jù)區(qū)域A2:F22，選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“分級(jí)顯示”選項(xiàng)組中“分類匯總”按鈕，打開“分類匯總”對(duì)話框，在“分類字段”下拉列表框中選擇“圖書名稱”；“匯總方式”下拉列表框中選擇“求和”；選擇“選定匯總項(xiàng)”列表框中的“銷售額”復(fù)選框，其他設(shè)置參數(shù)如圖4-52所示。③單擊“確定”按鈕，完成分類匯總操作，結(jié)果如圖4-53所示。此時(shí)表格中的數(shù)據(jù)自動(dòng)分成了三級(jí)，在編輯區(qū)左側(cè)單擊數(shù)字按鈕“1”、“2”、“3”，可以折疊或展開相應(yīng)級(jí)別。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.4分類匯總圖4-52

“分類匯總”對(duì)話框任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.4分類匯總圖4-53“分類匯總”結(jié)果圖1.自動(dòng)篩選自動(dòng)篩選是對(duì)各個(gè)字段建立篩選，在“自動(dòng)篩選”工作表中篩選出“計(jì)算機(jī)應(yīng)用基礎(chǔ)”圖書銷售數(shù)量大于等于200、小于300的記錄。操作步驟如下。①選擇“自動(dòng)篩選”工作表，將光標(biāo)定位在A2:F22數(shù)據(jù)區(qū)域中的任意一個(gè)單元格中，選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“篩選”按鈕，A2:F2區(qū)域內(nèi)每個(gè)單元格右上角會(huì)出現(xiàn)一個(gè)篩選箭頭，如圖4-54所示。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.5數(shù)據(jù)篩選圖4-54

“自動(dòng)篩選”圖②單擊“圖書名稱”字段右上角的篩選箭頭，打開篩選條件對(duì)話框，選中“計(jì)算機(jī)應(yīng)用基礎(chǔ)”和“圖書名稱”復(fù)選框，單擊“確定”按鈕，篩選出所有“計(jì)算機(jī)應(yīng)用基礎(chǔ)”的圖書銷售記錄。1.自動(dòng)篩選③單擊“數(shù)量”字段右上角的篩選箭頭，打開篩選條件對(duì)話框，單擊“數(shù)字篩選”菜單，選擇“自定義篩選”命令。④在彈出的“自定義自動(dòng)篩選方式”對(duì)話框中，設(shè)置數(shù)量的篩選條件為“大于或等于200與小于300”。⑤單擊“確定”按鈕，篩選出“計(jì)算機(jī)應(yīng)用基礎(chǔ)”圖書銷售數(shù)量大于等于200、小于300的記錄，篩選結(jié)果如圖4-58所示。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.5數(shù)據(jù)篩選圖4-58自動(dòng)篩選結(jié)果圖2.高級(jí)篩選自動(dòng)篩選對(duì)各字段的篩選是“邏輯與”的關(guān)系，即同時(shí)滿足多個(gè)條件。但若要實(shí)現(xiàn)字段間“邏輯或”的關(guān)系，即滿足任一條件，則必須借助于高級(jí)篩選。在“高級(jí)篩選”工作表中，篩選出經(jīng)售部門為“第1分店”或銷售圖書數(shù)量大于且等于230的銷售記錄，操作步驟如下。①選擇“高級(jí)篩選”工作表，在H2:I4區(qū)域中輸入篩選條件內(nèi)容，如圖4-59所示。②

選中數(shù)據(jù)區(qū)域A2:F22，選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“篩選”按鈕的擴(kuò)展按鈕，選擇“高級(jí)篩選”命令，打開“高級(jí)篩選”對(duì)話框，在“方式”區(qū)域中選擇“在原有區(qū)域顯示篩選結(jié)果”，單擊“條件區(qū)域”右側(cè)的折疊按鈕，拖動(dòng)鼠標(biāo)選擇H2:I4區(qū)域，按回車鍵或單擊“還原”按鈕返回“高級(jí)篩選”對(duì)話框，如圖4-60所示。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.5數(shù)據(jù)篩選圖4-59

高級(jí)篩選條件圖4-60“高級(jí)篩選”對(duì)話框2.高級(jí)篩選③單擊“確定”按鈕，完成高級(jí)篩選，結(jié)果如圖4-61所示。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.5數(shù)據(jù)篩選圖4-61“高級(jí)篩選”結(jié)果①

選擇“篩選滿足條件的前幾項(xiàng)”工作表，將光標(biāo)定位在數(shù)據(jù)區(qū)域A2:F22中的任意一個(gè)單元格中，選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“篩選”按鈕。②單擊“銷售額（元）”字段右上角的篩選箭頭，選擇“數(shù)字篩選”下拉菜單中的“前10項(xiàng)”命令。③在打開的“自動(dòng)篩選前10個(gè)”對(duì)話框中，設(shè)置顯示區(qū)域?yàn)椤白畲蟆薄?”“項(xiàng)”，單擊“確定”按鈕，篩選出銷售額排在前8位的記錄，結(jié)果如圖所示。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.6篩選滿足條件的前幾項(xiàng)記錄清單在進(jìn)行分類匯總操作的過程中，用戶可對(duì)同一字段進(jìn)行多種方式的匯總。下面對(duì)“圖書銷售情況表”按各季度的銷售額求和匯總，同時(shí)對(duì)各季度的銷售數(shù)量進(jìn)行統(tǒng)計(jì)，操作步驟如下。

選擇“多級(jí)分類匯總”工作表，選中數(shù)據(jù)區(qū)域A2:F22。選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“排序”按鈕的擴(kuò)展按鈕，選擇“自定義排序”選項(xiàng)，打開“排序”對(duì)話框，按照主要關(guān)鍵字“季度”升序和次要關(guān)鍵字“圖書名稱”的筆劃降序進(jìn)行排序。選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“分類匯總”按鈕，打開“分類匯總”對(duì)話框。在“分類字段”下拉列表中選擇“季度”選項(xiàng)，“匯總方式”下拉列表中選擇“求和”選項(xiàng)，在“選定匯總項(xiàng)”列表中選擇“銷售額”復(fù)選框。單擊“確定”按鈕，完成各季度銷售額求和分類匯總操作。

選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“分級(jí)顯示”選項(xiàng)組中的“分類匯總”按鈕，打開“分類匯總”對(duì)話框。在“分類字段”下拉列表框中選擇“季度”，在“匯總方式”下拉列表框中選擇“計(jì)數(shù)”，選中“選定匯總項(xiàng)”列表框中的“數(shù)量”復(fù)選框，取消選中“替換當(dāng)前分類匯總”復(fù)選框。任務(wù)4.3圖書銷售數(shù)據(jù)管理4.3.7創(chuàng)建多級(jí)分類匯總清單單擊“確定”按鈕。即在各季度銷售額求和分類匯總的基礎(chǔ)上，完成了各季度銷售數(shù)量的計(jì)數(shù)分類匯總，結(jié)果如圖4-66所示。圖4-66多級(jí)分類匯總?cè)蝿?wù)4.3圖書銷售數(shù)據(jù)管理4.3.7創(chuàng)建多級(jí)分類匯總清單【能力拓展】實(shí)訓(xùn)學(xué)生成績(jī)數(shù)據(jù)匯總1.實(shí)訓(xùn)目的①掌握工作表的格式設(shè)置。②掌握排序、分類匯總數(shù)據(jù)管理的方法。③掌握各種數(shù)據(jù)篩選的設(shè)置方法?！灸芰ν卣埂繉?shí)訓(xùn)學(xué)生成績(jī)數(shù)據(jù)匯總2.實(shí)訓(xùn)要求①打開“學(xué)生成績(jī)表.xlsx”工作簿，選中Sheet1工作表中E3:E30區(qū)域，單擊“條件格式”按鈕的擴(kuò)展按鈕，選擇“圖標(biāo)集”菜單項(xiàng)中的“其他規(guī)則(M)...”選項(xiàng)，在打開的“新建格式規(guī)則”對(duì)話框中，圖標(biāo)樣式選擇三色旗，當(dāng)值大于等于90（類型數(shù)字）時(shí)圖標(biāo)為小紅旗，當(dāng)值小于90且大于等于60（類型數(shù)字）時(shí)圖標(biāo)為小黃旗，當(dāng)值小于60時(shí)圖標(biāo)為紅色?。②將Sheet1工作表重命名為“學(xué)生成績(jī)表”并創(chuàng)建副本，將“學(xué)生成績(jī)表(2)”工作表重命名為“分類匯總”，對(duì)工作表內(nèi)數(shù)據(jù)清單的內(nèi)容按主要關(guān)鍵字“課程名稱”筆劃的遞增次序、次要關(guān)鍵字“成績(jī)”的遞減次序進(jìn)行排序。③對(duì)排序后的數(shù)據(jù)進(jìn)行分類匯總，分類字段為“課程名稱”，匯總方式為“平均值”，選定匯總項(xiàng)為“成績(jī)”，匯總結(jié)果顯示在數(shù)據(jù)下方。④再次進(jìn)行分類匯總，匯總出每門課程的考試人數(shù)，匯總結(jié)果與平均值一同顯示在數(shù)據(jù)下方，并且只顯示到3級(jí)；將所有匯總成績(jī)數(shù)字格式設(shè)置為“數(shù)值”，保留1位小數(shù)；將所有匯總的計(jì)數(shù)單元格數(shù)字格式設(shè)置為“數(shù)值”，無小數(shù)位，并清除條件格式。⑤為“學(xué)生成績(jī)表”創(chuàng)建副本，并重命名為“自動(dòng)篩選”，在“自動(dòng)篩選”工作表中篩選出系別為“計(jì)算機(jī)”且課程名稱為“人工智能”的記錄。⑥為“學(xué)生成績(jī)表”創(chuàng)建副本，并重命名為“自定義篩選”，選擇“自定義篩選”工作表，篩選出系別為“自動(dòng)控制”，且成績(jī)大于90或小于等于60的記錄。⑦為“學(xué)生成績(jī)表”創(chuàng)建副本，并重命名為“高級(jí)篩選”，選擇“高級(jí)篩選”工作表，篩選出系別為“計(jì)算機(jī)”或“數(shù)學(xué)”，且成績(jī)大于75的記錄，篩選條件寫在G2開始的區(qū)域內(nèi)，篩選結(jié)果顯示在當(dāng)前工作表G9開始的區(qū)域內(nèi)。⑧以原文件名進(jìn)行保存?！灸芰ν卣埂繉?shí)訓(xùn)學(xué)生成績(jī)數(shù)據(jù)匯總圖4-71“高級(jí)篩選”結(jié)果目錄任務(wù)4.1

制作學(xué)生信息登記表制作學(xué)生成績(jī)統(tǒng)計(jì)表任務(wù)4.2圖書銷售數(shù)據(jù)管理任務(wù)4.3制作職稱結(jié)構(gòu)統(tǒng)計(jì)圖任務(wù)4.4銷售數(shù)據(jù)分析任務(wù)4.5任務(wù)4.4制作職工工資統(tǒng)計(jì)圖圖4-72

“職工工資統(tǒng)計(jì)圖”結(jié)果任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.1創(chuàng)建圖表1.

圖表說明表格文檔中常見的圖表的類型包括柱形圖、折線圖、餅圖、條形圖、面積圖、組合圖等。插入圖表可通過“插入”選項(xiàng)卡中的“圖表”選項(xiàng)組按鈕實(shí)現(xiàn)，如“全部圖表”、“插入柱形圖”等。編輯圖表可選中圖表后，通過“繪圖工具”、“文本工具”、“圖表工具”選項(xiàng)卡進(jìn)行操作。在“圖表工具”選項(xiàng)卡中，我們可以進(jìn)行“添加元素”、“快速布局”、“更改顏色”、“設(shè)置樣式”、“更改類型”等操作。也可以通過圖表區(qū)右上角的五個(gè)按鈕快速完成圖表編輯。任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.1創(chuàng)建圖表2.創(chuàng)建圖表以“職工工資統(tǒng)計(jì)表”中的“職稱”為橫軸，以“所占比例”為縱軸，制作“職稱結(jié)構(gòu)統(tǒng)計(jì)圖”三維餅圖，并顯示各職稱所占比例。操作步驟如下。①打開“職工工資統(tǒng)計(jì)表.xlsx”工作簿，在“職工工資表”工作表中選中J3單元格，輸入“=I3/”，單擊插入函數(shù)按鈕，選擇COUNT函數(shù)，設(shè)置值1為“$D$3:$D$22”，綜合運(yùn)用公式和COUNT函數(shù)計(jì)算出“高工”占職工總?cè)藬?shù)的比例；設(shè)置數(shù)字格式為“百分比”，保留一位小數(shù)；將鼠標(biāo)移動(dòng)到J3單元格右下角，拖動(dòng)填充柄填充至J5單元格，計(jì)算出各種職稱占職工總?cè)藬?shù)的比例。②選中H2:H5區(qū)域，按住Ctrl鍵同時(shí)選中J2:J5區(qū)域；選擇“插入”選項(xiàng)卡，單擊“全部圖表”按鈕，打開“圖表”對(duì)話框，如圖所示；在左側(cè)列表中選擇“餅圖”，右側(cè)分類中選擇“三維餅圖”，預(yù)設(shè)圖表中選擇第一個(gè)，此時(shí)，編輯區(qū)將會(huì)生成一個(gè)三維餅圖。任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.1創(chuàng)建圖表2.創(chuàng)建圖表③將三維餅圖插入到H7:L18區(qū)域內(nèi)，拖動(dòng)圖表四角的圓形定位符可修改圖表的大??；此時(shí)，圖表的構(gòu)成包括圖表區(qū)、繪圖區(qū)、圖表標(biāo)題、圖例項(xiàng)，如圖4-74所示。選中圖表標(biāo)題，將“所占比例”修改為“職稱結(jié)構(gòu)統(tǒng)計(jì)圖”。④為“職工工資表”工作表創(chuàng)建副本，重命名為“組合圖”，刪除“職稱結(jié)構(gòu)統(tǒng)計(jì)圖”。⑤

同時(shí)選中H2:H5、I2:I5和K2:K5數(shù)據(jù)區(qū)域，即“職稱”列、“人數(shù)”列、“基本工資平均值（元）”列，單擊“插入”選項(xiàng)卡中的“全部圖表”按鈕，打開“圖表”對(duì)話框，在左側(cè)列表中選擇“組合圖”，在自定義組合右側(cè)設(shè)置“人數(shù)”系列圖表類型為“折線圖”，并勾選“次坐標(biāo)軸”選項(xiàng)；設(shè)置“基本工資平均值”系列圖表類型為“簇狀柱形圖”。點(diǎn)擊“插入預(yù)設(shè)圖表”按鈕，將自定義組合圖插入數(shù)據(jù)表中。圖4-74

“職稱結(jié)構(gòu)統(tǒng)計(jì)圖”圖表效果任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.1創(chuàng)建圖表2.創(chuàng)建圖表⑥將生成的自定義組合圖插入到H7:L20區(qū)域內(nèi)；將圖表標(biāo)題修改為“職稱工資統(tǒng)計(jì)圖”，效果如圖所示。圖4-76“職稱工資統(tǒng)計(jì)圖”圖表效果任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.2圖表編輯1.編輯圖表區(qū)內(nèi)容①在“組合圖”工作表中，選中“職稱工資統(tǒng)計(jì)圖”圖表，單擊圖表區(qū)域右上角的“圖表元素”按鈕，選擇“數(shù)據(jù)標(biāo)簽”，單擊其右側(cè)擴(kuò)展按鈕，選擇“數(shù)據(jù)標(biāo)簽外”，在圖表內(nèi)添加數(shù)據(jù)標(biāo)簽，如圖所示；添加數(shù)據(jù)標(biāo)簽操作也可單擊“圖表工具”選項(xiàng)卡中的“添加元素”按鈕實(shí)現(xiàn)。任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.2圖表編輯1.編輯圖表區(qū)內(nèi)容②選中工程師工資的數(shù)據(jù)標(biāo)簽“6722.22”，將其移動(dòng)到合適位置；選中工程師人數(shù)的數(shù)據(jù)標(biāo)簽“9”，將其移動(dòng)到合適位置；單擊“圖表工具”選項(xiàng)卡中的“添加元素”按鈕，在擴(kuò)展菜單列表中選擇“數(shù)據(jù)表”選項(xiàng)中的“顯示圖例項(xiàng)標(biāo)示”，效果如圖4-78所示。③單擊“圖表工具”選項(xiàng)卡中的“添加元素”按鈕，在擴(kuò)展菜單列表中選擇“坐標(biāo)軸”中的“更多選項(xiàng)...(M)”，會(huì)在工作表編輯區(qū)右側(cè)顯示出“屬性”窗格，此時(shí)默認(rèn)顯示的是水平軸的相關(guān)選項(xiàng)。④單擊“屬性”窗格中“坐標(biāo)軸選項(xiàng)”選項(xiàng)卡擴(kuò)展按鈕，在下拉菜單中選擇“垂直（值）軸”，窗格中將顯示出邊界和單位等信息；將“邊界”中的“最小值”設(shè)置為2000，“顯示單位”設(shè)置為“千”。結(jié)果如圖4-81所示。圖4-81

編輯圖表區(qū)內(nèi)容結(jié)果圖4-78顯示圖例項(xiàng)標(biāo)示任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.2圖表編輯2.設(shè)置圖表樣式①選中“職工工資統(tǒng)計(jì)圖”圖表，選擇“圖表工具”選項(xiàng)卡，單擊“更改顏色”按鈕，選擇彩色列表中的第三個(gè)。②單擊圖表區(qū)右上角的“設(shè)置圖表區(qū)域格式”按鈕，在右側(cè)打開的屬性窗格中，填充效果設(shè)置為“漸變填充”，并在色標(biāo)卡尺中刪除停止點(diǎn)2和3，將“亮度(B)”設(shè)置為100%，如圖所示。任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.2圖表編輯2.設(shè)置圖表樣式③打開“開始”選項(xiàng)卡，將字體設(shè)置為“加粗”；在圖表中選中圖表標(biāo)題，將字體設(shè)置為“楷體，18號(hào)，黑色”。④選中“繪圖區(qū)”，通過拖動(dòng)定位符修改繪圖區(qū)大?。辉谟覀?cè)“屬性”窗格中，設(shè)置填充效果為“純色填充”，顏色為“白色，背景1”。效果如圖所示。任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.2圖表編輯2.設(shè)置圖表樣式⑤打開“職工工資表”工作表，選中“職工工資統(tǒng)計(jì)圖”圖表，單擊圖表區(qū)域右上角的“圖表樣式”按鈕，在樣式分類中選擇“樣式2”，效果如圖4-85所示。⑥單擊“圖表工具”選項(xiàng)卡中的“更改類型”按鈕，在打開的“更改圖表類型”對(duì)話框中選擇“條形圖”中的“簇狀條形圖”；單擊“繪圖工具”選項(xiàng)卡，將圖表區(qū)高度設(shè)置為10厘米，寬度設(shè)置為15厘米；單擊“文本工具”選項(xiàng)卡，將文本字號(hào)設(shè)置為11，加粗顯示。完成效果如圖4-86所示。保存工作簿。圖4-85

使用預(yù)設(shè)樣式設(shè)置圖表效果圖4-86

更改圖表類型任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.3修改圖表數(shù)據(jù)源將“職稱結(jié)構(gòu)統(tǒng)計(jì)圖”修改為“職稱人數(shù)分布圖”，操作步驟如下。①為“職工工資表”工作表創(chuàng)建副本，重命名為“職稱人數(shù)分布圖”，選中“職稱結(jié)構(gòu)統(tǒng)計(jì)圖”圖表，將圖表標(biāo)題改為“職稱人數(shù)分布圖”；單擊“圖表工具”選項(xiàng)卡，在預(yù)設(shè)樣式列表中選擇“樣式5”。②單擊“圖表工具”選項(xiàng)卡中的“選擇數(shù)據(jù)”按鈕，彈出“編輯數(shù)據(jù)源”對(duì)話框；單擊“圖表數(shù)據(jù)區(qū)域”右側(cè)折疊按鈕，選擇數(shù)據(jù)區(qū)域“H2:H5,I2:I5”，絕對(duì)引用，如圖4-87所示。③單擊“圖例項(xiàng)(系列)”中的添加按鈕，打開圖4-88所示的“編輯數(shù)據(jù)系列”對(duì)話框，單擊“系列名稱”文本框右側(cè)的折疊按鈕，在工作簿中選中J2單元格（絕對(duì)引用）；單擊“系列值”文本框，刪除原內(nèi)容，單擊右側(cè)的折疊按鈕，在工作簿中選中J3:J5區(qū)域（絕對(duì)引用）；單擊“確定”按鈕，可為圖表增加名為“所占比例”的系列。圖4-88“編輯數(shù)據(jù)系列”對(duì)話框圖4-87“編輯數(shù)據(jù)源”對(duì)話框任務(wù)4.4制作職工工資統(tǒng)計(jì)圖4.4.3修改圖表數(shù)據(jù)源④選中系列“所占比例”數(shù)據(jù)標(biāo)簽，單擊繪圖區(qū)右上角“圖標(biāo)元素”按鈕，選擇“數(shù)據(jù)標(biāo)簽”的“更多選項(xiàng)”命令，在右側(cè)“屬性”窗格“標(biāo)簽”選項(xiàng)卡中，取消勾選“類別名稱(G)”，選擇“值(V)”；選中系列“人數(shù)”數(shù)據(jù)標(biāo)簽，在右側(cè)“屬性”窗格中打開“標(biāo)簽”選項(xiàng)卡，取消勾選“類別名稱(G)”，選擇“值(V)”，“標(biāo)簽位置”選擇“數(shù)據(jù)標(biāo)簽外(O)”。⑤單擊繪圖區(qū)右上角“圖標(biāo)元素”按鈕，選中“數(shù)據(jù)表”選項(xiàng)，完成效果如圖所示。將工作簿另存為“職工工資統(tǒng)計(jì)圖.xlsx”【能力拓展】實(shí)訓(xùn)1“汽車銷售統(tǒng)計(jì)圖”制作【能力拓展】實(shí)訓(xùn)2“設(shè)備購(gòu)置情況圖”制作目錄任務(wù)4.1

制作學(xué)生信息登記表制作學(xué)生成績(jī)統(tǒng)計(jì)表任務(wù)4.2圖書銷售數(shù)據(jù)管理任務(wù)4.3制作職稱結(jié)構(gòu)統(tǒng)計(jì)圖任務(wù)4.4銷售數(shù)據(jù)分析任務(wù)4.5任務(wù)4.5