EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)課件

1、繁雜的數(shù)據(jù)處理重復的數(shù)據(jù)處理傳統(tǒng)統(tǒng)計方法出錯多EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)學習EXCEL函數(shù)目的 通過功能說明與案例演示,了解函數(shù)的應用范圍與功效,激發(fā)后續(xù)學習函數(shù)的興趣 掌握函數(shù)使用的方法與技巧 充分發(fā)揮Excel的強大數(shù)據(jù)處理功能，提升工作效率 EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)課程大綱u課堂講解篇:函數(shù)功能說明與案例講解u課后自學篇:基礎函數(shù)功能說明EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)什么是函數(shù)？ 在數(shù)學中，函數(shù)表示每個輸入值對應唯一輸出值的一種對應關系 Excel 函數(shù)即是預先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務的特殊公式特殊公式。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計

2、技巧(入門級)EXCEL函數(shù)的結構Excel 函數(shù)結構:左右括號成對出現(xiàn)單一結構嵌套結構參數(shù)與參數(shù)之間使用半角逗號進行分隔EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級) 統(tǒng)計類:count counta countif countblank max min average 查找引用類:vlookup row column index 文本類：len left right mid find 日期時間類：date month year now today 數(shù)學類：abs round roundup rounddown Sum sumif trunc 邏輯類：if and or & 數(shù)據(jù)透視表

3、插入空格行(工資條)EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Count 用途：返回數(shù)據(jù)區(qū)域中非空值的單元格數(shù)目。利用函數(shù)COUNT 可以計算單元格區(qū)域中數(shù)據(jù)項的個數(shù)。 語法：COUNT(單元格區(qū)域1,單元格區(qū)域2) 說明：參數(shù)的個數(shù)為130 個。備注：只對數(shù)字型數(shù)據(jù)進行計數(shù)， 文本、邏輯值、錯誤信息、空單元格都不統(tǒng)計的EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Counta 用途：返回參數(shù)組中非空單元格的數(shù)目。利用函數(shù)COUNTA 可以計算數(shù)組或單元格區(qū)域中數(shù)據(jù)項的個數(shù)。 語法：COUNTA(單元格區(qū)域1,單元格區(qū)域2) 說明：參數(shù)的個數(shù)為130 個。備注：統(tǒng)計非空單元格,只要單元格有內容,就

4、會被統(tǒng)計,包括有些看不見的字符。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Countblank 用途：返回參數(shù)組中空單元格的數(shù)目。利用函數(shù)COUNTA 可以計算數(shù)組或單元格區(qū)域中數(shù)據(jù)項的個數(shù)。 語法：COUNTBLANK(單元格區(qū)域1,單元格區(qū)域2) 說明：參數(shù)的個數(shù)為130 個。備注：統(tǒng)計空單元格,只要單元格有內容,包括有些看不見的字符都不算空單元格。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Countif：條件計數(shù)函數(shù)。 用途：計算區(qū)域中滿足給定條件的單元格的個數(shù)。 語法：COUNTIF(統(tǒng)計區(qū)域，條件) 參數(shù)：“統(tǒng)計區(qū)域”為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域?！皸l件” 為確定哪

5、些單元格將被計算在內的條件，其形式可以為數(shù)字、表達式或文本。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)max：求最大值。 用途：計算區(qū)域中最大值，忽略邏輯及文本 語法：max(統(tǒng)計區(qū)域1,統(tǒng)計區(qū)域2，) 參數(shù)：“統(tǒng)計區(qū)域”為需要計算的單元格區(qū)域。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)min：求最小值。 用途：計算區(qū)域中最大小值，忽略邏輯及文本 語法：max(統(tǒng)計區(qū)域1,統(tǒng)計區(qū)域2，) 參數(shù)：“統(tǒng)計區(qū)域”為需要計算的單元格區(qū)域。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)average：求平均值。 用途：計算區(qū)域中數(shù)值單元格的平均數(shù)，忽略邏輯及文本 語法： average(統(tǒng)計區(qū)域1,統(tǒng)計區(qū)域

6、2，) 參數(shù)：“統(tǒng)計區(qū)域”為需要計算的單元格區(qū)域，只計算數(shù)值單元格。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Vlookup 返回表格或數(shù)組當前行中指定列處的數(shù)值。 語法：VLOOKUP(索引值，數(shù)據(jù)區(qū)域，列序號，查找方式) 參數(shù)： 索引值為需要在數(shù)據(jù)表第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字串。 數(shù)據(jù)區(qū)域為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，可以使用對區(qū)域或區(qū)域名稱的引用。 列序號 指數(shù)據(jù)區(qū)域 中待返回的匹配值的列序號。它等于1 時，返回數(shù)據(jù)區(qū)域 第一列中的數(shù)值；它等于 2時，返回數(shù)據(jù)區(qū)域第二列中的數(shù)值，以此類推。 查找方式為一邏輯值,指明函數(shù)VLOOKUP 返回時是精確匹配還是近似匹配。如果為

