Excel公式與函式大辭典(2018年人民郵電出版社出版的圖書)

《Excel公式與函式大辭典》是2018年人民郵電出版社出版的圖書。

基本介紹

• 中文名：Excel公式與函式大辭典
• 作者：宋翔 
• 出版時間：2018年4月
• 出版社： 人民郵電出版社
• ISBN： 9787115366009

內容簡介

《Excel公式與函式大辭典》全面細緻地介紹了Excel 2013/2010/2007/2003中公式和函式的使用方法、實際套用和操作技巧，從函式功能、函式格式、參數說明、注意事項、版本提醒、案例套用、交叉參考等7個方面對Excel函式進行了全方位的介紹，而且在需要的地方加入了提示或技巧等內容。3章還介紹了公式與函式在條件格式、數據有效性及圖表套用中發揮的強大功能。《Excel公式與函式大辭典》採用理論與實踐相結合的方式，提供了405個案例，涉及多個行業，讀者可以根據書中的案例舉一反三，將其直接套用到實際工作中，有效提高學習效果與實際套用能力。

《Excel公式與函式大辭典》附贈一張光碟，其中不僅包括本書所有案例的源檔案，還包括由作者編寫及製作的Excel數據透視表電子書、Excel VBA程式開發電子書、Excel函式速查工具、Word 2013多媒體教學視頻、Excel 2013多媒體教學視頻、PowerPoint 2013多媒體教學視頻、Windows 8多媒體教學視頻，以及Word/Excel/PowerPoint商務文檔模板。

《Excel公式與函式大辭典》既可作為函式速查工具手冊，又可作為豐富的函式套用案例寶典，適合對Excel公式與函式有需求的讀者閱讀。

圖書目錄

目　錄

第 1章　公式與函式基礎　1

1.1　了解公式　1

1.1.1　公式的組成　1

1.1.2　運算符及其優先權　1

1.1.3　普通公式與數組公式　3

1.1.4　單個單元格公式與多個單元格公式　3

1.2　輸入與編輯公式　3

1.2.1　A1引用與R1C1引用　4

1.2.2　輸入公式　5

1.2.3　修改公式　6

1.2.4　移動和複製公式　6

1.2.5　改變公式的計算方式　8

1.3　在公式中使用函式　9

1.3.1　為什麼使用函式　9

1.3.2　函式的類型　9

1.3.3　函式的參數　11

1.3.4　在公式中輸入函式　11

1.4　在公式中使用名稱　13

1.4.1　名稱的作用範圍　14

1.4.2　命名區域　14

1.4.3　命名公式　16

1.4.4　命名常量　16

1.4.5　將名稱套用到公式中　16

1.5　使用數組公式　18

1.5.1　單個單元格數組公式與多個單元格數組公式　18

1.5.2　數組的維數　19

1.5.3　輸入數組公式　20

1.5.4　修改數組公式　21

1.5.5　擴展或縮小多單元格數組公式　21

1.5.6　選擇數組公式所在區域　22

1.5.7　使用常量數組　22

1.6　使用外部公式　23

1.6.1　創建引用其他工作表中數據的公式　24

1.6.2　創建引用其他工作簿中數據的公式　24

1.6.3　更新外部公式　25

1.7　審核公式　25

1.7.1　使用公式錯誤檢查器　26

1.7.2　定位特定類型的數據　26

1.7.3　跟蹤單元格關係　27

1.7.4　監視單元格內容　28

1.7.5　使用公式求值器　29

1.8　處理公式中的錯誤　30

1.8.1　括弧不匹配　30

1.8.2　以#號填充單元格　30

1.8.3　空白但非空的單元格　30

1.8.4　顯示值與實際值　31

1.8.5　返回錯誤值　31

1.8.6　循環引用　34

1.9　公式使用技巧　34

1.9.1　在多個單元格中輸入同一個公式　35

1.9.2　顯示公式而不是值　35

1.9.3　查看公式的中間結果　35

1.9.4　將公式轉換為值　36

1.9.5　複製公式但不使用相對引用　36

1.9.6　隱藏公式　37

1.9.7　禁止任何人修改公式　38

第 2章　數學和三角函式　39

2.1　常規計算　42

2.1.1　SIGN——返回數字的符號　42

案例01　檢測商品銷量是否達標　43

2.1.2　ABS——計算數字的絕 對值　43

案例02　計算兩地溫差　44

2.1.3　SUM——計算數字之和　44

案例03　匯總兩種商品的銷售額　44

案例04　匯總大於15000的銷售總額　45

案例05　統計銷售部女員工人數　45

2.1.4　PRODUCT——計算數字之積　46

案例06　計算每種商品打折後的價格(PRODUCT+ROUND)　46

2.1.5　SQRT——計算正平方根　47

案例07　計算正方形邊長　47

2.1.6　MOD——返回商的餘數　47

案例08　判斷閏年(MOD+IF+OR+AND)　48

案例09　分別匯總奇數月和偶數月的銷量(MOD+SUM+IF+ROW)　48

2.1.7　QUOTIENT——返回商的整數部分　49

案例10　統計在預算內可購買的辦公用品數量　50

2.1.8　GCD——計算**大公約數　50

案例11　計算**大公約數　50

2.1.9　LCM——計算**小公倍數　51

案例12　計算**小公倍數　51

2.1.10　SUMIF——按給定條件對指定單元格求和　52

案例13　計算某部門員工年薪總和　52

案例14　計算前兩名和後兩名員工的銷售額總和(SUMIF+LARGE+SMALL)　53

2.1.11　SUMIFS——按多個條件對指定單元格求和　53

案例15　匯總指定銷售額範圍內的銷售總額　54

2.1.12　SUMPRODUCT——計算數組元素的乘積之和　54

案例16　計算商品打折後的總價格(SUMPRODUCT+ROUND)　55

案例17　統計銷售部女員工人數　55

2.1.13　SUMSQ——計算參數的平方和　56

案例18　計算直角三角形斜邊的長度(SUMSQ+SQRT)　56

2.1.14　SUMXMY2——計算數組對應值之差的平方和　56

案例19　計算數組對應值之差的平方和　57

2.1.15　SUMX2MY2——計算數組對應值的平方差之和　57

案例20　計算數組對應值的平方差之和　58

2.1.16　SUMX2PY2——計算數組對應值的平方和之和　58

案例21　計算數組對應值的平方和之和　58

2.1.17　SERIESSUM——計算基於公式的冪級數之和　59

案例22　計算自然對數的底數e的近似值　59

2.2　捨入計算　60

2.2.1　INT——返回永遠小於原數字的**接近的整數　60

案例23　匯總整數金額(INT+SUM)　60

2.2.2　TRUNC——返回數字的整數部分　61

案例24　匯總金額只保留一位小數(TRUNC+SUM)　61

2.2.3　ROUND——按指定位數對數字進行四捨五入　62

案例25　將金額取整捨入到百位(ROUND+SUM)　62

2.2.4　ROUNDDOWN——以絕 對值減小的方向按指定位數捨入數字　63

案例26　匯總金額，忽略分位(ROUNDDOWN+SUM)　64

2.2.5　ROUNDUP——以絕 對值增大的方向按指定位數捨入數字　64

案例27　計算上網費用(ROUNDUP+HOUR+MINUTE)　65

2.2.6　MROUND——捨入到指定倍數的數字　66

案例28　計算商品運送車次　67

2.2.7　CEILING——以絕 對值增大的方向按指定倍數捨入　67

案例29　計算上網費用(CEILING+HOUR+MINUTE)　68

2.2.8　CEILING.PRECISE——以算數值增大的方向按指定倍數捨入　68

