Excel 入力規則と手入力の差の怪

質問

入力規則を使用したセルがあります（型は「h:mm」です）。
次のような15分単位の時間のリストです（型は「h:mm」です）。
「18:00」「18:15」「18:30」
リスト以外の入力もできるように、エラーメッセージタブのチェックははずしてあります。
ここでリストより「18:15」と選択し、セルの型を「数値」に変更した場合、
セルの表示は「0.76041666666666800・・・・」となります。

別途同様のセルを使用し、リストからは選択せず「18:15」を直接手入力し、セルの型をセルの型を「数値」に変更した場合、
セルの表示は「0.76041666666666700・・・・」となります。
リストから選択した場合、手入力した場合で値が変わってしまうのはなぜでしょうか。
この後、別の式でこの値を利用したいのですが結果が変わってしまい困っています。
何か方法はないでしょうか。

回答No.1

keithin · ベストアンサー

＞入力規則を使用したセルがあります（型は「h:mm」です）。
＞次のような15分単位の時間のリストです

この入力規則の「元の値の一覧を作成したセル範囲」が別にあって，入力規則ではそのセル範囲を指定したと思います。

その元の値のセルで，同じように表示を変えて確認してみると，実は0.760416666666668で作成されていた事が確認できます。
その元の値一覧で現在「18:15」と表示されているそのセルに，改めて「18:15」と手で打ち込み直してみると，今度はちゃんと「0.760416666666667」で投入できる事が確認できます。

一般に時刻の値を「オートフィルドラッグで」つるつるっと引っ張って作成すると，ご相談のような状況がほぼ間違いなく発生します。

作成例：
A2に　15:00
A3に　15:15
と記入し，A2:A3を選んで下向けにオートフィルドラッグする

作成例：
B2に　15:00　を記入する
B3に　15:15　を記入する
B4に　15:30　を手で記入する
B5に　15:45　を手で記入する
　：
　以下ひたすら手で時刻を記入する

作成例：
C2に　=TIME(15,15*ROW(A1)-15,0)
を記入し，下向けにつるつるっとコピーする

で，３列の数字を表示を変えて一致するしないを目視します。
=A2=B2　=B2=C2　=C2=A2
の３列の式をずらりコピーして，どれとどれが一致する(TRUEになる)か計算で確認させてもいいですね。

お礼

詳細な解説をありがとうございます。

回答No.2

Cupper

んーと、15桁までしか計算できない 「 スプレッド方式 」 の表計算の宿命ですね。

本来は、0.7604166666666666666666666666666… とずっと６が続く無限小数になるのですが、
有限数として16桁目を四捨五入する関係で、0.76041666666666700 になってしまいます。
それを時間形式に直すことから、今度は2進数による 微少数誤差 が加わり、0.76041666666666800 になると考えられます。

実質問題はないと思いますが、大きな問題になるのでしたら面倒ですが、
「時」「分」「秒」それぞれの単位毎に計算をすることをお奨めします。

お礼

ご回答ありがとうございます。参考にさせていただきます。