Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4882 / 13646 ツリー ←次へ | 前へ→

【53746】条件式 多摩川 08/2/5(火) 17:08 質問[未読]
【53747】Re:条件式 Jaka 08/2/5(火) 17:30 発言[未読]
【53748】Re:条件式 多摩川 08/2/5(火) 17:30 質問[未読]
【53749】Re:条件式 多摩川 08/2/5(火) 17:43 質問[未読]
【53755】Re:条件式 りん 08/2/5(火) 21:11 発言[未読]
【53756】Re:条件式 VBWASURETA 08/2/5(火) 21:17 発言[未読]
【53758】Re:条件式 ichinose 08/2/5(火) 21:48 発言[未読]
【53759】Re:条件式 追伸 ichinose 08/2/5(火) 21:52 発言[未読]
【53760】Re:条件式 訂正 ichinose 08/2/5(火) 22:11 発言[未読]
【53764】Re:条件式 多摩川 08/2/6(水) 9:06 お礼[未読]
【53852】Re:条件式 ichinose 08/2/11(月) 22:52 発言[未読]
【53855】Re:条件式 多摩川 08/2/12(火) 8:50 お礼[未読]

【53746】条件式
質問  多摩川  - 08/2/5(火) 17:08 -

引用なし
パスワード
   度々お世話になっています。

ユーザー関数を作成するに当たり、条件として 1 ≦ x ≦ 3 の時に計算結果を得て、それ以外の時は「エラー」と表示させようと考えています。

そこで

function calcu(x) as double
if x >=1 and x <= 3 then
  '計算式(省略)
else
  calcu="エラー"
endif
end function

としました。

今、A列に A1 = 1, A2 = A1 + 0.1 ・・・ A21 = A20 + 0.1 = 3 とあります。
=calcu(A21) と関数を定義すると計算結果が得られるはずですが、なぜか 0 が帰ってきます。
計算式に進まないようです。(計算結果が 0 になるということではないです。)
=calcu(3) と直接 3 を入力するとちゃんと結果が得られます。

A21 の表示形式を標準にして値を確認しても、3.000001 のような誤差はないようです。
こうなるものなのでしょうか?

【53747】Re:条件式
発言  Jaka  - 08/2/5(火) 17:30 -

引用なし
パスワード
   これエラーになりませんか?

>function calcu(x) as double
> if x >=1 and x <= 3 then
>  '計算式(省略)
> else
>  calcu="エラー"
> endif

calcuは、数値型のdouble
これに文字を入れるとなると....。

ついでに
Function calcu(x As Range) As Double

【53748】Re:条件式
質問  多摩川  - 08/2/5(火) 17:30 -

引用なし
パスワード
   ▼Jaka さん:
こんにちは。
レス、ありがとうございます。

