Excel でささっと

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

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

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

2017 + (イニシャルのアルファべット２文字） + （２桁の数字）

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

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

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

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

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

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

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

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

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

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

［通し番号］
=if(イニシャル = １行上のイニシャル , １行上の通し番号 + 1, 1)

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

［10の位の数字］
=if(通し番号 = 1 , if(１行上の10の位の数字 > 80, 10, １行上の10の位の数字 + 10), 10の位の数字）

こんな感じになります。

sa11
ss21
ss22
ss23
st31
st32

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

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