《EXCEL函數教學》PPT課件.ppt

EXCEL函數應用 管理部人力資源組 2008年9月25日 課程大綱 u課堂講解篇:函數功能說明與案例講解( 共20個) u課后自學篇:基礎函數功能說明(25個) EXCEL函數教學目的 通過功能說明與案例演示,了解函數的應用范 圍與功效,激發(fā)后續(xù)學習函數的興趣 掌握函數使用的方法與技巧 充分發(fā)揮Excel的強大數據處理功能，提升工 作效率 函數應用初步演示 n利用函數在excel中制作工資條 Go EXCEL函數的結構 Excel 函數即是預先定義，執(zhí)行計算、分析等處理數 據任務的特殊公式。 Excel 函數結構: 也有一些函數 是沒有參數的, 如ROW() 左右括號成 對出現 單 一 結 構 嵌 套 結 構 參數與參數之間 使用半角逗號進 行分隔 函數參數常用符號或表示方法 n函數公式中的文本必須用半角引號，如：東南汽 車;而非直接輸入 東南汽車或“東南汽車” n連接符: =（大于等于）;82000,(A3-2000)*40%-10375,IF(A362000,(A3-2000)*35%- 6375,IF(A342000,(A3-2000)*30%-3375,IF(A322000,(A3-2000)*25%- 1375,IF(A37000,(A3-2000)*20%-375,IF(A34000,(A3-2000)*15%- 125,IF(A32500,(A3-2000)*10%-25,IF(A32000,(A3-2000)*5%,0) Go Sumif:條件求和函數 用途：根據指定條件對若干單元格、區(qū)域或引用求 和。 語法：SUMIF(條件區(qū)域，條件，需求和的區(qū)域) 參數：條件是由數字、邏輯表達式等組成的判定條 件。 Sumif案例 Go 請統(tǒng)計人資組同仁的工資總額: Sumif(A:A, 人資組,C:C) Countif：條件計數函數。 用途：計算區(qū)域中滿足給定條件的單元格的 個數。 語法：COUNTIF(統(tǒng)計區(qū)域，條件) 參數：“統(tǒng)計區(qū)域”為需要計算其中滿足條 件的單元格數目的單元格區(qū)域。“條件” 為 確定哪些單元格將被計算在內的條件，其形 式可以為數字、表達式或文本。 Countif案例 Go 以上述樣表為據,完成以下案例: n例1：依“師員”字段，統(tǒng)計師員級的人數 countif(C:C,師)；countif(C:C,員) n例2:統(tǒng)計全公司年資大於等於3年的人數及大于等于3年小于5年 的人數 countif(I:I, =3)；countif(I:I, =3)- countif(I:I, =5 ) n例3:檢測到職編號是否有重復值 countif(A:A, A2) Counta 用途：返回參數組中非空值的數目。利用函數 COUNTA 可以計算數組或單元格區(qū)域中數據項的個 數。 語法：COUNTA(單元格區(qū)域1,單元格區(qū)域2) 說明：參數的個數為130 個。 Counta的思考案例 n例1：如果A1=6.28、A2=3.74，其余單元 格為空，則公式“=COUNTA(A1:A7)” 的計算結果等于?。 n例2：統(tǒng)計C列的非空白單元格的表示方 法為：？; 若整張EXCEL表的每個單元 格都有數據,前述函數公式的結果為? Offset n用途：以指定的引用為參照系，通過給定偏移量得到新的引 用。返回的引用可以是一個單元格或單元格區(qū)域（并不返回 值）。 n語法：OFFSET(作為偏移量參照系的引用位置，上下偏移的 行數，左右偏移的列數，height，width)。 n參數： 作為偏移量參照系的引用位置：它必須是單元格或相連單元 格區(qū)域的引用； 上(下)偏移的行數：正數代表在起始引用下邊;負數代表在起 始引用的上邊； 0代表沒有上（下）偏移。 左(右)偏移的列數:正數代表在起始引用右邊;負數代表在起始 引用的左邊；0代表沒有左（右）偏移。 Height ：是要返回的引用區(qū)域的總行數（必須為正數） Width ：是要返回的引用區(qū)域的總列數（必須為正數）。 Offset的思考題 n問題一： OFFSET(B1,2,1,4,1)定位到哪幾 個單元格？ n問題二： 以A1單元格為參照，要定位 到紅線區(qū)域如何寫函數： =OFFSET(A1,6,1,2,2) Counta、offset的綜合應用案例 Go 要求： 當記錄增加時，公式自動統(tǒng)計總銷量（假設銷量存放600數值的單元 格為C3）： =SUM(OFFSET(C3,0,0,COUNTA(C:C)-1,1)或 =SUM(OFFSET(C2,1,0,COUNTA(C:C)-1,1) 注：以上公式只能正確計算不間斷的連續(xù)數據,如果表格中銷量的數據有 空白單元格,那么動態(tài)名稱的引用位置將發(fā)生錯誤 COLUMN 用途：返回給定引用的列標。 語法：COLUMN(單元格引用)。 參數：“單元格引用”為需要得到其列標的 單元格，如果省略，則假定函數COLUMN 是 對所在單元格的引用。 實例：公式“=COLUMN(A3)”返回1， =COLUMN(D5)返回?。 Row 用途：返回給定引用的行號。 語法：ROW(單元格引用)?！皢卧褚谩睘樾枰?得到其行號的單元格或單元格區(qū)域。 實例:利用row建立序號: Go MOD：取余 用途：返回兩數相除的余數，其結果的正負 號與除數相同。 語法：MOD(被除數，除數) 參數：除數不能為零。 Mod函數的案例 n實例1：公式“=MOD(14，4)”返回?； “=MOD(-5，-2)”返回？。 n實例2:如何利用MOD函數，將整張EXCEL表 的偶數行都標上底色 Go Vlookup 返回表格或數組當前行中指定列處的數值。 語法：VLOOKUP(索引值，數據區(qū)域，列序號，查找方式) 參數： 索引值為需要在數據表第一列中查找的數值，它可以是數值 、引用或文字串。 數據區(qū)域為需要在其中查找數據的數據表，可以使用對區(qū)域 或區(qū)域名稱的引用。 列序號 指數據區(qū)域 中待返回的匹配值的列序號。它等于1 時，返回數據區(qū)域 第一列中的數值；它等于 2時，返回數據 區(qū)域第二列中的數值，以此類推。 查找方式為一邏輯值,指明函數VLOOKUP 返回時是精確匹配 還是近似匹配。如果為1或省略，則返回近似匹配值，也就 是說，如果找不到精確匹配值，則返回小于索引值 的最大數 值；如果為0，函數VLOOKUP 將返回精確匹配值。如果找 不到，則返回錯誤值#N/A。 Vlookup案例 案例一:利用vlookup 抓取另一張表中的資料。 Go 案例二(請參考鏈接之案例,課后自學)：用 vlookup計算所得稅,解決IF只能嵌套七層的局 限。 Go match 用途：返回在指定方式下與指定數值匹配的數組中 元素的相應位置(不是具體的單元格)。 語法：MATCH(A，B，C)。 參數： A:為需要在數據表中查找的數值或單元格引用。 B:是可能包含所要查找的數值的連續(xù)單元格區(qū)域。 C:它說明Excel 如何在B 中查找A。C 的常用值為0， 表示函數MATCH 查找等于A 的第一個數值。 注意：MATCH 函數返回B 中目標值的位置，而不 是數值本身。 Match函數的案例Go =MATCH(11，A1:A9，0)返回的值是? =match(420,A3:E3,0)返回的值是? Vlookup與match函數的綜合運用Go 利用VLOOKUP和MATCH函數查找出相應月份和產 品的銷售額。 Left/Right：截取字符串函數 用途：根據指定的字符數返回文本串中的第 一個或前幾個字符。此函數用于雙字節(jié)字符 。 語法：LEFT(文本, 返回的字符數) Right(文本, 返回的字符數) 參數：返回的字符數必須大于或等于0。 實例：如果A1=電腦愛好者，則LEFT(A1，2) 返回? ;Right(A1,3)返回? Left與right的綜合運用 Go 如何利用Left及Right從身份證號中提取出生年 份。 提示：假如A2存放身份證號，提取年份的做法：=RIGHT(LEFT(A2,10),4) Mid：截取字符串函數 用途：MID 返回文本串中從指定位置開始的特定數 目的字符，該數目由用戶指定。 語法：MID(文本串,開始位置,返回字符的個數) 參數：開始位置是文本中要提取的第一個字符的位 置，文本中第一個字符的開始位置為1，以此類推。 實例1：如果a1=東南(福建)汽車工業(yè)有限公司，則 公式“=MID(A1，4，2)”返回“福建” 。 案例:利用MID從身份證號中提取出生年份。 Go Replace：替換指定位置處的任意文本 用途：REPLACE 使用其他文本串并根據所指定的 字符數替換另一文本串中的部分文本。 語法：REPLACE(需替換其部分字符的文本，被替 換字符的起始位置，替換的字符個數，用于替換到 原文本中去的字符) 。 思考：EXCEL本身就有替換功能，為什么要用 replace函數？-解決替換字符多變的問題 Replace的案例 案例一：請分別說出下列兩個函數公式的返回值： Go LEN：計算字符長度。 用途：LEN 返回文本串的字符數。 語法：LEN(text) 。 參數：Text 待要查找其長度的文本。 注意：此函數用于雙字節(jié)字符，且空格也將 作為字符進行統(tǒng)計。 實例：如果A1=電腦愛好者，則公式 “=LEN(A1)”返回？ If、Len及mid函數綜合運用 Go 如果A列里CM后沒有“00”的話，就把“00“加到CM后,但是如果CM后 有”00“話，就不加了（產生的效果如B列所示），怎么做？ 公式提示：=IF(MID(A1,3,2)2, 0)”返回FALSE;公式 “=AND(32, 86)”返回True OR 用途：所有參數中的任意一個邏輯值為真時 即返回TRUE(真)。 語法：OR(邏輯表達式1 ，邏輯表達式2，.) 參數：同and的參數。 實例：如果A1=6、A2=8，則公式 “=OR(A1+A2A2，A1=A2)”返回TRUE； 而公式“=OR(A1A2，A1=A2)”返回FALSE 。 ABS：求絕對值 用途：返回某一參數的絕對值。 語法：ABS(number) 參數：number 是需要計算其絕對值的一個實 數。 實例：如果A1=-16，則公式“=ABS(A1)”返 回16。 ROUND：對數值進取四舍五入。 用途：按指定位數四舍五入某個數字。 語法：ROUND(需四舍五入的數字，保留的小數位) 注意：如果保留的小數位大于0，則四舍五入到指定 的小數位；如果等于0，則四舍五入到最接近的整數 ；如果小于0，則在小數點左側按指定位數四舍五入 。 實例：如果A1=65.25，則公式“=ROUND(A1，1)” 返回65.3；=ROUND(82.149，2)返回82.15； =ROUND(21.5，-1)返回20；Round(A1,0)返回65。 Countblank 用途：計算某個單元格區(qū)域中空白單元格的 數目。 語法：COUNTBLANK(range) 參數：Range 為需要計算其中空白單元格數目 的區(qū)域。 Countblank的思考案例 1、在一個空白的excel表中： Countblank(1:1)=?(相當于EXCEL的列數) Countblank(a:a)=?(相當于EXCEL的行數) 2、在一個非空白的excel表中： Counta(a:a)+countblank(a:a)=? INT:無條件取整 用途：將任意實數向下取整為最接近的整數 。 語法：INT(實數) 參數：Number 為需要處理的任意一個實數。 實例：如果A1=16.24、A2=-28.389，則公式 “=INT(A1)”返回16，=INT(A2)返回-29。 Trunc 用途：將數字的小數部分依需截去（不作四舍五入 ），返回實數。 語法：TRUNC(需要截去小數部分的數字，保留小 數的位數) 注意：TRUNC 函數可以按需要截取數字的小數部 分，而INT函數則將數字向下舍入到最接近的整數 。INT 和TRUNC 函數在處理負數時有所不同： TRUNC(-4.3)返回-4，而INT(-4.3)返回-5。 實例：如果A1=78.652，則公式“=TRUNC(A1， 1)”返回78.6，=TRUNC(A1，2)返回78.65， =TRUNC(-8.963，2)返回8.96。 EXACT:比較兩個字符串是否相同 用途：測試兩個字符串是否完全相同。如果它們完 全相同，則返回TRUE；否則返回FALSE。EXACT 函數能區(qū)分大小寫，但忽略格式上的差異。 語法：EXACT(text1，text2)。 參數：Text1 是待比較的第一個字符串，Text2 是待 比較的第二個字符串。 實例：如果A1=得利卡、A2=富利卡、A3=戈藍，則 公式“=EXACT(A1，A2)”返回FALSE， =EXACT(A1，A3)返回FALSE，=EXACT(“word“， “word“)返回TRUE。 ISEVEN 用途：測試參數的奇偶性，如果參數為偶數返回 TRUE，否則返回FALSE。 語法：ISEVEN(number),Number 待測試的數值。如 果參數值不是整數，則自動截去小數部分取整。 注意：該函數必須加載“分析工具庫”方能使用。 如果參數number 不是數值，ISEVEN 函數返回錯誤 值#VALUE!。 實例：公式“=ISEVEN(11)返回FALSE”， =ISEVEN(6)返回TRUE。 ISERROR n用途：它們可以檢驗是否發(fā)生錯誤值。發(fā)生錯誤返回TRUE ，否則返回FALSE。 n語法：ISERROR(value) n參數：Value 是需要進行檢驗的參數。 n實例：用VLOOKUP函數找不到相應的值時，顯示空值。 =IF(ISSEROR(vlookup(A2,A2:G5,2,0), , vlookup(A2,A2:G5,2,0) CONCATENATE n用途：將若干文字串合并到一個文字串中， 其功能與“&“運算符相同。 n語法：CONCATENATE(text1，text2，.) n參數：Text1，text2，.為1 到30 個將要合并 成單個文本的文本項，這些文本項可以是文 字串、數字或對單個單元格的引用。 n實例：如果A1=98、A2=千米，則公式 “=CONCATENATE(A1，A2)”返回“98 千 米”，與公式“=A1&A2”等價。 YEARMONTHDAY n用途：返回某日期的年份/月份/日期。 n語法：YEAR/MONTH/DAY(日期值) n實例： 假設A2單元格存放的日期為2008-8-20 u 公式“=YEAR(A2)返回2008” u 公式“ =MONTH(A2)返回8” u 公式“ =DAY(A2)返回20” Date 用途：返回代表特定日期的序列號。 語法：DATE(year，month，day) 參數：year 為一到四位,代表年份。Month 代 表每年中月份的數字。Day 代表在該月份中 第幾天的數字。 實例：將身份證中提取出的年月日直接轉化 成出生年月日: 假若A=350102197405253613,則 date(mid(a,7,4),mid(a,11,2),mid(a,13,2)返回1974 -5-25 Today 用途：返回系統(tǒng)當前日期的序列號。 參數：無 語法：TODAY() 實例1：公式“=TODAY()”返回執(zhí)行公式時 的系統(tǒng)時間。 實例2：依出生日期計算年齡的示例 表示方法：（today()-出生日期）/365 weekday 用途：返回某日期的星期數。在默認情況下，它的 值為1(星期天)到7(星期六)之間的一個整數。 語法：WEEKDAY(日期，返回類型) 參數：“返回類型 ”為確定返回值類型的數字，數 字1 或省略則1 至7 代表星期天到數星期六，數字2 則1 至7 代表星期一到星期天（推薦使用），數字3 則0 至6 代表星期一到星期天。 實例：公式“=WEEKDAY(”2008/8/20“，2)”返 回3(星期三)，=WEEKDAY(“2008/8/20”，3)返回 2(星期三)。 EDATE 用途：返回指定日期之前或之后指定月份的 日期序列號。 語法：EDATE(開始日期，在開始日期之前或 之后的月份數) 參數：“在開始日期之前或之后的月份數” 未來日期用正數表示，過去日期用負數表示 。 實例：公式“=EDATE(“2008/8/20“，2)”返 回39741 即2008-10-20 FREQUENCY n用途：以一列垂直數組返回某個區(qū)域中數據的頻率分布 。它可以計算出在給定的值域和接收區(qū)間內，每個區(qū)間 包含的數據個數。 n語法：FREQUENCY(數據源，分段點) n注意該函數的特性： 1、忽略文本或空白單元格：數據源區(qū)域出現文本或空 格，只統(tǒng)計數據，其他沒有影響。 2、支持數據亂序：支持數據源及分段點的亂序。 3、對于每一分段點，統(tǒng)計小于等于此分段點，大于上 一分段點的頻數，最后一格始終統(tǒng)計大于最大分段點的 頻數。 4、要以數組形式輸入公式。(在公式的編輯欄同時按下 ctrl+alt+enter) FREQUENCY的案例 Go 統(tǒng)計某科成績不同分數段的人數： n思考：用FREQUENCY與用EXCEL原有的數據透視表功能有何不同？