《Excel 2021基礎(chǔ)與應(yīng)用》課件 項目七 數(shù)值計算與分析

數(shù)值計算與分析3621任務(wù)1處理實驗數(shù)據(jù)2任務(wù)2

制作員工工資表3任務(wù)3

分析身份證號目錄CONTENTS363任務(wù)1處理實驗數(shù)據(jù)364學(xué)習(xí)目標(biāo)1.能描述數(shù)據(jù)計算在Excel2021中的基礎(chǔ)作用及公式的組成和作用。2.能完成用公式直接輸入數(shù)據(jù)。365任務(wù)描述在本任務(wù)中利用公式計算下表中試樣的體積與密度。用到的公式如下：體積=長×寬×高，密度=質(zhì)量÷體積。366實驗數(shù)據(jù)相關(guān)知識1.相關(guān)概念（1）公式。公式用于對工作表中的數(shù)值進行運算，以等號（=）開頭。公式可以包括函數(shù)、引用、運算符和常量等。（2）函數(shù)。函數(shù)用于對一個或多個值按照預(yù)設(shè)的規(guī)則執(zhí)行運算，并返回一個或多個值。367（3）引用。在如圖所示的公式中，“A2”表示使用單元格A2中的值，這里的“A2”就是引用。368圓面積公式的組成（4）運算符。運算符是指一個標(biāo)記或符號，用于指定表達式內(nèi)執(zhí)行的計算類型，包括算術(shù)、比較、邏輯、位、賦值、連接運算符等。（5）常量。常量是指在運算過程中不發(fā)生變化的量，也就是不用計算的值，如數(shù)字“20”、文本“收入”等都是常量。3692.運算符（1）算術(shù)運算符（見下表）。如果要完成基本的數(shù)學(xué)運算（如加法、減法、乘法等）、數(shù)字合并以及數(shù)值結(jié)果生成，就可以使用算術(shù)運算符。370

算術(shù)運算符（2）比較運算符（見下表）。比較運算符可以用于比較兩個值，比較結(jié)果為邏輯值TRUE或者FALSE。371

比較運算符（3）文本連接運算符（見下表）?？梢允褂门c號（&）連接多個文本字符串，以生成一段文本。372文本連接運算符（4）引用運算符（見下表）。可以利用引用運算符對單元格區(qū)域進行合并計算。373引用運算符實踐操作（1）啟動Excel2021，新建空白工作簿，將其保存并命名為“實驗數(shù)據(jù)的處理”。（2）在工作表中輸入如圖所示的內(nèi)容。輸入表頭等內(nèi)容374（3）將實驗中用尺測得的試樣的長、寬、高以及用天平測得的質(zhì)量，分別輸入工作表中相應(yīng)的位置，如圖所示。輸入實驗測得的數(shù)據(jù)375（4）根據(jù)“體積=長×寬×高”，計算試樣1的體積，在單元格E2中（或者編輯欄中）輸入公式“=B2*C2*D2”（“*”代表乘號），如圖所示，輸入公式過程中引用單元格可以用單擊的方式。輸入完畢，按Enter鍵（或者單擊編輯欄上的“輸入”按鈕）即可在單元格E2中得到計算值，如圖所示。376輸入體積公式377378體積計算值（5）根據(jù)“密度=質(zhì)量÷體積”，在單元格G2中輸入公式“=F2/E2”，如圖所示，密度計算值如圖所示。379輸入密度公式密度計算值380381（6）編輯公式。若需要修改單元格G3中的公式，只需要雙擊單元格G3，出現(xiàn)光標(biāo)后就可以修改（見下圖）。修改完畢，按Enter鍵或者Esc鍵即可退出編輯公式狀態(tài)，修改公式后的計算結(jié)果如圖所示。修改公式382修改公式后的計算結(jié)果任務(wù)2制作員工工資表383學(xué)習(xí)目標(biāo)1.能描述Excel2021中函數(shù)的主要功能。2.能運用VLOOKUP、SUM、IF等函數(shù)計算處理數(shù)據(jù)。384任務(wù)描述本任務(wù)利用VLOOKUP、XLOOKUP、IF和SUM函數(shù)制作員工工資表。利用VLOOKUP和XLOOKUP函數(shù)在數(shù)組數(shù)據(jù)中找到并返回相對應(yīng)的值，可以減少輸入量并提高數(shù)據(jù)輸入的正確率。SUM函數(shù)用于工資表中的加法運算，可以求一系列數(shù)據(jù)的和。使用IF函數(shù)時，可以指定要執(zhí)行的邏輯檢測，在工資表特崗津貼的計算中可以用它來檢測是否給予津貼。385相關(guān)知識1.插入函數(shù)方法一：選中需要插入函數(shù)的單元格，在編輯欄中輸入“=”“函數(shù)名”。方法二：選中需要插入函數(shù)的單元格，單擊編輯欄中的“插入函數(shù)”按鈕，或單擊“公式”|“函數(shù)庫”|“插入函數(shù)”按鈕，在彈出的“插入函數(shù)”對話框中的“搜索函數(shù)”欄中輸入所需函數(shù)，單擊“轉(zhuǎn)到”按鈕，在“選擇函數(shù)”列表中選擇所需函數(shù)，單擊“確定”按鈕，即可插入所選函數(shù)。3862.函數(shù)的類型

