前、こんな記事を書きました。
Excelの2つの表（の一部）同士が完全に一致しているか確認する方法です。チェックサム（表の数値の単純な総和）でもある程度誤りの検出はできます。2つの表のチェックサムが異なれば、当然表のどこかが異なっているはずです。ただ、単なるチェックサムだと、2つのセルの値を入れ換えてしまった場合などは誤りに気づくことができません。そのためにハッシュ値というのがあります。例えばMD5というのがそれです。数値のブロックから1つの数値（MD5）を生成します。ブロックの1ヶ所でも異なればその数値は異なるものになります。あちらのワークシートのこのブロックではMD5はxxxx、こちらのワークシートのこのブロックではyyyyで異なっている、だからどこかが異なっている、という風に検査できるのです。こんな感じで、ドラッグした範囲からMD5を計算してくれるようになっていれば、記事の方法よりもずっと手軽です。でもMD5も他のハッシュ値も、Excelでは最初から使えるようになっているわけではなく、いろいろ手を加えないといけません（多分自前でやることになる）。
今回、学期末の成績処理で、複数のワークシートの一部を切り取って貼り合わせ、新たにワークシートを作って作業しました。途中で操作ミスでもしていたら大変ですから記事の方法でチェックします。別のワークシートの表の比較ですから、いったん全ての表をひとつのワークシートにコピーして……とやりました。そうしないと記事の方法が使いづらいからです。こうでなく、あっちのワークシートで表からハッシュ値のようなものを計算し、別のワークシートでもハッシュ値を計算し、2つの数値を比較……で済めば手軽です。今回はその方法の紹介です。
「ワークシート上で、どこかのセルで「=func1(」（丸括弧を開くまで）と入力し、マウスで範囲をドラッグしてfunc1()の引数に範囲を与えると、その範囲からハッシュ値のようなものが計算されてセルに入る」ようにしたいのです。そういう都合のいい関数はExcelにはなさそうなので自分で作ります。まずExcelの「開発タブ」が表示されていなければ表示します。方法はあちこちのサイトで説明されているので省略。
開発タブ → Visual Basic → 挿入 → 標準モジュール
と進み、開いたウィンドウに以下のコードを入力します。func1()という関数を定義しています。
--------------------------------------
Function func1(block As Range) As Single
Dim s, t, d As Single
d = 0.39817450983
s = 0
For i = 1 To block.Rows.Count
For j = 1 To block.Columns.Count
If WorksheetFunction.IsText(block.Cells(i, j).Value) Then
t = 0
Else
d = d + 0.28937404
t = block.Cells(i, j) * d
End If
s = s + t
Next j
Next i
func1 = s
End Function
--------------------------------------
やっていることは単純なので、Visual Basic を使ったことがない人でも、何かプログラミングの経験があれば内容は理解できると思います。関数の引数、blockはワークシートをドラッグすることにより入力されます。2重のFor～Nextループでは選択された範囲のセル一つ一つに対して、入っている値をd倍して総和してゆきます。ここではdはループを回る毎に変化するようにしています。変化させず、d = 1 にとすると単なるブロックの総和を求めるだけ、となります（これはつまりチェックサムを求めること）。dを変化させれば例えばどこかのデータを間違えて入れ換えてしまっても和が異なり、誤りに気づくわけです。これが例えばd = 1,2,3,……だとすると、
といった誤りを検出できません。この例では2ヶ所値を変えてつじつまを合わせているわけです。
もちろん、dを上のコードのように半端な数にしても偶然和が一致してしまうことはあり得て、これはもう防げません。しかし、少なくとも単なるチェックサムよりはずっと精度が高そうなのでこうしています。心配ならもっと高度なMD5とかSHAR-1（シャーワン）とかのハッシュ値を使うか、さもなければデータを1個1個照合するしかないのです。コードにはIf文がひとつあり、セルに入っているのが文字列なのか数値なのか判定しています。最初、文字列だったらバラバラにして文字コードの並びと判断して……とやればいいかな、と思ったのですが、文字コードを取り出す関数CODE()がうまく機能してくれません（実行するとエラーが出る）。もういいや、と思って文字列ならそのセルには0が入っているとして処理しています。要するに文字列（生徒の氏名など）は検査の対象にしない、ということです。実際には生徒を特定する情報（クラスと出席番号。あるいは学籍番号とか）はまず大抵表に入っているわけで、事実上、文字列は無視しても問題は起こりません。
具体例で試してみると以下の通りです。
1ヶ所値を変えてみると、計算される数値もこうして変わります。どこかのセルで
「=func1(」（丸括弧を開くまで）と入力し、マウスで点検したいセルをドラッグします。ENTERキーで上のように数値が入ります。
自分で書いたコードは「アドイン」にすれば、以降自分のExcelでは共通して使えるのだそうですが、いろいを試してもうまくいきませんでした（「正しいアドインではありません」みたいなエラーが出る）。少し面倒ですが次で解決です。コードを書く画面で ファイル → ファイルのエクスポート とやると xxxxxx.bas のようなファイルが保存されます。この関数を使いたいときは、ワークシートを開いて ファイル → ファイルのインポート でxxxxxx.basを選べばOKです。どうもワークシートを開くたびにいちいちさっきの関数をインポートしなければならないらしいです。イヤだけれど、まあいいです。
これで2学期以降、ストレスが少し減るかも！
追記：
d = d + 0.28937404
はIf文のすぐ上がいいかも知れません。です！
そうしないと、
269AB3902（途中、文字が2つ）
269ABC3902（途中、文字が3つ）
で計算される値が同じになってしまうからです！