2.2.9　CEILING.MATH——以絕 對值或算數值增大的方向按指定倍數捨入　69

2.2.10　FLOOR——以絕 對值減小的方向按指定倍數捨入　70

案例30　計算員工提成獎金　71

2.2.11　FLOOR.PRECISE——以算數值減小的方向按指定倍數捨入　72

2.2.12　FLOOR.MATH——以絕 對值或算數值減小的方向按指定倍數捨入　72

2.2.13　EVEN——以絕 對值增大的方向捨入到**接近偶數　74

案例31　隨機抽 獎(男員工)(EVEN+INDEX+RANDBETWEEN)　74

2.2.14　ODD——以絕 對值增大的方向捨入到**接近奇數　75

案例32　隨機抽 獎(女員工)(ODD+INDEX+RANDBETWEEN)　76

2.3　指數與對數計算　76

2.3.1　POWER——計算數字的乘冪　76

案例33　計算區域中各數字的倒數之和(POWER+SUM)　77

2.3.2　EXP——計算e的n次方　77

案例34　計算自然對數的底數e的n次冪　78

2.3.3　LN——計算自然對數　78

案例35　計算數值的自然對數　78

2.3.4　LOG——計算以指定數字為底的對數　78

案例36　計算以指定數字為底的對數　79

2.3.5　LOG10——計算以10為底的對數　79

案例37　計算以10為底的對數　79

2.4　階乘、矩陣與隨機數　80

2.4.1　COMBIN——計算給定數目對象的組合數　80

案例38　計算公司知識競賽對局次數　80

2.4.2　COMBINA——計算給定數目對象具有重複項的組合數　81

2.4.3　FACT——計算數字的階乘　81

案例39　計算10到20之間的數字階乘　82

2.4.4　FACTDOUBLE——計算數字的雙倍階乘　82

案例40　計算1到36之間偶數的乘積　82

2.4.5　MULTINOMIAL——計算多個數字和的階乘與各數字階乘乘積的比值　83

案例41　計算人員分組問題　83

2.4.6　MDETERM——計算數組的矩陣行列式的值　83

案例42　求解二元聯立方程組　84

2.4.7　MINVERSE——計算數組的逆矩陣　84

案例43　求解多元聯立方程組(MINVERSE+MMULT)　85

2.4.8　MMULT——計算兩個數組的矩陣乘積　85

案例44　計算商品在不同單價下的銷售額　85

案例45　計算某商品的**大銷量(MMULT+MAX+N+TRANSPOSE)　86

2.4.9　MUNIT——返回指定維度的單位矩陣　86

2.4.10　RAND——返回0到1之間的一個隨機數　87

案例46　隨機顯示A到Z之間的大寫字母(RAND+CHAR+INT)　87

2.4.11　RANDBETWEEN——返回某個範圍內的隨機數　88

案例47　生成1到50之間的隨機偶數　88

2.5　三角函式計算　89

2.5.1　DEGREES——將弧度轉換為角度　89

案例48　根據弧長和半徑計算角度(DEGREES+ROUND)　89

2.5.2　RADIANS——將角度轉換為弧度　89

案例49　根據角度和半徑計算弧長(RADIANS+ROUND)　90

2.5.3　SIN——計算給定角度的正弦值　90

案例50　計算給定角度的正弦值(SIN+ROUND)　90

2.5.4　ASIN——計算數字的反正弦值　91

案例51　計算數字的反正弦值(ASIN+ROUND)　91

2.5.5　SINH——計算數字的雙曲正弦值　91

案例52　計算數字的雙曲正弦值(SINH+ROUND)　92

2.5.6　ASINH——計算數字的反雙曲正弦值　92

案例53　計算數字的反雙曲正弦值(ASINH+ROUND)　92

2.5.7　COS——計算給定角度的餘弦值　93

案例54　計算給定角度的餘弦值(COS+ROUND)　93

2.5.8　ACOS——計算數字的反餘弦值　93

案例55　計算數字的反餘弦值(ACOS+ROUND)　94

2.5.9　COSH——計算數字的雙曲餘弦值　94

案例56　計算數字的雙曲餘弦值(COSH+ROUND)　94

2.5.10　ACOSH——計算數字的反雙曲餘弦值　95

案例57　計算數字的反雙曲餘弦值(ACOSH+ROUND)　95

2.5.11　TAN——計算給定角度的正切值　95

案例58　計算給定角度的正切值(TAN+ROUND)　96

2.5.12　ATAN——計算數字的反正切值　96

案例59　計算數字的反正切值(ATAN+ROUND)　96

2.5.13　TANH——計算數字的雙曲正切值　97

案例60　計算數字的雙曲正切值(TANH+ROUND)　97

2.5.14　ATANH——計算數字的反雙曲正切值　97

案例61　計算數字的反雙曲正切值(ATANH+ROUND)　98

2.5.15　ATAN2——計算給定坐標的反正切值　98

案例62　計算給定坐標的反正切值　99

2.5.16　ACOT——計算數字的反餘切值　99

案例63　計算數字的反餘切值(ACOT+ROUND)　99

2.5.17　ACOTH——計算數字的反雙曲餘切值　100

案例64　計算數字的反雙曲餘切值(ACOTH+ROUND)　100

2.5.18　COT——計算給定角度的餘切值　100

案例65　計算給定角度的餘切值　101

2.5.19　COTH——計算數字的雙曲餘切值　101

案例66　計算數字的雙曲餘切值(COTH+ROUND)　102

2.5.20　SEC——計算給定角度的正割值　102

案例67　計算給定角度的正割值(SEC+ROUND)　102

2.5.21　SECH——計算給定角度的雙曲正割值　103

案例68　計算給定角度的雙曲正割值(SECH+ROUND)　103

2.5.22　CSC——計算給定角度的餘割值　103

案例69　計算給定角度的餘割值(CSC+ROUND)　104

2.5.23　CSCH——計算給定角度的雙曲餘割值　104

案例70　計算給定角度的雙曲餘割值(CSCH+ROUND)　105

2.6　其他計算　105

2.6.1　PI——返回pi的值　105

案例71　計算圓周長(PI+ROUND)　105

2.6.2　SQRTPI——計算某數與pi的乘積的平方根　105

案例72　計算圓周率倍數的平方根　106

2.6.3　SUBTOTAL——返回指定區域的分類匯總結果　106

案例73　匯總某部門員工工資情況(一)　108

案例74　設定不間斷序號　108

2.6.4　AGGREGATE——返回指定區域的分類匯總結果　108

案例75　匯總某部門員工工資情況(二)　110

案例76　統計某部門年薪排在第 2位的員工姓名(AGGREGATE+INDEX+MATCH)　111

2.6.5　ROMAN——將阿拉伯數字轉為文本型羅馬數字　112

案例77　將人員編號轉換為羅馬數字格式(ROMAN+CHOOSE)　113

2.6.6　ARABIC——將羅馬數字轉換為阿拉伯數字　113

案例78　將人員編號轉換為阿拉伯數字格式(ARABIC+CHOOSE)　114

2.6.7　BASE——將一個數轉換為給定基數的文本格式　114

案例79　以給定基數轉換數字　115

2.6.8　DECIMAL——將給定基數的文本轉換為十進制數　115

案例80　將給定基數的文本轉換為十進制數　116

第3章　日期和時間函式　117

3.1　了解Excel日期系統　118

3.1.1　Excel提供的兩種日期系統　118

3.1.2　了解日期和時間序列號　120

3.1.3　輸入與設定日期和時間　120

3.1.4　兩位數年份問題　121

3.2　返回當前的日期、時間和指定的日期、時間　122

3.2.1　NOW——返回當前日期和時間　122

