![Excel 数据透视表将空单元格视为具有值](https://linux22.com/image/1613790/Excel%20%E6%95%B0%E6%8D%AE%E9%80%8F%E8%A7%86%E8%A1%A8%E5%B0%86%E7%A9%BA%E5%8D%95%E5%85%83%E6%A0%BC%E8%A7%86%E4%B8%BA%E5%85%B7%E6%9C%89%E5%80%BC.png)
我对数据透视表的这种奇怪行为感到困惑。想象一下这样的例子:在我的工作中,我们有公司和服务器。我制作了包含各种信息的 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 中完成。
脚步
- 从表/范围获取数据
- 选择所有列
- 用(小写字母)替换(分步)
""
和false
0
null
- 用(小写字母)替换(分步)
- 选定所有列后,
Unpivot
- 这将生成一个两列表,其列标签为“属性”和“值”
- 添加自定义或条件列,如果属性列 = 公司,则显示值列,否则
null
- 选择新
Company
列并Fill Down
- 这将填充已填充的单元格以替换 s
null
。
- 这将填充已填充的单元格以替换 s
- 旋转
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 键。轰隆隆,数据透视表按预期计数。