Excel表格制作与数据处理PDF电子书下载

第1篇 基础知识 2

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

1.1工作簿的基本操作 2

1.1.1创建新工作簿 2

1.1.2保存工作簿 4

1.1.3加密保护工作簿 6

1.2工作表的基本操作 7

1.2.1重命名工作表 7

1.2.2插入新工作表 8

1.2.3删除不需要的工作表 9

1.2.4调整工作表的位置 9

1.2.5复制工作表 10

1.2.6隐藏工作表实现保护 11

1.2.7保护工作表 12

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

1.3单元格的基本操作 15

1.3.1插入删除单元格 15

1.3.2插入行或列 16

1.3.3合并单元格 19

1.3.4设置单元格大小 19

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

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

2.1表格数据输入 24

2.1.1输入文本内容 24

2.1.2输入数值 25

2.1.3输入日期型时间数据 29

2.1.4输入特殊符号 31

2.1.5从Word/PPT中导入数据 32

2.1.6从网页中导入数据 33

2.1.7从数据库中导入数据 34

2.1.8数据填充输入 35

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

2.2.1修改数据 37

2.2.2将数据移动到新位置 38

2.2.3复制数据以实现快速输入 38

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

2.2.5删除数据 41

2.3查找与替换数据 41

2.3.1表格数据的查找 41

2.3.2表格数据的替换 42

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

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

3.1表格字体与对齐万式设置 48

3.1.1设置表格字体 48

3.1.2设置表格对齐方式 49

3.2表格边框与底纹设置 51

3.2.1设置表格边框效果 51

3.2.2设置表格底纹效果 53

3.3在表格中插入图片 56

3.3.1删除图片背景 56

3.3.2设置图片艺术效果 58

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

3.4套用表格格式来美化表格 61

3.4.1套用默认表格格式 61

3.4.2新建表样式 62

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

第4章 表格数据的计算 68

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

4.1.1什么是公式 68

4.1.2输入公式 68

4.1.3编辑公式 69

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

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

4.2.1了解函数种类 70

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

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

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

4.3.1引用相对数据源计算 76

4.3.2引用绝对数据源计算 77

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

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

4.4公式中对名称的使用 82

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

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

4.4.3名称管理 85

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

4.5.1“#####”错误值 87

4.5.2 “#DIV〉0！”错误值 87

4.5.3 “#N〉A”错误值 88

4.5.4 “#NAME”错误值 90

4.5.5 “#NLL！”错误值 92

4.5.6 “#NUM！”错误值 92

4.5.7 “#VALUE！”错误值 92

4.5.8 “#REF！”错误值 94

4.6公式检测与审核 95

4.6.1追踪引用单元格 95

4.6.2追踪从属单元格 96

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

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

第5章表格数据的分析管理 100

5.1表格数据排序 100

5.1.1按单个条件排序 100

5.1.2按多个条件排序 101

5.2表格数据筛选 102

5.2.1添加自动筛选 102

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

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

5.2.4高级筛选 106

5.2.5取消设置的筛选条件 108

5.3表格数据分类汇总 108

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

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

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

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

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

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

5.4.4新建条件格式规则 114

5.4.5管理条件格式规则 117

第6章 用数据透视表分析数据 117

6.1数据透视表概述 120

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

6.2.1创建数据透视表 122

6.2.2数据透视表的编辑 125

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

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

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

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

6.3.1创建数据透视图 136

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

第7章 用图表分析数据 140

7.1图表概述 140

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

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

7.2创建图表 144

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

7.2.2快速创建迷你图 146

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

7.3图表的编辑设置 148

7.3.1添加图表标题 148

7.3.2图例设置 149

7.3.3坐标轴设置 150

7.3.4添加数据标签 152

7.3.5图表对象的美化设置 154

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

第2篇 行业案例 163

第8章企业员工档案管理表的创建与分析 163

8.1在Excel中建立档案管理表 163

8.1.1规划员工档案表框架 163

8.1.2表格区域单元格格式设置 167

8.1.3设置公式自动返回相关信息 171

8.2快速查询任意员工档案 176

8.2.1建立员工档案查询表框架 176

8.2.2设置单元格的公式 177

8.2.3实现快速查询 179

8.3分析企业员工学历层次 179

8.3.1建立数据透视表分析员工学历层次 179

8.3.2用图表直观显示各学历占比情况 181

8.4分析企业员工年龄层次 182

8.4.1建立数据透视表分析员工年龄层次 182

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

8.5分析企业员工男女比例 185

第9章 企业员工考勤表的创建与统计分析 189

9.1创建考勤表记录员工出勤情况 189

9.1.1创建可自由选择年、月的考勤表表头 189

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

9.1.3员工考勤记录表表体的创建 198

9.2统计本月出勤状况和计算应扣工资 200

9.2.1统计各员工本月出勤数据 200

9.2.2计算满勤奖、应扣工资 202

9.3分析各部门的出勤情况 204

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

9.3.2建立数据透视图直观比较出勤状况 206

9.4员工加班统计与管理 208

9.4.1建立加班统计表 208

9.4.2统计加班的时长并计算加班费 209

