○第116回目 計算式をチェックする(その3)
3.からです。
3.ブロックの中央あたりのセルを選び出し、その後ろの行との列の偏差、行の偏差、定数の偏差(通常は0)を求めます。チェックの基準値とします。
'基準位置(中間地点)において偏差1(列),2(行)を取得します。
ii0 = Int((gyo_cnt + 1) / 2)
For m = 1 To mm1(ii0, 0) 'mm1(**, 0)には項目数が入っています。
hensa1(m) = mm1(ii0 + 1, m) - mm1(ii0, m)
hensa2(m) = mm2(ii0 + 1, m) - mm2(ii0, m)
Next m
4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
'計算式のチェック。一つでも違いがあれば色をつけます。
For ii = 2 To gyo_cnt '一番最初のセルは、前のセルがないのでチェックできません。
For m = 1 To mm1(ii0, 0) '基準となるセルの項目数までチェックします。
If mm_sh(ii, m) <> mm_sh(ii - 1, m) Then flag = 99: Exit For
If mm1(ii, m) - mm1(ii - 1, m) <> hensa1(m) Then flag = 99: Exit For
If mm2(ii, m) - mm2(ii - 1, m) <> hensa2(m) Then flag = 99: Exit For
Next
'色コード⇒色 20⇒水色、27⇒黄色、46⇒赤色、44⇒オレンジ色
If flag = 99 Then '偏差が異なる
Cells(gyo1 + ii - 1, retu1 + jj - 1).Interior.ColorIndex = 27
flag = 0
End If
If mm1(ii, 0) <> mm1(ii0, 0) Then '項目数が異なる
Cells(gyo1 + ii - 1, retu1 + jj - 1).Interior.ColorIndex = 46
End If
'ここまではある一列の中での処理。終了後、次の列の処理に移ります。
必要な配列変数を初期化して、次の列に処理を移します。
'全部終了後、計算式のないセルに色をつけます。
For j = 1 To retu_cnt
For i = 1 To gyo_cnt
a = Cells(gyo1 + i - 1, retu1 + j - 1).Formula
If InStr(a, "=") = 0 Then
Cells(gyo1 + i - 1, retu1 + j - 1).Interior.ColorIndex = 20
End If
Next i
Next j
7.全部終了したら、ダンプリストを取るか取らないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し、再度処理を走らせてから、取ってください。
'デバック用内部変数のダンプ出力
Dim セル範囲 As Range
db_flag = InputBox("デバック用ダンプリストを出力しますか。最後の列だけになります。" _
, Default:=9, xpos:=2000, ypos:=3000)
If db_flag = 1 Then
Set セル範囲 = Application.InputBox _
(Prompt:="出力先のセルを選択してください", Type:=8)
AAA = セル範囲.Address
retu21 = Range(AAA).Column
gyo21 = Range(AAA).Row
For i = 1 To gyo_cnt 'チェックした計算式の数は、行数となります。
Cells(gyo21 + (i - 1) * 3, retu21) = mm1(i, 0) '項目数
For m = 1 To mm1(i, 0)
Cells(gyo21 + (i - 1) * 3, retu21 + m) = mm_sh(i, m) 'シート名
Cells(gyo21 + (i - 1) * 3 + 1, retu21 + m) = mm1(i, m) '列の偏差
Cells(gyo21 + (i - 1) * 3 + 2, retu21 + m) = mm2(i, m) '行の偏差
Next
Next
End If
※この変数のダンプをとる処理は使い回しがきくかもしれません。整理して活用度の高いものにしておきましょう。
※チェックマクロは、1.数値を除いてのチェック、2.列偏差、行偏差の同一性のチェックを紹介しましたが、どちらもそれだけでは不十分なところがありますので、この二つを合体させる必要があります。単純につなげればできますとはいえませんが、うまく重複するところと、順番を調整して合体させるといいと思います。
※次回はちょっと力を抜いて、エラー箇所のカウントと、その場所への移動を考えて見ます。
2009年4月26日日曜日
2009年4月19日日曜日
第115回目 計算式をチェックする(その2)
○第115回目 計算式をチェックする(その2)
3.座標の偏位が途中からずれているぞ、というテーマです。
まずは、どういうことをチェックするか(どのような機能を持たせるか)などを整理しておきます。
計算式から座標(パス名、シート名を含む)及び定数を抜き出し、それを比較する。座標の列記号は列番号に変換します。上下の形式で、座標の行列番号に規則性があるかどうかをみます。また、パス名・シート名、定数は同じものが使われているかどうかみます。
規則性の基準は一列ブロックのほぼ中央のセルとその下のセルの間において、列番号の偏差、行番号の偏差、定数などの差を求め、それを偏差の基準値とします。
以下の順で色設定の優先度が強くなるとしました。
・計算式が入っていないセル
・比較項目の数が上下で異なっているセル。
・偏差(下の計算式から算出)が、基準となる偏差と違うセル(パス名・シート名が異なるものも含む)。
重複している座標はそのまま整理せず使用します。
関数、演算子等は、削除しているのでチェックの対象外となります。
(関数については、前回のチェックマクロで十分だと思います)
デバックのため変数値のダンプリストの出力の有無を入れます。出力場所は中で指定します。
流れです。
1.(チェックしたい範囲が指定された状態からはじめます)指定された範囲の列番号、行番号、列数、行数を取得します。
2.(一列単位で処理します)計算式を一つずつ取得して、不要なもの(関数、四則演算子など)を削除して、座標などの必要な項目をカンマで区切ります。残るのは、パス名･シート名、座標、定数です。パス名･シート名は、それ用の配列変数に入れます。座標は、列番号と行番号を求めてそれ用の配列変数に入れます。これも別々の配列変数に入れます。定数は列番号を入れる配列変数にのみに入れます。
これを一列の中のすべてのセルについては行います。
3.ブロックの中央あたりのセルを選び出し、その前の行との列の偏差、行の偏差、定数の偏差(通常は0でしょうね)を求めます。チェックの基準値となります。
4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
パス名･シート名は等しいことが正当条件です。
列番号、行番号、定数は、前のセルとの偏差が基準値と等しいことが正当条件です。
ここで問題が起きました。チェックする項目数が異なる場合があることです。したがって、項目数が基準のセルと異なる場合もエラーにします。
とりあえず、列番号、行番号、定数用の配列変数は0で初期化しているので、項目数が異なる場合でも、チェックは基準のセルが持っている項目数分だけ行いますので、チェックしてもエラーになりません。
5.色をつける順番は、偏差等が異なる、チェック項目数が異なる、計算式がないとなります。
6.一ブロック分が終了したので、必要な配列変数を初期化して、次の列に処理を移します。
7.全部終了したら、ダンプリストを取るかとらないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し処理を走らせてから、取ってください。
具体的にしていきます。(1.は定番なので省略します)
まずは配列の変数の紹介です。
Dim mm_sh(100, 200)…パス名･シート名用(添え字の最初は行をあらわし、次はその計算式の中の項目を表す。以下同じ)
Dim mm1(100, 200)…列番号･定数用
Dim mm2(100, 200)…行番号用
Dim hensa1(100)…列番号･定数用の偏差
Dim hensa2(100)…行番号用の偏差
2.計算式を一つずつ取得して、座標などの必要な項目を取得する。
これも前とかでやったものです。
まずは、パス名にあるコロンを変えます。C:とかのコロンです。ここでは、C:、D:、F:のコロンをC_、D_、F_にします。
'計算式の取得
a = "***" & Cells(gyo1 + ii - 1, retu1 + jj - 1).Formula
If InStr(a, "=") = 0 Then a = "" '計算式でない場合はクリアする
b = a
'ドライブ名のつなぎの置き換え。 :を _へ
del_mojis(1) = "C:"
del_mojis(2) = "D:"
del_mojis(3) = "F:"
For i = 1 To 3
If del_mojis(i) <> "" Then
Do While 1
p1 = InStr(b, del_mojis(i))
If p1 = 0 Then Exit Do
b = Left$(b, p1 - 1) & Left(del_mojis(i), 1) & "_" & Mid$(b, p1 + Len(del_mojis(i)))
Loop
End If
Next
'関数名の削除(省略)
"ROUND"、"LOOKUP"、"IF"、"INT"、"SIGN"、"SUMPRODUCT"、"INDIRECT"
"ADDRESS"、"MAX"、 "MIN"、 "SUM"
を削除します。
'特殊文字の置き換え(省略)
"("、")"、""""、"<"、">"、"="、"*"、"/"、"-"、"+"
":"、"^"、",,,,,"、",,,,"、",,,"、",,"
をカンマ一つに置き換えます。
'カンマで区切られた項目の取得
Do loop文でカンマのある位置を元に項目を分解して配列変数に入れます。最後の一個に気をつけてください(文字列中にカンマがない場合は、残っているのが最後の項目になります。これを取得するのに注意してください)。
これはDo loop文の外で取得します。以下の文では省略しています。また、取得した項目数を求めておいてください。
Do While 1
p1 = InStr(b, ",")
If p1 = 0 Then Exit Do
If Left$(b, p1 - 1) <> "" Then
p_xy_A1(i) = Left$(b, p1 - 1)
i = i + 1
End If
b = Mid$(b, p1 + 1)
Loop
'パス名･シート名と座標等の分離。座標でシート名がない場合は、当該シート名
パス名･シート名があるかないかは、"!"があるかないかで調べます。
定数の場合は、シート名はヌルにしています。
For i = 0 To p_xy_no 'これは項目数(0からカウント)
p1 = InStr(p_xy_A1(i), "!")
If p1 <> 0 Then
p_sh_name(i) = Left$(p_xy_A1(i), p1 - 1)
p_xy_A1(i) = Mid$(p_xy_A1(i), p1 + 1)
ElseIf IsNumeric(p_xy_A1(i)) = False Then
p_sh_name(i) = ActiveSheet.Name
Else
p_sh_name(i) = ""
End If
Next
'シート名、列番号、行番号を格納する。mm1(ii, 0)は項目の個数(1からのカウント)です。
今まで取得したものと、この時にこれを入れるmm1(ii, 0)とは、一つずれているので注意してください。
座標の場合、座標から列番号と、行番号を求めます。
mm1(ii, 0) = p_xy_no + 1 'iiは行の値です。
For k = 1 To p_xy_no + 1
mm_sh(ii, k) = p_sh_name(k - 1)
If p_xy_A1(k - 1) <> "" And IsNumeric(p_xy_A1(k - 1)) = False Then
mm1(ii, k) = Range(p_xy_A1(k - 1)).Column
mm2(ii, k) = Range(p_xy_A1(k - 1)).Row
ElseIf IsNumeric(p_xy_A1(k - 1)) = True Then 'これは定数の場合です。
mm1(ii, k) = p_xy_A1(k - 1)
mm2(ii, k) = 0
Else
mm1(ii, k) = 0
mm2(ii, k) = 0
End If
Next k
※これでやっと一つのセルの計算式の分解が終わりましたので、次のセルに処理を移します。
Next ii
※長くなりましたので、今回はこれでおしまい。
3.座標の偏位が途中からずれているぞ、というテーマです。
まずは、どういうことをチェックするか(どのような機能を持たせるか)などを整理しておきます。
計算式から座標(パス名、シート名を含む)及び定数を抜き出し、それを比較する。座標の列記号は列番号に変換します。上下の形式で、座標の行列番号に規則性があるかどうかをみます。また、パス名・シート名、定数は同じものが使われているかどうかみます。
規則性の基準は一列ブロックのほぼ中央のセルとその下のセルの間において、列番号の偏差、行番号の偏差、定数などの差を求め、それを偏差の基準値とします。
以下の順で色設定の優先度が強くなるとしました。
・計算式が入っていないセル
・比較項目の数が上下で異なっているセル。
・偏差(下の計算式から算出)が、基準となる偏差と違うセル(パス名・シート名が異なるものも含む)。
重複している座標はそのまま整理せず使用します。
関数、演算子等は、削除しているのでチェックの対象外となります。
(関数については、前回のチェックマクロで十分だと思います)
デバックのため変数値のダンプリストの出力の有無を入れます。出力場所は中で指定します。
流れです。
1.(チェックしたい範囲が指定された状態からはじめます)指定された範囲の列番号、行番号、列数、行数を取得します。
2.(一列単位で処理します)計算式を一つずつ取得して、不要なもの(関数、四則演算子など)を削除して、座標などの必要な項目をカンマで区切ります。残るのは、パス名･シート名、座標、定数です。パス名･シート名は、それ用の配列変数に入れます。座標は、列番号と行番号を求めてそれ用の配列変数に入れます。これも別々の配列変数に入れます。定数は列番号を入れる配列変数にのみに入れます。
これを一列の中のすべてのセルについては行います。
3.ブロックの中央あたりのセルを選び出し、その前の行との列の偏差、行の偏差、定数の偏差(通常は0でしょうね)を求めます。チェックの基準値となります。
4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
パス名･シート名は等しいことが正当条件です。
列番号、行番号、定数は、前のセルとの偏差が基準値と等しいことが正当条件です。
ここで問題が起きました。チェックする項目数が異なる場合があることです。したがって、項目数が基準のセルと異なる場合もエラーにします。
とりあえず、列番号、行番号、定数用の配列変数は0で初期化しているので、項目数が異なる場合でも、チェックは基準のセルが持っている項目数分だけ行いますので、チェックしてもエラーになりません。
5.色をつける順番は、偏差等が異なる、チェック項目数が異なる、計算式がないとなります。
6.一ブロック分が終了したので、必要な配列変数を初期化して、次の列に処理を移します。
7.全部終了したら、ダンプリストを取るかとらないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し処理を走らせてから、取ってください。
具体的にしていきます。(1.は定番なので省略します)
まずは配列の変数の紹介です。
Dim mm_sh(100, 200)…パス名･シート名用(添え字の最初は行をあらわし、次はその計算式の中の項目を表す。以下同じ)
Dim mm1(100, 200)…列番号･定数用
Dim mm2(100, 200)…行番号用
Dim hensa1(100)…列番号･定数用の偏差
Dim hensa2(100)…行番号用の偏差
2.計算式を一つずつ取得して、座標などの必要な項目を取得する。
これも前とかでやったものです。
まずは、パス名にあるコロンを変えます。C:とかのコロンです。ここでは、C:、D:、F:のコロンをC_、D_、F_にします。
'計算式の取得
a = "***" & Cells(gyo1 + ii - 1, retu1 + jj - 1).Formula
If InStr(a, "=") = 0 Then a = "" '計算式でない場合はクリアする
b = a
'ドライブ名のつなぎの置き換え。 :を _へ
del_mojis(1) = "C:"
del_mojis(2) = "D:"
del_mojis(3) = "F:"
For i = 1 To 3
If del_mojis(i) <> "" Then
Do While 1
p1 = InStr(b, del_mojis(i))
If p1 = 0 Then Exit Do
b = Left$(b, p1 - 1) & Left(del_mojis(i), 1) & "_" & Mid$(b, p1 + Len(del_mojis(i)))
Loop
End If
Next
'関数名の削除(省略)
"ROUND"、"LOOKUP"、"IF"、"INT"、"SIGN"、"SUMPRODUCT"、"INDIRECT"
"ADDRESS"、"MAX"、 "MIN"、 "SUM"
を削除します。
'特殊文字の置き換え(省略)
"("、")"、""""、"<"、">"、"="、"*"、"/"、"-"、"+"
":"、"^"、",,,,,"、",,,,"、",,,"、",,"
をカンマ一つに置き換えます。
'カンマで区切られた項目の取得
Do loop文でカンマのある位置を元に項目を分解して配列変数に入れます。最後の一個に気をつけてください(文字列中にカンマがない場合は、残っているのが最後の項目になります。これを取得するのに注意してください)。
これはDo loop文の外で取得します。以下の文では省略しています。また、取得した項目数を求めておいてください。
Do While 1
p1 = InStr(b, ",")
If p1 = 0 Then Exit Do
If Left$(b, p1 - 1) <> "" Then
p_xy_A1(i) = Left$(b, p1 - 1)
i = i + 1
End If
b = Mid$(b, p1 + 1)
Loop
'パス名･シート名と座標等の分離。座標でシート名がない場合は、当該シート名
パス名･シート名があるかないかは、"!"があるかないかで調べます。
定数の場合は、シート名はヌルにしています。
For i = 0 To p_xy_no 'これは項目数(0からカウント)
p1 = InStr(p_xy_A1(i), "!")
If p1 <> 0 Then
p_sh_name(i) = Left$(p_xy_A1(i), p1 - 1)
p_xy_A1(i) = Mid$(p_xy_A1(i), p1 + 1)
ElseIf IsNumeric(p_xy_A1(i)) = False Then
p_sh_name(i) = ActiveSheet.Name
Else
p_sh_name(i) = ""
End If
Next
'シート名、列番号、行番号を格納する。mm1(ii, 0)は項目の個数(1からのカウント)です。
今まで取得したものと、この時にこれを入れるmm1(ii, 0)とは、一つずれているので注意してください。
座標の場合、座標から列番号と、行番号を求めます。
mm1(ii, 0) = p_xy_no + 1 'iiは行の値です。
For k = 1 To p_xy_no + 1
mm_sh(ii, k) = p_sh_name(k - 1)
If p_xy_A1(k - 1) <> "" And IsNumeric(p_xy_A1(k - 1)) = False Then
mm1(ii, k) = Range(p_xy_A1(k - 1)).Column
mm2(ii, k) = Range(p_xy_A1(k - 1)).Row
ElseIf IsNumeric(p_xy_A1(k - 1)) = True Then 'これは定数の場合です。
mm1(ii, k) = p_xy_A1(k - 1)
mm2(ii, k) = 0
Else
mm1(ii, k) = 0
mm2(ii, k) = 0
End If
Next k
※これでやっと一つのセルの計算式の分解が終わりましたので、次のセルに処理を移します。
Next ii
※長くなりましたので、今回はこれでおしまい。
2009年4月12日日曜日
第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関数の指定範囲が一部分ずれている。
似ているようですが、前回とは別テーマです。
出来上がったエクセルのシステムにおける計算式をチェックしようというものです。
そんなものができるはずがない、というのが本当のところでしょう。
実はそのとおりなのです。
一般的に使えるようにする、それゆえに、非常に簡単なチェックしかできるわけがないということになります。ということは、一般的に使える、それゆえに、非常に簡単なチェックならできるということになります。(これを否定からの肯定への変換といいます。よくコップに半分の水がはいっている例があげられます。この状態を、半分しか入っていないと見るのか、半分(も)入っていると見るのか、どうせなら、半分(も)入っているというふうに見ようじゃないか、というのと同じです)
エクセルのシステムを作ると、全部はできませんが、部分々々、おおよそのところはチェックをします。それを前提として、漏れやすいもの(特に頭の中で)を対象にします。
チェック内容は？(ブロック単位でのチェックとなります)
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関数の指定範囲が一部分ずれている。
2009年4月5日日曜日
第113回目 計算式の内部の関数値を求める
○第113回目 計算式の内部の関数値を求める
これは、xxx=lookup(……)*ｃ10という式があったとき、lookup(……)の結果を求めようとするものです。他には、SUM関数、SUMPRODUCT関数などが考えられます。
これは、次のような流れで作ります。
1.計算式中の該当する部分のみを抜き出す。上の例では、lookup(……)の部分を抜き出すということになります。
2.その部分を計算式の形式に整えて、特殊な場所のセルに計算式として埋め込みます。
3.そのセルには計算結果が出てきますので、その結果を変数に入れます。また、エクセルの外で使えるように、クリップボードに貼付けます。処理終了後、特殊な場所のセルをクリアします。
4.計算式の内容一覧表の中に、該当する計算式とその結果を表示します。
1.において
最初の位置は簡単に取得できますが、後ろの位置は、いろいろな場合があるので、ちょっと工夫が必要です。現実には、それ以降のカンマの位置を求め、そこからそれ以降のカッコの閉じの位置を最後の位置としました。元にある数値が複雑な形式の場合は、参照の元となる変数の記述にカッコが使われる場合があるので、このようにしています。
しかしながら、要素が三つあるので、二つ目のカンマ以降のカッコの閉じのほうがより正確でしょう。応用ですので各自チャレンジしてみてください。
3.において
セルに計算式を埋め込むのは簡単ですが、それをクリップボードに入れるのは若干注意が必要です。コピペを実際に行ってからではないとクリップボードにはいかないからです。当該セルに当該セルの内容をデータのみコピペしてしまい、その後、そのセルをクリアするというのがいいようです。変数値から直接クリップボードに入れる関数があればいいのですが、今のところわかりませんので、このようにしています。
他の関数においても、それ専用に考えていけばいいと思います。すべてが一つのやり方ではできるとは思いませんので。
このことにより、計算式の中の項目を数値に置き換えることがかなりできるようになります。計算式のデバックがより簡単になると思います。
これは、xxx=lookup(……)*ｃ10という式があったとき、lookup(……)の結果を求めようとするものです。他には、SUM関数、SUMPRODUCT関数などが考えられます。
これは、次のような流れで作ります。
1.計算式中の該当する部分のみを抜き出す。上の例では、lookup(……)の部分を抜き出すということになります。
2.その部分を計算式の形式に整えて、特殊な場所のセルに計算式として埋め込みます。
3.そのセルには計算結果が出てきますので、その結果を変数に入れます。また、エクセルの外で使えるように、クリップボードに貼付けます。処理終了後、特殊な場所のセルをクリアします。
4.計算式の内容一覧表の中に、該当する計算式とその結果を表示します。
1.において
最初の位置は簡単に取得できますが、後ろの位置は、いろいろな場合があるので、ちょっと工夫が必要です。現実には、それ以降のカンマの位置を求め、そこからそれ以降のカッコの閉じの位置を最後の位置としました。元にある数値が複雑な形式の場合は、参照の元となる変数の記述にカッコが使われる場合があるので、このようにしています。
しかしながら、要素が三つあるので、二つ目のカンマ以降のカッコの閉じのほうがより正確でしょう。応用ですので各自チャレンジしてみてください。
3.において
セルに計算式を埋め込むのは簡単ですが、それをクリップボードに入れるのは若干注意が必要です。コピペを実際に行ってからではないとクリップボードにはいかないからです。当該セルに当該セルの内容をデータのみコピペしてしまい、その後、そのセルをクリアするというのがいいようです。変数値から直接クリップボードに入れる関数があればいいのですが、今のところわかりませんので、このようにしています。
他の関数においても、それ専用に考えていけばいいと思います。すべてが一つのやり方ではできるとは思いませんので。
このことにより、計算式の中の項目を数値に置き換えることがかなりできるようになります。計算式のデバックがより簡単になると思います。
登録:
コメント (Atom)
