目  录

本书资源库 XI

第1章  公式、函数、图表在员工档案

管理中的应用 1

（ 视频讲解：1小时26分钟）

1.1  基础知识 2

1.1.1  新建工作簿 2

1.1.2  重命名工作表 3

1.1.3  精确调整行高和列宽 3

1.1.4  输入当前系统日期 4

1.1.5  快速显示当前系统时间 5

1.1.6  计算员工年龄 5

1.1.7  更改数据系列颜色 6

1.1.8  更改图例项显示位置 7

1.2  员工档案管理表 8

1.2.1  创建员工档案管理表 8

1.2.2  利用数据有效性防止工号

重复输入 11

1.2.3  身份证号中提取有效信息 13

1.2.4  计算员工年龄和工龄 15

1.3  员工档案查询表 17

1.3.1  创建员工档案查询表 17

1.3.2  使用VLOOKUP函数查询

人员信息 18

1.4  使用函数制作到期提醒 21

1.4.1  生日到期提醒 21

1.4.2  试用期到期提醒 25

1.5  员工学历层次分析 27

1.5.1  编制员工学历数据透视表 27

1.5.2  制作员工学历透视图 30

1.6  员工年龄段分析 32

1.6.1  编制员工年龄段数据透视表 32

1.6.2  制作员工学历透视图 35

第2章  公式、函数、图表在考勤

管理中的应用 38

（ 视频讲解：1小时27分钟）

2.1  基础知识 39

2.1.1  插入新工作表 39

2.1.2  定义名称 39

2.1.3  使用定义的名称制作下拉

菜单 40

2.1.4  仅填充单元格的格式 41

2.1.5  让单元格自动换行 42

2.1.6  按工作日填充数据 42

2.1.7  自动筛选出符合条件的

数据 43

2.1.8  使用函数计算月出勤天数 44

2.1.9  判断员工是否已签到 45

2.2  员工考勤登记表 46

2.2.1  绘制员工基本考勤登记表 46

2.2.2  设置考勤日期和星期格式 51

2.2.3  冻结窗格显示固定数据 54

2.2.4  添加考勤符号 55

2.3  员工考勤统计表 56

2.3.1  创建员工考勤统计表 56

2.3.2  用特殊颜色标记迟到

早退人员 60

2.4  员工出勤情况分析 62

2.4.1  员工出勤率统计 63

2.4.2  筛选出勤情况最差的员工 66

2.4.3  使用数据透视表统计各部门

出勤情况 68

2.5  员工考勤扣款统计 71

2.5.1  员工出勤率统计 71

2.5.2  筛选出勤扣款最多的5位

员工 74

第3章  公式、函数、图表在薪资

管理中的应用 76

（ 视频讲解：1小时21分钟）

3.1  基础知识 77

3.1.1  设置输入数据的格式 77

3.1.2  为工作簿添加密码保护 78

3.1.3  一次性增加基本工资 79

3.1.4  在工作簿中移动工作表 80

3.1.5  计算员工工龄 81

3.1.6  计算员工工龄工资 82

3.1.7  统计各部门工资总额 83

3.1.8  计算员工业绩奖金 84

3.2  员工销售业绩奖金 85

3.2.1  创建销售记录表 85

3.2.2  计算本月基本业绩奖金 87

3.3  员工基本工资表 89

3.3.1  创建基本表格 89

3.3.2  计算员工工龄及工龄工资 91

3.4  员工考勤扣款及满勤奖 92

3.4.1  复制员工考勤扣款统计

工作表 92

3.4.2  计算员工满勤奖 93

3.5  员工福利表 94

3.5.1  创建员工福利表 94

3.5.2  计算员工福利补贴 95

3.6  员工社保缴费表 97

3.6.1  创建员工社保缴费表 97

3.6.2  计算各项社保缴费情况 98

3.7  工资统计表 100

3.7.1  创建员工月度工资表 100

3.7.2  计算工资表中应发金额 102

3.7.3  计算工资表中应扣金额 103

3.8  员工工资单 106

3.8.1  建立员工的工资单 106

3.8.2  快速生成每位员工的

工资单 110

第4章  公式、函数、图表在销售

管理中的应用 111

（ 视频讲解：1小时10分钟）

4.1  基础知识 112

4.1.1  标记出高于平均值的记录 112

4.1.2  更改图表类型 113

4.1.3  更改数据系列颜色 114

4.1.4  将数据透视表转换为

普通表格 115

4.1.5  设置图表标题与表格标题的

链接 116

4.1.6  计算总销售额 117

4.1.7  计算销售金额排名 118

4.1.8  计算指定员工销售

金额总和 119

4.2  销售记录表 120

4.2.1  创建销售记录表 120

4.2.2  标记出排名前三的销售

金额 121

4.2.3  筛选出指定的销售记录 124

4.3  按产品系列统计销售收入 127

4.3.1  创建基本表格 127

4.3.2  创建饼图显示各产品

销售情况 129

