高效隨身查：Excel函數(shù)與公式應(yīng)用技巧（2016版）

第1章　函數(shù)與公式 1

1.1　公式的輸入與編輯 1

技巧1　不會函數(shù)也能快速求和（或平均值） 1

技巧2　公式函數(shù)不分家 2

技巧3　啟用“插入函數(shù)”對話框編輯函數(shù) 3

技巧4　手寫編輯公式 7

技巧5　公式的修改 7

技巧6　不記得函數(shù)全稱也能正確輸入函數(shù) 8

技巧7　快速查找和學(xué)習(xí)某函數(shù)用法 9

技巧8　使用批量計算 11

技巧9　大范圍公式復(fù)制的方法 11

技巧10　跳過非空單元格批量建立公式 12

技巧11　數(shù)組公式 14

技巧12　為什么數(shù)字與“空”單元格相加出錯 15

技巧13　為什么明明顯示的是數(shù)據(jù)，計算結(jié)果卻為0 16

技巧14　暫時保留沒有輸入完整的公式 17

技巧15　將公式運算結(jié)果轉(zhuǎn)換為數(shù)值 18

技巧16　隱藏公式實現(xiàn)保護 19

1.2　公式中數(shù)據(jù)源的引用 20

技巧17　相對引用數(shù)據(jù)源計算 20

技巧18　*引用數(shù)據(jù)源計算 21

技巧19　引用當(dāng)前工作表之外的單元格 22

技巧20　引用多工作表的同一單元格計算 24

技巧21　引用其他工作簿中的數(shù)據(jù)源 25

技巧22　用“F4”鍵快速改變數(shù)據(jù)源引用類型 27

技巧23　為什么要定義名稱 27

技巧24　快速定義名稱的方法有哪些 28

技巧25　引用定義的名稱創(chuàng)建公式 29

技巧26　將公式定義為名稱 30

技巧27　將表格創(chuàng)建為動態(tài)名稱實現(xiàn)數(shù)據(jù)計算即時更新 31

技巧28　重新修改名稱的引用位置 32

1.3　學(xué)會分解、理解公式 33

技巧29　查看長公式中某一步的計算結(jié)果 33

技巧30　追蹤公式引用的單元格 34

技巧31　通過“公式求值”功能逐步分解公式 35

技巧32　在單元格中顯示所有公式 36

第2章 邏輯函數(shù)范例 38

1. AND函數(shù)（檢驗一組數(shù)據(jù)是否都滿足條件） 38

技巧33　考評學(xué)生的各門課程是否全部及格 38

技巧34　一次性判斷一組數(shù)據(jù)是否都滿足條件 39

2. NOT函數(shù)（對所給參數(shù)求反） 40

技巧35　篩選掉小于某一年齡的應(yīng)聘人員 40

3. OR函數(shù)（檢驗一組數(shù)據(jù)是否有一個滿足條件） 41

技巧36　檢驗員工是否通過考評 41

4. IF函數(shù)（根據(jù)條件判斷真假） 42

技巧37　利用IF函數(shù)進行金額校驗 43

技巧38　根據(jù)不同返利比計算返利金額 43

技巧39　根據(jù)業(yè)務(wù)處理量判斷員工業(yè)務(wù)水平 44

技巧40　分性別判斷成績是否合格 45

技巧41　根據(jù)消費卡類別與消費額派發(fā)贈品 47

技巧42　根據(jù)職工性別和職務(wù)判斷退休年齡 48

技巧43　根據(jù)年齡與學(xué)歷對應(yīng)聘人員二次篩選 49

技巧44　根據(jù)商品的名稱與顏色進行一次性調(diào)價 50

技巧45　根據(jù)3項業(yè)務(wù)的完成率計算綜合完成率 52

技巧46　比較兩個采購部門的采購價格 53

技巧47　計算個人所得稅 54

5. IFERROR函數(shù)（根據(jù)條件判斷真假） 56

技巧48　當(dāng)被除數(shù)為空值（或0值）時返回“計算數(shù)據(jù)源有錯誤”文字 56

第3章　文本函數(shù)范例 58

3.1　提取文本 58

1. LEFT函數(shù)（從*左側(cè)開始提取指定數(shù)目的字符） 58

技巧49　從E-mail地址中提取賬號 58

技巧50　從商品全稱中提取產(chǎn)地信息 59

2. LEFTB函數(shù)（按字節(jié)數(shù)從*左側(cè)提取指定數(shù)目的字符） 60

技巧51　根據(jù)產(chǎn)品編號提取類別 60

3. RIGHT函數(shù)（從*右側(cè)開始提取指定數(shù)目的字符） 61

技巧52　將電話號碼的區(qū)號與號碼分離 62

技巧53　從右側(cè)提取字符并自動轉(zhuǎn)換為數(shù)值 63

技巧54　發(fā)票金額的分列填寫 64

4. RIGHTB函數(shù)（按字節(jié)數(shù)從*右側(cè)提取指定數(shù)目的字符） 67

技巧55　提取產(chǎn)品的規(guī)格數(shù)據(jù) 67

5. MID函數(shù)（從指定位置開始提取字符） 68

技巧56　從規(guī)格數(shù)據(jù)中提取部分數(shù)據(jù) 69

