[[20100204014643]]　『並べ替えの間違い？』（ぷっか）　ページの最後に飛ぶ

『並べ替えの間違い？』（ぷっか）

以下のような表があります。
（表１）

 A     B     C     D
 107   17    40.8  66.2
 95    12    28.8  66.2

 A=数値入力
 B=数値入力
 C=B*3*0.8
 D=A-C

 表１をD>Aの優先度で昇順ソートした場合に、
 本来ならばDが同値なのでAの昇順に並び替って以下表２のようになると思いますが、
 うまく並び変わらず表１のままになってしまいます。

（表２）

 A     B     C     D
 95    12    28.8  66.2
 107   17    40.8  66.2

 切り分けとして実施したのは、
 C、もしくはDの数値も手入力するとうまく表２のように並び替ります。

 C、Dが計算式の場合に並び替えがうまくいかない状況なのですが、
 原因や対応策について心当たりありましたらご教授いただけますでしょうか？

[エクセルのバージョン]
Excel2007
[OSのバージョン]
Windows vista

 D1:D2の計算結果が同じと見なされていないのでしょう。
 =D1=D2　としたら　True　が返りますが、=D1-D2=0　とすると False が返ります。
 矛盾した結果のように思いますが・・・

 並べ替えのときにD1とD2は同値と判定されずに、D2が小さいと判定されているのでしょう。
 小数第1位まででよいなら、=Round(A1-C1,1)と丸めてやれば期待された順になるようです。
 　　　(Hatch)

 二つが同じにみなされない理由は下記を参照してください。

 「“達人”芳坂和行氏に学ぶ、エクセル(Excel)「演算誤差」対策講座」

http://pc.nikkeibp.co.jp/pc21/special/gosa/

 (独覚)

Hatchさん、独覚さん
お世話になります。

小数点計算時の微小誤差によるものということで、
ROUND関数で対応することで解決しそうです。

ありがとうございました。

 解決後ですが、ＶＢＡを知っている人限定で　もう少し掘り下げてみると・・・、

 新規ブックの標準モジュールに

 '===========================================================================
 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
 Sub Test()
    Range("A1:D2") = [{107, 17,"=B1*3*0.8","=A1-C1";95,12,"=B2*3*0.8","=A2-C2"}]
    MsgBox "Ａ，Ｂ列に数値、Ｃ列は、=B1*3*0.8　という数式のフィル操作、" & vbCrLf & vbCrLf & _
           "Ｄ列は、=A1-C1という数式のフィル操作 の結果はご覧のように表示されています" & vbCrLf & vbCrLf & _
           "Ｄ１とＤ２の値を数式で比較すると　=D1=D2 の結果は" & Evaluate("d1=d2") & "となりますが" & vbCrLf & vbCrLf & _
           "このＤ１とＤ２のデータをＶＢＡで比較すると" & vbCrLf & vbCrLf & _
           "range(""d1"").value=range(""d2"").value の結果は   " & (Range("d1").Value = Range("d2").Value) & vbCrLf & vbCrLf & _
           "何らかの小数誤差補正をしているExcelの数式とは結果が異なりますね。"
    MsgBox "この時、同じ66.2のセルＤ１とＤ２の値の内部データ(8バイトの倍精度）を見てみると" & vbCrLf & vbCrLf & _
           "D1(16進) =" & floating_img([D1].Value, 1) & "    D2(16進) =" & floating_img([D2].Value, 1) & "となり" & _
           vbCrLf & vbCrLf & _
           "最右端のビットのOff（Ｄ１）とON（Ｄ２）の差があります"
    MsgBox "次にＤ１とＤ２の値だけコピー&貼付して再度内部データを比較してみると・・・・・"
    With Range("d1:d2")
       .Value = .Value
    End With
    MsgBox "D1(16進) =" & floating_img([D1].Value, 1) & "    D2(16進) =" & floating_img([D2].Value, 1) & vbCrLf & vbCrLf & _
           "と今度は、同じ値になりますね！！"
 End Sub
 '===============================================================================
 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

 Testを実行してみて下さい。

 小数誤差を隠れ蓑にしたバグではないでしょうかねえ！！
 補正するなら、この辺りもきちんとしてほしいなあ。

 こんなバグ、Excelなんてただの計算の道具と思っている善良な市民はたまったもんじゃないですよねえ！！

 ichinose

 実験結果を報告します。
  　　　　　　　　(D1とD2が)
 Win98　XL97　　　同じに変わる
 WinXP　XL2002　　同じに変わる
 Vista　XL2007　　違ったまま

 >こんなバグ、Excelなんてただの計算の道具と思っている善良な市民はたまったもんじゃないですよねえ！！

 回答者レベルじゃないと対応にアタフタします。

 回答者だって、人から聞かれれば、分かった風な解説できますが、
 自分の実務ではミスっちゃいます。(こんなバカなこと、常時覚えて居られませんから) 
 しかも、バージョンによっても結果が違っちゃったら、職場じゃ 混乱間違いなし。

 (半平太) 2010/02/04 23:23

 　↑
 つまんない書き込み部分を自己削除しました
 (半平太) 2011/09/15 11:55

コメント返信：

[ 一覧(最新更新順) ]

[[20100204014643]] 『並べ替えの間違い？』（ぷっか） ページの最後に飛ぶ

[[20100204014643]]　『並べ替えの間違い？』（ぷっか）　ページの最後に飛ぶ