☰

データ例

tag-icon • tag-icon microsoft-excel worksheet-function
データ例

私は新進気鋭の e スポーツ チームの組織シートを作成しており、ここでの目標は、チームのプレイヤーのゲーム内平均ランクを計算することです。

細胞F2とすべての他の列の行にはF、「S3」や「G1」(ランクがシルバー 3 またはゴールド 1 の場合) などのランク識別子が含まれます。

これらのランク識別子間の数値マッピングを含む別のシートがあります (I1 は 1、I2 は 2 など)

したがって、この範囲 ( ) 内の他のすべての行については、識別子を取得し、シートに対してF2:F100を実行してそれを数値形式に変換し、結果に対して を実行し、 を逆にしてその数値をテキスト形式に戻す必要があります。VLOOKUPLookupsFLOOR(AVERAGE()VLOOKUP

すでにいくつかの手順を実行しましたが、このような以前の数式の結果を平均化する方法がよくわかりません。

ランクのテキストバージョンから数値バージョンを取得するには、次の式を使用します。

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

私が知らないのは:

  • 1つおきのセルを省略するセル参照を行う方法
  • 大量の VLOOKUP 結果を平均化する方法

データ例

メインシート

メインスプレッドシート

ルックアップテーブル

ルックアップスプレッドシート

2 列の各セットは 1 人のプレーヤーを表し、そのプレーヤーのランクは偶数列の F 行に格納されます。ルックアップ シートから、S3 は数値スコア 10 を表すことがわかります。次のプレーヤーの G2 はスコア 15 になります。

これらの数字を合計したら、平均を取り、チームの平均スコアを算出します。参照テーブルに戻ると、これは前の操作の逆順に実行され、シートにリストされているすべてのプレーヤーの平均スコアに対応する単一のランクが得られます。

現在シート上の 3 人のプレーヤーから、S3、G2、NA を選びます。この場合、スコアは 10、15、10 になります。これらの平均は 11 (切り捨て) で、ルックアップ テーブルを使用して S2 のランクに相当します。この仮定式を含むセルには「S2」と表示されます。

答え1

質問：F3、F5、F7などは空白のままになりますか?
A: はい、入力できます。何も入力してはいけないことを示すために、フィールドをグレー表示にしました。

最も簡単な方法は、これらの空白セルを使用して、個々のランクの数値を保持することです。その後、F2:F101 を平均すると、実際の数値のみが考慮されます。セルの数値形式を使用して、数値を非表示にすることができます;;;。これにより、生の値が保持されたまま、入力された値や数式から返された値に対して空白セルが表示されます。

50 回コピーして貼り付けたくない場合は、最初のコード行で Main ワークシートの名前を調整した後、この短いサブプロシージャを実行します。

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)

空白セルを再利用したくない場合は、「ヘルパー列」が実行可能な代替手段です。空白メソッドとヘルパー列メソッドをテストしましたが、結果は同じでした。

¹既存の順位が数式によって返され、入力されていない場合は、xlセルタイプ定数を次のように置き換える。xlセルタイプ数式。

答え2

1 行おきに値が必要な最初のルックアップを解決するには、結果が見つからない場合に空のテキストを返すだけの iferror 式の中に vlookup を配置します。

=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)平均を得るために使用する

関連情報