技巧57　提取產(chǎn)品的貨號 69

6. MIDB函數(shù)（按字節(jié)數(shù)從指定位置開始提取字符） 70

技巧58　從房號數(shù)據(jù)中提取單元號 71

7. CONCATENATE函數(shù)（合并多個字符） 72

技巧59　合并商品貨號碼數(shù)及顏色 72

技巧60　提取任意有用數(shù)據(jù)并合并 73

3.2　查找與替換文本 74

8. FIND函數(shù)（查找指定字符并返回其位置） 74

技巧61　查找品名所在位置并提取 74

技巧62　問卷調(diào)查時實現(xiàn)自動統(tǒng)計答案 75

9. REPLACE函數(shù)（替換字符中的部分字符） 77

技巧63　屏蔽中獎手機號碼的后幾位數(shù) 77

10. SEARCH函數(shù)（查找字符并返回其起始位置） 78

技巧64　從項目編碼中提取合同號 79

11. SUBSTITUTE函數(shù)（用新字符替換舊字符） 80

技巧65　去掉報表中多余的空格 80

技巧66　查找特定文本且將*次出現(xiàn)的刪除，其他保留 81

技巧67　嵌套使用SUBSTITUTE函數(shù)返回公司名稱簡稱 81

技巧68　根據(jù)報名學(xué)員統(tǒng)計人數(shù) 83

3.3　轉(zhuǎn)換文本格式 84

12. FIXED函數(shù)（按指定的小數(shù)位數(shù)進行取整） 84

技巧69　消除因四舍五入而造成的顯示誤差 84

13. WIDECHAR函數(shù)（半角字母轉(zhuǎn)換為全角字母） 85

技巧70　將半角英文字母轉(zhuǎn)換為全角英文字母 85

14. UPPER(將文本轉(zhuǎn)換為大寫形式) 85

技巧71　將小寫英文文本一次性變更為大寫 86

15. LOWER函數(shù)（大寫字母轉(zhuǎn)換為小寫字母） 86

技巧72　將大寫字母轉(zhuǎn)換為小寫字母 86

16. PROPER函數(shù)（將首字符轉(zhuǎn)換為大寫） 87

技巧73　一次性將每個單詞的首字母轉(zhuǎn)換為大寫 87

17. RMB函數(shù)（四舍五入數(shù)值，并添加千分位符號和￥符號） 88

技巧74　將銷售額一次性轉(zhuǎn)換為人民幣格式 88

18. DOLLAR函數(shù)（四舍五入數(shù)值，并添加千分位符號和$符號） 89

技巧75　將銷售金額一次性轉(zhuǎn)換為美元貨幣格式 89

19. TEXT函數(shù)（將數(shù)值轉(zhuǎn)換為指定格式的文本） 90

技巧76　讓計算得到金額顯示為特定形式 90

技巧77　返回值班日期對應(yīng)在的星期數(shù) 91

技巧78　按上下班時間計算加班時長并顯示為“*時*分”形式 91

技巧79　解決日期計算返回日期序列號問題 93

技巧80　讓數(shù)據(jù)統(tǒng)一顯示固定的位數(shù) 94

技巧81　讓合并的日期顯示正確格式 95

20. VALUE函數(shù)（將文本型數(shù)字轉(zhuǎn)換成數(shù)值型數(shù)字） 96

技巧82　解決總金額無法計算的問題 96

3.4　其他文本函數(shù) 97

21. LEN函數(shù)（返回字符串的字符數(shù)） 97

技巧83　從身份證號碼中提取出生年份 97

技巧84　從身份證號碼中提取性別 98

技巧85　配合多函數(shù)從身份證號碼中提取完整的出生年月日 99

22. REPT函數(shù)（重復(fù)文本） 101

技巧86　輸入身份證號碼填寫框 101

技巧87　根據(jù)銷售額用“★”評定等級 101

23. TRIM函數(shù)（清除空格） 102

技巧88　刪除文本單詞中多余的空格 103

24. EXACT函數(shù)（比較兩個字符串是否相同） 103

技巧89　比較兩個店鋪的平均售價是否相同 103

第4章　日期與時間函數(shù)范例 105

4.1　返回日期 105

1. NOW函數(shù)（返回當(dāng)前日期時間） 105

技巧90　計算員工在職天數(shù) 105

2. TODAY函數(shù)（返回當(dāng)前日期） 106

技巧91　判斷應(yīng)收賬款是否到期 106

3. DATE函數(shù)（返回指定日期的序列號） 107

技巧92　建立倒計時顯示牌 107

4. DAY函數(shù)（返回某日期的天數(shù)） 108

技巧93　顯示出全年中各月的天數(shù) 108

技巧94　計算本月上旬的銷售總額 109

5. YEAR函數(shù)（返回某日期中的年份） 110

技巧95　計算出員工年齡 110

技巧96　計算出員工工齡 111

6. WEEKDAY函數(shù)（返回某日期為星期幾） 113

技巧97　返回日期對應(yīng)的星期數(shù) 113

技巧98　判斷值班日期是平時加班還是雙休日加班 114

7. MONTH函數(shù)（返回某日期中的月份） 115

技巧99　自動填寫銷售報表中的月份 115

技巧100　計算本月賬款金額總計 116