案例81　統計員工在職時間(NOW+ROUND+IF)　122

案例82　元旦倒 計時(NOW+TEXT)　123

3.2.2　TODAY——返回當前日期　124

案例83　統計試用期到期的人數(TODAY+COUNTIF)　124

3.2.3　DATE——返回指定日期的序列號　125

案例84　計算2009年星期六的個數(DATE+SUM+N+TEXT+ROW+INDIRECT)　125

案例85　計算本月的天數(DATE+TEXT+YEAR+MONTH+TODAY)　126

3.2.4　TIME——返回指定時間的序列號　126

案例86　安排會議時間(TIME+TEXT+NOW)　127

3.3　返回日期和時間的某個部分　127

3.3.1　YEAR——返回年份　127

案例87　計算2000年以後的員工平均工資(YEAR+ROUND+AVERAGE+IF)　128

3.3.2　MONTH——返回月份　128

案例88　計算本月需要結算的金額(MONTH+TODAY+SUM+IF)　129

案例89　判斷閏年(MONTH+IF+DATE+YEAR)　129

3.3.3　DAY——返回日期中具體的某一 天　130

案例90　計算本月下旬需要結算的金額(DAY+SUM+IF)　130

3.3.4　WEEKDAY——返回當前日期是星期幾　131

案例91　統計周五的商品銷量(WEEKDAY+SUM+IF)　132

3.3.5　HOUR——返回小時數　132

案例92　計算用餐時間　133

3.3.6　MINUTE——返回分鐘數　133

案例93　計算用餐的精 確時間(HOUR+MINUTE)　133

3.3.7　SECOND——返回秒數　134

案例94　計算廣告時長　134

3.4　文本與日期、時間格式間的轉換　135

3.4.1　DATEVALUE——將文本格式的日期轉換為序列號　135

案例95　計算兩個月之間相差的天數　135

3.4.2　TIMEVALUE——將文本格式的時間轉換為序列號　135

案例96　計算加班費用(TIMEVALUE+SUBSTITUTE+ROUND)　136

3.5　其他日期函式　136

3.5.1　DATEDIF——計算開始和結束日期之間的時間間隔　136

案例97　統計辦公用品使用情況(DATEDIF+TODAY)　137

3.5.2　DAYS360——以360天為準計算兩個日期間天數　138

案例98　計算還款天數(一)　138

3.5.3　DAYS——計算兩個日期之間的天數　139

案例99　計算還款天數(二)　139

3.5.4　EDATE——計算從指定日期向前或向後幾個月的日期　139

案例100　計算還款日期(EDATE+TEXT+LEFT+LEN)　140

3.5.5　DATESTRING——將指定日期的序列號轉換為文本日期　141

案例101　計算還款日期(DATESTRING+EDATE+LEFT+LEN)　141

3.5.6　EOMONTH——計算從指定日期向前或向後幾個月後的那個月**後一 天的日期　142

案例102　統計員工的工資結算日期(EOMONTH+TEXT)　142

3.5.7　NETWORKDAYS——計算日期間的所有工作日數　143

案例103　計算工程施工的天數(一)　143

3.5.8　NETWORKDAYS.INTL——計算日期間的所有工作日數，使用參數指明周 末的日期和天數　144

案例104　計算工程施工的天數(二)　145

3.5.9　WEEKNUM——返回日期在一年中是第幾周　146

案例105　計算本月包含的周數(WEEKNUM+EOMONTH+NOW)　147

3.5.10　ISOWEEKNUM——返回日期在一年中的ISO周數　147

3.5.11　WORKDAY——計算與指定日期相隔數個工作日的日期　148

案例106　安排施工時間(一)(WORKDAY+TEXT+LEFT+LEN)　148

3.5.12　WORKDAY.INTL——計算與指定日期相隔數個工作日的日期，使用參數指明周 末的日期和天數　149

案例107　安排施工時間(二)(WORKDAY.INTL+TEXT+LEFT+LEN)　151

3.5.13　YEARFRAC——計算從開始日期到結束日期所經歷的天數占全年天數的百分比　151

案例108　計算全年盈利額(YEARFRAC+ROUND)　152

第4章　邏輯函式　153

4.1　邏輯值函式　153

4.1.1　TRUE——返回邏輯值TRUE　153

案例109　判斷兩列數據是否相同　154

4.1.2　FALSE——返回邏輯值FALSE　154

案例110　計算兩列中相同數據的個數　154

4.2　條件判斷函式　155

4.2.1　NOT——對邏輯值求反　155

案例111　計算區域內數字的個數(NOT+SUM+ISERROR)　155

4.2.2　AND——判斷多個條件是否同時成立　156

案例112　判斷面試人員是否被錄取　156

案例113　判斷是否為員工發放獎金　157

4.2.3　OR——判斷多個條件中是否有一個條件成立　157

案例114　判斷身份證號碼的長度是否正確(OR+LEN)　158

4.2.4　XOR——判斷多個條件中是否有一個條件成立　158

4.2.5　IF——根據條件判斷而返回不同結果　159

案例115　評定員工業績　159

案例116　計算需要發放獎金的總額(IF+SUM)　160

4.2.6　IFNA——判斷公式是否出現“#N/A”錯誤　160

案例117　根據姓名查找員工所在部門並排錯(IFNA+VLOOKUP)　161

4.2.7　IFERROR——根據公式結果返回不同內容　161

案例118　根據編號查找員工信息並排錯(IFERROR+VLOOKUP)　162

第5章　文本函式　163

5.1　返回字元或字元編碼　165

5.1.1　CHAR——返回與ANSI字元編碼對應的字元　165

案例119　在區域中自動生成大寫字母列表(CHAR+ROW)　165

5.1.2　UNICHAR——返回與Unicode字元編碼對應的字元　166

5.1.3　CODE——返回與字元對應的ANSI字元編碼　166

案例120　判斷單元格第 一個字元是否為字母(CODE+IF+OR+AND)　166

案例121　按升序排列順序錯亂的字母列表(CODE+CHAR+SMALL+ROW)　167

5.1.4　UNICODE——返回與字元對應的Unicode字元編碼　167

5.2　返回文本內容　168

5.2.1　LEFT——從文本左側起提取指定個數的字元　168

案例122　提取地址中的省市名稱(LEFT+FIND)　168

案例123　統計各地區參會人數(LEFT+SUM)　169

5.2.2　LEFTB——從文本左側起提取指定位元組數字元　170

5.2.3　LEN——計算文本中的字元個數　170

案例124　計算單元格中的數字個數(LEN+LENB)　170

案例125　根據身份證號判斷性別(LEN+IF+MOD+RIGHT+MID)　171

5.2.4　LENB——計算文本中代表字元的位元組數　172

5.2.5　MID——從文本指定位置起提取指定個數的字元　172

案例126　提取公司獲獎人員姓名(MID+FIND+LEN)　172

案例127　從身份證號中提取生日(MID+TEXT+IF+LEN)　173

5.2.6　MIDB——從文本指定位置起提取指定位元組數的字元　173

5.2.7　RIGHT——從文本右側起提取指定個數的字元　174

案例128　動態提取公司名稱列表(RIGHT+LEN+FIND)　174

5.2.8　RIGHTB——從文本右側起提取指定位元組數字元　175

5.2.9　REPT——生成重複的字元　175

案例129　自動為數字添加星號(REPT+LEN)　176

案例130　製作簡易的銷售圖表　177

5.3　合併文本　177

5.3.1　CONCATENATE——將多個文本合併到一處　177

案例131　評定員工考核成績(CONCATENATE+IF+SUM)　177

5.4　轉換文本格式　178

5.4.1　ASC——將全形字元轉換為半角字元　178

