サッカーのリーグ戦星取表をexcelで作ってます

質問

質問です。
画像のように星取表を作っています。

シートを２つ作成し１つは入力シートもう一つは出力シートとしておきます。

入力シートでは画像のように黄色の範囲に結果を打込むと灰色の範囲に＝で値を入れてあり
勝ち点や得失点の集計はSUMとCOUNTIFを使って入れています。

ここまではできるのですがそれを出力シートに
順位順に並べそのチームを結果も一緒に反映するようにするには
どのような仕組みを作ればできるのでしょうか。
分かる方いらっしゃいましたら教えてください
よろしくお願い致します。

回答No.4

kagakusuki

回答No.3の続きです。

　次に、出力シートのAB3セルに「勝」と入力して下さい。
　次に、出力シートのAB5セルに次の関数を入力して下さい。

=IF(ISNUMBER($AG5),COUNTIFS(入力!$C$5:$Z$5,"勝負",入力!$C6:$Z6,"○")+COUNTIFS(入力!$C$5:$Z$5,"勝負",入力!$C7:$Z7,"○"),"")

　次に、出力シートのAC3セルに「負」と入力して下さい。
　次に、出力シートのAC5セルに次の関数を入力して下さい。

=IF(ISNUMBER($AG5),COUNTIFS(入力!$C$5:$Z$5,"勝負",入力!$C6:$Z6,"●")+COUNTIFS(入力!$C$5:$Z$5,"勝負",入力!$C7:$Z7,"●"),"")

　次に、出力シートのAD3セルに「引分」と入力して下さい。
　次に、出力シートのAD5セルに次の関数を入力して下さい。

=IF(ISNUMBER($AG5),COUNTIFS(入力!$C$5:$Z$5,"勝負",入力!$C6:$Z6,"△")+COUNTIFS(入力!$C$5:$Z$5,"勝負",入力!$C7:$Z7,"△"),"")

　次に、出力シートのAE3セルに「勝点」と入力して下さい。
　次に、出力シートのAE5セルに次の関数を入力して下さい。

=IF(ISNUMBER(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),)),INDEX(入力!$AA:$AD,MATCH(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),),入力!$AL:$AL,0),COLUMNS($AE:AE)),"")

　次に、出力シートのAF3セルに「得失点差」と入力して下さい。
　次に、出力シートのAF5セルに次の関数を入力して下さい。

=IF(ISNUMBER(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),)),INDEX(入力!$AA:$AD,MATCH(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),),入力!$AL:$AL,0),COLUMNS($AE:AF)),"")

　次に、出力シートのAG3セルに「総得点数」と入力して下さい。
　次に、出力シートのAG5セルに次の関数を入力して下さい。

=IF(ISNUMBER(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),)),INDEX(入力!$AA:$AD,MATCH(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),),入力!$AL:$AL,0),COLUMNS($AE:AG)),"")

　次に、出力シートのAH3セルに「総反則点数」と入力して下さい。
　次に、出力シートのAH5セルに次の関数を入力して下さい。

=IF(ISNUMBER(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),)),INDEX(入力!$AA:$AD,MATCH(OFFSET($A$5,FLOOR(ROW()-ROW($A$5),2),),入力!$AL:$AL,0),COLUMNS($AE:AH)),"")

　次に、出力シートのAB列からAH列にかけての各列において、3行目のセルと4行目のセルを結合して下さい。
　次に、出力シートのAB列からAH列にかけての各列において、5行目のセルと6行目のセルを結合して下さい。
　次に、出力シートのA5～AH6のセル範囲セルをコピーして、A7～AH16のセル範囲に貼り付けて下さい。
　次に、出力シートのA3～AH16のセル範囲に、適時罫線を設定して下さい。

