☰

数据示例

tag-icon • tag-icon microsoft-excel worksheet-function
数据示例

我正在为一个刚刚起步的电子竞技团队编写组织表，我的目标是计算我团队中玩家的平均游戏排名。

细胞F2和每一个其他列中的行F有一个等级标识符，如“S3”或“G1”（代表等级银 3 或金 1）。

我有另一张表，其中包含这些等级标识符之间的数字映射（I1 是 1、I2 是 2，等等）

因此，对于此范围（）中的每一行F2:F100，我需要获取标识符，通过VLOOKUP对照工作Lookups表将其转换为数字形式，对结果执行操作FLOOR(AVERAGE()，然后通过反转将该数字转换回文本形式VLOOKUP。

我已经完成了几个步骤，但我不确定如何对这样的先前公式的结果进行平均。

要从文本版本中获取排名的数字版本，公式如下：

=VLOOKUP(F2, Lookups!A2:B29,2, FALSE)

我不知道的是：

  • 如何进行省略其他所有单元格的单元格引用
  • 如何对大量 VLOOKUP 结果求平均值

数据示例

主表

主电子表格

查找表

查找电子表格

每组两列代表一名玩家，该玩家的排名存储在偶数列的 F 行。从查找表中，我们可以看到 S3 代表数字分数 10。下一个玩家的 G2 分数为 15，依此类推。

将这些数字相加后，我会取平均值，从而得到该队的平均得分。回到查找表，此操作与上一个操作相反，并给出与表格上列出的每个球员的平均得分相对应的单个排名。

就目前表格上的三名球员而言，我会选择 S3、G2 和 NA。这样我的得分分别为 10、15 和 10。这些分数的平均值是 11（向下舍入），这对应于使用查找表得到的 S2 排名。包含此假设公式的单元格将显示“S2”。

答案1

问：F3、F5、F7 等会保持空白吗？
答：会的。我把这个字段变灰了，表示这里不能输入任何内容。

最简单的做法是使用这些空白单元格来保存各个等级的数值。然后您可以计算 F2:F101 的平均值，这样只会考虑真实数字。可以使用单元格数字格式隐藏数字。;;;这会显示一个空白单元格，用于显示任何键入的内容或公式返回的内容，同时保留原始值。

如果您不想复制和粘贴 50 次，请在第一个代码行中调整主工作表的名称后运行这个简短的子程序。

Option Explicit

Sub PopulateLookups()

    With Worksheets("sheet1")
        With .Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))
            With .SpecialCells(xlCellTypeConstants, xlTextValues)  '<~~ see footnote
                With .Offset(1, 0)

                    .FormulaR1C1 = "=vlookup(r[-1]c, lookups!r2c1:r29c2, 2, false)"
                    .NumberFormat = ";;;"

                End With
            End With
        End With
    End With

End Sub

现在，您可以使用以下方法之一来获得查找值的平均值。

=average(F2:F100)
=average(F:F)

如果您不想重新利用这些空白单元格，那么“辅助列”是一种可行的替代方案。我针对辅助列方法测试了空白方法，结果完全相同。

¹如果现有排名由公式返回，而不是输入，则单元格类型常量应替换为单元格类型公式。

答案2

我将解决第一个查找问题，其中您需要每隔一行都有一个值，方法是将 vlookup 放在 iferror 公式中，当找不到结果时，该公式仅返回空白文本：

=IFERROR(VLOOKUP(F2,Lookups!A2:B29,2,0),"")

接下来，您可以直接使用平均值公式，在四舍五入公式中截断小数。平均值公式将直接忽略空白行。

最后，vlookup 公式无法从这个平均值中获取“排名”，因为它总是从左边开始，然后向右移动 - 而您需要先查看右边，然后向左移动。为此，我使用 Index/Match 组合：

=INDEX(Lookups!A2:B29,MATCH(E11,Lookups!B2:B29,1),1)

对于匹配部分，我使用了“1”参数，这样它就可以找到最接近的值而不会超过，假设它可能不是完全匹配。查看 Excel 中的索引和匹配公式描述以获取更多信息和选项。

我在示例表中构建了此内容，其中显示了写出的公式。希望这对您有所帮助：显示带有公式示例的示例解决方案

答案3

假设您想要获取偶数行的平均值，请使用以下公式：

=IF(ROW()/2 = ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

否则使用：

=IF(ROW()/2 <> ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

然后使用=AVERAGE(G1:G5)得到平均值

相关内容