Excel&Power Query&Power Pivot 實(shí)戰(zhàn)數(shù)據(jù)分析與可視化

時長：每次3小時直播

課程大綱：

第一次直播：Excel數(shù)據(jù)規(guī)范、預(yù)警、查詢、統(tǒng)計

1.?Excel數(shù)據(jù)規(guī)范（一）

l 表格使用中的那些“坑”

l Excel中的真表格——超級表

n 超級表的六大優(yōu)勢

n 區(qū)域與表格的轉(zhuǎn)換

l 三大數(shù)據(jù)規(guī)范化工具

n 快速填充

n 數(shù)據(jù)分列

n 數(shù)據(jù)驗(yàn)證

2. Excel數(shù)據(jù)規(guī)范（二）

l 用定位解決合并單元格的“后遺癥”

l 定義屬于你的專有列表

n 按指定順序填充

n 按指定順序排序

l 案例實(shí)戰(zhàn)：

n 三步快速填充上方非空單元格數(shù)據(jù)

3. Excel條件格式提醒

l 條件格式突出顯示

n 突出顯示規(guī)則

n 前后規(guī)則

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

n 色階

n 圖標(biāo)集

l 用公式規(guī)則實(shí)現(xiàn)“漲紅跌綠”

l 案例實(shí)戰(zhàn)：

n 合同到期預(yù)警

4. INDEX+MATCH雙劍合璧

l INDEX函數(shù)基礎(chǔ)

l INDEX多區(qū)域引用

l MATCH函數(shù)基礎(chǔ)

l INDEX+MATCH常規(guī)精確查找

l INDEX+MATCH動態(tài)引用照片

l INDEX+MATCH多條件查找

l INDEX+MATCH一對多查詢

l INDEX+MATCH目標(biāo)值含有通配符的查找

l INDEX+MATCH返回分?jǐn)?shù)等級

5. 另類查詢與案例實(shí)戰(zhàn)

l 高級篩選常用應(yīng)用

l 不用函數(shù)的查詢工具—高級篩選

n 條件區(qū)域及規(guī)則

n 空白字段表達(dá)式

l 案例實(shí)戰(zhàn)

n 根據(jù)身份證號計算距下次生日天數(shù)，并提醒

n 根據(jù)人員實(shí)現(xiàn)智能變色和匯總

6. 實(shí)戰(zhàn)統(tǒng)計函數(shù)（一）

l SUM另類用法

n 累計求和

n 合并單元格統(tǒng)計

n 跨工作表求和

l MAX和MIN設(shè)置銷售提成上下限

l LARGE與SMALL函數(shù)

n 列出前三筆銷量

n 列出前三筆銷量對應(yīng)的日期

l COUNTIF/COUNTIFS條件計數(shù)

7. 實(shí)戰(zhàn)統(tǒng)計函數(shù)（二）

l 最不老實(shí)的函數(shù)SUMPRODUCT

n SUMPRODUCT函數(shù)基礎(chǔ)

n SUMPRODUCT演講比賽評分

n SUMPRODUCT綜合銷售提成

n SUMPRODUCT條件統(tǒng)計計算

l 篩選與隱藏狀態(tài)下的統(tǒng)計與求和

n SUBTOTAL函數(shù)基礎(chǔ)

n SUBTOTAL在篩選狀態(tài)下的統(tǒng)計

n SUBTOTAL隱藏行數(shù)據(jù)統(tǒng)計

n SUBTOTAL篩選下生成連續(xù)序號

第二次直播：Excel數(shù)據(jù)透視與可視化呈現(xiàn)

8.?數(shù)據(jù)透視表基礎(chǔ)應(yīng)用

l 數(shù)據(jù)透視表刷新

l 數(shù)據(jù)透視表排序

l 數(shù)據(jù)透視表切片器

l 數(shù)據(jù)透視表日期表

l 數(shù)據(jù)透視表中執(zhí)行計算

l 數(shù)據(jù)透視表函數(shù)獲取數(shù)據(jù)透視表數(shù)據(jù)

9. 實(shí)戰(zhàn)數(shù)據(jù)透視表案例分析

l 創(chuàng)建動態(tài)的數(shù)據(jù)透視表

l Excel財務(wù)數(shù)據(jù)分析報告

l Excel銷售產(chǎn)品數(shù)據(jù)儀表盤

