第Ⅰ部分基礎知識

第1章Excel概述 3

1.1Excel的用途 3

1.2Excel2013新增內容 4

1.3對象模型概念 5

1.4使用工作簿 5

1.4.1工作表 6

1.4.2圖表工作表 7

1.4.3宏表和對話表 7

1.5Excel用戶界面 7

1.5.1功能區 7

1.5.2后臺視圖 9

1.5.3快捷菜單和微型工具欄 10

1.5.4對話框 11

1.5.5定制用戶界面 11

1.5.6任務窗格 12

1.5.7鍵盤快捷鍵 13

1.5.8定制屏幕顯示 13

1.5.9對象和單元格的選擇 13

1.6Excel的幫助系統 14

1.7單元格格式化 15

1.7.1數字格式化 15

1.7.2樣式格式化 15

1.8表格 16

1.9工作表公式和函數 16

1.10繪圖層上的對象 17

1.10.1圖形和插圖 17

1.10.2控件 17

1.10.3圖表 18

1.10.4迷你圖 19

1.11定制Excel 19

1.11.1宏 20

1.11.2插件 20

1.12互聯網功能 20

1.13分析工具 21

1.13.1數據庫訪問 21

1.13.2方案管理 21

1.13.3數據透視表 21

1.13.4審核功能 21

1.13.5規劃求解插件 22

1.14保護選項 22

1.14.1保護公式不被覆...

1.14.2保護工作簿的結構 23

1.14.3用密碼保護工作簿 23

第2章公式基本內容 25

2.1輸入和編輯公式 25

2.1.1公式元素 25

2.1.2輸入公式 26

2.1.3粘貼名稱 27

2.1.4空格和空行 28

2.1.5公式的限制 29

2.1.6公式示例 29

2.1.7編輯公式 29

2.2在公式中使用運算符 30

2.2.1引用運算符 31

2.2.2運算符的優先級 32

2.3計算公式 35

2.4單元格和范圍引用 36

2.4.1創建絕對引用或混合引用 36

2.4.2引用其他工作表或工作簿 38

2.5復制或移動公式 39

2.6準確地復制公式 41

2.7把公式轉換成值 42

2.8隱藏公式 44

2.9公式中的錯誤 45

2.10處理循環引用 45

2.11單變量求解 47

2.11.1單變量求解示例 47

2.11.2更多有關單變量求解的內容 48

第3章使用名稱 49

3.1名稱的定義 49

3.2名稱的作用域 50

3.2.1引用名稱 51

3.2.2引用其他工作簿的名稱 51

3.2.3名稱沖突 52

3.3名稱管理器 52

3.3.1創建名稱 53

3.3.2編輯名稱 53

3.3.3刪除名稱 54

3.4創建單元格和范圍名稱的快捷方法 54

3.4.1“新建名稱”對話框 54

3.4.2使用名稱框創建名稱 55

3.4.3從單元格中的文本創建名稱 56

3.4.4命名整個行和列 58

3.4.5Excel創建的名稱 59

3.5創建多工作表名稱 59

3.6使用范圍和單元格名稱 61

3.6.1創建一個名稱列表 61

3.6.2在公式中使用名稱 62

3.6.3對名稱使用的交叉運算符 63

3.6.4使用帶名稱的范圍運算符 65

3.6.5引用多單元格命名范圍中的單個單元格 65

3.6.6在現有公式中使用名稱 66

3.6.7創建公式時自動應用名稱 66

3.6.8取消應用名稱 67

3.6.9有錯誤的名稱 67

3.6.10查看命名范圍 68

3.6.11在圖表中使用名稱 68

3.7Excel維護單元格和范圍名稱的方式 68

3.7.1插入行或列 69

3.7.2刪除行或列 69

3.7.3剪切和粘貼 69

3.8名稱中的潛在問題 69

3.8.1復制表單時的名稱問題 69

3.8.2刪除工作表時的名稱問題 70

3.9理解名稱的奧秘 72

3.9.1命名常量 73

3.9.2命名文本常量 73

3.9.3在命名公式中使用工作表函數 74

3.9.4在命名公式中使用單元格和范圍引用 75

3.9.5使用包含相對引用的命名公式 76

3.10使用名稱的高級技術 79

3.10.1使用INDIRECT函數處理命名范圍 79

3.10.2在命名公式中使用數組 80

3.10.3創建一個動態的命名公式 82

3.10.4在命名公式中使用XLM宏 83

第Ⅱ部分在公式中使用函數

第4章工作表函數入門 89

4.1什么是函數 89

4.1.1簡化公式 89

4.1.2執行其他方法無法實現的計算 90

4.1.3提高編輯任務的速度 90

4.1.4實現判斷功能 90

4.1.5其他函數功能 91

4.2函數參數類型 91

4.2.1將名字用作參數 92

4.2.2把整個行或整個列作為參數 93

4.2.3把字面值作為參數 93

4.2.4把表達式作為參數 93

4.2.5把其他函數作為參數 94

4.2.6把數組作為參數 94

4.3在公式中輸入函數的方法 94

4.3.1手工輸入函數 94

4.3.2使用“函數庫”命令 96

4.3.3使用“插入函數”對話框輸入函數 96

4.3.4輸入函數的其他技巧 98

4.4函數種類 100

4.4.1財務函數 100

4.4.2日期及時間函數 100

4.4.3數學及三角函數 100

4.4.4統計函數 101

4.4.5查找和引用函數 101

4.4.6數據庫函數 101

4.4.7文本函數 101

4.4.8邏輯函數 101

4.4.9信息函數 102

4.4.10用戶定義函數 102

4.4.11工程函數 102

4.4.12多維數據集函數 102

4.4.13兼容性函數 102

4.4.14Web函數 104

4.4.15其他函數類別 104

第5章處理文本 105

5.1文本的概念 105

5.1.1一個單元格可以容納的字符數 105

5.1.2把數字作為文本 106

5.2文本函數 107

5.2.1確定單元格中是否包含文本 107

5.2.2使用字符代碼工作 108

5.2.3確定兩個字符串是否相同 110

5.2.4連接兩個或多個單元格 111

5.2.5把格式化的值顯示成文本 112

