こんにちは。sosoblogの主です。本日は学校でよく使う生徒名簿について作成をしていたいと思います。とは、言ってもどのような名簿かと言いますと３年間の在籍期間でどのクラスに所属をしていたかが分かる名簿ファイルです。また、民間企業やイベント関係でも顧客管理などでも応用することができますので参考になさってください。
そして、新学年の学籍番号を入力すると自動で新学年の学籍番号に切り替わる関数も入れていきたいと思います。
表題の作成
今回の表題は、３年間の在籍を想定して作成していきますので下記の表題で進めていきたいと思います。
1.管理番号
2.現学籍番号
3.クラス
4.番号
5.氏
6.名
7.ウジ
8.ナ
9.１年学籍番号
10.２年学籍番号
11.３年学籍番号
この１１の項目で構成していきます。
管理番号
１．管理番号は、入学した年度内での管理番号です。これは、校内で重複する番号でないようにする必要があります。そうすることで他学年での同姓同名の区別をします。
現学籍番号
２．現在の学籍番号を関数で抽出します。
学籍番号とは、４桁の番号で管理をし４桁目（学年）３桁目（クラス）２桁目（十の位の番号）１桁目（一の位の番号）となります。
それ以外
３・４は現学籍番号から関数でクラスと番号を抽出します。
５〜８は氏名とウジナになります。今回は新クラスになった際に名前の順に並び替えることも想定しますのであえて分けました。
９〜１１どの学年でどの学籍番号であったかを割り振るために入力する項目です。ここには関数は入力しません。
表題の入力と完成図は下記の図のようになります。
管理番号
あいうえお順に入学生徒を並べたら、先頭に202400001と入力しセルの書式コピーで最後の生徒のセルまで下ろしましょう。
①あいうえお順に必ずソート（並び替え）をする必要はないです。
②管理番号は所属している学校で決めた番号で連続した数字の方が管理しやすいと思います。※探す時や並び替えをした時に見つけやすいです。（！実体験！）
オプションから連続データを選択して連続番号を作成します。
現学籍番号 （関数を使用します）
１年生から３年生までの３年間で使用することを想定します。
入力時の優先順位は、３年生＞２年生＞１年生という形です。要は次の学年での学籍番号を入力したら自動で新しい学年の学籍番号が現学籍番号へ反映されるようにします。
使用する関数
- IF関数
- ISBLANK関数
この２つになります。後は、関数の一部で絶対参照をするだけになります。
IF関数が分かりにくい方向けに下記に１年学籍番号だけをIF関数を使って表現する方法を記載しておきます。
IF関数を使って１年学籍番号に入力があったら表記・無かったら空白とする関数です。
上記をクリックすると答えが表示されます
入力するセルは、B2のセルにします。
=IF(I2=””,””,I2)
意味は「もし、I2のセルが空白なら、空白にして、そうでないならI2のセル値を反映するという意味です。」
これをフローチャートを使って見てみるとこのような形になります。
少し難しそうに見えますが、関数を少し勉強すればなんとなーく理解できる範囲だと思います。
ここで重要なのは、ISBLANK関数を使用して空白か否かを調べていることです。
当たり前ですが、１年学籍番号から埋まって行きますからそこが空白かどうか。そして次の学年が入力されているかどうかを調べる必要があるのでIF関数で判定させています。
クラスと番号の抽出
既にIFとISBLANK関数を組み合わせて現学籍番号は抽出が出来ているのでその抽出されたデータからクラスと番号の２種類の数値を取り出したいと思います。
使用する関数
- LEFT
- RIGHT
この２つのみです。
LEFT関数とRIGHT関数の意味
右または左から何文字取り出すかを決めることができます。
例：=RIGHT(セルを選択,3) 意味：右側から３文字抽出する
今回取り出しを行いたいのは、４桁番号で構成された学籍番号のクラスと番号です。
クラスの取り出し
クラスは、学籍番号の構成の左側から２桁目になります。ここで問題なのが、左側から２桁目ということなので、LEFT関数だけだと不要な部分まで取り出してしまうのでそれを回避する方法を教えします。
クラスだけを取り出すする方法
=LEFT(RIGHT(対象のセル,３））
LEFT関数の文字列の部分にRIGHT関数を合わせることで、１文字だけを取り出すことが可能です。
関数の中の３とは、右側から３文字目という意味です。要は、４桁の学籍番号の右から３文字目とは、クラスを意味しているのでその特定の部分だけを取り出したかったので３を入力しました。
番号だけを取り出す方法
=RIGHT(対象のセル,2)
４桁の学籍番号のうち、右側２文字分が番号となっていますので関数の中で２を入力しました。
実はこのままでは、取り出された数字は文字として認識されているはずなのでセル内で左側に寄っているはずです。なので、取り出したいのは数字なので数字として取り出せる関数も組み合わせましょう。
数字として取り出す
上記のクラス・番号を数値として取り出します。そのために使用する関数は
VALUE関数というものです。この関数は指定した文字列を数値に置き換えることができます。
クラスの文字を数値に置き換える場合
=VALUE(LEFT(RIGHT(対象のセル,3)))
これで、取り出した文字列はセル内で右側に寄って表示されていると思います。右側に寄るということは、数値として認識されている証拠です。
番号の文字を数値に置き換える場合
=VALUE(RIGHT(対象のセル,2))
上記と同じく取り出した文字列は、セル内で右側に寄って表記されたと思います。
対象の生徒がいなかった場合は空白とする
クラスには必ず４０名いると言う訳ではないはずですから、もし対象の生徒がいなかなった場合は空白とするという条件を付けて処理を行なっていきます。
使用する関数は、IF関数で非常にシンプルに仕上げていきます。
全ての式の前にIF関数を入れて、現学籍番号が空白なら空白でそうでないなら数値を取り出せという指示にしましょう。
クラスの場合
=IF(対象のセル=””,””,VALUE(LEFT(RIGHT(対象のセル,3))))
番号の場合
=IF(対象のセル=””,””,RIGHT(対象のセル,2))
IF関数でいう対象のセルとは、現学籍番号のことです。
最後に
ここまでがエクセルの中で使用する関数についてです。残りの氏名とウジナについては、手打ちをするか別のExcelファイルから貼り付けを行うことで完成です。
特に学校という環境は氏名を別々で管理しておいた方が都合がいいことが多いです。よく氏名を一緒にして管理している学校を目にしていましたが、くっつけているモノを編集するのと、別々で置かれており、関数でくっつけられているモノとでどちらが編集をしやすいかを考えてみてください。
こちらの記事のように別々で管理した方が編集後の確認や、ヒューマンエラーというのは最小限に抑えることができると思います。
是非学校運営や学級運営に役立ててください。
