问题来源

以下两列身份证号码，绝大部分是相同的，只有小部分数据不同，且两列的排序不同。现要求标识两列中不相同的数据。

关键操作

1、选中A2:A21区域，[开始]——[条件格式]——[新建规则]：

2、选择规则类型为[使用公式确定要设置格式的单元格]，并输入公式：=OR(EXACT(A2,$B$2:$B$21))=FALSE

3、选择上图右下角[格式]，在跳出的设置单元格格式对话框中选择[填充]，选择一种背景颜色，确定。

选中B2:B21,重复以上三个步骤，只不过输入公式改为：=OR(EXACT(B2,$A$2:$A$21))=FALSE。

最终标识出两列不同数据的结果如下图：

公式解释：

=OR(EXACT(A2,$B$2:$B$21))=FALSE：

其中EXACT(A2,$B$2:$B$21)，是比较A2单元格与$B$2:$B$21单元格区域的内容是否完全相同，共返回20个逻辑值，相同的返回TURE，不同的返回FLASE。

当A2与$B$2:$B$21单元格区域的内容完全不相同时，满足OR(EXACT(A2,$B$2:$B$21))=FALSE，执行单元格格式设置。

以2003及其以下版本为例：

1、生成散点图时，不要选择无数据点的散点图，可选“散点图”、“平滑线散点图”、“折线散点图”等。

对于已经生成图表的，可以点图表区——右键——图表类型——改选以上介绍的几种类型。

2、点图表区——右键——图表选项——数据标志：勾选“X值”（或“Y值”）——确定。

3、还可以只选择你想要知道的具体点——右键——数据点格式——数据标志：同样勾选X值（或Y值）——确定。

2007及其以上版本，其数据标签在：

点图表——菜单栏——图表工具——布局——标签版块——数据标签：选你要的。其他操作大同小异。

（本文内容由百度知道网友Stcxj贡献）

在日常工作中我们经常会遇到要比较两列数据的相同与不同之处，例如我们要对比下面A列和D列两列数据的不同之处，之前我们已经介绍过四种方法实现了对比，今天我们来介绍第五种方法比较这两列数据（本文使用的Excel版本是2010版）：

首先，在B1单元格中输入公式（大小写都可以）：=OR(A1=$D$1:$D$21)

这个公式中的【$D$1:$D$21】的意思是指定的区域是D1单元格到D21单元格的数组范围。这个公式的意思是拿A1单元格的内容与D1到D21的每一个单元格进行比较，如果只要有一个相同的就返回TRUE（真），如果在指定的范围中连一个相同的都没有则返回FALSE（假）。执行此公式需要特别注意的是不能简单的按回车键进行计算，因为它是要按数组公式进行计算，需要按组合键【Ctrl+Shift+Enter】进行计算，意思就是按住Ctrl和Shift键后再按Enter键。

按组合键【Ctrl+Shift+Enter】后的效果：

可以看到B1单元格中返回了TRUE，意思就是在D1到D21的范围中有跟A1单元格相同的内容。而且按组合键之后公式框中可以发现两边多了两个大括号，说明就是按数组进行了计算，是正确的。

然后，将鼠标移动到B1单元格的右下角，当鼠标变成黑色实心十字的时候按住鼠标左键往下拖动来填充公式计算：

凡是B列中返回TRUE对应的A列该行内容在D列中都有相同的内容。凡是B列中返回FALSE对应的A列该行内容在D列中就没有相同的。然后将B列内容复制后进行选择性粘贴成数值即可去掉公式，再进行筛选就可以找出A列中哪些内容在D列中有，哪些在D类中没有。

同理可以将D列的每一行内容与A1到A21的数组范围进行比较，可以得出D列中哪些在A列中有相同的，哪些没有相同的。

补充知识：

OR函数简介:

