Excel函数、公式与图表PDF电子书下载

第1篇 基础知识 2

第1章 电子表格的基本操作 2

1.1工作簿的操作 2

1.1.1新建工作簿 2

1.1.2保存工作簿 5

1.1.3设置工作簿配色方案 7

1.2工作表的操作 9

1.2.1插入与删除工作表 9

1.2.2重命名工作表 11

1.2.3设置工作表标签颜色 11

1.2.4移动与复制工作表 12

1.2.5隐藏和显示工作表 14

1.2.6调整工作表的显示比例 15

1.3单元格的操作 16

1.3.1插入、删除单元格 16

1.3.2合并与拆分单元格 18

1.3.3插入行或列 18

1.3.4删除行或列 20

1.3.5调整行高和列宽 21

1.3.6隐藏含有重要数据的行或列 22

第2章 数据的输入与编辑 26

2.1输入各种不同类型的数据 26

2.1.1输入文本内容 26

2.1.2输入数值 27

2.1.3输入日期与时间 29

2.1.4输入特殊符号 30

2.2以填充的方式批量输入数据 32

2.2.1快速填充相同的数据 32

2.2.2快速填充有规律的数据 33

2.2.3“自动填充选项”功能的使用 36

2.2.4自定义填充序列 37

2.3编辑数据 39

2.3.1移动数据 39

2.3.2修改数据 40

2.3.3复制和粘贴数据 40

2.3.4查找与替换数据 41

2.3.5删除数据 44

2.3.6数据条件格式的设置 44

2.3.7数据有效性 47

第3章 公式运算基础操作 52

3.1使用公式进行数据计算 52

3.1.1公式的运算符 52

3.1.2输入公式 52

3.1.3编辑公式 54

3.2公式计算中单元格的引用 54

3.2.1引用相对数据源 55

3.2.2引用绝对数据源 55

3.2.3引用当前工作表之外的单元格 57

3.3公式计算中名称的使用 58

3.3.1将单元格区域定义为名称 58

3.3.2使用定义的名称进行数据计算 60

3.3.3将公式定义为名称 60

3.3.4名称管理 61

3.4公式审核工具的应用 62

3.4.1显示公式 62

3.4.2追踪引用单元格 63

3.4.3使用“错误检查”功能辅助找寻公式错误原因 64

3.4.4使用“公式求值”功能逐步分解公式 65

第4章 Excel中各函数功能解析 68

4.1函数基础 68

4.1.1函数的构成 68

4.1.2什么是函数参数 69

4.1.3函数类型 69

4.1.4使用工具栏按钮输入函数 71

4.1.5如何选择合适的函数来解决问题 71

4.1.6使用“插入函数”对话框输入函数 71

4.1.7使用自动完成功能输入函数 72

4.1.8为什么表格列标题显示的是数字而不是字母 73

4.1.9如何不让函数提示信息遮掩到工作表的列号 74

4.2函数错误值的解决方法 75

4.2.1 “#####”错误值的处理 75

4.2.2 “#DIV＞0！”错误值的处理 76

4.2.3 “#N/A”错误值 76

4.2.4 “#NAME？”错误值 77

4.2.5 “#NUM！”错误值 79

4.2.6 “#VALUE！”错误值 80

4.2.7 “#REF！”错误值 81

4.2.8 “#NULL！”错误值 81

第5章 使用图表直观表现数据 84

5.1了解图表的组成 84

5.2将销售数据表现为图表格式 85

5.3修改图表 86

5.3.1图表大小和位置的调整 86

5.3.2图表的复制和删除 89

5.3.3图表类型的更改 91

5.3.4将多张图表组合成一个对象 92

5.3.5图表形状样式的设置 93

5.3.6将建立的图表转化为静态图片 94

5.4设置图表 95

5.4.1设置图表标题 95

5.4.2设置图例 96

5.4.3设置图表区格式 97

5.4.4添加数据标签 99

5.4.5设置数据系列格式 100

5.4.6设置坐标轴 101

5.4.7设置网格线 103

5.4.8添加涨/跌柱线 106

