EXCEL數(shù)據(jù)分析與主要函數(shù)課件

1、EXCEL函數(shù) 與數(shù)據(jù)分析Excel函數(shù)與數(shù)據(jù)處理第1頁，共85頁。EXCEL函數(shù) 與數(shù)據(jù)分析第2頁，共85頁。3數(shù)據(jù)分析是業(yè)務發(fā)展的推動力隨著公司的快速發(fā)展，對管理人員的數(shù)據(jù)分析能力提出了更高的要求，提高數(shù)據(jù)分析能力是提高管理能力和水平的重要內(nèi)容。當前企業(yè)數(shù)據(jù)當中的大部分都屬于非結(jié)構(gòu)化數(shù)據(jù)，比如獨立報表、零散數(shù)據(jù)、自由文本等，致使企業(yè)不能充分利用。另一方面，企業(yè)數(shù)據(jù)量非常大，而其中真正有價值的信息卻很少，因此管理人員就要從大量的數(shù)據(jù)中經(jīng)過深層分析，獲得有利于企業(yè)運營的信息 ，供領(lǐng)導決策。為貫徹落實“科學發(fā)展觀”思想，深入開展“管理提升月”活動，提高管理人員的數(shù)據(jù)挖掘和數(shù)據(jù)分析能力，特進行本次

2、交流，共同探討利用最常用的辦公軟件來管理和分析數(shù)據(jù)，提升管理水平 ，增強企業(yè)核心競爭力。第3頁，共85頁。4總 目 錄公式與函數(shù)常用函數(shù)用法應用舉例第4頁，共85頁。5一、公式與函數(shù)公式的特性公式的輸入公式中的運算符公式中的數(shù)據(jù)類型公式的復制和移動公式的調(diào)整函數(shù)格式內(nèi)置函數(shù)第5頁，共85頁。6公式的特性公式的基本特性：公式的輸入是以“ =”開始，公式的計算結(jié)果顯示在單元格中，公式本身顯示在編輯欄中。(工具選項菜單)如：=1+2+6 （數(shù)值計算） =A1+B2 （引用單元格地址） 第6頁，共85頁。7公式的輸入=IF(and(A1A2,B1B2),100*A1/A2, 100*B1/B2) （函

3、數(shù)計算）=“ABC”&”XYZ” (字符計算，結(jié)果:ABCXYZ) =25+count(A1:C4) (混和計算)公式中的自變量變化，則計算結(jié)果會自動調(diào)整第7頁，共85頁。8公式中的運算符算術(shù)運算符：+ - * / %字符運算符：&比較運算符：= = 邏輯運算符：and or not 以函數(shù)形式出現(xiàn)優(yōu)先級順序：算術(shù)運算符字符運算符比較運算符邏輯函數(shù)符 (使用括號可確定運算順序) 第8頁，共85頁。9公式中的數(shù)據(jù)類型輸入公式要注意公式中可以包括：數(shù)值和字符、單元格地址、區(qū)域、區(qū)域名字、函數(shù)等不要隨意包含空格公式中的字符要用半角引號括起來公式中運算符兩邊的數(shù)據(jù)類型要相同 如：=“ab”+25 出錯

4、 #VALUE第9頁，共85頁。10公式的復制和移動復制移動或公式時，公式會作相對調(diào)整公式的復制：使用填充柄菜單編輯復制/粘貼 或復制/選擇性粘貼 第10頁，共85頁。11公式的調(diào)整相對地址 在公式復制時將自動調(diào)整絕對地址 在公式復制時不變。例如：C3單元的公式=$B$1+$B$2復制到D5中，D5單元的公式=$B$1+$B$2混合地址 在公式復制時絕對地址不變，相對地址按規(guī)則調(diào)整。第11頁，共85頁。12函數(shù)格式函數(shù)是Excel附帶的預定義或內(nèi)置公式函數(shù)的格式：函數(shù)名(參數(shù)1，參數(shù)2,.)函數(shù)中的參數(shù)可以是：數(shù)值、字符、邏輯值、表達式、單元格地址、區(qū)域、區(qū)域名字等沒有參數(shù)的函數(shù)，括號不能省略

5、 例如: PI( )，RAND(), NOW( )第12頁，共85頁。13Excel內(nèi)置函數(shù)數(shù)學和三角函數(shù)統(tǒng)計函數(shù)文本函數(shù)日期與時間函數(shù)邏輯函數(shù)財務函數(shù)數(shù)據(jù)庫工作表函數(shù)工程函數(shù)信息函數(shù)查找與引用函數(shù)第13頁，共85頁。14二、常用函數(shù)用法 重點介紹50個常用函數(shù)的功能、格式、參數(shù)和用法，包括：數(shù)學函數(shù)（ABS、MOD、INT、ROUND、ROUNDDOWN、 ROUDUP、RAND、SQRT 、 SUBTOTAL ）三角函數(shù)（SIN、COS、PI）統(tǒng)計函數(shù)（AVERAGE、COUNT、MAX、MIN、SUM、RANK、 LARGE 、 FREQUENCY）文本函數(shù)（TEXT、MID、LEFT、