　以上で準備は完了で、後は入力シートのA6以下にチーム名を入力し、入力シート上の黄色で塗り潰されているセルの内、「得点」と記されている列の下のセルにはその試合における得点を、「失点」と記されている列の下のセルにはその試合における失点を、「反則」と記されている列の下のセルにはその試合における反則点をそれぞれ入力すると、出力シート上に各チーム名とその試合結果、及び成績が順位順に表示されます。
　尚、もし出力シート上に同じチーム名が重複して表示されていて、別のチーム名の中に表示されないものがある場合、それは成績が同じチームが複数生じているためですので、入力シートのAJ列に「どのチームを高順位とするのが良いか」という抽選結果の点数を入力する事で順位に差を設ける様にして下さい。

　後、因みに入力シートのAE列やAK列及びC4～Z4のセル範囲に表示される「評価点」とは、この回答の方法において順位を自動的に求めるための都合から、途中の計算結果を暫定的に表示しているだけの数値であって、サッカーのルール上の何らかの規定に直接関係する数値ではありません。

回答No.3

kagakusuki

回答No.2の続きです。

　次に、入力シートのAF3セルに「同評価点チーム間の対戦成績」と入力して下さい。
　次に、入力シートのAF4セルに「勝点」と入力して下さい。
　次に、入力シートのAF6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AE6),(COUNTIFS($C$4:$W$4,$AE6,$D6:$X6,"○")+COUNTIFS($C$4:$W$4,$AE6,$D7:$X7,"○"))*3+COUNTIFS($C$4:$W$4,$AE6,$D6:$X6,"△")+COUNTIFS($C$4:$W$4,$AE6,$D7:$X7,"△"),"")

　次に、入力シートのAG4セルに「得失点差」と入力して下さい。
　次に、入力シートのAG6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AE6),$AH6-SUMIF($C$4:$W$4,$AE6,$E6:$Y6)-SUMIF($C$4:$W$4,$AE6,$E7:$Y7),"")

　次に、入力シートのAH4セルに「得点数」と入力して下さい。
　次に、入力シートのAH6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AE6),SUMIF($C$4:$W$4,$AE6,$C6:$W6)+SUMIF($C$4:$W$4,$AE6,$C7:$W7),"")

　次に、入力シートのAI4セルに「反則点数」と入力して下さい。
　次に、入力シートのAI6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AE6),SUMIF($C$4:$W$4,$AE6,$F6:$Z6)+SUMIF($C$4:$W$4,$AE6,$F7:$Z7),"")

　次に、入力シートのAJ3セルに「抽選票数」と入力して下さい。
　次に、入力シートのAK3セルに「最終評価点」と入力して下さい。
　次に、入力シートのAK6セルに次の関数を入力して下さい。

=IF(COUNT($AE$6:$AI$6,$Y$1)=6,(RANK($AE6,$AE:$AE,1)-1)*$Y$1^5+(RANK($AF6,$AF:$AF,1)-1)*$Y$1^4+(RANK($AG6,$AG:$AG,1)-1)*$Y$1^3+(RANK($AH6,$AH:$AH,1)-1)*$Y$1^2+(RANK($AI6,$AI:$AI)-1)*$Y$1+IF(ISNUMBER($AJ6),RANK($AJ6,$AJ:$AJ,1)-1,0),"")

　次に、入力シートのAL3セルに「順位」と入力して下さい。
　次に、入力シートのAL6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AK6),RANK($AK6,$AK:$AK),"")

　次に、入力シートのAA列からAE列にかけての各列において、3行目～5行目の縦１列のセルを結合して下さい。
　次に、入力シートのAF1～AI1のセル範囲内のセルを結合して下さい。
　次に、入力シートのAF列からAI列にかけての各列において、4行目のセルと5行目のセルを結合して下さい。
　次に、入力シートのAJ列からAL列にかけての各列において、3行目～5行目の縦１列のセルを結合して下さい。
　次に、入力シートのAA列からAL列にかけての各列において、6行目のセルと7行目のセルを結合して下さい。
　次に、入力シートのA6～AL7のセル範囲をコピーして、A8～AL17のセル範囲に貼り付けて下さい。
　次に、以下の操作を行って条件付き書式を設定して下さい。