第10章 企业日常费用支出与预算管理 209

10.1建立日常费用支出统计表 215

10.1.1规划日常费用支出统计表框架 215

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

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

10.2.1统计不同部门产生的支出金额总计 220

10.2.2统计不同类别费用的支出金额总计 221

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

10.3.1统计各类别费用支出金额 222

10.3.2分析各部门费用支出明细 225

10.3.3比较各支出部门1、2月费用支出额 226

10.4日常支出费用预算分析 229

10.4.1建立全年费用预算记录表 229

10.4.2 1月份实际费用与预算费用比较分析 229

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

第11章企业员工工资核算与管理 239

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

11.1.1创建员工基本工资管理表 239

11.1.2创建员工福利补贴管理表 242

11.1.3创建本月奖惩管理表 244

11.1.4创建考勤扣款、满勤奖统计表 246

11.1.5创建加班工资统计表 247

11.2创建工资统计表 249

11.2.1创建工资统计表格 249

11.2.2计算工资表中应发金额 250

11.2.3计算工资表中应扣金额并生成工资表 253

11.3生成员工工资条 257

11.3.1建立第一位员工的工资条 257

11.3.2快速生成每位员工的工资条 259

11.4比较销售部门各员工业绩提成 260

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

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

第12章企业往来账款管理与分析 266

12.1建立应收账款清单表 266

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

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

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

12.3分客户统计应收账款 271

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

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

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

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

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

12.5建立应付账款清单表 278

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

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

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

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

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

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

第13章企业新产品市场调查与分析 290

13.1设计调查问卷和调查结果接收表 290

13.1.1在Excel工作表中建立调查问卷 290

13.1.2建立记录调查结果的工作表 294

13.2调查结果的汇总统计 297

13.2.1汇总所有调查结果 297

13.2.2替换编码生成结果数据库 298

13.3样本组成分析 302

13.3.1样本性别组成分析 303

13.3.2样本年龄组成分析 305

13.4产品认知度分析 307

13.4.1分析产品知名度 307

13.4.2年龄与产品认知度的关系 309

13.5分析产品最爱欢迎的特性 311

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

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

13.6分析产品返修率 313

13.7分析潜在消费者 315

第14章销售数据管理表格的创建与分析 320

14.1销售数据统计报表的创建与计算 320

14.1.1建立商品一览表 320

14.1.2建立销售数据统计报表 320

14.1.3计算交易金额、成本、毛利等 323

14.2各店铺的销售情况分析 326

14.2.1查看指定店铺指定品牌商品的销售记录 326

14.2.2各店铺交易金额比较 328

14.2.3各店铺不同品牌销售明细 329

14.2.4各店铺不同时段销售金额比较 330

14.3各品牌商品的销售情况分析 331

14.3.1各品牌商品交易金额比较 332

14.3.2各品牌在不同店铺销售明细 334

14.4业务员业绩分析 336

14.4.1计算销售员的业绩奖金 336

14.4.2各销售员销售的不同品牌商品明细 337

14.5比较分析各商品的利润率 338

14.6查询商品的最高销售价格 342

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

14.6.2设置公式查询各商品最高销售单价 343

第15章采购、库存数据管理表格的创建与分析 350

15.1建立商品一览表 350

15.2建立采购、库存数据管理表 350

15.2.1建立采购数据管理表 350

15.2.2建立出库数据管理表 355

15.3建立库存统计表 356

15.3.1设置公式返回产品基本信息 356

15.3.2设置公式计算本期入库、出库与库存 359

15.4采购库存数据统计分析 362

15.4.1从各供应商处采购汇总 362

15.4.2采购系列产品的汇总 364

15.4.3比较各系列商品的出入库数据 365

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

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

第3篇技能提高篇 376

第16章表格数据的输入、编辑技巧 376

16.1单元格格式设置技巧 376

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

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

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

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

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

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

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

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

16.2数据输入技巧 382

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

技巧2在输入数据前自动添加特定位数小数 383

技巧3快速增减数据的小数位数 383

技巧4在单元格中同时输入日期与时间 384

技巧5输入网址和电子邮件地址后不会建立超链接 384

技巧6在多个不连续单元格中输入相同数据 385

技巧7填充日期数据时，选择以月数来进行填充 385

技巧8填充日期数据时，选择以工作日来进行填充 386

技巧9自定义填充序列 387

技巧10为特定单元格添加批注信息 389

技巧11为特定单元格设置超链接 389

16.3表格数据的复制粘贴技巧 391

技巧1让复制的数据匹配目标区域的格式 391

技巧2实现数据行列转置 391

技巧3利用“选择性粘贴”功能实现加、减、剩、除运算 392

技巧4让粘贴数据随原数据自动更新 393

技巧5将表格转化为图片 393

技巧6将Excel表格插入到Word中 395

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

技巧8将表格转换为图片且图片中数据可以自动更新 397

技巧9找回丢失的“粘贴选项”按钮 398

第17章 数据透视表与图表使用技巧 398

17.1数据透视表应用技巧 400

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

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

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

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

技巧5隐藏特定的记录 403

技巧6显示字段的明细数据 403

