Excel 高效辦公 數(shù)據(jù)處理與分析（第3版）

目 錄
第 1 章 數(shù)據(jù)的輸入
1．1 Excel可以用來做什么 2
1．1．1 Excel到底能做什么 2
1． 制作表單 2
2． 完成復(fù)雜的運(yùn)算 2
3． 建立圖表 2
4． 數(shù)據(jù)管理 2
5． 決策指示 3
1．1．2 3種不同用途的表——數(shù)據(jù)表、統(tǒng)計(jì)報(bào)表、表單 3
1．2 基本數(shù)據(jù)的輸入 4
1．2．1 文本型數(shù)據(jù)的輸入 4
1．2．2 數(shù)值型數(shù)據(jù)的輸入 5
1．2．3 貨幣型數(shù)據(jù)的輸入 6
1．2．4 會(huì)計(jì)專用型數(shù)據(jù)的輸入 7
1．2．5 日期型數(shù)據(jù)的輸入 7
1．2．6 使用自定義快速輸入編號(hào) 9
1．2．7 自定義數(shù)據(jù)的格式 9
1． 文本和空格 10
2． 小數(shù)點(diǎn)、空格、顏色和條件 11
3． 貨幣、百分比和科學(xué)計(jì)數(shù) 12
4． 百分比 13
5． 日期和時(shí)間 13
1．2．8 使用自定義格式隱藏零值 15
1．3 有規(guī)律數(shù)據(jù)的填充 16
1．3．1 填充數(shù)字序列 16
1．3．2 填充文本序列 18
1．4 相同數(shù)據(jù)的填充 18
1．5 利用數(shù)據(jù)驗(yàn)證，高效輸入數(shù)據(jù) 19
1．5．1 通過下拉列表填充 20
1．5．2 限定文本長度 21

第 2 章 數(shù)據(jù)的格式化
2．1 不規(guī)范表格及數(shù)據(jù)的整理技巧 24
2．1．1 表格結(jié)構(gòu)的規(guī)范與整理 24
2．1．2 快速刪除空白行、空白列 26
1． 手動(dòng)刪除 26
2． 通過篩選刪除 26
3． 使用函數(shù)刪除 27
4． 使用定位功能刪除 29
2．1．3 快速刪除重復(fù)值、重復(fù)記錄 30
1． 刪除重復(fù)項(xiàng) 31
2． 高級篩選法 31
2．1．4 不規(guī)范日期的整理技巧 32
1． 查找替換 32
2． 分列功能 33
2．1．5 取消合并單元格的整理技巧 34
2．2 套用Excel表格格式 36
2．2．1 套用系統(tǒng)自帶格式 36
2．2．2 自定義樣式 37
2．3 套用單元格樣式 39
2．3．1 套用系統(tǒng)自帶樣式 39
2．3．2 自定義樣式 40
2．4 條件格式的設(shè)置 43
2．4．1 突出顯示重點(diǎn)單元格 44
2．4．2 輔助識(shí)別數(shù)據(jù)大小 44
2．4．3 輔助用戶查看數(shù)據(jù)走向 45

第 3 章 排序、篩選與分類匯總
3．1 排序 50
3．1．1 單個(gè)關(guān)鍵字的排序 50
3．1．2 多個(gè)關(guān)鍵字的排序 51
3．1．3 自定義排序 52
3．2 篩選 53
3．2．1 自動(dòng)篩選 53
1． 單一條件的篩選 53
2． 快速篩選前N項(xiàng) 54
3． 按字體顏色、單元格顏色篩選 55
4． 多條件的篩選 56
3．2．2 高級篩選 57
1． 同時(shí)滿足多個(gè)條件的篩選 57
2． 滿足其中一個(gè)條件的篩選 58
3．3 分類匯總 59
3．3．1 簡單分類匯總 59
3．3．2 高級分類匯總 61
3．3．3 嵌套分類匯總 61
1． 刪除分類匯總 62
2． 分類匯總 62

