Excel表格制作与专业数据分析PDF电子书下载

第1篇 基础知识 2

第1章 Excel 2013表格三元素的基本操作 2

1.1 工作簿的基本操作 2

1.1.1 创建新工作簿 2

1.1.2 保存工作簿 5

1.1.3 加密保护工作簿 6

1.2 工作表的基本操作 9

1.2.1 重命名工作表 9

1.2.2 插入新工作表 9

1.2.3 删除不需要的工作表 11

1.2.4 调整工作表的位置 11

1.2.5 复制工作表 12

1.2.6 隐藏工作表实现保护 13

1.2.7 保护工作表 14

1.2.8 保护工作表中的公式不被更改 15

1.3 单元格的基本操作 17

1.3.1 插入与删除单元格 17

1.3.2 插入行或列 18

1.3.3 合并单元格 21

1.3.4 设置单元格大小 22

1.3.5 隐藏含有重要数据的行或列 24

第2章 表格数据的输入与编辑 28

2.1 输入表格数据 28

2.1.1 输入文本内容 28

2.1.2 输入数值 29

2.1.3 输入日期型时间数据 33

2.1.4 输入特殊符号 36

2.1.5 从Word/PPT中导入数据 36

2.1.6 从网页中导入数据 37

2.1.7 从数据库中导入数据 38

2.1.8 数据填充输入 39

2.2 表格数据的修改、移动、复制与删除 42

2.2.1 修改数据 42

2.2.2 移动数据到新位置 42

2.2.3 复制数据 43

2.2.4 利用“粘贴预览”功能提前预览粘贴效果 43

2.2.5 删除数据 45

2.3 查找与替换数据 45

2.3.1 表格数据的查找 45

2.3.2 表格数据的替换 47

2.3.3 将所有查找到的数据标注特定格式 48

第3章 表格格式设置及美化 52

3.1 表格字体与对齐方式设置 52

3.1.1 设置表格字体 52

3.1.2 设置表格对齐方式 53

3.2 表格边框与底纹设置 55

3.2.1 设置表格边框效果 55

3.2.2 设置表格底纹效果 57

3.3 在表格中插入图片 59

3.3.1 删除图片背景 59

3.3.2 设置图片艺术效果 61

3.3.3 设置图片阴影、三维等效果 62

3.4 套用表格格式以美化表格 64

3.4.1 套用默认表格格式 64

3.4.2 新建表样式 65

3.5 利用格式刷快速复制表格样式 69

第4章 表格数据的计算 72

4.1 利用公式进行数据计算 72

4.1.1 什么是公式 72

4.1.2 输入公式 72

4.1.3 编辑公式 73

4.1.4 复制公式完成批量计算 73

4.2 公式计算中函数的使用 74

4.2.1 了解函数种类 74

4.2.2 通过“帮助”功能学习函数 75

4.2.3 编辑公式时使用“插入函数”向导 76

4.3 公式计算中的数据源引用 79

4.3.1 引用相对数据源计算 79

4.3.2 引用绝对数据源计算 80

4.3.3 在公式中引用当前工作表之外的数据源 81

4.3.4 在公式中引用其他工作簿数据源 84

4.4 公式中对名称的使用 85

4.4.1 将单元格区域定义为名称 85

4.4.2 在公式中使用定义的名称 86

4.4.3 名称管理 88

4.5 了解使用公式计算时经常出现的几种错误值 90

4.5.1 “#####”错误值 90

4.5.2 “#DIV＞0!”错误值 91

4.5.3 “#N/A”错误值 92

4.5.4 “#NAME?”错误值 93

4.5.5 “#NULL!”错误值 95

4.5.6 “#NUM!”错误值 96

4.5.7 “#VALUE!”错误值 96

4.5.8 “#REF!”错误值 98

4.6 公式检测与审核 98

4.6.1 追踪引用单元格 98

4.6.2 追踪从属单元格 99

