Excel公式與函數大辭典（第2版）

宋翔，微软全球最有价值专家（MVP），微软TechNet中文论坛版主，微软TechNet Webcast讲师，主要致力于微软Windows和Office 两大系列产品的研究与开发，出版过多部相关书籍，对Office在销售、人力资源、财务等方面的应用有丰富的经验。

第1章　公式和函數基礎

1.1　瞭解公式 1

1.1.1 公式的組成部分 1

1.1.2 運算符及其優先級 2

1.1.3 數據類型及其相互轉換 3

1.1.4 普通公式和數組公式 4

1.1.5　最大字符數、數字精度、函數的參數個數和嵌套層數等的限制 5

1.2 輸入和編輯公式 5

1.2.1 單元格的引用樣式 5

1.2.2 輸入公式 7

1.2.3 修改公式 8

1.2.4 移動公式 8

1.2.5 復制公式和單元格引用類型 9

1.2.6 刪除公式 10

1.2.7 更改公式的計算方式 11

1.3　在公式中使用函數 11

1.3.1 為何使用函數 11

1.3.2 函數的類型 12

1.3.3 函數的參數 13

1.3.4 在公式中輸入函數 13

1.4　在公式中使用名稱 15

1.4.1 名稱的作用範圍 15

1.4.2 命名單元格或單元格區域 15

1.4.3 命名公式 17

1.4.4 命名常量 17

1.4.5 將名稱應用到公式中 17

1.5　使用數組公式 18

1.5.1 數組的類型 18

1.5.2 數組的運算方式 19

1.5.3 輸入和編輯數組公式 21

1.6　在公式中引用非當前工作表中的數據 22

1.6.1 引用其他工作表中的數據 22

1.6.2 引用其他工作簿中的數據 22

1.6.3 引用多個相鄰工作表中相同單元格區域的數據 23

1.6.4 更新公式中引用其他工作簿中的數據 24

1.7　排查公式錯誤的常用工具 24

1.7.1 使用公式錯誤檢查器 24

1.7.2 追蹤單元格之間的關系 25

1.7.3 監視單元格中的內容 27

1.7.4 使用“公式求值”功能 27

1.8　公式常見錯誤和解決方法 28

1.8.1 單元格自動被#填滿 28

1.8.2 空白但非空的單元格 28

1.8.3 顯示值和實際值 29

1.8.4 返回錯誤值 30

1.8.5 循環引用 32

1.9　公式使用技巧 32

1.9.1 將同一個公式一次性輸入多個單元格中 32

1.9.2 顯示公式而不是值 32

1.9.3 查看公式的中間結果 33

1.9.4 將公式轉換為固定值 33

1.9.5 復制公式時不使用相對引用 34

1.9.6 隱藏公式 34

1.9.7 禁止用戶修改公式 35

第2章　數學和三角函數

2.1　常規計算 36

2.1.1 SIGN——判斷數字的符號 36

【案例】檢測商品銷量是否達標 36

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

【案例】計算兩地溫差 37

2.1.3 SUM——計算數字之和 37

【案例】匯總兩種商品的銷售額 38

【案例】匯總大於150000的總銷售額 38

【案例】統計銷售部女員工的人數 38

2.1.4 PRODUCT——計算數字之積 39

【案例】 計算每種商品打折後的價格（PRODUCT+ROUND） 39

2.1.5 SQRT——計算平方根 40

【案例】計算正方形的邊長 40

2.1.6 MOD——返回商的餘數 40

【案例】判斷是否是閏年（MOD+IF+OR+AND） 40

【案例】分別匯總奇數月和偶數月的銷量（MOD+SUM+IF+ROW） 41

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

【案例】統計預算內可購買的各類辦公用品的數量 42

2.1.8 GCD——計算最大公約數 42

【案例】計算最大公約數 43

2.1.9 LCM——計算最小公倍數 43

【案例】計算最小公倍數 43

2.1.10 SUMIF——根據指定條件對單元格求和 44

【案例】計算某部門所有員工的年薪總和 44

【案例】計算銷售額位於前兩名和後兩名員工的銷售額總和（SUMIF+LARGE+SMALL） 45

2.1.11 SUMIFS——根據指定的多個條件對單元格求和 45

【案例】匯總指定銷售額範圍內的總銷售額 46

2.1.12 SUMPRODUCT——計算各個數組元素的乘積之和 46

【案例】計算商品打折後的總價格（SUMPRODUCT+ROUND） 46

【案例】統計銷售部女員工人數 47

2.1.13 SUMSQ——計算數字的平方和 47

【案例】計算直角三角形斜邊的長度（SUMSQ+SQRT） 48

2.1.14 SUMXMY2——計算兩個數組中對應值之差的平方和 48

【案例】 計算兩個數組中對應值之差的平方和 49

2.1.15 SUMX2MY2——計算兩個數組中對應值的平方差之和 49

【案例】計算兩個數組中對應值的平方差之和 49

2.1.16 SUMX2PY2——計算兩個數組中對應值的平方和之和 50

【案例】計算兩個數組中對應值的平方和之和 50

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

【案例】 計算自然對數的底數e的近似值 51

2.2　舍入和取整 51

2.2.1 INT——返回小於或等於原數字的最大整數 51

【案例】匯總整數金額（INT+SUM） 51

2.2.2 TRUNC——返回數字的整數部分或按指定位數返回數字 52

【案例】 匯總金額只保留一位小數（TRUNC+SUM） 52

2.2.3 ROUND——按指定位數對數字四捨五入 53

【案例】將金額取整舍入到百位（ROUND+SUM） 53

2.2.4 ROUNDDOWN——向絕對值減小的方向舍入數字 54

【案例】匯總金額忽略分位（ROUNDDOWN+SUM） 54

2.2.5 ROUNDUP——向絕對值增大的方向舍入數字 55

【案例】計算書吧費用（ROUNDUP+HOUR+MINUTE） 55

2.2.6 MROUND——將數字舍入到指定倍數 56

【案例】計算商品運送車次 56

2.2.7 CEILING——向絕對值增大的方向將數字舍入到指定倍數 57

【案例】計算書吧費用（CEILING+HOUR+MINUTE） 58

2.2.8 CEILING.PRECISE——向算術值增大的方向將數字舍入到指定倍數 58

2.2.9 CEILING.MATH——向絕對值或算術值增大的方向將數字舍入到指定倍數 59

2.2.10 FLOOR——向絕對值減小的方向將數字舍入到指定倍數 60

【案例】計算員工提成獎金 61

2.2.11 FLOOR.PRECISE——向算術值減小的方向將數字舍入到指定倍數 61

2.2.12 FLOOR.MATH——向絕對值或算術值減小的方向將數字舍入到指定倍數 62

2.2.13 EVEN——向絕對值增大的方向將數字舍入到最接近的偶數 63

【案例】男員工隨機抽獎（EVEN+INDEX+RANDBETWEEN） 63

