2010年9月 5日 (日)

[パソコン講座] 陸上競技の処理(4)

【全国の校内陸上競技会の集計処理をする先生向けの記事】

総合得点の集計と全員短距離走の集計は、昨年度と同じ。

昨年度は、賞状印刷用のデータ入力と得点集計用のデータ入力とは別処理となっていて、二度手間でした。今年度は、個人種目の賞状印刷用シートと、その得点集計のさらなる自動化を考えました。連続印刷するために、学年が混ざった状態で、リストになっています。
screenshot_318.png

事前に入力しておく部分は、画像では、タイトル部分を青の白抜きにしてあります。
A列の学年、B列の性別、C列の種目、D列の順位と、I列の種別(後述)です。
A列とB列は、条件付き書式で、入力する場所のミスを防ぐ工夫をしています。

競技場で入力するのは、ゼッケン番号と記録だけです。画像では、タイトル部分をオレンジの白抜きにしてあります。記録は、「秒」や「M(メートル)」などの記号を入れると手間が掛かるので、数字の部分だけを入力し、自動整形させます。

まず、学級は、ゼッケン番号を100で割り算をして、整数にすることで求められます。
G2 → =IF(E2>0,INT(E2/100),"")

氏名は、INDEX関数を使うのですが、学年ごとに[1年][2年][3年]というシートに名簿が入っており、A列で入力してある学年によって、INDEX関数の参照先をINDIRECT関数で使い分けしている点がちょっとしたテクニックです。出席番号は、ゼッケン番号から求められた学級番号を再度100を掛けて、ゼッケン番号から引き算することで求められます。
H2 → =IF(E2>0,INDEX(INDIRECT(A2&"年!$A$1:$F$60"),E2-INT(E2/100)*100,INT(E2/100)+1),"")

記録の整形部分です。

(1) 入力したものを1000で除算して整数にします。長距離の「分」になります。
J2 → =INT(F2/1000)
(2) (1)を1000で割った余り
K2 → =MOD(F2,1000)
(3) (2)の余りを10で除算して整数にします。長距離の「秒」になります。
L2 → =INT(K2/10)
(4) 長距離の最後の部分
M2 → =MOD(K2,10)

(5) 入力したものを100で除算して整数にします。短距離やフィールドの前の部分。
N2 → =INT(F2/100)
(6) 入力したものを100で割った余り。短距離やフィールドの後ろの部分。
O2 → =MOD(F2,100)

Field フィールドだったときの文字列を整形します。後ろの数字が1桁のときは、0をつけます。
P2 → =N2&"M"&IF(O2>9,O2,"0"&O2)

Short 短距離だったときの文字列を整形します。後ろの数字が1桁のときは、0をつけます。
Q2 → =IF(J2>0,J2&"分","")&IF(J2>0,IF(L2>9,L2,"0"&L2),L2)&"秒"&M2

Long 長距離だったときの文字列を整形します。「分」の値がなかったときは、「分」の部分を表示しません。「分」があれば「秒」は強制的に2桁になるように、そうでなければ1桁の場合もあるようにします。
R2 → =N2&"秒"&IF(O2>9,O2,"0"&O2)

整形後の記録
セルI2に入力してある記号(S・L・F)で、どれになるかを判断させています。
S2 → =IF(F2="","",IF(I2="F",P2,IF(I2="L",Q2,R2)))

本校は、1位6点、2位5点...、6位1点となっています。
T2 → =IF(D2>0,7-D2,0)
同着があれば、その場でD列の数字を変えることで自動的に得点が変化し、得点集計のミスをなくすようにしてあります。

これを必要な分だけコピーします。緑と灰色の部分、つまりG列とH列、それからJ列からT列までです。

得点集計は、ピボットテーブルを使います。学年、組、性別、種目を使って、一気に得点集計します。

なお、選手名簿上10人を超えない種目は、賞状が3位までです。連続印刷時には、記録が入力されていないデータは、印刷しないようにマクロを組んでいます。

最近のコメント

サイト情報