时间戳

首页 > 解决方案 > Excel 超动态范围与 OFFSET(...COLUMN()) 转换为非易失性

excel - Excel 超动态范围与 OFFSET(...COLUMN()) 转换为非易失性

问题描述

我认为我在“超动态”范围方面取得了突破。我就是这样称呼它们的，因为它们不仅是动态的，而且是动态的。而且它们还根据写入它们的单元格地址来引用不同的范围！现在我需要进一步推进这一突破。那些在 Excel 中使用动态范围的人，尤其是动态范围专家，将会很高兴阅读下面的内容，并且可能有助于这一进步：

免责声明：如果您不熟悉动态范围，请勿尝试阅读以下内容！

背景：我们的工作表具有顶部的计算单元格和下方的数据透视表。上面的每个单元格指的是同一列中下面的数据透视表单元格。数据透视表的第一列（默认为“行标签”）按降序从上到下排序。接下来的每一列都有不同测试的结果。在数据透视表中间行的某处，有一条“标记线”将数据透视表的顶部与底部分开。让我们称顶部为“上部”，底部为“向下”。让我们将这两个部分统称为“人口”。人口是一个不连续的范围，因为将上部与唐纳分开的“标记线”介入。

对于枢轴上方的每个单元格，正好在下方的枢轴列的计算需要引用列本身的上层或下层或人口。

以前，我在上面所有单元格中的公式都是重复以下内容：

= MAX( OFFSET($A$79,$B$5+1,COLUMN()-1,$B$6,1) )

此示例在下面的同一列中给出了 Downers 的最大面积。

在哪里：

将公式的OFFSET...一部分复制到具有高于枢轴的计算的任何单元格时，将始终在同一列中给出唐纳区域。

在这里，我们来到了“超”动态部分：我测试了（它有效！）将一个范围命名为：

冷饮：=OFFSET($A$79,$B$5+1,COLUMN()-1,$B$6,1)

我用引用 Downers 的计算替换了所有单元格，例如：

= MAX( ColDowners )

奇迹般地，所有具有此命名超动态范围的单元格都在同一列中使用正确的 Downers 值进行计算！有时，当最初加载工作簿时，单元格显示为 0 值，但当您按下 F9 计算时，它们会立即获得正确的值（这当然不是问题）。

所以这个范围在两个方面是“动态的”：（1）因为开始行和结束行是动态的（像往常一样）和（2）因为它根据写入的位置产生不同的范围！

现在为了进步。任何动态范围专家的输入都将非常有价值：

OFFSET是具有已知性能问题的易失性函数。INDEX我们可以用冒号（“：”）分隔的两个非易失函数替换它吗？我知道如何命名从特定单元格开始并每次都以不同单元格结束的范围（例如。=A$155:INDEX(...COLUMN()...)）。但是范围的开始和结束都可以被索引[原文如此]？即它可以是类似的东西=INDEX(...COLUMN()...):INDEX(...COLUMN()...)。因此，如果我们用这个公式命名一个范围，它会起作用吗？

答案必须排除易变函数INDIRECT，并且必须尽可能简单。超动态范围公式的结果范围必须有所不同，具体取决于COLUMN()它在工作表中写入的内容（如OFFSET上面的那个），并且必须“能够”从下面的特定行数开始A79（上例中所写的数字B5）并以 中所写的数字结尾B6。

标签： excelexcel-formulaexcel-match

解决方案

定义用于替换“偏移”公式的超动态范围的正确公式：

=OFFSET($A$79,$B$5+1,COLUMN()-1,$B$6,1)

是：

=INDEX($A:$XX,ROW($A$79)+$B$5+1,COLUMN()):INDEX($A:$XX,ROW($A$79)+$B$7,COLUMN())

  • 其中 inB7是枢轴的最后一行的编号。（=像B5+B6加/减 1 或 2 之类的东西 - 针对您的情况进行测试）

如果您使用此超动态公式定义动态范围的名称，它将适应为您提供不同的范围，具体取决于您将其复制到的单元格的位置！它总是会在您的列中为您提供相同的平行范围，并且会在不同的列中产生不同的结果！我对其进行了测试，它运行良好，而且它使我的计算速度更快，因为INDEX它是非易失性的（而不是OFFSET）。

上面示例的另一个提示：我还尝试了嵌套范围名称并且它们有效！例如，我将 ColPopulation 的范围定义为：

=(ColUppers,ColDowners)

请注意，这是一个超动态嵌套范围名称！当然，它只能用于简单的函数（例如=MAX(ColPopulation)），而不能SUMPRODUCT用于需要连续范围的函数。尽管如此，知道您可以通过使用逗号添加其他范围名称来定义范围是一件非常有用的事情！

来自所有相关人员的大力帮助！非常感谢！

推荐阅读