5.5图表布局与样式套用 107

5.5.1图表布局的设置 107

5.5.2图表样式的设置 108

第6章 使用数据透视表分析数据 110

6.1数据透视表概述 110

6.1.1什么是数据透视表 110

6.1.2数据透视表的作用 110

6.1.3何时应该使用数据透视表 110

6.2了解数据透视表的结构 111

6.2.1数据透视表的结构 111

6.2.2数据透视表中的专业术语 111

6.3创建数据透视表分析财务数据 113

6.3.1新建数据透视表 113

6.3.2通过设置字段得到统计结果 114

6.4调整数据透视表获取不同分析结果 115

6.4.1更改数据透视表字段布局 115

6.4.2调整字段显示顺序 117

6.4.3修改汇总计算 117

6.4.4数据透视表的更新 118

6.5显示明细数据 119

6.6数据透视表布局的更改及样式套用 120

6.6.1设置数据透视表分类汇总布局 120

6.6.2更改数据透视表默认布局 121

6.6.3通过套用样式快速美化数据透视表 121

6.7数据透视图的建立 122

6.7.1建立数据透视图 123

6.7.2通过“图表布局”功能快速设置图表布局 123

6.7.3通过“图表样式”功能快速设置图表样式 124

6.7.4更改数据透视图的图表类型 125

6.7.5在新工作表中显示数据透视图 126

第2篇 行业案例 129

第7章 函数、公式、图表在员工档案管理中的应用 129

7.1设计员工档案管理表 129

7.1.1建立员工档案表框架 129

7.1.2表头文字和列标识输入与文字格式设置 130

7.1.3表格区域单元格格式及数据有效性设置 132

7.1.4设置公式自动返回相关信息 135

7.2建立工作表用于查询员工档案 138

7.2.1建立员工档案查询表框架 139

7.2.2设置单元格的公式 140

7.3分析员工的学历层次 142

7.3.1建立数据透视表统计各学历人数 142

7.3.2建立图表直观显示各学历人数分布 144

7.4分析员工年龄层次 145

7.4.1建立数据透视表统计各学历人数 145

7.4.2建立图表直观显示年龄层次 147

第8章 函数、公式、图表在考勤管理中的应用 150

8.1建立考勤表并实现考勤数据统计 150

8.1.1创建员工考勤记录表 151

8.1.2填制考勤表 158

8.1.3统计各员工本月请假天数、迟到次数及应扣款 159

8.1.4分析各部门请假情况 164

8.2建立加班工资统计表 167

8.2.1使用建立的考勤记录表进行加班记录 167

8.2.2加班工资统计 169

第9章 函数、公式、图表在销售数据管理中的应用 173

9.1建立产品销售数据统计报表 173

9.1.1建立产品基本信息备案表 173

9.1.2建立销售数据统计汇总表 174

9.2“分类汇总”统计 177

9.2.1统计各品种产品的交易数量、交易金额 177

9.2.2统计各客户本期购买金额 178

9.3利用函数、图表分析销售数据 179

9.3.1使用SUMIF函数分析各品种产品销售情况 179

9.3.2分析各销售人员业绩 183

9.3.3使用SUMPRODUCT函数精确分析 186

9.4利用数据透视表分析销售数据 190

9.4.1分析客户购买情况 190

9.4.2分析应收账款 193

第10章 函数、公式、图表在产品库存管理中的应用 199

10.1建立产品库存数据统计报表 199

10.1.1建立产品基本信息备案表 199

10.1.2建立出入库数据统计表 200

10.2统计各品种产品的出入库数据 203

10.2.1计算各品种产品的出入库数据 204

10.2.2建立图表比较各品种入库数量与入库金额 205

10.3利用函数实现按日查询出入库数据 209

10.3.1建立出入库数据日汇总、查询表 209

10.3.2设置公式实现自动统计指定日期出入库数据 212

10.4利用函数实现各系列产品出入库明细查询 215

10.4.1建立指定品种出入库记录查询表 216

10.4.2建立公式达到自动查询的目的 217

10.5出入库累计汇总 220

