數(shù)據(jù)管理與決策第七講ppt課件

1、EXCEL培訓(xùn)了解函數(shù)字符函數(shù)日期函數(shù)統(tǒng)計(jì)函數(shù)數(shù)學(xué)函數(shù)查找函數(shù)課后習(xí)題EXCEL函數(shù)運(yùn)用了解函數(shù)數(shù) 據(jù)規(guī)那么什 么 是 函 數(shù)？結(jié)果函 數(shù) 的 格 式SUM(1，3，5) = 9函 數(shù) 名數(shù) 據(jù)結(jié) 果EXCEL函數(shù)的構(gòu)造Excel 函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處置數(shù)據(jù)義務(wù)的特殊公式。Excel 函數(shù)構(gòu)造:也有一些函數(shù)是沒有參數(shù)的,如ROW()左右括號成對出現(xiàn)單一構(gòu)造嵌套構(gòu)造參數(shù)與參數(shù)之間運(yùn)用半角逗號進(jìn)展分隔運(yùn)用公式公式中的運(yùn)算符 算術(shù)運(yùn)算符算術(shù)運(yùn)算符號運(yùn)算符含義示 例+(加號)加2+3=5-(減號)減3-1=2*(星號乘3*2=6/(斜杠)除6/2=3%(百分號)百分號50%(脫字號)乘

2、方43=43=64運(yùn)用公式公式中的運(yùn)算符2. 文本運(yùn)算符 “&號,可以將文本銜接起來.3. 比較運(yùn)算符比較運(yùn)算符運(yùn)算符含義示 例=(等號)相等B1=C1,若B1中單元格內(nèi)的值確實(shí)與C1中的值相等,則產(chǎn)生邏輯真值TRUE,若不相等,則產(chǎn)生邏輯假值FALSE(小于號)小于B1(大于號)大于B1C1,若B1中數(shù)值為6,C1中數(shù)值為4,則條件成立產(chǎn)生邏輯真值TRUE,否則產(chǎn)生邏輯假值FALSE=(大于等于號)大于等于B1=C1(不等號)不等于B1C1=(小于等于號)小于等于B1=C1運(yùn)用公式公式中的運(yùn)算符4. 援用運(yùn)算符引用運(yùn)算符運(yùn)算符含義示 例:區(qū)域運(yùn)算符,產(chǎn)生對包括在丙相引用之間的所有單元格的引用

3、(A5:A15),聯(lián)合運(yùn)算符,將多個(gè)引用合并為一個(gè)引用SUM(A5:A15,C5:C15)(空格)交叉運(yùn)算符,產(chǎn)生對兩個(gè)引用構(gòu)有的單元格的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可寫為: A4=SUM(B4:F4)運(yùn)用公式公式中的運(yùn)算順序 假設(shè)在公式中要同時(shí)運(yùn)用多個(gè)運(yùn)算符,那么應(yīng)該了解運(yùn)算符的優(yōu)先級.算術(shù)運(yùn)算符的優(yōu)先級是先乘冪運(yùn)算,再乘、除運(yùn)算,最后為加、減運(yùn)算。一樣優(yōu)先級的運(yùn)算符按從左到右的次序進(jìn)展運(yùn)算。 公式中出現(xiàn)不同類型的運(yùn)算符混用時(shí),運(yùn)算次序是：援用運(yùn)算符算術(shù)運(yùn)算符文本運(yùn)算符。假設(shè)需求改動次序,可將先要計(jì)算的部分括上圓括號。運(yùn)用公式援用單元格相對援用 相對

4、援用指公式中的單元格位置將隨著公式單元格的位置而改動2. 絕對援用 絕對援用是指公式和函數(shù)中的位置是固定不變的. 絕對援用是在列字母和行數(shù)字之前都加上美圓符號$,如$A$4,$C$63. 混合援用 混合援用是指在一個(gè)單元格援用中,既有絕對援用,也有相對援用. 例:行變列不變: $A4 列變行不變:A$4創(chuàng)建公式公式是指對任務(wù)表中的數(shù)據(jù)進(jìn)展分析和處置的一個(gè)等式,不僅用于算術(shù)計(jì)算,也用于邏輯運(yùn)算、日期和時(shí)間的計(jì)算等。一個(gè)公式必需具備3個(gè)條件：以等式開頭;2. 含有運(yùn)算符和運(yùn)算對象;3. 能產(chǎn)生結(jié)果公式的3個(gè)根本組成部分:數(shù)值:包括數(shù)字和文本單元格援用操作符 創(chuàng)建公式創(chuàng)建公式的方法運(yùn)用編輯欄直接在單

5、元格中輸入創(chuàng)建3種類型的公式創(chuàng)建簡單公式;2. 創(chuàng)建包含援用或稱號的公式3. 創(chuàng)建包含函數(shù)的公式運(yùn)用函數(shù)函數(shù)是指預(yù)定義的內(nèi)置公式,可以進(jìn)展數(shù)學(xué)、文本和邏輯的運(yùn)算或者查找任務(wù)表的信息。函數(shù)是一個(gè)曾經(jīng)提供應(yīng)用戶的公式,并且有一個(gè)描畫性的稱號。函數(shù)構(gòu)造函數(shù)稱號：假設(shè)要查看可用函數(shù)的列表,可以單擊一個(gè)單元格并按Shift+F3鍵；參數(shù)：參數(shù)可以是數(shù)字、文本、邏輯值TRUE或FALSE、數(shù)值、錯(cuò)誤值如#N/A或單元格援用。參數(shù)工具提示：在輸入函數(shù)時(shí),會出現(xiàn)一個(gè)帶有語法和參數(shù)的工具提示。輸入公式：“插入函數(shù)對話框。函數(shù)的參數(shù)函數(shù)的參數(shù)是一個(gè)函數(shù)體中括號內(nèi)的部分邏輯值: FALSE :需求完全一致 TRUE

6、: 類似值亦可轉(zhuǎn)換運(yùn)用函數(shù)函數(shù)的類型 1. 常用函數(shù):常用函數(shù)指經(jīng)常運(yùn)用的函數(shù),如求和或計(jì)算算術(shù)平均值等.包括SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF和PMT.它們的語法和作用如下表:語法作用SUM(number1,number2, )返回單元格區(qū)域中所有數(shù)值的和ISPMT(Rate,Per,Nper,Pv)返回普通(無提保)的利息償還AVERAGE(Number1,Number2, )計(jì)算參數(shù)的算術(shù)平均數(shù),參數(shù)可以是數(shù)值或包含數(shù)值的名稱數(shù)組和引用IF(Logical_test,Value_if_true,Value_if_fal

7、se)執(zhí)行真假判斷,根據(jù)對指定條件進(jìn)行邏輯評價(jià)的真假而返回不同的結(jié)果。HYPERLINK(Link_location,Friendly_name)創(chuàng)建快捷方式,以便打開文檔或網(wǎng)絡(luò)驅(qū)動器或連接連接INTERNETCOUNT(Value1,Value2, )計(jì)算參數(shù)表中的數(shù)字參數(shù)和包含數(shù)字的單元格的個(gè)數(shù)MAX(number1,number2, )返回一組數(shù)值中的最大值,忽略邏輯值和文字符SIN (number)返回紀(jì)念品定角度的正弦值SUMIF (Range, Criteria, Sum_range)根據(jù)指定條伯對若干單元格求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下,投

8、資或貸款的等額分期償還額運(yùn)用函數(shù)函數(shù)的類型 2. 財(cái)務(wù)函數(shù):財(cái)務(wù)函數(shù)用于財(cái)務(wù)的計(jì)算,它可以根據(jù)利率、貸款金額和期限計(jì)算出所要支付的金額,它們的變量嚴(yán)密相互關(guān)聯(lián)。 如：PMT(Rate,Nper,Pv,Fv,Type) =PMT0.006,360,-300000) 其中,0.006是每一筆貸款項(xiàng)的利率,360是款項(xiàng)的數(shù)目,-300000是360筆款項(xiàng)的當(dāng)前金額(在計(jì)算支付金額時(shí),表示當(dāng)前和未來金額的變量至少有一個(gè)通常是負(fù)數(shù))注:Rate 貸款利率。Nper 該項(xiàng)貸款的付款總數(shù)。Pv 現(xiàn)值,或一系列未來付款的當(dāng)前值的累積和,也稱為本金。Fv 為未來值,或在最后一次付款后希望得到的現(xiàn)金余額,假設(shè)省略

