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

目    錄

 

 

第 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  使用功能區 8

1.4.1  功能區選項卡 8

1.4.2  上下文選項卡 9

1.4.3  功能區中的命令類型 10

1.4.4  用鍵盤訪問功能區 11

1.5  使用快捷菜單 13

1.6  自定義快速訪問工具欄 13

1.7  使用對話框 15

1.7.1  導航對話框 15

1.7.2  使用選項卡式對話框 16

1.8  使用任務窗格 16

1.9  創建第一個Excel工作簿 17

1.9.1  開始創建工作表 17

1.9.2  填充月份名稱 18

1.9.3  輸入銷售數據 18

1.9.4  設置數字的格式 19

1.9.5  讓工作表看上去更有吸引力 19

1.9.6  對值求和 20

1.9.7  創建圖表 20

1.9.8  打印工作表 21

1.9.9  保存工作簿 21

第2章  輸入和編輯工作表數據 23

2.1  瞭解數據類型 23

2.1.1  數值 23

2.1.2  文本輸入 24

2.1.3  公式 24

2.1.4  錯誤值 25

2.2  在工作表中輸入文本和值 25

2.2.1  輸入數值 25

2.2.2  輸入文本 25

2.2.3  使用輸入模式 26

2.3  在工作表中輸入日期和時間 26

2.3.1  輸入日期值 27

2.3.2  輸入時間值 27

2.4  修改單元格內容 27

2.4.1  刪除單元格內容 28

2.4.2  替換單元格內容 28

2.4.3  編輯單元格內容 28

2.4.4  學習一些實用的數據輸入方法 29

2.5  應用數字格式 35

2.5.1  使用自動數字格式 35

2.5.2  通過功能區設置數字格式 36

2.5.3  使用快捷鍵設置數字格式 36

2.5.4  使用“設置單元格格式”對話框設置數字格式 37

2.5.5  添加自定義數字格式 39

2.6  在平板電腦上使用Excel 39

2.6.1  探索Excel的平板電腦界面 39

2.6.2  在平板電腦上輸入公式 40

2.6.3  “繪圖”功能區簡介 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  使用監視窗口監視單元格 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區域和表格 59

4.1  單元格和區域簡介 59

4.1.1  選擇區域 60

4.1.2  選擇完整的行和列 60

4.1.3  選擇非連續的區域 61

4.1.4  選擇多工作表區域 61

4.1.5  選擇特殊類型的單元格 63

4.1.6  通過搜索選擇單元格 65

4.2  復制或移動區域 67

4.2.1  使用功能區中的命令進行復制 67

4.2.2  使用快捷菜單命令進行復制 68

4.2.3  使用快捷鍵進行復制 69

4.2.4  使用拖放方法進行復制或移動 70

4.2.5  復制到相鄰的單元格 70

4.2.6  向其他工作表復制區域 71

4.2.7  使用 Office剪貼板進行粘貼 71

4.2.8  使用特殊方法進行粘貼 72

4.2.9  使用“選擇性粘貼”對話框 74

4.3  對區域使用名稱 75

4.3.1  在工作簿中創建區域名稱 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  創建表格 86

4.6.3  向表格添加數據 87

4.6.4  排序和篩選表格數據 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  創建基於公式的規則 109

5.3.4  條件格式公式示例 110

5.3.5  使用條件格式 113

5.4  使用命名樣式方便地設置格式 114

5.4.1  應用樣式 114

5.4.2  修改現有樣式 115

5.4.3  創建新樣式 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  創建新工作簿 121

6.2  打開現有工作簿 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  調整常用頁面設置 141

7.3.1  選擇打印機 142

7.3.2  指定要打印的內容 142

7.3.3  更改頁面方向 143

7.3.4  指定紙張大小 143

7.3.5  打印多份報表 143

7.3.6  調整頁邊距 144

7.3.7  瞭解分頁符 145

7.3.8  打印行和列標題 145

7.3.9  對打印輸出進行縮放設置 146