案例132　計算全、半角混合文本中的字母個數(ASC+LEN+LENB)　178

5.4.2　WIDECHAR——將半角字元轉換為全形字元　179

案例133　計算全、半角混合文本中的漢字個數(WIDECHAR+LEN+LENB+ASC)　179

5.4.3　PHONETIC——返回文本中的拼音字元　180

5.4.4　BAHTTEXT——將數字轉換為泰語文本　180

5.4.5　DOLLAR——將數字轉換為帶美元符號＄的文本　180

案例134　出口商品價格轉換　180

5.4.6　RMB——將數字轉換為帶人民幣符號￥的文本　181

案例135　將數字轉換為帶人民幣符號￥的文本　181

5.4.7　NUMBERSTRING——將數值轉換為大寫漢字　182

案例136　將銷售額轉換為中文大寫漢字　182

5.4.8　NUMBERVALUE——以與區域設定無關的方式將文本轉換為數字　183

5.4.9　T——將參數轉換為文本　183

案例137　為公式添加文字說明(RMB+T+N)　184

5.4.10　LOWER——將文本轉換為小寫　184

5.4.11　UPPER——將文本轉換為大寫　184

案例138　將文本轉換為句首字母大寫其他小寫(UPPER+LEFT+LOWER+RIGHT+LEN)　185

5.4.12　PROPER——將文本中每個單詞的首字母轉換為大寫　185

案例139　整理人名數據　186

5.4.13　VALUE—將文本格式的數字轉換為普通數字　186

案例140　對文本格式的銷售數據求和(VALUE+SUM)　186

5.4.14　TEXT——多樣化格式設定函式　187

案例141　將金額顯示為特定的單位(TEXT+SUM)　187

案例142　將普通數字轉換為電話格式　187

案例143　自動生成12個月的英文月份名稱(TEXT+ROW)　188

5.4.15　FIXED——將數字按指定的小數位數取整　188

案例144　格式化數字格式　189

5.5　查找與替換文本　189

5.5.1　EXACT——比較兩個文本是否相同　189

案例145　核對數據錄入是否正確(EXACT+IF)　189

5.5.2　FIND——以字元為單位並區分大小寫地查找指定字元的位置　190

案例146　檢查聯繫地址是否詳細(FIND+ISERROR+IF)　190

案例147　提取公司名稱(FIND+MID)　191

5.5.3　FINDB——以位元組為單位並區分大小寫地查找指定字元的位置　191

5.5.4　REPLACE——以字元為單位根據指定位置進行替換　192

案例148　電話號碼位數升級　192

案例149　提取地區公司名稱(REPLACE+LEFT+FIND)　193

5.5.5　REPLACEB——以位元組為單位根據指定位置進行替換　193

5.5.6　SEARCH——以字元為單位不區分大小寫地查找指定字元的位置　194

案例150　將銷售圖書分類上架(SEARCH+IF+COUNT)　194

5.5.7　SEARCHB——以位元組為單位不區分大小寫地查找指定字元的位置　195

5.5.8　SUBSTITUTE——以指定文本進行替換　196

案例151　將日期轉換為標準格式　196

案例152　格式化公司名稱(SUBSTITUTE+REPLACE)　197

5.6　刪除文本中的字元　197

5.6.1　CLEAN——刪除無法列印的字元　197

案例153　對表格內容排版　197

5.6.2　TRIM——刪除多餘的空格　198

案例154　整理不規則數據(TRIM+CLEAN)　198

第6章　查找和引用函式　199

6.1　查找表中數據　200

6.1.1　CHOOSE——根據序號從列表中選擇對應的內容　200

案例155　評定員工業績(CHOOSE+IF)　201

案例156　製作購物打折收費系統　202

案例157　重組銷售數據　204

案例158　在多組銷售數據中查找銷量(CHOOSE+VLOOKUP+MATCH)　205

6.1.2　LOOKUP——僅在單行單列中查找(向量形式)　206

案例159　根據姓名查找員工編號　207

案例160　使用LOOKUP函式進行多條件判斷　207

案例161　提取文本中的金額(LOOKUP+LEFT+ROW+INDIRECT+LEN)　208

6.1.3　LOOKUP——僅在單行單列中查找(數組形式)　208

案例162　查找員工信息　209

6.1.4　HLOOKUP——在區域或數組的行中查找數據　209

案例163　查找商品在某季度的銷量(HLOOKUP+MATCH)　211

6.1.5　VLOOKUP——在區域或數組的列中查找數據　211

案例164　根據商品名稱查找銷量　212

案例165　根據銷量對員工進行評定　213

案例166　從多個表中計算員工的年終獎(VLOOKUP+IF)　213

案例167　逆向查找(VLOOKUP+IF)　214

6.1.6　MATCH——返回指定內容所在的位置　215

案例168　不區分大小寫查找數據(MATCH+INDEX)　216

案例169　區分大小寫查找數據(MATCH+INDEX+EXACT)　216

案例170　雙重定位查找數據(MATCH+INDEX)　217

案例171　提取某商品**後一次進貨日期(MATCH+TEXT+INDIRECT)　218

案例172　從多列數據中查找員工工資(MATCH+INDEX)　218

6.1.7　INDEX——返回指定位置中的內容(數組形式)　219

案例173　查找左側列中的數據(INDEX+MATCH)　220

6.1.8　INDEX——返回指定位置中的內容(引用形式)　220

案例174　提取某區域的銷售數據(INDEX+MATCH)　221

6.2　引用表中數據　221

6.2.1　ADDRESS——返回與指定行號和列號對應的單元格地址　221

案例175　確定**大銷售額的位置(ADDRESS+MAX+IF+ROW)　222

案例176　返回當前單元格的列字母(ADDRESS+COLUMN+SUBSTITUTE)　223

案例177　跨工作表返回匯總金額(ADDRESS+INDIRECT)　223

6.2.2　AREAS——返回引用中包含的區域數量　225

案例178　統計分公司數量　225

6.2.3　COLUMN——返回單元格或單元格區域首列的列號　225

案例179　在一行中快速輸入月份(COLUMN+TEXT)　226

案例180　匯總多個列中的銷量(COLUMN+SUM+IF+MOD)　226

6.2.4　COLUMNS——返回數據區域包含的列數　227

案例181　計算需要扣款的項目數量　227

6.2.5　ROW——返回單元格或單元格區域首行的行號　227

案例182　在一列中快速輸入月份(ROW+TEXT)　228

案例183　提取**後一次銷量大於800的日期(ROW+TEXT+INDEX+MAX)　228

案例184　計算員工的**高工資(ROW+MAX+MMULT)　229

6.2.6　ROWS——返回數據區域包含的行數　230

案例185　計算需要開支的員工人數　230

案例186　計算銷售數據中的報價數量(ROWS+COLUMNS)　231

案例187　檢驗日期是否重複(ROWS+IF+MATCH)　231

6.2.7　OFFSET——根據給定的偏移量返回新的引用區域　232

案例188　使用OFFSET函式查詢員工信息(OFFSET+MATCH)　233

案例189　對每日銷量累積求和(OFFSET+SUM+ROW)　234

案例190　匯總**近5天的銷量(OFFSET+SUBTOTAL+INDIRECT+MAX+ROW)　234

案例191　製作工資條(OFFSET+IF+MOD+ROW+ROUND+COLUMN)　235

6.2.8　TRANSPOSE——轉置數據區域的行列位置　237

案例192　轉換銷售數據區域　237

6.2.9　INDIRECT——返回由文本值指定的引用　238

案例193　統計銷量小於600的員工數(INDIRECT+SUM+COUNTIF)　239

