Excel高效數(shù)據(jù)分析總結(jié)實(shí)用教案

1、關(guān)于(guny)課程 課程目標(biāo)： 幫您樹(shù)立一種正確高效(o xio)使用Excel的基本理念和思路 為您提供一套高效(o xio)實(shí)用的Excel技能和方法第1頁(yè)/共44頁(yè)第一頁(yè)，共45頁(yè)。課程課程(kchng)目錄目錄 一、 使用Excel的正確理念與注意事項(xiàng) 二、 設(shè)計(jì)標(biāo)準(zhǔn)規(guī)范的Excel表格(biog) 三、 快速掌握實(shí)用的Excel函數(shù) 四、 快速掌握Excel數(shù)據(jù)透視表 五、 從原始數(shù)據(jù)到分析圖表第2頁(yè)/共44頁(yè)第二頁(yè)，共45頁(yè)。一、使用(shyng)Excel的正確理念與注意事項(xiàng) 1 1、ExcelExcel是什么 2 2、如何使用ExcelExcel做財(cái)務(wù)管理 3 3、基本原則

2、4 4、設(shè)計(jì)模版的基本步驟 5 5、財(cái)務(wù)分析模版設(shè)計(jì)應(yīng)考慮的幾個(gè)(j )(j )問(wèn)題 6 6、正確處理基礎(chǔ)表格與報(bào)告表格 7 7、如何學(xué)習(xí)ExcelExcel第3頁(yè)/共44頁(yè)第三頁(yè)，共45頁(yè)。精細(xì)化管理精細(xì)化管理(gunl)日日 常常 控控 制制科科 學(xué)學(xué) 決決 策策企業(yè)經(jīng)營(yíng)報(bào)表企業(yè)經(jīng)營(yíng)報(bào)表溝通溝通控制控制決策決策業(yè)績(jī)?cè)u(píng)價(jià)業(yè)績(jī)?cè)u(píng)價(jià)等等等等 Excel Excel是什么(shn me)(shn me)第4頁(yè)/共44頁(yè)第四頁(yè)，共45頁(yè)。5工作(gngzu)設(shè)計(jì)標(biāo)準(zhǔn)化技能技能Stpe3成果模版Step4理念Step1思想第5頁(yè)/共44頁(yè)第五頁(yè)，共45頁(yè)。 基本(jbn)(jbn)原則 從原始的表單最

3、終的報(bào)告 盡量使用原始數(shù)據(jù) 盡量減少中間環(huán)節(jié) 簡(jiǎn)潔的報(bào)告 高效(o xio)的公式第6頁(yè)/共44頁(yè)第六頁(yè)，共45頁(yè)。設(shè)計(jì)模版的基本(jbn)(jbn)步驟 Step1：獲取數(shù)據(jù)(shj) 分析數(shù)據(jù)(shj)的來(lái)源（手工表格；導(dǎo)入表格） 源數(shù)據(jù)(shj)的結(jié)構(gòu)（單表數(shù)據(jù)(shj)，多表數(shù)據(jù)(shj)） Step2：表格結(jié)構(gòu)設(shè)計(jì) 基礎(chǔ)表格 報(bào)告表格 Step3：呈現(xiàn)方式 Excel表格形式 Word書(shū)面形式 PPT演示形式第7頁(yè)/共44頁(yè)第七頁(yè)，共45頁(yè)。財(cái)務(wù)分析模版設(shè)計(jì)(shj)(shj)應(yīng)考慮的幾個(gè)問(wèn)題 設(shè)計(jì)財(cái)務(wù)分析模版應(yīng)考慮的幾個(gè)問(wèn)題 層次結(jié)構(gòu)： 單層結(jié)構(gòu)還是多層結(jié)構(gòu)？ 布局方向： 橫向布局

4、還是縱向布局？ 動(dòng)與靜： 靜態(tài)分析還是滾動(dòng)分析？ 靈活性： 分析項(xiàng)目是一種還是多種？ 直觀性： 圖形展示分析結(jié)果 整合性： 要不要與財(cái)務(wù)軟件整合起來(lái)？ 報(bào)告性： 是否(sh fu)做成一個(gè)最終的匯報(bào)性的報(bào)告？ 案例解析 案例01 單表分析模版演示案例 案例02 多表分析模版演示案例第8頁(yè)/共44頁(yè)第八頁(yè)，共45頁(yè)。 正確處理基礎(chǔ)表格(biog)(biog)與報(bào)告表格(biog)(biog) Excel的兩類表格 基礎(chǔ)表格 按照數(shù)據(jù)的功能來(lái)分別保存在不同的工作表 結(jié)構(gòu)越簡(jiǎn)單越好 報(bào)告表格 要考慮匯總過(guò)程的效率性和方便性，分析結(jié)果的呈現(xiàn)性 結(jié)構(gòu)越清楚越好 設(shè)計(jì)(shj)表格應(yīng)遵循的總體原則： 結(jié)構(gòu)

