Excel同Match ID双变量分析：英雄首选用位胜率统计求助

一、Excel函数规范实现（解决空行问题） #

针对公式空行的问题，核心是先提取无重复且有效的对局样本，再匹配对位英雄，避免无效空值：

假设数据源在Sheet1，字段对应：A=Match ID、D=首选英雄、F=对战阵营、C=战队名称、G=获胜方。

1. 提取Xelor首选的无重复对局ID（适用于Excel 365/2021动态数组版本）：

   =UNIQUE(FILTER(Sheet1!A:A, Sheet1!D:D="Xelor"))
   

   公式会自动溢出所有符合条件的Match ID，无空行。

2. 匹配每局的对位首选英雄：
   在相邻单元格（如J2）输入：

   =XLOOKUP(1, (Sheet1!A:A=I2)*(Sheet1!F:F<>XLOOKUP(I2, Sheet1!A:A, Sheet1!F:F)), Sheet1!D:D)
   

   其中I2是第一步得到的Match ID，公式会自动定位同对局对立阵营的首选英雄，无匹配时可加", ""避免错误显示。

3. 统计对战次数与胜率：

   • 次数：用COUNTIF(J:J, "目标英雄")统计该英雄的出现频次
   • 胜率：先在K列标记Xelor战队是否获胜：

     =--(XLOOKUP(I2, Sheet1!A:A, Sheet1!C:C)=XLOOKUP(I2, Sheet1!A:A, Sheet1!G:G))
     

     再用SUMIF(J:J, "目标英雄", K:K)/COUNTIF(J:J, "目标英雄")计算胜率

旧版Excel无动态数组时，用数组公式生成无空行列表：

=IFERROR(INDEX(Sheet1!A:A, SMALL(IF(Sheet1!D:D="Xelor", ROW(Sheet1!A:A)), ROW(A1))), "")

按Ctrl+Shift+Enter执行，下拉直到出现空值即可。

二、Power Query高效方案（推荐） #

用Power Query处理这类关联统计，彻底避免公式空行和复杂嵌套：

1. 导入数据源到Power Query：点击「数据」>「自表格/区域」，加载数据
2. 筛选Xelor首选样本：添加筛选，将「首选英雄」设为"Xelor"，保留Match ID、对战阵营、战队名称、获胜方字段
3. 匹配对位英雄：
   • 新建自定义列，定义对立阵营：= if [对战阵营] = "蓝方" then "红方" else "蓝方"（根据实际阵营名调整）
   • 合并查询：选择「合并查询」，匹配条件为Match ID = Match ID且对战阵营 = 自定义对立阵营，提取对方的「首选英雄」列
4. 分组统计：
   • 点击「分组依据」，分组字段选「首选英雄（对位）」，添加两个聚合项：
     • 对战次数：操作选「计数行」
     • 获胜次数：先新建自定义列= if [战队名称] = [获胜方] then 1 else 0，再聚合项选「求和」
5. 计算胜率：新建列= [获胜次数]/[对战次数]，设置百分比格式
6. 加载回Excel，得到无空行的规范统计表格，后续更新数据只需点击「刷新」。

三、进阶分析思路 #

• 多维度拆分：加入「对战地图」「是否为Top64战队」维度，分析Xelor在不同场景下的对位差异（比如某张地图对特定英雄的胜率）
• 动态交互：用数据透视表+切片器，将「首选英雄」设为切片器，快速切换目标英雄，一键查看所有对位数据

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