1、1常用常用EXCEL函數講解函數講解2018年年9月月18日日2 新手、初級用戶、中級用戶、高級用戶和專家五個層次新手、初級用戶、中級用戶、高級用戶和專家五個層次 新手：學習者需要大致了解到新手：學習者需要大致了解到Excel的基本操作方法和常用功能，諸如輸的基本操作方法和常用功能，諸如輸入數據，查找替換，設置單元格格式，排序、匯總、篩選和保存工作簿。入數據，查找替換，設置單元格格式，排序、匯總、篩選和保存工作簿。 初級用戶：可以開始在工作中運用初級用戶：可以開始在工作中運用Excel，比如建立一個簡單的表格，畫，比如建立一個簡單的表格，畫一張簡單的圖表，會一些基本簡單的函數，如一張簡單的圖表

2、，會一些基本簡單的函數，如SUM、IF 等。等。中級用戶三個標志：中級用戶三個標志：一是理解并熟練使用各個一是理解并熟練使用各個Excel菜單命令菜單命令二是熟練使用數據透視表二是熟練使用數據透視表三是至少掌握三是至少掌握20個常用函數以及函數的嵌套運用，掌握的基本函數有個常用函數以及函數的嵌套運用，掌握的基本函數有SUM函數、函數、IF函數、函數、VLOOKUP函數、函數、INDEX函數、函數、MATCH函數、函數、OFFSET函數、函數、TEXT函數等等函數等等 3高級用戶兩個標志：高級用戶兩個標志：一是熟練運用數組公式，也就是那種用花括號包圍起來，必須用一是熟練運用數組公式，也就是那種用

3、花括號包圍起來，必須用組合鍵才能完成錄入的公式。組合鍵才能完成錄入的公式。Excel專家：專家： 從從Excel的功能細分來看，精通全部的人想必寥寥無幾。的功能細分來看，精通全部的人想必寥寥無幾。Excel是應用性太強的軟件，意味著一個沒有任何工作經驗的普通學生是應用性太強的軟件，意味著一個沒有任何工作經驗的普通學生是很難成為是很難成為Excel專家的。所以專家的。所以Excel專家也必定是某個或多個行業的專專家也必定是某個或多個行業的專家，他們都擁有豐富的行業知識和經驗。高超的家，他們都擁有豐富的行業知識和經驗。高超的Excel技術配合行業經技術配合行業經驗來共同應用，才有可能把驗來共同應用

4、，才有可能把Excel發揮到極致，所以，如果希望成為發揮到極致，所以，如果希望成為Excel專家，就不能只單單學習專家，就不能只單單學習Excel了了 。4Excel函數的種類 財務函數 日期函數 時間函數 數學與三角函數 統計函數 數據庫管理函數 文本函數 信息類函數 5函數的基本語法 函數的基本語法為： = 函數名(參數1,參數2,參數n)。 注意問題： 函數名與其后的括號“（”之間不能有空格。 當有多個參數時，參數之間要用逗號“，”分隔。 參數部分總長度不能超過1024個字符。 參數可以是數值、文本、邏輯值、單元格引用，也可以是各種表達式或函數。 函數中的逗號“，”、引號“”等都是半角字

5、符，而不是全角字符。6常用函數介紹求和函數求和函數: SUM/SUMIF/SUMPRODUCT函數函數數學函數數學函數 AVERGAE、 MAX、MIN、 ABS、 SQRT函數函數 計數函數計數函數: COUNT、COUNTA、COUNTIF函數函數條件函數和邏輯函數條件函數和邏輯函數: IF函數、函數、AND函數、函數、OR函數函數日期和時間函數日期和時間函數: DAY、DATE、DAYS360、TODAY函數等函數等分析工具庫里的日期函數分析工具庫里的日期函數查找和引用函數查找和引用函數: VLOOKUP、 HLOOKUP、 MATCH、 INDEX、CHOOSE、OFFSET函數函數四

6、舍五入函數四舍五入函數: ROUND、FLOOR和和CEILING函數函數 取整函數取整函數: INT函數函數信息函數信息函數: ISBLANK、ISTEXT、ISNUMBER、ISERROR函數函數 文本函數：文本函數：LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函數等函數排序函數排序函數: RANK、LARGE、SMALL函數函數數據庫函數：數據庫函數：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數函數其他函數其他函數7求和函數 SUM/SUMIF/SUMPRODUCT函數 SUM函數 :無條件求和 = SUM (參數1,參數2,參數N) SUMIF函數 :條