5.2.6把格式化的貨幣值顯示為文本 113

5.2.7刪除額外的空格和非打印字符 114

5.2.8計算字符串中的字符個數 114

5.2.9重復字符或字符串 115

5.2.10創建文本直方圖 115

5.2.11填充數字 116

5.2.12改變文本的大小寫 117

5.2.13從字符串中提取字符 118

5.2.14替換文本 118

5.2.15在字符串中查找和搜索 119

5.2.16在字符串中查找和替換 120

5.3高級文本公式 120

5.3.1計算單元格中指定字符的個數 120

5.3.2計算單元格中指定子串的個數 120

5.3.3刪除尾部的減號 121

5.3.4把數字表示成序數詞 121

5.3.5為列數確定列字母 122

5.3.6從具體路徑中提取文件名 122

5.3.7提取字符串的第一個詞 123

5.3.8提取字符串的最后一個單詞 123

5.3.9提取字符串中除第一個詞以外的所有詞 124

5.3.10提取名字的名、中間名和姓 124

5.3.11刪除名字中的稱謂 126

5.3.12計算單元格中詞的數量 126

第6章處理日期和時間 127

6.1Excel如何處理日期和時間 127

6.1.1了解日期序列號 127

6.1.2輸入日期 128

6.1.3理解時間序列號 130

6.1.4輸入時間 131

6.1.5日期和時間的格式化 132

6.1.6有關日期的問題 134

6.2日期函數 135

6.2.1顯示當前日期 136

6.2.2使用函數顯示任意日期 137

6.2.3生成一系列日期 137

6.2.4把非日期字符串轉換為日期 139

6.2.5計算兩個日期之間的天數 139

6.2.6計算兩日期之間的工作日數 140

6.2.7計算指定工作日的日期 141

6.2.8計算兩日期之間的年數 142

6.2.9計算人的年齡 142

6.2.10確定在某年中的天數 143

6.2.11確定某天是星期幾 143

6.2.12確定在一年中的周數 144

6.2.13確定最近的星期日日期 144

6.2.14確定某個日期后面的第一個星期日期 144

6.2.15確定某月中的第n個星期日期 145

6.2.16計算星期日期的出現次數 145

6.2.17把日期表示為序數 146

6.2.18計算節假日期 147

6.2.19確定某月的最后一天 149

6.2.20確定某年是否是閏年 150

6.2.21確定一個日期的季度 150

6.2.22把年份轉換成羅馬數字 150

6.3時間函數 151

6.3.1顯示當前時間 151

6.3.2使用函數顯示任何時間 152

6.3.3計算兩個時間的差 153

6.3.4合計超過24小時的時間 154

6.3.5轉換軍事時間 156

6.3.6把帶小數點的小時、分鐘或秒轉換成時間 156

6.3.7在時間中加小時數、分鐘數或秒數 157

6.3.8時區之間的轉換 157

6.3.9時間值的舍入 158

6.3.10使用非時間值 159

第7章計數與求和 161

7.1工作表單元格的計數與求和 161

7.2其他計數方法 162

7.3基本計數公式 163

7.3.1統計單元格的總數 164

7.3.2統計空單元格的個數 164

7.3.3非空單元格的計數 165

7.3.4數字單元格計數 165

7.3.5文本單元格計數 165

7.3.6非文本單元格計數 165

7.3.7邏輯值計數 166

7.3.8某個范圍中的錯誤值計數 166

7.4高級計數公式 166

7.4.1使用COUNTIF函數統計單元格數量 166

7.4.2統計滿足多個條件的單元格數量 167

7.4.3出現頻率最高的項的計數 171

7.4.4確定特定文本的出現頻率 172

7.4.5統計唯一值的數目 173

7.4.6創建頻率分布 174

7.5求和公式 181

7.5.1對范圍內的所有單元格求和 181

7.5.2對包含錯誤的范圍求和 182

7.5.3計算累計和 182

7.5.4求n個最大值的和 184

7.6使用單個條件求和 185

7.6.1只對負值求和 185

7.6.2根據范圍Difference的值求和 186

7.6.3基于文本的比較結果求和 186

7.6.4基于日期的比較結果求和 187

7.7使用多重條件求和 187

7.7.1使用AND條件 188

7.7.2使用OR條件 189

7.7.3使用AND和OR條件 189

第8章使用查找函數 191

8.1什么是查找公式 191

8.2與查找相關的函數 192

8.3基本查找函數 193

8.3.1VLOOKUP函數 193

8.3.2HLOOKUP函數 194

8.3.3LOOKUP函數 195

8.3.4綜合使用MATCH和INDEX函數 196

8.4專業查找公式 198

8.4.1精確查找 199

8.4.2查找值的左側 200

8.4.3進行區分大小寫的查找 201

8.4.4在多個查找表中進行選擇 202

8.4.5確定考試分數的等級 202

8.4.6計算等級平均分(GPA) 203

8.4.7進行雙向查找 204

8.4.8進行雙列查找 205

8.4.9確定范圍中值的地址 206

8.4.10使用最接近匹配查找一個值 207

8.4.11使用線性插值法查找一個值 208

第9章表格和列表 211

9.1表格和術語 211

9.1.1列表示例 212

9.1.2表格示例 212

9.2處理表格 214

9.2.1創建表格 214

9.2.2改變表格的外觀 215

9.2.3在表格中導航和選擇 216

9.2.4添加新行或列 216

9.2.5刪除行或列 217

9.2.6移動表格 217

9.2.7從表格中刪除重復的行 217

9.2.8表格的排序和篩選 219

9.2.9處理匯總行 224

9.2.10在表格中使用公式 226

9.2.11引用表格中的數據 228

9.2.12將表格轉換為列表 232

9.3使用高級篩選功能 232

9.3.1設置條件范圍 233

9.3.2應用高級篩選 234

9.3.3清除高級篩選 235

9.4指定高級篩選條件 235

9.4.1指定單一條件 236

9.4.2指定多重條件 238

9.4.3指定計算條件 239

9.5使用數據庫函數 240

9.6插入分類匯總 242

第10章其他計算 247

10.1單位轉換 247

10.2舍入數字 251

