excelperfect

導(dǎo)語：繼續(xù)研究來自于excelxor.com的案例。這個案例應(yīng)用了前面分享的一些公式技巧，值得反復(fù)學(xué)習(xí)。

本次的練習(xí)是：如下圖1所示，在單元格A1中有一段英文文本，其中可能包含標(biāo)點符號或不包含標(biāo)點符號，在單元格B1中輸入一個公式，識別文本中包含五個元音字母的單詞，統(tǒng)計出這些單詞的個數(shù)。

圖1

注意，統(tǒng)計的單詞應(yīng)滿足：

1. 單詞中包含全部五個元音字母

2. 這五個元音字母在單詞中從左至右出現(xiàn)的順序是a、e、i、o、u

3. 這五個元音字母在單詞中只出現(xiàn)一次

在圖1中，紅色字體的單詞滿足條件，而黑色斜體的單詞雖然包含全部的五個元音字母但由于順序不符合要求，因此不滿足條件。

先不看答案，自已動手試一試。

公式

在單元格B1中輸入公式：

=SUMPRODUCT(0+(MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{'a','e','i','o','u'},''))=1),{1;1;1;1;1})=5),0+(ISNUMBER(SEARCH('a*e*i*o*u',Arry2))))

公式解析

公式中的Arry1和Arry2是定義的兩個名稱。

名稱：Arry1

引用位置：=ROW(INDIRECT('1:'&1+LEN($A1)-LEN(SUBSTITUTE($A1,'',''))))-1

名稱：Arry2

引用位置：=TRIM(MID(SUBSTITUTE(LOWER($A1),'',REPT(' ',LEN($A1))),LEN($A1)*Arry1+1,LEN($A1)))

注意，在定義名稱時確?；顒訂卧裎挥诠ぷ鞅淼牡谝恍?。

首先，來看看名稱Arry1：

=ROW(INDIRECT('1:' & 1+LEN($A1)-LEN(SUBSTITUTE($A1,'',''))))-1

由于單元格A1中字符串的長度為461，去掉空格后的長度為392，因此公式轉(zhuǎn)換為：

=ROW(INDIRECT('1:' & 1+461-392))-1

轉(zhuǎn)換為：

=ROW(INDIRECT('1:'& 70))-1

轉(zhuǎn)換為：

{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;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70}-1

結(jié)果為：

{0;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;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69}

中間獲得的數(shù)組中的70對應(yīng)著文本包含有70個子字符串（單詞）。

將得到的結(jié)果數(shù)組傳遞給名稱Arry2中MID函數(shù)的start_num參數(shù)：

=TRIM(MID(SUBSTITUTE(LOWER($A1),'',REPT(' ',LEN($A1))),LEN($A1)*Arry1+1,LEN($A1)))

注意，這里使用LOWER函數(shù)將文本轉(zhuǎn)換成小寫，因為SUBSTITUTE函數(shù)區(qū)分大小寫。

Arry2將生成由A1中的單詞組成的數(shù)組，其運行原理在本系列前面的文章中已作詳細講解，有興趣的朋友可查閱參考。Arry2生成的數(shù)組為：

{'abstemious';'people';'who';'are';'not';'facetious';'by';'nature';'should';'not';'be';'lacking';'the';'education';'imbibing';'of';'arsenious';'substances';'will';'not';'make';'them';'more';'abstentious.';'indeed,';'facetiousness';'aside,';'such';'practices';'are';'likely';'to';'be';'harmful,';'as';'many';'acheilous';'casualties';'can';'testify.';'a';'more';'reliable';'herbal';'remedy';'is';'a';'concoction';'of';'the';'caesious,';'annelidous';'plants';'found';'anemious';'plains';'of';'outer';'mongolia,';'plants';'which';'are';'thought';'to';'contributed';'to';'the';'diet';'of';'raeticodactylus.'}

數(shù)組中，有些單詞包含了標(biāo)點符號，但并不影響最終的結(jié)果。

