東京都内の3つの地区を中心とした平成29年度の行事予定をまとめました。
情報源によって日程や回数などに違いがあったため、
正しそうなを方のデータを採用していますが、
間違いの可能性もあるので、事前に正しい日程を確認してください。
東京都内の3つの地区を中心とした平成29年度の行事予定をまとめました。
情報源によって日程や回数などに違いがあったため、
正しそうなを方のデータを採用していますが、
間違いの可能性もあるので、事前に正しい日程を確認してください。
「射会の成績をExcelで自動集計(その2)」では決勝射詰めがある場合や予選と決勝に分かれる場合へ対応しました。しかし、参加者全体で順位を競う射会だけでなく、たとえば有段者の部と称号者の部という具合に複数の部門に分かれる射会も多いと思います。
今回は射会が複数の部門に分かれる場合に対応していきましょう。
(1)7部門対応版(基本バージョン)
複数の部門といってもいくつまで対応すれば良いか迷うところです。私が知っている射会の中では、7つの部門に分かれている射会があるため、これに対応できればほとんどの射会に対応できるかと思います。
そのためには、部門ごとに成績表と順位表のシートの対を設けるのが一番簡単ですが、その場合はシートの数が最低でも14シートになってしまい、使い勝手が非常に悪くなってしまいます。
そこで、決勝に対応する順位表は各部門15位までに限定し、それを一つのシートにまとめてシートの数を減らしました。また、7つある成績表の各シート(「成績表(1)」~「成績表(7)」)を区別しやすくするため、シートの右上に「第1部」~「第7部」という文字を赤で大きく表示するようにしました。この部分は有段者の部とか称号者の部というように、射会の内容に合わせて自由に変更してください。
また、成績表の各シート(「成績表(1)」~「成績表(7)」)の上部に順位表を設けているので、ここに決勝の結果を入力することが可能ですが、「決勝(射詰め)」シートに入力した場合の結果とは関連性がないため、どちらか一方を使用することにしてください。
なお、多くの射会では部門が分かれていても2、3部門程度までだと思います。そのため、多い分は削除して使用していただくか、この後にご紹介するサンプルファイルを使用してください。
7つの部門にまで対応可能なサンプルファイルは「shakaishukei3-7.xls」です。
(2)5部門対応版(基本バージョンに条件設定シートを追加)
実用的には5つの部門に対応できれば十分だと思われるので、予選のシートを減らして「設定条件」シートを追加しました。 あらかじめ予選通過条件を設定しておけば各参加者が予選に通過したかどうかが表示できるようになっています。
また、部門の名称を入力すれば、それが「決勝(射詰め)」シートの各表の左上と、各成績表(「成績表(1)」~「成績表(5)」)の右上に大きく赤地に白い文字で表示されます。
サンプルファイルは「shakaishukei3-5b.xls」です。
(3)3部門対応版(大人数の射会に対応)
【主な変更点】
①3つの部門それぞれに「成績表」シートと「決勝(射詰め)」シートを用意しました。
②成績表に上にあった順位表を削除しました。
③各「成績表」シートは200人まで対応できるようにしました。
④「決勝(射詰め)」シートは成績上位50位まで表示するようにしました。
なお、3部門以上になる場合は、対応する「成績表」シートと「決勝(射詰め)」シートをコピーして名前を変更して使用してください。 なお、上の図の右上にある部門の名称を表示する部分(赤い四角に白字で第2部と表示された部分)は、「設定条件」シートに欄を追加し、その内容を参照するようにしてください。
サンプルファイルは「shakaishukei3-3.xls」です。
(4)7部門対応版(部門ごとに予選の立ちを分けない場合へ対応)
たとえば、称号者の部と有段者の部に分かれていてこの順で予選を行う射会の場合、称号者の部と有段者の部の予選を全く別々に実施する場合もありますが、先に実施する称号者の部の最後の立ちで有段者の部の先頭の参加者を同じ立ちに加える場合があります。
今までの成績表では別の部門の参加者が同じ立ちになると、シートが別々のため結果の入力が面倒になり、使いづらくなってしまいます。
これに対応するために、予選の成績表を一つのシートにまとめて、部門が変わっても続けて入力できるようにしました。サンプルファイルでは7部門まで対応可能で、1~7の数字をその部門の先頭の参加者の位置に入力するだけで部門を分けて順位を計算できるようにしました。
そのために成績表シートの右上に各部門のデータ範囲を示す一覧表を設け、これを参照して各部門の順位を計算するようにしました。なお、このサンプルファイルのやり方よりスマートな方法があると思いますが、とりあえずこのサンプルファイルを暫定的に公開しています。
サンプルファイルは「shakaishukei3-1a4.xls」です。
1.サンプルファイルの入手方法
次のリンクから無料の電子ブックをダウンロードして下さい。
射会の成績をExcelで自動集計(その3) DLmarket版
サンプルファイルは次の4種類がダウンロードできます。
(1)7部門対応版(基本バージョン)
(2)5部門対応版(基本バージョンに条件設定シートを追加)
(3)3部門対応版(大人数の射会に対応)
(4)7部門対応版(部門ごとに予選の立ちを分けない場合へ対応)
2.サンプルファイルの使用方法
ここでは、いくつかのサンプルファイルのうち、4つ目の「shakaishukei3-1a4.xls」について説明しますが、他のサンプルファイルでも各部門に分ける部分以外は同じだと思います。
(1)サンプルデータ削除(最初に実施)
サンプルファイルには動作を確認するためのサンプルデータが入力されているので、最初にサンプルデータを削除してから使用してください。その際は水色の部分だけを削除して、それ以外の部分は基本的にはいじらないでください。なお、数式が入っているセルは基本的には変更禁止ですが、水色の部分にある氏名の下は、氏名を入力しなくても使えるように数式が入力されているだけなので、削除は可能です。
(3)条件の設定
条件設定シートのB列が条件を入力する場所です。 ここでは、4つのサンプルファイルのうち、「shakaishukei3-1a4.xls」について説明します。
▼予選通過条件
ここに予選通過条件(的中数か成績上位か)、その場合の的中数と何位以内かを入力しておきます。
▼最終順位決定方法
決勝のみの結果で最終順位を決定するか、予選と決勝の合計で成績を決定するかを選択します。
▼決勝の立順
決勝の立順を予選の成績上位順にするか、予選の立順に準拠した順序にするかを設定します。予選の立順に準拠した順序とは、予選の立順を基本としてそこから予選通過者だけ抜き出したリストになります。
(4)部門の名称設定
条件設定シートのE列にある赤いセルの中身が各部門の名称になります。よく使う部門の名称が複数ある場合には上の図のように右隣のF列やG列にあらかじめ入力しておくと便利です。変更する場合にはそれをコピーして赤いセルの部分に文字だけを貼り付けます。
(5)各部門の区切りの設定
成績表シートのC列のセルに、各部門を区別する1~7の数字を入力します。その場合、全参加者に入力するのは大変なので、各部門の先頭の参加者の位置だけに1~7の数字を入力します(右の図では「2」の下のセルです)。
先頭の参加者に数字を入力すると、次の数字までの参加者が自動で同じ数字がA列とX列に入り、Z列とAA列の緑色の表に各部門の範囲のリストが表示されます。
なお、成績表シートの左下に1~8の数字が入力されたセルがありますが、これはC列に数字が入力されていない場合にエラーが出ないようにするためのものなので、削除や変更はしないでください。
3.人数と矢数の変更について
Excelの操作方法(特に関数)に詳しくない方はなるべく変更せずに使用してください。そのままでも入力できる矢数は予選・決勝ともに10射までで、予選参加人数は「shakaishukei3-1a3.xls」の場合で200人までに対応できますが、どうしても減らしたり増やしたりしたい場合は、サンプルデータを削除する前に変更してエラーが出ないことを確認してください。
いかがでしたか?
(その4)では、的中制と点数制の両方に対応する場合について解説していく予定です。 ご期待ください。
「射会の成績をExcelで自動集計(その1)」ではExcelの関数を利用して的中数と順位をリアルタイムで自動集計する仕組みについて解説しました。しかし、これまでの内容では成績表と順位表の基本的な内容を実現しただけで、実際の射会で使用するとなるといくつかの問題点が出てきます。
例えば、射会が予選と決勝に分かれているとか、射詰めを行うこともあると思います。さらに、予選と決勝に分かれている射会で決勝進出条件を的中数で定めている場合があったり順位であったりということもあります。
「その1」で示したサンプルファイルでこれに対応することは不可能ではありませんが、そのままでは使いやすいとは言えません。
そこで、(その2)では決勝射詰めと予選・決勝に分かれる場合について対応していこうと思います。
まず、決勝の立ち順が「予選の成績順」になるか「予選の立ち順を基にした順序」になるかで事情が大きく異なります。「予選の成績順」であれば(その1)のサンプルファイルを少し変更するだけで対応できますが、「予選の立ち順を基にした順序」つまり、予選の立ち順から通過しなかった人を除外した立ち順になる場合は、決勝の表の並びを大幅に変更しなければならないため、使用するサンプルファイルは別々になってしまいます。
(1)決勝の立ち順が「予選の成績順」の場合
(その1)までの内容では自動集計と表示の仕組みを知っていただくため、表をシンプルに構成したことにより、簡素な射会にしか対応できないものでした。そこで、決勝射詰めや予選と決勝に分かれる場合に対応するために決勝(射詰め)の成績を入力する欄を順位表に追加しました。
それによって、決勝進出条件が的中数でも順位でも順位表の上から順に決勝の結果を入力することができるようになりました。
▼(その1)からの主な変更点
順位表の右側に成績表の的中状況(○×)入力欄を追加したことで決勝射詰めに対応するようにしました。順位の決定は順位表の右側の表の外に追加したU列の数字(スコア)で判定するようしました。この数字は予選の的中数の100倍と決勝の的中数をそのまま足し、さらに立ち順を考慮した数字を小数点以下で表現したものを「スコア」としてU列に表示するようにし、この数字の大小から順位を求めています。
なお、立ち順の早い人を有利にしたくない場合はV列のスコア(小数点以下の数字のないもの)とこれを使用した順位(X列)を適用します。
このどちらかをそのまま順位表の(青い部分の)右端にあるS列に表示すれば良いので、S列はW列かX列の数字がそのまま表示されます。どちらを使用するかが射会によって異なるため、使用する場合はどちらになっているかを確認し、違っていれば変更してください。
サンプルファイルでは最初の状態でX列の順位が表示されるようになっており、決勝の的中数が同じ場合は同じ順位になるようにしています。
サンプルファイルは「shakaishukei2a.xls」です。
(2)決勝の立ち順が「予選の立ち順を基にした順序」の場合
この場合、決勝の表の並びを「予選の成績順」から予選の立ち順から通過しなかった人を除外した立ち順(「予選の立ち順に準拠した順序」)に変更しなければなりません。 そのための変更点は次の通りです。
①予選通過条件入力欄を追加
新たに予選通過者かどうかの判定をする必要があるので、予選通過条件を入力する欄を新しいシートに追加しました。「設定条件」という名前のシートの左上の予選通過条件という文字の下(オレンジ色のセル)に予選通過条件の的中数を入力してください。
②最終順位決定方法設定欄を追加
「設定条件」シートの「予選通過条件」設定欄の下に最終順位決定方法設定欄を設けました。これは最終順位を決定する際に予選の的中数を含めたすべての的中数で順位を決定するか、決勝のみの成績で決定するかを選ぶものです。決勝の成績のみの場合は0を、予選の成績も含める場合は1を、A5セル(オレンジ色のセル)に入力してください。
③成績表(予選)の変更
成績表では青い表の右端(P列)を「順位表示」から「予選通過表示」に変えています。
予選を通過したかどうかの判定は、X列に通過スコアを表示するようにしました。
通過スコアの関数は少々複雑になっていますが、予選通過条件以上の的中数だった場合に100、通過できない的中数では0になるようにしています。これに立ち順を考慮した数を小数点以下の数字で表現し、足したものが通過スコアです。
この部分はIF文でも実現できますが。IF文ばかりを使用するのも脳がないので、あえて関数で表現するようにしました。この通過スコアの点数が高い順に順位表示したものが隣のU列です。この部分は順位表の黄色い部分の表示に必要なのでいじらないで下さい。
なお、「予選通過表示」は予選通過条件が的中数で設定された場合に有効で、順位で設定した場合は無視してください。
④決勝(射詰)の成績表変更
表を見てもそれほど変わっていないように見えますが、実際は中身を大きく変更しています。
つまり、今までは予選の成績順に上から表示していたものから、予選通過者を予選の立ち順にしたがって表示するようにしたことです。これは決勝の立ち順が予選の立ち順をもとに決められるからで、それに対応する変更となっています。
また、成績表の黄色い部分の左端にあった順位表示を予選に通過したかどうかの表示に変えました。なお、本来なら順位表には予選通過者だけを表示する方が良いかも知れませんが、途中経過を表示している間はまだ予選通過していない人の成績も見られる方が良いかと思い、このような表示にしています。
そのため、予選通過者かどうかを見やすくするためにB列とT列の表示を追加しました。さらに、通過者はB列に赤字で「通過」の文字を、T列に赤い帯を表示するようにしました。
なお、R列の的中数は最終順位に予選順位を反映させるかどうかで異なる的中数が自動で表示されるようにしました。
サンプルファイルは「shakaishukei2b.xls」です。
ということで、本来なら(その2)はここで終わりの予定だったのですが、サンプルファイルが別々では使いにくく、一つで両方を兼ねられないかという要望があったため、頑張って作ってみました。
(3)決勝の立ち順が「予選の成績順」と「予選の立ち順を基にした(準拠した)順序」の両方に対応
説明が長くなるので、ここでは数式の内容説明にとどめます。
【数式例】 「成績表(予選)」の1番目(1番上)の人の的中数から「スコア」を計算する数式
=SIGN(INT(O17/設定条件!$B$5))*NOT(設定条件!$B$15)*(SIGN(設定条件!$B$7-P17+0.5)+1)/2
+INT(O17)*設定条件!$B$15+(1000-$A17)/10000
【使用する値】
O17 「成績表(予選)」の1番目(1番上)の人の的中数
P17 「成績表(予選)」の1番目(1番上)の人の順位
【設定値】
B3 予選通過条件(「0:的中数」か「1:成績上位」かの選択 ) 未使用
B5($B$5) 予選通過の的中数を設定
B7($B$7) 上位何位以内で予選通過するかを設定
B15($B$15) 決勝の立順(「0:予選の立順に準拠」か「1:予選の成績順」かの選択 )
【数式の意味】
A:SIGN(INT(O17/設定条件!$B$5)) →的中数が予選通過条件以上の場合は1、そうでない場合は0
B:NOT(設定条件!$B$15) →決勝の立順設定値の「0」と「1」を切り替える
C:(SIGN(設定条件!$B$7-P17+0.5)+1)/2 →予選通過順位内に入った場合は1、そうでない場合は0
D:INT(O17)*設定条件!$B$15 →決勝の立順設定値が「1:予選の成績順」の場合は
的中数を示し、そうでない場合は0にする
E:(1000-$A17)/10000 →予選の並び順を0.0999から0.0001刻みに減らして表示させる
(1番目の人が0.0999、2番目の人が0.0998、3番目の人が0.0997・・・)
これらの数式を組み合わせることで、決勝の立ち順が「予選の成績順」と「予選の立ち順を基にした(準拠した)順序」の両方に対応できるようにしています。
ここで、具体的な数字でスコアがどうなるかと使用上の注意点について見てみようと思います。
【予選通過条件設定値】
的中数 B5 予選通過的中数を4中以上に設定
通過順位 B7 予選通過順位を5位以内に設定
「決勝の立順: 0:予選の立ち順に準拠」の場合のスコア(1以上が予選通過)
この場合、的中数と順位の両方とも予選通過条件を満たさないとスコアが予選通過できる数値にならないので、決勝通過条件と関係のない方を甘い数値(的中数の場合は1中とか、順位の場合は50位など)に設定しておく必要があります。
「決勝の立順: 1:予選の成績順」の場合のスコア
この場合は的中数に予選の並び順が考慮されたスコアになるので、特に問題はないと思います。
サンプルファイルは「shakaishukei2b2.xls」です。
1.便利な機能
Excelの便利な機能を利用して、的中状況(○か×)を入力するだけで順位などを自動で表示させるようにしました。なお、簡単な関数を使用しただけなのでマクロのような危険性は全くありません。
(1)射会の進行に伴って、各参加者の的中数を自動で集計し暫定順位を表示する
(2)暫定順位をもとに、成績上位者のリストを常に自動的に表示する
(3)最後の立ちが終わり的中状況をすべて入力すると、それが確定順位となります
つまり、射会の最中は参加者の的中(○、×)を入力するだけです。 さらに、
(4)的中数の代わりとなる「スコア」を計算することで、並び順の設定・変更ができるようになりました。
(5)決勝の立順設定を「予選の成績順」と「予選の立ち順を基にした(準拠した)順序」の
両方に対応できるようになりました。
2.サンプルファイルの入手方法
こちらから無料で入手可能です。
射会の成績をExcelで自動集計(その2) DLmarket版
3.サンプルファイルの使用方法
(1)サンプルデータ削除(最初に実施)
サンプルファイルには動作を確認するためのサンプルデータが入力されているので、最初にサンプルデータを削除してから使用してください。 なお、その際は水色の部分だけを削除して、それ以外の部分は基本的にはいじらないでください。
数式が入っているセルは基本的には変更禁止ですが、水色の部分にある氏名の下は、氏名を入力しなくても使えるように数式が入力されているだけなので、削除は可能です。
図 成績表(予選)シートの内容
水色の部分(所属から10となっている列)のデータを削除して使用してください
図 決勝(射詰め)シートの内容
水色の部分(1から10となっている列)のデータを削除して使用してください
(2)条件の設定
条件設定シートのB列が条件を入力する場所です。 ここでは、3つのサンプルファイルのうち、「shakaishukei2b2.xls」について説明します。
▼予選通過条件
ここに予選通過条件(的中数か成績上位か)、その場合の的中数と何位以内かを入力しておきます。
▼最終順位決定方法
決勝のみの結果で最終順位を決定するか、予選と決勝の合計で成績を決定するかを選択します。
▼決勝の立順
決勝の立順を予選の成績上位順にするか、予選の立順に準拠した順序にするかを設定します。予選の立順に準拠した順序とは、予選の立順を基本としてそこから予選通過者だけ抜き出したリストになります。
(3)人数と矢数の変更について
Excelの操作方法(特に関数)に詳しくない方はなるべく変更せずに使用してください。そのままでも入力できる矢数は予選・決勝ともに10射までで、参加人数は100人までに対応できますが、どうしても減らしたり増やしたりしたい場合は、サンプルデータを削除する前に変更してエラーが出ないことを確認してください。
(その3)では、称号者の部と有段者の部という具合に、複数の部門に対応する場合について対応していく予定です。 ご期待ください。