☰

如何使用包含未溢出数组的单元格进行计算？

tag-icon • tag-icon microsoft-excel worksheet-function
如何使用包含未溢出数组的单元格进行计算？

我有一个从 SQL 查询返回的表，其中包含以数组格式保存的各种值列（例如={1,2,3}）。由于存储在其中的信息，每个数组的大小各不相同且不一致。我需要能够获取此数组列并使用它们对其值执行诸如AVERAGE或之MEDIAN类的函数。

最初，我以为我可以简单地选择范围然后就可以了，但是这只会给我一个基于单元格的可见值（数组中的第一个值）的结果，或者Spill如果我弄乱了该列中的值并导致 excel 将该单元格识别为数组，则会出现错误。

我如何才能将这些数组转换为可用数组以供我使用公式？

答案1

您所描述的绝对是可能的。（除非我严重误解了您的描述。）

您的表格中填充了一列单元格，其中包含所述数组。假设您正在考虑将表格视为该列（当然！），并将其填充的所有列分散在每行的数组中。假设列是 A 列，最长的数组是一个 25 个元素的数组，因此该行扩展为包括在 X 列中显示一个值。（注意：这里只选择一列。它可以有 Excel 支持的任意数量的元素。稍后会详细介绍。）如果您有 35,000 行，那么表格将填充范围 A1:X35000 以满足我们的目的。

您无法在一个公式中轻松解决该范围。您可以做到，但更简单的方法加上几个变化才是可行的方法。因此，这些方法以及其他一些方法：

  1. 只需在公式中引用该范围即可。就这么简单。

  2. 选择一个“辅助区域”……也许是一个专门用于此目的的单独工作表，或者类似于所谓的“辅助列”，这将是一个“辅助表”。只需=A1:X35000在单元格 A1 中键​​入内容，就会出现一个数组，其中包含您输入的所有数据材料，它显示在单元格中，但这是一个单独的数组 SPILL-ing，可以使用您想要执行的任何操作直接寻址。您可以使用实际的工作表名称和单元格输入一些地址，例如“Sheet2!A1#”。

  3. 创建一个命名区域，并在其Refers to框中输入地址“A1:X35000”。现在具有与上述 1 中相同的功能，但没有辅助区域。

  4. 使用LET()并定义具有名称和“A1:X35000”地址的范围： =LET( Range,A1:X35000, MEDIAN(Range) )。

鉴于这些方法很容易，更难的方法就不堪设想了。

我尝试使用由三个元素组成的 39,312 行数组执行上述每一项操作，并且全部成功：因此，Excel 使用的数组大小没有明显的限制，这可能与单元格字符串长度限制有关，因为所有这些数组的最大长度都是 32,767 个字符，而我使用的数组长度是这个数字的四倍左右。

这里的本质是您有一系列数组，但无法以您需要的方式直接使用它们。您似乎正在使用范围寻址，例如（使用相同的示例）“A1:A35000”或“A1#”。前者将仅获取每个数组的第一个元素，同样，“B1:B35000”将选择所有第二个元素，依此类推（后者仅提供第一个单元格的数据）。Excel 将每个数组完全放在其所在的单元格中。因此，将其中一个数组放在 A1 中，虽然它显示为在 A1:A3 中，但它完全存储为 A1 的内容。但是，为了使功能直接可用而无需中间级别的函数，Excel 会处理寻址，因为您希望寻址的单元格是数据实际所在的位置。因此，所有中间工作都在“幕后”。

这意味着您得到了所描述的情况。所有数据都在“A1:A35000”中，但必须在其显示的位置进行寻址（“A1:X35000”）。

如上所述，每个单元格都是其自己的线性数组，而不是更大的二维数组中的数组元素。您必须通过寻址显示范围覆盖的整个单元格块来创建更大的数组。

继续……您可以应用所有已知的或可以找到的技术来使您的范围地址动态化。只是有些技术需要更巧妙一些。例如，Excel 将元素视为显示单元格中的元素以用于公式，因此进行上述寻址。这也意味着您可以直接访问 A1 的实际内容。但它是一个公式，所以要对其进行FORMULATEXT()操作。这意味着一个公式采用该输出并找到其LEN()，然后从中减去其公式字符串的长度（SUBSTITUTE()在其上应用逗号以删除逗号）再加上 1，就会得到元素的数量，这也是它显示的列数。因此，对 A 列执行此操作并将其包装起来MAX()会告诉您的公式显示结果的表格有多宽。因此，尝试一种技术来了解您想要什么，如果失败，请在上试一试FORMULATEXT()。

如果您可以得到不同的输出，那肯定是一种可行的方法。通常，在这种情况下，这是最好的方法。但是按原样处理并得到您想要的结果非常非常容易和“干净”。您只需要中间步骤将线性数组的列更改为单个二维数组。

相关内容