☰

Excel 数据透视表将空单元格视为具有值

tag-icon • tag-icon microsoft-excel pivot-table
Excel 数据透视表将空单元格视为具有值

我对数据透视表的这种奇怪行为感到困惑。想象一下这样的例子：在我的工作中，我们有公司和服务器。我制作了包含各种信息的 Excel 电子表格，例如备份了什么以及监控了什么。在这个简单的例子中，我想要一个数据透视表，它可以告诉我每个公司备份和监控了多少台服务器。如果我手动输入表中的所有值，它就会起作用，但自然我会使用公式用“是”或空字符串“”填充字段。

由于某种原因，数据透视表的面积计数总是计算所有行，无论它们是否有值或空字符串。我不知道如何实现这一点。

电子表格

请注意，如果您删除公式并在数据透视表上单击鼠标右键/刷新，它将正确计算

这是文件，您可以查看公式并尝试修复它（更新于 15.1）。

https://drive.google.com/file/d/17QIGEkvXbMKRmCrS8AoHy7rti8k6X1xJ/view?usp=sharing

编辑：感谢 Ron，我找到了 D 和 E 列的解决方法（按照他的建议，将“yes”替换为 1）。不幸的是，此解决方案不适用于许多其他情况。我添加了 C 和 F 列作为示例，我也希望它们在数据透视表中显示出来，但我真的不知道如何让 excel 理解它 :-] 假设某些服务器是物理的，因此 C 行将为空，因为在 VMWare 摘录中找不到主机名。我想知道该公司的虚拟机数量。app1 的状态由一些 powershell 脚本生成，该脚本扫描 AD 中的计算机，ping 它们并在服务器响应时返回 TRUE 或 FALSE。0 表示服务器无法访问。我想计算 TRUE 的次数。

我正在考虑条件格式 - 可以编写公式来确定单元格格式。不幸的是，我在数据透视表中没有看到这种可能性...

答案1

有很多选择。

可能最简单的方法是将公式改为：

=IF(ISNA(MATCH(B2,monitoring!A:A,0)),"",1)

然后将列的值设置更改为Sum而不是Count。

如果你必须仍有领域展示 Yes，然后格式化该字段：

 Value Field Settings --> Number Format --> Custom:  "Yes";;

对于更通用的解决方案，您可以将所有null strings/False/0条目替换为 true空值，然后创建数据透视表。

原始数据

在此处输入图片描述

为了不破坏您的原始数据和公式，您可以在 中执行此操作Power Query。除“添加列”之外的所有步骤都可以从 UI 中完成。

脚步

  • 从表/范围获取数据
  • 选择所有列
    • 用（小写字母）替换（分步）""和false0null
  • 选定所有列后，Unpivot
    • 这将生成一个两列表，其列标签为“属性”和“值”
  • 添加自定义或条件列，如果属性列 = 公司，则显示值列，否则null

在此处输入图片描述

  • 选择新Company列并Fill Down
    • 这将填充已填充的单元格以替换 s null。
  • 旋转Attribute柱
  • 删除company仅包含公司数量的列。
  • 关闭并加载到任意位置。

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="autoServers"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"company", type text}, {"server", type text}, {"Vmname", type text}, {"monitored", type text}, {"backed up", type text}, {"app1 installed", type any}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"company", "server", "Vmname", "monitored", "backed up", "app1 installed"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",false,null,Replacer.ReplaceValue,{"company", "server", "Vmname", "monitored", "backed up", "app1 installed"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",0,null,Replacer.ReplaceValue,{"company", "server", "Vmname", "monitored", "backed up", "app1 installed"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value2", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Company", each if [Attribute] = "company" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Company"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Attribute]), "Attribute", "Value", List.Count),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"company"})
in
    #"Removed Columns"

原始数据透视表位于顶部。下面是通过 PQ 处理后生成的 PT

数据透视表

在此处输入图片描述

如果您对数据做了任何更改，Refresh All则应刷新所有内容。

答案2

我遇到了同样的问题。我的解决方案是在数据表中的列名上创建一个过滤器。选择逐一显示每列中的空白值。然后选择那些过滤掉的空白单元格并按下键盘上的 Delete 键。轰隆隆，数据透视表按预期计数。

相关内容