2.2.14 ODD——向絕對值增大的方向將數字舍入到最接近的奇數 64

【案例】女員工隨機抽獎（ODD+INDEX+RANDBETWEEN） 64

2.3　指數和對數 65

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

【案例】 計算各個數字的倒數之和（POWER+SUM） 65

2.3.2 EXP——計算自然對數的底數e的n次冪 66

【案例】 計算自然對數的底數e的n次冪 66

2.3.3 LN——計算自然對數 66

【案例】計算數字的自然對數 67

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

【案例】 計算以2為底的對數 67

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

【案例】計算以10為底的對數 68

2.4　階乘和矩陣 68

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

【案例】計算公司知識競賽對局次數 69

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

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

【案例】計算10~20的連乘 70

2.4.4 FACTDOUBLE——計算數字的雙階乘 70

【案例】計算1到36之間所有偶數的乘積 71

2.4.5 MULTINOMIAL——計算多個數字總和的階乘與這些數字階乘的比值 71

【案例】計算人員分組問題 71

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

【案例】求解二元聯立方程組 72

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

【案例】求解多元聯立方程組（MINVERSE+MMULT） 73

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

【案例】 計算商品在不同單價下的銷售額 73

【案例】提取空調的最大銷量（MMULT+MAX+N+TRANSPOSE） 74

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

2.5　三角函數 75

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

【案例】根據弧長和半徑計算角度（DEGREES+ROUND） 75

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

【案例】根據角度和半徑計算弧長（RADIANS+ROUND） 75

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

【案例】計算給定角度的正弦值（SIN+ROUND） 76

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

【案例】 計算數字的反正弦值（ASIN+ROUND） 77

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

【案例】 計算數字的雙曲正弦值（SINH+ROUND） 77

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

【案例】 計算數字的反雙曲正弦值（ASINH+ROUND） 78

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

【案例】計算給定角度的餘弦值（COS+ROUND） 79

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

【案例】計算數字的反餘弦值（ACOS+ROUND） 79

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

【案例】計算數字的雙曲餘弦值（COSH+ROUND） 80

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

【案例】計算數字的反雙曲餘弦值（ACOSH+ROUND） 81

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

【案例】計算給定角度的正切值（TAN+ROUND） 81

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

【案例】計算數字的反正切值（ATAN+ROUND） 82

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

【案例】計算數字的雙曲正切值（TANH+ROUND） 83

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

【案例】計算數字的反雙曲正切值（ATANH+ROUND） 83

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

【案例】計算給定坐標的反正切值 84

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

【案例】計算數字的反餘切值（ACOT+ROUND） 85

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

【案例】計算數字的反雙曲餘切值（ACOTH+ROUND） 86

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

【案例】計算給定角度的餘切值 86

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

【案例】計算數字的雙曲餘切值（COTH+ROUND） 87

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

【案例】計算給定角度的正割值（SEC+ROUND） 88

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

【案例】計算給定角度的雙曲正割值（SECH+ROUND） 89

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

【案例】計算給定角度的餘割值（CSC+ROUND） 89

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

【案例】計算給定角度的雙曲餘割值（CSCH+ROUND） 90

2.6　其他計算 91

2.6.1 PI——返回圓周率π的值 91

【案例】計算圓周長（PI+ROUND） 91

2.6.2 SQRTPI——計算某數與π的乘積的平方根 91

【案例】計算圓周率倍數的平方根 91

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

【案例】匯總某部門員工工資情況（一） 93

【案例】設置不間斷序號 93

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

【案例】匯總某部門員工工資情況（二） 95

【案例】統計銷售部年薪排名第二的員工姓名（AGGREGATE+INDEX+MATCH） 96

2.6.5 ROMAN——將阿拉伯數字轉為羅馬數字 96

【案例】 將人員編號轉換為羅馬數字（ROMAN+CHOOSE） 97

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

2.6.7 BASE——在不同數制之間轉換數字 97

【案例】在不同數制之間轉換數字 98

2.6.8 DECIMAL——將非十進制數轉換為十進制數 98

【案例】將給定基數的文本轉換為十進制數 99

2.6.9 RAND——返回0到1之間的一個隨機數 99

【案例】隨機顯示A~Z中的大寫字母（RAND+CHAR+INT） 99

2.6.10 RANDBETWEEN——返回指定範圍內的隨機整數 100

【案例】生成1到50之間的隨機偶數 100

2.6.11 RANDARRAY——返回指定行/列數和最值的一組隨機數 101

【案例】隨機顯示A~Z中的大寫字母（RANDARRAY+CHAR） 102

2.6.12 SEQUENCE——返回指定行/列數和增量的一組連　續數字 102

【案例】創建以100為增量的一系列編號 102

第3章　日期和時間函數

3.1　瞭解Excel日期系統 104

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

3.1.2 日期和時間序列號 104

3.1.3 輸入與設置日期和時間 105

3.2　返回日期和時間 106

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

【案例】統計員工在職時間（NOW+ROUND+IF） 106

【案例】元旦倒計時（NOW+TEXT） 106

3.2.2 TODAY——返回當前日期 107

【案例】統計試用期到期的人數（TODAY+COUNTIF） 107

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

【案例】計算2023年星期六的個數（DATE+SUM+N+TEXT+ROW+INDIRECT） 108

【案例】計算本月的天數（DATE+TEXT+ YEAR+MONTH+TODAY） 109

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

【案例】安排會議時間（TIME+TEXT+NOW） 110

3.3　返回日期和時間的特定部分 110

3.3.1 YEAR——返回年份 110

【案例】計算2020年之後的員工平均工資（YEAR+ROUND+AVERAGE+IF） 110

3.3.2 MONTH——返回月份 111

【案例】計算本月需要結算的金額（MONTH+TODAY+SUM+IF） 111

【案例】判斷是否是閏年（MONTH+IF+DATE+YEAR） 112

3.3.3 DAY——返回日 112

【案例】計算本月下旬需要結算的金額（DAY+SUM+IF） 112

3.3.4 WEEKDAY——返回指定日期對應的星期 113

【案例】統計周五的商品銷量（WEEKDAY+SUM+IF） 113

3.3.5 HOUR——返回小時 114

【案例】計算用餐時間 114

3.3.6 MINUTE——返回分鐘 115

【案例】計算用餐的精確時間（HOUR+MINUTE） 115

3.3.7 SECOND——返回秒 116

【案例】計算廣告播放時長 116

3.4　文本和日期時間格式之間的轉換 116

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

【案例】計算兩個月之間相差的天數 117

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

【案例】 計算加班費用（TIMEVALUE+ SUBSTITUTE+ROUND） 117

3.5　其他日期函數 118

3.5.1 DATEDIF——計算兩個日期間隔的年數、月數和天數 118

【案例】統計辦公用品使用年數（DATEDIF+TODAY） 119