9、 fv那么假設(shè)其值為零,也就是一筆貸款的未來值為零。Type 數(shù)字 0 或 1,用以指定各期的付款時(shí)間是在期初還是期末。運(yùn)用函數(shù)函數(shù)的類型 3. 時(shí)間和日期函數(shù):主要用于分析和處置日期值和時(shí)間值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。例：函數(shù)DATA語法：DATE(year,month,day)在單元格中插入當(dāng)前日期和時(shí)間 插入靜態(tài)的日期和時(shí)間當(dāng)前日期 選取一個(gè)單元格,并按 Ctrl+; 當(dāng)前時(shí)間 選取一個(gè)單元格,并按 Ctrl+Shift+；當(dāng)前日期和時(shí)間 選取一個(gè)單元格,并按 Ctrl+;,然后按空格鍵,最后按 Ctrl+

10、Shift+; 插入會更新的日期和時(shí)間運(yùn)用 TODAY 和 NOW 函數(shù)完成這一任務(wù)。小貼士運(yùn)用函數(shù)函數(shù)的類型 4. 數(shù)學(xué)與三角函數(shù): 5. 統(tǒng)計(jì)函數(shù): 統(tǒng)計(jì)函數(shù)是用來對數(shù)據(jù)區(qū)域進(jìn)展統(tǒng)計(jì)分析。主要用于進(jìn)展各種各樣的計(jì)算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。例：ABS函數(shù)語法：ABS(number)函數(shù)是一種特殊的公式,因此一切的函數(shù)要以“=開場。運(yùn)用方法如下：1.選中要輸入函數(shù)的單元格 2.單擊Insert菜單中的 命令,彈出如下圖的對話框：運(yùn)用函數(shù)3.在上圖的對話框中,選擇函數(shù)分類下面的函數(shù)類型及函數(shù)名下面的對應(yīng)函數(shù)名。4.