Excel中OR函数可以用来对多个逻辑条件进行判断，只要有1个逻辑条件满足时就返回 TURE ，函数形式如下：OR(logical1,logical2, ...)，在Excel 2010版本中OR函数的参数不止30个，小编试了一下100多个条件参数都可以计算出来。而AND函数与之不同的是要求所有的条件参数都满足成立时才返回 TURE ，只要有条件不满足时就返回 FALSE 。

欢迎大家评论、留言、提问，谢谢大家阅读！

这座城市，一半人在拼命，一半人在认命；一半人在抢时间，一半人在耗时间；一半人在燃烧青春，一半人在虚度青春。

今天有学员，私信问我，纵向的折线图，如何来制作，并且发来了一张截图给我，我仔细看了一下，其实这个图表的真正表达应该叫散点图，每个新手在学习的过程中，都会或多或少的遇到表达上的不准确，但这都没有关系，最重要的是动起来。

不管做什么，关键是要先做起来。

如何才能做到图片中的效果呢，简单来说，可以分为4步，进行散点图的制作，接下来就跟大家说一下是哪四步。

1.框选数据插入散点图

2.将默认的数据源删除

3.添加新的数据系列值

4.设置图表格式，将线框，图表名称等数据全部删除

回复关键词：折线图，获取本文章课件

我是阿根，专注分享Excel技能技巧

点赞、评论，转发，是对我最大的鼓励与支持

我将努力写出更多优秀的教程

欢迎加入Excel训练营与近百位同学一同成

关注我们获得更多精彩内容

平时见得最多的也许是柱形图了，但我个人最喜欢的却是散点图。在讲散点图之前，我先阐述一个不太严谨的个人观点。我认为，所有的数据图表都可以分为两类，一类是偏重于展示，一类是偏重于研究。

如何理解？偏重于展示的图表，往往是将某个指标所蕴含的信息更为直观地表达，即该类图表中获取得的信息，是可以直接应用于业务决策的。比如某公司全国各省的销售员人均销售业绩的柱形图，孰高孰低被展现得很明显，那么哪些地区需要努力，或者哪些地区需要裁员，就可以进行决策了。

偏重于研究的图表，往往是显现出指标间的某些隐藏的关系，从图中得到的信息往往需要进一步的研究，才能落地为具体的业务判断或执行策略。

比如某公司人均销售业绩与员工离职率的散点图，你或许能看到两者存在某种数学关系，但你并不能获得显性的结论，而是需要通过进一步的研究，比如回归建模，才能提取出这种关系。

研究型的图表就像一扇门，经常能帮助我们打开另一番天地，获得的信息价值也更高；但相对的，也有更高的风险，许多时候你投入很多时间，获得的结论却没有价值。

散点图最核心的价值在于发现变量之间的关系，千万不要狭隘地将这个关系理解为线性回归关系。指标间的关系有很多，如线性关系、多项式关系、指数关系、对数关系等等，当然，没有关系也是一种重要的关系。

最基本的散点图我们来看一个典型的散点图，下图展示了一家外卖配送公司配送员人均负荷（一天需送掉的订单量）与订单的准时送达率。

散点图的基本元素就是点，即通过两个变量（纵坐标轴和横坐标轴）的数值，将点打在图表对应的位置上，通过散点的分布，来观察两个变量之间的相互关系。通过上图我们就能直观地看到，人均配送负载和订单的准时送达率是负相关的关系，即人均配送负载越高，准时送达率越低，而人均配送负载越低，则准时送达率越高。我们通过添加一条趋势线，来使得这个关系更为明显。

对于一家外卖配送公司，这个结论似乎是一个常识，当一个人一天要配送的订单越多，则他就越难准时将所有订单送到。

其实，这个逻辑未必是成立的，真正的解读是：如果订单是循序渐进，在时间分布上平缓地递交给配送员，那么单再多，配送员能一单单送完，则超时率是依旧平稳地；但实际情况并非如此，外卖配送需求都是在高峰时间段脉冲式增长，这就导致配送压力大时，配送员同时承接了多个订单，这就导致了手头总有一部分订单会因为绕路配送而超时，这才导致了如下散点图上的反映。

