2010年1月23日
有効数字を正確に実現するためには末尾に0を補う必要が生じる。
そういうプログラムを作る為には、どのような場合に0を追加しないといけないのかを考えなくてはならない。
普段、自分の目で見ていて「四捨五入したら1桁になったんだから仕方ないじゃないか」と追加するのだが、
プログラムでそれは通用しない。Excelも含めて、そんなことは気にしていないからだ。
ここで、仕様の中に入れていた、「表示形式を変更しない」というのが効いてくる。
これが出来たら意外と楽だったのかもしれない・・・。
しかし、ここで最初に決めた仕様を変更する勇気もないので、どうにか実装する方向で行く事にした。
考えなければいけないのは、上にも書いたように「どんな場合に0を追加する必要があるのか」という事。
これを、プログラムが処理できる方法で考えなくてはならないのだ。
こういう事を考えていると、人間の頭というのがいかに柔軟にできているかを実感させられる。
そんな哀愁にひたりながらも、じ〜っと数字を見ながら考えると、どうやら、数字を四捨五入する桁と、
その1つ上の桁が関連しているらしい事に気付く。まぁ末尾に0を追加するんだから、当然と言えば当然。
具体的には、その2桁を取り出してきて00、01・・・04の場合と、95、96・・・99の場合に末尾がゼロになるらしいのだ。
それよりも上の位は考えなくても良さそうだ。必ず残っているのだから。仮に99999と続いていても問題はない。
必ず最後の桁は0になっているのだから。
そして、条件がもう1つ。丸めた後の数字の絶対値が10より小さいものの場合のみである。
10以上の場合は小数点以下の桁がないので0を追加する必要はないのだ。
ここで問題になってくるのが、どうやってその2桁を取り出してくるのか、という事。
これもまた、人間の目なら簡単なのに・・・。
仕方ないので、必死に数式を組み立てる羽目に陥る。
方針としては、対数を使って四捨五入する桁が1の位になるように桁数をずらして、
Rightで下2桁を取り出す事にした。これで何とかなりそう。
ヘルプで対数関連の関数を見てみるとLogで自然対数（底がe）は計算できるものの
常用対数（底が10）は計算できないという・・・。
という事は、底の変換をしないといけないという事だ。
まぁその辺の細かい事は各自で高校数学の勉強をし直してもらうとして・・・。
相変わらず、対数を使うので真数には絶対値を使わないといけない。
とりあえず、常用対数が使えるExcel上で実現させて、それを底の変換を含めてプログラムに反映させていこうと思う。
まず、元々の数に対して、
=INT(LOG10(ABS(数字))):ABS()は絶対値
とすると、1以上では実際の桁数-1の数字が、小数では最初に出てくる位が出てくるというのは
ずっと前に書いたとおり。という事は、これで出てきた数字の符号を逆にして、10の累乗を掛ければ、
1以上10未満の数字になるという事だ（表参照）。
有効数字2桁なので、3桁目までを見ておくには、さらに10の2乗を掛ければいいので、
数字*10^(2-INT(LOG10(ABS(数字))))
とすると、1の位が四捨五入する位置になる。
| 数字 | INT(LOG10(ABS(数字))) | 数字*10^(-INT(LOG10(ABS(数字)))) | 数字*10^(2-INT(LOG10(ABS(数字)))) |
|---|---|---|---|
| 12345 | 4 | 1.2345 | 123.45 |
| 123 | 2 | 1.23 | 123 |
| 1 | 0 | 1 | 100 |
| 0.45 | -1 | 4.5 | 450 |
| 0.00479 | -3 | 4.79 | 479 |
| -34 | 1 | -3.4 | -340 |
| -0.00521 | -3 | -5.21 | -521 |
あとは、INTで小数点以下を切り取れば・・・と言いたいところなのだが、
負の数の場合、INTにすると単純に小数点以下を切り取った数字よりも1つ小さくなる
（INTは元の数を越えない最大の整数なので）。
となると、やはり絶対値にして、正の数にした後でINTに放り込んであげる事になる。
末尾が0になるか否かは数字自体が00〜04もしくは95〜99であって符号は関係ないのでここで絶対値にしても問題はない。
きっとそうだ、うん。
後はこれをプログラムの方に持っていって、Rightで下2桁を取り出せばいいのだ。
という事で、その下2桁を受け取る為の変数を用意しておかなくてはならない。
まぁ何でも良かったのだが、lastTwoDigitとしてみた。
底の変換まで含めて、下2桁を取り出すスクリプトはこんな感じになる。
lastTwoDigit = Right(Int(Abs(myCellValue) * 10 ^
(2 - Int(Log(Abs(myCellValue)) / Log(10)))), 2)
いやぁもう、カッコが多くて、何が何やら・・・。
あとは、丸めた後の数字が1以上10未満なら「.0」を、1未満なら「0」を追加すればいい。
これもSelect Caseでも良かったのだが、たった2つだし・・・という事でIfで作ってみた。
ただし、丸めた後の数字を直接取得するのはそこそこ面倒そうだったので、丸める前の元の数字を使ってみた。
ここまで全てを加えたsignificantFigure2を書いておく。
長いなしかし。
Sub significantFigure2()
Call significantCancel '重複防止の為、一度呼び出す
'変数の定義
Dim myRange As Range
Dim myCellFormula As String '式として変換する為の変数
Dim myCellValue As Variant 'variantで数字と文字列の判定
Dim startFrom As Variant 'variantで1文字目を取得
Dim lastTwoDigit As Integer '残る桁とその1つ下の桁の判定
Dim isMinus As Boolean '負の数の判別用
Dim isNotZero As Boolean '0でないことの確認
Set myRange = Application.Selection
For Each c In myRange.Cells '個々のセルを抽出
myCellFormula = c.Formula
myCellValue = c.Value
isNotZero = True
If Len(myCellFormula) > 0 Then '空白セルをエスケープ
If IsNumeric(myCellValue) Then '文字列セルをエスケープ
'値が正か負か0かを判定。
'isMinusのBooleanに値を代入
Select Case myCellValue
Case Is < 0
isMinus = True
Case Is > 0
isMinus = False
Case Is = 0
isNotZero = False
End Select
If isNotZero Then
'セルの先頭文字を取得して式か数かを判定。
'式なら=を除去
startFrom = Left(myCellFormula, 1)
If startFrom = "=" Then
myCellFormula = Mid(myCellFormula, 2)
End If
'ここで丸めを実行
c.Formula = "=ROUND(ABS(" & myCellFormula & "),1-INT(LOG10(ABS(" & myCellFormula & "))))"
'負の数の場合、マイナスを復活させる。
If isMinus Then
c.Formula = c.Formula & "*(-1)"
End If
' 0と.0の追加。
lastTwoDigit = Right(Int(Abs(myCellValue) * 10 ^ (2 - Int(Log(Abs(myCellValue)) / Log(10)))), 2) '2桁の取り出し
If lastTwoDigit <= 4 Or lastTwoDigit >= 95 Then
If Abs(myCellValue) < 9.5 And Abs(myCellValue) >= 0.95 Then '「.0」を追加。1の位に数が入っている。
c.Formula = c.Formula & "&"".0"""
c.HorizontalAlignment = xlRight
ElseIf Abs(myCellValue) < 0.95 Then '「.0」を追加する必要がない場合。1の位が0
c.Formula = c.Formula & "&""0"""
c.HorizontalAlignment = xlRight
End If
End If
End If
End If
End If
Next
End Sub
|
いくつか補足を。
セルに入力する&は文字列を連結する記号。こちらのプログラム上ではなくセルに
=ROUND(・・・) & "0"
のように入力される事で、表示の上で後ろに文字が付く。
この為にはセルに「"」を入力する必要があるのだが、
ただ単に「"」を入力しただけではプログラム上の区切りや文字列の結合と認識されてしまう。
そこで「"」は「""」と2つ続けて入力する事になっている。
c.HorizontalAlignment = xlRight
は、後ろに「0」を追加したことによって、そのセルは文字列扱いとなり、左詰めに表示されるため、
そのままでは他の数字のセルとバランスが悪いという事で、右詰めに変更している部分。
これで有効数字2桁のプログラムは完成である。
[1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14]