常用的20個(gè)EXCEL函數(shù)的使用詳解

1、常用的常用的20個(gè)個(gè)EXCEL函數(shù)函數(shù)第第6章章 使用函數(shù)Excel函數(shù)的種類(lèi) 函數(shù)的基本語(yǔ)法 函數(shù)的調(diào)用 嵌套函數(shù)的使用 使用Excel的幫助來(lái)理解函數(shù) 常用函數(shù)介紹使用自定義函數(shù)Excel函數(shù)的種類(lèi)函數(shù)的種類(lèi) 財(cái)務(wù)函數(shù) 日期函數(shù) 時(shí)間函數(shù) 數(shù)學(xué)與三角函數(shù) 統(tǒng)計(jì)函數(shù) 數(shù)據(jù)庫(kù)管理函數(shù) 文本函數(shù) 信息類(lèi)函數(shù) 函數(shù)的基本語(yǔ)法函數(shù)的基本語(yǔ)法 函數(shù)的基本語(yǔ)法為： = 函數(shù)名(參數(shù)1,參數(shù)2,參數(shù)n)。 注意問(wèn)題： 函數(shù)名與其后的括號(hào)“（”之間不能有空格。 當(dāng)有多個(gè)參數(shù)時(shí)，參數(shù)之間要用逗號(hào)“，”分隔。 參數(shù)部分總長(zhǎng)度不能超過(guò)1024個(gè)字符。 參數(shù)可以是數(shù)值、文本、邏輯值、單元格引用，也可以是各種表達(dá)式或

2、函數(shù)。 函數(shù)中的逗號(hào)“，”、引號(hào)“”等都是半角字符，而不是全角字符。函數(shù)的調(diào)用函數(shù)的調(diào)用 直接在單元格中輸入函數(shù) 利用插入函數(shù)按鈕或命令插入函數(shù)嵌套函數(shù)的使用嵌套函數(shù)的使用 當(dāng)一個(gè)函數(shù)中的參數(shù)為另外一個(gè)函數(shù)時(shí)，就是在使用嵌套函數(shù) 。 利用插入函數(shù)按鈕或命令插入各個(gè)函數(shù)。使用使用Excel的幫助來(lái)理解函數(shù)的幫助來(lái)理解函數(shù) 直接在Excel右上角輸入函數(shù)名 利用Excel函數(shù)幫助任務(wù)窗格。常用函數(shù)介紹常用函數(shù)介紹求和函數(shù): SUM/SUMIF/SUMPRODUCT函數(shù)數(shù)學(xué)函數(shù) AVERGAE、 MAX、MIN、 ABS、 SQRT函數(shù) 計(jì)數(shù)函數(shù): COUNT、COUNTA、COUNTIF函數(shù)條件函

3、數(shù)和邏輯函數(shù): IF函數(shù)、AND函數(shù)、OR函數(shù)日期和時(shí)間函數(shù): DAY、DATE、DAYS360、TODAY函數(shù)等分析工具庫(kù)里的日期函數(shù)查找和引用函數(shù): VLOOKUP、 HLOOKUP、 MATCH、 INDEX、CHOOSE、OFFSET函數(shù)四舍五入函數(shù): ROUND、FLOOR和CEILING函數(shù) 取整函數(shù): INT函數(shù)信息函數(shù): ISBLANK、ISTEXT、ISNUMBER、ISERROR函數(shù) 文本函數(shù)：LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函數(shù)排序函數(shù): RANK、LARGE、SMALL函數(shù)數(shù)據(jù)庫(kù)函數(shù)：DGET、DMAX、DMIN、DSUM、DAVGEAGE函

4、數(shù)其他函數(shù)求和函數(shù)求和函數(shù): SUM/SUMIF/SUMPRODUCT函數(shù)函數(shù) SUM函數(shù) :無(wú)條件求和 = SUM (參數(shù)1,參數(shù)2,參數(shù)N) SUMIF函數(shù) :條件求和 =SUMIF(range,criteria,sum_range) SUMPRODUCT函數(shù) :在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和 = SUMPRODUCT(array1,array2,array3, .) 求和函數(shù)求和函數(shù): 應(yīng)用舉例應(yīng)用舉例 SUM函數(shù) SUMIF函數(shù) SUMPRODUCT函數(shù)求和函數(shù)求和函數(shù): 應(yīng)用舉例應(yīng)用舉例 隔欄加總計(jì)算隔欄加總計(jì)算 某預(yù)算表如圖所示，每個(gè)月分為“預(yù)算數(shù)”和“

