Excel公式与函数实用技巧辞典 超值双色印刷PDF电子书下载

Chapter 01 公式的应用技巧 1

1.1 认识公式 2

001 了解公式的组成结构 2

002 使用复制公式与填充的方法快速计算数据 3

003 自定义公式的应用 5

1.2 公式中的运算符 6

004 认识Excel 2010中应用的运算符 6

005 了解运算符的顺序 8

1.3 单元格地址的引用 9

006 使用相对引用选择参与计算的单元格 10

007 使用绝对引用固定某一单元格 10

008 使用混合引用固定引用行或列 11

009 使用跨表引用快速选择参与计算的数据源 13

1.4 公式审核 15

010 使用追踪引用查看值的引用 15

011 使用追踪从属单元格查看数据的应用范围 16

012 使用显示公式查看编辑的公式 17

013 使用公式求值分步计算单元格数据 17

014 对工作表中的公式进行错误检查 19

015 使用监视窗口查看工作表中的变化 20

1.5 公式中计算出错的处理方法 21

016 “＃＃＃＃＃”错误及解决方法 21

017 “＃DIV/0！”错误及解决方法 22

018 “＃N/A”错误及解决方法 23

019 “＃NAME？”错误及解决方法 24

020 “＃NULL！”错误及解决方法 25

021 “＃NUM！”错误及解决方法 26

022 “＃REF！”错误及解决方法 27

023 “＃VALUE！”错误及解决方法 29

Chapter 02 定义与使用名称技巧 31

2.1 定义名称的方法 32

024 使用名称框定义名称 32

025 根据所选内容创建名称 34

2.2 定义名称的对象 35

026 定义工作表名称区分工作表 35

027 定义单元格区域 37

028 在工作表中定义常量 39

029 在工作表中定义常量数组 39

2.3 管理名称 40

030 使用名称管理器新建名称 40

031 使用名称管理器删除不需要的名称 42

032 使用名称管理器编辑已有名称 43

2.4 使用名称工作 45

033 在公式中使用定义的名称 45

034 将定义的数组名称应用于公式 46

035 根据定义的名称计算数据 47

Chapter 03 函数基础与常用函数应用技巧 49

3.1 认识Excel函数 50

036 了解函数的组成结构 50

037 使用函数的优势 51

038 了解参数的可选与必选 52

039 通过帮助学习函数的应用 54

3.2 函数编辑与使用 56

040 使用“插入函数”对话框输入函数 56

041 通过单击“插入函数”按钮输入函数 58

042 使用自动完成功能输入函数 59

043 自定义函数 60

044 使用函数提示工具 61

3.3 常用函数的使用 63

045 使用SUM函数快速求和 63

046 使用AVERAGE函数求平均值 64

047 使用MAX函数查找出最大值 65

048 使用MIN函数查找出最小值 67

049 使用COUNT函数统计单元格个数 68

Chapter 04 财务函数应用技巧 70

4.1 基本财务函数 71

050 使用PV函数计算投资的现值 71

051 使用FV函数计算一笔投资的期值 72

052 使用RATE函数计算年金的各期利率 73

053 使用NPER函数计算总期数 75

4.2 计算本金和利息函数 77

054 用PMT函数计算贷款偿还额 77

055 用IPMT函数计算贷款在给定期间内支付的利息 78

056 用PPMT函数计算贷款在给定期间内偿还的本金 79

057 用ISPMT函数计算特定投资期内支付的利息 80

058 用CUMIPMT函数计算两个付款期之间累计支付的利息 81

059 使用CUMPRINC函数计算两个付款期之间累计支付的本金 83

060 使用EFFECT函数计算有效的年利率 84

061 使用NOMINAL函数计算名义年利率 85

4.3 计算投资预算函数 87

062 用FVSCHEDULE函数基于一系列复利计算未来值 87

063 使用NPV函数计算投资的净现值 88

064 使用XNPV函数计算现金流的净现值 89

4.4 计算收益函数 90

065 使用IRR函数计算一系列现金流的内部收益率 90

066 使用MIRR函数计算修正内部收益率 92

067 使用XIRR函数计算现金流的内部收益率 93

4.5 计算折旧值函数 94

068 使用AMORDEGRC函数计算每个结算期间的折旧值 94

069 使用AMORLINC函数计算每个结算期间的折旧值 95

070 使用DB函数计算一笔资产在给定期间内的折旧值 96

071 使用DDB函数双倍余额递减计算折旧值 98

072 使用VDB函数返回折旧值 99

073 使用SLN函数计算某项资产在一个期间内的线性折旧值 101