10.2.1基本舍入公式 251

10.2.2舍入到最近倍數 252

10.2.4處理以分數表示的美元值 253

10.2.5使用INT和TRUNC函數 255

10.2.6舍入為一個偶數或奇數整數 255

10.2.7舍入為n個有效數字 256

10.3解直角三角形 256

10.4面積、表面積、周長和體積的計算 258

10.4.1計算正方形的面積和周長 258

10.4.2計算矩形的面積和周長 258

10.4.3計算圓的面積和周長 259

10.4.4計算梯形的面積 259

10.4.5計算三角形的面積 259

10.4.6計算球體的表面積和體積 259

10.4.7計算立方體的表面積和體積 260

10.4.8計算長方體的表面積和體積 260

10.4.9計算圓錐體的表面積和體積 260

10.4.10計算圓柱體的體積 260

10.4.11計算棱椎的體積 260

10.5解聯立方程組 261

10.6處理正態分布 262

第Ⅲ部分財務公式

第11章借貸和投資公式 267

11.1貨幣時值 267

11.2貸款計算 268

11.2.1用于計算貸款信息的工作表函數 268

11.2.2貸款計算示例 271

11.2.3信用卡還款 273

11.2.4創建貸款分期償還時間表 274

11.2.5計算不定期還款的貸款 275

11.3投資計算 277

11.3.1單筆存款的未來值 277

11.3.2一系列收益的現值 281

11.3.3一系列存款的未來值 282

第12章貼現和折舊函數 285

12.1使用NPV函數 285

12.1.1NPV的定義 286

12.1.2NPV函數示例 287

12.2使用IRR函數 291

12.2.1回報率 292

12.2.2幾何增長率 293

12.2.3檢查結果 294

12.3不定期的現金流 295

12.3.1凈現值 295

12.3.2內部回報率 296

12.4折舊計算 297

第13章財務計劃 301

13.1創建財務計劃 301

13.2創建分期償還計劃 301

13.2.1簡單的分期償還計劃 302

13.2.2動態的分期償還計劃 304

13.2.3信用卡計算 306

13.3使用數據表匯總貸款項 307

13.3.1創建單變量數據表 308

13.3.2創建雙變量數據表 309

13.4財務決算和比率 311

13.4.1基本財務決算 311

13.4.2比率分析 314

13.5創建指數 316

第Ⅳ部分數組公式

第14章數組 321

14.1數組公式 321

14.1.1多單元格數組公式 322

14.1.2單個單元格數組公式 323

14.1.3創建數組常量 324

14.1.4數組常量元素 325

14.2理解數組的維數 325

14.2.1一維橫向數組 325

14.2.2一維縱向數組 326

14.2.3二維數組 326

14.3命名數組常量 328

14.4使用數組公式 329

14.4.1輸入數組公式 329

14.4.2選擇數組公式范圍 329

14.4.3編輯數組公式 330

14.4.4擴展或壓縮多單元格數組公式 331

14.5使用多單元格數組公式 331

14.5.1從一個范圍的值中創建數組 331

14.5.2從一個范圍的值中創建數組常量 332

14.5.3對數組執行運算 332

14.5.4對數組使用函數 334

14.5.5數組轉置 334

14.5.6生成一個連續整數的數組 335

14.6使用單個單元格數組公式 336

14.6.1范圍中的字符計數 336

14.6.2范圍中最小的三個數值求和 337

14.6.3范圍中文本單元格的計數 338

14.6.4省略中間公式 340

14.6.5使用數組代替范圍引用 341

第15章使用數組公式的技巧 343

15.1使用單個單元格數組公式 343

15.1.1包含錯誤的范圍的求和 343

15.1.2范圍中錯誤值的計數 345

15.1.3范圍中n個最大值的求和 345

15.1.4計算非零數的平均值 345

15.1.5確定范圍中是否包含特定值 346

15.1.6兩個范圍中不同單元格的計數 347

15.1.7返回范圍中最大值的位置 349

15.1.8查找范圍中某個值第n次出現的行 349

15.1.9返回范圍中的最長文本 349

15.1.10確定一個范圍是否包含有效值 350

15.1.11整數數字求和 351

15.1.12舍入值求和 352

15.1.13范圍中相隔n個數的數值求和 353

15.1.14從字符串中刪除非數字字符 354

15.1.15確定范圍中最接近的數值 355

15.1.16返回一列中的最后一個數值 355

15.1.17返回一行中的最后一個數值 356

15.2使用多單元格數組公式 357

15.2.1只返回范圍中的正數 357

15.2.2返回范圍中的非空單元格 358

15.2.3反轉范圍中單元格的順序 358

15.2.4對一個范圍的值動態排序 359

15.2.5返回范圍中唯一元素的列表 359

15.2.6在范圍中顯示日歷 360

第Ⅴ部分其他公式

第16章導入和清洗數據 365

16.1關于數據 365

16.2導入數據 366

16.2.1從文件導入 366

16.2.2將文本文件導入到指定范圍 368

16.2.3復制并粘貼數據 370

16.3數據清洗技術 370

16.3.1刪除重復行 370

16.3.2標識重復行 371

16.3.3拆分文本 372

16.3.4改變文本大小寫 376

16.3.5刪除多余的空格 377

16.3.6刪除亂字符 378

16.3.7轉換值 378

16.3.8對值進行分類 379

16.3.9連接列 380

16.3.10重新安排列 380

16.3.11隨機化行 380

16.3.12在列表中匹配文本 381

16.3.13將縱向數據轉換為橫向數據 382

16.3.14填充導入報告中的空隙 383

16.3.15拼寫檢查 384

16.3.16替換或刪除單元格中的文本 385

16.3.17向單元格添加文本 386

16.3.18修復尾部的負號 386

16.4數據清洗檢查表 387

16.5導出數據 387

16.5.1導出到文本文件 388

16.5.2導出到其他文件格式 388

第17章圖表技術 391

17.1理解SERIES公式 391

17.1.1在SERIES公式中使用名稱 393

17.1.2斷開圖表系列與其數據范圍的鏈接 393

17.2創建與單元格的鏈接 394

17.2.1添加圖表標題鏈接 394

