Excel高效數(shù)據(jù)管理與分析（2天）

課程介紹? Introduction

工欲善其事必先利其器！想提高Excel的工作效率？請(qǐng)先從巧用電子表格開始！

掌握Excel平臺(tái)的各類技巧，運(yùn)用所學(xué)的各種方法及技巧在往后的工作中解決實(shí)際問題。通過有多年實(shí)戰(zhàn)經(jīng)驗(yàn)的講師的演示講解及親自輔導(dǎo)，使您系統(tǒng)掌握Excel操作工具的精髓并能現(xiàn)場(chǎng)解決您日常工作中遇到的疑問或提供相關(guān)參考，為您開拓解決問題的思路，避免簡(jiǎn)單模仿操作過程及零散學(xué)習(xí)的弊端，令您事半功倍。為Excel日常辦公人員度身設(shè)計(jì)的實(shí)用課程，邊學(xué)邊練，即時(shí)體驗(yàn)成長(zhǎng)與進(jìn)步。

課程通過4個(gè)模塊，由淺入深、循序漸進(jìn)的帶領(lǐng)走進(jìn)神奇的Excel世界!

關(guān)信息??Information

課程時(shí)長(zhǎng)：12學(xué)時(shí)/2天

適合對(duì)象：需要使用Excel 進(jìn)行數(shù)據(jù)管理和分析的商業(yè)人士（中級(jí)培訓(xùn)）

授課方式：理念講授 +?分組討論 +?案例分析 +?操作示范 +?學(xué)員上臺(tái)演練 +?一對(duì)一輔導(dǎo)

學(xué)員人數(shù)：為了達(dá)到更好的學(xué)習(xí)效果，課程人數(shù)建議在50人以內(nèi)

課程大綱??Outline

零亂到有序：表格規(guī)范設(shè)計(jì)（2h）

制表規(guī)范——“源數(shù)據(jù)表”的十宗罪

討論：工作表結(jié)構(gòu)設(shè)計(jì)中的常見問題
結(jié)構(gòu)方面：如果不想通過多個(gè)IF函數(shù)，僅用一個(gè)VLOOKUP函數(shù)就可以計(jì)算出所有科目的等級(jí)，請(qǐng)思考右側(cè)的參數(shù)表格結(jié)構(gòu)應(yīng)該如何優(yōu)化？

數(shù)據(jù)方面：你能說出這個(gè)表格存在哪些數(shù)據(jù)不規(guī)范的問題嗎？
?

正確的工作表標(biāo)題的輸入位置

調(diào)整頁面邊距，設(shè)置重復(fù)打印標(biāo)題行

一鍵秒刪手工制作的小計(jì)行

處理空白單元格、空白行

2022.1.1，這樣的日期格式正確嗎？快速完成文本\日期\數(shù)字格式的強(qiáng)制轉(zhuǎn)換

利用智能填充功能，快速按規(guī)律拆分?jǐn)?shù)據(jù)
?

精準(zhǔn)定位——使用定位精準(zhǔn)選擇特殊單元格

定位區(qū)域、常量、公式、空值和批注

定位行/列內(nèi)容差異單元格

定位可見單元格、條件格式和數(shù)據(jù)驗(yàn)證

測(cè)試：你能不用復(fù)制粘貼，僅僅一個(gè)求和搞定所有的小計(jì)嗎？

妙用粘貼——靈活使用選擇性粘貼功能

一鍵將表格粘貼為圖片

快速粘貼為引用區(qū)域

粘貼運(yùn)算

靈活復(fù)制列寬

超級(jí)行列轉(zhuǎn)置

跳過空單元格，跨行粘貼

數(shù)據(jù)審核——規(guī)范數(shù)據(jù)輸入

輸入前溫馨提示

制作下拉型輸入框

源數(shù)據(jù)一更新，下拉輸入框自動(dòng)更新

個(gè)性化定制錯(cuò)誤警告

數(shù)據(jù)對(duì)碰，圈出無效數(shù)據(jù)，輕松篩選無效數(shù)據(jù)

動(dòng)手實(shí)踐：如何利用數(shù)據(jù)有效性檢查錯(cuò)誤輸入值
?

條件格式——讓重點(diǎn)數(shù)據(jù)脫穎而出

突出顯示規(guī)則

最前/最后規(guī)則

圖標(biāo)集規(guī)則

使用公式定義條件規(guī)則

重復(fù)數(shù)據(jù)一鍵自動(dòng)標(biāo)識(shí)

用兩種顏色數(shù)據(jù)條突出正負(fù)

峰值可視化：一秒用五星標(biāo)出圖書銷售最高值
?

用旋風(fēng)圖反映兩組數(shù)據(jù)的對(duì)比
?

表格美顏——形成動(dòng)態(tài)數(shù)據(jù)源

自動(dòng)形成匯總行

快速調(diào)整行列順序

結(jié)構(gòu)化引用、自建名稱

自動(dòng)隔行變色及凍結(jié)首行

