作者：耿勇
出版社：电子工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
Excel数据处理与分析实战宝典(第2版)试读：
前言
本书的组织架构
本书以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。
耿勇
2019年1月读者服务
轻松注册成为博文视点社区用户（www.broadview.com.cn），扫码直达本书页面。
· 下载资源：本书提供示例代码及资源文件，可在 下载资源 处下载。
· 提交勘误：您对书中内容的修改意见可在 提交勘误 处提交，若被采纳，将获赠博文视点社区积分（在您购买电子书时，积分可用来抵扣相应金额）。
· 交流互动：在页面下方 读者评论 处留下您的疑问或观点，与我们和其他读者一同学习交流。
页面入口：http：//www.broadview.com.cn/35459第1章 数据处理基本技巧1.1 认识Excel表格
全球知名咨询公司麦肯锡称：“数据已经渗透到当今每一个行业和业务职能领域，并成为重要的生产因素。人们对海量数据的挖掘和运用，预示着新一轮生产率增长和消费者盈余浪潮的到来。”数据作为与人、财、物比肩的资源，正在成为组织的财富和创新的基础，数据分析能力正在成为组织的核心竞争力。
Excel 是美国微软公司推出的办公自动化系列 Office 应用软件中用于表格数据处理的应用软件，也是应用最广泛的电子表格软件。Excel是一个分析数据的极佳工具，并且它经常用于处理数据、汇总数据，并以表格和图形的形式展现数据信息。Excel数据处理方式已成为通用标准。可以说，Excel的魅力就在于它多种多样的数据处理方式。
具体地讲，Excel表格有以下几个具体用途。
★ 数据运算：可以通过各种基本编辑命令、排序、筛选、分类汇总、数据透视表、公式与函数等处理数据。
★ 数据存储：可将数据保存在Excel表格中供用户使用。
★ 创建各种图形：用各种专业化的图形直观地展现数据特点，即数据图表化。
★ 具有较强的数据交互功能，可以导入外部数据、导出数据。
★ 自动处理各种复杂任务：通过Excel VBA功能，可以高效地执行重复乏味的任务。
用Excel进行数据处理的目的主要有以下两方面。
★ 制作报表、图表。
★ 利用Excel进行数据分析，帮助管理者进行判断和决策。1.1.1 Excel表格中的对象
Excel表格从形状上看是一个矩形的数据区域，该区域由若干个单元格组成，单元格是由行和列交叉决定的，如同坐标系中横坐标、纵坐标交叉决定一个坐标点，单元格是 Excel 表格基本的构成要素。因此，在学习Excel知识前，有必要先了解一下Excel表格中的对象，其对象分类如图1-1所示。图1-1
我们接触得最多的是单元格中的对象，单元格中对象的数据类型可分为数值（日期和时间也属于数值）、文本、公式3种。
★ 数值：就是可以进行数学运算的数据，例如，价格、数量、百分比。其中日期与时间值在Excel中被存为数值形式，它拥有数值所具备的一切运算功能，属于一种特殊的数值。
★ 文本：就是文字信息，它不能参与数学运算。常见的文本如姓名、性别、身份证号码等。
★ 公式：就是对某个计算方法的描述，是为了解决某个计算问题而设置的计算式。公式主要分为普通公式、数组公式和命名公式（即定义名称的公式）。Excel公式通过计算式来返回值，Excel常见的返回值有数字、文本、逻辑值和错误值。
因此，从Excel数据类型来看，可以分为4种：文本、数值、逻辑值、错误值。在默认情况下，文本是左对齐的，数值是右对齐的，逻辑值和错误值是居中对齐的。
绘图层中的对象主要包括图、图表（图主要指的是在表格中插入的图片、联机图片、形状、SmartArt和屏幕截图等，图表则是依据表格单元格中的数据制作的图形），以及按钮和其他对象等。这些对象不在单元格中，而驻留在工作表格的绘图层中（绘图层是每张工作表中不可见的层）。1.1.2 认识Excel超级表
绝大多数 Excel 数据处理者都将具有一定行数和列数构成的单元格数据区域看成表，从严格意义上讲，这样的区域只能被称为数据区域，不能被称为“表”，但这种数据区域可快速地创建成表。选择区域内的任意单元格，单击“插入”选项卡下“表格”分组中的“表格”按钮，Excel会自动弹出“创建表”对话框，如图1-2所示，完成上述操作后，数据区域会被转换为表格。图1-2
此时可以看到原来的 Excel 数据区域发生了一些变化：表格标题变成了加粗字体，每行数据的背景色变成了深浅相间的样式，这就是Excel“超级表”（又称智能表）。
提示 数据区域中如果包含空白行或空白列，Excel 选择的区域会出现错误，这时可单击“设计”选项卡下的“转换为区域”或者撤销创建表，删除所有的空白行或列，然后进行创建表的操作。
超级表具有如下特点。
★ 选择表中的任意单元格，在功能区中将出现“表格工具”→“设计”选项卡。
★ 可直接在表格中创建切片器进行灵活的数据筛选。
★ 在表格列中定义公式会自动扩展至该列的其他单元格中。
★ 如果在表格右侧加入新列或者在表格下方的行中添加新的数据记录，表格会自动扩充以包含新列或新行。
用于表格的切片器是Excel 2013的一项新功能，如图1-3所示，此前的切片器在Excel 2010中只能用于数据透视表中。图1-3
基于超级表制作的数据透视表（包括超级数据透视表）等，会随着表格数据行或者列的增加（或者减少）而随之自动扩展（或压缩）数据引用范围。只需单击“刷新“按钮，即可更新数据。1.2 数据验证的强大功能“数据验证”在Excel 2010及以下版本中被称为“数据有效性”，它允许用户设置一些规则，用于规定单元格中输入的数据。例如，某科目考试成绩满分为100分，如果输入大于100或者小于0的非法数据，会自动弹出如图1-4所示的警告错误信息。图1-4
使用Excel设置数据验证的验证条件类型可在“数据验证”对话框中“设置”选项卡下的“允许”下拉列表框查看到，主要类型有任何值、整数、小数、列表、日期、时间、文本长度、自定义。
在“数据验证”对话框中的“设置”选项卡下还有“忽略空值”和“对有同样设置的所有其他单元格应用这些更改”两个复选框。
★ 忽略空值：如果选择此复选框，则允许为空值。
★ 对有同样设置的所有其他单元格应用这些更改：如果选择此复选框，则所做的更改可应用于其他已设置数据验证的其他单元格。
提示 Excel“数据验证”功能存在的一个问题是，如果用户复制一个不具有“数据验证”功能的单元格，并且将其粘贴到已经设置“数据验证”的单元格区域中，则会破坏已设置“数据验证”的单元格区域的“数据验证”功能。“数据验证”最常见的用途如下。
★ 可以对单元格的输入数据进行条件限制，防止无效数据的录入，并圈释无效数据。
★ 在单元格中创建下拉列表菜单，方便用户选择输入。
★ 通过下拉列表菜单可以实现数据源动态选择，用于制作动态图形。1.2.1 数据验证应用之一：规范数据输入
公司部门一般有“财务部、采购部、销售部、生产部、行政部、质量部、技术部、工程部、人事部”等名称，如果部门名称不统一，会给数据处理和分析带来诸多不便。现对表格中的“部门”所在列设置“数据验证”，步骤如下。
选择E2单元格；单击“数据”选项卡下的“数据验证”，弹出“数据验证”对话框。在“设置”选项卡的“允许”下拉列表中选择“序列”，在“来源”编辑框中输入“财务部，采购部，销售部，生产部，行政部，质量部，技术部，工程部，人事部”，完成后单击“确定”按钮，关闭“数据验证”对话框，如图1-5所示。
如果要使“数据验证”具有更强大的防错功能，还可以在“数据验证”对话框中的“出错警告”选项卡下“错误信息”处输入“请输入正确的部门！”，单击“确定”按钮，关闭“数据验证”对话框，如图1-6所示。当输入数据错误时，会自动阻止不正确的数据输入。图1-5图1-6
如果要将此单元格中的“数据验证”功能应用到此列其他单元格中，可以直接拖动复制 E2单元格到其他单元格区域中。也可以使用这种方法：“复制”此单元格，弹出“选择性粘贴”对话框，选择“粘贴”选项卡下的“验证”。
提示 各部门名称之间的逗号必须以英文半角的逗号隔开，而不能用全角的逗号，否则Excel会将逗号前后的值作为一个选项。
设置“数据验证”时也可在“来源”编辑框中直接选择事先在某列中输入的部门系列单元格区域，如图1-7所示。但此列不能删除，否则已经设置的“数据验证”会失去。图1-71.2.2 数据验证应用之二：制作二级下拉菜单
在如图1-8所示的E列和F列设置“数据验证”，以达到在E列单元格选择相应省份时，在F列单元格可以选择所在省份对应城市的目的。图1-8
STEP 01 选中 A1 单元格，单击“公式”选项卡下的“定义名称”，弹出“新建名称”对话框，“名称”自动默认为首个单元格中的文本“江苏省”，在“引用位置”处点选红色箭头，选择A2：A9单元格区域，单击“确定”按钮，关闭“新建名称”对话框，如图1-9所示。用同样的方法设置“辽宁省”和“湖北省”名称。图1-9
STEP 02 在E2单元格设置“省份”的“数据验证”功能，依照1.2.1节的方法设置，如图1-10所示。
STEP 03 在F2单元格设置“城市”的“数据验证”功能，在“数据验证”对话框的“来源”编辑框中输入=INDIRECT（$E2），如图1-11所示，单击“确定”按钮，关闭“数据验证”对话框。将E2、F2单元格的“数据验证”应用于同列其他单元格。图1-10图1-11
在 E2 单元格中随意选择“省份”，然后可在 F2 单元格的下拉列表框中选择对应的“城市”名称，这样就实现了二级下拉菜单的制作。
提示 作为数据验证第一级的名称，在数据源区域第一行的名称不能是
★ 阿拉伯数字，例如，“二”不能写作“2”；
★ 不能以R或/开头。1.2.3 名称管理器突破数据验证的限制
数据验证设置时要求，序列的源数据区必须是单行或单列的。如果选择多列区域，则会弹出如图1-12所示的警告窗口。图1-12
如何使多列数据出现在序列列表中呢？现利用名称管理器定义名称以突破数据验证限制，步骤如下。
STEP 01 选中A1单元格，单击“公式”选项卡下的“定义名称”，弹出“新建名称”对话框，“名称”自动默认为首个单元格中的文本“部门”，在“引用位置”处点选红色箭头，选择 A2：A7单元格区域，单击“确定”按钮，关闭“新建名称”对话框，如图1-13所示。图1-13
STEP 02 设置数据验证，来源区域设置为该名称“部门”，如图1-14所示。图1-14
STEP 03 在“名称管理器”对话框中，重新编辑“名称”的数据区，使之包括多行或多列，这里选择A2：B7单元格区域，然后单击“关闭”按钮，如图1-15所示。图1-15
接下来在 C2 单元格中，我们会发现现在数据验证下拉列表中已经包含多个数据，如图 1-16所示。图1-161.2.4 数据验证圈释无效数据
使用Excel数据验证功能可以圈出无效的数据，如图1-17所示，假设这是某班级的成绩，成绩的范围介于0～120分之间。
选定 B2：D16 这组数据，单击“数据”选项卡下的“数据验证”按钮，在“数据验证”对话框“设置”选项卡中的“验证条件”下设置成绩的范围：0～120，如图1-18所示。图1-17图1-18
回到“数据”选项卡中，单击“数据验证”按钮下的“圈释无效数据”，可以看到无效的数据都被椭圆圈出来了，如图1-19所示，据此可以更正数据输入。图1-191.3 神奇的选择性粘贴“选择性粘贴”是Excel的强大功能之一。
Excel选择性粘贴的对话框如图1-20所示，我们可以把它划成4个区域，即“粘贴”“运算”“特殊处理”“按钮区域”。其中，粘贴方式、运算方式、特殊处理设置相互之间可以同时使用。
★“全部”：包括内容和格式等，其效果等于直接粘贴。
★“公式”：只粘贴文本和公式，不粘贴字体、格式（对齐、文字方向、数字格式、底纹等）、边框、注释、内容校验等。
★“数值”：只粘贴文本，单元格的内容是计算公式则只粘贴计算结果，这两项不改变目标单元格的格式。
★“格式”：仅粘贴原单元格格式，但不能粘贴单元格的有效性，粘贴格式包括字体、对齐、文字方向、边框、底纹等，不改变目标单元格的文字内容（该功能相当于格式刷）。
★“批注”：把原单元格的批注内容复制过来，不改变目标单元格的内容和格式。
★“验证”：将复制单元格的数据有效性规则粘贴到粘贴区域，只粘贴有效性验证内容，其他的保持不变。
★“边框除外”：粘贴除边框外的所有内容和格式，保持目标单元格和原单元格相同的内容和格式。
★“列宽”：将某个列或列的区域粘贴到另一个列或列的区域，使目标单元格和原单元格拥有同样的列宽，不改变内容和格式。
★“公式和数字格式”：仅从选中的单元格粘贴公式和所有的数字格式选项。
★“值和数字格式”：仅从选中的单元格粘贴值和所有的数字格式选项。
★“转置”：可以将行的内容转换为列向排列，将列的内容转换为行向排列。图1-20
下面用具体案例来介绍“选择性粘贴”功能在数据处理中的奇妙应用。1.3.1 数据位置不变实现万元来回切换
在日常工作中，尤其是财务人员在数据处理时并不需要精确到元，往往需要以“万元”的形式对数据进行处理（统计上往往要求统计报表采取“千元”的方式填报）。
现需要直接将如图1-21所示的C列数据变换成以“万元”的形式表现出来，不能借助辅助列，而且要求能随时切换成原来的以“元”表现的数据。图1-21
处理方法如下：在除数据区域外的任意一个空白单元格中输入 10000，选择该单元格进行复制之后选择图1-21中的C2：C7单元格区域，单击鼠标右键，选择“选择性粘贴”。随后在“选择性粘贴”对话框中选择“运算”下的“除”单选项，单击“确定”按钮，关闭“选择性粘贴”对话框，“万元”数据即可转换完成，如图1-22所示。图1-22
如果要恢复到未转换成万元之前的数据状态，用户可按照前述转换万元的方法操作，唯一不同之处是，在“选择性粘贴”对话框中选择“运算”下的“乘”（即原来“除”的逆运算）。1.3.2 选择性粘贴清除超链接
有时我们会遇到一些含有超链接的表格，特别是从网页上下载下来的表格很多都有超链接。超链接过多往往会造成表格运行很慢的情况，这种超链接对数据处理者来说是无用的，所以必须清除。如图1-23所示，就是一个有不少超链接的表格。图1-23
处理方法如下：选择除数据区域外的任意一个空白单元格进行复制，其余步骤与1.2.1节的步骤相同，处理方式也可以选择“运算”下的“加”或者“减”。1.3.3 选择性粘贴转换数据类型
如图1-24所示，单元格区域内的数据都是文本型数字，这种数字左上角都有绿色小三角标记，由于文本型数字不能计算，因此需要将其转换为数值型数字，具体操作如下。
STEP 01 复制一个空白单元格，选中如图1-24所示的A1：E6单元格区域，单击鼠标右键，选择“选择性粘贴”，弹出“选择性粘贴”对话框。
STEP 02 在“选择性粘贴”对话框中选择“运算”下的“加”单选项，单击“确定”按钮，关闭“选择性粘贴”对话框，如图1-24所示。图1-241.3.4 跳过空单元格
在“选择性粘贴”对话框中勾选“跳过空单元”复选框，可以防止复制/粘贴数据覆盖原来非空白单元格中的内容。
如图1-25所示，将C3：C10单元格区域中的内容复制并粘贴到A1：A10单元格区域的空白单元格中，步骤如下。
选择C3：C10连续区域中的单元格进行复制，鼠标指针放在A2空白单元格中，然后单击鼠标右键，选择“选择性粘贴”，在弹出的“选择性粘贴”对话框中勾选“跳过空单元”复选框，单击“确定”按钮，关闭“选择性粘贴”对话框。图1-25
提示 必须选择 C3：C10 连续区域中的单元格进行复制，不能只选择有数据的单元格进行复制，否则会覆盖掉 A 列中原本有数据的单元格，即所选择复制的区域大小必须和粘贴区域大小一致，并且复制的数据区域中的间隔必须和欲粘贴区域中的间隔相同。1.4 查找和替换“查找”和“替换”功能在“开始”选项卡中的“查找和选择”命令下，也可以使用 Ctrl+F组合键与Ctrl+H组合键实现同样的功能。“查找”和“替换”功能在数据处理中的应用非常广泛，巧妙地利用这个功能往往可以起到事半功倍的效果。“查找”的默认设置是模糊查找。如果要实现精确查找，需要利用“查找和替换”对话框中的“选项”按钮。勾选“选项”中的“单元格匹配”复选框可实现精确查找，去掉勾选则是模糊查找，如图1-26所示。图1-26
图1-26中的“格式”按钮具有如下功能：按字体进行查找、按单元格边框粗细进行查找、按单元格文本对齐方式进行查找、按单元格填充色进行查找、按文本字形进行查找、按文本字体进行查找，如图1-27所示。图1-27
下面以几个案例来详细介绍“查找”和“替换”功能。1.4.1 批量清除单元格中的空格或换行符
我们经常从网页或者其他系统将数据导出到 Excel 表格中，这时往往包含大量的空格、换行符或者其他不可见的非法字符，在处理数据时明明存在该数据对象，却无法找到对应的数据。
如图1-28所示，根据“物料代码”查找“数量”时，由于物料代码列中的每个物料代码列后都包含空格，因此导致查询数据时出现＃
试读结束[说明：试读内容隐藏了图片]