7、件求和 =SUMIF(range,criteria,sum_range) SUMPRODUCT函數 :在給定的幾組數組中，將數組間對應的元素相乘，并返回乘積之和 = SUMPRODUCT(array1,array2,array3, .) 8求和函數: 應用舉例 SUM函數 SUMIF函數 SUMPRODUCT函數9求和函數: 應用舉例 隔欄加總計算 某預算表如圖所示，每個月分為“預算數”和“實際數”，現要求計算年度“預算數”和“實際數”。這實際上就是隔欄加總問題。如果一個一個單元格相加是很繁瑣的。 解決方法：巧妙應用第3行的標題。10求和函數: 應用舉例 往下（或往右）累加匯總 往下（或往右）

8、累加匯總是常見的實務問題。 解決方法：使用SUM函數，但單元格引用的第一個地址應為絕對引用，而第二個地址為相對引用。11求和函數: 應用舉例 動態匯總當天數據 工作表中存在每天的數據，要求動態匯總計算當前的數據。 可使用SUMIF函數。但要注意條件的寫法。12數學函數 AVERGAE、 MAX、MIN、 ABS、 SQRT函數 常用的數學函數有： AVERGAE函數：求平均值 MAX：求最大值 MIN：求最小值 ABS：求絕對值 SQRT：計算平方根13平均值函數: AVERGAE函數 AVERGAE函數 :求N個數的平均值 = AVERGAE(參數1,參數2,參數N) 14最大值與最小值函數

9、: MAX、MIN函數 MAX函數 : 求N個數的最大值 = MAX(參數1,參數2,參數N) MIN函數: 求N個數的最小值 = MIN(參數1,參數2,參數N) 15絕對值函數: ABS函數 ABS函數: 求某數的絕對值 = ABS(參數) 例如，ABS(-100)=10016開平方函數: SQRT函數 SQRT函數: 求某數的平方根 = SQRT(參數) 例如，SQRT(2)=1.414213562373117計數函數: COUNT、COUNTA、COUNTIF函數 COUNT函數: 計算給定區域內數值型參數的數目 = COUNT(參數1,參數2,參數N) COUNTA 函數: 返回參數

10、列表中非空值的單元格個數 = COUNTA(參數1，參數2，參數N ) COUNTIF函數: 計算給定區域內滿足特定條件的單元格的數目 = COUNTIF(range,criteria) 18計數函數:應用舉例 COUNT函數: COUNTA 函數: COUNTIF函數:19計數函數應用計算銷售業績20計數函數:計算高于平均業績的人數21條件函數和邏輯函數: IF函數、AND函數、OR函數 IF函數: IF函數也稱條件函數，它根據參數條件的真假，返回不同的結果。 = IF(條件表達式，條件值為真時返回的值，條件值為假時返回的值) AND函數：AND函數表示邏輯與，當所有條件都滿足時（即所有參數

11、的邏輯值都為真時），AND函數返回TRUE，否則，只要有一個條件不滿足即返回FALSE。 = AND(條件1，條件2，條件N) OR函數：只要有一個條件滿足時，OR函數返回TRUE，只有當所有條件都不滿足時才返回FALSE。 = OR(條件1,條件2,條件N)說明：這3個函數常常聯合使用。22IF函數應用舉例 之一計算獎金23IF函數應用舉例 之二計算增長率 計算增長率當被比較年份沒有數據時，會出現被除數為0的錯誤。可以利用IF函數進行處理。 如圖，去年沒有數據，而今年有數據，就顯示“新增項目”。 去年有數據，而今年沒有數據，就顯示“已經停產”。24IF函數應用舉例 之三 自動歸類問題 為了有

