中国4200万家企业需要精益生产;全球70亿人都需要精益思维;
学精益,就上环球精益网
  • 精益管理名词解释大全

    本月热词:

    栏目分类
    热门物料管理文章推荐

    主页 > 物料管理 > INTRODUCE

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

    2019-11-03 14:24 作者:阿忠凯 来源: 浏览: 我要评论 (条) 字号:

    摘要:平时的仓库物料管理,有很多种材料要进进出出。 如果是用Excel做手工台账的,可以看看我的分享! 我有手工台账如下: 小本买卖,上面都是便利店的王牌销售产品! 随着种类的不断丰富,我想知道我进货的种类一共有多少! 怎么做? 思路一: 文字描述: 找到每

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

    如果是用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基础知识分享!欢迎长按下面图片关注公众号!

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

    (责任编辑:环球精益网)
    顶一下
    (0)
    0%
    踩一下
    (0)
    0%
    ------分隔线----------------------------
    特别说明

    此处放横条广告

    ◎最新评论
        谈谈您对该文章的看
        表  情:
        评论内容:
        * 请注意用语文明且合法,谢谢合作 审核后才会显示! Ctrl+回车 可以直接发表

        精益疑问
        免费咨询

        一键加群交流

        石老师

        18970479044