多年来，我一直依赖 Excel 进行简单的计算和创建基本表格。虽然我使用常用公式并进行简单的数据操作，但直到我的项目变得越来越复杂，我才感到有必要深入研究 Excel 更复杂的功能。
引发我探索的挑战
我所在地区的PC零部件价格不断上涨——由于市场波动和进口税的影响，价格通常高于美国——这促使我开始调查价格差异。我的目标是确定直接从亚马逊或新蛋网等平台购买是否比从当地零售商购买更经济。为此，我收集了几个月的PC关键零部件价格数据，包括CPU、GPU和RAM。我最初以为这只是一个简单的追踪项目，但很快却变成了一项艰巨的任务。
我收集的数据非常混乱。每个零售商的文件格式都不一样，这使得合并数据变得非常困难。例如，亚马逊记录日期的格式是 MM/DD/YYYY，而 Newegg 使用的是 YYYYMMDD，而我当地的 Shopee 商店则遵循 DD-MM-YYYY 的标准。
数据不一致的问题不仅仅局限于日期格式。列名也完全不同：Newegg 将价格标记为“零售价”，Amazon 选择了“单价美元”，而 Shopee 则简单地将其标记为“价格php”。此外，价格格式也各不相同，有些条目显示货币符号，例如“₱18, 600”，而另一些条目则以纯数字表示，例如“320”。甚至品牌名称也不一致，以各种格式出现，例如“gigabyte”、“GIGABYTE INC.”或“Gigabyte Tech”。
手动清理和整合这些数据变成了一项令人精疲力尽的工作。我发现自己花了无数的时间在文件之间复制、替换不一致的值，并一丝不苟地删除空行。此外，将价格从 PHP 转换为 USD 需要不断参考第二台显示器上的实时汇率。这个劳动密集型的过程既繁琐又容易出错，几乎让我放弃了这个项目。
就在那时，我决定探索一下 Excel 爱好者们经常赞不绝口的一个功能——Power Query。虽然 Excel 中有很多高级工具，但我发现 Power Query 特别解决了我的问题。在观看了 YouTube 上的教学视频后，我发现使用 Power Query 编辑器整理我收集的杂乱数据确实可以节省大量时间。
我使用 Power Query 的分步方法
我很快就在 Power Query 编辑器中开发了一个简单的程序来清理那些混乱的 CSV 文件并将它们转换为连贯、有条理的电子表格。
我首先将数据导入 Power Query 编辑器。我打开了一个新的 Excel 工作簿，从功能区中选择“数据” ，然后选择“从文本/CSV”导入。选择 CSV 文件后，我单击“转换数据”以进入 Power Query 编辑器。
第一步是标准化“日期”列。由于两个数据源的时间线相差 12 小时，因此确保日期格式统一至关重要。这个过程非常简单。我选中“日期”列，右键单击，然后选择“更改类型”>“使用区域设置”。在随后弹出的窗口中，我将类型设置为“日期”，并选择“英语（美国）”以确保格式一致。Power Query 能够熟练地识别各种格式——MM/DD/YYYY、YYYY/MM/DD、DD-MM-YYYY——并轻松地将它们标准化为单一格式。
日期格式修正后，我开始清理该列。由于大多数错误源于我的数据抓取工具，我使用了“删除错误”过滤器来消除错误条目。此步骤有效地删除了空值和任何剩余的问题数据，确保所有文件的日期表示一致。
接下来，我通过“替换值”功能解决了品牌名称混乱的问题。选中相关列后，我右键单击，打开“替换值”，输入并标准化所有品牌名称的变体，例如将“gigabyte”和“GIGABYTE INC.”转换为一个一致的条目：“GIGABYTE”。同样的方法也应用于 AMD，有效简化了所有数据源中的“品牌”列。
-
截图由 Jayric Maning 提供 – 无需署名 -
截图由 Jayric Maning 提供 – 无需署名
最后，我标准化了价格列。这带来了一个挑战，因为许多条目仅仅是数字，缺乏货币背景信息，使得转换工作变得复杂。幸运的是，“商店”列提供了即时背景信息，可以直接将Shopee PH价格转换为美元。
为了解决这个问题，我首先使用“替换值”功能从“价格”列中删除所有货币符号（例如 $、₱、USD、PHP）。然后，通过选择“更改类型”>“小数”将这些条目转换为十进制格式，并在“列”选项卡下使用“自定义列”创建了一个名为“价格（美元）”的新列。我实施了以下公式：
if [Store] = "Shopee PH"then Number. Round([Price] / 55, 2) else Number. Round([Price], 2)
通过单击“确定”，我将 Shopee PH 的所有价格转换为美元，以便进行清晰的比较。
该公式采用Power Query M 公式语言编写。该脚本语言语法简单，相对容易理解。无需掌握整个语言即可有效使用它；我仅在构建公式时才参考它。
一旦数据被纠正，我只需关闭 Power Query 编辑器，单击“保留”即可保存对工作簿所做的所有更改。
Power Query 节省的时间
起初，我犹豫是否要使用 Power Query，担心它会引入一个复杂的功能，占用我太多时间。结果出乎意料，我发现它简单得多。Power Query 让我能够高效地清理抓取工具生成的数据，而无需执行无休止的查找和替换操作。
更让我惊讶的是，我实施的每项调整都记录在案，让我能够毫不费力地复制清理过程。这本质上就像一个数据清理的自动化脚本，将混乱的 CSV 文件转换成井然有序的电子表格——非常适合通过网页抓取生成的自定义数据集，因为这类数据集的数据质量通常参差不齐。
对于经常处理繁琐的数据清理任务、不一致的格式或多个数据源的用户，Power Query 将这些流程简化为易于管理的自动化工作流程。无需每周花费数小时手动修复问题，只需单击“刷新”即可直接进行分析。这是 Excel 的一项强大功能，真希望自己早点用上它。一旦体验到自动化、易于重复的清理流程的高效性，就再也回不去了。
发表回复