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