案例194　提取多個工作表中的**大值(INDIRECT+SUBTOTAL)　239

6.2.10　FORMULATEXT——返回給定引用公式的文本形式　240

案例195　提取單元格中的公式(一)　241

6.2.11　GETPIVOTDATA——返回數據透視表中的數據　241

案例196　使用函式從數據透視表中提取數據　242

6.2.12　HYPERLINK——為指定內容創建超連結　243

案例197　在銷售數據表中指定公司郵件地址　243

案例198　實現自動跳轉到數據結尾的功能(HYPERLINK+OFFSET+COUNTA)　244

6.2.13　RTD——返回支持COM自動化程式的實時數據　245

案例199　使用COM載入宏快速顯示時間　246

第7章　信息函式　247

7.1　返回信息　248

7.1.1　CELL——返回單元格格式、位置或內容的信息　248

案例200　獲取當前工作簿的完整路徑　250

7.1.2　INFO——返回當前操作環境的相關信息　250

案例201　獲取當前操作環境　251

7.1.3　SHEET——返回引用工作表的工作表編號　252

7.1.4　SHEETS——返回引用中的工作表總數　252

案例202　統計當前工作簿包含的工作表總數(一)　253

7.2　返回各類型的值　253

7.2.1　ERROR.TYPE——返回對應於錯誤類型的數字　253

案例203　根據錯誤代碼顯示錯誤原因(ERROR.TYPE+CHOOSE)　254

7.2.2　N——返迴轉換為數字的值　254

案例204　生成指定員工的年薪列表(N+OFFSET)　255

7.2.3　NA——返回錯誤值“#N/A”　255

案例205　統計無貨商品的數量　255

7.2.4　TYPE——返回表示數據類型的數字　256

案例206　簡易的輸入驗證工具(TYPE+LOOKUP)　256

7.3　使用IS函式進行各種判斷　257

7.3.1　ISBLANK——判斷單元格是否為空　257

案例207　統計員工缺勤人數(ISBLANK+SUM)　257

7.3.2　ISLOGICAL——判斷值是否為邏輯值　258

7.3.3　ISNUMBER——判斷值是否為數字　258

案例208　統計指定商品的銷量總和(ISNUMBER+SUM+FIND)　258

7.3.4　ISTEXT——判斷值是否為文本　259

案例209　判斷員工是否已簽到(ISTEXT+IF)　259

7.3.5　ISNONTEXT——判斷值是否為非文本　259

案例210　判斷員工是否已簽到(ISNONTEXT+IF)　259

7.3.6　ISFORMULA——判斷單元格是否包含公式　260

案例211　判斷單元格中的內容是否包含公式　260

7.3.7　ISEVEN——判斷數字是否為偶數　261

案例212　統計公司女員工人數(ISEVEN+SUM+MID)　261

7.3.8　ISODD——判斷數字是否為奇數　261

案例213　統計公司男員工人數(ISODD+SUM+MID)　262

7.3.9　ISNA——判斷值是否為錯誤值“#N/A”　262

案例214　查找員工信息(ISNA+IF+VLOOKUP)　263

7.3.10　ISREF——判斷值是否為單元格引用　263

7.3.11　ISERR——判斷值是否為除“#N/A”以外的其他錯誤值　264

7.3.12　ISERROR——判斷值是否為錯誤值　264

案例215　統計員工年薪總和(ISERROR+SUM+IF)　264

第8章　統計函式　265

8.1　統計數量與頻率　269

8.1.1　COUNT——計算參數中包含數字的個數　269

案例216　統計員工人數　270

案例217　統計不重複員工人數(COUNT+MATCH+ROW)　271

8.1.2　COUNTA——計算參數中包含非空值的個數　271

案例218　統計遲到人數　272

案例219　創建動態區域名稱(COUNTA+OFFSET)　272

8.1.3　COUNTBLANK——計算區域中空白單元格的個數　273

案例220　統計會議未到場人數　273

8.1.4　COUNTIF——計算滿足給定條件的單元格的個數　274

案例221　統計銷量大於800的員工人數　274

案例222　計算兩列數據中相同數據的個數(COUNTIF+SUM)　274

案例223　統計不重複員工人數(COUNTIF+SUM)　275

案例224　提取不重複員工姓名(COUNTIF+LOOKUP+NOT)　275

8.1.5　COUNTIFS——計算滿足多個給定條件的單元格的個數　276

案例225　統計銷量在600到1000之間的男員工人數　276

8.1.6　FREQUENCY——以垂直數組形式返回數據的頻率分布　277

案例226　統計不同銷量區間的員工人數　278

8.2　統計均值和眾數　278

8.2.1　AVEDEV——計算一組數據與其平均值的絕 對偏差的平均值　278

案例227　計算零件質量係數的平均偏差　279

8.2.2　AVERAGE——計算參數的平均值　279

案例228　計算某商品的平均價格　279

案例229　計算男員工的平均銷量(一)(AVERAGE+ROUND+IF)　280

8.2.3　AVERAGEA——計算參數中非空值的平均值　280

案例230　計算員工的平均銷量(含未統計者)(AVERAGEA+ROUND)　281

8.2.4　AVERAGEIF——計算滿足給定條件的所有單元格的平均值　281

案例231　計算男員工的平均銷量(二)(AVERAGEIF+ROUND)　282

8.2.5　AVERAGEIFS——計算滿足多個給定條件的所有單元格的平均值　283

案例232　計算銷量大於600的男員工的平均銷量(AVERAGEIFS+ROUND)　284

8.2.6　GEOMEAN——計算幾何平均值　284

案例233　計算銷售業績的平均增長率　285

8.2.7　HARMEAN——計算調和平均值　285

案例234　計算從第 一 天開始到第三天每天的平均產量　286

8.2.8　TRIMMEAN——計算內部平均值　286

案例235　計算選手**後得分(TRIMMEAN+ROUND)　286

8.2.9　MEDIAN——返回中值　287

案例236　計算銷量的中間值　288

案例237　計算銷量的中間值所在的日期(MEDIAN+INDEX+MATCH)　288

8.2.10　MODE.SNGL——返回數據中出現次數**多的值　289

案例238　統計投票次數**多的選手(一)　289

案例239　提取出現次數**多的數字(MODE.SNGL+MID+ROW+INDIRECT+LEN)　289

8.2.11　MODE.MULT——返回數據中出現頻率**高或重複出現的數值的垂直數組　290

案例240　統計投票次數**多的選手(二)　291

8.3　統計極值與排位　291

8.3.1　MAX——返回一組數字中的**大值　291

案例241　統計男員工中完成的**大銷量　292

案例242　計算單日**高銷量(MAX+SUMIF)　292

案例243　提取**後一次出貨的日期(MAX+TEXT+INDEX+IF+ROW)　292

8.3.2　MAXA——返回一組非空值中的**大值　293

8.3.3　MIN——返回一組數字中的**小值　294

案例244　顯示一個不超過限定日期的動態時間(MIN+TODAY)　294

案例245　提取完成**小銷量的員工姓名(MIN+INDEX+MATCH)　294

8.3.4　MINA——返回一組非空值中的**小值　295

8.3.5　LARGE——返回數據集中第k個**大值　296

案例246　計算銷量前3名的銷量總和(LARGE+SUM)　296

8.3.6　SMALL—返回數據集中第k個**小值　296

案例247　計算銷量後3名的銷量總和(SMALL+SUM)　297

8.3.7　RANK.EQ——返回一個數字在一組數字中的排位　297

案例248　對員工銷量降序排名(一)　298

案例249　計算兩列數據中相同數據的個數(RANK.EQ+COUNT)　298

8.3.8　RANK.AVG——返回一個數字在一組數字中的排位　299

