ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

データベース(Jリーグ順位表)をつくる

 自習のために、エクセルでJリーグ順位表を作成しました。J1の試合をデータベースに入力していくと、順位表が作成されるというものです(下図参照)。4月6日現在、わがアルビレックス新潟は、攻撃力(1試合あたりの平均得点数)が、J1の中で最下位となっており、今年も苦戦を強いられることになりそうです。

c0136904_2016543.jpg



(エクセルファイルのダウンロード)
 今回作成したエクセルファイルは、下記のサイトからダウンロードすることができます。例によって、Excel2007以降をお使いの方用とそれ以前のExcelをお使いの方用に分けて作成してあります。よくわからないという方は、ファイル名の末尾が「xls」となっている方をお使いください。

http://p.tl/ZdhC


(シートの構成)  
1.シート「チ-ム」
 データベースを入力する際に用いるチーム名をリスト化するためのデータと、勝ち点を配分するためのデータが登録され、それぞれ名前が定義されています。名前の定義については、名前ボックス(セルA1のすぐ上にある白い枠。通常は、現在カーソルがあるセルの行列番号が表示されています)の右にある▼ボタンをクリックすると、定義されている名前の一覧が表示されるので、それをクリックすると名前の範囲が選択状態になります。


c0136904_2018201.jpg



 名前を定義するには、範囲を選択した状態で名前ボックスに名前を入力するのが簡単です。


2.シート「結果」
 これは、各節の試合の結果を入力するためのシートで、この部分がデータベースになっています。以前も申し上げましたが、データベースは、先頭行が項目名となっていて、2行目以降にデータを入力していくという形式となります。これに対して、集計表というのは先頭行と左端列にそれぞれ項目名が記入されているものをいい、このサンプルでは、シート「順位表」がそれになります。
 このシートでは、誤入力を防ぐために、データ(試合日・チーム名・得点)を入力するところを黄色く着色してあり、さらに、入力中にチーム名を間違えては何にもならないので、チーム名はリストから選択するようにしてあります。

c0136904_2019175.jpg



 なお、試合結果を入力するには、「Jリーグ公式サイト」のトップページに、各節の試合結果が掲載されているので、それを参考にしてください。

(Jリーグ公式サイト)
http://www.j-league.or.jp/

 Windows Vista 以降をお使いの方であれば、「Shipping Tool」というアクセサリを使うと、試合結果のところだけを画像としてパソコンに取り込んで、そのままExcelに貼り付けることができるので便利です。(下図参照)


c0136904_20205573.jpg


 
 また、このシートでは、順位表を作成するための下ごしらえとしていくつかの計算をするようになっています。それらの計算式は、C列とH列の間、J列とO列の間に隠されています。たとえば、C列とH列をドラッグして、右クリックしたメニューから「再表示」を選ぶと、それらが表示されます。アウェイの側にも同様の計算式が隠されているので、再表示していただくとわかりやすいと思います。


c0136904_20214074.jpg




(計算式の説明)
 入力するデータは、試合日とチーム名と得点だけであり、それらのデータから試合の結果などを導き出して、順位表を作成する材料を作成するために、各種計算式が用意されています。

2-1 「勝敗」欄の計算式
 =IF(AND($C30="",$J30=""),"",IF($C30>$J30,"勝",IF($C30<$J30,"負","引分")))

 ここには、同じ行の得点(対戦相手のもの)を比較して、自チームの得点の方が多ければ「勝」、少なければ「負」、同じであれば「引分」と表示させる計算式が入力されています。ただし、試合がまだ行われていない(両チームの得点欄がともに空白)場合、結果については何も表示させないようにしてあります。

2-2 「勝点」欄の計算式
 =IF(ISERROR(VLOOKUP(D30,勝ち点,2,FALSE)),"",VLOOKUP(D30,勝ち点,2,FALSE))
 
 ここには、勝敗欄に表示される値に応じて、そのチームの勝ち点を計算する式が入力されています。勝敗の結果は「勝」「負」「引分」の3種類なので、それに応じた勝ち点を与えればいいのですが、今回はVLOOKUP関数を用いました。なお、試合がまだ行われていない場合、VLOOKUP関数はエラーを返してしまいます。それを防ぐためにIF関数とISERROR関数を使って、試合が行われていない(VLOOKUP関数の結果がエラー)場合は、セルには何も表示させないようにしています。

2-3 「得失差」欄の計算式
 =C30-J30

 自チームと相手チームの得点(=自チームの失点)の差が得失点差ですので、ここは単純な引き算が入力してあります。

2-4 「作業用」欄の計算式
 =B30&D30

 これは、順位表を作成する際に、そのチームの勝ち数、負け数、引分け数をカウントするために設けた作業用セルです。他のチームの勝ち負けと区別するために、「チーム名」+「勝敗」を表示させるようにしてあります。Excelでは2つのセルの内容をくっつけて表示させるときに演算子「&」を用います。


3.シート「順位表」
 これは、シート「結果」で入力したデータベースを集計するためのシートです。「順位」欄と「チーム名」欄には値が入力されており、それ以外の列はすべて計算式が入力されています。(下図参照)なお、ここではわかりやすくするために、行列番号と枠線を表示させています。(非表示にするには、「オプション」の「詳細設定」で設定を変更します)