17.2.2添加坐標軸標題鏈接 395

17.2.3添加文本鏈接 395

17.2.4為圖表添加鏈接的圖片 396

17.3圖表示例 396

17.3.1單數據點圖表 396

17.3.2在柱形圖表中顯示帶條件的顏色 398

17.3.3創建比較直方圖 399

17.3.4創建甘特圖 400

17.3.5創建箱式圖 403

17.3.6繪出每隔n個點的數據 404

17.3.7在圖表中標識最大值和最小值 406

17.4創建時間軸 407

17.4.1繪制數學函數 408

17.4.2繪制圓 411

17.4.3創建鐘表圖 413

17.4.4創建卓越的設計 415

17.5使用趨勢線 416

17.5.1線性趨勢線 417

17.5.2非線性趨勢線 420

17.5.3趨勢線方程總結 422

17.6創建交互式圖表 423

17.6.1從下拉列表中選擇系列 423

17.6.2繪制最后n個數據點 423

17.6.3選擇起始日期和點的數量 424

17.6.4顯示人口數據 425

17.6.5顯示天氣數據 426

第18章數據透視表 429

18.1數據透視表簡介 429

18.2數據透視表示例 430

18.3適合數據透視表的數據 432

18.4自動創建數據透視表 434

18.5手工創建數據透視表 435

18.5.1指定數據 436

18.5.2指定數據透視表的位置 436

18.5.3數據透視表的布局 438

18.5.4格式化數據透視表 439

18.5.5修改數據透視表 441

18.6更多的數據透視表示例 442

18.6.1問題1：每個支行每天的新存款金額有多少 442

18.6.2問題2：一星期中哪天的存款最多 443

18.6.3問題3：按賬戶類型分，每個支行開了多少賬戶 444

18.6.4問題4：不同賬戶類型的金額分布情況如何 444

18.6.5問題5：出納員新開的哪類賬戶最多 445

18.6.6問題6：“中心”支行與其他兩個支行相比，情況怎樣 445

18.6.7問題7：哪個支行為新客戶開的支票賬戶最多 446

18.7組合數據透視表中的項 447

18.7.1手工組合示例 447

18.7.2查看組合的數據 449

18.7.3自動組合示例 450

18.8創建頻率分布 453

18.9創建計算字段或計算項 455

18.9.1創建計算字段 457

18.9.2插入計算項 459

18.10使用切片器篩選數據透視表 461

18.11使用日程表篩選數據透視表 462

18.12引用數據透視表中的單元格 464

18.13另一個數據透視表示例 465

18.14使用數據模型 468

18.15創建數據透視圖 471

18.15.1數據透視圖示例 471

18.15.2關于數據透視圖的更多內容 473

第19章條件格式 475

19.1條件格式簡介 475

19.2指定條件格式 477

19.2.1可以應用的格式類型 477

19.2.2定義自己的規則 478

19.3使用圖形的條件格式 479

19.3.1使用數據條 479

19.3.2使用色階 481

19.3.3使用圖標集 483

19.4創建基于公式的規則 486

19.4.1理解相對和絕對引用 487

19.4.2條件格式公式示例 488

19.5使用條件格式 495

19.5.1管理規則 495

19.5.2復制包含條件格式的單元格 495

19.5.3刪除條件格式 496

19.5.4定位包含條件格式的單元格 496

第20章數據驗證 497

20.1數據驗證簡介 497

20.2指定驗證條件 498

20.3可應用的驗證條件類型 499

20.4創建下拉列表 500

20.5將公式用于數據驗證規則 501

20.6理解單元格引用 502

20.7數據驗證公式示例 503

20.7.1只接受文本 503

20.7.2接受比前一個單元格大的數值 503

20.7.3只接受不重復的輸入項 504

20.7.4接受以特定字符開頭的文本 504

20.7.5只接受是星期一的日期 505

20.7.6只接受不超過總和的值 505

20.7.7創建有依賴關系的列表 505

第21章創建元公式 507

21.1什么是元公式 507

21.2創建元公式：一個簡單例子 508

21.3元公式示例 510

21.3.1使用元公式刪除中間名 510

21.3.2使用元公式返回字符串中最后一個空格字符的位置 514

21.3.3使用元公式判斷信用卡號碼的有效性 517

21.3.4生成隨機姓名 521

21.4元公式的優缺點 523

第22章調試公式的工具和方法 525

22.1什么是公式調試 525

22.2公式的問題及其解決辦法 526

22.2.1括號不匹配 526

22.2.2用#填充的單元格 527

22.2.3空白單元格并非空白 528

22.2.4額外的空格字符 528

22.2.5返回錯誤的公式 529

22.2.6絕對/相對引用問題 533

22.2.7運算符優先級問題 534

22.2.8公式不計算 535

22.2.9實際數值與顯示數值 535

22.2.10浮點數錯誤 536

22.2.11幻影鏈接錯誤 537

22.2.12邏輯值錯誤 537

22.2.13循環引用錯誤 538

22.3Excel的審核工具 539

22.3.1辨別特定類型的單元格 539

22.3.2查看公式 540

22.3.3跟蹤單元格關系 542

22.3.4跟蹤錯誤值 543

22.3.5糾正循環引用錯誤 544

22.3.6使用后臺錯誤檢查特性 544

22.3.7使用Excel的公式求值器 546

第Ⅵ部分開發自定義工作表函數

第23章VBA簡介 549

23.1關于VBA 549

23.2顯示“開發工具”選項卡 550

23.3關于宏的安全性 550

23.4保存包含宏的工作簿 552

23.5VisualBasic編輯器 552

23.5.1激活VB編輯器 552

23.5.2VB編輯器的組件 553

23.5.3使用“工程”窗口 555

23.5.4使用代碼窗口 557

23.5.5輸入VBA代碼 559

23.5.6保存工程 561

第24章函數過程基礎 563

24.1為什么要創建自定義函數 563

24.2VBA函數的范例 564

24.3關于函數過程 566

24.3.1聲明函數 566

24.3.2選擇函數名 566

24.3.3在公式中使用函數 567

24.3.4使用函數參數 568

24.4使用“插入函數”對話框 569

24.4.1添加函數說明 569