10.5.1建立库存汇总统计表 220

10.5.2汇总出入库数量、金额 222

第11章 函数、公式、图表在企业日常费用支出与预算中的应用 229

11.1建立日常费用支出统计表 229

11.1.1规划日常费用支出统计表框架 229

11.1.2费用支出表编辑区域数据有效性及单元格格式 230

11.2分类汇总不同产生部门与不同类别的支出费用 233

11.2.1统计不同部门产生的支出金额总计 234

11.2.2统计不同类别费用的支出金额总计 235

11.3利用数据透视表（图）统计费用支出额 236

11.3.1统计各类别费用支出金额 236

11.3.2分析各部门费用支出明细 239

11.3.3比较各支出部门一月、二月费用支出额 240

11.4日常支出费用预算分析 242

11.4.1建立全年费用预算记录表 242

11.4.2一月份实际费用与预算费用比较分析 243

11.4.3建立其他月份实际费用与预算费用比较分析表 248

第12章 函数、公式、图表在企业往来账款管理与分析中的应用 253

12.1建立应收账款清单表 253

12.1.1规划应收账款清单表的框架 254

12.1.2设置公式计算未收金额、判断是否到期及计算未到期金额 256

12.2计算各条应收账款的账龄 257

12.3分客户统计应收账款 259

12.3.1建立数据透视表统计各客户应收账款 259

12.3.2建立数据透视图直观显示各客户应收账款 261

12.4分客户分账龄分析应收账款 262

12.4.1建立“分客户分账龄分析应收账款”表 262

12.4.2使用图表直观分析各客户账龄 264

12.5建立应付账款清单表 265

12.5.1规划应付账款清单表的框架 266

12.5.2设置公式分析各项应付账款 267

12.5.3查看应付账款逾期天数超过30天的记录 269

12.6各往来客户总应付账款统计 270

12.6.1建立“各往来单位应付账款统计”表 270

12.6.2分析各往来单位应付账款占比 272

第13章 设计动态图表——动态显示企业各分部支出费用情况 277

13.1图表效果展示与分析 277

13.1.1图表最终效果 277

13.1.2分析图表与数据的关系 278

13.2建立动态图表数据源 278

13.2.1 OFFSET函数的应用 278

13.2.2使用OFFSET函数建立数据源 279

13.3创建折线图 281

13.4添加下拉菜单控制图表显示 282

13.4.1添加控件工具栏 282

13.4.2在工作表中添加“数值调节按钮”控件 283

13.4.3在图表中添加控件 284

13.5图表优化设置 286

第14章 设计动态图表——动态显示商品房的销售情况 291

14.1图表效果展示与分析 291

14.1.1图表最终效果 291

14.1.2分析图表与数据的关系 291

14.2使用OFFSET为数据系列定义名称 292

14.2.1建立辅助序列并设置图表名称引用单元格的公式 292

14.2.2将图表数据系列定义为名称 293

14.3使用定义的名称新建饼形图表 295

14.4添加下拉菜单式控件控制“销售金额”、“首付金额”等类型显示 296

14.5添加下拉菜单式控件控制“一居”、“二居”等户型显示 298

14.6图表优化设置 299

14.6.1添加文本框到图表 299

14.6.2添加百分比标签并显示两位小数 300

第3篇 提高技能 304

第15章 文本、逻辑函数范例应用技巧 304

15.1文本函数范例应用技巧 304

技巧1 自动生成凭证号 304

技巧2 自动比较两个部门的采购价格是否一致 304

技巧3 提取产品的类别编码 305

技巧4 从E-mail地址中提取账号 306

技巧5 使用LEN函数验证身份证号码的位数 306

技巧6 使用MID函数提取产品的类别编码 307

技巧7 从身份证号码中提取出生年份 308

技巧8 从身份证号码中提取完整的出生日期 308

技巧9 从身份证号码中判别性别 309

技巧10 将手机号码的后4位替换为特定符号 310

技巧11 使用“★”为考评结果标明等级 311

技巧12 分离8位电话号码的区号与号码 312

技巧13 分离7位、8位混合显示的电话号码的区号与号码 313

