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

目 錄
第 1 章 數(shù)據(jù)的輸入
1．1 Excel可以用來做什么 2
1．1．1 Excel到底能做什么 2
1． 制作表單 2
2． 完成復雜的運算 2
3． 建立圖表 2
4． 數(shù)據(jù)管理 2
5． 決策指示 3
1．1．2 3種不同用途的表——數(shù)據(jù)表、統(tǒng)計報表、表單 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 會計專用型數(shù)據(jù)的輸入 7
1．2．5 日期型數(shù)據(jù)的輸入 7
1．2．6 使用自定義快速輸入編號 9
1．2．7 自定義數(shù)據(jù)的格式 9
1． 文本和空格 10
2． 小數(shù)點、空格、顏色和條件 11
3． 貨幣、百分比和科學計數(shù) 12
4． 百分比 13
5． 日期和時間 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ù)驗證，高效輸入數(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． 手動刪除 26
2． 通過篩選刪除 26
3． 使用函數(shù)刪除 27
4． 使用定位功能刪除 29
2．1．3 快速刪除重復值、重復記錄 30
1． 刪除重復項 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 突出顯示重點單元格 44
2．4．2 輔助識別數(shù)據(jù)大小 44
2．4．3 輔助用戶查看數(shù)據(jù)走向 45

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

第 4 章 公式與函數(shù)的應用
4．1 認識公式與函數(shù) 67
4．1．1 初識公式 67
1． 單元格引用 68
2． 運算符 69
4．1．2 初識函數(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ù)——計算字符串長度 76
4．2．6 TEXT函數(shù)——字符串“整容”器 78
4．3 數(shù)學與三角函數(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． 一個參數(shù) 83
2． 兩個參數(shù) 84
3． 多個參數(shù) 84
4． 按條件求和 85
4．3．5 SUBTOTAL函數(shù)——輕松分類匯總 86
4．3．6 MOD函數(shù)——求余計算器 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ù)——只要有一個值為真，結(jié)果就為真 95
4．4．3 AND函數(shù)——只要有一個值為假，結(jié)果就為假 97
4．4．4 IFS函數(shù)（2019）——多條件判斷很簡單 100
4．5 統(tǒng)計函數(shù) 103
4．5．1 COUNTA函數(shù)——非空格計算器 103
4．5．2 COUNT函數(shù)——數(shù)字計算器 104
4．5．3 MAX函數(shù)——極大值搜索器 105
4．5．4 MIN函數(shù)——極小值搜索器 106
4．5．5 AVERAGE函數(shù)——算數(shù)平均值計算器 107
4．5．6 COUNTIF函數(shù)——單條件自定義計算器 108
4．5．7 COUNTIFS函數(shù)——多條件自定義計算器 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ù)進行縱向查找 120
2． LOOKUP函數(shù)進行橫向查找 122
3． LOOKUP函數(shù)進行條件判斷 122
4． LOOKUP函數(shù)進行逆向查詢 124
4．7 日期和時間函數(shù) 127
4．7．1 YEAR、MONTH、DAY函數(shù)——年、月、日函數(shù) 127
4．7．2 DATE函數(shù)——生成日期 129
4．7．3 DATEDIF函數(shù)——計算兩日期之差 132
4．7．4 EDATE函數(shù)——計算相差幾個月的日期 133
4．7．5 EMONTH函數(shù)——計算相差幾個月的月末日期 134
4．7．6 TODAY函數(shù)——計算當前日期 134
4．8 財務(wù)函數(shù) 136
4．8．1 SLN函數(shù)——平均折舊 136
4．8．2 DDB函數(shù)——指定期間折舊 137
4．8．3 SYD函數(shù)——年數(shù)總計折舊 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 認識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ù)組公式的應用 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 圖表的類型及應用原則 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散點圖及其適用場合 161
5．1．7 股價圖及其適用場合 161
5．1．8 雷達圖及其適用場合 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． 選定的部分數(shù)據(jù) 165
5．2．2 利用固定常量創(chuàng)建圖表 166
5．2．3 利用定義名稱創(chuàng)建圖表 168
5．3 圖表的編輯 170
5．3．1 更改圖表類型 171
1． 更改整個圖表的圖表類型 171
2． 更改某個數(shù)據(jù)系列的圖表類型 172
5．3．2 編輯數(shù)據(jù)系列 173
1． 減少數(shù)據(jù)系列 173
2． 增加數(shù)據(jù)系列 173
5．3．3 編輯圖表標題 175
5．3．4 編輯圖例 176
5．3．5 添加坐標軸標題 177
5．3．6 添加數(shù)據(jù)標簽 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 格式化圖表標題、坐標軸標題、圖例 183
5．4．5 格式化坐標軸 186
5．4．6 格式化數(shù)據(jù)系列 187
5．4．7 格式化數(shù)據(jù)標簽 189
5．5 制作高級圖表 190
5．5．1 用箭頭替代數(shù)據(jù)條 190
5．5．2 制作金字塔分布圖 191
5．5．3 將精美小圖應用于圖表 195
5．5．4 雙軸復合圖表 198
5．6 制作動態(tài)圖表 200
5．6．1 利用查找函數(shù)創(chuàng)建動態(tài)圖表 200
5．6．2 利用INDIRECT函數(shù)創(chuàng)建動態(tài)圖表 203
1． INDIRECT函數(shù)基本解析 203
2． ADDRESS函數(shù)基本解析 204
3． ROW函數(shù)基本解析 204
4． CELL函數(shù)基本解析 204
5． 創(chuàng)建動態(tài)圖表 207

第 6 章 數(shù)據(jù)透視表與數(shù)據(jù)透視圖
6．1 創(chuàng)建數(shù)據(jù)透視表 210
6．1．1 認識數(shù)據(jù)和數(shù)據(jù)透視表 210
6．1．2 字段與值 210
6．1．3 創(chuàng)建初始數(shù)據(jù)透視表 211
6．2 布局數(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ù)透視表（圖）的高級應用 220
6．6．1 使用切片器篩選 220
1． 插入并使用切片器 220
2． 設(shè)置切片器樣式 222
3． 一個切片器控制多個數(shù)據(jù)透視表（圖） 224
4． 刪除切片器 225
6．6．2 使用日程表篩選 225
6．7 數(shù)據(jù)透視表的計算 227
6．7．1 添加計算字段 228
6．7．2 添加計算項 229

第 7 章 數(shù)據(jù)分析工具的使用
7．1 安裝分析工具庫 233
7．2 模擬運算表 233
7．2．1 單變量模擬運算表 234
7．2．2 雙變量模擬運算表 235
7．3 單變量求解 236
7．4 規(guī)劃求解 237
7．4．1 線性規(guī)劃概述 237
7．4．2 加載規(guī)劃求解 238
7．4．3 實例：建立規(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 移動平均 251

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

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

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

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