本課程為工具類培訓(xùn)，面向所有想學(xué)數(shù)據(jù)分析工具的人員。

本課程的主要目的是，幫助學(xué)員掌握Excel數(shù)據(jù)分析工具，學(xué)會數(shù)據(jù)預(yù)處理，簡化工作量，提升效率，避免重復(fù)工作；學(xué)會數(shù)據(jù)可視化，直觀呈現(xiàn)各種業(yè)務(wù)信息和含義。

通過本課程的學(xué)習(xí)，達(dá)到如下目的：

1、  熟悉Power Query數(shù)據(jù)預(yù)處理操作，能夠處理各種數(shù)據(jù)源

2、  掌握透視表，以及高級的統(tǒng)計函數(shù)，實(shí)現(xiàn)數(shù)據(jù)匯總和計算

3、  掌握Excel可視化功能，及制圖技巧，制作美觀的圖形

4、  理解動態(tài)圖表原理，掌握動態(tài)圖表的制作

5、  熟悉Excel高級函數(shù)的應(yīng)用

6、  熟悉模擬運(yùn)算、規(guī)劃求解等高級功能

第一部分：  數(shù)據(jù)分析基礎(chǔ)

1、 數(shù)據(jù)分析的六步曲

‐  明確目的、收集數(shù)據(jù)（需要人員參與）

‐  整理數(shù)據(jù)、分析數(shù)據(jù)、數(shù)據(jù)可視化（可以借助工具）

‐  形成結(jié)論（需要人員參與）

2、 Excel的主要功能

‐  數(shù)據(jù)讀取、保存、共享

‐  數(shù)據(jù)預(yù)處理（整理、清洗、集成、派生等）

‐  數(shù)據(jù)計算（匯總、統(tǒng)計、透視表等）

‐  數(shù)據(jù)可視化（圖表）

第二部分：  Excel數(shù)據(jù)處理

1、 傳統(tǒng)工具最大問題：重復(fù)工作

2、 數(shù)據(jù)預(yù)處理的四大任務(wù)

‐  數(shù)據(jù)集成、數(shù)據(jù)清洗、樣本處理、變量處理

1、 Power Query高級功能

2、 多數(shù)據(jù)源讀取

演練：從文本/Excel/數(shù)據(jù)庫/Web頁獲取數(shù)據(jù)源

3、 數(shù)據(jù)組合/數(shù)據(jù)集成

‐  樣本追加：橫向合并

‐  字段合并：縱向合并/連接類型

‐  表連接的類型（左/右/完全外連接、內(nèi)連接、左反/右反連接）

演練3：多表合并，實(shí)現(xiàn)在職、離職、新入職員工名單

4、 數(shù)據(jù)預(yù)處理

‐  文件夾合并

演練1：文件夾下多文件單工作表合并

演練2：文件夾下多文件多工作表合并

‐  多工作表合并

演練1：單文件多個規(guī)范工作表合并

演練2：單文件多個不規(guī)范工作表合并

3、 異常數(shù)據(jù)處理

‐  重復(fù)值處理

‐  缺失值處理

‐  無效值/錯誤值

‐  離群值處理

4、 Power Query數(shù)據(jù)預(yù)處理實(shí)戰(zhàn)

‐  文本字段拆分列/拆分行（按分隔符、字符數(shù)、非數(shù)字到數(shù)字等）

‐  展開列表為行/分組依據(jù)

‐  去重/排序/跳過行/第一行作標(biāo)題

‐  新增列/新增索引/新增條件列

‐  文本提取/文本移除/文本轉(zhuǎn)數(shù)字

演練1：銷售人員績效自動排名

演練2：借貸憑證數(shù)據(jù)處理

演練3：采購計劃處理（多表合并處理）

演練4：文本數(shù)字混合計算金額

5、 排序和篩選

‐  排序依據(jù)、順序

演練：學(xué)歷如何排序（自定義順序）

‐  篩選：簡單篩選、復(fù)合篩選、自定義篩選

第三部分：  Excel條件格式

1、 條件格式：根據(jù)條件來設(shè)置單元格的格式

2、 條件規(guī)則類型及條件

‐  根據(jù)值條件、值排名設(shè)置條件

‐  根據(jù)內(nèi)容條件

‐  根據(jù)公式自定義條件

3、 設(shè)置格式

‐  字體、填充、邊框、對齊等等

‐  已有樣式：數(shù)據(jù)條、色階、圖標(biāo)集

演練：單元格自行添加邊框

演練：高亮顯示選中內(nèi)容的單元格

演練：自動添加箭頭顯示同比/環(huán)比變化

4、 表格美化技巧

‐  突出顯示

‐  前后項(xiàng)突出

‐  數(shù)據(jù)條

‐  色階

‐  圖標(biāo)集

‐  迷你圖

5、 自定義格式：<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

6、 Text函數(shù)改變數(shù)據(jù)格式

第四部分：  Excel高級函數(shù)

1、 Excel常用函數(shù)種類

2、 文本函數(shù)

a)   查找：Find/Search

b)   替換：Substitue/Replace

c)   提?。?span>Left/Right/Mid/TextBefore/TextAfter