4.6.3 使用“错误检查”功能辅助查找公式错误原因 100

4.6.4 通过“公式求值”功能逐步分解公式 100

第5章 Excel 2013基本数据分析功能的应用 104

5.1 表格数据排序 104

5.1.1 按单个条件排序 104

5.1.2 按多个条件排序 105

5.2 表格数据筛选 106

5.2.1 添加自动筛选 106

5.2.2 筛选出大于、小于、介于指定值等记录 107

5.2.3 筛选出同时满足两个或多个条件的记录 108

5.2.4 高级筛选 109

5.2.5 取消设置的筛选条件 111

5.3 表格数据分类汇总 112

5.3.1 创建分类汇总统计数据 112

5.3.2 更改汇总方式得到不同的统计结果 113

5.4 表格数据条件格式设置 114

5.4.1 设置大于特定值时显示特殊格式 114

5.4.2 让排名前5项的显示特殊格式 115

5.4.3 设置高于平均值时显示特殊格式 116

5.4.4 新建条件格式规则 117

5.4.5 管理条件格式规则 119

第6章 用数据透视表对数据库进行统计分析 122

6.1 数据透视表概述 122

6.2 使用数据透视表进行数据分析 123

6.2.1 创建数据透视表 124

6.2.2 编辑数据透视表 126

6.2.3 套用样式快速美化数据透视表 131

6.2.4 在数据透视表中对数据进行排序 132

6.2.5 在数据透视表中对数据进行筛选 134

6.3 创建数据透视图更直观地查看数据分析结果 137

6.3.1 创建数据透视图 137

6.3.2 在数据透视图中通过筛选查看部分数据 139

第7章 用图表进行可视化数据分析 142

7.1 图表概述 142

7.1.1 不同图表类型应用范围解析 142

7.1.2 准确选中图表中的对象 145

7.2 创建图表 146

7.2.1 用不同数据源建立不同表达效果的图表 146

7.2.2 快速创建迷你图 149

7.2.3 快速更改创建的图表类型 150

7.3 图表的编辑设置 151

7.3.1 添加图表标题 151

7.3.2 图例设置 152

7.3.3 坐标轴设置 153

7.3.4 添加数据标签 155

7.3.5 图表对象的美化设置 157

7.3.6 套用图表样式快速美化图表 162

第8章 用高级分析工具进行专业数据分析 166

8.1 安装与卸载分析工具库 166

8.1.1 安装分析工具库 166

8.1.2 卸载分析工具库 167

8.2 数据表（模拟运算表） 167

8.2.1 单变量数据表运算 167

8.2.2 双变量数据表运算 168

8.2.3 将模拟运算结果转换为常量 171

8.2.4 删除模拟运算结果 171

8.3 单变量求解 172

8.3.1 单变量求解预定销售计划 172

8.3.2 单变量求解可贷款金额 173

8.3.3 单变量求解贷款年限 174

8.4 方案管理器 175

8.4.1 建立方案 175

8.4.2 显示方案 178

8.4.3 合并方案 179

8.4.4 编辑方案 181

8.4.5 创建方案摘要 182

8.5 规划求解 183

8.5.1 规划求解确定最优生产方案 183

8.5.2 规划求解确定最优运输方案 186

第2篇 行业案例 194

第9章 企业办公用品管理与分析 194

9.1 创建办公用品采购申请表 194

9.1.1 规划办公用品采购申请表框架 194

9.1.2 表格区域格式的设置 194

9.1.3 设置公式自动返回相关信息 196

9.2 创建办公用品采购表 198

9.2.1 建立办公用品采购表 198

9.2.2 插入图片并设置超链接 199

9.3 办公用品采购记录与分析 200

9.3.1 建立办公用品采购记录统计表 200

9.3.2 对办公用品创建分类汇总 201

9.3.3 比较分析采购办公用品费用 203