5、的科學(xué)性 數(shù)據(jù)的易讀性 匯總的便宜性 分析的靈活性 外觀的美觀性第9頁(yè)/共44頁(yè)第九頁(yè)，共45頁(yè)。如何(rh)(rh)學(xué)習(xí)ExcelExcel Excel一定要結(jié)合企業(yè)管理來(lái)學(xué)習(xí) 從最初(zuch)的表單設(shè)計(jì)，一直到最終報(bào)表，都需要按照企業(yè)精細(xì)化管理的要求來(lái)進(jìn)行 必須掌握Excel的實(shí)用技能 高效辦公技能 數(shù)據(jù)跟蹤技能 分類匯總技能 鏈接查詢技能 合并工作表技能 數(shù)據(jù)挖掘技能 圖形展示技能 自動(dòng)化技能第10頁(yè)/共44頁(yè)第十頁(yè)，共45頁(yè)。二、設(shè)計(jì)(shj)標(biāo)準(zhǔn)規(guī)范的Excel表格 1、標(biāo)準(zhǔn)化表格解析 2、表格數(shù)據(jù)的整理與規(guī)范 3 、使用數(shù)據(jù)有效性控制規(guī)范數(shù)據(jù)輸入 4 、使用基本的函數(shù)獲取基本數(shù)據(jù)

6、 5 、使用條件格式自動(dòng)(zdng)美化表格 6 、創(chuàng)建列表自動(dòng)(zdng)復(fù)制公式 7 、設(shè)計(jì)表格綜合練習(xí)第11頁(yè)/共44頁(yè)第十一頁(yè)，共45頁(yè)。 標(biāo)準(zhǔn)化表格(biog)(biog)解析 標(biāo)準(zhǔn)化表格的基本要求 數(shù)據(jù)庫(kù)結(jié)構(gòu) 結(jié)構(gòu)最簡(jiǎn)單 填寫(xiě)數(shù)據(jù)方便 沒(méi)有合并單元格 沒(méi)有空行和空列 沒(méi)有不必要的小計(jì)和總計(jì) 沒(méi)有不必要的公式(gngsh) 數(shù)據(jù)規(guī)范第12頁(yè)/共44頁(yè)第十二頁(yè)，共45頁(yè)。表格數(shù)據(jù)(shj)(shj)的整理與規(guī)范 很多表格中的數(shù)據(jù)是非法的、錯(cuò)誤的、不規(guī)范的，需要進(jìn)行整理規(guī)范和標(biāo)準(zhǔn)化。 正確處理日期(rq)數(shù)據(jù) 正確處理文本和數(shù)字第13頁(yè)/共44頁(yè)第十三頁(yè)，共45頁(yè)。正確處理日期(rq)(

7、rq)數(shù)據(jù) Excel是如何處理日期的？ 日期是特殊的數(shù)字。日期永遠(yuǎn)是大于零的正整數(shù)。 正確輸入日期 正確輸入日期：2010-10-8，2010/10/8，8/oct/2010， 8-oct-2010 錯(cuò)誤的輸入日期方式：2010.10.08，或者 20101008 錯(cuò)誤日期的類型 輸入錯(cuò)誤 從系統(tǒng)導(dǎo)入的日期是文本格式 如何把日期顯示為需要(xyo)的格式？使用“分列”功能 修改非法日期第14頁(yè)/共44頁(yè)第十四頁(yè)，共45頁(yè)。正確處理文本(wnbn)(wnbn)和數(shù)字 如何將文本型數(shù)字轉(zhuǎn)換為純數(shù)字 有些情況下，從數(shù)據(jù)庫(kù)導(dǎo)入的數(shù)字是文本型數(shù)字。文本型數(shù)字無(wú)法使用SUM等函數(shù)進(jìn)行計(jì)算，需要轉(zhuǎn)換為純數(shù)