8. EOMONTH函數(shù)（返回某個月份*后一天的序列號） 117

技巧101　根據(jù)活動開始日期計算各月活動天數(shù) 117

技巧102　根據(jù)當(dāng)前月份自動顯示本月所有日期序列 118

技巧103　自動返回各日期對應(yīng)的星期數(shù) 120

9. WEEKNUM函數(shù)（返回指定日期是第幾周） 121

技巧104　快速得知2016年中各節(jié)日在第幾周 121

4.2　日期計算 122

10. DATEDIF函數(shù)（計算兩個日期之間的年、月和天數(shù)） 122

技巧105　計算總借款天數(shù) 123

技巧106　計算借款至今日的時長 123

技巧107　根據(jù)員工工齡自動追加工齡工資 124

11. DAYS360函數(shù)（計算兩日期間相差的天數(shù)） 125

技巧108　計算還款剩余天數(shù) 125

技巧109　計算固定資產(chǎn)已使用月份 126

技巧110　利用DAYS360函數(shù)判斷借款是否逾期 127

12. YEARFRAC函數(shù)（計算兩日期間天數(shù)占全年天數(shù)的百分比） 128

技巧111　計算各地點工作時間占全年天數(shù)百分比 129

13. WORKDAY函數(shù)（計算工作日） 129

技巧112　根據(jù)休假天數(shù)自動顯示出休假結(jié)束日期 130

14. NETWORKDAYS函數(shù)（去除指定日期之后的工作日數(shù)） 130

技巧113　計算元旦到春節(jié)之間的工作日 131

技巧114　計算年假占全年工作日的百分比 131

15. EDATE函數(shù)（計算與指定日期相隔指定月份數(shù)的日期） 133

技巧115　計算應(yīng)收賬款的到期日期 133

4.3　時間函數(shù) 134

16. HOUR函數(shù)（返回某時間中的小時數(shù)） 134

技巧116　計算來訪簽到時間的區(qū)間 134

17. MINUTE函數(shù)（返回某時間中的分鐘數(shù)） 135

技巧117　計算停車時間 135

18. SECOND函數(shù)（返回某時間中的秒數(shù)） 136

技巧118　計算通話秒數(shù) 136

技巧119　顯示高于或低于標(biāo)準(zhǔn)時間的值 137

4.4　日期與時間轉(zhuǎn)換 138

19. DATEVALUE函數(shù)（將文本日期轉(zhuǎn)換為可識別的日期序列號） 138

技巧120　計算借款天數(shù) 139

20. TIMEVALUE函數(shù)（將時間轉(zhuǎn)換為對應(yīng)的小數(shù)值） 140

技巧121　將指定時間轉(zhuǎn)換為時間小數(shù)值 140

第5章 數(shù)學(xué)函數(shù)范例 141

5.1　常規(guī)數(shù)學(xué)計算函數(shù) 141

1. SUM函數(shù)（求和） 141

技巧122　一次性對多列數(shù)據(jù)求和 141

技巧123　根據(jù)銷售數(shù)量與單價計算總銷售額 142

技巧124　只計算某兩個產(chǎn)品的合計金額 143

技巧125　分奇偶行統(tǒng)計數(shù)據(jù) 144

2. SUMIF函數(shù)（按條件求和） 145

技巧126　按經(jīng)辦人計算銷售金額 145

技巧127　統(tǒng)計各部門工資總額 146

技巧128　分別統(tǒng)計前半個月與后半個月的銷售額 147

技巧129　用通配符對某一類數(shù)據(jù)求和 148

技巧130　計算銷售金額前3名合計值 149

3. SUMIFS函數(shù)（按多條件求和） 150

技巧131　滿足指定類別指定時間統(tǒng)計總銷售額 150

技巧132　多條件統(tǒng)計某一類數(shù)據(jù)總和 152

技巧133　按不同性質(zhì)統(tǒng)計應(yīng)收款 153

4. SUMPRODUCT函數(shù)（求多組數(shù)的乘積之和） 155

技巧134　統(tǒng)計銷售部女員工人數(shù) 155

技巧135　統(tǒng)計出指定班級中分數(shù)大于指定值的人數(shù) 156

技巧136　統(tǒng)計出指定部門獲取獎金的人數(shù)（去除空值） 157

技巧137　統(tǒng)計出指定部門、指定職務(wù)的員工人數(shù) 158

技巧138　統(tǒng)計非工作日銷售金額 159

技巧139　按月匯總出庫數(shù)量 160

技巧140　統(tǒng)計大于12個月的賬款 161

技巧141　統(tǒng)計出某兩種商品的銷售金額 162

技巧142　統(tǒng)計某一時間段出現(xiàn)的次數(shù) 163

技巧143　統(tǒng)計指定日期區(qū)間中指定性別的人數(shù) 164

技巧144　分單位統(tǒng)計賬齡 165

5. ABS函數(shù)（求*值） 168

技巧145　比較今年銷售額與去年銷售額 168

技巧146　計算支出金額總計值 169

6. MOD函數(shù)（求兩個數(shù)相除后的余數(shù)） 169

技巧147　按奇數(shù)月與偶數(shù)月統(tǒng)計銷量 170

技巧148　計算每位員工的加班時長 171

7. SQRT函數(shù)（求算術(shù)平方根） 172