5、實(shí)際數(shù)”，現(xiàn)要求計(jì)算年度“預(yù)算數(shù)”和“實(shí)際數(shù)”。這實(shí)際上就是隔欄加總問(wèn)題。如果一個(gè)一個(gè)單元格相加是很繁瑣的。 解決方法：巧妙應(yīng)用第3行的標(biāo)題。求和函數(shù)求和函數(shù): 應(yīng)用舉例應(yīng)用舉例 往下（或往右）累加匯總往下（或往右）累加匯總 往下（或往右）累加匯總是常見(jiàn)的實(shí)務(wù)問(wèn)題。 解決方法：使用SUM函數(shù)，但單元格引用的第一個(gè)地址應(yīng)為絕對(duì)引用，而第二個(gè)地址為相對(duì)引用。求和函數(shù)求和函數(shù): 應(yīng)用舉例應(yīng)用舉例 動(dòng)態(tài)匯總當(dāng)天數(shù)據(jù)動(dòng)態(tài)匯總當(dāng)天數(shù)據(jù) 工作表中存在每天的數(shù)據(jù)，要求動(dòng)態(tài)匯總計(jì)算當(dāng)前的數(shù)據(jù)。 可使用SUMIF函數(shù)。但要注意條件的寫(xiě)法。數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù) AVERGAE、 MAX、MIN、 ABS、 SQRT函數(shù)

6、函數(shù) 常用的數(shù)學(xué)函數(shù)有：AVERGAE函數(shù)：求平均值MAX：求最大值MIN：求最小值A(chǔ)BS：求絕對(duì)值SQRT：計(jì)算平方根平均值函數(shù)平均值函數(shù): AVERGAE函數(shù)函數(shù) AVERGAE函數(shù) :求N個(gè)數(shù)的平均值 = AVERGAE(參數(shù)1,參數(shù)2,參數(shù)N) 最大值與最小值函數(shù)最大值與最小值函數(shù): MAX、MIN函數(shù)函數(shù) MAX函數(shù) : 求N個(gè)數(shù)的最大值 = MAX(參數(shù)1,參數(shù)2,參數(shù)N) MIN函數(shù): 求N個(gè)數(shù)的最小值 = MIN(參數(shù)1,參數(shù)2,參數(shù)N) 絕對(duì)值函數(shù)絕對(duì)值函數(shù): ABS函數(shù)函數(shù) ABS函數(shù): 求某數(shù)的絕對(duì)值 = ABS(參數(shù)) 例如，ABS(-100)=100開(kāi)平方函數(shù)開(kāi)平方函

7、數(shù): SQRT函數(shù)函數(shù) SQRT函數(shù): 求某數(shù)的平方根 = SQRT(參數(shù)) 例如，SQRT(2)=1.4142135623731計(jì)數(shù)函數(shù)計(jì)數(shù)函數(shù): COUNT、COUNTA、COUNTIF函數(shù)函數(shù) COUNT函數(shù): 計(jì)算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目 = COUNT(參數(shù)1,參數(shù)2,參數(shù)N) COUNTA 函數(shù): 返回參數(shù)列表中非空值的單元格個(gè)數(shù) = COUNTA(參數(shù)1，參數(shù)2，參數(shù)N ) COUNTIF函數(shù): 計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目 = COUNTIF(range,criteria) 計(jì)數(shù)函數(shù)計(jì)數(shù)函數(shù):應(yīng)用舉例應(yīng)用舉例 COUNT函數(shù): COUNTA 函數(shù): COUNTIF

8、函數(shù):計(jì)數(shù)函數(shù)應(yīng)用計(jì)數(shù)函數(shù)應(yīng)用計(jì)算銷(xiāo)售業(yè)績(jī)計(jì)算銷(xiāo)售業(yè)績(jī)計(jì)數(shù)函數(shù)計(jì)數(shù)函數(shù):計(jì)算高于平均業(yè)績(jī)的人數(shù)計(jì)算高于平均業(yè)績(jī)的人數(shù)條件函數(shù)和邏輯函數(shù)條件函數(shù)和邏輯函數(shù): IF函數(shù)、函數(shù)、AND函數(shù)、函數(shù)、OR函數(shù)函數(shù) IF函數(shù): IF函數(shù)也稱(chēng)條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。 = IF(條件表達(dá)式，條件值為真時(shí)返回的值，條件值為假時(shí)返回的值) AND函數(shù)：AND函數(shù)表示邏輯與，當(dāng)所有條件都滿足時(shí)（即所有參數(shù)的邏輯值都為真時(shí)），AND函數(shù)返回TRUE，否則，只要有一個(gè)條件不滿足即返回FALSE。 = AND(條件1，條件2，條件N) OR函數(shù)：只要有一個(gè)條件滿足時(shí)，OR函數(shù)返回TRUE，只有當(dāng)所

