構建高效數據分析模板

《構建高效數據分析模板》是2014年8月中國鐵道出版社出版的圖書，作者是韓小良。

基本介紹

• 中文名：構建高效數據分析模板
• 作者：韓小良
• 出版時間：2014年8月1日
• 出版社：中國鐵道出版社
• ISBN：9787113183233

內容簡介

Excel 是一個強大的數據管理和分析工具，但是很多人對Excel 的學習和套用，熱衷於快捷鍵和小技巧的使用，並沒有真正掌握數據分析的兩個重要的工具：函式和動態圖表。本書結合大量的企業實際案例，重點介紹函式和動態圖表在數據管理和分析中的實用技能、解決問題的邏輯、思路和方法，以及實際套用模板，使讀者快速提高Excel數據分析能力！本書介紹的大量案例，都是來自於作者的培訓第一線，非常具有實用價值，大部分案例實際上可作為現成的模板，套用於實際工作中，可使工作效率迅速成倍提高。

　　本書適合各類企事業單位的管理者，也可作為大專院校經濟專業的本科生、研究生和MBA學員的教材和學習參考書。

圖書目錄

Part 01 函式和公式

第1章 為什麼要進行數據分析

1.1 一個實際諮詢案例3

1.2 企業要做好四個數據流的管理和分析7

1.3 正確認識Excel工具8

1.4 怎樣正確高效使用Excel 8

1．樹立正確的Excel理念8

2．設計標準化、規範化的Excel表格9

3．掌握Excel實用的數據管理和分析的思路、方法和技能9

4．利用掌握的Excel數據分析技能製作各種適合企業具體情況的自動化數據分析模板9

第2章 標準化、規範化Excel 表格數據

2.1 設計標準化、規範化的Excel基礎表格12

1．結構的科學性13

2．數據的易讀性14

3．匯總的方便性14

4．分析的靈活性16

5．外觀的美觀性17

案例2-1 設計標準化表格的方法與技巧 18

1．表格結構設計18

2．工號的自動連續填充19

3．規範姓名的輸入，不允許在姓名文字中輸入空格19

4．規範快速輸入部門名稱19

5．快速輸入學歷名稱20

6．快速輸入婚姻狀況20

7．輸入不重複的18位身份證號碼20

8．自動輸入性別21

9．自動輸入出生日期21

10．自動計算年齡21

11．規範輸入入職時間21

12．自動計算本公司工齡22

13．保證員工基本信息的完整性22

14．規範輸入離職時間22

15．規範輸入離職原因22

16．創建表格，自動複製有效性和公式，並自動美化表格23

2.2 不規範表格數據的標準化、規範化25

案例2-2 修改非法日期，使之成為真正日期 26

案例2-3 修改文本型數字，使之能夠使用函式計算 27

案例2-4 刪除數據中的特殊字元，讓數據還原本來面目 28

案例2-5 填充數據，保證數據的完整性 29

案例2-6 數據分列，讓不同類型數據各居其位 31

案例2-7 分列數字和漢字——規律明顯 33

案例2-8 分列數字和漢字——規律不明顯 34

案例2-9 數據重新布局，將多次刷卡數據分成列 34

案例2-10 將道路及其門牌號分列 35

總結40

第3章 必須掌握的函式和公式基本技能

3.1 梳理表格數據的邏輯關係42

1．搞清楚自己要做什麼42

案例3-1 管理費用的趨勢分析和結構分析 42

2．分析表格數據之間的邏輯關係43

案例3-2 銷售收入達成分析 43

案例3-3 計算物料最新採購價格和加權平均採購價格 44

3．學會畫邏輯流程圖45

案例3-4 根據工齡工資標準計算工齡工資 45

案例3-5 根據不同城市、不同工齡計算補貼標準 46

4．設計好分析報告的結構和標題47

案例3-6 重新設計報告標題以簡化計算公式 47

案例3-7 設計二級分層表格結構 48

3.2 學會創建嵌套函式公式49

1．分解綜合法49

案例3-8 根據商品名稱查找對應編碼 50

3

2．函式對話框——名稱框法51

