Excel高效數(shù)據(jù)分析

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

2、原則、基本原則 4 4、設(shè)計(jì)模版的基本步驟、設(shè)計(jì)模版的基本步驟 5 5、財(cái)務(wù)分析模版設(shè)計(jì)應(yīng)考慮的幾個(gè)問題、財(cái)務(wù)分析模版設(shè)計(jì)應(yīng)考慮的幾個(gè)問題 6 6、正確處理基礎(chǔ)表格與報(bào)告表格、正確處理基礎(chǔ)表格與報(bào)告表格 7 7、如何學(xué)習(xí)、如何學(xué)習(xí)ExcelExcel精細(xì)化管理精細(xì)化管理日日 常常 控控 制制科科 學(xué)學(xué) 決決 策策企業(yè)經(jīng)營報(bào)表企業(yè)經(jīng)營報(bào)表溝通溝通控制控制決策決策業(yè)績評價(jià)業(yè)績評價(jià)等等等等 Excel Excel是什么是什么6工作設(shè)計(jì)標(biāo)準(zhǔn)化技能技能Stpe3成果模版Step4理念Step1思想 基本原則基本原則 從原始的表單最終的報(bào)告 盡量使用原始數(shù)據(jù) 盡量減少中間環(huán)節(jié) 簡潔的報(bào)告 高效的公式設(shè)計(jì)模

3、版的基本步驟設(shè)計(jì)模版的基本步驟 Step1：獲取數(shù)據(jù) 分析數(shù)據(jù)的來源（手工表格；導(dǎo)入表格） 源數(shù)據(jù)的結(jié)構(gòu)（單表數(shù)據(jù)，多表數(shù)據(jù)） Step2：表格結(jié)構(gòu)設(shè)計(jì) 基礎(chǔ)表格 報(bào)告表格 Step3：呈現(xiàn)方式 Excel表格形式 Word書面形式 PPT演示形式財(cái)務(wù)分析模版設(shè)計(jì)應(yīng)考慮的財(cái)務(wù)分析模版設(shè)計(jì)應(yīng)考慮的幾個(gè)問題幾個(gè)問題 設(shè)計(jì)財(cái)務(wù)分析模版應(yīng)考慮的幾個(gè)問題設(shè)計(jì)財(cái)務(wù)分析模版應(yīng)考慮的幾個(gè)問題 層次結(jié)構(gòu)：層次結(jié)構(gòu)： 單層結(jié)構(gòu)還是多層結(jié)構(gòu)？ 布局方向：布局方向： 橫向布局還是縱向布局？ 動(dòng)與靜：動(dòng)與靜： 靜態(tài)分析還是滾動(dòng)分析？ 靈活性：靈活性： 分析項(xiàng)目是一種還是多種？ 直觀性：直觀性： 圖形展示分析結(jié)果 整合性

4、：整合性： 要不要與財(cái)務(wù)軟件整合起來？ 報(bào)告性：報(bào)告性： 是否做成一個(gè)最終的匯報(bào)性的報(bào)告？ 案例解析 案例01 單表分析模版演示案例 案例02 多表分析模版演示案例 正確處理基礎(chǔ)表格與報(bào)告表格正確處理基礎(chǔ)表格與報(bào)告表格 Excel的兩類表格 基礎(chǔ)表格 按照數(shù)據(jù)的功能來分別保存在不同的工作表 結(jié)構(gòu)越簡單越好 報(bào)告表格 要考慮匯總過程的效率性和方便性，分析結(jié)果的呈現(xiàn)性 結(jié)構(gòu)越清楚越好 設(shè)計(jì)表格應(yīng)遵循的總體原則： 結(jié)構(gòu)的科學(xué)性 數(shù)據(jù)的易讀性 匯總的便宜性 分析的靈活性 外觀的美觀性如何學(xué)習(xí)如何學(xué)習(xí)ExcelExcel Excel一定要結(jié)合企業(yè)管理來學(xué)習(xí) 從最初的表單設(shè)計(jì)，一直到最終報(bào)表，都需要按照企

