如何用Excel非数组公式生成排除指定玩家的对手列表

方法1：用非数组公式实现（无需按组合键） #

假设你的选手统计表里，Player列是A列（从A2开始），要填Opponent的是B列，完整选手列表在$P$2:$P$100（自行替换为实际单元格范围）。

在B2单元格输入以下公式，然后向下填充：

=IFERROR(INDEX($P$2:$P$100, AGGREGATE(15, 6, (ROW($P$2:$P$100)-ROW($P$2)+1)/($P$2:$P$100<>$A2), ROWS($B$2:B2))), "")

公式说明： #

• AGGREGATE(15,6,...)：15对应SMALL函数取最小值，6代表自动忽略错误值，等于当前Player的行会被直接排除
• ROW(...) - ROW($P$2)+1：计算P列中每个选手的相对位置，方便INDEX函数调用
• ROWS($B$2:B2)：下拉时自动递增计数，依次取出第1、2、3...个符合条件的对手
• IFERROR兜底：所有对手列完后，单元格显示空值，不会出现错误提示

方法2：用Power Query更省心 #

如果觉得公式复杂，Power Query是更直观的选择，Mac版Excel 16.16.27完全支持：

1. 选中P列的完整选手列表，点击顶部菜单栏「数据」>「从表格/范围」，弹出窗口勾选「我的表格有标题」（若P列有表头），确认创建表格
2. 进入Power Query编辑器后，点击「主页」>「合并查询」>「合并查询作为新查询」
3. 选择你的选手统计表格作为第二个表，在合并条件中选择「Player列」和「P列的选手名」，匹配规则改为「不等于」
4. 展开合并后的列，只保留选手名字字段
5. 点击「主页」>「关闭并上载」，结果会加载到新工作表。后续修改Player列或选手列表后，右键结果表选择「刷新」即可自动更新

小提示 #

• 方法1中的$P$2:$P$100务必替换为你实际的选手列表范围
• 使用Power Query时，建议将选手统计表格也转为Excel表格（按Ctrl+T），这样刷新时能自动识别新增的选手或Player行

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