074 使用SYD函数按年限总和折旧法计算折旧值 102

4.6 计算债券函数 103

075 使用ACCRINT函数计算证券的利息 103

076 使用COUPDAYBS函数计算付息期与成交日的天数 104

077 使用COUPDAYS函数计算付息期的天数 105

078 使用COUPDAYSNC函数计算固定付息日间的天数 106

079 使用COUPNCD函数计算结算日之后的付息日 107

080 使用COUPNUM函数计算付息次数 109

081 使用COUPPCD函数计算结算日之前的付息日 110

082 使用DISC函数计算证券的贴现率 111

083 使用DURATION函数计算证券的修正期限 112

084 使用INTRATE函数计算一次性付息证券的利率 114

085 使用ODDFPRICE函数计算有价证券的价格 115

086 使用ODDFYIELD函数计算有价证券的收益率 116

087 使用ODDLPRICE函数计算有价证券的价格 118

088 使用ODDLYIELD函数计算有价证券的收益率 119

089 使用PRICE函数计算定期付息的有价证券价格 121

090 使用PRICEDISC函数计算折价发行的有价证券价格 122

091 使用PRICEMAT函数计算到期付息的有价证券价格 123

092 使用RECEIVED函数计算有价证券的金额 124

093 使用TBILLPRICE函数计算国库券的价格 125

094 使用TBILLYIELD函数计算国库券的收益率 126

095 使用YIELD函数计算有价证券的收益率 127

096 使用YIELDDISC函数计算证券的年收益率 128

097 使用YIELDMAT函数计算到期付息的有价证券年收益率 129

098 使用MDURATION函数计算假设面值的修正Macauley 131

099 使用TBILLEQ函数计算国库券的等效收益率 132

Chapter 05 查找与引用函数应用技巧 134

5.1 查找表中数据函数 135

100 使用CHOOSE函数根据序号从列表中选择对应的内容 135

101 使用HLOOKUP函数在区域或数组的行中查找数据 136

102 使用VLOOKUP函数在区域或数组的列中查找数据 138

103 使用LOOKUP函数以向量形式仅在单行单列中查找 139

104 使用INDEX函数以引用形式返回指定位置中的内容 140

5.2 引用表中数据函数 142

105 使用MATCH函数返回指定内容所在的位置 142

106 使用ADDRESS函数返回指定行号和列标对应的单元格地址 144

107 使用COLUMN函数返回单元格或单元格区域首列的列标 145

108 使用ROW函数返回单元格或单元格区域首行的行号 146

109 使用OFFSET函数根据给定的偏移量返回新的引用区域 148

110 使用TRANSPOSE函数转置数据区域的行列位置 149

111 使用INDIRECT函数返回由文本值指定的引用 151

5.3 显示数量函数 152

112 使用AREAS函数返回引用中包含的区域数量 152

113 使用COLUMNS函数返回数据区域包含的列数 153

114 使用ROWS函数返回数据区域包含的行数 154

Chapter 06 统计与求和函数应用技巧 156

6.1 基本计数函数 157

115 使用COUNTA函数返回指定内容所在的位置 157

116 使用COUNTBLANK函数返回单元格个数 158

6.2 条件统计函数 159

117 使用COUNTIF函数根据条件进行计数 160

118 使用COUNTIFS函数根据多条件进行计数 161

6.3 条件求和类函数 162

119 使用SUMIF函数根据条件求和 162

120 使用SUMIFS函数计算多重条件的和 163

6.4 条件均值类函数 164

121 使用AVERAGEA函数计算参数中非空值的平均值 165

122 使用AVERAGEIF函数计算指定条件的平均值 166

123 使用AVERAGEIFS函数计算多条件平均值 167

124 使用TRIMMEAN函数计算去除最高最低分的平均值 169

6.5 方差函数 170

125 使用VAR函数计算方差 170

126 使用VARA函数计算样本方差 171

127 使用VARP函数计算给定样本总体方差 172

128 使用VARPA函数计算整个样本总体方差 173

6.6 频率统计函数 174

129 使用FREQUENCY函数分段统计学生成绩 174

130 使用MODE函数计算出现频率最多的数值 175

131 使用MODE.SNGL函数返回数组或区域出现频率最多的数值 176

132 使用MODE.MULT函数返回阵列中频率最多的数值 177

6.7 极值应用函数 178

133 使用MAXA函数返回列表中的最大值 179

134 使用MINA函数返回列表中的最小值 180

135 使用LARGE函数返回指定的第N大的值 181

