Excel公式技巧中的降維技術

Excel公式技巧中的降維技術

看過前面一系列文章的朋友，一定會熟悉“重新定義數組維度”的概念。這是一項非常有用且非常重要的技術，使我們可以接受二維數組并將其轉換為一維數組，同時將元素保留在該數組中。

如果希望進一步操縱某二維數組的元素，則需要使用這種技術。例如，由于某種原因，在某種情形下，需要將二維數組中的每個元素傳遞給一個或多個參數進行進一步處理。但是，由于需要使用的Excel函數不能處理多維數組，因此必須首先將原數組轉換為一維數組。

以示例來說明，如下圖1所示的工作表。

圖1

可以構造各種公式，如：

=MID(A1,1,1)

結果顯然是“A”。

下面的公式：

=MID(A1,{1,2},1)

得到一維數組{“A”,”m”}，是一個單行向量。

當然，可以使用公式：

=MID(A1,{1;2},1)

得到一維數組{“A”;”m”}，是一個單列向量。

同樣，對于單元格A2、A3、A4，使用公式可以得到：

{“E”,”s”}

{“P”,”e”}

{“C”,”e”}

等等。

進一步，使用公式：

=MID(A1,{1,2},{1;2;3})

可以得到一個3行2列數組：

{“A”,”m”;”Am”,”ma”;”Ama”,”map”}

公式中兩個參數值的數組彼此正交，MID函數的參數start_num（{1,2}）是一個單行向量，參數num_chars（{1;2;3}）是一個單列向量。

當然可以交換這兩個參數的向量類型，公式為：

=MID(A1,{1;2},{1,2,3})

得到一個2行3列的數組：

{“A”,”Am”,”Ama”;”m”,”ma”,”map”}

可以看到，只有在傳遞給MID函數的兩個數組正交的情況下，才能成功地獲得所需的6個結果。如果我們使用公式：

=MID(A1,{1,2},{1,2,3})

返回的不是預想的6個元素組成的數組，而是一個由3個元素組成的數組：

{“A”,”ma”,#N/A}

其原因是，當兩個數組屬于相同的向量類型時，即兩個都是單行數組或都是單列數組，Excel將一個數組的元素與另一個數組中相應位置的元素“配對”。因此，公式：

=MID(A1,{1,2},{1,2,3})

等價于執行下面3個公式的結果：

=MID(A1,1,1)

=MID(A1,2,2)

=MID(A1,,2)

數組中根本沒有第三個元素作為MID函數的start_num參數與num_chars參數中的第三個元素配對。這樣，Excel返回#N/A作為結果數組中的第三個元素。

實際上，Excel為了解決傳遞兩個大小不同的數組的問題，重新定義了兩個中較小的一個，使其匹配較大的數組。這樣，結果數組中任何額外的不配對的單元格都將填充為#N/A。

在某些情況下，我們接受其中的數組被“重新定義維數”，即便使用錯誤值填充，前提是我們隨后可以根據需要對結果數組進行操作。

繼續！我們知道，可以給函數傳遞多個單元格。因此，可以構造公式：

=MID(A1:A9,1,1)

返回一個9行1列的一維數組，該數組由A1:A9中每個字符串的第一個字符組成，即：

{“A”;”E”;”P”;”C”;”R”;”B”;”M”;”A”;”A”}

進一步擴展：

=MID(A1:A9,{1,2},1)

返回一個9行2列的二維數組：

{“A”,”m”;”E”,”s”;”P”,”e”;”C”,”e”;”R”,”i”;”B”,”a”;”M”,”a”;”A”,”m”;”A”,”c”}

因為A1:A9是列向量，所以MID函數的參數start_num的值必須是行向量。如果試圖使用公式：

=MID(A1:A9,{1;2},1)

結果將是{“A”,”s”,#N/A, #N/A, #N/A, #N/A, #N/A, #N/A, #N/A}。

再繼續擴展，公式：

=MID(A1:C9,{1,2},1)

我們希望其返回由54個元素組成的數組，該數組等于54個單獨的MID構造的結果：

=MID(A1,1,1)

=MID(A1,2,1)

=MID(A2,1,1)

=MID(A2,2,1)

等等。

但實際上，結果是一個僅包含27個元素的數組：

{“A”,”?”,#N/A;”E”,”a”,#N/A;”P”,”l”,#N/A;”C”,”a”,#N/A;”R”,”o”,#N/A;”B”,”i”,#N/A;”M”,”o”,#N/A;”A”,”i”,#N/A;”A”,”i”,#N/A}

可參考《Excel公式技巧06： COUNTIFS函數如何處理以數組方式提供的條件》中解的對數組的解析的內容。

同樣，改變公式中參數的向量類型：

=MID(A1:C9,{1;2},1)

結果是：

{“A”,”S”,”P”;”s”,”a”,”a”;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A;#N/A,#N/A,#N/A}

現在怎么辦呢？Excel對可以操作的數組維數的固有限制，是否意味著我們必須放棄獲取正在尋找的54個元素數組的嘗試？

的確，我們不能改變維數數量，但并不是說不能通過其他方式實現。

在繼續剛才的MID函數示例之前，我們以另一個示例來解釋。假設在單元格A1:E10中的數據如下圖2所示。

圖2

顯然，這里的數據是二維的，是一個10行5列的數組，其Excel表示為：

{“A1″,”B1″,”C1″,”D1″,”E1″;”A2″,”B2″,”C2″,”D2″,”E2″;”A3″,”B3″,”C3″,”D3″,”E3″;”A4″,”B4″,”C4″,”D4″,”E4″;”A5″,”B5″,”C5″,”D5″,”E5″;”A6″,”B6″,”C6″,”D6″,”E6″;”A7″,”B7″,”C7″,”D7″,”E7″;”A8″,”B8″,”C8″,”D8″,”E8″;”A9″,”B9″,”C9″,”D9″,”E9″;”A10″,”B10″,”C10″,”D10″,”E10”}

但是，由于某些原因，我們需要將上述數據放置在一維數組中：

{“A1″;”B1″;”C1″;”D1″;”E1″;”A2″;”B2″;”C2″;”D2″;”E2″;”A3″;”B3″;”C3″;”D3″;”E3″;”A4″;”B4″;”C4″;”D4″;”E4″;”A5″;”B5″;”C5″;”D5″;”E5″;”A6″;”B6″;”C6″;”D6″;”E6″;”A7″;”B7″;”C7″;”D7″;”E7″;”A8″;”B8″;”C8″;”D8″;”E8″;”A9″;”B9″;”C9″;”D9″;”E9″;”A10″;”B10″;”C10″;”D10″;”E10”}

如何得到這個50行1列的數組？

（或者，一個1行50列的數組：

{“A1″,”B1″,”C1″,”D1″,”E1″,”A2″,”B2″,”C2″,”D2″,”E2″,”A3″,”B3″,”C3″,”D3″,”E3″,”A4″,”B4″,”C4″,”D4″,”E4″,”A5″,”B5″,”C5″,”D5″,”E5″,”A6″,”B6″,”C6″,”D6″,”E6″,”A7″,”B7″,”C7″,”D7″,”E7″,”A8″,”B8″,”C8″,”D8″,”E8″,”A9″,”B9″,”C9″,”D9″,”E9″,”A10″,”B10″,”C10″,”D10″,”E10″}）

通常使用下面的公式：

=INDEX(A1:E10,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1)/COLUMNS(A1:E10))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1),COLUMNS(A1:E10))))))