技巧149　計算指定數(shù)值對應(yīng)的算術(shù)平方根 173

8. GCD函數(shù)（求*公約數(shù)） 173

技巧150　返回兩個或多個整數(shù)的*公約數(shù) 173

9. LCM函數(shù)（求*小公倍數(shù)） 174

技巧151　計算兩個或多個整數(shù)的*小公倍數(shù) 174

10. POWER函數(shù)（計算方根） 175

技巧152　根據(jù)指定的底數(shù)和指數(shù)計算出方根值 175

5.2　舍入計算函數(shù) 176

11. INT函數(shù)（向下取整） 176

技巧153　計算平均銷售數(shù)量時取整數(shù) 176

12. TRUNC函數(shù)（截去數(shù)據(jù)小數(shù)部分） 177

技巧154　計算銷售金額時取整或保留指定位數(shù)小數(shù) 177

13. CEILING函數(shù)（舍入計算） 179

技巧155　以6秒（不足6秒按6秒計算）為單位計算通話費用 179

14. ROUND函數(shù)（四舍五入） 180

技巧156　以一個百分點為單位計算獎金或扣款 181

15. ROUNDUP函數(shù)（向上舍入） 182

技巧157　使用ROUNDUP函數(shù)計算物品的快遞費用 182

技巧158　計算上網(wǎng)費用 183

16. ROUNDDOWN函數(shù)（向下舍入） 184

技巧159　根據(jù)給定時間界定整點范圍 185

17. FLOOR函數(shù)（去尾舍入） 186

技巧160　計件工資中的獎金計算 186

18. MROUND函數(shù)（按指定倍數(shù)舍入） 187

技巧161　計算商品運送車次 187

19. QUOTIENT函數(shù)（返回數(shù)值相除后的整數(shù)） 189

技巧162　計算參加某活動的每組人數(shù) 189

5.3　階乘、*數(shù)計算函數(shù) 189

20. FACT函數(shù)（求指定正數(shù)值的階乘） 189

技巧163　求指定正數(shù)值的階乘值 190

21. MULTINOMIAL函數(shù)（計算指定數(shù)值階乘與各數(shù)值階乘乘積的比值） 190

技巧164　計算指定數(shù)值和的階乘與各數(shù)值階乘乘積的比值 190

22. RAND函數(shù)（返回一個大于等于0小于1的*數(shù)） 191

技巧165　*獲取選手編號 192

技巧166　自動生成彩票7位開獎號碼 192

23. RANDBETWEEN函數(shù)（返回兩個數(shù)值間的*數(shù)） 193

技巧167　自動*生成3位數(shù)編碼 193

第6章　統(tǒng)計函數(shù)范例 194

6.1　平均值計算函數(shù) 194

1. AVERAGE函數(shù)（求平均值） 194

技巧168　快速自動求平均值 194

技巧169　在成績表中忽略0值求平均分 195

技巧170　計算指定學(xué)校學(xué)生的平均成績 196

2. AVERAGEA函數(shù)（求包括文本和邏輯值的平均值） 197

技巧171　計算平均分時將“缺考”的也計算在內(nèi) 197

技巧172　統(tǒng)計各月份的平均銷售額（計算區(qū)域含文本值） 198

3. AVERAGEIF函數(shù)（按條件求平均值） 199

技巧173　統(tǒng)計各班級平均分 199

技巧174　計算月平均出庫數(shù)量 200

技巧175　排除新店計算平均利潤 201

4. AVERAGEIFS函數(shù)（按多條件求平均值） 201

技巧176　計算一車間女職工平均工資 202

技巧177　求介于某一區(qū)間內(nèi)的平均值 203

技巧178　統(tǒng)計指定店面所有男裝品牌的平均利潤 203

技巧179　忽略0值求指定班級的平均分 204

5. GEOMEAN函數(shù)（求幾何平均值） 205

技巧180　比較兩種產(chǎn)品的銷售利潤的穩(wěn)定性 205

6. TRIMMEAN函數(shù)（去頭尾后求平均值） 206

技巧181　通過10位評委打分計算選手的*后得分 206

6.2　數(shù)目統(tǒng)計函數(shù) 207

7. COUNT函數(shù)（統(tǒng)計數(shù)目） 207

技巧182　統(tǒng)計出故障的機器臺數(shù) 207

技巧183　統(tǒng)計各個部門獲取交通補助的人數(shù) 208

技巧184　統(tǒng)計出指定學(xué)歷員工人數(shù) 209

技巧185　統(tǒng)計其中一科得滿分的人數(shù) 210

8. COUNTA函數(shù)（求包括文本和邏輯值的數(shù)目） 211

技巧186　統(tǒng)計參與某項課程的總?cè)藬?shù) 211

技巧187　統(tǒng)計非正常出勤的人數(shù) 211

9. COUNTIF函數(shù)（按條件統(tǒng)計數(shù)目） 212

技巧188　統(tǒng)計工資大于3000元的人數(shù) 212

技巧189　統(tǒng)計某課程的報名人數(shù) 213

技巧190　在成績表中分別統(tǒng)計及格人數(shù)與不及格人數(shù) 213

技巧191　統(tǒng)計出成績大于平均分數(shù)的學(xué)生人數(shù) 215