10. Excel 數(shù)據(jù)可視化圖表初探

l 商務(wù)圖表格式

n 不同類型圖表的展示場合

n 圖表類型更改

n 圖表要素與布局

n 圖表格式快速調(diào)用

l 常用三大基礎(chǔ)圖表

n 柱狀圖

n 折線圖

n 圓餅圖

l 柱狀圖的變化

n 圖片柱狀圖

n 目標(biāo)柱狀圖

n 參考線柱狀圖

n 雙Y軸柱狀圖

n 懸浮柱狀圖

l 新版本新增酷炫圖表

n 樹狀圖

n 瀑布圖

n 漏斗圖

n 旭日圖

n 直方圖

n 箱形圖

11. Excel 數(shù)據(jù)可視化圖表實(shí)戰(zhàn)

l 柱狀展示圖

n 長分類標(biāo)簽圖表

l 溫度計對比圖

n 柱狀溫度計對比圖

n 條形溫度計對比圖

n 溫度計百分圖

l 填充式圖表

n 填充式折線圖

n 填充式圓環(huán)圖

l 另類柱形圖

n 帶參考線的柱形圖

n 正負(fù)柱形圖

12. Excel 動態(tài)可視化圖表實(shí)戰(zhàn)

l 函數(shù)動態(tài)圖表

l 控件動態(tài)圖表

n 動態(tài)折線圖

n 動態(tài)雷達(dá)圖

第三次直播：Power Query數(shù)據(jù)規(guī)范實(shí)戰(zhàn)

13.?Power Query整理規(guī)范數(shù)據(jù)

l 規(guī)范化數(shù)據(jù)六大特點(diǎn)介紹

l 一維數(shù)據(jù)轉(zhuǎn)二維數(shù)據(jù)

l 二位數(shù)據(jù)轉(zhuǎn)一維數(shù)據(jù)

l 轉(zhuǎn)置數(shù)據(jù)和反轉(zhuǎn)數(shù)據(jù)

l 數(shù)據(jù)分組依據(jù)

l 添加與自定義數(shù)據(jù)列

l 案例實(shí)戰(zhàn)：

n 整理規(guī)范人事檔案

n 數(shù)據(jù)的拆分合并提取

n 統(tǒng)計每個地區(qū)的設(shè)備數(shù)量

n 用Power Query統(tǒng)計班次

14. Power Quer數(shù)據(jù)規(guī)范案例實(shí)戰(zhàn)

l POWERQUERY合并列實(shí)戰(zhàn)

l 統(tǒng)計每個報訂人的金額

l 二維表整理得到新的二維表

l POWER QUERY二維轉(zhuǎn)一維數(shù)據(jù)

l 統(tǒng)計班次（日班&夜班）

l LINES.TOTEXT或EACH TEXT.COMBINE

l 求料號最新的采購單價（即最后采購日期單價）

l POWER QUERY創(chuàng)建訂單查詢

l POWER QUERY利用分隔符創(chuàng)建分組

第四次直播：Power Pivot數(shù)據(jù)建模分析可視化

15.?Power Pivot數(shù)據(jù)建模分析實(shí)戰(zhàn)

l 趨勢分析

l 年度增長率YOY分析

l 同比環(huán)比分析

l 產(chǎn)品占比分析

l 關(guān)鍵績效指標(biāo)(KPI)介紹

l 銷售業(yè)績考核

l 預(yù)算成本預(yù)測考核

16. Power Pivot進(jìn)階DAX函數(shù)（一）

l 多條件應(yīng)用

n CALCULATE、IN、NOT

l 年同比，月環(huán)比

n CALCULATE、DATEADD、DIVIDE

l 年，季度，月累計

n TOTALYTD、TOTALQTD、TOTALMTD

l 年累計同比

n TOTALYTD、DIVIDE

17. Power Pivot進(jìn)階DAX函數(shù)（二）

l 按指定字段排名

n SUMMARIZE、RANKX、ALL

l 排名在矩陣表中的優(yōu)化

n IF、HASONEVALUE

l 篩選小于指定金額地區(qū)

n CALCULATE、FILTER、VALUES

l 篩選并排序前10名產(chǎn)品

n RANKX、ALL、CALCULATE、VALUES

l VAR聲明變量函數(shù)

n VAR創(chuàng)建輔助列

n VAR創(chuàng)建度量值

趙保恒老師的其它課程