《EXCEL函數(shù)教學(xué)》PPT課件.ppt

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