科技>计算机>办公软件
Excel数据处理与分析实战宝典(第2版)

Excel数据处理与分析实战宝典(第2版)"

作者:耿勇
ISBN:9787121354595
定价:¥89.0
字数:660千字
页数:464
出版时间:2018-12
开本:16开
版次:01-01
装帧:
出版社:电子工业出版社
简介

随着数据时代的来临,Excel 的数据处理与分析能力对职场人士而言是一项必备的职业技能,数据处理与分析能力也正在成为组织的核心竞争力。本书并没有对Excel 的各个功能进行全面介绍,而是侧重于Excel 数据处理与分析在实际工作中的应用,书中精选了众多的技巧和经典案例,并辅以深入浅出的解析,力求让更多希望深入掌握Excel 数据处理与分析技巧的读者取得长足的进步。 本书从实际工作应用出发,重点介绍了数据处理的重要技巧及函数的应用,特别是数据清理技术的应用能让读者对数据去伪存真,掌握数据主动权,全面掌控数据;Excel 中SQL、数据透视表、Power Query、Power Pivot、VBA 的应用重在挖掘隐藏的数据价值,轻松整合海量数据;各种图表类型的制作技巧及Power View 的应用可展现数据可视化效果,让数据说话。 本书内容丰富,图文并茂,既适合Excel 中高级用户阅读,也可作为Excel 初学者的参考用书。通过对本书的学习,读者可以学到数据处理与分析的科学工作方法,快速掌握各种Excel 数据处理与分析技巧。 本书附赠内容包括超大容量的教学视频及典型函数与公式案例。

前言

