Excel 表单里储存的数据,经常会出现重复值,比如员工表或客户表的城市一栏,会反复出现一些城市的名字。有的时候,我们想知道有员工或客户的城市的数量,也就是求唯一值的数量。我们可以用很多方法来做,比如用 Advanced Filter 功能来列出唯一的城市名再计算总数, 除此之外,我们还可以用公式来求这个唯一值的数量。
在讲用公式求唯一值的技巧前,我们先要了解一下Frequency 这个函数,这个函数是用来计算各个分组内数据出现的频率的。语法:
FREQUENCY(data_array,bins_array)
第一个参数data_array 好理解,就是原始的包含重复值也好,不包含重复值也好的一列数据。第二个参数 bins_array 表示拿来做标准对原始数据进行分组的一列数据,返回的结果就是每组里的项目的数量。看一个例子:
A | B | C | D | E | F | G | |
1 | score | Bin1 | Bin2 | Bin3 | |||
2 | 80 | 60 | 2 | 95 | 3 | 80 | 3 |
3 | 79 | 70 | 0 | 85 | 3 | 85 | 1 |
4 | 72 | 80 | 3 | 75 | 3 | 70 | 2 |
5 | 60 | 90 | 2 | 1 | 93 | 2 | |
6 | 55 | 3 | 2 | ||||
7 | 91 | ||||||
8 | 84 | ||||||
9 | 95 | ||||||
10 | 88 | ||||||
11 | 97 |
A 栏是某门考试的分数,现在我们要考察分数段的人数分布。B 栏是第一个考察标准,小于等于60分的,大于60小于等于70分的,大于70小 于等于80分的,大于80小于等于90分的,还有90分以上的。Frequency 这个函数的工作原理就是这样的,它返回的分组数比第二个参数里的元素数多1。这很好理解,一刀切下去是分成两块,两刀分成三块,得到的分组数总是比步长数多1。
知道这个规则后,我们选择C2:C6 的单元格,输入=FREQUENCY(A2:A11,B2:B5),按CTRL+SHIFT+ENTER 作为数组公式输入。结果如上表所述。
再拿D 栏的Bin2 做次练习。这次的标准是小于等于75分的,大于75小于等于85分的,大于85小于等于95分的,以及大于95分的。注意Bin2 是从高往低排列的,和Bin1有所不同。
选择E2:E5 的单元格,输入=FREQUENCY(A2:A11,D2:D4)。
前两个练习,分组都是从小到大,或从大到小排列的。如果我们的分组没有顺序,乱写一气呢?其实也没有问题,Frequency 函数自己会内部做个排序,总之使分组是头尾相衔的。看一下Bin3。
选择G2:G6,输入=FREQUENCY(A2:A11,F2:F5)。核对一下结果:大于70小于等于80分的有三个,大于80小于等于85分 的有一个,小于等于70分的有两个,大于85小于等于93分的有两个,大于93分的有两个。
Frequency 函数在统计分组内的元素数量时,会忽略空白的单元格和包含文本的单元格。这对我们是个约束,下面通过例子来看一下如何利用它并结合其它函数统计出唯一值。
A | B | C | D | |
1 | 996 | 1 | 上海 | 1 |
2 | Dodsworth | 2 | 北京 | 2 |
3 | 67 | 1 | 杭州 | 3 |
4 | 1 | 武汉 | 4 | |
5 | Buchanan | 0 | 北京 | 2 |
6 | 689 | 0 | 广州 | 6 |
7 | Dodsworth | #N/A | 上海 | 1 |
8 | 56 | #N/A | 上海 | 1 |
9 | 67 | #N/A | 杭州 | 3 |
10 | #N/A | 南京 | 10 | |
11 |
先看A 栏,里面有文本,有数字,有空白单元。前面的例子,原始数据和分组标准来自不同的两列,如果这两个参数都指向原始数据呢?那就是得出原始数列中每个元素出现的次数。
选择B1:B10,输入=FREQUENCY(A1:A9,A1:A9),看一下结果:996有一个,67有两个,但是不是和A 栏的67 显示在同一行,因为67 上面有 个包含文本的单元格,在结果集里,Excel 把非数字的单元格给剔除了再排列的,相当于{(996,1),(67,2),(689,1),(56,1)}。67这个值第二 次出现的时候就对应0 了(Frequency 函数对于某个特定值第一次出现时,返回这个值出现的次数,这个值再出现时,就返回0 了),比最大的 值还大的元素没有,所以是0,这就是两个0 的意义。三个有文本的单元格和一个空白的单元格函数不能处理,所以返回#N/A,这是四个#N/A 的意思。
到现在为止,我们已经得出每个数值元素出现的频率。不管出现一次,两次还是好多次,在计算唯一值的时候都只计数一次,我们可以用个 小技巧,用IF 函数来做个映射,只要出现了,即频率大于等于1,就计数一次,=IF(FREQUENCY(A1:A9,A1:A9)>0, 1)。最后,再用SUM 把 总个数加总,
=SUM(IF(FREQUENCY(A1:A9,A1:A9)>0, 1))。
对于纯数值型的数列,用上面的公式,我们算是完成了统计唯一值数量的任务,但是我们的工作中更多的时候是要统计字符串变量的唯一值 。我们还需要一些技巧。
现在让我们统计C 栏中唯一的城市数。
既然Frequency 函数不处理文本,只处理数值,我们就需要一个函数把文本映射成数值,Match 函数可以担当这个重任,它可以把每个文本 值跟这个值所处的位置对应起来。
选择D1:D10,输入=MATCH(C1:C10,C1:C10,0),返回值表示某个城市在整个数列中(C1:C10)对应的位置。当这个城市再次出现时,返回的 位置还是第一次出现时的位置。
完成这步后,就可以把我们前面刚刚学到的接上去用了。城市的反复出现映射成了某个代表位置的数值的反复出现,Frequency 函数计算出现频率,IF 函数做个是否出现和一次计数的映射,最后用SUM 计算总数。我们的结果就是:
=SUM( IF( FREQUENCY(MATCH(C1:C10, C1:C10, 0), MATCH(C1:C10, C1:C10, 0))>0, 1) )