內容簡介

本書是“新起點電腦教程”系列叢書的一個分冊，以通俗易懂的語言、精挑細選的實用技巧和翔實生動的操作案例，全面介紹了Excel 2013公式、函數、圖表與數據分析的基礎知識及應用案例，主要內容包括Excel快速入門與應用、Excel公式與函數基礎知識、公式審核與錯誤處理、文本與邏輯函數、日期與時間函數、數學與三角函數、財務函數、統計函數、查找與引用函數、數據庫函數、圖表入門與應用、數據處理與分析，以及使用數據透視表和數據透視圖等方面的知識、技巧及應用案例。

本書配套一張多媒體全景教學光盤，收錄了書中全部知識點的視頻教學課程，同時還贈送了4套相關視頻教學課程，以及多本電子圖書，可以幫助讀者循序漸進地學習、掌握和提高。

本書可作為有一定Excel基礎操作知識的讀者學習公式、函數、圖表與數據分析的參考用書，也可以作為函數速查工具手冊，又可以作為豐富的函數應用案例寶典，適合廣大電腦愛好者及各行各業人員作為學習Excel的自學手冊使用，同時還可以作為初、中級電腦培訓班的電腦課堂教材。

 

　　

前    言

　　

　　Excel 2013是Office 2013中最重要的家族成員之一，它比以往的老版本功能更強大、更具人性化、設計更專業、使用更方便，被廣泛地應用于數據管理、財務統計、金融等領域。為了幫助初學者快速學習與應用Excel 2013軟件，以便在日常的學習和工作中學以致用，我們編寫了本書。

　　本書根據初學者的學習習慣，采用由淺入深的方式講解，通過大量的實際案例，全面介紹了Excel 2013的公式、函數、圖表和數據分析的功能、應用，以及一些實用的操作技巧，讀者還可通過隨書贈送的多媒體教學視頻進行學習。全書結構清晰，內容豐富，共分為13章，主要包括以下5個部分。

1. Excel 2013快速入門與應用 

　　本書第1章，介紹了Excel 2013快速入門與應用的相關知識，包括Excel的啟動與退出、工作簿的基本操作、工作表的基本操作、單元格的基本操作和格式化工作表方面的內容。

2. Excel公式與函數基礎知識

　　本書第2章，介紹了Excel公式與函數基礎知識，包括認識公式與函數、單元格引用、公式中的運算符及其優先級、輸入與編輯公式、函數的結構和種類、輸入函數的方法，以及定義和使用名稱等方面的相關知識及操作方法。

3. 公式審核與錯誤處理

　　本書第3章，介紹了公式審核與錯誤處理的方法，包括審核公式、公式返回錯誤及解決方法和處理公式中常見錯誤的操作方法。

4. Excel函數的應用舉例

　　本書第4～10章，介紹了Excel各類函數的應用舉例，包括文本與邏輯函數、日期與時間函數、數學與三角函數、財務函數、統計函數、查找與引用函數和數據庫函數的相關知識及應用舉例。

5. 圖表與數據分析

　　本書第11～13章，介紹了圖表與數據分析的相關知識，包括圖表入門與應用、數據處理與分析，以及使用數據透視表和數據透視圖等方面的知識、技巧及應用案例。

　　本書由文杰書院編著，參與本書編寫工作的有李軍、袁帥、文雪、肖微微、李強、高桂華、藺丹、張艷玲、李統財、安國英、賈亞軍、藺影、李偉、馮臣、宋艷輝等。

　　我們真切希望讀者在閱讀本書之后，可以開闊視野、增長實踐操作技能，并從中學習和總結操作的經驗和規律，靈活運用。鑒于編者水平有限，書中紕漏和考慮不周之處在所難免，熱忱歡迎讀者予以批評、指正，以便我們日后能為您編寫出更好的圖書。

　　　

 

　　編  者  

目    錄

 

第1章  Excel快速入門與應用 1

1.1  Excel的啟動與退出 2

1.1.1  啟動Excel 2

1.1.2  熟悉Excel 2013工作界面 3

1.1.3  退出Excel 7

1.2  工作簿的基本操作 8

1.2.1  創建新工作簿 8

1.2.2  開始輸入數據 10

1.2.3  保存和關閉工作簿 12

