Excelで数値の丸め

質問

JIS Z8401 規則Aによる数値の丸めをエクセルの関数で行ないたいと考えています。
ただし有効数字を3桁にする必要があります。
つまり有効数字4桁目以降を丸める際、「二つの隣り合う整数倍がある場合は」「丸めた数値として偶数倍のほうを選ぶ」との規則になります。
具体的には下記の様にしたいのです。

54.25→54.2
79.13→79.1
108.5→108.0
110.95→111.0
119.55→120.0
207.2→207.0

過去ログにこれに近い物もありましたがうまくいきません。
よろしくお願いします。

回答No.2

SortaNerd_ · ベストアンサー

次の方法でできると思います。

A1: 元数値
B1: =CEILING(LOG10(A1),1)
元数値の桁数を求めるためLOGを取り切り上げ。
C1: =MOD(A1,10^(B1-3))*10^(3-B1)
有効桁以外の部分を算出、および桁合わせ。
D1: =IF(ABS(C1-0.5)<0.000001,ROUND(A1/2,3-B1)*2,ROUND(A1,3-B1))
それが誤差範囲以内(※)で0.5に等しければ、1/2して丸めて2倍する。さもなくば普通に丸める。

※Excelは小数を正確に扱えないので、"等しい"を条件にすると誤差で正常に動作しない。
よって"絶対値がある小さな値以下"で書き換える。
今回の「0.000001」は適当。用途に合わせて意味のある数字の数桁下にしておけばよいと思う。

なおNo1さんのものは間違っているようです。
単なる五捨六入かと思いきやその下1桁だけ考慮しているようなので、50捨51入とでも言うのでしょうかね。

お礼

ご回答ありがとうございます。
早速活用できそうです。

回答No.7

noname#204879

[回答番号：No.5この回答への補足]へのコメント、

ご質問の冒頭に「有効数字を3桁」と明記されている部分を見落としていました。大変失礼しました。

ゴリ押しで作成した次式を試してみてください。どうなりますか？

=IF(MOD(MID(SUBSTITUTE(A1,".",""),3,1),2)+MOD(MID(SUBSTITUTE(A1,".",""),4,9)/10^(LEN(MID(SUBSTITUTE(A1,".",""),4,9))-1),5),ROUND(A1,3-ROUNDUP(LOG(A1),0)),ROUNDDOWN(A1,3-ROUNDUP(LOG(A1),0)))

回答No.6

Wendy02

#4 の回答者です。

すでに回答はあるようですが、「偶数丸め」というのは、もともと、コンビュータの統計の際に、誤差を少なくするために発案された、特別な丸める方法と解釈していました。だから、上位桁から4桁目を丸めても、あまり意味がないのではないかと思いました。

>今回はとりあえず関数で作成してみます。

また、関数で行う、マクロでも可能かという主旨は、最初に提示していただいたほうがよいです。関数の得意な方は大勢いらっしゃるようですから、私などが書いても、レスはゴミになってしまうかもしれません。

なお、数式の回答として掲示しておきます。

=IF(MOD(MOD(INT(A1*10^(2-INT(LOG(A1)))),10),2),ROUND(A1,2-INT(LOG(A1))),ROUNDDOWN(A1+0.49*10^(INT(LOG(A1))-2),2-INT(LOG(A1))))

なお、これは、負数は入れると、LOG関数にエラーが発生します。

お礼

ありがとうございます。
質問の仕方も今後勉強させていただきます。

回答No.5

noname#204879

》 具体的には下記の様にしたい

提示された例では、JIS で言うところの「丸めの幅」が統一されておりませんが、ソコントコはどう考えていますか？

補足

JIS Z8401 で言うところの「丸めの幅」に付いて私の理解では小数点以下何桁、あるいは10の位、100の位等のどこで丸めるのかの規則かと思っておりますがそれでよろしいのでしょうか。
質問の内容が不十分だったのですが、「有効数字を3桁にする必要」と言うのは別の JIS G3444 STK で表記されている重量の計算基準に基づいているものです。
説明不足で申し訳ありませんでした。

回答No.4

Wendy02

こんにちは。

それは、「JIS丸め」とか、「最近接丸め」というものだと思いますが、