5、業(yè)精細(xì)化管理的要求來進(jìn)行 必須掌握Excel的實(shí)用技能 高效辦公技能 數(shù)據(jù)跟蹤技能 分類匯總技能 鏈接查詢技能 合并工作表技能 數(shù)據(jù)挖掘技能 圖形展示技能 自動(dòng)化技能二、設(shè)計(jì)標(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ù) 5 、使用條件格式自動(dòng)美化表格 6 、創(chuàng)建列表自動(dòng)復(fù)制公式 7 、設(shè)計(jì)表格綜合練習(xí) 標(biāo)準(zhǔn)化表格解析標(biāo)準(zhǔn)化表格解析 標(biāo)準(zhǔn)化表格的基本要求 數(shù)據(jù)庫結(jié)構(gòu) 結(jié)構(gòu)最簡單 填寫數(shù)據(jù)方便 沒有合并單元格 沒有空行和空列 沒有不必要的小計(jì)和總計(jì) 沒有不必要的公式 數(shù)據(jù)規(guī)范表格數(shù)據(jù)的整理與規(guī)范表格數(shù)

6、據(jù)的整理與規(guī)范 很多表格中的數(shù)據(jù)是非法的、錯(cuò)誤的、不規(guī)范的，需要進(jìn)行整理規(guī)范和標(biāo)準(zhǔn)化。 正確處理日期數(shù)據(jù) 正確處理文本和數(shù)字正確處理日期數(shù)據(jù)正確處理日期數(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)入的日期是文本格式 如何把日期顯示為需要的格式？使用“分列”功能 修改非法日期正確處理文本和數(shù)字正確處理文本和數(shù)字 如何將文本型數(shù)字轉(zhuǎn)換為純數(shù)字 有些情

