物料管理小能手(统计不重复数据)

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

如果是用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)),),"")}

感觉好累好难!

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

原文发布于微信公众号 - 阿凯的Excel(akexcel)

原文发表时间:2017-02-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏阿凯的Excel

巧妙解决二维表信息匹配问题

1152
来自专栏李成熙heyli

babel到底将代码转换成什么鸟样?

前言 将babel捧作前端一个划时代的工具一定也不为过,它的出现让许多程序员幸福地用上了es6新语法。但你就这么放心地让babel跑在外网?反正我是不放心,我就...

30810
来自专栏Stone的专栏

一篇文章精通 VLOOKUP 函数

相信不少人看到标题,立即嗤之以鼻,VLOOKUP 谁不会?是的,大家都会,但用的好的人不多。相信我,这篇文章一定可以算得上通俗易懂,又有深度的一篇文章,熟练掌握...

960
来自专栏CDA数据分析师

新人必备!15个常用EXCEL函数

本文实际涵盖了15个Excel常用函数,但是按照分类只分了十类。 很难说哪十个函数就绝对最常用,但这么多年来人们的经验总结,一些函数总是会重复出现的。 这些函数...

1818
来自专栏老码农专栏

原 荐 OSGL 工具库 - Java 字串

1424
来自专栏恰同学骚年

设计模式的征途—23.解释器(Interpreter)模式

虽然目前计算机编程语言有好几百种,但有时人们还是希望用一些简单的语言来实现特定的操作,只需要向计算机输入一个句子或文件,就能按照预定的文法规则来对句子或文件进行...

692
来自专栏xingoo, 一个梦想做发明家的程序员

实用的JS代码段(表单篇)

整理了下比较实用的Javascript代码段,完整的代码参考 1 多个window.onload方法   由于onload方法时在页面加载完成后,自动调用...

1695
来自专栏携程技术中心

干货 | Kotlin超棒的语言特性

1214
来自专栏web前端教室

javascript 红皮高程(18)-- 布尔操作符

可算是把绕来绕去的二进制-位操作符,给学完了。至少我学到了十之八九,你呢,,, 接下来是布尔操作符,它一共有三个,非(NOT),与(AND),或(OR)。 1,...

1839
来自专栏软件开发

Excel基础

一、基础 一个Excel文档称为工作簿(workbook)、一个工作簿中可以包含多个工作表(sheet) ? ctrl+向右箭头  查看最后一列 ctrl+向...

1625

扫描关注云+社区