Excel数据处理与分析实战宝典PDF电子书下载

第1章 数据处理基本技巧 1

1.1认识Excel表格 1

1.1.1 Excel表格中的对象 2

1.1.2认识Excel超级表 3

1.2数据验证的强大功能 4

1.2.1数据验证应用之一：规范数据输入 5

1.2.2数据验证应用之二：制作二级下拉菜单 6

1.2.3名称管理器突破数据验证的限制 8

1.2.4数据验证圈释无效数据 10

1.3神奇的选择性粘贴 11

1.3.1数据位置不变实现万元来回切换 12

1.3.2选择性粘贴清除超链接 13

1.3.3选择性粘贴转换数据类型 14

1.3.4跳过空单元格 14

1.4查找和替换 15

1.4.1批量清除单元格中的空格或换行符 16

1.4.2批量替换公式 18

1.4.3批量替换通配符*或？ 18

1.4.4批量插入年份 19

1.4.5垂直数据转换为水平数据 20

1.5奇妙的定位条件 22

1.5.1由上向下批量填充 23

1.5.2左右批量填充 25

1.5.3阶梯状批量填充 27

1.5.4删除对象实现文件瘦身 29

1.5.5定位空值实现批量求和 30

1.6创建、关闭输入超链接 31

1.6.1创建超链接 31

1.6.2关闭输入超链接 34

1.7单元格格式设置 34

1.7.1认识各种数据格式 34

1.7.2空单元格与空文本 35

1.7.3自定义单元格格式 36

1.7.4合并单元格 40

1.8基本技巧综合应用案例 42

1.8.1提取混合单元格中的数字 42

1.8.2查找和替换的妙用 45

第2章 数据专项处理技巧 48

2.1条件格式 48

2.1.1认识条件格式 48

2.1.2条件格式的简单应用 49

2.1.3用四色交通灯标示财务状态 51

2.1.4对查询的数据高亮显示 52

2.1.5标识两列中不同的物料名称 54

2.2排序、筛选与分类汇总 56

2.2.1排序、筛选与分类汇总对数据的要求 56

2.2.2按图标集进行数据排序 57

2.2.3使用自定义序列排序 58

2.2.4利用排序生成成绩单 61

2.3合并计算 63

2.3.1利用公式合并计算 63

2.3.2按位置进行合并计算 64

2.3.3按项目进行合并计算 66

2.3.4利用合并计算对比差异 66

2.4名称管理器 69

2.4.1认识名称管理器 69

2.4.2创建名称的3种方式 69

2.4.3名称在函数中的应用：合并报表编制 72

2.5数据分列 73

2.5.1固定宽度的数据分列 73

2.5.2对SAP屏幕中复制出来的数据分列 75

2.5.3按分隔符号进行数据分列 76

2.5.4利用分列改变数据类型 77

2.5.5快速填充处理无法分列的数据 79

2.6数据异常处理 80

2.6.1数据异常的常见问题及处理技巧 80

2.6.2记事本程序“捉妖记” 81

2.6.3利用函数清理异常数据 83

2.6.4利用分列清理异常数据 83

2.6.5利用Word清理异常数据 85

2.6.6无法插入列或行表格的处理 86

2.6.7删除重复数据 87

2.6.8利用SQL语句实现文件的瘦身 89

2.6.9利用剪贴板处理同一列中带不同货币符号的数据 92

2.7数据的导入与导出 94

2.7.1 Excel数据的导入、导出简介 94

2.7.2 Excel SQL基础知识 95

2.7.3使用OLE DB导入外部数据 98

2.7.4使用Microsoft Query查询外部数据 103

2.7.5联合查询、子查询 111

2.7.6 SQL与数据透视表 114

2.7.7导入文本格式的数据 121

2.7.8导出到文本文件 124

2.8数据专项处理技巧综合案例 125

2.8.1巧用批量插入行 125

2.8.2批量合并单元格 128

第3章 数据透视表基础 131

3.1认识数据透视表 131

3.2制作数据透视表的一般步骤 133

3.3数据透视表的修改及其布局调整 135

3.4数据透视表的格式设置 140

3.5数据透视表值字段的设置 148

第4章 数据透视表与Power系列 153

4.1在数据透视表中定义公式 153

4.2对数据透视表中的项进行组合 158

4.3利用名称创建动态数据透视表 167

4.4切片器在数据透视表中的应用 169

4.5日程表在数据透视表中的应用 174

4.6单页字段数据透视表 176

4.7利用数据透视表拆分表格 180

4.8利用数据透视表转换表结构 184

4.9 PowerPivot和数据透视表 188

4.10使用数据模型 191

4.11利用PowerPivot和切片器制作销售看板 195

4.12 Power Query逆操作二维表 204

4.13利用Power Query实现发货标签数据整理 207

4.14多表数据关联关系的基本概念 212

4.15 利用Power Query展开BOM计算产品材料成本 214

4.16利用Power Query拆分连续号码查找差异 225

第5章 Excel函数与公式 230

5.1函数与公式基础 230

5.2公式中的引用 233

5.3公式的查错与监视 238

第6章 逻辑函数 242

6.1逻辑函数概述 242

6.2逻辑函数案例：个人所得税计算 244

6.3逻辑函数综合应用1：业务员星级评定 246

6.4逻辑函数综合应用2：应收账款账龄分析模型 246

6.5利用IF函数实现数据的批量填充 247

第7章 求和、统计函数 249

7.1求和、统计函数概述 249

