Excel與數據處理(第6版)（簡體書）

本書較全面地介紹了Excel 2016在數據的組織、管理、運算、分析、圖表處理和商業智能等方面的強大功能，主要包括：數據輸入、數組公式、圖表設計、函數、名稱、表格結構變換、表與結構化引用、數據模型、數據倉庫、M語言、DAX語言等基礎知識；Excel與外部數據交換、Microsoft Query、SQL等數據鏈接和查詢功能；數據排序、查找、透視、篩選、迷你圖、切片器、分類匯總、數據建模等數據管理功能；數據審核、單變量求解、模擬運算、方案分析、規劃求解、度量公式等數據運算和分析功能；Power Query數據清洗與整合、Power Pivot數據建模與分析、Power View數據可視化等商業智能數據處理方面的內容。

杜茂康，男，重慶郵電大學教授，主要研究方向：信息系統設計與研究、數據庫系統應用、商務數據挖掘。
出版教材8部，英文譯著3部，主持和參與了省部級以上項目6項、信息化平臺建設方面的橫向課題3項，獲得省部級教成果獎4項。

前 言
Microsoft Excel是最優秀的電子表格軟件之一，具有強大的數據處理和分析能力，在各行各業中應用廣泛，是財會人員、證券從業人員、辦公室文秘、科技人員、企業管理者不可缺少的表格處理、圖表製作和數據分析的理想工具，通常用於賬務處理、證券圖表分析、人事檔案管理、績效工資計算、科研數據仿真、經營數據分析……
本書正是以上述題材為核心，介紹Excel在數據處理方面的強大功能。本書自2002年第1版出版以來，受到讀者好評，不斷有讀者結合自身實際發來郵件，提出了在用Excel進行辦公事務處理過程中的一些疑難問題。這些疑難問題和讀者的關愛是本書不斷修訂的源泉，那些被不同讀者多次問及或富有啟發性的辦公實例已被整理成了書中的案例，使本書更具實用價值。
但是近年來，隨著各國對網絡經濟的重視，互聯網成了人們創新、創業的基礎平臺和工具，基於社交網絡、自媒體、商業網站產生的數據呈現爆炸式的增長，企業經營者需要從產生速度越來越快、規模越來越龐大、結構越來越複雜化和多樣化的大數據環境中快速挖掘和分析出價值巨大的知識，實現企業的商業智能。微軟公司適時推出了它的自助式商業智能工具――Power BI，該軟件由Power Query、Power Pivot和Power View三部分構成，分別實現了異構數據的清洗與整合、數據建模與分析、數據可視化功能，以幫助經營決策人員和數據分析師能夠快速、便捷地對各類來源不一、結構不同的海量數據進行分析，挖掘出有價值的知識，為企業經營者提供決策支持。
Power BI是一個輕量級的數據分析工具，功能強大但簡單易學，對於傳統Excel難以解決的多源數據清洗、整合、建模分析，以及具有年、季、月重複性特徵的數據處理工作提出了智能化的解決方案，是對大數據環境下Excel數據分析的有力補充。Power BI的三個組成部分既可以整合運行，也可以獨立使用，並以加載項的方式提供給了Excel 2013。Power Query因實用性強已經被集成在Excel 2016中，可以直接使用了。
商業智能是數據分析的方向，Excel已經對此做出了適應性和前瞻性的調整，在用它進行數據處理時，可以優先使用其提供的Power BI新功能，以提高工作效率或智能化地完成週期性的重複工作。因此，Power BI插件的學習已是Excel數據處理不可缺少的內容了。
由於內容太多，本書本次修訂的主要變化就是對Excel的傳統內容進行了精減，但增加了商業智能數據處理的內容。在第5版的基礎上，本書刪去了第9章“Excel與財務分析”和第12章“VBA程序設計”，重構了第6章“圖表分析”的內容，並對其餘部分的某些章節結構和內容進行了整合和更新。全書共分為12章。
第1章介紹Excel的基本概念、單元格和工作表常規操作、數據查看和打印設置等應用，讓讀者從電子表格的角度瞭解Excel的主要功能，具備進一步學習Excel的基礎知識。
第2章介紹Excel的數據輸入功能。針對不同數據類型，本章介紹不同的數據輸入方法，尤其是大批量有規律數據的高效輸入方法。
第3章介紹工作表、單元格和數據的格式化功能，主要包括：表格式套用、主題、條件格式、數據格式化和日期格式化等內容，以及工作表背景、邊框和底紋等內容的格式化方法。
第4章介紹公式與函數在數據處理中的特殊應用，特別是數組公式、名字和函數在大批量數據處理中的應用方法與技巧，以及錯誤信息的處理方法等。
第5章介紹Excel的數據管理和分析功能，包括數據的排序、篩選、分類匯總、數據透視表、數據鏈接、切片器和多工作表的合併計算等。
第6章介紹Excel與外部數據的交換方法。本章的主要目的是把Excel作為外部數據庫的分析工具，在專業數據庫系統中進行數據的存儲、處理和更新等操作，在Excel中對保存在專業數據庫中的數據進行分析，製作報表或分析圖表等，提高工作效率。
第7章介紹動態報表、數據查找與提取、報表結構變換與調整等功能，包括表、結構化引用、數據庫函數、查找引用類函數、文本查找函數、SQL查詢，以及工作表中各類數據的查找技術。本章以案例形式介紹了Excel的多種數據查找方法，大多數案例是從辦公實例和讀者疑問中抽象出來的，這對於解決工作中的實際問題有著較強的啟發性。
第8章介紹Excel圖表類型、數據源表設計、圖表選擇與設計、格式化和典型應用。
第9章介紹Excel數據分析工具的應用，主要包括數據的審核和追蹤、單變量求解、方案設計、線性回歸分析和規劃求解等內容。
第10～12章介紹Excel商業智能基礎。這3章是一個完整的結構，對應商業智能的數據清洗與轉換、數據建模與分析、數據可視化三個階段。第10章介紹Power Query超級查詢功能，數據清洗、轉換的方法，M語言，多工作簿與工作表的智能合併；第11章介紹Power Pivot數據建模基礎，包括關係模型、數據倉庫的理論基礎與建立方法，多維度數據分析，DAX與度量公式等內容；第12章介紹Power View數據可視化、數據模型分析結果圖表設計和儀錶板製作。
本書注重用實例來介紹Excel的使用方法和技巧，每章都有從實際工作中精心提煉出來的應用案例，這些案例是從一些辦公實例中抽象出來的，對於提高日常辦公事務中數據管理的效率有較強的參考價值。為了方便教師教學和讀者在實際操作中理解書中的案例和方法，本書準備了以下輔助資源：本書各章所應用的Excel實例工作簿、習題工作簿（含參考答案），以及教學課件。這些資源可從網站http://www.hxedu.com.cn免費下載。
本書由杜茂康、劉友軍、武建軍、李昌兵、王永、曹慧英編寫。劉友軍編寫了第1～4章，武建軍編寫了第5～6章，李昌兵編寫了第7章，王永編寫了第8章，曹慧英編寫了第9章，杜茂康編寫了第10～12章。全書由杜茂康審校和統稿。
特別感謝本書的策劃編輯章海濤老師，沒有他的策劃、指導、無私幫助和辛勤工作，就不會有本書的出版！
本書主要從數據的組織、管理、計算、分析和商務智能方面討論Excel的功能，書中所論並不完美。鑒於作者水平有限，經驗不足，書中錯誤與疏漏之處在所難免，懇請讀者給予指正。