7、1或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于索引值 的最大數(shù)值；如果為0，函數(shù)VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值#N/A。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)函數(shù)參數(shù)中的單元格引用n 根據(jù)公式所在單元格的位置發(fā)生變化時，單元格引用的變化情況，我們可以引用分為相對引用（如A2）、絕對引用（$A$2）和混合引用三種類型n 混合引用有“絕對列和相對行”（如$A3:$E3）；或是“絕對行和相對列”（如A$3:E$3）兩種形式。前者不論公式怎么復制,列都不變。 后者不論公式怎么復制，行號不變。n 絕對符號的添加: 用F4放在公式中的單元格引用中進行

8、切換n 幾種單元格引用表示法： A2:B7（某一單元格區(qū)域）； 1:1（第一行）； 1:5(1至5行)； F:F（F列）；A：N（A到N列）EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Row 用途：返回給定引用的行號。 語法：ROW(單元格引用)。“單元格引用”為需要得到其行號的單元格或單元格區(qū)域。 如空格則返回當前單元格行號EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)COLUMN 用途：返回給定引用的列標。 語法：COLUMN(單元格引用)。 參數(shù)：“單元格引用”為需要得到其列標的單元格，如果省略，則假定函數(shù)COLUMN 是對所在單元格的引用。 實例：公式“=COLUMN(A3)”返回1，=CO

9、LUMN(D5)返回?。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Index 用途：返回表格或區(qū)域中的數(shù)值。 語法：INDEX(單元格區(qū)域，行序號，列序號)返回數(shù)組中指定的單元格或單元格數(shù)組的數(shù)值。 案例：題一：=INDEX（A1：C5，2，2）的返回值是？題二：=INDEX（A1：A5，5，1）的返回值是？EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)LEN：計算字符長度。 用途：LEN 返回文本串的字符數(shù)。 語法：LEN(text) 。 參數(shù)：Text 待要查找其長度的文本。 注意：此函數(shù)用于雙字節(jié)字符，且空格也將作為字符進行統(tǒng)計。 實例：如果A1=電腦愛好者，則公式“=LEN(A1)”返回？

10、EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Left/Right：截取字符串函數(shù) 用途：根據(jù)指定的字符數(shù)返回文本串中的第一個或前幾個字符。此函數(shù)用于雙字節(jié)字符。 語法：LEFT(文本, 返回的字符數(shù)) Right(文本, 返回的字符數(shù)) 參數(shù)：返回的字符數(shù)必須大于或等于0。 實例：如果A1=電腦愛好者，則LEFT(A1，2)返回? ;Right(A1,3)返回?EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)mid：截取字符串函數(shù) 用途：從文本串中指定的位置起，返回指定字符數(shù)的字符 。 語法：mid (文本, 開始位置，返回的字符數(shù)) 參數(shù)：返回的字符數(shù)必須大于或等于0。 實例：如果A1=電腦愛好者，