9、有條件都不滿足時(shí)才返回FALSE。 = OR(條件1,條件2,條件N)說(shuō)明：這3個(gè)函數(shù)常常聯(lián)合使用。IF函數(shù)應(yīng)用舉例函數(shù)應(yīng)用舉例 之一之一計(jì)算獎(jiǎng)金計(jì)算獎(jiǎng)金IF函數(shù)應(yīng)用舉例函數(shù)應(yīng)用舉例 之二之二計(jì)算增長(zhǎng)率計(jì)算增長(zhǎng)率 計(jì)算增長(zhǎng)率當(dāng)被比較年份沒(méi)有數(shù)據(jù)時(shí)，會(huì)出現(xiàn)被除數(shù)為0的錯(cuò)誤?？梢岳肐F函數(shù)進(jìn)行處理。 如圖，去年沒(méi)有數(shù)據(jù)，而今年有數(shù)據(jù)，就顯示“新增項(xiàng)目”。 去年有數(shù)據(jù)，而今年沒(méi)有數(shù)據(jù)，就顯示“已經(jīng)停產(chǎn)”。IF函數(shù)應(yīng)用舉例函數(shù)應(yīng)用舉例 之三之三 自動(dòng)歸類(lèi)問(wèn)題 為了有效管理零用金，希望在輸入現(xiàn)金開(kāi)支數(shù)據(jù)后，該金額數(shù)據(jù)自動(dòng)依部門(mén)歸類(lèi)到適當(dāng)?shù)牧?，從而更加醒目地?biāo)示出來(lái)。 解決辦法：使用IF函數(shù) 如圖，先在

