☰

資料範例

tag-icon • tag-icon microsoft-excel worksheet-function
資料範例

我正在為一支新興的電競團隊編寫組織表，我的目標是計算團隊中玩家的平均遊戲排名。

細胞F2和每個其他列中的行F具有等級標識符，例如“S3”或“G1”（用於等級銀 3 或金 1）。

我有另一張表，其中包含這些排名標識符之間的數字映射（I1 是 1，I2 是 2 等）

因此，對於此範圍 ( F2:F100) 中的所有其他行，我需要獲取標識符，通過VLOOKUP針對工作表的方式將其轉換為數字形式，對結果Lookups進行 a 操作。FLOOR(AVERAGE()VLOOKUP

我已經完成了幾個步驟，但我不確定如何對像這樣的先前公式的結果進行平均。

要從文字版本中取得排名的數位版本，公式為：

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

我不知道的是：

  • 如何進行省略所有其他儲存格的儲存格引用
  • 如何對大量 VLOOKUP 結果求平均值

資料範例

主表

主電子表格

查找表

尋找電子表格

每組兩列代表一個玩家，該玩家的排名儲存在偶數列的 F 行上。從查找表中，我們可以看到 S3 代表分數 10。

一旦我得到了這些數字的總和，我就取平均值，得到團隊的平均分數。回到查找表，這是與先前的操作相反的操作，並給出了與表中列出的每個玩家的平均得分相對應的單一排名。

就目前名單上的三名選手而言，我會選擇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)

如果您不想重新利用這些空白儲存格，「輔助列」是一個可行的替代方案。我將空白法與輔助柱法進行了測試，結果是相同的。

1如果現有排名由公式傳回且未輸入，則xlCellType常數應替換為xlCellType公式。

答案2

我將解決第一個查找問題，即每隔一行都需要一個值，方法是將 vlookup 放入 iferror 公式中，該公式在找不到結果時僅返回空白文字：

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

接下來，您可以只使用舍入公式內的平均值公式來截斷小數。平均公式將簡單地忽略空白行。

最後，vlookup 公式無法從此平均值中獲取“排名”，因為它總是從左側開始，然後向右移動 - 您需要查看右側，然後向左移動。為此，我使用索引/匹配組合：

=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)得到平均值

相關內容