9.4 创建办公用品领用申请表与登记表 204

9.4.1 建立办公用品领用申请表 204

9.4.2 建立并保护办公用品领取登记表 206

9.5 办公用品库存统计与分析 209

9.5.1 建立办公用品库存统计表 209

9.5.2 比较分析办公用品库存 210

第10章 企业员工培训管理与考核成绩分析 215

10.1 创建员工培训计划表 215

10.1.1 规划员工培训计划表框架 215

10.1.2 复制并粘贴单元格内容 216

10.1.3 在表格中插入多行 217

10.2 创建员工培训课程安排表 218

10.2.1 建立员工培训课程安排表框架 218

10.2.2 设置不同的数字格式 220

10.3 创建员工培训成绩表 223

10.3.1 建立员工培训成绩表框架 223

10.3.2 设置公式返回培训成绩、名次等 223

10.4 创建员工培训成绩查询表 227

10.4.1 建立员工培训成绩查询表框架 227

10.4.2 在查询表中设置员工编号的数据有效性 228

10.4.3 在查询表中通过员工编号设置公式 229

第11章 企业员工档案信息管理与分析 233

11.1 建立档案管理表 233

11.1.1 规划员工档案表框架 233

11.1.2 表格区域单元格格式设置 237

11.1.3 设置公式自动返回相关信息 241

11.2 快速查询任意员工档案 245

11.2.1 建立员工档案查询表框架 246

11.2.2 设置单元格的公式 247

11.2.3 实现快速查询 249

11.3 分析企业员工学历层次 249

11.3.1 建立数据透视表分析员工学历层次 249

11.3.2 用图表直观显示各学历占比情况 251

11.4 分析企业员工年龄层次 252

11.4.1 建立数据透视表分析员工年龄层次 252

11.4.2 用图表直观显示各年龄段人数占比情况 254

11.5 分析企业员工男女比例 255

第12章 企业员工考勤记录统计与分析 259

12.1 创建考勤表记录员工出勤情况 259

12.1.1 创建可自由选择年、月的考勤表表头 259

12.1.2 设置根据当前年、月自动显示日期及对应星期数 263

12.1.3 员工考勤记录表表体的创建 269

12.2 统计本月出勤状况和计算应扣工资 270

12.2.1 统计员工指定月出勤数据 270

12.2.2 计算满勤奖、应扣工资 272

12.3 分析各部门的出勤情况 273

12.3.1 建立数据透视表分析各部门出勤情况 274

12.3.2 建立数据透视图直观比较出勤状况 275

12.4 员工加班统计与管理 277

12.4.1 建立加班统计表 277

12.4.2 统计加班的时长并计算加班费 279

第13章 企业日常费用支出管理与预算分析 283

13.1 建立日常费用支出统计表 283

13.1.1 规划日常费用支出统计表框架 283

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

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

13.2.1 统计不同部门产生的支出金额总计 288

13.2.2 统计不同类别费用的支出金额总计 288

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

13.3.1 统计各类别费用支出金额 290

13.3.2 分析各部门费用支出明细 292

13.3.3 比较各支出部门1、2月费用支出额 293

13.4 日常支出费用预算分析 296

13.4.1 建立全年费用预算记录表 296

13.4.2 1月份实际费用与预算费用比较分析 296

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

第14章 企业员工工资核算管理与分析 308

14.1 在一个工作簿中创建工资结算时要使用的多张表格 308

14.1.1 创建员工基本工资管理表 308

14.1.2 创建员工福利补贴管理表 311

14.1.3 创建本月奖惩管理表 314

14.1.4 创建考勤扣款、满勤奖统计表 315

14.1.5 创建加班工资统计表 316

14.2 创建工资统计表 318

14.2.1 创建工资统计表格 318

14.2.2 计算工资表中应发金额 319

14.2.3 计算工资表中应扣金额并生成工资表 323

14.3 生成员工工资条 326

