高效隨身查：Excel函數與公式應用技巧（2016版）

第1章　函數與公式 1

1.1　公式的輸入與編輯 1

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

技巧2　公式函數不分家 2

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

技巧4　手寫編輯公式 7

技巧5　公式的修改 7

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

技巧7　快速查找和學習某函數用法 9

技巧8　使用批量計算 11

技巧9　大范圍公式復制的方法 11

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

技巧11　數組公式 14

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

技巧13　為什么明明顯示的是數據，計算結果卻為0 16

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

技巧15　將公式運算結果轉換為數值 18

技巧16　隱藏公式實現保護 19

1.2　公式中數據源的引用 20

技巧17　相對引用數據源計算 20

技巧18　*引用數據源計算 21

技巧19　引用當前工作表之外的單元格 22

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

技巧21　引用其他工作簿中的數據源 25

技巧22　用“F4”鍵快速改變數據源引用類型 27

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

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

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

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

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

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

1.3　學會分解、理解公式 33

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

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

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

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

第2章 邏輯函數范例 38

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

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

技巧34　一次性判斷一組數據是否都滿足條件 39

2. NOT函數（對所給參數求反） 40

技巧35　篩選掉小于某一年齡的應聘人員 40

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

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

4. IF函數（根據條件判斷真假） 42

技巧37　利用IF函數進行金額校驗 43

技巧38　根據不同返利比計算返利金額 43

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

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

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

技巧42　根據職工性別和職務判斷退休年齡 48

技巧43　根據年齡與學歷對應聘人員二次篩選 49

技巧44　根據商品的名稱與顏色進行一次性調價 50

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

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

技巧47　計算個人所得稅 54

5. IFERROR函數（根據條件判斷真假） 56

技巧48　當被除數為空值（或0值）時返回“計算數據源有錯誤”文字 56

第3章　文本函數范例 58

3.1　提取文本 58

1. LEFT函數（從*左側開始提取指定數目的字符） 58

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

技巧50　從商品全稱中提取產地信息 59

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

技巧51　根據產品編號提取類別 60

3. RIGHT函數（從*右側開始提取指定數目的字符） 61

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

技巧53　從右側提取字符并自動轉換為數值 63

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

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

技巧55　提取產品的規(guī)格數據 67

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

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

技巧57　提取產品的貨號 69

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

技巧58　從房號數據中提取單元號 71

7. CONCATENATE函數（合并多個字符） 72

技巧59　合并商品貨號碼數及顏色 72

技巧60　提取任意有用數據并合并 73

3.2　查找與替換文本 74

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

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

技巧62　問卷調查時實現自動統計答案 75

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

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

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

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

11. SUBSTITUTE函數（用新字符替換舊字符） 80

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

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

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

技巧68　根據報名學員統計人數 83

3.3　轉換文本格式 84

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

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

13. WIDECHAR函數（半角字母轉換為全角字母） 85

技巧70　將半角英文字母轉換為全角英文字母 85

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

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

15. LOWER函數（大寫字母轉換為小寫字母） 86

技巧72　將大寫字母轉換為小寫字母 86

16. PROPER函數（將首字符轉換為大寫） 87

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

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

技巧74　將銷售額一次性轉換為人民幣格式 88

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

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

19. TEXT函數（將數值轉換為指定格式的文本） 90

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

技巧77　返回值班日期對應在的星期數 91

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

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

技巧80　讓數據統一顯示固定的位數 94

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

20. VALUE函數（將文本型數字轉換成數值型數字） 96

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

3.4　其他文本函數 97

21. LEN函數（返回字符串的字符數） 97

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

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

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

22. REPT函數（重復文本） 101

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

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

23. TRIM函數（清除空格） 102

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

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

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

第4章　日期與時間函數范例 105

4.1　返回日期 105

1. NOW函數（返回當前日期時間） 105

技巧90　計算員工在職天數 105

2. TODAY函數（返回當前日期） 106

技巧91　判斷應收賬款是否到期 106

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

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

4. DAY函數（返回某日期的天數） 108

技巧93　顯示出全年中各月的天數 108

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

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

技巧95　計算出員工年齡 110

技巧96　計算出員工工齡 111

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

技巧97　返回日期對應的星期數 113

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

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

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

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

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

技巧101　根據活動開始日期計算各月活動天數 117

技巧102　根據當前月份自動顯示本月所有日期序列 118

技巧103　自動返回各日期對應的星期數 120

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

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

4.2　日期計算 122

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