下面，我們需要對上面生成的數(shù)組中的每個元素執(zhí)行兩項測試：第一項測試是確定每個元素是否按順序包含“a”、“e”、“i”、“o”、“u”這五個元素，第二項測試確定這五個元音字母在元素中僅出現(xiàn)一次。

先看看公式中的：

ISNUMBER(SEARCH('a*e*i*o*u',Arry2))

SEARCH函數(shù)有一個很好的特性，接受通配符。因此，在合適的地方插入通配符后，可以使用字符串“a*e*i*o*u”作為該函數(shù)的find_text參數(shù)。這樣，如果在查找的字符串中按順序包含“a”、“e”、“i”、“o”、“u”這五個元素的話，則返回代表找到字符位置的數(shù)字。上述公式可轉(zhuǎn)換為：

{TRUE;FALSE;FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}

我們將數(shù)組中的TRUE采用紅色字體，與Arry2中相應(yīng)的元素對應(yīng)：

{'abstemious';'people';'who';'are';'not';'facetious';'by';'nature';'should';'not';'be';'lacking';'the';'education';'imbibing';'of';'arsenious';'substances';'will';'not';'make';'them';'more';'abstentious.';'indeed,';'facetiousness';'aside,';'such';'practices';'are';'likely';'to';'be';'harmful,';'as';'many';'acheilous';'casualties';'can';'testify.';'a';'more';'reliable';'herbal';'remedy';'is';'a';'concoction';'of';'the';'caesious,';'annelidous';'plants';'found';'anemious';'plains';'of';'outer';'mongolia,';'plants';'which';'are';'thought';'to';'contributed';'to';'the';'diet';'of';'raeticodactylus.'}

顯然，這些紅色字體的元素滿足我們的條件，但并不是所有都滿足，其中有兩個“facetiousness”和“raeticodactylus.”中有些元音多于一個。

這樣，我們需要進行第二項測試：

MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{'a','e','i','o','u'},''))=1),{1;1;1;1;1})=5

這是一個標(biāo)準(zhǔn)的公式技術(shù)，用來確定字符串中某個字符有多少個：使用原始字符串的長度減去剔除掉指定字符后的字符串的長度。

注意到，我們要確定的字符不是一個而是五個。

對于LEN(Arry2)，轉(zhuǎn)換為原始字符串的長度：

{10;6;3;3;3;9;2;6;6;3;2;7;3;9;8;2;9;10;4;3;4;4;4;12;7;13;6;4;9;3;6;2;2;8;2;4;9;10;3;8;1;4;8;6;6;2;1;10;2;3;9;10;6;5;8;6;2;5;9;6;5;3;7;2;11;2;3;4;2;16}

公式中的：

