![数据示例](https://linux22.com/image/1601235/%E6%95%B0%E6%8D%AE%E7%A4%BA%E4%BE%8B.png)
我正在为一个刚刚起步的电子竞技团队编写组织表,我的目标是计算我团队中玩家的平均游戏排名。
细胞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)
得到平均值