告別無效學(xué)習(xí)：Excel人力資源精細(xì)化管理和量化分析

引子閱讀本書之前，先測試一下自己的Excel 水平 1

I 現(xiàn)在動手，自我測試.........................................................................1

測試題目01 你會設(shè)計Excel 表單嗎？ ....................................................1

測試題目02 怎樣快速整理基礎(chǔ)數(shù)據(jù)？ ....................................................2

測試題目03 合同管理如何做到提前自動提醒？ ......................................2

測試題目04 你有最基本的邏輯判斷處理能力嗎？ ..................................3

測試題目05 如何快速引用指定條件的數(shù)據(jù)？ .........................................3

測試題目06 如何引用滿足多個條件的數(shù)據(jù)？ .........................................4

測試題目07 如何對海量數(shù)據(jù)進(jìn)行快速統(tǒng)計分析？ ..................................4

測試題目08 如何將枯燥的數(shù)據(jù)變?yōu)橹庇^易懂的結(jié)果？ ...........................5

測試題目09 需要每個月都做相同的數(shù)據(jù)處理分析嗎？ ..........................5

測試題目10 如何為老板解釋數(shù)據(jù)的差異及其背后的原因？ ...........................5

II 評估自己的Excel 水平......................................................................6

III 如何快速掌握和提升自己的Excel 水平？.........................................6

CHAPTER 01 如何正確使用Excel——先從了解Excel 規(guī)則入手 8

1.1 如何使用Excel ...............................................................................8

1.1.1 先了解Excel 的重要規(guī)則................................................................9

1.1.2 掌握日常數(shù)據(jù)處理的基本工具和技能..............................................9

1.1.3 掌握最核心的幾個常用核心函數(shù) .....................................................9

1.1.4 學(xué)會做數(shù)據(jù)量化分析.....................................................................10

1.1.5 HR 亂用Excel 的十宗罪...............................................................10

1.2　正確區(qū)分基礎(chǔ)表格和分析報告.......................................................12

1.2.1 基礎(chǔ)表單.......................................................................................13

1.2.2 分析報告.......................................................................................13

1.2.3 兩個表格的關(guān)系............................................................................14

1.3　正確輸入文本、日期和數(shù)字..........................................................14

1.3.1 關(guān)于文本.......................................................................................15

1.3.2 關(guān)于日期和時間............................................................................16

1.3.3 關(guān)于數(shù)字.......................................................................................16

1.4　邏輯思維是Excel 的核心..............................................................17

1.4.1 表單設(shè)計，是對數(shù)據(jù)管理的邏輯思考............................................17

1.4.2 數(shù)據(jù)分析，是對數(shù)據(jù)背后信息的邏輯思考....................................17

1.4.3 函數(shù)公式，是對表格計算的邏輯思考............................................18

1.5　最終是數(shù)據(jù)的科學(xué)管理和量化分析...............................................19

1.5.1 對數(shù)據(jù)要做好科學(xué)管理.................................................................19

1.5.2 對數(shù)據(jù)要做好量化分析.................................................................20

CHAPTER 02 HR 如何做到高效數(shù)據(jù)管理——先整理好目前的亂表 21

2.1　現(xiàn)有表格結(jié)構(gòu)整理：讓數(shù)據(jù)流動順暢............................................21

2.1.1 實際案例剖析................................................................................21

2.1.2 不要再犯這樣的錯誤了.................................................................22

2.2　數(shù)據(jù)整理之一：修改非法日期和時間............................................23

2.2.1 讓人忍無可忍的日期輸入陋習(xí)......................................................23

2.2.2 幾秒完成非法日期修改 .................................................................23

2.2.3 刷卡考勤機(jī)導(dǎo)出的日期和時間都要整理........................................25

2.3　數(shù)據(jù)整理之二：將無法計算的文本型數(shù)字轉(zhuǎn)換為純數(shù)字 ...............25

2.3.1 為什么我表格里的數(shù)字無法求和？...............................................25

