这些年，为了在 Excel 中给序列去重，不知道坑死了多少人

BI佐罗

发布于 2019-09-25 15:18:03

2.7K1

发布于 2019-09-25 15:18:03

老铁们应该已经非常熟悉罗叔给出的这个公式：

PowerBI = （ Excel + PPT ）²
= Excel² + 2·Excel·PPT + PPT²

请大家熟记这个公式，并记住来自罗叔的正确解释：

第一，Excel 很重要。
第二，PPT 很重要。
第三，PowerBI 更重要。

而我们为了在数据时代领先一步，你需要的不仅仅是有局限的学习 Excel 或者 PPT，而是三者。任何一个只强调一者而忽略他们的组合性的方式都会引入误区。

关于 PowerBI 在 Excel 基础上如何进入，可以参考：这些年，Excel不知道坑死了多少人，你有幸免吗？

本文快速讲述 Excel 序列去重问题。

本文表面在讲解 Excel 序列去重问题，实际在通过一个点问题，让各位老铁看到一个面，一个空间，一个原来你没有思考过的方式，它是我们持续改进的思维模式。

问题描述

有一个销售数据，如下：

现在想从这个销售数据中提取产品，而这个产品应该是非重复的，用做后续其他计算，问题是：如何对项目去重？

请记住罗叔的话：做正确的事比正确地做事重要得多得多…。就这个问题而言，99.99% 的人几乎掉坑的，待罗叔慢慢道来。

不同的人会给出不同的方法，罗叔先列举你在市面上可以看到的方法，再列举你看不到的方法，再来仔细体会，以悟出大道。

Excel 按钮点击法

这是最初级最简单以及问题最大的方法，如下：

其步骤如下：

复制订单数据的产品数据到目标位置；
点击【数据】【删除重复值】，勾选【数据包含标题】，点击【确定】。

得到结果如下：

问题得到解决。

这是标准的办公小白做法。该做法可以临时解决问题，最大问题在于：当订单数据发生变化时，去重的产品数据是无法自动变化，保持最新的。

Excel 第二定律

该定律与微软无关，为了方便描述，由罗叔给出。如果你不希望自己总是要修改自己做过的事情，那就请记住这条铁律：在系统中，一个功能（公式）使用后，请忘记它，且该功能（公式）永远保持预期逻辑，绝不出错。这就是罗叔给出的 Excel 第一定律。这是对非科班出身的老铁们说的，因为这些老铁花了很多钱，买了很多教程，结果都是错的，而却不自知。有的老铁会说，没事啊，有 VBA 啊，VBA 可以把刚刚的过程自动化。罗叔笑而不语。我们等会再来说 VBA 的问题。

老铁永强坐不住了，说他有好方法，可以用透视表，我们来看看老铁永强的方法。

永强问，怎么是 Excel 第二定律，那第一定律，去哪里了？罗叔：在之前的文章找。等下再说。

Excel 透视表去重

这个非常简单，只需要基于订单数据创建透视表，然后即可，如下：

永强说，这个就满足罗叔你说的 Excel 第二定律：当一个功能使用后，忘记，且该功能永远保持预期逻辑，绝不出错。这个透视表就可以自动变化，保持产品一直是去重的。不错，这确实不违背 Excel 第二定律。但马上它会违反 Excel 第三定律。

Excel 第三定律

该定律与微软无关，为了方便描述，由罗叔给出。描述为：系统的功能应该是可接通传递的。在本案例中，当去重已经进入透视表后，由于透视表是一个相对独立的系统，很难基于透视表继续写公式，而我们去重的目的就是为了后续参加更多的函数计算，所以，透视表虽然表面上解决了这个问题，但实则切断了计算计算的很多可能，所以，仍然不是理想的方法。

另外，透视表的天然用途是分组汇总计算，并不是去重，这里其实是用到了透视表的分组，而却又不汇总，并不是透视表的设计初衷，这种技巧并没有在微软的主功能线路上，尽量避免。

老铁玉田冲了出来：永强你不行，这个还是得来个数组函数，看我的。

Excel 数组函数去重

玉田是 Excel 的忠粉，他几乎购置了所有 Excel 类图书，学遍了 Excel 各类函数，罗叔经常也为玉田能写出很巧妙的函数而震撼，玉田给出这样一个函数。（如果又和哪个Excel博客写的一样了，不好意思了）

先看下玉田的效果：

其公式为：

=INDEX( $A$2:$A$7, SMALL( IF( MATCH( $A$2:$A$7, $A$2:$A$7, 0 ) + 1 = ROW( $A$2:$A$7 ), ROW( $A$2:$A$7 ) - ROW( $A$1 ) ), ROW( ZX3 ) ) )

