第7章WPS表格的計(jì)算與管理目錄7.1公式的基本知識(shí)7.2函數(shù)的基本知識(shí)7.3表格數(shù)據(jù)的管理實(shí)戰(zhàn)演練

制作員工工資表學(xué)習(xí)目標(biāo)：WPS表格公式應(yīng)用與數(shù)據(jù)管理實(shí)戰(zhàn)掌握WPS表格的公式、函數(shù)應(yīng)用及數(shù)據(jù)管理功能，包括排序、篩選、分類(lèi)匯總和合并計(jì)算。通過(guò)實(shí)戰(zhàn)制作工資表，提升數(shù)據(jù)處理能力，并完成課后練習(xí)鞏固知識(shí)思政目標(biāo)：WPS表格：數(shù)據(jù)管理，決策支持，提升智慧在信息時(shí)代，有效處理和管理信息至關(guān)重要。WPS表格功能助力快速準(zhǔn)確處理數(shù)據(jù)，支持決策，提升生活選擇智慧公式的基本知識(shí)7.17.1.1公式的使用輸入與顯示公式輸入公式步驟在Excel中輸入公式，需先選中單元格，然后以等號(hào)開(kāi)始，比如=120*2，表示計(jì)算120與2的乘積。按下“Enter”或點(diǎn)擊編輯欄中的“輸入”按鈕，單元格顯示計(jì)算結(jié)果，編輯欄仍保留公式。修改或刪除公式要修改公式，點(diǎn)擊單元格編輯后按Enter；刪除公式，選中單元格后按Delete。7.1.2單元格引用：1．相對(duì)引用本節(jié)所說(shuō)的引用，是指使用單元格地址標(biāo)識(shí)公式中使用的數(shù)據(jù)的位置，使用引用可簡(jiǎn)化工作表的修改和維護(hù)流程01相對(duì)引用的概念相對(duì)引用是在公式中依據(jù)單元格位置變化的引用方式，如D2*E2，當(dāng)復(fù)制公式時(shí)，引用會(huì)根據(jù)目標(biāo)位置相對(duì)調(diào)整。02相對(duì)引用的自動(dòng)調(diào)整在Excel中，如果使用填充手柄復(fù)制含有相對(duì)引用的公式，公式會(huì)自動(dòng)調(diào)整引用以反映新的位置，如從F2到F3、F4時(shí)，D列和E列的單元格會(huì)相應(yīng)遞增。7.1.2單元格引用絕對(duì)引用：?jiǎn)卧竦刂非坝小?”標(biāo)志，公式復(fù)制后仍引用原單元格，如$E$2*$F$2。混合引用包括絕對(duì)引用行（如A$1、B$1）和絕對(duì)引用列（如$A1、$B1）。復(fù)制時(shí)，相對(duì)引用會(huì)自動(dòng)調(diào)整，而絕對(duì)引用保持不變。2．絕對(duì)引用3．混合引用7.1.3在公式中使用定義名稱(chēng)在公式中使用有意義的名稱(chēng)可提高可讀性，如“=利潤(rùn)*（100%-稅率）”，比“=D3*(100%-$B$11)”更易理解。使用名稱(chēng)簡(jiǎn)化公式01在工作表中選中單元格區(qū)域，輸入名稱(chēng)后按Enter，然后在“公式”選項(xiàng)卡中點(diǎn)擊“指定”以創(chuàng)建名稱(chēng)。定義名稱(chēng)的步驟02根據(jù)首行和末行的單元格內(nèi)容為多列區(qū)域命名，最左列和最右列同樣適用，便于管理數(shù)據(jù)。名稱(chēng)創(chuàng)建規(guī)則03在“名稱(chēng)管理器”中可修改、刪除名稱(chēng)或按條件篩選，操作時(shí)選中目標(biāo)名稱(chēng)，使用相應(yīng)按鈕進(jìn)行編輯。管理和篩選名稱(chēng)047.1.4審核公式在WPS中，通過(guò)“錯(cuò)誤檢查”工具可定位和修正公式錯(cuò)誤，從編輯欄修改錯(cuò)誤，直至“顯示計(jì)算步驟”按鈕變?yōu)榭捎谩９藉e(cuò)誤檢查與修正忽略錯(cuò)誤后，Excel將不再顯示錯(cuò)誤提示，通過(guò)追蹤引用和從屬單元格，能有效檢查和確保數(shù)據(jù)的正確性與計(jì)算范圍。忽略錯(cuò)誤與追蹤引用函數(shù)的基本知識(shí)7.27.2.1函數(shù)的結(jié)構(gòu)結(jié)構(gòu)說(shuō)明：以"="開(kāi)頭加函數(shù)名，如"ROUND"，圓括號(hào)內(nèi)用逗號(hào)隔開(kāi)參數(shù)，如"A10,2"。函數(shù)結(jié)構(gòu)解析01函數(shù)名定義：字母起始，可含字母、數(shù)字、下劃線(xiàn)，避開(kāi)特定保留字。函數(shù)命名規(guī)則02參數(shù)構(gòu)成：可含數(shù)字、文本、邏輯值、數(shù)組、錯(cuò)誤值或單元格引用，需確保有效。參數(shù)包含范圍03使用輔助：內(nèi)置函數(shù)輸入時(shí)，會(huì)顯示幫助信息，提示語(yǔ)法和參數(shù)含義。功能提示047.2.2插入函數(shù)插入函數(shù)操作步驟選擇單元格，點(diǎn)擊編輯欄的“插入函數(shù)”按鈕。在“選擇類(lèi)別”和“選擇函數(shù)”列表中挑選需要的函數(shù)，查閱相應(yīng)函數(shù)說(shuō)明。0102函數(shù)參數(shù)設(shè)置在“函數(shù)參數(shù)”對(duì)話(huà)框中輸入單元格引用或選擇數(shù)據(jù)區(qū)域。點(diǎn)擊/按鈕輔助定位參數(shù)，最后點(diǎn)擊“確定”完成函數(shù)輸入并顯示計(jì)算結(jié)果。7.2.3常用函數(shù)的應(yīng)用在財(cái)務(wù)計(jì)算中，如使用"PV"計(jì)算現(xiàn)值，"FV"求未來(lái)值，"IRR"求內(nèi)部收益率，簡(jiǎn)化財(cái)務(wù)分析步驟。函數(shù)應(yīng)用示例運(yùn)用"AVERAGE"計(jì)算平均值，"COUNT"統(tǒng)計(jì)數(shù)量，"STDEV"計(jì)算標(biāo)準(zhǔn)差，輔助數(shù)據(jù)分析。統(tǒng)計(jì)分析函數(shù)使用"DATE"創(chuàng)建日期，"DATEDIF"計(jì)算日期間隔，"EOMONTH"獲取月底日期，便于日期相關(guān)計(jì)算。日期時(shí)間處理7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)（1）SUM函數(shù)SUM函數(shù)計(jì)算指定單元格區(qū)域總和，如=B3:B14，按Enter鍵確認(rèn)，快速得出結(jié)果。SUM函數(shù)格式：SUM(Number1,Number2，...)，參數(shù)為需計(jì)算的值或單元格，例：SUM(D3：H3)計(jì)算D3至H3區(qū)域總和。函數(shù)應(yīng)用示例函數(shù)使用格式7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)（2）AVERAGE函數(shù)01函數(shù)AVERAGE求參數(shù)算術(shù)平均值，格式AVERAGE(number1,...)，參數(shù)≤30，含"0"值計(jì)算，空白或字符不計(jì)。02數(shù)組公式輸入選C3：C14，輸入=B3:B14/銷(xiāo)售總額，Ctrl+Shift+Enter，自動(dòng)添加{}，填充計(jì)算結(jié)果。7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)（3）MAX函數(shù)求一組數(shù)最大值，格式MAX(n1,n2,...)，參數(shù)≤30，忽略文本與邏輯值。函數(shù)名稱(chēng)MAX示例：=MAX(E44:J44,7,8,9,10)，顯示區(qū)域E44至J44與7,8,9,10中的最大值。MAX函數(shù)應(yīng)用7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)（4）MIN函數(shù)01函數(shù)名稱(chēng)MIN求一組數(shù)最小值，格式MIN(number1,...)，參數(shù)≤30，忽略文本與邏輯值。02MIN函數(shù)應(yīng)用舉例公式=MIN(E44：J44,7,8,9,10)顯示區(qū)域E44至J44和數(shù)值7至10的最小值。（5）IF函數(shù)函數(shù)名稱(chēng)：IFIF函數(shù)依據(jù)邏輯判斷結(jié)果返回內(nèi)容，格式為=IF(Logical,Value_if_true,Value_if_false)，Logical為判斷表達(dá)式，Value_if_true和Value_if_false分別對(duì)應(yīng)真假結(jié)果的顯示內(nèi)容。函數(shù)應(yīng)用舉例在C29單元格輸入=IF(C26>=18,"符合要求","不符合要求")，若C26>=18則C29顯示“符合要求”，反之顯示“不符合要求”。單元格應(yīng)用靈活，示例僅供參考。7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)（6）COUNT函數(shù)01函數(shù)名稱(chēng)COUNT統(tǒng)計(jì)參數(shù)中數(shù)值單元格個(gè)數(shù)，使用格式COUNT(number1,...)，參數(shù)不超過(guò)30個(gè)，空白或字符單元格不計(jì)入。02函數(shù)應(yīng)用舉例在B8輸入=COUNT(B2：D8)，確認(rèn)后統(tǒng)計(jì)B2至D8區(qū)域中所有數(shù)值型數(shù)據(jù)的個(gè)數(shù)，特別提醒：不統(tǒng)計(jì)空白或字符單元格。7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)（7）COUNTIF函數(shù)統(tǒng)計(jì)指定條件下單元格數(shù)目，格式COUNTIF(Range,Criteria)，例：=COUNTIF(C1：C12,">=90")統(tǒng)計(jì)大于等于90的單元格數(shù)。函數(shù)名稱(chēng)COUNTIFRange為統(tǒng)計(jì)區(qū)域，Criteria為條件，允許區(qū)域含空白單元格，確保條件正確表達(dá)。COUNTIF使用細(xì)節(jié)7.2.3常用函數(shù)的應(yīng)用：1.常用函數(shù)（8）RANK函數(shù)01函數(shù)RANK應(yīng)用返回?cái)?shù)值在列表中的排位，格式：RANK(Number,ref,order)，Number為排序值，ref為數(shù)值區(qū)域，order為排序方式，0為降序，非0為升序。02函數(shù)RANK實(shí)例在C2單元格輸入=RANK(B2,$B$2:$B$31,0)，Number相對(duì)引用，ref絕對(duì)引用，拖動(dòng)填充柄完成全班成績(jī)排名。7.2.3常用函數(shù)的應(yīng)用：2.使用函數(shù)按照提示，學(xué)習(xí)函數(shù)操作：選中單元格，點(diǎn)擊“插入函數(shù)”按鈕，選擇類(lèi)別和函數(shù)，設(shè)置參數(shù)，確定即可輸入函數(shù)并計(jì)算結(jié)果。使用函數(shù)表格數(shù)據(jù)的管理7.37.3.1數(shù)據(jù)排序升序排序規(guī)則排序特殊處理邏輯值排序降序排序規(guī)則按0-9-a-z-A-Z順序，文本中帶連字符的靠后，錯(cuò)誤值等效，空格最后。與升序相反，錯(cuò)誤值順序不變，空格仍最后。False在True前，無(wú)論升序降序。包含數(shù)字的文本按數(shù)字大小排序，排序時(shí)考慮單元格內(nèi)容的完整文本。7.3.1數(shù)據(jù)排序：1.按關(guān)鍵字排序按數(shù)據(jù)表某一列排序，點(diǎn)擊該列任意單元格，然后點(diǎn)擊“數(shù)據(jù)”菜單中“升序”或“降序”命令，即可完成排序。按關(guān)鍵字排序基礎(chǔ)當(dāng)關(guān)鍵字重復(fù)時(shí)，排序操作會(huì)基于選定的下一個(gè)關(guān)鍵字重新進(jìn)行，確保排序的準(zhǔn)確性。處理重復(fù)關(guān)鍵字排序數(shù)據(jù)排序選中單元格，使用“自定義排序”，設(shè)置主要與次要關(guān)鍵字，調(diào)整次序，復(fù)制或刪除條件，完成排序。排序操作通過(guò)“數(shù)據(jù)”選項(xiàng)卡，打開(kāi)“排序”對(duì)話(huà)框，定義排序依據(jù)和方式，利用“添加”、“上移”、“下移”按鈕管理?xiàng)l件。7.3.1數(shù)據(jù)排序：2.自定義條件排序在“自定義序列”中輸入新序列項(xiàng)，用Enter鍵分隔，添加后新序列自動(dòng)選中，然后確定返回“排序”對(duì)話(huà)框，設(shè)置的序列可用于排列次序。自定義序列的創(chuàng)建與應(yīng)用單擊“確定”按鈕，即可按指定序列排序。應(yīng)用自定義序列排序在Excel中，可按特定順序排列數(shù)據(jù)，通過(guò)“排序”對(duì)話(huà)框設(shè)置主要關(guān)鍵字、排序依據(jù)和自定義序列。排序需求與操作步驟01、02、03、數(shù)據(jù)篩選自動(dòng)篩選支持單個(gè)多字段，邏輯與關(guān)系，頂行單元格下拉圖標(biāo)設(shè)定條件，顯示符合條件記錄。篩選條件用戶(hù)通過(guò)下拉圖標(biāo)設(shè)定篩選條件，只顯示數(shù)據(jù)表中符合條件的記錄。數(shù)據(jù)篩選區(qū)域選擇如果數(shù)據(jù)表的首行為標(biāo)題行，可以單擊數(shù)據(jù)表中的任意一個(gè)單元格。單擊“數(shù)據(jù)”選項(xiàng)卡中的“篩選”按鈕/，數(shù)據(jù)表的所有列標(biāo)志右側(cè)會(huì)顯示一個(gè)下拉按鈕7.3.2數(shù)據(jù)篩選：1.自動(dòng)篩選"篩選條件選擇方法"如果當(dāng)前篩選的數(shù)據(jù)列中為單元格設(shè)置了多種顏色，可以切換到“顏色篩選”選項(xiàng)卡按單元格顏色進(jìn)行篩選。如果要對(duì)篩選結(jié)果進(jìn)行排序，單擊自動(dòng)篩選下拉列表頂部的“升序”、“降序”或“顏色排序”按鈕單擊“確定”按鈕，即可顯示符合條件的篩選結(jié)果多條件自動(dòng)篩選設(shè)置如果要取消篩選，顯示數(shù)據(jù)表中的所有數(shù)據(jù)行，單擊“數(shù)據(jù)”選項(xiàng)卡中的“全部顯示”按鈕。7.3.2數(shù)據(jù)篩選：1.自動(dòng)篩選7.3.2數(shù)據(jù)篩選：2.高級(jí)篩選默認(rèn)“邏輯與”，需全部滿(mǎn)足條件。實(shí)現(xiàn)“邏輯或”，需設(shè)置多個(gè)條件，滿(mǎn)足其一即顯示。在自動(dòng)篩選的“高級(jí)”中設(shè)置，多個(gè)條件各占一行，表示“或”關(guān)系，滿(mǎn)足任一條件即生效。高級(jí)篩選應(yīng)用邏輯關(guān)系調(diào)整7.3.2數(shù)據(jù)篩選：2.高級(jí)篩選（1）創(chuàng)建條件區(qū)域01數(shù)據(jù)篩選：高級(jí)篩選需建條件區(qū)域，含列標(biāo)題與篩選條件，同行條件為“邏輯與”，不同行條件為“邏輯或”，結(jié)果可顯示原位或他處。02條件區(qū)域構(gòu)建確保列標(biāo)題與原數(shù)據(jù)精確匹配，條件由關(guān)系符號(hào)和數(shù)據(jù)組成，避免使用“＝”開(kāi)頭，關(guān)系符號(hào)表達(dá)意義如表7-1所示，多條件“與”同放一行，“或”放不同行。使用的符號(hào)表達(dá)的中文意義>大于一個(gè)給定的數(shù)值<小于一個(gè)給定的數(shù)值>＝大于或等于一個(gè)給定的數(shù)值<＝小于或等于一個(gè)給定的數(shù)值<>不等于一個(gè)給定的數(shù)值或者文本不寫(xiě)符號(hào)（表示“=”）等于一個(gè)給定的數(shù)值或者文本“高級(jí)篩選”條件區(qū)域的表達(dá)式意義（2）單擊“數(shù)據(jù)”選項(xiàng)卡“篩選”下拉列表中的“高級(jí)篩選”命令，打開(kāi)如圖所示的“高級(jí)篩選”對(duì)話(huà)框（3）保存篩選結(jié)果位置（4）“列表區(qū)域”文本框自動(dòng)填充數(shù)據(jù)區(qū)域，單擊右側(cè)的/按鈕可以在工作表中重新選擇篩選的數(shù)據(jù)區(qū)域（5）單擊“條件區(qū)域”文本框右側(cè)的/按鈕，在工作表中選擇條件區(qū)域所在的單元格區(qū)域，選擇時(shí)應(yīng)包含條件列標(biāo)志和條件。也可以直接輸入條件區(qū)域的單元格引用（6）如果選擇“將篩選結(jié)果復(fù)制到其他位置”選項(xiàng)，單擊“復(fù)制到”文本框右側(cè)的/按鈕，在工作表中選擇篩選結(jié)果首行顯示的位置（7）在數(shù)據(jù)表中，選中單元格，點(diǎn)擊“數(shù)據(jù)”>“篩選”>“高級(jí)”。在彈出的對(duì)話(huà)框中選擇“將篩選結(jié)果復(fù)制到其他位置”，設(shè)置列表區(qū)域?yàn)锳2：C15，條件區(qū)域?yàn)镋3：E4，目標(biāo)位置為原表下方的第二行。點(diǎn)擊“確定”后，篩選結(jié)果會(huì)顯示，并且條件間為“或”關(guān)系。7.3.2數(shù)據(jù)篩選：2.高級(jí)篩選（8）修改條件區(qū)域，在F列增加條件，且條件值與前兩個(gè)條件不在同一行，表明該條件與前兩個(gè)條件是邏輯“或”的關(guān)系，如圖所示。（9）選擇數(shù)據(jù)表中任一單元格，點(diǎn)擊“數(shù)據(jù)”菜單的“高級(jí)篩選”選項(xiàng)，進(jìn)入對(duì)話(huà)框。在“方式”區(qū)域選擇“復(fù)制到其他位置”，然后在“列表區(qū)域”選擇A2：C15，接著在“條件區(qū)域”選擇E3：F5。同時(shí)勾選“擴(kuò)展結(jié)果區(qū)域”以覆蓋原有數(shù)據(jù)。最后，點(diǎn)擊“確定”以篩選出“占總產(chǎn)值百分比”超過(guò)10%或“月份”為3月的行。7.3.2數(shù)據(jù)篩選：2.高級(jí)篩選7.3.3數(shù)據(jù)分類(lèi)匯總：1.簡(jiǎn)單分類(lèi)匯總對(duì)數(shù)據(jù)進(jìn)行排序后，通常還會(huì)將數(shù)據(jù)按指定的字段進(jìn)行分類(lèi)匯總，分級(jí)顯示分析數(shù)據(jù)。對(duì)數(shù)據(jù)行分類(lèi)匯總之后，如果修改了其中的明細(xì)數(shù)據(jù)，匯總數(shù)據(jù)會(huì)隨之自動(dòng)更新打開(kāi)數(shù)據(jù)表，按匯總字段排序，然后選擇“分類(lèi)匯總”功能進(jìn)行設(shè)置。簡(jiǎn)單分類(lèi)匯總步驟選擇分類(lèi)字段，匹配排序列。選定匯總方式，如求和、平均等。選擇匯總項(xiàng)，可多選進(jìn)行多列匯總。設(shè)置分類(lèi)匯總參數(shù)決定是否保留先前的分類(lèi)匯總。選擇分頁(yè)顯示，便于查看分類(lèi)數(shù)據(jù)。確認(rèn)設(shè)置，查看分類(lèi)匯總結(jié)果。保留與顯示匯總結(jié)果對(duì)一列數(shù)據(jù)進(jìn)行分類(lèi)匯總后，還可以對(duì)這一列進(jìn)行其他方式的匯總；或者在原有分類(lèi)匯總的基礎(chǔ)上，再對(duì)其他的字段進(jìn)行分類(lèi)匯總，從而生成多級(jí)分類(lèi)匯總（1）打開(kāi)已創(chuàng)建的簡(jiǎn)單分類(lèi)匯總，選中數(shù)據(jù)區(qū)域，在“數(shù)據(jù)”選項(xiàng)卡中單擊“”分類(lèi)匯總按鈕，打開(kāi)“分類(lèi)匯總”對(duì)話(huà)框（2）設(shè)置分類(lèi)匯總方式（3）取消替換分類(lèi)匯總（4）顯示多級(jí)匯總結(jié)果7.3.3數(shù)據(jù)分類(lèi)匯總：2.多級(jí)分類(lèi)匯總7.3.3數(shù)據(jù)分類(lèi)匯總：3.分級(jí)顯示匯總結(jié)果使用分級(jí)工具條切換數(shù)據(jù)創(chuàng)建三級(jí)顯示的分類(lèi)匯總，利用分級(jí)工具條切換顯示級(jí)別，隱藏或顯示明細(xì)數(shù)據(jù)。數(shù)據(jù)分類(lèi)匯總通過(guò)單擊數(shù)據(jù)按鈕，可選擇顯示一級(jí)、二級(jí)或三級(jí)數(shù)據(jù)，其他數(shù)據(jù)自動(dòng)隱藏，便于查看不同級(jí)別的匯總信息。分級(jí)顯示匯總結(jié)果7.3.3數(shù)據(jù)分類(lèi)匯總：3.分級(jí)顯示匯總結(jié)果單擊最后一級(jí)數(shù)據(jù)按鈕，可查看所有原始明細(xì)，實(shí)現(xiàn)數(shù)據(jù)的完全展開(kāi)。顯示全部明細(xì)數(shù)據(jù)7.3.3數(shù)據(jù)分類(lèi)匯總可以通過(guò)分級(jí)按鈕和展開(kāi)/折疊按鈕查看分類(lèi)明細(xì)數(shù)據(jù)，或使用“數(shù)據(jù)”選項(xiàng)卡中的“顯示明細(xì)數(shù)據(jù)”和“隱藏明細(xì)數(shù)據(jù)”按鈕進(jìn)行操作。4.查看明細(xì)數(shù)據(jù)7.3.3數(shù)據(jù)分類(lèi)匯總：5.保存分類(lèi)匯總數(shù)據(jù)復(fù)制分類(lèi)匯總數(shù)據(jù)可便于在其他工作表中使用，只需復(fù)制匯總行到其他單元格或區(qū)域。復(fù)制分類(lèi)匯總數(shù)據(jù)選中分類(lèi)匯總數(shù)據(jù)區(qū)域，點(diǎn)擊“開(kāi)始”選項(xiàng)卡中的“查找”下拉菜單，選擇“定位”命令，然后在“定位”對(duì)話(huà)框中選中“可見(jiàn)單元格”并確認(rèn)。選中并定位匯總數(shù)據(jù)復(fù)制并粘貼匯總數(shù)據(jù)時(shí)，僅包含分類(lèi)匯總，不包括明細(xì)。操作步驟：選中要復(fù)制的單元格，Ctrl+C復(fù)制，新工作表中選定位點(diǎn)，Ctrl+V粘貼。復(fù)制并粘貼匯總數(shù)據(jù)7.3.3數(shù)據(jù)分類(lèi)匯總?cè)粢宄诸?lèi)匯總，可點(diǎn)擊“數(shù)據(jù)”中的“分類(lèi)匯總”按鈕，然后選擇“全部刪除”，