☰

创建“常规”图表和包含间隙的数据透视表

tag-icon • tag-icon microsoft-excel charts pivot-table
创建“常规”图表和包含间隙的数据透视表

我的 excel 文件中有很多数据，我想将它们显示在 2 个图表中：常规图表（所有数据）数据透视表（平均值）问题是我的数据中有很多空白，我不知道如何显示它们以便同时使用这两个图表。如果我将空白显示为 NA()，则数据透视表平均值返回 NA()。如果我将空白显示为空单元格（“”），则图表会将这些值显示为 0。

简化示例：

NA()的问题： NA() 的问题

空白（“”）的问题： 空白（“”）问题 *选择数据中的“隐藏和空单元格”选项没有影响（我尝试了所有 3 个选项）。

有什么推荐的解决方案吗？（除了复制所有数据之外，一个带有空白（“”），一个带有NA（））。

谢谢。

答案1

正如您所发现的，“”不是一个空单元格，而是一小段文本，绘制为零。因此，它可能适用于您的数据透视表源，但不适合您的图表源。

制作两个数据范围，一个用于使用“”的数据透视表，另一个用于使用#N/A 的图表。

答案2

使用空单元格，但对于显示所有数据的图表 - 创建散点图，然后添加实线。

选择数据。

使用插入>图表>散点图：

在此处输入图片描述

通过单击其中一个点来选择系列。右键单击选定的点，然后选择“格式化数据系列”。

在出现的选项窗格中，单击倾斜的油漆桶，然后在“线”下选择“实线”：

在此处输入图片描述

这将确保图表中存在数据缺口的位置。

答案3

  1. 真正的空单元格选项

处理此问题的最佳方法是使用真实且适当的空单元格（无值，无公式），但可以理解的是，对于每列连续运行公式的情况，这并不总是可行的。

这种方法具有额外的优点（对某些人来说是缺点？），即在正常散点图中出现不连续的线。

  1. 筛选“”空白选项

在公式中使用 ="" 将使数据透视表按预期工作。然后，您可以向数据添加常规过滤器并过滤掉空白。常规图表将忽略隐藏的行并绘制一条连续的线。

遗憾的是，如果您的多个系列中的“空白”不均匀地分布在各行之间，则此选项不起作用。

  1. 枢轴刷新选项

您还可以利用数据透视表不会自动刷新这一事实。将单元格命名为“plotforpivot”，并赋予其值 TRUE。然后对您的“空白”单元格使用此公式：

=IF(plotforpivot,"",NA())

现在刷新数据透视表。接下来将“plotforpivot”单元格更改为 FALSE。您的数据透视表和正常图现在将正确反映，直到下一次数据透视表刷新。

  1. AGGREGATE 函数选项

您可以选择自己计算平均值（即不使用数据透视表）。如果您使用 NA()，则普通的 AVERAGE 函数（也由 Pivot 使用）也将返回 #N/A。但是，可以使用 AGGREGATE 函数计算平均值，同时忽略错误，例如=AGGREGATE(1,7,B2:B7)。遗憾的是，没有使用 AGGREGATE 函数来汇总数据透视值的选项。

  1. 过滤枢轴

最后但并非最不重要的一点是，您可以过滤数据透视表中的 #N/A 值。首先将=NA()其用于空白，以便正常图表绘制得很漂亮。接下来，根据需要设置数据透视表，同时将系列数据拖到数据透视表“过滤器”部分下。现在，单击数据透视表顶部系列旁边的向下过滤箭头，单击“选择多个项目”并取消选中“#N/A”。不幸的是，这与使用选项 #2 进行过滤存在同样的问题，因为多个系列也会一次过滤整行。我猜从根本上说这是一个问题，因为 Excel 无法平均一个系列的 10 个项目，然后只平均另一个系列的 9 个项目。它不能只隐藏一个单元格，只能隐藏整行或整列。也许您可以改为创建多个数据透视表？每个系列一个？

综上所述

最后，如果您有多个系列，您可能别无选择，只能复制每个系列列，一个带有 #N/A，另一个带有 ""。您至少可以轻松地从原始公式（B 列）中的 NA() 开始，然后在副本中使用 =IFERROR(B2,"")。

理想情况下，应该能够在数据透视中使用 AGGREGATE(1,7...)。在 Google 上搜索是否可行，我确实使用 PowerPivot / PowerQuery 找到了一些结果。如果你愿意，你可以尝试一下，但这不是我的强项。

我认为我个人更喜欢数据透视表刷新选项（#3）。如果客户足够聪明，能够刷新数据透视表，他们肯定可以切换单元格值。您甚至可以将“plotforpivot”单元格设为下拉列表，仅允许 TRUE 或 FALSE，并且您可以在 A1 中包含一个警告公式，用于检测数据透视表中是否存在 #N/A 错误以及数据中是否存在“”空白，例如=HYPERLINK("#plotforpivot",IF(ISNA(GETPIVOTDATA("Data",$D$1)), "WARNING #N/A ERROR: PLEASE TOGGLE PLOTFORPIVOT AND REFRESH THE PIVOT TABLE", IF(COUNTIF(B2:B7,"")>0,"WARNING PLOTTING ERROR: PLEASE TOGGLE PLOTFORPIVOT","ALL GOOD!")))

相关内容