11、單擊確定按鈕,彈出如下圖的相應(yīng)的調(diào)用函數(shù)對話框：運(yùn)用函數(shù)函數(shù)與公式通用技巧限制：突破： 運(yùn)用定義稱號。當(dāng)參數(shù)為單元格區(qū)域援用時(shí)，可運(yùn)用一對括號來包含多個(gè)單元格區(qū)域的援用部分構(gòu)成合并區(qū)域援用作為函數(shù)參數(shù)。1、提早將單元格格式設(shè)置為文本后再輸入數(shù)字2、在輸入數(shù)字時(shí)用半角單引號開頭。公式內(nèi)容的長度不能超越1024個(gè)字符；公式中函數(shù)的嵌套不能超越7層；公式中函數(shù)的參數(shù)不能超越30個(gè)；數(shù)字計(jì)算精度為15位。函數(shù)與公式通用技巧改動任務(wù)表的計(jì)算精度 在實(shí)踐任務(wù)中，許多用戶在計(jì)算數(shù)字時(shí)會設(shè)置單元格格式為2為小數(shù)的數(shù)值、貨幣格式或者是整數(shù)值。但在運(yùn)用公式進(jìn)展數(shù)據(jù)處置的過程中，經(jīng)常會發(fā)現(xiàn)幾個(gè)單元格之和與合計(jì)單元格

12、顯示的數(shù)據(jù)之和不相等。 由于excel將數(shù)值按照15位計(jì)算精度帶入計(jì)算所致。如圖，在excel選項(xiàng)中勾選“將精度設(shè)為所顯示的精度，可以處理上述情況的發(fā)生，但這樣也降低了任務(wù)薄中一切任務(wù)表的計(jì)算精度，但不影響其他任務(wù)薄。 此外，也可以用ROUND函數(shù)對數(shù)值進(jìn)展修約后再求和。函數(shù)與公式通用技巧分布查看公式計(jì)算結(jié)果方法一： 選中包含公式的單元格，單擊“公式選項(xiàng)卡中的“公式求值按鈕，彈出“公式求值對話框，然后多次單擊“求值按鈕，“求值域中將按照公式計(jì)算的順序逐漸地顯示公式的計(jì)算過程。方法二： 功能鍵除了可以重算任務(wù)薄外，還可以用于查看公式的運(yùn)算結(jié)果。 當(dāng)一個(gè)函數(shù)公式比較長時(shí)，可以用鍵對各個(gè)部分進(jìn)展分段

