電卓が好きなクライアントのためのvba
日々仕事でvbaを書いているhiroです。毎日お疲れ様です。
配列で何か書こうかなと自分が過去に書いたプロシージャを眺めていたら、最近使わないけど、ちょっと便利かもと思うものを掘り起こしました。
それは、私の相手先となる業界によくいる、「足し算して100％にならないことに対して怒る人たち」をかわすためのプロシージャです。
例えば、こんな表があるとします。
比率の部分は例えばaの箇所は 655/4208＝15.6％ と計算して表記しているわけですが、実際は割り切れないので 15.5655893536・・・%となります。それを四捨五入して 15.6% とするのがエクセルの標準。
ですが、a～i までの比率の部分を電卓をたたいて合計すると、100.1% になるのです。
正直、「そんなこと気にする人いる？」というレベルのことなのですが、数年に１人はいます。そんな人たち。
特に何ページにもわたって計算式がつながっているような表、例えば表１の合計値と表２の合計値を表３に代入して計算する、などというかたちの資料をつくったときは大変です。相手はセルの中の計算式が分からないので、電卓をたたいて計算過程の確からしさを確かめようとします。このときに表記と本来の数値の0.1％のずれが最終の数値に影響してくる。そうすると「貴方は間違っている。訂正せよ」となるわけです。
こちらもいろいろな根拠を固めつつ、計算過程を間違わないようにスプレッドシートをつくるわけですから、相手が紙で出力した数値を電卓で確認するなんて思いもよらないのです。ですが数年に１人は出現します。
それを指摘されたら修正しないと相手が成果として受け取ってくれないのですから、修正せざるを得ない。下らないことに時間を費やしているなと思うのですが、仕方ない。
そこで編み出したのがこのコードです。ずいぶん前に書いたので、変数の使い方が最近の私好みではなく分かりにくいかもしれないのですが、ご容赦ください。
Sub 表示どおり四捨五入◆シート()
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long, g As Long
Dim h As Long, i As Long, j As Long, k As Long, l As Long, m As Long, n As Long
Dim o As Long, p As Long, q As Long, r As Long, s As Long, t As Long, u As Long
Dim v As Long, w As Long, x As Long, y As Long, z As Long
Dim rng As Range
Dim str1 As String, str2 As String, str3 As String, str4 As String, str5 As String
Set rng = ActiveSheet.UsedRange
c = rng.Cells(1).row
d = rng.Cells(1).Column
e = rng.Cells(rng.count).row - c + 1
f = rng.Cells(rng.count).Column - d + 1
Set rng = Nothing
For g = 1 To e
For h = 1 To f
If Cells(c + g - 1, d + h - 1).HasFormula And IsNumeric(Cells(c + g - 1, d + h - 1).Value) Then
str1 = Cells(c + g - 1, d + h - 1).Value
str2 = Cells(c + g - 1, d + h - 1).Text
j = InStr(str1, ".")
If j > 0 Then
k = Len(str1) - j
Else
k = 0
End If
m = InStr(str2, ".")
If m > 0 Then
n = Len(str2) - m
Else
n = 0
End If
If InStr(str2, "%") = 0 Then
If k > 0 Then
str3 = Application.WorksheetFunction.Substitute(Cells(c + g - 1, d + h - 1).Formula, "=", "")
str4 = "," & n & ")"
str5 = "=round(" & str3 & str4
Cells(c + g - 1, d + h - 1).Formula = str5
End If
End If
End If
Next h
Next g
End Subこのコードは簡単にいえば、シートの中で％表記しているセルを探して、小数点第何位までの％なのかを計算し、それに合わせて、計算式に四捨五入のround関数を入れてしまう、というものです。
これですべて解決するわけではなく、例えば合計値はちゃんとsum関数を使って検証し、99.9％ や 100.1％ になったら、どこかで辻褄合わせの計算式を入れないといけません。ですが、このプロシージャを使えば、いちいち電卓で検証する労力はかなり減ると思います。
まぁこんなくだらないことに労力を使っていたら、日本は30年も成長しないのはわかりますね・・・・