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

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

如果是用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 条评论
登录 后参与评论

相关文章

来自专栏ACM算法日常

当七夕遇上算法竞赛

  七夕节因牛郎织女的传说而被扣上了「情人节」的帽子。于是TYVJ今年举办了一次线下七夕祭。Vani同学今年成功邀请到了cl同学陪他来共度七夕,于是他们决定去T...

642
来自专栏数据库

《数据库系统概念》15-可扩展动态散列

静态散列要求桶的数目始终固定,那么在确定桶数目和选择散列函数时,如果桶数目过小,随着数据量增加,性能会降低;如果留一定余量,又会带来空间的浪费;或者定期重组散列...

2037
来自专栏菩提树下的杨过

AS3:小游戏“贪吃蛇”的实现

前几天在园子里看到有人用Silverlight做了一个"贪吃蛇",一时兴起也想用AS3.0做一个,虽然这个游戏已经被很多开发者做烂了,但是作为AS的初学者,重新...

3197
来自专栏Golang语言社区

go语言mongdb管道使用(二)

原始代码: /* 重点项目实体机需求汇总 查询数据 */ func (this *IndexController) ProjectReqTotalData(...

3137
来自专栏ascii0x03的安全笔记

【C】用C语言提取bmp图片像素,并进行K-means聚类分析——容易遇到的问题

关于bmp图片的格式,网上有很多文章,具体可以参考百度百科,也有例子程序。这里只提要注意的问题。 (1)结构体定义问题:首先按照百度百科介绍的定义了结构体,但是...

4306
来自专栏用户2442861的专栏

CSDN-markdown基本语法说明

MathJax是一款运行在浏览器中的开源的数学符号渲染引擎,使用MathJax可以方便的在浏览器中显示数学公式,不需要使用图片。这篇文章介绍如何使用LaTeX...

772
来自专栏ml

斗地主算法

       不得不承认,算法搁置了一些时间,代码的风格下降了好多!  贴上一个曹点多多且丑的代码!  Orz...  题目要求:      编码:3表示3点 ...

5818
来自专栏王亚昌的专栏

A*算法C实现

参考 http://www.cppblog.com/christanxw/archive/2006/04/07/5126.html 实现了A*算法,模拟了一下,...

742
来自专栏GreenLeaves

Oracle计算时间差函数

1、months_between(date1,date2)  返回两个日期之间的月份的差值 (1)、如果两个日期月份内天数相同,或者都是某个月的最后一天,返回一...

1696
来自专栏应兆康的专栏

100个Numpy练习【5】

Numpy是Python做数据分析必须掌握的基础库之一,非常适合刚学习完Numpy基础的同学,完成以下习题可以帮助你更好的掌握这个基础库。

52410

扫码关注云+社区