1.2.4  打開保存的工作簿 15

1.2.5  保護工作簿 15

1.3  工作表的基本操作 16

1.3.1  選取工作表 17

1.3.2  重命名工作表 19

1.3.3  添加與刪除工作表 20

1.3.4  復制與移動工作表 21

1.3.5  保護工作表 23

1.4  單元格的基本操作 24

1.4.1  選取單元格 25

1.4.2  插入單元格 25

1.4.3  刪除單元格 29

1.4.4  復制與移動單元格 33

1.5  格式化工作表 34

1.5.1  調整表格的行高與列寬 34

1.5.2  設置字體格式 36

1.5.3  設置對齊方式 37

1.5.4  添加表格邊框 38

1.5.5  自動套用表格格式 39

1.6  實踐案例與上機指導 40

1.6.1  隱藏與顯示工作表 40

1.6.2  合并單元格 42

1.6.3  更改工作表標簽顏色 43

1.6.4  設置背景 44

1.7  思考與練習 45

第2章  Excel公式與函數基礎知識 47

2.1  認識公式與函數 48

2.1.1  什么是公式 48

2.1.2  什么是函數 48

2.2  單元格引用 49

2.2.1  相對引用 49

2.2.2  絕對引用 50

2.2.3  混合引用 51

2.2.4  改變引用類型 53

2.3  公式中的運算符及其優先級 54

2.3.1  算術運算符 54

2.3.2  比較運算符 55

2.3.3  引用運算符 55

2.3.4  文本運算符 55

2.3.5  運算符的優先級順序 55

2.4  輸入與編輯公式 56

2.4.1  輸入公式 56

2.4.2  修改公式 58

2.4.3  復制與移動公式 59

2.4.4  隱藏公式 61

2.4.5  刪除公式 63

2.5  函數的結構和種類 65

2.5.1  函數的結構 65

2.5.2  函數的種類 65

2.6  輸入函數的方法 67

2.6.1  直接輸入函數 67

2.6.2  通過【插入函數】對話框輸入

函數 68

2.7  定義和使用名稱 70

2.7.1  定義名稱 70

2.7.2  根據所選內容一次性創建多個

名稱 73

2.7.3  讓定義的名稱只應用于當前

工作表 74

2.8  實踐案例與上機指導 75

2.8.1  將公式定義為名稱 75

2.8.2  使用【函數庫】組中的功能

按鈕插入函數 77

2.8.3  將公式結果轉換為數值 78

2.9  思考與練習 80

第3章  公式審核與錯誤處理 83

3.1  審核公式 84

3.1.1  使用公式錯誤檢查功能 84

3.1.2  添加追蹤箭頭追蹤引用和從屬

單元格 85

3.1.3  監視單元格內容 87

3.1.4  定位特定類型的數據 88

3.1.5  使用公式求值功能 89

3.2  公式返回錯誤及解決方法 91

3.2.1  "#DIV/0!"錯誤及解決方法 91

3.2.2  "#N/A"錯誤及解決方法 92

3.2.3  "#NAME?"錯誤及解決方法 92

3.2.4  "#NULL!"錯誤及解決方法 93

3.2.5  "#NUM!"錯誤及解決方法 94

3.2.6  "#REF!"錯誤及解決方法 94

3.2.7  "#VALUE!"錯誤及解決

方法 95

3.2.8  "#####"錯誤及解決方法 95

3.3  處理公式中常見的錯誤 96

3.3.1  括號不匹配 96

3.3.2  循環引用 97

3.3.3  空白但非空的單元格 98

3.3.4  顯示值與實際值 98

3.4  實踐案例與上機指導 99

3.4.1  在工作表中顯示公式而非計算

結果 99

3.4.2  在多個單元格中輸入同一個

公式 100

3.4.3  查看長公式中的某一步計算

結果 101

3.4.4  自動求和 102

3.5  思考與練習 102

第4章  文本與邏輯函數 105

4.1  文本函數 106

4.1.1  什么是文本函數 106

4.1.2  認識文本數據 106

4.1.3  區分文本與數值 106

4.1.4  文本函數介紹 107

4.2  文本函數應用舉例 108

4.2.1  使用ASC函數將全角字符轉換

為半角字符 108