11、則mid(A1,3,3)返回?EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Find：查找字符串的位置 用途：返回一個字符串在另一個字符串中出現(xiàn)的起始位置。 語法：find(要查找的字符串, 要在其中進行搜索的字符串，起始搜索位置) 參數(shù)：起始搜索位置如省略則為1。 實例：如果A1=電腦愛好者， 則find(“愛“,A1)返回?EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)sum：求和 用途：計算單元格區(qū)域所有值的和 語法：sum(區(qū)域1,區(qū)域2、） 快捷鍵： Alt +EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Sumif:條件求和函數(shù) 用途：根據(jù)指定條件對若干單元格、區(qū)域或引用求和。 語法：SUMI

12、F(條件區(qū)域，條件，需求和的區(qū)域) 參數(shù)：條件是由數(shù)字、邏輯表達式等組成的判定條件。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Round Roundup Rounddown Trunc 用途：根據(jù)數(shù)據(jù)需要保留小數(shù)位。 語法：Round(要四舍五入的數(shù)值，保留的小數(shù)位） Roundup（要向上取舍的數(shù)值，保留的小數(shù)位） Rounddown（要向下取舍的數(shù)值，保留的小數(shù)位） Trunc（要向取舍的數(shù)值，保留的小數(shù)位）EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)if：條件函數(shù) 用途：判斷是否滿足條件，如滿足返一個值，如不滿足則返回另一個值。 語法：if(條件，條件滿足所返回的值，條件不滿足時所返回的值

13、） if函數(shù)最多可嵌套7層EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)AND 用途：所有參數(shù)的邏輯值為真時返回TRUE(真)；只要有一個參數(shù)的邏輯值為假，則返回FALSE(假)。 語法：AND(邏輯表達式1，邏輯表達式2， )。 參數(shù)：邏輯表達式最多可達30個，它們的結論或為TRUE(1)或為FALSE(0)。參數(shù)必須是邏輯值或者包含邏輯值的數(shù)組或引用。如果指定的單元格區(qū)域內包括非邏輯值，AND 將返回錯誤值#VALUE！。 實例：公式“=AND(32, 0)”返回FALSE;公式“=AND(32, 86)”返回TrueEXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)OR 用途：所有參數(shù)中的任意一個邏

14、輯值為真時即返回TRUE(真)。 語法：OR(邏輯表達式1 ，邏輯表達式2，.) 參數(shù)：同and的參數(shù)。 實例：如果A1=6、A2=8，則公式“=OR(A1+A2A2，A1=A2)”返回TRUE；而公式“=OR(A1A2，A1=A2)”返回FALSE。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)自學篇：函數(shù)基礎知識及常用函數(shù)EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)函數(shù)的種類-擴展函數(shù)的啟用按照函數(shù)的來源，Excel 函數(shù)可以分為：n內置函數(shù)：只要啟動了Excel，用戶就可以使用它們。如：sum; averagen擴展函數(shù)：必須通過單擊“工具加載宏”菜單

15、命令加載，然后才能像內置函數(shù)那樣使用。當你發(fā)現(xiàn)你某些函數(shù)不可用時，請執(zhí)行這一步驟。如：iseven函數(shù)的使用必須執(zhí)行“工具加載宏分析工具庫”方能使用EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Rank 用途：返回一個數(shù)值在一組數(shù)值中的排位(如果數(shù)據(jù)清單已經排過序了，則數(shù)值的排位就是它當前的位置)。 語法：RANK(需要計算其排位的數(shù)字，存放要比較的數(shù)字的區(qū)域，排序方式) 參數(shù)：排序方式為一數(shù)字，指明排位的方式。如果排序方式為0 或省略，則按降序排列的數(shù)據(jù)清單進行排位。如果排序方式不為零，當作按升序排列的數(shù)據(jù)清單進行排位。 注意：函數(shù)RANK 對重復數(shù)值的排位相同。但重復數(shù)的存在將影響后續(xù)數(shù)值的排

16、位。如在一列整數(shù)中，若整數(shù)60 出現(xiàn)兩次，其排位為5，則61 的排位為7(沒有排位為6 的數(shù)值)。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Subtotal：返回數(shù)據(jù)清單的分類匯總 用途：返回數(shù)據(jù)清單或數(shù)據(jù)庫中的分類匯總。如果用戶使用“數(shù)據(jù)”菜單中的“分類匯總”命令創(chuàng)建了分類匯總數(shù)據(jù)清單，即可編輯SUBTOTAL 函數(shù)對其進行修改。 語法：SUBTOTAL(函數(shù)名代表號 ，區(qū)域1，區(qū)域2 ) 語法說明：函數(shù)名代表號為1 到11 之間的自然數(shù)，用來指定分類匯總計算使用的函數(shù)。常用的代表號為：1 是AVERAGE；3 是COUNTA；4 是MAX；5 是MIN；9 是SUM。區(qū)域1、區(qū)域2 則是需