24.4.2指定函數類別 571

24.4.3添加參數說明 572

24.5測試和調試函數 573

24.5.1使用VBA的MsgBox語句 574

24.5.2在代碼中使用Debug.Print語句 576

24.5.3調用子過程中的函數 576

24.5.4在函數中設置斷點 579

24.6為函數創建加載項 580

第25章VBA編程概念 583

25.1一個函數過程示例 583

25.2在代碼中使用注釋 585

25.3使用變量、數據類型和常量 586

25.3.1定義數據類型 586

25.3.2聲明變量 587

25.3.3使用常量 589

25.3.4使用字符串 589

25.3.5使用日期 590

25.4使用賦值表達式 591

25.5使用數組 592

25.5.1聲明數組 592

25.5.2聲明多維數組 593

25.6使用VBA的內置函數 593

25.7控制執行 594

25.7.1If-Then結構 595

25.7.2SelectCase結構 596

25.7.3循環指令塊 597

25.7.4OnError語句 601

25.8使用范圍 603

25.8.1ForEach-Next結構 603

25.8.2引用范圍 604

25.8.3一些有用的范圍屬性 606

25.8.4Set關鍵字 609

25.8.5lntersect函數 609

25.8.6Union函數 610

25.8.7UsedRange屬性 610

第26章VBA自定義函數示例 613

26.1簡單函數 613

26.1.1檢查單元格是否含有公式 614

26.1.2返回單元格的公式 614

26.1.3確定單元格是否隱藏 615

26.1.4返回工作表名稱 615

26.1.5返回工作簿名稱 616

26.1.6返回應用程序名稱 616

26.1.7返回Excel的版本號 617

26.1.8返回單元格的格式信息 617

26.2確定單元格的數據類型 619

26.3多功能函數 620

26.4生成隨機數 623

26.4.1產生不變的隨機數 623

26.4.2隨機選擇單元格 624

26.5計算銷售傭金 625

26.5.1用于簡單傭金結構的函數 626

26.5.2用于復雜傭金結構的函數 626

26.6文本處理函數 627

26.6.1反轉字符串 628

26.6.2攪亂文本 628

26.6.3返回首字母縮寫詞 629

26.6.4檢查文本是否與模式匹配 629

26.6.5檢查單元格中是否包含特定單詞 630

26.6.6檢查單元格是否包含文本 631

26.6.7從字符串中提取第n個元素 632

26.6.8拼寫一個數字 633

26.7計數函數 633

26.7.1計算與模式匹配的單元格數 634

26.7.2計算工作簿中的工作表個數 634

26.7.3計算范圍中的單詞數 634

26.8日期函數 635

26.8.1計算下一個星期一的日期 635

26.8.2計算下一個星期幾的日期 636

26.8.3指出日期在月中的第幾個星期 636

26.8.4處理1900年以前的日期 637

26.9返回列或行中最后一個非空單元格 638

26.9.1LASTINCOLUMN函數 638

26.9.2LASTINROW函數 639

26.10多表函數 639

26.10.1返回所有工作表中的最大值 640

26.10.2SHEETOFFSET函數 641

26.11高級函數技術 642

26.11.1返回錯誤值 642

26.11.2從函數中返回一個數組 643

26.11.3返回一個沒有重復隨機整數的數組 645

26.11.4隨機排列一個范圍 646

26.11.5使用可選參數 648

26.11.6使用不確定數量的參數 650

第Ⅶ部分附錄

附錄AExcel函數參考 657

附錄B使用自定義數字格式 673 2100433B

這部深入討論Excel的著作涵蓋了工作表函數、命名單元格和范圍，以及用于查找數值和財務計算的公式。此外，還有整整一個部分用來討論強大的數組公式。您將學習到元公式、公式調試、使用VBA創建自定義工作表函數，以及更多內容。

領悟專家技巧，釋放Excel潛能

如果只是使用Excel來創建簡單的表格和圖表，現在是時候深挖這個軟件的真正功能了：公式。本書是一本理想的指南，介紹了公式的方方面面，向讀者展示了Excel的強大功能。

利用本書的提示和技巧，讀者的工作流程將變得更快、更輕松、更精確。本書介紹的專家技巧是在別的地方找不到的。無論是初學者還是高級用戶，這本書都是一本重要參考，可帶領讀者深入Excel，最大程度地利用Excel 2016版本的功能。

第Ⅰ部分 基礎知識

第1章 Excel用戶界面概述 3

1.1 Excel的工作方式 3

1.1.1 工作表 4

1.1.2 圖表工作表 5

1.1.3 宏表和對話表 5

1.2 Excel用戶界面 5

1.2.1 功能區 5

1.2.2 后臺視圖 7

1.2.3 快捷菜單和浮動工具欄 7

1.2.4 對話框 7

1.2.5 自定義用戶界面 7

1.2.6 任務窗格 8

1.2.7 自定義屏幕顯示 8

1.2.8 數字格式化 8

1.2.9 樣式格式化 9

1.3 保護選項 9

1.3.1 保護整個工作簿的訪問 9

1.3.2 限制對特定工作表范圍的

訪問 12

1.3.3 保護工作簿結構 15

第2章 公式基本內容 17

2.1 輸入和編輯公式 17

2.1.1 公式元素 17

2.1.2 輸入公式 18

2.1.3 粘貼名稱 19

2.1.4 空格和空行 20

2.1.5 公式的限制 20

2.1.6 公式示例 20

2.1.7 編輯公式 21

2.2 在公式中使用運算符 22

2.2.1 引用運算符 23

2.2.2 運算符的優先級 24

2.3 計算公式 26

2.4 單元格和范圍引用 27

2.4.1 創建絕對引用或混合引用 27

2.4.2 引用其他工作表或工作簿 29

2.5 復制或移動公式 30

2.6 準確地復制公式 31

2.7 把公式轉換成值 32

2.8 隱藏公式 34

2.9 公式中的錯誤 3...

2.10處理循環引用35

2.11單變量求解37

2.11.1單變量求解示例37

2.11.2更多有關單變量求解的內容38

第3章使用名稱39

3.1名稱的定義39

3.2名稱的作用域40

3.2.1引用名稱41

