使用Power Query将果园列表格式种植数据转换为指定布局的矩阵

嘿，看起来你在果园数据标准化上遇到了挺头疼的具体问题——手动维护多份格式不一的文件确实容易出错，用单份主表自动生成矩阵报表的思路太对了！我刚好对Power Query处理这类结构化转换很熟，咱们一步步来搞定它：

一、先做源数据的基础预处理 #

首先把你的主工作表数据导入Power Query（点击Excel菜单栏「数据」→「自表格/区域」），先做几个关键清洗步骤：

• 统一文本格式：比如Variety列里的「Big Leaf」和「Big leaf」大小写不一致，用Text.Proper([Variety])或者Text.Upper统一成标准格式，避免后续分组出错；
• 确保Loc和RowN是文本类型：选中这两列，点击「转换」→「数据类型」→「文本」，防止数字排序把「001」当成1处理，打乱布局；
• 去重（如果需要）：如果存在同一个Block+RowN+Loc的重复记录，用「开始」→「删除行」→「删除重复项」，或者提前按Planting Year取最新记录。

二、按Block拆分生成独立工作表 #

你的需求是每个Block对应一个工作表，用Power Query可以批量处理：

1. 在Power Query编辑器里，选中Block列，点击「转换」→「分组依据」：
   • 分组依据选Block，新列名设为BlockData，操作选「所有行」，这样每个Block就会对应一个包含其所有记录的子表；
2. 接下来把每个子表加载到单独工作表：
   • 点击「开始」→「关闭并上载至」→选择「仅创建连接」，确定后在Excel的「查询与连接」面板里，右键每个分组后的查询→「加载到」→选择「新工作表」，就能自动生成每个Block对应的工作表了。

三、把每个Block的列表转成目标矩阵 #

针对每个Block的子表，回到Power Query编辑器（右键工作表→「编辑」），做以下核心转换：

1. 准备反向排序的列标题：
   • 先提取唯一的RowN+RowNm组合：添加一个自定义列ColumnHeader，公式写[RowN] & " " & [RowNm]；
   • 然后对RowN做降序排序（点击RowN列标题→选「降序」，确保是文本排序而非数字排序），这样后续生成的列就是从Z到A的顺序；
2. 生成矩阵结构：
   • 点击「转换」→「透视列」：
     • 值列选择你要显示的内容：先添加自定义列CellValue，公式[Variety] & " " & Text.From([Planting Year])，然后透视时值列选CellValue；
     • 列选择RowN（或者直接选ColumnHeader），行选择Loc；
     • 聚合函数选「不要聚合」（因为每个Loc+RowN应该是唯一记录，如果有重复可以提前处理）；
3. 调整列顺序：如果透视后的列顺序不是Z到A，手动拖动列调整，或者在透视前确保RowN已经按降序排好，透视后列就会自动按这个顺序生成。

四、设置条件格式（Excel端操作） #

Power Query负责数据结构，条件格式需要在加载后的工作表里设置，这样能自动跟随主表更新：

1. 选中矩阵的数据区域（排除行头和列标题）；
2. 点击「开始」→「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」：
   • Living（绿色填充）：公式写=XLOOKUP($A2&B$1, 主表!$G:$G&主表!$D:$D, 主表!$H:$H, "")="Living"（这里假设A列是Loc，B列开始是RowN列，主表是你的源数据工作表，G列是Loc，D列是RowN，H列是Status）；
   • Dead（红色填充）：把公式里的"Living"换成"Dead"，设置红色填充；
   • Unplanted（蓝色填充）：换成"Unplanted"，设置蓝色填充。

五、处理特殊情况的小技巧 #

• 空白单元格：Power Query透视时，没有匹配记录的单元格会自动留空，完全符合果园空白地块的情况，如果你想显示「空白」字样，可以在透视时设置默认值，或者添加自定义列替换空值；
• 不同Loc范围的RowN：比如RowN A有001-060，RowN B有001-120，Power Query会自动把所有出现过的Loc作为行，没有数据的单元格留空，正好还原果园的实际布局，不需要额外处理。

六、可选美化（满足额外需求） #

1. 自动生成标题：在矩阵上方插入一行，输入公式=CONCAT(UNIQUE(主表!$B:$B), " ", UNIQUE(主表!$C:$C))，就能自动生成「Block+BlockNm」的标题；
2. 添加方向标注：根据果园的布局对应关系，在矩阵的顶部标注North、底部标注South，左侧标注West、右侧标注East，直接插入单元格输入即可。

备注：内容来源于stack exchange，提问作者GNG85