案例250　對員工銷量降序排名(二)　299

8.3.9　PERCENTRANK.INC——返回數據集中值的百分比排位　300

案例251　計算某個銷售員在所有銷售員中的銷量百分比排位　300

8.3.10　PERCENTRANK.EXC——返回數據集中值的百分比排位　301

8.3.11　QUARTILE.INC——返回數據集的四分位數　301

案例252　根據員工銷量計算四分位數　302

8.3.12　QUARTILE.EXC——返回數據集的四分位數　302

8.3.13　PERCENTILE.INC——返回區域中數值的第k個百分點的值　303

案例253　根據員工銷量計算指定百分點的值　304

8.3.14　PERCENTILE.EXC——返回區域中數值的第k個百分點的值　304

8.3.15　PERMUT——返回給定數目對象的排列數　305

案例254　計算中獎率　305

8.3.16　PERMUTATIONA——返回給定數目對象(含重複)的排列數　306

8.4　統計數據的散布度　306

8.4.1　DEVSQ——計算偏差的平方和　306

案例255　計算零件質量係數的偏差平方和　307

8.4.2　STDEV.S——估算基於樣本的標準偏差，忽略文本和邏輯值　307

案例256　計算員工工齡樣本的標準偏差　307

8.4.3　STDEVA——估算基於樣本的標準偏差，包括文本和邏輯值　308

案例257　計算員工工齡樣本的標準偏差(含未統計者)　308

8.4.4　STDEV.P——估算基於整個樣本總體的標準偏差，忽略文本和邏輯值　309

案例258　計算員工工齡樣本總體的標準偏差　310

8.4.5　STDEVPA——估算基於整個樣本總體的標準偏差，包括文本和邏輯值　310

案例259　計算員工工齡樣本總體的標準偏差(含未統計者)　311

8.4.6　VAR.S——計算基於給定樣本的方差，忽略文本和邏輯值　311

案例260　計算員工工齡樣本的方差　312

8.4.7　VARA——計算基於給定樣本的方差，包括文本和邏輯值　312

案例261　計算員工工齡樣本的方差(含未統計者)　312

8.4.8　VAR.P——計算基於整個樣本總體的方差，忽略文本和邏輯值　313

案例262　計算員工工齡樣本總體的方差　313

8.4.9　VARPA——計算基於整個樣本總體的方差，包括文本和邏輯值　314

案例263　計算員工工齡樣本總體的方差(含未統計者)　314

8.4.10　KURT——返回數據集的峰值　315

案例264　計算商品在一段時期內價格的峰值　315

8.4.11　SKEW——返回分布的不對稱度　316

案例265　計算商品在一段時期內價格的不對稱度　316

8.4.12　SKEW.P——返回某一分布相對其平均值的不對稱度　316

8.5　統計機率分布　317

8.5.1　BINOM.DIST——返回一元二項式分布的機率　317

案例266　計算沒有不合格產品的機率　318

8.5.2　BINOM.INV——返回使累積二項式分布小於或等於臨界值的**小值　318

案例267　計算允許不合格產品的數量　319

8.5.3　BINOM.DIST.RANGE——返回二項式分布試驗結果的機率　319

8.5.4　NEGBINOM.DIST——返回負二項式分布的機率　320

案例268　計算談判成功的機率　321

8.5.5　PROB——返回區域中的數值落在指定區間內的機率　321

案例269　計算中獎機率　322

8.5.6　GAUSS——返回比標準正態累積分布函式小0.5的值　322

案例270　計算比標準正態累積分布函式小0.5的值　322

8.5.7　PHI——返回標準常態分配的密度函式值　323

案例271　計算標準常態分配的密度函式值　323

8.5.8　NORM.DIST——返回正態累積分布函式　323

案例272　計算機率密度函式的值　324

8.5.9　NORM.INV——返回標準正態累積分布的反函式值　324

案例273　計算累積分布函式的反函式的值　325

8.5.10　NORM.S.DIST——返回標準正態累積分布函式　325

案例274　製作常態分配表　326

8.5.11　NORM.S.INV——返回標準正態累積分布函式的反函式值　326

案例275　計算標準常態分配函式的反函式　327

8.5.12　STANDARDIZE——返回正態化數值　327

案例276　計算正態化數值　327

8.5.13　LOGNORM.DIST——返回對數累積分布函式　329

案例277　計算對數累積分布函式的值　329

8.5.14　LOGNORM.INV——返回對數累積分布函式的反函式　330

案例278　計算對數累積分布函式的反函式的值　330

8.5.15　HYPGEOM.DIST——返回超幾何分布　331

案例279　計算沒有不合格產品的機率　332

8.5.16　POISSON.DIST——返回泊松分布　332

案例280　計算產品不發生故障的機率　333

8.5.17　EXPON.DIST——返回指數分布　333

案例281　計算在經過指定期限後，產品在兩家公司發生故障的機率　333

8.5.18　WEIBULL.DIST——返回韋伯分布　334

案例282　計算產品的不同故障發生的機率　335

8.5.19　GAMMA——返回伽瑪函式值　335

案例283　計算伽瑪函式值　336

8.5.20　GAMMA.DIST——返回伽瑪分布函式　336

案例284　計算伽瑪分布函式的值　337

8.5.21　GAMMA.INV——返回伽瑪累積分布函式的反函式值　337

案例285　計算伽瑪分布函式的反函式的值　338

8.5.22　GAMMALN——返回伽瑪函式的自然對數　338

案例286　計算伽瑪函式的自然對數值　338

8.5.23　GAMMALN.PRECISE——返回伽瑪函式的自然對數　339

案例287　計算伽瑪函式的自然對數值　339

8.5.24　BETA.DIST——返回β累積分布函式　339

案例288　計算β累積分布函式的值　340

8.5.25　BETA.INV——返回指定β累積分布函式的反函式值　340

案例289　計算β累積分布函式的反函式的值　341

8.5.26　CONFIDENCE.NORM——返回總體平均值的置信區間　341

案例290　計算平均視力的95％置信區間　342

8.5.27　CONFIDENCE.T——返回總體平均值的置信區間　343

8.6　檢驗數據的傾向性　343

8.6.1　CHISQ.DIST.RT——返回x2分布的右尾機率　343

案例291　計算x2分布的單尾機率　344

8.6.2　CHISQ.DIST——返回x2分布　344

8.6.3　CHISQ.INV.RT——返回x2分布的右尾機率的反函式值　345

案例292　計算x2分布的單尾機率的反函式　345

8.6.4　CHISQ.INV——返回x2分布的左尾機率的反函式值　346

8.6.5　CHISQ.TEST——返回獨立性檢驗值　346

案例293　檢驗電腦用戶與患頸椎病的關係　347

8.6.6　F.DIST.RT——返回F機率分布　347

案例294　計算F分布的機率　348

8.6.7　F.DIST——返回F機率分布　348

8.6.8　F.INV.RT——返回F機率分布的反函式值　349

案例295　計算F累積分布機率的反函式　349

8.6.9　F.INV——返回F機率分布的反函式值　349

8.6.10　T.DIST.2T——返回t分布的雙尾機率　350

案例296　計算t分布的機率　351

8.6.11　T.DIST.RT——返回t分布的右尾機率　351

8.6.12　T.DIST——返回t分布　351

8.6.13　T.INV.2T——返回t分布的雙尾反函式值　352

案例297　計算t分布機率的反函式　353

8.6.14　T.INV——返回t分布的左尾反函式值　353

8.6.15　F.TEST——返回F檢驗的結果　353

案例298　檢驗空調和冰櫃耗電量的方差　354

8.6.16　T.TEST——返回與t檢驗相關的機率　354

