Excel公式技巧中的降维技术

时间：2021-09-28 来源：互联网编辑：宝哥软件园浏览：次

看过前面一系列文章的朋友会对“重新定义数组维数”这个概念比较熟悉。这是一项非常有用和重要的技术，它使我们能够接受二维数组并将其转换为一维数组，同时将元素保留在数组中。

如果您想进一步操作2D数组的元素，您需要使用这种技术。例如，出于某种原因，在某些情况下，有必要将2D数组中的每个元素传递给一个或多个参数，以便进一步处理。但是由于需要使用的Excel函数无法处理多维数组，所以必须先将原始数组转换为一维数组。

举例来说，下图1所示的工作表。

图1

您可以构建各种公式，例如：

=MID(A1，1，1)

结果显然是“一”。

以下公式：

=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”；“女士”、“女士”；“Ama”，“地图”}

公式中两个参数值的数组相互正交，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函数的两个数组正交时，才能成功获得所需的六个结果。如果我们使用公式：

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

返回的不是预期的6个元素的数组，而是3个元素的数组：

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

原因是当两个数组属于同一向量类型，即都是单行数组或单列数组时，Excel会将一个数组的元素与另一个数组中对应位置的元素“配对”。因此，公式为：

=MID(A1，{1，2}，{1，2，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中每个字符串的第一个字符组成，即：

{“一”；“E”；“P”；“C”；“R”；“B”；“M”；“一”；{ A }

进一步扩展：

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

返回一个9行2列的二维数组：

{“A”、“m”；“E”、“s”；“P”、“e”；“C”、“e”；“R”、“I”；“乙”、“甲”；“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个元素的数组：

{“一”、“一”、#不适用；“E”、“A”、#不适用；“P”、“l”、#不适用；“C”、“A”、#不适用；“R”、“o”、#不适用；“乙”、“我”、“不适用”；“M”，“o”，#不适用；“一”、“我”、“不适用”；“一”、“我”、“不适用”

请参考《Excel公式技巧06： COUNTIFS函数如何处理以数组方式提供的条件》中解决方案中数组解析的内容。

同样，更改公式中参数的向量类型：

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

结果是：

{“A”、“S”、“P”；“s”、“a”、“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”

通常使用以下公式：

=INDEX(A1:E10，N(IF(1，1)(INT((ROW(INDEX(a : a，1):INDEX(A:A，ROW(a 1: e 10)* COLUMNS(a 1: e 10)))-1)/COLUMNS(a 1: e 10)))))，N(IF(1，1(MOD((ROW(INDEX(a 3360 a，1):INDEX(A:A，ROW(a 133: a)

其思想是将这个二维数组中的每个元素精确索引一次，上面的公式转换为：

=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结构具有不可否认的优势，不仅可以用来重新定义工作表区域的维度，还可以用来重新定义公式中其他一些子函数产生的数组的维度。

但是，也可以使用更短的公式：

=间接(文本(模式。MULT(10^5*(1 MOD(行(A1:E20)-1，10))列(A1:E10))、“R0C0000 ”)、0))

注意，上面的公式结构使用了函数t，所以单元格区域A1:E10中的值要求为非数字。对于由数值组成的单元格区域，可以使用n函数。对于混合数据类型的区域，建议使用INDEX方法。

关键是要利用MODE的特性。MULT函数返回一维数组，这同样适用于传递给函数本身的数组是一维数组还是二维数组。然而，模式。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}

此时，在公式中：

间接(文本(模式。MULT(10^5*(1 MOD(行(A1:E20)-1，10))列(A1:E10))、“R0C0000 ”)、0))

转换为：

间接(文本(模式。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)))

转换为：

间接(文本(模式。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))

转换为：

间接(文本(模式。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(间接的(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(INternet({“R1C 00001”；)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))

结果是：

我们需要的只是一维数组。

回到上面的MID函数示例，我们尝试使用公式：

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

我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说.杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生、杨俊钦先生：

MID(间接（文字模式。MULT(10^5*(1国防部(列(a 1: c19)-1.10))、COLUMN(A1:C9))、R0C0000)、0))、1.2 }、1)

菲兰达：

mid({ amapa " "；不透明";"巴拉那";"圣灵";"santacatarina "马拉尼昂";"伯南布哥";"阿拉戈阿斯";"大家伙";"凯撒";"停止";"我喜欢";"南里奥格兰德";"朗多尼亚";"托坎廷斯";"巴伊亚";"地雷";"罗赖马";"大剂量杀手";"戈亚斯";"住手";"亚马逊";"北大略湖";"里约热内卢";"英亩";"大区";"sergipe " }，{1，2}，(1)

菲兰达：

{“a”、“m”；“s”、“an”；“p”、“a”；“e”、“s”；“s”、“a”；“m”、“a”；“p”、“e”；“a”、“l”；“m”、“a”；“c”、“e”；“p”、“a”；“p”、“我”；“r”、“I”；“r”、“o”；“t”、“o”；“b”、“a”；“米”、“我”；“r”、“o”；“m”、“a”；“g”、“o”；“p”、“a”；“a”、“m”；“r”、“I”；“r”、“I”；“a”、“c”；“d”、“我”；“s”、“e”

范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹，范仲淹。

吴仪、吴仪、"吴仪、"吴仪、":

=MID(A1:C9，{1，2}，{ 1 }；2 .2 .3})

杨俊钦是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子，他是杨俊钦的儿子。

=MID(INDEX(ReDim1，N(IF，1，int)(行(index(a : a，1):INDEX(A:A，行(redim 1)*列(redim 1)))-1)/列(redim 1)))))))))；n(if，1(mod(index(row(a : a，1):INDEX(A:A，row(redim 1)* columns(redim 1))))))))))；1,{1

谢赫谢赫谢赫谢赫谢赫谢赫谢赫谢赫谢赫：

=MID(间接（文字模式。MULT(10^5*(1国防部(列$ 1: $ c $ 19)-1.10)，栏($1:$C$9))、R0C0000)、(0))、(1.2)、最大（列($ 1: $ 9加元))

阿叔呀！高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽高丽！

Excel公式技巧中的降维技术

新天龙八部

热门手游排行榜