Excel公式： 獲取非連續(xù)單元格區(qū)域中只出現(xiàn)一次的數(shù)字
本次的練習(xí)是：如下圖1所示，有一組非連續(xù)的單元格區(qū)域，由任意數(shù)量的單列區(qū)域組成，每個區(qū)域中的值有數(shù)字、文本或空格。要求從單元格A2開始，使用公式生成一個列表，這個列表由上述非連續(xù)單元格區(qū)域中所有只出現(xiàn)了一次的數(shù)字組成（如圖1所示，1、2和9這三個數(shù)字在非連續(xù)的單元格區(qū)域中只出現(xiàn)了一次）。
圖1
注意，雖然圖1中在單元格區(qū)域C1:N12中有很多單元格為空，但解決方案的公式中要考慮這些單元格也可能存在數(shù)據(jù)的情況。
先不看答案，自已動手試一試。
公式
在單元格A2中輸入公式：
=IF(ROWS(A$2:A2)>$A$1,””,AGGREGATE(15,6,(ROW(INDIRECT(“1:”& MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT(“1:” &MAX(RNG)+1))-1)=1),ROWS(A$2:A2)))
下拉直至出現(xiàn)空單元格為止。
在單元格A1中，公式：
=SUMPRODUCT(–(FREQUENCY(RNG,ROW(INDIRECT(“1:” &MAX(RNG)+1))-1)=1))
計(jì)算該非連續(xù)單元格區(qū)域中滿足要求的數(shù)字?jǐn)?shù)量。
公式解析
公式中的RNG是定義的名稱。
名稱：RNG
引用位置：=$C$2:$C$7,$E$2:$E$4,$E$6:$E$9,$G$3:$G$11,$J$1:$J$9,$L$5:$L$6,$N$3:$N$7,$N$9:$N$10,$N$12
注意，這個聯(lián)合的單元格區(qū)域并不能傳遞給所有的工作表函數(shù)，但還是有些工作表函數(shù)能夠處理它們。
1. 首先，看看單元格A1中返回滿足要求的數(shù)字?jǐn)?shù)量的公式：
=SUMPRODUCT(–(FREQUENCY(RNG,ROW(INDIRECT(“1:”&MAX(RNG)+1))-1)=1))
這里的重點(diǎn)是使用FREQUENCY函數(shù)，該函數(shù)是非常有用的一個函數(shù)，能夠處理這種不連續(xù)的單元格區(qū)域。
另一個函數(shù)是MAX函數(shù)，也可以操作多個、非連續(xù)的單元格區(qū)域，因此：
MAX(RNG)
能夠得到組成RNG的單元格區(qū)域中所有數(shù)值的最大值，忽略邏輯值、文本。很顯然，其返回的結(jié)果是9。
這樣，公式中的：
ROW(INDIRECT(“1:”&MAX(RNG)+1))-1
轉(zhuǎn)換成：
ROW(INDIRECT(“1:”&9+1))-1
轉(zhuǎn)換成：
ROW(INDIRECT(“1:”&10))-1
轉(zhuǎn)換成：
{1;2;3;4;5;6;7;8;9;10}-1
結(jié)果為：
{0;1;2;3;4;5;6;7;8;9}
這里，我們創(chuàng)建了一個由0到區(qū)域中最大值的數(shù)值組成的數(shù)組，用于FREQUENCY函數(shù)的參數(shù)bins_array。
此時，公式中的：
FREQUENCY(RNG,ROW(INDIRECT(“1:”&MAX(RNG)+1))-1)
成為：
FREQUENCY(RNG,{0;1;2;3;4;5;6;7;8;9})
結(jié)果為：
{0;1;1;0;0;5;2;2;0;1;0}
因此，公式：
=SUMPRODUCT(–(FREQUENCY(RNG,ROW(INDIRECT(“1:”&MAX(RNG)+1))-1)=1))
可轉(zhuǎn)換為：
=SUMPRODUCT(–({0;1;1;0;0;5;2;2;0;1;0}=1))
轉(zhuǎn)換為：
=SUMPRODUCT(–({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}))
兩個減號強(qiáng)迫TRUE/FALSE轉(zhuǎn)換成1/0，即：
=SUMPRODUCT({0;1;1;0;0;0;0;0;0;1;0})
結(jié)果為3。
2. 下面來看看從單元格A2開始用來獲取值的公式：
=IF(ROWS(A$2:A2)>$A$1,””,AGGREGATE(15,6,(ROW(INDIRECT(“1:” &MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT(“1:” &MAX(RNG)+1))-1)=1),ROWS(A$2:A2)))
根據(jù)前面公式推導(dǎo)的內(nèi)容，上面的公式中：
AGGREGATE(15,6,(ROW(INDIRECT(“1:”& MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT(“1:” &MAX(RNG)+1))-1)=1),ROWS(A$2:A2))
可以轉(zhuǎn)換為：
AGGREGATE(15,6,({0;1;2;3;4;5;6;7;8;9})/({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}),ROWS(A$2:A2))
這是以這種方式使用AGGREGATE函數(shù)時要注意的關(guān)鍵技術(shù)。因?yàn)槿绻覀冊诖撕瘮?shù)中將第二個參數(shù)options設(shè)置為6，即“忽略錯誤值”，那么它將恰好做到這一點(diǎn)。
上述公式可轉(zhuǎn)換為：
AGGREGATE(15,6,{#DIV/0!;1;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;9;#N/A},ROWS(A$2:A2))
其第一個參數(shù)function_num被設(shè)置成15，等價于執(zhí)行SMALL函數(shù)。（你可能想，為什么不將第一個參數(shù)設(shè)置成5，即MIN，這是不合適的。因?yàn)樵贏GGREGATE函數(shù)的第一個參數(shù)的所有可選項(xiàng)中，僅14-15能夠保證在傳遞給函數(shù)的數(shù)組不是實(shí)際的工作表區(qū)域時能正常運(yùn)行，而這里的數(shù)組是由其他函數(shù)生成的，如果設(shè)置成1-13中的任一個，則需要傳遞給函數(shù)的數(shù)組是實(shí)際的工作表區(qū)域。）
對于單元格A2的公式中來說，最后一個參數(shù)k的值是1，即ROWS(A$2:A2)的返回值。因此，AGGREGATE函數(shù)部分轉(zhuǎn)換為：
AGGREGATE(15,6,{#DIV/0!;1;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;9;#N/A},1)
結(jié)果為1。
這樣，單元格A2中的公式轉(zhuǎn)換為：
=IF(1>$A$1,””,1)
即：
=IF(1>3,””,1)
結(jié)果為1。
