当日期不为空时，如何删除Excel中的空值？ [英] How to remove empty values in excel chart when dates are not empty?

问题描述

我有一个有两种图表的图表。一个显示订购的商品数量，第二个 - 每个订单损坏的商品率。对于第一个图我使用条形图，第二个点图。问题是如何删除点号图表中的空/零值？

I have a chart with two types of graphs. One shows number of items ordered and the second one - damaged items rate for each order. For the first graph I use bar chart, for the second dot chart. The question is how to remove empty/zero values in dot chart? "Hide empty values" doesn't work because date column is filled and used for bar chart.

以下是数据：

>

以下是图表：

推荐答案

TL; DR （很可能） c> =对于空白单元格，您需要返回＃N / A 。

TL;DR (most likely) you are returning a ="" for a blank cell, you need to return #N/A.

根据图表设置方式，有几种方法可以解决此问题。

There are a couple of ways to resolve this issue depending on how you chart is setup.

默认

首先，默认情况下，Excel不会绘制真正为空的单元格。如果我只是创建一个图表（条形图，散点图，线等）并删除一个数据点，它将被从图表中删除，而不是设置为0.这取决于 Hidden and Empty 设置默认为 Gaps 。

First, by default, Excel will not plot a cell that is truly empty. If I just create a chart (bar, scatter, line, etc.) and delete a data point, it will be removed from the chart, not set to 0. This depends on the Hidden and Empty setting which defaults to Gaps.

出错了

因此，如果您看到一个空白单元格的数据点，则表示以下几种情况之一：

So if you are seeing a data point for a blank cell, it means one of a couple things:

• 更改了图表如何处理隐藏和空单元格


• 单元格不是真空。如果你有一个返回的公式，这是可能的。这将显示为0，而不管隐藏和空设置，因为它不是空单元格。

• The setting was changed for the chart on how to handle Hidden and empty cells
• The cell is not truly empty. This is possible if you have a formula that returns "". This will show as a 0 regardless of the Hidden and empty setting because it is not an empty cell.

如何修复

要解决此问题，请执行以下更改之一：

To resolve this issue then requires one of the following changes:

• 完全删除单元格内容，使其真正为空单元格。这假设隐藏和空单元格选项默认为 Gaps 。


• 您的公式返回＃N / A ，图表将会隐藏数据点，而不管其他任何设置。

• Completely delete the cell contents so that it is truly an empty cell. This assumes that the Hidden and empty cells option is at the default of Gaps.
• Have your formula return an #N/A and the chart will hide the data point regardless of any other settings.

>

• 将隐藏和空单元格设置为 Gaps 而不是 Zero ）。这不会影响条形图。它将只用于隐藏点图上缺少的条目。这只有在单元格真的为空时才有效。

• Change the Hidden and empty cells setting to Gaps (instead of Zero). This will not affect the bar chart. It will only serve to hide the missing entries on the dot plot. This only works if the cell is truly empty.

这篇关于当日期不为空时，如何删除Excel中的空值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！