7.3.10  打印單元格網格線 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  為工作表創建自定義視圖 152

7.5.5  創建PDF文件 153

第8章  自定義Excel用戶界面 155

8.1  自定義快速訪問工具欄 155

8.1.1  快速訪問工具欄簡介 155

8.1.2  向快速訪問工具欄添加新命令 156

8.1.3  其他快速訪問工具欄操作 158

8.2  自定義功能區 159

8.2.1  自定義功能區的目的 159

8.2.2  可以自定義的項 159

8.2.3  如何自定義功能區 160

8.2.4  重置功能區 161

 

第 II 部分  使用公式和函數

第9章  公式和函數簡介 165

9.1  瞭解公式的基礎知識 165

9.1.1  在公式中使用運算符 166

9.1.2  瞭解公式中的運算符優先級 167

9.1.3  在公式中使用函數 169

9.2  在工作表中輸入公式 171

9.2.1  通過指向輸入公式 172

9.2.2  將區域名稱粘貼到公式中 173

9.2.3  向公式中插入函數 173

9.2.4  函數輸入提示 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函數 180

9.5.2  公式變量的應用 180

9.6  在表格中使用公式 181

9.6.1  匯總表格中的數據 181

9.6.2  在表格中使用公式 183

9.6.3  引用表格中的數據 184

9.7  更正常見的公式錯誤 184

9.7.1  處理循環引用 185

9.7.2  指定在什麽時候計算公式 186

9.8  使用高級命名方法 187

9.8.1  為常量使用名稱 187

9.8.2  為公式使用名稱 188

9.8.3  使用區域交集 188

9.8.4  對現有引用應用名稱 189

9.9  使用公式 190

9.9.1  不使用硬編碼數值 190

9.9.2  將編輯欄用作計算器 190

9.9.3  精確復制公式 190

9.9.4  將公式轉換為數值 191

第10章  理解和使用數組公式 193

10.1  理解遺留數組公式 194

10.1.1  遺留數組公式的示例 194

10.1.2  編輯遺留的數組公式 194

10.2  動態數組簡介 195

10.2.1  理解溢出區域 197

10.2.2  引用溢出區域 198

10.3  探索動態數組函數 199

10.3.1  SORT函數 200

10.3.2  SORTBY函數 201

10.3.3  UNIQUE函數 201

10.3.4  RANDARRAY函數 202

10.3.5  SEQUENCE函數 202

10.3.6  FILTER函數 204

10.3.7  XLOOKUP函數 206

第11章  使用公式執行常用數學運算 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  數字舍入 215

11.2.1  使用公式舍入數字 215

11.2.2  舍入到最接近的分 215

11.2.3  舍入到有效位 216

11.3  統計區域中的值 217

11.4  使用Excel的轉換函數 218

第12章  使用公式處理文本 219

12.1  使用文本 219

12.2  使用文本函數 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  統計單元格中的特定字符 226

12.2.9  在公式中添加換行 226

12.2.10  清理文本字段中的奇怪字符 227

12.2.11  在數字中填充0 228

12.2.12  設置文本字符串中數字的格式 228

12.2.13  使用DOLLAR函數 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的日期和時間函數 236

13.2.1  獲取當前日期和時間 236

13.2.2  計算年齡 237

13.2.3  計算兩個日期之間的天數 238

13.2.4  計算兩個日期之間的工作日天數 238

13.2.5  排除假日，生成營業日列表 239

13.2.6  提取日期的一部分 240

13.2.7  計算兩個日期之間的年數和月份數 241

13.2.8  將日期轉換為儒略日期格式 242

13.2.9  計算一年已完成天數的百分比和剩餘天數的百分比 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  將用小數表達的小時、分鐘或秒鐘轉換為時間 249

13.2.20  向時間增加小時、分鐘或秒鐘 250

第14章  使用公式進行條件分析 251

14.1  瞭解條件分析 251

14.1.1  檢查是否滿足簡單條件 251

