忽略＃'¡DIV / 0！条形图的两个轴上的值-Excel [英] Ignore #'¡DIV/0! Values in both axes of a bar chart - Excel

问题描述

我在excel中有此条形图：

如您所见，如果存在＃'¡DIV / 0！，值是 0 。我真正想要的是条形图仅显示数字值。在这种情况下。仅前三个值必须可视化。我尝试了公式 IF.ERROR（FORMULA，），但结果是相同的，

所以，我该怎么做？

解决方案

您需要图表的动态范围，或者过滤数据源以隐藏带有错误。 Excel图表将不包含被过滤器隐藏的数据。

要应用动态范围，请使用以下公式创建两个范围名称：

  chtLabels = OFFSET（chtValues，0，-1）
 chtValues = Sheet1！$ B $ 12：INDEX（Sheet1！$ B：$ B，MATCH （99 ^ 99，Sheet1！$ B：$ B，1））
  

调整chtValues范围名称

编辑柱形图系列值并输入范围名称，然后在工作表之前名称，例如

  = Sheet1！chtValues 
  

编辑水平轴标签，并用动态命名范围替换范围

  = Sheet1 ！chtLabels 
  

退出对话框时，Excel会将工作表名称替换为文件名。结果将类似于以下内容：

请注意系列公式如何显示范围名称而不是单元格范围。

I have this bar chart in excel :

As you see, if there is #'¡DIV/0!, the values are 0. What I really want is that the bar chart just shows the numeric values. In this case. just the first three values would have to be visualized. I tried the formula IF.ERROR(FORMULA,"") but the result was the same,

So, How Can I do that?

解决方案

You need dynamic ranges for the chart, or filter the data source to hide the rows with the error. Excel charts will not include data that is hidden with a filter.

To apply dynamic ranges, create two range names with the following formulas:

chtLabels   =OFFSET(chtValues,0,-1)
chtValues   =Sheet1!$B$12:INDEX(Sheet1!$B:$B,MATCH(99^99,Sheet1!$B:$B,1))

Adjust the chtValues range name to start in the first cell with a number in column B instead of B12 of the example.

Edit the column chart series values and enter the range name, preceded by the sheet name, like

=Sheet1!chtValues

Edit the Horizontal axis labels and replace the range with the dynamic named range

=Sheet1!chtLabels

When you exit the dialog, Excel will replace the sheet name with the file name. The result will be similar to this:

Note how the series formula shows the range names instead of the cell ranges.

这篇关于忽略＃'¡DIV / 0！条形图的两个轴上的值-Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！