罗叔表示看不懂玉田的公式（怎么可能呢），但确实好用。玉田的智力是很不错的，能把这东西写出来，但从今天来说，玉田对于这个问题在正确地做事，但却没有做正确的事。

玉田，首先你实现了核心诉求，但似乎并没有处理错误值，玉田很快改为：

其公式为：

=IFERROR( INDEX( $A$2:$A$7, SMALL( IF( MATCH( $A$2:$A$7, $A$2:$A$7, 0 ) + 1 = ROW( $A$2:$A$7 ), ROW( $A$2:$A$7 ) - ROW( $A$1 ) ), ROW( ZZ1 ) ) ), "" )

注意玉田使用了 Excel 的数组函数，如果您不清楚数组函数，不要紧，本文不是数组函数的广告，不 Care。

好的，玉田，确实没有问题了，但其实还有问题。

玉田说：罗叔，这个既满足自动计算，又满足可以支持后续继续写函数，哪里有问题？

罗叔说：没错。你已经做到了大部分 Excel 用户做到的地方，这也着实会有成就感，因为这个公式并不简单了，可以挑战自己写出类似的 Excel 公式。但它不满足 Excel 第四定律。

Excel 第四定律

玉田，你会了很多技巧，可是，从心法层面，你可以说出几条呢？

罗叔已经给出了 Excel 的三条定律了，玉田你有着大量地实践，所以你自然而然地选择了相对较好的方法，相信你肯定知道按钮点击法和透视表法，但你不是用心法和定律衡量的，而是用经验。那你再看看罗叔给出的 Excel 第四定律：一个功能做好后，不应该被修改，但可以扩展。这个定律也很简单。你的公式存在两个大问题：

你这个公式必须位于页面的首行；
不可以将这个目标复制粘贴到其他位置；
另外，源区域和目标区域都用硬编码的写法，无法识记。

玉田：那罗叔你说咋改？

设计模式

首先，类似的，我们应该先使用表，当然这个概念你知道，你却没有用。其效果大致是：

去重后效果：

其公式是：

