Excel图表不会更新 [英] Excel chart won't update

问题描述

我有一个带有图表（聚类列）的Excel文档（集群列），它从包含计算值的单元格获取其数据系列

计算值不会直接更改，但是由于其他单元格的更改，只能在表单中更改。

当我更改工作表中的其他单元格时，数据系列单元格将重新计算并显示新值 - 但是基于此数据系列的图表拒绝自动更新

我可以通过保存/关闭来获取图表更新，或切换其中一个设置（如反转x / y轴，然后将其放回），或通过重新选择数据系列

我在网上找到的每个解决方案都不起作用

• 是我的计算设置为
  自动


• Ctrl + Alt + F9更新一切，除了图表


• 我已经重新创建了图表，并在不同的计算机上



• 我已经尝试过VBA脚本，如：

  
  
  

  Application.Calculate

Application.CalculateFull

Application.CalculateFullRebuild

ActiveWorkbook.RefreshAll

DoEvents

这些更新或刷新图表

我注意到，如果我输入数据系列，实际数字而不是计算，它将更新图表 - 就好像Excel不想识别计算中的更改

有没有人经历过这件事，或者知道我可以做些什么来解决这个问题？
谢谢

解决方案

这是我发现一直在更新图表的唯一的事情。它削减了问题的根本原因（我假设）：系列数据正在图表缓存。通过强制图表重新评估系列，我们正在清除缓存。

 '强制图表更新
设置sht = ActiveSheet 
对于每个co在sht.ChartObjects 
 co.Activate 
对于每个sc在ActiveChart.SeriesCollection 
 sc.Select 
 temp = sc.Formula 
 sc.Formula == SERIES（,, 1,1）
 sc.Formula = temp 
下一个sc 
下一个co 
  

I have an Excel document (2007) with a chart (Clustered Column) that gets its Data Series from cells containing calculated values

The calculated values never change directly, but only as a result of other cells in the sheet changing

When I change other cells in the sheet, the Data Series cells are recalculated, and show new values - but the Chart based on this Data Series refuses to update automatically

I can get the Chart to update by saving/closing, or toggling one of the settings (such as reversing x/y axis and then putting it back), or by re-selecting the Data Series

Every solution I have found online doesn't work

• Yes I have Calculation set to automatic
• Ctrl+Alt+F9 updates everything fine, EXCEPT the chart
• I have recreated the chart several times, and on different computers

• I have tried VBA scripts like:

  Application.Calculate
Application.CalculateFull
Application.CalculateFullRebuild
ActiveWorkbook.RefreshAll
DoEvents

None of these update or refresh the chart

I do notice that if I type over my Data Series, actual numbers instead of calculations, it will update the chart - it's as if Excel doesn't want to recognize changes in the calculations

Has anyone experienced this before or know what I might do to fix the problem? Thank you

解决方案

This is the only thing I've found to consistently update a chart. It cuts the root cause of the problem (I assume): the series data is getting cached in the chart. By forcing the chart to re-evaluate the series, we are clearing the cache.

' Force the charts to update
Set sht = ActiveSheet
For Each co In sht.ChartObjects
    co.Activate
    For Each sc In ActiveChart.SeriesCollection
        sc.Select
        temp = sc.Formula
        sc.Formula = "=SERIES(,,1,1)"
        sc.Formula = temp
    Next sc
Next co

这篇关于Excel图表不会更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！