補助列を使用せずに、複数列をキーとした順位付けを行う計算式

Excelでデータを管理する際、複数の基準に基づいて順位付けを行いたい場合があります。1つのやり方としてはRANK.EQ関数、もしくはRANK.AVG関数と補助列を作成しての順位付けです。このやり方については下記のページを参照してください。

複数の列を基に順位をつける(RANK系の関数)

複数の列を基に順位をつける手順 エクセルにおいて、RANK.EQ関数を用いて1つの列だけでなく、複数の列を基に順位をつける方法を解説します。具体的な例を使って説明しますので、ぜひ参考にしてみてください。 準備するデータ 以下のようなデータを...

www.helpaso.net

2023.07.15

RANK系の関数や補助列を使用せずにこの操作を行いたい場合には、動的配列関数を組み合わせることで、効率的に目的を達成できます。本記事では、XMATCH関数とSORTBY関数を使用して、複数列をキーとした順位付けを行う計算式を詳しく紹介します。

コピペ用使用する計算式

下記の式は配列内の順序を求める計算式です。「キー列N」はSORTBY関数の引数内で増やすことが可能です。また「-1」は「降順(大きい順)」を意味しています。「昇順(小さい順)」にしたい場合は「1」に変えてください。

=XMATCH(SEQUENCE(ROWS(配列)), SORTBY(SEQUENCE(ROWS(配列)), キー列1, -1, キー列2, -1))

以下のサンプルは「配列」を「A2:G11」とし、第一優先キーをF列、第二優先キーをG列としています。

=XMATCH(SEQUENCE(ROWS(A2:G11)),SORTBY(SEQUENCE(ROWS(A2:G11)),F2:F11,-1,G2:G11,-1))

使用する各関数

この計算式では、以下の関数を使用します。簡単な概略を掲載します。引数名は英語版のものを掲載しています。

SEQUENCE関数

SEQUENCE関数は、指定した数の連続した数値を生成するために使用します。この関数の構文は以下の通りです:

SEQUENCE(rows, [columns], [start], [step])

ここで、rowsは生成する行数、columnsは生成する列数、startは開始値、stepはステップ値です。例えば、SEQUENCE(10)は1から10までの連続した数値を生成します。

ROWS関数

ROWS関数は、指定した範囲内の行数を返します。例えば、ROWS(A2:A11)は範囲A2からA11までの行数である10を返します。SEQUENCE関数と組み合わせることで、動的に生成する数値の範囲を指定できます。

SORTBY関数

SORTBY関数は、指定した基準に基づいて範囲または配列をソート(並べ替え)します。この関数の構文は以下の通りです:

SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

ここで、arrayはソートする範囲または配列、by_array1はソートの第1基準、sort_order1はソート順序（1は昇順、-1は降順）を指定します。例えば、SORTBY(A2:C11, B2:B11, -1, C2:C11, -1)は、範囲A2:C11を第1基準としてB2:B11を降順に、次にC2:C11を降順にソートします。B列もC列も同じ場合は元の並び順を受け継ぎます。

XMATCH関数

XMATCH関数は、指定した検索値が指定された範囲内でどの位置にあるかを返します。この関数の構文は以下の通りです:

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

ここで、lookup_valueは検索する値、lookup_arrayは検索が行われる範囲または配列、match_modeは検索方法、search_modeは検索の方向や方法を指定します。例えば、XMATCH(A2:A11,A2:A11)は、連続番号を生成します。

計算式の詳細な解説

ここでは、上記の各関数を組み合わせて、複数列をキーとした順位付けを行う計算式を詳細に解説します。

=XMATCH(SEQUENCE(ROWS(配列)), SORTBY(SEQUENCE(ROWS(配列)), キー列1, -1, キー列2, -1))

SEQUENCE(ROWS(配列))

まず、SEQUENCE(ROWS(配列))は、配列の行数に基づいて連続した数値の配列を生成します。これにより、配列の各行に対するインデックスが作成されます。

SORTBY(SEQUENCE(ROWS(配列)), キー列1, -1, キー列2, -1)

次に、SORTBY関数を使用して、生成されたインデックスをキー列1およびキー列2に基づいてソートします。ここで、-1は降順を意味します。これにより、キー列1を第1基準として降順に、次にキー列2を第2基準として降順にソートされたインデックスの配列が生成されます。

XMATCH(SEQUENCE(ROWS(配列)), ソートされた配列)

最後に、XMATCH関数を使用して、元の連続数値の配列とソートされた配列を比較し、各行の順位を求めます。XMATCH関数は、元のインデックスの配列がソートされたインデックスの配列内でどの位置にあるかを返します。

具体的な完成例

例えば、以下の表(A1:G11)に基づいて順位付けを行う場合を考えます。第一優先キーをF列(勝率)、第二優先キーをG列(得失点)とします。

この場合、次の計算式を使用します。これをセルI2へ入れるとします。

=XMATCH(SEQUENCE(ROWS(A2:G11)), SORTBY(SEQUENCE(ROWS(A2:G11)), F2:F11, -1, G2:G11, -1))

ここで、範囲A2:G11の各行に対して、F列（勝率）を第1基準として降順に、次にG列（得失点）を第2基準として降順に並べ替えをします。最後に並べ替えた時の上からの位置を提示します。

計算式コピペの流れ

計算式テンプレートをコピペしてからの使用の流れを見てみましょう。以下の計算式をI2へコピペし、「関数の挿入」(fx)で編集します。

=XMATCH(SEQUENCE(ROWS(配列)), SORTBY(SEQUENCE(ROWS(配列)), キー列1, -1, キー列2, -1))

数式バーで最初のROWS関数をクリックして編集状態にし、「配列」で表の高さを指定します。

2個目のROWS関数の「配列」でも同様に表の高さを指定します。

SORTBY関数をクリックし「基準配列・並べ替え順序」の指定に進みます。「-1」は降順、「1」は昇順です。

第一優先キーをF列、第二優先キーをG列、ともに降順とした場合の例です。設定後はOKします。

補助列を使わずに複数列をキーとした順位付けができました。