13、計(jì)算，以便公式過失和更正。在分段選擇時(shí)，留意要包含一個(gè)完好的運(yùn)算對象。按功能鍵切換數(shù)據(jù)的挑選請使用若要查找?（問號）任何單個(gè)字符例如，sm?th 查找“smith”和“smyth”*（星號）任何字符數(shù)例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 問號、星號或波形符例如，“fy91?”將會查找“fy91?”通配符以下通配符可作為挑選以及查找和交換內(nèi)容時(shí)的比較條件:分類匯總的概述分類匯總是對數(shù)據(jù)清單進(jìn)展數(shù)據(jù)分析的一種方法.Data/ Subtotal分類匯總確保要進(jìn)展分類匯總的數(shù)據(jù)為以下格式：第一行的每一列都有標(biāo)志，并且同一列中應(yīng)包含類似的數(shù)據(jù)，在

14、區(qū)域中沒有空行或空列。 單擊要分類匯總的列中的單元格。在上面的例如中，應(yīng)單擊“運(yùn)動列列 B中的單元格。 單擊“升序排序 或“降序排序。 在“數(shù)據(jù)菜單上，單擊“分類匯總。 在“分類字段框中，單擊要分類匯總的列。在上面例如中，應(yīng)單擊“運(yùn)動列。 在“匯總方式框中，單擊所需的用于計(jì)算分類匯總的匯總函數(shù)。 在“選定匯總項(xiàng)框中，選中包含了要進(jìn)展分類匯總的數(shù)值的每一列的復(fù)選框。在上面的例如中，應(yīng)選中“銷售列。 分類匯總要分類匯總的列分類匯總步驟:假設(shè)想在每個(gè)分類匯總后有一個(gè)自動分頁符，請選中“每組數(shù)據(jù)分頁復(fù)選框。 假設(shè)希望分類匯總結(jié)果出如今分類匯總的行的上方，而不是在行的下方，請去除“匯總結(jié)果顯示在數(shù)據(jù)下方

15、復(fù)選框。 單擊“確定。 注釋 可再次運(yùn)用“分類匯總命令來添加多個(gè)具有不同匯總函數(shù)的分類匯總。假設(shè)要防止覆蓋已存在的分類匯總，請去除“交換當(dāng)前分類匯總復(fù)選框。分類匯總要分類匯總的列分類匯總刪除分類匯總刪除分類匯總時(shí)，Microsoft Excel 也刪除分級顯示以及隨分類匯總一同插入列表中的一切分頁符。在含有分類匯總的列表中，單擊任一單元格。 在“數(shù)據(jù)菜單上，單擊“分類匯總。 單擊“全部刪除。 分類匯總刪除分類匯總刪除分類匯總時(shí)，Microsoft Excel 也刪除分級顯示以及隨分類匯總一同插入列表中的一切分頁符。在含有分類匯總的列表中，單擊任一單元格。 在“數(shù)據(jù)菜單上，單擊“分類匯總。 單擊

16、“全部刪除。 分類匯總字符函數(shù)Left/Right: 截取字符串函數(shù)用途: 根據(jù)指定的字符數(shù)前往文本串中的第一個(gè)或前幾個(gè)字符。語法：Left(文本，前往的字符數(shù)) Right(文本，前往的字符數(shù))參數(shù)：前往的字符數(shù)必需大于或等于0實(shí)例：前往客戶姓名中的姓氏 A1單元格字符串“張三,運(yùn)用Left(A1,1) 得到結(jié)果“張字符函數(shù)Left/Right:綜合運(yùn)用前往身份證號中的“出生年份信息例如：A1單元格有身份證號碼用Right(Left(A1,10),4) 得到結(jié)果1982字符函數(shù)Mid: 截取字符串函數(shù)用途: Mid前往文本串中從指定位置開場的特定數(shù)目的

