○第119回目 計算式をチェックする(その6)

○第119回目　計算式をチェックする(その6)

　前回の宿題です。ｎ回目のチェックをしている時、チェック元のシート上の座標と計算式を格納する配列変数の添え字との関係は、というものでした。
　まず記号の整理をします。
　シートの有効範囲の最後は…gyo1とretu1とします。
　チェック範囲の先頭は…g20とr20とします。
　今チェックしているセルの座標は、i行、j列とします。
　i行は、チェック範囲のたて位置を200で区切ったとき位置は次のようになります。
　(Int((i - g20) / ll200) + 1)
　先頭位置を20行と仮定すると、219行までは一番目のブロック、220行からは二番目のブロックとなります。
　同様に、j列は
(Int((j - r20) / ll150) + 1)
となります。
　ブロック内のi行の位置は、g20 と ブロック番号-1 掛ける 200 をiから引いたものに +1 したものになります。
　i - g20 - [(Int((i - g20) / ll200) + 1) - 1]*200 + 1
　i = 219では、219 - 20 - (0 + 1 - 1) * 200 + 1 = 200となります。
　i = 220では、220 - 20 - (1 + 1 - 1) * 200 + 1 = 1となります。
　このような変換式で、セルの座標が配列変数の添え字と結び付けられます。
　(列も同様です)
　チェックの回数は、i、jに依存しているので不要でした。
　宿題はおしまい。

　また、今の例でのチェックの範囲の先頭の行は、
　ｘ- 20 - (0 + 1 - 1) * 200 + 1 = 1
　ｘ- 20 - (1 + 1 - 1) * 200 + 1 = 1
　を解けばいいことになります。それぞれｘは、20と220になります。
　これから、チェック範囲の最後も出てきます。チェック範囲の最後はシートの有効範囲を超えませんので注意願います。

　7.元のシートでの計算式の中のシート名をチェック先のシート名に置き換える
　それぞれのシート名は、ww1()、ww2()にありますので、違っている場合のみ、ww1()をww2()に変換してください。
'シート名の変換。nはシート数の添え字。
For n = 1 To ii_max
If ww1(n) <> ww2(n) Then
Do While 1
p1 = InStr(mm(j1, i1), ww1(n))
If p1 = 0 Then Exit Do
mm(j1, i1) = Left$(mm(j1, i1), p1 - 1) & ww2(n) & Mid$(mm(j1, i1), p1 + Len(ww1(n)))
Loop
End If
Next n

　8.チェック先のファイルの同番号のシートを指定して、チェック範囲内の計算式と、既に取得した計算式との同一チェック
　計算式で違う箇所は大体はシート名なので、シート名を変換してやれば一致するはずです。変換は7.でおこなっているので同一性の比較は単純にやればいいことになります。
　この段階で、チェックする範囲の色をチェック範囲の色に全部変えます。その上でチェックでエラーになった場合のみ、指定の色をつけるということにします。
　有り有りで同一＝⇒何もしない
　有り有りで違う＝⇒3　赤色
　有り無し　　　＝⇒7　濃い桃色
　無し有り　　　＝⇒40　肌色
If a <> "" And mm(j1, i1) <> "" Then
If a <> mm(j1, i1) Then Cells(i, j).Interior.ColorIndex = 3: err_cnt1(m) = err_cnt1(m) + 1 '赤色
If a = "" And mm(j1, i1) <> "" Then Cells(i, j).Interior.ColorIndex = 7: err_cnt2(m) = err_cnt2(m) + 1 '濃い桃色
If a <> "" And mm(j1, i1) = "" Then Cells(i, j).Interior.ColorIndex = 40: err_cnt3(m) = err_cnt3(m) + 1 '肌色
　※ここでいう変数aは、チェック先のセルの計算式のことです。
err_cnt1(m)の添え字は、チェック回数のことです。

　9.チェック単位でエラー件数を表示する
　8.でエラーに該当したら、エラー件数をカウントしておきましょう。1回のチェックが終わったら、msgboxでエラー件数を表示しておきます。
　エラー件数以外には、チェックしたシート名とチェック範囲を表示します。

　10.チェック回数だけ繰り返す。チェック単位数の最大は100回とする(時間が分単位でかかる)
　100回の場合かなり時間がかかります。とりあえずは、巨大な表が対象となる場合があるとするならば、20回ぐらいで試してみてはどうでしょうか。

　11.次のシートへの処理に行き、繰り返す

　※現在の問題点
　現在チェックできるのは、ファイルが異なっても、パラメータは同じシートの同じ座標になっている場合です。
　しかし、例えば男女別にファイルは分かれているが、パラメータは男女とも、例えば、男のファイルのあるシートに両方が入っている場合があります。この場合はパラメータの座標が男女で異なります。
　これには対応できないのです。男のファイルには男のパラメータしか入っていなくて、女のパラメータと同じ座標だという場合しかできないということです。
　この場合がことごとくエラーになってしまうのです。
　使っている座標が違うのですからエラーとするのが当然ですが、よくみるとそのパラメータの箇所だけが違っていて、後はまったく同じなのです。つまり、正しい計算式なのです。このような場合は現時点ではお手上げですが、何とかならないのか、と思っています。
　エラー箇所が少なければいいのですが、このエラーが2万セルもあると、手作業で確認していくのは不可能になってしまいます。
　正当もどきの計算式をどうやって、本当のエラーから区分したらよいのか？
　例えば、
　1回目は無条件にチェックをかける。
　→エラー原因を分析し、それが正当と判断できれば、変換表を作り、座標の列記号を変換して、再度チェックをかけるというのはどうかと思っています。
　変換表のファイルをあけておき、そこから列記号の変換表を入手するという構想です。
　当初考えていたものより、かなり複雑なものに発展してきてしまいました。
　ファイル間の計算式同一性チェックは、当面は荷が重いということにしておきましょう。