8、字。 方法1：利用智能標(biāo)記(bioj) 方法2：利用VALUE函數(shù) 方法3：利用公式（兩個(gè)負(fù)號(hào)或者乘以1或除以1） 方法4：利用選擇性粘貼的批量修改功能（乘以1或者除以1） 方法5：利用分列工具(只能一列一列改） 如何把數(shù)字轉(zhuǎn)換為文本型數(shù)字? 使用分列工具 使用TEXT函數(shù)第15頁(yè)/共44頁(yè)第十五頁(yè)，共45頁(yè)。 使用(shyng)(shyng)數(shù)據(jù)有效性控制規(guī)范數(shù)據(jù)輸入 利用有效性，可以控制規(guī)范數(shù)據(jù)輸入，快速準(zhǔn)確輸入數(shù)據(jù)，讓別人按照(nzho)您的要求填列數(shù)據(jù)。 比如： 只能輸入規(guī)定格式的數(shù)據(jù) 只能輸入規(guī)定的序列數(shù)據(jù) 只有在上一行所有單元格都輸入數(shù)據(jù)后，才能在下一行輸入新的數(shù)據(jù) 多重限制輸入數(shù)

9、據(jù) 不允許輸入重復(fù)數(shù)據(jù) 案例03 有效性典型應(yīng)用第16頁(yè)/共44頁(yè)第十六頁(yè)，共45頁(yè)。其他數(shù)據(jù)整理(zhngl)(zhngl)技巧 批量修改數(shù)據(jù) 轉(zhuǎn)置數(shù)據(jù)位置 復(fù)制(fzh)可見(jiàn)單元格數(shù)據(jù) 批量插入空行和空列 批量填充單元格 第17頁(yè)/共44頁(yè)第十七頁(yè)，共45頁(yè)。 使用基本(jbn)(jbn)的函數(shù)獲取基本(jbn)(jbn)數(shù)據(jù) 使用有關(guān)(yugun)的查找函數(shù)，可以快速準(zhǔn)確輸入基本資料，避免復(fù)制粘貼等手工操作。 常用的函數(shù)有 查找函數(shù)VLOOKUP 分類匯總函數(shù)SUMIF、SUMPRODUCT 文本函數(shù)LEFT、RIGHT、MID、TEXT 日期函數(shù)TODAY、EDATE、DATEDIF

10、案例練習(xí) 案例04 使用函數(shù)輸入數(shù)據(jù)第18頁(yè)/共44頁(yè)第十八頁(yè)，共45頁(yè)。使用條件格式(g shi)(g shi)自動(dòng)美化表格 使用(shyng)條件格式，可以自動(dòng)美化表格，既便于快速輸入數(shù)據(jù)，有使表格干凈美觀。 案例練習(xí) 案例05 條件格式美化表格第19頁(yè)/共44頁(yè)第十九頁(yè)，共45頁(yè)。創(chuàng)建列表自動(dòng)(zdng)(zdng)復(fù)制公式 使用列表工具，不僅可以自動(dòng)建立( jinl)篩選，還可以自動(dòng)復(fù)制公式、格式、有效性等。 案例練習(xí) 案例06 使用列表自動(dòng)復(fù)制公式第20頁(yè)/共44頁(yè)第二十頁(yè)，共45頁(yè)。三、快速掌握(zhngw)實(shí)用的Excel函數(shù) 1、函數(shù)基礎(chǔ) 2、處理(chl)日期常用的3個(gè)函數(shù)

11、3、處理(chl)文本常用的4個(gè)函數(shù) 4、邏輯判斷常用的4個(gè)函數(shù) 5、分類匯總常用的3個(gè)函數(shù) 6、數(shù)據(jù)查找常用的4個(gè)函數(shù) 7、函數(shù)綜合練習(xí)第21頁(yè)/共44頁(yè)第二十一頁(yè)，共45頁(yè)。函數(shù)(hnsh)基礎(chǔ) 創(chuàng)建公式（以“”開(kāi)始，可以包括(boku)：運(yùn)算符、單元格引用、值或常量以及工作表函數(shù)。） 公式中的運(yùn)算符 例如: （求冪）、 &（文本連接運(yùn)算符）、 （不等于）等 公式實(shí)例： =150*.01 =A1+A2 =SUM(A1:A2) =A1=C12 =(B2-B3)*B4 一個(gè)公式最多可以容納1024個(gè)字符第22頁(yè)/共44頁(yè)第二十二頁(yè)，共45頁(yè)。函數(shù)(hnsh)基礎(chǔ) 絕對(duì)引用/相對(duì)引用（演示(yn

12、sh)） 引用（說(shuō)明） 更改為 $A$1（絕對(duì) 列和絕對(duì)行） $A$1 A$1（相對(duì) 列和絕對(duì)行） C$1 $A1（絕對(duì)列和相對(duì)行） $A3 A1（相對(duì)列和相對(duì)行） C3第23頁(yè)/共44頁(yè)第二十三頁(yè)，共45頁(yè)。函數(shù)(hnsh)基礎(chǔ)錯(cuò)誤值原因說(shuō)明#該列寬度不夠，或者包含一個(gè)無(wú)效時(shí)間或日期#DIV/0!該公式使用了0作為除數(shù)，或者公式中使用了一個(gè)空單元格#N/A公式中引用的數(shù)據(jù)對(duì)函數(shù)或公式不可用#NAME?公式中使用了Excel不能辨認(rèn)的文本或名稱#NULL!公式中使用了一種不允許出現(xiàn)相交卻交叉了的兩個(gè)區(qū)域#NUM!使用了無(wú)效的數(shù)字值#REF!公式引用了一個(gè)無(wú)效的單元格#VALUE!函數(shù)中使用的變

13、量或參數(shù)類型錯(cuò)誤 函數(shù)使用(shyng)常見(jiàn)的錯(cuò)誤第24頁(yè)/共44頁(yè)第二十四頁(yè)，共45頁(yè)。處理日期常用(chn yn)(chn yn)的3 3個(gè)函數(shù)3個(gè)重要的日期函數(shù)TODAY函數(shù)：獲取當(dāng)天日期EDATE函數(shù)：計(jì)算到期日DATEDIF函數(shù)：計(jì)算期限案例練習(xí)(linx)案例07 計(jì)算新車(chē)庫(kù)存成本案例08 計(jì)算合同到期日第25頁(yè)/共44頁(yè)第二十五頁(yè)，共45頁(yè)。 處理文本(wnbn)(wnbn)常用的4 4個(gè)函數(shù)4個(gè)重要的文本(wnbn)函數(shù)LEFTRIGHTMIDTEXT案例練習(xí)案例04 使用函數(shù)輸入數(shù)據(jù)第26頁(yè)/共44頁(yè)第二十六頁(yè)，共45頁(yè)。邏輯判斷(pndun)(pndun)常用的4 4個(gè)函數(shù)

14、4個(gè)重要的邏輯判斷函數(shù)IF函數(shù)（嵌套IF）AND函數(shù)OR函數(shù)ISERROR函數(shù)（IFERROR函數(shù)）案例練習(xí)(linx)案例09 計(jì)算工齡工資第27頁(yè)/共44頁(yè)第二十七頁(yè)，共45頁(yè)。分類匯總(huzng)(huzng)常用的3 3個(gè)函數(shù)3個(gè)重要的分類匯總(huzng)函數(shù)SUMIFSUMIFSSUMPRODUCT案例練習(xí)案例10 分類匯總(huzng)函數(shù)基本練習(xí)第28頁(yè)/共44頁(yè)第二十八頁(yè)，共45頁(yè)。數(shù)據(jù)(shj)(shj)查找常用的4 4個(gè)函數(shù)4個(gè)重要的查找引用(ynyng)函數(shù)VLOOKUP函數(shù)MATCH函數(shù)INDEX函數(shù)INDIRECT函數(shù)案例練習(xí)案例11 VLOOKUP函數(shù)應(yīng)用案例1

15、2 查找函數(shù)高級(jí)應(yīng)用第29頁(yè)/共44頁(yè)第二十九頁(yè)，共45頁(yè)。函數(shù)綜合(zngh)(zngh)練習(xí)針對(duì)從系統(tǒng)導(dǎo)出的維修記錄表，設(shè)計(jì)一個(gè)動(dòng)態(tài)的查詢系統(tǒng)，可以：任選年份(ninfn)任選月份任選日期使用技能和函數(shù)滾動(dòng)查找技術(shù)MATCH函數(shù)INDEX函數(shù)INDIRECT函數(shù)IF函數(shù)ISERROR函數(shù)第30頁(yè)/共44頁(yè)第三十頁(yè)，共45頁(yè)。 四、快速(kui s)掌握Excel數(shù)據(jù)透視表 1、什么(shn me)是數(shù)據(jù)透視表 2、制作數(shù)據(jù)透視表的基本方法 3、了解數(shù)據(jù)透視表的結(jié)構(gòu) 4、數(shù)據(jù)透視表的美化 5、利用數(shù)據(jù)透視表分析數(shù)據(jù) 6、數(shù)據(jù)透視表的特殊應(yīng)用第31頁(yè)/共44頁(yè)第三十一頁(yè)，共45頁(yè)。什么(shn

16、 me)(shn me)是數(shù)據(jù)透視表什么是數(shù)據(jù)透視表？一種動(dòng)態(tài)的匯總分析報(bào)表(bobio)數(shù)據(jù)透視表能做什么？分類匯總多維度統(tǒng)計(jì)分析匯總大量工作表制作明細(xì)表高效數(shù)據(jù)查詢對(duì)比分析怎樣靈活使用數(shù)據(jù)透視表？制作正確的數(shù)據(jù)透視表掌握數(shù)據(jù)透視表的各種功能第32頁(yè)/共44頁(yè)第三十二頁(yè)，共45頁(yè)。 制作(zhzu)(zhzu)數(shù)據(jù)透視表的基本方法基本步驟Step1：標(biāo)準(zhǔn)化規(guī)范化基礎(chǔ)表格Step2：采用合適的方法(fngf)制作數(shù)據(jù)透視表Step3：布局透視表，完成報(bào)告第33頁(yè)/共44頁(yè)第三十三頁(yè)，共45頁(yè)。了解(lioji)(lioji)數(shù)據(jù)透視表的結(jié)構(gòu)行字段：是分類(fn li)字段，是指在數(shù)據(jù)透視表中被

17、指定為行方向的字段。列字段：是分類(fn li)字段，是指在數(shù)據(jù)透視表中被指定為列方向的字段。頁(yè)字段：是篩選字段，允許篩選整個(gè)數(shù)據(jù)透視表。值字段：是匯總計(jì)算字段。 第34頁(yè)/共44頁(yè)第三十四頁(yè)，共45頁(yè)。 數(shù)據(jù)透視(tush)(tush)表的美化 在數(shù)據(jù)透視表上對(duì)數(shù)據(jù)透視表進(jìn)行重新布局 修改字段名稱 設(shè)置字段的數(shù)字(shz)格式 取消數(shù)據(jù)透視表的行或列匯總 取消字段的分類匯總 讓數(shù)據(jù)透視表數(shù)據(jù)按照某一字段進(jìn)行排序 合并數(shù)據(jù)標(biāo)志 設(shè)置錯(cuò)誤值的顯示方式 更新數(shù)據(jù)透視表的數(shù)據(jù)第35頁(yè)/共44頁(yè)第三十五頁(yè)，共45頁(yè)。利用(lyng)(lyng)數(shù)據(jù)透視表分析數(shù)據(jù) 基本方法： 布局透視表 設(shè)置字段的分類

18、匯總方式(fngsh) 設(shè)置字段的顯示方式(fngsh) 自定義數(shù)據(jù)透視表 組合字段 制作明細(xì)表 案例練習(xí) 案例13 數(shù)據(jù)透視表綜合分析第36頁(yè)/共44頁(yè)第三十六頁(yè)，共45頁(yè)。數(shù)據(jù)(shj)(shj)透視表的特殊應(yīng)用快速合并大量的工作表 多個(gè)二維表格合并計(jì)算案例(n l)14 全年管理費(fèi)用匯總分析快速對(duì)比兩個(gè)表格案例(n l)15 快速核對(duì)社保第37頁(yè)/共44頁(yè)第三十七頁(yè)，共45頁(yè)。五、從原始數(shù)據(jù)到分析(fnx)圖表 制作圖表的基本方法 使用組合圖表分析深層次信息 使用動(dòng)態(tài)圖表靈活(ln hu)分析數(shù)據(jù)第38頁(yè)/共44頁(yè)第三十八頁(yè)，共45頁(yè)。制作圖表的基本(jbn)(jbn)方法兩種基本的繪圖方法直接命令法手工添加法案例練習(xí)案例16 繪制(huzh)圖表基本練習(xí)第39頁(yè)/共44頁(yè)第三十九頁(yè)，共45頁(yè)。使用組合圖表(tbio)(tbio)分析深層次信息組合圖表：通過(guò)數(shù)據(jù)的重新調(diào)整，或者通過(guò)圖表的格式設(shè)置，所制作的特殊分析圖表。常用方法輔助繪圖區(qū)域法多圖表組合法設(shè)置漲跌柱線、高低點(diǎn)連線、趨勢(shì)線案例(n l)練習(xí)案例(n l)17 同比分析圖案例(n l)18