数据分段统计神器! Excel中的隐藏宝藏函数FREQUENCY很强大


    今天跟大家分享的是Excel中的隐藏宝藏函数FREQUENCY，简直就是数据分段统计神器！只需几步，轻轻松松搞定数据分组，效率提升不是一星半点，同事都惊呆了！
    FREQUENCY函数介绍：
    
  • 功能：FREQUENCY函数主要是计算值在某个范围内出现的频率, 然后返回一个垂直的数字数组。
  • 语法：FREQUENCY(一组数值,一组间隔值)

    Frequency函数的作用与参数
    Frequency函数：计算数字出现的频率，然后以一列垂直数组返回结果。
    
    语法：=FREQUENCY(data_array, bins_array)
    第一参数：data_array，需要计算频率的数据区域第二参数：bins_array，计算频率的间隔点以上就是这个函数的作用与参数，相信很多粉丝看到这里都是云里雾里的，不要担心，我通过一个例子，来具体分析下这个函数
    应用场景 场景一：统计学员各年龄段人数
    如下图所示，左侧是学员信息表，要统计这些学员在各年龄段人数，分三个年龄段来统计人数“18-26”、“27-35”、“36以上”，这三个年龄段的分界点是26和35。
    
    只需在目标单元格中输入公式：
    =FREQUENCY(B2:B8,F2:F3)
    然后点击回车即可
    
    解读：
    
    FREQUENCY函数的第1参数是B2:B8单元格区域是学员年龄数据；第2参数是F2:F3单元格区域是年龄段的分界点26和35，函数会统计年龄数据中小于等于当前分界点，同时大于上一分界点的数量。
    当然如果想统计男性学员在每个年龄段的人数，只需修改公式为：
    =FREQUENCY(IF(C2:C8="男",B2:B8,""),F2:F3)
    上面公式利用IF函数想判断是否为男性，如果成立返回对应的年龄，否则返回空值。
    
    场景二：统计连续到岗最多天数
    如下图所示，表格中是员工10.1假期加班情况，需要统计每个员工连续加班到岗最大天数。
    
    只需在目标单元格中输入公式：
    =MAX(FREQUENCY(IF(B2:I2="√",COLUMN(B:I)),IF(B2:I2<>"√",COLUMN(B:I))))
    然后点击回车即可
    
    
    解读：
    
  • ①IF(B2:I2="√",COLUMN(B:I))部分使用IF函数判断B2:I2单元格区域中是否是"√"，如果是说明加班到岗，公式返回相应单元格的列号，否则返回逻辑值FALSE。
  • 结果返回一个内存数组：{2,3,4,FALSE,FALSE,7,FALSE,FALSE}
  • ②IF(B2:I2<>"√",COLUMN(B:I))部分的计算规则与上一个IF函数相反，在B2:I2单元格区域中不是"√"（没有加班））时返回对应的列号，否则（加班到岗）返回逻辑值FALSE。
  • 结果返回一个内存数组：{FALSE,FALSE,FALSE,5,6,FALSE,8,9}
  • ③然后借助FREQUENCY函数忽略参数中逻辑值的特点，以不加班对应的列号{5,6,8,9}为分组间隔值，统计加班到岗对应的列号{2,3,4,7}在各个分组中的数量，相当于分别统计在两个不加班列号之间有多少个加班的列号。
  • 结果返回一个垂直内存数组：{3;0;1;0;0}
  • ④最后使用MAX函数从中提取出最大值，就是连续加班到岗最大天数。

    通用公式：
    当然，如果觉得上面公式计算逻辑过程不容易理解，可以记住按条件统计频率个数的通用公式，到时直接套用即可：
    =FREQUENCY(IF(符合条件,相应列号),IF(不符合条件,相应列号))
    实例分析 案例1：满足条件的个数
    我们可以使用COUNTIF来处理，也可以使用FREQUENCY！
    
    案例2：去重计数
    这里主要利用我们前面说过的一个点，FREQUENCY只会在第一次出现的间隔值上统计次数，第二次0,0/0会报错，我们统计一下最后有几个数值就是想要的结果
    
    我们拆解看一下过程，方便大家理解！
    第一次出现19，计数是2，第二次出现是0,最后一个大于20，没有对应的内容,结果是0，这样用0除以这组数，就可以得到不报错的个数或者或还是数值的个数就是不重复个数！
    
    案例3：
    如下图所示，我们想要根据【考核得分】来计算下每个区间的人数，如果使用FREQUENCY函数的话，是需要设置一个【分割点】的辅助表的，表格设置之后，直接设置函数即可
    公式：=FREQUENCY(B2:B17,H2:H4)
    
    跟大家简单的讲解下这函数，公式为：
    =FREQUENCY(B2:B17,H2:H4)
    
  • 第一参数：B2:B17，就是需要统计数据的区域
  • 第二参数：H2:H4，就是构建的分割点表格

    FREQUENCY函数会根据分割点来统计每个区间的数字个数，在这里可能很多人会疑问：为什么四个区域仅仅需要3个分割点呢？
    在这里我们可以将数据看作是一条直线，想要将它划分为四段。将一条直线划分为四段，我们仅仅需要设置3个分割点就能做到了，这个也就是为什么分割点只有3个原因，效果如下图所示。
    
    还有一点需要注意的是FREQUENCY进行区间统计，分割点会是包含在对应区间的，比如在这里第一个分割点是60，这个60会计算在第一个区间就是考核得分≤60这个区间，而不会将60计算在【60-80】这个区间，以上就是FREQUENCY函数的计算规则，理解起来可能还是有一定难度的，下面我们来看下他都能解决哪些问题
    实先声明下，如果你的基础不好，看到这里就可以了，下面能看懂的估计也已经是Excel大神了
    案例4：统计指定数字的个数
    如下图，我们想要统计下【60-80】区间的数值个数，只需要将函数设置为：
    =INDEX(FREQUENCY(B2:B17,{60,80}),2)
    在这里{60,80}就表示分割点，FREQUENCY函数会得到3个结果，我们利用index函数将第二个结果取出来，它就是我们需要的结果
    
    案例5：计算最大连续次数
    如下图所示，我们想要计算下班级的最大连续次数，就是计算下图黄色区域3班的个数，公式为：
    =MAX(FREQUENCY(ROW(2:17),IF(B2:B17<>B3:B18,ROW(2:17))))
    这个计算过程还是比较复杂的主要是利用了IF函数来判断出相同的数据，然后再利用FREQUENCY获取相同数据的个数，最后使用max函数取出最大值
    
    案例6：查找最近接的值
    如下图所示，我们想要在表格中查找最接近56的数字是多少，只需要将函数设置为：
    =LOOKUP(1,0/FREQUENCY(0,ABS(C2:C11-F2)),C2:C11)
    在FREQUENCY函数中第一参数是0，ABS(C2:C11-F2)求【考核得分】这一列与56的绝对值，将这个绝对值作为分割点，这样的话只有第一个分割点是有数据的，其余的都是没有的。
    
    注意事项
    1.因为FREQUENCY函数的结果是一个数组公式，我们需要先选择数据区域，然后输入公式，按下Ctrl+Shift+回车来三键填充公式，需要注意的是选择的数据区域要比第二参数分割点多一个单元格,如下动图所示
    
    2. FREQUENCY会自动的文本与空白单元额忽略掉，并且在进行数据统计的数据，会自动的根据第二参数的【分割点】来对第一参数【统计区域】进行升序排序。
相关文章！