隐藏Excel折线图中的数据点-HidingdatapointsinExcellinecharts

5  

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

4  

"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。

3  

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代码（至少在大多数时间都有效）

3  

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 IF function - that returns an #N/A as 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值时，这种方法很有效。

1  

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周围的引号

1  

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.

我不能绘制的唯一方法是将单元格留空。

0  

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”，结果数据点在图中显示为零。它没有引号。