3.5.2 DAYS360——以一年360天為基準計算兩個日期間隔的天數 119

【案例】計算還款天數（一） 119

3.5.3 DAYS——計算兩個日期間隔的天數 120

【案例】計算還款天數（二） 120

3.5.4 EDATE——計算與某個日期相隔幾個月（之前或之後）的日期 121

【案例】計算還款日期（EDATE+TEXT+LEFT+LEN） 121

3.5.5 DATESTRING——將日期序列號轉換為文本格式的　日期 121

【案例】 計算還款日期（DATESTRING+EDATE+LEFT+LEN） 122

3.5.6 EOMONTH——計算某個日期相隔幾個月（之前或之後）的那個月最後一天的日期 122

【案例】 統計員工的工資結算日期（EOMONTH+TEXT） 123

3.5.7 NETWORKDAYS——計算兩個日期間隔的工作日數 123

【案例】計算工程施工天數（一） 124

3.5.8 NETWORKDAYS.INTL——計算兩個日期間隔的工作日數（可自定義周末及其天數） 124

【案例】計算工程施工天數（二） 125

3.5.9 WEEKNUM——返回某個日期在一年中是第幾周 125

【案例】計算本月包含的周數（WEEKNUM+EOMONTH+NOW） 126

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

3.5.11 WORKDAY——計算與某個日期相隔數個工作日（之前或之後）的日期 127

【案例】計算上門安裝日期（WORKDAY+TEXT+LEFT+LEN） 127

3.5.12 WORKDAY.INTL——計算與某個日期相隔數個工作日（之前或之後）的日期（可自定義周末及其天數） 128

【案例】 計算上門安裝日期（WORKDAY. INTL+ TEXT+LEFT+LEN） 129

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

【案例】計算全年盈利額（YEARFRAC+ROUND） 130

第4章　邏輯函數

4.1　邏輯值函數 131

4.1.1 TRUE——返回邏輯值TRUE 131

【案例】判斷兩列數據是否相同 131

4.1.2 FALSE——返回邏輯值FALSE 132

【案例】計算兩列中相同數據的個數 132

4.2　條件判斷函數 132

4.2.1 NOT——對邏輯值求反 132

【案例】計算區域中數字的個數（NOT+SUM+ISERROR） 133

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

【案例】 判斷面試人員是否被錄取 134

【案例】判斷是否為員工發放獎金 134

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

【案例】 判斷身份證號碼的長度是否正確（OR+LEN） 135

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

4.2.5 IF——根據條件判斷結果返回不同的值 136

【案例】 評定員工業績（一） 136

【案例】計算需要發放獎金的總額（IF+SUM） 137

4.2.6 IFS——從多個條件中返回滿足的第一個條件對應的值 137

【案例】評定員工業績（二） 138

4.2.7 SWITCH——返回與指定值相匹配的第一個值所對應的結果 138

【案例】自動為商品分類 138

4.2.8 IFNA——判斷公式是否出現#N/A錯誤 139

【案例】根據姓名查找員工所屬部門並排錯（IFNA+VLOOKUP） 139

4.2.9 IFERROR——根據公式結果返回不同內容 140

【案例】根據編號查找員工信息並排錯（IFERROR+VLOOKUP） 140

第5章　文本函數

5.1　返回字符或字符編碼 141

5.1.1 CHAR——返回與ANSI字符編碼對應的字符 141

【案例】生成大寫字母序列（CHAR+ROW） 141

5.1.2 UNICHAR——返回與Unicode字符編碼對應的字符 142

5.1.3 CODE——返回與字符對應的ANSI字符編碼 142

【案例】判斷單元格中的第一個字符是否是字母（CODE+IF+OR+AND） 142

【案例】按照升序排列亂序的字母列表（CODE+CHAR+SMALL+ROW） 143

5.1.4 UNICODE——返回與字符對應的Unicode字符編碼 144

5.2　返迴文本內容 144

5.2.1 LEFT——提取文本左側指定個數的字符 144

【案例】提取地址中的省市名稱（LEFT+FIND） 144

【案例】統計各地區參會人數（LEFT+SUM） 145

5.2.2 LEFTB——提取文本左側指定字節數的字符 146

5.2.3 LEN——計算文本的字符數 146

【案例】計算單元格中的數字個數（LEN+LENB） 146

【案例】根據身份證號判斷性別（LEN+IF+MOD+RIGHT+MID） 147

5.2.4 LENB——計算文本的字節數 148

5.2.5 MID——從文本中的指定位置提取指定個數的字符 148

【案例】提取公司獲獎人員姓名（MID+FIND+LEN） 148

【案例】從身份證號中提取生日（MID+TEXT+IF+LEN） 149

5.2.6 MIDB——從文本中的指定位置提取指定字節數的字符 149

5.2.7 RIGHT——提取文本右側指定個數的字符 150

【案例】 動態提取公司名稱（RIGHT+LEN+FIND） 150

5.2.8 RIGHTB——提取文本右側指定字節數的字符 151

5.2.9 REPT——生成重復的字符 151

【案例】自動為數字添加星號（REPT+LEN） 151

【案例】製作簡易的銷售圖表 152

5.3　合並文本 152

5.3.1 CONCATENATE——將多個文本合並在一起 152

【案例】評定員工考核成績（CONCATENATE+IF+SUM） 153

5.3.2 CONCAT——將來自多個範圍的文本合並在一起 153

【案例】提取各行中的第一個非0數字 154

5.3.3 TEXTJOIN——以指定分隔符將來自多個範圍的文本合並在一起 154

【案例】將數字合並為IP地址 154

5.3.4 LET函數——創建中間變量以簡化公式 155

【案例】創建中間變量以簡化公式 155

5.4　轉換文本格式 156

5.4.1 ASC——將全角字符轉換為半角字符 156

【案例】計算全、半角混合文本中的字母個數（ASC+LEN+LENB） 156

5.4.2 WIDECHAR——將半角字符轉換為全角字符 157

【案例】計算全、半角混合文本中的漢字個數（WIDECHAR+LEN+LENB+ASC） 157

5.4.3 PHONETIC——返迴文本中的拼音字符 157

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

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

【案例】出口商品價格轉換 158

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

【案例】進口商品價格轉換 159

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

【案例】將銷售額轉換為中文大寫漢字 160

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

5.4.9 T——將指定內容轉換為文本 161

【案例】為公式添加註釋（RMB+T+N） 161

5.4.10 LOWER——將英文字母轉換為小寫 161

5.4.11 UPPER——將英文字母轉換為大寫 162

【案例】將文本轉換為句首字母大寫其他字母小寫（UPPER+LEFT+LOWER+RIGHT+LEN） 162

5.4.12 PROPER——將每個單詞的首字母轉換為大寫 162

【案例】整理人名數據 163

5.4.13 VALUE——將文本型數字轉換為數值 163

【案例】對文本格式的銷售額求和（VALUE+SUM） 163

