構建高效數據分析模板：職場必學的Excel函數與動態(tài)圖表高級應用

Part 01 函數和公式
第1章 為什么要進行數據分析
1.1 一個實際咨詢案例3
1.2 企業(yè)要做好四個數據流的管理和分析7
1.3 正確認識Excel工具8
1.4 怎樣正確高效使用Excel 8
1．樹立正確的Excel理念8
2．設計標準化、規(guī)范化的Excel表格9
3．掌握Excel實用的數據管理和分析的思路、方法和技能9
4．利用掌握的Excel數據分析技能制作各種適合企業(yè)具體情況的自動化數據分析模板9
第2章 標準化、規(guī)范化Excel 表格數據
2.1 設計標準化、規(guī)范化的Excel基礎表格12
1．結構的科學性13
2．數據的易讀性14
3．匯總的方便性14
4．分析的靈活性16
5．外觀的美觀性17
案例2-1 設計標準化表格的方法與技巧 18
1．表格結構設計18
2．工號的自動連續(xù)填充19
3．規(guī)范姓名的輸入，不允許在姓名文字中輸入空格19
4．規(guī)范快速輸入部門名稱19
5．快速輸入學歷名稱20
6．快速輸入婚姻狀況20
7．輸入不重復的18位身份證號碼20
8．自動輸入性別21
9．自動輸入出生日期21
10．自動計算年齡21
11．規(guī)范輸入入職時間21
12．自動計算本公司工齡22
13．保證員工基本信息的完整性22
14．規(guī)范輸入離職時間22
15．規(guī)范輸入離職原因22
16．創(chuàng)建表格，自動復制有效性和公式，并自動美化表格23
2.2 不規(guī)范表格數據的標準化、規(guī)范化25
案例2-2 修改非法日期，使之成為真正日期 26
案例2-3 修改文本型數字，使之能夠使用函數計算 27
案例2-4 刪除數據中的特殊字符，讓數據還原本來面目 28
案例2-5 填充數據，保證數據的完整性 29
案例2-6 數據分列，讓不同類型數據各居其位 31
案例2-7 分列數字和漢字——規(guī)律明顯 33
案例2-8 分列數字和漢字——規(guī)律不明顯 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 學會創(chuàng)建嵌套函數公式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 計算數據區(qū)域內N 個最大數或最小數之和 64
案例3-11 獲取數據列中最后一個非空單元格數據 64
3.5 適當使用名稱65
1．定義名稱的規(guī)則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 計算含有錯誤值的單元格區(qū)域 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 統(tǒng)計員工的出勤情況（固定時間） 80
案例4-5 統(tǒng)計員工的出勤情況（階段時間） 82
案例4-6 統(tǒng)計18:00~20:00 以及20:00以后的刷卡人數 84
案例4-7 處理重復刷卡數據 87
案例4-8 按成本中心分析工資的四分位值 88
案例4-9 根據稅后工資反算稅前工資 91
案例4-10 制作個人所得稅計算表 91
案例4-11 調整信用期 92
案例4-12 判斷應收賬款賬齡區(qū)間 92
4.4 邏輯函數和信息函數的其他應用93
1．自定義數據有效性93
案例4-13 在員工信息表中輸入身份證號碼 94
2．復雜的條件格式94
案例4-14 自動跟蹤不同的業(yè)績變動率 94
第5章 日期函數及其實際應用
5.1 常用日期函數的基本使用方法97
1．TODAY函數：動態(tài)日期計算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 設計動態(tài)的日程表和考勤表 104
案例5-6 計算應付款截止日，遇雙休日順延 108
案例5-7 設計動態(tài)的固定資產折舊表 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 將金額的各個數字依次填入到連續(xù)的單元格 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 根據明細表統(tǒng)計每種材料的購買次數 136
3．精確匹配下的單條件求和136
案例7-5 計算每個供應商的采購次數和采購總金額 136
4．模糊匹配下的單條件求和137
案例7-6 計算每個部門和每個費用的總金額 137
7.3 多條件計數與多條件求和138
1．精確匹配下的多條件計數138
案例7-7 按部門統(tǒng)計總人數、男女人數、已婚未婚人數、
各個學歷的人數 138
2．模糊匹配下的多條件計數139
3．精確匹配下的多條件求和140
案例7-8 按地區(qū)分別匯總自營店和加盟店的銷售總額和銷售成本 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 函數的優(yōu)點和缺點150
7.5 條件計數函數與條件求和函數的一個特殊問題150
第8章 查找引用函數及其實際應用
8.1 查找引用問題概述153
1．單條件查找和多條件查找154
2．精確匹配數據查找和模糊匹配數據查找155
3．精確定位單元格查找和模糊定位單元格查找155
4．單一數據查找和重復數據查找156
5．單區(qū)域查找和多區(qū)域查找156
6．固定區(qū)域查找和滾動區(qū)域查找157
7．常用查找引用函數的使用語法158
8.2 VLOOKUP函數各種實際應用161
1．精確匹配下的單條件查詢161
案例8-1 查詢每個人的實發(fā)工資 161
2．模糊匹配下的單條件查詢161
案例8-2 查找指定城市價格 161
3．多個區(qū)域的數據查找161
案例8-3 根據編碼從“低值品”和“原材料”中查詢對應的數據 161
4．從不同列取數的數據查找162
案例8-4 隨意指定一個工資項目就能把每個員工的工資項目取出來 162
案例8-5 查詢指定員工的所有工資項目
（工資項目橫向排列，順序與工資表一致） 163
案例8-6 查詢指定員工的所有工資項目（工資項目垂直排列，順序
與工資表一致） 163
5．模糊定位單元格查找163
案例8-7 在應收賬款清單中通過逾期天數判斷賬齡 164
案例8-8 統(tǒng)計各個稅率下的人數、稅前工資總額、個稅總額 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 從一個數據區(qū)域中查找最大值所屬的地區(qū)和產品 168
5．多條件精確查找169
案例8-13 根據數據表計算每個員工的小時津貼 170
案例8-14 從流水賬整理成分類匯總表 171
6．模糊查找171
案例8-15 分列漢字、字母和數字 171
案例8-16 自動根據工作令號把真正的成本對象編碼查詢出來 172
7．財務中的典型應用：成本費用分析173
案例8-17 從管理費用數據中按照部門和費用項目分類匯總 174
案例8-18 制作動態(tài)成本分析報告，查看任意產品的成本構成 174
8．人力資源中的典型應用：制作員工簡歷表175
案例8-19 設計動態(tài)查詢表，查詢指定員工的基本信息 175
8.4 INDIRECT函數的各種實際應用177
1．滾動循環(huán)查找技術：制作明細表177
案例8-20 制作對賬單表格，把指定客戶的所有銷售明細查找出來 177
2．滾動匯總技術：快速匯總大量工作表181
案例8-21 各月利潤表數據的動態(tài)滾動匯總 181
案例8-22 制作動態(tài)員工工資單查詢表 182
案例8-23 制作每個部門各個月的預算執(zhí)行情況跟蹤分析表 183
案例8-24 制作查看指定部門、指定費用各個月的跟蹤分析表 183
案例8-25 按部門匯總每個月的應發(fā)工資合計數 184
案例8-26 按部門、按費用匯總跟蹤分析每個月的預算執(zhí)行情況 185
案例8-27 指定部門、指定費用的各個月匯總分析 186
3．工作表間的累計值自動計算187
案例8-28 自動計算每天的累計數 187
4．利用INDIRECT 函數與ROW函數構建常量數組188
8.5 OFFSET函數的各種實際應用188
案例8-29 獲取動態(tài)數據區(qū)域，制作動態(tài)數據源的數據透視表 188
案例8-30 獲取動態(tài)數據區(qū)域，作為數據有效性序列來源 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函數設計產品圖片查看系統(tǒng) 197
8.7 查詢數據的幾個應注意的問題197
1．查不到數據出現錯誤怎么辦197
2．為什么會查不到數據198
3．如何區(qū)別對待零值與空值198
Part 02 動態(tài)圖表
第9章 制作動態(tài)圖表的基本原理和方法
9.1 兩類動態(tài)圖表解析201
案例9-1 自動調整數據源的動態(tài)圖表 201
案例9-2 控件控制的動態(tài)圖表 202
案例9-3 兩類動態(tài)圖表的結合 202
案例9-4 在單元格利用數據有效性來控制圖表的顯示 203
9.2 制作動態(tài)圖表五大基本方法及其原理203
1．篩選法204
案例9-5 利用Excel的篩選功能實現訂單動態(tài)監(jiān)控 204
2．動態(tài)名稱法205
3．輔助區(qū)域法206
4．數據透視圖法208
案例9-6 制作數據透視圖 208
5．導入數據法208
案例9-7 制作動態(tài)圖表 209
6．必須牢記的動態(tài)圖表制作的六大步驟213
9.3 讓圖表能動的元素都動起來213
案例9-8 制作動態(tài)的圖表標題 214
案例9-9 制作動態(tài)的坐標軸標題 214
案例9-10 制作動態(tài)的數據標簽 215
9.4 制作動態(tài)圖表需要注意的幾個問題216
1．如何保護繪圖數據的安全性216
2．使用表單控件制作動態(tài)圖表需要注意的一個問題216
第10章 利用表單控件制作各種動態(tài)圖表
10.1 插入控件的基本方法和注意事項218
1．插入表單控件的基本方法218
2．在功能區(qū)顯示開發(fā)工具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 動態(tài)高亮顯示數據點 240
案例10-11 動畫效果的計劃完成對比分析圖 243
10.6 數值調節(jié)鈕使用方法及應用案例244
案例10-12 動態(tài)顯示最新幾個數據 245
案例10-13 動態(tài)顯示指定日期之前或之后的幾個數據 246
10.7 列表框使用方法及應用案例249
案例10-14 使用列表框控制圖表顯示 250
Part 03 自動化數據分析模板
第11章 管理費用滾動跟蹤分析模板
11.1 設計滾動匯總表253
11.2 制作動態(tài)趨勢分析圖254
11.3 制作動態(tài)結構分析圖255
1．指定部門下的費用結構分析255
2．指定費用下的部門占比分析257
第12章 產品成本滾動跟蹤分析模板
12.1 分析模板效果260
12.2 產品成本變動趨勢跟蹤分析261
12.3 產品成本結構跟蹤分析262
第13章 預算執(zhí)行情況滾動跟蹤分析模板
13.1 基礎數據265
13.2 模板效果266
13.3 模板制作266
1．建立滾動匯總表266
2．繪制動態(tài)分析圖267
第14章 薪酬滾動匯總分析模板
14.1 基礎薪酬表270
14.2 制作員工的全年工資單270
14.3 制作部門工資清單271
14.4 制作部門工資項目分析表272
14.5 制作全部工資項目分析表273
14.6 制作工資四分位值分析表274
1．分析指定部門的各個月的工資四分位值276
2．分析指定月份的各個部門的工資四分位值279