推荐序 随着信息技术的迅猛发展与应用,数据呈现出海量、多样与快速三大特点,并已渗透到当今各行业和业务职能领域,成为重要的生产因素,深刻影响到技术、商业、法律、社会规范等人类生活的方方面面。数据作为与人、财、物比肩的资源,正在成为组织的财富和创新的基础,数据分析能力正在成为组织的核心竞争力。 既然数据在当今社会中如此重要,那么我们如何获取、处理和分析数据呢?Excel 已经是广大用户经常使用的一个数据处理和分析工具,也是应用最广泛的电子表格处理软件之一。Excel 从1997 版、2003 版到2016 版,一直处于不断完善和改进中,实用易学。随着大数据浪潮的来临,Excel 的商务智能化、可视化效果、数据交互功能越来越受到广大用户的喜爱。然而,不少用户存在着“Excel 不过如此”和“Excel 只是一个数据统计表格”等肤浅认识,在Excel 的使用上也是浅尝辄止,没有进一步领略Excel 的数据处理与分析能力,当然更谈不上如何利用Excel 的强大功能来提高工作效率和管理水平了。 本书并没有对Excel 的各个功能进行全面介绍,只是就Excel 中的重要知识点结合实例进行重点详述和拓展应用,其中也有Excel 2013 中新功能的一些说明和使用技巧。无论是要在Excel 中进行数据清理、导入/导出数据,还是利用Power 系列处理数据,或是在Excel 中创建图表,如何完成这些任务尽在本书的讲解中。本书主要结合公司实际环境下的应用实例来讲解各种数据处理与分析技术,注重点面结合,同时还注重逻辑思维的拓展,以便培养读者的数据观察与函数构建能力。学习完本书若干章节和附录中有关数据规范性的相关知识后,相信对读者Excel 数据应用水平的提升会有所裨益。 张学才 国家开发银行高级经济师 前言 本书的组织架构 本书以Excel 2013 为基准平台,兼顾早期版本的不同点,使新旧用户都能快速掌握Excel 应用技巧,积累Excel 应用知识。全书共16 章,另外还有一个附录,主要内容如下。 第1 章:数据处理基本技巧。本章通过实例形式阐述数据处理的各种基础技巧。 第2 章:数据专项处理技巧。本章包含条件格式、排序、筛选、汇总、合并计算、定义名称、分列、数据导入/导出等方面的内容,着重介绍了数据清理方法及SQL 语句在数据查询中的应用。 第3、4 章:数据透视表,包括数据透视表的基础应用、扩展、PowerPivot 与数据透视表、数据模型,以及Power Query 应用实例。 第5~12 章:Excel 公式与函数。这里主要通过实例的形式展示在Excel 中执行计算工作的公式与函数,以及重点函数的应用。 第13 章:函数与公式的综合应用。本章主要展示函数与公式在解决实际工作复杂问题中的应用。 第14、15 章:Excel 图表制作。这里通过一些实例介绍Excel 图表制作以及利用Power View制作BI 图。 第16 章:VBA 在数据处理中的应用。本章主要介绍几个常用的VBA 数据处理实例。 本书附录部分介绍如何用数据标准化思维规范数据和如何养成良好的数据处理习惯。 读者对象 本书面向的读者群包括Excel 中高级用户和从事Excel 开发的相关IT 人员。因此,读者在阅读本书前应具备使用Windows 操作系统且至少具有Excel 2007 的使用经验,并了解键盘和鼠标在Excel 中的一些使用方法,掌握Excel 的一些基本功能和常用命令按钮的操作方法。 致谢 本书主要由耿勇编写,参与编写的人员还有刘钰、俞丹、龚学雷和谭春成。感谢“魔术师中的笨小孩们”QQ 群的各位朋友、ExcelHome 在线培训教管团队,以及ExcelHome 网站站长周庆麟、知名版主祝洪忠老师和各位同事对本书的支持与帮助,他们分别是赵兰、张飞燕、朱楠、李敏、彭智勇、时坤、文雪儒、刘欣悦、王永州等,在此向这些最可爱的人表示由衷的感谢。 学习Excel 的方法 作为ExcelHome 论坛培训中心助教和公司Excel 培训讲师,我经常会面对这样的提问:“如何学习Excel?”“怎么样才能快速成为Excel 高手?”然而,这些问题并不好直接回答。在这里,我只想说:Excel 的学习是一个循序渐进的过程,没什么秘籍或绝招可以让我们在短期内成为高手。笔者认为,只有具备积极的心态、正确的方法并持之以恒地努力,才能学有所长。回首自己学习Excel 的经历,正应了“非学无以广才,非志无以成学”这句话。最初的胆怯心态并不可怕,最可怕的是心态浮躁、难以静下心来学习。面对数据处理与分析,我们不必浪费大把的时间去学习那些令人生畏的大型数据库软件或者各种程序设计语言。只要你花点时间,静下心来读一本Excel 的书,练一练Excel 数据处理技巧,相信对工作中遇到的绝大部分数据处理与分析问题都可游刃有余地解决。 学习Excel 的方式如下:阅读相关图书,用Office 自带的联机帮助,问身边的同事,用网络搜索解决问题等。其中在网络搜索时需要注意,问题表述要尽可能准确。在互联网时代,有关Excel数据处理应用的文章、动画、视频非常多,这些都是我们学习的资源。学习Excel,实践最重要,正可谓你听见了会忘记,你看见了就记住了,你做了就明白了。而这其中听—看—做就是学习Excel的最佳结合,在做中学,在学中做,多练习是最好的方法,只有多练,才能将其转化为自己所有。随着信息技术的飞速发展,Excel 数据处理技术也日新月异。从Excel 1997/Excel 2003 更新升级到Excel 2016,无论是用户界面还是其交互功能,Excel 的数据处理方式越来越人性化。因此,随着Excel 的升级,用户也应花点时间去了解和学习新版本Excel 的新功能,而不能故步自封、裹足不前。 为方便读者学习,本书还附赠超大容量的教学视频及典型函数与公式案例,其下载地址为http://www.broadview.com.cn/35459。

目录

第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

作者简介

编辑推荐

作者寄语

电子资料

www.luweidong.cn

下一个