數據管理與決策 第七講

1、EXCELEXCEL培訓培訓理解函數字符函數日期函數統(tǒng)計函數數學函數查找函數課后習題EXCELEXCEL函數應用函數應用理解函數理解函數數 據規(guī)則什 么 是 函 數？結果函 數 的 格 式SUM(1，3，5) = 9函 數 名數 據結 果EXCEL函數的結構 Excel 函數即是預先定義，執(zhí)行計算、分析等處理數據任務的特殊公式特殊公式。Excel 函數結構:也有一些函數是沒有參數的,如ROW()左右括號成對出現單一結構嵌套結構參數與參數之間使用半角逗號進行分隔使用公式公式中的運算符1. 算術運算符算術運算符號算術運算符號運算符含義運算符含義示示 例例+(加號加號)加加2+3=5-(減號減號)減

2、減3-1=2*(星號星號乘乘3*2=6/(斜杠斜杠)除除6/2=3%(百分號百分號)百分號百分號50%(脫字號脫字號)乘方乘方43=43=64使用公式公式中的運算符2. 文本運算符 “&”號,可以將文本連接起來.3. 比較運算符比較運算符比較運算符運算符含義運算符含義示示 例例=(等號等號)相等相等B1=C1,若若B1中單元格內的值確實與中單元格內的值確實與C1中的值相中的值相等等,則產生邏輯真值則產生邏輯真值TRUE,若不相等若不相等,則產生邏輯則產生邏輯假值假值FALSE(小于號小于號)小于小于B1(大于號大于號)大于大于B1C1,若若B1中數值為中數值為6,C1中數值為中數值為4

3、,則條件成則條件成立產生邏輯真值立產生邏輯真值TRUE,否則產生邏輯假值否則產生邏輯假值FALSE=(大于等于號大于等于號)大于等于大于等于B1=C1(不等號不等號)不等于不等于B1C1=(小于等于號小于等于號)小于等于小于等于B1=C1使用公式公式中的運算符4. 引用運算符引用運算符引用運算符運算符含義運算符含義示示 例例:區(qū)域運算符區(qū)域運算符,產生對包產生對包括在丙相引用之間的括在丙相引用之間的所有單元格的引用所有單元格的引用(A5:A15),聯合運算符聯合運算符,將多個引將多個引用合并為一個引用用合并為一個引用SUM(A5:A15,C5:C15)(空格空格)交叉運算符交叉運算符,產生對兩

4、產生對兩個引用構有的單元格個引用構有的單元格的引用的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可寫為: A4=SUM(B4:F4)使用公式公式中的運算順序 如果在公式中要同時使用多個運算符,則應該了解運算符的優(yōu)先級.算術運算符的優(yōu)先級是先乘冪運算,再乘、除運算,最后為加、減運算。相同優(yōu)先級的運算符按從左到右的次序進行運算。 公式中出現不同類型的運算符混用時,運算次序是：引用運算符算術運算符文本運算符。如果需要改變次序,可將先要計算的部分括上圓括號。使用公式引用單元格1. 相對引用 相對引用指公式中的單元格位置將隨著公式單元格的位置而改變2. 絕對引用 絕對引用是指公

5、式和函數中的位置是固定不變的. 絕對引用是在列字母和行數字之前都加上美元符號”$”,如$A$4,$C$63. 混合引用 混合引用是指在一個單元格引用中,既有絕對引用,也有相對引用. 例:行變列不變: $A4 列變行不變:A$4創(chuàng)建公式公式是指對工作表中的數據進行分析和處理的一個等式,不僅用于算術計算,也用于邏輯運算、日期和時間的計算等。一個公式必須具備3個條件：1. 以等式開頭;2. 含有運算符和運算對象;3. 能產生結果公式的3個基本組成部分:1. 數值:包括數字和文本2. 單元格引用3. 操作符 創(chuàng)建公式創(chuàng)建公式的方法1. 使用編輯欄2. 直接在單元格中輸入創(chuàng)建3種類型的公式1. 創(chuàng)建簡單

6、公式;2. 創(chuàng)建包含引用或名稱的公式3. 創(chuàng)建包含函數的公式使用函數函數是指預定義的內置公式,可以進行數學、文本和邏輯的運算或者查找工作表的信息。函數是一個已經提供給用戶的公式,并且有一個描述性的名稱。函數結構1. 函數名稱：如果要查看可用函數的列表,可以單擊一個單元格并按Shift+F3鍵；2. 參數：參數可以是數字、文本、邏輯值（TRUE或FALSE）、數值、錯誤值（如#N/A）或單元格引用。3. 參數工具提示：在輸入函數時,會出現一個帶有語法和參數的工具提示。4. 輸入公式：“插入函數”對話框。函數的參數函數的參數是一個函數體中括號內的部分邏輯值: FALSE :需要完全一致 TRUE:

7、 相似值亦可轉換使用函數函數的類型 1. 常用函數:常用函數指經常使用的函數,如求和或計算算術平均值等.包括SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF和PMT.它們的語法和作用如下表:語法語法作用作用SUM(number1,number2, )返回單元格區(qū)域中所有數值的和返回單元格區(qū)域中所有數值的和ISPMT(Rate,Per,Nper,Pv)返回普通返回普通(無提保無提保)的利息償還的利息償還AVERAGE(Number1,Number2, )計算參數的算術平均數計算參數的算術平均數,參數可以是數值或包含數值參數可以是數值或包含數

8、值的名稱數組和引用的名稱數組和引用IF(Logical_test,Value_if_true,Value_if_false)執(zhí)行真假判斷執(zhí)行真假判斷,根據對指定條件進行邏輯評價的真假根據對指定條件進行邏輯評價的真假而返回不同的結果。而返回不同的結果。HYPERLINK(Link_location,Friendly_name) 創(chuàng)建快捷方式創(chuàng)建快捷方式,以便打開文檔或網絡驅動器或連接連以便打開文檔或網絡驅動器或連接連接接INTERNETCOUNT(Value1,Value2, )計算參數表中的數字參數和包含數字的單元格的個數計算參數表中的數字參數和包含數字的單元格的個數MAX(number1,n

9、umber2, )返回一組數值中的最大值返回一組數值中的最大值,忽略邏輯值和文字符忽略邏輯值和文字符SIN (number)返回紀念品定角度的正弦值返回紀念品定角度的正弦值SUMIF (Range, Criteria, Sum_range)根據指定條伯對若干單元格求和根據指定條伯對若干單元格求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下返回在固定利率下,投資或貸款的等額分期償還額投資或貸款的等額分期償還額使用函數函數的類型 2. 財務函數:財務函數用于財務的計算,它可以根據利率、貸款金額和期限計算出所要支付的金額,它們的變量緊密相互關聯。 如：PMT(Rate,Nper

10、,Pv,Fv,Type) =PMT（0.006,360,-300000) 其中,0.006是每一筆貸款項的利率,360是款項的數目,-300000是360筆款項的當前金額(在計算支付金額時,表示當前和將來金額的變量至少有一個通常是負數)注:Rate 貸款利率。Nper 該項貸款的付款總數。Pv 現值,或一系列未來付款的當前值的累積和,也稱為本金。Fv 為未來值,或在最后一次付款后希望得到的現金余額,如果省略 fv則假設其值為零,也就是一筆貸款的未來值為零。Type 數字 0 或 1,用以指定各期的付款時間是在期初還是期末。使用函數函數的類型 3. 時間和日期函數:主要用于分析和處理日期值和時間