技巧192　統(tǒng)計是三好學(xué)生且參加數(shù)學(xué)競賽的人數(shù) 215

技巧193　統(tǒng)計連續(xù)3次考試都進入前10名的人數(shù) 216

10. COUNTIFS函數(shù)（按多條件統(tǒng)計數(shù)目） 217

技巧194　統(tǒng)計各店面男裝的銷售記錄條數(shù)（雙條件） 217

技巧195　統(tǒng)計指定時間指定類別商品的銷售記錄數(shù) 218

技巧196　統(tǒng)計指定產(chǎn)品每日的銷售記錄數(shù) 219

11. COUNTBLANK函數(shù)（統(tǒng)計空單元格的數(shù)目） 220

技巧197　統(tǒng)計缺考人數(shù) 221

6.3　*值與*小值統(tǒng)計函數(shù) 221

12. MAX函數(shù)（求*值） 221

技巧198　快速返回數(shù)據(jù)區(qū)域中的*值 221

技巧199　返回企業(yè)女性員工的*年齡 222

技巧200　返回上半個月單筆*銷售金額 223

技巧201　計算單日銷售金額并返回*值 224

13. MIN函數(shù)（求*小值） 225

技巧202　返回*銷售金額 225

技巧203　忽略0值求*分數(shù) 226

技巧204　返回多次短跑中用時*短的編號 227

14. MAXA函數(shù)（包含文本與邏輯值時求*值） 228

技巧205　返回成績表中的*分數(shù)（包含文本） 228

15. MINA函數(shù)（包含文本與邏輯值時求*小值） 229

技巧206　返回*利潤額（包含文本） 229

16. LARGE函數(shù)（返回某一數(shù)據(jù)集中的某個*值） 230

技巧207　返回排名前3位的銷售金額 230

技巧208　計算成績表中前5名的平均值 231

技巧209　分別統(tǒng)計各班級*名成績 231

17. SMALL函數(shù)（返回某一數(shù)據(jù)集中的某個*小值） 232

技巧210　統(tǒng)計成績表中后5名的平均分 232

技巧211　統(tǒng)計成績表中后5名的平均分（忽略0值） 233

18. MODE函數(shù) 234

技巧212　返回被投訴次數(shù)*多的客服的編號 234

6.4　排位統(tǒng)計函數(shù) 235

19. RANK.EQ函數(shù)（返回數(shù)字的排位） 235

技巧213　為學(xué)生考試成績排名次 235

技巧214　對不連續(xù)單元格排名次 236

20. RANK.AVG函數(shù)（排位有相同名次時返回平均排位） 236

技巧215　用RANK.AVG函數(shù)對銷售額排名 237

21. PERCENTILE.INC函數(shù)（返回數(shù)值指定百分點的值） 237

技巧216　返回數(shù)值區(qū)域的k指定百分比對應(yīng)的數(shù)值 238

22. PERCENTRANK.INC函數(shù)（返回數(shù)字的百分比排位） 239

技巧217　將各月銷售利潤按百分比排位 239

23. MEDIAN函數(shù)（返回數(shù)據(jù)集的中位數(shù)） 240

技巧218　統(tǒng)計全年各月利潤值中的中位數(shù) 241

24. QUARTILE.INC函數(shù)（返回數(shù)據(jù)集的四分位數(shù)） 241

技巧219　從全年各月銷售利潤數(shù)據(jù)集中求四分位數(shù) 242

第7章　財務(wù)函數(shù)范例 244

7.1　投資計算函數(shù) 244

1. PMT函數(shù)（計算貸款每期付款額） 244

技巧220　計算貸款的每年償還額 244

技巧221　計算貸款的每月償還額 245

2. IPMT函數(shù)（計算每期償還額中的利息額） 245

技巧222　計算貸款每年償還金額中有多少是利息 245

技巧223　計算貸款每月償還金額中有多少是利息 246

3. PPMT函數(shù)（計算本金） 247

技巧224　計算貸款每年償還金額中有多少是本金 247

技巧225　計算貸款每月償還金額中有多少是本金 248

4. ISPMT函數(shù)（等額本金還款方式下的利息計算） 249

技巧226　在等額本金還款方式下計算某貸款的利息 249

5. FV函數(shù)（計算投資未來值） 250

技巧227　計算分期存款的未來值 250

技巧228　計算購買某項保險的未來值 251

6. FVSCHEDULE函數(shù)（投資在變動或可調(diào)利率下的未來值） 251

技巧229　計算某項整存整取存款的未來值 251

7. PV函數(shù)（計算投資現(xiàn)值） 252

技巧230　判斷購買某項保險是否合算 252

8. NPV函數(shù)（計算投資凈現(xiàn)值） 253

技巧231　計算某投資的凈現(xiàn)值 253

9. XNPV函數(shù)（計算一組不定期現(xiàn)金流的凈現(xiàn)值） 255

技巧232　計算出一組不定期盈利額的凈現(xiàn)值 255

10. NPER函數(shù)（計算投資期數(shù)） 255

技巧233　計算出貸款的清還年數(shù) 256

技巧234　計算出某項投資的投資期數(shù) 256

7.2　償還率計算函數(shù) 257

11. IRR函數(shù)（計算一組現(xiàn)金流的內(nèi)部收益率） 257