=IFERROR(INDEX(tData[项目名称],SMALL(IF(MATCH(tData[项目名称],tData[项目名称],0)+1=ROW(tData[项目名称]),ROW(tData[项目名称])-ROW(tData[#标题])),ROW()-ROW(tDemo2[#标题]))),"")

这时候已经不错了，如下：

我们故意将这两个表分开，注意目标表是可以不在第一行的。

但是，还是有一个问题，当我们想再做一遍时，我们 必须重新写一遍。这里给出一个重要的思想：面向接口，以及我们刚刚提到的：一个功能做好后，不应该被修改，但可以扩展。

我们发现这个目标结构仅仅依赖于三件事：

源表名
源表列
目标表名

我们现在做一件事，将这个功能和依赖的内容彻底分开。我们先来看看效果：

首先，罗叔来解释一下：

【tb销售记录】是源表名；
【产品】是我们希望去重的列名；
【tb目标】是我们去重后要放置的位置。

我们的公式内容不需要做任何修改，当你希望去重的内容是销售额时，只需要：

此时，销售额被去重了。我们仅仅将来源表列名称从产品改为了销售额即可。让我们来看看这个一劳永逸且无懈可击的公式吧：

=
IFERROR(
   INDEX(  INDIRECT( Distinct.SourceTable&"[" & Distinct.SourceColumn &"]" ),
      SMALL(
         IFNA(
            IF(
               MATCH(
                  INDIRECT( Distinct.SourceTable&"[" & Distinct.SourceColumn &"]" ),
                  INDIRECT( Distinct.SourceTable&"[" & Distinct.SourceColumn &"]" ), 0
               ) = ROW( INDIRECT( Distinct.SourceTable&"[" & Distinct.SourceColumn &"]" ) ) - IFERROR( ROW( INDIRECT( Distinct.SourceTable&"[#标题]" ) ), ROW( INDIRECT( Distinct.SourceTable&"[#Headers]" ) ) ),
               ROW(  INDIRECT( Distinct.SourceTable&"[" & Distinct.SourceColumn &"]" )  ) - IFERROR( ROW( INDIRECT( Distinct.SourceTable&"[#标题]" ) ), ROW( INDIRECT( Distinct.SourceTable&"[#Headers]" ) ) )
            ),
            FALSE
         ),
         ROW( ) - IFERROR( ROW( INDIRECT( Distinct.ResultTable&"[#标题]" ) ), ROW( INDIRECT( Distinct.ResultTable&"[#Headers]" ) ) )
      )
   ), ""
)

再说一遍这个公式满足 4 个严格的效果：

自动变化
可以到处复制
绝不出错
不依赖于名称

如果要走火入魔，就来得狠一点，才能涅槃，这是罗叔年轻时候不懂事写的，时隔多年，发现很多年轻人在这个道路上，不进不退。

以上全错

对上述的完美方案来说，确实是无懈可击的，而且该方案考虑了中文版和英文版以及错误处理，可谓函数版的终极版，罗叔非常期待有人说他可以想出一个函数方法，可以在不违反我们所谈到的 Excel 的 4 条定律来实现。

好了，罗叔接下来要颠覆的是自己，罗叔上述教学在 Excel 的角度是对的，但从更高层面来说是错的。罗叔充分诠释了正确地做事，但这却不是正确的事。

下面指出上述的错误：

去重是一个非常基本的数据分析特性，居然不是内置的，而要这么折腾，合理吗？
我们不管是办公还是数据分析，我们已经忘记了初衷而迷失了方向。

在业界如出一辙的问题就是：作图。用 Excel 去做一个非常特殊的图，然后研究坐标轴怎么设计，对于目标不是作图师而是办公或者分析师来说，只需要用工具一键出图，而不是自己重新造轮子。文末给出 3 篇雄文（必读推荐）来说明该问题，此处不再展开。

因此，从 Excel 函数的角度来说，罗叔当年已经将这个问题做到极致，而罗叔意识到自己错了。这种认错是在极致后的领悟。微软也很清楚他的问题，但 Excel 是世界标准，所有函数一旦形成，不会变化，因为一个变动，全世界都要变，VLOOKUP 就是再好不过的例子。

微软的补救

微软已经在 Office 365 以及 Office 2019 中给出了正确的方法，就是一个函数，就叫UNIQUE，如下：

这是满足 Excel 4 条定律的真正正确的做法，这就叫工具，微软将去重的逻辑全部封闭在一个函数里，也就是不需要我们看到细节，它相当于内置实现了罗叔此前的完美无懈可击版，但它来得晚了10年。

如果你的 Excel 没有这个函数，只能说明你的版本有点低，当然这不是你的问题，因为 99% 的人的版本都还没有带这个函数，在这种情况下，从函数角度来说，罗叔给出的去重函数还是可用的最好的版本。

何为正确 - 四个定律

罗叔提出的 Excel 的四个定律全部来自工程界长期的实践总结，属于设计模式。这里再次一起说明：

Excel 第一定律（DRY定律），Don’t Repeat Yourself，当你在Excel中不断重复一个操作的时候，一定有一个一键解决的方法只是你不知道，或者微软还没开发这个功能，但 99% 的情况是前者。典型案例为用数据模型代替VLOOKUP。如果你不希望自己的工作陷入不停的Ctrl C+V 然后不停地点按钮，那就请记住这条铁律。
Excel 第二定律（封闭性定律），系统应该是自动封闭的，当外界变化时，系统自动变化而不用关心系统的内部。典型案例为函数的自动计算、点击刷新按钮自动重算。如果你不希望自己总是要修改自己做过的事情，那就请记住这条铁律。
Excel 第三定律（传递性定律），系统的功能应该是可接通传递的。典型案例为单元格计算、函数等。如果你不希望自己的工作断掉，走入死胡同，那就请记住这条铁律。
Excel 第四定律（OCP定律），Open Close Principle，又称开放闭合定律，指的是系统应该对修改关闭，对扩展开放，也就是说，写好的函数不应该被修改，但是可以扩展。典型案例为上述函数去重的无懈可击版。

所谓正确，在这里有个非常基础的标准，就是提高生产力，提高生产力的终极思想就是：可复用。作为科班的科学思想，罗叔不想展开，罗叔在 Excel 以及 PowerBI 的培训中针对非科班的各类老铁已经按照正确的思想帮助大家建立模式和心法，对于老铁们可以在市面看到的无数的招数（无数的函数用法）来说，心法和思想是稳定不变的，而举一反三更为重要。

微软在当年设计 Excel 的时候，的确是定位于办公工具，另外，随着软件工程等领域以及数据分析领域的成熟，时代不可避免的会出现更强大的工具，你可以闭上眼睛装睡，但世界早已发生变化，来势汹汹。

在 Excel 面前，PowerBI 算个啥：

啥都不算，但 PowerBI 隐藏的潜力将为那些愿意相信罗叔的老铁们带来未来数据时代的钥匙：

至此，正确的事是什么：Power Query（PowerBI），下面说明。

正确的事

如果说 UNIQUE 函数在办公场景是正确的选择，但 80% 的情况可能是分析的场景，作为分析师，正确的事或者选择只有一个，那就是 Office 2010 就可以支持的 Power Query 或者独立的 PowerBI 软件。

在 Power Query 来说，可以得到：

以及加载到 Excel 得到：

晕，原来一下就好了。

该方法也满足上述的四个定律：

源数据发生变化，点击刷新按钮即可。满足第一定律。
源数据发生变化，公式不需要被修改，点击刷新按钮即可。满足第二定律。
得到的数据加载到 Excel 表中，可以继续后续计算。满足第三定律。
不需要修改 Power Query 的转换逻辑，修改区域的名称就可以自动重算。满足第四定律。

至此，罗叔展示了罗叔是如何一步步逐渐探索问题，追求极致，否定自己，做出选择的过程。这个过程不是为了证明 PowerBI 比 Excel 强大，而是为了说明判断一件事的准则和逻辑以及在适当的场景所选择的适当的工具。

玉田说：我明白了，VBA 的方法我就不说了，罗叔老师，我知道 VBA 更不是适合的工具了，时代真的变了。罗叔欣然一笑。

你以为用了 PowerBI 你就对了

如果你认为本文就是为了说明让你选择 PowerBI 那你就又错了。本文用这么真实的过程是让人知道，你在判断事物的时候在自己心里有准则吗？有定律吗？

你有思想吗？还是只会像当年背历史地理以及数学公式一样，记住一堆公式。毫无意义。

好好想想看，如何提升自己抽象的思维能力以及逻辑推演能力和举一反三的创造思维能力，那才是真正的正确。

当你进入了 PowerBI，出现在你面前的只不过是新的一堆选择罢了：

用 Power Query 还是 DAX ？
用计算列还是度量值？
用哪个可视化？
用哪种地图？

你看到了一大堆的函数和不停的招数，迫不及待的扑了上去，只能说：罗叔又白写了一篇以及不得不羡慕年轻就是勇敢。

罗叔提醒各位老铁，在 PowerBI 中有着和 Excel 一样无尽的招数，而上述的四个定律在 PowerBI 中一样适用，请仔细体会。有人说他会了 PowerBI 的很多功能和 DAX，不好意思，罗叔根本不 Care，罗叔只好奇你可以讲出什么思想和心法。

另外，从本文的来看，PowerBI 是一个比 Excel 更加简单的工具，而且确实如此。你要知道，再简单的工具也架不住懒得学和守旧不进。

总结

本文提出的几个定律来自来自设计模式。这里再次一起说明：

DRY定律，Don’t Repeat Yourself，当你在Excel中不断重复一个操作的时候，一定有一个一键解决的方法只是你不知道，或者微软还没开发这个功能，但 99% 的情况是前者。典型案例为用数据模型代替VLOOKUP。如果你不希望自己的工作陷入不停的Ctrl C+V 然后不停地点按钮，那就请记住这条铁律。
封闭性定律，系统应该是自动封闭的，当外界变化时，系统自动变化而不用关心系统的内部。典型案例为函数的自动计算、点击刷新按钮自动重算。如果你不希望自己总是要修改自己做过的事情，那就请记住这条铁律。
传递性定律，系统的功能应该是可接通传递的。典型案例为单元格计算、函数等。如果你不希望自己的工作断掉，走入死胡同，那就请记住这条铁律。
OCP定律，Open Close Principle，又称开放闭合定律，指的是系统应该对修改关闭，对扩展开放，也就是说，写好的函数不应该被修改，但是可以扩展。典型案例为上述函数去重的无懈可击版。

罗叔和所有深爱 Excel 的老铁一样深爱 Excel，但罗叔从来不说她：你好强大啊，你好漂亮啊！Excel 作为数据分析的世界标准级模特，难道她不知道自己漂亮吗？她不会为任何一个只知道她漂亮的人停留，她需要的是真正理解她的人。

如果你想学习比招数更加透彻的思想，那么我们便是同道，不怕你不学，就怕你学了，上海，线下，等着那些对数据分析和她有真爱的人。最后，PowerBI = ( Excel + PPT )²，有思想，才能真正平方，一切才刚刚开始。

此处罗叔想起一句成语： - 昨夜西风凋碧树，望尽 Excel 那些套路。 - 衣带渐宽终不悔，做到 Excel 极致。 - 默然回首，那人却在 PowerBI 处。

新的一周，祝各位老铁玩得开心。