技巧14 从编码中提取合同号 314

15.2逻辑函数范例应用技巧 314

技巧1 IF函数配合LEFT函数根据代码返回部门名称 314

技巧2 使用IF函数建立单元格条件格式 315

技巧3 使用AND函数配合IF函数进行成绩考评 316

技巧4 使用OR函数对员工的考核成绩进行综合评定 316

技巧5 OR函数配合AND函数对考核成绩进行综合评定 317

技巧6 使用IF与OR函数解决计算结果为“0”、错误值的问题 317

技巧7 使用IF函数计算个人所得税 318

技巧8 使用逻辑函数判断未来年份是闰年还是平年 319

第16章 日期函数范例应用技巧 322

技巧1 TODAY函数在账龄分析中的应用 322

技巧2 将非日期数据转换为标准的日期 323

技巧3 判断一个月的最大天数 324

技巧4 使用DAYS360函数计算总借款天数 324

技巧5 使用DAYS360函数计算还款剩余天数 324

技巧6 使用DAYS360函数计算固定资产的已使用月份 325

技巧7 自动填写报表中的月份 326

技巧8 根据员工的出生日期快速计算其年龄 326

技巧9 使用DATEDIF函数计算总借款天数 327

技巧10 使用DATEDIF函数计算精确账龄 327

技巧11 使用DATEDIF函数自动追加工龄工资 328

技巧12 快速返回值班安排表中日期对应的星期数 328

技巧13 快速返回日期对应的星期数（中文星期数） 329

技巧14 使用YEAR与TODAY函数计算出员工工龄 330

技巧15 使用YEAR与TODAY函数计算出员工年龄 330

技巧16 计算两个日期之间的实际工作日 331

技巧17 计算指定日期到月底的天数 332

技巧18 使用EOMONTH函数查看指定年份各月天数 333

技巧19 根据当前月份自动计算出本月日期 334

技巧20 返回指定月份下指定日期对应的星期数 334

第17章 数学、统计函数范例应用技巧 336

17.1数学函数范例应用技巧 336

技巧1 计算总销售额（得知每种产品的销售量与销售单价） 336

技巧2 使用SUM函数统计不同时间段不同类别产品的销售笔数 336

技巧3 使用SUM函数统计不同时间段不同类别产品的销售金额 337

技巧4 按业务发生时间进行汇总 339

技巧5 统计各部门工资总额 339

技巧6 统计某个时段之前或之后的销售总金额 340

技巧7 使用SUMIF函数统计两种类别或多种类别产品总销售金额 341

技巧8 使用SUMIFS函数实现多条件统计 341

技巧9 使用SUMIFS函数统计某一日期区域的销售金额 342

技巧10 使用CEILING函数根据通话总秒数以7秒为计价单位来计算总话费 342

技巧11 使用INT函数对平均销售量取整 343

技巧12 使用SUMPRODUCT函数计算总销售额 343

技巧13 从销售统计表中统计指定类别产品的总销售额 344

技巧14 使用SUMPRODUCT函数同时统计出某两种型号产品的销售件数 344

技巧15 统计出指定部门、指定职务的员工人数 345

技巧16 统计出指定部门获取奖金的人数（去除空值） 346

技巧17 统计出指定部门奖金大于固定值的人数 346

技巧18 从学生档案表中统计出某一出生日期区间中指定性别的人数 347

技巧19 统计指定店面指定类别产品的销售金额合计值 347

17.2统计函数范例应用技巧 348

技巧1 求平均值时忽略计算区域中的0值 348

技巧2 按指定条件求平均值 349

技巧3 同时满足多个条件求平均值 349

技巧4 隔列计算各销售员的产品平均销售量 350

技巧5 计算平均成绩（AVERAGE） 350

技巧6 使用AVERAGEA函数求包含文本值的平均值 351

技巧7 使用AVERAGEIF函数计算满足条件的数据的平均值 352

技巧8 在AVERAGEIF函数中使用通配符 353

技巧9 使用AVERAGEIFS函数计算出满足多重条件的数据的平均值 353

