COUNTIFを使用してExcelでランクおよびSUMPRODUCT関数を使用する方法

COUNTIF、RANK.EQ、およびSUMPRODUCT関数の組み合わせを使用して、アイテムを昇順または降順でランク付けするのは比較的簡単です。重複の有無にかかわらずアイテムを並べ替えることができ、シーケンス内のランクをスキップするかスキップしないかの制御を維持できます。

同じランクを複数の同一のエントリに割り当てることにより、RANK.EQ関数はシーケンス全体の番号をスキップします。このスキップを回避するには、SUMPRODUCT関数をCOUNTIF関数と一緒に使用します。この組み合わせは、数値をスキップせずに重複する値をランク付けします。

Excelのさまざまなランク関数

その実装を詳しく見る前に、Excelにある他の2つのフォームを簡単に確認しましょう。 RANK.AVGおよびRANK。

以前のバージョンのExcelでは、RANK関数のみが使用可能でした。ただし、Excel 2010以降、RANK.AVGとRANK.EQの2つのランク関数が追加されました。それらはすべてExcelで表示される可能性があるため、それらの違いを調べてみましょう。

RANK.EQ関数: RANK.EQは、RANK関数の新しく改良されたバージョンであり、最新バージョンのExcelでサポートされています。ただし、両方の機能は同じであり、同じ目的を果たします。何を使用しても、同じ結果が得られます。

RANK関数:一方、RANK関数は、Excel 2007以前との互換性のために使用可能であり、新しいExcelバージョンのいずれかで削除される可能性があります。現在、誰もが最新のExcelバージョンを使用しているため、他のユーザーとシートを共有する必要がある場合は、常にRANK.EQ関数を使用してください。

ランク。 AVG:ランクシーケンスに重複が含まれている場合、RANK.AVG関数は重複の各セットに平均ランクを割り当てます。したがって、アイテムをランク付けするのと同じ目的を果たしますが、その主な目的は重複をキャッチすることです。

この記事ではアイテムリストのランクの計算を扱っているので、RANK.EQ関数がそれらをランク付けする方法を見てみましょう。さらに、COUNTIF関数と組み合わせて使用​​することもできます。

式を実装する前に、RANK.EQ関数の構文の概要を説明しましょう。

Excelのランク関数の概要

したがって、RANK.EQ関数には3つの引数があります。番号、参照、および順序。最初の2つの引数は必須ですが、3番目の引数はオプションです。数値の配列に対してRANK.EQを実行するには、常に最初の2つの引数を指定する必要があります。

各引数の関数を見てみましょう。

  1. 番号:この引数は、ランクを知りたい番号を参照します。
  2. Ref: Ref引数は、シーケンス内の数値のリストの配列を参照します。 refは、配列で見つかった数値以外の値を無視することに注意してください。
  3. 順序:オプションの引数ですが、この引数で0または1を選択することにより、RANK関数を指定してアイテムを降順または昇順でランク付けできます。デフォルトでは、省略した場合、アイテムは降順でランク付けされます。

Excelランク関数の例

それで、RANK.EQ関数の引数を理解したので、それがどのように機能するかを確認するために、データセットでそれを実践してみましょう。

例として、10人の学生のパーセンテージを含むサンプルデータを見てください。 Rank.EQ関数を使用して、これらの学生のパーセンテージに基づいてランクをすばやく計算できます。これにより、手動で計算する必要がなくなります。

ただし、重複が含まれる場合は数値をスキップするため、この記事で後述する他の関数を使用する必要がある場合があります。以下は、最終的なパーセンテージを持つ学生のリストです。

最初の引数は、ランク付けする数値です。これは、この例の行の下にあるJamesWalkerと他の学生のパーセンテージです。したがって、最初の引数としてセルB2を選択します。

この場合の基準範囲はセルB2からB11であり、順序引数で昇順を選択しましょう。以下に、RANK.EQ関数の実装を示します。式は次のようになります。

 =RANK.EQ(B2,$B$2:$B$11,0)

Enterキーを押すと数式が実行され、JamesWalkerのランクが表示されます。

この例では、James Walkerはパーセンテージに基づいてクラスで5番目にランク付けされています。これは、配列内の他の数値と比較して、彼のパーセンテージが5番目に高いことを意味します。

RANK関数をCOUNTIF関数と組み合わせて使用​​する

RANK.EQ関数をCOUNTIF関数と組み合わせて使用​​して、数値のスキップを停止することは可能ですが、ランクの重複も無視されます。番号が2回繰り返されることはありませんが、同じ番号の2人の生徒のランクが異なる場合があります。

これをよりよく理解するために、RANK.EQがCOUNTIFとどのように連携するかを見てみましょう。式は次のようになります。

 =RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1

この式を実装すると、数値のスキップの問題が解決されます。

上記のランクに重複はありません。しかし、同じランクになるはずだったJamesWalkerとGillianTillmanは、現在、ランクが異なります。

したがって、COUNTIFでRANK.EQを使用すると、問題の半分は解決されましたが、目的の結果を生成できません。

関連:生産性を向上させるためのExcel for theWebの新機能

COUNTIF関数でのSUMPRODUCT関数の使用

番号をスキップせずに同等のパーセンテージに同じランクを割り当ててリスト内の学生をランク付けするには、COUNTIFでSUMPRODUCT関数を使用できます。

以下の式を見てください。

式は複雑に見えるかもしれませんが、アイテムを正しくランク付けするための最良の方法です。このようにして、ランクを複製し、番号をスキップすることなく、目的の結果を得ることができます。

学生のために結果を作成している間、RANK関数の代わりにSUMPRODUCT式を直接使用できます。重複しないランキング計算の場合は、RANK.EQ関数を単独で使用するか、COUNTIF関数と一緒に使用できます。

最終結果の順序の変更

[データ]タブで、[並べ替えとフィルター]グループをクリックし、昇順を選択してランキングを並べ替えます。

3行の結果を並べて比較し、アイテムのランク付けの各方法がどのように機能するかをよりよく理解します。

関連: MicrosoftExcelのデータテーブルで結果を比較する方法

RANK.EQ関数とSUMPRODUCT関数を使用してアイテムを簡単にランク付けする

これは、RANK関数とSUMPRODUCT関数をCOUNTIF関数と組み合わせて使用​​して、重複の有無にかかわらず、アイテムをランク付けする方法です。シーケンス内の任意の番号をスキップするかどうかを決定することもできます。

状況に応じて、さまざまな方法を使用して、指定された一連の基準に従って数値をランク付けできます。これは、Excel関数が手動計算で多くの時間と労力を節約するのにどのように役立つかの一例です。プロ並みのExcelスプレッドシートを設計するために、さらに多くのことを検討する必要があります。