（1）求和函數(shù)SUMSUM函數(shù)用于計算單元格區(qū)域中所有數(shù)值的和，其語法格式為=SUM(number1,number2,…)，該函數(shù)中各參數(shù)含義如下。第1個參數(shù)“number1”為必需參數(shù)，是要相加的第1個數(shù)值，該數(shù)值可以是數(shù)字、引用的單元格以及單元格區(qū)域。第2個參數(shù)“number2”是要相加的第2個數(shù)值。387（2）邏輯函數(shù)IFIF函數(shù)用來判斷是否滿足某個條件，若滿足則返回一個值，若不滿足則返回另一個值。其語法格式為=IF(logical_test,value_if_true,value_if_false)，該函數(shù)中各參數(shù)含義如下。第1個參數(shù)“l(fā)ogical_test”為條件，可以是任何能被計算為TRUE或FALSE的數(shù)值或表達式。第2個參數(shù)“value_if_true”是logical_test為TRUE時的返回值，若忽略，則返回TRUE。第3個參數(shù)“value_if_false”是logical_test為FALSE時的返回值，若忽略，則返回FALSE。IF函數(shù)最多可嵌套7層。388（3）邏輯函數(shù)VLOOKUP和XLOOKUP1）VLOOKUP函數(shù)是Excel2021中的一個縱向查找函數(shù)，具有核對數(shù)據(jù)、在多個表格之間快速導(dǎo)入數(shù)據(jù)等功能。2）XLOOKUP函數(shù)用于搜索數(shù)據(jù)區(qū)域中的值，返回找到的第一個匹配結(jié)果。可以把它看作VLOOKUP函數(shù)的升級版，XLOOKUP函數(shù)彌補了VLOOKUP函數(shù)的諸多不足，可以說是Excel2021中功能最強大的查找函數(shù)。389（4）數(shù)據(jù)庫函數(shù)DCOUNTDCOUNT函數(shù)的作用是返回列表或數(shù)據(jù)庫中滿足指定條件的記錄字段（列）中包含數(shù)字的單元格個數(shù)。（5）統(tǒng)計函數(shù)AVERAGEAVERAGE函數(shù)是Excel2021中用于計算平均值的函數(shù)。（6）統(tǒng)計函數(shù)MAX和MINMAX和MIN函數(shù)是Excel2021中用于取最大或最小值的函數(shù)。其語法格式為=MAX/MIN(number1,number2,…)，該函數(shù)中參數(shù)含義如下。參數(shù)“number1”“number2”可以是數(shù)值，也可以是單元格引用，最多可以設(shè)置255個參數(shù)值。3903.輸入函數(shù)時的注意事項（1）必須在英文輸入法狀態(tài)下書寫公式，除非是必要的漢字。（2）所有的公式必須以“=”開頭，所有的函數(shù)必須滿足格式“函數(shù)名()”，如SUM()。（3）注意公式運算的優(yōu)先級，必要時用括號更改。（4）即使公式正確，數(shù)據(jù)格式也可能導(dǎo)致錯誤結(jié)果。（5）注意相對引用和絕對引用的區(qū)別。（6）部分函數(shù)的可選參數(shù)在省略的情況下會被當(dāng)作省略值處理，如VLOOKUP函數(shù)的第4個參數(shù)如果省略，將被當(dāng)作TRUE處理，進行近似匹配。391392實踐操作（1）啟動Excel2021，創(chuàng)建新的空白工作簿，并將其命名為“工資表”，輸入工資表內(nèi)容。在工作表的單元格區(qū)域A1:D6中輸入如圖所示的信息。

輸入信息393（2）用序列填充的方法填寫員工編號。在單元格A9和A10中輸入“2007001”“2007002”，選中這兩個單元格，如圖所示，用序列填充的方法按照遞增順序自動填充員工編號，如圖所示。

選中兩個連續(xù)編號的單元格