3.2.2引用其他工作簿的名稱41

3.2.3名稱沖突42

3.3名稱管理器42

3.3.1創建名稱43

3.3.2編輯名稱43

3.3.3刪除名稱43

3.4創建單元格和范圍名稱的

快捷方法44

3.4.1“新建名稱”對話框44

3.4.2使用名稱框創建名稱45

3.4.3從單元格中的文本創建名稱46

3.4.4命名整個行和列46

3.4.5Excel創建的名稱47

3.5創建多表名稱48

3.6使用范圍和單元格名稱49

3.6.1創建一個名稱列表49

3.6.2在公式中使用名稱50

3.6.3對名稱使用的交叉運算符51

3.6.4使用帶名稱的范圍運算符52

3.6.5引用多單元格命名范圍中的

單個單元格53

3.6.6在現有公式中使用名稱53

3.6.7創建公式時自動應用名稱54

3.6.8取消名稱的應用54

3.6.9有錯誤的名稱55

3.6.10查看命名范圍55

3.6.11在圖表中使用名稱55

3.7Excel維護單元格和

范圍名稱的方式56

3.7.1插入行或列56

3.7.2刪除行或列56

3.7.3剪切和粘貼56

3.8名稱中的潛在問題56

3.8.1復制工作表時的名稱問題57

3.8.2刪除工作表時的名稱問題57

3.9理解名稱的奧秘58

3.9.1命名常量59

3.9.2命名文本常量59

3.9.3在命名公式中使用

工作表函數60

3.9.4在命名公式中使用單元格和

范圍引用61

3.9.5使用包含相對引用的

命名公式62

3.10使用名稱的高級技術64

3.10.1使用INDIRECT函數

處理命名范圍64

3.10.2在命名公式中使用數組66

3.10.3創建一個動態的命名公式67

3.10.4在命名公式中使用XLM宏68

第Ⅱ部分在公式中使用函數

第4章工作表函數入門73

4.1什么是函數73

4.1.1簡化公式73

4.1.2執行其他方法無法實現的計算74

4.1.3提高編輯任務的速度74

4.1.4實現判斷功能74

4.1.5其他函數75

4.2函數參數類型75

4.2.1將名稱用作參數76

4.2.2把整行或整列作為參數76

4.2.3把字面值作為參數77

4.2.4把表達式作為參數77

4.2.5把其他函數作為參數77

4.2.6把數組作為參數78

4.3在公式中輸入函數的方法78

4.3.1手工輸入函數78

4.3.2使用“函數庫”命令79

4.3.3使用“插入函數”對話框

輸入函數80

4.3.4輸入函數的其他技巧82

第5章處理文本85

5.1文本的概念85

5.1.1一個單元格可以容納的

字符數85

5.1.2把數字作為文本85

5.2文本函數87

5.2.1確定單元格中是否包含文本87

5.2.2使用字符代碼88

5.2.3確定兩個字符串是否相同90

5.2.4連接兩個或多個單元格90

5.2.5把格式化的值顯示成文本91

5.2.6把格式化的貨幣值顯示為

文本93

5.2.7刪除額外的空格和非打印字符93

5.2.8計算字符串中的字符個數93

5.2.9重復字符或字符串94

5.2.10創建文本直方圖94

5.2.11填充數字95

5.2.12改變文本的大小寫96

5.2.13從字符串中提取字符97

5.2.14用其他文本替換文本97

5.2.15在字符串中查找和搜索98

5.2.16在字符串中進行查找和替換99

5.3高級文本公式99

5.3.1計算單元格中指定字符的

個數99

5.3.2計算單元格中指定子串的

個數100

5.3.3刪除尾部的減號100

5.3.4把數字表示成序數詞100

5.3.5為列數確定列字母101

5.3.6從具體路徑中提取文件名101

5.3.7提取字符串的第一個詞102

5.3.8提取字符串的最后一個單詞102

5.3.9提取字符串中除第一個詞

以外的所有詞102

5.3.10提取名字的名、中間

名和姓103

5.3.11刪除姓名中的稱謂104

5.3.12計算單元格中詞的數量105

第6章處理日期和時間107

6.1Excel如何處理日期和時間107

6.1.1了解日期序列號107

6.1.2輸入日期108

6.1.3理解時間序列號110

6.1.4輸入時間111

6.1.5日期和時間的格式化112

6.1.6有關日期的問題114

6.2日期函數115

6.2.1顯示當前日期116

6.2.2使用函數顯示任意日期117

6.2.3生成一系列日期117

6.2.4把非日期字符串轉換為日期119

6.2.5計算兩個日期之間的天數119

6.2.6計算兩日期之間的工作日數120

6.2.7計算指定工作日天數的日期121

6.2.8計算兩日期之間的年數121

6.2.9計算人的年齡122

6.2.10確定在某年中的天數123

6.2.11確定某天是星期幾123

6.2.12確定在一年中的周數124

6.2.13確定最近星期日的日期124

6.2.14確定某個日期后面的第一個

星期的某一天的日期124

6.2.15確定某月中第n個星期的

某一天的日期125

6.2.16計算星期日期的出現次數125

6.2.17把日期表示為序數126

6.2.18計算節假日期127

6.2.19確定某月的最后一天129

6.2.20確定某年是否是閏年129

6.2.21確定某個日期的季度130

6.2.22把年份轉換成羅馬數字130

6.3時間函數130

6.3.1顯示當前時間131

6.3.2使用函數顯示任何時間131

6.3.3計算兩個時間的差132

6.3.4合計超過24小時的時間133

6.3.5轉換軍事時間135

6.3.6把帶小數點的小時、分鐘

或秒轉換成時間136

6.3.7在時間中添加小時數、

分鐘數或秒數136

6.3.8時區之間的轉換137

6.3.9時間值的舍入138

6.3.10計算期間值138

第7章計數與求和141

7.1工作表單元格的計數與求和141

7.2其他計數方法143

7.3基本計數公式143

7.3.1統計單元格的總數144

7.3.2統計空單元格的個數144

7.3.3非空單元格計數145

7.3.4數字單元格計數145

7.3.5文本單元格計數145

7.3.6非文本單元格計數145