5.4.14 TEXT——設置數字格式 164

【案例】以特定的單位顯示金額（TEXT+SUM） 164

【案例】將數字轉換為電話號碼格式 164

【案例】 自動生成12個月的英文名稱（TEXT+ROW） 165

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

【案例】格式化數字格式 165

5.5　查找和替換文本 166

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

【案例】核對錄入的數據是否正確（EXACT+IF） 166

5.5.2 FIND——以字符為單位區分大小寫地查找字符的位置 167

【案例】檢查聯系地址是否詳細（FIND+ISERROR+IF） 167

【案例】提取公司名稱（FIND+MID） 167

5.5.3 FINDB——以字節為單位區分大小寫地查找字符的位置 168

5.5.4 REPLACE——以字符為單位替換指定位置上的內容 169

【案例】電話號碼位數升級 169

【案例】 提取地區和公司名稱（REPLACE+LEFT+FIND） 169

5.5.5 REPLACEB——以字節為單位替換指定位置上的內容 170

5.5.6 SEARCH——以字符為單位不區分大小寫地查找字符的位置 170

【案例】設置圖書分類上架建議（SEARCH+IF+COUNT） 171

5.5.7 SEARCHB——以字節為單位不區分大小寫地查找字符的位置 171

5.5.8 SUBSTITUTE——替換指定內容 172

【案例】將日期轉換為標準格式 172

【案例】格式化公司名稱（SUBSTITUTE+REPLACE） 173

5.6　刪除無用字符 173

5.6.1 CLEAN——刪除非打印字符 173

【案例】對表格內容排版 173

5.6.2 TRIM——刪除多餘空格 174

【案例】整理格式不規範的數據（TRIM+CLEAN） 174

第6章　查找和引用函數

6.1　查找數據 175

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

【案例】評定員工業績（CHOOSE+IF） 176

【案例】 重組數據 176

【案例】在多組銷售數據中查找銷量（CHOOSE+VLOOKUP+MATCH） 177

6.1.2 LOOKUP——在單行或單列中查找數據（向量 形式） 178

【案例】 根據姓名查找員工編號 178

【案例】使用LOOKUP函數實現多條件判斷 179

【案例】提取文本中的金額（LOOKUP+LEFT+ ROW+INDIRECT+LEN） 179

6.1.3 LOOKUP——在單行或單列中查找數據（數組 形式） 180

【案例】查找員工信息（一） 180

6.1.4 HLOOKUP——在區域的行中查找數據 181

【案例】查找商品在某季度的銷量（HLOOKUP+MATCH） 181

6.1.5 VLOOKUP——在區域的列中查找數據 182

【案例】根據商品名稱查找銷量 183

【案例】根據銷量評定員工業績 183

【案例】 從多個表中計算員工的年終獎（VLOOKUP+IF） 184

【案例】逆向查找（VLOOKUP+IF） 184

6.1.6 XLOOKUP——在單行或單列中查找數據並返回一個或多個匹配值 185

【案例】查找員工信息（二） 186

6.1.7 MATCH——返回數據在區域或數組中的位置 186

【案例】不區分大小寫查找數據（MATCH+INDEX） 187

【案例】區分大小寫查找數據（MATCH+INDEX+EXACT） 188

【案例】雙重定位查找數據（MATCH+INDEX） 188

【案例】提取某商品最後一次進貨日期（MATCH+TEXT+INDIRECT） 188

【案例】從多列數據中查找員工工資（MATCH+INDEX） 189

6.1.8 XMATCH——返回指定內容在區域或數組中的位置或數量 190

【案例】 統計工齡不低於10年的員工人數 191

6.1.9 INDEX——返回指定位置上的數據（數組形式） 191

【案例】查找左側列中的數據（INDEX+MATCH） 191

6.1.10 INDEX——返回指定位置上的數據（引用形式） 192

【案例】提取某區域的銷售數據（INDEX+MATCH） 192

6.2　引用數據 193

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

【案例】確定最大銷售額的位置（ADDRESS+MAX+IF+ROW） 194

【案例】返回當前單元格的列標（ADDRESS+ COLUMN+SUBSTITUTE） 194

【案例】跨工作表返回匯總金額（ADDRESS+INDIRECT） 194

6.2.2 AREAS——返回引用中包含的區域個數 195

【案例】統計分公司數量 195

6.2.3 COLUMN——返回區域首列的列號 196

【案例】在一行中快速輸入連續的月份（COLUMN+TEXT） 196

【案例】匯總多個列中的銷量（COLUMN+SUM+IF+MOD） 196

6.2.4 COLUMNS——返回區域的列數 197

【案例】 計算需要扣款的項目數 197

6.2.5 ROW——返回區域首行的行號 198

【案例】在一列中快速輸入連續的月份（ROW+TEXT） 198

【案例】定位最後一次銷量大於800的日期（ROW+TEXT+INDEX+MAX） 198

【案例】計算員工的最高工資（ROW+MAX+MMULT） 199

6.2.6 ROWS——返回區域的行數 199

【案例】計算需要開支的員工人數 200

【案例】計算報價數量（ROWS+COLUMNS） 200

【案例】檢驗日期是否重復（ROWS+IF+MATCH） 201

6.2.7 OFFSET——根據指定的偏移量返回新的單元格或區域 201

【案例】查詢員工信息（OFFSET+MATCH） 202

【案例】計算每日累積銷量（OFFSET+SUM+ROW） 202

【案例】匯總最近5天的銷量（OFFSET+SUBTOTAL+ INDIRECT+MAX+ROW） 203

【案例】製作工資條（OFFSET+IF+MOD+ROW+ ROUND+COLUMN） 203

6.2.8 TRANSPOSE——轉置區域中的行列位置 205

【案例】轉置銷售數據區域 205

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

【案例】統計銷量小於600的員工人數（INDIRECT+SUM+COUNTIF） 206

【案例】提取多個工作表中的最大值（INDIRECT+SUBTOTAL） 207

6.2.10 FORMULATEXT——返回公式的文本形式 207

【案例】提取單元格中的公式（一） 208

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

【案例】使用函數從數據透視表中提取數據 208

6.2.12 HYPERLINK——為指定內容創建超鏈接 209

【案例】在銷售數據表中設置公司郵件地址 210

【案例】自動跳轉到數據結尾（HYPERLINK+OFFSET+COUNTA） 210

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

6.3　排序和篩選數據 212

6.3.1 SORT——對區域中的數據排序 212

【案例】按工齡降序排列員工信息 212

6.3.2 SORTBY——以一個或多個行或列作為參照對數據排序 213

【案例】按部門升序和年薪降序排列員工信息 213

6.3.3 FILTER——根據指定的條件篩選數據 214

【案例】提取工程部中的員工信息並按工齡降序 排列 214

6.3.4 UNIQUE——返回區域中的不重復值 215

【案例】提取不重復的員工姓名 215

