Excel數(shù)據(jù)處理與分析實(shí)戰(zhàn)寶典

第1章 數(shù)據(jù)處理基本技巧 1
1．1 認(rèn)識(shí)Excel表格 1
1．1．1 Excel表格中的對(duì)象 2
1．1．2 認(rèn)識(shí)Excel超級(jí)表 2
1．2 數(shù)據(jù)驗(yàn)證的強(qiáng)大功能 4
1．2．1 數(shù)據(jù)驗(yàn)證應(yīng)用之一：規(guī)范數(shù)據(jù)輸入 5
1．2．2 數(shù)據(jù)驗(yàn)證應(yīng)用之二：制作二級(jí)下拉菜單 6
1．2．3 名稱管理器突破數(shù)據(jù)驗(yàn)證限制 8
1．2．4 數(shù)據(jù)驗(yàn)證圈釋無效數(shù)據(jù) 10
1．3 神奇的選擇性粘貼 11
1．3．1 數(shù)據(jù)位置不變實(shí)現(xiàn)萬(wàn)元來回切換 12
1．3．2 選擇性粘貼清除超鏈接 13
1．3．3 選擇性粘貼轉(zhuǎn)換數(shù)據(jù)類型 13
1．3．4 跳過空單元格 14
1．3．5 合并條件格式 14
1．3．6 轉(zhuǎn)置 15
1．4 查找和替換 15
1．4．1 批量清除單元格中的空格或換行符 16
1．4．2 批量替換公式 18
1．4．3 批量替換通配符*或？ 18
1．4．4 批量插入年份 19
1．4．5 垂直數(shù)據(jù)轉(zhuǎn)換為水平數(shù)據(jù) 20
1．4．6 合并單元格區(qū)域中的文本 22
1．5 奇妙的定位條件 23
1．5．1 由上向下批量填充 24
1．5．2 左右批量填充 26
1．5．3 階梯狀批量填充 28
1．5．4 刪除對(duì)象實(shí)現(xiàn)文件瘦身 30
1．5．5 復(fù)制和粘貼可見單元格 31
1．5．6 定位空值實(shí)現(xiàn)批量求和 32
1．6 創(chuàng)建、關(guān)閉和刪除超鏈接 33
1．6．1 創(chuàng)建超鏈接 33
1．6．2 避免或關(guān)閉輸入超鏈接 35
1．6．3 刪除超鏈接 35
1．7 單元格格式設(shè)置 36
1．7．1 認(rèn)識(shí)各種數(shù)據(jù)格式 36
1．7．2 空單元格與空文本 36
1．7．3 自定義單元格格式 37
1．7．4 合并單元格 42
1．7．5 工作成果保護(hù) 44
1．7．6 批注打印和避免錯(cuò)誤值打印 47
1．8 基本技巧綜合應(yīng)用案例 48
1．8．1 用格式刷保持合并單元格樣式 48
1．8．2 提取混合單元格中的數(shù)字 50
1．8．3 查找和替換的妙用 52
第2章 數(shù)據(jù)專項(xiàng)處理技巧 56
2．1 條件格式 56
2．1．1 認(rèn)識(shí)條件格式 56
2．1．2 條件格式簡(jiǎn)單應(yīng)用 57
2．1．3 利用條件格式數(shù)據(jù)條替代條形圖的制作 59
2．1．4 用四色交通燈標(biāo)示財(cái)務(wù)狀態(tài) 59
2．1．5 對(duì)查詢的數(shù)據(jù)高亮顯示 60
2．1．6 條件格式在數(shù)據(jù)透視表中的應(yīng)用 63
2．1．7 標(biāo)識(shí)兩列中不同的物料名稱 64
2．2 排序、篩選與分類匯總 66
2．2．1 排序、篩選與分類匯總對(duì)數(shù)據(jù)的要求 66
2．2．2 按圖標(biāo)集進(jìn)行數(shù)據(jù)排序 66
2．2．3 使用自定義序列排序 68
2．2．4 利用排序生成成績(jī)單 71
2．2．5 分級(jí)顯示創(chuàng)建組――折疊式報(bào)表 72
2．3 合并計(jì)算 75
2．3．1 利用選擇性粘貼合并計(jì)算 75
2．3．2 利用公式合并計(jì)算 76
2．3．3 按位置進(jìn)行合并計(jì)算 77
2．3．4 按項(xiàng)目進(jìn)行合并計(jì)算 78
2．3．5 利用合并計(jì)算對(duì)比差異 79
2．4 名稱管理器 81
2．4．1 認(rèn)識(shí)名稱管理器 81
2．4．2 創(chuàng)建名稱的三種方式 82
2．4．3 名稱在函數(shù)中的應(yīng)用：合并報(bào)表編制 84
2．4．4 利用名稱制作動(dòng)態(tài)圖形 85
2．5 數(shù)據(jù)分列 86
2．5．1 固定寬度的數(shù)據(jù)分列 87
2．5．2 對(duì)SAP屏幕中復(fù)制出來的數(shù)據(jù)分列 89
2．5．3 按分隔符號(hào)數(shù)據(jù)分列 90
2．5．4 利用分列改變數(shù)據(jù)類型 91
2．5．5 分列法提取單元格中的公式 92
2．5．6 利用函數(shù)對(duì)數(shù)據(jù)分列 93
2．5．7 快速填充處理無法分列的數(shù)據(jù) 94
2．6 數(shù)據(jù)異常處理 95
2．6．1 數(shù)據(jù)異常常見問題及處理技巧 95
2．6．2 記事本“捉妖記” 96
2．6．3 利用函數(shù)清理異常數(shù)據(jù) 97
2．6．4 利用分列清理異常數(shù)據(jù) 98
2．6．5 利用Word清理異常數(shù)據(jù) 99
2．6．6 無法插入列或行表格的處理 100
2．6．7 刪除重復(fù)數(shù)據(jù) 101
2．6．8 利用SQL語(yǔ)句實(shí)現(xiàn)文件瘦身 104
2．7 數(shù)據(jù)導(dǎo)入與導(dǎo)出 106
2．7．1 Excel數(shù)據(jù)導(dǎo)入、導(dǎo)出簡(jiǎn)介 106
2．7．2 Excel SQL基礎(chǔ)知識(shí) 108
2．7．3 使用OLE DB導(dǎo)入外部數(shù)據(jù) 111
2．7．4 使用Microsoft Query查詢外部數(shù)據(jù) 116
2．7．5 聯(lián)合查詢、子查詢 124
2．7．6 SQL與數(shù)據(jù)透視表 127
2．7．7 導(dǎo)入文本格式數(shù)據(jù) 133
2．7．8 Excel與Internet數(shù)據(jù)交互 135
2．7．9 批量提取Excel中的圖片 140
2．7．10 導(dǎo)出到文本文件 141
2．8 數(shù)據(jù)專項(xiàng)處理技巧綜合案例 142
2．8．1 隔一空行生成工資條 142
2．8．2 巧用批量插入行 144
2．8．3 批量合并單元格 147
第3章 數(shù)據(jù)透視表基礎(chǔ) 150
3．1 認(rèn)識(shí)數(shù)據(jù)透視表 150
3．2 制作數(shù)據(jù)透視表的一般步驟 152
3．3 數(shù)據(jù)透視表的修改及其布局調(diào)整 154
3．4 數(shù)據(jù)透視表基礎(chǔ)操作系列 157
第4章 數(shù)據(jù)透視表與Power系列 169
4．1 在數(shù)據(jù)透視表中定義公式 169
4．2 對(duì)數(shù)據(jù)透視表中的項(xiàng)分組 174
4．3 利用名稱創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表 183
4．4 切片器在數(shù)據(jù)透視表中的應(yīng)用 185
4．5 單頁(yè)字段數(shù)據(jù)透視表 187
4．6 利用數(shù)據(jù)透視表轉(zhuǎn)換表結(jié)構(gòu) 192
4．7 PowerPivot和數(shù)據(jù)透視表 196
4．8 使用數(shù)據(jù)模型 199
4．9 利用PowerPivot和切片器制作銷售看板 203
4．10 Power Query逆操作二維表 213
4．11 利用Power Query展開BOM計(jì)算產(chǎn)品材料成本 216
第5章 Excel函數(shù)與公式 227
5．1 函數(shù)與公式基礎(chǔ) 227
5．2 公式中的引用 230
5．3 公式的查錯(cuò)與監(jiān)視 235
第6章 邏輯函數(shù) 239
6．1 邏輯函數(shù)介紹 239
6．2 邏輯函數(shù)案例：個(gè)人所得稅計(jì)算 241
6．3 邏輯函數(shù)綜合應(yīng)用1：業(yè)務(wù)員星級(jí)評(píng)定 242
6．4 邏輯函數(shù)綜合應(yīng)用2：應(yīng)收賬款賬齡分析模型 243
第7章 求和、統(tǒng)計(jì)函數(shù) 245
7．1 求和、統(tǒng)計(jì)函數(shù)介紹 245
7．2 求和函數(shù)應(yīng)用案例 246
7．2．1 多條件求和公式 246
7．2．2 模糊條件求和 250
7．2．3 幾個(gè)特殊方式求和 251
7．2．4 條件計(jì)數(shù) 253
7．2．5 不重復(fù)數(shù)據(jù)統(tǒng)計(jì) 256
7．2．6 頻率分布 257
7．2．7 不重復(fù)排名與中國(guó)式排名 258
7．2．8 線性插值法應(yīng)用 259
第8章 查找與引用函數(shù) 262
8．1 查找與引用函數(shù)介紹 262
8．2 VLOOKUP函數(shù)應(yīng)用案例 263
8．2．1 按列查詢 263
8．2．2 逆向查詢 263
8．2．3 多條件查詢 264
8．2．4 一對(duì)多查詢 265
8．2．5 模糊查找 266
8．2．6 巧用VLOOKUP核對(duì)銀行賬 267
8．3 LOOKUP函數(shù)應(yīng)用案例 269
8．3．1 LOOKUP向量和數(shù)組查詢基礎(chǔ) 269
8．3．2 數(shù)組型查找 270
8．3．3 分組查詢 271
8．3．4 單一條件查詢 272
8．3．5 多條件查找 273
8．3．6 在合并單元格內(nèi)查詢 274
8．4 INDEX函數(shù) 275
8．4．1 INDEX函數(shù)基本用法 275
8．4．2 INDEX函數(shù)引用形式 276
8．4．3 執(zhí)行雙向查找 276
8．4．4 創(chuàng)建動(dòng)態(tài)區(qū)域 277
8．5 OFFSET函數(shù) 280
8．5．1 OFFSET函數(shù)基本用法 280
8．5．2 在二維區(qū)域內(nèi)查找 281
8．5．3 儲(chǔ)值卡余額計(jì)算及查詢 282
8．5．4 OFFSET與動(dòng)態(tài)數(shù)據(jù)驗(yàn)證 283
8．5．5 按關(guān)鍵字設(shè)置智能記憶式下拉菜單 284
8．6 INDIRECT函數(shù) 285
8．6．1 認(rèn)識(shí)INDIRECT函數(shù) 285
8．6．2 匯總各分表數(shù)據(jù) 287
8．6．3 查詢特殊分表數(shù)據(jù) 289
8．6．4 查詢區(qū)域中的倒數(shù)第二個(gè)數(shù) 290
8．6．5 按最近值查詢 290
8．7 HYPERLINK函數(shù) 292
8．7．1 建立超鏈接并高亮顯示數(shù)據(jù)記錄 292
8．7．2 編制工作表目錄 293
8．7．3 取得硬盤指定目錄下的文件名 293
第9章 日期與時(shí)間函數(shù) 296
9．1 認(rèn)識(shí)日期與時(shí)間的本質(zhì) 296
9．2 返回與月份相關(guān)的數(shù)據(jù) 297
9．3 與星期、工作日有關(guān)的函數(shù) 298
9．4 利用假日函數(shù)巧解票據(jù)缺失問題 299
9．5 隱藏函數(shù)DATEDIF 300
9．6 時(shí)間函數(shù)計(jì)算應(yīng)用實(shí)例 301
第10章 文本函數(shù) 303
10．1 常見的文本函數(shù) 303
10．2 文本函數(shù)基礎(chǔ) 304
10．3 兩組文本函數(shù)用法比較 305
10．4 根據(jù)關(guān)鍵字確定費(fèi)用性質(zhì) 306
10．5 分離中英文 307
10．6 根據(jù)多個(gè)關(guān)鍵字確定結(jié)果 308
10．7 從路徑中提取文件名 309
10．8 付款模板設(shè)計(jì) 310
10．9 從文本中分離物料代碼 311
10．10 “文本函數(shù)之王”――TEXT函數(shù) 312
第11章 信息函數(shù) 315
11．1 常見的信息函數(shù) 315
11．2 檢驗(yàn)數(shù)據(jù)類型函數(shù) 315
11．3 CELL函數(shù)及其應(yīng)用 316
11．3．1 CELL函數(shù)概述 316
11．3．2 CELL函數(shù)應(yīng)用 317
11．4 根據(jù)關(guān)鍵字設(shè)置智能模糊查詢 318
第12章 數(shù)組公式 321
12．1 數(shù)組公式的概念與特性 321
12．2 單一單元格數(shù)組公式 324
12．2．1 單一單元格數(shù)組公式的兩個(gè)實(shí)例 324
12．2．2 MMULT函數(shù)應(yīng)用 326
12．2．3 應(yīng)收賬款余額賬齡的計(jì)算 327
12．2．4 一對(duì)多查詢經(jīng)典應(yīng)用 329
12．3 多單元格數(shù)組公式 331
12．3．1 條件求和 331
12．3．2 按年齡段統(tǒng)計(jì)辭職人數(shù)頻率分布 332
12．3．3 預(yù)測(cè)未來值 333
第13章 Excel圖形制作技巧 335
13．1 Excel圖表制作基礎(chǔ) 335
13．1．1 認(rèn)識(shí)Excel圖表要素 335
13．1．2 Excel圖表制作原則 336
13．1．3 Excel數(shù)據(jù)關(guān)系與圖表選擇 337
13．1．4 具體圖表制作應(yīng)注意的事項(xiàng) 338
13．2 Excel圖表制作技巧系列 340
13．2．1 快速向圖表追加數(shù)據(jù)系列 340
13．2．2 讓折線圖從縱軸開始 341
13．2．3 設(shè)置圖表互補(bǔ)色 342
13．2．4 自動(dòng)繪制參考線 342
13．2．5 將數(shù)據(jù)錯(cuò)行與空行組織 343
13．2．6 利用漸變填充美化圖形 345
13．2．7 使用漲跌柱線顯示預(yù)算與實(shí)際差異 346
13．2．8 添加誤差線 348
13．2．9 作圖前的數(shù)據(jù)排序 351
13．2．10 用顏色區(qū)分業(yè)績(jī)高低的柱形圖 353
13．2．11 平板圖制作 355
13．2．12 居于條形圖之間的分類軸標(biāo)簽 357
第14章 專業(yè)圖表制作 360
14．1 制作圓環(huán)圖 360
14．2 制作氣泡圖 362
14．3 制作迷你圖 365
14．4 矩陣圖制作 366
14．5 反轉(zhuǎn)條形圖 370
14．6 制作不等寬柱形圖 372
14．7 滑珠圖制作 375
14．8 不等距水平軸散點(diǎn)圖 378
14．9 不等距縱坐標(biāo)圖形 381
14．10 百分比堆積柱形圖 384
14．11 利用數(shù)據(jù)有效性創(chuàng)建動(dòng)態(tài)圖形 388
14．12 本量利分析動(dòng)態(tài)圖 389
14．13 利用名稱與控件制作動(dòng)態(tài)圖形 393
14．14 Power View基礎(chǔ) 397
14．15 Power View制作BI圖 400
第15章 VBA在數(shù)據(jù)處理中的應(yīng)用 404
15．1 制作目錄鏈接報(bào)表 404
15．2 利用循環(huán)分解連續(xù)發(fā)票號(hào)碼 405
15．3 VBA自定義函數(shù) 406
15．4 合并工作表 410
15．5 合并工作簿 411
15．6 拆分工作表 414
15．7 批量復(fù)制工作簿中的多張工作表到總表 414
附錄A 用數(shù)據(jù)標(biāo)準(zhǔn)化思維規(guī)范數(shù)據(jù) 417