EXCEL函數(shù)的使用

1、浩強精密機械（青島）有限公司浩強精密機械（青島）有限公司GRAND STRONG PRECISION MACHINES CO.,LTD20152015公司內(nèi)部公司內(nèi)部ITIT培訓培訓1 培訓大綱培訓大綱為什么使用函數(shù) Excel函數(shù)的種類及功能 函數(shù)的基本語法 函數(shù)的調用 嵌套函數(shù)的使用 使用Excel的幫助來理解函數(shù) 常用函數(shù)介紹數(shù)據(jù)透視表2為什么使用函數(shù)為什么使用函數(shù)示例：單元格數(shù)字之和：=A1+A2+A3 函數(shù)=sum（A1:A100)函數(shù)的優(yōu)勢：簡化公式很多計算無法通過輸入表達式實現(xiàn)，而必須使用函數(shù)才能完成特定運算公式中輸入函數(shù)名時，以小寫或大寫的形式輸入均可，但建議用小寫的形式輸入，

2、按下Enter鍵后,如函數(shù)名拼寫無誤，Excel會自動轉化為大寫形式，通過此功能檢測所輸入的函數(shù)名是否正確。3Excel函數(shù)的種類及功能函數(shù)的種類及功能Excel提供上百個內(nèi)置函數(shù)，函數(shù)類別（共14種）及其功能邏輯函數(shù)：設置判斷條件以使公式更加智能化文本函數(shù)：對公式和單元格中的文本進行提取查找替換或格式化處理信息函數(shù)：判定單元格或公式中的數(shù)據(jù)類型，或返回某些特定信息統(tǒng)計函數(shù)：統(tǒng)計和分析工作表中的數(shù)據(jù)財務函數(shù)：分析和計算財務數(shù)據(jù)工程函數(shù)：分析和處理工程數(shù)據(jù)Web函數(shù)：用于與網(wǎng)絡數(shù)據(jù)進行交互數(shù)據(jù)庫函數(shù)：分類、查找與計算數(shù)據(jù)表中的數(shù)據(jù) 4兼容性函數(shù)：已被新增函數(shù)代替，提供它們以便可用于Excel早期

3、版本數(shù)學和三角函數(shù)：進行數(shù)學計算，包括常規(guī)計算和三角函數(shù)方面的計算日期和時間函數(shù)：對公式中的日期和時間進行計算與格式設置查找和引用函數(shù)：查找或返回工作表中的匹配數(shù)據(jù)或特定信息多維數(shù)據(jù)集函數(shù)：分析多維數(shù)據(jù)集合中的數(shù)據(jù)加載宏和自動化函數(shù)：通過加載宏提供的函數(shù)，擴展Excel的函數(shù)功能 5函數(shù)的基本語法函數(shù)的基本語法函數(shù)的基本語法為： = 函數(shù)名(參數(shù)1,參數(shù)2,參數(shù)n)。注意問題：函數(shù)名與其后的括號“（”之間不能有空格。當有多個參數(shù)時，參數(shù)之間要用逗號“，”分隔。參數(shù)部分總長度不能超過1024個字符。參數(shù)可以是數(shù)值、文本、邏輯值、單元格引用，也可以是各種表達式或函數(shù)。函數(shù)中的逗號“，”、引號“”等

4、都是半角字符，而不是全角字符。6函數(shù)的調用函數(shù)的調用 直接在單元格中輸入函數(shù)。=函數(shù)名（）利用插入函數(shù)按鈕或命令插入函數(shù)（快捷鍵shift+F3）7嵌套函數(shù)的使用嵌套函數(shù)的使用當一個函數(shù)中的參數(shù)為另外一個函數(shù)時，就是在使用嵌套函數(shù) 。利用插入函數(shù)按鈕或命令插入各個函數(shù)。8使用使用Excel的幫助來理解函數(shù)的幫助來理解函數(shù)直接在Excel右上角輸入函數(shù)名利用Excel函數(shù)幫助任務窗格。9常用函數(shù)介紹常用函數(shù)介紹求和函數(shù): SUM/SUMIF/SUMPRODUCT函數(shù)數(shù)學函數(shù) AVERGAE、 MAX、MIN、計數(shù)函數(shù): COUNT、COUNTA、COUNTIF函數(shù)條件函數(shù)和邏輯函數(shù): IF函數(shù)、

5、AND函數(shù)、OR函數(shù)日期和時間函數(shù): DAY、DATE、DAYS360、TODAY函數(shù)等分析工具庫里的日期函數(shù)查找和引用函數(shù): VLOOKUP、 HLOOKUP、 MATCH、 INDEX、CHOOSE、OFFSET函數(shù)文本函數(shù)：LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函數(shù)排序函數(shù): RANK、LARGE、SMALL函數(shù)數(shù)據(jù)庫函數(shù)：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數(shù)其他函數(shù)10求和函數(shù)求和函數(shù): SUM/SUMIF/SUMPRODUCT函數(shù)函數(shù)SUM函數(shù) :計算數(shù)字之和 = SUM (參數(shù)1,參數(shù)2,參數(shù)N) 參數(shù)可以是直接輸入的數(shù)字、單元格引用或

