如何阻止Excel改变细胞内的公式？
我有这个公式 =IFERROR(VLOOKUP("实际/估计船舶单位预测",'WD ALPS'!$A:$K,MATCH(Date1,'WD ALPS'!$A$651:$K$651,0),FALSE) ，“”） 每次我运行宏来重新提取此公式中引用的数据时，Excel 都会将范围更改为 1。 （这打乱了我所有的计算。） 所以我从 MATCH(Date1,'WD ALPS'!$A$651:$K$651,0) 到 MATCH(Date1,'WD ALPS'!$A$650:$K$650 ,0) 无缘无故。我希望无论数据表发生什么情况它都保持不变。
I have this formula=IFERROR(VLOOKUP("Actual/Estimated Ship Units Forecast",'WD ALPS'!$A:$K,MATCH(Date1,'WD ALPS'!$A$651:$K$651,0),FALSE),"")
Each time I run a macro to re-pull the data referenced in this formula, excel changes the range by one. (which messes up all of my calculations.)
So I go from MATCH(Date1,'WD ALPS'!$A$651:$K$651,0) to MATCH(Date1,'WD ALPS'!$A$650:$K$650,0)
without reason. I want it to stay constant no matter what happens to the data sheet.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一种可能性是使用偏移。这是一个挥发性的功能，可能导致响应时间问题，但在小规模上，通常不是问题。
您将偏移量锁定到一个不会因运行的宏而不会更改的锚单元。在这种情况下，我将假设A1受到影响。您可以将您的公式更改为：
另一种不会挥发的方式，并且对锚的依赖性不依赖于以相同的方式使用索引：
这选择了整个纸，然后用绝对的方法切出您想要的零件，固定索引。不足的一面是它评估了整个表格 - 我不确定哪个会更快。
One possibility is to use OFFSET. This is a volatile function, which may result in response time issues, but at a small scale, it is usually not an issue.
You would lock the OFFSET to an anchor cell that is not changed by the macro that you run. In this case, I will assume A1 is not impacted. You could change your formula to:
Another way that would not be volatile and would not have a dependency on an anchor would be to use INDEX in the same way:
This selects the whole sheet and then cuts out the piece that you want with absolute, fixed indices. The down side is that it evaluates the whole sheet - I am not sure which would run faster.