エクセルで出現回数のランキング表を作りたい

質問

・6桁以下の選手番号
・試合日（西暦8桁）
・試合結果
・その他

無作為の順番で上記のデータが入ったエクセルの表があるのですが、
この表を使い、試合に出た回数が多い順番で選手をランキング付けしたいのです。

関数やVBAは若干かじったことある程度の経験しかないのですが、
COUNTIF関数を使い、その選手にとって、
その試合が何試合目なのかは、一応目で分かるようにはなりました。

※ 選手番号がA列だとすると、B1=COUNTIF($A$1:A1,A1)をB列にコピーしました。

本当はこの数字を上手く使ってランキング表を作ろうと思ったのですが、
良い案が浮かばず、今回質問させていただきました。

1位　選手番号　試合回数
2位　選手番号　試合回数
・
・
・

できることなら、上記のようにランキング表を作り、
また選手の数が多いので3回以上試合を行なっている選手だけで表を作りたいのですが、
関数やVBAを使い上記の表を作成する良い案はありませんでしょうか？

回答No.2

tom04 · ベストアンサー

こんばんは！
一例です。

せっかくCOUNTIF関数で試合数を出されているので、余計なお世話になるかもしれませんが・・・
↓の画像のようにこちらで勝手に表を考えてみました。

作業用の列を2列使わせてもらっています。
作業列E2セルに
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,COUNTIF($A$2:$A$100,A2),""))

F2セルに
=IF(E2="","",COUNTIF($E$2:$E$100,">"&E2)+COUNTIF($E$2:E2,E2))
として、E2・F2セルを範囲指定し、F2セルのフィルハンドルで下へずぃ～～～！っとコピーします。

（F列は単純にRANK関数で処理してしまうと、同試合数の場合抽出が困難になりますので、敢えて同試合数でも順位を変えています。
尚、同試合数の場合は上側の行の方が上位になります）

そしれ、I2セルに
=IF(COUNT($E$2:$E$100)<ROW(A1),"",INDEX($A$2:$A$100,MATCH(ROW(A1),$F$2:$F$100,0)))
J2セルに
=IF(I2="","",COUNTIF($A$2:$A$100,I2))
という数式を入れ、I2・J2セルを範囲指定し、J2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

数式は100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

画像まで添付していただきありがとうございました！！！
補足もしていただき、おかげさまで上手くいきました！

回答No.4

KURUMITO

シート1ではＡ1セルに選手番号、Ｂ1セルに試合日、Ｃ1セルに試合結果、Ｄ1セルにその他の文字列がそれぞれ入力されておりデータが2行目から下方にあるとします。
最初にすべての選手について試合回数についてのランク付けの方法を示します。当然3回以上試合を行っている選手についてもランク付けする方法についてもその後に示します。
シート1には作業列としてＥ1セルには試合回数と文字列を入力しＥ2セルには次の式を入力します。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),""))

Ｆ2セルには次の式を入力します。

=IF(E2="","",RANK(E2,E:E)+COUNTIF(E$2:E2,E2)/10000)

Ｅ2セルとＦ2セルを選択してそれらの式を下方にオートフィルドラッグします。

答えとなるシート2ではＡ1セルに順位、Ｂ1セルに選手番号、Ｃ1セルに試合回数と文字列を入力します。

Ａ2セルには次の式を入力します。

=IF(ISNUMBER(SMALL(Sheet1!$F:$F,ROW(A1))),INT(SMALL(Sheet1!$F:$F,ROW(A1))),"")

Ｂ2セルには次の式を入力します。

=IF(A2="","",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)))

Ｃ2セルには次の式を入力します。

=IF(B2="","",INDEX(Sheet1!E:E,MATCH(SMALL(Sheet1!$F:$F,ROW(B1)),Sheet1!$F:$F,0)))

最後にＡ2セルからＣ2セルを選択しそれらの式を下方にオートフィルドラッグします。

試合回数が3回以上の選手だけを表示するのでしたらシート1のＥ2セルに入力する式を次のようにして下方にオートフィルドラッグすればよいでしょう。シート2での変更は必要ありません。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,IF(COUNTIF(A:A,A2)>=3,COUNTIF(A:A,A2),""),""))

お礼

知らない関数がいくつかあり、勉強になりました！
ありがとうございます！

回答No.3

tom04

No．2です！
たびたびごめんなさい。
投稿した後で質問を読み返していると・・・

>選手の数が多いので3回以上試合を行なっている選手だけで・・・

という事ですので、
前回の作業列E2セルの数式を変更してみてください。

=IF(AND(COUNTIF($A$2:A2,A2)=1,COUNTIF($A$2:$A$100,A2)>=3),COUNTIF($A$2:$A$100,A2),"")

という数式にして、後の作業は前回と同じで大丈夫だと思います。

どうも何度も失礼しました。m(__)m

回答No.1

nza49739

VBAとか使ったように、1発で表示させることはできませんが、EXCELの基本機能を利用すれば、近い形にはなります。

順位はEXCELの持つソート機能で並び替えすればいいでしょう。先頭のセルから1と入れてコピーしていけばいいですが、同じ試合回数があると順位が正しくならないですけど。
特定回数以上のものに限定したい場合は、抽出機能（フィルタ）で表示しないようにできます。