10、單元格F2輸入公式“=IF($D2=F$1,$C2, )”。 選定單元格區(qū)域F2:J2，按【F2】，使處于編輯狀態(tài)。 按【Ctrl+Enter】組合鍵注意單元格的引用方式。AND函數(shù)應(yīng)用舉例函數(shù)應(yīng)用舉例OR函數(shù)應(yīng)用舉例函數(shù)應(yīng)用舉例聯(lián)合使用聯(lián)合使用IF、AND和和OR函數(shù)函數(shù)計(jì)算獎(jiǎng)金計(jì)算獎(jiǎng)金日期函數(shù)日期函數(shù): DAY、DATE、DAYS360、TODAY函數(shù)函數(shù) DAY函數(shù): 返回以序列號(hào)表示的某日期的天數(shù)，用整數(shù) 131 表示。 = DAY(日期序列號(hào)) 例如 DAY(2006-12-22)=22 DATE函數(shù)：返回代表特定日期的序列號(hào)。 = DATE(年,月,日) 例如 DATE(2006,

11、12,23)= 2006-12-23 DAYS360函數(shù)：按照一年 360 天計(jì)（每個(gè)月以 30 天，一年共計(jì) 12 個(gè)月），返回兩個(gè)日期間相差的天數(shù)。 = DAYS360(開(kāi)始日期,截止日期,邏輯值) 例如 DAYS360(2000-1-15,2005-12-16)=2131天 TODAY函數(shù)：返回系統(tǒng)當(dāng)前的日期。日期函數(shù)應(yīng)用日期函數(shù)應(yīng)用(舉例）舉例） 設(shè)置動(dòng)態(tài)標(biāo)題：=今天是 &TEXT(TODAY(),yyyy年m月d日)=今天是 &TEXT(TODAY(),yyyy年m月d日)& &TEXT(WEEKDAY(TODAY(),aaaa) 設(shè)置上月標(biāo)題：=IF

12、(MONTH(TODAY()=1,12,MONTH(TODAY()-1)&月份收支情況“ 設(shè)置本月標(biāo)題=MONTH(TODAY()&月份收支情況“ 將日期轉(zhuǎn)換為星期 中文星期：=TEXT(WEEKDAY(A1),aaaa) 英文星期：=TEXT(WEEKDAY(A1),“dddd) 計(jì)算2年5個(gè)月20天后的日期=DATE(YEAR(A1)+2,MONTH(A1)+5,DAY(S1)+20) 計(jì)算實(shí)際歲數(shù)：=INT(YEARFRAC(出生日期,今天,1) =DATEIF(出生日期,今天,”Y”)日期函數(shù)應(yīng)用舉例日期函數(shù)應(yīng)用舉例確定具體日期確定具體日期日期函數(shù)應(yīng)用舉例日期函數(shù)應(yīng)用舉

13、例確定兩個(gè)日期間的天數(shù)確定兩個(gè)日期間的天數(shù)日期函數(shù)應(yīng)用舉例日期函數(shù)應(yīng)用舉例設(shè)置日期顯示格式設(shè)置日期顯示格式日期函數(shù)應(yīng)用舉例日期函數(shù)應(yīng)用舉例編制下周計(jì)劃表編制下周計(jì)劃表 編制下周計(jì)劃表 可以在本周的任何一天制作下周的計(jì)劃表 注意：任何一天的日期減去本身的星期數(shù)，就一定等于上一個(gè)星期天的日期。日期函數(shù)綜合應(yīng)用日期函數(shù)綜合應(yīng)用設(shè)計(jì)考勤表設(shè)計(jì)考勤表時(shí)間函數(shù)時(shí)間函數(shù) 輸入時(shí)間 輸入22:00與輸入10:00 PM是一樣的。 如何計(jì)算跨午夜零時(shí)的時(shí)間間隔？ 輸入：=B2+(B2A2)-A2 或者：=B2+IF(B2A2,1,0)-A2 思路：如果下班時(shí)間小于上班時(shí)間，就表示已經(jīng)過(guò)了1天，因此要加1。否則，

14、如果下班時(shí)間大于上班時(shí)間，就表示還在當(dāng)前，因此不需要加1天。分析工具庫(kù)里的日期函數(shù)分析工具庫(kù)里的日期函數(shù) EDATE函數(shù) EOMONTH函數(shù) WEEKNUM函數(shù) WORKDAY函數(shù) NETWORKDAYS函數(shù) 一個(gè)特殊的日期函數(shù)：DATEDIF 注意：在使用這些函數(shù)之前，必須加載分析工具庫(kù)，即：?jiǎn)螕簟竟ぞ摺俊炯虞d宏】命令，打開(kāi)【加載宏】對(duì)話框，選擇“分析工具庫(kù)”，如上圖。EDATE函數(shù)函數(shù) EDATE函數(shù)：返回指定日期往前或往后幾個(gè)月的日期。 例： 2007年4月12日之后3個(gè)月的日期：=EDATE(“2007-4-12”,3)，為2007-7-12 2007年4月12日之前3個(gè)月的日期：=E

15、DATE(“2007-4-12”,-3)，為2007-1-12 計(jì)算應(yīng)付賬款的到期日：如果一筆應(yīng)付款的到期日為自交易日起3個(gè)月的那一天，比如交易日為2006年11月30日，滿3個(gè)月后為2007-2-28：=EDATE(“2006-11-30”,3)，為2007-2-28EOMONTH函數(shù)函數(shù) EOMONTH函數(shù)：返回指定日期往前或往后幾個(gè)月的特定月份的月底日期。 例： 2007年4月12日之后3個(gè)月的月末日期：= EOMONTH(“2007-4-12”,3)，為2007-7-31 2007年4月12日之前5個(gè)月的月末日期：=EDATE(“2007-4-12”,-5)，為2006-11-30 計(jì)

16、算應(yīng)付賬款的到期日：如果一筆應(yīng)付款的到期日為自交易日起滿3個(gè)月后的下一個(gè)月的5號(hào)，比如交易日為2006年11月20日，滿3個(gè)月后下個(gè)月5號(hào)就是2007-3-5：=EOMONTH(2006-11-20,3-(DAY(A1)0.5)表示只有在完成率超過(guò)50% 時(shí)才利用公式(B2-0.49999)*10進(jìn)行計(jì)算。信息函數(shù)信息函數(shù): ISBLANK、ISTEXT、ISNUMBER、ISERROR函數(shù)函數(shù) ISBLANK函數(shù)：判斷單元格是否為空白單元格 ISTEXT函數(shù)：判斷單元格數(shù)據(jù)是否為文本 ISNUMBER函數(shù)：判斷單元格數(shù)據(jù)是否為數(shù)字 ISERROR函數(shù)：判斷單元格是否出現(xiàn)錯(cuò)誤文本函數(shù)文本函數(shù)L

17、EN、LEFT、RIGHT、TRIM、FIND、TEXT等函數(shù)等函數(shù) LEN函數(shù)：獲取字符串的長(zhǎng)度 LEFT函數(shù)：獲取字符串左邊指定個(gè)數(shù)的文本 RIGHT函數(shù)：獲取字符串右邊指定個(gè)數(shù)的文本 TRIM函數(shù)：取消字符串兩側(cè)的空格 FIND函數(shù)：查找某字符在字符串中第一次出現(xiàn)的位置 TEXT函數(shù)：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本 其他的文本函數(shù)文本函數(shù)應(yīng)用舉例文本函數(shù)應(yīng)用舉例 例1：LEN(ABCD)=4LEFT(ABCD,2)= ABRIGHT(ABCD,2)= CDTRIM( ABCD )= ABCDFIND(.,12345.65)=6TEXT(12345.6687,￥0.00)= ￥123

18、45.67 例2：從身份證號(hào)碼獲取出生日期和性別文本函數(shù)應(yīng)用舉例文本函數(shù)應(yīng)用舉例例3：將科目編碼與科目名稱(chēng)分離（假設(shè)科目編碼與科目名稱(chēng)之間有一個(gè)空格）問(wèn)題：如果科目編碼與科目名稱(chēng)之間沒(méi)有空格，該怎么辦呢？可使用LEN函數(shù)和LENB函數(shù)：文本函數(shù)應(yīng)用舉例文本函數(shù)應(yīng)用舉例例4：編制簡(jiǎn)單的統(tǒng)計(jì)表 選取單元格C2。 輸入“ =REPT( ”，（不要按回車(chē)鍵，還沒(méi)有完成輸入） 單擊【插入】【特殊符號(hào)】命令，插入一個(gè)黑正方形。 接著輸入“ ,B2*50) ” 將單元格C2向下填充復(fù)制。 最后將C列的各個(gè)單元格的字體顏色分別設(shè)置為不同的顏色排序函數(shù)排序函數(shù): RANK、LARGE、SMALL函數(shù)函數(shù) RAN

19、K函數(shù)：返回一個(gè)數(shù)字在數(shù)字列表中的排位。 LARGE函數(shù)：返回?cái)?shù)據(jù)集中第 k 個(gè)最大值。例如，可以使用函數(shù) LARGE 得到第一名、第二名或第三名的得分。 SMALL函數(shù)：返回?cái)?shù)據(jù)集中第 k 個(gè)最小值。排序函數(shù)應(yīng)用舉例排序函數(shù)應(yīng)用舉例數(shù)據(jù)庫(kù)函數(shù)：數(shù)據(jù)庫(kù)函數(shù)：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數(shù)函數(shù)Microsoft Excel提供了一些用于對(duì)存儲(chǔ)在列表或數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行分析的函數(shù)，這些函數(shù)統(tǒng)稱(chēng)為數(shù)據(jù)庫(kù)函數(shù)（Dfunctions）。這些函數(shù)的語(yǔ)法結(jié)構(gòu)基本相同，即：Dfunctions (database,field,criteria)函數(shù)的三個(gè)參數(shù)database、fi

20、eld和criteria的含義如下：database：構(gòu)成列表或數(shù)據(jù)庫(kù)的單元格區(qū)域。數(shù)據(jù)庫(kù)是包含一組相關(guān)數(shù)據(jù)的列表，其中包含相關(guān)信息的行為記錄，而包含數(shù)據(jù)的列為字段。列表的第一行包含著每一列的標(biāo)志項(xiàng)。field：指定函數(shù)所使用的數(shù)據(jù)列。列表中的數(shù)據(jù)列必須在第一行具有標(biāo)志項(xiàng)。Field可以是文本，即兩端帶引號(hào)的標(biāo)志項(xiàng)，如“使用年數(shù)”或“產(chǎn)量”；此外，F(xiàn)ield也可以是代表列表中數(shù)據(jù)列位置的數(shù)字：1表示第一列，2表示第二列，等等。criteria：為一組包含給定條件的單元格區(qū)域?？梢詾閰?shù)criteria指定任意區(qū)域，只要它至少包含一個(gè)列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格。在財(cái)務(wù)管理中，常用的數(shù)據(jù)庫(kù)函數(shù)有DSUM函數(shù)、DMAX函數(shù)、DMIN函數(shù)、DGET函數(shù)等。復(fù)合條件的語(yǔ)法復(fù)合條件的語(yǔ)法同一行的構(gòu)成了“與”關(guān)系條件。查找薪金在5000至8000元之間的紀(jì)錄。同一列構(gòu)成了“或”關(guān)系條件。 查找部門(mén)為銷(xiāo)售部和辦公室的記錄。不同列、同行構(gòu)成了不同字段的“與”關(guān)系。 查找男性且為經(jīng)理的紀(jì)錄。不同列、不同行構(gòu)成了不同字段的“或”關(guān)系條件。