4.2.2  使用CONCATENATE函數

自動提取序號 108

4.2.3  使用CLEAN函數清理非打印

字符 109

4.2.4  使用CHAR函數返回對應于

數字代碼的字符 110

4.2.5  使用DOLLAR函數轉換貨幣

格式 111

4.2.6  使用EXACT函數比較產品

編號是否相同 112

4.2.7  使用LEN函數檢查身份證位數

是否正確 112

4.2.8  使用LOWER函數將文本轉換

為小寫 113

4.2.9  使用REPLACE函數為電話

號碼升級 114

4.2.10  使用PROPER函數將每個

單詞的首字母轉換為大寫 115

4.3  邏輯函數 115

4.3.1  什么是邏輯函數 115

4.3.2  邏輯函數介紹 116

4.4  邏輯函數應用舉例 116

4.4.1  使用AND函數檢測產品是否

合格 116

4.4.2  使用FALSE函數判斷兩列

數據是否相等 117

4.4.3  使用IF函數標注不及格

考生 118

4.4.4  使用IFERROR函數檢查數據

的正確性 119

4.4.5  使用NOT函數進行篩選 120

4.4.6  使用OR函數判斷員工考核

是否達標 120

4.4.7  使用TRUE函數判斷兩列數據

是否相同 121

4.5  實踐案例與上機指導 122

4.5.1  使用IF和NOT函數選擇面試

人員 122

4.5.2  使用IF和OR函數對產品進行

分類 122

4.5.3  根據年齡判斷職工是否

退休 123

4.5.4  比對文本 124

4.6  思考與練習 124

第5章  日期與時間函數 127

5.1  日期函數 128

5.1.1  Excel提供的兩種日期系統 128

5.1.2  日期序列號和時間序列號 128

5.1.3  常用的日期函數 129

5.2  日期函數應用舉例 129

5.2.1  使用DATE函數計算已知

第幾天對應的準確日期 129

5.2.2  使用DATEVALUE函數計算

完成計劃內容所需天數 130

5.2.3  使用DAYS360函數計算兩個

日期間的天數 131

5.2.4  使用DAY函數計算指定日期

對應的天數 132

5.2.5  使用EOMONTH函數計算

指定日期到月底的天數 133

5.2.6  使用MONTH函數計算指定

日期的月份 135

5.2.7  使用NOW函數得到當前的

日期和時間 135

5.2.8  使用TODAY函數推算春節

倒計時 136

5.2.9  使用WEEKDAY函數返回

指定日期的星期值 136

5.2.10  使用WORKDAY函數計算

項目完成日期 138

5.3  時間函數 138

5.3.1  時間的加減運算 138

5.3.2  時間的取舍運算 139

5.3.3  常用的時間函數 139

5.4  時間函數應用舉例 139

5.4.1  使用HOUR函數計算電影播放

時長 139

5.4.2  使用MINUTE函數精確到分鐘

計算工作時間 140

5.4.3  使用SECOND函數計算選手

之間相差的秒數 141

5.4.4  使用TIME函數計算比賽到達

終點的時間 142

5.4.5  使用TIMEVALUE函數計算

口語測試時間 143

5.5  實踐案例與上機指導 144

5.5.1  計算員工轉正時間 144

5.5.2  計算員工的應付工資 145

5.5.3  計算應付賬款的還款天數 146

5.5.4  根據出生日期快速計算

年齡 146

5.6  思考與練習 147

第6章  數學與三角函數 149

6.1  常用的數學與三角函數 150

6.2  常規計算 152

6.2.1  使用ABS函數計算兩地的

溫差 152

6.2.2  使用MOD函數計算庫存

結余 152

6.2.3  使用SUM函數計算學生總分

成績 153

6.2.4  使用SUMIF函數統計指定

商品的銷售數量 154

6.2.5  使用SUMIFS函數統計某日期

區間的銷售金額 155

6.3  舍入計算 155

6.3.1  使用CEILING函數計算通話

費用 156

6.3.2  使用FLOOR函數計算員工的

提成獎金 156

6.3.3  使用INT函數對平均銷量

取整 157

6.3.4  使用ROUND函數將數字按

指定位數舍入 158

6.3.5  使用ROUNDUP函數計算人均

