使用Excel公式从表格数据动态构建数组

Question

我不确定是否可以使用单个单元格公式完成此操作。希望使用与Excel 2010 兼容（及以上版本）的公式。

目的是使用如下公式：

{=SUM(INDEX(built_Array;N(IF(1;ROW(INDIRECT(x1 &":"& x2)))))} 

x1和x2是引用包含起始索引和结束索引的单元格，这些索引与构建的数组兼容。

Excel公式中的“构建的数组”部分应由其他两个表中的数据构建：在要选择实际内容的表上的标识符（使用Excel查找函数）。一个表包括重复值的数量，另一个表包括实际值。

例如：

	P1	P2	P3		P1	P2	P3
i01	2	4		i01	20.0	20.6
i02	3			i02	10.0
i03	2	7	9	i03	30.0	30.4	30.2
i04	4	2		i04	15.0	15.1
i05	5			i05	10.0

因此，i03的构建数组将会是：

{30.0;30.0;30.4;30.4;30.4;30.4;30.4;30.4;30.4;30.2;30.2;30.2;30.2;30.2;30.2;30.2;30.2;30.2} 

i04 对应的是

{15.0;15.0;15.0;15.0;15.1;15.1}

接下来，使用上述数组（ctrl-shift-enter）公式对i04取第三到第五个值的结果为45.1，对i04取第二到第三个值的结果为30。

我在构建公式中遇到困难的是从表格中构建数组，特别是由于每个标识符可能具有不同数量的组件。

我有预感这应该是可行的，在其他编程语言中会使用迭代或递归，但我想探索这种方式，而不是回到VBA（如果在Excel公式中这种方法不可能存在概念上的原因，我也很感兴趣，以防我需要改变方法，因为我对公式结果感兴趣）。

- user1938578

---

所以你基本上需要动态地对这些值进行求和？因此，输入i04应该返回90.2？ - Evil Blue Monkey

@EvilBlueMonkey 不是的 - 他需要返回值 y，其中 y 来自右表格，x 来自左表格，并重复 x 次。 - urdearboy

是的，但在此之后，您不是要从x1到x2对built_Array的一部分进行求和吗？因此，如果输入为i04 1到6，则输出应为90.2，对吗？i04 2到3会返回30，对吗？ - Evil Blue Monkey

我想出了一个可能的解决方案（直接计算总和，而不是创建数组），但需要花费一些时间来适当地解释并发布它。 - Evil Blue Monkey

Answer 1

经过一些研究，已经找到了完整的可行答案（其中包括stackoverflow和贡献者的建议，非常感谢）。

方法1（首选）

以下单元格CSE（数组）公式使用了几种以下变化。

=((ROW(INDIRECT("1:"& e1))>=lb1)*(ROW(INDIRECT("1:"& e1))<=ub1))

其中e1是一列数组中元素的数量，lb1是需要为其指定为1的下限，ub1是上限，超出边界的元素值为0。

针对建议的公式，OP数据需要转换为简单的累积索引。

        P1  P2  P3          P1      P2      P3
i01     2   6   6   i01     20.0    20.6    
i02     3   3   3   i02     10.0        
i03     2   9   18  i03     30.0    30.4    30.2
i04     4   6   6   i04     15.0    15.1    
i05     5   5   5   i05     10.0        

以下是一个CSE公式，应用于与现有列相同数量的范围（例如上面的示例：3个连续列），然后在公式栏中插入公式后，使用ctrl-shift-enter。

公式

{=TRANSPOSE(MMULT(TRANSPOSE(((ROW(INDIRECT("1:"&e1))>=IF(COLUMN(set_of_pos)-COLUMN(INDEX(set_of_pos;1;0)=0;0;OFFSET(set_of_pos;0;-1))+1)*(ROW(INDIRECT("1:"&e1))<=set_of_pos))*((ROW(INDIRECT("1:"&e1))>=x2+1)*(ROW(INDIRECT("1:"&e1))<=x1)));--(ROW(INDIRECT("1:"&e1))>0)))}

其中，e1 是第一列数组中元素的数量（也是最后一个位置的值），x1 和 x2 是引用包含起始索引和结束索引的单元格；set_of_pos 是范围，告诉每个部分中每个位置的最后位置（例如，对于 i03：2,9,18）

PARTS ((ROW(INDIRECT("1:"&e1))>=x2+1)*(ROW(INDIRECT("1:"&e1))<=x1))) 创建一个由连续的 1 组成的（在公式中）数组，由表示需要保留或注册的索引的 0 限制。 （尺寸为 e1 × 1。）

((ROW(INDIRECT("1:"&e1))>=IF(set_of_pos=1;0;OFFSET(set_of_pos;0;-1))+1)*(ROW(INDIRECT("1:"&e1))<=set_of_pos))创建了一个大小为 e1 × columns(set_of_pos) 的（在公式中的）数组，在其中每个部分单元格都将该值扩展成一堆 1。[在这部分中，对于每列，上限是实际值，下限是左边的值加 1 - 因为 set_of_pos 是一个范围，只要使用 CSE 输入公式，就会针对 set_of_pos 中的每列执行此操作；注意：如果它是最左边的单元格，则使用 0。]

然后两个部分都使用 * 运算符进行操作，从而获得一个大小为 e1 × columns(set_of_pos) 的（在公式中的）数组，其中满足在部分堆栈中且在要保留的索引集中的位置被方便地分离在列中。

我们需要转置这个结果，得到一个（在公式中）维数为列数（set_of_pos）× e1的数组。MMULT（和全1的e1 × 1定义为--（ROW（INDIRECT（“1：”&e1））>0）使我们能够计算压缩数组中每一列中1的数量，并将其放入不同列[columns(set_of_pos) × 1]中。[注意，我们需要包含双重否定“--”来将布尔值转换为数字，因为MMULT需要数字数组，否则它会抛出错误 - 根据函数规格，MMULT不接受布尔值。]

还需要进行额外的转置以将数组横向排列[1 × columns(set_of_pos)]。

现在，最终结果可以通过=SUMPRODUCT(previous_result;set_of_val)获得，其中previous_result是上面的结果，set_of_val是带有值的范围。

保留哪些堆栈被修改的方法还有其他用途（可以计算已用堆栈），而获得最终结果只需应用函数SUMPRODUCT即可。列数或部分数不是硬编码的 - 它由在公式范围内选择set_of_pos并在应用（CSE）数组公式时选择适当数量的连续列来设置。

方法2 为了完整起见，我们探索了第二种方法。如果认为这种方法的资源消耗较少，我肯定想知道。我的直觉是它更加资源密集，尽管打字较少，但我无法进行测试。

基本上，一个包含所有值的字符串被构建到一个单元格中（例如，使用REPT和竖杠|，对于i03，我们将有“30.0 | 30.0 | 30.4 | 30.4 | 30.4 | 30.4 | 30.4 | 30.4 | 30.4 | 30.2 | 30.2 | 30.2 | 30.2 | 30.2 | 30.2 | 30.2 | 30.2 | 30.2 |”）

一个可行的解决方案（不需要CSE - SUMPRODUCT足够处理三个适当构建的1列数组）

=SUMPRODUCT(((ROW(INDIRECT("1:"& e1))<=x1)*(ROW(INDIRECT("1:"&e1))>=x2+1))*TRIM(MID(SUBSTITUTE(s1;"|";REPT(" ";LEN(s1)));(ROW(INDIRECT("1:"& e1))-1)*LEN(s1)+1;LEN(s1))))

其中x1、x2和e1是引用包含起始索引和结束索引以及总元素数量（如上所定义）的单元格，s1是按顺序包含所有值的字符串。

零件

(ROW(INDIRECT("1:"& e1))<=x1)*(ROW(INDIRECT("1:"&e1))>=x2+1))