第7章　信息函數

7.1　返回信息 216

7.1.1 CELL——返回單元格的相關信息 216

【案例】獲取當前工作簿的完整路徑 217

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

【案例】獲取當前操作環境 218

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

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

【案例】統計當前工作簿包含的工作表總數（一） 219

7.2　返回不同類型的值 219

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

【案例】根據錯誤代碼顯示錯誤原因（ERROR.TYPE+CHOOSE） 220

7.2.2 N——返回轉換為數字的值 220

【案例】生成指定員工的年薪列表（N+OFFSET） 221

7.2.3 NA——返回#N/A錯誤值 222

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

【案例】簡易的輸入驗證工具（TYPE+LOOKUP） 222

7.3　使用IS函數進行各種判斷 223

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

【案例】統計缺勤人數（ISBLANK+SUM） 223

7.3.2 ISLOGICAL——判斷值是否是邏輯值 224

7.3.3 ISNUMBER——判斷值是否是數字 224

【案例】 統計指定商品的銷量總和（ISNUMBER+SUM+FIND） 224

7.3.4 ISTEXT——判斷值是否是文本 225

【案例】判斷員工是否已簽到（ISTEXT+IF） 225

7.3.5 ISNONTEXT——判斷值是否不是文本 225

【案例】 判斷員工是否已簽到（ISNONTEXT+IF） 226

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

【案例】 判斷單元格是否包含公式 226

7.3.7 ISEVEN——判斷數字是否是偶數 227

【案例】統計女員工人數（ISEVEN+SUM+MID） 227

7.3.8 ISODD——判斷數字是否是奇數 228

【案例】統計男員工人數（ISODD+SUM+MID） 228

7.3.9 ISNA——判斷值是否是#N/A錯誤值 228

【案例】查找員工信息（三） 229

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

7.3.11 ISERR——判斷值是否是除了#N/A之外的其他錯誤值 230

7.3.12 ISERROR——判斷值是否是錯誤值 230

【案例】統計員工年薪總和（ISERROR+SUM+IF） 230

第8章　統計函數

8.1　統計數量和頻率 231

8.1.1 COUNT——計算數字的個數 231

【案例】統計員工人數 232

【案例】統計不重復員工人數（COUNT+MATCH+ROW） 232

8.1.2 COUNTA——計算非空值的個數 233

【案例】統計遲到人數 233

【案例】創建動態區域名稱（COUNTA+OFFSET） 233

8.1.3 COUNTBLANK——計算空白單元格的個數 234

【案例】統計會議未到場人數 234

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

【案例】統計銷量大於800的員工人數 235

【案例】計算兩列數據中相同數據的個數（COUNTIF+SUM） 235

【案例】統計不重復員工人數（COUNTIF+SUM） 236

【案例】提取不重復員工姓名（COUNTIF+LOOKUP+NOT） 236

8.1.5 COUNTIFS——計算同時滿足多個條件的單元格個數 237

【案例】統計銷量在600到1000之間的男員工人數 237

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

【案例】統計不同銷量區間的員工人數 238

8.2　統計均值和眾數 239

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

【案例】計算零件質量系數的平均偏差 239

8.2.2 AVERAGE——計算平均值 240

【案例】計算某商品的平均價格 240

【案例】 計算男員工的平均銷量（AVERAGE+ROUND+IF） 240

8.2.3 AVERAGEA——計算非空值的平均值 241

【案例】計算員工的平均銷量（含未統計者）（AVERAGEA+ROUND） 241

8.2.4 AVERAGEIF——計算滿足指定條件的數據的平均值 242

【案例】計算男員工的平均銷量（AVERAGEIF+ROUND） 242

8.2.5 AVERAGEIFS——計算同時滿足多個條件的數據的平均值 243

【案例】 計算銷量大於600的男員工的平均銷量（AVERAGEIFS+ROUND） 243

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

【案例】 計算銷售業績的平均增長率 244

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

【案例】計算從第1天到第5天每天的平均產量 245

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

【案例】計算選手最後得分（TRIMMEAN+ROUND） 246

8.2.9 MEDIAN——返回中值 246

【案例】計算銷量的中間值 247

【案例】 計算銷量的中間值所屬的日期（MEDIAN+INDEX+MATCH） 247

8.2.10 MODE.SNGL——返回出現次數最多的值 248

【案例】 統計被投票最多的選手（一） 248

【案例】提取出現次數最多的數字（MODE.SNGL+ MID+ROW+INDIRECT+LEN） 248

8.2.11 MODE.MULT——返回出現頻率最高或重復出現的數值的垂直數組 249

【案例】統計被投票最多的選手（二） 249

8.3　統計極值和排位 250

8.3.1 MAX——返回一組數字中的最大值 250

【案例】統計男員工完成的最大銷量 250

【案例】計算單日最高銷量（MAX+SUMIF） 251

【案例】提取最後一次出貨日期（MAX+TEXT+INDEX+IF+ROW） 251

8.3.2 MAXA——返回一組非空值中的最大值 252

8.3.3 MAXIFS——返回滿足一個或多個條件的所有數字中的最大值 252

【案例】統計年薪在15萬元以上的部門經理的最高 年薪 253

8.3.4 MIN——返回一組數字中的最小值 253

【案例】隨機顯示一個不超過指定日期的日期（MIN+DATE+RANDBETWEEN） 253

【案例】提取完成最小銷量的員工姓名（MIN+INDEX+MATCH） 254

8.3.5 MINA——返回一組非空值中的最小值 254

8.3.6 MINIFS——返回滿足一個或多個條件的所有數字中的最小值 255

【案例】統計年薪在15萬元以下的普通職員的最低年薪 255

8.3.7 LARGE——返回第k個最大值 256

【案例】 計算前3名銷量的總和（LARGE+SUM） 256

8.3.8 SMALL——返回第k個最小值 256

【案例】計算後3名銷量的總和（SMALL+SUM） 257

8.3.9 RANK.EQ——返回數字的排位 257

【案例】對員工銷量降序排名（一） 258

【案例】計算兩列數據中相同數據的個數（RANK.EQ+COUNT） 258

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

【案例】對員工銷量降序排名（二） 259

8.3.11 PERCENTRANK.INC——返回百分比排位 259

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

8.3.12 PERCENTRANK.EXC——返回百分比排位 260

8.3.13 QUARTILE.INC——返回四分位數 261

【案例】根據員工銷量計算四分位數 262

8.3.14 QUARTILE.EXC——返回四分位數 262

8.3.15 PERCENTILE.INC——返回第k個百分點的值 263

【案例】 根據員工銷量計算指定百分點的值 263

8.3.16 PERCENTILE.EXC——返回第k個百分點的值 264

8.3.17 PERMUT——返回對象的排列數 264

【案例】 計算中獎率 265

8.3.18 PERMUTATIONA——返回對象（含重復）的排列數 265

8.4　統計數據的散佈度 266

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