>これエラーになりませんか?
>
>>function calcu(x) as double
>> if x >=1 and x <= 3 then
>>  '計算式(省略)
>> else
>>  calcu="エラー"
>> endif
>
>calcuは、数値型のdouble
>これに文字を入れるとなると....。
確かにそうですね (^^ヾ

しかし、as double をなくして variant に変更しても、だめなようです。
ちなみに、round 関数を絡めると結果が得られます。
実際には四捨五入してしまうと都合が悪いので何とか round 関数なしで結果が得られないでしょうか?

【53749】Re:条件式
質問  多摩川  - 08/2/5(火) 17:43 -

引用なし
パスワード
   色々試してみましたが、CSng(x) とするとうまいこと流れました。
とりあえずは何とかなりそうです。

ただ、色々試した中で疑問が・・・
A21=6/2 とした場合は、ちゃんと結果が得られました。
○:A21=6/2  =3
×:A21=A20+0.1=3
なぜなのでしょうか?
やはり、内部的に誤差があるのでしょうか?

【53755】Re:条件式
発言  りん E-MAIL  - 08/2/5(火) 21:11 -

引用なし
パスワード
   多摩川 さん、こんばんわ。

>ただ、色々試した中で疑問が・・・
>A21=6/2 とした場合は、ちゃんと結果が得られました。
>○:A21=6/2  =3
>×:A21=A20+0.1=3
>なぜなのでしょうか?
>やはり、内部的に誤差があるのでしょうか?

浮動小数点がらみでしょうねぇ。
htt p://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=49418;id=excel

【53756】Re:条件式
発言  VBWASURETA  - 08/2/5(火) 21:17 -

引用なし
パスワード
   ▼多摩川 さん:

こんばんは。

ちょっと気になったので以下のような
関数を作って確認してみました。


Function calc(x As Range) As Double
  If x.Value >= 1 And x <= 3 Then
    calc = x.Value + 0.1
  Else
    MsgBox "エラー"
  End If
  
End Function

Sub main()
  MsgBox calc(ActiveSheet.Cells(20, 1))  'A20のセルを渡す。 A20(値:2.9) + 0.1 = 3
End Sub


シートのA20セルには計算式:A19 + 0.1 = 2.9が入っています。

で上記のソースで確認しましたらちゃんと3という結果を
得られましたけど違います?

【53758】Re:条件式
発言  ichinose  - 08/2/5(火) 21:48 -

引用なし
パスワード
   こんばんは。

標準モジュールに
'===================================================
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-----floating_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

(以前は、一度ファイルに落としていたんですが、
 Lsetで出来ることをとあるサイトで教えていただきました)


として、

内部形式まで調べてみます。


問題のセルA21に対して

適当なセルに

=floating_img(A21,1)




=floating_img(3,1)

との結果の違いを確認してください。

セルA21は、

4008000000000004

数値の3の場合は、

4008000000000000

かなり深い桁で値が違ってますよね?

ご推察どおり、小数の誤差です。


この結果の違いが影響しています。
Singleに変換すると、値が切られているので(Singleは4バイト)

この場合は、等しくなります。

数式内で比較すると、何らかの補正がされているみたいですが!!

Function test(x) As Variant
  If Evaluate("and(" & x & ">=1," & x & "<=3)") Then
    test = "ok"
  Else
    test = "ng"
    End If
End Function

=test(a21)
適当なセルで上記の関数を試してみてください

【53759】Re:条件式 追伸
発言  ichinose  - 08/2/5(火) 21:52 -

引用なし
パスワード
   この手の質問では、必ず 言ってたんだ

とにかく、小数は 要注意ですよ

【53760】Re:条件式 訂正
発言  ichinose  - 08/2/5(火) 22:11 -

引用なし
パスワード
   >
>数式内で比較すると、何らかの補正がされているみたいですが!!
>
>Function test(x) As Variant
>  If Evaluate("and(" & x & ">=1," & x & "<=3)") Then
>    test = "ok"
>  Else
>    test = "ng"
>    End If
>End Function
>

=test("a21")

>適当なセルで上記の関数を試してみてください

【53764】Re:条件式
お礼  多摩川  - 08/2/6(水) 9:06 -

引用なし
パスワード
   おはようございます。

やはり、少数の誤差なのですね!
単純な 0.1 刻みの足し算でこんなことが起きるなんて・・・

以後、少数の取り扱いには注意するようにします。
皆さま、お時間を割いていただきありがとうございました。

【53852】Re:条件式
発言  ichinose  - 08/2/11(月) 22:52 -

引用なし
パスワード
   こんばんは。

達人さん日替わりQ

hp.vector.co.jp/authors/VA016119/xlq/q10211.html

を見て改めてこのご質問とちょっと前のご質問だった

www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=53662;id=excel

この事が気になり検証してみました。

但し、達人さん日替わりQは一日で替わってしまうので
ログがあるのかなあ?

申し上げたかったのは、

数式の小数誤差がコピー&値の貼り付けで丸められていると言う事実です。

多摩川さん御提示のコードも

Sub sample1()
  Range("a1").Value = 0.1
  Range("a2:a20").Formula = "=a1+0.1"
  
  MsgBox "a20=2 の判定は " & (Range("a20").Value = 2)
  
  With Range("a20")
    .Value = .Value 'ここで値に変換すると
    End With
  
  MsgBox "再度、a20=2 の判定は " & (Range("a20").Value = 2)
 
End Sub

本来の結果をもたらします。

又、映画館スタッフさんが問題にしたコードも

Sub main2()

  Dim time1 As Date, time2 As Date
 
  time1 = CDate("23:01")
  time2 = CDate("23:00")
 
  'time1 を5分単位に切り下げ
  time1 = WorksheetFunction.Floor(time1, CDate("0:05"))
  With Range("a1")
   .Value = time1
   .Value = .Value '値を改めて代入しなおすと・・・
   time1 = .Value '値を代入しなおしたデータを変数に移して
   End With
  If time1 = time2 Then
    Debug.Print "ok"
  Else
    Debug.Print "no"
  End If
 
End Sub

これだとちゃんとok になります。

それでも小数は要注意なんですが、おもしろいなあと思ったもので
投稿しました。

【53855】Re:条件式
お礼  多摩川  - 08/2/12(火) 8:50 -

引用なし
パスワード
   ▼ichinose さん:
おはようございます。

>    .Value = .Value 'ここで値に変換すると
これは、すごく助かります。
いくら少数を注意するといっても、見落とししてしまう可能性もあるなぁ〜と思っていましたが、この一文の追加でかなりの見落としミスが無くせそうです。

解決後に貴重な情報を投稿頂きありがとうございました。

4882 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free