7、況下，從數(shù)據(jù)庫導(dǎo)入的數(shù)字是文本型數(shù)字。文本型數(shù)字無法使用SUM等函數(shù)進(jìn)行計(jì)算，需要轉(zhuǎn)換為純數(shù)字。 方法1：利用智能標(biāo)記 方法2：利用VALUE函數(shù) 方法3：利用公式（兩個(gè)負(fù)號或者乘以1或除以1） 方法4：利用選擇性粘貼的批量修改功能（乘以1或者除以1） 方法5：利用分列工具(只能一列一列改） 如何把數(shù)字轉(zhuǎn)換為文本型數(shù)字? 使用分列工具 使用TEXT函數(shù) 使用數(shù)據(jù)有效性控制規(guī)范數(shù)據(jù)輸入使用數(shù)據(jù)有效性控制規(guī)范數(shù)據(jù)輸入 利用有效性，可以控制規(guī)范數(shù)據(jù)輸入，快速準(zhǔn)確輸入數(shù)據(jù)，讓別人按照您的要求填列數(shù)據(jù)。 比如： 只能輸入規(guī)定格式的數(shù)據(jù) 只能輸入規(guī)定的序列數(shù)據(jù) 只有在上一行所有單元格都輸入數(shù)據(jù)后，才能在下

8、一行輸入新的數(shù)據(jù) 多重限制輸入數(shù)據(jù) 不允許輸入重復(fù)數(shù)據(jù) 案例03 有效性典型應(yīng)用其他數(shù)據(jù)整理技巧其他數(shù)據(jù)整理技巧 批量修改數(shù)據(jù) 轉(zhuǎn)置數(shù)據(jù)位置 復(fù)制可見單元格數(shù)據(jù) 批量插入空行和空列 批量填充單元格 使用基本的函數(shù)獲取基本數(shù)據(jù)使用基本的函數(shù)獲取基本數(shù)據(jù) 使用有關(guān)的查找函數(shù)，可以快速準(zhǔn)確輸入基本資料，避免復(fù)制粘貼等手工操作。 常用的函數(shù)有 查找函數(shù)VLOOKUP 分類匯總函數(shù)SUMIF、SUMPRODUCT 文本函數(shù)LEFT、RIGHT、MID、TEXT 日期函數(shù)TODAY、EDATE、DATEDIF 案例練習(xí) 案例04 使用函數(shù)輸入數(shù)據(jù)使用條件格式自動(dòng)美化表格使用條件格式自動(dòng)美化表格 使用條件格

9、式，可以自動(dòng)美化表格，既便于快速輸入數(shù)據(jù)，有使表格干凈美觀。 案例練習(xí) 案例05 條件格式美化表格創(chuàng)建列表自動(dòng)復(fù)制公式創(chuàng)建列表自動(dòng)復(fù)制公式 使用列表工具，不僅可以自動(dòng)建立篩選，還可以自動(dòng)復(fù)制公式、格式、有效性等。 案例練習(xí) 案例06 使用列表自動(dòng)復(fù)制公式三、快速掌握實(shí)用的Excel函數(shù) 1、函數(shù)基礎(chǔ) 2、處理日期常用的3個(gè)函數(shù) 3、處理文本常用的4個(gè)函數(shù) 4、邏輯判斷常用的4個(gè)函數(shù) 5、分類匯總常用的3個(gè)函數(shù) 6、數(shù)據(jù)查找常用的4個(gè)函數(shù) 7、函數(shù)綜合練習(xí)函數(shù)基礎(chǔ)創(chuàng)建公式（以“”開始，可以包括：運(yùn)算符、單元格引用、值或常量以及工作表函數(shù)。）公式中的運(yùn)算符 例如: （求冪）、 &（文本連接運(yùn)算符）

10、、 （不等于）等公式實(shí)例：=150*.01=A1+A2=SUM(A1:A2)=A1=C12=(B2-B3)*B4一個(gè)公式最多可以容納1024個(gè)字符函數(shù)基礎(chǔ) 絕對引用/相對引用（演示）引用（說明）引用（說明） 更改為更改為 $A$1（絕對 列和絕對行） $A$1A$1（相對 列和絕對行） C$1$A1（絕對列和相對行） $A3A1（相對列和相對行） C3函數(shù)基礎(chǔ)錯(cuò)誤值原因說明#該列寬度不夠，或者包含一個(gè)無效時(shí)間或日期#DIV/0!該公式使用了0作為除數(shù)，或者公式中使用了一個(gè)空單元格#N/A公式中引用的數(shù)據(jù)對函數(shù)或公式不可用#NAME?公式中使用了Excel不能辨認(rèn)的文本或名稱#NULL!公式中使

11、用了一種不允許出現(xiàn)相交卻交叉了的兩個(gè)區(qū)域#NUM!使用了無效的數(shù)字值#REF!公式引用了一個(gè)無效的單元格#VALUE!函數(shù)中使用的變量或參數(shù)類型錯(cuò)誤函數(shù)使用常見的錯(cuò)誤處理日期常用的處理日期常用的3 3個(gè)函數(shù)個(gè)函數(shù)3個(gè)重要的日期函數(shù)TODAY函數(shù)：獲取當(dāng)天日期EDATE函數(shù)：計(jì)算到期日DATEDIF函數(shù)：計(jì)算期限案例練習(xí)案例07 計(jì)算新車庫存成本案例08 計(jì)算合同到期日 處理文本常用的處理文本常用的4 4個(gè)函數(shù)個(gè)函數(shù)4個(gè)重要的文本函數(shù)LEFTRIGHTMIDTEXT案例練習(xí)案例04 使用函數(shù)輸入數(shù)據(jù)邏輯判斷常用的邏輯判斷常用的4 4個(gè)函數(shù)個(gè)函數(shù)4個(gè)重要的邏輯判斷函數(shù)IF函數(shù)（嵌套IF）AND函數(shù)

12、OR函數(shù)ISERROR函數(shù)（IFERROR函數(shù)）案例練習(xí)案例09 計(jì)算工齡工資分類匯總常用的分類匯總常用的3 3個(gè)函數(shù)個(gè)函數(shù)3個(gè)重要的分類匯總函數(shù)SUMIFSUMIFSSUMPRODUCT案例練習(xí)案例10 分類匯總函數(shù)基本練習(xí)數(shù)據(jù)查找常用的數(shù)據(jù)查找常用的4 4個(gè)函數(shù)個(gè)函數(shù)4個(gè)重要的查找引用函數(shù)VLOOKUP函數(shù)MATCH函數(shù)INDEX函數(shù)INDIRECT函數(shù)案例練習(xí)案例11 VLOOKUP函數(shù)應(yīng)用案例12 查找函數(shù)高級應(yīng)用函數(shù)綜合練習(xí)函數(shù)綜合練習(xí)針對從系統(tǒng)導(dǎo)出的維修記錄表，設(shè)計(jì)一個(gè)動(dòng)態(tài)的查詢系統(tǒng)，可以：任選年份任選月份任選日期使用技能和函數(shù)滾動(dòng)查找技術(shù)MATCH函數(shù)INDEX函數(shù)INDIREC

13、T函數(shù)IF函數(shù)ISERROR函數(shù) 四、快速掌握Excel數(shù)據(jù)透視表 1、什么是數(shù)據(jù)透視表 2、制作數(shù)據(jù)透視表的基本方法 3、了解數(shù)據(jù)透視表的結(jié)構(gòu) 4、數(shù)據(jù)透視表的美化 5、利用數(shù)據(jù)透視表分析數(shù)據(jù) 6、數(shù)據(jù)透視表的特殊應(yīng)用什么是數(shù)據(jù)透視表什么是數(shù)據(jù)透視表什么是數(shù)據(jù)透視表？一種動(dòng)態(tài)的匯總分析報(bào)表數(shù)據(jù)透視表能做什么？分類匯總多維度統(tǒng)計(jì)分析匯總大量工作表制作明細(xì)表高效數(shù)據(jù)查詢對比分析怎樣靈活使用數(shù)據(jù)透視表？制作正確的數(shù)據(jù)透視表掌握數(shù)據(jù)透視表的各種功能 制作數(shù)據(jù)透視表的基本方法制作數(shù)據(jù)透視表的基本方法 基本步驟 Step1：標(biāo)準(zhǔn)化規(guī)范化基礎(chǔ)表格 Step2：采用合適的方法制作數(shù)據(jù)透視表 Step3：布局

14、透視表，完成報(bào)告了解數(shù)據(jù)透視表的結(jié)構(gòu)了解數(shù)據(jù)透視表的結(jié)構(gòu)行字段：是分類字段，是指在數(shù)據(jù)透視表中被指定為行方向的字段。列字段：是分類字段，是指在數(shù)據(jù)透視表中被指定為列方向的字段。頁字段：是篩選字段，允許篩選整個(gè)數(shù)據(jù)透視表。值字段：是匯總計(jì)算字段。 數(shù)據(jù)透視表的美化數(shù)據(jù)透視表的美化 在數(shù)據(jù)透視表上對數(shù)據(jù)透視表進(jìn)行重新布局 修改字段名稱 設(shè)置字段的數(shù)字格式 取消數(shù)據(jù)透視表的行或列匯總 取消字段的分類匯總 讓數(shù)據(jù)透視表數(shù)據(jù)按照某一字段進(jìn)行排序 合并數(shù)據(jù)標(biāo)志 設(shè)置錯(cuò)誤值的顯示方式 更新數(shù)據(jù)透視表的數(shù)據(jù)利用數(shù)據(jù)透視表分析數(shù)據(jù)利用數(shù)據(jù)透視表分析數(shù)據(jù) 基本方法： 布局透視表 設(shè)置字段的分類匯總方式 設(shè)置字段的

15、顯示方式 自定義數(shù)據(jù)透視表 組合字段 制作明細(xì)表 案例練習(xí) 案例13 數(shù)據(jù)透視表綜合分析數(shù)據(jù)透視表的特殊應(yīng)用數(shù)據(jù)透視表的特殊應(yīng)用 快速合并大量的工作表 多個(gè)二維表格合并計(jì)算案例14 全年管理費(fèi)用匯總分析 快速對比兩個(gè)表格 案例15 快速核對社保五、從原始數(shù)據(jù)到分析圖表 制作圖表的基本方法 使用組合圖表分析深層次信息 使用動(dòng)態(tài)圖表靈活分析數(shù)據(jù)制作圖表的基本方法制作圖表的基本方法 兩種基本的繪圖方法 直接命令法 手工添加法 案例練習(xí) 案例16 繪制圖表基本練習(xí)使用組合圖表分析深層次信息使用組合圖表分析深層次信息組合圖表：通過數(shù)據(jù)的重新調(diào)整，或者通過圖表的格式設(shè)置，所制作的特殊分析圖表。常用方法輔助繪圖區(qū)域法多圖表組合法設(shè)置漲跌柱線、高低點(diǎn)連線、趨勢線案例練習(xí)案例17 同比分析圖案例18 價(jià)格變化趨勢圖案例19 差異分析圖案例20 資金流動(dòng)圖使用動(dòng)態(tài)圖表靈活分析數(shù)據(jù)使用動(dòng)態(tài)圖表靈活分析數(shù)據(jù)動(dòng)態(tài)圖表可以按照圖表使用者任意顯示的圖表動(dòng)態(tài)圖表的基本原理使用控件控制單元格數(shù)據(jù)，進(jìn)而控制圖表顯示需要掌握的基本技能窗體控件（表單控件）的使用方法查找函