物料管理小能手（统计不重复数据）

平时的仓库物料管理，有很多种材料要进进出出。

如果是用Excel做手工台账的，可以看看我的分享！

我有手工台账如下：

小本买卖，上面都是便利店的王牌销售产品！

随着种类的不断丰富，我想知道我进货的种类一共有多少！

怎么做？

思路一：

文字描述：

找到每一个品种在整个列表中有几个，如果有N个，则自己的数字变为1/N，N个1/N相加等于1。

公式：{=SUM(1/COUNTIF(A2:A8,A2:A8))}

再次啰嗦一下：公式的{ }不是手动输入的，而是输入=SUM(1/COUNTIF(A2:A8,A2:A8))公式后，同时按住Ctrl+Shift+Enter自动生成的，{ }这个符号表示的数组运算，如果对数组运算不太熟悉的，请看一下本公众号的其它文章。

我们将公式做一下拆解

COUNTIF(A2:A8,A2:A8) 这个函数是怎么用的呢？

COUNTIF（要判断的区域，判断条件），平时最最常用的使用方式其实是=COUNTIF(A2:A8,A2) 就是查找A2（方便面）在整个区域出现了几次。

重新粘贴一下原表格

第一步：{=COUNTIF(A2:A8,A2:A8) } 实现了什么结果呢？就是得到从A2至A8，统计每一个单元格的内容在整个区域出现的次数，返回结果

{1,2,1,1,2,2,2}

第二步：{=1/COUNTIF(A2:A8,A2:A8)} 实现了什么呢？1除以次数，就是把每个物品的个数作为分母，返回值

{1,1/2,1,1,1/2,1/2,1/2,1/2}

第三步：{=SUM(1/COUNTIF(A2:A8,A2:A8))}实现Sum函数将全部数组结果相加，即sum(1,1/2,1,1,1/2,1/2,1/2,1/2)=5

思路2：

直接放解决方案

公式：{=SUM(--(MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1)))}

从简至难：

第一步：ROW(A2:A8)，返回A2至A8的行数，得到数组{2;3;4;5;6;7;8}

第二步：ROW(A2:A8)-1，得到数组{1;2;3;4;5;6;7}

第三步：先解释一下MATCH函数，MATCH函数是返回某个文本在某个数组的第几位，

=MATCH(文本,一个数组区域,0) 我们工作中基本上用到的都是精确匹配，所以记住第三个参数是0就行。

假如编辑的函数是=MATCH(A8,A2:A8,0)，则是查找加多宝在整个数组中的第几位？虽然整个区域中有两个加多宝，但是Excel找到第一个加多宝的时候，就默认已经找到，不会继续查找了，所以=MATCH(A8,A2:A8,0)的返回值是2 而不是7。

所以MATCH(A2:A8,A2:A8,0)，得到数组{1;2;3;4;5;5;2}

重新粘贴一下原表

用MATCH(A2:A8,A2:A8,0)函数得到数组{1;2;3;4;5;5;2}

用ROW(A2:A8)-1函数得到数组{1;2;3;4;5;6;7}

有没有意识到，如果这个物品是第一次出现的时候两个数组的值是一样的，如果这个物品是第二次出现的，两个数组对应的数字就是不一样的。

第四步：MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1)，这样一判断，返回值就是

{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}

第五步：这一步也很重要哦，--(MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1))，这个相当于做了数字运算，将TRUE转化为1，将FALSE转化为0。所以上面的返回值就是{1;1;1;1;1;0;0}

第六步：最后一步就比较简单了，=SUM(--(MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1)))就是=SUM{1;1;1;1;1;0;0}=5

记住！输入公式后同时按住Ctrl+Shift+Enter！

文字描述：用MATCH获取某个物品在整个列表中处于第几个，如果是多个相同的物品，MATCH函数返回的是第一个，比如加多宝，用MATCH返回的就是整个列表的第二个。然后在用ROW函数获取每个单元格所在的行数，行数减去1就是这个单元格在数组中的位置。如果两个数字相等，就是第一次出现，如果不等就不是第一次出现。然后将相等的转化为1，相加就是不重复的物品数量。

到这里我感觉对于实战的帮助其实还不是最大的，最大的应该是把不重复的自动列出来，然后就可以根据自动列出来的数据进行出入库，剩余库存统计。

但是怎么实现这个功能呢？？

老衲用了7*7 49分钟才把这个用函数实现，贴出来让大家感受一下：

{=IF(ROW(A1)<=SUM(1/COUNTIF($A$2:$A$8,$A$2:$A$8)),OFFSET($A$1,LARGE(IF(MATCH($A$2:$A$8,$A$2:$A$8,)=(ROW($A$2:$A$8)-1),MATCH($A$2:$A$8,$A$2:$A$8,),0),ROW(A1)),),"")}

巨长无比，当然我不知道有没有更简单的方法，如果有的话，小伙伴可以分享一下。我把绝对引用都去掉，简单的分析一下这个功能实现的思路

