对 Excel 表中的公式进行排序

对 Excel 表中的公式进行排序

对于我的项目,我需要根据到不同地点的最短驾驶时间来安排地点。我模拟了驾驶距离(D、E 和 F 行),但高度取决于在不同级别输入的产品。公式采用驾驶距离(D、E 和 F)和高度(G 行)并计算驾驶时间(L、M 和 N 行)。

我现在想对行驶时间结果进行排序,以便输入产品,但这会破坏表格,因为公式会出现错误。我怎样才能让表格按时间排序而不破坏我的公式?

图一显示的表格有 6000 多个单元格长(因此无法手动输入)

表格概览

图二显示了时间的公式,它基本上将结果四舍五入到小数点后三位(AFRONDEN = ROUNDING),并使用公式检查高度是否为零,使其成为地板水平,或更高,使其成为空中的位置。

时间公式

图三显示了公式排序时发生的情况,它基本上给出了“值”和“参考”错误。

排序后出错

这些是 G 行中的公式

G 行中的公式

我希望有人能帮助我解决这个问题

答案1

看起来您的 Col G(高度)有一个公式,该公式依赖于当前行上方或下方的行值(我的猜测)。这将导致每次对表进行排序时,G 都会重新计算。REF 错误意味着排序导致 G 引用不存在的单元格。VALUE 错误可能是由于错误引用导致的内部公式计算问题造成的。G 中的错误将级联到 N(看起来像 M 和 L)。

利用您的评论,这里是另一种方法:

方法1

不知道 G 中的公式,您能否创建一次,然后将其复制/粘贴为值?每次排序时都不会重新计算,但如果可以接受,则可以解决问题。

方法2

您可以为原始排序顺序创建一个 SortKey。这将允许您维护您开始时的引用。

需要注意的是:您将用 XLOOKUP 或 INDEX 替换直接单元格引用,因此这可能会降低包含 6000 行的工作簿的速度,但让我们看看......

以下是您的工作表的简化模拟:

在此处输入图片描述

公式G依赖于来自同一行的数据[其他的东西]列以及列的上方和下方行(其他内容)和(Act Height)。如果对此进行排序,则会产生垃圾。

现在我将在表中引入一个新列,称为排序键它只是按原始顺序保存了行的枚举。

现在表格如下所示:

在此处输入图片描述

G 中的新公式用 XLOOKUP 替换对 I 和 K 列的直接引用,XLOOKUP 搜索 SortKey +/- 1 并返回 I 和 K 中的相应值。因此我们在 G 中转换了此公式:

=( [@[Other Stuff]] - N( I5 ) )
  / ( N( I7 ) - [@[Other Stuff]] )
  / ( N( K5 ) + 1 )

到这个:

=( [@[Other Stuff]] - N( XLOOKUP( [@SortKey]-1, [SortKey], [Other Stuff] ) ) )
   / ( N( XLOOKUP( [@SortKey]+1, [SortKey], [Other Stuff] ) ) - [@[Other Stuff]] )
   / ( N( XLOOKUP( [@SortKey]-1, [SortKey], [Act Height] ) ) + 1 )

当你对其进行排序时,G 中的值会被保留:

在此处输入图片描述

但请注意 G 中的 N/A 错误 - 这是因为 XLOOKUP 尝试引用不存在的 SortKey 值。它要么引用最后一行下方的行,要么引用第一行上方的行。为了防止这种情况,您需要告诉 XLOOKUP 发生这种情况时要传递什么值。它是第 4 个参数 - 我在这里将其留空。

方法 3

您可以按原始顺序对列进行排序,然后选择 G 列并执行 REPLACE ALL 将 K 更改为 K$(假设公式中没有其他带有 K 的单词,例如 KOLOM 或 TEKEN)。这是一种进行批量编辑的方法,可以创造出相当于 F4ing 的幸福之路。

我所能想到的就这些。

相关内容