主页 / computer / 问题 / 1473766
Accepted
Karu
Asked: 2019-08-22 09:09:35 +0800 CST

Excel 公式 - 源字段中每个其他字段的 vlookup 结果的平均值

  • 772

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

单元格和列F2中的每一行都有一个等级标识符，如“S3”或“G1”（用于等级银 3 或金 1）。F

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

因此，对于此范围 ( ) 中的每一行F2:F100，我需要获取标识符，通过VLOOKUP对工作Lookups表将其转换为数字形式，对结果执行 a 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”。

microsoft-excel worksheet-function

3 个回答

  1. 我将通过将 vlookup 放在 iferror 公式中来解决第一个查找问题，您需要每隔一行输入一个值，该公式在找不到结果时只返回空白文本：

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

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

    最后，vlookup 公式无法从这个平均值中获取“排名”，因为它总是从左边开始，然后向右移动——你需要向右看，然后向左移动。为此，我使用索引/匹配组合：

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

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

    我在显示写出的公式的示例表中构建了它。希望这会有所帮助：显示公式示例的示例解决方案

    • 1
  2. Best Answer

    Q：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)

    如果您不想重新利用这些空白单元格，“辅助列”是一个可行的选择。我针对辅助柱方法测试了空白方法，结果是相同的。

    ¹如果现有排名由公式返回而不是输入，则xlCellTypeConstants应替换为xlCellTypeFormulas。

    • 1

  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)用来取平均值

    • 0

相关问题