Excel 2003函数在办公中的应用PDF电子书下载

第1章 Microsoft Excel 2003基础知识 1

1．1　Microsoft Excel 2003工作环境 2

1．2　数据的收集与格式化 3

1．2.1　逐个输入法 3

1．2.2　自动填充序列 4

1．2.3　导入外部数据 5

1．2.4　单元格的选择 7

1．2.5　单元格格式化 7

1．3　数据的分析与组织 9

1．3.1　数据排序 10

1．3.2　数据筛选 11

1．3.3　数据分类汇总 12

1．3.4　数据透视表 13

1．3.5 图表 14

1．4　初步了解Excel公式 15

1．4.1　公式的组成 16

1．4.2　公式的输入方法 16

1．4.3　编辑公式 17

1．5　公式中的运算符 18

1．5.1　算术运算符 18

1．5.2　比较运算符 18

1．5.3　文本运算符 19

1．5.4　引用运算符 19

1．5.5　运算符的优先级 19

1．5.6　使用括号更改运算顺序 19

1．6　公式中的单元格引用方式 20

1．6.1 在公式中使用相对、绝对和混合引用 21

1．6.2　引用其他工作表中的单元格 23

1．6.3 引用其他工作簿的单元格 23

1．7　在公式中使用名称 24

1．7.1　使用名称的意义 24

1．7.2　定义名称的方法及注意事项 24

1．7.3　名称应用举例 25

1．8　使用数组公式进行计算 30

1．8.1　数组公式的概念 30

1．8.2　创建数组公式 30

1．8.3　编辑数组公式 32

1．8.4　选择包含数组公式的单元格区域 33

第2章 在公式中使用函数 35

2．1　在公式中使用函数的优势 36

2．1.1　使用函数简化公式 36

2．1.2　实现一般公式无法实现的计算 37

2．1.3　实现智能判断 37

2．2　函数的结构与参数 38

2．2.1 函数的基本结构与组成 38

2．2.2 函数的参数类型 39

2．3　在Excel中输入函数的方法 41

2．3.1 利用手动输入法输入函数 41

2．3.2　利用插入法输入函数 42

2．4　Excel函数的分类 44

2．4.1　文本函数 44

2．4.2 日期和时间函数 45

2．4.3　数学和三角函数 46

2．4.4　逻辑函数 48

2．4.5　统计函数 48

2．4.6　财务函数 50

2．4.7　查找和引用函数 52

2．4.8　数据库函数 53

2．4.9　信息函数 53

2．4.10　工程函数 54

2．4.11　用户自定义函数 55

2．5　公式中的常见错误与公式审核 55

2．5.1 常见的公式错误 56

2．5.2　公式审核 56

2．6　使用函数的其他技巧 59

2．6.1　在条件格式中应用函数 59

2．6.2　在数据有效性规则中应用函数 60

2．6.3　在Excel图表中应用函数 63

2．6.4　打印公式 66

2．6.5　保护Excel工作表中的函数 67

第3章 文本函数 69

3．1　文本的基本操作 70

3．1.1 获取字符串长度——LEN（或LENB） 70

3．1.2　合并多个字符串——CONCATENATE 72

3．1.3　返回特定字符——MID（或MIDB） 73

3．1.4　查找文本字符串——FIND（或FINDB） 75

3．1.5 从指定位置替换文本——SEARCH（或SEARCHB）、REPLACE（或REPLACEB） 76

3．1.6 返回左边字符——LEFT（或LEFTB） 78

3．1.7　返回右边字符——RIGHT（或RIGHTB） 79

3．1.8　替换文本——SUBSTITUTE　 81

3．1.9　清除文本中的多余空格——TRIM　 83

3．1.10　比较字符串是否相同——EXACT　 84

3．1.11 复制文本——REPT　 88

3．2　文本的转换 90

3．2.1 将全角字符更改为半角字符——ASC 90

3．2.2 改变文本大小写——UPPER、LOWER、PROPER 91

3．2.3 将数值转换为按指定格式表示的文本——TEXT 94

3．2.4　将数值四舍五入并转换为可带有千位分隔符和小数点的文本——FIXED 101

3．2.5　按照货币格式将数值四舍五入到指定位数并转换成文 本——RMB或DOLLAR 104

3．2.6 数值与文本之间的相互转换——VALUE、T 107

3．2.7　字符与数字代码之间的相互转换——CODE、CHAR 108