基本上在 METHOD 1 中存在：一个由连续数量的1（来自布尔值）组成的（公式内的）数组，由0限定表示需要保留的索引。（尺寸为 e1 × 1。）

TRIM(MID(SUBSTITUTE(s1;"|";REPT(" ";LEN(s1)));(ROW(INDIRECT("1:"& e1))-1)*LEN(s1)+1;LEN(s1)))

该公式接受一个字符串（s1），并将每个分隔符替换为与字符串长度相等的空格数量，然后将不同的部分放置在由ROW(INDIRECT("1:"& e1))创建的数组的每个元素上。

[更一般的解决方案是通过获取字符串和去除分隔符的字符串之间的差异来获得元素的数量，但在我的小项目中需要元素的数量，并在标签e1下定义。这种技术在其他地方已经简洁地解释了，它将原始字符串长度为序列号乘以ROW(INDIRECT("1:"& e1))的长度开始的部分放置在序列号给定的位置，然后使用TRIM删除所有周围的空格。]

致谢和参考文献

感谢答案和评论，我们找到了两个具有相关评论的stackoverflow问题。

https://dev59.com/-F8e5IYBdhLWcg3w2NNR#25316426[点击此处进入]，以及在Excel中是否有一种不使用VBA连接两个数组的方法？

这两篇文章都值得一读。第一种方法部分是从头开始开发的，但第二种方法通过仔细阅读得到了很大的改进，特别是将字符串转换为公式数组。选择ROW(INDIRECT())而不是SEQUENCE()，因为需要与Excel 2010兼容。