其思路是將這個二維數組中的每個元素精確地索引一次，上面的公式轉換為：

=INDEX(A1:E10,N(IF(1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4;5;5;5;5;5;6;6;6;6;6;7;7;7;7;7;8;8;8;8;8;9;9;9;9;9;10;10;10;10;10})),N(IF(1,{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})))

這里的關鍵是，傳遞給INDEX函數的數組（用于參數row_num和參數column_num的數組）都是相同的向量類型（均為列向量），從而確保了由INDEX產生的數組也是這種向量類型。根據其定義，列向量當然是一維的。這里使用的技術請參閱《Excel公式技巧03：INDEX函數，給公式提供數組》、《Excel公式練習44：從多列中返回唯一且按字母順序排列的列表》。

可以看出，INDEX結構具有不可否認的優勢，不僅可以將其用于重新定義工作表區域的維度，還可以重新定義公式中某些其他子函數產生的數組的維度。

然而，還可以使用更短的公式：

=T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),”R0C00000″),0))

注意，上述公式結構使用了函數T，因此要求單元格區域A1:E10內的值是非數字的。對于由數值組成的單元格區域，可以使用N函數。對于包含混合數據類型的區域，建議使用INDEX方法。

關鍵是要利用MODE.MULT函數的特性來返回返回一維數組，無論傳遞給該函數的數組本身是一維數組還是二維數組，這都同樣適用。然而，MODE.MULT函數自身也存在缺點：傳遞的數組中的任何元素都要至少出現一次，否則將出錯，這意味著我們要強制解決該問題。因此，這里故意使用了擴展的單元格區域A1:E20：

1+MOD(ROW(A1:E20)-1,10)

轉換為：

1+MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1,10)

轉換為：

1+MOD({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},10)

轉換為：

1+{0;1;2;3;4;5;6;7;8;9;0;1;2;3;4;5;6;7;8;9}

得到：

{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}

此時，公式中的：

T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),”R0C00000″),0))

轉換為：

T(INDIRECT(TEXT(MODE.MULT(10^5*{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}+COLUMN(A1:E10)),”R0C00000″),0))

轉換為：

