高效隨身查：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　使用批量計(jì)算 11

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

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

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

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

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

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

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

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

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

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

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

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

技巧20　引用多工作表的同一單元格計(jì)算 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)名稱實(shí)現(xiàn)數(shù)據(jù)計(jì)算即時(shí)更新 31

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧36　檢驗(yàn)員工是否通過考評 41

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

技巧37　利用IF函數(shù)進(jìn)行金額校驗(yàn) 43

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

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

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

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

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

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

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

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

技巧46　比較兩個(gè)采購部門的采購價(jià)格 53

技巧47　計(jì)算個(gè)人所得稅 54

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

技巧48　當(dāng)被除數(shù)為空值（或0值）時(shí)返回“計(jì)算數(shù)據(jù)源有錯(cuò)誤”文字 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ù)中提取部分?jǐn)?shù)據(jù) 69

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

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

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

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

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

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

3.2　查找與替換文本 74

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

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

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

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

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

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

技巧64　從項(xiàng)目編碼中提取合同號 79

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

技巧65　去掉報(bào)表中多余的空格 80

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

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

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

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

12. FIXED函數(shù)（按指定的小數(shù)位數(shù)進(jìn)行取整） 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　一次性將每個(gè)單詞的首字母轉(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　讓計(jì)算得到金額顯示為特定形式 90

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

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

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

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

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

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

技巧82　解決總金額無法計(jì)算的問題 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ù)（比較兩個(gè)字符串是否相同） 103

技巧89　比較兩個(gè)店鋪的平均售價(jià)是否相同 103

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

4.1　返回日期 105

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

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

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

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

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

技巧92　建立倒計(jì)時(shí)顯示牌 107

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

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

技巧94　計(jì)算本月上旬的銷售總額 109

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

技巧95　計(jì)算出員工年齡 110

技巧96　計(jì)算出員工工齡 111

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

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

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

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

技巧99　自動填寫銷售報(bào)表中的月份 115

技巧100　計(jì)算本月賬款金額總計(jì) 116

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

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

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

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

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

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

4.2　日期計(jì)算 122

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

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

技巧106　計(jì)算借款至今日的時(shí)長 123

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.3　時(shí)間函數(shù) 134

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

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

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

技巧117　計(jì)算停車時(shí)間 135

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧130　計(jì)算銷售金額前3名合計(jì)值 149

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧146　計(jì)算支出金額總計(jì)值 169

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

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

技巧148　計(jì)算每位員工的加班時(shí)長 171

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

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧155　以6秒（不足6秒按6秒計(jì)算）為單位計(jì)算通話費(fèi)用 179

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

技巧156　以一個(gè)百分點(diǎn)為單位計(jì)算獎(jiǎng)金或扣款 181

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

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

技巧158　計(jì)算上網(wǎng)費(fèi)用 183

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

技巧159　根據(jù)給定時(shí)間界定整點(diǎn)范圍 185

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

技巧160　計(jì)件工資中的獎(jiǎng)金計(jì)算 186

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

技巧161　計(jì)算商品運(yùn)送車次 187

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

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

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

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

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

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

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

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

技巧165　*獲取選手編號 192

技巧166　自動生成彩票7位開獎(jiǎng)號碼 192

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

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧175　排除新店計(jì)算平均利潤 201

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

技巧176　計(jì)算一車間女職工平均工資 202

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧202　返回*銷售金額 225

技巧203　忽略0值求*分?jǐn)?shù) 226

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

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

技巧205　返回成績表中的*分?jǐn)?shù)（包含文本） 228

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

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

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

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

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

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

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

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

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

18. MODE函數(shù) 234

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

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

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

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

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

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

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

21. PERCENTILE.INC函數(shù)（返回?cái)?shù)值指定百分點(diǎn)的值） 237

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

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

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

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

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

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

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

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

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

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

技巧220　計(jì)算貸款的每年償還額 244

技巧221　計(jì)算貸款的每月償還額 245

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

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

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

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

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

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

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

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

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

技巧227　計(jì)算分期存款的未來值 250

技巧228　計(jì)算購買某項(xiàng)保險(xiǎn)的未來值 251

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

技巧229　計(jì)算某項(xiàng)整存整取存款的未來值 251

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

技巧230　判斷購買某項(xiàng)保險(xiǎn)是否合算 252

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

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

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

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

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

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

技巧234　計(jì)算出某項(xiàng)投資的投資期數(shù) 256

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

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

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

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

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

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

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

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

技巧238　計(jì)算購買某項(xiàng)保險(xiǎn)的收益率 260

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧245　計(jì)算指定會計(jì)期間的折舊值 268

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

8.1　查找函數(shù) 269

1. CHOOSE函數(shù)（從參數(shù)列表中選擇并返回一個(gè)值） 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ù)不同的返利率計(jì)算各筆訂單的返利金額 278

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

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

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

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

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

技巧256　使用VLOOKUP函數(shù)進(jìn)行反向查詢 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　實(shí)現(xiàn)隔列求總銷售金額 298

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

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

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

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

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

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

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

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

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

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

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

技巧273　按指定的范圍計(jì)算平均值 307

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

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

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

技巧276　對每日出庫量累計(jì)求和 312

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧284　檢驗(yàn)數(shù)據(jù)是否為“ N/A”之外的任意錯(cuò)誤值 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é)果是否達(dá)標(biāo) 324

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

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

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

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

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

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

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

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

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

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

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

技巧293　去除某一個(gè)（或多個(gè)）部門計(jì)算總工資 331

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

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

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

技巧296　計(jì)算指定班級平均分 334

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

技巧314　計(jì)算女性員工工齡的總體方差 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　庫存量過小時(shí)提示補(bǔ)貨 367

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

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

技巧325　提示出貨數(shù)量大于進(jìn)貨數(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. 公式運(yùn)算問題 378

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

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

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

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

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

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

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

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

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

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

實(shí)現(xiàn)查找 386

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

號 387

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

斷的）參數(shù)時(shí)，如何處理?xiàng)l件判斷問題 388

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

2. 公式返回錯(cuò)誤值問題 390

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

390

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

） 391

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

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

號） 393

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

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

格） 394

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

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

值運(yùn)算） 395

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

致） 396

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

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

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

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

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

398

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

398

問題27　填充序號時(shí)不能自動遞增 399

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

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

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

值（無法計(jì)算） 402

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