入力シートのC6セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=INT((ROW()-ROW($C$6))/2)<INT((COLUMN()-COLUMN($C$6))/4)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=INT((ROW()-ROW($C$6))/2)>INT((COLUMN()-COLUMN($C$6))/4)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある灰色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄を2箇所とも

=$C$6:$Z$17

に変更（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、出力シートのA3セルに「順位」と入力して下さい。
　次に、出力シートのA5セルに次の関数を入力して下さい。

=IF(ROUNDUP(ROWS(A$5:A5)/2,0)>COUNT(入力!$AL:$AL),"",ROUNDUP(ROWS(A$5:A5)/2,0))

　次に、出力シートのB5セルに次の関数を入力して下さい。

=IF(ISNUMBER($A5),INDEX(入力!$A:$A,MATCH($A5,入力!$AL:$AL,0))&"","")

　次に、出力シートのC5セルに「前半」、C6セルに「後半」と入力して下さい。
　次に、出力シートのD3セルに次の関数を入力して下さい。

=OFFSET($B$5,INT((COLUMN()-COLUMN($D$3))/4)*2,)&""

　次に、出力シートのD4セルに「得点」、出力シートのE4セルに「勝負」、出力シートのF4セルに「失点」、出力シートのG4セルに「反則」と入力して下さい。
　次に、出力シートのD5セルに次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(入力!$C$6:$Z$16,MATCH(OFFSET($B$5,FLOOR(ROW()-ROW($D$5),2),),入力!$A$6:$A$16,0)+MOD(ROW()-ROW($D$5),2),MATCH(OFFSET($D$3,,FLOOR(COLUMN()-COLUMN($D$5),4)),入力!$C$3:$Z$3,0)+MOD(COLUMN()-COLUMN($D$5),4))<>"")),"",INDEX(入力!$C$6:$Z$16,MATCH(OFFSET($B$5,FLOOR(ROW()-ROW($D$5),2),),入力!$A$6:$A$16,0)+MOD(ROW()-ROW($D$5),2),MATCH(OFFSET($D$3,,FLOOR(COLUMN()-COLUMN($D$5),4)),入力!$C$3:$Z$3,0)+MOD(COLUMN()-COLUMN($D$5),4)))

　次に、出力シートのD5セルをコピーして、D5～G6のセル範囲に貼り付けて下さい。
　次に、出力シートのA3セルとA4セルを結合して下さい。
　次に、出力シートのA5セルとA6セルを結合して下さい。
　次に、出力シートのB5セルとB6セルを結合して下さい。
　次に、出力シートのD3～G3のセル範囲内にあるセルを結合して下さい。
　次に、出力シートのD3～G6のセル範囲セルをコピーして、H3～AA6のセル範囲に貼り付けて下さい。

※ まだ途中なのですが、このサイトの回答欄の入力可能文字数である4000文字をそろそろ超えそうですので、残りは又後で投稿致します。

回答No.2

kagakusuki

確認したいのですが質問者様が仰る「順位」は、一体どの様な優先順位に従って決める事を想定しておられるのでしょうか？
　単純に勝った回数が多い順で決める方法もあれば、全ての試合で得た得点の総計順で決める方法もあるかと思います。
　どういった方法で順位を決めるのか御質問文には説明がない様ですが、2015年の明治安田生命Ｊ１リーグ及び同J2リーグにおける順位の決め方(２０１５明治安田生命Ｊ１・Ｊ２リーグ戦試合実施要項　第19 条〔Ｊ１第１ステージ、第２ステージの順位決定〕)に準じた方法で宜しいでしょうか？

【参考URL】
　２０１５明治安田生命Ｊ１・Ｊ２リーグ戦試合実施要項
　　http://www.jleague.jp/docs/aboutj/regulation/15.pdf
　　　※第19 条〔Ｊ１第１ステージ、第２ステージの順位決定〕