案例299　檢驗空調和冰櫃耗電量的平均值　355

8.6.17　Z.TEST——返回z檢驗的單尾機率　356

案例300　檢驗2009年度與5年前商品銷量的平均記錄　357

8.7　統計協方差、相關係數與回歸　357

8.7.1　COVARIANCE.P——計算總體協方差，即成對偏差乘積的平均值　357

案例301　計算以年齡和視力為樣本數據的協方差　358

8.7.2　COVARIANCE.S——計算樣本協方差，即成對偏差乘積的平均值　358

案例302　計算以年齡和視力為樣本數據的協方差　359

8.7.3　CORREL——返回兩個數據集之間的相關係數　359

案例303　計算年齡和視力的相關係數　359

8.7.4　FISHER——返回Fisher變換值　360

案例304　計算Fisher變換的相關係數(FISHER+CORREL)　360

8.7.5　FISHERINV——返回Fisher變換的反函式值　360

案例305　計算Fisher變換的反函式　361

8.7.6　PEARSON——返回皮爾生乘積矩相關係數　361

案例306　計算年齡和視力的皮爾生乘積矩相關係數　362

8.7.7　RSQ——返回皮爾生乘積矩相關係數的平方　362

案例307　計算年齡和視力的皮爾生乘積矩相關係數的平方　363

8.7.8　FORECAST——根據現有的數據計算或預測未來值　363

案例308　預測特定年齡的視力　364

8.7.9　GROWTH——根據現有的數據計算或預測指數增長值　364

案例309　預測未來月份的銷量　365

8.7.10　TREND——計算一條線性回歸線的值　365

案例310　預測回歸線上的視力　366

8.7.11　LINEST——返回線性回歸線的參數　366

案例311　根據年齡和視力求線性回歸線　367

8.7.12　LOGEST——返回指數回歸線的參數　368

案例312　根據時間和銷量求指數回歸線　369

8.7.13　SLOPE——返回線性回歸線的斜率　369

案例313　從年齡和視力中求回歸線的斜率　370

8.7.14　INTERCEPT——返回線性回歸線的截距　370

案例314　從年齡和視力中求回歸線的截距　371

8.7.15　STEYX——返回通過線性回歸法預測每個x的y值時所產生的標準誤差　371

案例315　從年齡和視力中求回歸線的標準誤差　372

第9章　財務函式　373

9.1　了解貨幣的時間價值　375

9.2　計算本金和利息　376

9.2.1　PMT——計算貸款的每期付款額　376

案例316　計算貸款的每期付款額　377

9.2.2　IPMT——計算貸款在給定期間內支付的利息　377

案例317　計算貸款在給定期間內支付的利息　378

9.2.3　PPMT——計算貸款在給定期間內償還的本金　378

案例318　計算貸款在給定期間內償還的本金　379

9.2.4　ISPMT——計算特定投資期內支付的利息　379

案例319　計算特定投資期內支付的利息　380

9.2.5　CUMIPMT——計算兩個付款期之間累積支付的利息　380

案例320　計算兩個付款期之間累積支付的利息　381

9.2.6　CUMPRINC——計算兩個付款期之間累積支付的本金　381

案例321　計算兩個付款期之間累積支付的本金　382

9.2.7　EFFECT——將名義年利率轉換為實際年利率　383

案例322　將名義年利率轉換為實際年利率　383

9.2.8　NOMINAL——將實際年利率轉換為名義年利率　383

案例323　將實際年利率轉換為名義年利率　384

9.2.9　RATE——計算年金的各期利率　384

案例324　計算年金的各期利率　385

9.3　計算投資預算　385

9.3.1　FV——計算一筆投資的未來值　385

案例325　計算一筆投資的未來值　386

9.3.2　FVSCHEDULE——使用一系列複利率計算初始本金的未來值　386

案例326　使用一系列複利率計算初始本金的未來值　387

9.3.3　NPER——計算投資的期數　387

案例327　計算投資的期數(NPER+ROUNDUP)　387

9.3.4　PDURATION——計算投資到達指定值所需的期數　388

案例328　計算投資到達指定值所需的期數(PDURATION+ROUNDUP)　388

9.3.5　PV——計算投資的現值　389

案例329　計算投資的現值　389

9.3.6　NPV——基於一系列定期的現金流和貼現率計算投資的淨現值　389

案例330　計算投資的淨現值　390

9.3.7　XNPV——計算一組未必定期發生的現金流的淨現值　390

案例331　計算未必定期發生的投資的淨現值　391

9.3.8　RRI——計算某項投資增長的等效利率　391

案例332　計算某項投資增長的等效利率　392

9.4　計算收益率　392

9.4.1　IRR——計算一系列現金流的內部收益率　392

案例333　計算一系列現金流的內部收益率　392

9.4.2　MIRR——計算正負現金流在不同利率下支付的內部收益率　393

案例334　計算在不同利率下支付的修正內部收益率　393

9.4.3　XIRR——計算一組未必定期發生的現金流的內部收益率　394

案例335　計算未必定期發生的現金流的內部收益率　394

9.5　計算折舊值　395

9.5.1　AMORDEGRC——根據資產的耐用年限，計算每個結算期間的折舊值　395

案例336　計算每個結算期間的餘額遞減折舊值　396

9.5.2　AMORLINC——計算每個結算期間的折舊值　396

案例337　計算每個結算期間的餘額遞減折舊值　397

9.5.3　DB——使用固定餘額遞減法，計算一筆資產在給定期間內的折舊值　398

案例338　使用固定餘額遞減法計算折舊值(DB+ROW)　398

9.5.4　DDB——使用雙倍餘額遞減法或其他指定方法，計算一筆資產在給定期間內的折舊值　398

案例339　使用雙倍餘額遞減法計算折舊值(DDB+ROW)　399

9.5.5　VDB——使用餘額遞減法，計算一筆資產在給定期間或部分期間內的折舊值　399

案例340　使用餘額遞減法計算折舊值　400

9.5.6　SYD——計算某項資產按年限總和折舊法計算的指定期間的折舊值　400

案例341　按年限總和折舊法計算折舊值(SYD+ROW)　400

9.5.7　SLN——計算某項資產在一個期間內的線性折舊值　401

案例342　計算期間內的線性折舊值　401

9.6　計算證券與國庫券　401

9.6.1　ACCRINT——計算定期支付利息的有價證券的應計利息　401

案例343　計算定期支付利息的有價證券的應計利息　403

9.6.2　ACCRINTM——計算在到期日支付利息的有價證券的應計利息　403

案例344　計算在到期日支付利息的有價證券的應計利息　404

9.6.3　COUPDAYBS——計算當前付息期內截止到成交日的天數　404

案例345　計算當前付息期內截止到成交日的天數　405

9.6.4　COUPDAYS——計算成交日所在的付息期的天數　405

案例346　計算成交日所在的付息期的天數　406

9.6.5　COUPDAYSNC——計算從成交日到下一個付息日之間的天數　407

案例347　計算從成交日到下一個付息日之間的天數　408

9.6.6　COUPNCD——計算成交日之後的下一個付息日　408

案例348　計算成交日之後的下一個付息日　409

9.6.7　COUPNUM——計算成交日和到期日之間的應付利息次數　409

案例349　計算成交日和到期日之間的應付利息次數　410

9.6.8　COUPPCD——計算成交日之前的上一付息日　411

案例350　計算成交日之前的上一付息日　412

9.6.9　DISC——計算有價證券的貼現率　412

案例351　計算有價證券的貼現率　413

9.6.10　DURATION——計算定期支付利息的有價證券的修正期限　413

案例352　計算定期支付利息的有價證券的修正期限　414

