過去のテスト成績（得点）を呼び出す方法

質問

毎年、国語と算数の学力テストを行っています。結果が複数のシートに現在の学年が過去の学年で行ったテストの点数が出席番号順に記録されています。

毎年クラス編制替えをしていたり、転出入があるなどで年度ごとに児童の出席番号は変わるので、特定児童の年度別得点推移を確認するのに手間がいります。

そこで、特定の児童名を入力する等簡単な操作で、年度ごとの得点データを呼び出せるような方法はないか模索しています。

エクセルを使用し、できればシンプルな関数などで対応できる方法があればご教授願いたいのですが。

どなたかご存じの方、宜しくお願いします。

回答No.1

keithin · ベストアンサー

この手のご相談で最初に確認しておく必要があるのは、「同姓同名の生徒」の扱いはいったいどうなってるのか、という点になります。
毎年クラス替えで出席番号が変わるのは仕方ないとして、同姓同名の子の識別は一体どうしてるのでしょうか。

クラス替え後にその子の去年の成績としてどっちの記録を見れば良いのか識別するには、例えば名前の方に「藤子不二雄Ａ」「藤子不二雄Ｂ」みたいに「同姓同名じゃない名前」を記入して同姓同名を回避するか、あるいは「出席番号」じゃなく在籍期間を通じて共通の「生徒番号」を振っておく、といった配慮が必要になります。

という前提の上で、ここでは「同姓同名は無い、すべて固有の名前が記録されている」前提でお話しします（といっても「生徒番号」でも、やることは全く一緒ですが）。

次に、できるだけ計算を簡単にしたいなら、成績リストの作り方からまず工夫して作成します。
例えば
シート２のＢ列に去年の生徒の名前（あるいは生徒番号）を羅列
シート２のＡ列には去年のクラス名の一覧（使いませんが必要なら出席番号を並べても良い）
シート２のＣ列に各生徒のあるテストの得点を羅列
シート２のＤ列に各生徒の別のテストの得点を羅列
　：

といった具合に準備しておけば

シート１のＢ列に今年の生徒の名前（あるいは生徒番号）を記入
Ｃ列にその子の去年の「あるテスト」の得点は
=SUMIF(Sheet2!B:B,B2,Sheet2!C:C)
あるいは
=VLOOKUP(B2,Sheet2!B:C,2,FALSE)
のようにさくっと取り出してきます。

勿論もうもう一工夫して、
=IF(VLOOKUP(B2,Sheet2!B:C,2,FALSE)="","",VLOOKUP(B2,Sheet2!B:C,2,FALSE))
あるいはご利用のエクセルのバージョンが不明のご相談ですが
=IFERROR(IF(VLOOKUP(B2,Sheet2!B:C,2,FALSE)="","",VLOOKUP(B2,Sheet2!B:C,2,FALSE)),"")
のようにして、「点数の記入が無ければ異なる処置をする」「今年の四月から転入して来た（＝去年のリストに無い新しい子）ら別の処置をする」ように細工を追加しても構いません。

＃しばしば見かける矛盾したご相談ですが、「シンプルにしたい」と言いながら、実は蓋を開けてみると「いま使ってるエクセルのまま何も変えたりとかしたくない」のでもっとずっと複雑な事になってしまっても構わない、という方も少なくはありません。

そういう縛りがある場合は、もっと具体的に今のあなたの「変えたくないとは具体的にどーなってるのか」とかを、詳しく目に見えるように情報を添えてご相談を出しなおしてもらう必要があります。

お礼

詳細な情報提供、ありがとうございました。

回答No.2

MarcoRossiItaly

データベースのキーとして、識別番号（ID）を作製しておくことが肝要です。それさえあれば、特定の人物のレコード（行）のみ抽出するのは、オートフィルタのボタンをクリックするだけでできますね。

ID は、データがいくら増えても個人を特定できるよう、一意な番号とすることが必要です。この目的のために、年月日や時間を番号の一部に与えることが多いです。

