個人成績一覧表をつくろう
前回までで、ボウリングリーグ戦の個人成績に関する計算式はひと通り、出来るようになったと思いますが、今度は1枚の紙にまとめて表示する成績表を作りましょう。
成績表で自動的に計算してほしい事は、全部エクセルにやってもらえば楽ちんですよね。
実際は印刷したい枚数を指定して印刷するだけなんです。
エクセルにやってもらいたいこと??
どこまでできるんでしょうね、自動で?
成績一覧表は全自動で完成させる
とにかく一番面倒な作業の成績一覧表は、作業がたくさんあり間違えの原因にもなりますので、とにかく人の手を入れないで、エクセル自体に全ての計算などをやってもらえば、一瞬で間違えの無い成績表が出来上がりますよ。
では、エクセルにやってもらいたい作業とは何でしょうか?
- 参加者全員分の必要な数字を自動的に一覧表に反映させる
- 今週の成績表を自動的に選んで表示させる
- 未登録選手の行は必要ないので非表示にする
- A4の紙いっぱいにするために人数に応じて行幅や文字の大きさを調整する
- 自動的に順位順に並べ替えをする
- HGやHSなどのトップのスコアーを見つけ出す
- 見つけたスコアーを目立つようにする
- 見つけたスコアーの選手名を探し出して表示する
- 男女別に振り分けて表示項目を作る
なんとなく書き出すとこんな感じで、この作業が一瞬でできたら楽ですよね。
ムリ言うな!
手品じゃあるまいし、コツコツやるのがいいんじゃない?
時間の無駄です。
成績一覧表のシートを開いた瞬間に!
「ハイ!完成!」するんです。
ファンタスティック!
今回のバージョンは8と9はありませんので、1~7の項目をお話しします!
エクセル関数とエクセルVBAですよ!
表の中から必要な値を取り出すDGET関数
先ほどの成績表の画像の名前以外の各セル(E列~Q列)にはすべて同じ、DGET関数を使っています。
これにより、上の図の各個人のスコアーを記録している表の中から、指定した条件(成績表に表示したい項目)、例えば勝数や負数から始まり、次回HDCPまでの値を取り出して表示させます。
最初の画像の成績表では、表示したい項目が「勝」~「次回HD」まで13項目あり、参加者が10人ならば、DGET関数を使っているセルの数は130か所になり、20人ならば260か所とどんどん増えます。
まさか、260か所全部にDGET関数の数式を入れるって事?
地味な作業で、1か所にはこんな計算式を入れていますよ。
=IFERROR(DGET(HOME!$BA$303:$BZ$327,HOME!$BV$303,HOME!$Y$2:$Y$3),””)
なんかの暗号ですか?よくわからないけど、コピーしちゃえばあっという間じゃない!
残念でした!
選手ごとに表が違う点と、指定した条件が違うのでコピーしても手直しなくちゃ使い物になりません。
あとはエラー表示を0と表示するようにIFERRORで囲っています。
根気と集中力と睡魔との闘いですなあ、この作業・・・
で、これが完成するとどうなるの?
DGET関数で成績表を全て埋め尽くすと、1週目を印刷したい時は、特定のセルの値を「1」と入力すると、成績一覧表の数字が全部1週目になります。
5週目ならば「5」とすると、5週目の成績に早変わりします。
もちろん第5週で日付も変わりますよ!
便利だー!
ピンすけ使っているエクセルは、1週ごとにシートがあってシートを選んで印刷しているけど、20週ぐらいになるとシート探すのが面倒。
私のところは、選手がシートになっているから参加人数多くなると選手を探すのが面倒
これ、簡単でしょ!数式書くのが最初面倒だけどね。
下の図で言うとセル(Y7)が2になっているので週目が印刷されます。
そうそう、DGET関数の詳細の説明についてはここでは省略します。
未登録選手の行は必要ないので非表示にする
ボウリングのリーグ成績表をエクセルで作る時に、例えば最大24名とした場合にいつも必ず24名の参加者がいればいいのですが、そうではない場合もありますよね。
20人しかいない時は、当たり前ですが4人分空欄になってしまいますので、出来ればこの4人分を非表示にして全体を大きく表示できると見やすくなりますよね。
もちろん行の非表示をして、行の幅を広くして、文字サイズを大きくしてって毎回手作業で行う事も可能ですが、面倒なのでボタンワンクリックで一発で調整できるようにしましょう。
では準備として、成績表一覧シートはQ列までしか使っていないので余白のZ列に数式をセットしておきます。
これは、C列には選手登録時の性別が入力されていて、「0」は未登録の場合、「5」はおばけこの時は「0」にして、男性・女性・従業員・プロは「1」と計算させます。
そうすることにより、「0」は表示の必要のない行で、「1」だけを読み取って表示するようにします。
さらに、「1」となった行の数を合計する為のセルをAA1に計算結果を表示するようにすると、必要な表示人数が出ますよね。
エクセルにそんな機能あったっけ?
リボンバーの中のどこにそんな機能あるの?
「開発」っていうメニューだよ!
表示が無ければ、エクセルのオプションからリボンのユーザー設定です。
あとは、下のシート上で右クリックしてコードの表示だね。
何ですか?この画面?
エクセルですか?本当に?
エクセルVBAの画面ですよ。
コマンドボタンにこのコードを書き込むとボタンを押した時に自動的に必要のない行は非表示して文字サイズ変更してくれるので便利ですね。
ActiveSheet.Unprotect
Select Case Worksheets(“RESULT”).Range(“AA1”).Value
Case Is <= 10
Range(“3:27”).RowHeight = 44
Range(“A3:T42”).Font.Size = 17
Case Is = 11
Range(“3:28”).RowHeight = 40
Range(“A3:T42”).Font.Size = 16
Case Is = 12
Range(“3:29”).RowHeight = 40
Range(“A3:T42”).Font.Size = 16
Case Is = 13
Range(“3:30”).RowHeight = 36
Range(“A3:T42”).Font.Size = 16
Case Is = 14
Range(“3:31”).RowHeight = 34
Range(“A3:T42”).Font.Size = 15
Case Is = 15
Range(“3:32”).RowHeight = 33
Range(“A3:T42”).Font.Size = 15
Case Is = 16
Range(“3:33”).RowHeight = 29
Range(“A3:T42”).Font.Size = 15
Case Is = 17
Range(“3:34”).RowHeight = 29
Range(“A3:T42”).Font.Size = 15
Case Is = 18
Range(“3:35”).RowHeight = 27
Range(“A3:T42”).Font.Size = 15
Case Is = 19
Range(“3:36”).RowHeight = 27
Range(“A3:T42”).Font.Size = 14
Case Is = 20
Range(“3:37”).RowHeight = 23
Range(“A3:T42”).Font.Size = 14
Case Is = 21
Range(“3:38”).RowHeight = 23
Range(“A3:T42”).Font.Size = 14
Case Is >= 22
Range(“3:39”).RowHeight = 22
Range(“A3:T42”).Font.Size = 14
Case Is = 23
Range(“3:40”).RowHeight = 20
Range(“A3:T42”).Font.Size = 14
Case Is = 24
Range(“3:40”).RowHeight = 20
Range(“A3:T42”).Font.Size = 14
End Select
For i = 3 To 26
If Cells(i, 26).Value = 0 Then
Rows(i).Hidden = True
End If
Next i
ActiveSheet.Protect
End Sub
さっぱりわからん?呪文ですか?
説明が足りないですよ!
このエクセルVBAコードを最初から説明すると長くなりすぎるので、ここでは省略します!
自動的に順位順に並べ替えをする
成績一覧表なので、ポイント順に並べ替えを行ってから印刷してリーグ参加者の皆さんへ配布しますよね。
その時に毎回「データ」➡「並べ替え」➡「最優先されるキー」を選んで、追加して「次に優先されるキー」とか毎回やるのって面倒ですよね。
当たり前の作業じゃないの?
まさか並べ替えせずに配るって事?
そうじゃなくて、シートを移動した瞬間に自動的に並べ替えをしてから成績表シートを表示するようにセットしておけばいいんですよ。
Application.ScreenUpdating = False
ActiveSheet.Protect
Sheets(“RESULT”).Select
ActiveSheet.Unprotect
Range(“B3:Q26”).Sort Key1:=Range(“E2”), Order1:=xlDescending, KEY2:=Range(“K2”), ORDER2:=xlDescending, KEY3:=Range(“I2”), ORDER3:=xlDescending
ActiveSheet.Protect
Application.ScreenUpdating = True
Unload UserForm5
UserForm7.Show
End Sub
またきたぞ呪文が!
このエクセルVBAコードでさっきの作業をやってくれるんですよ。
ちなみに最優先はポイント順、2番目が累計/Hで、3番目が累計で、数字が大きい順に並べ替えられるようになっています。
HGやHSなどのトップのスコアーを見つけて目立つように色を変える
並べ替えも自動で表示したら、ついでにHGやHSなどのトップスコアーも自動的に探して欲しいですよね。
そして、見つけ出したら目立つように背景色や文字色を変えて表示してくれたら便利なので、これもエクセルに自動的にやってもらいましょう。
そんなに楽できるの?
HGとか探すのすごい面倒なんだよね、見逃すこともあるしね。
ここは、条件付き書式設定を使いましょう。
「ホーム」➡「条件付き書式」➡「新規ルール」➡「上位または下位に入る・・・」で、上位を1にして文字色などを指定してあげれば完成です。
次はレコードシートも自動化させるための設定をつくろう
レコードシートについて
作成中
エクセルで作るリーグスタンディングへ戻って続きを見ましょうね
スポンサーリンク