其实散点图的制作只需要三部操作：

1.点出散点图制作控件；2.选择数据；3.调整图表参数首先点击“插入”选项卡下的“图表”模块，在左边的图表选择项中，选择“XY（散点图）”，暂时先选择第一种类型，后面的几种类型会慢慢介绍。

如图2-2，点击“确定”按钮后，就会生成一个空的散点图。如图2-3，右键点击图形，选择“选择数据”，就会出现如图2-4的数据源选择框，在这里点击“编辑”按钮，进入如图2-5显示的“编辑数据序列”界面。在“编辑数据序列”这个界面当中，我们需要设置三个数据，第一个是图标标题，你可以选择某个单元格，会出现对你选中单元格的绝对引用参数；第二个是X轴上要显示的指标，你可以操作鼠标在Excel表中圈选，也可以自己输入绝对引用语句；第三个是Y轴上要显示的指标，方式与第二个相同。

图2-2

图2-3

图2-4

选好数据后，一个最初级的散点图就会展现在你面前了。需要注意的是，最初的这个图往往没有你想象的漂亮，经常你会发现散点集中在某几个较小的区域。如图2-6，不要惊慌，这样的原因往往是你没有调整好横纵坐标轴的“坐标轴选项”。双击坐标轴，将数据展现区间调整好合适的范围。如图2-6，原来的横坐标轴区间是0.0-0.95，但是我们大部分的点集中在0.75-0.95之间，因此我们将横坐标轴区间调整到0.75-0.95，这样图表看上去就美观多了，更重要的数据间的关系也被清晰地呈现出来了。除了对坐标轴缩放的操作，我们还经常会用到“坐标轴选项”里的“单位”和网格线的格式，因为有时我们需要调整坐标轴的颗粒度，以使得散点的位置展示得更为精确。当散点较多且较为细密时，我们需要将网格线调整为虚线并弱化颜色，使得散点间的关系能更为突出。

图2-5

散点图只表达了两个变量的关系？不是的，我们还可以在点上做文章。可以用点的大小来衡量另一个变量。这样，散点图就变成了气泡图。为什么叫气泡图？答案非常直观，有大有小的圈圈，最像的就是气泡了。且我们在制作气泡图时，往往会设置颜色的透明度，以展示气泡的密集程度。这么一来，气泡图就更直观了~

图2-8

如图2-8，我们用气泡的大小来表示配送费收入，这样我们就可以观察三个变量之间的关系了。我们看到，无论从人均负载的角度还是准时送达率的角度，配送费都没有呈现出与其他两个指标的相关关系。

小结一下，从图2-8中，我们能够获得的认知有如下几条：

1. 人均配送负载与准时送达率有明显的负相关关系，即一个指标随着另一个指标的增长而减少。2. 每单的配送费与人均配送负载和准时送达率都没有呈现出明显的相关关系。3. 样本点大部分集中在准时送达率87%-93%，人均负载11-15的区间范围内，说明配送员的一般状态就在这个范围内。

通过以上几点，相信大家以及能领略到散点图的强大了。我们更近一步，给散点图再增加一个变量，即用气泡的颜色来代表另一个变量。需要注意的是，用颜色代表的变量，不适合再用连续型变量了，最好是用类型变量或有序变量。在Excel中，我们不能直接通过导入数据来用颜色来代表某个变量。这是一点小小的遗憾，但我们依然可以手动将效果做出来。

图2-9

如图2-9，四种气泡的颜色代表了四个配送团队。我们会明显的看到，红色的配送团队的准时率显著领先于其他团队，但在人均配送负载上则比较低，说明红色团队的工作量不是很大，因此效率较高。黑色团队的人均配送负荷较高，准时送达率较低，与红色团队的状况正好相反。蓝色团队的成员则分布非常分散，有的人负载高准时率低，有的人负载低准时率高，可见蓝色团队的管理方式和其他三个团队或许非常的不同，值得进一步的调查分析。

