中文版Excel 2016公式與函式套用寶典（第7版）

基本介紹

• 書名：中文版Excel 2016公式與函式套用寶典
• 作者：【美】 Michael Alexander, Dick Kusleika
• 譯者：陳姣 李恒基
• 定價：128元
• 出版時間：2017.03.01
• 印次：1-1
• 印刷日期：2017.02.21

基本信息

作者：[美] Michael Alexander, Dick Kusleika 著 陳姣 李恒基 譯
定價：128元
印次：1-1
ISBN：9787302460978
出版日期：2017.03.01
印刷日期：2017.02.21

圖書簡介

領悟專家技巧，釋放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