常用EXCEL函數(shù)使用詳解課件

1、(最新整理)常用EXCEL函數(shù)使用詳解2021/7/261常用EXCEL函數(shù)講解2018年9月18日2021/7/262 新手、初級用戶、中級用戶、高級用戶和專家五個層次 新手：學習者需要大致了解到Excel的基本操作方法和常用功能，諸如輸入數(shù)據(jù)，查找替換，設置單元格格式，排序、匯總、篩選和保存工作簿。 初級用戶：可以開始在工作中運用Excel，比如建立一個簡單的表格，畫一張簡單的圖表，會一些基本簡單的函數(shù)，如SUM、IF 等。中級用戶三個標志：一是理解并熟練使用各個Excel菜單命令二是熟練使用數(shù)據(jù)透視表三是至少掌握20個常用函數(shù)以及函數(shù)的嵌套運用，掌握的基本函數(shù)有SUM函數(shù)、IF函數(shù)、VL

2、OOKUP函數(shù)、INDEX函數(shù)、MATCH函數(shù)、OFFSET函數(shù)、TEXT函數(shù)等等 2021/7/263高級用戶兩個標志：一是熟練運用數(shù)組公式，也就是那種用花括號包圍起來，必須用組合鍵才能完成錄入的公式。Excel專家： 從Excel的功能細分來看，精通全部的人想必寥寥無幾。Excel是應用性太強的軟件，意味著一個沒有任何工作經(jīng)驗的普通學生是很難成為Excel專家的。所以Excel專家也必定是某個或多個行業(yè)的專家，他們都擁有豐富的行業(yè)知識和經(jīng)驗。高超的Excel技術配合行業(yè)經(jīng)驗來共同應用，才有可能把Excel發(fā)揮到極致，所以，如果希望成為Excel專家，就不能只單單學習Excel了 。2021

3、/7/264Excel函數(shù)的種類財務函數(shù)日期函數(shù)時間函數(shù)數(shù)學與三角函數(shù)統(tǒng)計函數(shù)數(shù)據(jù)庫管理函數(shù)文本函數(shù)信息類函數(shù) 2021/7/265函數(shù)的基本語法函數(shù)的基本語法為： = 函數(shù)名(參數(shù)1,參數(shù)2,參數(shù)n)。注意問題：函數(shù)名與其后的括號“（”之間不能有空格。當有多個參數(shù)時，參數(shù)之間要用逗號“，”分隔。參數(shù)部分總長度不能超過1024個字符。參數(shù)可以是數(shù)值、文本、邏輯值、單元格引用，也可以是各種表達式或函數(shù)。函數(shù)中的逗號“，”、引號“”等都是半角字符，而不是全角字符。2021/7/266常用函數(shù)介紹求和函數(shù): SUM/SUMIF/SUMPRODUCT函數(shù)數(shù)學函數(shù) AVERGAE、 MAX、MIN、 A

