在Excel中创建忽略＃N / A或空白单元格的图表 [英] Creating a chart in Excel that ignores #N/A or blank cells

问题描述

我正在尝试使用动态数据系列创建图表。图表中的每个系列来自绝对范围，但只有一定数量的该范围可能有数据，其余的将为＃N / A 。

I am attempting to create a chart with a dynamic data series. Each series in the chart comes from an absolute range, but only a certain amount of that range may have data, and the rest will be #N/A.

问题是图表将所有的＃N / A 单元格作为值而不是忽略它们。我使用命名的动态范围（即插入>名称>定义）来处理它，但是这是非常低效的，因为每个图表都有4个动态系列，我必须使这些图表中的25个。

The problem is that the chart sticks all of the #N/A cells in as values instead of ignoring them. I have worked around it by using named dynamic ranges (i.e. Insert > Name > Define), but that is extremely inefficient, as each chart has 4 dynamic series, and I must make 25 of these charts.

是否有任何其他解决方案允许我像​​数据系列一样指定一个范围，但是告诉图表忽略所有的＃N / A或空白单元格？ strong>

Are there any other solutions that allow me to specify a range, as normal, for a data series, but tell the chart to ignore all "#N/A" or blank cells?

推荐答案

通过使用IF语句将不需要的值返回到，我有同样的问题，而图表会像你所说的那样做。

I was having the same issue by using an IF statement to return an unwanted value to "", and the chart would do as you described.

但是，当我使用＃N / A 而不是（重要的是注意，它没有引号，如＃N / A 而不是＃N / A），图表忽略无效数据。我甚至尝试放入一个无效的 FALSE 语句，它工作相同，唯一的区别是 #NAME？返回为单元格中的错误而不是＃N / A 。我将使用一个组成的IF语句来告诉我我的意思：

However, when I used #N/A instead of "" (important, note that it's without the quotation marks as in #N/A and not "#N/A"), the chart ignored the invalid data. I even tried putting in an invalid FALSE statement and it worked the same, the only difference was #NAME? returned as the error in the cell instead of #N/A. I will use a made up IF statement to show you what I mean:

=IF(A1>A2,A3,"")  
---> Returned "" into cell when statement is FALSE and plotted on chart 
     (this is unwanted as you described)

=IF(A1>A2,A3,"#N/A")  
---> Returned #N/A as text when statement is FALSE and plotted on chart 
     (this is also unwanted as you described)

=IF(A1>A2,A3,#N/A)  
---> Returned #N/A as Error when statement is FALSE and does not plot on chart (Ideal)

=IF(A1>A2,A3,a)  
---> Returned #NAME? as Error when statement is FALSE and does not plot on chart 
    (Ideal, and this is because any letter without quotations is not a valid statement)

这篇关于在Excel中创建忽略＃N / A或空白单元格的图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！