案例3-9 套用單流程嵌套IF公式計算工齡工資 51

3.3 學會使用條件表達式解決複雜問題58

1．什麼是條件表達式58

2．了解邏輯運算符59

3．簡單的條件表達式59

4．複雜的條件表達式59

5．很多問題使用條件表達式來解決更方便60

3.4 學點數組公式的知識61

1．數組的概念61

2．數組公式的概念62

3．輸入數組公式63

4．數組公式套用案例剖析63

5．常見數組公式套用舉例64

案例3-10 計算數據區域內N 個最大數或最小數之和 64

案例3-11 獲取數據列中最後一個非空單元格數據 64

3.5 適當使用名稱65

1．定義名稱的規則65

2．定義名稱的三種方法66

3．編輯、修改和刪除名稱68

3.6 不可忽略的絕對引用和相對引用知識68

1．單元格引用方式68

2．單元格引用方式對公式的影響69

3．引用其他工作表單元格70

4．單元格引用的兩種表示法A1 和R1C1有什麼不同70

3.7 公式和函式的其他應當掌握的小技巧72

1．複製公式本身73

2．複製公式的一部分73

3．查看公式中的某些表達式計算結果73

4．將公式分行輸入，以便使公式更加容易理解和查看73

第4 第一章 邏輯判斷函式及其實際套用

4.1 只使用IF函式的邏輯判斷75

1．IF函式的基本語法75

案例4-1 計算含有錯誤值的單元格區域 76

2．嵌套IF函式76

3．使用條件表達式來代替嵌套IF函式77

4.2 邏輯函式、信息函式聯合使用的複雜邏輯判斷77

1．使用AND函式組合與條件77

案例4-2 將逾期數據按時間不同標識出來 77

2．使用OR 函式組合或條件78

案例4-3 計算公司年休假天數 78

3．使用IFERROR 或者ISERROR函式處理公式的錯誤值79

4．使用ISNUMBER函式處理特殊的問題80

4.3 實際套用案例解析80

案例4-4 統計員工的出勤情況（固定時間） 80

案例4-5 統計員工的出勤情況（階段時間） 82

案例4-6 統計18:00~20:00 以及20:00以後的刷卡人數 84

案例4-7 處理重複刷卡數據 87

案例4-8 按成本中心分析工資的四分位值 88

案例4-9 根據稅後工資反算稅前工資 91

案例4-10 製作個人所得稅計算表 91

案例4-11 調整信用期 92

案例4-12 判斷應收賬款賬齡區間 92

4.4 邏輯函式和信息函式的其他套用93

1．自定義數據有效性93

案例4-13 在員工信息表中輸入身份證號碼 94

2．複雜的條件格式94

案例4-14 自動跟蹤不同的業績變動率 94

第5章 日期函式及其實際套用

5.1 常用日期函式的基本使用方法97

1．TODAY函式：動態日期計算98

2．EDATE函式：計算幾個月之前或之後的日期98

3．EOMONTH函式：計算幾個月之前或之後的月底日期98

4．DATEDIF 函式：計算兩個日期之間的期限99

5．DATE函式：把代表年、月、日的三個數字組合成一個日期99

6．YEAR、MONTH和DAY函式：把日期拆分成年、月、日三個數字100

7．WEEKDAY函式：判斷日期為星期幾100

8．WEEKNUM函式：判斷日期為當年的第幾周101

9．YEARFRAC函式：計算小數點年數101

5.2 常用日期函式的實際套用案例101

案例5-1 指定月數或年數後的具體日期 101

案例5-2 指定天數、月數或年數後的月底日期 102

案例5-3 根據出生日期計算年齡 103

案例5-4 根據入職日期計算工齡 104

案例5-5 設計動態的日程表和考勤表 104

案例5-6 計算應付款截止日，遇雙休日順延 108

案例5-7 設計動態的固定資產折舊錶 108

1．計算折舊開始日期109

2．計算固定資產到期日109

3．計算已計提折舊月數109

4．計算剩餘折舊月數109

5．計算當月折舊額110

6．計算本年度累計折舊額110