第4章 日期和时间函数 109

4．1　Excel如何处理日期和时间 110

4．1.1　Excel中的日期序列号和时间序列号 110

4．1.2　1900年和1904年日期系统 111

4．1.3　在Excel中输入时间和日期 112

4．1.4　Excel如何解释以两位数字表示的年份 113

4．2　显示指定的日期和时间 113

4．2.1　显示当前时间和日期——NOW、TODAY 113

4．2.2　计算员工工龄——YEAR 116

4．2.3　获取当前月份——MONTH 117

4．2.4　计算日期对应的星期数——WEEKDAY 121

4．2.5 与显示日期和时间相关的其他函数——DAY、HOUR、MINUTE、SECOND 124

4．3 日期相关的计算、合并及转换 125

4．3.1　返回日期和时间的序列号——DATE、TIME 125

4．3.2 文本表示的日期和时间转换成序列号——DATEVALUE、TIMEVALUE 128

4．3.3　准确计算员工年龄——DATEDIF 130

4．3.4　计算计息天数——DAYS360 131

4．3.5 计算两个日期之间的工作日——NETWORKDAYS 132

4．3.6　确定交货日期——WORKDAY 135

4．3.7　计算起止日期——EOMONTH 136

4．3.8　求两个日期之间相差的星期数——WEEKNUM 137

4．3.9　计算有效日期——EDATE 139

4．3.10　计算员工年龄——YEARFRAC 140

第5章　数学函数 143

5．1　常用数学函数 144

5．1.1　对指定的单元格区域进行求和——SUM 144

5．1.2　对满足条件的单元格区域进行求和——SUMIF 145

5．1.3　返回一组数字的乘积值——PRODUCT 147

5．1.4　返回对应数值的乘积之和——SUMPRODUCT 148

5．1.5　返回数值的平方根或平方和——SQRT、SUMSQ 149

5．1.6 其他与平方计算相关的函数——SUMX2MY2、SUMX2PY2、SUMXMY2 150

5．1.7　返回分类汇总计算结果——SUBTOTAL 151

5．1.8　返回商的整数和余数部分——QUOTIENT、MOD 153

5．1.9　计算最大公约数和最小公倍数——GCD、LCM 155

5．1.10　返回数字的符号——SIGN 156

5．1.11　返回指定数值的阶乘——FACT 157

5．1.12　返回随机数——RAND 158

5．1.13　返回指定两数之间的随机整数——RANDBET-WEEN 159

5．2　数值处理函数 160

5．2.1　返回数字的绝对值——ABS 161

5．2.2　返回不大于当前数值的最大整数——INT 161

5．2.3　按照指定的位数截取数值——TRUNC、ROUN DDOWN、ROUNDUP、ROUND 162

5．2.4 将当前数值向上舍入为偶数或奇数——EVEN、ODD 165

5．2.5 将当前数值按照指定的条件向下或向上舍入——FLOOR、CEILING 166

5．3　矩阵函数 168

5．3.1 返回数组的矩阵行列式值——MDETERM 168

5．3.2　返回数组矩阵的逆矩阵、返回矩阵的乘积——MINVERSE、MMULT 171

5．4　三角函数 173

5．4.1 角度单位与弧度单位的相互转换——RADIANS、DEGREES、PI 173

5．4.2　返回给定角度的正弦值、余弦值——SIN、COS 174

5．4.3　返回给定数值的反正弦值、反余弦值——ASIN、ACOS 176

5．5　指数、对数函数 178

5．5.1 返回指定数值的乘幂——POWER 178

5．5.2　返回e的n次幂——EXP 179

5．5.3　返回指定底数的对数——LOG 179

5．5.4　自然对数与常用对数——LN、LOG10 180

第6章　逻辑函数 181

6．1　逻辑函数基础知识 182

6．1.1　条件判断 182

6．1.2　返回逻辑值——TRUE、FALSE 182

6．1.3 对当前逻辑进行取反运算——NOT 182

6．1.4　依据逻辑判断的真假返回不同的结果——IF 183

6．1.5　多重条件判断——OR、AND 185

6．2　应用条件格式 187

6．2.1　定义条件格式 187

6．2.2　使用基于公式的条件格式 188

6．3　逻辑函数综合应用实例 189

6．3.1　指定有效的单元格输入 189

6．3.2　使用逻辑函数判断故障当前的处理状况 190

6．3.3　利用数组公式统计故障数量 192