技巧235　計算某項投資的內(nèi)部收益率 257

12. XIRR函數(shù)（計算一組不定期現(xiàn)金流的內(nèi)部收益率） 258

技巧236　計算出一組不定期盈利額的內(nèi)部收益率 258

13. MIRR函數(shù)（計算修正內(nèi)部收益率） 259

技巧237　計算某項投資的修正內(nèi)部收益率 259

14. RATE函數(shù)（計算年金每個期間的利率） 260

技巧238　計算購買某項保險的收益率 260

7.3　資產(chǎn)折舊計算函數(shù) 261

15. SLN函數(shù)（直線法） 261

技巧239　用直線法計算出固定資產(chǎn)的每年折舊額 261

技巧240　用直線法計算出固定資產(chǎn)的每月折舊額 261

16. SYD函數(shù)（年數(shù)總和法） 262

技巧241　用年數(shù)總和法計算出固定資產(chǎn)的每年折舊額 262

17. DB函數(shù)（固定余額遞減法） 263

技巧242　用固定余額遞減法計算出固定資產(chǎn)的每年折舊額 263

18. DDB函數(shù)（雙倍余額遞減法） 264

技巧243　用雙倍余額遞減法計算出固定資產(chǎn)的每年折舊額 264

19. VDB函數(shù)（計算部分期間內(nèi)的折舊值） 265

技巧244　計算出固定資產(chǎn)部分期間的設(shè)備折舊值 266

20. AMORDEGRC函數(shù)（指定會計期間的折舊值） 267

技巧245　計算指定會計期間的折舊值 268

第8章　查找和引用函數(shù)范例 269

8.1　查找函數(shù) 269

1. CHOOSE函數(shù)（從參數(shù)列表中選擇并返回一個值） 269

技巧246　配合IF函數(shù)找出短跑成績的前三名 269

技巧247　根據(jù)產(chǎn)品不合格率決定產(chǎn)品處理辦法 270

2. LOOKUP函數(shù)（向量型）（按條件查找并返回值） 271

技巧248　根據(jù)產(chǎn)品編號查詢庫存數(shù)量（向量型語法） 272

3. LOOKUP函數(shù)（數(shù)組型）（按條件查找并返回值） 273

技巧249　根據(jù)產(chǎn)品編號查詢庫存數(shù)量（數(shù)組型語法） 274

技巧250　按姓名查詢學(xué)生的各科目成績 275

4. HLOOKUP函數(shù)（橫向查找） 277

技巧251　根據(jù)不同的返利率計算各筆訂單的返利金額 278

技巧252　快速查詢?nèi)我饪颇康某煽冃蛄?279

5. VLOOKUP函數(shù)（縱向查找） 281

技巧253　在銷售表中自動返回產(chǎn)品單價 282

技巧254　將多張工作表中的數(shù)據(jù)合并到一張工作表中 283

技巧255　根據(jù)多條件派發(fā)贈品 285

技巧256　使用VLOOKUP函數(shù)進行反向查詢 286

6. MATCH函數(shù) INDEX函數(shù)（MATCH查找并返回找到值所在位置，INDEX函數(shù)返

回指定位置的值） 288

技巧257　了解MATCH函數(shù)與INDEX函數(shù) 288

技巧258　查找任意指定銷售員的銷售總金額 290

技巧259　查找指定月份指定專柜的銷售金額 291

技巧260　返回成績*的學(xué)生的姓名 293

技巧261　查找遲到次數(shù)*多的學(xué)生 294

技巧262　查詢*總金額所對應(yīng)的店鋪 295

8.2　引用函數(shù) 296

7. ADDRESS函數(shù)（建立文本類型單元格的地址） 296

技巧263　查找*銷售額所在位置 296

8. COLUMN函數(shù)（返回引用的列標(biāo)） 297

技巧264　在一行中快速輸入月份 298

技巧265　實現(xiàn)隔列求總銷售金額 298

9. COLUMNS函數(shù)（返回引用的列數(shù)） 299

技巧266　返回參與考試的科目數(shù)量 300

10. ROW函數(shù)（返回引用的行號） 300

技巧267　讓數(shù)據(jù)自動隔4行（自定義）加1（自定義） 300

技巧268　提取季度合計值計算全年銷售額 301

技巧269　根據(jù)借款期限返回相應(yīng)的年數(shù)序列 302

11. ROWS函數(shù)（返回引用的行數(shù)） 304

技巧270　統(tǒng)計銷售記錄條數(shù) 304

技巧271　判斷值班人員是否重復(fù) 304

12. INDIRECT函數(shù)（返回指定的引用） 305

技巧272　解決合并單元格引用數(shù)據(jù)列出現(xiàn)跳躍的問題 306

技巧273　按指定的范圍計算平均值 307

技巧274　INDIRECT解決跨工作表查詢時名稱匹配問題 309

13. OFFSET函數(shù)（根據(jù)指定偏移量得到新引用） 310

技巧275　實現(xiàn)數(shù)據(jù)的動態(tài)查詢 311

技巧276　對每日出庫量累計求和 312

技巧277　OFFSET函數(shù)常用于創(chuàng)建動態(tài)圖表數(shù)據(jù)源 313

第9章　信息函數(shù)范例 316