7．計算累計折舊額110

8．其他計算公式和輸入項目110

9．折舊錶的使用110

案例5-8 編制銷售周報表111

5.3 日期函式的其他套用案例112

案例5-9 確定某日期所在年已經過去的天數和剩餘天數 112

案例5-10 獲取某個月最後一天的日期以及該月有多少天 113

案例5-11 確定某個日期在該年的第幾季度 113

第6 第一章 文本函式及其實際套用

6.1 獲取文本的部分數據115

案例6-1 從文本字元串左邊往右取指定長度的字元 116

案例6-2 從文本字元串右邊往左取指定長度的字元 116

案例6-3 從文本字元串中間指定位置往右取指定長度的字元 117

案例6-4 從文本字元串右邊數指定位置往左取指定長度的字元 117

案例6-5 從某個特定字元位置取指定長度的字元 118

案例6-6 將前部分是數字、後部分是漢字的字元串分成兩列 120

案例6-7 將前部分是漢字、後部分是數字的字元串分成兩列 121

案例6-8 將數字、漢字混合的字元串中的數字取出 121

案例6-9 將前部分是字母、後部分是數字的字元串分成兩列 123

案例6-10 將前部分是數字、後部分是字母的字元串分成兩列 123

案例6-11 將金額的各個數字依次填入到連續的單元格 124

案例6-12 從身份證號碼獲取出生日期和性別 125

6.2 把數字和日期轉換為指定格式的文本126

1．TEXT函式的基本用法126

2．將一個單元格的日期和時間分成兩列127

3．將日期轉換成中文月份或者英文月份名稱127

案例6-13 根據日期流水賬製作月份（英文標題）匯總表 127

6.3 獲取當前檔案的有關信息127

案例6-14 獲取檔案路徑、檔案名稱和工作表名等信息 127

第7章 分類匯總函式及其實際套用

7.1 常見的數據分類匯總問題概述130

1．兩種常見的分類匯總方式：計數與求和130

2．兩種條件下的分類匯總：單條件與多條件131

3．兩種匹配模式下的分類匯總：精確匹配與模糊匹配131

4．五個分類匯總函式的基本使用語法131

7.2 單條件計數與單條件求和132

1．精確匹配下的單條件計數132

案例7-1 COUNTIF 函式進行單條件計數：簡單套用 132

案例7-2 使用COUNTIF函式進行自定義有效性設定：

不允許輸入重複數據 133

案例7-3 使用COUNTIF函式核對兩個表格的數據 134

2．模糊匹配下的單條件計數136

案例7-4 根據明細表統計每種材料的購買次數 136

3．精確匹配下的單條件求和136

案例7-5 計算每個供應商的採購次數和採購總金額 136

4．模糊匹配下的單條件求和137

案例7-6 計算每個部門和每個費用的總金額 137

7.3 多條件計數與多條件求和138

1．精確匹配下的多條件計數138

案例7-7 按部門統計總人數、男女人數、已婚未婚人數、

各個學歷的人數 138

2．模糊匹配下的多條件計數139

3．精確匹配下的多條件求和140

案例7-8 按地區分別匯總自營店和加盟店的銷售總額和銷售成本 140

4．模糊匹配下的多條件求和141

案例7-9 按客戶、按產品大類進行匯總 141

7.4 SUMPRODUCT函式的綜合套用141

1．SUMPRODUCT 函式的基本用法142

案例7-10 計算所有產品的銷售總額、折扣額、銷售淨額 142

2．SUMPRODUCT 函式的變形用法143

案例7-11 通過銷售表計算每個客戶的銷售總額 143

3．SUMPRODUCT 函式用來進行條件計數144

案例7-12 製作能夠查看任意指定年份員工流動率變化的模板 144

4．SUMPRODUCT 函式用來進行多條件求和145

案例7-13 計算每個產品類別、每個月的總金額 145

案例7-14 根據管理費用科目餘額表匯總每個費用項目

每個月的總金額 146

案例7-15 從銷售及回款清單匯總每個客戶每個賬齡的應收

賬款總額 147

