Excel 2016函數(shù)與公式從入門到精通

目 錄
第1章 公式基礎
1.1 認識公式 2
1.1.1　 公式的組成 2
1.1.2　 公式中的幾種運算符 3
1.1.3　 公式中“?”“*”通配符的使用 4
1.2 公式的輸入與修改 4
1.2.1　 輸入公式 4
1.2.2　 修改公式 5
1.3 公式的復制與填充 7
1.3.1　 在連續(xù)單元格區(qū)域中填充公式 7
1.3.2　 在大范圍區(qū)域中填充公式 8
1.3.3　 將公式復制到其他位置 8
1.4 公式的保護、隱藏與顯示 9
1.4.1　 保護公式 9
1.4.2　 隱藏公式 11
1.5 為什么公式正確卻得到不正確的結(jié)果 13
1.5.1　 有文本數(shù)據(jù)參與運算 13
1.5.2　 空白單元格不為空 14
1.5.3　 實際的值與顯示的值不同 15
第2章 函數(shù)基礎
2.1 認識函數(shù) 19
2.1.1 函數(shù)的組成 19
2.1.2 了解函數(shù)的類別及其包含的參數(shù) 20
2.1.3 了解函數(shù)的參數(shù)規(guī)則 21
2.2 學習函數(shù)的用法 22
2.2.1 查看一個新函數(shù)的參數(shù) 22
2.2.2 使用幫助功能學習函數(shù) 23
2.3 函數(shù)輸入 23
2.3.1 手動輸入 23
2.3.2 使用“插入函數(shù)”按鈕輸入 24
2.4 函數(shù)修改與刪除 26
2.4.1 重設函數(shù)參數(shù) 26
2.4.2 保留暫未設置完整的函數(shù) 27
2.5 嵌套函數(shù) 27
2.5.1 當兩項成績都達標時給予合格 27
2.5.2 只對打印機產(chǎn)品進行調(diào)價 28
第3章 公式對單元格的引用
3.1 名稱定義和使用 33
3.1.1 為什么要定義名稱 33
3.1.2 快速定義名稱 33
3.1.3 修改名稱或刪除名稱 35
3.1.4 在公式中應用名稱 36
3.1.5 將公式定義為名稱 37
3.1.6 創(chuàng)建動態(tài)名稱 39
3.2 單元格引用 40
3.2.1 相對引用 40
3.2.2 絕對引用 41
3.2.3 引用當前工作表之外的單元格 42
3.2.4 跨工作簿引用 43
3.2.5 引用多個工作表中的同一單元格 44
第4章 了解數(shù)組與數(shù)組公式
4.1 數(shù)組類型 50
4.1.1 一維水平數(shù)組 50
4.1.2　 一維垂直數(shù)組 50
4.1.3　 二維數(shù)組 51
4.1.4　 常量數(shù)組在公式中的應用 52
4.1.5　 調(diào)用內(nèi)存數(shù)組 52
4.2 使用數(shù)組公式 53
4.2.1　 普通公式與數(shù)組公式 53
4.2.2　 多個單元格數(shù)組公式 54
4.2.3　 單個單元格數(shù)組公式 55
4.3 多項計算與數(shù)組公式的區(qū)別 56
4.4 修改或刪除數(shù)組公式 56
第5章 公式審核與修正
5.1 公式檢測與審核 61
5.1.1　 使用“監(jiān)視窗口”監(jiān)視數(shù)據(jù) 61
5.1.2　 使用“錯誤檢查”來檢查公式 62
5.1.3　 使用“追蹤”功能輔助查錯 63
5.1.4　 “顯示公式”功能查看全部公式 64
5.1.5　 使用F9查看公式中部分公式的結(jié)果 64
5.1.6　 幾種常見錯誤公式的修正 65
5.2 分析與解決公式返回錯誤值 69
5.2.1　 分析與解決“####”錯誤值 69
5.2.2　 分析與解決“#DIV/0！”錯誤值 69
5.2.3　 分析與解決“#N/A”錯誤值 70
5.2.4　 分析與解決“#NAME?”錯誤值 71
5.2.5　 分析與解決“#NUM!”錯誤值 73
5.2.6　 分析與解決“#VALUE!”錯誤值 74
5.2.7　 分析與解決“#REF!”錯誤值 75
5.2.8　 分析與解決“#NULL!”錯誤值 76
第6章 邏輯函數(shù)
6.1 邏輯判斷函數(shù) 79
6.1.1　 AND：判斷指定的多個條件是否全部成立 79
6.1.2　 OR：判斷參數(shù)值是否全部為TRUE 80
6.1.3　 NOT：判斷指定的條件不成立 82
6.2 根據(jù)邏輯判斷結(jié)果返回值 83
6.2.1　 IF：根據(jù)邏輯測試值返回指定值 84
6.2.2　 IFERROR：根據(jù)錯誤值返回指定值 89
第7章 文本函數(shù)
7.1 查找字符位置與提取文本 91
7.1.1　 FIND：返回字符串在另一個字符串中的起始位置 91
7.1.2　 FINDB：返回字符串在另一個字符串中的起始位置（以字節(jié)為單位） 92
7.1.3　 SEARCH：查找字符串中指定字符起始位置（不區(qū)分大小寫） 93
7.1.4　 SEARCHB：查找字符串中指定字符起始位置（以字節(jié)為單位） 94
7.1.5　 LEFT：從最左側(cè)提取指定個數(shù)字符 95
7.1.6　 LEFTB：從最左側(cè)提取指定個數(shù)字符（以字節(jié)為單位） 97
7.1.7　 RIGHT：從最右側(cè)開始提取指定字符數(shù)的字符 98
7.1.8　 RIGHTB：從最右側(cè)開始提取指定字節(jié)數(shù)的字符 99
7.1.9　 MID：提取文本字符串中從指定位置開始的特定個數(shù)的字符 100
7.1.10　 MIDB：提取文本字符串中從指定位置開始的特定個數(shù)的字符（以字節(jié)數(shù)為單位） 102
7.2 文本新舊替換 102
7.2.1　 REPLACE：將一個字符串中的部分字符用另一個字符串替換 102
7.2.2　 REPLACEB：將部分字符根據(jù)所指定的字節(jié)數(shù)用另一個字符串替換 104
7.2.3　 SUBSTITUTE：用新字符串替換字符串中的部分字符串 104
7.3 文本格式的轉(zhuǎn)換 107
7.3.1　 ASC：將全角字符更改為半角字符 107
7.3.2　 DOLLAR：四舍五入數(shù)值，并添加千分位符號和$符號 108
7.3.3　 RMB：四舍五入數(shù)值，并添加千分位符號和￥符號 108
7.3.4　 VALUE：將文本轉(zhuǎn)換為數(shù)值 109
7.3.5　 TEXT：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本 110
7.3.6　 FIXED：將數(shù)字顯示千分位符樣式并轉(zhuǎn)換為文本 113
7.3.7　 T：判斷給定的值是否是文本 113
7.3.8　 PROPER：將文本字符串的首字母轉(zhuǎn)換成大寫 114
7.3.9　 UPPER：將文本轉(zhuǎn)換為大寫形式 115
7.3.10　 LOWER：將文本轉(zhuǎn)換為小寫形式 115
7.3.11　 BAHTTEXT：將數(shù)字轉(zhuǎn)換為泰語文本 116
7.4 文本的其他操作 116
7.4.1　 CONCATENATE：將多個文本字符串合并成一個文本字符串 116
7.4.2　 LEN：返回文本字符串的字符數(shù) 118
7.4.3　 TRIM：刪除文本中的多余空格 119
7.4.4　 CLEAN：刪除文本中不能打印的字符 120
7.4.5　 EXACT：比較兩個文本字符串是否完全相同 120
7.4.6　 REPT：按照給定的次數(shù)重復顯示文本 121
第8章　　數(shù)學與三角函數(shù)
8.1　 數(shù)據(jù)計算函數(shù) 123
8.1.1　 SUM：求和 123
8.1.2　 SUMIF：根據(jù)指定條件對若干單元格求和 124
8.1.3　 SUMIFS：對區(qū)域中滿足多個條件的單元格求和 129
8.1.4　 MOD：求余 130
8.1.5　 PRODUCT：求所有參數(shù)的乘積 131
8.1.6　 QUOTIENT：求除法的整除數(shù) 131
8.1.7　 SUMPRODUCT：求數(shù)組間對應的元素乘積的和 132
8.1.8　 ABS：求出相應數(shù)值或引用單元格中數(shù)值的絕對值 137
8.2　 舍入函數(shù) 138
8.2.1　 INT：返回實數(shù)向下取整后的整數(shù)值 138
8.2.2　 ROUND：按指定位數(shù)對數(shù)值四舍五入 139
8.2.3　 TRUNC：不考慮四舍五入對數(shù)字進行截斷 140
8.2.4　 ROUNDUP（遠離零值向上舍入數(shù)值） 141
8.2.5　 ROUNDDOWN：靠近零值向下舍入數(shù)值 144
8.2.6　 CEILING.PRECISE：向上舍入到最接近指定數(shù)字的某個值的倍數(shù)值 145
8.2.7　 FLOOR.PRECISE：向下舍入到最接近指定數(shù)字的某個值的倍數(shù)值 147
8.2.8　 MROUND：按照指定基數(shù)的倍數(shù)對參數(shù)四舍五入 149
8.2.9　 EVEN：將數(shù)值向上舍入到最接近的偶數(shù) 150
8.2.10　 ODD：將數(shù)值向上舍入到最接近的奇數(shù) 151
8.3　 隨機數(shù)函數(shù) 151
8.3.1　 RAND：返回大于或等于0且小于1的均勻分布隨機數(shù) 151
8.3.2　 RANDBETWEEN：產(chǎn)生隨機整數(shù) 152
第9章　　統(tǒng)計函數(shù)
9.1 基礎統(tǒng)計函數(shù) 155
9.1.1　 AVERAGE：返回參數(shù)的平均值 155
9.1.2　 AVERAGEA：計算參數(shù)列表中非空單元格中數(shù)值的平均值 156
9.1.3　 AVERAGEIF：查找給定條件指定的單元格的平均值 157
9.1.4　 AVERAGEIFS：查找一組給定條件指定的單元格的平均值 158
9.1.5　 COUNT：統(tǒng)計參數(shù)列表中含有數(shù)值數(shù)據(jù)的單元格個數(shù) 161
9.1.6　 COUNTA：計算指定單元格區(qū)域中非空單元格的個數(shù) 162
9.1.7　 COUNTBLANK：計算空白單元格的個數(shù) 162
9.1.8　 COUNTIF：求滿足給定條件的數(shù)據(jù)個數(shù) 163
9.1.9　 COUNTIFS：統(tǒng)計一組給定條件所指定的單元格數(shù) 165
9.1.10　 MAX：返回一組值中的最大值 166
9.1.11　 MIN：返回一組值中的最小值 168
9.1.12　 QUARTILE.INC：返回數(shù)據(jù)集的四分位數(shù) 170
9.1.13　 PERCENTILE.INC：返回數(shù)組的K百分點值 171
9.1.14　 PERCENTRANK.INC：返回特定數(shù)值在一組數(shù)
　　　 中的百分比排名 172
9.1.15　 TRIMMEAN：截頭尾返回數(shù)據(jù)集的平均值 173
9.1.16　 GEOMEAN：返回幾何平均值 173
9.1.17　 HARMEAN：返回數(shù)據(jù)集的調(diào)和平均值 174
9.2　 方差、協(xié)方差與偏差 175
9.2.1　 VAR.S：計算基于樣本的方差 175
9.2.2　 VARA：計算基于樣本的方差 176
9.2.3　 VAR.P：計算基于樣本總體的方差 177
9.2.4　 VARPA：計算基于樣本總體的方差 177
9.2.5　 STDEV.S：計算基于樣本估算標準偏差 178
9.2.6　 STDEVA：計算基于給定樣本的標準偏差 179
9.2.7　 STDEV.P：計算樣本總體的標準偏差 180
9.2.8　 STDEVPA：計算樣本總體的標準偏差 180
9.2.9　 COVARIANCE.S：返回樣本協(xié)方差 181
9.2.10　 COVARIANCE.P：返回總體協(xié)方差 182
9.2.11　 DEVSQ：返回平均值偏差的平方和 183
9.2.12　 AVEDEV：計算數(shù)值的平均絕對偏差 183
9.3　 數(shù)據(jù)預測 184
9.3.1　 LINEST：對已知數(shù)據(jù)進行最佳直線擬合 184
9.3.2　 TREND：構(gòu)造線性回歸直線方程 185
9.3.3　 LOGEST：回歸擬合曲線返回該曲線的數(shù)值 186
9.3.4　 GROWTH：對給定的數(shù)據(jù)預測指數(shù)增長值 187
9.3.5　 FORECAST：根據(jù)已有的數(shù)值計算或預測未來值 187
9.3.6　 SLOPE：求一元線性回歸的斜率 188
9.3.7　 CORREL：求一元線性回歸的相關系數(shù) 189
9.4　 其他函數(shù) 189
9.4.1　 PERMUT：返回排列數(shù) 190
9.4.2　 PERMUTATIONA：允許重復的情況下返回排列數(shù) 190
9.4.3　 MODE.SNGL：返回數(shù)組中的眾數(shù) 191
9.4.4　 MEDIAN：求一組數(shù)的中值 192
9.4.5　 MODE.MULT：返回一組數(shù)據(jù)集中出現(xiàn)頻率最高的數(shù)值 192
9.4.6　 FREQUENCY：頻率分布統(tǒng)計 193
9.4.7　 PROB：返回數(shù)值落在指定區(qū)間內(nèi)的概率 194
9.4.8　 KURT：返回數(shù)據(jù)集的峰值 195
9.4.9　 SKEW：返回分布的偏斜度 196
第10章　　日期與時間函數(shù)
10.1　 返回當前日期和時間 198
　 10.1.1　 TODAY：返回當前日期 198
　 10.1.2　 NOW：返回當前的日期和時間 199
10.2　 用序列號表示或計算日期和時間 200
　 10.2.1　 DATE：構(gòu)建標準日期 200
　 10.2.2　 TIME：構(gòu)建標準時間 201
　 10.2.3　 YEAR：返回某日期對應的年份 202
　 10.2.4　 MONTH：返回以序列號表示的日期中的月份 203
　 10.2.5　 DAY：返回以序列號表示的日期中的天數(shù) 204
　 10.2.6　 EOMONTH：從序列號或文本中算出指定月最后一天的序列號 205
　 10.2.7　 WEEKDAY：返回制定日期對應的星期數(shù) 207
　 10.2.8　 WEEKNUM：返回序列號對應的一年中的第幾周 209
　 10.2.9　 HOUR：返回時間值的小時數(shù) 209
　 10.2.10　 MINUTE：返回時間值的分鐘數(shù) 210
　 10.2.11　 SECOND：返回時間值的秒數(shù) 212
10.3　 期間差 213
　 10.3.1　 DAYS360：返回兩日期間相差的天數(shù)（按照一年360天的算法） 213
　 10.3.2　 DATEDIF：用指定的單位計算起始日和結(jié)束日之間的天數(shù) 215
　 10.3.3　 NETWORKDAYS：計算某時段的工作日天數(shù) 217
　 10.3.4　 WORKDAY：從序列號或文本中計算出指定工作日之后的日期 218
　 10.3.5　 YEARFRAC：從開始到結(jié)束日所經(jīng)過的天數(shù)占全年天數(shù)的比例 219
　 10.3.6　 EDATE：計算出所制定月數(shù)之前或之后的日期 220
10.4 文本日期與文本時間的轉(zhuǎn)換 221
　 10.4.1　 DATEVALUE：將日期字符串轉(zhuǎn)換為可計算的序列號 221
　 10.4.2　 TIMEVALUE：將時間轉(zhuǎn)換為對應的小數(shù)值 222
第11章　　查找與引用函數(shù)
11.1　 數(shù)據(jù)的引用 227
　 11.1.1　 CHOOSE：根據(jù)給定的索引值，返回數(shù)值參數(shù)清單中的數(shù)值 227
　 11.1.2　 ROW：返回引用的行號函數(shù) 230
　 11.1.3　 COLUMN：返回引用的列號函數(shù) 233
　 11.1.4　 ROWS：返回引用或數(shù)組的行數(shù) 234
　 11.1.5　 COLUMNS：返回數(shù)組或引用的列數(shù) 235
　 11.1.6　 OFFSET：以制定引用為參照數(shù)，通過給定偏移量得到新引用 235
　 11.1.7　 INDIRECT：返回由文本字符串指定的引用 239
　 11.1.8　 AREAS：返回引用中包含的區(qū)域個數(shù) 241
11.2　 數(shù)據(jù)的查找 242
　 11.2.1　 VLOOKUP：查找指定的數(shù)值并返回當前行中指定列處的數(shù)值 242
　 11.2.2　 LOOKUP：從向量（數(shù)組）中查找一個數(shù)值 248
　 11.2.3　 MATCH：返回指定方式下與制定數(shù)值匹配的元素的相應位置 253
　 11.2.4　 INDEX：返回指定行列交叉處引用的單元格 254
第12章　　信息函數(shù)
12.1 信息獲得函數(shù) 259
　 12.1.1　 CELL：返回單元格的信息 259
　 12.1.2　 INFO：返回當前操作環(huán)境的信息 262
　 12.1.3　 TYPE：返回單元格內(nèi)的數(shù)值類型 263
12.2 IS 函數(shù) 263
　 12.2.1　 ISBLANK：判斷測試對象是否為空單元格 263
　 12.2.2　 ISERR：檢測一個值是否為#N/A以外的錯誤值 266
　 12.2.3　 ISERROR：檢測一個值是否為錯誤值 266
　 12.2.4　 ISEVEN：檢測一個值是否為偶數(shù) 268
　 12.2.5　 ISLOGICAL：檢測一個值是否為邏輯值 269
　 12.2.6　 ISNA：檢測一個值是否為#N/A錯誤值 269
　 12.2.7　 ISNONTEXT：檢測一個值是否不是文本 270
　 12.2.8　 ISNUMBER：檢測一個值是否為數(shù)值 271
12.2.9　 ISODD：檢測一個值是否為奇數(shù) 272
　 12.2.10　 ISREF：檢測一個值是否為引用 273
　 12.2.11　 ISTEXT：檢測一個值是否為文本 274
　 12.2.12　 ISFORMULA：檢測是否包含公式的單元格引用 275
12.3 N：將參數(shù)轉(zhuǎn)換為數(shù)值形式 276
第13章　　 財務函數(shù)
13.1　 投資計算函數(shù) 279
　 13.1.1　 FV：基于固定利率及等額分期付款方式返回未來值 279
　 13.1.2　 FVSCHEDULE：計算投資在變動或可
　 調(diào)利率下的未來值 280
　 13.1.3　 IPMT：返回給定期數(shù)內(nèi)利息償還率 281
　 13.1.4　 ISPMT：等額本金還款方式下的利息計算 282
　 13.1.5　 PMT：返回貸款的每期等額付款額 283
　 13.1.6　 PPMT：返回給定期間內(nèi)本金償還額 284
　 13.1.7　 NPV：返回一項投資的凈現(xiàn)值 285
　 13.1.8　 PV：返回投資的現(xiàn)值 286
　 13.1.9　 XNPV：返回一組不定期現(xiàn)金流的凈現(xiàn)值 287
　 13.1.10　 EFFECT：計算實際的年利率 287
　 13.1.11　 NOMINAL：計算名義利率 288
　 13.1.12　 NPER：返回某項投資的總期數(shù) 288
　 13.1.13　 RRI：返回投資增長的等效利率 289
13.2　 拆舊計算函數(shù) 290
　 13.2.1　 DB：使用固定余額遞減法計算折舊值 290
　 13.2.2　 DDB：使用雙倍余額遞減法計算折舊值 291
　 13.2.3　 SLN：使用線性折舊法計算折舊值 292
　 13.2.4　 SYD：使用年限總和折舊法計算折舊值 293
　 13.2.5　 VDB：使用雙倍余額遞減法計算折舊值 294
13.3 償還率計算函數(shù) 296
　 13.3.1　 IRR：計算內(nèi)部收益率 296
　 13.3.2　 MIRR：計算修正內(nèi)部收益率 296
　 13.3.3　 RATE：返回年金的各期利率 297
　 13.3.4　 XIRR：計算不定期現(xiàn)金流的內(nèi)部收益率 298