☰

是否可以自动将脏数据过滤掉，以免它们进入 Excel 中的数据透视表？

tag-icon • tag-icon microsoft-excel microsoft-office pivot-table
是否可以自动将脏数据过滤掉，以免它们进入 Excel 中的数据透视表？

我有一个数据库，里面有脏数据。
例如，天数应该是整数，但人们填写了双精度数，如 2.3 或 5.2...
我的透视表示例数据来自此数据库。

工作流程（不可改变）是：

  1. 以 .csv 格式导出不同部门的数据，其中包含脏数据。
  2. 将所有 .csv 上传到服务器
  3. 使用查询将所有.csv 读入一个大的 excel，因此脏数据进入大 excel 文件。
  4. 使用另一个 Excel 中的数据透视表进行分析。

我需要的是让我的数据透视表自动忽略数据源中的这些脏数据。
基本上就是忽略这 2.3 天或 5.2 天。
因此，当我在数据透视表中计算总天数时，我得到的只是整数。

我无法清理数据库，因为

  • 这是从在线数据收集器导出的
  • 数据太多，类型混乱。Int -> double、空字段、拼写错误等。
  • 幸运的是，这些类型的脏东西都有规律，因此也许我可以在它们进入数据透视表之前将它们过滤掉

4 月 21 日编辑 Power Query 看起来很有前途。我会看看的。非常感谢 SuperUser 社区和你们的反馈！

对于那些可能会问的人来说，在句子末尾输入 2 个空格，然后按 Enter 键，您将得到一个新行，而不是一个新段落。
就像这样。

答案1

您可以像这样过滤整数：

=FILTER(A1:B5, IF( ISNUMBER(B1:B5), MOD(B1:B5,1), 1) = 0)

它检查条目的类型是否为有效数字，如果是，则使用MOD()1，因此任何整数都将返回 0。这将在测试范围内的每个单元格时返回 TRUE。如果测试失败ISNUMBER()，则返回“1”。它是否是整数并不重要，因为它没有被测试是否为 1，只是它等于零或不等于零（当然它不等于零），因此在测试中返回 FALSEFILTER()是在范围内执行的。

然后，您可以用它作为进一步包装它的函数的输入FILTER()，但您必须记住，您的测试必须始终覆盖整个原始行集，尽管列可以随着每个包装函数而改变：永远不要尝试仅在当前结果的数据集上运行测试......始终覆盖首先使用的整个行集。

然后，您可能能够逐渐找出最糟糕的数据，然后随着时间的推移，逐步深入清理，并且您有时间去做这件事，或者有关于如何表征事物以进行更深入清理的想法。

答案2

我找到了解决方案！Power Query 是正确的选择！如果从数据库中提取数据时数据透视表中出现脏数据，就不要考虑如何处理数据透视表中的脏数据。直接进入数据库，在数据库中清理或排除脏数据。正确的方法始终是确保脏数据不会进入数据库，而不是在处理时找到处理脏数据的方法！

相关内容