《Excel 2016数据处理与分析实战秘籍》

内容简介

《Excel2016数据处理与分析实战秘籍》围绕Excel数据处理和分析的六项关键技术，聚焦于实战技巧，专注于讲透讲全，使读者醍醐灌顶，豁然开朗，了然于胸。在详解、细讲、深研的基础上，辐射了自定义格式、表格、函数、名称、去重、分列、宏、VBA等功能，使《Excel2016数据处理与分析实战秘籍》成为Excel数据处理和分析六大基本功能的工具书，更成为拓展、延伸学习Excel的立交桥。《Excel2016数据处理与分析实战秘籍》展示的数据分析技巧精彩纷呈，读者可从书中的实战案例中快速掌握知识点，并应用到实际工作中，显著提高工作效率。

《Excel2016数据处理与分析实战秘籍》内容丰富实用，操作详细具体，既适合Excel2016初、中级读者阅读，也适合公司或企业中从事文秘、管理、市场、营销、财务等工作的办公人员学习，同时还可作为高等院校和社会培训机构的相关教材。

精彩书摘

2在“格式样式”下拉列表中选择“数据条”选项。

3在“条形图方向”下拉列表中选择“从右到左”选项，其他保持默认设置。

4单击“确定”按钮，条形图就反向了。

操作过程及效果如图5-20所示。

1234

图5-20通过高级格式化设置条形图反向的过程及效果

按“Ctrl+Z”组合键撤销本例条形图反向的操作。

5.2.3.4设置负值和坐标轴

在“数据条”工作表，“名次升降”列有负值，数据条默认负值与正值以0值为坐标轴

（分界线），方向相反；绝对值越大，数据条越长；坐标轴基于负值显示在可变位置（意思

是如果负值变化了，坐标轴的位置就会随之变动）。其实，可以设置坐标轴的位置居中，或

者设置无坐标轴，即正、负值在同一个方向上。

如果要将C3:C8区域数据条的坐标轴居中，应该如何设置呢？事先选择C3:C8区域，按

照前面介绍的方法打开“新建格式规则”对话框，然后进行如下操作：

1在“选择规则类型”列表框中选择“基于各自值设置所有单元格的格式”选项。

2在“格式样式”下拉列表中选择“数据条”选项，其他保持默认设置。

3单击“负值和坐标轴”按钮。

4在打开的“负值和坐标轴设置”对话框中，在“坐标轴设置”组中的“选择单元格

中的坐标轴位置可更改负值条形图的外观”三个选项中进行设置。默认选择“自动（基于负

值显示在可变位置）”单选按钮。如果坐标轴要居中，请选择“单元格中点值”单选按钮；

5.3.1为单元格值设置单元格格式

5.3.1.1通过高级格式化设置单元格值的格式

1.文本值的比较

文本有读音，有拼音的首字母，而字母在Excel中有严格的排序，相当于具有一种

“值”。所以在Excel中，可以对文本进行先、后之类的比较，这种先、后次序用数学语言

来表达就是大于、小于、大于或等于、小于或等于等逻辑关系。

在“数据”工作表中，如果要通过高级格式化标识“姓名”列按音序排在“卢光”之后

的名字，应该如何操作呢？

事先选择A3:A8区域，按照前面介绍的方法打开“新建格式规则”对话框，然后进行如

下操作：

1在“选择规则类型”列表框中选择“只为满足以下内容的单元格设置格式”选项。

2在“只为满足以下条件的单元格设置格式”下的第1个下拉列表中选择“单元格值”

选项。下拉列表中包括“单元格值”“特定文本”“发生日期”“空值”“无空值”“错

误”“无错误”等7个选项。

3在第2个下拉列表中选择“大于”选项。下拉列表中包括“介于”“未介于”“等

于”“不等于”“大于”“小于”“大于或等于”“小于或等于”等8个选项。

4在右侧框中输入“卢光”。

前言/序言

大家知道，Excel是微软公司办公套装软件MicrosoftOffice的一个重要组成部分，可以处理各种数据、统计分析和辅助决策，广泛地应用于统计、财经、金融、管理等众多领域。自1987年第一款适用于Windows系统的产品诞生以来，Excel即将迎来而立之年，最新版本为Excel2016，又称为Excel15。

Excel是办公自动化中非常重要的一款软件。在数据统计分析领域，Excel独占鳌头，是当之无愧的世界领导者，至今还没有一款软件在普及程度、应用水平等综合性能上能撼动Excel的领导地位。可以这么说，Excel博大精深，是一座取之不尽的金矿，也是一部值得拥有的数据统计分析领域的“葵花宝典”。