　それと、

>出力シートに順位順に並べそのチームを結果も一緒に反映するようにする

と言う説明だけでは、チーム名を順位順に縦一列に並べて、その右側にあるセルに結果を表示させれば良いのか、それともチーム名を横一列に並べて、その下のセルに結果を表示させれば良いのかも不明ですし、結果というだけではどの様なデータをどの様な順番で並べれば良いのかも解りません。
　その上、入力シートの画像も文字が潰れてしまっていて、どんなデータが入力されているのか殆ど判りません。
　入力シートにどのようにデータが入力されているのか不明ですし、出力シートのレイアウトや表示しなければならない結果とはどの様なデータの事なのかも不明ですので、

>入力シートでは画像のように黄色の範囲に結果を打込むと灰色の範囲に＝で値を入れてあり

という点だけは残した上で、入力シートや出力シートのレイアウトや表示する結果に関しては、こちらで適当に決めてしまっても宜しいでしょうか？
　それで宜しければ以下の様な方法となります。
　尚、質問者様御自身が添付された画像を御覧になればお解りの様に、チーム名があまり多過ぎては添付画像の文字が潰れてしまって読めなくなりますので、取りあえず仮の話として全6チームの場合における例を御伝え致しますので、もしチーム数をもっと増やされる場合には、下記の各関数を実際のチーム数に合わせて修正して下さい。
　まず、入力シートのB6セルに「前半」、B7セルに「後半」と入力して下さい。
　次に、入力シートのA6～B7のセル範囲に対して内側には細い罫線が格子状にひかれていて、外枠が太い罫線となっている様に、罫線を設定して下さい。
　次に、入力シートのA6セルとA7セルを結合して下さい。
　次に、入力シートのC5セルに「得点」、入力シートのD5セルに「勝負」、入力シートのE5セルに「失点」、入力シートのF5セルに「反則」と入力して下さい。
　次に、入力シートのC3セルに次の関数を入力して下さい。

=IFERROR(INDEX($A:$A,ROW($A$6)+INT((COLUMN()-COLUMN($C3))/4)*2)&"","")

　次に、入力シートのC4セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX($AE:$AE,MATCH(C$3,$A:$A,0))),INDEX($AE:$AE,MATCH(C$3,$A:$A,0)),"")

　次に、入力シートのC3～F5のセル範囲に対して内側には細い罫線が格子状に引かれていて、外枠が太い罫線となっている様に、罫線を設定して下さい。
　次に、入力シートのC3～F3のセル範囲内にあるセルを結合して下さい。
　次に、入力シートのC4～F4のセル範囲内にあるセルを結合して下さい。
　次に、入力シートのC6セルに次の関数を入力して下さい。

=IF(INT((ROW()-ROW($C$6))/2)>INT((COLUMN()-COLUMN($C$6))/4),IF(OFFSET($C$6,INT((COLUMN()-COLUMN($C$6))/4)*2+MOD(ROW()-ROW($C$6),2),INT((ROW()-ROW($C$6))/2)*4+2)="","",OFFSET($C$6,INT((COLUMN()-COLUMN($C$6))/4)*2+MOD(ROW()-ROW($C$6),2),INT((ROW()-ROW($C$6))/2)*4+2)),"")

　次に、入力シートのD6セルに次の関数を入力して下さい。

=IF(COUNT(C8,E8)=2,CHOOSE(SIGN(C8-E8)+2,"●","△","○"),"")

　次に、入力シートのE6セルに次の関数を入力して下さい。

=IF(INT((ROW()-ROW($C$6))/2)>INT((COLUMN()-COLUMN($C$6))/4),IF(OFFSET($C$6,INT((COLUMN()-COLUMN($C$6))/4)*2+MOD(ROW()-ROW($C$6),2),INT((ROW()-ROW($C$6))/2)*4)="","",OFFSET($C$6,INT((COLUMN()-COLUMN($C$6))/4)*2+MOD(ROW()-ROW($C$6),2),INT((ROW()-ROW($C$6))/2)*4)),"")

