标签Excel

Excel获取不重复数据结果（一）中介绍了利用公式获取不重复用户名的方法。通常情况下，我们希望更进一步：利用这些获取到的名称，来作为筛选项，然后查看每个筛选项的细项指标结果。例如，查看每个用户下单的订单明细：

操作方法：

1、在文件→选项→自定义功能区中，勾选开发工具选项

2、在开发工具选项卡中选择插入→列表框

3、选择列表框，右键选择设置控件格式，输入内容如下（即我们将数据来源中的数据放到控件列表中，控件将根据我们的选择将列表中人名对应的数字映射到E1单元格）：

4、在A列添加数据序号，F1单元格输入：=VLOOKUP(E1,$A$2:$B$20,2,0)。用以返回选择的人名。

5、组织H1:L2表格，在H3单元格输入：=IFERROR(INDEX(订单明细!$A$1:$A$32,SMALL(IF(订单明细!$B$1:$B$32=$F$1,ROW(订单明细!$A$1:$A$32),99999),ROW(A1))),””) ，按ctrl+shift+enter结束。下拉即可获取F1单元格人名所对应的订单号明细。

6、由于订单号唯一，我们就可以利用简单点的offset函数获取订单对应的其他信息，在I3输入：=IFERROR(OFFSET(订单明细!$A$1,MATCH($H3,订单明细!$A$2:$A$32,0),COLUMN(A1),1,1),””) ，填充其他单元格即可。

主要难点为第5步中的公式，解读如下：

1、利用公式IF(订单明细!$B$1:$B$32=$F$1,ROW(订单明细!$A$1:$A$32),99999)，返回一个列表LIST，即如果订单明细!$B$1:$B$32中的名称和F1相同，则返回该名称所在的行数，否则返回一个很大的值

2、再利用small函数，获取上面LIST中的第k个最小值，即该名称第k次出现的行号

3、利用index返回订单明细表中的该位置的订单号数据。

4、使用iferror函数进行美化。

百度网盘，文件下载：

链接: https://pan.baidu.com/s/10eWwu6KuvAahrIXT_eXBFQ

密码: jmbh

为一致为获取不重复的数据记录，可以利用Excel的高级筛选、数据透视表等功能进行操作。当高级筛选功能的好处在于快速，易操作，可以操作多列；坏处在于复用性差，数据量变更后得重新筛选。

我们可以利用公式完成对数据的去重操作，如下表中获取用户名称列表。

操作方法：

1、上表中，明细数据表命名为”订单明细”

2、在结果表中，B2中，输入=IFERROR(INDEX(订单明细!$B$2:$B$32,MATCH(,COUNTIF($B$1:B1,订单明细!$B$2:$B$32),)),””)，按ctrl+shift+enter结束，向下拉到结果显示为空时，表示已经获取了所有用户名称。

以上公式的原理在于：

首先使用COUNTIF计算在$B$2:$B$32区域中是否存在$B$1:B1区域的值，如果有则返回1，没有则返回0。此处返回一个1和0匹配列表LIST，1表示这个用户名已经出现在结果区域了

再利用match去匹配第一个0在LIST中的位置，返回具体的位置代号

最后使用INDEX函数，获取订单明细!$B$2:$B$32对应LIST位置的未出现过的用户名

IFERROR为美化修饰函数，使表格看起来更整洁。

文档下载请参见第二篇。

Excel获取不重复数据结果（二）

我们知道Excel条件格式可以做很多预警、热力图等相关数据可视化，因此在预警的一个特殊领域——项目管理中，条件格式也可以发挥其作用，可以利用条件格式制作一个功能简便的甘特图。

结果如下：

操作方法：

1、根据项目需要编制项目时间表如下：

2、首先填充计划时间的进度条，选中H3:AK11区域，点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格。

3、在”为符合此公式的值设置格式”中，输入：=AND(H$2>=$C3,H$2<=$D3)，设置格式填充为淡绿色，以确定计划时间段

4、重复2和3的步骤，在”为符合此公式的值设置格式”中，输入：=AND(H$2>=$E3,H$2<=$F3)，设置格式填充为较深的绿色

以上即为最终的效果，但我们发现一个问题：项目3中，实际结束时间超过了计划时间，这种情况下我们希望将超过的时间突出展示，可以执行以下操作：

5、新建规则，公式输入：=AND($F3>$D3,H$2>$D3,H$2<=$F3)，设定填充为橘红色。即可突出展示出6月26和27日超过计划时间

百度网盘，文件下载：

地址：https://pan.baidu.com/s/1gG6s2P_WAmmolQhpSXaN0w

提取码：itxx

在市场研究或产品监测中，通常会遇到对同一个数据连续多期的表现的预警监测。如，连续两期低于某个值、连续三期低于某个值等等。

仍以”单一目标值的条件格式预警“中的区域和产品为例。假设我们连续监测了8个区域5个产品在1-3月的销售情况，对于各个产品，如果某区域的连续2个月销量低于各区域当月平均值则用黄色预警，如果连续3个月均低于当月平均值则用红色预警。最终结果如下：

操作方法：

1、选中3月的数据区域C21:G26，点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格

2、在”为符合此公式的值设置格式”中，输入：=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=15，点击格式，设置填充为黄色。用以设置在2月和3月连续两个月均低于平均值的数据预警

3、上述步骤确定完成后，再重复步骤1，在设置公式时输入：=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=16，点击格式，设置填充为红色。用以设置在1-3月均低于平均值的数据预警

至此我们可以观察验证公式设置是否正确。以上问题的解决思路主要为：

我们判断某个月的，某区域的产品销售量是否小于所有区域的均值，如果小于则返回一个特定值，否则返回0。我们通过对不同月份的判断结果返回不同的值，然后对这些值相加即可知道某个区域是在哪几个月份销售额小于均值。

如我们设置某区域一月销售额小于均值则返回1，二月小于均值则返回5，三月则返回10，将三个月返回值相加，得到最终结果。通过对最终结果的判断，我们知道15则表示二三月份同时低于均值，16则表示一二三月份同时低于均值。

文件下载，百度网盘：https://pan.baidu.com/s/1NGSqr663kRUCvOUuCb8JtQ 提取码：umk6

Excel的条件格式可根据设定条件突出显示单元格数据，因此是制作预警表格的优良工具。

比如，有一系列数据如果小于某个值就设置为预警数据突出显示。在生产环境中，结果如下：

操作方法：

1、选定要预警的数据区域C3:G8，点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格

2、在“为符合此公式的值设置格式”中，输入： =c3<c$9

3、点开右下角的格式，设置填充为你想要的颜色。一路确定即可。

条件设置过程中主要难点在第二步的公式设置，如果对公式设置不太理解，请参考相对引用和绝对引用。以下做简单解释：

文中c3<c$9，表示将第9行固定，所有行的数据与第9行的数据比较。即C3和C9比较，C4/C5….C8都与C9比较。如果不固定第9行，那么比较对象就会变为C3对C9、C4对C10，依次类推。如果目标值只有一个而不是一行，那么设置为c3<$c$9即可。