12、效管理零用金，希望在輸入現金開支數據后，該金額數據自動依部門歸類到適當的列，從而更加醒目地標示出來。 解決辦法：使用IF函數 如圖，先在單元格F2輸入公式“=IF($D2=F$1,$C2, )”。 選定單元格區域F2:J2，按【F2】，使處于編輯狀態。 按【Ctrl+Enter】組合鍵注意單元格的引用方式。25AND函數應用舉例26OR函數應用舉例27聯合使用IF、AND和OR函數計算獎金28日期函數: DAY、DATE、DAYS360、TODAY函數 DAY函數: 返回以序列號表示的某日期的天數，用整數 131 表示。 = DAY(日期序列號) 例如 DAY(2006-12-22)=22 D

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

14、mp;TEXT(WEEKDAY(TODAY(),aaaa) 設置上月標題：=IF(MONTH(TODAY()=1,12,MONTH(TODAY()-1)&月份收支情況“ 設置本月標題=MONTH(TODAY()&月份收支情況“ 將日期轉換為星期 中文星期：=TEXT(WEEKDAY(A1),aaaa) 英文星期：=TEXT(WEEKDAY(A1),“dddd) 計算2年5個月20天后的日期=DATE(YEAR(A1)+2,MONTH(A1)+5,DAY(S1)+20) 計算實際歲數：=INT(YEARFRAC(出生日期,今天,1) =DATEIF(出生日期,今天,”Y”)30日

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

16、天。36分析工具庫里的日期函數 EDATE函數 EOMONTH函數 WEEKNUM函數 WORKDAY函數 NETWORKDAYS函數 一個特殊的日期函數：DATEDIF 注意：在使用這些函數之前，必須加載分析工具庫，即：單擊【工具】【加載宏】命令，打開【加載宏】對話框，選擇“分析工具庫”，如上圖。37EDATE函數 EDATE函數：返回指定日期往前或往后幾個月的日期。 例： 2007年4月12日之后3個月的日期：=EDATE(“2007-4-12”,3)，為2007-7-12 2007年4月12日之前3個月的日期：=EDATE(“2007-4-12”,-3)，為2007-1-12 計算應付賬

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

18、的5號，比如交易日為2006年11月20日，滿3個月后下個月5號就是2007-3-5：=EOMONTH(2006-11-20,3-(DAY(A1)0.5)表示只有在完成率超過50% 時才利用公式(B2-0.49999)*10進行計算。65信息函數: ISBLANK、ISTEXT、ISNUMBER、ISERROR函數 ISBLANK函數：判斷單元格是否為空白單元格 ISTEXT函數：判斷單元格數據是否為文本 ISNUMBER函數：判斷單元格數據是否為數字 ISERROR函數：判斷單元格是否出現錯誤66文本函數LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函數 LEN函數：獲取字符串

19、的長度 LEFT函數：獲取字符串左邊指定個數的文本 RIGHT函數：獲取字符串右邊指定個數的文本 TRIM函數：取消字符串兩側的空格 FIND函數：查找某字符在字符串中第一次出現的位置 TEXT函數：將數值轉換為按指定數字格式表示的文本 其他的文本函數67文本函數應用舉例 例1：LEN(ABCD)=4LEFT(ABCD,2)= ABRIGHT(ABCD,2)= CDTRIM( ABCD )= ABCDFIND(.,12345.65)=6TEXT(12345.6687,￥0.00)= ￥12345.67 例2：從身份證號碼獲取出生日期和性別68文本函數應用舉例例3：將科目編碼與科目名稱分離（假設

20、科目編碼與科目名稱之間有一個空格）問題：如果科目編碼與科目名稱之間沒有空格，該怎么辦呢？可使用LEN函數和LENB函數：69文本函數應用舉例例4：編制簡單的統計表 選取單元格C2。 輸入“ =REPT( ”，（不要按回車鍵，還沒有完成輸入） 單擊【插入】【特殊符號】命令，插入一個黑正方形。 接著輸入“ ,B2*50) ” 將單元格C2向下填充復制。 最后將C列的各個單元格的字體顏色分別設置為不同的顏色70排序函數: RANK、LARGE、SMALL函數 RANK函數：返回一個數字在數字列表中的排位。 LARGE函數：返回數據集中第 k 個最大值。例如，可以使用函數 LARGE 得到第一名、第二名或第三名的得分。 SMALL函數：返回數據集中第 k 個最小值。71排序函數應用舉例72數據庫函數：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數Microsoft Excel提供了一些用于對存儲在列表或數據庫中的數據進行分析的函數，這些函數統稱為數