一鍵快速選擇整行、整列及整表

公式輸入完成后自動(dòng)填充到整列

明細(xì)表一更新，圖表、透視表聯(lián)動(dòng)更新

表格不僅內(nèi)容重要，顏值也很重要

表格怎樣設(shè)計(jì)才具美感？專業(yè)商務(wù)表格賞析

向優(yōu)秀的表格模版學(xué)習(xí)表格美顏

新建表格樣式

插入動(dòng)態(tài)篩選器——切片器

學(xué)函數(shù)掌技巧：函數(shù)組合嵌套（5h）

函數(shù)基礎(chǔ)——函數(shù)基本知識(shí)

設(shè)置：顯示/隱藏公式編輯欄

參數(shù)：了解公式的語法和參數(shù)設(shè)置

搜索：學(xué)會(huì)搜索函數(shù)和查看幫助

竅門：掌握高手才知道的多層函數(shù)嵌套的秘密通道

技巧：還在用“拖”的方法復(fù)制公式嗎？按“Enter”鍵公式自動(dòng)填充到最后一行

公式引用——復(fù)雜公式必備知識(shí)

切換引用：利用快捷鍵切換絕引用、相對(duì)引用和混合引用

動(dòng)手實(shí)踐：怎樣?jì)故斓那袚Q三種不同引用方式

公式審核：名稱的定義、公式中應(yīng)用名稱、名稱的管理

常用函數(shù)——熟練掌握各種類型常用函數(shù)

邏輯函數(shù)：IF+AND+OR多層嵌套時(shí)，邏輯樹分析來梳理

統(tǒng)計(jì)函數(shù)：COUNTIFS、SUMIFS多條件計(jì)數(shù)和求和、模糊統(tǒng)計(jì)

查找函數(shù)：VLOOKUP的精確\區(qū)間\模糊查詢、IFERROR屏蔽錯(cuò)誤值#N/A
???????????????? VLOOKUP+CHOOSE+MATCH實(shí)現(xiàn)逆向查詢、多條件查詢、多結(jié)果返回

文本函數(shù)：LEFT+MID+RIGHT文本截取、TEXT格式轉(zhuǎn)換

日期函數(shù)：DATEDIF計(jì)算日期間隔、EDATE幾個(gè)月之前或之后的日期、TODAY當(dāng)天日期

問題分析：函數(shù)出錯(cuò)的原因分析和排錯(cuò)方法

功能結(jié)合：與條件格式和有效性結(jié)合，限定輸入并突出重點(diǎn)

顛覆認(rèn)知——你知道函數(shù)在不同場(chǎng)景中的用法嗎？

你知道函數(shù)的參數(shù)使用名稱引用，數(shù)據(jù)能自動(dòng)擴(kuò)展的功能嗎？IF函數(shù)在多條件判斷的情況下，如何輕松嵌套并做到完全不會(huì)出錯(cuò)嗎？

在不改變列的順序的情況下，實(shí)現(xiàn)VLOOKUP函數(shù)的逆向查詢嗎？

如何根據(jù)一個(gè)查詢條件帶出多個(gè)相關(guān)結(jié)果，并把公式一鍵填充到所有的空白單元格嗎？

返回相同查詢條件的多個(gè)結(jié)果

查詢出兩張表格的差異

如何實(shí)現(xiàn)合同到期提醒

能說話會(huì)表達(dá)：圖表可視化分析（2h）

圖表基礎(chǔ)——認(rèn)識(shí)圖表中的各個(gè)元素

坐標(biāo)軸：最大值、最小值、間隔單位、顯示單位、排序、顯示次坐標(biāo)

系列：填充色（用互補(bǔ)色突出顯示負(fù)值）、間距（柱形/條形圖）、線型、數(shù)據(jù)標(biāo)記（折線圖/散點(diǎn)圖）、分析線（趨勢(shì)線、誤差線、連接線）

圖例：位置、顯示項(xiàng)目

網(wǎng)格線：線型、顏色

數(shù)據(jù)標(biāo)簽：字體、位置、定制數(shù)據(jù)標(biāo)簽

圖表標(biāo)題：鏈接單元格，讓標(biāo)題自動(dòng)更新

添加或刪除圖表元素

決定保留的元素、數(shù)據(jù)范圍和序列

重調(diào)系列的順序、圖表的美化技巧

編輯數(shù)據(jù)源和更改圖表類型

制圖原則——為數(shù)據(jù)選擇適合的圖表類型

數(shù)據(jù)變成圖表的過程

選擇圖表類型的誤區(qū)

確定你所要表達(dá)的主題

成分\項(xiàng)類\時(shí)間序列\(zhòng)頻率分布\相關(guān)性對(duì)比關(guān)系

圖表選擇矩陣

餅圖\條形圖\柱形圖\折線圖\散點(diǎn)圖，五大圖表類型的使用場(chǎng)景和注意事項(xiàng)