通过从散点图到气泡图的应用，我们不断地窥探出数据指标间的关系，并且挖掘出了许多新的信息，这真的是一个欣喜的发现过程。所以，你们应该能理解为什么我喜欢散点图了吧。

作者：胡晨川（中国统计网特邀认证作者）

版权归原作者所有，转载仅供学习使用，不用于任何商业用途，如有侵权请留言联系删除，感谢合作。

大家好，上周介绍完饼图之后，您有没有亲自动手尝试一下呢？

今天小数要带大家熟悉的是散点图，本文图表生成所用工具仍然是大众熟知的Excel。

说到散点图，它既能用来呈现数据点的分布，表现两个元素的相关性，也能像折线图一样表示时间推移下的发展趋势。可以说是最灵活多变的图表类型。

大数据时代，人们更关注数据之间的相关关系而非因果关系。那么如何考量各变量之间的相关关系呢？散点图就是一种最直观简单的形式。

散点图的特点？

适用于展现两个或三个变量之间的关系

数据量越大，散点图发挥的作用越好

需要关注离群点（异常点）对整体的影响

普通散点图与可视化

在实际应用中使用频率最多的是二维散点图和三维散点图，而三维散点图又与气泡图有密切关系，我们后面会提到。

二维散点图的制作相对简单，无需特殊设置。可视化方面其实也还是咱们的老套路，这里再通过一个例子带大家简单过一遍。

小数收集了某餐馆服务满意度与排队时间相关的15组数据，导入Excel ，直接生成散点图如下：

可视化操作步骤：

背景颜色应用（还是以 DataHunter 的 logo 绿为主哦~）

字体颜色、数据点颜色调整

淡化坐标网格线

插入趋势线。这是散点图的特色，为了分析两个变量之间的关系，我们通常会引入趋势线，Excel中目前给我们提供了线性、指数、线性预测、双周期移动平均等多种类型选择。

现实操作中判断两个变量之间的关系类型，还需要引入回归分析等方法，我在这里只是作为可视化的一个简单举例，所以就直接插入了线性趋势线。

优化后如下图所示：

曲线图

在Excel 的散点图分类中，我们还可以看到一类变体的散点图——曲线图。它经常被用来表示某个变量随时间变量变化的趋势，与折线图的功能类似。

我将自己最近30天的步行数作为数据源，然后生成曲线图。

这里不再赘述可视化过程，与普通散点图基本相同（通过线条填充半透明处理、背景渐变填充、日期格式和坐标轴调整等）

气泡图

气泡图与散点图最直观的区别为：散点图中的数据点长得都一样，气泡图中的气泡却大小不同。因为气泡图在原先散点图的基础上引入了第三个值来控制气泡的大小。

举个例子，我将某校30名学生的期末成绩与其对应的出勤率作为x值和y值，并引入“去图书馆的时间”作为z值，默认生成气泡图如下：

气泡挤在一起了，怎么办呢？

调整坐标轴，将出勤率和分数的最小值和最大值进行修改，调整相应颜色，然后再插入趋势线，就得到了下图：

可以看出，除了出勤率与分数之间有简单的正相关关系外，分数越高的同学，去图书馆的时间也相对较高（气泡普遍更大）。

最后再强调一下：散点图实际应用中更适合大范围的数据，本篇文章中的举例只是为了说明问题，所以数据量都不大。

小结

散点图适用于研究大规模数据中两个变量之间的相关关系

气泡图可用于研究展示三个变量之间的关系

曲线图作为一种特殊的散点图，适用于研究连续变量的变化趋势。

题外话：其实看到这些散点图，总让小数想起一款大圆吃小圆的网页游戏，当初我也是沉溺其中，无法自拔，它的页面如下图，游戏规则挺有意思的，感兴趣的小伙伴可以去体验一下~