4.4  销售收入变动趋势分析 133

4.4.1  创建基本表格 133

4.4.2  创建图表显示销售变动

趋势 135

4.5  销售员业绩分析 138

4.5.1  创建数据透视表 138

4.5.2  创建数据透视图 141

第5章  公式、函数、图表在销售成本

分析中的应用 147

（ 视频讲解：1小时19分钟）

5.1  基础知识 148

5.1.1  创建混合型图表 148

5.1.2  启用次坐标轴 149

5.1.3  隐藏数据系列 150

5.1.4  绘制文本框 151

5.1.5  快速复制图表格式 153

5.1.6  预测下次订单的销售成本 154

5.1.7  计算产品销售总额 155

5.1.8  按产品条件统计销售数量 156

5.2  单一产品保本点预测 157

5.2.1  本、量、利分析法 157

5.2.2  贡献毛利分析法 159

5.3  多产品保本点预测 160

5.3.1  利用加权平均法进行预测 160

5.3.2  预测多种产品的保本

销售额 164

5.3.3  利用贡献毛利分解计算

利润额 165

5.4  产品成本降低完成情况

分析 166

5.4.1  创建基本表格 166

5.4.2  创建柱形图分析产品成本 168

5.5  产品单位成本升降分析 172

5.5.1  创建基本表格 172

5.5.2  创建图表分析成本 174

第6章  公式、函数、图表在销售利润

管理中的应用 179

（ 视频讲解：1小时14分钟）

6.1  基础知识 180

6.1.1  添加“开发工具”选项卡 180

6.1.2  让控件随着单元格移动 181

6.1.3  重新设置水平轴数据 182

6.1.4  更改水平轴数据位置 184

6.1.5  更改网格线颜色 185

6.1.6  统计出指定店面的平均

利润 186

6.1.7  统计最高（最低）

销售量 187

6.1.8  计算销售产品的利润

金额 188

6.2  单位售价对利润的影响 189

6.2.1  创建基本计算表 189

6.2.2  建立本量利分析图 192

6.3  单位可变成本对利润的

影响 197

6.3.1  创建基本计算表 197

6.3.2  建立盈亏平衡图表 201

6.4  不同单位售价保本点分析 209

6.4.1  求解不同售价下的保本点 209

6.4.2  建立图表直观显示盈亏平衡

销量与单位售价 211

第7章  公式、函数、图表在筹资

管理中的应用 217

（ 视频讲解：1小时4分钟）

7.1  基础知识 218

7.1.1  计算贷款按年、按月的

偿还金额 218

7.1.2  计算贷款前4个月应付的

本金额 219

7.1.3  计算贷款前4个月应付的

利息额 221

7.1.4  计算贷款在第3年支付的

利息金额 222

7.1.5  计算出某项贷款的

清还年数 223

7.2  长期借款筹资单变量模拟

运算 224

7.2.1  计算每月偿还数 224

7.2.2  使用模拟运算工具分析 226

7.3  租赁筹资模拟运算表 227

7.3.1  建立租赁筹资分析模型 227

7.3.2  使用模拟运算表分析 229

7.4  长期借款筹资决策分析 232

7.4.1  创建基本信息 232

7.4.2  创建筹资决策分析表格 232

7.5  贷款偿还进度分析 236

7.5.1  贷款偿还进度计算 236

7.5.2  创建图表显示还款进度

情况 239

第8章  公式、函数、图表在投资方式

分析中的应用 244

（ 视频讲解：1小时7分钟）

8.1  基础知识 245

8.1.1  计算投资未来收益 245

8.1.2  计算某项投资在可变利率下的

未来值 246

8.1.3  计算某项投资所需要的

金额 246

8.1.4  分析企业项目投资的

利润值 247

8.1.5  计算投资项目回报金额 248

8.1.6  计算投资的内部收益率 249

8.1.7  计算5年投资的修正内部

收益率 251

8.1.8  计算不定期发生现金流的

内部收益率 253

8.2  投资计划表 254

8.2.1  创建投资计划表 254

8.2.2  投资本金与回报分析 258

8.3  投资可行性分析 262

8.3.1  创建分析表格 262

8.3.2  打印表格 264

8.4  净现值法投资模型 266

8.4.1  创建分析模型 266

8.4.2  以只读方式打开工作表 270

8.5  内部收益率法评价投资

方案 272

8.5.1  创建投资方案表格 272

8.5.2  使用IRR函数分析 275

第9章  公式、函数、图表在财务

预测中的应用 277

（ 视频讲解：1小时）

9.1  基础知识 278

9.1.1  在图表中显示数据源 278

9.1.2  将图表保存为图片 278

9.1.3  保护图表不被随意更改 279

9.1.4  预计9月份生产成本 280

9.1.5  使用GROWTH函数预测

销量 281

9.1.6  使用TREND函数预测

销量 282

9.1.7  预算产品的寿命测试值 283

9.1.8  使用MATCH函数返回指定

元素所在位置 284

9.2  日常费用线性预测 285

