|
一、案例 如下图所示，A1：D14区域为某工厂三月份各车间员工加班情况表。现在要求在F2单元格统计三月共有多少名员工加班。
从B列可以看出，同一员工在三月份多次加班。统计三月共有多少名员工加班，实际是统计B2：B14非重复值的个数。 二、操作步骤 方法一、sumproduct+countif函数 在F2单元格输入公式 =SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14))
公式解析： （1）countif函数用于统计满足给定条件的单元格数目，语法为countif(range,criteria)。COUNTIF(B2:B14,B2:B14)的range和criteria参数均为B2：B14，函数返回的结果为数组{3;1;3;3;1;1;2;2;3;1;3;3;1}。每个数字代表对应的名字出现的次数，比如“张华”出现3次，“林桂香”出现1次。 （2）1/COUNTIF(B2:B14,B2:B14)用1除以countif生成的数组，结果得到一组数组{0.333333333333333;1;0.333333333333333;0.333333333333333;1;1;0.5;0.5;0.333333333333333;1;0.333333333333333;0.333333333333333;1}。 只出现一次的名字1/COUNTIF(B2:B14,B2:B14)结果为1，比如“林桂香”、“张桂香”、“卢文秀”。出现多次的名字1/COUNTIF(B2:B14,B2:B14)结果为1/n（n为出现次数），比如“张华”出现3次，1/COUNTIF(B2:B14,B2:B14)=1/3=0.333333333333333，出现3次就有3个1/3。 （3）SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14))对1/COUNTIF(B2:B14,B2:B14)生成的数组求和，最终返回B2：B14区域非重复值的个数。 方法二、sumproduct+frequency函数 在单元格F2输入公式 =SUMPRODUCT(--(FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)>0))
公式解析： （1）match函数用于返回特定值在区域中的相对位置，语法为 match(lookup_value,lookup_array,[match_type])。MATCH(B2:B14,B2:B14,0)中lookup_value和lookup_array参数均为B2：B14，表示返回B2：B14区域每一个姓名在B2：B14区域的相对位置。Match函数返回的数组为{1;2;1;4;5;6;7;7;4;10;1;4;13}。当姓名不止出现一次时，match函数返回第1次出现的位置，比如“张华”出现在B2：B14区域的第1、3、11行，match返回的结果均为1。 （2）ROW(B2:B14)-ROW(B2)+1返回一组序列值{1;2;3;4;5;6;7;8;9;10;11;12;13}。 （3）frequency函数返回一组数据的频率分布，语法为frequency(data_array,bins_array)。FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)，参数data_array为match函数生成的数组，参数bins_array为row函数生成的数组。Frequency函数生成数组{3;1;0;3;1;1;2;0;0;1;0;0;1;0}，“张华”出现3次，“林桂香”出现1次。当某个姓名已经被统计出现次数时，再次出现时，frequency函数统计0次，例如B2单元格的“张华”frequency结果为3，B4单元格的“张华”frequency结果为0。 （4）FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)>0结果为{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}。True的个数就是非重复值的个数。 （5）SUMPRODUCT(--(FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)>0))，两个负号（--）将True转为1，False转为0，然后用sumproduct函数求和，最终得到的结果就是True的个数，即非重复值的个数。 方法一的公式比方法二简单。但方法一适用于数据量较小的统计，方法二适用于数据量较大的统计。 |
|
|