技巧7显示或隐藏整个报表的总计项 404

技巧8让数据透视表打开时自动更新 404

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

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

技巧11将数据透视表转换为普通表格 406

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

技巧13删除数据透视表 407

17.2图表应用技巧 407

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

技巧2按实际需要更改系列的名称 408

技巧3一张图表中使用两种图表类型 409

技巧4让图表绘制出被隐藏的数据源 411

技巧5以0值替代数据源的空值 412

技巧6使用模板新建图表 413

技巧7固定图表的大小与位置让其不改变 413

技巧8快速复制图表格式 414

技巧9切换行列改变图表的表达重点 414

技巧10保护图表不被随意更改 415

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

技巧12实现让水平轴从右向左显示 416

技巧13重新设置刻度值的数字格式 416

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

技巧15重新更改分类轴标签 418

技巧16更改垂直轴与水平轴的交叉位置 418

技巧17让垂直轴显示在右侧 419

技巧18解决条形图垂直轴标签显示次序颠倒的问题 420

技巧19使用对数刻度绘制图表 420

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

技巧21隐藏特定的数据系列 422

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

技巧23更改数据系列的显示次序 424

技巧24设置饼图第一扇区的起始角度 424

技巧25将饼图中特定的扇面分离出来 425

第18章 常用函数使用技巧 428

18.1数学函数使用技巧 428

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

技巧2用SUM函数计算其总销售额 428

技巧3统计某一经办人的总销售金额 429

技巧4统计销售部女员工人数 429

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

技巧6统计各部门工资总额 431

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

技巧8统计“裙”类衣服的销售总金额 432

技巧9计算销售金额前两名合计值 433

技巧10统计上半个月各类产品的销售合计值 433

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

技巧12根据销量与单价计算总销售额 435

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

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

技巧15统计指定店面、指定类别产品的销售金额合计值 436

技巧16统计非工作日销售金额 437

技巧17计算每次通话费用 437

技巧18计算物品的快递费用 438

技巧19根据上网时间计算上网费用 439

18.2文本、逻辑函数使用技巧 439

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

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

技巧3计算各项课程的实际参加人数 442

技巧4根据代码返回部门名称 443

技巧5根据产品的名称与颜色进行一次性调价 443

技巧6三门功课都达标时考评结果为达标 445

技巧7三项成绩有一项大于80分时考评结果为“参与培训” 445

技巧8根据消费卡类别与消费额派发赠品 446

技巧9根据职工性别和职务判断退休年龄 446

18.3查找和引用函数使用技巧 447

技巧1根据值班日期自动返回工资标准 447

技巧2查找成绩最高的学生姓名 448

技巧3通过下拉菜单查询任意科目的成绩 449

技巧4在销售表中自动返回产品单价 450

技巧5将两张成绩表合并为一张成绩表 452

技巧6根据多条件计算员工年终奖 453

技巧7使用VLOOKUP函数进行反向查询 454

技巧8查询指定学号学生指定科目的成绩 454

技巧9查找迟到次数最多的学生 456

技巧10查找指定月份指定专柜的销售金额 456

技巧11查询成绩最高的学生姓名 457

技巧12对每日出库量累计求和 457

技巧13进出库合计统计 458

技巧14实现隔列求总销售金额 459

18.4统计函数使用技巧 460

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

技巧2统计各班级平均分 461

技巧3计算一车间女职工平均工资 462

技巧4计算每季度平均出库数量 462

技巧5忽略0值求指定班级的平均分 463

技巧6统计学生成绩及格率 464

技巧7统计出大于指定销售金额的人数 465

技巧8统计出大于平均分数的学生人数 465

技巧9统计是三好学生目参加数学竞赛的人数 466

技巧10统计连续3次考试都进入前10名的人数 466

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

技巧12返回企业女性员工的最大年龄 468

技巧13计算单日最高销售金额 468

技巧514快速判断某项测试的最佳成绩 469

技巧15忽略0值求最低分数 469

技巧16统计成绩表中前5名的平均值 469

技巧17分别统计各班级第一名成绩 470

技巧18为学生考试成绩排名次 471

技巧19返回指定季度销售额排名（对不连续单元格排名次） 471

技巧20解决出现相同名次时缺省名次数的问题 472

18.5财务函数使用技巧 473

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

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

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

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

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

技巧6计算住房公积金的未来值 477

技巧7计算购买某项保险的现值 478

技巧8计算出贷款的清还年数 478

技巧9计算某项投资的内部收益率 479

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

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

技巧12用直线法计算出固定资产每年的折旧额 482

技巧13用年数总和法计算出固定资产每年的折旧额 483

第19章 条件格式与数据有效性应用技巧 486

19.1条件格式应用技巧 486

技巧1标识出两个部门不同的采购价格 486

技巧2突出显示成绩前三名的学生 487

技巧3标识出重复值班的员工 488

技巧4将成绩高于平均值的标注为“优” 489

19.2数据有效性应用技巧 490

技巧5避免输入错误日期值 490

技巧6避免输入重复的产品编码 490

技巧7提示出货数量大于进货数量 491

技巧8禁止录入不完整的产品规格 492