Excel函数经典应用实例

第1章Excel2007快速入门 1

1.1输入成绩表数据 1

1.2管理考勤表 4

1.3格式化员工薪资记录表 7

1.4美化排版公司设备登记表 14

第2章公式与函数的运用 17

2.1计算采购成本计算表中的数据 17

2.2计算年度考核表中的数据 20

2.3计算家庭收入表中的数据 26

第3章管理表格中的数据 30

3.1排序期中考试成绩 30

3.2分类汇总CPU报价表 36

3.3高级筛选员工销售业绩表 39

第4章会计账务表单 43

4.1创建银行账户数据表 43

4.1.1定义银行账户数据 43

4.1.2创建银行账户数据 44

4.2创建日记账表 48

4.2.1编制日记账表 48

4.2.2冻结窗格 49

4.2.3查询日记账 50

4.3创建明细账表 52

4.3.1编制工作表 52

4.3.2计算发生额及余额 52

4.3.3分类汇总发生额 55

4.4创建企业账务总账表 57

第5章固定资产表单 62

5.1直线折旧法 62

5.1.1直线折旧公式 63

5.1.2计算净残值 63

5.1.3计算月折旧额 64

5.1.4输入本年折旧月数 65

5.1.5计算本年折旧额 66

5.1.6计算已提折旧月数 66

5.1.7计算总折旧额 67

5.2单倍余额递减法 68

5.2.1编制单倍余额递减折旧表 68

5.2.2计算净残值 69

5.2.3计算已提折旧月数 70

5.2.4计算已使用年数 71

5.2.5计算本年折旧额 72

5.3双倍余额递减法 73

5.3.1编制双倍余额递减折旧表 73

5.3.2计算本年折旧额 74

5.4年数总和法 75

5.4.1编制年数总和折旧表 75

5.4.2计算本年折旧额 76

第6章会计报表 78

6.1资产负债表 78

6.1.1编制资产负债表 78

6.1.2编制科目余额表 79

6.1.3设置报表时间 80

6.1.4导入流动资产年初数 80

6.1.5导入长期投资和固定资产

年初数 83

6.1.6导入无形资产和递延税款

年初数 84

6.1.7导入流动负债年初数 85

6.1.8导入长期负债和所有者权益

年初数 87

6.1.9计算年初数的合计项目 88

6.1.10导入期末数 91

6.2利润表 92

6.2.1编制利润表 92

6.2.2计算利润 93

6.3现金流量表 94

6.3.1编制现金流量表 94

6.3.2计算现金流量 95

6.4会计报表分析法 97

6.4.1比较分析法 97

6.4.2比率分析法 100

6.4.3趋势分析法 101

第7章固定资产分析 102

7.1分析双倍余额递减法求得的

折旧费用 102

7.1.1利用数据透视表分析余额

递减折旧费用 102

7.1.2利用图表分析余额递减

折旧费用 104

7.2分析直线法求得的折旧费用 106

7.2.1利用数据透视表分析直线

折旧费用 106

7.2.2利用图表分析直线折旧

费用 107

第8章部门成本预测 109

8.1高低点法成本预测表 109

8.1.1编制高低点法成本预测表 110

8.1.2计算年度 110

8.1.3计算最高产量 111

8.1.4计算最低产量 112

8.1.5计算最高成本 113

8.1.6计算最低成本 114

8.1.7计算其他成本 114

8.2因素分析法成本预测表 116

8.2.1编制因素分析法成本

预测表 116

8.2.2计算成本预测 116

8.3回归直线法成本预测表 120

8.3.1编制回归直线法成本

预测表 120

8.3.2计算单位变动成本 120

8.3.3计算固定成本和下一年度

预测总成本 121

8.4利润推测法成本预测表 122

第9章部门成本分析 125

9.1单位产品成本核算表 125

9.1.1计算日期 125

9.1.2计算合计生产成本 126

9.2单位成本分析图 127

9.3单位成本饼图分析 130

9.4产品总成本核算表 131

9.4.1计算生产费用合计 131

9.4.2计算产品生产成本合计 132