9.2.1  创建日常费用预测模型 285

9.2.2  预测下半年费用 288

9.3  生产成本预测分析 290

9.3.1  使用函数预测成本 290

9.3.2  创建散点图 291

9.4  指数平滑法预测产品销量 295

9.4.1  产品销量预测 295

9.4.2  产品销售预测分析 297

9.5  资金需要预测分析 301

9.5.1  资金预测分析 301

9.5.2  保护工作表 304

第10章  公式、函数、图表在固定资产

管理中的应用 306

（ 视频讲解：1小时9分钟）

10.1  基础知识 307

10.1.1  计算固定资产的每年折旧额

（DB函数） 307

10.1.2  计算固定资产的每年折旧额

（SYD函数） 308

10.1.3  计算固定资产的每年折旧额

（DDB函数） 309

10.1.4  计算出固定资产部分期间的

设备折旧值 310

10.1.5  采用余额递减法计算累计折

旧额 312

10.1.6  计算每个会计期间的

折旧值 313

10.1.7  以法国会计系统计算每个会

计期间的折旧值 314

10.1.8  计算固定资产每天

折旧额 315

10.2  固定资产清单 316

10.2.1  创建固定资产清单 316

10.2.2  使用公式计算固定资产

状态 319

10.2.3  查询部门固定资产清单 320

10.3  固定资产查询表 322

10.3.1  返回固定资产各项信息 322

10.3.2  计算固定资产折旧情况 327

10.4  企业固定资产折旧计算 329

10.4.1  余额法折旧计算 330

10.4.2  双倍余额递减法折旧

计算 331

10.4.3  年限总和法折旧计算 333

第11章  公式、函数、图表在经济

预测中的应用 335

（ 视频讲解：1小时12分钟）

11.1  基础知识 336

11.1.1  计算中位数 336

11.1.2  预测10月份产品销量 336

11.1.3  计算下半年销售额的

偏斜度 337

11.1.4  查看下半年销量的分布

情况 338

11.1.5  查看测试检测结果是否

相同 339

11.1.6  计算月平均销售量 340

11.1.7  计算设计模型的误差值 341

11.2  定性预测 341

11.2.1  德尔菲法 342

11.2.2  主观概率法 345

11.3  回归分析预测 348

11.3.1  一元线性回归预测 348

11.3.2  多元回归分析 352

11.3.3  非线性回归预测 354

11.4  季节变动预测 356

11.4.1  平均数趋势法预测 356

11.4.2  环比法预测 361

第12章  公式、函数、图表在动态

图表中的应用 366

（ 视频讲解：1小时17分钟）

12.1  基础知识 367

12.1.1  解决鼠标指向时不显示文字

问题 367

12.1.2  让图表绘制出被隐藏的

数据源 368

12.1.3  以0值替代数据源中的

空值 369

12.1.4  将创建的图表保存为模板 370

12.1.5  使用模板新建图表 371

12.1.6  查看员工业务水平 373

12.1.7  查询员工销售情况 373

12.1.8  求和累积销售数量 375

12.2  列表框式动态图表 376

12.2.1  创建销售数量汇总表 377

12.2.2  创建动态图表 380

12.3  复选框式动态图表 384

12.3.1  定义名称 384

12.3.2  创建圆环图 386

12.4  组合框式动态图表 395

12.4.1  创建动态图表数据源 395

12.4.2  创建动态图表 398

第13章  函数基础知识 405

（ 视频讲解：1小时3分钟）

13.1  函数的概述 406

13.1.1  什么是函数 406

13.1.2  什么是函数参数 406

13.1.3  函数的构成 407

13.1.4  函数类型 407

13.1.5  使用函数应注意的事项 408

13.2  公式的输入与编辑 408

13.2.1  公式的运算符 408

13.2.2  输入公式 409

13.2.3  编辑公式 411

13.3  引用数据源 412

13.3.1  引用相对数据源 412

13.3.2  引用绝对数据源 413

13.3.3  引用混合数据源 413

13.3.4  引用三维数据源来进行

计算 414

13.3.5  在公式中引用多张工作表中

特定数据源来进行计算 415

13.4  名称定义与使用 415

13.4.1  名称定义 416

13.4.2  使用定义的名称计算 416

13.4.3  名称的管理 417

13.5  公式审核工具的应用 418

13.5.1  追踪引用单元格 418

13.5.2  追踪从属单元格 419

13.5.3  移去箭头 420

13.5.4  错误检查 420

13.5.5  显示计算步骤 423

13.6  公式错误值与解决方法 427

13.6.1  “#####”错误值的处理 427

13.6.2  “#DIV>0!”错误值的

处理 429

13.6.3  “#N/A”错误值的处理

方法 430

13.6.4  “#NAME?”错误值 430

13.6.5  “#NUM!”错误值 434

13.6.6  “#VALUE!”错误值 435

13.6.7  “#REF!”错误值 436

13.6.8  “#NULL!”错误值 436

X

IX