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

Part 01 函數(shù)和公式
第1章 為什么要進行數(shù)據(jù)分析
1.1 一個實際咨詢案例3
1.2 企業(yè)要做好四個數(shù)據(jù)流的管理和分析7
1.3 正確認識Excel工具8
1.4 怎樣正確高效使用Excel 8
1．樹立正確的Excel理念8
2．設計標準化、規(guī)范化的Excel表格9
3．掌握Excel實用的數(shù)據(jù)管理和分析的思路、方法和技能9
4．利用掌握的Excel數(shù)據(jù)分析技能制作各種適合企業(yè)具體情況的自動化數(shù)據(jù)分析模板9
第2章 標準化、規(guī)范化Excel 表格數(shù)據(jù)
2.1 設計標準化、規(guī)范化的Excel基礎表格12
1．結構的科學性13
2．數(shù)據(jù)的易讀性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ī)范表格數(shù)據(jù)的標準化、規(guī)范化25
案例2-2 修改非法日期，使之成為真正日期 26
案例2-3 修改文本型數(shù)字，使之能夠使用函數(shù)計算 27
案例2-4 刪除數(shù)據(jù)中的特殊字符，讓數(shù)據(jù)還原本來面目 28
案例2-5 填充數(shù)據(jù)，保證數(shù)據(jù)的完整性 29
案例2-6 數(shù)據(jù)分列，讓不同類型數(shù)據(jù)各居其位 31
案例2-7 分列數(shù)字和漢字——規(guī)律明顯 33
案例2-8 分列數(shù)字和漢字——規(guī)律不明顯 34
案例2-9 數(shù)據(jù)重新布局，將多次刷卡數(shù)據(jù)分成列 34
案例2-10 將道路及其門牌號分列 35
總結40
第3章 必須掌握的函數(shù)和公式基本技能
3.1 梳理表格數(shù)據(jù)的邏輯關系42
1．搞清楚自己要做什么42
案例3-1 管理費用的趨勢分析和結構分析 42
2．分析表格數(shù)據(jù)之間的邏輯關系43
案例3-2 銷售收入達成分析 43
案例3-3 計算物料最新采購價格和加權平均采購價格 44
3．學會畫邏輯流程圖45
案例3-4 根據(jù)工齡工資標準計算工齡工資 45
案例3-5 根據(jù)不同城市、不同工齡計算補貼標準 46
4．設計好分析報告的結構和標題47
案例3-6 重新設計報告標題以簡化計算公式 47
案例3-7 設計二級分層表格結構 48
3.2 學會創(chuàng)建嵌套函數(shù)公式49
1．分解綜合法49
案例3-8 根據(jù)商品名稱查找對應編碼 50
3
2．函數(shù)對話框——名稱框法51
案例3-9 應用單流程嵌套IF公式計算工齡工資 51
3.3 學會使用條件表達式解決復雜問題58
1．什么是條件表達式58
2．了解邏輯運算符59
3．簡單的條件表達式59
4．復雜的條件表達式59
5．很多問題使用條件表達式來解決更方便60
3.4 學點數(shù)組公式的知識61
1．數(shù)組的概念61
2．數(shù)組公式的概念62
3．輸入數(shù)組公式63
4．數(shù)組公式應用案例剖析63
5．常見數(shù)組公式應用舉例64
案例3-10 計算數(shù)據(jù)區(qū)域內(nèi)N 個最大數(shù)或最小數(shù)之和 64
案例3-11 獲取數(shù)據(jù)列中最后一個非空單元格數(shù)據(jù) 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 公式和函數(shù)的其他應當掌握的小技巧72
1．復制公式本身73
2．復制公式的一部分73
3．查看公式中的某些表達式計算結果73
4．將公式分行輸入，以便使公式更加容易理解和查看73
第4 章 邏輯判斷函數(shù)及其實際應用
4.1 只使用IF函數(shù)的邏輯判斷75
1．IF函數(shù)的基本語法75
案例4-1 計算含有錯誤值的單元格區(qū)域 76
2．嵌套IF函數(shù)76
3．使用條件表達式來代替嵌套IF函數(shù)77
4.2 邏輯函數(shù)、信息函數(shù)聯(lián)合使用的復雜邏輯判斷77
1．使用AND函數(shù)組合與條件77
案例4-2 將逾期數(shù)據(jù)按時間不同標識出來 77
2．使用OR 函數(shù)組合或條件78
案例4-3 計算公司年休假天數(shù) 78
3．使用IFERROR 或者ISERROR函數(shù)處理公式的錯誤值79
4．使用ISNUMBER函數(shù)處理特殊的問題80
4.3 實際應用案例解析80
案例4-4 統(tǒng)計員工的出勤情況（固定時間） 80
案例4-5 統(tǒng)計員工的出勤情況（階段時間） 82
案例4-6 統(tǒng)計18:00~20:00 以及20:00以后的刷卡人數(shù) 84
案例4-7 處理重復刷卡數(shù)據(jù) 87
案例4-8 按成本中心分析工資的四分位值 88
案例4-9 根據(jù)稅后工資反算稅前工資 91
案例4-10 制作個人所得稅計算表 91
案例4-11 調(diào)整信用期 92
案例4-12 判斷應收賬款賬齡區(qū)間 92
4.4 邏輯函數(shù)和信息函數(shù)的其他應用93
1．自定義數(shù)據(jù)有效性93
案例4-13 在員工信息表中輸入身份證號碼 94
2．復雜的條件格式94
案例4-14 自動跟蹤不同的業(yè)績變動率 94
第5章 日期函數(shù)及其實際應用
5.1 常用日期函數(shù)的基本使用方法97
1．TODAY函數(shù)：動態(tài)日期計算98
2．EDATE函數(shù)：計算幾個月之前或之后的日期98
3．EOMONTH函數(shù)：計算幾個月之前或之后的月底日期98
4．DATEDIF 函數(shù)：計算兩個日期之間的期限99
5．DATE函數(shù)：把代表年、月、日的三個數(shù)字組合成一個日期99
6．YEAR、MONTH和DAY函數(shù)：把日期拆分成年、月、日三個數(shù)字100
7．WEEKDAY函數(shù)：判斷日期為星期幾100
8．WEEKNUM函數(shù)：判斷日期為當年的第幾周101
9．YEARFRAC函數(shù)：計算小數(shù)點年數(shù)101
5.2 常用日期函數(shù)的實際應用案例101
案例5-1 指定月數(shù)或年數(shù)后的具體日期 101
案例5-2 指定天數(shù)、月數(shù)或年數(shù)后的月底日期 102
案例5-3 根據(jù)出生日期計算年齡 103
案例5-4 根據(jù)入職日期計算工齡 104
案例5-5 設計動態(tài)的日程表和考勤表 104
案例5-6 計算應付款截止日，遇雙休日順延 108
案例5-7 設計動態(tài)的固定資產(chǎn)折舊表 108
1．計算折舊開始日期109
2．計算固定資產(chǎn)到期日109
3．計算已計提折舊月數(shù)109
4．計算剩余折舊月數(shù)109
5．計算當月折舊額110
6．計算本年度累計折舊額110
7．計算累計折舊額110
8．其他計算公式和輸入項目110
9．折舊表的使用110
案例5-8 編制銷售周報表111
5.3 日期函數(shù)的其他應用案例112
案例5-9 確定某日期所在年已經(jīng)過去的天數(shù)和剩余天數(shù) 112
案例5-10 獲取某個月最后一天的日期以及該月有多少天 113
案例5-11 確定某個日期在該年的第幾季度 113
第6 章 文本函數(shù)及其實際應用
6.1 獲取文本的部分數(shù)據(jù)115
案例6-1 從文本字符串左邊往右取指定長度的字符 116
案例6-2 從文本字符串右邊往左取指定長度的字符 116
案例6-3 從文本字符串中間指定位置往右取指定長度的字符 117
案例6-4 從文本字符串右邊數(shù)指定位置往左取指定長度的字符 117
案例6-5 從某個特定字符位置取指定長度的字符 118
案例6-6 將前部分是數(shù)字、后部分是漢字的字符串分成兩列 120
案例6-7 將前部分是漢字、后部分是數(shù)字的字符串分成兩列 121
案例6-8 將數(shù)字、漢字混合的字符串中的數(shù)字取出 121
案例6-9 將前部分是字母、后部分是數(shù)字的字符串分成兩列 123
案例6-10 將前部分是數(shù)字、后部分是字母的字符串分成兩列 123
案例6-11 將金額的各個數(shù)字依次填入到連續(xù)的單元格 124
案例6-12 從身份證號碼獲取出生日期和性別 125
6.2 把數(shù)字和日期轉換為指定格式的文本126
1．TEXT函數(shù)的基本用法126
2．將一個單元格的日期和時間分成兩列127
3．將日期轉換成中文月份或者英文月份名稱127
案例6-13 根據(jù)日期流水賬制作月份（英文標題）匯總表 127
6.3 獲取當前文件的有關信息127
案例6-14 獲取文件路徑、文件名和工作表名等信息 127
第7章 分類匯總函數(shù)及其實際應用
7.1 常見的數(shù)據(jù)分類匯總問題概述130
1．兩種常見的分類匯總方式：計數(shù)與求和130
2．兩種條件下的分類匯總：單條件與多條件131
3．兩種匹配模式下的分類匯總：精確匹配與模糊匹配131
4．五個分類匯總函數(shù)的基本使用語法131
7.2 單條件計數(shù)與單條件求和132
1．精確匹配下的單條件計數(shù)132
案例7-1 COUNTIF 函數(shù)進行單條件計數(shù)：簡單應用 132
案例7-2 使用COUNTIF函數(shù)進行自定義有效性設置：
不允許輸入重復數(shù)據(jù) 133
案例7-3 使用COUNTIF函數(shù)核對兩個表格的數(shù)據(jù) 134
2．模糊匹配下的單條件計數(shù)136
案例7-4 根據(jù)明細表統(tǒng)計每種材料的購買次數(shù) 136
3．精確匹配下的單條件求和136
案例7-5 計算每個供應商的采購次數(shù)和采購總金額 136
4．模糊匹配下的單條件求和137
案例7-6 計算每個部門和每個費用的總金額 137
7.3 多條件計數(shù)與多條件求和138
1．精確匹配下的多條件計數(shù)138
案例7-7 按部門統(tǒng)計總人數(shù)、男女人數(shù)、已婚未婚人數(shù)、
各個學歷的人數(shù) 138
2．模糊匹配下的多條件計數(shù)139
3．精確匹配下的多條件求和140
案例7-8 按地區(qū)分別匯總自營店和加盟店的銷售總額和銷售成本 140
4．模糊匹配下的多條件求和141
案例7-9 按客戶、按產(chǎn)品大類進行匯總 141
7.4 SUMPRODUCT函數(shù)的綜合應用141
1．SUMPRODUCT 函數(shù)的基本用法142
案例7-10 計算所有產(chǎn)品的銷售總額、折扣額、銷售凈額 142
2．SUMPRODUCT 函數(shù)的變形用法143
案例7-11 通過銷售表計算每個客戶的銷售總額 143
3．SUMPRODUCT 函數(shù)用來進行條件計數(shù)144
案例7-12 制作能夠查看任意指定年份員工流動率變化的模板 144
4．SUMPRODUCT 函數(shù)用來進行多條件求和145
案例7-13 計算每個產(chǎn)品類別、每個月的總金額 145
案例7-14 根據(jù)管理費用科目余額表匯總每個費用項目
每個月的總金額 146
案例7-15 從銷售及回款清單匯總每個客戶每個賬齡的應收
賬款總額 147
5．SUMPRODUCT 函數(shù)的其他應用148
案例7-16 從采購數(shù)據(jù)計算物料的加權平均采購價格和算術
平均采購價格 148
案例7-17 按照客戶編碼和存貨名稱核對從兩套軟件中導出
的數(shù)據(jù)是否一致 149
6．SUMPRODUCT 函數(shù)的優(yōu)點和缺點150
7.5 條件計數(shù)函數(shù)與條件求和函數(shù)的一個特殊問題150
第8章 查找引用函數(shù)及其實際應用
8.1 查找引用問題概述153
1．單條件查找和多條件查找154
2．精確匹配數(shù)據(jù)查找和模糊匹配數(shù)據(jù)查找155
3．精確定位單元格查找和模糊定位單元格查找155
4．單一數(shù)據(jù)查找和重復數(shù)據(jù)查找156
5．單區(qū)域查找和多區(qū)域查找156
6．固定區(qū)域查找和滾動區(qū)域查找157
7．常用查找引用函數(shù)的使用語法158
8.2 VLOOKUP函數(shù)各種實際應用161
1．精確匹配下的單條件查詢161
案例8-1 查詢每個人的實發(fā)工資 161
2．模糊匹配下的單條件查詢161
案例8-2 查找指定城市價格 161
3．多個區(qū)域的數(shù)據(jù)查找161
案例8-3 根據(jù)編碼從“低值品”和“原材料”中查詢對應的數(shù)據(jù) 161
4．從不同列取數(shù)的數(shù)據(jù)查找162
案例8-4 隨意指定一個工資項目就能把每個員工的工資項目取出來 162
案例8-5 查詢指定員工的所有工資項目
（工資項目橫向排列，順序與工資表一致） 163
案例8-6 查詢指定員工的所有工資項目（工資項目垂直排列，順序
與工資表一致） 163
5．模糊定位單元格查找163
案例8-7 在應收賬款清單中通過逾期天數(shù)判斷賬齡 164
案例8-8 統(tǒng)計各個稅率下的人數(shù)、稅前工資總額、個稅總額 164
8.3 MATCH函數(shù)和INDEX函數(shù)的各種實際應用166
1．核對兩個表格166
案例8-9 使用MATCH 函數(shù)核對兩個表格數(shù)據(jù) 166
2．精確定位指定數(shù)據(jù)的單元格地址167
案例8-10 使用MATCH函數(shù)和ADDRESS函數(shù)查找指定
數(shù)據(jù)的單元格地址 167
3．模糊定位指定數(shù)據(jù)所在單元格168
案例8-11 利用查找函數(shù)進行數(shù)學插值計算 168
4．定位最大值/最小值的單元格地址168
案例8-12 從一個數(shù)據(jù)區(qū)域中查找最大值所屬的地區(qū)和產(chǎn)品 168
5．多條件精確查找169
案例8-13 根據(jù)數(shù)據(jù)表計算每個員工的小時津貼 170
案例8-14 從流水賬整理成分類匯總表 171
6．模糊查找171
案例8-15 分列漢字、字母和數(shù)字 171
案例8-16 自動根據(jù)工作令號把真正的成本對象編碼查詢出來 172
7．財務中的典型應用：成本費用分析173
案例8-17 從管理費用數(shù)據(jù)中按照部門和費用項目分類匯總 174
案例8-18 制作動態(tài)成本分析報告，查看任意產(chǎn)品的成本構成 174
8．人力資源中的典型應用：制作員工簡歷表175
案例8-19 設計動態(tài)查詢表，查詢指定員工的基本信息 175
8.4 INDIRECT函數(shù)的各種實際應用177
1．滾動循環(huán)查找技術：制作明細表177
案例8-20 制作對賬單表格，把指定客戶的所有銷售明細查找出來 177
2．滾動匯總技術：快速匯總大量工作表181
案例8-21 各月利潤表數(shù)據(jù)的動態(tài)滾動匯總 181
案例8-22 制作動態(tài)員工工資單查詢表 182
案例8-23 制作每個部門各個月的預算執(zhí)行情況跟蹤分析表 183
案例8-24 制作查看指定部門、指定費用各個月的跟蹤分析表 183
案例8-25 按部門匯總每個月的應發(fā)工資合計數(shù) 184
案例8-26 按部門、按費用匯總跟蹤分析每個月的預算執(zhí)行情況 185
案例8-27 指定部門、指定費用的各個月匯總分析 186
3．工作表間的累計值自動計算187
案例8-28 自動計算每天的累計數(shù) 187
4．利用INDIRECT 函數(shù)與ROW函數(shù)構建常量數(shù)組188
8.5 OFFSET函數(shù)的各種實際應用188
案例8-29 獲取動態(tài)數(shù)據(jù)區(qū)域，制作動態(tài)數(shù)據(jù)源的數(shù)據(jù)透視表 188
案例8-30 獲取動態(tài)數(shù)據(jù)區(qū)域，作為數(shù)據(jù)有效性序列來源 189
案例8-31 計算截至指定月份的累計值 190
案例8-32 從任意指定項目列中進行分類匯總 191
案例8-33 復雜的考勤匯總 192
8.6 其他查找函數(shù)的實際應用194
案例8-34 LOOKUP函數(shù)實際應用 194
案例8-35 CHOOSE函數(shù)實際應用 195
案例8-36 TRANSPOSE 函數(shù)實際應用 196
案例8-37 使用HYPERLINK函數(shù)自動建立超鏈接 196
案例8-38 使用HYPERLINK函數(shù)設計產(chǎn)品圖片查看系統(tǒng) 197
8.7 查詢數(shù)據(jù)的幾個應注意的問題197
1．查不到數(shù)據(jù)出現(xiàn)錯誤怎么辦197
2．為什么會查不到數(shù)據(jù)198
3．如何區(qū)別對待零值與空值198
Part 02 動態(tài)圖表
第9章 制作動態(tài)圖表的基本原理和方法
9.1 兩類動態(tài)圖表解析201
案例9-1 自動調(diào)整數(shù)據(jù)源的動態(tài)圖表 201
案例9-2 控件控制的動態(tài)圖表 202
案例9-3 兩類動態(tài)圖表的結合 202
案例9-4 在單元格利用數(shù)據(jù)有效性來控制圖表的顯示 203
9.2 制作動態(tài)圖表五大基本方法及其原理203
1．篩選法204
案例9-5 利用Excel的篩選功能實現(xiàn)訂單動態(tài)監(jiān)控 204
2．動態(tài)名稱法205
3．輔助區(qū)域法206
4．數(shù)據(jù)透視圖法208
案例9-6 制作數(shù)據(jù)透視圖 208
5．導入數(shù)據(jù)法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)的數(shù)據(jù)標簽 215
9.4 制作動態(tài)圖表需要注意的幾個問題216
1．如何保護繪圖數(shù)據(jù)的安全性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 三年資產(chǎn)價格同比分析 224
案例10-4 成本趨勢分析 225
10.4 組合框使用方法及應用案例226
案例10-5 使用一個組合框 227
案例10-6 使用多個彼此獨立的組合框 229
案例10-7 使用多個彼此關聯(lián)的組合框 232
案例10-8 分析指定時間段內(nèi)的數(shù)據(jù) 235
10.5 滾動條使用方法及應用案例237
案例10-9 利潤敏感性分析模型 238
案例10-10 動態(tài)高亮顯示數(shù)據(jù)點 240
案例10-11 動畫效果的計劃完成對比分析圖 243
10.6 數(shù)值調(diào)節(jié)鈕使用方法及應用案例244
案例10-12 動態(tài)顯示最新幾個數(shù)據(jù) 245
案例10-13 動態(tài)顯示指定日期之前或之后的幾個數(shù)據(jù) 246
10.7 列表框使用方法及應用案例249
案例10-14 使用列表框控制圖表顯示 250
Part 03 自動化數(shù)據(jù)分析模板
第11章 管理費用滾動跟蹤分析模板
11.1 設計滾動匯總表253
11.2 制作動態(tài)趨勢分析圖254
11.3 制作動態(tài)結構分析圖255
1．指定部門下的費用結構分析255
2．指定費用下的部門占比分析257
第12章 產(chǎn)品成本滾動跟蹤分析模板
12.1 分析模板效果260
12.2 產(chǎn)品成本變動趨勢跟蹤分析261
12.3 產(chǎn)品成本結構跟蹤分析262
第13章 預算執(zhí)行情況滾動跟蹤分析模板
13.1 基礎數(shù)據(jù)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