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

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