○第99回目 計算式の加工("#DIV/0!"対応、端数処理とif文)
計算式は、一般が難しいのでなかなかマクロの対象にはなりません。しかしながら、一部に基本となる計算式を加工するという例があり、それはある程度一般化できそうです。
ここでは、Round関数と"#DIV/0!"対応を考えてみます。
CJ11*CK11/CI11、CJ11*CK11/(CI11+CH11)というような式があったとします。
これらの式の問題点は、分母がゼロになった場合の"#DIV/0!"エラーが出てしまう。端数処理がされていないというものです。
この二つを何とかしようというマクロです。
計算式を作る最初から、この二つ問題点が出ないように組立てることは可能ですが、なんせめんどくさいです(これは人によって違うと思います)。
なお、割り算が入っていないとしても端数処理は必要です。
上の計算式は、いってみれば核となるものです。これだけは確実につくらなければなりません。
そこからの加工として何か有効なマクロはないか、という問題意識です。
上の一番目の式の場合、"#DIV/0!"対応としては、大体の人が次のようにしているのではないでしょうか。
=if(CI11<>0,CJ11*CK11/CI11,"") または
=if(CI11<>0,CJ11*CK11/CI11,0)
ここに端数処理をつければ、
=if(CI11<>0,ROUND(CJ11*CK11/CI11,3),"")
となります。
=if(CI11<>0,ROUND( CJ11*CK11/CI11 ,3),"")
と三つに分けると、処理内容が見えてきます。
しかし、変動項目があります。if文のCI11<>0のCI11と端数処理値の3です。
前者は中から持ってくるとして、3は入力値として与えます。
前者は、/の後の部分と勝手解釈してもよさそうです。
流れは次のようになります。
1.選択範囲を取得する
2.端数処理値を入力する
3.選択範囲内のあるセルの計算式を取得する
4.分母を切り出し取得する
5.if文及びROUND文を作る
6.該当セルに加工した計算式を埋め込む
7.選択範囲のセルについて繰り返す
ここで気になるのは、加工した計算式が、計算式としてエラーであった場合、どこの段階で止まりどうなるのかということです。
＝⇒マクロの中で加工した計算式をセルに埋め込む時に止まる、ということでした。
それはさておき、
第51回目に次のような記述があります。
'範囲を指定して実行する。
'計算式のあるセルの計算式に特殊な記号を付加して文字化する
Dim CCC As Object
For Each CCC In Selection
If CCC.HasFormula = True Then
CCC = "xxx" & CCC.Formula
End If
Next
この辺を使って組み立てましょう。1と7はこれで十分ですね。
2の端数処理値の入力も説明を省略してもいいでしょう。
if文のなかを拡充していきましょう。
3.選択範囲内のあるセルの計算式を取得する
AAA = CCC.Formula
4.分母を切り出し取得する
p1 = InStrRev(AAA, "/")
bunbo = Mid(AAA, p1 + 1)
5.if文及びROUND文を作る
AAA = "=if(" & bunbo & "<> 0," & "ROUND(" & Mid(AAA, 2) & "," & p3 & "),"""")"
6.該当セルに加工した計算式を埋め込む
CCC.Formula = AAA
※InStrRev(AAA, "/")は、後ろから見ていき、/がある(先頭からの)場所を求める関数
p3は端数処理値。
"),"""")" は、『),"")』のこと。"を文字として使う時は、""と二つ重ねて初めて一つの"になる、と解釈される。
計算式としての条件を満たさない場合の対応方法としては、6の段階で、CCC = "xxx" & AAA として、処理後に文字化計算式を計算式にする処理をしてください。
同様の処理として、if文作成があります。
これは核となる計算式を、一例としては、｢if( =<> ,｣と ｢," ")｣とかではさむというものです。この場合は、計算式としての条件を満たしませんので、頭にxxxを付加して文字化計算式にしてください。
これはネスト回数を入力することにすると、多少は利用度が広がります。