　　　　　　　　A　　　　　　 B　　C　　 D　　E　　 F　　 G　　 H　　 I
(1)　　20130401001
(2)　　2013-04-01-001-記号
(3)　　2013　　　　　　　　　 -　　04　　-　　01　　-　　001　　-　　記号

※「-」や「記号」は、不要であれば、なくても構いません。
※「数値」のセルは、Excel の仕様により、15 桁以内にします。超えてしまう場合は、列を分けます。
※数字に数字でない文字を付加すると、全体では「文字列」となります。

VLOOKUP 関数などの数式を使う関係で、なるべく(1)のタイプの ID にしたほうが計算がラクになります。ただし(3)を数式で結合することにより別の列に(1)を作るということも、後で紹介するとおり、簡単にできます。

オートフィルタにより簡単に、「2013 年 4 月 1 日に入学／転入してきた生徒（児童）」のみを表示できます。具体的には、「20130401001 以上で 20130501001 より小さい」などの条件でできます。

レコードの「並べ替え」（ソート）は、データタブからもできるし、オートフィルタのボタンからもできます。つまりグチャグチャな順番になっていても、ID で並べ替えれば、いつでも元どおりの順番に戻せます。

また「=date(a4,b4,c4)」という数式により、いつでも日付のシリアル値を算出できます。「2013/4/1」というシリアル値は、セルの書式を設定することにより、「平成 25 年 4 月 1 日（月）」とか「H25.4」とか、自在に表示することが可能です。

個人の ID その他の基礎情報から成る一覧を Sheet1、成績などの記録表を Sheet2、というふうにシートを分けると、後で管理がしやすくなるはずです。

Sheet2 にID を記入すると、それに該当する氏名などを Sheet1 から拾ってきて自動的に表示する数式を、Sheet2 にセットしておきます。

テストを表す番号も用意すると、便利です。この番号も別シートに分けて（必要な場合は）そこからテスト名称を数式で拾うなどしてもいいのですが、数式の量が増えるほど、ファイルが重くなります。そのため、この番号をあちこちで繰り返し使用する頻度が低いとすれば、必ずしもシートを分ける必要はないと思います。

添付図では、次式を記入しています。Sheet1、Sheet2 それぞれにオートフィルタを取り付けましょう。そして ID で絞り込み、その人だけの成績の履歴を表示させます。

Sheet1
H4　=0+(a4&text(c4,"00")&text(e4,"00")&text(g4,"000"))

Sheet2
H15　20130401004　　など
I15　=vlookup(h15,sheet1!$H:$W,2,)

Sheet1 に新しい人を追加する際は、次のとおりに操作します。

(1)
　既存データの最下端から下に 3、4 行くらい空けた位置に記入
(2)
　姓名の間にスペースを入れるなど、必要な加工、修正を文字列に施す。
(3)
　(2)が完全に終わったら、必ず、氏名の振り仮名を正しく整備
(4)
　追加した人たちだけを対象として、クラスに関係なく、並べ替え。振り仮名の順で並びます。既存データは、3、4 行空けた効果により、Excel が自動認識する並べ替えの対象範囲に含まれない。
(5)
　空けていた 3、4 行を削除（隙間は 1 行も残さない）

※(2)と(3)の順序を逆にすると、振り仮名が乱れて意図どおりにならないことがあります。
※振り仮名は、PHONETIC 関数で抜き出すことができます。当然、整備されていないまま抜き出しても、おかしな振り仮名が表示されます。
※姓と名を 2 列や 3 列に分けても構いませんが、姓だけでの抽出などを行う機会はそれほどないのであれば、上のとおりスペースを入れる程度でもよいでしょう。
※スペースを基準にして、姓のみ、名のみを抽出することは、FIND、LEFT、MID、RIGHT 関数により可能です。

セルの振り仮名を表示させるショートカットキーは、Shift+Alt+↑です。振り仮名のフォントの大きさなどを設定したい場合は、セル範囲を選択しておいて、ホームタブの振り仮名のボタンの右にある▼マークをクリックするなどします。

お礼

詳しい説明、ありがとうございました。