|
excelperfect 看過(guò)前面一系列文章的朋友，一定會(huì)熟悉“重新定義數(shù)組維度”的概念。這是一項(xiàng)非常有用且非常重要的技術(shù)，使我們可以接受二維數(shù)組并將其轉(zhuǎn)換為一維數(shù)組，同時(shí)將元素保留在該數(shù)組中。 如果希望進(jìn)一步操縱某二維數(shù)組的元素，則需要使用這種技術(shù)。例如，由于某種原因，在某種情形下，需要將二維數(shù)組中的每個(gè)元素傳遞給一個(gè)或多個(gè)參數(shù)進(jìn)行進(jìn)一步處理。但是，由于需要使用的Excel函數(shù)不能處理多維數(shù)組，因此必須首先將原數(shù)組轉(zhuǎn)換為一維數(shù)組。 以示例來(lái)說(shuō)明，如下圖1所示的工作表。 圖1 可以構(gòu)造各種公式，如： =MID(A1,1,1) 結(jié)果顯然是“A”。 下面的公式： =MID(A1,{1,2},1) 得到一維數(shù)組{'A','m'}，是一個(gè)單行向量。 當(dāng)然，可以使用公式： =MID(A1,{1;2},1) 得到一維數(shù)組{'A';'m'}，是一個(gè)單列向量。 同樣，對(duì)于單元格A2、A3、A4，使用公式可以得到： {'E','s'} {'P','e'} {'C','e'} 等等。 進(jìn)一步，使用公式： =MID(A1,{1,2},{1;2;3}) 可以得到一個(gè)3行2列數(shù)組： {'A','m';'Am','ma';'Ama','map'} 公式中兩個(gè)參數(shù)值的數(shù)組彼此正交，MID函數(shù)的參數(shù)start_num（{1,2}）是一個(gè)單行向量，參數(shù)num_chars（{1;2;3}）是一個(gè)單列向量。 當(dāng)然可以交換這兩個(gè)參數(shù)的向量類(lèi)型，公式為： =MID(A1,{1;2},{1,2,3}) 得到一個(gè)2行3列的數(shù)組： {'A','Am','Ama';'m','ma','map'} 可以看到，只有在傳遞給MID函數(shù)的兩個(gè)數(shù)組正交的情況下，才能成功地獲得所需的6個(gè)結(jié)果。如果我們使用公式： =MID(A1,{1,2},{1,2,3}) 返回的不是預(yù)想的6個(gè)元素組成的數(shù)組，而是一個(gè)由3個(gè)元素組成的數(shù)組： {'A','ma',#N/A} 其原因是，當(dāng)兩個(gè)數(shù)組屬于相同的向量類(lèi)型時(shí)，即兩個(gè)都是單行數(shù)組或都是單列數(shù)組，Excel將一個(gè)數(shù)組的元素與另一個(gè)數(shù)組中相應(yīng)位置的元素“配對(duì)”。因此，公式： =MID(A1,{1,2},{1,2,3}) 等價(jià)于執(zhí)行下面3個(gè)公式的結(jié)果： =MID(A1,1,1) =MID(A1,2,2) =MID(A1,,2) 數(shù)組中根本沒(méi)有第三個(gè)元素作為MID函數(shù)的start_num參數(shù)與num_chars參數(shù)中的第三個(gè)元素配對(duì)。這樣，Excel返回#N/A作為結(jié)果數(shù)組中的第三個(gè)元素。 實(shí)際上，Excel為了解決傳遞兩個(gè)大小不同的數(shù)組的問(wèn)題，重新定義了兩個(gè)中較小的一個(gè)，使其匹配較大的數(shù)組。這樣，結(jié)果數(shù)組中任何額外的不配對(duì)的單元格都將填充為#N/A。 在某些情況下，我們接受其中的數(shù)組被“重新定義維數(shù)”，即便使用錯(cuò)誤值填充，前提是我們隨后可以根據(jù)需要對(duì)結(jié)果數(shù)組進(jìn)行操作。 繼續(xù)！我們知道，可以給函數(shù)傳遞多個(gè)單元格。因此，可以構(gòu)造公式： =MID(A1:A9,1,1) 返回一個(gè)9行1列的一維數(shù)組，該數(shù)組由A1:A9中每個(gè)字符串的第一個(gè)字符組成，即： {'A';'E';'P';'C';'R';'B';'M';'A';'A'} 進(jìn)一步擴(kuò)展： =MID(A1:A9,{1,2},1) 返回一個(gè)9行2列的二維數(shù)組： {'A','m';'E','s';'P','e';'C','e';'R','i';'B','a';'M','a';'A','m';'A','c'} 因?yàn)?/span>A1:A9是列向量，所以MID函數(shù)的參數(shù)start_num的值必須是行向量。如果試圖使用公式： =MID(A1:A9,{1;2},1) 結(jié)果將是{“A”,”s”,#N/A, #N/A, #N/A, #N/A, #N/A, #N/A, #N/A}。 再繼續(xù)擴(kuò)展，公式： =MID(A1:C9,{1,2},1) 我們希望其返回由54個(gè)元素組成的數(shù)組，該數(shù)組等于54個(gè)單獨(dú)的MID構(gòu)造的結(jié)果： =MID(A1,1,1) =MID(A1,2,1) =MID(A2,1,1) =MID(A2,2,1) 等等。 但實(shí)際上，結(jié)果是一個(gè)僅包含27個(gè)元素的數(shù)組： {'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函數(shù)如何處理以數(shù)組方式提供的條件》中講解的對(duì)數(shù)組的解析的內(nèi)容。 同樣，改變公式中參數(shù)的向量類(lèi)型： =MID(A1:C9,{1;2},1) 結(jié)果是： {'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} 現(xiàn)在怎么辦呢？Excel對(duì)可以操作的數(shù)組維數(shù)的固有限制，是否意味著我們必須放棄獲取正在尋找的54個(gè)元素?cái)?shù)組的嘗試？ 的確，我們不能改變維數(shù)數(shù)量，但并不是說(shuō)不能通過(guò)其他方式實(shí)現(xiàn)。 在繼續(xù)剛才的MID函數(shù)示例之前，我們以另一個(gè)示例來(lái)解釋。假設(shè)在單元格A1:E10中的數(shù)據(jù)如下圖2所示。 圖2 顯然，這里的數(shù)據(jù)是二維的，是一個(gè)10行5列的數(shù)組，其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'} 但是，由于某些原因，我們需要將上述數(shù)據(jù)放置在一維數(shù)組中： {'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'} 如何得到這個(gè)50行1列的數(shù)組？ （或者，一個(gè)1行50列的數(shù)組： {'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)))))) 其思路是將這個(gè)二維數(shù)組中的每個(gè)元素精確地索引一次，上面的公式轉(zhuǎn)換為： =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}))) 這里的關(guān)鍵是，傳遞給INDEX函數(shù)的數(shù)組（用于參數(shù)row_num和參數(shù)column_num的數(shù)組）都是相同的向量類(lèi)型（均為列向量），從而確保了由INDEX產(chǎn)生的數(shù)組也是這種向量類(lèi)型。根據(jù)其定義，列向量當(dāng)然是一維的。這里使用的技術(shù)請(qǐng)參閱《Excel公式技巧03：INDEX函數(shù)，給公式提供數(shù)組》、《Excel公式練習(xí)44：從多列中返回唯一且按字母順序排列的列表》。 可以看出，INDEX結(jié)構(gòu)具有不可否認(rèn)的優(yōu)勢(shì)，不僅可以將其用于重新定義工作表區(qū)域的維度，還可以重新定義公式中某些其他子函數(shù)產(chǎn)生的數(shù)組的維度。 然而，還可以使用更短的公式： =T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),'R0C00000'),0)) 注意，上述公式結(jié)構(gòu)使用了函數(shù)T，因此要求單元格區(qū)域A1:E10內(nèi)的值是非數(shù)字的。對(duì)于由數(shù)值組成的單元格區(qū)域，可以使用N函數(shù)。對(duì)于包含混合數(shù)據(jù)類(lèi)型的區(qū)域，建議使用INDEX方法。 關(guān)鍵是要利用MODE.MULT函數(shù)的特性來(lái)返回返回一維數(shù)組，無(wú)論傳遞給該函數(shù)的數(shù)組本身是一維數(shù)組還是二維數(shù)組，這都同樣適用。然而，MODE.MULT函數(shù)自身也存在缺點(diǎn)：傳遞的數(shù)組中的任何元素都要至少出現(xiàn)一次，否則將出錯(cuò)，這意味著我們要強(qiáng)制解決該問(wèn)題。因此，這里故意使用了擴(kuò)展的單元格區(qū)域A1:E20： 1+MOD(ROW(A1:E20)-1,10) 轉(zhuǎn)換為： 1+MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1,10) 轉(zhuǎn)換為： 1+MOD({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},10) 轉(zhuǎn)換為： 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} 此時(shí)，公式中的： T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),'R0C00000'),0)) 轉(zhuǎn)換為： 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)) 轉(zhuǎn)換為： 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)) 轉(zhuǎn)換為： 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)) 轉(zhuǎn)換為： 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)) 轉(zhuǎn)換為： 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)) 結(jié)果為： {'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'} 正是我們需要的一維數(shù)組。 回到上文中的MID函數(shù)示例，我們?cè)噲D通過(guò)公式： =MID(A1:C9,{1,2},1) 生成由給定這些參數(shù)的所有54個(gè)排列組成的數(shù)組。使用我們的重新定義維數(shù)的技術(shù)，只需使用A1:C9對(duì)上述公式相應(yīng)位置進(jìn)行替換： MID(T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:C19)-1,10))+COLUMN(A1:C9)),'R0C00000'),0)),{1,2},1) 轉(zhuǎn)換為： 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) 轉(zhuǎn)換為： {'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個(gè)元素。 同樣，我們可以將這項(xiàng)技術(shù)運(yùn)用到“四維數(shù)組”： =MID(A1:C9,{1,2},{1;2;3}) 對(duì)于第二次重新定義數(shù)組維數(shù)，必須使用前面的INDEX構(gòu)造： =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是我們第一次重新定義數(shù)組維數(shù)的公式： =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))) 太復(fù)雜了！腦筋都不夠用了！ 注：本技巧整理自excelxor.com，有興趣的朋友對(duì)照原文研讀，收獲更豐。
|
|
|
來(lái)自： hercules028 > 《excel》