LEN(SUBSTITUTE(Arry2,{'a','e','i','o','u'},'')

傳遞由五個值組成的數(shù)組給SUBSTITUTE函數(shù)的參數(shù)old_text，要確保這五個值組成的數(shù)組與Arry2正交。由于Arry2是單列數(shù)組向量，那么這五個數(shù)組應(yīng)該是單行數(shù)組向量。這樣，就形成了一個69行5列的數(shù)組，對應(yīng)著每個單詞刪除一個元音字母后的字符串。例如，生成的數(shù)組的第一行應(yīng)該為：

{'bstemious','abstmious','abstemous','abstemius','abstemios'}

最終的結(jié)果為：

{9,9,9,9,9;6,4,6,5,6;3,3,3,2,3;2,2,3,3,3;3,3,3,2,3;8,8,8,8,8;2,2,2,2,2;5,5,6,6,5;6,6,6,5,5;3,3,3,2,3;2,1,2,2,2;6,7,6,7,7;3,2,3,3,3;8,8,8,8,8;8,8,5,8,8;2,2,2,1,2;8,8,8,8,8;9,9,10,10,9;4,4,3,4,4;3,3,3,2,3;3,3,4,4,4;4,3,4,4,4;4,3,4,3,4;11,11,11,11,11;7,5,6,7,7;12,11,12,12,12;5,5,5,6,6;4,4,4,4,3;8,8,8,9,9;2,2,3,3,3;6,5,5,6,6;2,2,2,1,2;2,1,2,2,2;7,8,8,8,7;1,2,2,2,2;3,4,4,4,4;8,8,8,8,8;8,9,9,10,9;2,3,3,3,3;8,7,7,8,8;0,1,1,1,1;4,3,4,3,4;7,6,7,8,8;5,5,6,6,6;6,4,6,6,6;2,2,1,2,2;0,1,1,1,1;10,10,9,7,10;2,2,2,1,2;3,2,3,3,3;8,8,8,8,8;9,9,9,9,9;5,6,6,6,6;5,5,5,4,4;7,7,7,7,7;5,6,5,6,6;2,2,2,1,2;5,4,5,4,4;8,9,8,7,9;5,6,6,6,6;5,5,4,5,5;2,2,3,3,3;7,7,7,6,6;2,2,2,1,2;11,10,10,10,10;2,2,2,1,2;3,2,3,3,3;4,3,3,4,4;2,2,2,1,2;14,15,15,15,15}

從上面生成的第一個數(shù)組減去第二個數(shù)組，等于由每個元素中分別包含五個元音的數(shù)量組成的數(shù)組。例如第一個數(shù)組的第一個元素10（即單詞'abstemious'的長度）減去第二個數(shù)組中的第一行{9,9,9,9,9}（即單詞'abstemious'分別去掉五個元音后的長度）：

10-{9,9,9,9,9}

得到：

{1,1,1,1,1}

也就是單詞'abstemious'中元音'a','e','i','o','u'的個數(shù)組成的數(shù)組。

最終的結(jié)果為：

{1,1,1,1,1;0,2,0,1,0;0,0,0,1,0;1,1,0,0,0;0,0,0,1,0;1,1,1,1,1;0,0,0,0,0;1,1,0,0,1;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,0,1,0,0;0,1,0,0,0;1,1,1,1,1;0,0,3,0,0;0,0,0,1,0;1,1,1,1,1;1,1,0,0,1;0,0,1,0,0;0,0,0,1,0;1,1,0,0,0;0,1,0,0,0;0,1,0,1,0;1,1,1,1,1;0,2,1,0,0;1,2,1,1,1;1,1,1,0,0;0,0,0,0,1;1,1,1,0,0;1,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,0,0,0;1,0,0,0,1;1,0,0,0,0;1,0,0,0,0;1,1,1,1,1;2,1,1,0,1;1,0,0,0,0;0,1,1,0,0;1,0,0,0,0;0,1,0,1,0;1,2,1,0,0;1,1,0,0,0;0,2,0,0,0;0,0,1,0,0;1,0,0,0,0;0,0,1,3,0;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,1,1,1,1;1,0,0,0,0;0,0,0,1,1;1,1,1,1,1;1,0,1,0,0;0,0,0,1,0;0,1,0,1,1;1,0,1,2,0;1,0,0,0,0;0,0,1,0,0;1,1,0,0,0;0,0,0,1,1;0,0,0,1,0;0,1,1,1,1;0,0,0,1,0;0,1,0,0,0;0,1,1,0,0;0,0,0,1,0;2,1,1,1,1}

代表著每個單詞中元音'a','e','i','o','u'分別出現(xiàn)的個數(shù)。

由于我們感興趣的僅僅是這五個元音只出現(xiàn)一次的單詞，將上面的數(shù)組與1相比較：

LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{'a','e','i','o','u'},''))=1

得到：

{TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,TRUE;FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE,TRUE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,FALSE,FALSE;TRUE,FALSE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,TRUE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,TRUE;TRUE,FALSE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,TRUE,FALSE,TRUE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,TRUE,FALSE;TRUE,FALSE,TRUE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,TRUE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;TRUE,TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,TRUE,TRUE,TRUE,TRUE}

將布爾值轉(zhuǎn)換成數(shù)字：