T(INDIRECT(TEXT(MODE.MULT({100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000;100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000}+{1,2,3,4,5}),”R0C00000″),0))

轉換為：

T(INDIRECT(TEXT(MODE.MULT({100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005;100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005}),”R0C00000″),0))

轉換為：

T(INDIRECT(TEXT({100001;100002;100003;100004;100005;200001;200002;200003;200004;200005;300001;300002;300003;300004;300005;400001;400002;400003;400004;400005;500001;500002;500003;500004;500005;600001;600002;600003;600004;600005;700001;700002;700003;700004;700005;800001;800002;800003;800004;800005;900001;900002;900003;900004;900005;1000001;1000002;1000003;1000004;1000005},”R0C00000”),0))

轉換為：

T(INDIRECT({“R1C00001″;”R1C00002″;”R1C00003″;”R1C00004″;”R1C00005″;”R2C00001″;”R2C00002″;”R2C00003″;”R2C00004″;”R2C00005″;”R3C00001″;”R3C00002″;”R3C00003″;”R3C00004″;”R3C00005″;”R4C00001″;”R4C00002″;”R4C00003″;”R4C00004″;”R4C00005″;”R5C00001″;”R5C00002″;”R5C00003″;”R5C00004″;”R5C00005″;”R6C00001″;”R6C00002″;”R6C00003″;”R6C00004″;”R6C00005″;”R7C00001″;”R7C00002″;”R7C00003″;”R7C00004″;”R7C00005″;”R8C00001″;”R8C00002″;”R8C00003″;”R8C00004″;”R8C00005″;”R9C00001″;”R9C00002″;”R9C00003″;”R9C00004″;”R9C00005″;”R10C00001″;”R10C00002″;”R10C00003″;”R10C00004″;”R10C00005”},0))

結果為：

{“A1″;”B1″;”C1″;”D1″;”E1″;”A2″;”B2″;”C2″;”D2″;”E2″;”A3″;”B3″;”C3″;”D3″;”E3″;”A4″;”B4″;”C4″;”D4″;”E4″;”A5″;”B5″;”C5″;”D5″;”E5″;”A6″;”B6″;”C6″;”D6″;”E6″;”A7″;”B7″;”C7″;”D7″;”E7″;”A8″;”B8″;”C8″;”D8″;”E8″;”A9″;”B9″;”C9″;”D9″;”E9″;”A10″;”B10″;”C10″;”D10″;”E10″}

正是我們需要的一維數組。

回到上文中的MID函數示例，我們試圖通過公式：

=MID(A1:C9,{1,2},1)

生成由給定這些參數的所有54個排列組成的數組。使用我們的重新定義維數的技術，只需使用A1:C9對上述公式相應位置進行替換：

MID(T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:C19)-1,10))+COLUMN(A1:C9)),”R0C00000”),0)),{1,2},1)

轉換為：

MID({“Amapá”;”S?oPaulo”;”Paraná”;”Espírito Santo”;”SantaCatarina”;”Maranh?o”;”Pernambuco”;”Alagoas”;”MatoGrosso”;”Ceará”;”Paraíba”;”Piauí”;”RioGrande do Sul”;”Rond?nia”;”Tocantins”;”Bahia”;”MinasGerais”;”Roraima”;”Mato Grosso doSul”;”Goiás”;”Pará”;”Amazonas”;”RioGrande do Norte”;”Rio de Janeiro”;”Acre”;”DistritoFederal”;”Sergipe”},{1,2},1)

轉換為：

{“A”,”m”;”S”,”?”;”P”,”a”;”E”,”s”;”S”,”a”;”M”,”a”;”P”,”e”;”A”,”l”;”M”,”a”;”C”,”e”;”P”,”a”;”P”,”i”;”R”,”i”;”R”,”o”;”T”,”o”;”B”,”a”;”M”,”i”;”R”,”o”;”M”,”a”;”G”,”o”;”P”,”a”;”A”,”m”;”R”,”i”;”R”,”i”;”A”,”c”;”D”,”i”;”S”,”e”}

生成了想要的54個元素。

同樣，我們可以將這項技術運用到“四維數組”：

=MID(A1:C9,{1,2},{1;2;3})

對于第二次重新定義數組維數，必須使用前面的INDEX構造：

=MID(INDEX(ReDim1,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(ReDim1)*COLUMNS(ReDim1)))-1)/COLUMNS(ReDim1))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(ReDim1)*COLUMNS(ReDim1)))-1),COLUMNS(ReDim1)))))),1,{1,2,3})

其中的ReDim1是我們第一次重新定義數組維數的公式：

=MID(T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW($A$1:$C$19)-1,10))+COLUMN($A$1:$C$9)),”R0C00000″),0)),{1,2},MAX(LEN($A$1:$C$9)))

太復雜了！腦筋都不夠用了！

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。