5．SUMPRODUCT 函式的其他套用148

案例7-16 從採購數據計算物料的加權平均採購價格和算術

平均採購價格 148

案例7-17 按照客戶編碼和存貨名稱核對從兩套軟體中導出

的數據是否一致 149

6．SUMPRODUCT 函式的優點和缺點150

7.5 條件計數函式與條件求和函式的一個特殊問題150

第8章 查找引用函式及其實際套用

8.1 查找引用問題概述153

1．單條件查找和多條件查找154

2．精確匹配數據查找和模糊匹配數據查找155

3．精確定位單元格查找和模糊定位單元格查找155

4．單一數據查找和重複數據查找156

5．單區域查找和多區域查找156

6．固定區域查找和滾動區域查找157

7．常用查找引用函式的使用語法158

8.2 VLOOKUP函式各種實際套用161

1．精確匹配下的單條件查詢161

案例8-1 查詢每個人的實發工資 161

2．模糊匹配下的單條件查詢161

案例8-2 查找指定城市價格 161

3．多個區域的數據查找161

案例8-3 根據編碼從“低值品”和“原材料”中查詢對應的數據 161

4．從不同列取數的數據查找162

案例8-4 隨意指定一個工資項目就能把每個員工的工資項目取出來 162

案例8-5 查詢指定員工的所有工資項目

（工資項目橫向排列，順序與工資表一致） 163

案例8-6 查詢指定員工的所有工資項目（工資項目垂直排列，順序與工資表一致） 163

5．模糊定位單元格查找163

案例8-7 在應收賬款清單中通過逾期天數判斷賬齡 164

案例8-8 統計各個稅率下的人數、稅前工資總額、個稅總額 164

8.3 MATCH函式和INDEX函式的各種實際套用166

1．核對兩個表格166

案例8-9 使用MATCH 函式核對兩個表格數據 166

2．精確定位指定數據的單元格地址167

案例8-10 使用MATCH函式和ADDRESS函式查找指定

數據的單元格地址 167

3．模糊定位指定數據所在單元格168

案例8-11 利用查找函式進行數學插值計算 168

4．定位最大值/最小值的單元格地址168

案例8-12 從一個數據區域中查找最大值所屬的地區和產品 168

5．多條件精確查找169

案例8-13 根據數據表計算每個員工的小時津貼 170

案例8-14 從流水賬整理成分類匯總表 171

6．模糊查找171

案例8-15 分列漢字、字母和數字 171

案例8-16 自動根據工作令號把真正的成本對象編碼查詢出來 172

7．財務中的典型套用：成本費用分析173

案例8-17 從管理費用數據中按照部門和費用項目分類匯總 174

案例8-18 製作動態成本分析報告，查看任意產品的成本構成 174

8．人力資源中的典型套用：製作員工簡歷表175

案例8-19 設計動態查詢表，查詢指定員工的基本信息 175

8.4 INDIRECT函式的各種實際套用177

1．滾動循環查找技術：製作明細表177

案例8-20 製作對賬單表格，把指定客戶的所有銷售明細查找出來 177

2．滾動匯總技術：快速匯總大量工作表181

案例8-21 各月利潤表數據的動態滾動匯總 181

案例8-22 製作動態員工工資單查詢表 182

案例8-23 製作每個部門各個月的預算執行情況跟蹤分析表 183

案例8-24 製作查看指定部門、指定費用各個月的跟蹤分析表 183

案例8-25 按部門匯總每個月的應發工資合計數 184

案例8-26 按部門、按費用匯總跟蹤分析每個月的預算執行情況 185

案例8-27 指定部門、指定費用的各個月匯總分析 186

3．工作表間的累計值自動計算187

案例8-28 自動計算每天的累計數 187

4．利用INDIRECT 函式與ROW函式構建常量數組188

8.5 OFFSET函式的各種實際套用188

案例8-29 獲取動態數據區域，製作動態數據源的數據透視表 188

案例8-30 獲取動態數據區域，作為數據有效性序列來源 189

案例8-31 計算截至指定月份的累計值 190