11、值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。例：函數DATA語法：DATE(year,month,day)在單元格中插入當前日期和時間 插入靜態(tài)的日期和時間當前日期 選取一個單元格,并按 Ctrl+; 當前時間 選取一個單元格,并按 Ctrl+Shift+；當前日期和時間 選取一個單元格,并按 Ctrl+;,然后按空格鍵,最后按 Ctrl+Shift+; 插入會更新的日期和時間使用 TODAY 和 NOW 函數完成這一工作。使用函數函數的類型 4. 數學與三角函數: 5. 統(tǒng)計函數: 統(tǒng)計函數是用來對數據區(qū)域進行統(tǒng)計分析。主要用

12、于進行各種各樣的計算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。例：ABS函數語法：ABS(number)函數是一種特殊的公式,因此所有的函數要以“=”開始。使用方法如下：1.選中要輸入函數的單元格 2.單擊Insert菜單中的 命令,彈出如圖所示的對話框：使用函數3.在上圖的對話框中,選擇函數分類下面的函數類型及函數名下面的對應函數名。4. 單擊確定按鈕,彈出如圖所示的相應的調用函數對話框：使用函數限制：限制：突破：突破： 使用定義名稱。使用定義名稱。當參數為單元格區(qū)域引用時，當參數為單元格區(qū)域引用時，可使用一對括號來包含多個單可使

