如何在Excel作物-国家种植数据中查找行与列交集及相似作物

嘿，这个需求我之前帮同事处理过，给你几个实用的操作思路，从简单到进阶都覆盖到了：

假设你的表格结构是：A列是作物名称，B到N列是国家，单元格里的1/0表示是否种植。

1. 先计算每个作物的总种植国家数：
   在O2单元格输入公式：=SUM(B2:N2)，下拉填充到所有作物行，这样就能知道每个作物在多少个国家有种植。
2. 计算任意两个作物的共同种植国家数：
   比如要对比第2行（作物A）和第3行（作物B）的交集，在空白单元格输入：=SUMPRODUCT((B2:N2)*(B3:N3))
   这个公式的原理是把两行的0/1对应相乘，只有当两个单元格都是1时结果才是1，求和后就是共同种植的国家数量。
3. 批量筛选高相似度作物：
   如果你想找和某作物（比如作物A）共同种植国家超过总国家数60%的作物，可以用条件格式：
   • 选中所有作物行的国家数据区域（B2:NXX）
   • 点击「开始」→「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
   • 输入公式：=SUMPRODUCT((B$2:N$2)*(B2:N2))/COUNTA(B$1:N$1)>=0.6
   • 设置高亮格式，这样符合条件的作物行就会自动标出来。

如果你的作物或国家数量很多，手动计算效率太低，Power Query能帮你批量处理：

1. 导入数据到Power Query：
   选中数据区域→「数据」→「从表格/区域」，勾选「我的表格有标题」，进入Power Query编辑器。
2. 转置数据并整理：
   点击「转换」→「转置」，此时国家变成行，作物变成列。然后筛选出值为1的列（即该国家种植的作物）。
3. 统计作物共同出现的频次：
   添加自定义列，用Text.Combine(List.Select(Table.ColumnNames(#"转置的表"), each Record.Field(_, [国家])=1), ",")提取每个国家的种植作物列表，然后分组统计每个作物组合出现的次数——次数越多，说明这些作物共同种植的国家越多。
4. 导出结果：
   点击「关闭并上载」，就能得到所有作物组合的共同种植国家数，直接筛选高频次的组合即可。

如果你只想先找出在多数国家都种植的单个作物，再看它们的交集，可以用数据透视表：

1. 选中数据区域→「插入」→「数据透视表」
2. 将「作物」拖到「行」区域，「国家」拖到「值」区域，把值字段设置为「计数」（只统计1的话可以用COUNTIF自定义计算）
3. 筛选出计数结果大于「总国家数×阈值」的作物（比如总共有20个国家，阈值设为10，就筛选计数≥10的作物）
4. 把这些高频作物单独提取出来，再用方法1计算它们之间的相似度。

内容的提问来源于stack exchange，提问作者Michael Saneke