案例8-32 從任意指定項目列中進行分類匯總 191

案例8-33 複雜的考勤匯總 192

8.6 其他查找函式的實際套用194

案例8-34 LOOKUP函式實際套用 194

案例8-35 CHOOSE函式實際套用 195

案例8-36 TRANSPOSE 函式實際套用 196

案例8-37 使用HYPERLINK函式自動建立超連結 196

案例8-38 使用HYPERLINK函式設計產品圖片查看系統 197

8.7 查詢數據的幾個應注意的問題197

1．查不到數據出現錯誤怎么辦197

2．為什麼會查不到數據198

3．如何區別對待零值與空值198

Part 02 動態圖表

第9章 製作動態圖表的基本原理和方法

9.1 兩類動態圖表解析201

案例9-1 自動調整數據源的動態圖表 201

案例9-2 控制項控制的動態圖表 202

案例9-3 兩類動態圖表的結合 202

案例9-4 在單元格利用數據有效性來控制圖表的顯示 203

9.2 製作動態圖表五大基本方法及其原理203

1．篩選法204

案例9-5 利用Excel的篩選功能實現訂單動態監控 204

2．動態名稱法205

3．輔助區域法206

4．數據透視圖法208

案例9-6 製作數據透視圖 208

5．導入數據法208

案例9-7 製作動態圖表 209

6．必須牢記的動態圖表製作的六大步驟213

9.3 讓圖表能動的元素都動起來213

案例9-8 製作動態的圖表標題 214

案例9-9 製作動態的坐標軸標題 214

案例9-10 製作動態的數據標籤 215

9.4 製作動態圖表需要注意的幾個問題216

1．如何保護繪圖數據的安全性216

2．使用表單控制項製作動態圖表需要注意的一個問題216

第10章 利用表單控制項製作各種動態圖表

10.1 插入控制項的基本方法和注意事項218

1．插入表單控制項的基本方法218

2．在功能區顯示開發工具218

10.2 選項按鈕使用方法及套用案例220

案例10-1 可控費用與不可控費用分析 220

案例10-2 兩年銷售收入、銷售成本、銷售利潤分析 222

10.3 複選框使用方法及套用案例223

案例10-3 三年資產價格同比分析 224

案例10-4 成本趨勢分析 225

10.4 組合框使用方法及套用案例226

案例10-5 使用一個組合框 227

案例10-6 使用多個彼此獨立的組合框 229

案例10-7 使用多個彼此關聯的組合框 232

案例10-8 分析指定時間段內的數據 235

10.5 滾動條使用方法及套用案例237

案例10-9 利潤敏感性分析模型 238

案例10-10 動態高亮顯示數據點 240

案例10-11 動畫效果的計畫完成對比分析圖 243

10.6 數值調節鈕使用方法及套用案例244

案例10-12 動態顯示最新幾個數據 245

案例10-13 動態顯示指定日期之前或之後的幾個數據 246

10.7 列表框使用方法及套用案例249

案例10-14 使用列表框控制圖表顯示 250

Part 03 自動化數據分析模板

第11章 管理費用滾動跟蹤分析模板

11.1 設計滾動匯總表253

11.2 製作動態趨勢分析圖254

11.3 製作動態結構分析圖255

1．指定部門下的費用結構分析255

2．指定費用下的部門占比分析257

第12章 產品成本滾動跟蹤分析模板

12.1 分析模板效果260

12.2 產品成本變動趨勢跟蹤分析261

12.3 產品成本結構跟蹤分析262

第13章 預算執行情況滾動跟蹤分析模板

13.1 基礎數據265

13.2 模板效果266

13.3 模板製作266

1．建立滾動匯總表266

2．繪製動態分析圖267

第14章 薪酬滾動匯總分析模板

14.1 基礎薪酬表270

14.2 製作員工的全年工資單270

14.3 製作部門工資清單271

14.4 製作部門工資項目分析表272

14.5 製作全部工資項目分析表273

14.6 製作工資四分位值分析表274

1．分析指定部門的各個月的工資四分位值276

2．分析指定月份的各個部門的工資四分位值279