【案例】計算零件質量系數的偏差平方和 266

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

【案例】計算員工工齡樣本的標準偏差 267

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

【案例】計算員工工齡樣本的標準偏差（含未統計者） 268

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

【案例】計算員工工齡樣本總體的標準偏差 269

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

【案例】計算員工工齡樣本總體的標準偏差（含未統計者） 270

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

【案例】計算員工工齡樣本的方差 271

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

【案例】計算員工工齡樣本的方差（含未統計者） 271

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

【案例】計算員工工齡樣本總體的方差 272

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

【案例】 計算員工工齡樣本總體的方差（含未統計者） 273

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

【案例】計算商品在一段時期內價格的峰值 274

8.4.11 SKEW——返回分佈的不對稱度 274

【案例】計算商品在一段時期內價格的不對稱度 275

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

8.5　統計概率分佈 276

8.5.1 BINOM.DIST——返回一元二項式分佈的概率 276

【案例】計算沒有不合格產品的概率 276

8.5.2 BINOM.INV——返回使累積二項式分佈大於等於臨界值的最小值 277

【案例】計算允許的不合格產品數 277

8.5.3 BINOM.DIST.RANGE——返回二項式分佈試驗結果的概率 278

8.5.4 NEGBINOM.DIST——返回負二項式分佈的概率 278

【案例】計算談判成功的概率 279

8.5.5 PROB——返回數值落在指定區間內的概率 279

【案例】計算中獎概率 280

8.5.6 GAUSS——返回比標準正態累積分佈函數小0.5的值 280

【案例】 計算比標準正態累積分佈函數小0.5的值 280

8.5.7 PHI——返回標準正態分佈的密度函數值 281

【案例】計算標準正態分佈的密度函數值 281

8.5.8 NORM.DIST——返回正態累積分佈函數 281

【案例】計算概率密度函數的值

8.5.9 NORM.INV——返回標準正態累積分佈的反函數值 282

【案例】計算累積分佈函數的反函數的值

8.5.10 NORM.S.DIST——返回標準正態累積分佈函數 283

【案例】製作正態分佈表 284

8.5.11 NORM.S.INV——返回標準正態累積分佈函數的反函數值 284

【案例】 計算標準正態分佈函數的反函數 284

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

【案例】計算正態化數值 285

8.5.13 LOGNORM.DIST——返回對數累積分佈函數 287

【案例】 計算對數累積分佈函數的值 287

8.5.14 LOGNORM.INV——返回對數累積分佈函數的反函數 287

【案例】計算對數累積分佈函數的反函數的值 288

8.5.15 HYPGEOM.DIST——返回超幾何分佈 288

【案例】計算沒有不合格產品的概率 289

8.5.16 POISSON.DIST——返回泊松分佈 289

【案例】計算產品不發生故障的概率 290

8.5.17 EXPON.DIST——返回指數分佈 290

【案例】 計算在經過指定期限後，產品在兩家公司發生故障的概率 291

8.5.18 WEIBULL.DIST——返回韋伯分佈 291

【案例】計算產品的不同故障發生的概率 292

8.5.19 GAMMA——返回伽馬函數值 292

【案例】計算伽馬函數值 292

8.5.20 GAMMA.DIST——返回伽馬分佈函數 293

【案例】計算伽馬分佈函數的值 293

8.5.21 GAMMA.INV——返回伽馬累積分佈函數的反函數值 294

【案例】計算伽馬分佈函數的反函數的值 294

8.5.22 GAMMALN——返回伽馬函數的自然對數 295

【案例】計算伽馬函數的自然對數值 295

8.5.23 GAMMALN.PRECISE——返回伽馬函數的自然對數 295

【案例】計算伽馬函數的自然對數值 296

8.5.24 BETA.DIST——返回β累積分佈函數 296

【案例】 計算β累積分佈函數的值 297

8.5.25 BETA.INV——返回指定β累積分佈函數的反函數值 297

【案例】計算β累積分佈函數的反函數的值 298

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

【案例】計算平均視力的95％置信區間 299

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

8.6　檢驗數據的傾向性 300

8.6.1 CHISQ.DIST.RT——返回χ2分佈的右尾概率 300

【案例】計算χ2分佈的單尾概率 300

8.6.2 CHISQ.DIST——返回χ2分佈 300

8.6.3 CHISQ.INV.RT——返回χ2分佈的右尾概率的反函數值 301

【案例】計算χ2分佈的單尾概率的反函數值 301

8.6.4 CHISQ.INV——返回χ2分佈的左尾概率的反函數值 302

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

【案例】檢驗電腦用戶與頸椎病的關系 303

8.6.6 F.DIST.RT——返回F概率分佈 303

【案例】計算F概率分佈 304

8.6.7 F.DIST——返回F概率分佈 304

8.6.8 F.INV.RT——返回F概率分佈的反函數值 304

【案例】計算F累積分佈概率的反函數值 305

8.6.9 F.INV——返回F概率分佈的反函數值 305

8.6.10 T.DIST.2T——返回t分佈的雙尾概率 306

【案例】計算t分佈的概率 306

8.6.11 T.DIST.RT——返回t分佈的右尾概率 307

8.6.12 T.DIST——返回t分佈 307

8.6.13 T.INV.2T——返回t分佈的雙尾反函數值 308

【案例】計算t分佈概率的反函數值 308

8.6.14 T.INV——返回t分佈的左尾反函數值 308

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

【案例】檢驗空調和冰箱耗電量的方差 309

8.6.16 T.TEST——返回與t檢驗相關的概率 310

【案例】檢驗空調和冰箱耗電量的平均值 310

8.6.17 Z.TEST——返回z檢驗的單尾概率 311

【案例】檢驗2023年與5年前商品銷量的平均記錄 311

8.7　統計協方差、相關系數和回歸 312

8.7.1 COVARIANCE.P——計算總體協方差（成對偏差乘積的平均值） 312

【案例】計算以年齡和視力為樣本數據的協方差 312

8.7.2 COVARIANCE.S——計算樣本協方差（成對偏差乘積的平均值） 313

【案例】計算以年齡和視力為樣本數據的協方差 313

8.7.3 CORREL——返回兩組數據的相關系數 313

【案例】計算年齡和視力的相關系數 314

8.7.4 FISHER——返回Fisher變換 314

【案例】計算Fisher變換的相關系數（FISHER+CORREL） 314

8.7.5 FISHERINV——返回Fisher變換的反函數值 315

【案例】計算Fisher變換的反函數值 315

8.7.6 PEARSON——返回皮爾遜乘積矩相關系數 316

【案例】計算年齡和視力的皮爾遜乘積矩相關系數 316

8.7.7 RSQ——返回皮爾遜乘積矩相關系數的平方 316

【案例】計算年齡和視力的皮爾遜乘積矩相關系數的平方 317

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

【案例】預測特定年齡的視力 318

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

