Excel在職場中的高效應用專題培訓

Excel高效應用專題培訓

?

課程大綱

模塊1 Excel數據表格創建及常用高效數據處理技巧

目標：本模塊主要是讓學員掌握數據高效錄入、規范導入外部數據、制作專業的模板型報表以及安全的分享報表數據，同時結合功能講解一系列的快捷鍵，提高日常電子表格使用的效率。

1.1快速輸入有規律的序列數據

填充數據序列

填充日期序列

自定義文本序列

結合序列自定義排序表格

結合函數使用自動編號

各類編號輸入總結

1.2單元格格式個性設置

運用自定義數字定制日期格式

給數字或字符添加前輟與后輟

通過自定義設置小數點對齊

通過自定義設置萬位數據顯示

設置單元格的邊框

設置斜線表頭

1.3數據的高效編輯技巧

使用Ctrl+Enter批量添加、修改數據

使用Ctrl+Enter無格式復制數據、高效復制公式

對單元格的合并區域進行處理

高效率合并相同內容的單元格區域

使用選擇性粘貼轉置表格行與列

使用選擇性粘貼進行數據的運算

使用函數進行動態表格行列列的轉置

運用特殊復制方法進行超級鏈接的取消

?

1.4數據導入的規范

不規范日期數據如何轉換成規范

不規范的數字如何實現計算

超過15位數字的導入與添加

包含特殊字符的字符串處理方法

數值數據如何轉換為文本，以便于數據的對比

靈活運用分列功能實現表格的規范呈現

1.5靈活運用數據有效性

使用有效性規范控制數據輸入，讓別人按照您的要求填寫數據

限定數據長度

顯示提示信息

采用下拉列表進行數據選擇

使用圈釋無效數據功能快速查找非法的數據

對比兩個表格數據的異同

1.6保護工作的安全

防止無關人員隨意更改表格的結構

設置單元格為非鎖定狀態

設置某些敏感單元格憑密碼編輯

保護工作表不允許修改

給文件加密

解除文件的密碼

共享工作簿的方法

?

模塊2根據單元格數據內容有條件設置單元格格式

目標：本模塊通過條件格式的功能提升數據報表的洞察力，讓報表更直觀、更專業。

2.1條件格式概述

2.2使用條件格式按單元格數值大小改變單元格格式

2.3使用公式做為條件設置復雜的單元格格式

2.4條件格式的拓展應用

2.5靈活的運用數據條創建迷你圖表

2.6靈活的運用色階標記熱點數據

2.7靈活的運用圖標集更直觀呈現數據的變化與趨勢等

模塊3規范使用公式和應用函數進行數據的高效處理

目標：本模塊主要講解公式高效復制、保證數據計算的精度、區分單元格地址引用方式、利用工作組高效同步操作表格以及跨表格、跨文件的數據引用計算；同時結合實際案例講解邏輯、文本、數據檢索配對、統計應用以及日期計算方面的函數。

3.1規范公式的應用

相對引用

*引用

數組的使用

規范公式的應用

如何設置單元格數據的四舍五入

如何保證運算中四舍五入的準確性（避免計算誤差）

快速復制公式的常用技巧

3.2常用函數應用例舉

常用的基本函數：

Sum、Average、Count、Max、Min、Product、Today

邏輯運算類函數：

IF、And和Or

統計函數：

Count、Counta、Countblank、Countif、Countifs、Sum、Sumif、Sumifs、Averageif、Averageifs

數據檢索函數：

Vlookup、Hlookup、Lookup

排名次函數：

Rank、Subtotal、large、Small

日期間隔計算函數：

Datedif、Networkdays、Workday

?

模塊4數據的分析與管理

目標：本模塊主要講解如何定制化排序報表、自動篩選與高級篩選的應用以及數據的分類匯總統計等。

4.1數據排序的技巧

按主要關鍵字排序

按多個關鍵字排序

更改排序選項，可按筆畫排序、按行排序

結合自定義列表進行自定義排序

對單獨某列數據進行排序

有合并區域時如何排序

防止排序時破壞表格的有效方法

4.2自動篩選與高級篩選

自動篩選與高級篩選功能概述

使用快捷鍵打開自動篩選

如何按單一條件進行篩選

如何按多重條件進行篩選

高級篩選的基本使用方法

如何區分高級篩選中的并與或的條件

如何將篩選的結果復制到其他工作表中

如何有針對性的選擇篩選結果包含的列數據

如何運用高級篩選獲取不重復記錄

如何結合宏來將高級篩選設置為自動搜索模式

4.3分類匯總與合并計算

創建分類匯總的基本條件

按單一條件進行分類匯總

按多級條件進行分類匯總

復制分類匯總結果如何避免隱藏數據

多張明細表如何快速合并計算生成匯總表

?

模塊5讓數據會說話——專業數據圖表創建

目標：本模塊主要講解專業數據圖表創建的基本理念、圖表創建的基本方法、圖表創建就避免的細節、各類常見圖表制作例舉以及常用動態圖表創建的基本方法等。

5.1突破常規的作圖方法

專業圖表的特點分析

有效傳遞信息，促進溝通