2.3.2 快速轉(zhuǎn)換法寶之一：利用智能標(biāo)記...............................................26

2.3.3 快速轉(zhuǎn)換法寶之二：選擇性粘貼...................................................26

2.3.4 快速轉(zhuǎn)換法寶之三：利用分列工具...............................................27

2.4　數(shù)據(jù)整理之三：快速刪除眼睛看不見的字符.................................27

2.4.1 你為什么非要在名字中間加空格呢？............................................27

2.4.2 不知從何而來的特殊字符..............................................................28

2.4.3 刷卡考勤數(shù)據(jù)中會出現(xiàn)換行符......................................................29

2.5　數(shù)據(jù)整理之四：數(shù)據(jù)分列 ..............................................................29

2.5.1 分列法寶之一：分列工具，根據(jù)分隔符號分.................................29

2.5.2 分列法寶之二：分列工具，根據(jù)固定寬度分.................................30

2.5.3 分列法寶之三：使用函數(shù)..............................................................31

2.5.4 分列法寶之四：使用快速填充工具...............................................34

2.6　處理合并單元格及填充數(shù)據(jù)..........................................................34

2.6.1 取消數(shù)據(jù)列中的合并單元格并填充數(shù)據(jù)........................................34

2.6.2 取消數(shù)據(jù)行中的合并單元格并填充數(shù)據(jù)........................................36

2.7　其他數(shù)據(jù)整理技巧.........................................................................37

2.7.1 復(fù)制可見單元格數(shù)據(jù).....................................................................37

2.7.2 對單元格區(qū)域數(shù)據(jù)進(jìn)行批量修改...................................................37

2.7.3 將行列數(shù)據(jù)進(jìn)行對調(diào).....................................................................38

CHAPTER 03 HR 如何做到高效數(shù)據(jù)管理——設(shè)計規(guī)范的基礎(chǔ)數(shù)據(jù)表單 40

3.1　要掌握數(shù)據(jù)高效輸入的重要技能：數(shù)據(jù)驗證.................................40

3.1.1 重要的數(shù)據(jù)驗證工具.....................................................................40

3.1.2 限制只能輸入規(guī)定的日期..............................................................41

3.1.3 為單元格設(shè)置下拉菜單.................................................................42

3.1.4 為單元格設(shè)置二級下拉菜單..........................................................43

3.1.5 控制不能輸入重復(fù)數(shù)據(jù).................................................................46

3.2　從身份證號碼自動提取信息..........................................................47

3.2.1 身份證號碼的編碼規(guī)則 .................................................................47

3.2.2 提取辦理身份證所在地的地址......................................................48

3.2.3 獲取真正的生日數(shù)字.....................................................................48

3.2.4 計算年齡（周歲） ..........................................................................48

3.2.5 判斷性別.......................................................................................49

3.2.6 判斷星座.......................................................................................50

3.3　利用函數(shù)從其他基本資料表自動獲取基本數(shù)據(jù).............................50

3.3.1 誰也離不開的最基本取數(shù)函數(shù)......................................................51

3.3.2 固定標(biāo)準(zhǔn)數(shù)據(jù)用公式來解決..........................................................51

3.3.3 變動數(shù)據(jù)用函數(shù)自動計算..............................................................51

3.4　HR 基礎(chǔ)表單設(shè)計練習(xí)之一：員工基本信息管理 ...........................52

3.4.1 繪制數(shù)據(jù)管理邏輯架構(gòu)圖..............................................................52

3.4.2 字段詳細(xì)描述................................................................................53

3.4.3 表格架構(gòu)設(shè)計................................................................................53

3.4.4 規(guī)范工號輸入（只允許4 位編碼，不允許重復(fù)）...........................53

3.4.5 規(guī)范姓名輸入（不允許在姓名中輸入空格）...................................54

3.4.6 快速輸入部門名稱........................................................................55

