読者です 読者をやめる 読者になる 読者になる

Excel でささっと

派遣先の大学で使われている学生の成績などを管理するシステムや、学内で利用するEメールなどを一括して登録することになった。どうも、やり方は毎年違うらしい。その時の担当者の詳しい方法でやるのだろう。ということで、今年は私のやり方に。


学生の学籍番号は入試番号と紐付けられており、入学が決まると番号は振られる。そして、学内のシステムやEメールには、学籍番号とは違うユニークなIDが設定される。

今年は、次のフォーマットで設定した。

2017 + (イニシャルのアルファべット2文字) + (2桁の数字)

仮に、サイトウ ツカサ さんという学生がいたら、2017ss11 とかになる。


これをどうやって作るか。
私は、Excel でやることにしました。

まず、元になる情報は、学籍番号、漢字の氏名、ひらがなの氏名です。
例えば、こんな感じ。

"2017-01-001" "斉藤 司" "サイトウ ツカサ"

ここから、IDを作成することになります。


まず、イニシャルのアルファベット2文字をどうすか。
ネットで調べると、VBA で作った記事が目を引きました。
そのコードを見ると、文字を判定して、該当するアルファベットを選んでいるだけでした。これなら、VBA にする必要はありません。辞書のシートを作っておいてvlookup() で検索するだけです。

 

1文字目は、"サイトウ ツカサ"の1文字目を left() で切り出し、vlookup() でアルファベットを検索します。


2文字目は、"サイトウ ツカサ"の苗字と名前の間の空白の位置を find() で計算し、その次の文字を mid() で切り出します。そして、1文字目と同じように vlookup()
で辞書を検索してアルファベットを作ります。

 

2桁の数字をどうするか。去年までは rand() で作ったランダムな数字を作って、後から一致してものを探して、手で直したのだそうです。そんな手作業は、間違いを生む原因なので、もちろん却下。

 

ユニークなIDにするには、イニシャルに通し番号を振ればいいだけです。なので、作成途中のシートをコピーして、イニシャルでソートした表を作り、通し番号を振る計算式を作りました。難しいことは何もやってません。こんなのです。

 

[通し番号]
=if(イニシャル = 1行上のイニシャル , 1行上の通し番号 + 1, 1)

 

これで、同じ1行上のイニシャルと同じなら通し番号をふり、違うイニシャルなら1を振ります。あとは、イニシャルが変わる度に、10の位を1つ上げていくだけ。

 

[10の位の数字]
=if(通し番号 = 1 , if(1行上の10の位の数字 > 80, 10, 1行上の10の位の数字 + 10), 10の位の数字)


こんな感じになります。

sa11
ss21
ss22
ss23
st31
st32

この4ケタでユニークがIDができあがりです。これに、年度を足して、2017ss11 といったIDを設定します。


これで、600個を超えるユニークな ID が一瞬で作れます。
まあ、通し番号を作るシートを別に作っているので、元のリストに修正が入ると面倒です。ここは、工夫のしがいがあるなと思ったのですが、そんな依頼は無く、これで完了でした。これくらいあっさりいくと苦労しないで済むのでいいですね。