7.3.7邏輯值計數145

7.3.8某個范圍中的錯誤值計數145

7.4高級計數公式146

7.4.1使用COUNTIF函數統計

單元格數量146

7.4.2統計滿足多個條件的

單元格數量147

7.4.3出現頻率最高的項的計數150

7.4.4確定特定文本的出現頻率151

7.4.5統計唯一值的數目153

7.4.6創建頻率分布154

7.5求和公式160

7.5.1對范圍內的所有單元格求和160

7.5.2對包含錯誤的范圍求和161

7.5.3計算累計和161

7.5.4求n個最大值的和162

7.6使用單個條件求和163

7.6.1只對負值求和164

7.6.2根據范圍Difference中的

值求和165

7.6.3基于文本的比較結果求和165

7.6.4基于日期的比較結果求和165

7.7使用多重條件求和165

7.7.1使用AND條件166

7.7.2使用OR條件167

7.7.3使用AND和OR條件167

第8章使用查找函數169

8.1什么是查找公式169

8.2與查找相關的函數170

8.3基本查找函數171

8.3.1VLOOKUP函數171

8.3.2HLOOKUP函數173

8.3.3LOOKUP函數173

8.3.4組合使用MATCH和

INDEX函數175

8.4專業查找公式176

8.4.1精確查找177

8.4.2查找值的左側178

8.4.3進行區分大小寫的查找179

8.4.4在多個查找表中進行選擇179

8.4.5確定考試分數的等級180

8.4.6計算等級平均分(GPA)181

8.4.7進行雙向查找182

8.4.8進行雙列查找183

8.4.9確定范圍中值的地址184

8.4.10使用最接近匹配查找

一個值184

8.4.11使用線性插值法查找

一個值185

第9章表格和列表189

9.1表格和術語189

9.1.1列表示例190

9.1.2表格示例190

9.2處理表格191

9.2.1創建表格192

9.2.2改變表格的外觀193

9.2.3在表格中導航和選擇193

9.2.4添加新行或新列194

9.2.5刪除行或列194

9.2.6移動表格195

9.2.7從表格中刪除重復的行195

9.2.8表格的排序和篩選196

9.2.9處理匯總行201

9.2.10在表格中使用公式202

9.2.11引用表格中的數據204

9.2.12將表格轉換為列表208

9.3使用高級篩選功能208

9.3.1設置條件范圍209

9.3.2應用高級篩選209

9.3.3清除高級篩選211

9.4指定高級篩選條件211

9.4.1指定單一條件211

9.4.2指定多重條件213

9.4.3指定計算條件215

9.5使用數據庫函數216

9.6插入分類匯總217

第10章其他計算221

10.1單位轉換221

10.2舍入數字224

10.2.1基本舍入公式225

10.2.2舍入到最近倍數226

10.2.3舍入貨幣值226

10.2.4處理以分數表示的

美元值227

10.2.5使用INT和TRUNC

函數228

10.2.6舍入為一個偶數或奇數228

10.2.7舍入為n位有效數字229

10.3解直角三角形229

10.4面積、表面積、周長和

體積的計算231

10.4.1計算正方形的面積和周長231

10.4.2計算矩形的面積和周長232

10.4.3計算圓的面積和周長232

10.4.4計算梯形的面積232

10.4.5計算三角形的面積232

10.4.6計算球體的表面積和體積233

10.4.7計算立方體的表面積和

體積233

10.4.8計算長方體的表面積和

體積233

10.4.9計算圓錐體的表面積和

體積233

10.4.10計算圓柱體的體積234

10.4.11計算棱椎的體積234

10.5解聯立方程組234

10.6處理正態分布235

第Ⅲ部分財務公式

第11章借貸和投資公式241

11.1貨幣時值241

11.2貸款計算242

11.2.1用于計算貸款信息的

工作表函數242

11.2.2貸款計算示例245

11.2.3信用卡還款246

11.2.4創建貸款分期償還時間表248

11.2.5計算不定期還款的貸款249

11.3投資計算251

11.3.1單筆存款的未來值251

11.3.2一系列收益的現值256

11.3.3一系列存款的未來值256

第12章貼現和折舊計算公式259

12.1使用NPV函數259

12.1.1NPV的定義260

12.1.2NPV函數示例261

12.2使用IRR函數265

12.2.1回報率266

12.2.2幾何增長率267

12.2.3檢查結果267

12.3不定期的現金流268

12.3.1凈現值269

12.3.2內部回報率270

12.4折舊計算270

第13章財務計劃275

13.1創建財務計劃275

13.2創建分期償還計劃275

13.2.1簡單的分期償還計劃276

13.2.2動態的分期償還計劃278

13.2.3信用卡計算280

13.3使用數據表匯總貸款項282

13.3.1創建單變量數據表282

13.3.2創建雙變量數據表283

13.4財務決算和比率285

13.4.1基本財務決算285

13.4.2比率分析288

13.5創建指數290

第Ⅳ部分數組公式

第14章數組295

14.1數組公式295

14.1.1多單元格數組公式296

14.1.2單個單元格數組公式297

14.1.3創建數組常量298

14.1.4數組常量元素299

14.2理解數組的維數299

14.2.1一維橫向數組299

14.2.2一維縱向數組300

14.2.3二維數組300

14.3命名數組常量301

14.4使用數組公式303

14.4.1輸入數組公式303

14.4.2選擇數組公式范圍303

14.4.3編輯數組公式303

14.4.4擴展或壓縮多單元格

數組公式305

14.5使用多單元格數組公式305

14.5.1從一個范圍的值中

創建數組305

14.5.2從一個范圍的值中創建

數組常量306

14.5.3對數組執行運算306

14.5.4對數組使用函數307

14.5.5數組轉置308

14.5.6生成一個連續整數的數組309

14.6使用單個單元格數組公式310

14.6.1范圍中的字符計數310

14.6.2范圍中最小的三個

數值求和311

14.6.3范圍中文本單元格的計數312

14.6.4省略中間公式313

14.6.5使用數組代替范圍引用315

第15章使用數組公式的技巧317

15.1使用單個單元格數組公式317

15.1.1對包含錯誤的范圍

進行求和317