【案例】預測未來月份的銷量 319

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

【案例】 預測回歸線上的視力 320

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

【案例】根據年齡和視力求線性回歸值 321

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

【案例】根據時間和銷量求指數回歸值 322

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

【案例】從年齡和視力中求回歸線的斜率 323

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

【案例】從年齡和視力中求回歸線的截距 323

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

【案例】從年齡和視力中求回歸線的標準誤差 324

第9章　財務函數

9.1　瞭解貨幣的時間價值 325

9.2　計算本金和利息 326

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

【案例】計算貸款的每期付款額 326

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

【案例】計算貸款在給定期間內支付的利息 327

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

【案例】計算貸款在給定期間內償還的本金 328

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

【案例】計算特定投資期內支付的利息 329

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

【案例】計算兩個付款期之間累計支付的利息 329

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

【案例】 計算兩個付款期之間累計支付的本金 330

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

【案例】將名義年利率轉換為實際年利率 331

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

【案例】將實際年利率轉換為名義年利率 331

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

【案例】計算年金的各期利率 332

9.3　計算投資預算 333

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

【案例】計算一筆投資的未來值 333

9.3.2 FVSCHEDULE——使用一系列復利率計算本金的未來值 334

【案例】使用一系列復利率計算初始本金的未來值 334

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

【案例】計算投資的期數（NPER+ROUNDUP） 335

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

【案例】計算投資到達指定值所需的期數（PDURATION+ROUNDUP） 335

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

【案例】計算投資的現值 336

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

【案例】計算投資的凈現值 337

9.3.7 XNPV——計算一組不定期發生的現金流的凈現值 337

【案例】計算未必定期發生的投資的凈現值 338

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

【案例】計算某項投資增長的等效利率 338

9.4　計算收益率 339

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

【案例】計算一系列現金流的內部收益率 339

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

【案例】計算在不同利率下支付的修正內部收益率 340

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

【案例】計算未必定期發生的現金流的內部收益率 341

9.5　計算折舊值 342

9.5.1 AMORDEGRC——根據資產的使用年限計算每個結算期的折舊值 342

【案例】計算每個結算期的餘額遞減折舊值 343

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

【案例】 計算每個結算期的餘額遞減折舊值 343

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

【案例】使用固定餘額遞減法計算折舊值（DB+ROW） 344

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

【案例】使用雙倍餘額遞減法計算折舊值（DDB+ROW） 345

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

【案例】使用餘額遞減法計算折舊值 345

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

【案例】按年限總和折舊法計算折舊值（SYD+ROW） 346

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

【案例】計算期間內的線性折舊值 347

9.6　計算證券與國庫券 347

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

【案例】計算定期支付利息的有價證券的應計利息 348

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

【案例】計算在到期日支付利息的有價證券的應計 利息 349

9.6.3 COUPDAYBS——計算當前付息期內截至成交日的天數 349

【案例】計算當前付息期內截至成交日的天數 350

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

【案例】計算成交日所在的付息期的天數 350

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

【案例】計算從成交日到下一個付息日之間的天數 351

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

【案例】計算成交日之後的下一個付息日 352

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

【案例】計算成交日和到期日之間的應付利息次數 353

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

【案例】計算成交日之前的上一付息日 353

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

【案例】計算有價證券的貼現率 354

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

【案例】計算定期支付利息的有價證券的修正期限 355

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

【案例】計算定期付息的面值￥100的有價證券的價格 356

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

【案例】計算折價發行的面值￥100的有價證券的價格 357

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

【案例】計算到期付息的面值￥100的有價證券的價格 357

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

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

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

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

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

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

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

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

9.6.18 MDURATION——計算假設面值為￥100的有價證券的麥考利久期 362

【案例】 計算假設面值為￥100的有價證券的麥考利 久期 362

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

【案例】計算一次性付息證券的利率 363

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

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

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

【案例】計算國庫券的等價債券收益 365

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

【案例】計算面值￥100的國庫券的價格 365

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

【案例】 計算國庫券的收益率 366

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

【案例】計算定期支付利息的有價證券的收益率 367

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

【案例】計算折價發行的有價證券的年收益率 368

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

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

9.7　轉換美元價格的格式 369

9.7.1 DOLLARDE——將分數格式的美元轉換為小數格式的美元 369

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

9.7.2 DOLLARFR——將小數格式的美元轉換為分數格式的美元 369

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

第10章　工程函數

10.1　比較數據 371

10.1.1 DELTA——比較兩個值是否相等 371

【案例】檢測商品銷量的變化情況 371

10.1.2 GESTEP——測試某個值是否大於或等於閾值 372

【案例】計算需要繳納稅金的人數 372

10.2　轉換數制 373

10.2.1 BIN2OCT——將二進制數轉換為八進制數 373

【案例】將二進制數轉換為八進制數 374

10.2.2 BIN2DEC——將二進制數轉換為十進制數 374

【案例】將二進制數轉換為十進制數 374

10.2.3 BIN2HEX——將二進制數轉換為十六進制數 375

【案例】將二進制數轉換為十六進制數 375

10.2.4 OCT2BIN——將八進制數轉換為二進制數 376

【案例】將八進制數轉換為二進制數 376

10.2.5 OCT2DEC——將八進制數轉換為十進制數 377

【案例】將八進制數轉換為十進制數 377

10.2.6 OCT2HEX——將八進制數轉換為十六進制數 378

【案例】將八進制數轉換為十六進制數 378

10.2.7 DEC2BIN——將十進制數轉換為二進制數 378

【案例】將十進制數轉換為二進制數 379

10.2.8 DEC2OCT——將十進制數轉換為八進制數 379

【案例】將十進制數轉換為八進制數 380

10.2.9 DEC2HEX——將十進制數轉換為十六進制數 380

【案例】將十進制數轉換為十六進制數 381

10.2.10 HEX2BIN——將十六進制數轉換為二進制數 381

【案例】將十六進制數轉換為二進制數 382

10.2.11 HEX2OCT——將十六進制數轉換為八進制數 382

【案例】將十六進制數轉換為八進制數 382

10.2.12 HEX2DEC——將十六進制數轉換為十進制數 383

【案例】將十六進制數轉換為十進制數 383

10.3　計算復數 384

10.3.1 COMPLEX——根據實部和虛部轉換復數 384

【案例】根據實部和虛部轉換復數 384

10.3.2 IMREAL——返回復數的實部 384

【案例】返回復數的實部 385

10.3.3 IMAGINARY——返回復數的虛部 385

【案例】返回復數的虛部 385

10.3.4 IMCONJUGATE——返回復數的共軛復數 386

【案例】返回復數的共軛復數 386

10.3.5 IMABS——計算復數的模 386

【案例】計算復數的模 386

10.3.6 IMPOWER——計算復數的整數冪 387

【案例】計算復數的整數冪 387

10.3.7 IMSQRT——計算復數的平方根 388

