Microsoft Excel 365學習手冊（第11版）

第 I 部分  Excel基礎知識
第1章  Excel簡介 3
1.1  了解 Excel的用途 3
1.2  了解工作簿和工作表 4
1.3  在工作表中導航 6
1.3.1  用鍵盤導航 6
1.3.2  用鼠標導航 7
1.4  使用功能區(qū) 8
1.4.1  功能區(qū)選項卡 8
1.4.2  上下文選項卡 9
1.4.3  功能區(qū)中的命令類型 10
1.4.4  用鍵盤訪問功能區(qū) 11
1.5  使用快捷菜單 13
1.6  自定義快速訪問工具欄 13
1.7  使用對話框 15
1.7.1  導航對話框 15
1.7.2  使用選項卡式對話框 16
1.8  使用任務窗格 16
1.9  創(chuàng)建第一個Excel工作簿 17
1.9.1  開始創(chuàng)建工作表 17
1.9.2  填充月份名稱 18
1.9.3  輸入銷售數(shù)據(jù) 18
1.9.4  設置數(shù)字的格式 19
1.9.5  讓工作表看上去更有吸引力 19
1.9.6  對值求和 20
1.9.7  創(chuàng)建圖表 20
1.9.8  打印工作表 21
1.9.9  保存工作簿 21
第2章  輸入和編輯工作表數(shù)據(jù) 23
2.1  了解數(shù)據(jù)類型 23
2.1.1  數(shù)值 23
2.1.2  文本輸入 24
2.1.3  公式 24
2.1.4  錯誤值 25
2.2  在工作表中輸入文本和值 25
2.2.1  輸入數(shù)值 25
2.2.2  輸入文本 25
2.2.3  使用輸入模式 26
2.3  在工作表中輸入日期和時間 26
2.3.1  輸入日期值 27
2.3.2  輸入時間值 27
2.4  修改單元格內(nèi)容 27
2.4.1  刪除單元格內(nèi)容 28
2.4.2  替換單元格內(nèi)容 28
2.4.3  編輯單元格內(nèi)容 28
2.4.4  學習一些實用的數(shù)據(jù)輸入方法 29
2.5  應用數(shù)字格式 35
2.5.1  使用自動數(shù)字格式 35
2.5.2  通過功能區(qū)設置數(shù)字格式 36
2.5.3  使用快捷鍵設置數(shù)字格式 36
2.5.4  使用“設置單元格格式”對話框設置數(shù)字格式 37
2.5.5  添加自定義數(shù)字格式 39
2.6  在平板電腦上使用Excel 39
2.6.1  探索Excel的平板電腦界面 39
2.6.2  在平板電腦上輸入公式 40
2.6.3  “繪圖”功能區(qū)簡介 41
第3章  基本工作表操作 43
3.1  學習Excel工作表基本原理 43
3.1.1  使用Excel窗口 43
3.1.2  激活工作表 45
3.1.3  向工作簿添加新工作表 46
3.1.4  刪除不再需要的工作表 46
3.1.5  更改工作表名稱 47
3.1.6  更改工作表選項卡顏色 47
3.1.7  重新排列工作表 47
3.1.8  隱藏和取消隱藏工作表 48
3.2  控制工作表視圖 49
3.2.1  放大或縮小視圖以便更好地查看工作表 49
3.2.2  在多個窗口中查看工作表 50
3.2.3  并排比較工作表 51
3.2.4  將工作表窗口拆分成窗格 51
3.2.5  通過凍結窗格來保持顯示標題 52
3.2.6  使用監(jiān)視窗口監(jiān)視單元格 53
3.3  使用行和列 53
3.3.1  選擇行和列 54
3.3.2  插入行和列 54
3.3.3  刪除行和列 55
3.3.4  更改列寬和行高 55
3.3.5  隱藏行和列 56
第4章  使用Excel區(qū)域和表格 59
4.1  單元格和區(qū)域簡介 59
4.1.1  選擇區(qū)域 60
4.1.2  選擇完整的行和列 60
4.1.3  選擇非連續(xù)的區(qū)域 61
4.1.4  選擇多工作表區(qū)域 61
4.1.5  選擇特殊類型的單元格 63
4.1.6  通過搜索選擇單元格 65
4.2  復制或移動區(qū)域 67
4.2.1  使用功能區(qū)中的命令進行復制 67
4.2.2  使用快捷菜單命令進行復制 68
4.2.3  使用快捷鍵進行復制 69
4.2.4  使用拖放方法進行復制或移動 70
4.2.5  復制到相鄰的單元格 70
4.2.6  向其他工作表復制區(qū)域 71
4.2.7  使用 Office剪貼板進行粘貼 71
4.2.8  使用特殊方法進行粘貼 72
4.2.9  使用“選擇性粘貼”對話框 74
4.3  對區(qū)域使用名稱 75
4.3.1  在工作簿中創(chuàng)建區(qū)域名稱 76
4.3.2  管理名稱 78
4.4  向單元格添加批注 79
4.4.1  顯示批注 80
4.4.2  回復批注 81
4.4.3  編輯批注和回復 81
4.4.4  刪除批注和回復 81
4.4.5  關閉批注會話 81
4.5  添加單元格注釋 82
4.5.1  顯示注釋 83
4.5.2  設置注釋格式 83
4.5.3  編輯注釋 84
4.5.4  刪除注釋 85
4.6  使用表格 85
4.6.1  理解表格的結構 85
4.6.2  創(chuàng)建表格 86
4.6.3  向表格添加數(shù)據(jù) 87
4.6.4  排序和篩選表格數(shù)據(jù) 87
4.6.5  更改表格外觀 92
第5章  設置工作表格式 95
5.1  了解格式設置工具 95
5.1.1  使用“開始”選項卡中的格式設置工具 96
5.1.2  使用浮動工具欄 96
5.1.3  使用“設置單元格格式”對話框 97
5.2  設置工作表格式 97
5.2.1  使用字體來設置工作表的格式 97
5.2.2  更改文本對齊方式 99
5.2.3  使用顏色和陰影 102
5.2.4  添加邊框和線條 103
5.3  使用條件格式 105
5.3.1  指定條件格式 105
5.3.2  使用圖形條件格式 105
5.3.3  創(chuàng)建基于公式的規(guī)則 109
5.3.4  條件格式公式示例 110
5.3.5  使用條件格式 113
5.4  使用命名樣式方便地設置格式 114
5.4.1  應用樣式 114
5.4.2  修改現(xiàn)有樣式 115
5.4.3  創(chuàng)建新樣式 116
5.4.4  從其他工作簿合并樣式 116
5.4.5  使用模板控制樣式 116
5.5  了解文檔主題 117
5.5.1  應用主題 118
5.5.2  自定義主題 119
第6章  了解Excel文件和模板 121
6.1  創(chuàng)建新工作簿 121
6.2  打開現(xiàn)有工作簿 122
6.2.1  篩選文件名 123
6.2.2  選擇文件顯示首選項 124
6.3  保存工作簿 125
6.4  使用自動恢復 126
6.4.1  恢復當前工作簿的版本 127
6.4.2  恢復未保存的工作 127
6.4.3  配置自動恢復 127
6.5  使用密碼保護工作簿 127
6.6  組織文件 128
6.7  其他工作簿信息選項 128
6.7.1  “保護工作簿”選項 128
6.7.2  “檢查問題”選項 129
6.7.3  “版本歷史”選項 129
6.7.4  “管理工作簿”選項 129
6.7.5  “瀏覽器視圖”選項 129
6.7.6  “兼容模式”部分 129
6.8  關閉工作簿 130
6.9  保護工作的安全 130
6.10  使用模板 131
6.10.1  探索Excel模板 131
6.10.2  使用默認模板 133
6.10.3  使用自定義工作簿模板 134
第7章  打印工作成果 137
7.1  基本打印功能 137
7.2  更改頁面視圖 138
7.2.1  “普通”視圖 138
7.2.2  “頁面布局”視圖 139
7.2.3  “分頁預覽”視圖 140
7.3  調(diào)整常用頁面設置 141
7.3.1  選擇打印機 142
7.3.2  指定要打印的內(nèi)容 142
7.3.3  更改頁面方向 143
7.3.4  指定紙張大小 143
7.3.5  打印幾份報表 143
7.3.6  調(diào)整頁邊距 144
7.3.7  了解分頁符 145
7.3.8  打印行和列標題 145
7.3.9  對打印輸出進行縮放設置 146
7.3.10  打印單元格網(wǎng)格線 147
7.3.11  打印行和列標題 147
7.3.12  使用背景圖像 147
7.4  為報表添加頁眉或頁腳 148
7.4.1  選擇預定義的頁眉或頁腳 149
7.4.2  了解頁眉和頁腳元素代碼 149
7.4.3  其他頁眉和頁腳選項 150
7.5  其他與打印相關的主題 151
7.5.1  在工作表之間復制頁面設置 151
7.5.2  禁止打印特定的單元格 151
7.5.3  禁止打印對象 152
7.5.4  為工作表創(chuàng)建自定義視圖 152
7.5.5  創(chuàng)建PDF文件 153
第8章  自定義Excel用戶界面 155
8.1  自定義快速訪問工具欄 155
8.1.1  快速訪問工具欄簡介 155
8.1.2  向快速訪問工具欄添加新命令 156
8.1.3  其他快速訪問工具欄操作 158
8.2  自定義功能區(qū) 159
8.2.1  自定義功能區(qū)的目的 159
8.2.2  可以自定義的項 159
8.2.3  如何自定義功能區(qū) 160
8.2.4  重置功能區(qū) 161
第 II 部分  使用公式和函數(shù)
第9章  公式和函數(shù)簡介 165
9.1  了解公式的基礎知識 165
9.1.1  在公式中使用運算符 166
9.1.2  了解公式中的運算符優(yōu)先級 167
9.1.3  在公式中使用函數(shù) 169
9.2  在工作表中輸入公式 171
9.2.1  通過指向輸入公式 172
9.2.2  將區(qū)域名稱粘貼到公式中 173
9.2.3  向公式中插入函數(shù) 173
9.2.4  函數(shù)輸入提示 174
9.3  編輯公式 175
9.4  在公式中使用單元格引用 176
9.4.1  使用相對、絕對和混合引用 176
9.4.2  更改引用類型 178
9.4.3  引用工作表外部的單元格 178
9.5  公式變量簡介 179
9.5.1  理解LET函數(shù) 180
9.5.2  公式變量的應用 180
9.6  在表格中使用公式 181
9.6.1  匯總表格中的數(shù)據(jù) 181
9.6.2  在表格中使用公式 183
9.6.3  引用表格中的數(shù)據(jù) 184
9.7  更正常見的公式錯誤 184
9.7.1  處理循環(huán)引用 185
9.7.2  指定在什么時候計算公式 186
9.8  使用高級命名方法 187
9.8.1  為常量使用名稱 187
9.8.2  為公式使用名稱 188
9.8.3  使用區(qū)域交集 188
9.8.4  對現(xiàn)有引用應用名稱 189
9.9  使用公式 190
9.9.1  不使用硬編碼數(shù)值 190
9.9.2  將編輯欄用作計算器 190
9.9.3  精確復制公式 190
9.9.4  將公式轉換為數(shù)值 191
第10章  理解和使用數(shù)組公式 193
10.1  理解遺留數(shù)組公式 194
10.1.1  遺留數(shù)組公式的示例 194
10.1.2  編輯遺留的數(shù)組公式 194
10.2  動態(tài)數(shù)組簡介 195
10.2.1  理解溢出區(qū)域 197
10.2.2  引用溢出區(qū)域 198
10.3  探索動態(tài)數(shù)組函數(shù) 199
10.3.1  SORT函數(shù) 200
10.3.2  SORTBY函數(shù) 201
10.3.3  UNIQUE函數(shù) 201
10.3.4  RANDARRAY函數(shù) 202
10.3.5  SEQUENCE函數(shù) 202
10.3.6  FILTER函數(shù) 204
10.3.7  XLOOKUP函數(shù) 206
第11章  使用公式執(zhí)行常用數(shù)學運算 209
11.1  計算百分比 209
11.1.1  計算目標的百分比 209
11.1.2  計算百分比變化 210
11.1.3  計算帶負值的百分比變化 211
11.1.4  計算百分比分布 212
11.1.5  計算累積總計 212
11.1.6  使用百分比增加或減小值 213
11.1.7  處理除零錯誤 214
11.2  數(shù)字舍入 215
11.2.1  使用公式舍入數(shù)字 215
11.2.2  舍入到最接近的分 215
11.2.3  舍入到有效位 216
11.3  統(tǒng)計區(qū)域中的值 217
11.4  使用Excel的轉換函數(shù) 218
第12章  使用公式處理文本 219
12.1  使用文本 219
12.2  使用文本函數(shù) 220
12.2.1  連接文本字符串 220
12.2.2  將文本設為句子形式 221
12.2.3  刪除文本字符串中的空格 222
12.2.4  從文本字符串中提取部分字符串 223
12.2.5  在文本字符串中查找特定字符 223
12.2.6  找到字符的第二個實例 224
12.2.7  替換文本字符串 225
12.2.8  統(tǒng)計單元格中的特定字符 226
12.2.9  在公式中添加換行 226
12.2.10  清理文本字段中的奇怪字符 227
12.2.11  在數(shù)字中填充0 228
12.2.12  設置文本字符串中數(shù)字的格式 228
12.2.13  使用DOLLAR函數(shù) 229
第13章  使用公式處理日期和時間 231
13.1  Excel如何處理日期和時間 231
13.1.1  了解日期序號 231
13.1.2  輸入日期 232
13.1.3  了解時間序號 233
13.1.4  輸入時間 234
13.1.5  設置日期和時間格式 234
13.1.6  日期問題 235
13.2  使用Excel的日期和時間函數(shù) 236
13.2.1  獲取當前日期和時間 236
13.2.2  計算年齡 237
13.2.3  計算兩個日期之間的天數(shù) 238
13.2.4  計算兩個日期之間的工作日天數(shù) 238
13.2.5  排除假日，生成營業(yè)日列表 239
13.2.6  提取日期的一部分 240
13.2.7  計算兩個日期之間的年數(shù)和月份數(shù) 241
13.2.8  將日期轉換為儒略日期格式 242
13.2.9  計算一年已完成天數(shù)的百分比和剩余天數(shù)的百分比 243
13.2.10  返回給定月份的最后一個日期 243
13.2.11  計算日期的日歷季度 244
13.2.12  計算日期的財季 245
13.2.13  從日期返回財務月 246
13.2.14  計算一個月中第N個工作日的日期 246
13.2.15  計算每個月最后一個工作日的日期 247
13.2.16  提取時間的一部分 248
13.2.17  計算流逝的時間 248
13.2.18  舍入時間值 249
13.2.19  將用小數(shù)表達的小時、分鐘或秒鐘轉換為時間 249
13.2.20  向時間增加小時、分鐘或秒鐘 250
第14章  使用公式進行條件分析 251
14.1  了解條件分析 251
14.1.1  檢查是否滿足簡單條件 251
14.1.2  檢查多個條件 252
14.1.3  驗證條件數(shù)據(jù) 253
14.1.4  檢查是否同時滿足條件1和條件2 254
14.1.5  檢查是否滿足條件1或條件2 255
14.2  執(zhí)行條件計算 256
14.2.1  對滿足特定條件的所有值求和 256
14.2.2  對滿足兩個或更多個條件的所有值求和 258
14.2.3  對給定日期范圍內(nèi)的值求和 259
14.2.4  統(tǒng)計滿足特定條件的值 260
14.2.5  統(tǒng)計滿足兩個或更多個條件的值 261
14.2.6  獲取滿足特定條件的所有數(shù)字的平均值 262
14.2.7  獲取滿足兩個或更多個條件的所有數(shù)字的平均值 262
第15章  使用公式進行匹配和查找 265
15.1  查找公式簡介 265
15.2  使用Excel的查找函數(shù) 266
15.2.1  基于左側查找列精確查找值 266
15.2.2  基于任意列查找精確值 268
15.2.3  橫向查找值 269
15.2.4  隱藏查找函數(shù)返回的錯誤 269
15.2.5  在區(qū)間值列表中找到最接近匹配 270
15.2.6  從多個表格中查找值 272
15.2.7  基于雙向矩陣查找值 274
15.2.8  基于多個條件查找值 275
15.2.9  找出列中的最后一個值 276
第16章  在表格和條件格式中使用公式 279
16.1  突出顯示滿足特定條件的單元格 279
16.2  突出顯示列表1中存在但列表2中不存在的值 281
16.3  突出顯示既在列表1中存在又在列表2中存在的值 282
16.4  基于日期突出顯示 283
16.4.1  突出顯示兩個日期之間的日期 284
16.4.2  基于到期日突出顯示日期 285
第17章  避免工作表出錯 287
17.1  發(fā)現(xiàn)并更正公式錯誤 287
17.1.1  括號不匹配 288
17.1.2  單元格中顯示一組井號(#) 289
17.1.3  空白單元格不為空 289
17.1.4  多余的空格字符 289
17.1.5  返回錯誤的公式 290
17.1.6  運算符優(yōu)先級問題 293
17.1.7  未計算的公式 293
17.1.8  小數(shù)位精度的問題 293
17.1.9  “虛鏈接”錯誤 294
17.2  使用Excel中的審核工具 294
17.2.1  找出特殊類型的單元格 295
17.2.2  查看公式 295
17.2.3  追蹤單元格關系 295
17.2.4  追蹤錯誤值 296
17.2.5  修復循環(huán)引用錯誤 297
17.2.6  使用后臺錯誤檢查功能 297
17.2.7  使用公式求值 298
17.3  查找和替換 298
17.3.1  查找信息 299
17.3.2  替換信息 299
17.3.3  查找格式 300
17.3.4  工作表拼寫檢查 300
17.4  使用自動更正 301
第 III 部分  創(chuàng)建圖表和其他可視化
第18章  開始創(chuàng)建Excel圖表 305
18.1  圖表的概念 305
18.1.1  了解Excel的圖表處理方式 306
18.1.2  嵌入式圖表 307
18.1.3  圖表工作表 307
18.1.4  圖表的組成部分 308
18.1.5  圖表的限制 310
18.2  創(chuàng)建圖表的基本步驟 310
18.2.1  創(chuàng)建圖表 310
18.2.2  切換行和列的方向 311
18.2.3  更改圖表類型 311
18.2.4  應用圖表布局 312
18.2.5  應用圖表樣式 313
18.2.6  添加和刪除圖表元素 313
18.2.7  設置圖表元素的格式 313
18.3 修改和自定義圖表 314
18.3.1  移動圖表和調(diào)整圖表大小 315
18.3.2  將嵌入式圖表轉換為圖表工作表 315
18.3.3  復制圖表 315
18.3.4  刪除圖表 316
18.3.5  添加圖表元素 316
18.3.6  移動和刪除圖表元素 316
18.3.7  設置圖表元素的格式 316
18.3.8  復制圖表的格式 317
18.3.9  重命名圖表 317
18.3.10  打印圖表 318
18.4  了解圖表類型 318
18.4.1  選擇圖表類型 318
18.4.2  柱形圖 320
18.4.3  條形圖 321
18.4.4  折線圖 322
18.4.5  餅圖 323
18.4.6  XY(散點圖) 324
18.4.7  面積圖 325
18.4.8  雷達圖 325
18.4.9  曲面圖 326
18.4.10  氣泡圖 326
18.4.11  股價圖 327
18.5  Excel的較新圖表類型 327
18.5.1  直方圖 328
18.5.2  排列圖 328
18.5.3  瀑布圖 329
18.5.4  箱形圖 329
18.5.5  旭日圖 330
18.5.6  樹狀圖 330
18.5.7  漏斗圖 330
18.5.8  地圖 330
第19章  使用高級圖表技術 333
19.1  選擇圖表元素 333
19.1.1  使用鼠標進行選擇 334
19.1.2  使用鍵盤進行選擇 334
19.1.3  使用“圖表元素”控件進行選擇 335
19.2  用于修改圖表元素的用戶界面選項 335
19.2.1  使用“設置格式”任務窗格 335
19.2.2  使用圖表自定義按鈕 336
19.2.3  使用功能區(qū) 337
19.2.4  使用浮動工具欄 337
19.3  修改圖表區(qū) 337
19.4  修改繪圖區(qū) 338
19.5  處理圖表中的標題 339
19.6  處理圖例 339
19.7  處理網(wǎng)格線 341
19.8  修改坐標軸 341
19.8.1  更改數(shù)值坐標軸 341
19.8.2  更改類別坐標軸 345
19.9  處理數(shù)據(jù)系列 347
19.9.1  刪除或隱藏數(shù)據(jù)系列 347
19.9.2  為圖表添加新數(shù)據(jù)系列 347
19.9.3  更改數(shù)據(jù)系列所使用的數(shù)據(jù) 348
19.9.4  在圖表中顯示數(shù)據(jù)標簽 350
19.9.5  處理丟失的數(shù)據(jù) 351
19.9.6  添加誤差線 352
19.9.7  添加趨勢線 353
19.9.8  創(chuàng)建組合圖 353
19.9.9  顯示數(shù)據(jù)表 355
19.10  創(chuàng)建圖表模板 355
第20章  創(chuàng)建迷你圖 357
20.1  迷你圖類型 357
20.2  創(chuàng)建迷你圖 358
20.3  自定義迷你圖 360
20.3.1  調(diào)整迷你圖單元格的大小 360
20.3.2  處理隱藏或丟失的數(shù)據(jù) 360
20.3.3  更改迷你圖類型 361
20.3.4  更改迷你圖的顏色和線寬 361
20.3.5  突出顯示某些數(shù)據(jù)點 361
20.3.6  調(diào)整迷你圖坐標軸刻度 362
20.3.7  偽造參考線 363
20.4  指定日期坐標軸 364
20.5  自動更新迷你圖 365
20.6  顯示動態(tài)區(qū)域的迷你圖 365
第21章  使用自定義數(shù)字格式和形狀實現(xiàn)可視化 367
21.1  使用數(shù)字格式進行可視化 367
21.1.1  設置基本數(shù)字格式 367
21.1.2  創(chuàng)造性設置自定義數(shù)字格式 369
21.1.3  使用符號增強報表 374
21.2  將形狀和圖標用作視覺元素 377
21.2.1  插入形狀 377
21.2.2  插入SVG圖標圖形 378
21.2.3  插入3D模型 378
21.2.4  設置形狀和圖標的格式 379
21.2.5  使用形狀增強Excel報表 380
21.2.6  創(chuàng)建動態(tài)標簽 382
21.2.7  創(chuàng)建鏈接的圖片 382
21.3  使用SmartArt和藝術字 384
21.3.1  SmartArt基礎 384
21.3.2  藝術字基礎 385
21.4  使用其他圖形類型 385
21.4.1  圖形文件簡介 385
21.4.2  插入屏幕快照 386
21.4.3  顯示工作表背景圖像 386
21.5  使用公式編輯器 386
第 IV 部分  管理和分析數(shù)據(jù)
第22章  導入和清理數(shù)據(jù) 391
22.1  導入數(shù)據(jù) 391
22.1.1  從文件導入 391
22.1.2  對比導入與打開操作 393
22.1.3  導入文本文件 394
22.1.4  復制和粘貼數(shù)據(jù) 396
22.2  清理數(shù)據(jù) 396
22.2.1  刪除重復的行 396
22.2.2  識別重復的行 397
22.2.3  拆分文本 398
22.2.4  更改文本的大小寫 401
22.2.5  刪除多余空格 402
22.2.6  刪除奇怪字符 402
22.2.7  轉換值 403
22.2.8  對值進行分類 403
22.2.9  連接列 404
22.2.10  重新排列各列 405
22.2.11  隨機排列行 405
22.2.12  從URL中提取文件名 405
22.2.13  匹配列表中的文本 406
22.2.14  將縱向數(shù)據(jù)更改為橫向數(shù)據(jù) 407
22.2.15  填補已導入報表中的空白 408
22.2.16  拼寫檢查 409
22.2.17  替換或刪除單元格中的文本 410
22.2.18  將文本添加到單元格 410
22.2.19  解決結尾負號問題 411
22.2.20  數(shù)據(jù)清理檢查表 411
22.3  導出數(shù)據(jù) 411
22.3.1  導出到文本文件 411
22.3.2  導出到其他文件格式 412
第23章  使用數(shù)據(jù)驗證 413
23.1  數(shù)據(jù)驗證簡介 413
23.2  指定驗證條件 414
23.3  能夠應用的驗證條件類型 415
23.4  創(chuàng)建下拉列表 416
23.5  對數(shù)據(jù)驗證規(guī)則使用公式 417
23.6  了解單元格引用 417
23.7  數(shù)據(jù)驗證公式示例 418
23.7.1  只接受文本 419
23.7.2  接受比前一個單元格更大的值 419
23.7.3  只接受非重復的輸入項 419
23.7.4  接受以特定字符開頭的文本 419
23.7.5  只接受一周中的特定日期 420
23.7.6  只接受總和不超過特定值的數(shù)值 420
23.7.7  創(chuàng)建從屬列表 420
23.8  使用數(shù)據(jù)驗證但不限制輸入 421
23.8.1  顯示輸入信息 421
23.8.2  提供建議項 421
第24章  創(chuàng)建和使用工作表分級顯示 423
24.1  工作表分級顯示簡介 423
24.2  創(chuàng)建分級顯示 426
24.2.1  準備數(shù)據(jù) 426
24.2.2  自動創(chuàng)建分級顯示 427
24.2.3  手工創(chuàng)建分級顯示 427
24.3  使用分級顯示 429
24.3.1  顯示級別 429
24.3.2  向分級顯示中添加數(shù)據(jù) 429
24.3.3  刪除分級顯示 429
24.3.4  調(diào)整分級顯示符號 429
24.3.5  隱藏分級顯示符號 430
第25章  鏈接和合并計算工作表 431
25.1  鏈接工作簿 431
25.2  創(chuàng)建外部引用公式 432
25.2.1  了解鏈接公式的語法 432
25.2.2  通過指向創(chuàng)建鏈接公式 433
25.2.3  粘貼鏈接 433
25.3  使用外部引用公式 433
25.3.1  創(chuàng)建指向未保存的工作簿的鏈接 433
25.3.2  打開一個包含外部引用公式的工作簿 434
25.3.3  更改啟動提示 435
25.3.4  更新鏈接 435
25.3.5  更改鏈接源 435
25.3.6  斷開鏈接 436
25.4  避免外部引用公式中的潛在問題 436
25.4.1  重命名或移動源工作簿 436
25.4.2  使用“另存為”命令 436
25.4.3  修改源工作簿 437
25.4.4  使用中間鏈接 437
25.5  合并計算工作表 438
25.5.1  通過公式合并計算工作表 439
25.5.2  使用“選擇性粘貼”功能合并計算工作表 439
25.5.3  使用“合并計算”對話框來合并計算工作表 440
25.5.4  工作簿合并計算示例 441
25.5.5  刷新合并計算 443
25.5.6  有關合并計算的更多信息 443
第26章  數(shù)據(jù)透視表簡介 445
26.1  數(shù)據(jù)透視表簡介 445
26.1.1  數(shù)據(jù)透視表示例 445
26.1.2  適用于數(shù)據(jù)透視表的數(shù)據(jù) 447
26.2  自動創(chuàng)建數(shù)據(jù)透視表 450
26.3  手動創(chuàng)建數(shù)據(jù)透視表 451
26.3.1  指定數(shù)據(jù) 451
26.3.2  指定數(shù)據(jù)透視表的放置位置 451
26.3.3  指定數(shù)據(jù)透視表布局 452
26.3.4  設置數(shù)據(jù)透視表的格式 454
26.3.5  修改數(shù)據(jù)透視表 455
26.4  更多數(shù)據(jù)透視表示例 457
26.4.1  每個分行每天新增的存款總額是多少 457
26.4.2  一周中哪一天的存款金額最多 458
26.4.3  每個分行的每種賬戶類型的開戶數(shù)是多少 458
26.4.4  開戶時存取的金額是多少 458
26.4.5  柜員最常開的賬戶類型是什么 459
26.4.6  哪個分行的柜員為新客戶開的支票賬戶最多 460
26.5  學習更多知識 460
第27章  使用數(shù)據(jù)透視表分析數(shù)據(jù) 461
27.1  使用非數(shù)值數(shù)據(jù) 461
27.2  對數(shù)據(jù)透視表中的項進行分組 463
27.2.1  手動分組示例 463
27.2.2  自動分組示例 464
27.3  使用數(shù)據(jù)透視表創(chuàng)建頻率分布 467
27.4  創(chuàng)建計算字段或計算項 469
27.4.1  創(chuàng)建計算字段 470
27.4.2  插入計算項 471
27.5  使用切片器篩選數(shù)據(jù)透視表 474
27.6  用日程表篩選數(shù)據(jù)透視表 475
27.7  引用數(shù)據(jù)透視表中的單元格 476
27.8  創(chuàng)建數(shù)據(jù)透視圖 477
27.8.1  數(shù)據(jù)透視圖示例 478
27.8.2  關于數(shù)據(jù)透視圖的更多介紹 480
27.9  使用數(shù)據(jù)模型 481
第28章  執(zhí)行電子表格模擬分析 487
28.1  模擬分析示例 487
28.2  模擬分析的類型 488
28.2.1  執(zhí)行手動模擬分析 489
28.2.2  創(chuàng)建模擬運算表 489
28.2.3  使用方案管理器 494
28.3 借助人工智能分析數(shù)據(jù) 499
28.3.1  使用Excel的建議 499
28.3.2  查詢分析的數(shù)據(jù) 500
第29章  使用單變量求解和規(guī)劃求解來分析數(shù)據(jù) 503
29.1  反向執(zhí)行模擬分析 503
29.2  單一單元格單變量求解 504
29.2.1  單變量求解示例 504
29.2.2  有關單變量求解的更多信息 505
29.3  規(guī)劃求解簡介 506
29.3.1  適合通過規(guī)劃求解來解決的問題 506
29.3.2  一個簡單的規(guī)劃求解示例 507
29.3.3  探索規(guī)劃求解的選項 510
29.4  規(guī)劃求解示例 511
29.4.1  求解聯(lián)立線性方程 511
29.4.2  最小化運輸成本 512
29.4.3  分配資源 514
29.4.4  優(yōu)化投資組合 515
第30章  使用分析工具庫分析數(shù)據(jù) 519
30.1  分析工具庫概述 519
30.2  安裝分析工具庫加載項 520
30.3  使用分析工具 520
30.4  分析工具庫工具簡介 521
30.4.1  方差分析工具 521
30.4.2  相關系數(shù)工具 521
30.4.3  協(xié)方差工具 522
30.4.4  描述統(tǒng)計工具 522
30.4.5  指數(shù)平滑工具 523
30.4.6  F-檢驗(雙樣本方差檢驗)工具 523
30.4.7  傅里葉分析工具 523
30.4.8  直方圖工具 523
30.4.9  移動平均工具 524
30.4.10  隨機數(shù)發(fā)生器工具 525
30.4.11  排位與百分比排位工具 526
30.4.12  回歸工具 526
30.4.13  抽樣工具 527
30.4.14  t-檢驗工具 527
30.4.15  z-檢驗工具(雙樣本平均差檢驗) 527
第31章  保護工作成果 529
31.1  保護類型 529
31.2  保護工作表 529
31.2.1  取消鎖定單元格 530
31.2.2  工作表保護選項 531
31.2.3  分配用戶權限 532
31.3  保護工作簿 532
31.3.1  需要密碼才能打開工作簿 533
31.3.2  保護工作簿的結構 533
31.4  VBA工程保護 534
31.5  相關主題 535
31.5.1  將工作表保存為PDF文件 535
31.5.2  將工作簿標記為最終版本 535
31.5.3  檢查工作簿 536
31.5.4  使用數(shù)字簽名 536
第 V 部分
了解Power Pivot和Power Query
第32章  Power Pivot簡介 541
32.1  了解Power Pivot的內(nèi)部數(shù)據(jù)模型 541
32.1.1  Power Pivot功能區(qū) 541
32.1.2  將Excel表格鏈接到Power Pivot 542
32.2  從其他數(shù)據(jù)源載入數(shù)據(jù) 548
32.2.1  從關系數(shù)據(jù)庫載入數(shù)據(jù) 548
32.2.2  從平面文件載入數(shù)據(jù) 550
32.2.3  刷新和管理外部數(shù)據(jù)連接 554
第33章  直接操作內(nèi)部數(shù)據(jù)模型 557
33.1  直接為內(nèi)部數(shù)據(jù)模型提供數(shù)據(jù) 557
33.2  管理內(nèi)部數(shù)據(jù)模型中的關系 561
33.3  管理查詢和連接 562
第34章  向Power Pivot添加公式 563
34.1  使用計算列增強Power Pivot數(shù)據(jù) 563
34.1.1  創(chuàng)建你的第一個計算列 563
34.1.2  設置計算列的格式 565
34.1.3  在其他計算中引用計算列 566
34.1.4  向最終用戶隱藏計算列 566
34.2  使用DAX創(chuàng)建計算列 567
34.2.1  找出可安全用于計算列的DAX函數(shù) 567
34.2.2  創(chuàng)建DAX驅動的計算列 568
34.2.3  引用其他表的字段 570
34.2.4  嵌套函數(shù) 572
34.3  了解計算度量值 572
34.4  使用Cube函數(shù)來解放數(shù)據(jù) 574
第35章  Power Query簡介 577
35.1  Power Query的基礎知識 577
35.1.1  了解查詢步驟 582
35.1.2  查看高級查詢編輯器 583
35.1.3  刷新Power Query數(shù)據(jù) 583
35.1.4  管理現(xiàn)有查詢 584
35.1.5  了解列級操作 585
35.1.6  了解表級操作 586
35.2  從外部源獲取數(shù)據(jù) 587
35.2.1  從文件導入數(shù)據(jù) 588
35.2.2  從數(shù)據(jù)庫系統(tǒng)導入數(shù)據(jù) 590
35.3  從其他數(shù)據(jù)系統(tǒng)獲取數(shù)據(jù) 591
35.4  管理數(shù)據(jù)源設置 591
35.5 使用Power Query進行數(shù)據(jù)分析 593
35.5.1 數(shù)據(jù)分析選項 593
35.5.2 數(shù)據(jù)分析的快速操作 594
第36章  使用Power Query轉換數(shù)據(jù) 595
36.1  執(zhí)行常見的轉換任務 595
36.1.1  刪除重復記錄 595
36.1.2  填充空字段 597
36.1.3  填充空字符串 598
36.1.4  連接列 598
36.1.5  改變大小寫 599
36.1.6  查找和替換文本 600
36.1.7  修整和清除文本 600
36.1.8  提取左側、右側和中間的值 602
36.1.9  提取首字符和結尾字符 603
36.1.10  提取中間字符 603
36.1.11  使用字符標記拆分列 604
36.1.12  逆透視列 606
36.1.13  逆透視其他列 606
36.1.14  透視列 607
36.2  創(chuàng)建自定義列 608
36.2.1  使用自定義列進行連接 609
36.2.2  了解數(shù)據(jù)類型轉換 610
36.2.3  使用函數(shù)擴展自定義列的功能 611
36.2.4  向自定義列添加條件邏輯 612
36.3  分組和聚合數(shù)據(jù) 613
36.4  使用自定義數(shù)據(jù)類型 615
第37章  使查詢協(xié)同工作 619
37.1  重用查詢步驟 619
37.2  了解追加功能 622
37.2.1  創(chuàng)建必要的基礎查詢 623
37.2.2  追加數(shù)據(jù) 623
37.3  了解合并功能 625
37.3.1  了解Power Query連接 625
37.3.2  合并查詢 626
37.3.3  了解模糊匹配 628
第38章  提高Power Query的生產(chǎn)率 631
38.1  關于如何提高Power Query生產(chǎn)率的一些提示 631
38.1.1  快速獲取關于查詢的信息 631
38.1.2  將查詢組織成組 631
38.1.3  更快速地選擇查詢中的列 632
38.1.4  重命名查詢步驟 632
38.1.5  快速創(chuàng)建引用表 632
38.1.6  復制查詢來節(jié)省時間 633
38.1.7  查看查詢依賴項 633
38.1.8  設置默認加載行為 633
38.1.9  防止數(shù)據(jù)類型自動更改 633
38.2  避免Power Query的性能問題 635
38.2.1  使用視圖而不是表 635
38.2.2  讓后臺數(shù)據(jù)庫服務器完成一些計算 635
38.2.3  升級到64位Excel 635
38.2.4  通過禁用隱私設置來提高性能 636
38.2.5  禁用關系檢測 636
第 VI 部分
Excel自動化
第39章  VBA簡介 641
39.1  VBA宏簡介 641
39.2  顯示“開發(fā)工具”選項卡 642
39.3  宏安全性簡介 642
39.4  保存含有宏的工作簿 643
39.5  兩種類型的VBA宏 644
39.5.1  VBA子過程 644
39.5.2  VBA函數(shù) 645
39.6  創(chuàng)建VBA宏 646
39.6.1  錄制VBA宏 646
39.6.2  關于錄制VBA宏的更多信息 652
39.6.3  編寫VBA代碼 654
39.7  學習更多知識 662
第40章  創(chuàng)建自定義工作表函數(shù) 663
40.1  VBA函數(shù)概述 663
40.2  查看一個簡單示例 664
40.2.1  創(chuàng)建自定義函數(shù) 664
40.2.2  在工作表中使用函數(shù) 664
40.2.3  分析自定義函數(shù) 665
40.3  函數(shù)過程簡介 666
40.4  執(zhí)行函數(shù)過程 667
40.4.1  從過程中調(diào)用自定義函數(shù) 667
40.4.2  在工作表公式中使用自定義函數(shù) 667
40.5  使用函數(shù)過程參數(shù) 668
40.5.1  創(chuàng)建無參數(shù)的函數(shù) 668
40.5.2  創(chuàng)建有一個參數(shù)的函數(shù) 668
40.5.3  創(chuàng)建另一個有一個參數(shù)的函數(shù) 669
40.5.4  創(chuàng)建有兩個參數(shù)的函數(shù) 670
40.5.5  創(chuàng)建有一個區(qū)域參數(shù)的函數(shù) 670
40.5.6  創(chuàng)建一個簡單但有用的函數(shù) 671
40.6  調(diào)試自定義函數(shù) 671
40.7  插入自定義函數(shù) 672
40.8  學習更多知識 673
第41章  創(chuàng)建用戶窗體 675
41.1  理解為什么要創(chuàng)建用戶窗體 675
41.2  用戶窗體的替代方法 676
41.2.1  使用 InputBox函數(shù) 676
41.2.2  使用MsgBox函數(shù) 677
41.3  創(chuàng)建用戶窗體：概述 679
41.3.1  使用用戶窗體 679
41.3.2  添加控件 680
41.3.3  更改控件屬性 680
41.3.4  處理事件 681
41.3.5  顯示用戶窗體 682
41.4  查看用戶窗體示例 682
41.4.1  創(chuàng)建用戶窗體 682
41.4.2  測試用戶窗體 683
41.4.3  創(chuàng)建事件處理程序過程 684
41.5  查看另一個用戶窗體示例 684
41.5.1  創(chuàng)建用戶窗體 685
41.5.2  創(chuàng)建事件處理程序過程 686
41.5.3  顯示用戶窗體 687
41.5.4  測試用戶窗體 688
41.5.5  從工作表按鈕執(zhí)行宏 688
41.5.6  從快速訪問工具欄中執(zhí)行宏 689
41.6  增強用戶窗體 689
41.6.1  添加熱鍵 689
41.6.2  控制Tab鍵順序 690
41.7  學習更多知識 690
第42章  在工作表中使用用戶窗體控件 691
42.1  理解為什么要在工作表中使用控件 691
42.2  使用控件 693
42.2.1  添加控件 693
42.2.2  了解設計模式 693
42.2.3  調(diào)整屬性 694
42.2.4  使用通用屬性 695
42.2.5  將控件鏈接到單元格 695
42.2.6  為控件創(chuàng)建宏 695
42.3  查看可用的ActiveX控件 697
42.3.1  復選框控件 697
42.3.2  組合框控件 697
42.3.3  命令按鈕控件 698
42.3.4  圖像控件 698
42.3.5  標簽控件 698
42.3.6  列表框控件 698
42.3.7  選項按鈕控件 699
42.3.8  滾動條控件 699
42.3.9  數(shù)值調(diào)節(jié)鈕控件 700
42.3.10  文本框控件 700
42.3.11  切換按鈕控件 701
第43章  使用Excel事件 703
43.1  了解事件 703
43.2  輸入事件處理程序的VBA代碼 704
43.3  使用工作簿級別的事件 705
43.3.1  使用Open事件 706
43.3.2  使用SheetActivate事件 706
43.3.3  使用NewSheet事件 707
43.3.4  使用BeforeSave事件 707
43.3.5  使用BeforeClose事件 707
43.4  使用工作表事件 708
43.4.1  使用Change事件 708
43.4.2  監(jiān)控特定區(qū)域中的更改 709
43.4.3  使用SelectionChange事件 710
43.4.4  使用BeforeRightClick事件 711
43.5  使用特殊應用程序事件 711
43.5.1  使用OnTime事件 711
43.5.2  使用OnKey事件 712
第44章  一些VBA示例 715
44.1  使用區(qū)域 715
44.1.1  復制區(qū)域 716
44.1.2  復制大小可變的區(qū)域 716
44.1.3  選擇從活動單元格到行或列結尾的內(nèi)容 717
44.1.4  選擇一行或一列 718
44.1.5  移動區(qū)域 718
44.1.6  高效地遍歷區(qū)域 718
44.1.7  提示輸入單元格值 719
44.1.8  確定選中內(nèi)容的類型 720
44.1.9  識別多重選定區(qū)域 721
44.1.10  對選中的單元格進行計數(shù) 721
44.2  使用工作簿 722
44.2.1  保存所有工作簿 722
44.2.2  保存并關閉所有工作簿 722
44.3  使用圖表 723
44.3.1  修改圖表類型 723
44.3.2  修改圖表屬性 723
44.3.3  應用圖表格式 724
44.4  VBA加速技巧 724
44.4.1  關閉屏幕更新 724
44.4.2  禁止警告消息 724
44.4.3  簡化對象引用 725
44.4.4  聲明變量類型 725
第45章  創(chuàng)建自定義Excel加載項 727
45.1  加載項的概念 727
45.2  使用加載項 728
45.3  為什么要創(chuàng)建加載項 729
45.4  創(chuàng)建加載項 729
45.5  加載項示例 730
45.5.1  了解Module1 731
45.5.2  關于用戶窗體 731
45.5.3  測試工作簿 732
45.5.4  添加描述性信息 732
45.5.5  為加載宏創(chuàng)建用戶界面 732
45.5.6  保護工程 732
45.5.7  創(chuàng)建加載項 733
45.5.8  安裝加載項 733