9.1　使用IS函數(shù)進行判斷 316

1. ISBLANK函數(shù)（判斷值是否為空值） 316

技巧278　標(biāo)注出缺考學(xué)生 316

2. ISTEXT函數(shù)（判斷數(shù)據(jù)是否為文本） 317

技巧279　快速統(tǒng)計缺考人數(shù) 317

3. ISLOGICAL函數(shù)（判斷數(shù)據(jù)是否為邏輯值） 318

技巧280　檢驗數(shù)據(jù)是否為邏輯值 318

4. ISNUMBER函數(shù)（判斷數(shù)據(jù)是否為數(shù)字） 318

技巧281　統(tǒng)計實考人數(shù) 319

技巧282　統(tǒng)計銷售量總計值 319

5. ISNA函數(shù)（判斷數(shù)據(jù)是否為錯誤值“ N/A”） 320

技巧283　檢驗數(shù)據(jù)是否為“ N/A”錯誤值 320

6. ISERR函數(shù)（判斷數(shù)據(jù)是否為“ N/A”之外的任意錯誤值） 321

技巧284　檢驗數(shù)據(jù)是否為“ N/A”之外的任意錯誤值 321

7. ISODD函數(shù)（判斷數(shù)據(jù)是否為奇數(shù)） 321

技巧285　根據(jù)身份證號碼判斷其性別1 322

8. ISEVEN函數(shù)（判斷數(shù)據(jù)是否為偶數(shù)） 322

技巧286　根據(jù)身份證號碼判斷其性別2 323

9.2　返回相關(guān)值 323

9. CELL函數(shù)（返回單元格、位置等） 323

技巧287　判斷測試結(jié)果是否達標(biāo) 324

10. ERROR.TYPE函數(shù)（返回錯誤對應(yīng)的編號） 325

技巧288　根據(jù)錯誤代碼顯示錯誤原因 326

11. N函數(shù)（返回轉(zhuǎn)換為數(shù)值后的值） 326

技巧289　用訂單生成日期的序列號與當(dāng)前行號生成訂單的編號 327

12. TYPE函數(shù)（返回數(shù)值類型） 327

技巧290　返回數(shù)值對應(yīng)的類型數(shù)字 328

第10章 數(shù)據(jù)庫函數(shù)范例 329

10.1　常規(guī)統(tǒng)計 329

1. DSUM函數(shù)（從數(shù)據(jù)庫中按給定條件求和） 329

技巧291　計算指定經(jīng)辦人的訂單總金額 329

技巧292　計算上半月中指定名稱產(chǎn)品的總銷售額（滿足雙條件） 330

技巧293　去除某一個（或多個）部門計算總工資 331

技巧294　使用通配符實現(xiàn)利潤求和統(tǒng)計 332

技巧295　解決模糊匹配造成統(tǒng)計錯誤問題 332

2. DAVERAGE函數(shù)（從數(shù)據(jù)庫中按給定條件求平均值） 334

技巧296　計算指定班級平均分 334

技巧297　計算指定車間指定性別員工的平均工資（雙條件） 335

技巧298　實現(xiàn)對各科目平均成績查詢 336

3. DCOUNT函數(shù)（從數(shù)據(jù)庫中按給定條件統(tǒng)計記錄條數(shù)） 337

技巧299　統(tǒng)計成績小于60分的人數(shù)（忽略0值） 337

技巧300　統(tǒng)計出指定性別且工資大于3000元的人數(shù) 338

技巧301　統(tǒng)計記錄條數(shù)時使用通配符 339

4. DCOUNTA函數(shù)（從數(shù)據(jù)庫中按給定條件統(tǒng)計非空單元格數(shù)目） 339

技巧302　統(tǒng)計業(yè)務(wù)水平為“好”的人數(shù) 340

技巧303　統(tǒng)計出指定性別測試合格的人數(shù) 341

技巧304　按條件統(tǒng)計來訪總?cè)藬?shù)（使用通配符） 341

5. DMAX函數(shù)（從數(shù)據(jù)庫中按給定條件求*值） 342

技巧305　返回指定車間指定性別員工的*工資 342

技巧306　實現(xiàn)查詢各科目成績中的*分 343

6. DMIN函數(shù)（從數(shù)據(jù)庫中按給定條件求*小值） 345

技巧307　返回指定班級的*分 345

技巧308　實現(xiàn)查詢各科目成績中的*分 346

7. DGET函數(shù)（從數(shù)據(jù)庫中提取符合條件的單個值） 347

技巧309　在列表或數(shù)據(jù)庫中按條件查詢 347

8. DPRODUCT函數(shù)（從數(shù)據(jù)庫中返回滿足指定條件的數(shù)值的乘積） 348

技巧310　判斷指定類別與品牌的商品是否被維修過 349

10.2　方差、標(biāo)準(zhǔn)差計算 350

9. DSTDEV函數(shù)（按條件計算樣本標(biāo)準(zhǔn)偏差） 350

技巧311　計算女性員工工齡的樣本標(biāo)準(zhǔn)偏差 351

10. DSTDEVP函數(shù)（按條件計算總體標(biāo)準(zhǔn)偏差） 351

技巧312　計算女性員工工齡的總體標(biāo)準(zhǔn)偏差 352