4、BS、 SQRT函數(shù) 計數(shù)函數(shù): COUNT、COUNTA、COUNTIF函數(shù)條件函數(shù)和邏輯函數(shù): IF函數(shù)、AND函數(shù)、OR函數(shù)日期和時間函數(shù): DAY、DATE、DAYS360、TODAY函數(shù)等分析工具庫里的日期函數(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、

5、SMALL函數(shù)數(shù)據(jù)庫函數(shù)：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數(shù)其他函數(shù)2021/7/267求和函數(shù) SUM/SUMIF/SUMPRODUCT函數(shù)SUM函數(shù) :無條件求和 = SUM (參數(shù)1,參數(shù)2,參數(shù)N) SUMIF函數(shù) :條件求和 =SUMIF(range,criteria,sum_range) SUMPRODUCT函數(shù) :在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，并返回乘積之和 = SUMPRODUCT(array1,array2,array3, .) 2021/7/268求和函數(shù): 應用舉例SUM函數(shù) SUMIF函數(shù)SUMPRODUCT函數(shù)2021/7/26

6、9求和函數(shù): 應用舉例 隔欄加總計算某預算表如圖所示，每個月分為“預算數(shù)”和“實際數(shù)”，現(xiàn)要求計算年度“預算數(shù)”和“實際數(shù)”。這實際上就是隔欄加總問題。如果一個一個單元格相加是很繁瑣的。解決方法：巧妙應用第3行的標題。2021/7/2610求和函數(shù): 應用舉例 往下（或往右）累加匯總往下（或往右）累加匯總是常見的實務問題。解決方法：使用SUM函數(shù)，但單元格引用的第一個地址應為絕對引用，而第二個地址為相對引用。2021/7/2611求和函數(shù): 應用舉例 動態(tài)匯總當天數(shù)據(jù)工作表中存在每天的數(shù)據(jù)，要求動態(tài)匯總計算當前的數(shù)據(jù)?？墒褂肧UMIF函數(shù)。但要注意條件的寫法。2021/7/2612數(shù)學函數(shù) A

7、VERGAE、 MAX、MIN、 ABS、 SQRT函數(shù) 常用的數(shù)學函數(shù)有：AVERGAE函數(shù)：求平均值MAX：求最大值MIN：求最小值ABS：求絕對值SQRT：計算平方根2021/7/2613平均值函數(shù): AVERGAE函數(shù)AVERGAE函數(shù) :求N個數(shù)的平均值 = AVERGAE(參數(shù)1,參數(shù)2,參數(shù)N) 2021/7/2614最大值與最小值函數(shù): MAX、MIN函數(shù) MAX函數(shù) : 求N個數(shù)的最大值 = MAX(參數(shù)1,參數(shù)2,參數(shù)N) MIN函數(shù): 求N個數(shù)的最小值 = MIN(參數(shù)1,參數(shù)2,參數(shù)N) 2021/7/2615絕對值函數(shù): ABS函數(shù)ABS函數(shù): 求某數(shù)的絕對值 = A

8、BS(參數(shù)) 例如，ABS(-100)=1002021/7/2616開平方函數(shù): SQRT函數(shù) SQRT函數(shù): 求某數(shù)的平方根 = SQRT(參數(shù)) 例如，SQRT(2)=1.41421356237312021/7/2617計數(shù)函數(shù): COUNT、COUNTA、COUNTIF函數(shù)COUNT函數(shù): 計算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目 = COUNT(參數(shù)1,參數(shù)2,參數(shù)N) COUNTA 函數(shù): 返回參數(shù)列表中非空值的單元格個數(shù) = COUNTA(參數(shù)1，參數(shù)2，參數(shù)N ) COUNTIF函數(shù): 計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目 = COUNTIF(range,criteria) 2021/

9、7/2618計數(shù)函數(shù):應用舉例COUNT函數(shù):COUNTA 函數(shù):COUNTIF函數(shù):2021/7/2619計數(shù)函數(shù)應用計算銷售業(yè)績2021/7/2620計數(shù)函數(shù):計算高于平均業(yè)績的人數(shù)2021/7/2621條件函數(shù)和邏輯函數(shù): IF函數(shù)、AND函數(shù)、OR函數(shù)IF函數(shù): IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。 = IF(條件表達式，條件值為真時返回的值，條件值為假時返回的值) AND函數(shù)：AND函數(shù)表示邏輯與，當所有條件都滿足時（即所有參數(shù)的邏輯值都為真時），AND函數(shù)返回TRUE，否則，只要有一個條件不滿足即返回FALSE。 = AND(條件1，條件2，條件N)OR函數(shù)

10、：只要有一個條件滿足時，OR函數(shù)返回TRUE，只有當所有條件都不滿足時才返回FALSE。 = OR(條件1,條件2,條件N)說明：這3個函數(shù)常常聯(lián)合使用。2021/7/2622IF函數(shù)應用舉例 之一計算獎金2021/7/2623IF函數(shù)應用舉例 之二計算增長率計算增長率當被比較年份沒有數(shù)據(jù)時，會出現(xiàn)被除數(shù)為0的錯誤。可以利用IF函數(shù)進行處理。如圖，去年沒有數(shù)據(jù)，而今年有數(shù)據(jù)，就顯示“新增項目”。去年有數(shù)據(jù)，而今年沒有數(shù)據(jù)，就顯示“已經(jīng)停產(chǎn)”。2021/7/2624IF函數(shù)應用舉例 之三自動歸類問題 為了有效管理零用金，希望在輸入現(xiàn)金開支數(shù)據(jù)后，該金額數(shù)據(jù)自動依部門歸類到適當?shù)牧?，從而更加醒目?/p>

11、標示出來。解決辦法：使用IF函數(shù)如圖，先在單元格F2輸入公式“=IF($D2=F$1,$C2, )”。選定單元格區(qū)域F2:J2，按【F2】，使處于編輯狀態(tài)。按【Ctrl+Enter】組合鍵注意單元格的引用方式。2021/7/2625AND函數(shù)應用舉例2021/7/2626OR函數(shù)應用舉例2021/7/2627聯(lián)合使用IF、AND和OR函數(shù)計算獎金2021/7/2628日期函數(shù): DAY、DATE、DAYS360、TODAY函數(shù)DAY函數(shù): 返回以序列號表示的某日期的天數(shù)，用整數(shù) 131 表示。 = DAY(日期序列號) 例如 DAY(2006-12-22)=22DATE函數(shù)：返回代表特定日期的