9.6.11　PRICE——計算定期付息的面值￥100的有價證券的價格　415

案例353　計算定期付息的面值￥100的有價證券的價格　416

9.6.12　PRICEDISC——計算折價發行的面值￥100的有價證券的價格　416

案例354　計算折價發行的面值￥100的有價證券的價格　417

9.6.13　PRICEMAT——計算到期付息的面值￥100的有價證券的價格　418

案例355　計算到期付息的面值￥100的有價證券的價格　419

9.6.14　ODDFPRICE——計算首期付息日不固定的面值￥100的有價證券價格　419

案例356　計算首期付息日不固定的面值￥100的有價證券價格　420

9.6.15　ODDFYIELD——計算首期付息日不固定的有價證券的收益率　420

案例357　計算首期付息日不固定的有價證券的收益率　421

9.6.16　ODDLPRICE——計算末期付息日不固定的面值￥100的有價證券價格　422

案例358　計算末期付息日不固定的面值￥100的有價證券價格　423

9.6.17　ODDLYIELD——計算末期付息日不固定的有價證券的收益率　423

案例359　計算末期付息日不固定的有價證券的收益率　424

9.6.18　MDURATION——計算假設面值為￥100的有價證券的Macauley修正期限　425

案例360　計算假設面值為￥100的有價證券的Macauley修正期限　426

9.6.19　INTRATE——計算一次性付息證券的利率　426

案例361　計算一次性付息證券的利率　427

9.6.20　RECEIVED——計算一次性付息的有價證券到期收回的金額　427

案例362　計算一次性付息的有價證券到期收回的金額　428

9.6.21　TBILLEQ——計算國庫券的等價債券收益　429

案例363　計算國庫券的等價債券收益　429

9.6.22　TBILLPRICE——計算面值￥100的國庫券的價格　429

案例364　計算面值￥100的國庫券的價格　430

9.6.23　TBILLYIELD——計算國庫券的收益率　430

案例365　計算國庫券的收益率　431

9.6.24　YIELD——計算定期支付利息的有價證券的收益率　431

案例366　計算定期支付利息的有價證券的收益率　432

9.6.25　YIELDDISC——計算折價發行的有價證券的年收益率　433

案例367　計算折價發行的有價證券的年收益率　434

9.6.26　YIELDMAT——計算到期付息的有價證券的年收益率　434

案例368　計算到期付息的有價證券的年收益率　435

9.7　轉換美元價格的格式　435

9.7.1　DOLLARDE——將以分數表示的美元價格轉換為以小數表示的美元價格　435

案例369　將小數格式的美元轉換為分數格式的美元　436

9.7.2　DOLLARFR——將以小數表示的美元價格轉換為以分數表示的美元價格　436

案例370　將分數格式的美元轉換為小數格式的美元　436

第 10章　資料庫函式　437

10.1　計算資料庫中的數據　438

10.1.1　DPRODUCT——計算滿足條件的數字的乘積　438

案例371　統計商品的維修記錄　438

10.1.2　DCOUNTA——計算滿足條件的數字的總和　439

案例372　計算符合特定條件的員工工資總和　439

10.2　對資料庫中的數據進行常規統計　440

10.2.1　DAVERAGE——計算滿足條件的數值的平均值　440

案例373　計算符合特定條件的員工的平均月薪　441

10.2.2　DCOUNT——計算滿足條件的包含數字的單元格的個數　441

案例374　計算公司各職位的員工人數　442

10.2.3　DCOUNTA——計算滿足條件的非空單元格的個數　443

案例375　計算上班遲到的女員工人數　443

10.2.4　DGET——返回符合條件的單個值　444

案例376　提取指定商品的價格　445

10.2.5　DMAX——返回滿足條件的列表中的**大值　445

案例377　提取指定商品的**大銷量　446

10.2.6　DMIN——返回滿足條件的列表中的**小值　446

案例378　提取指定商品的**小銷量　447

10.3　對資料庫中的數據進行散布度統計　447

10.3.1　DSTDEV——返回滿足條件的數字作為一個樣本估算出的樣本標準偏差　447

案例379　計算男員工工齡的樣本標準偏差　448

10.3.2　DSTDEVP——返回滿足條件的數字作為樣本總體計算出的總體標準偏差　449

案例380　計算男員工工齡的總體標準偏差　449

10.3.3　DVAR——返回滿足條件的數字作為一個樣本估算出的樣本總體方差　450

案例381　計算男員工工齡的樣本總體方差　451

10.3.4　DVARP——返回滿足條件的數字作為樣本總體計算出的總體方差　451

案例382　計算男員工工齡的總體方差　452

第 11章　Web函式與宏表函式　453

11.1　Web函式　453

11.1.1　ENCODEURL——將文本轉換為URL編碼　453

案例383　對包含中文字元的網址進行編碼　454

11.1.2　WEBSERVICE——從Web服務中獲取網路數據　454

11.1.3　FILTERXML——在XML結構化內容中獲取指定路徑下的信息　455

11.2　宏表函式　455

11.2.1　GET.WORKBOOK——返回工作簿的相關信息　455

案例384　統計當前工作簿包含的工作表總數(二)(GET.WORKBOOK+T+NOW)　456

11.2.2　GET.CELL——返回單元格的相關信息　457

案例385　提取單元格中的公式(二)　458

11.2.3　EVALUATE——計算包含文本的表達式的值　459

案例386　計算單元格中的文本公式　459

案例387　計算不規則格式的文本公式(EVALUATE+SUBSTITUTE+ISTEXT)　460

第 12章　在條件格式中使用公式　461

12.1　條件格式簡介　461

12.2　在條件格式中使用公式　462

12.2.1　創建基於公式的條件格式的原則　462

12.2.2　條件格式公式示例　464

案例388　標記重複值　464

案例389　標記**大值　464

案例390　隱藏錯誤值　465

案例391　設定隔行底紋　465

案例392　設定固定不變的隔行底紋　466

案例393　設定生日到期提醒　466

第 13章　在數據有效性中使用公式　467

13.1　數據有效性簡介　467

13.2　在數據有效性中使用公式　469

13.2.1　創建基於公式的數據有效性的原則　469

13.2.2　數據有效性公式示例　469

案例395　禁止輸入重複值　470

案例396　將輸入值限制在總數內　471

案例397　快速切換數據有效性來源　471

案例398　創建可自動擴展的動態列表　473

案例399　創建二級下拉列表　474

第 14章　在圖表中使用公式　475

14.1　圖表簡介　475

14.1.1　圖表類型　475

14.1.2　圖表元素　480

14.1.3　創建圖表　481

14.1.4　圖表在工作簿中的位置　483

14.2　使用公式與控制項創建動態圖表　484

14.2.1　在圖表中使用公式　484

14.2.2　在圖表中使用控制項　485

14.2.3　了解數據系列的SERIES公式　486

14.2.4　動態圖表示例　487

案例400　製作可自動擴展的圖表　487

案例401　製作顯示**近幾天銷售額的圖表　489

案例402　製作可指定起點和終點的圖表　490

案例403　使用下拉列表控制餅圖的顯示　492

案例404　使用滾動條控制圖表的顯示　494

案例405　使用複選項控制圖表中顯示的數據系列　496

附錄1　Excel快捷鍵　500

附錄2　Excel函式速查表　505

附錄3　Excel 2003到Excel2013命令參考　525

作者簡介

宋翔，微軟全球有價值專家（MVP），微軟TechNet中文論壇版主，微軟TechNet Webcast講師，主要致力於微軟Windows和Office 兩大系列產品的研究與開發，出版過多部相關書籍，對Office在銷售、人力資源、財務等方面的套用有豐富的經驗。