銷售額 158

6.4  階乘與隨機數 159

6.4.1  使用COMBIN函數確定所有

可能的組合數目 159

6.4.2  使用FACT函數計算數字的

階乘 160

6.4.3  使用MULTINOMIAL函數

解決分組問題 161

6.4.4  使用RAND函數隨機創建彩票

號碼 161

6.4.5  使用SUMPRODUCT函數計算

參保人數 162

6.5  指數與對數 163

6.5.1  使用EXP函數返回e的

n次方 163

6.5.2  使用LN函數計算均衡

修正項 163

6.5.3  使用LOG函數計算無噪信道

傳輸能力 164

6.5.4  使用LOG10函數計算

分貝數 165

6.5.5  使用POWER函數計算數字的

乘冪 165

6.6  三角函數計算 166

6.6.1  使用ACOS函數計算

反余弦值 166

6.6.2  使用ATAN2函數計算射擊

目標的方位角 167

6.6.3  使用ATANH函數計算反雙曲

正切值 168

6.6.4  使用ASIN函數計算數字的

反正弦值 168

6.6.5  使用COS函數計算直角三角形

中銳角的鄰邊長度 169

6.6.6  使用DEGREES函數計算扇形

運動場角度 170

6.6.7  使用RADIANS函數計算

弧長 170

6.6.8  使用SIN函數計算指定角度的

正弦值 171

6.6.9  使用TAN函數計算給定角度

的正切值 172

6.6.10  使用TANH函數計算雙曲

 正切值 172

6.7  其他數學與三角函數 173

6.7.1  使用PI函數計算圓周長 173

6.7.2  使用ROMAN函數將阿拉伯

數字轉為羅馬數字 174

6.7.3  使用SUBTOTAL函數匯總

員工工資情況 175

6.8  實踐案例與上機指導 176

6.8.1  使用MROUND函數計算

車次 177

6.8.2  計算四項比賽對局總次數 177

6.8.3  隨機抽取中獎號碼 178

6.9  思考與練習 178

第7章  財務函數 181

7.1  常用的財務函數 182

7.2  折舊值計算函數 183

7.2.1  使用AMORDEGRC函數計算

第一時期的折舊值 184

7.2.2  使用AMORLINC函數計算

第一時期的折舊值 185

7.2.3  使用DB函數計算每年的

折舊值 186

7.2.4  使用SLN函數計算線性

折舊值 187

7.2.5  使用SYD函數按年限總和

折舊法計算折舊值 188

7.2.6  使用VDB函數按余額遞減法

計算房屋折舊值 188

7.3  投資計算函數 189

7.3.1  使用FV函數計算存款加

利息數 190

7.3.2  使用NPER函數計算存款

達到1萬元需要幾個月 190

7.3.3  使用NPV函數計算某項投資

的凈現值 191

7.3.4  使用PV函數計算貸款買車的

貸款額 192

7.3.5  使用XNPV函數計算未必定期

發生的投資凈現值 193

7.4  本金與利息函數 194

7.4.1  使用EFFECT函數將名義

年利率轉換為實際年利率 194

7.4.2  使用IPMT函數計算在給定

期間內的支付利息 194

7.4.3  使用NOMINAL函數計算某

債券的名義年利率 195

7.4.4  使用PMT函數計算貸款的

每月分期付款額 196

7.4.5  使用RATE函數計算貸款

年利率 197

7.5  收益率函數 198

7.5.1  使用IRR函數計算某項投資的

內部收益率 198

7.5.2  使用MIRR函數計算某項投資

的修正內部收益率 199

7.5.3  使用XIRR函數計算未必定期

發生的現金流的內部收益率 199

7.6  債券與證券函數 200

7.6.1  使用ACCRINT函數計算定期

付息債券應計利息 200

7.6.2  使用COUPDAYBS函數計算

當前付息期內截止到結算日的

天數 202

7.6.3  使用DISC函數計算有價證券

的貼現率 202

7.6.4  使用INTRATE函數計算

一次性付息證券的利率 203

7.6.5  使用YIELD函數計算有價

證券的收益率 204

7.7  實踐案例與上機指導 205

7.7.1  計算汽車的折舊值 205

7.7.2  計算貸款每期返還的本金

金額 206