6、數(shù)組。SUMIF函數(shù) :按給定條件對指定單元格求和 =SUMIF(range,criteria,sum_range) range:表示要進行條件判斷的單元格區(qū)域；criteria:表示要進行判斷的條件；sum_range:表示根據(jù)條件判斷的結果要進行計算的單元格區(qū)域。SUMPRODUCT函數(shù) :計算數(shù)組元素的乘積之和 = SUMPRODUCT(array1,array2,array3, .) array表示要參與 的數(shù)組11求和函數(shù)求和函數(shù): 應用舉例應用舉例SUM函數(shù)：計算數(shù)字之和 SUMIF函數(shù)：按給定條件對指定單元格求和SUMPRODUCT函數(shù)：計算數(shù)組元素的乘積之和12求和函數(shù)求和函數(shù)

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

8、件的寫法。15數(shù)學函數(shù)數(shù)學函數(shù) AVERGAE、 MAX、MIN、函數(shù)函數(shù) 常用的數(shù)學函數(shù)有：AVERGAE函數(shù)：求平均值MAX：求最大值MIN：求最小值16平均值函數(shù)平均值函數(shù): AVERGAE函數(shù)函數(shù)AVERGAE函數(shù) :求N個數(shù)的平均值 = AVERGAE(參數(shù)1,參數(shù)2,參數(shù)N) 17最大值與最小值函數(shù)最大值與最小值函數(shù): MAX、MIN函數(shù)函數(shù) MAX函數(shù) : 求N個數(shù)的最大值 = MAX(參數(shù)1,參數(shù)2,參數(shù)N) MIN函數(shù): 求N個數(shù)的最小值 = MIN(參數(shù)1,參數(shù)2,參數(shù)N) 18計數(shù)函數(shù)計數(shù)函數(shù) count函數(shù):只返回數(shù)值型的數(shù)字個數(shù)，錯誤值、空值、邏輯值、文字則被忽略。C

9、OUNTA函數(shù):返回參數(shù)列表中非空值的單元格個數(shù)。Countif函數(shù)：對指定區(qū)域中符合指定條件的單元格計數(shù)。19計數(shù)函數(shù)計數(shù)函數(shù):計算高于平均業(yè)績的人數(shù)計算高于平均業(yè)績的人數(shù)20條件函數(shù)和邏輯函數(shù)條件函數(shù)和邏輯函數(shù): IF函數(shù)、函數(shù)、AND函數(shù)、函數(shù)、OR函數(shù)函數(shù)IF函數(shù): IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結果。 = IF(條件表達式，條件值為真時返回的值，條件值為假時返回的值) AND函數(shù)：AND函數(shù)表示邏輯與，當所有條件都滿足時（即所有參數(shù)的邏輯值都為真時），AND函數(shù)返回TRUE，否則，只要有一個條件不滿足即返回FALSE。 = AND(條件1，條件2，條件N)OR