17、字符，該數(shù)目可以由用戶指定.語法：Mid(文本串，開場位置，前往字符的個(gè)數(shù))參數(shù)：開場位置是文本中要提取的第一個(gè)字符的位置，文本中第一個(gè)字符的開場位置為1，以此類推。例如：A1單元格有身份證號碼用Mid(A1,7,4)得到結(jié)果1982字符函數(shù)LEN: 計(jì)算字符串長度用途: LEN前往文本的字符數(shù) 語法：LEN(文本)參數(shù)：此函數(shù)用于雙字節(jié)字符，且空格也將作為字符進(jìn)展統(tǒng)計(jì)。例如：檢查身份證號碼能否輸入了18位。 A1單元格有身份證號碼用LEN(A1)得到結(jié)果18類似的函數(shù)LENB，前往單字節(jié)字符。例如：LENB(“A

18、BC) 前往 3，LENB(“張三)前往4字符函數(shù)TRIM: 刪除指定字符串多余的前導(dǎo)和后綴的空格用途: 刪除前導(dǎo)和后綴的空格 語法：TRIM(文本)參數(shù)：字符串例如：A1單元格有姓名“ 張三 運(yùn)用TRIMA1得到結(jié)果“張三字符函數(shù)Replace: 交換指定位置處的恣意文本用途: REPLACE運(yùn)用其他文本串并根據(jù)所指定的字符數(shù)交換另一文本串中的部分文本。 語法：Replace(需求交換其部分字符的文本，被交換字符的起始位置，交換的字符個(gè)數(shù)，用于交換到原文本中去的字符)例如：A1單元格有公司稱號“廣東XXX，將“廣東交換成“廣東省。運(yùn)用Replace(A1,1,2,廣東省)得到結(jié)果廣東省XXX

19、字符函數(shù)SUBSTITUTE: 交換指定文本用途: 在文字串中用新文本替代舊文本。假設(shè)需求在一個(gè)文字串中交換指定文本，可以運(yùn)用SUBSTITUTE；假設(shè)需求在某一字符串中交換指定位置處的恣意文本，就應(yīng)該運(yùn)用函數(shù)REPLACE。 語法： SUBSTITUTE(需求交換其部分字符的文本，需被交換舊文本，用于交換的文本，第幾次出現(xiàn)的舊文本)參數(shù)：假設(shè)不輸入第四個(gè)參數(shù)將交換一切舊文本。例如：A1單元格有客戶編碼G000G001,將第二個(gè)G交換成0.運(yùn)用SUBSTITUTE(A1,G,“0,2)得到結(jié)果G0000001運(yùn)用SUBSTITUTE(A1,G,“0)得到結(jié)果00000001字符函數(shù)Replac

20、e、 SUBSTITUTE綜合運(yùn)用案例闡明：將以下圖中混亂的編碼進(jìn)展整理運(yùn)用函數(shù)：=REPLACE(SUBSTITUTE(A1, ,),2,1,M )字符函數(shù)Rept: 補(bǔ)位函數(shù)用途: 按照給定的次數(shù)反復(fù)顯示文本?？梢越?jīng)過REPT函數(shù)對單元格進(jìn)展反復(fù)填充。語法：Rept(需求反復(fù)顯示的文本，反復(fù)顯示的次數(shù))參數(shù)：反復(fù)顯示的次數(shù)需求為正數(shù)。實(shí)例：Rept很好,2前往“很好很好。字符函數(shù)Rept函數(shù)與LEN函數(shù)的綜合運(yùn)用運(yùn)用函數(shù)：=REPT(0,6-LEN(A1) & A1日期函數(shù)NOW/TODAY(): 前往當(dāng)前日期用途: 前往當(dāng)前電腦的日期和時(shí)間語法：NOW()參數(shù)：此函數(shù)為無參數(shù)函數(shù)實(shí)例：=NOW() 前往 “2021-11-05 11:00相對應(yīng)的