第 4 章 公式與函數(shù)的應(yīng)用
4．1 認(rèn)識(shí)公式與函數(shù) 67
4．1．1 初識(shí)公式 67
1． 單元格引用 68
2． 運(yùn)算符 69
4．1．2 初識(shí)函數(shù) 69
1． 函數(shù)的基本構(gòu)成 70
2． 函數(shù)的種類 70
4．2 文本函數(shù) 70
4．2．1 LEFT函數(shù)——從左側(cè)截取字符 70
4．2．2 RIGHT函數(shù)——從右側(cè)截取字符 72
4．2．3 MID函數(shù)——截取中間字符 73
4．2．4 FIND函數(shù)——查找字符位置 74
4．2．5 LEN函數(shù)——計(jì)算字符串長度 76
4．2．6 TEXT函數(shù)——字符串“整容”器 78
4．3 數(shù)學(xué)與三角函數(shù) 80
4．3．1 SUM函數(shù)——輕松求和 80
4．3．2 SUMIF函數(shù)——單一條件求和 81
4．3．3 SUMIFS函數(shù)——多條件求和 82
4．3．4 SUMPRODUCT——乘積求和 83
1． 一個(gè)參數(shù) 83
2． 兩個(gè)參數(shù) 84
3． 多個(gè)參數(shù) 84
4． 按條件求和 85
4．3．5 SUBTOTAL函數(shù)——輕松分類匯總 86
4．3．6 MOD函數(shù)——求余計(jì)算器 90
4．3．7 INT函數(shù)——整數(shù)切割器 92
4．4 邏輯函數(shù) 94
4．4．1 IF函數(shù)——專精條件選擇 94
1． Excel 中的邏輯關(guān)系 94
2． 用于條件判斷的IF函數(shù) 94
4．4．2 OR函數(shù)——只要有一個(gè)值為真，結(jié)果就為真 95
4．4．3 AND函數(shù)——只要有一個(gè)值為假，結(jié)果就為假 97
4．4．4 IFS函數(shù)（2019）——多條件判斷很簡單 100
4．5 統(tǒng)計(jì)函數(shù) 103
4．5．1 COUNTA函數(shù)——非空格計(jì)算器 103
4．5．2 COUNT函數(shù)——數(shù)字計(jì)算器 104
4．5．3 MAX函數(shù)——極大值搜索器 105
4．5．4 MIN函數(shù)——極小值搜索器 106
4．5．5 AVERAGE函數(shù)——算數(shù)平均值計(jì)算器 107
4．5．6 COUNTIF函數(shù)——單條件自定義計(jì)算器 108
4．5．7 COUNTIFS函數(shù)——多條件自定義計(jì)算器 109
4．5．8 RANK．EQ函數(shù)——排序好幫手 111
4．6 查找與引用函數(shù) 112
4．6．1 VLOOKUP函數(shù)——縱向查找利器 112
4．6．2 HLOOKUP函數(shù)——橫向查找利器 115
4．6．3 MATCH函數(shù)——確定數(shù)據(jù)位置 117
4．6．4 LOOKUP函數(shù)——查找神器 119
1． LOOKUP函數(shù)進(jìn)行縱向查找 120
2． LOOKUP函數(shù)進(jìn)行橫向查找 122
3． LOOKUP函數(shù)進(jìn)行條件判斷 122
4． LOOKUP函數(shù)進(jìn)行逆向查詢 124
4．7 日期和時(shí)間函數(shù) 127
4．7．1 YEAR、MONTH、DAY函數(shù)——年、月、日函數(shù) 127
4．7．2 DATE函數(shù)——生成日期 129
4．7．3 DATEDIF函數(shù)——計(jì)算兩日期之差 132
4．7．4 EDATE函數(shù)——計(jì)算相差幾個(gè)月的日期 133
4．7．5 EMONTH函數(shù)——計(jì)算相差幾個(gè)月的月末日期 134
4．7．6 TODAY函數(shù)——計(jì)算當(dāng)前日期 134
4．8 財(cái)務(wù)函數(shù) 136
4．8．1 SLN函數(shù)——平均折舊 136
4．8．2 DDB函數(shù)——指定期間折舊 137
4．8．3 SYD函數(shù)——年數(shù)總計(jì)折舊 139
4．8．4 PMT函數(shù)——等額還款 140
4．8．5 PPMT函數(shù)——等額還款本金 141
4．8．6 IPMT函數(shù)——等額還款利息 142
4．9 定義名稱 144
4．9．1 認(rèn)識(shí)Excel中的名稱 144
4．9．2 定義名稱 145
1． 為數(shù)據(jù)區(qū)域定義名稱 145
2． 為數(shù)據(jù)常量定義名稱 146
3． 為公式定義名稱 147
4．9．3 編輯和刪除名稱 148
1． 編輯名稱 149
2． 刪除名稱 149
4．9．4 在公式中使用名稱 150
4．10 數(shù)組與數(shù)組公式 151
4．10．1 什么是數(shù)組 151
4．10．2 什么是數(shù)組公式 152
4．10．3 數(shù)組公式的應(yīng)用 152
4．11 函數(shù)輸入、編輯中的技巧 153
4．11．1 根據(jù)需求搜索函數(shù) 153
4．11．2 函數(shù)參數(shù)不用背的秘密 154
4．11．3 函數(shù)較長記不住怎么辦 154
4．11．4 事半功倍——快速輸入函數(shù) 155
4．11．5 通過單擊選擇的方式快速引用其他工作表數(shù)據(jù) 155