9.4.3计算产品总成本 133

9.5产品总成本分析图 133

第10章员工培训管理 135

10.1新员工培训成绩统计表 135

10.1.1计算平均成绩 135

10.1.2计算总成绩并确定名次 137

10.2设计新员工培训成绩查询系统 139

10.3制作平均成绩图表 142

第11章员工出勤管理 144

11.1员工年假标准表 144

11.2员工年假天数计算表 145

11.3员工加班费计算表 148

11.4员工缺勤扣款计算表 151

11.5员工值班工资计算表 156

第12章员工业绩管理 159

12.1企业员工业绩考核表 159

12.2趋势预测表 161

12.3员工业绩奖金统计表 163

第13章员工薪资管理 171

13.1员工工龄工资表 171

13.2员工调薪记录表 173

13.3员工工资统计表 176

13.4部门工资汇总表 179

13.5制作工资条 180

第14章员工档案管理 182

14.1利用身份证号码提取员工

性别信息 182

14.2利用身份证号码提取员工出生

日期信息 183

14.3利用身份证号码提取员工籍贯 185

14.4计算员工的年龄 186

14.5计算员工的工龄 187

14.6统计员工的性别 189

14.7员工年龄分段统计 190

14.8员工年龄排次查询 191

14.9年龄排名统计 193

14.10年龄条件查询 194

第15章证券投资分析 196

15.1制作K线图分析股票行情 196

15.2制作股票收益计算器 200

第16章企业长期投资分析 209

16.1净现值法投资分析 210

16.2内部报酬率法投资分析 214

16.3现值指数法投资分析 217

16.4回收期法投资分析 220

第17章筹资管理 224

17.1年金现值计算表 225

17.2等额还款计算表 226

17.3本金支付计算表 228

17.4利息支付计算表 229

17.5年金终值计算表 231

第18章生产管理 233

18.1产销预算分析表 233

18.2生产方案选择表 245

18.2.1明确约束条件 245

18.2.2制作生产方案选择表 246

18.3单位生产成本分析表 253

18.4成本收益利润汇总表 257

18.5企业贷款方案分析表 267

18.6企业原料采购 272

18.6.1企业原料采购申请表 272

18.6.2企业原料采购登记表 275

18.7企业采购预算分析表 279

18.8企业进销存货统计表 286

18.9单个产品库存记录表 290

第19章应收应付账款管理 294

19.1应收账款分析表 294

19.2应付账款分析表 300

第20章企业费用统计管理 305

20.1员工医疗费用统计表 305

20.2日常费用记录统计表 310

第21章企业销售管理 313

21.1产品销售额预测分析表 315

21.2产品销售方式决策表 318

21.3销售数据汇总表 327

21.4销售统计分析表 329

21.5销售收入、成本、费用、税金

年度分析表 334

21.6销售收入、成本汇总表 340

第22章市场分析管理 345

22.1客户资料统计表 345

22.2客户交易评估系统 347

第23章理财分析 351

23.1整存整取 352

23.2计算教育基金本利和 353

23.3计算贴现 353

23.4零存整取 354

23.5计算买卖房屋利润率 355

23.6房屋按揭计划 356

23.7计算最高贷款金额 362

23.8利率转换 363

23.9计算贷款成本 369

23.10计算统一费率下的贷款

实际成本 370

23.11计算按月平均还款型贷款的

实际成本 372

23.12计算全部实际支付利息 372

第24章经济预测 374

24.1公司利润预测表 375

24.2销售人员判断预测 378

24.3长期投资与收益关系表 381

第25章工程管理 385

25.1基因码转换 387

25.2商品条形码转换 388

25.3商品条形码逆转换 389

25.4度量衡转换 390

第26章决策管理 392

26.1最优生产决策 392

26.2收益最大化的投资组合 395

26.3最优配餐方案 398

26.4成本最小的运输计划 399

26.5最优选址方案 402

26.6员工任务指派 404

第27章日期文本函数应用 407

27.1计算目标日期是全年中的

第几天 408

27.2判断是否为闰年 409

27.3计算指定日期的星期值 410

