vba 在 Excel 折线图中隐藏数据点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/53719/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hiding data points in Excel line charts
提问by d91-jal
It is obviously possible to hide individual data points in an Excel line chart.
显然可以在 Excel 折线图中隐藏单个数据点。
- Select a data point.
- Right click -> Format Data Point...
- Select Patterns
- Tab Set Line to None
- 选择一个数据点。
- 右键单击 -> 格式化数据点...
- 选择图案
- 选项卡将行设置为无
How do you accomplish the same thing in VBA? Intuition tells me there should be a property on the Point objectChart.SeriesCollection(<index>).Points(<index>which deals with this...
你如何在 VBA 中完成同样的事情？直觉告诉我Point 对象上应该有一个属性Chart.SeriesCollection(<index>).Points(<index>来处理这个......
采纳答案by d91-jal
"Describe it to the teddy bear" works almost every time...
“把它描述给泰迪熊”几乎每次都有效......
You have to go to the Border child object of the Point object and set its LineStyle to xlNone.
您必须转到 Point 对象的 Border 子对象并将其 LineStyle 设置为 xlNone。
回答by
Actually if you are going to use SpyJournal's answer it has to be =IF(b2=0,NA(),b2), otherwise Excel just recognizes it as text, not as an 'official' #N/A
实际上，如果您要使用 SpyJournal 的答案，它必须是=IF(b2=0,NA(),b2)，否则 Excel 只会将其识别为文本，而不是“官方”#N/A
回答by sdfx
As a general tip: If you know how to do something in excel, but don't know how to do it in VBA you could just record a macro and look at the recorded VBA-code (works at least most of the time)
作为一般提示：如果您知道如何在 excel 中执行某些操作，但不知道如何在 VBA 中执行此操作，则可以只录制一个宏并查看录制的 VBA 代码（至少大部分时间都有效）
回答by SpyJournal
There is a Non VBA solution as well that can also be controlled from the VBA code as well.
In excel a data point represented by a #N/A will not display. Thus you can use a formula - the easiest is an IFfunction - that returns an #N/Aas text in the graph data. This data point will then not display which means you don't need to try and manipulate the format for it.
还有一个非 VBA 解决方案，也可以通过 VBA 代码进行控制。在 excel 中，由 #N/A 表示的数据点将不会显示。因此，您可以使用公式 - 最简单的是IF函数 - 返回#N/A图形数据中的文本。该数据点将不会显示，这意味着您无需尝试操作它的格式。
An example is simply to generate your graph data in a table, and then replicate it below with a formula that simply does this
一个例子是简单地在表格中生成你的图形数据，然后用一个简单的公式在下面复制它
=If(B2=0,"#N/A",B2)
=If(B2=0,"#N/A",B2)
This works when you want to stop line charts from displaying 0 values for example.
例如，当您想要阻止折线图显示 0 值时，这会起作用。
回答by SpyJournal
This is probably too late to be of assistance but the answer by SpyJournal, whilst easy and elegant,is slightly incorrect as it is necessary to omit the quotes around #N/A
这可能为时已晚，无法提供帮助，但 SpyJournal 的答案虽然简单而优雅，但有点不正确，因为有必要省略 #N/A 周围的引号
回答by Garrydene
Yes. It doesn't have to have the quotes to be a true not available cell content but for me N/A still plot as 0 in my charts.
是的。它不必让引号成为真正不可用的单元格内容，但对我来说 N/A 在我的图表中仍然绘制为 0。
The only way I can get it not to plot is to have the cell blank.
我可以让它不绘图的唯一方法是将单元格留空。
回答by Volker Piasta
I tried "#N/A" with quotes in Excel 207 and as a result the data point is shown like a zero in the graph. It Works without the quotes.
我在 Excel 207 中尝试了带引号的“#N/A”，结果数据点在图表中显示为零。它无需引号即可工作。