11. DVAR函數(shù)（按條件計算樣本總體方差） 352

技巧313　計算女性員工工齡的樣本總體方差 353

12. DVARP函數(shù)（按條件計算總體方差） 353

技巧314　計算女性員工工齡的總體方差 354

第11章　用公式設(shè)置單元格格式及限制數(shù)據(jù)輸入 355

11.1　函數(shù)在條件格式中的應(yīng)用 355

技巧315　判斷兩列數(shù)據(jù)是否完全相同 355

技巧316　自動標(biāo)識值班安排表中的周末日期 357

技巧317　為不包含某一特定字符的單元格設(shè)置格式 359

技巧318　將成績高于平均值的標(biāo)注為“優(yōu)” 360

技巧319　突出標(biāo)識非數(shù)字的單元格 361

技巧320　滿足條件的整行突出顯示 362

技巧321　突出顯示每行的**分 364

技巧322　次日值班人員自動提醒 365

技巧323　庫存量過小時提示補貨 367

11.2　函數(shù)在數(shù)據(jù)有效性中的應(yīng)用 368

技巧324　避免輸入重復(fù)的產(chǎn)品編碼 368

技巧325　提示出貨數(shù)量大于進貨數(shù)量 370

技巧326　避免輸入文本值 371

技巧327　限制輸入的數(shù)據(jù)必須包含兩位小數(shù) 372

技巧328　禁止輸入空格 373

技巧329　禁止錄入不完整的產(chǎn)品規(guī)格 374

技巧330　設(shè)置單元格輸入必須包含指定內(nèi)容 375

技巧331　限制數(shù)據(jù)輸入的長度 376

第12章 Excel函數(shù)與公式使用常見問題集 378

1. 公式運算問題 378

問題1　想復(fù)制公式卻找不到填充柄 378

問題2　利用公式計算時不顯示計算結(jié)果只顯示公式 379

問題3　兩個日期相減時不能得到差值天數(shù)，卻返回一個日期值 380

問題4　更改數(shù)據(jù)源的值，公式的計算結(jié)果為什么不自動更新 381

問題5　公式引用單元格明明顯示的是數(shù)據(jù)，計算結(jié)果卻為0 381

問題6　數(shù)字與“空”單元格，結(jié)果卻報錯 382

問題7　新輸入的行中不能自動填充上一行的公式 383

問題8　合并單元格中公式怎么復(fù)制 384

問題9　LOOKUP查找總是找不到正確結(jié)果 385

問題10　VLOOKUP查找時，查找內(nèi)容與查找區(qū)域首列內(nèi)容不精確匹配，用辦法

實現(xiàn)查找 386

問題11　日期數(shù)據(jù)與其他數(shù)據(jù)合并時，不能顯示原樣日期，只顯示一串序列

號 387

問題12　在設(shè)置按條件求和（按條件計數(shù)等）函數(shù)的criteria（用于條件判

斷的）參數(shù)時，如何處理條件判斷問題 388

問題13　解決浮點運算造成ROUND函數(shù)計算不準(zhǔn)確的問題 389

2. 公式返回錯誤值問題 390

問題14　公式返回“ DIV>0!”錯誤值（“0”值或空白單元格被作除數(shù)）

390

問題15　公式返回“ N>A”錯誤值（公式中引用的數(shù)據(jù)源不正確或不能使用

） 391

問題16　公式返回“ NAME”錯誤值1（輸入的函數(shù)和名稱拼寫錯誤） 392

問題17　公式返回“ NAME”錯誤值2（公式中使用文本作為參數(shù)時未加雙引

號） 393

問題18　公式返回“ NAME”錯誤值3（在公式中使用了未定義的名稱） 393

問題19　公式返回“ NAME”錯誤值4（引用其他工作表時工作表名稱包含空

格） 394

問題20　公式返回“ NUM!”錯誤值（引用了無效的參數(shù)） 395

問題21　公式返回“ VALUE!”錯誤值1（公式中將文本類型的數(shù)據(jù)參與了數(shù)

值運算） 395

問題22　公式返回“ VALUE!”錯誤值2（公式中函數(shù)使用的參數(shù)與語法不一

致） 396

問題23　公式返回“ VALUE!”錯誤值3（數(shù)組運算未按“Ctrl Shift Enter

”組合鍵結(jié)束） 397

問題24　公式返回“ REF!”錯誤值（公式計算中引用了無效的單元格） 397

3. Excel應(yīng)用中的其他常見問題 398

問題25　滑動鼠標(biāo)中鍵向下查看數(shù)據(jù)時，工作表中的內(nèi)容卻隨之進行縮放

398

問題26　要輸入一串產(chǎn)品代碼，按下回車后顯示為了科學(xué)計數(shù)方式的數(shù)字

398

問題27　填充序號時不能自動遞增 399

問題28　填充時間時為何不能按分鐘數(shù)（或秒數(shù)）遞增 400

問題29　向單元格中輸入數(shù)據(jù)老是彈出對話框 401

問題30　對于文本型數(shù)字，為它應(yīng)用了“數(shù)值”格式后怎么還是沒有變成數(shù)

值（無法計算） 402

問題31　只想復(fù)制篩選結(jié)果但粘貼后是所有未篩選前的數(shù)據(jù) 403