图表不会在Excel中更新(2007) [英] Chart won't update in Excel (2007)

问题描述

我有一个Excel文档(2007)，其中带有一个图表(集群列)，该图表从包含计算值的单元格获取其数据系列

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

我可以通过保存/关闭或切换其中一项设置(例如反转x/y轴然后放回原位)或重新选择数据系列来更新图表.

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

• 是的，我将计算"设置为 自动
• Ctrl + Alt + F9可以更新所有内容，除了图表
• 我已经在不同的计算机上多次创建了图表

• 我尝试过VBA脚本，例如:

• 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

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

这些都不更新或刷新图表

None of these update or refresh the chart

我确实注意到，如果我在数据系列上键入实际数字而不是计算，它将更新图表-就像Excel不想识别计算中的变化一样

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中更新(2007)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！