3.4.7 快速輸入學(xué)歷名稱........................................................................55

3.4.8 快速輸入婚姻狀況........................................................................55

3.4.9 輸入不重復(fù)的18 位身份證號碼....................................................55

3.4.10 自動輸入性別..............................................................................55

3.4.11 自動輸入出生日期.......................................................................56

3.4.12 自動計算年齡..............................................................................56

3.4.13 規(guī)范輸入入職時間......................................................................56

3.4.14 自動計算本公司工齡...................................................................56

3.4.15 保證員工基本信息的完整性........................................................57

3.4.16 規(guī)范輸入離職時間......................................................................57

3.4.17 規(guī)范輸入離職原因......................................................................58

3.4.18 設(shè)置條件格式，自動美化表格....................................................58

3.4.19 保護(hù)公式.....................................................................................58

3.4.20 數(shù)據(jù)輸入與數(shù)據(jù)維護(hù)...................................................................59

3.5　HR 基礎(chǔ)表單設(shè)計練習(xí)之二：動態(tài)考勤表 ......................................60

3.5.1 動態(tài)考勤表樣式............................................................................60

3.5.2 設(shè)計動態(tài)標(biāo)題................................................................................61

3.5.3 自動標(biāo)識雙休日............................................................................62

3.5.4 快速輸入請假類型........................................................................63

3.5.5 自動統(tǒng)計各類請假天數(shù).................................................................63

3.6　HR 基礎(chǔ)表單設(shè)計練習(xí)之三：休假管理 .........................................64

3.6.1 建立員工休假標(biāo)準(zhǔn)信息表..............................................................64

3.6.2 建立員工休假記錄表單.................................................................65

3.6.3 統(tǒng)計每個人的休假情況.................................................................65

CHAPTER 04 HR 如何高效批量采集數(shù)據(jù)——快速獲取外部數(shù)據(jù) 67

4.1　從其他工作表導(dǎo)入數(shù)據(jù).................................................................67

4.1.1 使用Microsoft Query 工具導(dǎo)出其他工作表數(shù)據(jù)...........................67

4.1.2 使用Power Query 工具導(dǎo)出其他工作表數(shù)據(jù)................................70

4.2　獲取幾個表的數(shù)據(jù).........................................................................71

4.2.1 利用Mrosoft Query 獲取幾個表都存在的數(shù)據(jù)：根據(jù)年初年末.........

信息表抓取在職人員信息 .............................................................72

4.2.2 利用Power Query 只獲取某個表格存在的數(shù)據(jù)：根據(jù)年初年末 .......

信息表抓取離職人員信息 .............................................................74

4.2.3 利用現(xiàn)有連接 SQL 語句獲取兩個表都存在的數(shù)據(jù)：在職 .............

老員工信息.............................................................................76

4.2.4 利用現(xiàn)有連接 SQL 語句獲取只存在某個表的數(shù)據(jù)：當(dāng)年離職 ........

員工信息......................................................................................78

4.2.5 利用現(xiàn)有連接 SQL 語句獲取只存在某個表的數(shù)據(jù)：當(dāng)年新進(jìn) ........

員工信息......................................................................................78

4.3　快速匯總?cè)?2 個月的工資表....................................................78

4.3.1 利用現(xiàn)有連接 SQL 方法匯總當(dāng)前工作簿的12 個月工資表.........78

4.3.2 利用Power Query 匯總當(dāng)前工作簿的12 個月工資表...................81

4.3.3 利用Power Query 匯總12 個月工作簿工資表.............................84

4.4　快速匯總集團(tuán)各個分公司全年數(shù)百個工資表.................................92

4.4.1 每個月工資表中有月份名稱列......................................................92

4.4.2 每個月工資表中沒有月份名稱列...................................................93

4.5　導(dǎo)入文本數(shù)據(jù)..............................................................................101

4.5.1 導(dǎo)入全部數(shù)據(jù)：CSV 格式文本文件.............................................101