7.7.3  計算投資期內所支付的

利息 206

7.8  思考與練習 207

第8章  統計函數 209

8.1  常用的統計函數 210

8.2  平均值函數 211

8.2.1  使用AVERAGE函數計算人均

銷售額 211

8.2.2  使用AVERAGEA函數求包含

文本值的平均值 211

8.2.3  使用AVERAGEIF函數求

每季度平均支出金額 212

8.2.4  使用AVERAGEIFS函數計算

滿足多重條件的數據的

平均值 213

8.2.5  使用GEOMEAN函數計算

銷售量的幾何平均值 214

8.2.6  使用TRIMMEAN函數進行

評分統計 215

8.2.7  使用MEDIAN函數計算中間

成績 215

8.3  數理統計函數 216

8.3.1  使用FORECAST函數預測

未來指定日期的天氣 216

8.3.2  使用FREQUENCY函數統計

每個分數段的人員個數 217

8.3.3  使用GROWTH函數預測

下一年的銷量 218

8.3.4  使用MODE.SNGL函數統計

配套生產最佳產量值 219

8.4  條目統計函數 220

8.4.1  使用COUNT函數統計生產

車間異常機臺個數 220

8.4.2  使用COUNTA函數統計出勤

異常人數 221

8.4.3  使用COUNTBLANK函數統計

未檢驗完成的產品數 222

8.4.4  使用COUNTIF函數統計不及格

的學生人數 223

8.4.5  使用COUNTIFS函數統計A班

成績優秀的學生數 223

8.5  最大值與最小值函數 224

8.5.1  使用LARGE函數提取銷售

季軍的銷售額 224

8.5.2  使用MAX函數統計銷售額

中的最大值 225

8.5.3  使用MAXA函數計算已上

報銷售額中的最大值 226

8.5.4  使用MIN函數統計銷售額中的

最小值 227

8.5.5  使用SMALL函數提取最后

一名的銷售額 227

8.6  實踐案例與上機指導 228

8.6.1  根據總成績對考生進行

排名 228

8.6.2  計算單日最高銷售金額 229

8.6.3  計算平均成績(忽略缺考

人員) 230

8.7  思考與練習 231

第9章  查找與引用函數 233

9.1  查找與引用函數概述 234

9.2  普通查詢 234

9.2.1  使用CHOOSE函數標注熱銷

產品 234

9.2.2  使用HLOOKUP函數查找

某業務員在某季度的銷量 235

9.2.3  使用LOOKUP函數查找信息

(向量型) 237

9.2.4  使用LOOKUP函數查找信息

(數組型) 238

9.2.5  使用VLOOKUP函數對崗位

考核成績進行評定 239

9.3  引用查詢 240

9.3.1  使用INDEX函數快速提取

員工編號 240

9.3.2  使用MATCH函數不區分

大小寫提取成績 241

9.3.3  使用OFFSET函數根據指定

姓名和科目查詢成績 242

9.3.4  使用TRANSPOSE函數轉換

數據區域 243

9.4  引用表中數據 244

9.4.1  使用ADDRESS函數定位年會

抽獎號碼位置 244

9.4.2  使用AREAS函數統計選手

組別數量 246

9.4.3  使用COLUMNS函數統計公司

的部門數量 246

9.4.4  使用HYPERLINK函數添加

客戶的電子郵件地址 247

9.4.5  使用ROW函數快速輸入

12個月份 248

9.5  實踐案例與上機指導 249

9.5.1  進、出庫合計查詢 249

9.5.2  根據產量計算員工產量

得分 250

9.5.3  通過差旅費報銷明細統計出差

人數 251

9.6  思考與練習 251

第10章  數據庫函數 253

10.1  數據庫函數概述 254

10.2  計算數據庫中的數據 254

10.2.1  使用DPRODUCT函數統計

 手機的返修記錄 254

10.2.2  使用DSUM函數統計符合

 條件的銷售額總和 255

10.3  數據庫常規統計 256

10.3.1  使用DAVERAGE函數統計

 符合條件的銷售額平均值 256

10.3.2  使用DCOUNT函數統計銷售

 精英人數 257

10.3.3  使用DCOUNTA函數計算

 公司使用手機的人數 258