作 者
2019年2月

目 錄
第1章 Excel基礎 1
1.1 Excel的基本概念 1
1.2.1 工作簿與工作表的關係 4
1.2.2 創建工作簿 5
1.2.3 工作簿文件格式與兼容性 6
1.3 工作簿的基本操作 7
1.3.1 工作表操作 7
1.3.2 單元格操作 8
1.4 公式和單元格引用 12
1.4.1 運算符及其優先級 13
1.4.2 單元格引用 13
1.4.3 內部引用與外部引用 16
1.5 查看工作表數據 17
1.5.1 按照打印的“頁面布局”視圖查看工作表數據 17
1.5.2 按照打印的“分頁預覽”視圖查看工作表數據 18
1.5.3 在多窗口中查看數據 19
1.5.4 在拆分窗口中查看數據 20
1.5.5 凍結行、列 21
1.6 工作表打印的常見問題 21
1.6.1 打印紙及頁面設置 21
1.6.2 打印預覽 22
1.6.3 縮放打印比例以適應紙張 23
1.6.4 打印標題和頁碼 23
小結 25
習題1 25
第2章 數據輸入與編輯基礎 28
2.1 Excel的數據類型 28
2.2 基本數據類型的輸入 29
2.2.1 輸入數值 30
2.2.2 輸入文本 31
2.2.3 輸入日期和時間 31
2.2.4 用Tab和Enter鍵輸入選定區域的數據 32
2.3 輸入相同數據 32
2.3.1 在多個工作表中輸入相同的區域數據 33
2.3.2 按Ctrl+Enter組合鍵或用填充輸入相同數據 34
2.4 編號的輸入 34
2.4.1 通過複製生成連續編號 35
2.4.2 用填充序列生成連續編號 35
2.4.3 利用自定義格式產生特殊編號 36
2.4.4 超長數字編號的輸入 37
2.5 用“＆”組合多個單元格數據 39
2.6 採用下拉列表選擇輸入數據 40
2.6.1 通過“數據驗證”創建數據輸入下拉列表 40
2.6.2 利用右鍵快捷菜單從下拉列表中進行數據輸入 41
2.6.3 利用窗體組合框創建下拉式列表 41
2.6.4 用數據驗證設計二級下拉列表 42
2.7 設置受限輸入數據 43
2.8 利用填充序列輸入數據 45
2.8.1 內置序列的輸入 45
2.8.2 自定義序列 46
2.9 行列轉置輸入 47
2.10 利用隨機函數產生批量仿真數據 48
2.11 編輯工作表數據 49
2.11.1 複製、剪切、移動、粘貼、撤銷和恢復 49
2.11.2 在編輯欄中處理長公式 52
2.11.3 應用自動更正修正錯誤 53
2.11.4 單元格批註 53
小結 54
習題2 54
第3章 數據格式化 56
3.1 工作表的格式化 56
3.1.1 自動套用表格式 56
3.1.2 應用主題格式化工作表 59
3.1.3 應用單元格樣式格式化工作表 60
3.1.4 設置工作表的邊框和底紋 61
3.2 格式化數字 62
3.2.1 Excel的默認數字格式 62
3.2.2 使用系統提供的數字格式 63
3.2.3 自定義格式 64
3.2.4 文本格式化函數TEXT 66
3.3 格式化日期 68
3.4 條件格式 70
3.4.1 條件格式概述 70
3.4.2 條件格式規則 73
3.4.3 自定義條件格式 74
3.4.4 條件格式規則的管理 76
3.5 自定義格式的應用 77
小結 78
習題3 79
第4章 數組公式、名稱和函數 82
4.1 數組公式及其應用 82
4.1.1 數組公式的應用 82
4.1.2 使用數組公式的規則 84
4.1.3 數組擴展 84
4.1.4 二維數組 85
4.2 公式的循環引用 86
4.3 名稱 87
4.3.1 名稱概述 87
4.3.2 名稱的定義 88
4.3.3 定義常量名稱 89
4.3.4 名稱應用舉例 90
4.3.5 名稱管理器 92
4.4 函數簡介 93
4.5 邏輯函數 94
4.5.1 比較運算和邏輯運算 95
4.5.2 邏輯函數AND、NOT、OR、TRUE、FALSE 95
4.5.3 條件函數IF 96
4.6 工作表常用統計函數 98
4.6.1 匯總求和函數 98
4.6.2 平均值函數 101
4.6.3 統計個數的函數 102
4.7 數學和三角函數 106
4.8 日期及時間函數 108
4.8.1 Excel的日期系統 108
4.8.2 函數YEAR、MONTH、DAY、NOW、TODAY 109
4.8.3 函數WEEKDAY和NETWORKDAYS 109
4.8.4 函數EDATE、DATEDIF和YEARFRAC 110
4.8.5 日期函數舉例――計算工齡、小時加班工資 111
4.9 常用文本函數 113
4.9.1 文本轉換與合併函數 113
4.9.2 文本子串提取函數 115
4.9.3 文本重複、清理和替換函數 116
4.10 錯誤信息函數 118
4.10.1 Excel的常見錯誤信息 118
4.10.2 Excel錯誤信息函數 119
小結 121
習題4 121
第5章 數據管理與數據透視 124
5.1 數據排序 124
5.1.1 排序規則 124
5.1.2 數字排序 125
5.1.3 漢字與字符排序 126
5.1.4 自定義排序次序 127
5.1.5 多關鍵字排序 128
5.2 數據篩選 129
5.2.1 自動篩選 129
5.2.2 條件區域 131
5.2.3 高級篩選 132
5.2.4 使用高級篩選提取無重複的數據行 135
5.3 數據分類與匯總 136
5.3.1 分類匯總概述 136
5.3.2 建立分類匯總 137
5.3.3 高級分類匯總 138
5.3.4 嵌套分類匯總 138
5.3.5 刪除分類匯總 139
5.4 數據透視表 139
5.4.1 數據透視表概述 140
5.4.2 建立數據透視表 141
5.4.3 查看透視表中數據的具體來源 143
5.4.4 利用報表篩選創建分頁顯示 143
5.4.5 建立多字段的數據透視表 144
5.4.6 修改匯總函數並多次透視同一字段 145
5.4.7 修改透視表數據的顯示方式 145
5.4.8 顯示數據項的明細數據 146
5.4.9 對時間按年、季、月進行分組透視 147
5.4.10 篩選器在數據透視表和透視圖的應用 148
5.5 數據鏈接與多工作簿匯總 149
5.6 合併計算與多工作簿、工作表匯總 151
5.6.1 多工作簿內表格結構不同的合併計算 151
5.6.2 同一工作簿內結構相同的多工作表匯總 153
小結 154
習題5 154
第6章 Excel與外部數據交換 157
6.1 Excel與數據庫概述 157
6.2 Microsoft Query與外部數據庫訪問 158
6.2.1 外部數據庫與Microsoft Query概述 158
6.2.2 建立或指定Microsoft Query的數據源 160
6.2.3 操作Microsoft Query 162
6.2.4 在Microsoft Query中進行多表查詢 165
6.2.5 條件查詢 167
6.3 Excel與其他文件類型的相互轉換 168
6.3.1 Excel和Web頁之間的轉換 169
6.3.2 Excel與Access數據交換 171
6.3.3 Excel與文本文件的數據轉換 176
小結 177
習題6 178
第7章 動態報表與數據查找 181
7.1 表格與動態報表 181
7.1.1 表格 182
7.1.2 結構化引用和動態報表 184
7.2 D函數與動態報表 188
7.2.1 D函數簡介 188
7.2.2 D函數與表結合構造動態數據分析報表 189
7.3 查找大工作表的特定數據行 191
7.4 查找與引用函數 192
7.4.1 用行、列函數定位與提取數據 192
7.4.2 用INDIRECT函數和名稱查詢其他工作表中的數據 194
7.4.3 用ADDRESS和OFFSET函數進行定位查找與數據提取 195
7.4.4 用CHOOSE函數進行值查詢 198
7.4.5 用MATCH和INDEX函數構造靈活多樣的查詢 199
7.4.6 用LOOKUP函數查找不同工作表中的數據 202
7.4.7 用VLOOKUP函數進行表查找 203
7.5 用數據庫函數進行查找統計 208
7.6 用數組公式進行數據查找和匯總統計 209
7.7 文本比對和查找 210
7.8 用SQL查詢工作表數據 212
7.8.1 Excel SQL查詢基礎 213
7.8.2 SQL條件查詢和多表數據查詢 214
7.8.3 使用SQL進行分組統計查詢 216
7.8.4 用SQL從重複數據中提取不重複數據 218
7.9 數據提取與表格結構變換 218
7.9.1 隨機編排座次表問題 218
7.9.2 提取間隔數據行問題 220
7.9.3 數據表行列轉換問題 222
小結 224
習題7 224
第8章 圖表處理 229
8.1 Excel圖表基礎 229
8.1.1 認識Excel圖表 229
8.1.2 圖表設計功能簡介 230
8.1.3 圖表格式化 233
8.2 圖表建立的一般過程 236
8.2.1 插入初始圖表 236
8.2.2 圖表設計 23