【案例】計算復數的平方根 388

10.3.8 IMARGUMENT——返回以弧度表示的復數的輻角 388

【案例】計算復數的輻角，以弧度表示 388

10.3.9 IMEXP——計算復數的指數 389

【案例】計算復數的指數 389

10.3.10 IMLN——計算復數的自然對數 389

【案例】 計算復數的自然對數 390

10.3.11 IMLOG10——計算復數以10為底的對數 390

【案例】 計算復數以10為底的對數 390

10.3.12 IMLOG2——計算復數以2為底的對數 391

【案例】計算復數以2為底的對數 391

10.3.13 IMSUM——計算兩個或多個復數的總和 391

【案例】計算多個復數的總和 391

10.3.14 IMSUB——計算兩個復數的差 392

【案例】 計算兩個復數的差 392

10.3.15 IMPRODUCT——計算復數的乘積 392

【案例】計算多個復數的乘積 393

10.3.16 IMDIV——計算兩個復數的商 393

【案例】計算兩個復數的商 393

10.3.17 IMSIN——計算復數的正弦值 394

【案例】計算復數的正弦值 394

10.3.18 IMSINH——計算復數的雙曲正弦值 394

【案例】 計算復數的雙曲正弦值 395

10.3.19 IMCOS——計算復數的餘弦值 395

【案例】計算復數的餘弦值 395

10.3.20 IMCOSH——計算復數的雙曲餘弦值 395

【案例】計算復數的雙曲餘弦值 396

10.3.21 IMTAN——計算復數的正切值 396

【案例】計算復數的正切值 396

10.3.22 IMSECH——計算復數的雙曲正切值 397

【案例】計算復數的雙曲正切值 397

10.3.23 IMCOT——計算復數的餘切值 397

【案例】計算復數的餘切值 398

10.3.24 IMSEC——計算復數的正割值 398

【案例】計算復數的正割值 398

10.3.25 IMCSC——計算復數的餘割值 399

【案例】計算復數的餘割值 399

10.3.26 IMCSCH——計算復數的雙曲餘割值 400

【案例】計算復數的雙曲餘割值 400

10.4　其他工程函數 400

10.4.1 CONVERT——將數字從一種度量系統轉換為另一種度量系統 400

【案例】 轉換食品的度量單位 401

10.4.2 BESSELJ——返回n階第1種貝塞爾函數值 402

【案例】計算n階第1種貝塞爾函數值 402

10.4.3 BESSELY——返回n階第2種貝塞爾函數值 402

【案例】計算n階第2種貝塞爾函數值 403

10.4.4 BESSELI——返回n階第1種修正的貝塞爾函數值 403

【案例】計算n階第1種修正貝塞爾函數值 403

10.4.5 BESSELK——返回n階第2種修正的貝塞爾函數值 404

【案例】計算n階第2種修正貝塞爾函數值 404

10.4.6 ERF——返回誤差函數 404

【案例】計算誤差函數在上下限之間的積分 405

10.4.7 ERF.PRECISE——返回誤差函數 405

10.4.8 ERFC——返回餘誤差函數 405

【案例】 計算餘誤差函數 405

10.4.9 ERFC.PRECISE——返回餘誤差函數 406

10.4.10 BITAND——返回兩個數按位“與”的結果 406

【案例】返回兩個數按位“與”的結果 407

10.4.11 BITOR——返回兩個數按位“或”的結果 408

【案例】返回兩個數按位“或”的結果 408

10.4.12 BITXOR——返回兩個數按位“異或”的結果 409

【案例】 返回兩個數按位“異或”的結果 409

10.4.13 BITLSHIFT——返迴向左移動指定位數後的值 410

【案例】 返迴向左移動指定位數後的值 410

10.4.14 BITRSHIFT——返迴向右移動指定位數後的值 411

【案例】返迴向右移動指定位數後的值 411

第11章　數據庫函數

11.1　計算數據庫中的數據 413

11.1.1 DPRODUCT——計算滿足條件的數字乘積 413

【案例】統計商品的維修記錄 413

11.1.2 DSUM——計算滿足條件的數字總和 414

【案例】 計算符合特定條件的員工工資總和 414

11.2　對數據庫中的數據進行常規統計 415

11.2.1 DAVERAGE——計算滿足條件的數字的平均值 415

【案例】計算符合特定條件的員工的平均月薪 415

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

【案例】計算公司各職級的員工人數 416

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

【案例】計算上班遲到的女性員工人數 417

11.2.4 DGET——返回滿足條件的單個值 417

【案例】 提取指定商品的價格 418

11.2.5 DMAX——返回滿足條件的最大值 418

【案例】提取指定商品的最大銷量 418

11.2.6 DMIN——返回滿足條件的最小值 419

【案例】提取指定商品的最小銷量 419

11.3　對數據庫中的數據進行散佈度統計 420

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

【案例】 計算男性員工工齡的樣本標準偏差 420

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

【案例】計算男性員工工齡的總體標準偏差 421

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

【案例】 計算男性員工工齡的樣本總體方差 422

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

【案例】計算男性員工工齡的總體方差 423

第12章　Web函數和宏表函數

12.1　Web函數 424

12.1.1 ENCODEURL——將文本轉換為URL編碼 424

12.1.2 WEBSERVICE——從Web服務中獲取網絡數據 424

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

12.2　宏表函數 425

12.2.1 GET.WORKBOOK——返回工作簿的相關信息 425

【案例】統計當前工作簿包含的工作表總數（二）（GET.WORKBOOK+T+NOW） 426

12.2.2 GET.CELL——返回單元格的相關信息 426

【案例】提取單元格中的公式（二） 427

12.2.3 EVALUATE——計算包含文本的表達式的值 428

【案例】計算單元格中的文本公式 428

【案例】計算不規則格式的文本公式（EVALUATE+SUBSTITUTE+ISTEXT） 428

第13章　在條件格式、數據驗證和圖表中使用公式

13.1　公式在條件格式中的應用 430

【案例】標記重復值 431

【案例】標記最大值 431

【案例】隱藏錯誤值 432

【案例】為奇數行數據設置灰色背景 432

【案例】設置固定不變的隔行背景色 433

【案例】設置生日到期提醒 434

13.2　公式在數據驗證中的應用 434

【案例】僅限輸入某種類型的數據 435

【案例】禁止輸入重復值 436

【案例】將輸入值限制在總數內 436

【案例】快速切換數據列表 437

【案例】創建可自動擴展的動態列表 437

【案例】創建二級下拉列表 438

13.3　在圖表中使用公式 439

13.3.1 圖表數據系列的SERIES公式 439

13.3.2 公式在圖表中的實際應用 440

【案例】製作可自動擴展的圖表 440

【案例】使用下拉列表控制餅圖的顯示 441

【案例】使用滾動條控制圖表的顯示 443

【案例】使用復選框控制圖表中數據系列的顯示 狀態 444