10.3.4  使用DGET函數提取指定

 條件的銷售額 259

10.3.5  使用DMAX函數提取銷售員

 的最高銷售額 260

10.4  數據庫散布度統計 260

10.4.1  使用DSTDEV函數計算員工

 的年齡標準差 261

10.4.2  使用DSTDEVP函數計算

 員工的總體年齡標準差 261

10.4.3  使用DVAR函數計算男員工

 銷售量的方差 262

10.4.4  使用DVARP函數計算男員工

 銷售量的總體方差 263

10.5  實踐案例與上機指導 264

10.5.1  統計文具類和廚具類產品的

 最低單價 264

10.5.2  計算公司員工銷售額 265

10.5.3  計算語文成績大于90分者的

 最高總成績 266

10.6  思考與練習 266

第11章  圖表入門與應用 269

11.1  認識圖表 270

11.1.1  圖表的類型 270

11.1.2  圖表的組成 273

11.2  創建圖表的方法 274

11.2.1  通過對話框創建圖表 274

11.2.2  使用功能區創建圖表 275

11.2.3  使用快捷鍵創建圖表 276

11.3  設置圖表 277

11.3.1  更改圖表類型 278

11.3.2  更改數據源 279

11.3.3  設計圖表布局 280

11.3.4  設計圖表樣式 281

11.4  創建各種類型的圖表 281

11.4.1  使用折線圖顯示產品銷量 281

11.4.2  使用餅圖顯示人口比例 282

11.4.3  使用柱形圖顯示學生成績

 差距 282

11.4.4  使用XY散點圖顯示人口

 分布 283

11.5  美化圖表 284

11.5.1  設置圖表標題 284

11.5.2  設置圖表背景 284

11.5.3  設置圖例 286

11.5.4  設置數據標簽 287

11.5.5  設置坐標軸標題 287

11.5.6  設置網格線 288

11.6  實踐案例與上機指導 289

11.6.1  為圖表添加趨勢線 289

11.6.2  添加數據系列 290

11.6.3  插入迷你圖 293

11.7  思考與練習 295

第12章  數據處理與分析 297

12.1  數據的篩選 298

12.1.1  自動篩選 298

12.1.2  高級篩選 299

12.2  數據的排序 302

12.2.1  單條件排序 302

12.2.2  多條件排序 303

12.2.3  按行排序 304

12.2.4  按筆畫排序 305

12.3  數據的分類匯總 306

12.3.1  簡單分類匯總 306

12.3.2  刪除分類匯總 308

12.4  合并計算 309

12.4.1  按位置合并計算 309

12.4.2  按類別合并計算 311

12.5  分級顯示數據 313

12.5.1  新建分級顯示 313

12.5.2  隱藏與顯示明細數據 314

12.6  實踐案例與上機指導 315

12.6.1  使用通配符進行模糊篩選 315

12.6.2  按顏色排序 316

12.6.3  取消和替換當前的分類

 匯總 317

12.6.4  刪除重復數據 318

12.7  思考與練習 320

第13章  使用數據透視表和數據

 透視圖 321

13.1  認識數據透視表與數據透視圖 322

13.1.1  認識數據透視表 322

13.1.2  認識數據透視圖 322

13.2  創建與編輯數據透視表 323

13.2.1  創建數據透視表 323

13.2.2  設置數據透視表字段 324

13.2.3  刪除數據透視表 325

13.3  操作數據透視表中的數據 326

13.3.1  刷新數據透視表 326

13.3.2  數據透視表的排序 326

13.3.3  篩選數據透視表中的數據 327

13.4  美化數據透視表 328

13.4.1  更改數據透視表布局 328

13.4.2  應用數據透視表樣式 329

13.5  創建與操作數據透視圖 330

13.5.1  使用數據區域創建數據

 透視圖 330

13.5.2  更改數據透視圖類型 332

13.5.3  篩選數據 333

13.5.4  分析數據透視圖 333

13.6  實踐案例與上機指導 335

13.6.1  使用數據透視表創建數據

 透視圖 335

13.6.2  設置數據透視表的顯示

 方式 336

13.6.3  對數據透視表中的項目進行

 組合 337

13.6.4  清除數據透視圖 338

13.7  思考與練習 339

思考與練習答案 340