第 5 章 可視化分析工具——圖表
5．1 圖表的類型及應(yīng)用原則 158
5．1．1 柱形圖及其適用場合 158
5．1．2 折線圖及其適用場合 159
5．1．3 餅圖及其適用場合 159
5．1．4 條形圖及其適用場合 160
5．1．5 面積圖及其適用場合 160
5．1．6 XY散點(diǎn)圖及其適用場合 161
5．1．7 股價(jià)圖及其適用場合 161
5．1．8 雷達(dá)圖及其適用場合 162
5．1．9 樹狀圖及其適用場合 162
5．1．10 旭日圖及其適用場合 162
5．1．11 直方圖及其適用場合 163
5．1．12 瀑布圖及其適用場合 163
5．1．13 漏斗圖及其適用場合 164
5．2 圖表的創(chuàng)建 164
5．2．1 利用固定數(shù)據(jù)區(qū)域創(chuàng)建圖表 164
1． 連續(xù)區(qū)域的所有數(shù)據(jù) 165
2． 選定的部分?jǐn)?shù)據(jù) 165
5．2．2 利用固定常量創(chuàng)建圖表 166
5．2．3 利用定義名稱創(chuàng)建圖表 168
5．3 圖表的編輯 170
5．3．1 更改圖表類型 171
1． 更改整個(gè)圖表的圖表類型 171
2． 更改某個(gè)數(shù)據(jù)系列的圖表類型 172
5．3．2 編輯數(shù)據(jù)系列 173
1． 減少數(shù)據(jù)系列 173
2． 增加數(shù)據(jù)系列 173
5．3．3 編輯圖表標(biāo)題 175
5．3．4 編輯圖例 176
5．3．5 添加坐標(biāo)軸標(biāo)題 177
5．3．6 添加數(shù)據(jù)標(biāo)簽 178
5．3．7 添加趨勢線 179
5．4 格式化圖表 180
5．4．1 保證圖表的完整性 180
5．4．2 格式化圖表區(qū) 181
5．4．3 格式化繪圖區(qū) 183
5．4．4 格式化圖表標(biāo)題、坐標(biāo)軸標(biāo)題、圖例 183
5．4．5 格式化坐標(biāo)軸 186
5．4．6 格式化數(shù)據(jù)系列 187
5．4．7 格式化數(shù)據(jù)標(biāo)簽 189
5．5 制作高級圖表 190
5．5．1 用箭頭替代數(shù)據(jù)條 190
5．5．2 制作金字塔分布圖 191
5．5．3 將精美小圖應(yīng)用于圖表 195
5．5．4 雙軸復(fù)合圖表 198
5．6 制作動(dòng)態(tài)圖表 200
5．6．1 利用查找函數(shù)創(chuàng)建動(dòng)態(tài)圖表 200
5．6．2 利用INDIRECT函數(shù)創(chuàng)建動(dòng)態(tài)圖表 203
1． INDIRECT函數(shù)基本解析 203
2． ADDRESS函數(shù)基本解析 204
3． ROW函數(shù)基本解析 204
4． CELL函數(shù)基本解析 204
5． 創(chuàng)建動(dòng)態(tài)圖表 207