7.2求和、统计函数应用案例 250

7.2.1多条件求和公式 250

7.2.2模糊条件求和 255

7.2.3几种特殊方式求和 256

7.2.4条件计数 258

7.2.5筛选状态下的SUBTOTAL函数 260

7.2.6不重复数据统计 262

7.2.7频率分布 263

7.2.8不重复排名与中国式排名 265

7.2.9线性插值法的应用 265

7.2.10舍入函数的应用 267

7.2.11上下限函数的应用 270

第8章 查找与引用函数 272

8.1查找与引用函数概述 272

8.2 VLOOKUP函数应用案例 273

8.2.1按列查找 273

8.2.2逆向查找 273

8.2.3多条件查找 274

8.2.4一对多查找 275

8.2.5模糊查找 276

8.2.6巧用VLOOKUP核对银行账 277

8.2.7 VLOOKUP的常见错误类型及解决方法 279

8.3 LOOKUP函数应用案例 280

8.3.1 LOOKUP向量和数组查找基础 280

8.3.2数组型查找 281

8.3.3分组查找 282

8.3.4单一条件查找 283

8.3.5多条件查找 284

8.3.6在合并单元格内查找 285

8.4 INDEX函数 286

8.4.1 INDEX函数的基本用法 286

8.4.2 INDEX函数的引用形式 286

8.4.3执行双向查找 287

8.4.4创建动态区域 288

8.5 OFFSET函数 291

8.5.1 OFFSET函数的基本用法 291

8.5.2在二维区域内查找 291

8.5.3储值卡余额的计算及查询 292

8.5.4 OFFSET与动态数据验证 294

8.5.5按关键字设置智能记忆式下拉菜单 295

8.6 INDIRECT函数 296

8.6.1认识INDIRECT函数 296

8.6.2汇总各分表数据 298

8.6.3查询特殊分表数据 300

8.6.4查询区域中的倒数第二个数 301

8.6.5按最近值查询 302

8.7 HYPERLINK函数 303

8.7.1建立超链接并高亮显示数据记录 303

8.7.2编制工作表目录 304

8.7.3取得硬盘指定目录下的文件名 305

第9章 日期与时间函数 308

9.1认识日期与时间的本质 308

9.2返回与月份相关的数据 309

9.3与星期、工作日有关的函数 310

9.4利用假日函数巧解票据缺失问题 311

9.5隐藏DATEDIF函数 312

9.6时间函数计算应用实例 314

第10章 文本函数 316

10.1常见的文本函数 316

10.2文本函数基础 317

10.3两组文本函数用法的比较 318

10.4分离中英文 319

10.5根据多个关键字确定结果 320

10.6从路径中提取文件名 321

10.7付款模板设计 322

10.8从文本中分离物料代码 323

10.9“文本函数之王”——TEXT函数 325

第11章 信息函数 328

11.1常见的信息函数 328

11.2检验数据类型函数 328

11.3 CELL函数及其应用 329

11.3.1 CELL函数概述 329

11.3.2 CELL函数应用 330

11.4根据关键字设置智能模糊查询 331

第12章 数组公式 334

12.1数组公式的概念与特性 334

12.2单一单元格数组公式 337

12.2.1单一单元格数组公式的两个实例 337

12.2.2 MMULT函数应用 339

12.2.3应收账款余额账龄的计算 341

12.2.4一对多查询经典应用 342

12.3多单元格数组公式 344

12.3.1条件求和 344

12.3.2按年龄段统计辞职人数的频率分布 345

12.3.3预测未来值 346

第13章 函数与公式的综合应用 348

13.1循环引用与迭代计算 348

13.2随机函数的应用 350

13.3规划求解的应用 351

13.4直线法折旧计算表 354

13.5先进先出法计算库存物料的账龄 356

第14章 Excel图表制作技巧 359

14.1 Excel图表制作基础 359

14.1.1认识Excel图表要素 359

14.1.2 Excel图表制作原则 360

14.1.3 Excel数据关系与图表选择 361

14.1.4图表制作的注意事项 362

14.2 Excel图表制作技巧系列 364

14.2.1快速向图表追加数据系列 364

14.2.2让折线图从纵坐标轴开始 365

14.2.3设置图表互补色 366

14.2.4图表配色原则和取色方法 366

14.2.5自动绘制参考线 367

14.2.6将数据错行与空行组织 368

14.2.7使用涨/跌柱线显示预算与实际差异 370

14.2.8添加误差线 372

14.2.9绘图前的数据排序 375

14.2.10用颜色区分业绩高低的柱形图 377

14.2.11居于条形图之间的分类轴标签 379

第15章 专业图表制作 382

15.1圆环图 382

15.2弧线对比图 384

15.3气泡图 386

15.4矩阵图 389

15.5平板图 392

15.6不等宽柱形图 394

15.7滑珠图 397

15.8不等距纵坐标图形 400

15.9百分比堆积柱形图 404

15.10利用数据验证创建动态图形 407

15.11本量利分析动态图 409

15.12利用名称与控件制作动态图形 413

15.13 Power View基础 417

15.14 Power View BI图 420

第16章 VBA在数据处理中的应用 424

16.1制作目录链接报表 424

16.2利用循环分解连续发票号码 425

16.3 VBA自定义函数 427

16.4合并工作表 430

16.5合并工作簿 431

16.6将工作簿中的多张工作表批量复制到总表 434

16.7简易报价单系统 436

附录A用数据标准化思维规范数据 438