スタミナ貯金走「個人別順位表」
これまでの記事はこちらです。
「スタミナ貯金走」のトライアルが無事終わりホッとしていると、担当の先生からスタミナ貯金走大会の「企画書」のコピーを頂きました。
学校の体育行事として正式イベントに取り上げる様で、何やら本格的になってきました。
予定通り学校の正式行事となると全学年児童 約５００名に展開していく事になります。
参加人数が拡大しても管理出来るよう急遽クラス別の管理表も準備しました。
クラス別・実施日毎のセルに実施回数を記入し、そのセルの色を見れば一目で状態がわかるようにしました。
色で示す状態とは［実施済み］［データスキャン済み］［データ確認中］［データ正常確認済み］［全校集計データ追加済］そして［本番:９］は色を分けました。
尚、各セルの記入と色付けは手作業で行いました。異常データが見つかり［データ確認中］の場合は赤で注意を喚起するなど、多少の手間はかかりましたが、クラス別の「今」の進捗状態が先生方と共有出来て大変便利でした。
管理表をもう一つご紹介します。全校集計データは件数がどんどん増えていくため蓄積データの内容を精査するのは至難の業です。
ある日集計結果を見ていると同じデータが２度出現。
調べてみると二重登録による不具合だとわかり、速攻で「全校集計データの件数表」を作りました。
これにより、全校明細データをひっくり返さなくても集計の全貌が把握できました。
下記は予定していた全ての「スタミナ貯金走」が全て終わったときの［全校件数］の内容です。
マウスで赤枠で囲ったデータ範囲を指定すると合計件数が自動集計。合計：1963と表示されています。
「３年生本戦成績グラフ」の作成手順を説明したいと思います。
まず、3年本戦_回数 という名前のシートを作りその中に３年生９回目の個人別順位表とグラフを作ります。
グラフをつくるため、全校の蓄積データから（B列）３年生の（C列）９回目（本戦=９回）を引っ張ってきます。
今回はQuery関数という超便利な機能を使います。
Query関数の詳しい解説はこちらをご覧ください。
スプレッドシートのQUERY関数を使う最初の一歩！クエリを理解する
=query('持久走記録全校'!A:I,"select * where B = 3 AND C=09 order by F desc, A ASC, C ASC",1)
Query関数の説明
1番目の引数は「データ」でクエリを実行するセルの範囲を指定します。
'持久走記録全校'!A:I
と指定しています。スプレッドシートの '持久走記録全校' という名のシートの A～I 列 を指しています。
Ａ～Ｉ（9列分）を「データ」とします。
2番目の引数は「クエリ」でデータ操作を実行する条件を指定します。
"select * where B = 3 AND C=09 order by F desc, A ASC, C ASC"
まず、先頭に書かれているのは、
slelct *
select は入力データ項目の定義です。"*" は「データ」で指定した範囲 A:I 9つ全ての列項目をデータとして表示する という意味です。
where B = 3 AND C=09
whereは選択条件の定義です。B = 3 且つ（AND） C = 09 となり、入力データを見ると B列は学年、C列は回数なので 3年 且つ 09回（=本戦）の条件に一致したデータを選択すると言う意味になります。
order by F desc, A ASC, C ASC",
order by は並び替え条件です。F列を降順、そしてA列を昇順、次いでC列を昇順 に並び替えるという意味です。
3番目の引数はデータの上部にある見出し行の数を指定します。
1
参照元の表はタイトル１行なので 1 と指定しています。
この長い式をA1セルに正しく入力すると大きな表が現れます。
Query関数を入力した A列～I列（9列分）が出力部分。A列～I列は"A1" に先程のQuery関数が入っているだけで、他のセルにデータが湧いてくる不思議な感じです。
表示されたデータをもとに個人別のグラフを作るのですが、QRでスキャンしたデータには 出席番号や氏名はありません。
Query関数でE列に表示したQRID をもとに Vlookup関数でJ列の key4 （年組番号）や K列の 氏名 のデータを表示しています。
ここではVlookup関数の説明は省略しますが詳しい解説はこちらをご覧ください。
VLOOKUP関数の使い方とその威力を存分に味わうデータ準備
次の表はQuery関数で引っ張ってきた３年９回目のデータを高成績順（Ｉ列順）に並び替えた表です。
M列「本戦順位」をRANK関数で算出しました。
セル F2 にはシャトルランの記録が入っていて、この数値が大きい順（上位から順に）に並んでいます。
M2 には次の数式を入れました。
（わかりやすくするため、要素ごと意図的にスペースを入れています）
=arrayformula(rank( F2, $F$2 : $F))
=arrayformula( )
arrayformula 関数に後続の関数を書くと、以下の行に同じ関数が入っているかのように機能してくれます。
これで順位が1, 1, 3, 3, 5, 6, 7,・・・と表示されるようになりました。
次に右側にグラフを作っていきます。
・先ずグラフで表すデータの範囲 A1:L34 を選択します。
・挿入→グラフ でグラフウィンドウを表示し、グラフの種類を「横棒グラフ」に変更します。
・系列 は
L列に「表示名」という列を設定し J列：「年組番号」, K列：「氏名」, M列：「ランク」の３項目を半角スペース； " " を入れながら & で連結しグラフに表示できるようにしました。
=J2 & " " & K2 & " " & M2
集計表の右側に高成績者のグラフが完成。
全校・各学年・低中高学年 10種類のグラフを作成しました。
別途グラフ部分だけを「スタミナ貯金走」の実績をスライドにまとめて報告しました。
次回はこの内容を説明したいと思います。