Excel 单元格仅在第二个单元格更改后更新
更新：文件
根据要求，我提供了文件的链接：此处。 文件不再可用
我已将原始数据转换为类似游戏的上下文，其中工作表跟踪点对于已完成的活动并达到不同的积分金额后，用户的排名就会上升。查看问题的一个简单方法是在包含 500 的单元格旁边的 F15 中输入 1（然后是 2 和 3）。执行此操作时，请注意 Q5:Q6 中的值如何从 0 变为 1，但排名在C2 是计算此范围的 SUM 函数，但它不会像应有的那样增加到 3。因此，基于它的 L12 和 L14 中的值也不会更新。现在，如果您重新计算，排名会上升并且 L 单元格会更新。现在，基于 L 单元格的单元格 E3 无法反映正确的值，您必须再次重新计算工作表才能更新。
希望这一切都是有道理的 - 如果您需要任何说明，请告诉我！
原始问题
我的一些公式在输入数据后没有立即更新。只是为了在有人建议之前解决这个问题，我确实将工作簿的计算设置为自动，并且我不打开任何其他工作簿并将其计算方法设置为手动。所以这不是问题。
我会给出公式的细节，但我认为这与此无关。该公式是一个简单的 SUM 函数，它将一个范围加在一起。范围正确更新 - 一旦我输入数据，范围中的数字就会相应变化。 SUM 函数同样应该自动更新，但我必须在另一个单元格中输入数据或在空单元格中按删除键（基本上，让工作表再次重新计算）才能让 SUM 函数反映范围内的变化。
我唯一能想到的是，这是因为我启用了迭代计算（在“文件”>“选项”>“公式”选项卡中），以便允许单元格在特定条件为真时保留其先前的值，否则更新其值。我的最大迭代次数设置为 1，最大变化默认设置为 0.001。但是，我以前使用过它，但简单的 SUM 函数没有出现问题。我的工作表中还有其他同样简单的功能，可以正确更新，所以我有点困惑......
让我知道更多信息是否有帮助。感谢您的任何建议！
Update: File
Upon request, I am including the link to the file: here. File no longer available
I have transformed my original data into a game-like context, in which the sheet keeps track of points for completed activities and upon reaching various point amounts, the user goes up in rank. An easy way to see the issue is to enter a 1 (and then 2 and 3) into F15 next to the cell with 500. When you do so, notice how the values in Q5:Q6 change from 0 to 1, but Ranking in C2 which is the SUM function which counts this range does not increase to 3 like it should. And consequently the values in L12 and L14 which are based on it also don't update. Now, if you recalculate, the rank goes up and the L cells update. NOW, cell E3 which is based on the L cells doesn't reflect the correct value, and you have to once again recalculate the sheet for it to update.
Hope all that makes sense - let me know if you need any clarification!
Original Question
Some of my formulas are not updating right away after I have entered in data. Just to get this out of the way before someone suggests it, I DO have the calculation of the workbook set to automatic, and I DON'T open any other workbooks with their calculation method set to manual. So that's not the issue.
I would give specifics on formulas, but I don't think it has anything to do with that. The formula is a simple SUM function which adds together a range. The range updates properly - as soon as I've entered data, the numbers in the range change accordingly. The SUM function should likewise automatically update, but I have to enter data in another cell or press delete in an empty cell (basically, get the sheet to recalculate once again) to get the SUM function to reflect the changes in the range.
The only thing that I can think is that it's because I've enabled iterative calculation (in File > Options > Formulas tab) in order to allow cells to retain their previous value if a certain condition is true, else update their value. My settings are 1 for Maximum Iterations and the default of 0.001 for Maximum Change. However, I've used that before without having issues with a simple SUM function. And I have other just as simple functions in my sheet that DO update properly, so I'm a little confused...
Let me know if any more info would be helpful. Thanks for any suggestions!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
更新：
抱歉，我没有意识到这是一个公式，而不是函数。我的不好。
我会留下答案，以防有人遇到同样的问题，但有一个功能。对不起！
尝试在代码中添加此内容：
每次在出现此函数的工作表上更改单元格时，这将强制重新计算该函数。
参考： http://msdn.microsoft.com/ en-us/library/aa213653(v=office.11).aspx
UPDATE:
Sorry, I didn't catch that this is for a formula, not function. My bad.
I'll leave the answer up in case someone has the same issue, but with a function. Sorry!
Try adding this inside the code:
This will force recalculation for the function each time a cell is changed on the sheet in which this function appears.
Reference: http://msdn.microsoft.com/en-us/library/aa213653(v=office.11).aspx
您有循环引用：
S4:AL103范围内的单元格公式全部引用自身（在某些条件下）。这会导致excel停止计算，因为它无法解决冲突。您需要重新设计您的公式
You have circular references: cell formula in range
S4:AL103all refer to themselves (under cetain conditions). This will cause excel to stop calculating because it can't resolve the conflict.You wll need to redesign your formulas
最初添加到我的第一篇文章中；将其移至实际答案，以便可以接受
所以我找到了解决方案。通过对迭代进行更多阅读，我发现在迭代模式下，Excel 按特定顺序（我认为是按字母顺序？）一次处理一个单元格。因此，如果单元格 A 依赖于单元格 B，但单元格 B 稍后在此过程中更改值，则单元格 A 将保留基于单元格 B 的旧值而不是新值的值，直到提示另一次重新计算...或者，直到另一个迭代开始。因此，将最大迭代设置为大于 1 的数字可以解决该问题。由于我给你们的示例表中有两个单元格未更新，因此需要将其设置为 3 左右。在我的实际表中，我必须将其设置为 5。
感谢您的想法！希望这能帮助其他困惑的灵魂！
Originally added to my initial post; moved it to an actual answer so that it could be accepted
So I figured out the solution. From doing a little more reading up on iteration, I discovered that in iterative mode, Excel processes cells one at a time, in a certain order (alphabetical, I think?). So if cell A is dependent on cell B, but cell B changes value later in the process, cell A will retain a value based on cell B's old value, rather than the new one, until another recalculation is prompted... OR, until another iteration starts. So, setting maximum iteration to a higher number than 1 fixes the issue. Since I had two cells not updating in the example sheet I gave you guys, it would need to be set at about 3. In my actual sheet, I had to set it at 5.
Thanks for the thoughts! Hope this helps some other confused soul!