技巧105　計算總借款天數 123

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

技巧107　根據員工工齡自動追加工齡工資 124

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

技巧108　計算還款剩余天數 125

技巧109　計算固定資產已使用月份 126

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

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

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

13. WORKDAY函數（計算工作日） 129

技巧112　根據休假天數自動顯示出休假結束日期 130

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

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

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

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

技巧115　計算應收賬款的到期日期 133

4.3　時間函數 134

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

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

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

技巧117　計算停車時間 135

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

技巧118　計算通話秒數 136

技巧119　顯示高于或低于標準時間的值 137

4.4　日期與時間轉換 138

19. DATEVALUE函數（將文本日期轉換為可識別的日期序列號） 138

技巧120　計算借款天數 139

20. TIMEVALUE函數（將時間轉換為對應的小數值） 140

技巧121　將指定時間轉換為時間小數值 140

第5章 數學函數范例 141

5.1　常規(guī)數學計算函數 141

1. SUM函數（求和） 141

技巧122　一次性對多列數據求和 141

技巧123　根據銷售數量與單價計算總銷售額 142

技巧124　只計算某兩個產品的合計金額 143

技巧125　分奇偶行統計數據 144

2. SUMIF函數（按條件求和） 145

技巧126　按經辦人計算銷售金額 145

技巧127　統計各部門工資總額 146

技巧128　分別統計前半個月與后半個月的銷售額 147

技巧129　用通配符對某一類數據求和 148

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

3. SUMIFS函數（按多條件求和） 150

技巧131　滿足指定類別指定時間統計總銷售額 150

技巧132　多條件統計某一類數據總和 152

技巧133　按不同性質統計應收款 153

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

技巧134　統計銷售部女員工人數 155

技巧135　統計出指定班級中分數大于指定值的人數 156

技巧136　統計出指定部門獲取獎金的人數（去除空值） 157

技巧137　統計出指定部門、指定職務的員工人數 158

技巧138　統計非工作日銷售金額 159

技巧139　按月匯總出庫數量 160

技巧140　統計大于12個月的賬款 161

技巧141　統計出某兩種商品的銷售金額 162

技巧142　統計某一時間段出現的次數 163

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

技巧144　分單位統計賬齡 165

5. ABS函數（求*值） 168

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

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

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

技巧147　按奇數月與偶數月統計銷量 170

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

7. SQRT函數（求算術平方根） 172

技巧149　計算指定數值對應的算術平方根 173

8. GCD函數（求*公約數） 173

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

9. LCM函數（求*小公倍數） 174

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

10. POWER函數（計算方根） 175

技巧152　根據指定的底數和指數計算出方根值 175

5.2　舍入計算函數 176

11. INT函數（向下取整） 176

技巧153　計算平均銷售數量時取整數 176

12. TRUNC函數（截去數據小數部分） 177

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

13. CEILING函數（舍入計算） 179

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

14. ROUND函數（四舍五入） 180

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

15. ROUNDUP函數（向上舍入） 182

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

技巧158　計算上網費用 183

16. ROUNDDOWN函數（向下舍入） 184

技巧159　根據給定時間界定整點范圍 185

17. FLOOR函數（去尾舍入） 186

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

18. MROUND函數（按指定倍數舍入） 187

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

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

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

5.3　階乘、*數計算函數 189

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

技巧163　求指定正數值的階乘值 190

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

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

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

技巧165　*獲取選手編號 192

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

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

技巧167　自動*生成3位數編碼 193

第6章　統計函數范例 194

6.1　平均值計算函數 194

1. AVERAGE函數（求平均值） 194

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

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

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

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

技巧171　計算平均分時將“缺考”的也計算在內 197

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

3. AVERAGEIF函數（按條件求平均值） 199

技巧173　統計各班級平均分 199

技巧174　計算月平均出庫數量 200

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

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

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

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

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

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

5. GEOMEAN函數（求幾何平均值） 205

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

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

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

6.2　數目統計函數 207

7. COUNT函數（統計數目） 207

技巧182　統計出故障的機器臺數 207

技巧183　統計各個部門獲取交通補助的人數 208

技巧184　統計出指定學歷員工人數 209

技巧185　統計其中一科得滿分的人數 210

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

技巧186　統計參與某項課程的總人數 211

技巧187　統計非正常出勤的人數 211

9. COUNTIF函數（按條件統計數目） 212

技巧188　統計工資大于3000元的人數 212

技巧189　統計某課程的報名人數 213

