[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『[談]ROUNDDOWNの疑問』（kazu）
知り合いに聞かれたのですが、
ROUNDDOWN でおかしなケースがあります。
この数値は誤差？バグ？
A B C (=A-B) =rounddown(C,2)
43 42.850 0.150 0.140 <-- 43 42.875 0.125 0.120 43 42.900 0.100 0.100 43 42.925 0.075 0.070 43 42.950 0.050 0.040 <-- 43 42.975 0.025 0.020 43 43.000 0.000 0.000 電卓 43 43.025 -0.025 -0.020 43 43.050 -0.050 -0.040 <-- 43 43.075 -0.075 -0.070 43 43.100 -0.100 -0.100 43 43.125 -0.125 -0.120 43 43.150 -0.150 -0.140 <-- 43 43.175 -0.175 -0.170
Ｃ列をコピーして、Ｅ列に値のみ貼り付けを実行してみてください。 表示された値ではなく、数式バーにて、値を確認してみてください。
これが小数演算の誤差です。
値の近い数値同士の引き算の答えが小数の場合、結構この誤差が多いみたいですね
ichinose
追伸 私が最初に知ったExcel小数誤差
Ａ Ｂ Ｃ
34.8 32.2 =rounddown(a1-b1,1) ---> 2.5 2.6なのに・・・。
ichinose
はー、なるほどこういうことなのですね、
ありがとうございます。（kazu）
A B A-B =rounddown(C,2) ↓Cの値貼りつけに近い値
43 42.850 0.150 0.140 0.1499999999999990 43 42.875 0.125 0.120 0.1250000000000000 43 42.900 0.100 0.100 0.1000000000000010 43 42.925 0.075 0.070 0.0750000000000028 43 42.950 0.050 0.040 0.0499999999999972 43 42.975 0.025 0.020 0.0249999999999986 43 43.000 0.000 0.000 0.0000000000000000 43 43.025 -0.025 -0.020 -0.0249999999999986 43 43.050 -0.050 -0.040 -0.0499999999999972 43 43.075 -0.075 -0.070 -0.0750000000000028 43 43.100 -0.100 -0.100 -0.1000000000000010 43 43.125 -0.125 -0.120 -0.1250000000000000 43 43.150 -0.150 -0.140 -0.1499999999999990
セルの数式でも、文字列型や通貨型にすると大丈夫みたいですね。
=ROUNDDOWN(TEXT(A1-B1,"@"),2) =ROUNDDOWN(YEN(A1-B1,10),2)
表計算ソフトが小数点苦手ってどうなんでしょう・・・ （ｍｏｍｏ）
>文字列型や通貨型にすると大丈夫みたいですね。
ただ、セル上の見た目にもわかる誤差もありますから、注意は必要ですね
簡単な引き算では 実例が示せませんが・・・、
例えば、
=(0.5-0.4-0.1)
結果は、0なのに 0にはならない例も数式によってはありますからねえ
=ROUNDDOWN(TEXT((0.5-0.4-0.1),"@")*10^15,2)
>表計算ソフトが小数点苦手ってどうなんでしょう・・・ これは、同感ですねえ、数値範囲を狭めても電卓と同じ結果がでるモード （書式、データ型等）がほしいですねえ！！
ichinose ーーーーーーーー A-Bの時点の誤差ですね（=ROUNDDOWNによるごさではありません） 内部計算は２進数で計算、小数計算の誤差です
電卓でも誤差は出ますよ （ゴサっチ）
>A-Bの時点の誤差ですね そうですね！！ですから、初回投稿時に >Ｃ列をコピーして、Ｅ列に値のみ貼り付けを実行してみてください。 という記述をしていますが・・・・。
>電卓でも誤差は出ますよ これは、本当なら、私の認識不足です。 出来ましたら、実例を挙げて教えてください。
それと上記の記述の真意は、
Excelをちょっと便利な電卓代わりに使っているユーザーは、 ２進数の誤差なんて事を知らなければ、使えないんじゃあ、電卓でいいや
なんて思っている方は、たくさんいると思います。
私の世代では、中学１年生の数学で２進数というものを教わりました。 私の子供は、中学では教わらなかったそうです。ゆとり何とかかな？
教わった私も１０進小数を２進数で表すと？？ なんて事は、 この業界に入ってから学んだことです。
もっと言えば、Excelの数式の小数計算のメカニズムは、私はまだわかっていません。
だって、VBAとは小数計算の結果が違う場合がありますからねえ！！
こんな難しいことを理解しなくても、電卓と同じ結果を何らかのモード設定で 出せるようにしてくれたらいいのに・・・。
という意味です。
中盤で申し上げた実例 よろしかったら、教えて下さい。
ichinose
1 1 1 1 1 0 32+16+8+4+2=62
2進数 １１１１１０ １０進数は６２
1/2 1/4 1/8 0.1 1 1 0.5 0.25 0.125
2進数0.111は １０進数では０．５＋０．２５＋０．１２５ 0.875です
0.5 1/2 0.25 1/4 0.125 1/8 0.0625 1/16 １ 0.03125 1/32 １ 0.015625 1/64 0.0078125 1/128 0.00390625 1/258 １
10進数の0.1は0.0625＋0.03125＋0.00390625＋0.001953125＋小さい数 0.099609375＋小さい数 0.000110011＋小さい数・・・・2進数
小数は２進数で表すのは複雑ですね 10進数の0.1は３文字で ２進数で表すのには桁数が多くなり限界桁数を超えた場合に 切り捨てた場合どうなります
1/3*3 PCと電卓で比較してみてください
PCと電卓では計算結果の処理に違いはありますよ
（ゴサっチ） 訂正 電卓でも1部の計算結果では誤差は出る事もあります
>２進数で表すのには桁数が多くなり限界桁数を超えた場合に >切り捨てた場合どうなります 内部的には、単に切り捨てではないと思いますよ！！
限界値の桁を丸めていますね！！ 2進数で ０捨１入 かな？ これは、きちんと検証していませんが・・・。
新規ブックにて 以下のコードを実行してみてください。
標準モジュール(Module1）に
'===============================================
Sub main()
Dim idx As Long
Dim cnvnum As Variant
Dim csinstr As String
Dim cdblstr As String
cnvnum = Application.InputBox("数字を入力してください", "単精度・倍精度浮動小数点構造", , , , , , 1)
If cnvnum <> "Boolean" Then
With ActiveSheet
With .Range("a:a")
.ColumnWidth = 40
.HorizontalAlignment = xlCenter
End With
With .Range("b:b")
.ColumnWidth = 10
.HorizontalAlignment = xlCenter
End With
With .Range("c:c")
.ColumnWidth = 40
.HorizontalAlignment = xlCenter
End With
With .Range("d:d")
.ColumnWidth = 100
.HorizontalAlignment = xlLeft
End With
With .Range("a1:e1")
.HorizontalAlignment = xlLeft
.MergeCells = True
.Value = "単精度浮動小数点型"
.Font.Size = 14
.HorizontalAlignment = xlLeft
.Interior.ColorIndex = 34
End With
.Range("a2:e2").Value = Array("説明", "符号", "指数部", "仮数部", "ヘキサイメージ")
.Range("d2").HorizontalAlignment = xlCenter
.Range("e2").HorizontalAlignment = xlCenter
csinstr = floating_img(cnvnum, 0)
.Range("a3").Value = "'値---- " & cnvnum
.Range("b3").Value = "'" & Mid(hextobin(Mid(csinstr, 1, 1)), 1, 1)
.Range("c3").Value = "'" & Mid(hextobin(Mid(csinstr, 1, 1)), 2, 3) & " " & hextobin(Mid(csinstr, 2, 1)) & _
" " & Mid(hextobin(Mid(csinstr, 3, 1)), 1, 1)
.Range("d3").Value = "'" & Mid(hextobin(Mid(csinstr, 3, 1)), 2, 3)
For idx = 4 To 8
.Range("d3").Value = .Range("d3").Value & " " & hextobin(Mid(csinstr, idx, 1))
Next
With .Range("e3")
.Value = "'" & csinstr
.HorizontalAlignment = xlLeft
End With
.Range("a4:e4").Value = Array("ＢＩＴ構成", "'1", "'8", "' 23", "'32")
.Range("e4").HorizontalAlignment = xlCenter
With .Range("a5:e5")
.MergeCells = True
.Value = "倍精度浮動小数点型"
.Font.Size = 14
.HorizontalAlignment = xlLeft
.Interior.ColorIndex = 34
End With
.Range("a6:d6").Value = Array("説明", "符号", "指数部", "仮数部", "ヘキサイメージ")
.Range("d6").HorizontalAlignment = xlCenter
.Range("e6").HorizontalAlignment = xlCenter
csinstr = floating_img(cnvnum, 1)
.Range("a7").Value = "'値---- " & cnvnum
.Range("b7").Value = "'" & Mid(hextobin(Mid(csinstr, 1, 1)), 1, 1)
.Range("c7").Value = "'" & Mid(hextobin(Mid(csinstr, 1, 1)), 2, 3) & " " & hextobin(Mid(csinstr, 2, 1)) & _
" " & hextobin(Mid(csinstr, 3, 1))
.Range("d7").Value = "'" & hextobin(Mid(csinstr, 4, 1))
For idx = 5 To 16
.Range("d7").Value = .Range("d7").Value & " " & hextobin(Mid(csinstr, idx, 1))
Next
With .Range("e7")
.Value = "'" & csinstr
.HorizontalAlignment = xlLeft
End With
.Range("a8:e8").Value = Array("ＢＩＴ構成", "'1", "'11", "' 52", "'64")
.Range("e8").HorizontalAlignment = xlCenter
.Range("a:e").EntireColumn.AutoFit
End With
End If
End Sub
'=====================================================================
Function hextobin(hexstr As String) As String
'指定されたハーフバイト分(一桁）の１６進数を２進数に変換する
'in----hexstr----１６進数（０～Ｆ）
'out---hextobin--変換されたBITイメージ（２進数）ハーフバイト分
Dim idx As Long
Dim wk As Integer
wk = Int("&h" & hexstr)
For idx = 3 To 0 Step -1
If 2 ^ idx And wk Then
hextobin = hextobin & "1"
Else
hextobin = hextobin & "0"
End If
Next
End Function
別の標準モジュール（Module2）に
'============================================================
Option Explicit
Type d_data
dbl As Double
End Type
Type s_data
sng As Single
End Type
Type bd_data
byt(0 To 7) As Byte
End Type
Type bs_data
byt(0 To 3) As Byte
End Type
Function floating_img(ByVal myvalue As Variant, ByVal typ As Long) As String
'指定された型の数値のメモリーイメージをHEXコードで出力する
'in ----myvalue----数値
' typ=0--single 1--double
'out-----ｆloating_img ---メモリーイメージ（HEXコードで）
On Error Resume Next
Const typ_sin = 0
Const typ_dbl = 1
Dim g0 As Long
Dim g1 As Long
Dim dd As d_data
Dim ss As s_data
Dim bb_s As bs_data
Dim bb_d As bd_data
Dim wk As String
Select Case typ
Case typ_sin
ss.sng = CSng(myvalue)
LSet bb_s = ss
Case typ_dbl
dd.dbl = CDbl(myvalue)
LSet bb_d = dd
End Select
If typ = 0 Then
g1 = UBound(bb_s.byt())
Else
g1 = UBound(bb_d.byt())
End If
floating_img = ""
For g0 = g1 To 0 Step -1
If typ = 0 Then
wk = Hex(bb_s.byt(g0))
Else
wk = Hex(bb_d.byt(g0))
End If
If Len(wk) = 1 Then wk = "0" & wk
floating_img = floating_img & wk
Next
End Function
以上です。 mainを実行してみてください。
Inputboxにて、入力が要求されますから、数字を入力して下さい。
例 0.1 0.3 等 入力後、OKボタンを押してください。
結果として、入力された数字をSingle型とDouble型のふたつのデータ型に代入した場合の内部データを2進数および、16進数で表した結果が表示されます。
浮動小数点の規則にしたがっていますから、例えば、0.1を指定したときに 2進数の小数値は、0.0001100110011・・・・・と同じ値が入るわけではありませんが、結果として表示される仮数部は、近い値をしめしています。
詳しくは、浮動小数点で検索してみてください。
例 0.1 を指定すると、
Singleの場合、23BITが仮数部にあたります。
0011の繰り返しですね！！ 本来は、 100 1100 1100 1100 1100 11001 続くのですが、限界があります。24Bit目を0捨1入を行い、
100 1100 1100 1100 1100 1101
という結果になっています。
Doubleの場合、 仮数部の52bitが 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 ・・・・
となり、53bit目は、1なので、 桁上がりして、 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1001 1010
なのでしょうね！！
因みにSingle ４バイト変数 Doubleは、８バイト変数
Excelのセルは、数字は、Double型で処理されています。
上記コードは、だいぶ前に作ったものです。よって、１０進の0.1が ２進数では、循環小数になる ということは承知しています。 プログラマがこれを知らないと小数を扱うと永久ループを起こしそうです。
長いので次投稿に続く
ichinose (ちょっと訂正の訂正)
>1/3*3 PCと電卓で比較してみてください PCというには、Excelでということでよいですか？ =1/3*3 この数式が実例だということでよいですか？
私の手元にあった安そうな電卓では、0.999999999999999 と表示されました。 30年来使っている関数電卓では、1と表示されました。 Excel2002では、=1/3*3 は、1と表示されました。 Windowsに付いている付録の電卓では 1と表示されました。
この0.99999999999 と 1 の違いを誤差と仰っているということですか？
だとしたら・・・・。
これは、10進演算内で発生する誤差ですよね？
2進数の小数演算が引き起こす誤差ではないですね！！
今、ここで問題にしているのは、あくまでもPCが2進数で管理されているが故に 引き起こされている誤差を問題にしているのですよ！！
1/3*3 が0.99999999になるという理屈は小学生でもわかります。 だって、1/3が 0.333333・・・ ということは、小学生で学びますからね！！
今、問題にしているのは、43-42.850が、0.15にはならないという 2進数の小数の実態をしらないと理解できない誤差を 問題視しています。
現在、私の父（83歳）には、何かさせるために（ボケ防止）、 金銭出納帳をExcelで入力してもらっています。
お金ですから、小数はないので問題はありませんが、 四則計算の数式ぐらいは、自分で入力しています。
この父に 43-42.850が0.15にならない理由を説明するのは容易ではありません。 でも、1/3*3が1にならないのは、説明できますし、理解できると思いますよ！！
ichinose
最後に 前投稿で
>１０進の0.1が２進数では、循環小数になる ということは承知しています。 これは、理解していても
>Excelの数式の小数計算のメカニズムは、私はまだわかっていません。 ということなんです。
数式で =0.5-0.4-0.1 は、0を返しますが、 =(0.5-0.4-0.1)は、0にならない なんて例があるからです。
VBAでdouble型で計算させると、 どちらも等しく 0 には、なりません。
Excelでは、何らかの補正をしているらしいのですがね！！
これらことがあるから、 小数は、要注意ですよ と１０年ぐらい前から言い続けています。
ichinose
ハル「でも、最初の式の『0.3-0.2=0.1』という比較では『同じ』になるし、『=0.3-0.2-0.1』は『0』になりますよね… どういうことなんですか？」 エリカ「実は、エクセルには自動的に誤差を取り除く働きがあるの。『0.3-0.2=0.1』という比較では、『0.3-0.2』の答えは『0.1』にものすごく近い数だから同じ数と見なされているわけ」 ハル「そんな機能があったんですか… でも『0.3-0.2-0.1=0』の比較では、誤差は取り除かれないんですか？」 エリカ「うん。『0.1』と比較する場合は『0.1』を基準にして、それよりずっと桁の小さい数は誤差だと判断できるんだけど、『0』と比較する場合は、桁の小さい数があっても『0』よりは桁が大きくなるから、それが誤差だとは判断できなくなるみたい」 ハル「比較する数の大きさによっても違ってくるんですね… 『=0.3-0.2-0.1』が『0』になるのに、『=0.3-0.2-0.1+0』と『=(0.3-0.2-0.1)』が『0』にならないのは、どうしてですか？」 エリカ「『=0.3-0.2-0.1』の場合、計算の中で一番最後に使った『0.1』を基準にして、それよりずっと桁の小さい数は誤差だと判断して『0』にしているみたい。『=0.3-0.2-0.1+0』の場合は最後の『0』を基準にするんだけど、桁の小さい数があっても『0』よりは桁が大きくなるから、それが誤差だとは判断できなくなってしまうの。『=(0.3-0.2-0.1)』のようにカッコで囲んだ場合も、理由はわからないけど、誤差の大きさを自動的に判断することができないみたい
てな説明のHPがありました （ゴサっチ）
循環小数にはてこずりますね（１０進数、２進数に関わらず）
循環小数であろがなかろうが。１０進数だろうが、２進数だろうが、
処理すべきデータが有効桁以上になる事態に陥ったら、 どこかにひずみが出るんじゃないですか？
補正とは、正しい方向へ持って行くものだと思いますが、 何が正しい方向か分かんないのに強行するなら、 それは「どた勘補正」であり、「補不正」にならない保証はないでしょう。
(半平太) 2011/01/05 21:07
なんだか難しいやり取りがされていますね・・・
>Excelのセルは、数字は、Double型で処理されています。 64bit CPUのマシンがだいぶ浸透してきていると思いますが（私のは32bitです・・・） 64bit対応？のエクセル2010とかだとセル計算は128bit浮動小数で計算されるんですかね・・・？ 2010だと計算結果が違うとかありそう・・・？
VBAで128bit浮動小数の変数宣言ってあるんですかね？（調べていません・・・）
本題からはずれていそうなのでスルーしちゃってください・・・
（てつろう）
>数値範囲を狭めても電卓と同じ結果がでるモード >（書式、データ型等）がほしいですねえ！！ と申し上げた思いは、伝わったのでしょうか？
Excel講習では、 >=(0.5-0.4-0.1)は、0にならない これは、皆さんびっくりしてくれます。
こういうことが起こらないようなモードでもあれば、2進数の事など気にしなくていいのに・・・。 という事だったのですが・・・。
>64bit CPUのマシンがだいぶ浸透してきていると思いますが（私のは32bitです・・・） >64bit対応？のエクセル2010とかだとセル計算は128bit浮動小数で計算されるんですかね・・・？
レジスタの大きさと変数の大きさは、直接の関係はないと思いますけどねえ・・・。 16Bitパソコンの時代に倍精度実数型64Bit変数は、ありましたよ！！ >VBAで128bit浮動小数の変数宣言ってあるんですかね？ 進化を止めたVBAの中のVBのほうは、ないと思いますけどねえ！！
ichinose
2010/12/30 Microsoft にWebから問い合わせてみました。（kazu）
Excel の小数点誤差について Excel97からExcel2010でも43 - 42.85 が 0.15 ではなく 0.149999999 になり rounddown ,2 の結果 0.14 になってしまいます。
私たちが議論して、 ○ゼロに近い小数点は誤差があるので気をつけなさい ○整数で計算できるものはできるだけ整数で ○できるだけ大きな数字で割らない という結果がでていますが、Microsoft社として具体的のこうしなさいという対応方法は ないのでしょうか？ [関連サイト] https://www.excel.studio-kazu.jp/kw/20101226202847.html
2011/01/08 Microsoft から問い合わせについての回答がきました。（kazu）
==================== ■ 操作手順 ====================
操作：数式に ROUND 関数を組み合わせる
~~~~~~~~~~~~~~~~~~~~~~~~~ ご記載いただきました Web サイトより、シート上に以下のように入力されているものとします。
A1 B1 C1 (=A1-B1) D1（=rounddown(C1,2)） 43 42.85 0.15 0.14
1. C1 をクリックし、以下の数式を入力します。 =ROUND(A1-B1,2) ※ 入力時に数式エラーと表示される場合は、上記の文字列をコピーしてください。 ※ 上記の数式の右横の「2」は四捨五入する桁数を指定するための数字になります。
2. D1 に「0.15」と正しい計算結果が表示されたことをご確認ください。
本お問い合わせに関しまして、以前のバージョンの内容も含まれますが、以下の 弊社 Web サイトにて情報を公開させていただいております。ご参照いただければ幸いです。
◆文書番号 : 813530 文書タイトル : [XL2003] 小数を使用した計算の誤差について http://support.microsoft.com/kb/813530/ja
◆文書番号 : 78113 文書タイトル : [XL2003] Excel で浮動小数点演算の結果が正しくない場合がある http://support.microsoft.com/kb/78113/ja
◆数値を丸めるhttp://office.microsoft.com/ja-jp/excel-help/HP010342857.aspx?CTT=1現在参照不可
◆文書番号 : 416572 文書タイトル : [XL2003]数値を丸める方法 (ROUND 関数の使用例ほか) http://support.microsoft.com/kb/416572/ja
◆タイトル：ROUND 関数http://office.microsoft.com/ja-jp/excel-help/HP010342858.aspx?CTT=5&origin=HA010342655現在参照不可
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.