14.3.1 建立第一位员工的工资条 326

14.3 2快速生成每位员工的工资条 328

14.4 比较销售部门各员工业绩提成 329

14.4.1 创建数据透视表显示销售部的业绩提成记录 329

14.4.2 创建数据透视图直观比较销售部各员工业绩提成 331

第15章 企业往来账款管理与分析 336

15.1 建立应收账款清单表 336

15.1.1 规划应收账款清单表的框架 336

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

15.2 计算各条应收账款的账龄 340

15.3 分客户统计应收账款 342

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

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

15.4 分客户分账龄分析应收账款 345

15.4.1 建立“分客户分账龄分析应收账款”表 345

15.4.2 用图表直观分析各客户账龄 347

15.5 建立应付账款清单表 348

15.5.1 规划应付账款清单表的框架 348

15.5.2 设置公式分析各项应付账款 349

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

15.6 各往来客户总应付账款统计 353

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

15.6.2 分析各往来单位应付账款占比 355

第16章 企业新产品市场调查信息统计与分析 360

16.1 设计调查问卷和调查结果接收表 360

16.1.1 在Excel工作表中建立调查问卷 360

16.1.2 建立记录调查结果的工作表 363

16.2 调查结果的汇总统计 366

16.2.1 汇总所有调查结果 366

16.2.2 替换编码生成结果数据库 367

16.3 样本组成分析 372

16.3.1 样本性别组成分析 372

16.3.2 样本年龄组成分析 374

16.4 产品认知度分析 376

16.4.1 分析产品知名度 376

16.4.2 年龄与产品认知度的关系 378

16.5 分析产品最爱欢迎的特性 379

16.5.1 建立表格统计产品各特性被选中的次数 380

16.5.2 利用图表直观显示出消费者喜欢的性能 381

16.6 分析产品返修率 381

16.7 分析潜在消费者 383

第17章 企业销售数据统计管理与分析 387

17.1 销售数据统计报表的创建与计算 387

17.1.1 建立商品一览表 388

17.1.2 建立销售数据统计报表 388

17.1.3 计算交易金额、成本、毛利等 391

17.2 各店铺的销售情况分析 393

17.2.1 查看指定店铺指定品牌商品的销售记录 394

17.2.2 各店铺交易金额比较 395

17.2.3 各店铺不同品牌销售明细 396

17.2.4 各店铺不同时段销售金额比较 397

17.3 各品牌商品的销售情况分析 398

17.3.1 各品牌商品交易金额比较 398

17.3.2 各品牌在不同店铺的销售明细 400

17.4 业务员业绩分析 402

17.4.1 计算销售员的业绩奖金 402

17.4.2 各销售员销售的不同品牌商品明细 403

17.5 比较分析各商品的利润率 404

17.6 查询商品的最高销售价格 407

17.6.1 在“销售记录表”中定义名称方便公式使用 407

17.6.2 设置公式查询各商品最高销售单价 408

第18章 企业材料采购、库存数据管理与分析 413

18.1 建立商品一览表 413

18.2 建立采购、出库数据管理表 414

18.2.1 建立采购数据管理表 414

18.2.2 建立出库数据管理表 419

18.3 建立库存统计表 419

18.3.1 设置公式返回产品基本信息 420

18.3.2 设置公式计算本期入库、出库与库存 422

18.4 采购库存数据统计分析 425

18.4.1 从各供应商处采购汇总 425

18.4.2 采购系列产品的汇总 427

18.4.3 比较各系列商品的出入库数据 428

18.4.4 查询期末库存较小的记录为下期采购做准备 431

18.5 根据本期出入库数据计算销售成本、利润等 433

第3篇 技能提高 438

第19章 表格数据编辑技巧 438

19.1 单元格格式设置技巧 438

技巧1 当输入内容超过宽度时自动调整列宽 438

技巧2 当输入内容超过宽度时自动换行 439