最后，基础图表篇的可视化系列文章很快就要结束啦，下个系列大家想了解点啥呢？留言给小数，你想要的内容，统统给你！

上次分享我们学习了非字型数据图表的绘制方法，将员工上半年和下半年的销售业绩进行了形象生动的对比展示，那么还有没有其它的图表能够让两组数据更具对比性呢？答案是肯定的，双层图表能够帮助我们将两组数据非常直观的以对比的方式展现出来，也可以说是非字型图表的升级版。

快来看看今天的案例吧。根据上图左侧的员工销量表绘制右侧的双层柱状条形图，要求上半年的销量显示在上方，下半年的销量显示在下方。

这个案例有以下3个特点：

① 同一个图表中要上下分别显示两组数据（上半年的销量和下半年的销量）。

② 上下两组数据都要有自己单独的横坐标轴和纵坐标轴。

③ 两组数据的纵坐标轴刻度都是从0开始，并且有相同的边界和单位。

看起来是不是超级烧脑，别害怕，跟着我一步一步的来。要绘制出如案例中的双层图表，我们大概要经历5大步骤。

步骤1：初步创建图表。

①为「员工销售业绩表」添加辅助列D列，命名为【中间差】。

【中间差】的值=上半年和下半年两组数据中的最大值向上取整×150% - 员工下半年的销量。例如案例中的两组数据中的最大值为964，那么向上取整就是1000，【中间差】的值=1000×150% - 上半年的销量。

为什么要乘以150%呢？这个值是我经过测试之后找出的比较适合的值，当然这个值也是可以自由调整的，如果向上调整，则两层图表中间的间隔就会变大，反之则变小。

奉上动态演示：

② 选择A1:D7区域插入堆积柱形图，为图表选择一个默认的样式。奉上动态演示：

③ 调整数据系列位置顺序，格式设置为无填充、无边框、无阴影。奉上动态演示：

④ 删除图表网格线、图例、图表标题。

经过上述设置，双层图表的样式已经初见端倪，可是离我们最终的演示效果还是相差甚远。

步骤2：绘制上层图表的虚拟横坐标轴

① 添加辅助列E列命名为【虚拟横坐标轴】，其所有值设置为上半年和下半年两组数据的最大值向上取整×150%，本例中即设置为1500。奉上动态演示：

②将【虚拟横坐标轴】数据添加到图表中。动态演示如下：

③更改【虚拟横坐标轴】系列的图表类型为折线图。动态演示如下：

④为折线图设置趋势线并调整格式。动态演示如下：

⑤设置折线图的格式同趋势线的格式一致。动态演示如下：

⑥选择【虚拟横坐标轴】系列添加数据标签，标签包括类别名称，不显示值。标签位置设置靠下。动态演示如下：

步骤3：绘制虚拟纵坐标轴

①设置纵坐标轴的边界和单位。边界最大值=上半年和下半年两组数据的最大值向上取整×150%×2（本例中即3000），边界最小值为0，单位=上半年和下半年两组数据的最大值向上取整×150%（本例中即1500）。动态演示如下：

这个步骤是为了确保两组数据的纵坐标轴是对称的。为了不与之后设置的虚拟纵坐标轴重复，设置完成后需要删除纵坐标轴。

②新建两列辅助列F列命名为【散点X】、G列命名为【散点Y】，F列所有数值都为0.5，G列的数值要设置为等差数列，且必须包含虚拟横坐标值（本例中即1500）。

这是为了保证两组数据纵坐标轴的刻度对称一致。F和G列的行数需要满足虚拟横坐标值上方的行数=虚拟横坐标值开始往下的行数这个要求。如下图所示：

③选择G列数据添加到图表中，系列名称命名为【虚拟纵坐标】，更改其图表类型为XY散点图。动态演示如下：

④编辑【虚拟纵坐标】数据系列，将F列数据添加到X轴系列值。动态演示如下：