高級(jí)圖表——生動(dòng)刻畫趨勢(shì)差異關(guān)系

圖表易容術(shù)：怎樣用圖片來呈現(xiàn)圖表的數(shù)據(jù)

用兩種顏色的圖片，展示數(shù)據(jù)的百分比
?

高級(jí)圖表——生動(dòng)刻畫趨勢(shì)差異關(guān)系

復(fù)合條餅圖：增強(qiáng)占比值較小數(shù)據(jù)的可讀性

單層圓環(huán)圖：可以在圓環(huán)的中心顯示總體情況

平滑折線圖：顯示更好的顯示數(shù)據(jù)變化趨勢(shì)

逆序條形圖：更直觀的呈現(xiàn)數(shù)據(jù)的排名

多圖表組合：柱形+折線的組合圖表，體現(xiàn)目標(biāo)或平均線

次要坐標(biāo)軸：將數(shù)據(jù)差異較大的值通過主、次坐標(biāo)分別展示

從靜態(tài)到動(dòng)態(tài)：篩選排序和數(shù)據(jù)透視（3h）

三表思維——用數(shù)據(jù)庫的標(biāo)準(zhǔn)化思維來規(guī)范表格結(jié)構(gòu)

參數(shù)表：系統(tǒng)配置參數(shù)

明細(xì)表：利用數(shù)據(jù)有效性、函數(shù)與參數(shù)表關(guān)聯(lián)

匯總表：數(shù)據(jù)透視表多維度分析

數(shù)據(jù)排序——?dú)w納常用但易被忽視的排序功能

多關(guān)鍵字排序

按顏色排序

導(dǎo)入自定義排序列表

自動(dòng)篩選——剖析典型篩選數(shù)據(jù)的應(yīng)用方法

文本篩選：包含某些關(guān)鍵詞

日期篩選：按年份、季度、月份篩選

數(shù)字篩選：大于、小于、10個(gè)最大值

復(fù)制篩選后的數(shù)據(jù)區(qū)域

高級(jí)篩選——篩選多組無規(guī)律數(shù)據(jù)

設(shè)置多個(gè)篩選條件

篩選結(jié)果的存放位置不同

可篩選不重復(fù)記錄

不包含單元格引用的篩選條件

包含單元格引用的篩選條件

結(jié)合公式的條件篩選

多條件篩選分為“條件與”、“條件或”

多表合并——利用Power Query實(shí)現(xiàn)多表合并，源表更新匯總表自動(dòng)更新

追加查詢：多張工作表的縱向合并

實(shí)戰(zhàn)案例：同一文件夾中結(jié)構(gòu)相同的多個(gè)工作簿，動(dòng)態(tài)合并到一張總表中

交叉分析——?jiǎng)?chuàng)建數(shù)據(jù)透視表

什么是數(shù)據(jù)透視表

了解數(shù)據(jù)透視表的使用情境

了解透視表相關(guān)術(shù)語

掌握數(shù)據(jù)透視表的組成結(jié)構(gòu)

查看明細(xì)數(shù)據(jù)、修改匯總方式和顯示方式

在數(shù)據(jù)透視表中應(yīng)用排序和篩選

對(duì)數(shù)字、日期、文本進(jìn)行分組

明細(xì)表增加數(shù)據(jù)透視表自動(dòng)更新

添加切片器，實(shí)現(xiàn)多透視表的動(dòng)態(tài)篩選

問題解決——數(shù)據(jù)透視表常見問題分析

為什么會(huì)多出一行空白數(shù)據(jù)

為什么日期數(shù)據(jù)不能組合

案例解析——數(shù)據(jù)透視表案例分析

篩選各地區(qū)各產(chǎn)品的銷量前10名

計(jì)算各地區(qū)、各產(chǎn)品占銷售總額的百分比

計(jì)算每年每個(gè)月的銷量、累計(jì)銷量及年度占比

當(dāng)期的數(shù)據(jù)較去年同期變動(dòng)率（YOY）

數(shù)據(jù)看板——生成動(dòng)態(tài)儀表板

修改數(shù)據(jù)透視表樣式

數(shù)據(jù)透視表刷新后保持調(diào)整好的列寬

以表格形式顯示，重復(fù)所有項(xiàng)目標(biāo)簽

數(shù)據(jù)透視表報(bào)告的整理和美化

?

?

課程超值福利包

20份精美專業(yè)的報(bào)表模板

14個(gè)圖表模板：只需一鍵，即可生成精美圖表

常用公式教程

圖表的10個(gè)提示

數(shù)據(jù)透視表基礎(chǔ)和進(jìn)階教程

學(xué)習(xí)資源支持

提供課堂同步學(xué)習(xí)手冊(cè)、資源素材以及案例學(xué)習(xí)樣本

提供表格、函數(shù)、圖表和透視表功能場(chǎng)景卡，方便學(xué)員參考使用

課程現(xiàn)場(chǎng)??The Scene

學(xué)員反饋??Feedback

甘彬老師的其它課程