技巧3 当输入内容超过宽度时自动缩小字体 439

技巧4 自定义日期的格式 440

技巧5 快速输入大写人民币值 440

技巧6 快速输入大量负数 441

技巧7 快速输入有部分重复的数据 442

技巧8 快速标记重复数据 443

技巧9 巧妙划分数据等级 444

技巧10 一次清除表格中所有单元格的格式设置 444

19.2 数据输入技巧 445

技巧1 重新设置工作表默认字体、字号 445

技巧2 快速增减数据的小数位数 445

技巧3 快速输入以0开头的数字 446

技巧4 快速输入15位以上的数据 446

技巧5 在多个不连续单元格中输入相同的数据 447

技巧6 填充日期数据时，选择以月数（或工作日）来进行填充 448

技巧7 自定义填充序列 448

技巧8 为特定单元格添加批注信息 450

技巧9 为特定单元格设置超链接 451

19.3 表格数据的复制粘贴及行、列操作技巧 452

技巧1 在同一行或同一列中复制数据 452

技巧2 利用“选择性粘贴”实现加、减、乘、除运算 452

技巧3 让粘贴数据随源数据自动更新 453

技巧4 将表格转化为图片 454

技巧5 将Excel表格插入到Word中且能自动更新 455

技巧6 快速选择数据区域 456

技巧7 隔行插入空白行 457

技巧8 快速删除空行 457

技巧9 实现数据行列转置 459

第20章 数据透视表使用技巧 462

20.1 数据透视表编辑技巧 462

技巧1 使用外部数据源建立数据透视表 462

技巧2 更改透视表的数据源 463

技巧3 更改数据透视表默认布局 463

技巧4 在数据透视表中双击鼠标查看明细数据 464

技巧5 显示字段的明细数据 464

技巧6 隐藏特定的记录 465

技巧7 更改“数据透视表字段”任务窗格的布局 466

技巧8 更改数据透视表的分类汇总布局 466

技巧9 自定义数据透视表样式 467

技巧10 使用数据透视表进行分数段统计 468

技巧11 查找某列中出现次数最多的10种数据 470

技巧12 启用字段拖放功能 471

技巧13 快速清除数据透视表中的所有字段 471

技巧14 将数据透视表转换为普通表格 472

20.2 数据透视表属性设置技巧 472

技巧1 让数据透视表打开时自动更新 472

技巧2 禁止他人通过双击单元格查看明细数据 473

技巧3 显示或隐藏整个报表的总计项 473

技巧4 解决刷新数据透视表后所有格式都消失的问题 474

技巧5 让数据透视表中的空白单元格显示为0 474

技巧6 恢复选中单元格时显示提示文字 475

第21章 图表使用技巧 478

21.1 图表数据编辑设置技巧 478

技巧1 重新更改图表数据源 478

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

技巧3 按实际需要更改系列的名称 480

技巧4 一键快速创建Excel图表 481

技巧5 让图表绘制出被隐藏的数据源 481

技巧6 以0值替代数据源的空值 481

技巧7 更改数据系列的显示次序 482

技巧8 切换行列改变图表的表达重点 483

技巧9 隐藏特定的数据系列 484

技巧10 让图表数据源显示在图表中 485

21.2 图表中对象格式设置技巧 486

技巧1 让图表标题自动与表格标题同步 486

技巧2 快速设置图表标题格式 487

技巧3 重新设置刻度值的数字格式 488

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

技巧5 更改垂直轴与水平轴的交叉位置 490

技巧6 快速设置让垂直轴显示在右侧 491

技巧7 设置数据系列分离（重叠）显示 491

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

技巧9 更改图表默认布局 493

技巧10 固定图表的大小与位置使其不改变 494

技巧11 解决柱形图水平轴标签显示次序颠倒的问题 494

技巧12 设置饼图第一扇区的起始角度 495

技巧13 将饼图中特定的扇面分离出来 496