技巧190　在成績表中分別統計及格人數與不及格人數 213

技巧191　統計出成績大于平均分數的學生人數 215

技巧192　統計是三好學生且參加數學競賽的人數 215

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

10. COUNTIFS函數（按多條件統計數目） 217

技巧194　統計各店面男裝的銷售記錄條數（雙條件） 217

技巧195　統計指定時間指定類別商品的銷售記錄數 218

技巧196　統計指定產品每日的銷售記錄數 219

11. COUNTBLANK函數（統計空單元格的數目） 220

技巧197　統計缺考人數 221

6.3　*值與*小值統計函數 221

12. MAX函數（求*值） 221

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

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

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

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

13. MIN函數（求*小值） 225

技巧202　返回*銷售金額 225

技巧203　忽略0值求*分數 226

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

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

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

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

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

16. LARGE函數（返回某一數據集中的某個*值） 230

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

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

技巧209　分別統計各班級*名成績 231

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

技巧210　統計成績表中后5名的平均分 232

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

18. MODE函數 234

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

6.4　排位統計函數 235

19. RANK.EQ函數（返回數字的排位） 235

技巧213　為學生考試成績排名次 235

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

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

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

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

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

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

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

23. MEDIAN函數（返回數據集的中位數） 240

技巧218　統計全年各月利潤值中的中位數 241

24. QUARTILE.INC函數（返回數據集的四分位數） 241

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

第7章　財務函數范例 244

7.1　投資計算函數 244

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

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

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

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

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

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

3. PPMT函數（計算本金） 247

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

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

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

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

5. FV函數（計算投資未來值） 250

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

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

6. FVSCHEDULE函數（投資在變動或可調利率下的未來值） 251

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

7. PV函數（計算投資現值） 252

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

8. NPV函數（計算投資凈現值） 253

技巧231　計算某投資的凈現值 253

9. XNPV函數（計算一組不定期現金流的凈現值） 255

技巧232　計算出一組不定期盈利額的凈現值 255

10. NPER函數（計算投資期數） 255

技巧233　計算出貸款的清還年數 256

技巧234　計算出某項投資的投資期數 256

7.2　償還率計算函數 257

11. IRR函數（計算一組現金流的內部收益率） 257

技巧235　計算某項投資的內部收益率 257

12. XIRR函數（計算一組不定期現金流的內部收益率） 258

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

13. MIRR函數（計算修正內部收益率） 259

技巧237　計算某項投資的修正內部收益率 259

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

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

7.3　資產折舊計算函數 261

15. SLN函數（直線法） 261

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

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

16. SYD函數（年數總和法） 262

技巧241　用年數總和法計算出固定資產的每年折舊額 262

17. DB函數（固定余額遞減法） 263

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

18. DDB函數（雙倍余額遞減法） 264

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

19. VDB函數（計算部分期間內的折舊值） 265

技巧244　計算出固定資產部分期間的設備折舊值 266

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

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

第8章　查找和引用函數范例 269

8.1　查找函數 269

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

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

技巧247　根據產品不合格率決定產品處理辦法 270

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

技巧248　根據產品編號查詢庫存數量（向量型語法） 272

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

技巧249　根據產品編號查詢庫存數量（數組型語法） 274

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

4. HLOOKUP函數（橫向查找） 277

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

技巧252　快速查詢任意科目的成績序列 279

5. VLOOKUP函數（縱向查找） 281

技巧253　在銷售表中自動返回產品單價 282

技巧254　將多張工作表中的數據合并到一張工作表中 283

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

技巧256　使用VLOOKUP函數進行反向查詢 286

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

回指定位置的值） 288

技巧257　了解MATCH函數與INDEX函數 288

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

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

技巧260　返回成績*的學生的姓名 293

技巧261　查找遲到次數*多的學生 294

技巧262　查詢*總金額所對應的店鋪 295

8.2　引用函數 296

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

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

8. COLUMN函數（返回引用的列標） 297

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

技巧265　實現隔列求總銷售金額 298

9. COLUMNS函數（返回引用的列數） 299

技巧266　返回參與考試的科目數量 300

10. ROW函數（返回引用的行號） 300

技巧267　讓數據自動隔4行（自定義）加1（自定義） 300

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

技巧269　根據借款期限返回相應的年數序列 302

11. ROWS函數（返回引用的行數） 304

技巧270　統計銷售記錄條數 304

技巧271　判斷值班人員是否重復 304

12. INDIRECT函數（返回指定的引用） 305