　次に、入力シートのF6セルに次の関数を入力して下さい。

=IF(INT((ROW()-ROW($C$6))/2)>INT((COLUMN()-COLUMN($C$6))/4),IF(OFFSET($C$6,INT((COLUMN()-COLUMN($C$6))/4)*2+MOD(ROW()-ROW($C$6),2),INT((ROW()-ROW($C$6))/2)*4+3)="","",OFFSET($C$6,INT((COLUMN()-COLUMN($C$6))/4)*2+MOD(ROW()-ROW($C$6),2),INT((ROW()-ROW($C$6))/2)*4+3)),"")

　次に、入力シートのC6～F6のセル範囲をコピーして、C7～F7のセル範囲に貼り付けて下さい。
　次に、入力シートのE6～E7のセル範囲とF6～F7のセル範囲との間に細い罫線を引いて下さい。
　次に、入力シートのC6～F7のセル範囲を太い罫線の外枠で囲って下さい。
　次に、入力シートのC3～F7のセル範囲をコピーして、G3～Z7のセル範囲に貼り付けて下さい。

　次に、入力シートのX1セルに「チーム数」と入力して下さい。
　次に、入力シートのY1セルに次の関数を入力して下さい。

=COUNTIF($A:$A,"*?")-COUNTIF($A$1:$A$5,"*?")

　次に、入力シートのAA3セルに「勝点」と入力して下さい。
　次に、入力シートのAA6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AC6),(COUNTIFS($C$5:$Z$5,"勝負",$C6:$Z6,"○")+COUNTIFS($C$5:$Z$5,"勝負",$C7:$Z7,"○"))*3+COUNTIFS($C$5:$Z$5,"勝負",$C6:$Z6,"△")+COUNTIFS($C$5:$Z$5,"勝負",$C7:$Z7,"△"),"")

　次に、入力シートのAB3セルに「得失点差」と入力して下さい。
　次に、入力シートのAB6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AC6),SUMIF($C$5:$Z$5,"得点",$C6:$Z6)+SUMIF($C$5:$Z$5,"得点",$C7:$Z7)-SUMIF($C$5:$Z$5,"失点",$C6:$Z6)-SUMIF($C$5:$Z$5,"失点",$C7:$Z7),"")

　次に、入力シートのAC3セルに「総得点数」と入力して下さい。
　次に、入力シートのAC6セルに次の関数を入力して下さい。

=IF(COUNT($C6:$Z7),SUMIF($C$5:$Z$5,"得点",$C6:$Z6)+SUMIF($C$5:$Z$5,"得点",$C7:$Z7),"")

　次に、入力シートのAD3セルに「総反則点数」と入力して下さい。
　次に、入力シートのAD6セルに次の関数を入力して下さい。

=IF(ISNUMBER($AC6),SUMIF($C$5:$Z$5,"反則",$C6:$Z6)+SUMIF($C$5:$Z$5,"反則",$C7:$Z7),"")

　次に、入力シートのAE3セルに「各総点数を基準とした評価点」と入力して下さい。
　次に、入力シートのAE6セルに次の関数を入力して下さい。

=IF(COUNT($AA6:$AD6,$Y$1)=5,(RANK($AA6,$AA:$AA,1)-1)*$Y$1^3+(RANK($AB6,$AB:$AB,1)-1)*$Y$1^2+(RANK($AC6,$AC:$AC,1)-1)*$Y$1+(RANK($AD6,$AD:$AD)-1),"")

※ まだ途中なのですが、このサイトの回答欄の入力可能文字数である4000文字をそろそろ超えそうですので、残りは又後で投稿致します。

回答No.1

aokii

ピボットテーブルを使ってみてください。