13、用一對括號來包含多個單元格區(qū)域的引用部分形成合并元格區(qū)域的引用部分形成合并區(qū)域引用作為函數參數。區(qū)域引用作為函數參數。1 1、提前將單元格格式設置為文、提前將單元格格式設置為文本后再輸入數字本后再輸入數字2 2、在輸入數字時用半角單引號、在輸入數字時用半角單引號開頭。開頭。公式內容的長度不能超過公式內容的長度不能超過10241024個個字符；字符；公式中函數的嵌套不能超過公式中函數的嵌套不能超過7 7層；層；公式中函數的參數不能超過公式中函數的參數不能超過3030個；個；數字計算精度為數字計算精度為1515位。位。 在實際工作中，許多用戶在計算在實際工作中，許多用戶在計算數字時會設置單元格格式

14、為數字時會設置單元格格式為2 2為小數的為小數的數值、貨幣格式或者是整數值。但在數值、貨幣格式或者是整數值。但在使用公式進行數據處理的過程中，經使用公式進行數據處理的過程中，經常會發(fā)現幾個單元格之和與合計單元常會發(fā)現幾個單元格之和與合計單元格顯示的數據之和不相等。格顯示的數據之和不相等。 因為因為excelexcel將數值按照將數值按照1515位計算精位計算精度帶入計算所致。如圖，在度帶入計算所致。如圖，在excelexcel選項選項中勾選中勾選“將精度設為所顯示的精度將精度設為所顯示的精度”，可以解決上述情況的發(fā)生，但這樣也降可以解決上述情況的發(fā)生，但這樣也降低了工作薄中所有工作表的計算精度

15、，低了工作薄中所有工作表的計算精度，但不影響其他工作薄。但不影響其他工作薄。 此外，也可以用此外，也可以用ROUNDROUND函數對數值函數對數值進行修約后再求和。進行修約后再求和。方法一：方法一： 選中包含公式的單元格，單擊選中包含公式的單元格，單擊“公式公式”選項卡中的選項卡中的“公式求值公式求值”按鈕，彈出按鈕，彈出“公式公式求值求值”對話框，然后多次單擊對話框，然后多次單擊“求值求值”按鈕，按鈕，“求值求值”域中將按照公式計算的順序逐步地域中將按照公式計算的順序逐步地顯示公式的計算過程。顯示公式的計算過程。方法二：方法二： 功能鍵除了可以重算工作薄外，功能鍵除了可以重算工作薄外，還可以

16、用于查看公式的運算結果。還可以用于查看公式的運算結果。 當一個函數公式比較長時，可以用當一個函數公式比較長時，可以用鍵對各個部分進行分段計算，以便鍵對各個部分進行分段計算，以便公式差錯和更正。在分段選擇時，注意公式差錯和更正。在分段選擇時，注意要包含一個完整的運算對象。要包含一個完整的運算對象。按按功能鍵切換功能鍵切換數據的篩選請使用若要查找?（問號）任何單個字符例如，sm?th 查找“smith”和“smyth”*（星號）任何字符數例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 問號、星號或波形符例如，“fy91?”將會查找“fy91?”通配符

17、通配符以下通配符可作為篩選以及查找和替換內容時的比較條件 :分類匯總的概述分類匯總是對數據清單進行數據分析的一種方法.Data/ Subtotal分類匯總1.確保要進行分類匯總的數據為下列格式：第一行的每一列都有標志，并且同一列中應包含相似的數據，在區(qū)域中沒有空行或空列。 2.單擊要分類匯總的列中的單元格。在上面的示例中，應單擊“運動”列（列 B）中的單元格。 3.單擊“升序排序” 或“降序排序”。 4.在“數據”菜單上，單擊“分類匯總”。 5.在“分類字段”框中，單擊要分類匯總的列。在上面示例中，應單擊“運動”列。 6.在“匯總方式”框中，單擊所需的用于計算分類匯總的匯總函數。 7.在“選定

18、匯總項”框中，選中包含了要進行分類匯總的數值的每一列的復選框。在上面的示例中，應選中“銷售”列。 分類匯總 要分類匯總的列 分類匯總步驟步驟:8.如果想在每個分類匯總后有一個自動分頁符，請選中“每組數據分頁”復選框。 9.如果希望分類匯總結果出現在分類匯總的行的上方，而不是在行的下方，請清除“匯總結果顯示在數據下方”復選框。 10.單擊“確定”。 11.注釋 可再次使用“分類匯總”命令來添加多個具有不同匯總函數的分類匯總。若要防止覆蓋已存在的分類匯總，請清除“替換當前分類匯總”復選框。分類匯總 要分類匯總的列 分類匯總刪除分類匯總刪除分類匯總時，Microsoft Excel 也刪除分級顯示以

