Top＞ 小ネタ色々＞ Excelで有効数字

Excelで有効数字

2010年1月23日

11．負の数を乗り越える

負の数を回避するのは案外簡単である。絶対値をとればいいのだ。
ただし、これでは符号の情報が失われてしまうので、「どこかにマイナスだった」という情報をとっておいて、
マイナスだった場合には=ROUND(・・・)の後に(-1)を掛ければいいのだ。地味に面倒だが、仕方ない。

だが、ここでさらに、もう1点、気付いてしまった。

0（ゼロ）もダメじゃん・・・

絶対値を取ろうが何をしようが、ゼロはゼロ。ゼロの対数はやはりExcelでエラーを返される。

ところで、有効数字2桁、3桁の「0」って幾らだろうか・・・。

悩んだ。大いに悩んだ。0.0、0.00にしようかとも思った。だが、これが有効数字かと言われるとやはり違う。
例えば、
0.0001
であったとしても
0.00010
0.000100
であり、決して
0.0
0.00
ではないからだ。

悩み抜いた挙句、「いいや、0のままで」という結論に達した。
つまり、ゼロのセルは空白セルや文字のセルと同じようにエスケープさせてあげればいいのだ。

何となく、色々と方法がありそうだが、正の数か負の数か、ゼロかゼロではないかの2つのBoolean変数を用意して
その時に応じてTrueとFalseを入れ替えてあげるという方法を取ることにした。
今までのスクリプトを何となく生かせそうだったので。
それぞれisMinusとisNotZeroという変数を宣言することにした。

何故isNotZeroでゼロでない場合をTrueにするかは、if文との関連であって、完全に趣味の世界。
perlだと「Unless」というのがあって、偽ならThen以降を実行というのがあるが、残念ながらこちらにはない・・・。

でもって、実際の制御だが、正の数か負の数かゼロかで3種類の分岐があることが分かるだろう。
if文を入れ子にしてもできるのだが、もう少し便利なものがあるので、そちらを使う事にした。

Select Case
条件式の値に従って、複数のステートメント ブロックのいずれかを実行させるフロー制御ステートメントです。

なんか、小難しい事が書いてあるが、if文のような分岐ではなく、3つ、4つ・・・と分岐を増やせるようだ。
これを使おう。

その他に、絶対値を使うので、数字を丸める部分の処理を、

c.Formula = "=ROUND(ABS(" & myCellFormula & "),1-INT(LOG10(ABS(" & myCellFormula & "))))"

に変更している。また、これに合わせて、significantCancelで、Leftで取り出して参照する文字列と、
Midで取り出す位置の数字も変更している。

全部を書くと長くなってきたので、significantFigure2とsignificantCancelだけを書いておく。 

Sub significantFigure2()

Call significantCancel '重複防止の為、一度呼び出す

'変数の定義
Dim myRange As Range
Dim myCellFormula As String '式として変換する為の変数
Dim myCellValue As Variant 'variantで数字と文字列の判定
Dim startFrom As Variant 'variantで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
		End If
	End If
End If

Next

End Sub


Sub significantCancel()
'有効数字をキャンセル

'変数の定義
Dim myRange As Range
Dim myCellFormula As String
Dim commaPosition '式中のコンマの位置。variant型

Set myRange = Application.Selection

For Each c In myRange.Cells 'セルを抽出
	myCellFormula = c.Formula

	If Left(myCellFormula, 11) = "=ROUND(ABS(" Then '=ROUND(ABS(以外のセルをエスケープ

		commaPosition = InStr(myCellFormula, ",") 'コンマの位置を取得

		'=ROUND(ABS(を除いた12文字目からコンマ前までを取得
		If IsNumeric(Mid(myCellFormula, 12, commaPosition - 13)) Then
			c.Value = Mid(myCellFormula, 12, commaPosition - 13) '数字であればそのまま代入
		Else
			c.Formula = "=" & Mid(myCellFormula, 12, commaPosition - 13) '式であれば最初に=をつけて代入
		End If
	End If
Next

End Sub

ちょっと補足を。

For Eachの直後にisNotZero = Trueとしているのは、変数の初期化のようなもの。
仮に、直前に評価したセルがゼロで、isNotZero = Falseとなり、その値がそのままで次のセルの評価に
使われるのを避ける為に、セルを選びなおす度に初期値に戻すためのもの。

丸める部分の処理を丸ごとIf isNotZero Thenの中に入れ、ゼロのセルをエスケープするようにしている。
丸めた後にIf isMinus Thenで、負の数だった場合に、(-1)を掛けるという処理を追加している。

significantCancelの方は負の数関連の処理は全く追加していない。それは、もともとの数が、正だろうが負だろうが
そのままの形で丸める式に放り込んでいるからだ。絶対値を使っているので、式の中に入るのが負の数でも関係ないからだ。
その部分を抜き出してくるという処理なので、別に関係ないのだ。うん、よくできたプログラムだ。

[1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14]

<<Prev. | 小ネタTOP | NEXT>>