27.4计算基金赎回入账日期 410

27.5提取连续的数字编号 411

27.6足彩连胜场数统计 412

27.7将数值转换为中文大写金额 413

第28章函数综合应用 418

28.1实时工资计算表 418

28.2备忘日推算表 421

28.3出货单 424 2100433B

MicrosoftExcel2007是目前市场上最强大的电子表格制作软件，它和Word、PowerPoint、Access等组件一起，组成了Office2007办公软件的完整体系，被广泛应用于财政、金融、统计、管理等领域，成为用户处理数据信息、进行数据统计分析工作的得力工具。

前言

根据Excel的应用范围不同，本书从自学与培训的角度出发，精心设计了以下四篇。

第一篇：第1章至第3章，其中，第1章主要介绍了中文版Excel2007的基础知识；第2章主要介绍了公式与函数的应用；第3章主要介绍了管理表格中数据的方法。

第二篇：第4章至第9章，其中，第4章主要介绍了使用Excel制作会计账务表单的方法；第5章主要介绍了使用Excel制作固定资产表单的方法；第6章主要介绍了使用Excel制作会计报表的方法；第7章主要介绍了使用Excel对固定资产进行分析的方法；第8章主要介绍了使用Excel对部门成本进行预测的方法；第9章主要介绍了使用Excel对部门成本进行分析的方法。

第三篇：第10章至第14章，其中第10章主要介绍了使用Excel对员工培训进行管理的方法；第11章主要介绍了使用Excel对员工出勤进行管理的方法；第12章主要介绍了使用Excel对员工业绩进行管理的方法；第13章主要介绍了使用Excel对员工薪资进行管理的方法；第14章主要介绍了使用Excel对员工档案进行管理的方法。

第四篇：第15章至第28章，其中第15章主要介绍了使用Excel对证券投资进行分析的方法；第16章主要介绍了使用Excel对企业长期投资进行分析的方法；第17章主要介绍了使用Excel对筹资行为进行管理的方法；第18章主要介绍了使用Excel对企业生产进行管理的方法；第19章主要介绍了使用Excel对应付应收账款进行管理的方法；第20章主要介绍了使用Excel对企业费用进行统计管理的方法；第21章主要介绍了使用Excel对企业产品销售进行管理的方法；第22章主要介绍了使用Excel对市场进行分析管理的方法；第23章主要介绍了使用Excel对投资理财行为进行分析管理的方法；第24章主要介绍了使用Excel对企业经济进行预测分析的方法；第25章主要介绍了使用Excel对工程进行管理的方法；第26章主要介绍了使用Excel对决策进行管理的方法；第27章主要介绍了日期文本函数的应用；第28章主要介绍了Excel函数的综合应用。

本书特别挑选Excel中最大部分且最实用的函数，剔除过于偏涩的应用，以清晰详细的功能注解，外加大量的实例，帮你理解这些公式的运用及技巧。通过本书的学习，读者可以掌握Excel的精华，制作出精美实用的电子表格，成为Excel的高手。

《Excel经典应用实例》全书分为四篇，第一篇是行政篇，通过7个典型的实例详细介绍了电子表格的基本制作和设置方法：第二篇是商务篇，通过5个典型实例介绍了商务表格的制作方法和技巧；第三篇是财务篇，通过6个典型实例介绍了Excel在财务领域中的应用；第四篇是个人篇，通过4个典型实例介绍了个人理财、家庭收支记录分析以及学习、工作计划制定等表格的制作方法。《Excel经典应用实例》从不同的行业角度全方位地对电子表格的制作进行了细致的讲解，有助于读者有效地学习使用Excel制作电子表格的技巧和方法，提高自己的电子表格设计能力。

《Excel经典应用实例》还附赠了约900个Excel模板，这些模板文件和书中的实例文件可以到化学工业出版社官方网站下载页面免费下载。

第1章 Excel公式基础 1

1-1 Excel中的公式应用 1

1-1-1 在工作表中输入公式 2

1-1-2 在工作表中编辑公式 3

1-1-3 在公式中使用运算符 3

1-1-4 在公式中使用Excel内置函数 6