技巧14 快速复制图表格式 496

技巧15 保护图表不被随意更改 497

技巧16 只允许对工作表中指定的图表进行编辑 497

第22章 常用函数使用技巧 500

22.1 数学函数使用技巧 500

技巧1 用SUM函数计算各仓库总库存量 500

技巧2 用SUM函数计算产品的总销售额 500

技巧3 统计某一销售员的总销售金额 501

技巧4 统计技术部中具有本科学历的员工人数 501

技巧5 将出库数据按月份进行汇总 502

技巧6 统计各部门年终奖发放总额 503

技巧7 统计某个时段的销售总额 503

技巧8 统计编号含“NY”产品的销售总额 504

技巧9 计算销售金额前3名的合计值 505

技巧10 统计上半年各类产品的销售合计值 505

技巧11 统计某一日期区间的销售金额 506

技巧12 用SUMPRODUCT函数计算产品的总销售额 507

技巧13 统计出某两种商品的销售金额合计值 507

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

22.2 文本、逻辑函数使用技巧 508

技巧1 从商品全称中提取其产地 508

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

技巧3 计算公司季度会议的实际参加人数 510

技巧4 根据代码返回产品名称 512

技巧5 根据员工的部门与职位进行工资的增长 512

技巧6 综合评定员工的考核成绩是否达标 513

技巧7 根据考核成绩决定哪些员工可以“参与培训” 514

22.3 查找和引用函数使用技巧 515

技巧1 查找绩效成绩最高的员工姓名 515

技巧2 在销售表中自动返回产品销售单价 515

技巧3 根据多个条件计算员工年终奖 517

技巧4 查询指定编号员工指定季度的销售量 517

技巧5 查找迟到次数最多的员工 518

技巧6 对每日采购量累计求和 519

技巧7 进出库合计统计 520

技巧8 实现隔列求总销售金额 521

22.4 统计函数使用技巧 522

技巧1 忽略0值求平均销售金额 522

技巧2 计算行政部女员工的平均工资 522

技巧3 计算每季度平均出库数量 523

技巧4 忽略0值求指定部门的平均报销金额 523

技巧5 统计员工培训考核成绩及格率 524

技巧6 统计出大于指定销售数量的人数 525

技巧7 统计出大于平均销售金额的人数 526

技巧8 统计指定时间指定类别商品的销售记录数 526

技巧9 计算单日最高销售金额 527

技巧10 忽略0值求最低销售金额 527

技巧11 分别统计各部门第一名销售业绩 528

技巧12 为员工的销售业绩进行名次排序 529

22.5 财务函数使用技巧 529

技巧1 计算贷款的每期付款额 529

技巧2 计算贷款指定期间的本金偿还额 530

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

技巧4 计算投资期内要支付的利息额 532

技巧5 计算购买某项保险的收益率 533

技巧6 计算购买某项保险的现值 534

第23章 表格安全、打印与输出技巧 536

23.1 表格安全设置技巧 536

技巧1 设置允许用户进行的操作 536

技巧2 隐藏含有重要数据的工作表 537

技巧3 指定工作表中的可编辑区域 538

技巧4 设置可编辑区域的权限 539

技巧5 解决设置工作表保护却起不到保护作用的问题 540

技巧6 保护工作簿不能被修改 541

技巧7 设置工作簿打开权限密码 541

技巧8 保护公式不被更改 542

技巧9 阻止文档中的外部内容 543

23.2 表格打印与输出操作技巧 544

技巧1 打印指定的页 544

技巧2 打印工作表中某一特定区域 545

技巧3 一次性打印多张工作表 546

技巧4 将行、列号一同打印出来 546

技巧5 将工作表上的网格线打印出来 547

技巧6 将数据缩放在一页纸内打印 548

技巧7 打印超宽工作表 549

技巧8 重复打印标题行 549

技巧9 打印页眉或页脚时添加分割线 551