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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

15、2 查找函數(shù)高級應用第29頁/共44頁第二十九頁，共45頁。函數(shù)綜合(zngh)(zngh)練習針對從系統(tǒng)導出的維修記錄表，設計一個動態(tài)的查詢系統(tǒng)，可以：任選年份(ninfn)任選月份任選日期使用技能和函數(shù)滾動查找技術MATCH函數(shù)INDEX函數(shù)INDIRECT函數(shù)IF函數(shù)ISERROR函數(shù)第30頁/共44頁第三十頁，共45頁。 四、快速(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ù)透視表的特殊應用第31頁/共44頁第三十一頁，共45頁。什么(shn

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

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

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