6、RIGHT、TRIM、VALUE、LEN 、 CONTAENATE ）日期函數(shù)（NOW、DATE、DAY、MONTH、TODAY、WEEKDAY、DATEIF）條件函數(shù)（IF、SUMIF、COUNTIF）邏輯函數(shù)（OR、AND）查找函數(shù)（COLUMN、 INDEX、MATCH、 VLOOKUP ）財務函數(shù)（ PMT、 PV、NPV、IRR）數(shù)據(jù)庫函數(shù)(DCOUND )其他函數(shù)（ ISBLANK 、ISERROR）第14頁，共85頁。15數(shù)學函數(shù)ABS主要功能：求出相應數(shù)字的絕對值。 使用格式：ABS(number) 參數(shù)說明：number代表需要求絕對值的數(shù)值或引用的單元格。 應用舉例：如果在

7、B2單元格中輸入公式：=ABS(A2)，則在A2單元格中無論輸入正數(shù)（如100）還是負數(shù)（如-100），B2中均顯示出正數(shù)（如100）。 特別提醒：如果number參數(shù)不是數(shù)值，而是一些字符（如A等），則B2中返回錯誤值“#VALUE！”。 第15頁，共85頁。16數(shù)學函數(shù)MOD主要功能：求出兩數(shù)相除的余數(shù)。 使用格式：MOD(number,divisor) 參數(shù)說明：number代表被除數(shù)；divisor代表除數(shù)。 應用舉例：輸入公式：=MOD(13,4)，確認后顯示出結(jié)果“1”。 特別提醒：如果divisor參數(shù)為零，則顯示錯誤值“#DIV/0!”；MOD函數(shù)可以借用函數(shù)INT來表示：上述

8、公式可以修改為：=13-4*INT(13/4)。 第16頁，共85頁。17數(shù)學函數(shù)INT主要功能：將數(shù)值向下取整為最接近的整數(shù)。 使用格式：INT(number) 參數(shù)說明：number表示需要取整的數(shù)值或包含數(shù)值的引用單元格。 應用舉例：輸入公式：=INT(18.89)，確認后顯示出18。 特別提醒：在取整時，不進行四舍五入；如果輸入的公式為=INT(-18.89)，則返回結(jié)果為-19。第17頁，共85頁。18數(shù)學函數(shù) ROUND將數(shù)字“12.3456”按照指定的位數(shù)進行四舍五入，可以在D3單元格中輸入以下公式：“=ROUND(B3,C3)“第18頁，共85頁。19數(shù)學函數(shù) ROUNDDOW

