日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

如下圖1所示，在單元格A1中有一段英文文本，其中可能包含標點符號或不包含標點符號，在單元格B1中輸入一個公式，識別文本中包含五個元音字母的單詞，統計出這些單詞的個數。

圖1

注意，統計的單詞應滿足：

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

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

3. 這五個元音字母在單詞中只出現一次

在圖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，因此公式轉換為：

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

轉換為：

=ROW(INDIRECT(“1:”& 70))-1

轉換為：

{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

結果為：

{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}

中間獲得的數組中的70對應著文本包含有70個子字符串（單詞）。

將得到的結果數組傳遞給名稱Arry2中MID函數的start_num參數：

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

注意，這里使用LOWER函數將文本轉換成小寫，因為SUBSTITUTE函數區分大小寫。

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

{“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.”}

數組中，有些單詞包含了標點符號，但并不影響最終的結果。

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

先看看公式中的：

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

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

{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}

我們將數組中的TRUE采用紅色字體，與Arry2中相應的元素對應：

{“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

這是一個標準的公式技術，用來確定字符串中某個字符有多少個：使用原始字符串的長度減去剔除掉指定字符后的字符串的長度。

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

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

{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”},””)

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

{“bstemious”,”abstmious”,”abstemous”,”abstemius”,”abstemios”}

最終的結果為：

{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}

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

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

得到：

{1,1,1,1,1}

也就是單詞”abstemious”中元音”a”,”e”,”i”,”o”,”u”的個數組成的數組。

最終的結果為：

{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”分別出現的個數。

由于我們感興趣的僅僅是這五個元音只出現一次的單詞，將上面的數組與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}

將布爾值轉換成數字：

{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}

現在要找到數組中由5個1組成的行，使用MMULT函數：

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}

我們將數組中為5的與Arry2中相應的元素對應：

{“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個結果滿足，但只滿足單詞中出現五個元音字母一次而不滿足其按”a”,”e”,”i”,”o”,”u”的順序出現。

將上面的數組與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}

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

=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})

結果為：

8