{=IF(ROW(A1)<=SUM(1/COUNTIF(A2:A8,A2:A8)),OFFSET(A1,LARGE(IF(MATCH(A2:A8,A2:A8,)=(ROW(A2:A8)-1),MATCH(A2:A8,A2:A8,),0),ROW(A1)),),"")}

再贴一次图片

先解释一下案例中用到的函数，大部分已经熟知，两个新面孔就是OFFSET函数和LARGE函数。

=OFFSET函数其实是Excel高阶玩家经常用的，广泛应用在数据引用和动态图表中。他的用法比较多，在这里只介绍最简单的用法。

OFFSET函数的功能是以某个单元格为参照系，通过给定偏移量得到新的单元格（本描述为通俗描述，最严谨的可以自行百度）

=OFFSET（坐标原点，垂直偏移几个单元格，水平偏移几个单元格）

比如我如果在新的单元格想要以A1（物品种类）为原点，得到可乐？我需要怎么做？是不是垂直向下移动4个单元格，水平不用偏移？

所以=OFFSET(A1,4,) 返回值就是可乐。

函数的第二个参数数字为正数是向下移动，负数是向上移动；

函数的第三个参数数字为正数是向右移动，负数是向左移动。

LARGE函数，返回数据集中的第K个最大值。用法比较简单

LARGE（数组，第几个大的数），它的姐妹函数是SMALL

基本函数介绍完成，开始拆分函数

{=IF(ROW(A1)<=SUM(1/COUNTIF(A2:A8,A2:A8)),OFFSET(A1,LARGE(IF(MATCH(A2:A8,A2:A8,)=(ROW(A2:A8)-1),MATCH(A2:A8,A2:A8,),0),ROW(A1)),),"")}

第一步：我想在E列罗列出来不重复的物品，首先需要用OFFSET函数实现数据的获取OFFSET(A1，向下偏移多少，）第三个参数如果是0，可以直接为空

第二步：我需要解决的问题就是向下偏移多少怎么定义，如果问题简化，可以允许重复的物品也罗列过来，那偏移的数字定义为1至7就可以，但是因为我需要把重复物品剔除，所以就要判断一下，哪些是我想留下的，哪些不想。使用下面的函数实现

IF(MATCH(A2:A8,A2:A8,)=(ROW(A2:A8)-1),MATCH(A2:A8,A2:A8,),0)

我再拆分一下函数MATCH(A2:A8,A2:A8,)=(ROW(A2:A8)-1)，这个函数前面已经解释过了，如果两个相等，证明这个物品是第一次出现，如果不等，证明不是第一次出现。使用IF语句判断，如果是第一次出现的，就返回这个单元格对应在数组中的位置，否则返回0。

第三步：我将第二步，反向拆解

MATCH(A2:A8,A2:A8,)=(ROW(A2:A8)-1)返回值是{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}

MATCH(A2:A8,A2:A8,)返回值是{1;2;3;4;5;5;2}

则IF(MATCH(A2:A8,A2:A8,)=(ROW(A2:A8)-1),MATCH(A2:A8,A2:A8,),0)返回值是{1;2;3;4;5;0;0}

将偏移的范围变为了1至5.

现在套入OFFSET函数，OFFSET（A1，返回值是1至5，），但是每个单元格只能返回一个值，我怎么来保证E列每个单元格的返回值是不一样的呢？

用到了LARGE函数，OFFSET(A1,LARGE({1;2;3;4;5;0;0},ROW(A1)),)

第一个单元格是ROW（A1）=1，则第一个单元格返回的是最大的，双击单元格向下填充函数，第二个单元格就会变为OFFSET(A1,LARGE({1;2;3;4;5;0;0},ROW(A2)),)

则第二个单元格偏移量就是第二大的数字。

至此基本实现了将不重复的物品罗列的功能。

第四步：如果将OFFSET直接向下双击填充，会发现在E7单元格的时候，函数会变为OFFSET(A1,LARGE({1;2;3;4;5;0;0},ROW(A6)),)=OFFSET(A1,LARGE({1;2;3;4;5;0;0},6),)=OFFSET(A1,0,)=物品种类

因为第六个大的数字是0，则后面的单元格都填充为A1单元格的内容，为了优化函数，我需要加个判断语句，即如果返回的数量小于等于不重复的总数量的时候，返回偏移的结果，否则就返回一个空白的文本""。

函数实现：=IF(ROW(A1)<=SUM(1/COUNTIF(A2:A8,A2:A8)),正常返回偏移结果,"")

SUM(1/COUNTIF(A2:A8,A2:A8)就是统计不重复的物品数量。然后IF语句判断，决定返回值！

最后把不想变的都用$锁住，形成超长的公式：{=IF(ROW(A1)<=SUM(1/COUNTIF($A$2:$A$8,$A$2:$A$8)),OFFSET($A$1,LARGE(IF(MATCH($A$2:$A$8,$A$2:$A$8,)=(ROW($A$2:$A$8)-1),MATCH($A$2:$A$8,$A$2:$A$8,),0),ROW(A1)),),"")}

感觉好累好难！

感谢收看！下期不见不散！