17、要分類匯總的1 到29 個區(qū)域或引用。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)ABS：求絕對值 用途：返回某一參數(shù)的絕對值。 語法：ABS(number) 參數(shù)：number 是需要計算其絕對值的一個實數(shù)。 實例：如果A1=-16，則公式“=ABS(A1)”返回16。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)INT:無條件取整 用途：將任意實數(shù)向下取整為最接近的整數(shù)。 語法：INT(實數(shù)) 參數(shù)：Number 為需要處理的任意一個實數(shù)。 實例：如果A1=16.24、A2=-28.389，則公式“=INT(A1)”返回16，=INT(A2)返回-29。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)

18、EXACT:比較兩個字符串是否相同 用途：測試兩個字符串是否完全相同。如果它們完全相同，則返回TRUE；否則返回FALSE。EXACT 函數(shù)能區(qū)分大小寫，但忽略格式上的差異。 語法：EXACT(text1，text2)。 參數(shù)：Text1 是待比較的第一個字符串，Text2 是待比較的第二個字符串。 實例：如果A1=得利卡、A2=富利卡、A3=戈藍，則公式“=EXACT(A1，A2)”返回FALSE，=EXACT(A1，A3)返回FALSE，=EXACT(word，word)返回TRUE。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)ISERRORn 用途：它們可以檢驗是否發(fā)生錯誤值。發(fā)生錯誤返回

19、TRUE ，否則返回FALSE。n 語法：ISERROR(value) n 參數(shù)：Value 是需要進行檢驗的參數(shù)。n 實例：用VLOOKUP函數(shù)找不到相應的值時，顯示空值。 =IF(ISSEROR(vlookup(A2,A2:G5,2,0), , vlookup(A2,A2:G5,2,0)EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)CONCATENATEn用途：將若干文字串合并到一個文字串中，其功能與&運算符相同。n語法：CONCATENATE(text1，text2，.)n參數(shù)：Text1，text2，.為1 到30 個將要合并成單個文本的文本項，這些文本項可以是文字串、數(shù)字或對單個

20、單元格的引用。n實例：如果A1=98、A2=千米，則公式“=CONCATENATE(A1，A2)”返回“98 千米”，與公式“=A1&A2”等價。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)YEARMONTHDAYn用途：返回某日期的年份/月份/日期。n語法：YEAR/MONTH/DAY(日期值)n實例： 假設A2單元格存放的日期為2008-8-20u 公式“=YEAR(A2)返回2008”u 公式“ =MONTH(A2)返回8”u 公式“ =DAY(A2)返回20”EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Date 用途：返回代表特定日期的序列號。 語法：DATE(year，month

21、，day) 參數(shù)：year 為一到四位,代表年份。Month 代表每年中月份的數(shù)字。Day 代表在該月份中第幾天的數(shù)字。 實例：將身份證中提取出的年月日直接轉化成出生年月日: 假若A=3553613,則 date(mid(a,7,4),mid(a,11,2),mid(a,13,2)返回1974-5-25EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)Today 用途：返回系統(tǒng)當前日期的序列號。 參數(shù)：無 語法：TODAY() 實例1：公式“=TODAY()”返回執(zhí)行公式時的系統(tǒng)時間。 實例2：依出生日期計算年齡的示例 表示方法：（today()-出生日期）/365EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入

22、門級)Rept：補位函數(shù) 用途：按照給定的次數(shù)重復顯示文本?？梢酝ㄟ^REPT 函數(shù)對單元格進行重復填充。 語法：REPT(需重復顯示的文本，重復顯示的次數(shù))。 參數(shù)：重復顯示的次數(shù)需為正數(shù)。 注意：REPT 函數(shù)的結果不能多于255 個字符。 實例：公式“=REPT(很好，2)”返回“很好很好”。EXCEL函數(shù)應用及數(shù)據(jù)統(tǒng)計技巧(入門級)SUBSTITUTE：替換指定文本 用途：在文字串中用新文本替代舊文本。如果需要在一個文字串中替換指定的文本，可以使用函數(shù)SUBSTITUTE；如果需要在某一文字串中替換指定位置處的任意文本，就應當使用函數(shù)REPLACE。 語法：SUBSTITUTE(需要替換其中字符的文本，需被替換的舊文本，用於替換的文本，第