14.1.2  檢查多個條件 252

14.1.3  驗證條件數據 253

14.1.4  檢查是否同時滿足條件1和條件2 254

14.1.5  檢查是否滿足條件1或條件2 255

14.2  執行條件計算 256

14.2.1  對滿足特定條件的所有值求和 256

14.2.2  對滿足兩個或更多個條件的所有值求和 258

14.2.3  對給定日期範圍內的值求和 259

14.2.4  統計滿足特定條件的值 260

14.2.5  統計滿足兩個或更多個條件的值 261

14.2.6  獲取滿足特定條件的所有數字的平均值 262

14.2.7  獲取滿足兩個或更多個條件的所有數字的平均值 262

第15章  使用公式進行匹配和查找 265

15.1  查找公式簡介 265

15.2  使用Excel的查找函數 266

15.2.1  基於左側查找列精確查找值 266

15.2.2  基於任意列查找精確值 268

15.2.3  橫向查找值 269

15.2.4  隱藏查找函數返回的錯誤 269

15.2.5  在區間值列表中找到最接近匹配 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  發現並更正公式錯誤 287

17.1.1  括號不匹配 288

17.1.2  單元格中顯示一組井號(#) 289

17.1.3  空白單元格不為空 289

17.1.4  多餘的空格字符 289

17.1.5  返回錯誤的公式 290

17.1.6  運算符優先級問題 293

17.1.7  未計算的公式 293

17.1.8  小數位精度的問題 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  修復循環引用錯誤 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 部分  創建圖表和其他可視化

第18章  開始創建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  創建圖表的基本步驟 310

18.2.1  創建圖表 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  移動圖表和調整圖表大小 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  使用功能區 337

19.2.4  使用浮動工具欄 337

19.3  修改圖表區 337

19.4  修改繪圖區 338

19.5  處理圖表中的標題 339

19.6  處理圖例 339

19.7  處理網格線 341

19.8  修改坐標軸 341

19.8.1  更改數值坐標軸 341

19.8.2  更改類別坐標軸 345

19.9  處理數據系列 347

19.9.1  刪除或隱藏數據系列 347

19.9.2  為圖表添加新數據系列 347

19.9.3  更改數據系列所使用的數據 348

19.9.4  在圖表中顯示數據標簽 350

19.9.5  處理丟失的數據 351

19.9.6  添加誤差線 352

19.9.7  添加趨勢線 353

19.9.8  創建組合圖 353

19.9.9  顯示數據表 355

19.10  創建圖表模板 355

第20章  創建迷你圖 357

20.1  迷你圖類型 357

20.2  創建迷你圖 358

20.3  自定義迷你圖 360

20.3.1  調整迷你圖單元格的大小 360

20.3.2  處理隱藏或丟失的數據 360

20.3.3  更改迷你圖類型 361

20.3.4  更改迷你圖的顏色和線寬 361

20.3.5  突出顯示某些數據點 361

20.3.6  調整迷你圖坐標軸刻度 362

20.3.7  偽造參考線 363

20.4  指定日期坐標軸 364

20.5  自動更新迷你圖 365

20.6  顯示動態區域的迷你圖 365

第21章  使用自定義數字格式和形狀實現可視化 367

21.1  使用數字格式進行可視化 367

21.1.1  設置基本數字格式 367

21.1.2  創造性設置自定義數字格式 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  創建動態標簽 382

21.2.7  創建鏈接的圖片 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 部分  管理和分析數據

第22章  導入和清理數據 391

22.1  導入數據 391

22.1.1  從文件導入 391

22.1.2  對比導入與打開操作 393

22.1.3  導入文本文件 394

22.1.4  復制和粘貼數據 396

22.2  清理數據 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  將縱向數據更改為橫向數據 407

22.2.15  填補已導入報表中的空白 408

22.2.16  拼寫檢查 409

22.2.17  替換或刪除單元格中的文本 410

22.2.18  將文本添加到單元格 410

22.2.19  解決結尾負號問題 411

22.2.20  數據清理檢查表 411

22.3  導出數據 411

22.3.1  導出到文本文件 411

22.3.2  導出到其他文件格式 412

第23章  使用數據驗證 413

23.1  數據驗證簡介 413

23.2  指定驗證條件 414

23.3  能夠應用的驗證條件類型 415

23.4  創建下拉列表 416

23.5  對數據驗證規則使用公式 417

23.6  瞭解單元格引用 417

23.7  數據驗證公式示例 418

23.7.1  只接受文本 419

23.7.2  接受比前一個單元格更大的值 419

23.7.3  只接受非重復的輸入項 419

23.7.4  接受以特定字符開頭的文本 419

23.7.5  只接受一周中的特定日期 420

23.7.6  只接受總和不超過特定值的數值 420

23.7.7  創建從屬列表 420

23.8  使用數據驗證但不限制輸入 421

23.8.1  顯示輸入信息 421

23.8.2  提供建議項 421

第24章  創建和使用工作表分級顯示 423

24.1  工作表分級顯示簡介 423

24.2  創建分級顯示 426

24.2.1  準備數據 426

24.2.2  自動創建分級顯示 427

24.2.3  手工創建分級顯示 427

24.3  使用分級顯示 429

24.3.1  顯示級別 429

24.3.2  向分級顯示中添加數據 429

24.3.3  刪除分級顯示 429

24.3.4  調整分級顯示符號 429

24.3.5  隱藏分級顯示符號 430

第25章  鏈接和合並計算工作表 431

25.1  鏈接工作簿 431

25.2  創建外部引用公式 432

25.2.1  瞭解鏈接公式的語法 432

25.2.2  通過指向創建鏈接公式 433

25.2.3  粘貼鏈接 433

25.3  使用外部引用公式 433

25.3.1  創建指向未保存的工作簿的鏈接 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章  數據透視表簡介 445

26.1  數據透視表簡介 445

26.1.1  數據透視表示例 445

26.1.2  適用於數據透視表的數據 447

26.2  自動創建數據透視表 450

26.3  手動創建數據透視表 451

26.3.1  指定數據 451

26.3.2  指定數據透視表的放置位置 451

26.3.3  指定數據透視表佈局 452

26.3.4  設置數據透視表的格式 454

26.3.5  修改數據透視表 455

26.4  更多數據透視表示例 457

26.4.1  每個分行每天新增的存款總額是多少 457

26.4.2  一周中哪一天的存款金額最多 458

26.4.3  每個分行的每種賬戶類型的開戶數是多少 458

26.4.4  開戶時存取的金額是多少 458

26.4.5  櫃員最常開的賬戶類型是什麽 459

26.4.6  哪個分行的櫃員為新客戶開的支票賬戶最多 460

26.5  學習更多知識 460

第27章  使用數據透視表分析數據 461

27.1  使用非數值數據 461

27.2  對數據透視表中的項進行分組 463

27.2.1  手動分組示例 463

27.2.2  自動分組示例 464

27.3  使用數據透視表創建頻率分佈 467

27.4  創建計算字段或計算項 469

27.4.1  創建計算字段 470

27.4.2  插入計算項 471

27.5  使用切片器篩選數據透視表 474

27.6  用日程表篩選數據透視表 475

27.7  引用數據透視表中的單元格 476

27.8  創建數據透視圖 477

27.8.1  數據透視圖示例 478

27.8.2  關於數據透視圖的更多介紹 480

27.9  使用數據模型 481

第28章  執行電子表格模擬分析 487

28.1  模擬分析示例 487

28.2  模擬分析的類型 488

28.2.1  執行手動模擬分析 489

28.2.2  創建模擬運算表 489

28.2.3  使用方案管理器 494

28.3 借助人工智能分析數據 499

28.3.1  使用Excel的建議 499

28.3.2  查詢分析的數據 500

第29章  使用單變量求解和規劃求解來分析數據 503

29.1  反向執行模擬分析 503

29.2  單一單元格單變量求解 504

29.2.1  單變量求解示例 504

29.2.2  有關單變量求解的更多信息 505

29.3  規劃求解簡介 506

29.3.1  適合通過規劃求解來解決的問題 506

29.3.2  一個簡單的規劃求解示例 507

29.3.3  探索規劃求解的選項 510

29.4  規劃求解示例 511

29.4.1  求解聯立線性方程 511

29.4.2  最小化運輸成本 512

29.4.3  分配資源 514

29.4.4  優化投資組合 515

第30章  使用分析工具庫分析數據 519

30.1  分析工具庫概述 519

30.2  安裝分析工具庫加載項 520

30.3  使用分析工具 520

30.4  分析工具庫工具簡介 521

30.4.1  方差分析工具 521

30.4.2  相關系數工具 521

30.4.3  協方差工具 522

30.4.4  描述統計工具 522

30.4.5  指數平滑工具 523

30.4.6  F-檢驗(雙樣本方差檢驗)工具 523

30.4.7  傅里葉分析工具 523

30.4.8  直方圖工具 523

30.4.9  移動平均工具 524

30.4.10  隨機數發生器工具 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  使用數字簽名 536

第 V 部分

瞭解Power Pivot和Power Query

第32章  Power Pivot簡介 541

32.1  瞭解Power Pivot的內部數據模型 541

32.1.1  Power Pivot功能區 541

32.1.2  將Excel表格鏈接到Power Pivot 542

32.2  從其他數據源載入數據 548

32.2.1  從關系數據庫載入數據 548

32.2.2  從平面文件載入數據 550

32.2.3  刷新和管理外部數據連接 554

第33章  直接操作內部數據模型 557

33.1  直接為內部數據模型提供數據 557

33.2  管理內部數據模型中的關系 561

33.3  管理查詢和連接 562

第34章  向Power Pivot添加公式 563

34.1  使用計算列增強Power Pivot數據 563

34.1.1  創建你的第一個計算列 563

34.1.2  設置計算列的格式 565

34.1.3  在其他計算中引用計算列 566

34.1.4  向最終用戶隱藏計算列 566

34.2  使用DAX創建計算列 567

34.2.1  找出可安全用於計算列的DAX函數 567

34.2.2  創建DAX驅動的計算列 568

34.2.3  引用其他表的字段 570

34.2.4  嵌套函數 572

34.3  瞭解計算度量值 572

34.4  使用Cube函數來解放數據 574

第35章  Power Query簡介 577

35.1  Power Query的基礎知識 577

35.1.1  瞭解查詢步驟 582

35.1.2  查看高級查詢編輯器 583

35.1.3  刷新Power Query數據 583

35.1.4  管理現有查詢 584

35.1.5  瞭解列級操作 585

35.1.6  瞭解表級操作 586

35.2  從外部源獲取數據 587

35.2.1  從文件導入數據 588

35.2.2  從數據庫系統導入數據 590

35.3  從其他數據系統獲取數據 591

35.4  管理數據源設置 591

35.5 使用Power Query進行數據分析 593

35.5.1 數據分析選項 593

35.5.2 數據分析的快速操作 594

第36章  使用Power Query轉換數據 595

36.1  執行常見的轉換任務 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  創建自定義列 608

36.2.1  使用自定義列進行連接 609

36.2.2  瞭解數據類型轉換 610

36.2.3  使用函數擴展自定義列的功能 611

36.2.4  向自定義列添加條件邏輯 612

36.3  分組和聚合數據 613

36.4  使用自定義數據類型 615

第37章  使查詢協同工作 619

37.1  重用查詢步驟 619

37.2  瞭解追加功能 622

37.2.1  創建必要的基礎查詢 623

37.2.2  追加數據 623

37.3  瞭解合並功能 625

37.3.1  瞭解Power Query連接 625

37.3.2  合並查詢 626

37.3.3  瞭解模糊匹配 628

第38章  提高Power Query的生產率 631

38.1  關於如何提高Power Query生產率的一些提示 631

38.1.1  快速獲取關於查詢的信息 631

38.1.2  將查詢組織成組 631

38.1.3  更快速地選擇查詢中的列 632

38.1.4  重命名查詢步驟 632

38.1.5  快速創建引用表 632

38.1.6  復制查詢來節省時間 633

38.1.7  查看查詢依賴項 633

38.1.8  設置默認加載行為 633

38.1.9  防止數據類型自動更改 633

38.2  避免Power Query的性能問題 635

38.2.1  使用視圖而不是表 635

38.2.2  讓後台數據庫服務器完成一些計算 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  顯示“開發工具”選項卡 642

39.3  宏安全性簡介 642

39.4  保存含有宏的工作簿 643

39.5  兩種類型的VBA宏 644

39.5.1  VBA子過程 644

39.5.2  VBA函數 645

39.6  創建VBA宏 646

39.6.1  錄制VBA宏 646

39.6.2  關於錄制VBA宏的更多信息 652

39.6.3  編寫VBA代碼 654

39.7  學習更多知識 662

第40章  創建自定義工作表函數 663

40.1  VBA函數概述 663

40.2  查看一個簡單示例 664

40.2.1  創建自定義函數 664

40.2.2  在工作表中使用函數 664

40.2.3  分析自定義函數 665

40.3  函數過程簡介 666

40.4  執行函數過程 667

40.4.1  從過程中調用自定義函數 667

40.4.2  在工作表公式中使用自定義函數 667

40.5  使用函數過程參數 668

40.5.1  創建無參數的函數 668

40.5.2  創建有一個參數的函數 668

40.5.3  創建另一個有一個參數的函數 669

40.5.4  創建有兩個參數的函數 670

40.5.5  創建有一個區域參數的函數 670

40.5.6  創建一個簡單但有用的函數 671

40.6  調試自定義函數 671

40.7  插入自定義函數 672

40.8  學習更多知識 673

第41章  創建用戶窗體 675

41.1  理解為什麽要創建用戶窗體 675

41.2  用戶窗體的替代方法 676

41.2.1  使用 InputBox函數 676

41.2.2  使用MsgBox函數 677

41.3  創建用戶窗體：概述 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  創建用戶窗體 682

41.4.2  測試用戶窗體 683

41.4.3  創建事件處理程序過程 684

41.5  查看另一個用戶窗體示例 684

41.5.1  創建用戶窗體 685

41.5.2  創建事件處理程序過程 686

41.5.3  顯示用戶窗體 687

41.5.4  測試用戶窗體 688

41.5.5  從工作表按鈕執行宏 688

41.5.6  從快速訪問工具欄中執行宏 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  調整屬性 694

42.2.4  使用通用屬性 695

42.2.5  將控件鏈接到單元格 695

42.2.6  為控件創建宏 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  數值調節鈕控件 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  監控特定區域中的更改 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  使用區域 715

44.1.1  復制區域 716

44.1.2  復制大小可變的區域 716

44.1.3  選擇從活動單元格到行或列結尾的內容 717

44.1.4  選擇一行或一列 718

44.1.5  移動區域 718

44.1.6  高效地遍歷區域 718

44.1.7  提示輸入單元格值 719

44.1.8  確定選中內容的類型 720

44.1.9  識別多重選定區域 721

44.1.10  對選中的單元格進行計數 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章  創建自定義Excel加載項 727

45.1  加載項的概念 727

45.2  使用加載項 728

45.3  為什麽要創建加載項 729

45.4  創建加載項 729

45.5  加載項示例 730

45.5.1  瞭解Module1 731

45.5.2  關於用戶窗體 731

45.5.3  測試工作簿 732

45.5.4  添加描述性信息 732

45.5.5  為加載宏創建用戶界面 732

45.5.6  保護工程 732

45.5.7  創建加載項 733

45.5.8  安裝加載項 733