10、函數(shù)：只要有一個條件滿足時，OR函數(shù)返回TRUE，只有當所有條件都不滿足時才返回FALSE。 = OR(條件1,條件2,條件N)說明：這3個函數(shù)常常聯(lián)合使用。21IF函數(shù)應用舉例函數(shù)應用舉例 之一之一計算獎金計算獎金22IF函數(shù)應用舉例函數(shù)應用舉例 之二之二計算增長率計算增長率計算增長率當被比較年份沒有數(shù)據(jù)時，會出現(xiàn)被除數(shù)為0的錯誤?？梢岳肐F函數(shù)進行處理。如圖，去年沒有數(shù)據(jù)，而今年有數(shù)據(jù)，就顯示“新增項目”。去年有數(shù)據(jù)，而今年沒有數(shù)據(jù)，就顯示“已經(jīng)停產(chǎn)”。23IF函數(shù)應用舉例函數(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】組合鍵注意單元格的引用方式。24AND函數(shù)應用舉例函數(shù)應用舉例25OR函數(shù)應用舉例函數(shù)應用舉例26聯(lián)合使用聯(lián)合使用IF、AND和和OR函數(shù)函數(shù)計算獎金計算獎金27日期函數(shù)日期函數(shù): DAY、DATE、DAYS360、TODAY函數(shù)函數(shù)DAY函數(shù): 返回以序列號表示的某日期的天數(shù)，用整數(shù) 131 表示。 = DAY(日期序列號) 例如 DAY(2015-8-27)=27DATE函數(shù)：返回代表特定日期的

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

13、KDAY(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(A1)+20)29時間函數(shù)時間函數(shù)輸入時間輸入22:00與輸入10:00 PM是一樣的。如何計算跨午夜零時的時間間隔？輸入：=B2+(B2A2)-A2 或者：=

14、B2+IF(B2A2,1,0)-A2思路：如果下班時間小于上班時間，就表示已經(jīng)過了1天，因此要加1。否則，如果下班時間大于上班時間，就表示還在當前，因此不需要加1天。30EDATE函數(shù)函數(shù)EDATE函數(shù)：返回指定日期往前或往后幾個月的日期。例：2007年4月12日之后3個月的日期：=EDATE(“2007-4-12”,3)，為2007-7-122007年4月12日之前3個月的日期：=EDATE(“2007-4-12”,-3)，為2007-1-12計算應付賬款的到期日：如果一筆應付款的到期日為自交易日起3個月的那一天，比如交易日為2006年11月30日，滿3個月后為2007-2-28：=EDAT

15、E(“2006-11-30”,3)，為2007-2-2831EOMONTH函數(shù)函數(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計算應付賬款的到期日：如果一筆應付款的到期日為自交易日起滿3個月后的下一個月的5號，比如交易日為2006年11月20日，滿3個月后下個月5號就是2007-3-5：=EOMONTH(2006-11-20,3-(DAY(A1)

16、=5)+532WEEKNUM函數(shù)函數(shù)WEEKNUM函數(shù)：返回指定日期是該年的第幾周。例：2007年4月12日是2007年的第15周：=WEEKNUM(“2007-4-12”)，為第15周33WORKDAY函數(shù)函數(shù)WORKDAY函數(shù)：返回某指定日期之前或之后的給定工作日天數(shù)的日期（除去雙休日和國家法定假日） 。例：指定日期為2007年4月12日，往后30個工作日的日期為（要出去國家法定的五一3天假日）2007年5月29日：=WORKDAY(2007-4-12,30,2007-5-1,2007-5-2,2007-5-3)假若一項工程開始日期為2007年4月20日，預計需要60個工作日，那么預計完工

17、的時間為哪天？34NETWORKDAYS函數(shù)函數(shù)NETWORKDAYS函數(shù)：返回兩個工作日之間的工作天數(shù)（除去雙休日和國家法定假日）。例：2007年4月12日至2007年6月20日之間的工作天數(shù)（除去雙休日和國家法定假日） 為47天。35查找和引用函數(shù)查找和引用函數(shù): LOOKUP、HLOOKUP、 VLOOKUP、 MATCH、 INDEX、CHOOSE、OFFSET函數(shù)函數(shù)LOOKUP函數(shù)：僅在單行單列中查找（數(shù)組形式）。HLOOKUP函數(shù)：在區(qū)域或數(shù)組的行中查找數(shù)據(jù)。VLOOKUP函數(shù)：在區(qū)域或數(shù)組的列中查找數(shù)據(jù) 。MATCH函數(shù)：返回指定內(nèi)容所在的位置。INDEX函數(shù):：返回指定位置中

18、的內(nèi)容（數(shù)組形式或引用形式）CHOOSE函數(shù)：根據(jù)序號從列表中選擇對應的內(nèi)容。OFFSET函數(shù)：動態(tài)引用單元格。提示：我們還可以利用名稱來查找數(shù)據(jù)（詳見文件“通過名稱查找數(shù)據(jù).xls”）36LOOKUP函數(shù)應用舉例函數(shù)應用舉例 371、查找員工信息、查找員工信息2、分數(shù)評定、分數(shù)評定HLOOKUP函數(shù)應用函數(shù)應用舉例之一舉例之一 38HLOOKUP函數(shù)應用舉例函數(shù)應用舉例 之二之二根據(jù)指定的行、列查找數(shù)據(jù)39VLOOKUP函數(shù)應用函數(shù)應用舉例之一舉例之一 40VLOOKUP函數(shù)應用函數(shù)應用舉例之二舉例之二 制作制作采購表采購表根據(jù)兩個表格制作采購表，如圖所示，計算步驟如下：單元格區(qū)域B10:B

19、13為各種商品的采購數(shù)量，由人工輸入。在單元格C10中輸入公式“=VLOOKUP(A10,$A$3:$B$6,2,FALSE)”，獲取某商品的單價。在單元格D10中輸入公式=VLOOKUP(B10,$D$3:$H$6,MATCH(A10,$D$3:$H$3,0),TRUE)”，獲取某商品的折扣。在單元格E10中輸入公式“=B10*C10*(1-D10)”，計算采購金額。選取單元格區(qū)域C10:E10，將其向下填充復制到單元格區(qū)域C13:E13，得到其他商品的單價、折扣和采購金額。41MATCH函數(shù)應用函數(shù)應用舉例舉例注意：注意：MATCH返回的位置是相對于指定的單元格區(qū)域而言的，而不返回的位置是相對于指定的單元格區(qū)域而言的，而不是對整個工作表區(qū)域而言的。是對整個工作表區(qū)域而言的。例如：例如：MATCH(455,A2:E2,0)=342INDEX函數(shù)應用舉例函數(shù)應用舉例43MATCH函數(shù)與函數(shù)與INDEX函數(shù)聯(lián)合使用函數(shù)聯(lián)合使用