技巧272　解決合并單元格引用數據列出現跳躍的問題 306

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

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

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

技巧275　實現數據的動態(tài)查詢 311

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

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

第9章　信息函數范例 316

9.1　使用IS函數進行判斷 316

1. ISBLANK函數（判斷值是否為空值） 316

技巧278　標注出缺考學生 316

2. ISTEXT函數（判斷數據是否為文本） 317

技巧279　快速統計缺考人數 317

3. ISLOGICAL函數（判斷數據是否為邏輯值） 318

技巧280　檢驗數據是否為邏輯值 318

4. ISNUMBER函數（判斷數據是否為數字） 318

技巧281　統計實考人數 319

技巧282　統計銷售量總計值 319

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

技巧283　檢驗數據是否為“ N/A”錯誤值 320

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

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

7. ISODD函數（判斷數據是否為奇數） 321

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

8. ISEVEN函數（判斷數據是否為偶數） 322

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

9.2　返回相關值 323

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

技巧287　判斷測試結果是否達標 324

10. ERROR.TYPE函數（返回錯誤對應的編號） 325

技巧288　根據錯誤代碼顯示錯誤原因 326

11. N函數（返回轉換為數值后的值） 326

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

12. TYPE函數（返回數值類型） 327

技巧290　返回數值對應的類型數字 328

第10章 數據庫函數范例 329

10.1　常規(guī)統計 329

1. DSUM函數（從數據庫中按給定條件求和） 329

技巧291　計算指定經辦人的訂單總金額 329

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

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

技巧294　使用通配符實現利潤求和統計 332

技巧295　解決模糊匹配造成統計錯誤問題 332

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

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

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

技巧298　實現對各科目平均成績查詢 336

3. DCOUNT函數（從數據庫中按給定條件統計記錄條數） 337

技巧299　統計成績小于60分的人數（忽略0值） 337

技巧300　統計出指定性別且工資大于3000元的人數 338

技巧301　統計記錄條數時使用通配符 339

4. DCOUNTA函數（從數據庫中按給定條件統計非空單元格數目） 339

技巧302　統計業(yè)務水平為“好”的人數 340

技巧303　統計出指定性別測試合格的人數 341

技巧304　按條件統計來訪總人數（使用通配符） 341

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

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

技巧306　實現查詢各科目成績中的*分 343

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

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

技巧308　實現查詢各科目成績中的*分 346

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

技巧309　在列表或數據庫中按條件查詢 347

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

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

10.2　方差、標準差計算 350

9. DSTDEV函數（按條件計算樣本標準偏差） 350

技巧311　計算女性員工工齡的樣本標準偏差 351

10. DSTDEVP函數（按條件計算總體標準偏差） 351

技巧312　計算女性員工工齡的總體標準偏差 352

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

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

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

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

第11章　用公式設置單元格格式及限制數據輸入 355

11.1　函數在條件格式中的應用 355

技巧315　判斷兩列數據是否完全相同 355

技巧316　自動標識值班安排表中的周末日期 357

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

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

技巧319　突出標識非數字的單元格 361

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

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

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

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

11.2　函數在數據有效性中的應用 368

技巧324　避免輸入重復的產品編碼 368

技巧325　提示出貨數量大于進貨數量 370

技巧326　避免輸入文本值 371

技巧327　限制輸入的數據必須包含兩位小數 372

技巧328　禁止輸入空格 373

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

技巧330　設置單元格輸入必須包含指定內容 375

技巧331　限制數據輸入的長度 376

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

1. 公式運算問題 378

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

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

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

問題4　更改數據源的值，公式的計算結果為什么不自動更新 381

問題5　公式引用單元格明明顯示的是數據，計算結果卻為0 381

問題6　數字與“空”單元格，結果卻報錯 382

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

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

問題9　LOOKUP查找總是找不到正確結果 385

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

實現查找 386

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

號 387

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

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

問題13　解決浮點運算造成ROUND函數計算不準確的問題 389

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

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

390

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

） 391

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

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

號） 393

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

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

格） 394

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

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

值運算） 395

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

致） 396

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

”組合鍵結束） 397

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

3. Excel應用中的其他常見問題 398

問題25　滑動鼠標中鍵向下查看數據時，工作表中的內容卻隨之進行縮放

398

問題26　要輸入一串產品代碼，按下回車后顯示為了科學計數方式的數字

398

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

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

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

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

值（無法計算） 402

問題31　只想復制篩選結果但粘貼后是所有未篩選前的數據 403