6．3.4　对工作表中实施保护策略 193

第7章　统计函数 197

7．1　基础统计函数 198

7．1.1　统计单元格个数——COUNT（或COUNTA） 198

7．1.2　统计空白单元格个数——COUNTBLANK 201

7．1.3　按条件统计——COUNTIF 202

7．1.4 计算频率分布——FREQENCY 208

7．1.5　返回频率最多的数值——MODE 210

7．1.6　返回最大值——MAX（或MAXA） 211

7．1.7　返回最小值——MIN（或MINA） 213

7．1.8　返回中值——MEDIAN 215

7．1.9　计算平均值——AVERAGE（或AVERAGEA） 217

7．1.10　计算数据集的内部平均值——TRIMMEAN 219

7．2　排序函数 221

7．2.1　返回排位——RANK 221

7．2.2　返回第k个最大值——LARGE 224

7．2.3　返回第k个最小值——SMALL 225

7．3　统计函数的高级应用 228

7．3.1　返回一组数据与其均值的绝对偏差的平均值——AVEDEV 228

7．3.2　返回数据集的峰值——KURT 230

7．3.3　根据现有数据预测指数增长值——GROWTH 231

7．3.4　返回样本的方差——VAR（或VARA） 234

7．3.5　返回数据点与各自样本平均值偏差的平方和——DEVSQ 236

7．3.6　返回超几何分布——HYPGEOMDIST 236

7．3.7　返回泊松分布——POISSON 240

第8章　财务函数 243

8．1　常用理财函数 244

8．1.1 实际利率与名义利率之间的相互转换——EFFECT、NOMINAL 244

8．1.2 基于固定利率返回贷款的分期付款额——PMT 245

8．1.3　计算平均收益率或固定利率——RATE 246

8．1.4　返回某项投资的总期数——NPER 248

8．1.5　返回一项投资的现值——PV 249

8．1.6　返回一项投资的未来值——FV 250

8．1.7　计算指定期数的某一具体还款中的利息与本金部分——IPMT、PPMT 251

8．1.8　返回在给定期间内累计偿还贷款的利息与本金——CUMIPMT、CUMPRINC 252

8．2　贴现和折旧函数 253

8．2.1 依据定期的现金流计算投资净现值——NPV 253

8．2.2 计算一组定期现金流的内部收益率（内含报酬率）——IRR 255

8．2.3　依据不定期的现金流计算投资净现值——XNPV 256

8．2.4　计算不定期现金流的内部收益率——XIRR 257

8．2.5　解决多重利率的计算问题——MIRR 258

8．2.6　计算本金在波动或可调利率下的未来值——FVSCHDULE 261

8．2.7　返回固定资产的线性折旧值——SLN 262

8．2.8　固定余额递减法计算折旧值——DB 263

8．2.9　双倍余额递减法计算折旧值——DDB 264

8．2.10　返回指定期间的资产折旧值——VDB 265

8．3　证券金融函数 266

8．3.1 计算按期付息的有价证券的利息累计值——ACCRINT 267

8．3.2　返回一次性付息证券的应计利息——ACCRINTM 268

8．3.3 返回与付息期天数相关的计算结果——COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD 269

8．3.4　返回在成交日和到期日之间的付息次数——COUPNUM 270

8．3.5　返回一次性付息证券的利率——INTRATE 271

8．3.6　返回有价证券到期收回的金额——RECEIVED 272

8．3.7　返回有价证券的贴现率——DISC 273

8．3.8 返回面值＄100元的有价证券价格——ODDFPRICE、ODDLPRICE 274

8．3.9　返回面值＄100有价证券的收益率——ODDFYIELD、ODDLYIELD 276

8．3.10　返回定期付息的有价证券的价格——PRICE 277

8．3.11　返回折价发行的有价证券的价格——PRICEDISC 278

8．3.12　返回到期付息的有价证券的价格——PRICEMAT 279

8．3.13　返回定期付息的有价证券的年收益率——YIELD 280

8．3.14　返回折价发行的有价证券的年收益率——YIELDDISC 281

8．3.15 返回到期付息的有价证券的年收益率——YIELDMAT 281

第9章　查找和引用函数 283

9．1　查找函数 284

9．1.1 从参数列表中返回数值——CHOOSE 284

9．1.2 水平查找——HLOOKUP 285

9．1.3　垂直查找——VLOOKUP 288

9．1.4　查找数据——LOOKUP 290