136 使用SMALL函数返回指定的第N小的值 182

6.8 概率分布函数 183

137 使用BETADIST函数计算概率密度 183

138 使用BETAINV函数计算概率密度的反函数值 184

139 使用BINOMDIST函数计算概率值 185

140 使用CHIDIST函数计算单尾概率 186

141 使用CHIINV函数计算单尾概率的反函数值 188

142 使用EXPONDIST函数计算指数分布 189

143 使用FDIST函数计算F概率分布 190

144 使用FINV函数计算F概率分布的反函数值 192

145 使用GAMMADIST函数计算伽马累积分布 193

146 使用GAMMAINV函数计算伽马累积分布函数的反函数值 194

147 使用HYPGEOMDIST函数计算超几何分布 196

148 使用LOGNORMDIST函数计算累积分布函数 197

149 使用LOGINV函数计算累积分布函数的反函数值 198

150 使用NEGBINOMDIST函数计算负二项式分布 199

151 使用NORMDIST函数计算正态分布函数 200

152 使用NORMINV函数计算正态累积分布函数的反函数值 202

153 使用NORMSDIST函数计算正态累积分布函数 203

154 使用NORMSINV函数计算正态累积分布函数的反函数值 205

155 使用STANDARDIZE函数计算正态化数值 206

156 使用POISSON函数计算泊松分布 208

157 使用TDIST函数计算t分布的百分点 210

158 使用TINV函数计算t分布的t值 211

159 使用WEIBULL函数计算韦伯分布 213

6.9 检验函数 214

160 使用CHITEST函数计算独立性检验值 215

161 使用FTEST函数计算F检验结果 216

162 使用TTEST函数计算t检验概率 217

163 使用ZTEST函数计算z检验的单尾概率值 218

6.10 排名应用类函数 220

164 使用RANK函数对数据进行排名 220

165 使用RANK.EQ函数返回一个数字在一组数字中的排位 221

166 使用RANK.AVG函数返回单元格地址 223

Chapter 07 日期和时间函数应用技巧 225

7.1 年月日函数 226

167 使用YEAR函数返回年份数 226

168 使用MONTH函数返回指定月数 228

169 使用DAY函数返回指定日期的具体天数 229

170 使用DATE数返回指定日期的序列号 231

171 使用EDATE函数返回指定日期 232

172 使DAYS360数计算两日期之间的天数 234

173 使用EOMONTH函数返回指定月份的最后一天 236

174 使用YEARFRAC函数返回两日期天数占全年天数的百分比 237

7.2 星期相关函数 239

175 使用WEEKDAY函数返回一周中的第几天的数值 239

176 使用WEEKNUM函数返回一年中的周数 241

7.3 工作日相关函数 242

177 使用NETWORKDAYS函数返回两日期之间的完整工作日数 243

178 使用NETWORKDAYS.INTL函数按周末参数返回工作日数 244

179 使用TODAY函数返回日期格式的当前日期 246

180 使用WORKDAY函数返回指定日期之前或之后的日期值 248

181 使用WORKDAY.INTL函数返回指定日期之前或之后的日期 249

7.4 返回时间段函数 250

182 使用HOUR函数返回小时数值 251

183 使用MINUTE函数返回分钟数值 252

184 使用NOW函数返回当前日期和时间 253

185 使用SECOND函数返回秒值 254

186 使用TIME函数返回时间的小数值 255

7.5 文本与日期和时间格式转换函数 257

187 使用DATEVALUE函数将文本格式的日期转换为序列号 257

188 使用TIMEVALUE函数将文本格式的时间转换为序列号 259

Chapter 08 数学和三角函数应用技巧 261

8.1 常规数学计算函数 262

189 使用ABS函数计算数字的绝对值 262

190 使用SIGN函数获取数值的符号 263

191 使用PRODUCT函数计算乘积 265

192 使用PI函数返回pi值 266

193 使用SQRT函数计算正平方根 268

194 使用SQRTPI函数计算pi乘积的平方根 269

195 使用MOD函数计算两数相除的余数 270

196 使用QUOTIENT函数返回商的整数部分 271

197 使用GCD函数计算最大公约数 271

198 使用LCM函数计算最小公倍数 273

8.2 舍入与取整函数 274

199 使用TRUNC函数返回数字的整数部分 274

200 使用INT函数返回永远小于原数字的最接近的整数 275

201 使用CEILING函数按条件向上舍入 276

202 使用EVEN函数沿绝对值增大的方向舍入到最接近的偶数 277

203 使用FLOOR函数按条件向下舍入 278

