我有一些 VBA 代码,可以运行 SQL 查询,将其转储到 Excel 2010 电子表格中,并根据数据刷新 2 个数据透视表。我还有 2 个基于数据透视表的数据透视图,每次更新表格时,图表上的格式都会丢失。
我按照本书第 12.3 节建议根据我的格式偏好创建自定义图表类型,并将其应用于我希望保持该格式的图表。这些说明非常有用如果我手动修改数据,然后手动刷新数据透视表 - 图表保持正确格式。但是,当我执行此代码时:
For Each pt In NewSheet.PivotTables
pt.RefreshTable
Next
格式立即丢失。
之前的数据如下:
以及我精心制作的饼图:
一旦我更新数据并刷新表格:
这是我得到的:
同样,如果手动操作,应用自定义图表样式将保留格式,但执行 VBA 代码会以某种方式重置自定义图表类型。
答案1
要在不使用数据透视图的情况下实现此目的,您只需要一个动态命名范围和一个简单的(如果格式混乱)图表。
1) 使用以下公式创建定义的名称:Series_A=OFFSET(Sheet!B1,0,0,COUNT(A:A),1)
,其中 - Sheet1!B1 指的是数据透视表中的第一个计数单元格(或任何其他引用,如果您对 OFFSET 感到满意) - Count(A:A) 是带有行标签的列
2)使用系列=Series_A(以及您需要的任何其他图表)创建图表。
当您更新数据透视表时,定义的名称将引用行标签列中的扩展/收缩值,并且仅返回计数列中的值。无需 0、#N/A 或辅助列。