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

Excelで有効数字

2010年1月23日

8．3桁の部分も作ったけど・・・

もうこれでいいだろう。
あとは丸ごとコピーして、新しいサブルーチンsignificantFigure3()の中身としてコピー、
数字を丸める部分の処理を

c.Formula = "=ROUND(" & myCellFormula & ",2-int(LOG10(" & myCellFormula & ")))"

とすれば、有効数字3桁の処理の部分が完成。

これで完成だ！といじっていた時に、ふとある挙動に気付いた。
一度2桁に丸めた数字を、思い直して3桁にしようとしてもならないのだ。

「12345」と入力して2桁に丸めて、続いて3桁に丸めてみると、表示は「12000」のまま。
そこで、セルの中身を見てみると

=ROUND(ROUND(12345,1-INT(LOG10(12345))),2-INT(LOG10(ROUND(12345,1-INT(LOG10(12345))))))

もうカッコが多すぎて、何の事か分からない感じになっているが、要は2桁に丸めた数字を
再び3桁に丸めているのだ。これはいかん。

折角、式が入っていてもセルの元々の数字が残るようにしてあるのに、これでは何の意味もないではないか！
そんなわけで、仕様に更に追加する事になる。

新しくサブルーチンsignificantCancelを作ってから中身を考える。

結局は、このプログラムで有効数字の設定をした所を探し出して、元の数字なり数式なりを
引っ張り出せばいいのだ。

有効数字の設定をしたところは、セルの最初の部分が「=ROUND(」になっているので、
これを指標に探し出せば良さそうだ。

あとは、どこまで抜き出すか、という事だが、「,」の位置が分かれば、
前に挙げた「Mid」で何とかなりそうだ、という事は分かる。

じゃぁどうやって探そうか、と、perl使いの私は正規表現を探したのだが、正規表現は使えないという事が明らかになるばかり。
仕方がないので、ヘルプをぼんやりと眺めていると、何となく見つかってくるのだ。

InStr
InStr([start, ]string1, string2[, compare])
バリアント型 (内部処理形式 Long の Variant) の値を返します。ある文字列 (string1) の中から
指定した文字列 (string2) を検索し、最初に見つかった文字位置 (先頭からその位置までの文字数) を
返す文字列処理関数です。

うむ、これで「,」の位置を探し出せば良さそうだ。
Variantで返ってくるので、何らかの変数を用意しておいた方が良さそうだ。

小難しい説明は後にして、とりあえずサブルーチンの中身を示しておこう。 

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 Mid(myCellFormula, 1, 7) = "=ROUND(" Then '=ROUND(以外のセルをエスケープ

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

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

	End If

End If

Next

End Sub

見れば分かると思うが、簡単に説明を。

変数の定義は、これまでと同じようにmyRangeとmyCellFormula。必ずしも数字ではなく、数式が入っている
可能性もあるのでValueではなくFormulaで。

やはり同じようにmyRangeをアプリケーションから取得してFor〜Nexのループに放り込んでmyCellFormula。
Midで1文字目から7文字目までを調べて「=ROUND(」だったらIf文のThen以下を実施する
（MidではなくLeftでも良かった・・・）。
この段階で、以前のように、文字列のセルや空白のセルは除かれるので、ここでは考えなくてもいいのだ。
地味にありがたい。

Thenの中では、「,」の位置をInStrで取り出してくる。

Mid(myCellFormula, 8, commaPosition - 8)

は、=ROUND(・・・)の中の8文字目（「=ROUND(」で7文字なので、その次）から抜き出すということ。
Midに入れるのは取り出す文字数。「,」がある場所の数字から、「,」自身の1文字分と
最初の「=ROUND(」の7文字分を引き算すると（つまりcommaPosition - 8）、 丸める前の数字が出てくる、というカラクリ。

ただし、これだと数式の場合は「=」がつかないので、IsNumericで数字か式かを判定して、
式なら「=」をくっつけてセルに代入する、というもの（IsNumericがFALSEになるのでElse以下を実行する）。

まぁ、これと言って新しい物は使っていないので、ゆっくり追いかければ理解できるでしょう。

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

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