1-1-5 计算Excel公式 7

1-1-6 把公式转换为数值 7

1-1-7 在单元格中显示公式 8

1-2 在公式中使用单元格和区域引用 9

1-2-1 绝对引用、相对引用与混合引用 9

1-2-2 改变引用类型 12

1-2-3 引用当前工作表外的单元格 12

1-2-4 引用多个工作表上的同一单元格 13

1-3 处理常见的公式错误 14

1-4 处理循环引用 15

1-5 公式运算时的改动 16

1-6 在公式中使用名称 16

1-6-1 对单元格和区域定义名称 17

1-6-2 在公式中使用区域名 18

1-6-3 显示区域名称列表 19

1-6-4 更改已有区城名称 20

1-6-5 使用常量名称 20

1-6-6 为公式定义名称 21

第2章 工作表函数概述 23

2-1 函数基础 23

2-1-1 函数的结构 23

2-1-2 函数参数类型 24

2-2 在公式中输入函数 25

2-3 函数的种类 27

2-3-1 数据库函数 27

2-3-2 日期与时间函数 27

2-3-3 工程函数 27

2-3-4 财务函数 27

2-3-5 信息函数 27

2-3-6 逻辑函数 28

2-3-7 查询和引用函数 28

2-3-8 数学和三角函数 28

2-3-9 统计函数 28

2-3-10 文本函数 28

2-3-11 用户自定义函数 28

2-4 函数中的逻辑判断 29

2-4-1 AND、OR、NOT函数 29

2-4-2 TRUE、FALSE函数 31

2-4-3 IF函数 31

第3章 使用函数和公式操作文本 37

3-1 确定单元格是否含有文本 39

3-1-1 使用ISTEXT函数检验单元格内容 39

3-1-2 使用TYPE函数决定数据类型 39

3-2 使用字符代码工作 40

3-2-1 用CODE函数转换字符为数字代码 40

3-2-2 用CHAR函数根据ASCII的值返回所代表的字符 40

3-3 确定两个字符串是否相同 42

3-3-1 用运算符号判断字符串是否相同 42

3-3-2 用EXACT函数进行字符串的比较 42

3-4 连接两个或多个单元格的内容 43

3-4-1 用运算符连接字符串 43

3-4-2 用CONCATENATE函数连接两个或多个字符串 43

3-5 文本大小写转换 44

3-6 文本与数值的互相转换 45

3-6-1 用TEXT函数将数值转换为格式化的文本 45

3-6-2 用VALUE函数将文本转换为数值 46

3-7 删除多余空格和非打印字符 50

3-7-1 用TRIM函数去除字符串的空白 50

3-7-2 用CLEAN函数去除字符串中不能打印的字符 50

3-8 取出字符串中的部分字符 51

3-8-1 用LEN函数统计字符串中字符数 51

3-8-2 用LEFT函数从字符串中第一个字符开始截取部分字符 52

3-8-3 用MID函数从字符串中的不固定位置开始截取部分字符串 52

3-8-4 用RIGHT函数从字符串的最后字符开始截取 53

3-9 字符串的查找与替换 53

3-9-1 用FIND函数在字符串中查找字符 54

3-9-2 用SEARCH函数在字符串中查找字符 54

3-9-3 用SUBSTITUTE函数在字符串中查找和替换 55

3-9-4 用REPLACE函数在字符串中查找与替换字符 59

3-10 其他文本函数 60

3-10-1 用REPT函数重复一个字符或字符串 60

3-10-2 用DOLLAR与RMB函数依照货币格式将数字转换成文本 62

第4章 处理日期和时间 63

4-1 Excel如何处理日期和时间 63

4-1-1 了解日期和时间的序列号 63

4-1-2 输入日期和时间 64

4-1-3 格式化日期和时间 64

4-1-4 在两个日期之间计算天数 65

4-1-5 日期的函数 65

4-2 显示与计算日期 66

4-2-1 用TODAY函数显示当前系统日期信息 66

4-2-2 用DATE函数显示指定数值的日期 67

4-2-3 用DATEVALUE函数将日期转换为序列值 68

