|
一、案例 如下圖所示，A1：D14區(qū)域?yàn)槟彻S三月份各車間員工加班情況表。現(xiàn)在要求在F2單元格統(tǒng)計(jì)三月共有多少名員工加班。
從B列可以看出，同一員工在三月份多次加班。統(tǒng)計(jì)三月共有多少名員工加班，實(shí)際是統(tǒng)計(jì)B2：B14非重復(fù)值的個(gè)數(shù)。 二、操作步驟 方法一、sumproduct+countif函數(shù) 在F2單元格輸入公式 =SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14))
公式解析： （1）countif函數(shù)用于統(tǒng)計(jì)滿足給定條件的單元格數(shù)目，語法為countif(range,criteria)。COUNTIF(B2:B14,B2:B14)的range和criteria參數(shù)均為B2：B14，函數(shù)返回的結(jié)果為數(shù)組{3;1;3;3;1;1;2;2;3;1;3;3;1}。每個(gè)數(shù)字代表對(duì)應(yīng)的名字出現(xiàn)的次數(shù)，比如“張華”出現(xiàn)3次，“林桂香”出現(xiàn)1次。 （2）1/COUNTIF(B2:B14,B2:B14)用1除以countif生成的數(shù)組，結(jié)果得到一組數(shù)組{0.333333333333333;1;0.333333333333333;0.333333333333333;1;1;0.5;0.5;0.333333333333333;1;0.333333333333333;0.333333333333333;1}。 只出現(xiàn)一次的名字1/COUNTIF(B2:B14,B2:B14)結(jié)果為1，比如“林桂香”、“張桂香”、“盧文秀”。出現(xiàn)多次的名字1/COUNTIF(B2:B14,B2:B14)結(jié)果為1/n（n為出現(xiàn)次數(shù)），比如“張華”出現(xiàn)3次，1/COUNTIF(B2:B14,B2:B14)=1/3=0.333333333333333，出現(xiàn)3次就有3個(gè)1/3。 （3）SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14))對(duì)1/COUNTIF(B2:B14,B2:B14)生成的數(shù)組求和，最終返回B2：B14區(qū)域非重復(fù)值的個(gè)數(shù)。 方法二、sumproduct+frequency函數(shù) 在單元格F2輸入公式 =SUMPRODUCT(--(FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)>0))
公式解析： （1）match函數(shù)用于返回特定值在區(qū)域中的相對(duì)位置，語法為 match(lookup_value,lookup_array,[match_type])。MATCH(B2:B14,B2:B14,0)中l(wèi)ookup_value和lookup_array參數(shù)均為B2：B14，表示返回B2：B14區(qū)域每一個(gè)姓名在B2：B14區(qū)域的相對(duì)位置。Match函數(shù)返回的數(shù)組為{1;2;1;4;5;6;7;7;4;10;1;4;13}。當(dāng)姓名不止出現(xiàn)一次時(shí)，match函數(shù)返回第1次出現(xiàn)的位置，比如“張華”出現(xiàn)在B2：B14區(qū)域的第1、3、11行，match返回的結(jié)果均為1。 （2）ROW(B2:B14)-ROW(B2)+1返回一組序列值{1;2;3;4;5;6;7;8;9;10;11;12;13}。 （3）frequency函數(shù)返回一組數(shù)據(jù)的頻率分布，語法為frequency(data_array,bins_array)。FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)，參數(shù)data_array為match函數(shù)生成的數(shù)組，參數(shù)bins_array為row函數(shù)生成的數(shù)組。Frequency函數(shù)生成數(shù)組{3;1;0;3;1;1;2;0;0;1;0;0;1;0}，“張華”出現(xiàn)3次，“林桂香”出現(xiàn)1次。當(dāng)某個(gè)姓名已經(jīng)被統(tǒng)計(jì)出現(xiàn)次數(shù)時(shí)，再次出現(xiàn)時(shí)，frequency函數(shù)統(tǒng)計(jì)0次，例如B2單元格的“張華”frequency結(jié)果為3，B4單元格的“張華”frequency結(jié)果為0。 （4）FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)>0結(jié)果為{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}。True的個(gè)數(shù)就是非重復(fù)值的個(gè)數(shù)。 （5）SUMPRODUCT(--(FREQUENCY(MATCH(B2:B14,B2:B14,0),ROW(B2:B14)-ROW(B2)+1)>0))，兩個(gè)負(fù)號(hào)（--）將True轉(zhuǎn)為1，F(xiàn)alse轉(zhuǎn)為0，然后用sumproduct函數(shù)求和，最終得到的結(jié)果就是True的個(gè)數(shù)，即非重復(fù)值的個(gè)數(shù)。 方法一的公式比方法二簡(jiǎn)單。但方法一適用于數(shù)據(jù)量較小的統(tǒng)計(jì)，方法二適用于數(shù)據(jù)量較大的統(tǒng)計(jì)。 |
|
|
