第114回目 計算式をチェックする

○第114回目　計算式をチェックする

　似ているようですが、前回とは別テーマです。
　出来上がったエクセルのシステムにおける計算式をチェックしようというものです。
　そんなものができるはずがない、というのが本当のところでしょう。
　実はそのとおりなのです。
　一般的に使えるようにする、それゆえに、非常に簡単なチェックしかできるわけがないということになります。ということは、一般的に使える、それゆえに、非常に簡単なチェックならできるということになります。(これを否定からの肯定への変換といいます。よくコップに半分の水がはいっている例があげられます。この状態を、半分しか入っていないと見るのか、半分(も)入っていると見るのか、どうせなら、半分(も)入っているというふうに見ようじゃないか、というのと同じです)

　エクセルのシステムを作ると、全部はできませんが、部分々々、おおよそのところはチェックをします。それを前提として、漏れやすいもの(特に頭の中で)を対象にします。

　チェック内容は？(ブロック単位でのチェックとなります)
　1.計算式が抜けていた(何かのはずみでクリアしてしまったのでしょう)。
　2.計算式を最後まで直してなかった(縦方向、または、横方向にコピーし忘れていたということでしょう)。
　3.座標の偏位が途中からずれている。
　4.SUM関数の指定範囲が一部分ずれている。

　こんなところにしておきます。
　対応の考え方です。
　1.計算式がないところに色をつけます。変なところに色がついているかどうかでわかります。
　2.(簡単にするため縦方向のブロックを考えます)問題は、計算式が違うというのをどうやって判断するかです。一つ一つの計算式は異なっていますので、厳密に見てしまうと、みんな違うはずです。
　　通常は一つ計算式を作って、縦にコピーします。すると座標は変わりますが、計算式の構造は変わりません。つまり、ここに着目して計算式の構造が変わっているかどうかで判断します。この対応としては、計算式から数値を取り除いてそれで一致するかどうかを見ることにします。
　3.(これも縦方向のブロックを考えます)2.で取り除いた数値に着目します。この差が全部の計算式で一定かどうかを見ることにします。
　4.(これは横方向のブロックを考えます。縦方向のものは、2,3で対応できる)SUM関数内の最初と最後の座標(ここでは行数)が全部の計算式で同じかどうかを見ることにします。これをやるためには、まず、横の一般系(3に対応するもの)を考えたほうがいいです。

　具体例
　1.計算式が抜けていた。
　範囲を選択してから実行します。
　とあるセルに計算式があるかどうかを判断し、計算式がない場合は色をつけます。
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula <> True Then
CCC.Interior.ColorIndex = 42
End If
Next
　色をなくす場合は次のようにします。
CCC.Interior.ColorIndex = xlColorIndexNone
すべてのセルが対象であれば、if文はいりません。

ちょっとしたコマンドの使い方さえ知っていればこれは思ったより簡単です。

　2.計算式を最後まで直していなかった。
　これは少し分析してからではないと混乱します。
　1.範囲内の計算式を配列変数に入れます。計算式がない場合はヌルとします。配列変数は二次元となります。セルの移動の仕方は、列に飛び、列が終わると次の行に飛ぶということになります。(以下の3.の関係でこの段階で計算式のないセルに色をつけます)
　2.その計算式から数値及び"."を除きます。
　3.計算式は、1行目にある場合と2行目にある場合があります。また、場合によっては一行目と2行目以降の計算式が異なることがあります。
　そこでチェックの内容ですが、
　　一行目に計算式がない場合は、一行目を元にした計算式のチェックはしません。
　　二行目に計算式がない場合は、二行目を元にした計算式のチェックはしません。
　　一行目、二行目に計算式がある場合は、それぞれでつける色を変えます。重なる場合は二行目の色が上塗りされます。計算式がないセルはチェックしません(前回の機能を含めて、この場合は別な色をつけることにします)。
　とします。

　・計算式がないセル
　・一行目の計算式と異なるセル(次の場合は二行目のチェックの結果が優先されます)
　・二行目の計算式と異なるセル
　に色がつくことになります。

　1.範囲内の計算式を配列変数に入れます
'選択範囲の座標の取得
AAA = Selection.Address
p1 = InStr(AAA, ":")
AAA1 = Left(AAA, p1 - 1)
AAA9 = Mid(AAA, p1 + 1)
retu1 = Range(AAA1).Column
gyo1 = Range(AAA1).Row
retu9 = Range(AAA9).Column
gyo9 = Range(AAA9).Row
gyo_cnt = gyo9 - gyo1 + 1
retu_cnt = retu9 - retu1 + 1
'計算式の取得
For j = 1 To retu_cnt
For i = 1 To gyo_cnt
mm(j, i) = Cells(gyo1 + i - 1, retu1 + j - 1).Formula
if instr(mm(j, i),"=") = 0 then
mm(j, i) = ""
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 20
end if
Next i
Next j
　※"="が入っているかで計算式かどうか判断しています。入っていない場合は、色をつけます。

　2.その計算式から数値及び"."を除きます。
　これは、『第110回目　計算式から場所情報を取得できないか』で"ある文字を除く"処理の説明がありますので、それを参照してください。
　これによって計算式から、0-9と.が除かれます。

　3.計算式が同一かどうかのチェック
　一行目、二行目に計算式がない場合はチェックしません。また、計算式のないセルはチェックしません。
　'計算式のチェック
For j = 1 To retu_cnt
For i = 2 To gyo_cnt
If (mm(j, i) <> "") Then
If (mm(j, 1) <> "") and (mm(j, i) <> mm(j, 1)) Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 43
End If
If (mm(j, 2) <> "") and (mm(j, i) <> mm(j, 2)) Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 44
End If
End If
Next i
Next j
　※カラーパレットについては下記参照
　　http://www.feedsoft.net/excel/tips/vba_63.html

　※以下は次回以降
　3.座標の偏位が途中からずれている。
　4.SUM関数の指定範囲が一部分ずれている。