d)   合并：Concat/TextJoin/&

e)   分割：TextSplit

演練：身份證號提取籍貫、出生日期、性別

演練：住址省市區(qū)縣鄉(xiāng)鎮(zhèn)街道等拆分

3、 邏輯函數(shù)

a)   條件判斷：IF/IFS

b)   邏輯運(yùn)算：AND/OR/NOT

c)   函數(shù)計算：Let/Lambda/Map/Reduce/ByCol/ByRow

d)   名稱管理器

演練：自定義函數(shù)及調(diào)用

4、 信息函數(shù)

‐  ISBLANK/ISERROR/ISNA

‐  ISNUMBER/ISTEXT/ISFORMULA/ISREF

‐  ISODD/ISEVEN/ISLOGICAL

5、 查找和引用函數(shù)

a)   值查找：VLookup/HLookup/XLookup

b)   序號查找：Match/XMatch

c)   條件篩選：Filter

d)   提取數(shù)據(jù)：Take/Drop/ChooseCols/ChooseRows

e)   引用函數(shù)：Index/Offset/Indirect

演練：銷量數(shù)據(jù)查找

演練：銷量數(shù)據(jù)篩選

演練：下拉菜單、多級下拉聯(lián)動菜單

6、 統(tǒng)計函數(shù)

a)   計數(shù)：Count/CountA/CountBlank/CountIF/CountIFS

b)   均值：Average/AverageIF/AverageIFS

c)   極值：Max/Min/Large/Small

d)   分類統(tǒng)計：GroupBy/PivotBy

演練：銷量統(tǒng)計、分類統(tǒng)計

7、 數(shù)學(xué)和三角函數(shù)

a)   匯總：Sum/SubTotal

b)   條件匯總：SumIF/SumIFS/SumProduct

8、 日期和時間函數(shù)

a)   時間提?。?span>Year/Month/Day/Hour/Minute/Second/Weekday

b)   時間間隔：YearFrac/Days/Networkdays/Workday

9、 正則表達(dá)式

演練：電話號碼提取、費(fèi)用數(shù)字提取

10、     分級顯示: 組合/取消組合

第五部分：  Excel模擬運(yùn)算

1、 單變量求解

2、 模擬運(yùn)算表

3、 方案管理器

第六部分：  Excel規(guī)劃求解

1、 規(guī)劃求解工具介紹

a)   求取極值的最優(yōu)輸入

b)   約束條件使用

c)   全局最優(yōu)及局部最優(yōu)

2、 如何實(shí)現(xiàn)產(chǎn)品最優(yōu)定價

演練：利潤最大化下的產(chǎn)品生產(chǎn)與分配

演戲：成本最小化下的產(chǎn)品組成配比

第七部分： Excel數(shù)據(jù)分析

1、 數(shù)據(jù)統(tǒng)計常用工具

‐  透視表

‐  數(shù)據(jù)分析

2、 透視表原理與操作技巧

‐  匯總方式

‐  值顯示方式：無、百分比、差異等

‐  創(chuàng)建動態(tài)透視表

3、 透視表高級功能

a)   三種布局

b)   值顯示方式：百分比、排名等

c)   總計/小計的顯示

d)   合并類別項(xiàng)

e)   分層分級透視表

演練：銷售分析報表

4、 數(shù)據(jù)分析

‐  描述統(tǒng)計

‐  相關(guān)分析（原因分析）

‐  方差分析（影響因素分析）

‐  回歸分析（多因素關(guān)鍵探索與預(yù)測）

演練：影響消費(fèi)水平的因素分析

演練：影響銷量的關(guān)鍵要素分析

第八部分： Excel可視化

1、 數(shù)據(jù)呈現(xiàn)的兩種格式：圖與表

2、 常用圖形類型及選擇原則

‐  對比：柱狀圖、條形圖

‐  趨勢：折線圖

‐  分布：直方圖、箱線圖

‐  結(jié)構(gòu)：餅圖、瀑布圖

3、 常用圖表畫圖技巧

‐  柱狀圖的畫圖原則

‐  直方圖的畫圖原則

‐  餅圖的畫圖原則

‐  折線圖的畫圖原則

‐  ……

演練：基本圖形繪制(柱狀圖、餅圖、折線圖、直方圖、散點(diǎn)圖/氣泡圖/象限圖)

演練：復(fù)雜圖形繪制(雙坐標(biāo)圖、漏斗圖、雷達(dá)圖)

4、 圖形美化原則

第九部分：  Excel動態(tài)圖表

1、 動態(tài)圖表的實(shí)質(zhì)

2、 動態(tài)圖表實(shí)現(xiàn)方式

a)   篩選器、切片器（單行、復(fù)選）

b)   動態(tài)鉆取

c)   函數(shù)實(shí)現(xiàn)（OFFSET/INDEX/MATCH/VLOOKUP/CHOOSE/IF）

d)   控件（單行、復(fù)選、下拉框等）

e)   定義名稱

演練：貸款金額計算及模擬運(yùn)算

3、 名稱管理器

結(jié)束：課程總結(jié)與問題答疑。