>有効数字4桁目以降を丸める際、
>「二つの隣り合う整数倍がある場合は」「丸めた数値として偶数倍のほうを選ぶ」との規則になります。

（上位から)有効桁数を持ち出している時点で、原則自体は、変則的になっていますね。偶数丸め自体は、Excel VBAには備わっているのですから、それを使わない手はありませんね。ただし、VBA.Round は、マイナス値は入れられません。

有効桁数３となっているのですから、以下のようにしたら良いと思います。

-------------------------------------------------------------
Function VBRound(rng As Range) As Double
　　Dim k As Long　'桁
　　Dim i As Long
　　Dim v As Double
　　Dim sn As Integer
　　Const N As Integer = 2 '有効桁 -1

　　sn = Sgn(rng.Value)　'符号
　　v = Abs(rng.Value)　'絶対値
　　i = Int(Log(v) / Log(10))
　　If i >= N Then
　　　 k = 0
　　Else
　　　 k = N - i '小数点
　　End If
　　If i > N Then
　　　　i = i - N
　　　　VBRound = VBA.Round(Int(v / 10 ^ i), k) * 10 ^ i * sn
　　Else
　　　　VBRound = VBA.Round(v, k) * sn
　　End If
End Function

他の方とそれほど結果は変わりませんが、
0.1234
-13.5512

こういう値で、変わってきます。

お礼

ありがとうございます。
今回はとりあえず関数で作成してみます。
ただし次の段階でVBAも必要になってくるかと思われますので、その際参考にさせていただきます。

回答No.3

mt2008

こんな感じかなぁ……
演算式でやろうと思ったら、かなり長ったらしくなってしまいました。
=ROUND(A1-(0.5*10^(INT(LOG(A1))-2))*(MOD(INT(A1*(10^(3-INT(LOG(A1))))),10)=5)*ISEVEN(MOD(INT(A1*10^(2-INT(LOG(A1)))),10)),2-INT(LOG(A1)))

これは、ユーザ関数を作ってやった方が良いでしょうね。
Function fMarume(Target As Range) As Double
Dim figure As Long
Dim TargetFig As Integer
Dim TargetWork As Long

figure = 3 '有効桁数

TargetFig = Int(Log(Target) / Log(10)) 'ターゲットの桁数を把握
TargetWork = CLng(Target * 10 ^ (figure - TargetFig)) '有効桁数+1桁で整数化　ex)54.25→5425

'4桁目が5で3桁目が偶数なら、4桁目を0に　ex)5425→5420
If (TargetWork Mod 10 = 5) * (Int(TargetWork / 10) Mod 2 = 0) Then
TargetWork = TargetWork - 5
End If

'整数→実数して四捨五入　ex)5420→54.20→54.2
fMarume = WorksheetFunction.Round(TargetWork * 0.1 ^ (figure - TargetFig), figure - 1 - TargetFig)

End Function

お礼

ありがとうございます。
ユーザ関数についてはそれ自体がよくわからないのですがこんな使い方ができるのですね。勉強してみます。

回答No.1

okdeath

JIS Z8401 規則Aはよくわかりませんので少ししらべましたが、恐らくこういった結果でいいのでしょうか？（かなり無理やりですが＾＾；）

Ａ　元となる数字
Ｂ　=+A1*1000
Ｃ　=LEFT(B1,5)
Ｄ　=IF(AND(MID(C1,4,1)="5",RIGHT(C1)="0"),ROUNDDOWN(C1,-2),ROUND(C1,-2))
Ｅ　=LEN(B1)
Ｆ　=D1/10^(8-E1)

Ｆが計算結果です。

　A　　　B　　　　　C　　　　D　　　E　　　　　F
1 54.25 54250 54250 54200 5 54.2
2 79.13 79130 79130 79100 5 79.1
3 108.5 108500 10850 10800 6 108
4 110.95 110950 11095 11100 6 111
5 119.55 119550 11955 12000 6 120
6 207.2 207200 20720 20700 6 207

こんな感じで例題のとおりの結果になります＠＠

３桁をそろえるより、丸めの幅をそろえるもののように見えましたが？
まあ、よくわかりませんがパズル感覚で暇つぶしになりました。参考にどうぞ。

お礼

まっさきにご回答いただきありがとうございます。