Ⅰ excel公式技巧從單元格區域的字元串中提取唯一值
如下圖1所示，在單元格區域A1:A10中有一些數據。現在，想從該區域中提取單詞並創建唯一值列表，如列B中的數據所示。
圖1
可以在單元格B1中編寫一個公式，向下拖拉以創建該唯一值列表。如何編寫這個公式呢？
先不看答案，自已動手試一試。
公式
在單元格B1中輸入數組公式：
=IF(ROWS($1:1)>$C$1,」」,INDEX(Arry3,SMALL(IF(FREQUENCY(IF(Arry3>」」,MATCH(Arry3,Arry3,0)),Arry2),Arry2),ROWS($1:1))))
下拉直至出現空單元格為止。
在單元格C1中，下面的數組公式：
=SUM((Arry3>」」)/MMULT(0+(Arry3=TRANSPOSE(Arry3)),ROW(INDIRECT(「1:」& COUNTA(Arry3)))^0))
計算單元格區域A1:A10中不重復的單個單詞的數量。
公式解析
公式中的Arry1、Arry2、Arry3是定義的三個名稱。
名稱：Arry1
引用位置：=1+LEN(Data)-LEN(SUBSTITUTE(Data,」」,」」))
名稱：Arry2
引用位置：=ROW(INDIRECT(「1:」 &(MAX(Arry1)*ROWS(Data))))
名稱：Arry3
引用位置：=INDEX(TRIM(MID(SUBSTITUTE(Data,」」,REPT(」 「,999)),TRANSPOSE(999*(ROW(INDIRECT(「1:」&MAX(Arry1)))-1)+1),999)),N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))
在上述名稱中，使用了另一個定義的名稱：Data
引用位置：=Sheet1!$A$1:$A$10
1. 我們首先來看一個名稱Arry3，這是我們公式的關鍵部分。名稱Arry3的定義公式：
=INDEX(TRIM(MID(SUBSTITUTE(Data,」」,REPT(」 「,999)),TRANSPOSE(999*(ROW(INDIRECT(「1:」&MAX(Arry1)))-1)+1),999)),N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))
（1）使用TRIM、MID、SUBSTITUTE和REPT函數的構造是一種標準的（並且非常有用）組合，給定一個以某字元（空格、逗號、分號等）分隔的字元串，該構造能用於提取這些被分隔的子字元串中的任何一個，或者（像本例一樣）生成由這些被分隔的子字元串的組成的數組，以便按我們希望的方式進行操作。
現在，僅將上述公式應用於Data中的第一個單元格，以了解其工作原理。我們還將用SUMPRODUCT封裝該結構，不是因為真想對「Amaranth」、「Bronze」和「Silver」求和，而是使我們能夠強制返回數組。因此，我們將使用的公式為：
=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1,」 「,REPT(「」,99)),99*(ROW(INDIRECT(「1:」 &1+LEN(A1)-LEN(SUBSTITUTE(A1,」 「,」」))))-1)+1,99)))
（為便於解釋，這里將上面的999縮小為99。）
關於此公式構造的關鍵點是字元串中所有空格的初始替換，這次替換基本上使用更多的空格。如果所使用的字元串用逗號或分號分隔，則情況將相同：我們將用更多的空格替換所有逗號或分號。
這里，生成那些更多空格字元串的部分就是REPT(」 」,99)，構成了一個包含99個空格的字元串。
使用空格替換後的公式為：
=SUMPRODUCT(TRIM(MID(「Amaranth Bronze Silver,99*(ROW(INDIRECT(「1:」& 1+LEN(A1)-LEN(SUBSTITUTE(A1,」 「,」」))))-1)+1,99)))
此時，在單詞之間已放置了大量的空格。
再看看MID函數中參數start_num部分：
99*(ROW(INDIRECT(「1:」 &1+LEN(A1)-LEN(SUBSTITUTE(A1,」 「,」」))))-1)+1
在單元格A1中字元串的長度是22，將單詞之間的空格去掉後長度是20。因此：
1+LEN(A1)-LEN(SUBSTITUTE(A1,」」,」」))
的結果為：1+22-20，為3。
注意這種公式構造，該構造可以有效地計算字元串中（以空格分隔的）子字元串的數量。
這樣，MID函數的參數start_num部分轉換成：
99*(ROW(INDIRECT(「1:」 & 3))-1)+1
即為：
99*({0;1;2})+1
結果為：
{1;100;199}
這樣，可以確保本例中所拆分的單詞都在空格分隔的區域內。其實，只要單詞不長且我們選擇的類似99這樣的數字夠大，都可以保證能夠有效拆分單詞。
實際上，可以保證有效獲取分隔區域的數值的長度應該始終大於字元串中任何單個單詞的長度。這樣，我們可以選擇該值為字元串的長度，因為單個子字元串的長度都不能大於整個字元串本身的長度。所以，建議在這種類型的公式結構中使用LEN(A1)而不是99，甚至999。本例中由於要將公式應用於一系列單元格，故沒有使用該方法，而是直接取值999。
這里取MID函數的第三個參數為99，以保證能夠將單詞包含到我們獲取到子字元串中。
這樣，上面的SUMPRODUCT公式變為：
=SUMPRODUCT(TRIM(MID(「Amaranth Bronze Silver」,{1;100;199},99)))
轉換為：
=SUMPRODUCT(TRIM({「Amaranth 「;」 Bronze 「;」 Silver」}))
TRIM函數去掉字元串前後的空格：
=SUMPRODUCT({「Amaranth「;」Bronze「;」Silver」})
好了！原理講清楚了，現在回到名稱Arry3：
=INDEX(TRIM(MID(SUBSTITUTE(Data,」」,REPT(」 「,999)),TRANSPOSE(999*(ROW(INDIRECT(「1:」&MAX(Arry1)))-1)+1),999)),N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))
記住，我們沒有傳遞單個單元格到TRIM(MID(SUBSTITUTE(中，而是單元格區域。先看看公式中MID函數的指定起始位置的參數部分：
TRANSPOSE(999*(ROW(INDIRECT(「1:」& MAX(Arry1)))-1)+1)
首先看看定義的名稱Arry1：
1+LEN(Data)-LEN(SUBSTITUTE(Data,」」,」」))
轉換為：
1+LEN({「Amaranth BronzeSilver」;」Bronze」;」」;」Violet BronzeAmaranth」;」Red」;」Puce Bronze」;」Taupe Ochre BronzeCerise」;」Silver Red CeriseOrange」;」」;」Cerise」})-LEN(SUBSTITUTE({「AmaranthBronze Silver」;」Bronze」;」」;」Violet BronzeAmaranth」;」Red」;」Puce Bronze」;」Taupe Ochre BronzeCerise」;」Silver Red CeriseOrange」;」」;」Cerise」},」 「,」」))
轉換為：
1+{22;6;0;22;3;11;25;24;0;6}-{20;6;0;20;3;10;22;21;0;6}
結果為：
{3;1;1;3;1;2;4;4;1;1}
即單元格區域Data中每個單元格內單個單詞的數量，除了其中第3行和第9行為空但仍返回不正確的數字1外。但由於我們只是想獲取所構造的數組的最大值，因此這些不正確的結果不會對我們有影響。
這樣，MID函數的指定起始位置的參數部分轉換為：
TRANSPOSE(999*(ROW(INDIRECT(「1:」 &MAX({3;1;1;3;1;2;4;4;1;1})))-1)+1)
轉換為：
TRANSPOSE({1;1000;1999;2998})
結果為：
{1,1000,1999,2998}
此時，公式中的一部分轉換為：
TRIM(MID(SUBSTITUTE(Data,」」,REPT(」 「,999)),{1,1000,1999,2998},999))
轉換為：
TRIM({「Amaranth 「,」 Bronze 「,」 Silver」,」」;」Bronze」,」」,」」,」」;」」,」」,」」,」」;」Violet 「,」 Bronze 「,」 Amaranth」,」」;」Red」,」」,」」,」」;」Puce 「,」 Bronze」,」」,」」;」Taupe 「,」 Ochre 「,」 Bronze 「,」 Cerise」;」Silver 「,」 Red 「,」 Cerise 「,」 Orange」;」」,」」,」」,」」;」Cerise」,」」,」」,」」})
這里為一個10行4列的數組。
下圖2展示了MID函數運行的結果。
圖2
TRIM函數使上述數組變為：
{「Amaranth」,」Bronze」,」Silver」,」」;」Bronze」,」」,」」,」」;」」,」」,」」,」」;」Violet」,」Bronze」,」Amaranth」,」」;」Red」,」」,」」,」」;」Puce」,」Bronze」,」」,」」;」Taupe」,」Ochre」,」Bronze」,」Cerise」;」Silver」,」Red」,」Cerise」,」Orange」;」」,」」,」」,」」;」Cerise」,」」,」」,」」}
現在，創建了一個由單元格區域Data中所有單個子字元串（或單詞）組成的數組，接著可以開始考慮處理該數組中的元素以達到我們的要求。
（2）下面，要考慮從數組中創建唯一值列表。我們有一些從列表中創建唯一值的標准公式，例如下圖3所示。
圖3
在單元格B2中，計算列表中返回的唯一值個數：
=SUMPRODUCT((A2:A10>」」)/(COUNTIF(A2:A10,A2:A10&」」)))
在列D中，使用FREQUENCY函數來獲取唯一值列表。在單元格D2中輸入數組公式：
=IF(ROWS($1:1)>$B$2,」」,INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10>」」,MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1),ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1),ROWS($1:1))))
下拉至出現空單元格為止。
在列E中，使用COUNTIF函數來獲取唯一值列表。在單元格E2中輸入數組公式：
=IF(ROWS($1:1)>$B$2,」」,INDEX($A$2:$A$10,MATCH(0,IF($A$2:$A$10>」」,COUNTIF(E$1:E1,$A$2:$A$10&」」)),0)))
下拉至出現空單元格為止。
（作者個人傾向於使用第1個公式，更靈活且比COUNTIF版本要更快，特別是，想要從中獲得唯一值的數組是從公式中的其他函數生成的數組的情形下。COUNTIF函數的缺點在於傳遞給它的參數必須是實際的工作表區域引用。）
從上面的示例中可以看出，FREQUENCY函數可以處理單行或單列數組，而我們這里生成的是10行4列數組，那麼FREQUENCY函數可以處理這樣的二維數組嗎？不幸的是，答案是否定的。雖然INDEX、SMALL和FREQUENCY函數可以處理這類數組，但MATCH函數不能，傳遞給它的lookup_array參數必須是單行或單列。
因此，我們需要採用一種將這里的數組轉換成單行或單列數組的技術。
（3）回到前面，現在定義名稱Arry3的公式可以轉換成：
INDEX({「Amaranth」,」Bronze」,」Silver」,」」;」Bronze」,」」,」」,」」;」」,」」,」」,」」;」Violet」,」Bronze」,」Amaranth」,」」;」Red」,」」,」」,」」;」Puce」,」Bronze」,」」,」」;」Taupe」,」Ochre」,」Bronze」,」Cerise」;」Silver」,」Red」,」Cerise」,」Orange」;」」,」」,」」,」」;」Cerise」,」」,」」,」」},N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))
我們可以看到，這里對INDEX的行參數和列參數使用了兩個構造：
N(IF(1,1+INT((Arry2-1)/MAX(Arry1))))
和
N(IF(1,1+MOD(Arry2-1,MAX(Arry1))))
這里引用了名稱Arry2：
ROW(INDIRECT(「1:」& (MAX(Arry1)*ROWS(Data))))
上文中已計算出Arry1的最大值為4，Data中的行數為10，因此上面的公式轉換為：
ROW(INDIRECT(「1:」 & 40))
於是，Arry2為由1至40組成的單列數組：
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40}
這樣，上述構造中的：
1+INT((Arry2-1)/MAX(Arry1))
成為：
1+INT(({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40}-1)/4)
轉換為：
1+INT({0;0.25;0.5;0.75;1;1.25;1.5;1.75;2;2.25;2.5;2.75;3;3.25;3.5;3.75;4;4.25;4.5;4.75;5;5.25;5.5;5.75;6;6.25;6.5;6.75;7;7.25;7.5;7.75;8;8.25;8.5;8.75;9;9.25;9.5;9.75})
轉換為：
1+{0;0;0;0;1;1;1;1;2;2;2;2;3;3;3;3;4;4;4;4;5;5;5;5;6;6;6;6;7;7;7;7;8;8;8;8;9;9;9;9}
結果為：
{1;1;1;1;2;2;2;2;3;3;3;3;4;4;4;4;5;5;5;5;6;6;6;6;7;7;7;7;8;8;8;8;9;9;9;9;10;10;10;10}
同樣，列參數構造中的：
1+MOD(Arry2-1,MAX(Arry1))
可以轉換為：
{1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4}
由於這兩個數組都具有相同的向量位移（即它們都是單列數組），我們知道，將它們傳遞給INDEX函數進行處理時，這些數組中相對應的元素將被「配對」，因此我們將指示INDEX返回一個值數組，其row_num和col_num參數將依次為：1/1、1/2、1/3、1/4、2/1、2/2、2/3、2/4、3/1，…，依此類推。也就是說，我們將依次從上文生成的10行4列的數組中取值。
現在定義名稱Arry3的公式可以轉換成：
INDEX({「Amaranth」,」Bronze」,」Silver」,」」;」Bronze」,」」,」」,」」;」」,」」,」」,」」;」Violet」,」Bronze」,」Amaranth」,」」;」Red」,」」,」」,」」;」Puce」,」Bronze」,」」,」」;」Taupe」,」Ochre」,」Bronze」,」Cerise」;」Silver」,」Red」,」Cerise」,」Orange」;」」,」」,」」,」」;」Cerise」,」」,」」,」」},{1;1;1;1;2;2;2;2;3;3;3;3;4;4;4;4;5;5;5;5;6;6;6;6;7;7;7;7;8;8;8;8;9;9;9;9;10;10;10;10},{1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4})
轉換成最終的結果：
{「Amaranth」;」Bronze」;」Silver」;」」;」Bronze」;」」;」」;」」;」」;」」;」」;」」;」Violet」;」Bronze」;」Amaranth」;」」;」Red」;」」;」」;」」;」Puce」;」Bronze」;」」;」」;」Taupe」;」Ochre」;」Bronze」;」Cerise」;」Silver」;」Red」;」Cerise」;」Orange」;」」;」」;」」;」」;」Cerise」;」」;」」;」」}
至此，成功地將原來的10行4列數組轉換成40行1列的數組。這樣，就可以將這個數組傳遞給MATCH函數而不會出錯了。
注意，在上述構造中，前面的部分為N(IF(1,是為了強制INDEX返回數組，詳細原因參見《Excel公式技巧03：INDEX函數，給公式提供數組》。
2. 使用Arry3替換掉上文中使用FREQUENCY函數求唯一值的公式中的單元格區域，並進行適當的調整，得到單元格B2中的公式：
=IF(ROWS($1:1)>$C$1,」」,INDEX(Arry3,SMALL(IF(FREQUENCY(IF(Arry3>」」,MATCH(Arry3,Arry3,0)),Arry2),Arry2),ROWS($1:1))))
3. 對於單元格C1中求唯一值個數的公式：
=SUM((Arry3>」」)/MMULT(0+(Arry3=TRANSPOSE(Arry3)),ROW(INDIRECT(「1:」& COUNTA(Arry3)))^0))
（1）Arry3中的元素是否為空進行比較，得到數組：
{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
（2）看看MMULT中的第二個數組：
ROW(INDIRECT(「1:」 &COUNTA(Arry3)))^0
我們已經知道Arry3中元素個數為40，因此上述數組為：
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40}^0
結果為：
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}
（3）看看MMULT中的第一個數組：
0+(Arry3=TRANSPOSE(Arry3))
這將轉換成40行40列的數組。由於數組太大，為了方便解釋其原理，將數據區域Data縮減為A1:A2，這樣Arry3為：
{「Amaranth」;」Bronze」;」Silver」;」Bronze」;」」;」」}
此時，MMULT中的第一個數組轉換為：
0+({「Amaranth」;」Bronze」;」Silver」;」Bronze」;」」;」」}={「Amaranth」,」Bronze」,」Silver」,」Bronze」,」」,」」})
兩個正交數組比較後的結果為：
0+{TRUE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE,TRUE}
加上0強制轉換為1/0組成的數組：
{1,0,0,0,0,0;0,1,0,1,0,0;0,0,1,0,0,0;0,1,0,1,0,0;0,0,0,0,1,1;0,0,0,0,1,1}
（4）此時，MMULT公式為：
MMULT({1,0,0,0,0,0;0,1,0,1,0,0;0,0,1,0,0,0;0,1,0,1,0,0;0,0,0,0,1,1;0,0,0,0,1,1},{1;1;1;1;1;1})
得到：
{1;2;1;2;2;2}
（5）此時，SUM公式為：
=SUM({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}/{1;2;1;2;2;2})
轉換為：
=SUM({1;0.5;1;0.5;0;0})
結果為3。表明如果數據區域為A1:A2，有3個唯一值。
（6）回到示例中的數據區域A1:A10，此時的SUM公式為：
=SUM({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}/{2;5;2;21;5;21;21;21;21;21;21;21;1;5;2;21;2;21;21;21;1;5;21;21;1;1;5;3;2;2;3;1;21;21;21;21;3;21;21;21})
轉換為：
=SUM({0.5;0.2;0.5;0;0.2;0;0;0;0;0;0;0;1;0.2;0.5;0;0.5;0;0;0;1;0.2;0;0;1;1;0.2;0.333333333333333;0.5;0.5;0.333333333333333;1;0;0;0;0;0.333333333333333;0;0;0})
結果為10。表明數據區域A1:A10中有10個唯一值。
小結
解決本案例的過程是，首先從原來的以空格分隔的字元串中生成子字元串數組，重新構建該數組，以便能夠對其進行處理。我們從本案例中至少可以學到：
1. 使用大量的空格替換來拆分由分隔符分隔的字元串。
2. 從列表中獲取唯一值的標准公式。
3. 將二維數組轉換成一維數組的方法。