技巧10 在AVERAGEIFS函数中使用通配符 354

技巧11 使用COUNT函数统计销售记录条数 354

技巧12 使用COUNT函数按条件统计 355

技巧13 使用COUNTA函数统计包含文本值的单元格数 355

技巧14 使用COUNTBLANK函数统计空白单元格的数目 356

技巧15 使用COUNTIF函数统计出各类别产品的销售记录条数 356

技巧16 使用COUNTIF函数统计出空白单元格的个数 357

技巧17 利用COUNTIF函数统计出某一数据区间的数目 357

技巧18 使用COUNTIF函数设置双条件 358

技巧19 使用COUNTIF函数避免重复输入 358

技巧20 统计出特定区域中指定值（多个值）的个数 359

技巧21 使用COUNTIFS函数统计指定区域中满足多个条件的记录数目 359

技巧22 返回数据表中前三名的数据 360

技巧23 统计数据表中前五名的平均值 360

技巧24 在LARGE函数中按指定条件返回第一名数据 361

技巧25 在LARGE函数中按指定条件返回前三名的平均值 361

技巧26 返回数据表中后三名的数据 362

技巧27 统计数据表中后五名的平均成绩 362

技巧28 使用MAX（MIN）函数统计最高（最低）销售量 363

技巧29 按条件求取最大值 363

技巧30 求最小值时忽略0值 364

第18章 财务函数范例应用技巧 366

18.1筹资计算中的函数范例应用技巧 366

技巧1 计算贷款的每期偿还额 366

技巧2 当支付次数为按季度支付时计算每期应偿还额 366

技巧3 计算贷款每期偿还额中包含的本金额 367

技巧4 计算贷款每期偿还额中包含的利息额 367

技巧5 计算出住房贷款中每月还款利息额 368

技巧6 计算贷款在指定期间中（如第三年）的本金金额 368

技巧7 计算贷款在指定期间中（如第三年）的利息金额 369

18.2投资计算中的函数范例应用技巧 369

技巧1 计算购买某项保险的未来值 369

技巧2 计算住房公积金的未来值 370

技巧3 计算购买某项保险的现值 371

技巧4 计算出某项贷款的清还年数 371

技巧5 计算出某项投资的投资期数 372

技巧6 计算企业项目投资净现值 372

技巧7 计算某项投资的年金现值 373

技巧8 计算出一组不定期盈利额的净现值 373

技巧9 计算某项投资在可变利率下的未来值 374

技巧10 计算投资期内要支付的利息 374

技巧11 计算某项投资的内部收益率 375

技巧12 计算某项投资的修正内部收益率 375

技巧13 计算某项借款的收益率 376

技巧14 计算某项保险的收益率 376

18.3资产折旧计算中的函数范例应用技巧 377

技巧1 采用直线法计算出固定资产的每年折旧额 377

技巧2 采用直线法计算出固定资产的每月折旧额 378

技巧3 采用直线法计算出固定资产的每天折旧额 378

技巧4 采用固定余额递减法计算出固定资产的每年折旧额 379

技巧5 采用固定余额递减法计算出固定资产的每月折旧额 380

技巧6 采用双倍余额递减法计算出固定资产的每年折旧额 380

技巧7 计算出固定资产部分期间的设备折旧值 381

技巧8 采用年限总和法计算出固定资产的每年折旧额 382

技巧9 采用直线法计算累计折旧额 383

技巧10 采用余额递减法计算累计折旧额 384

技巧11 采用年限总和法计算累计折旧额 385

技巧12 使用AMORDEGRC函数计算每个会计期间的折旧值 387

第19章 查找、数据库函数范例应用技巧 390

19.1查找和引用函数范例应用技巧 390

技巧1 使用CHOOSE函数判断员工考核成绩是否合格 390

技巧2 使用CHOOSE函数评定多个等级 390

技巧3 求取一组数据的反转数据 391

技巧4 使用COLUMN函数建立有规律的三级序列编号 391

技巧5 使用COLUMN函数配合其他函数使用 392

技巧6 返回引用数据源包含的总列数 392