Excel2000刚出来的时候，由于工作需要，笔者开始接触Excel。后来，随着Excel版本的更新和使用的增加，逐渐喜欢上Excel。经过不断学习和深入研究，到现在，算得上略有心得、不离不弃。

在工作中，笔者以熟练掌握和应用Excel为己任，近年来加大了研究力度，4项市、区级相关课题成功结题，在公开期刊上发表论文10余篇。平时，笔者不时观察别人如何使用Excel，想从中偷学一招半式。但很多时候发现，本来直截了当的操作，由于操作者对一些技巧不了解或不熟悉，走了一些弯路，枉费了不少精力。于是，笔者便萌生了写一本专著的想法。《Excel 2016数据处理与分析实战秘籍》既适合Excel初、中级用户学习，又适合高级用户借鉴；既适合个人自学，也适合作为高中、高职和办公自动化培训机构的选修或培训教材。

应该说，Excel用户群在应用水平上呈金字塔分布，多数用户居于塔基和塔身部位。俗话说，“磨刀不误砍柴工”。如果初、中级用户能够挤出一些时间学习Excel，那么，通过提高工作效率而节省下来的时间可以远远多于学习时间，何乐而不为呢？俗话又说，“基础不牢，地动山摇”，基础的重要性是显而易见的。基本知识和基本能力是学习任何一门科学的基础。熟练的Excel基本功是修炼Excel这部“武学秘籍”的下盘功夫。熟练的Excel技巧可以破解一些疑难困境，不至于临阵磨枪。夯实了Excel基础，用户的水平何愁不能得到质的提升呢？

在日常工作中，Excel数据填充、数据验证、数据排序、数据筛选、条件格式和数据透

II

视表，是Excel大众群体和初、中级用户最常使用的、最容易掌握的，能够快速、显著提高

数据处理与分析工作效率的六大技术，堪称Excel数据处理和分析的六项关键技术，也可以

说是Excel数据处理和分析的“六脉神剑”，可以匹配其实际需求。运用Excel处理和分析数

据，要实现从新手到高手的华丽转身，必须掌握这六项关键技术。当然，这六项关键技术并

非截然分离，它们之间互相补充，提升了Excel的综合实力。从总体上来说，前两者侧重数

据录入的快速、规范，后四者侧重数据的处理、分析，多数技巧属于初、中级技巧。Excel

用户牢固掌握了这六大技术，无异于占据高效办公和深入学习的支点，可以举重若轻，四两

拨千斤。退，可以坐享其成数年；进，就有了腾飞的发射平台。学习这六大技术，投入少、

容易学、周期短、见效快、终身受益。

基于以上缘由，《Excel 2016数据处理与分析实战秘籍》共分为6章。

第1章介绍数据填充功能，主要包括使用鼠标拖放或双击填充、自定义序列填充、使用

填充命令填充、快速填充、定位填充等内容。

第2章介绍数据验证功能，包括“数据验证”对话框设置，非序列设置，序列设置，二

级序列实例、三级序列实例、五级序列实例，数据验证的查找、更改、清除、扩展等内容。

第3章介绍数据排序功能，包括简单排序、指定条件排序、自定义列表排序，排序规则

和问题，排序功能的拓展应用，在数据透视表中排序等内容。

第4章介绍数据筛选功能，包括自动筛选、高级筛选、使用SUBTOTAL函数处理筛选结

果、在数据透视表中筛选、使用记录单查询、在外部数据库中筛选等内容。

第5章介绍条件格式功能，包括基于各自值设置所有单元格的格式，只为包含特定内容

的单元格设置格式，使用公式确定要设置格式的单元格，使用“快速分析”以应用条件格

式，条件格式的复制、查找、清除和管理等内容。

第6章介绍数据透视表功能，包括创建简单的、多条件汇总的或多重合并区域的几种数

据透视表，数据透视表的多样化设置和布局，数据透视表的值汇总和显示方式，为数据透视

表添加新的计算字段和计算项、进行多重合并计算、使用数据透视表函数进行计算，数据透

视表对象的操作等内容。

《Excel 2016数据处理与分析实战秘籍》围绕Excel数据处理和分析的六项关键技术，聚焦于实战技巧，专注于讲透讲全，

使读者醍醐灌顶，豁然开朗，了然于胸。在详解、细讲、深研的基础上，辐射了自定义格

式、表格、函数、名称、去重、分列、宏、VBA等功能，使《Excel 2016数据处理与分析实战秘籍》成为Excel数据处理和分析

