MIS.W 公式ブログ

早稲田大学公認、情報系創作サークル「早稲田大学経営情報学会」(MIS.W)の公式ブログです!

早稲田の学籍番号のCDをExcelで計算したい【カウントダウンカレンダー2018冬22日目】

みなさんこんにちは。たぶんMIDI研だった51代のげんたろうです。

過去記事の一覧。

音楽理論入門【カウントダウンカレンダー2016冬8日目】 - MIS.W 公式ブログ

Bootstrapを活用したR Shinyのカスタマイズ【カウントダウンカレンダー2017冬14日目】 - MIS.W 公式ブログ

ベクター画像をBlenderに取り込んで立体化して動画化する【新歓ブログリレー2018 3日目】 - MIS.W 公式ブログ

で、今回が私が担当する最後のアドカレになるわけですが、今回も絶妙に役に立つのかどうかわからないラインの記事です。よろしくお願いいたします。

チェックディジットとは?

f:id:xxgentaroxx:20181210234728p:plain

みなさん、チェックディジット(CD)はご存知でしょうか?

数字が並んだものを人間が入力するとき、どうしても間違えてしまうことがあります。隣の数字を打ってしまったがためにシステム上で大きな問題が発生する可能性もあります。数字列を「なんかの数式」にかけて、それで出た数字を末尾につけ足しておくことで、入力した数字が正しいものか、検査することができるのがチェックディジットです。

要は、学籍番号が

1X180000

だったとして、

1X180000-8

って書いたときの最後の「8」がCDです。

一見不規則につけられているように見えるCDですが、前述の通り「なんかの数式」があるのです。

早稲田の学籍番号のCD

というわけで、早稲田大学の学籍番号の後ろについているCDにも式があります。

そんなのどうやって探し出せばいいのか・・・?何と、

ググれば出てくる。

さすが、グーグル先生は何でも知っている。

・・・と言いたいところですが、どのサイトに出てくる計算式も、リサーチ不足により「不十分」な情報になっています。

結論から言うと、「文・文構除く文系学部」「文・文構」「理工三学部」で微妙に処理が違います。

そういったことも含め、少しずつ解き明かしていきましょう。

先に早稲田の学籍番号のおさらい

まず、最初の2桁が「学部コード」です。

学部コード 学部 備考
1A 政治経済学部
1B 法学部
1C 第一文学部 廃止
1D 第二文学部 廃止
1E 教育学部
1F 商学部
1G 理工学部 廃止
1H 社会科学部
1J 人間科学部
1K スポーツ科学
1M 国際教養学部
1T 文化構想学部
1U 文学部
1W 基幹理工学部
1X 創造理工学部
1Y 先進理工学部

今回、通信課程や大学院は調査対象外です。どういう計算式なんだろう。

次の2桁が、入学年度です。多分2099年までは現行の方式で行ける。

次の1桁は学部によって異なり、アルファベットが入っているものに関しては基本的には「学科コード」となっています。学部ごとの学科コードについては割愛。 ここに「0」しか入らない学部もあるようです。基幹理工学部は「学系」が入るようです。国際コースはまた別。誰か全部教えて。

最後の3桁が「通し番号」です。いわゆる出席番号のようなものです。再入学・転部・転科・編入学などの特殊な入り方をした人は何百番台みたいなこともあるようです。

ステップ・バイ・ステップで計算していく

ここでようやくExcelの登場。エクセルのA1セルに「1Y18A999」と入力したとして、Excel関数を用いてCDを計算しましょう。

文字を分割する

まず、学部コードを切り出して、数字に変換します。と言っても、頭の「1」は計算に使いません。

というわけで、A1セルに入力した文字列の2文字目を切り出す関数がこちら。

=MID(A1,2,1)

第2引数に切り出し開始文字の位置、第3引数に切り出す文字の長さを指定します。今回はこれをB2セルに入れています。

この調子で、次は入学年度。”とりあえず”C2セルに2つ一緒に切り出します。

=MID(A1,3,2)

5桁目もD2セルに切り出します。

=MID(A1,5,1)

最後に通し番号ですが、1桁ずつ別々に切り出します。E2、F2、G2セルにそれぞれ

=MID(A1,6,1)
=MID(A1,7,1)
=RIGHT(A1,1)

と入力します。RIGHT関数は、右端から文字数を指定して切り出す関数です。別にMIDでもいいけど。

ここまでの進捗。

f:id:xxgentaroxx:20181208230629p:plain

分割した文字を変換する

まず、学部コードのアルファベットを数字に変換します。ルールは単純で、「A」なら「1」、「B」なら「2」・・・といった具合です。

今回はYなので、25に変換したいです。

・・・が、それを直接やってくれる関数はExcelにはない。

これもググると、変換表を作ってVLOOKUPとかで置換する方法が出てきますが、そんなもの作りたくないので今回はこちらの方法を使ってみました。

=CODE(B2)-64

CODE関数は、文字コードを返してくれる関数で、ASCII文字の場合はASCIIコードを返してくれます。なので、大文字アルファベットを入力すると、65~122の範囲の数字を返してくれます。つまり、そこから64を引けば、望みの変換関数が作れるというわけです。小文字のアルファベットを入力された時の対応もできなくはないだろうけど、面倒なので今回は実装していません。