204 使用ROUND函数按指定位数对数字进行四舍五入 279

205 使用ODD函数沿绝对值增大的方向舍入到最接近的奇数 280

8.3 指数与对数函数 281

206 使用POWER函数计算数字的乘幂 281

207 使用EXP函数计算e的n次幂 282

208 使用LN函数计算自然对数 283

209 使用LOG函数计算以指定数字为底数的对数 284

8.4 三角函数 285

210 使用DEGREES函数将弧度转换为角度 285

211 使用RADIANS函数将角度转换为弧度 286

212 使用COS函数计算给定角度的余弦值 287

213 使用ACOS函数计算反余弦值 288

214 使用ACOSH函数计算数字反双曲余弦值 288

215 使用SIN函数计算角度的正弦值 289

216 使用ASIN函数计算数字的反正弦值 290

217 使用ASINH函数计算数字反双曲正弦值 291

218 使用ATAN函数计算以弧度表示的反正切值 292

219 使用ATAN2函数计算以坐标值表示的反正切值 293

220 使用ATANH函数计算反双曲正切值 294

221 使用COSH函数计算双曲余弦值 295

222 使用SINH函数计算双曲正弦值 296

223 使用TAN函数计算正切值 297

224 使用TANH函数计算双曲正切值 298

Chapter 09 文本函数应用技巧 299

9.1 字符串编辑函数 300

225 使用CONCATENATE函数将多个字符串合并到一处 300

226 使用EXACT函数比较两个字符串是否相同 301

227 使用LEN函数计算文本中的字符个数 302

228 使用LENB函数计算文本中代表字符的字节数 303

229 使用REPT函数按给定次数重复文本 305

230 使用FIXED函数将数字按指定的小数位数取整 305

9.2 返回文本内容函数 306

231 使用LEFT函数从文本左侧起提取指定个数的字符 307

232 使用LEFTB函数从文本左侧起提取指定字节数的字符 307

233 使用MID函数从文本指定位置起提取指定个数的字符 308

234 使用MIDB函数从文本指定位置起提取指定字节数的字符 310

235 使用RIGHT函数从文本右侧起提取指定个数的字符 311

236 使用RIGHTB函数从文本右侧起提取指定字节数字符 312

9.3 转换文本格式函数 313

237 使用ASC函数将全角字符转换为半角字符 313

238 使用WIDECHAR函数将半角字符转换为全角字符 314

239 使用CODE函数将文本字符串中第一个字符转换为数字代码 315

240 使用CHAR函数将数字代码转换为对应的字符 316

241 使用RMB函数将数字转换为带人民币符号￥的文本 317

242 使用DOLLAR函数将数字转换为带美元符号？的文本 318

243 使用TEXT函数将数字转换为文本 319

244 使用BAHTTEXT函数将数字转换为泰语文本 323

245 使用T函数将参数转换为文本 323

246 使用LOWER函数将文本转换为小写 325

247 使用UPPER函数将文本转换为大写 326

248 使用PROPER函数将每个单词的首字母转换为大写 326

249 使用VALUE函数将文本格式的数字转换为普通数字 327

9.4 查找与替换文本函数 328

250 使用FIND函数查找指定字符的位置 329

251 使用FINDB函数查找指定字符的位置 330

252 使用SEARCH函数以字符为单位查找指定字符的位置 331

253 使用SEARCHB函数以字节为单位查找指定字符的位置 333

254 使用REPLACE函数以字符为单位根据指定位置进行替换 334

255 使用REPLACEB函数按指定位置以字节为单位进行替换 335

256 使用SUBSTITUTE函数以指定文本进行替换 336

9.5 删除文本中的字符函数 338

257 使用CLEAN函数删除无法打印的字符 338

258 使用TRIM函数删除多余的空格 339

Chapter 10 逻辑函数应用技巧 340

10.1 逻辑值函数 341

259 使用TRUE函数返回逻辑值TRUE 341

260 使用FALSE函数返回逻辑值FALSE 343

10.2 交集、并集和求反函数 344

261 使用AND函数判断指定的多个条件是否同时成立 344

262 使用OR函数判断指定的任一条件为真就返回真 345

263 使用NOT函数对逻辑值求反 347

264 使用IF函数计算应缴税金 348

10.3 用IFERROR函数判断公式结果 349

Chapter 11 工程函数应用技巧 351

11.1 贝塞尔函数 352

265 使用BESSELI函数返回修正的贝塞尔函数 352

266 使用BESSELJ函数返回贝塞尔函数 353

