小伙伴们好啊,情人节过的咋样?
据说昨天的Excel曾经一度出现了严重Bug,主要有两种比较悲催的情况,首先是这种:
更加悲催的是这种:
言归正传,今天和大家分享与不重复数据计算有关的内容,这个在工作中经常遇到哦。
来看看我的手工台账:
小本买卖,上面都是便利店的王牌销售产品哦。
随着种类的不断丰富,我想知道我进货的种类一共有多少,怎么做?
思路一
文字描述:
找到每一个品种在整个列表中有几个,如果有N个,则自己的数字变为1/N,N个1/N相加等于1。
公式:
{=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返回的就是2。然后在用ROW函数获取每个单元格所在的行数,行数减去1就是这个单元格在数组中的位置。
如果两个数字相等,就是第一次出现,如果不等就不是第一次出现。
然后将相等的转化为1,相加就是不重复的物品数量。
好了,今天的内容就先和大家分享这么多,小伙伴们明天见~~
图文作者:阿忠凯
联系客服