c0136904_20223523.jpg




3-1 「勝ち点」欄の計算式
 =SUMIF(結果!$B$3:$B$183,C4,結果!$E$3:$E$183)+SUMIF(結果!$I$3:$I$183,C4,結果!$L$3:$L$183)

 シート「結果」において、1試合ごとに、そのチームの勝ち点を計算してあるので、ここではそれを合計すればよいようになっています。そのために、SUMIF関数を用いるのですが、勝ち点はホームとアウェイの2カ所にあるので、両方を足した結果をここに表示させるようにしています。

3-2 「試合数」欄の計算式
 =COUNTIF(結果!$B$3:$B$183,C4)+COUNTIF(結果!$I$3:$I$183,C4)

 シート「結果」のホームとアウェイの両方に記録されているチーム名を数えて、それを合計しています。

3-3 「勝ち」欄の計算式
 =COUNTIF(結果!$G$3:$G$183,$C4&"勝")+COUNTIF(結果!$N$3:$N$183,$C4&"勝") 

 シート「結果」の作業用セルの中にある「チーム名」+「勝」の個数を、ホームとアウェイで数えて、その合計値を表示させるものです。

3-4 「引分」欄の計算式
 =COUNTIF(結果!$G$3:$G$183,$C4&"引分")+COUNTIF(結果!$N$3:$N$183,$C4&"引分")

 考えかたは、「勝ち」欄の計算式と同じです。

3-5 「負け」欄の計算式
 =COUNTIF(結果!$G$3:$G$183,$C4&"負")+COUNTIF(結果!$N$3:$N$183,$C4&"負")

 これまでと同じなので省略します。

3-6 「得点」欄の計算式
 =SUMIF(結果!$B$3:$B$183,C4,結果!$C$3:$C$183)+SUMIF(結果!$I$3:$I$183,C4,結果!$J$3:$J$183)

 シート「結果」のアームとアウェイに記録されている、そのチームの得点をSUMIF関数を使って合計しています。

3-7 「失点」欄の計算式
 =I4-K4

 「得点」欄と同じ考えかたで計算式を組み立てることもできますが、得点-失点=得失点差ですから、ここでは単純に「得失点差」-「得点」としました。

3-8 「得失差」欄の計算式
 =SUMIF(結果!$B$3:$B$183,C4,結果!$F$3:$F$183)+SUMIF(結果!$I$3:$I$183,C4,結果!$M$3:$M$183)

 考えかたは、「得点」欄の計算式と同じです。


 順位表そのものはこれまでご説明した計算式でつくることができます。今回は、せっかく順位表をつくって、データベースを集計できるようにしたのですから、多少なりとも数値分析もしてみたいと思います。

3-9 「1試合あたり平均得点」欄の計算式
 =I4/E4

 式そのものは非常に単純です。これまでの得点の合計を試合数で割るだけです。こうすることで、そのチームの1試合あたりの得点数(=攻撃力)を明らかにすることができます。なお、この数値は試合数が少ないときは偶然が左右する要素も大きいのですが、試合数が増えるにつれて、次第にそのチームの実力を反映したものになっていきます。

3-10 「順位」欄の計算式
 =RANK(M4,$M$4:$M$21)

 そのチームの1試合あたりの平均得点数が、J1全チームの中でどの辺に位置するのかを計算する式です。順位を計算すればいいのですから、単純にRANK関数を用いています。

3-11 「1試合あたりの失点数」欄の計算式
 =J4/E4

 この値が少なければ少ないほど、守備力が高いことになります。

3-12 「順位」欄の計算式
 =RANK(O4,$O$4:$O$21,1)
 
 先ほどと同じ考えかたですが、RANK関数の引数の最後に「1」と指定している点が先ほどと異なります。RANK関数では、最後の引数は省略可能であり、省略した場合順位を降順(値の大きなものから順番)でつけるのですが、ここに「0」以外の数値を指定すると昇順(値の小さな物から順番)に順位をつけるようになります。「守備力=失点を少なくする力」なので、ここでは昇順で順位付けをするようにしているわけです。

3-13 順位表の更新
 試合が行われるたびに、集計結果である「順位表」の内容は変化していきます。その変化に応じて順位付けをする必要があるわけですが、Jリーグではいかのルールに基づいて順位を決めているようです。

1)勝ち点の多いチームが上位になる
2)勝ち点が同じ場合、得失点差の大きい方が上位になる
3)得失点差も同じ場合、得点数が多い方が上位になる

 以上のルールで並べ替えを行うと下図のようになります。


c0136904_2023534.jpg



 なお、この操作をマクロとして記録し、ボタンに貼り付けたものが「順位表更新」です。試合結果を入力した後、このボタンをクリックすると並べ替えを自動で行ってくれます。


4.データベースの入力上の注意点
 データベースを入力する際に、入力ミスがあると集計結果もおかしなものになります。今回のサンプルでいえば、「鹿島」を「鹿嶋」と入力してしまうと、パソコンは別なデータとして処理するので、集計が間違ったものになります。それを防ぐために、リストを作成して、その中から選択するようにしているわけです。
 なお、ドロップダウンリストは、いちいちマウスを使わなくても、Altキーと↓キーの同時押しで展開されます。
by t_am | 2013-04-07 20:24 | Excel のあの手この手