对于我的项目,我需要根据到不同地点的最短驾驶时间来安排地点。我模拟了驾驶距离(D、E 和 F 行),但高度取决于在不同级别输入的产品。公式采用驾驶距离(D、E 和 F)和高度(G 行)并计算驾驶时间(L、M 和 N 行)。
我现在想对行驶时间结果进行排序,以便输入产品,但这会破坏表格,因为公式会出现错误。我怎样才能让表格按时间排序而不破坏我的公式?
图一显示的表格有 6000 多个单元格长(因此无法手动输入)
图二显示了时间的公式,它基本上将结果四舍五入到小数点后三位(AFRONDEN = ROUNDING),并使用公式检查高度是否为零,使其成为地板水平,或更高,使其成为空中的位置。
图三显示了公式排序时发生的情况,它基本上给出了“值”和“参考”错误。
这些是 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 的幸福之路。
我所能想到的就这些。