文・文構に関しては、もうひと手間。さらに17引く必要があります。これは勘で当てた数字ではなく、最初に上げた表に関連しています。

文構と文の学部コードはそれぞれ「1T」「1U」なのですが、CDの計算においてはかつての第一・第二文学部の学部コードである「1C」「1D」のまま計算するようです。これは私の推測なので、たまたま当たっているだけの可能性もあります。それも考慮したものがこちら。

=IF(OR(B2="T",B2="U"),CODE(B2)-64-17,CODE(B2)-64)

今回はこれをB3セルに入れています。

次は入学年度ですが、もう数字なので変換しなくていいかと思いきや、これも学部によって扱いが異なります。

理工三学部は、2桁そのまま使うのですが、それ以外だと下1桁しか使わないようです。下一桁しか使わないのか、10引いた数字を使っているのか、どちらが正解なのかは分かりません。2020年度入学生の学籍番号のCDを見ればはっきりするのだが…

というわけでコードはこちら。

=IF(OR(B2="W",B2="X",B2="Y"),C2,RIGHT(C2,1))

これをC3セルに入れます。

次の1桁ですが、学部によって数字だったりアルファベットだったりします。アルファベットの場合は先ほどの要領で数字に変換する必要があります。

セルに入力された文字が数値かどうかを調べる関数には、ISNUMBERがありますが、今回はこれが使えません。というのも、今回入力されているのは数値ではなく、文字列を切り出した文字あるいは数字、つまりやっぱり文字列なわけです。文字列で入力された数字を数値に変換する関数はVALUEで、これをかませれば、ISNUMBERもTRUEを返してくれるようになります。しかし、アルファベットが入力されているとエラーになってしまうので意味がないです。

…というわけで、これを逆手に取ったコードを考えてみました。

=IFERROR(VALUE(D2),CODE(D2)-64)

IFERROR関数は、第1引数に書いた処理を実行したときに通るかエラーが返るかで結果を分岐させることができます。通ったときはそのまま実行し、エラーの時は第2引数に書いた処理を実行します。つまり、数字であればそのまま数値に変換し、アルファベットであれば、先ほどの変換式を実行してくれます。これをD3セルに入れます。

最後に通し番号ですが、これは1文字ずつ切り出したものをそのまま移しておきます。E3、F3、G3セルにそれぞれ

=E2
=F2
=G2

と書いておきます。あんまり意味ないけど、見栄えですね…

ここまでの進捗。

f:id:xxgentaroxx:20181210230152p:plain

数字を掛けて、足す

いよいよCDらしい計算フェーズに入ります。あとここから先は全学部共通です。

ここまで出した6つの数字について、それぞれ2~7の数字を掛けていきます。いっぺんにコードを載せます。

=B3*2
=C3*3
=D3*4
=E3*5
=F3*6
=G3*7

これを、B4~G4セルに入れていきます。

そして、これらの数字の合計を出します。

=SUM(B4:G4)

これを、H5セルに入れます。

ここまでの進捗。

f:id:xxgentaroxx:20181210231823p:plain

11と格闘して、完成

先ほどの合計値を11で割り、余りを求めます。

=MOD(H5,11)

これをH6セルに入れています。

この数字をさらに、11から引きます。

=11-H6

これをH7セルに入れます。

最後に、得られた数字の下1桁を切り出します。ここでRIGHT関数を使ってしまうとまた文字列になってしまうので、10で割ったときの余りを使う方がいいと思います。

=MOD(H7,10)

これをH8セルに入れて、CDが得られます。

せっかくなんで、「1Y18A999-5」という形式で表示してくれるようにしてみます。

=CONCATENATE(A1,"-",H8)

CONCATENATEが文字列結合の関数です。

これをA9セルに入れると、最終的にこういう感じになります。

f:id:xxgentaroxx:20181210232925p:plain

わーいわーい。

CDを一発で計算する

途中経過なんて見なくていいから、パッと出してくれたまえ、ということであれば、すべてを1行で書くことになります。全部繋げちゃえばいいわけで…

=MOD(11-MOD(SUM(IF(OR(MID(A1,2,1)="T",MID(A1,2,1)="U"),CODE(MID(A1,2,1))-64-17,CODE(MID(A1,2,1))-64)*2,IF(OR(MID(A1,2,1)="W",MID(A1,2,1)="X",MID(A1,2,1)="Y"),MID(A1,3,2),MID(A1,4,1))*3,IFERROR(VALUE(MID(A1,5,1)),CODE(MID(A1,5,1))-64)*4,MID(A1,6,1)*5,MID(A1,7,1)*6,RIGHT(A1,1)*7),11),10)

うぎゃー。

これさえあれば、一発で算出してくれます。何に使うんだろうねこれ。

あとがき

学籍番号のCDを計算する行為も、Excelでいろんな関数を使うことも、両方複雑な話なのに、いっぺんにやってしまいました。これちゃんと読めるんだろうか。

あと、他人の記事を調査不足と言っておきながら、人科・スポ科の検証が足りていません。一応「本キャン学部」と同じ方式で計算していますが、合っているんだろうか。

もちろん、この計算式は公式のものではありません。間違っている可能性はあるので、使用は自己責任でお願いいたします。

以上、最後までお読みいただき、ありがとうございました。

明日は、53代fuchiくんの「イラストを描くためのファッション・インプット」です。インプット、大事だと思います。