Excel - 从单元格范围创建图表，但不包括空值？ [英] Excel - Create chart from range of cells while excluding null values?

问题描述

我有这个Excel表，基本上包含大量的数据。
现在，此Excel表通过导入数据的宏动态更新。因此，数据可能会改变，也就是说，某些单元格可能会填充，而其他单元格则不会填充。

：A60到M2：M60，其基本上看起来像这样：

  = IF（Sheet1！E2<> 0; Sheet1！A2 ;）
  

含义是，如果我所在行上的单元格E2为0，复制的值在新的电子表格是什么。下一行也一样：

  = IF（Sheet1！E3<> 0; Sheet1！A3;）
  

重复此操作直到第60行。

现在，我想做的是选择范围A2：A60并将该数据插入图表。但事情是，图表添加没有值的单元格。我想从图表中排除它，而不必更改图表的范围。是否可以在图表选择中使用公式？或者我必须使用宏吗？

编辑：现在，当我创建它基于A2：A60时，图表看起来像这样。注意，只有A4：A17实际上有任何值，其他没有什么，因为上述公式。

解决方案

您可以使用命名范围。

例如：

 名称定义
 Date = OFFSET（Sheet1！$ A $ 1,1,0，COUNTA ：$ A）-1）
 Value = OFFSET（Sheet1！$ B $ 1,1,0，COUNTA（$ B：$ B）-1）
  

然后在图表中使用它们作为源。

您可以找到更多信息 。

以下是其他两个示例：

• 精彩教程： http://chandoo.org/wp / 2009/10/15 / dynamic-chart-data-series /


• 较短的一个，但是有一个示例（如果您无法阅读 ve发表在我的评论）： http：/ /www.databison.com/index.php/create-chart-using-named-range-in-excel/

聊天内的工作结果：

问题： COUNTA 公式在工作表上无效，因为单元格包含公式，所以 COUNTA 仍会计算这些单元格，即使值为空。 / p>

解决方案：我们使用 SUMPRODUCT 公式计算空值

 名称定义
 Date = OFFSET（Sheet1！$ A $ 2,1,0，SUMPRODUCT（IF（Sheet1！A2：A60< >，1,0）），1））
值= OFFSET（Sheet1！$ B $ 2,1,0，SUMPRODUCT（IF（Sheet1！B2：B60& ）），1））
  

注意，

Max

I've got this Excel sheet which basically contains a lot of data. Now, this Excel sheet is updated dynamically via a macro that imports the data. So the data might change, meaning, some cells might be populated, while others won't.

So I've got this formula in Sheet 2 in each cell from A2:A60 to M2:M60 which basically looks like this:

=IF(Sheet1!E2<>0;Sheet1!A2;"")

Meaning, if cell E2 on the row I'm in is 0, then the copied value in the new spreadsheet is nothing. Same goes for the next row:

=IF(Sheet1!E3<>0;Sheet1!A3;"")

This is repeated until row 60.

Now, what I want to do is to select the range A2:A60 and insert that data to a chart. But the thing is, the chart adds the cells that doesn't have a value. I want to exclude it from the chart without having to change the range of the chart. Is this possible using a formula in the Chart selection? Or would I have to use a macro?

Edit: Right now, the chart looks like this when I create it based on A2:A60. Notice that only A4:A17 actually have any value, the other ones have nothing, because of the formula described above.

解决方案

You can probably use named range.

You can define names like :

Name    Definition
Date    =OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)
Value   =OFFSET(Sheet1!$B$1,1,0,COUNTA($B:$B)-1)

And then use them as a source in your chart.

You can find more information on MS Website.

[EDIT] Here are two other examples:

• a great tutorial: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
• a shorter one but with a sample example (in case you couldn't read the one i've posted in my comment): http://www.databison.com/index.php/create-chart-using-named-range-in-excel/

[EDIT 2] Results of the work within the chat:

Problem: the COUNTA formula did not work on the worksheet because the cells contained formulas so the COUNTA still counted these cells even if the value was empty.

Solution: we used the SUMPRODUCT formula to count the empty values

Name   Definition
Date   =OFFSET(Sheet1!$A$2,1,0,SUMPRODUCT(IF(Sheet1!A2:A60<>"",1,0)),1))
Value  =OFFSET(Sheet1!$B$2,1,0,SUMPRODUCT(IF(Sheet1!B2:B60<>"",1,0)),1))

Regards,

Max

这篇关于Excel - 从单元格范围创建图表，但不包括空值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！