19、及隨分類匯總一起插入列表中的所有分頁符。在含有分類匯總的列表中，單擊任一單元格。 在“數據”菜單上，單擊“分類匯總”。 單擊“全部刪除”。 分類匯總刪除分類匯總刪除分類匯總時，Microsoft Excel 也刪除分級顯示以及隨分類匯總一起插入列表中的所有分頁符。在含有分類匯總的列表中，單擊任一單元格。 在“數據”菜單上，單擊“分類匯總”。 單擊“全部刪除”。 分類匯總字符函數字符函數Left/Right: 截取字符串函數用途: 根據指定的字符數返回文本串中的第一個或前幾個字符。語法：Left(文本，返回的字符數) Right(文本，返回的字符數)參數：返回的字符數必須大于或等于0實例：返回客

20、戶姓名中的姓氏 A1單元格字符串“張三”,使用Left(A1,1) 得到結果“張”字符函數字符函數Left/Right:綜合應用返回身份證號中的“出生年份”信息例如：A1單元格有身份證號碼使用Right(Left(A1,10),4) 得到結果1982字符函數字符函數Mid: 截取字符串函數用途: Mid返回文本串中從指定位置開始的特定數目的字符，該數目可以由用戶指定.語法：Mid(文本串，開始位置，返回字符的個數)參數：開始位置是文本中要提取的第一個字符的位置，文本中第一個字符的開始位置為1，以此類推。例如：A1單元格有身份證號碼“35262419820

21、8226319”使用Mid(A1,7,4)得到結果”1982”字符函數字符函數LEN: 計算字符串長度用途: LEN返回文本的字符數 語法：LEN(文本)參數：此函數用于雙字節(jié)字符，且空格也將作為字符進行統(tǒng)計。例如：檢查身份證號碼是否輸入了18位。 A1單元格有身份證號碼使用LEN(A1)得到結果18類似的函數LENB，返回單字節(jié)字符。例如：LENB(“ABC”) 返回 3，LENB(“張三”)返回4字符函數字符函數TRIM: 刪除指定字符串多余的前導和后綴的空格用途: 刪除前導和后綴的空格 語法：TRIM(文本)參數：字符串例如：A1單元格有姓名“ 張

22、三 ”使用TRIM（A1）得到結果“張三”字符函數字符函數Replace: 替換指定位置處的任意文本用途: REPLACE使用其他文本串并根據所指定的字符數替換另一文本串中的部分文本。 語法：Replace(需要替換其部分字符的文本，被替換字符的起始位置，替換的字符個數，用于替換到原文本中去的字符)例如：A1單元格有公司名稱“廣東XXX有限公司”，將“廣東”替換成“廣東省”。使用Replace(A1,1,2,”廣東省”)得到結果”廣東省XXX有限公司”字符函數字符函數SUBSTITUTE: 替換指定文本用途: 在文字串中用新文本替代舊文本。如果需要在一個文字串中替換指定文本，可以使用SUBST

23、ITUTE；如果需要在某一字符串中替換指定位置處的任意文本，就應該使用函數REPLACE。 語法： SUBSTITUTE(需要替換其部分字符的文本，需被替換舊文本，用于替換的文本，第幾次出現的舊文本)參數：如果不輸入第四個參數將替換所有舊文本。例如：A1單元格有客戶編碼”G000G001”,將第二個”G”替換成”0”.使用SUBSTITUTE(A1,G,“0,2)得到結果”G0000001”使用SUBSTITUTE(A1,G,“0)得到結果”00000001”字符函數字符函數Replace、 SUBSTITUTE綜合應用案例說明：將下圖中凌亂的編碼進行整理使用函數：=REPLACE(SUBSTITUTE(A1, ,),2,1,M )字符函數字符函數Rept: 補位函數用途: 按照給定的次數重復顯示文本?？梢酝ㄟ^REPT函數對單元格進行重復填充。語法：Rept(需要重復顯示的文本，重復顯示的次數)參數：重復顯示的次數需要為正數。實例：Rept（”很好”,2）返回“很好很好”。字符函數字符函數Rept函數與LEN函數的綜合應用使用函數：=REPT(0,6-LEN(A1) & A1日期日期函數函數NOW/TODAY(): 返回當前