第 6 章 數(shù)據(jù)透視表與數(shù)據(jù)透視圖
6．1 創(chuàng)建數(shù)據(jù)透視表 210
6．1．1 認(rèn)識(shí)數(shù)據(jù)和數(shù)據(jù)透視表 210
6．1．2 字段與值 210
6．1．3 創(chuàng)建初始數(shù)據(jù)透視表 211
6．2 布局?jǐn)?shù)據(jù)透視表 213
6．2．1 數(shù)據(jù)透視表字段窗格 213
6．2．2 數(shù)據(jù)透視表的布局 214
1． 數(shù)據(jù)結(jié)構(gòu)的布局 214
2． 顯示方式的布局 214
6．3 格式化數(shù)據(jù)透視表 215
6．4 數(shù)據(jù)透視表的排序與篩選 216
6．4．1 數(shù)據(jù)透視表的排序 216
6．4．2 數(shù)據(jù)透視表的篩選 217
6．5 生成數(shù)據(jù)透視圖 219
6．6 數(shù)據(jù)透視表（圖）的高級應(yīng)用 220
6．6．1 使用切片器篩選 220
1． 插入并使用切片器 220
2． 設(shè)置切片器樣式 222
3． 一個(gè)切片器控制多個(gè)數(shù)據(jù)透視表（圖） 224
4． 刪除切片器 225
6．6．2 使用日程表篩選 225
6．7 數(shù)據(jù)透視表的計(jì)算 227
6．7．1 添加計(jì)算字段 228
6．7．2 添加計(jì)算項(xiàng) 229

第 7 章 數(shù)據(jù)分析工具的使用
7．1 安裝分析工具庫 233
7．2 模擬運(yùn)算表 233
7．2．1 單變量模擬運(yùn)算表 234
7．2．2 雙變量模擬運(yùn)算表 235
7．3 單變量求解 236
7．4 規(guī)劃求解 237
7．4．1 線性規(guī)劃概述 237
7．4．2 加載規(guī)劃求解 238
7．4．3 實(shí)例：建立規(guī)劃求解模型——人員最優(yōu)配置方案 238
7．5 分析工具庫 242
7．5．1 方差分析 242
7．5．2 相關(guān)系數(shù) 245
7．5．3 協(xié)方差 247
7．5．4 指數(shù)平滑 248
7．5．5 移動(dòng)平均 251

第 8 章 生產(chǎn)決策數(shù)據(jù)的處理
8．1 生產(chǎn)成本分析 254
8．1．1 知識(shí)點(diǎn) 254
1． 公式的應(yīng)用 254
2． 盈虧平衡點(diǎn) 254
3． IF函數(shù)的應(yīng)用 254
4． 單變量求解的應(yīng)用 254
8．1．2 成本分析的流程 254
8．1．3 實(shí)例：設(shè)備生產(chǎn)能力優(yōu)化決策分析 255
8．1．4 實(shí)例：量本利分析 257
1． 降低單位變動(dòng)成本 257
2． 降低固定成本 258
8．2 產(chǎn)品最優(yōu)組合決策分析 259
8．2．1 知識(shí)點(diǎn)：規(guī)劃求解 259
8．2．2 決策分析的步驟 259
8．2．3 實(shí)例：利潤最大化 259
8．2．4 實(shí)例：成本最小化 263