⑤为散点图添加数据标签（左侧），设置散点图的格式为无填充、无轮廓、无阴影。动态演示如下：

⑥从虚拟横坐标（1500）开始的位置修改标签值。动态演示如下：

步骤4：调整格式。

①添加图例，只显示上半年和下半年两个图例，其它删除，调整图例位置。动态演示如下：

②添加图表标题，命名为「员工销售业绩对比表」。

③调整图表字体和字号。

好了，双层图表就这样完成了。

在之前的推文中，我向大家介绍了散点图，以及它在Excel中可实现的几种图表形式。

可点击查看：Excel图表｜散点图（气泡图）

在今天这篇推文中，我将着重教大家如何将分类变量轻松映射给不同颜色的散点，以表现另一个分类变量。

以制作下图为例（图一）：

数据来源：网络 | 数据分析：数据挖掘与可视化分析

1、在Excel中，制作一般的二维散点图时，数据组的罗列形式如下表（表一）：

2、现在我们将上表的数据罗列成下表（表二）的形式：

3、选中“中国”列，在“插入—图表”中选择插入“散点图”（表三、表四）：

4、选中上面的散点图（表四），点击“设计—选择数据”（表五），会出现一个对话框（表六）：

5、在表六的图例项中，点击“添加”，将另外两个国家的数据添加进来，如下表（表七）：

同时，也可以在“编辑”中更改“系列名称”，用上下键调换系列的前后位置（最终生成图表的颜色覆盖层次会随之发生变化）。

在此，敲黑板敲黑板！！！

在选择X轴和Y轴系列值的过程中，只需选择数值，一定不要把变量标题选进来哦！！

6、添加完成后，对话框如下表（表八）所示，点击“确定”，即可生成图表（图二）：

通过调整上图（图二）的格式，最终可以生成图一的图标样式。

上述的这种方式并不是最简单的，但可以让大家明白这种图表制作方法的原理所在。

下面教大家更简单的方法：

在生成表二后，全选它，然后选择“插入—图表—散点图”，马上就可以得到下图（图三）：

点击选中上图（图三）后，点击“设计—选择数据”（表五），出现下面的对话框（表九）：

1.首先把需要两组或者多组数据录入表格，把数据的字体，格式，大小都编辑为统一的格式。

2.然后点击菜单栏的插入-----图表。

3.然后选择 柱形图， 这是一种常用的图形，一眼就可以看出两组数据的差距。

点击下一步继续。

4.然后点击数据区域后面的箭头， 

5.然后框选需要进行对比的数据。

如图所示，这里只需要把2012年和2013年每月的数据框选即可。

接着进入下一步。

6.接着在图形选项里面做一些简单的编辑，这个按需选择，默认不添加也可以的。

7.最后确定，即可完成。

最终 2012年和2013年出货统计对比图如下

（本文内容由百度知道网友茗童贡献）

今天跟大家分享下，在Excel中，如果对两组数据进行对比，并快速找出不同数据的方法。方法其实有很多，今天跟大家分享其中的3种方法

1，建辅助列，进行对比

这是最常用的一种对比方法，但效率不是很高。

即使用公式计算两组数据的差值，然后筛选，并隐藏差值为“0”的单元格后，显示的就是两组数据中数值不同的列了。

具体操作如下：

2，使用条件格式

使用条件格式，在条件格式中，使用公式来新建格式规则，输入的公式为 “=G2<>F2”，满足条件的单元格，填充黄色。

具体操作如下：

3，使用快捷键 "Ctrl + \"

使用快捷键的方法是最快，也是最方便的方法。

操作步骤：

选择需要对比的两列数据B2：C8

按快捷 "Ctrl + \"

具体操作如下：

使用快捷键的方法，不仅可以快速对两组数据进行对比，还可以对多组数据进行对比，操作的方法都是一样的。

比如下图所示的对四组数据进行对比。

今天的分享就到这里，明天继续。