15.1.2范圍中錯誤值的計數318

15.1.3范圍中n個最大值的求和319

15.1.4計算非零數的平均值319

15.1.5確定范圍中是否包含

特定值320

15.1.6兩個范圍中不同單元格的

計數321

15.1.7返回范圍中最大值的位置323

15.1.8查找范圍中某個值第n次

出現的行323

15.1.9返回范圍中的最長文本323

15.1.10確定一個范圍是否包含

有效值324

15.1.11整數數字求和325

15.1.12舍入值求和326

15.1.13對范圍中相隔n個數的

數值求和327

15.1.14從字符串中刪除非

數字字符328

15.1.15確定范圍中最接近的

數值329

15.1.16返回一列中的最后

一個數值330

15.1.17返回一行中的最后

一個數值330

15.2使用多單元格數組公式331

15.2.1只返回范圍中的正數331

15.2.2返回范圍中的非空單元格332

15.2.3反轉范圍中單元格的順序333

15.2.4對一個范圍的值動態排序334

15.2.5返回范圍中唯一元素的

列表334

15.2.6在范圍中顯示日歷335

第Ⅴ部分其他公式

第16章導入和清洗數據341

16.1關于數據341

16.2導入數據341

16.2.1從文件導入342

16.2.2將文本文件導入到

指定范圍344

16.2.3復制并粘貼數據345

16.3數據清洗技術346

16.3.1刪除重復行346

16.3.2標識重復行347

16.3.3拆分文本348

16.3.4改變文本大小寫353

16.3.5刪除多余的空格354

16.3.6刪除亂字符354

16.3.7轉換值355

16.3.8對值進行分類355

16.3.9連接列357

16.3.10重新安排列357

16.3.11隨機化行358

16.3.12在列表中匹配文本358

16.3.13將縱向數據轉換為

橫向數據359

16.3.14填充導入報告中的空隙361

16.3.15拼寫檢查362

16.3.16替換或刪除單元格中的

文本362

16.3.17向單元格添加文本364

16.3.18修復尾部的負號364

16.4數據清洗檢查表365

16.5導出數據365

16.5.1導出到文本文件366

16.5.2導出到其他文件格式366

第17章圖表技術369

17.1理解SERIES公式369

17.1.1在SERIES公式中

使用名稱371

17.1.2斷開圖表系列與其數據

范圍的鏈接371

17.2創建與單元格的鏈接372

17.2.1添加圖表標題鏈接372

17.2.2添加坐標軸標題鏈接373

17.2.3添加文本鏈接373

17.2.4為圖表添加鏈接的圖片374

17.3圖表示例374

17.3.1單數據點圖表374

17.3.2在柱形圖表中顯示帶

條件的顏色376

17.3.3創建比較直方圖378

17.3.4創建甘特圖379

17.3.5創建箱式圖381

17.3.6繪出每隔n個點的數據383

17.3.7在圖表中標識最大值和

最小值384

17.4創建時間軸385

17.4.1繪制數學函數386

17.4.2繪制圓390

17.4.3創建鐘表圖393

17.4.4創建卓越的設計394

17.5使用趨勢線396

17.5.1線性趨勢線396

17.5.2非線性趨勢線402

17.5.3趨勢線方程總結403

17.6創建交互式圖表404

17.6.1從下拉列表中選擇系列404

17.6.2繪制最后n個數據點404

17.6.3選擇起始日期和點的數量405

17.6.4顯示人口數據406

17.6.5顯示天氣數據407

第18章數據透視表411

18.1數據透視表簡介411

18.2數據透視表示例412

18.3適合數據透視表的數據414

18.4自動創建數據透視表416

18.5手工創建數據透視表417

18.5.1指定數據417

18.5.2指定數據透視表的位置418

18.5.3數據透視表的布局420

18.5.4格式化數據透視表421

18.5.5修改數據透視表423

18.6更多的數據透視表示例425

18.6.1問題1：每個支行每天的

新存款金額有多少？425

18.6.2問題2：一星期中哪天的

存款最多？425

18.6.3問題3：按賬戶類型分，每

個支行開了多少賬戶？426

18.6.4問題4：不同賬戶類型的

金額分布情況如何？426

18.6.5問題5：柜員新開的哪類

賬戶最多？427

18.6.6問題6：“中心”支行與其他兩

個支行相比，情況怎樣？428

18.6.7問題7：哪個支行為新客戶

開的支票賬戶最多？429

18.7組合數據透視表中的項429

18.7.1手工組合示例430

18.7.2查看組合的數據431

18.7.3自動組合示例432

18.8創建頻率分布436

18.9創建計算字段或計算項437

18.9.1創建計算字段439

18.9.2插入計算項441

18.10使用切片器篩選數據

透視表443

18.11使用日程表篩選數據

透視表444

18.12引用數據透視表中的

單元格446

18.13另一個數據透視表示例447

18.14使用數據模型450

18.15創建數據透視圖453

18.15.1數據透視圖示例453

18.15.2關于數據透視圖的

更多內容455

第19章條件格式457

19.1條件格式簡介457

19.2指定條件格式459

19.2.1可以應用的格式類型459

19.2.2定義自己的規則460

19.3使用圖形的條件格式461

19.3.1使用數據條461

19.3.2使用色階462

19.3.3使用圖標集465

19.4創建基于公式的規則468

19.4.1理解相對和絕對引用469 2100433B

內容簡介

《Excel公式與函數實例應用大全》是一本專門針對Excel函數應用的圖書，在講解每一個函數的格式、參數及使用方法后，都以一個實例詳細演示該函數的應用，讓讀者不但知道函數的用途、參數及格式，而且能融會貫通地將這些函數應用到日常工作中，從而提高效率。 《Excel公式與函數實例應用大全》圖文并茂、實例豐富，主要內容包括：Excel基本操作、Excel公式使用、函數應用基礎、文本函數、時間/日期函數、查詢與引用函數、財務函數、數學和三角函數、統計函數、工程函數、數據庫函數、邏輯函數。 《Excel公式與函數實例應用大全》適合于對Excel應用有較高要求的用戶，也可以作為一本Excel函數備查手冊，以備不時之需。2100433B