4.5.2 導(dǎo)入全部數(shù)據(jù)：其他格式文本文件 .............................................102

4.5.3 獲取文本文件中滿足條件的數(shù)據(jù).................................................103

CHAPTER 05 HR 數(shù)據(jù)自動高效處理和分析——掌握核心函數(shù)公式 109

5.1　HR 人必須了解和掌握的函數(shù)公式基礎(chǔ)知識 ................................109

5.1.1 常見的幾種公式運算...................................................................109

5.1.2 絕對引用和相對引用................................................................... 110

5.1.3 公式中如何輸入常量................................................................... 111

5.1.4 公式和函數(shù)中的標(biāo)點符號都必須是半角字符............................... 112

5.1.5 如何快速準(zhǔn)確地輸入函數(shù)............................................................112

5.1.6 如何快速準(zhǔn)確地輸入嵌套............................................................112

5.2　HR 必須掌握和熟練運用的邏輯判斷函數(shù) .................................... 115

5.2.1 常用邏輯判斷函數(shù)IF、AND 和OR............................................ 115

5.2.2 邏輯函數(shù)HR 應(yīng)用之一：計算帶薪年休假...................................116

5.2.3 邏輯函數(shù)HR 應(yīng)用之二：計算工齡工資...................................... 116

5.2.4 邏輯函數(shù)HR 應(yīng)用之三：計算正常出勤...................................... 116

5.2.5 邏輯函數(shù)HR 應(yīng)用之四：計算上班遲到區(qū)間............................... 117

5.3　常用計數(shù)統(tǒng)計函數(shù)COUNTIF、COUNTIFS................................ 118

5.3.1 函數(shù)基本使用方法...................................................................... 118

5.3.2 精確條件計數(shù)..............................................................................119

5.3.3 模糊條件計數(shù)..............................................................................119

5.4　常用求和匯總函數(shù)SUMIF、SUMIFS.........................................120

5.4.1 函數(shù)基本使用方法......................................................................120

5.4.2 精確條件求和..............................................................................121

5.4.3 模糊條件求和..............................................................................121

5.5　用SUMPRODUCT 來高效統(tǒng)計..................................................122

5.5.1 函數(shù)基本原理..............................................................................122

5.5.2 條件計數(shù).....................................................................................123

5.5.3 條件求和.....................................................................................125

5.6　最常用的查找函數(shù)VLOOKUP....................................................125

5.6.1 VLOOKUP 函數(shù)基本應(yīng)用...........................................................125

5.6.2 與MATCH 函數(shù)聯(lián)合使用............................................................127

5.6.3 反向查找.....................................................................................128

5.6.4 根據(jù)關(guān)鍵詞來匹配查找...............................................................129

5.6.5 模糊定位查找..............................................................................129

5.7　聯(lián)合使用MATCH 和INDEX 做更靈活查找.................................131

5.8　用IFERROR 處理公式的錯誤值.................................................132

5.9　常用日期函數(shù)及其HR 應(yīng)用........................................................133

5.9.1 獲取當(dāng)天日期..............................................................................133

5.9.2 組合日期 .....................................................................................134

5.9.3 拆分日期.....................................................................................134

5.9.4 計算到期日.................................................................................134

5.9.5 計算年齡和工齡..........................................................................135

5.9.6 判斷星期幾.................................................................................135

5.10　常用HR 公式集錦.....................................................................136

5.10.1 計算年齡...................................................................................136

5.10.2 計算工齡...................................................................................136

5.10.3 計算帶薪年休假 ........................................................................136

5.10.4 計算個稅...................................................................................136

5.10.5 根據(jù)稅后反算稅前工資.............................................................136

5.10.6 從身份證號碼提取信息.............................................................136

5.10.7 判斷星期幾...............................................................................137

5.10.8 一個月的天數(shù)............................................................................137

5.10.9 當(dāng)月月底日期............................................................................137

5.10.10 當(dāng)月月初日期..........................................................................137