自動填充員工編號394395（3）利用公式輸入部門名稱。在單元格D9中輸入公式“=VLOOKUP(C9,$A$2:$B$6,2）”，其中“=”是公式符號，“VLOOKUP”是查找函數(shù)，“C9”是待查找的單元格，“$A$2:$B$6”是查找區(qū)域，“2”表示返回區(qū)域中第2列值，如圖所示。按Enter鍵確認(rèn)，生成部門編號“B03”對應(yīng)的部門名稱“銷售部”。利用公式輸入部門名稱396397（4）利用插入函數(shù)的方法輸入類別名稱。在單元格F9中插入函數(shù)，設(shè)置XLOOKUP函數(shù)的參數(shù)，如圖所示，單擊“確定”按鈕即可生成“銷售人員”。設(shè)置XLOOKUP函數(shù)的參數(shù)398（5）按照公司規(guī)定，特崗津貼根據(jù)類別名稱計算，管理人員的特崗津貼為300元，技術(shù)人員的特崗津貼為500元，其余人員沒有特崗津貼。在單元格J9中輸入公式“=IF(F9="

管理人員",300,IF(F9="技術(shù)人員",500,0))”，這個公式可理解為判斷單元格F9的值是否為“管理人員”，若是，則返回值為300，若不是，則再判斷是否為“技術(shù)人員”，若是，則返回值為500，否則為0，結(jié)束函數(shù)計算。按Enter鍵確認(rèn)即可計算出第一位員工的特崗津貼為0，如圖所示。399計算特崗津貼400（6）通過公式計算應(yīng)發(fā)工資。應(yīng)發(fā)工資=基本工資+住房補貼+交通補貼+特崗津貼，因此在單元格K9中輸入“=SUM(G9,H9,I9,J9)”（見下圖）或“=G9+H9+I9+J9”后，按Enter鍵確認(rèn)即可生成應(yīng)發(fā)工資的數(shù)值“6500”。401輸入計算應(yīng)發(fā)工資的公式402（7）復(fù)制填充公式。分別對其他員工的部門名稱、類別名稱、特崗津貼、應(yīng)發(fā)工資進行計算，向下拖動填充柄選中想要填充公式的單元格區(qū)域，松開鼠標(biāo)，得到填充數(shù)據(jù)，如圖所示。填充數(shù)據(jù)403（8）全部操作完成后，將工作簿保存。任務(wù)3分析身份證號404學(xué)習(xí)目標(biāo)1.能判斷Excel公式常見錯誤值出現(xiàn)的原因并解決。2.能運用MID、YEAR等函數(shù)計算處理數(shù)據(jù)。405任務(wù)描述在身份證號中，第7～14位表示出生年月日為1966年03月23日。在本任務(wù)中利用MID、YEAR函數(shù)以及公式根據(jù)工作表中的身份證號分析出出生日期、出生年份和年齡。利用MID函數(shù)可以從文本字符串中的指定位置開始返回特定數(shù)目的字符，利用YEAR函數(shù)可以返回某日期所對應(yīng)的年份。406相關(guān)知識如果公式不能正確計算出結(jié)果，Excel將顯示一個錯誤值。每個錯誤類型都有不同的原因和解決方法。1.顯示“＃DIV/0”錯誤原因：公式中的除數(shù)為0。解決方法：修改單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值。4072.顯示“＃N/A”錯誤原因：當(dāng)在函數(shù)或公式中沒有可用的數(shù)值時，將產(chǎn)生錯誤值“＃N/A”。解決方法：如果工作表中某些單元格暫時沒有數(shù)值，則在這些單元格中輸入“＃N/A”，公式在引用這些單元格時，將不進行數(shù)值計算，而是返回“＃N/A”。3.顯示“＃NAME？”錯誤原因：在公式中使用了Excel不能識別的文本，如函數(shù)名稱拼寫錯誤。解決方法：改正錯誤的文本內(nèi)容。4084.顯示“＃NULL！”錯誤原因：試圖為兩個并不相交的區(qū)域指定交叉點，如在引用單元格區(qū)域時誤將“:”寫為空格，或引用兩個不相交區(qū)域時，誤將“,”寫為空格。解決方法：將單元格區(qū)域與運算符修改正確。5.顯示“＃NUM！”錯誤原因：公式或函數(shù)中某些數(shù)字有問題。解決方法：檢查數(shù)字是否超出限定區(qū)域，確認(rèn)函數(shù)中使用的參數(shù)類型是否正確。4096.顯示“＃REF！”錯誤原因：單元格引用無效。解決方法：修改公式中的單元格引用。7.顯示“＃VALUE！”錯誤原因：使用錯誤的參數(shù)或運算對象類型，或Excel執(zhí)行自動更改公式功能后仍未能更正公式。解決方法：確認(rèn)公式或函數(shù)所需的參數(shù)或運算符正確，并且確認(rèn)公式引用的單元格中均為有效的數(shù)據(jù)。410實踐操作（1）啟動Excel2021，創(chuàng)建空