[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日時を配列に代入して出力すると0:00のデータが前日になる』（う）
例えば、セルA1に「2019/1/1 20:00」と入力し、セルA2に「=A1+1/24」を入力し、セルA7までコピーします。
そうすると、セルは以下のようになります。
2019/1/1 20:00 （2019/1/1 20:00）
2019/1/1 21:00 （=A1+1/24）
2019/1/1 22:00 （=A2+1/24）
2019/1/1 23:00 （=A3+1/24）
2019/1/2 0:00 （=A4+1/24） ←注目
2019/1/2 1:00 （=A5+1/24）
2019/1/2 2:00 （=A6+1/24）
※()は入力値
これを、以下のように配列に一括で代入して、その配列をセルに出力すると以下のようになります。
Sub 日付がおかしくなる()
Dim var As Variant
var = Range("A1:A7").Value
Range("C1").Resize(UBound(var, 1)).Value = var
End Sub
＜結果＞
2019/1/1 20:00
2019/1/1 21:00
2019/1/1 22:00
2019/1/1 23:00
2019/1/1 0:00 ←注目
2019/1/2 1:00
2019/1/2 2:00
上記結果のように2019/1/2 0:00 が、2019/1/1 0:00になってしまいます。
配列の中身をローカルウィンドウで確認した場合、#2019/01/02#と正しく入っています。
なお、「=A1+1/24」を「=$A$1+(ROW()-1)/24」で作成した時は正常に「2019/1/2 00:00」と表示されます。
また、以下のように、配列に代入した値をDouble型にした場合は正常に「2019/1/2 00:00」と表示されます。
Sub 日付はおかしくならない()
Dim var As Variant
var = Range("A1:A7").Value
For i = 1 To UBound(var, 1)
var(i, 1) = CDbl(var(i, 1))
Next
Range("C1").Resize(UBound(var, 1)).Value = var
End Sub
日付型を扱う場合の何かあるある的(?)なものがあるのでしょうか。
なお、上記は再現のために簡単にしていますが、実際には日時に対する複数のデータがあり、そのデータから計算した結果を最終的に別シートに一括出力するという処理を行っています。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
A列の表示形式を標準にするだけでも正しくコピーされるようだ。 （ねむねむ） 2019/06/25(火) 11:28
たぶん小数の演算誤差の丸め方がDate型とDouble型で違うのでしょう。
A5の値は、整数にすると「43467」で、 実際、他のセルに「=A5=43467」なんて式を入れてみても「TRUE」が返りますし、 A5をコピーして別セルに値だけ貼り付けても数式バー上は「43467」に見えます。 でも、厳密には「43467」ではありません。
=DELTA(A5,43467)
で検算してみれば戻り値はゼロで「A5と43467は等しくない」となります。
日付型としての扱いをやめるか、 A2 =--TEXT(A1+1/24,"yyyy/mm/dd hh:mm") の様にして誤差を修正しておく。 みたいな事が必要なのではないかと思います。
（白茶） 2019/06/25(火) 11:34
なるほど！
最初に表示形式を変更してから配列に入れれば、日付型ではなくDouble型で取り込まれるので大丈夫なのですね。こちらの方がスッキリしました。
Sub 日付がおかしくならない2()
Dim var As Variant
Range("A1:A7").NumberFormat = "標準"
var = Range("A1:A7").Value
Range("A1:A7").NumberFormat = "YYYY/M/D hh:mm"
Range("D1").Resize(UBound(var, 1)).Value = var
End Sub
ありがとうございます！
（う） 2019/06/25(火) 11:35
日付を入力するのは自分とは限らないので、日時の入力での対応は難しいと考えていました。
また、データ件数が多い（時間データが1年分×数十項目）ので、一括で配列に入れてしまいたく、入れた後に対応するしかないかなと思っていました。
DELTA関数初めて知りました。
勉強になりました。
ありがとうございます！
（う） 2019/06/25(火) 11:45
Sub 日付がおかしくならない3()
Dim var As Variant
var = Range("A1:A7").Value2
Range("D1").Resize(UBound(var, 1)).Value = var
Range("D1").Resize(UBound(var, 1)).NumberFormat = "YYYY/M/D hh:mm"
End Sub
Value2を使えば、日付ではなくシリアル値をゲットできることを思い出しました。
この場合、出力結果もシリアル値になってしまうので、出力後に日時の表示形式に変更しました。
日時の場合は、Value2で取得し日付型は使用しないことにしました。
（う） 2019/06/25(火) 14:32
これは小数演算誤差の問題と言うより、DATE型特有のバグです。
演算誤差は、有効桁数15〜16桁辺りで常に発生している問題
このバグは、日付が変わる辺りだけ、0.5秒レベルで起きる問題
※日付部分は繰り上がらず、時刻部分だけが繰り上がる(つまり0:00になる）状態でシート上の値に変換される問題 結局、ほぼ1日分マイナスとなる。
（半平太） 2019/06/25(火) 16:18
DATE型のバグなのですね。。。
バグということが分かりスッキリしました。
ありがとうございます！
（う） 2019/06/25(火) 16:47
＞これは小数演算誤差の問題と言うより、DATE型特有のバグです。
ちょっと不正確だったような気がします。
DATE型のバグと言うより、 RangeオブジェクトのValueプロパティ(＝プログラムの一種）が、DATE型を引数に受け取った時、 次の日付までに0.5秒以内の不足があるものは正しく繰り上げ処理できないバグ、と言った方がよさそうです。
また、小数演算誤差も絡んではいます。
A5セルの日付は、2019/1/2の値よりわずかに小さいです。
A6セル =(43467-A5)/0.5^37 とすると
行 _______A_______ 1 2019/1/1 20:00 2 2019/1/1 21:00 3 2019/1/1 22:00 4 2019/1/1 23:00 5 2019/1/2 0:00 6 1
つまり、A5セルは 0.5の37乗だけ 2019/1/2より小さい。
幾ら小さくても、0.5秒以内の不足には違いないので 次の日への繰り上がり処理が行われるハズだが、 Valueプロパティによる馬鹿げた変換処理が行われる。
（半平太） 2019/06/25(火) 22:49
詳細なご説明ありがとうございます。
時刻は少数値で持っている関係上、少数誤差により気づかない値の違いがあるだろうと思い、比較する時はFormatや日付・時刻関数、秒(10進)に変換等で合わせるようにしていましたが、そんなところにバグがあるとは思いもしませんでした。
とても勉強になりました。
（う） 2019/06/26(水) 10:00
Function TO_DATE(v As Double) As Date
TO_DATE = CDate(v)
End Function
Sub Test2()
[A:C].NumberFormatLocal = "yyyy/mm/dd hh:mm:ss.000"
[A1] = "2019/6/26 23:59:59.49"
[A2] = "=A1+1/24/60/60/1000"
[A2:A20].FillDown
[B1:B20].FormulaR1C1 = "=TO_DATE(RC[-1])"
[C1:C20] = [B1:B20].Value
[A:C].Columns.AutoFit
End Sub
>Valueプロパティによる馬鹿げた変換処理 おお! ホントだー!
Date型に罪は無かったのですね。 半平太さんありがとうございました。(便乗して御礼)
負のゼロはゼロに変換してくれるのに こんな大きな落とし穴があったとは...
（白茶） 2019/06/26(水) 11:25
Sub 日付がおかしくならない4()
Dim var As Variant
var = Range("A1:C7").Value
Range("D1").Resize(UBound(var, 1)).Value2 = var
Range("D1").Resize(UBound(var, 1)).NumberFormat = "YYYY/M/D hh:mm"
End Sub
Rangeの日時データを扱う時は、Value2を使用するよう留意しようと思います。
みなさま、ありがとうございました。
（う） 2019/06/26(水) 11:32
今の私には「Valueプロパティの不具合であれば、配列には2019/1/2と入っていたのだから、出力する時にValueプロパティを使わなければいいのかも？」くらいの発想しかできませんでした。
DELTA関数といい、こういった確認をする術・発想力も身に付けていきたいと思いました。
ありがとうございました。
（う） 2019/06/26(水) 13:15
この内容を実際のプログラムに適用したところ、気づいた事があったので追記しておきます。
実際のプログラムは、出力結果をジャグ配列で作成し、最後にWorksheetFunction.Indexで2次元配列に変換していたのですが、WorksheetFunction.Index実行後に同現象が発生していました。
配列の型が、Index前はDate型、Index後はString型に変わっていました。
こちらに記載した再現プログラムでも、配列にValueプロパティでセット後、イミディエイトウィンドウで
「?worksheetfunction.Index(var,5,1)」を実行したところ、「2019/1/1 0:00:00」（正しくは2019/1/2）と返ってきました。
ワークシート関数だからかなぁと思っています。
そんなわけで、出力する時にValue2にするだけでいいやと安易に考えていたのですが、結局、配列に入れる時点でValue2にしておき、Data型は使わないという結論になりました。
本当は誤差が無くなるようプログラムで再セットするのがベストだとは思いますが。。。
以上、ご報告まで。
（う） 2019/06/26(水) 15:54
＞WorksheetFunction.Index実行後に同現象が発生していました。
こちらでも確認しました。
こんなのでも、発生しますね。
↓
Debug.Print Application.Text(var(5, 1), "yyyy/m/d hh:mm") '2019/1/1 00:00
Debug.Print Format(var(5, 1), "yyyy/m/d hh:nn") '2019/1/2 00:00 ↑ これはならない。不思議。
近づきたくない型ですね。
うさん、ご報告ありがとうございました。
（半平太） 2019/06/26(水) 16:32
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.