9、N向下舍入函數(shù)。例如：出租車的計費標準是：起步價為5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。輸入不同的公里數(shù)，然后計算其費用?？梢栽贑3單元格中輸入以下公式：=IF(B3=80,A,IF(C2=60,B,C),IF(D2=80,A,IF(D2=60,B,C),IF(E2=80,A,IF(E2=60,B,C)，然后把鼠標指針指向F2單元格的右下角，等鼠標指針變成黑色十字加號時，按住左鍵向右拖動到這列單元格的最后放手。 第69頁，共85頁。70第70頁，共85頁。71利用函數(shù)進行等級評定(續(xù))也可以在F2單元格中輸入：=IF(C2=80,A,IF(C2=60,

10、B,C)&IF(D2=80,A,IF(D2=60,B,C)&IF(E2=80,A,IF(E2=60,B,C)，然后把鼠標指針指向F2單元格的右下角，等鼠標指針變成黑色十字加號時，按住左鍵向右拖動到這列單元格的最后放手。 第71頁，共85頁。72第72頁，共85頁。73統(tǒng)計學生考試成績 第73頁，共85頁。74選中f23:f26單元格，拖動填充句柄向右填充公式至h26單元格，松開鼠標，各學科的統(tǒng)計數(shù)據(jù)就出來了。至于各科分數(shù)段人數(shù)的統(tǒng)計，那得先選中f28:f35單元格，在編輯欄中輸入公式：=FREQUENCY(F$3:F$22,$C$28:$C$35)。然后按下“Ctrl+Shift+Enter”

11、快捷鍵，可以看到在公式的最外層加上了一對大括號。現(xiàn)在，我們就已經(jīng)得到了語文學科各分數(shù)段人數(shù)了。在K列中的那些數(shù)字，就是我們統(tǒng)計各分數(shù)段時的分數(shù)分界點。現(xiàn)在再選中f28:f35單元格，拖動其填充句柄向右至h列，那么，其它學科的分數(shù)段人數(shù)也立即顯示在我們眼前了。 第74頁，共85頁。75自動錄入性別在d3單元格中輸入“IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,女,男),IF(MOD(MID(C3,15,1),2)=0,女,男)”。回車后即可在單元格獲得該職工的性別，而后只要把公式復制到D3、D4等單元格，即可得到其他職工的性別。 第75頁，共85頁。76根據(jù)身份

12、證號提取出生日期在單元格中輸入公式“=IF(LEN(C3)=15,CONCATENATE(19,MID(C3,7,2),年,MID(C3,9,2),月,MID(C3,11,2),日),CONCATENATE(MID(C3,7,4),年,MID(C3,11,2),月,MID(C3,13,2),日)”。 第76頁，共85頁。77年齡統(tǒng)計 第77頁，共85頁。78位次閾值統(tǒng)計 假設(shè)C2:C21區(qū)域存放著學生的考試成績，首先在D列選取空白單元格D3，在其中輸入公式“=PERCENTILE(C2:C21,0.67)”。其中D2作為輸入百分點變量的單元格，如果你在其中輸入0.33，公式就可以返回名次達到前

13、1/3所需要的成績。第78頁，共85頁。79讓Excel按人打出工資條 新建一Excel文件，在sheet1中存放工資表的原始數(shù)據(jù)，假設(shè)有N列。第一行是工資項目，從第二行開始是每個人的工資。在sheet2中我們來設(shè)置工資條。根據(jù)實際情況，工資條由三行構(gòu)成，一行對應工資項目，一行對應一個人的工資數(shù)據(jù)，然后是一個空行用來方便切割。這樣三行構(gòu)成一個工資條。工資項目處在行號除以3余數(shù)為1的行上；空行處在行號能整除3的行上。以上兩行不難設(shè)置，關(guān)鍵是工資數(shù)據(jù)行，牽扯到sheet1與 sheet2中數(shù)據(jù)的對應，經(jīng)分析不難看出“sheet2中的數(shù)據(jù)行=INT(sheet1中的數(shù)據(jù)行+4)/3)”。第79頁，共

14、85頁。80這樣我們在sheet2的A1單元格中輸入公式“=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT(ROW()+4)/3),COLUMN()”。確認后選擇A1單元格，把鼠標放在A1單元格的右下角，鼠標變成“+”時，向右拖動鼠標自動填充至N列，這樣工資條中的第一行就出來了。選定A1：N1，把鼠標放在N1單元格的右下角，鼠標再次變成“+”時，向下拖動鼠標自動填充到數(shù)據(jù)的最后一行，工資條就全部制作完成了。該公式運用IF函數(shù)，對MOD函數(shù)所取的引用行號與3的余數(shù)進行判斷。如果余數(shù)為0，則產(chǎn)生一個空行；如

15、果余數(shù)為1，則固定取sheet1中第一行的內(nèi)容；否則運用INDEX函數(shù)和INT函數(shù)來取Sheet1對應行上的數(shù)。最后來設(shè)置一下格式，選定A1：N2設(shè)上表格線，空行不設(shè)。然后選定A1：N3，拖動N3的填充柄向下自動填充，這樣有數(shù)據(jù)的有表格線，沒有數(shù)據(jù)的沒有表格線。最后調(diào)整一下頁邊距，千萬別把一個工資條打在兩頁上。 第80頁，共85頁。81Word表格計算用法： 點擊“插入”“域”“公式”，彈出“公式”對話框。 可從“粘貼函數(shù)”下拉列表框中選擇相應的函數(shù)如SUM、AVERAGE、COUNT、MAX等，也可直接輸入函數(shù)名。 函數(shù)參數(shù)為ABOVE（數(shù)據(jù)在單元格之上）、LEFT（左）、RIGHT（右）中

16、其一。 還可以在“數(shù)字格式” 下拉列表框中選擇相應的數(shù)字格式。舉例：=sum(above) 計算單元格之上本列單元格數(shù)據(jù)的總和。提醒：當原數(shù)據(jù)發(fā)生變化時，結(jié)果單元格內(nèi)的數(shù)據(jù)不會隨之發(fā)生變化，需要右擊單元格選擇“更新域”；公式中可以包含運算符，如=sum(above)/count(above)第81頁，共85頁。82下次交流內(nèi)容一、利用EXCEL函數(shù)進行統(tǒng)計分析： RANK(排名)、 FREQUENCY(頻數(shù))、 MODE(眾數(shù))、 MEDIAN(中位數(shù))、 CORREL(相關(guān)系數(shù))、 VAR(樣本方差)、 VARP(總體方差)、 FTEST(F檢驗值)、 CRITBINOM(質(zhì)量檢驗)、 TRIMMEAN(內(nèi)部平