4-2-4 用DAY函数得到日期的天数 69

4-2-5 用MONTH函数得到日期的月份 70

4-2-6 用YEAR函数得到日期的年份 71

4-2-7 用DATEDIF函数计算日期 72

4-2-8 用WEEKDAY函数确定星期几 74

4-3 分析工具库中的日期函数 83

4-3-1 用EOMONTH函数获得月份最后一天的日期 83

4-3-2 用NETWORKDAYS函数获得一段时期之间的工作日 84

4-3-3 用WORKDAY函数获得相隔指定工作日后的日期 85

4-4 显示与计算时间 86

4-4-1 用NOW函数显示时间 86

4-4-2 用TIME函数显示指定数值的时间 87

4-4-3 用TIMEVALUE函数将时间转换为序列值 88

第5章 计数与求和技术 95

5-1 与求和有关的函数应用 95

5-1-1 使用SUM函数进行基本数值求和 95

5-1-2 使用SUMIF函数进行高级条件求和 98

5-1-3 用SUBTOTAL函数进行分类汇总 101

5-2 与计数有关的函数应用 103

5-2-1 用COUNT函数进行基本计数 103

5-2-2 用COUNTA函数进行忽略空值的计数 103

5-2-3 用COUNTIF函数进行指定条件的计数 104

第6章 查询与引用函数 113

6-1 引用的基础知识 113

6-2 引用函数的使用 114

6-2-1 用COLUMNS函数得到列数 114

6-2-2 用ROWS函数得到行数 114

6-2-3 用MATCH函数得到给定值在列表中的位置 114

6-2-4 用INDIRECT函数得到字符串指定的引用 115

6-2-5 用OFFSET函数根据偏移量得到新的引用 117

6-3 获得单元格的地址 119

6-3-1 用COLUMN 函数获得单元格列标号 119

6-3-2 用ROW函数获得单元格行标号 119

6-3-3 用ADDRESS函数确定值的地址 120

6-4 理解查找公式 122

6-5 创建查找公式 123

6-5-1 用VLOOKUP函数在列中查找值 123

6-5-2 用HLOOKUP函数在行中查找值 127

6-5-3 用LOOKUP函数在行或列中查找值 130

6-5-4 用INDEX从单元格引用中选取某个值 134

6-5-5 用CHOOSE函数从数字的列表中查找一个值 138

第7章 数学函数与统计函数 141

7-1 常用数学函数 141

7-1-1 用RAND函数生成随机数 141

7-1-2 用INT函数把数字转换为整数 142

7-1-3 常用的四舍五入函数 143

7-1-4 用ABS函数获取绝对值 147

7-1-5 用MOD函数返回两数相除后的余数 147

7-2 用于求平均值的统计函数 148

7-2-1 用AVERAGE函数求算术平均值 148

7-2-2 用TRIMMEAN函数求数据集的内部平均值 148

7-3 查找一组数据中满足不同要求的数值 149

7-3-1 求一组数据的最大值MAX与最小值MIN 149

7-3-2 求一组数据第K个最大值LARGE与第k个最小值SMALL 150

7-3-3 用MEDIAN求一组数据的中位数 154

7-3-4 用MODE求一组数据出现频率最多的数 154

7-4 建立频率分布 155

7-4-1 用FREQUENCY函数建立频率分布 155

7-4-2 使用公式建立频率分布 157

7-5 用来排位的函数 158

7-5-1 用RANK函数获得数值在一组数据中的排位 158

7-5-2 用PERCENTRANK函数获得数值在一组数据的百分比排位 158

7-6 其他计算方法 159

7-6-1 用CONVERT函数进行度量衡转换 159

7-6-2 在不同的数字系统间进行数值转换 160

第8章 财务函数 163

8-1 货币时间价值的计算 164

8-1-1 求某项投资的未来值FV 164

8-1-2 贷款分期偿还额PMT 165

8-1-3 求某项投资的现值PV 169

8-1-4 投资期数的计算NPER 170

8-2 本金和利息的计算 171

8-2-1 PPMT与IPMT 171