第 9 章 銷售數(shù)據(jù)的處理
9．1 公司銷售發(fā)展趨勢分析 269
9．1．1 知識(shí)點(diǎn) 269
1． 趨勢分析 269
2． 趨勢分析的影響因素 269
3． 同比和環(huán)比 270
4． 發(fā)展速度 270
5． 平均發(fā)展速度 270
6． POWER函數(shù) 271
7． 增長速度 271
9．1．2 實(shí)例：計(jì)算銷售額的同比增速 271
9．1．3 實(shí)例：計(jì)算銷售額的環(huán)比發(fā)展速度 272
9．1．4 實(shí)例：計(jì)算銷售額的發(fā)展速度 274
9．1．5 實(shí)例：計(jì)算銷售額的平均發(fā)展速度 276
9．1．6 實(shí)例：計(jì)算銷售額的增長速度 276
9．1．7 實(shí)例：制作分析圖表 277
1． 同比分析圖表 277
2． 環(huán)比分析圖表 280
3． 銷售走勢圖表 282
9．2 公司銷售相關(guān)分析 286
9．2．1 知識(shí)點(diǎn) 286
1． 什么是相關(guān)分析 286
2． 相關(guān)分析的分類 287
3． 相關(guān)表和相關(guān)圖 288
4． 相關(guān)系數(shù) 290
5． CORREL函數(shù) 290
9．2．2 實(shí)例：計(jì)算直通車與銷售額的相關(guān)程度 291
1． CORREL函數(shù)法 291
2． 數(shù)據(jù)分析工具庫 292
9．2．3 實(shí)例 直通車與銷售額相關(guān)性分析——散點(diǎn)圖 293
9．3 公司結(jié)構(gòu)分析 296
9．3．1 知識(shí)點(diǎn) 296
1． 什么是結(jié)構(gòu)分析 296
2． 結(jié)構(gòu)相對指標(biāo) 297
3． 餅圖 297
9．3．2 實(shí)例：公司份額結(jié)構(gòu)分析 297
9．3．3 實(shí)例：分析各類商品的貢獻(xiàn)率 301
9．4 門店的客流量分析 303
9．4．1 知識(shí)點(diǎn) 303
1． 描述分析 303
2． 刪除重復(fù)值 303
3． 數(shù)據(jù)透視表 303
4． 折線圖 303
9．4．2 實(shí)例：確定客流高峰期的時(shí)間段 303
1． 刪除重復(fù)訂單編號(hào) 304
2． 匯總各時(shí)間段的訂單量 305
3． 折線圖——清晰展示客流量 307

第 10 章 人力資源數(shù)據(jù)的處理
10．1 在職人員結(jié)構(gòu)分析 310
10．1．1 知識(shí)點(diǎn) 310
1． 自動(dòng)篩選 310
2． COUNTIFS函數(shù) 310
3． 數(shù)據(jù)透視表 310
10．1．2 實(shí)例：統(tǒng)計(jì)在職人員結(jié)構(gòu)信息 310
1． 篩選法 311
2． 函數(shù)法 315
3． 數(shù)據(jù)透視表法 320
10．1．3 實(shí)例：分析在職人員結(jié)構(gòu) 326
1． 簡單圖表 326
2． 多級聯(lián)動(dòng)圖表 328
10．2 分析人員流動(dòng)情況 331
10．2．1 知識(shí)點(diǎn) 331
1． SUMPRODUCT函數(shù) 331
2． YEAR函數(shù) 332
3． MONTH函數(shù) 332
10．2．2 實(shí)例：統(tǒng)計(jì)分析各部門的人員流動(dòng)情況 332
1． 計(jì)算入職人數(shù) 332
2． 計(jì)算離職人數(shù) 333
3． 分析人員流動(dòng) 335

第 11 章 財(cái)務(wù)數(shù)據(jù)的處理
11．1 財(cái)務(wù)比率分析 339
11．1．1 知識(shí)點(diǎn) 339
1． 變現(xiàn)能力比率 339
2． 資產(chǎn)管理比率 339
3． 負(fù)債比率 341
4． 盈利能力比率 342
11．1．2 實(shí)例：計(jì)算變現(xiàn)能力比率 343
11．1．3 實(shí)例：計(jì)算資產(chǎn)管理比率指標(biāo) 343
11．1．4 實(shí)例 計(jì)算負(fù)債比率指標(biāo) 346
11．1．5 實(shí)例：計(jì)算盈利能力比率指標(biāo) 348
11．2 財(cái)務(wù)比較分析 349
11．3 財(cái)務(wù)趨勢分析 354
11．4 杜邦分析 355
11．4．1 知識(shí)點(diǎn) 355
1． 資產(chǎn)凈利率 355
2． 權(quán)益乘數(shù) 356
3． 權(quán)益凈利率 356
11．4．2 實(shí)例：計(jì)算各財(cái)務(wù)比率 356