12、序列號。 = DATE(年,月,日) 例如 DATE(2006,12,23)= 2006-12-23DAYS360函數(shù)：按照一年 360 天計（每個月以 30 天，一年共計 12 個月），返回兩個日期間相差的天數(shù)。 = DAYS360(開始日期,截止日期,邏輯值) 例如 DAYS360(2000-1-15,2005-12-16)=2131天TODAY函數(shù)：返回系統(tǒng)當前的日期。2021/7/2629日期函數(shù)應用(舉例）設置動態(tài)標題：=今天是 &TEXT(TODAY(),yyyy年m月d日)=今天是 &TEXT(TODAY(),yyyy年m月d日)& &TEXT(WEEKDAY(TODAY(),a

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

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

15、此不需要加1天。2021/7/2636分析工具庫里的日期函數(shù)EDATE函數(shù)EOMONTH函數(shù)WEEKNUM函數(shù)WORKDAY函數(shù)NETWORKDAYS函數(shù)一個特殊的日期函數(shù)：DATEDIF注意：在使用這些函數(shù)之前，必須加載分析工具庫，即：單擊【工具】【加載宏】命令，打開【加載宏】對話框，選擇“分析工具庫”，如上圖。2021/7/2637EDATE函數(shù)EDATE函數(shù)：返回指定日期往前或往后幾個月的日期。例：2007年4月12日之后3個月的日期：=EDATE(“2007-4-12”,3)，為2007-7-122007年4月12日之前3個月的日期：=EDATE(“2007-4-12”,-3)，為20

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

17、為自交易日起滿3個月后的下一個月的5號，比如交易日為2006年11月20日，滿3個月后下個月5號就是2007-3-5：=EOMONTH(2006-11-20,3-(DAY(A1)0.5)表示只有在完成率超過50% 時才利用公式(B2-0.49999)*10進行計算。2021/7/2665信息函數(shù): ISBLANK、ISTEXT、ISNUMBER、ISERROR函數(shù) ISBLANK函數(shù)：判斷單元格是否為空白單元格ISTEXT函數(shù)：判斷單元格數(shù)據(jù)是否為文本ISNUMBER函數(shù)：判斷單元格數(shù)據(jù)是否為數(shù)字ISERROR函數(shù)：判斷單元格是否出現(xiàn)錯誤2021/7/2666文本函數(shù)LEN、LEFT、RIGH

18、T、TRIM、FIND、TEXT等函數(shù)LEN函數(shù)：獲取字符串的長度LEFT函數(shù)：獲取字符串左邊指定個數(shù)的文本RIGHT函數(shù)：獲取字符串右邊指定個數(shù)的文本TRIM函數(shù)：取消字符串兩側(cè)的空格FIND函數(shù)：查找某字符在字符串中第一次出現(xiàn)的位置TEXT函數(shù)：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本 其他的文本函數(shù)2021/7/2667文本函數(shù)應用舉例例1：LEN(ABCD)=4LEFT(ABCD,2)= ABRIGHT(ABCD,2)= CDTRIM( ABCD )= ABCDFIND(.,12345.65)=6TEXT(12345.6687,￥0.00)= ￥12345.67例2：從身份證號碼獲取出生日

19、期和性別2021/7/2668文本函數(shù)應用舉例例3：將科目編碼與科目名稱分離（假設科目編碼與科目名稱之間有一個空格）問題：如果科目編碼與科目名稱之間沒有空格，該怎么辦呢？可使用LEN函數(shù)和LENB函數(shù)：2021/7/2669文本函數(shù)應用舉例例4：編制簡單的統(tǒng)計表選取單元格C2。輸入“ =REPT( ”，（不要按回車鍵，還沒有完成輸入）單擊【插入】【特殊符號】命令，插入一個黑正方形。接著輸入“ ,B2*50) ”將單元格C2向下填充復制。最后將C列的各個單元格的字體顏色分別設置為不同的顏色2021/7/2670排序函數(shù): RANK、LARGE、SMALL函數(shù)RANK函數(shù)：返回一個數(shù)字在數(shù)字列表中的排位。LARGE函數(shù)：返回數(shù)據(jù)集中第 k 個最大值。例如，可以使用函數(shù) LARGE 得到第一名、第二名或第三名的得分。SMALL函數(shù)：返回數(shù)據(jù)集中第 k 個最小值。2021/7/2671排序函數(shù)應用舉例2021/7/2672數(shù)據(jù)庫函數(shù)：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數(shù)Microsoft Excel提供了一些用于對存儲在列表或數(shù)據(jù)庫中的數(shù)據(jù)進行分析的函數(shù)，這些函數(shù)統(tǒng)稱為數(shù)據(jù)庫函數(shù)（Dfunctions）。這些函數(shù)的語法結(jié)構(gòu)基本相同，即：Dfunctions (database,field,criteria)函數(shù)的三個參數(shù)dat