8-2-2 CUMIPMT与CUMPRINC 172

8-3 固定资产折旧计算 173

8-3-1 直线法折旧函数SLN 174

8-3-2 固定余额递减法函数DB 175

8-3-3 双倍余额递减法函数DDB 177

8-3-4 计算指定的期间内的折旧值VDB 177

8-3-5 年数总和法函数SYD 178

8-4 投资决策中有关指标的计算 179

8-4-1 求投资的净现值NPV 179

8-4-2 返回内部收益率的函数IRR 180

8-4-3 计算某项投资的实际赢利率RATE 181

8-5 债券及其他金融函数 182

8-5-1 求定期付息有价证券的应计利息的函数ACCRINT 183

8-5-2 计算一次性付息有价证券的应计利息函数ACCRINTM 184

8-5-3 求有价证券的贴现率DISC 185

第9章 信息函数 187

9-1 用CELL函数获得单元格格式、位置或内容等信息 187

9-2 用TYPE函数返回数值的类型 190

9-3 用INFO函数获得当前操作环境的信息 191

9-4 用COUNTBLANK计算区域内空白单元格的个数 191

9-5 用ERROR.TYPE函数返回对应于错误类型的数字 192

9-6 IS类函数检验数值或引用类型 193

第10章 数据库函数 197

10-1 数据库函数基础 197

10-1-1 函数的共同特点 197

10-1-2 数据库函数列表 197

10-1-3 数据库函数的参数含义 198

10-1-4 条件定义 198

10-2 数据库函数应用 200

10-2-1 查找符合条件的值DGET 200

10-2-2 计算满足条件的数值和DSUM 201

10-2-3 计算满足条件的数值平均值DAVERAGE 203

10-2-4 查找满足条件的最大值DMAX 204

10-2-5 查找满足条件的最小值DMIN 205

10-2-6 高级筛选技术 205

第11章 数组与数组公式 211

11-1 了解数组公式 211

11-1-1 多单元格数组公式 211

11-1-2 单元格数组公式 212

11-1-3 数组常量 213

11-2 了解数组的维数 214

11-2-1 创建—维水平数组 214

11-2-2 刨建一维纵向数组 214

11-2-3 创建二维数组 214

11-3 命名数组常量 215

11-4 使用数组公式 216

11-4-1 输入数组公式 216

11-4-2 选择数组公式区域 216

11-4-3 编辑数组公式 216

11-4-4 用TRANSPOSE函数将工作表中的水平数据表变为垂直数据 217

11-5 创建多单元格数组公式 219

11-6 创建单一单元格数组公式 223

11-6-1 使用数组公式求和与计数 224

11-6-2 在查找与引用中使用数组公式 232

11-7 用Row()函数在数组公式中生成连续整数 239

11-8 数组公式中的逻辑运算 244

第12章 创建自定义函数 253

12-1 VBA基础知识 253

12-1-1 VBE编辑器 253

12-1-2 代码窗口的操作 255

12-1-3 变量及变量的数据类型 256

12-1-4 声明变量 257

12-1-5 常量 258

12-1-6 VBA的流程控制与循环 259

12-2 一个简单的VBA自定义函数范例 260

12-2-1 创建一个简单的自定义函数 260

12-2-2 在工作表中使用函数 261

12-2-3 分析自定义函数 261

12-3 创建函数程序 262

12-3-1 声明函数 262

12-4 执行函数程序 263

12-4-1 从其他函数中调用自定义函数 263

12-4-2 在工作表公式中使用自定义函数 263

12-5 学习函数程序的参数 264

12-5-1 不使用参数的函数 264

12-5-2 有—个参数的函数 264

12-5-3 使用两个参数的函数 266

12-5-4 使用区域参数的函数 267

12-6 调试自定义函数 268

12-7 插入自定义函数 269

附录 Excel的内部函数 271

1 数学和三角函数 271

2 文本函数 275

3 日期与时间函数 279

4 信息函数 281

5 逻辑函数 283

6 查找和引用函数 283

7 统计函数 286

8 财务函数 295

9 数据库函数 304

10 工程函数 3052100433B