我正在尝试在没有 VBA、宏或 Power Query 的情况下在 Excel 中完成此任务。
这是一个工作簿，其中包含示例数据表、中间表和显示所需结果的表：https://1drv.ms/x/s!AiN1s6QeAJ28nCoh1gYWmXAXaw2L?e=Xr3JoP
问题的关键在于从中间表获得期望的结果。我想将不同长度的数组堆叠到一个主数组中。但是，我无法获得VSTACK()引用另一个单元格的评估输出的最终代码。相反，只有手动将每个单独的数组公式输入代码中，我才能获得最终代码VSTACK()。显然，我不想这样做。
这是可行代码，它生成一个包含 13 列和可变行数的数组，从工作簿中的其他工作表中提取数据。IFERROR如果没有匹配的工作表名称或该工作表上的条目数为 0，则计算结果为空白。HSTACK检索工作表名称并将具有该工作表名称的列附加到HSTACK公式中的另一个数组。第二个数组用于HSTACK查找INDIRECT列 A:A 中列出的工作表名称，并根据列 B:B 中找到的最高数字返回该工作表中的列 B:M，其行数可变。
=IFERROR(HSTACK(TRANSPOSE(TEXTSPLIT(REPT(INDIRECT("'"&'Example intermediate sheet'!$A2&"'!$B$4")&"|",IFERROR(MAX(INDIRECT("'"&'Example intermediate sheet'!$A2&"'!B:B")),"")),"|",,TRUE)),INDIRECT("'"&'Example intermediate sheet'!$A2&"'!$B$13:M"&SUM(IFERROR(MAX(INDIRECT("'"&'Example intermediate sheet'!$A2&"'!B:B")),""),12))),"")
我的问题是采用这个等式并将多个条目组合成最终VSTACK公式。如果在 D:D 列中输入上述代码，我想执行VSTACK类似于此的最终操作：
=VSTACK('Sheetname'!D:D)甚至=VSTACK(D2:D56)
但是，当我尝试引用包含中间代码的单元格时，我得到的只是列 D:D 中显示的内容，其中包含很多#SPILL!，但不包括每个数组的其他列。我尝试了各种形式的，FORMULATEXT但INDIRECT都没有成功。到目前为止，我发现唯一能实现最终目标的成功公式如下，但这个例子只合并了 2 个数组，而不是我需要合并的数百个数组。
=VSTACK(IFERROR(HSTACK(TRANSPOSE(TEXTSPLIT(REPT(INDIRECT("'"&'Example intermediate sheet'!$A2&"'!$B$4")&"|",IFERROR(MAX(INDIRECT("'"&'Example intermediate sheet'!$A2&"'!B:B")),"")),"|",,TRUE)),INDIRECT("'"&'Example intermediate sheet'!$A2&"'!$B$13:M"&SUM(IFERROR(MAX(INDIRECT("'"&'Example intermediate sheet'!$A2&"'!B:B")),""),12))),""), IFERROR(HSTACK(TRANSPOSE(TEXTSPLIT(REPT(INDIRECT("'"&'Example intermediate sheet'!$A3&"'!$B$4")&"|",IFERROR(MAX(INDIRECT("'"&'Example intermediate sheet'!$A3&"'!B:B")),"")),"|",,TRUE)),INDIRECT("'"&'Example intermediate sheet'!$A3&"'!$B$13:M"&SUM(IFERROR(MAX(INDIRECT("'"&'Example intermediate sheet'!$A3&"'!B:B")),""),12))),""))