- user1938578

---

我已经私下被要求使用最终版本，因为公式编辑很小。这可能会使公式稍微不太清晰，但大部分需要相同的解释。现在，它已经更新。 - user1938578

Answer 2

为了达到我们的目标，我们需要创建一系列数组，方便地“混合”，将得到我们需要相加的一系列值。整个过程在此处以图形方式呈现：

在左侧是源数据和用户的输入。假设源数据（起始数组）由两个连续单元格（区域）组成。除了这两个范围之外，所有其他数据都将通过名称表达/计算。以下是所需的名称：

名称	参考R1C1
目标	=Sheet1!R4C12 <<<此处应返回要搜索的值（例如：“i03”）
MLR矩阵地址	=Sheet1!R5C12 <<<此处应返回一个字符串，报告乘数数组的地址（例如：“B5:D9”）
MLD矩阵地址	=Sheet1!R6C12 <<<此处应返回一个字符串，报告被乘数组的地址（例如：“G5:I9”）
起始值	=Sheet1!R7C12 <<<此处应返回公式将进行求和的起始值（例如：2）
结束值	=Sheet1!R8C12 <<<此处应返回公式将进行求和的结束值（例如：11）
数组01	=(((ROW(INDIRECT("1:"&MLR_Max_Val)))^1)+(COLUMN(INDIRECT("C1:C"&MLR_Col_Cnt,FALSE))^1-1)*MLR_Max_Val)
数组02	=((ROW(INDIRECT("1:"&MLR_Max_Val)))^1*(COLUMN(INDIRECT("C1:C"&MLR_Col_Cnt,FALSE))^0))
数组03	=Array01/((Array02<=INDIRECT(MLR_Tar_Rel_Row_Addr)))
数组04	=AGGREGATE(15,6,Array03,From)
数组05	=AGGREGATE(15,6,Array03,To)
数组06	=IFERROR((Array03>=Array04)*(Array03<=Array05)*INDIRECT(MLD_Tar_Rel_Row_Addr),0)
MLD目标相关行	=MATCH(Target,OFFSET(INDIRECT(MLD_Mtrx_Addr),0,-1,,1),0)
MLD目标相关行地址	=SUBSTITUTE(CELL("address",OFFSET(INDIRECT(MLD_Mtrx_Addr),MLD_Tar_Rel_Row-1,0,1,1))&":"&CELL("address",OFFSET(INDIRECT(MLD_Mtrx_Addr),MLD_Tar_Rel_Row-1,COLUMNS(INDIRECT(MLD_Mtrx_Addr))-1,1,1)),"$","")
MLR列数	=COLUMNS(INDIRECT(MLR_Mtrx_Addr))
MLR最终值数	=SUM(INDIRECT(MLR_Tar_Rel_Row_Addr))
MLR最大值	=MAX(INDIRECT(MLR_Tar_Rel_Row_Addr))
MLR目标相关行	=MATCH(Target,OFFSET(INDIRECT(MLR_Mtrx_Addr),0,-1,,1),0)
MLR目标相关行地址	=SUBSTITUTE(CELL("address",OFFSET(INDIRECT(MLR_Mtrx_Addr),MLR_Tar_Rel_Row-1,0,1,1))&":"&CELL("address",OFFSET(INDIRECT(MLR_Mtrx_Addr),MLR_Tar_Rel_Row-1,COLUMNS(INDIRECT(MLR_Mtrx_Addr))-1,1 注意 前五个名称仍与范围相关。 这些是用户的输入数据。 用户应根据侧注要求相应地修改其ReferenceToR1C1。 这段代码应该能够快速创建所有所述的名称： Sub SubAddNames() With ActiveWorkbook.Names .Add name:="Target", RefersToR1C1:="=Sheet1!R4C12" .Add name:="MLR_Mtrx_Addr", RefersToR1C1:="=Sheet1!R5C12" .Add name:="MLD_Mtrx_Addr", RefersToR1C1:="=Sheet1!R6C12" .Add name:="From", RefersToR1C1:="=Sheet1!R7C12" .Add name:="To", RefersToR1C1:="=Sheet1!R8C12" .Add name:="Array01", RefersToR1C1:="=(((ROW(INDIRECT(""1:""&MLR_Max_Val)))^1)+(COLUMN(INDIRECT(""C1:C""&MLR_Col_Cnt,FALSE))^1-1)*MLR_Max_Val)" .Add name:="Array02", RefersToR1C1:="=((ROW(INDIRECT(""1:""&MLR_Max_Val)))^1*(COLUMN(INDIRECT(""C1:C""&MLR_Col_Cnt,FALSE))^0))" .Add name:="Array03", RefersToR1C1:="=Array01/((Array02<=INDIRECT(MLR_Tar_Rel_Row_Addr)))" .Add name:="Array04", RefersToR1C1:="=AGGREGATE(15,6,Array03,From)" .Add name:="Array05", RefersToR1C1:="=AGGREGATE(15,6,Array03,To)" .Add name:="Array06", RefersToR1C1:="=IFERROR((Array03>=Array04)*(Array03<=Array05)*INDIRECT(MLD_Tar_Rel_Row_Addr),0)" .Add name:="MLD_Tar_Rel_Row", RefersToR1C1:="=MATCH(Target,OFFSET(INDIRECT(MLD_Mtrx_Addr),0,-1,,1),0)" .Add name:="MLD_Tar_Rel_Row_Addr", RefersToR1C1:="=SUBSTITUTE(CELL(""address"",OFFSET(INDIRECT(MLD_Mtrx_Addr),MLD_Tar_Rel_Row-1,0,1,1))&"":""&CELL(""address"",OFFSET(INDIRECT(MLD_Mtrx_Addr),MLD_Tar_Rel_Row-1,COLUMNS(INDIRECT(MLD_Mtrx_Addr))-1,1,1)),""$"","""")" .Add name:="MLR_Col_Cnt", RefersToR1C1:="=COLUMNS(INDIRECT(MLR_Mtrx_Addr))" .Add name:="MLR_Fin_Val_Cnt", RefersToR1C1:="=SUM(INDIRECT(MLR_Tar_Rel_Row_Addr))" .Add name:="MLR_Max_Val", RefersToR1C1:="=MAX(INDIRECT(MLR_Tar_Rel_Row_Addr))" .Add name:="MLR_Tar_Rel_Row", RefersToR1C1:="=MATCH(Target,OFFSET(INDIRECT(MLR_Mtrx_Addr),0,-1,,1),0)" .Add name:="MLR_Tar_Rel_Row_Addr", RefersToR1C1:="=SUBSTITUTE(CELL(""address"",OFFSET(INDIRECT(MLR_Mtrx_Addr),MLR_Tar_Rel_Row-1,0,1,1))&"":""&CELL(""address"",OFFSET(INDIRECT(MLR_Mtrx_Addr),MLR_Tar_Rel_Row-1,COLUMNS(INDIRECT(MLR_Mtrx_Addr))-1,1,1)),""$"","""")" End With End Sub 一旦名称正确设置，最终公式将是： =IF(OR(INT(From)<>From,INT(To<>To),To>MLR_Fin_Val_Cnt,From>To),#VALUE!,SUM(IFERROR(Array06,0))) 这个公式只能作为数组公式使用，因此我们需要将其复制，选择所需的单元格，粘贴该公式，然后按Ctrl+Shift+Enter。

- Evil Blue Monkey

---

谢谢你，邪恶的蓝猴子。非常感谢你的努力。我自己也有一个可行的解决方案，计划很快发布。你的解决方案看起来非常好，但我相信我已经成功地使用布尔值动态构建了数组。= ((ROW(INDIRECT("1:"& e1))>=lb1)*(ROW(INDIRECT("1:"& e1))<=ub1))，其中e1是1列数组中元素的数量，lb1是需要1的最低索引，ub1是1的最高位置，其余的数组元素填充为0。 - user1938578

Answer 3

进一步阅读问题后，我们可以采用避免使用易失性函数的版本。如果需要一个非易失性的 Method 1 工作公式，可以通过将易失性的 INDIRECT 替换为非易失性的 INDEX 引用形式函数来实现。

总体表达式：

((ROW(INDIRECT("1:"& n1))>=lb1)*(ROW(INDIRECT("1:"& n1))<=ub1))

转换为

((ROW($A$1:INDEX($A:$A;n1;0))>=lb1)*(ROW($A$1:INDEX($A:$A;n1;0))<=ub1))

其中n1是1列数组中元素的数量，lb1是需要1的下限，ub1是上限。

OFFSET是另一个易变函数，仅使用一次（从左侧单元格获取下限）。它可以用INDEX数组形式函数替换：

IF(COLUMN(set_of_pos)-COLUMN(INDEX(set_of_pos;0;1))=0;0;OFFSET(set_of_pos;0;-1))+1

转换为

IF(COLUMN(set_of_pos)-COLUMN(INDEX(set_of_pos;0;1))=0;0;INDEX(set_of_pos;0;COLUMN(set_of_pos)-COLUMN(INDEX(set_of_pos;0;1)))+1

同样地，从普通的网格创建一个数组（-1 是偏移代码）。

IF(COLUMN($A$1:INDEX($1:$1;0;COLUMNS(set_of_pos)))=1;0;INDEX(set_of_pos;0;COLUMN($A$1:INDEX($1:$1;0;COLUMNS(set_of_pos)))-1))+1

]

其中set_of_pos是水平范围，它告诉原始帖子每个部分的最后位置（因此，当它是第一个-最左边的单元格时，公式部分会给出0 + 1，而左侧单元格的值为1 +，从而获得了部分的下限）。

选择一定数量的部分单元格的水平范围后，需要使用Ctrl-Shift-Enter输入公式。

{=TRANSPOSE(MMULT(TRANSPOSE(((ROW($A$1:INDEX($A:$A;e1;0))>=IF(COLUMN(set_of_pos)-COLUMN(INDEX(set_of_pos;0;1))=0;0;INDEX(set_of_pos;0;COLUMN(set_of_pos)-COLUMN(INDEX(set_of_pos;0;1)))+1)*(ROW($A$1:INDEX($A:$A;e1;0))<=set_of_pos))*((ROW($A$1:INDEX($A:$A;e1;0))>=x2+1)*(ROW($A$1:INDEX($A:$A;e1;0))<=x1)));--(ROW($A$1:INDEX($A:$A;e1;0))>0)))}

其中e1是1列数组中的元素数量（或者是一个包含该数量的单元格引用），x1和x2是包含起始索引和结束索引的单元格引用；set_of_pos是RANGE，它告诉了每个部分中最后位置的定义，这些定义使用了不稳定函数。（给定源数据，可以通过表达式INDEX(set_of_pos;0;COLUMNS(set_of_pos))获取e1。）

[值得注意的是，由于公式中所有INDEX函数使用了一维范围引用，可以省略0值参数，因为INDEX会正确地处理1维度的额外参数。]

在这里找到了ROW(INDIRECT())的非稳定方法 [在“Without the volatile”中搜索，Scott Craner的评论]。

- user1938578

Answer 4

这是我解决问题的方法：

=SUMPRODUCT((B2:D6*B10:D14)*(RIGHT(A2:A6)>=RIGHT(X1))*(RIGHT(A2:A6)<=RIGHT(X2)))

SUMPRODUCT()函数允许我们将其作为数组进行评估，'=SUM()' + CSE也是一种同样有效的解决方案。

(B2:D6*B10:D14)是我们的数组。我浪费了很多时间试图强制数组评估 {20, 20, 20.6, 20.6, 20.6, 20.6}，直到我意识到我们只是要总计整行。我不会详细说明，但如果您想这样做，请从这里开始使用REPT函数（https://exceljet.net/formula/text-split-to-array）。

(RIGHT(A2:A6)>=RIGHT(X1))*(RIGHT(A2:A6)<=RIGHT(X2))，由于最后一个字符是唯一的升序数字，我只需在X1引用结尾的数字小于或等于行号时将其评估为TRUE（），当然对于X2相反。

布尔值TRUE和FALSE可以进行代数运算，其中TRUE为1，FALSE为0。因此，我只需将它们相乘以丢弃搜索区域外的任何值。

- I like Excel very much

---

相信公式不行，特别是如果约束条件适用于任意名称的P1-P3（RIGHT（A2：A6）对我来说似乎是这样）。提出了两种值得审查的方法。1-REPT Excel函数可以实现字符串方法，但我会避免硬编码最大组件数。2-SUMPRODUCT方法，将把一个数组（对于i03 {2;7;9}）转换为另一个数组，具体取决于起始和结束时间，如果是第3到第12个，则为{0;7;3}，这可以通过使用x1和x2来实现，更喜欢可以更改范围的通用公式。如果我误解了什么，请原谅。 - user1938578

@user1938578，我认为你走在正确的道路上，这正是挑战可能性极限的时候。但这是我最喜欢的问题类型，因为它能够推动我的学习极限，让我学到新的东西。我误解了问题，我会保留原始的“答案”以供后人参考。如果我稍后找到更好的答案，我会通过编辑将其放在顶部。 - I like Excel very much