9．1.5　在数组中查找值的位置——MATCH 294

9．1.6　返回指定内容——INDEX 296

9．2　引用函数 301

9．2.1　返回引用地址——ADDRESS 301

9．2.2　计算区域个数——AREAS 302

9．2.3　返回列标和行号——COLUMN、ROW 302

9．2.4　返回引用的数目——COLUMNS、ROWS 304

9．2.5 返回由文本字符串指定的引用——INDIRECT 305

9．2.6　返回新的引用——OFFSET 306

9．3　其他相关函数 311

9．3.1　创建快捷方式——HYPERLINK 312

9．3.2　转置单元格区域——TRANSPOSE 314

9．3.3　检索数据透视表中的数据——GETPIVOTDATA 316

第10章　数据库和列表 323

10．1　Excel数据库和列表简介 324

10．1.1 了解数据库和列表 324

10．1.2　创建列表 325

10．1.3　插入汇总行 325

10．1.4　冻结标题行 326

10．2　对数据进行自动筛选 326

10．3　使用高级条件筛选 329

10．3.1　创建条件区域 329

10．3.2　多重条件筛选 331

10．3.3　使用公式进行筛选 332

10．4　数据库和列表函数 333

10．4.1　统计各个地区全年订货金额的总和——DSUM 333

10．4.2　返回华北地区在第一季度订单金额的平均值——DAVERAGE 336

10．4.3 找出全年最高一笔订货金额的相关信息——DGET 336

10．4.4　找出天津地区全年最大和最低的订货金额——DMAX、DMIN 337

10．4.5　统计销售人员王先生全年经手的订单数量——DCOUNTA 338

10．4.6 根据筛选结果估算全年订单金额的标准偏差和方差——DSTDEV、DVAR 338

10．5　创建动态汇总图表 339

第11章　信息函数 343

11．1　获取当前工作表中的相关信息 344

11．1.1　返回单元格信息——CELL 344

11．1.2　判断错误类型——ERROR、TYPE 346

11．1.3　返回当前操作环境的信息——INFO 347

11．1.4　返回数值的类型——TYPE 348

11．2　IS类函数 349

11．2.1 判断单元格是否为空——ISBLANK 350

11．2.2 判断参数是否为错误值——ISERR、ISERROR 355

11．2.3 判断参数是否是逻辑值——ISLOGICAL 358

11．2.4　判断错误值是否是＃N/A——ISNA 361

11．2.5 判断参数是否是非字符串——ISNONTEXT 363

11．2.6　判断参数是否是数字——ISNUMBER 365

11．2.7　判断参数是否是引用——ISREF 367

11．2.8 判断参数是否是文本——ISTEXT 367

11．3　其他信息函数 368

11．3.1 判断参数是否是奇数或偶数——ISODD、ISEVEN 369

11．3.2　产生错误值（#N/A）——NA 371

11．3.3 返回转化为数值后的值——N 373

第12章　其他函数 375

12．1　数据的转换 376

12．1.1　度量转换——CONVERT 376

12．1.2　进制转换 378

12．2　数据的比较 379

12．2.1 判断两数值是否相等——DELTA 380

12．2.2　检测数字是否大于某个临界值——GESTEP 380

12．3　与复数相关的函数 381

12．3.1 将实系数和虚系数转换为复数——COMPLEX 382

12．3.2　返回复数的模——IMABS 382

12．3.3 从复数中取出实数部分和虚数部分——IMREAL、IMAGINARY 383

12．3.4　返回共轭复数——IMCONJUGATE 384

12．3.5　求复数的和——IMSUM 384

12．3.6　求复数的差——IMSUB 385

12．3.7　求复数的乘积——IMPRODUCT 385

12．3.8　求复数的商——IMDIV 386

12．4　自定义函数 387

12．4.1 关于VBA 387

12．4.2　VBA的开发环境 387

12．4.3　自定义函数举例 389

第13章　函数综合应用范例 393

13．1　制作参赛选手成绩排行榜 394

13．1.1 案例说明 394

13．1.2　案例实现 395

13．2　绘制项目实施追踪图 397

13．2.1 案例说明 397

13．2.2　案例实现 399

13．3　制作年度销售业绩统计表　 408

13．3.1 案例说明　 408

13．3.2 案例实现　 409

13．4　综合运用财务函数进行投资分析　 417

13．4.1 案例说明　 417

13．4.2 案例实现 418