技巧7 建立有规律的三级序列编号（ROW） 393

技巧8 使用ROW函数自动控制要显示的行数 393

技巧9 返回引用数据源包含的总行数 394

技巧10 使用LOOKUP函数进行查询（向量型） 395

技巧11 使用LOOKUP函数进行查询（数组型） 395

技巧12 使用HLOOKUP函数获取数据 396

技巧13 使用HLOOKUP函数实现查询 397

技巧14 使用VLOOKUP函数进行查询 397

技巧15 使用VLOOKUP函数合并两张表的数据 398

技巧16 使用VLOOKUP函数进行反向查询 399

技巧17 使用MATCH函数返回指定元素所在位置 399

技巧18 使用INDEX函数实现查找（引用型） 400

技巧19 使用INDEX函数实现查找（数组型） 401

技巧20 使用INDEX配合其他函数实现查询出满足同一条件的所有记录 402

技巧21 配合使用INDEX与MATCH函数实现查询 403

技巧22 配合使用INDEX与MATCH函数实现双条件查询 404

技巧23 配合使用INDEX与MATCH函数实现反向查询 404

19.2数据库函数范例应用技巧 405

技巧1 统计特定产品的总销售数量 405

技巧2 实现双条件计算 406

技巧3 统计出去除某一位或多位销售员之外的销售数量 406

技巧4 使用通配符来统计满足条件的产品总销量 407

技巧5 避免DSUM函数的模糊匹配 408

技巧6 DSUM与SUMIF函数的区别 410

技巧7 统计特定班级平均分 410

技巧8 使用通配符统计出所有一店的平均利润 410

技巧9 统计某一特定班级各个科目的平均分 411

技巧10 计算出指定销售日期之前或之后的平均销售金额 412

技巧11 统计满足条件的记录条数 413

技巧12 使用DCOUNT函数实现双条件统计 413

技巧13 从成绩表中统计出某一分数区间的人数 414

技巧14 忽略0值统计记录条数 414

技巧15 统计满足指定条件的且为“文本”类型的记录条数 415

技巧16 使用DCOUNTA函数实现双条件统计 416

技巧17 统计各班成绩最高分 416

技巧18 统计各班成绩最低分 417

第20章 图表创建、编辑与设置技巧 420

20.1图表创建与编辑技巧 420

技巧1 不连续数据源建立图表 420

技巧2 图表数据源不连续时，实现向图表中增加数据源 421

技巧3 为图表添加数据 422

技巧4 使用颜色标记的区域向图表中添加数据 423

技巧5 更新图表中的数据 423

技巧6 创建混合型图表 424

技巧7 将创建的图表保存为模板 425

技巧8 使用模板新建图表 426

技巧9 一次性设置图表中所有文字的格式 426

技巧10 快速更改图表类型 427

技巧11 将建立的图表转化为静态图片 428

技巧12 隐藏工作表中的图表 429

技巧13 保护图表不被随意更改 430

技巧14 只允许对工作表中指定的某一个图表进行编辑 431

20.2图表坐标轴设置技巧 432

技巧1 删除（恢复）坐标轴的显示 432

技巧2 根据图表需求更改刻度线标签的位置 434

技巧3 重新更改分类轴标签的值 435

技巧4 让垂直轴显示在右侧 436

20.3图表数据系列设置技巧 437

技巧1 显示单个系列的数据标志 437

技巧2 显示单个数据点的数据标签 437

技巧3 重新建立图表数据源与数据标签的链接 438

技巧4 隐藏特定的数据系列 439

技巧5 更改数据系列的显示次序 440

技巧6 将饼图中特定的扇面分离出来 440

20.4趋势线、误差线应用与设置技巧 441

技巧1 向图表中添加趋势线 441

技巧2 用R平方值判断趋势线的可靠性 442

技巧3 使用移动平均趋势线 443

技巧4 重新更改趋势线的名称 444

技巧5 向图表中添加误差线 445

技巧6 在图表中表示只允许固定的负值误差 446

技巧7 快速设置各个数据点不同误差量时的误差线 446