塑造可信度

傳遞專業、敬業、值得依賴的職業形象

專業圖表至少具有以下特點：

專業的外觀

簡潔的類型

明確的觀點

完美的細節

5.2突破Excel的默認顏色

5.3突破Excel的圖表布局

Excel默認布局的不足主要有：

標題不夠突出，信息量不足

繪圖區占據了過大的面積

繪圖區的四周浪費了很多地方，空間利用率不高

圖例在繪圖區右側，閱讀視線往返跳躍，需要長距離檢索、翻譯

商業圖表的布局特點

完整的圖表要素

突出的標題區

豎向的構圖方式

5.4商業圖表的字體選擇

5.5突破Excel的作圖元素

使用“圖表+所有Excel元素”這種方法基本只使用圖表對象來制作圖表的核心部分，其他部分多使用單元格、文本框、自選圖形等其他非圖表元素來制作。

?

5.6成為圖表高手的技術準備

作圖數據準備

以整理后的數據為源做柱形默認圖表

使用各類設置對話框進行格式化

設置數據系列格式

利用其他表格元素完成圖表

將圖表錨定到單元格

組合圖表制作習練

Excel圖表缺陷及補救

在Excel中制作條形圖時，默認生成圖表的條形順序總是與數據源順序相反。可以在“設置縱坐標軸格式”中勾選“逆序類別”和選擇橫坐標交叉“*分類”

讓曲線圖從Y軸開始

作圖數據的組織技巧

原始數據不等于作圖數據

在使用條形圖、柱形圖、餅圖進行分類對比時，除分類名稱有特殊順序要求的情況外，專業人士一般都會先將數據進行降序或升序的排列，做出的圖表將呈現排序的效果，便于閱讀與比較

5.7高級圖表制作——制作動態圖表

動態圖表也稱交互式圖表，即圖表的內容可以隨用戶的選擇而變化，是圖表分析的較高級形式

制作動態圖表最常用，也是最直觀、易于理解的做法是輔助系列法

5.8選擇恰當的圖表類型

曲線圖：用來反映隨時間變化的趨勢

柱形圖：用來反映分類項目之間的比較，也可以用來反映時間趨勢；

條形圖：用來反映分類項目之間的比較；

餅圖：用來反映構成，即部分占總體的比例；

散點圖：用來反映相關性或分布關系；

地圖：用來反映區域之間的分類比較。

5.9圖表類型選擇辨析

柱形圖和條形圖如何選擇？

柱形圖和曲線圖如何選擇？

需要放大波動幅度而使用非零起點坐標時，建議使用曲線圖

面積圖和曲線圖如何選擇？

當只有一個數據系列的時候，二者完全等價，都可以使用

當在大型會議室演示時，坐在后排的人很可能無法看清曲線圖的線條，而面積圖則更易讓人看清楚

當比較多個數據系列的趨勢時，建議使用曲線圖

5.10常見圖表類型創建綜合例舉

?

模塊6數據透視表——數據檢索匯總的萬花筒

目標：本模塊主要講解如何運用數據透視表進行高效數據分析方法。

6.1數據透視表的概述

數據透視表功能概述

數據透視表創建對數據源的要求

整理數據源為*效果

數據透視表字段列表知識

6.2數據透視表的創建基本操作

調整行字段的排列順序

顯示隱藏透視表字段的分類匯總

為一個字段添加多種分類匯總方式

報表布局的三種顯示形式及區別

在每個項目后插入空行

合并居中顯示項目標簽

6.3數據透視表排序與篩選

手動排序

自動排序

應用“其他排序選項”排序

在數據透視表中篩選數據

6.4數據透視表項目組合

運用手動方式組合數據項

對數值型數據進行組合

對日期數據進行組合

借助輔助列進行數據組合

6.5在透視表中執行計算

對同一字段進行多種分類匯總設置

透視表自定義值顯示方式

在透視表中添加計算字段

在透視表中使用計算項進行計算

6.6數據透視表的切片區（均用于2013或2016）

切片器的應用概述

創建切片器

更改切片器的布局

?

模塊7使用加載宏完成高效數據處理

目標：本模塊主要是講解一些“干貨”內容，讓日常常見的報表數據處理提升到一個更高的層次，可以將一天或半天的工作量，提升到分鐘級的操作。

1.快速批量復制數據（制作工資條、在一張工作表內以“秒”為單位復制多張表格……）

2.快速批量復制工作表、批量命名工作表（適合于創建年度報告、多地區報表等應用）

3.按條件拆分工作表（按照報表的某列或多列為條件，將報表“秒”拆為多張工作表或多個工作簿文件）

4.拆分工作簿（可以將工作簿中的多張工作表“秒”拆成獨立的單個文件）

5.合并工作表（可以將多個工作表進行“秒”合并，然后進行各類統計、數據處理等操作）

6.合并工作簿（可以將多個文件進行高效率的合并至一張工作表或一個工作簿文件中，以便于進行數據的統計等操作）

7.自定義函數（合并文本、轉換大寫金額、按顏色計數和求和、取身份證號碼的相關數據、計算*的個人所得稅等）

?

Excel高效應用專題培訓