267 使用BESSELK函数返回修正的贝塞尔函数 354

268 使用BESSELY函数返回贝塞尔函数 355

11.2 转换函数 356

269 使用BIN2DEC函数将二进制数转换为十进制数 356

270 使用BIN2HEX函数将二进制数转换为十六进制数 357

271 使用BIN2OCT函数将二进制数转换为八进制数 358

272 使用COMPLEX函数将实系数及虚系数转换为复数 359

273 使用CONVERT函数对数字进行量度转换 359

274 使用DEC2BIN函数将十进制数转换为二进制数 362

275 使用DEC2HEX函数将十进制数转换为十六进制数 363

276 使用DEC2OCT函数将十进制数转换为八进制数 364

277 使用HEX2BIN函数将十六进制数转换为二进制数 365

278 使用HEX2DEC函数将十六进制数转换为十进制数 366

279 使用HEX2OCT函数将十六进制数转换为八进制数 367

280 使用OCT2BIN函数将八进制数转换为二进制数 368

281 使用OCT2DEC函数将八进制数转换为十进制数 369

282 使用OCT2HEX函数将八进制数转换为十六进制数 370

11.3 复数函数 371

283 使用IMABS函数计算复数的绝对值 371

284 使用IMAGINARY函数计算复数的虚系数 372

285 使用IMREAL函数返回复数的实系数 373

286 使用IMCONJUGATE函数返回复数的共轭复数 374

287 使用IMSIN函数计算复数的正弦值 375

288 使用IMSQRT函数计算复数的平方根 376

289 使用IMSUB函数计算两个复数的差 377

11.4 其他工程函数 378

290 使用DELTA函数检测两个函数的相等性 378

291 使用GESTEP函数检验数字是否大于阈值 379

292 使用ERF函数计算误差函数的积分 380

293 使用ERFC函数计算补余误差函数 381

Chapter 12 其他函数应用技巧 382

12.1 数据库函数 383

294 使用DAVERAGE函数计算满足给定条件数据的平均值 383

295 使用DCOUNT函数计算数据库中满足给定条件的单元格个数 384

296 使用DCOUNTA函数计算满足给定条件的非空单元格个数 385

297 使用DGET函数计算数据库中满足给定条件的单个值 387

298 使用DMAX函数计算数据库中满足给定条件的最大数值 388

299 使用DMIN函数计算数据库中满足给定条件的最小数值 389

300 使用DPRODUCT函数计算满足给定条件数值的乘积 390

301 使用DSTDEV函数计算数据库中样本总体标准偏差 391

302 使用DSTDEVP函数计算数据库中总体标准偏差 392

303 使用DSUM函数计算数据库中满足给定条件的数字和 393

304 使用DVAR函数根据数据库条目样本估算数据的方差 395

305 使用DVARP函数计算数据的总体方差 396

12.2 信息函数 397

306 使用CELL函数返回引用单元格信息 397

307 使用ERROR.TYPE函数返回对应错误类型数值 400

308 使用INFO函数返回与当前操作环境有关的信息 401

309 使用N函数返回转换为数字后的值 402

310 使用TYPE函数返回表示值的数据类型的数字 403

311 使用NA函数返回错误值 404

12.3 检测类函数 405

312 使用ISBLANK函数判断测试对象 405

313 使用ISEVEN函数判断数值是否为偶数 407

314 使用ISODD函数判断数值是否为奇数 408

315 使用ISERR函数检测值是否有＃N/A以外的错误值 409

316 使用ISERROR函数检测值是否为错误值 410

317 使用ISLOGICAL函数检测值是否是逻辑值 411

318 使用ISNA函数检测值是否为＃N/A错误值 412

319 使用ISNONTEXT函数检测值是否不为文本值 413

320 使用ISNUMBER函数检测值是否为数值 414

321 使用ISREF函数检测值是否为引用 415

322 使用ISTEXT函数检测值是否为文本 416

Chapter 13 数组公式应用技巧 418

13.1 输入数组公式 419

323 输入一维横向数组 419

324 输入一维纵向数组 420

325 输入二维数组 421

326 由行列函数生成数组 422

327 将二维数组转换为一维数组 423

13.2 提取子数组 425

328 从一维数组中提取子数组 425

329 从二维数组中提取子数组 427

13.3 数组运算 428

330 在一个单元格中进行数组运算 428

331 多个单元格的数组运算 429

13.4 应用数组公式 430

332 对数据区域满足条件的值进行求和 430

333 为指定范围内的数值分类 432

334 创建交叉数据分析表 433

335 利用数组公式排序 433