{1,1,1,1,1;0,0,0,1,0;0,0,0,1,0;1,1,0,0,0;0,0,0,1,0;1,1,1,1,1;0,0,0,0,0;1,1,0,0,1;0,0,0,1,1;0,0,0,1,0;0,1,0,0,0;1,0,1,0,0;0,1,0,0,0;1,1,1,1,1;0,0,0,0,0;0,0,0,1,0;1,1,1,1,1;1,1,0,0,1;0,0,1,0,0;0,0,0,1,0;1,1,0,0,0;0,1,0,0,0;0,1,0,1,0;1,1,1,1,1;0,0,1,0,0;1,0,1,1,1;1,1,1,0,0;0,0,0,0,1;1,1,1,0,0;1,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,0,0,0;1,0,0,0,1;1,0,0,0,0;1,0,0,0,0;1,1,1,1,1;0,1,1,0,1;1,0,0,0,0;0,1,1,0,0;1,0,0,0,0;0,1,0,1,0;1,0,1,0,0;1,1,0,0,0;0,0,0,0,0;0,0,1,0,0;1,0,0,0,0;0,0,1,0,0;0,0,0,1,0;0,1,0,0,0;1,1,1,1,1;1,1,1,1,1;1,0,0,0,0;0,0,0,1,1;1,1,1,1,1;1,0,1,0,0;0,0,0,1,0;0,1,0,1,1;1,0,1,0,0;1,0,0,0,0;0,0,1,0,0;1,1,0,0,0;0,0,0,1,1;0,0,0,1,0;0,1,1,1,1;0,0,0,1,0;0,1,0,0,0;0,1,1,0,0;0,0,0,1,0;0,1,1,1,1}

現(xiàn)在要找到數(shù)組中由5個1組成的行，使用MMULT函數(shù)：

MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{'a','e','i','o','u'},''))=1),{1;1;1;1;1})

得到：

{5;1;1;2;1;5;0;3;2;1;1;2;1;5;0;1;5;3;1;1;2;1;2;5;1;4;3;1;3;2;2;1;1;2;1;1;5;3;1;2;1;2;2;2;0;1;1;1;1;1;5;5;1;2;5;2;1;3;2;1;1;2;2;1;4;1;1;2;1;4}

我們將數(shù)組中為5的與Arry2中相應(yīng)的元素對應(yīng)：

{'abstemious';'people';'who';'are';'not';'facetious';'by';'nature';'should';'not';'be';'lacking';'the';'education';'imbibing';'of';'arsenious';'substances';'will';'not';'make';'them';'more';'abstentious.';'indeed,';'facetiousness';'aside,';'such';'practices';'are';'likely';'to';'be';'harmful,';'as';'many';'acheilous';'casualties';'can';'testify.';'a';'more';'reliable';'herbal';'remedy';'is';'a';'concoction';'of';'the';'caesious,';'annelidous';'plants';'found';'anemious';'plains';'of';'outer';'mongolia,';'plants';'which';'are';'thought';'to';'contributed';'to';'the';'diet';'of';'raeticodactylus.'}

有9個結(jié)果滿足，但只滿足單詞中出現(xiàn)五個元音字母一次而不滿足其按'a','e','i','o','u'的順序出現(xiàn)。

將上面的數(shù)組與5比較，得到：

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

將上面得到的兩個條件的結(jié)果代入公式：

=SUMPRODUCT(0+(MMULT(0+(LEN(Arry2)-LEN(SUBSTITUTE(Arry2,{'a','e','i','o','u'},''))=1),{1;1;1;1;1})=5),0+(ISNUMBER(SEARCH('a*e*i*o*u',Arry2))))

得到：

=SUMPRODUCT(0+{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}),0+{TRUE;FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE;FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE })

得到：

=SUMPRODUCT({1;0;0;0;0;1;0;0;0;0;0;0;0;1;0;0;1;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},{1;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;1;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1})

結(jié)果為：

8

疑問

在我的Excel中，按照作者原來提供的文本，得到的結(jié)果是#VALUE!，我將文本中去掉了6個單詞之后，公式才得到正確的值。很奇怪，似乎文本長度沒有超過限制，怎么會這樣呢？