六大基本功能的工具书，更成为拓展、延伸学习Excel的立交桥。Excel2016比起以往版本，

在界面、功能、命令、对话框等方面均有较大的变化，因此初学者未必有接触或接触不多。

有些用户可能直接从Excel2003跳升到Excel2016，跨度很大。基于此，对几大功能的介绍是

从零开始的。

《Excel 2016数据处理与分析实战秘籍》具有如下特色。

●

由浅入深。介绍时，从入门的基础知识开始，循序渐进，逐步引申、拓展，力求遵

Excel2016数据处理与分析实战秘籍三校文前.indd2

2018-4-239:46:49

III

循知识的内在规律，把握知识的逻辑性，做到脉络清晰、步骤简明，帮助读者构建

一种“知识树”。

●

深入浅出。“一图胜千言”，力求图文并茂，以图代文，多图合一，标注清楚，减

少文字，通俗易懂，强化操作步骤的流畅度，帮助读者快速掌握重点，突破难点

，

增强技能。对需要关注的地方用“注意”二字加以提醒，对比较难懂的函数公式进行

解析。为了知其所以然，理论知识少不了，但尽量做到文风活泼，不枯燥生涩。

●

一例多用。把多数章节的实例安排在一个工作簿的若干个工作表中，尽量做到一例

多用，一题多解，务使读者举一反三、触类旁通、融会贯通，多角度、多层面思考

问题。

●

案例驱动。围绕实际工作中的问题，以问题和案例为导向，引发读者思考。既提出

问题，又分析问题，并解决问题，在实战中形成解决问题的策略和方法，提高实战

技能与本领。

阅读时，读者可以根据自己的实际情况进行。可以围绕一个专题深耕细作，也可以飞机

播种，普种广收；可以逐章逐节阅读，也可以跳跃式阅读，还可以把《Excel 2016数据处理与分析实战秘籍》当作一本工具书，

需要时按图索骥，对症下药。当然，通读后再精读，是最好的阅读方式，至少分章阅读。毕

竟有一些技巧有前后的知识衔接，一些函数在前面作了介绍，后面就没有再作重复介绍了。

在同一章，一些操作步骤在前面作了介绍，后面就可能省略了。

阅读当中，如遇困难，可暂时搁置。随着功力的提升，回过头来，一些问题会迎刃而

解。特别是函数公式，功力不够，犹如啃硬骨头，需要坐禅打坐，苦修内力。函数和VBA功

力达到九重，可以创造一些匪夷所思的用法，Excel的应用出神入化。

学习时，还要注意归纳思维方法。一些复杂、精妙的用法，很可能就是一些简单技巧

的新颖组合。技巧的排列组合，很有可能化腐朽为神奇。当遇到一些问题难以解决，似乎到

“山穷水尽疑无路”的境地时，不妨换一个角度，换一种思路，也许“柳暗花明又一村”。

遇到问题时，多思考、多实践，也许你会发现Excel背后的“宝藏”远超想象。如能结合自

身工作领域和实际情况，学以致用，推陈出新，就是笔者莫大的欣慰了。

《Excel 2016数据处理与分析实战秘籍》实例基于Excel2016，但真正专属于Excel2016的技巧较少。例如快速填充，多数

技巧可以在2007、2010、2013等版本上运行。用户如果没有安装Excel2016，也可以学习本

书，不少技巧能够在Excel2007及其以上版本中使用，只是操作步骤可能有所不同。但为了

学习和工作的方便，如果计算机硬件配置许可，建议读者尽量使用高版本的Excel。高版本

的Excel功能更完善、强大，性能更优秀、卓越。

为减少文件个数，减少对文件的打开、关闭操作，多数章节的实例

被安排在一个工作簿的若干个工作表中。多数实例选自笔者熟悉的教育

管理领域。很多方法，用处不同，但原理相同，学习时要注意抽象成方

法和思路。《Excel 2016数据处理与分析实战秘籍》实例，读者可以扫描二维码下载。

IVExcel2016数据处理与分析

除了封面署名人员之外，参与《Excel 2016数据处理与分析实战秘籍》编写的人员还有李海庆、王咏梅、康显丽、王黎、汤

莉、倪宝童、赵俊昌、方宁、郭晓俊、杨宁宁、王健、连彩霞、丁国庆、牛红惠、石磊、王

慧、李卫平、张丽莉、王丹花、王超英、王新伟等。在撰写《Excel 2016数据处理与分析实战秘籍》的过程中难免会有疏漏，欢

迎读者与我们联系，帮助我们改正提高。

著者