条件付き書式で塗りつぶしたとき、１セル足りなかったり多かったりする。

Question

まえがき

※当方、Googleスプレッドシート使用者です。
スプレッドシートのタグが見当たらないので、Excelタグを付けさせていただいています。

本文

こちらの質問の関連です。
M4からDD4にかけて、１セルごとに0:00から23:45まで、15分刻みの時間を入力し、
条件付き書式のカスタム数式に「=AND(M$4>=$D6,M$4<$E6)」と入力しました。
（D6が開始時刻、E6が終了時刻です。）

すると、対象時刻のセルが塗りつぶされます。
できたできたと思って喜んでいたのですが、塗りつぶされた表をよく見ると、
たま～に、塗りつぶしが１セル足りなかったり、１セル多かったり、
なんだか挙動が不安定であることに気が付いてしまいました。
なにか原因があるのだと思うのですが、見当がつきません。
ただ、「０（というか、0:00）」の扱いがキーになっているような気はします。
とはいえ、「０」が絡まないところでも、１セル足りなかったり多かったりすることもあるので、
ほかにも原因があるのかもしれません。

塗りつぶしの表は、概ね正確なのですが、
ちょっと間違ってることに気が付いてしまったので、解決したいです。
なにか、原因として思い当たることはありますでしょうか。
あるいは、条件付き書式というのはそういう、ざっくりしたものなのでしょうか。

#～解決法～
Excelで時刻部分をオートフィルで作って（A1=0:00 A2=A1+time(0,15,0)）、
その値をコピーしてスプレッドシートの時刻部分にペーストすると、
どうやら期待通りの結果となるみたいです…？
Excelの質問として質問しているのに、不覚にも盲点でした…。
その時刻部分だけExcelからいただけばよかったみたいです。

KSwordOfHasteさんの回答および、コメント欄のやりとりは参考になります。

Answer 1

Excelあるあるなんだけど、0のつもりが0.5になってたりして、しかもそれが書式のせいで見えないとか。

その足りないとか余計とかのセルの時刻表示をべつの書式にしてみればなんかわかるんでは

Comments

[koromo_t]

さっそくの回答、ありがとうございます！
扱っているのは時刻だけなので、小数点が現れたりということはなく、
おそらく原因ではないのではないかと…？

「0:00」と入力したところのセルの塗りつぶしが足りないのは、
「0:00」を「０」と認識されているからだと、
まだ納得がいくのですが（これも解決したいことですけども）、
ただの「20:15」と入力したところが、塗りつぶしの表では「20:30」と認識されていたり、
わりと謎です。。。

[y_waiwai]

まずは、そのおかしいというセルの時刻のセルの書式設定がどうなってるか見てみましょう。
「日付」になってるでしょうか
また、改めてそこの時刻をきっちり入力し直してみましょう

[koromo_t]

「日付」というより、「時間」ですが、書式は大丈夫かと思いますし、
改めて時刻を打ち込んでみても、やはり現状のままですね…。

[y_waiwai]

Excelはいろんな機能を詰め込んでいるために、どの機能がそこに影響してるかってのが分かりづらくなってしまってます
どうしてもうまくいかない場合は、あっさり新規のシートの上で組み直してみるってのもいいかもしれません。

[koromo_t]

回答ありがとうございます！
別の方にも解説していただきまして、解決することができました！
アドバイスくださり、ありがとうございました！！

Answer 2

y_waiwaiさん回答と同じ内容なのですが・・・

日付も時間も内部では浮動小数点数（即ち単なる数値）であることは覚えておくとよいかも知れません。例えば

値	書式=hh:mm
0.1	02:24
0.1001	02:24

表示上同じにみえたとしても「内部的な値（数値）」が違うのです。当然ながら上記を比較すれば0.1の方が「小さい」という結果になります。内部の値を確認するには「日付」の形式で値を見ようとしても違いがつかめないことも普通にあるわけですね。

ノート：数値として表示してみましょう。違いがわかる場合もあります。

また数値として表示しても違いがわからないぐらい微妙な差の場合もあります。

Formula	書式=標準
=1	1
=1+1E-9	1

これも同様に上の方が「小さい」という結果になります。

ノート：値の大小を(=A1=B2のような)formulaを使って確認してみてください。その結果が内部的な値の大小関係を正確に示したものになります。

---

どうしたらこうした期待外の結果をなくせんるんでしょうか・・・この辺りは一言で言い難いややこしさがあるように思います。

15分おきの時刻をセルに設定しておられると思いますが、具体的にどのようにして設定しておられるのでしょうか？

"hh:mm"という書式を設定しておいて最初の2つのセルへ9:0,9:15と入力しそれをコピペして19:00まで拡張するなんてやっておくと、各々のセルの値は後から時刻を手入力で12:15などとしたときの値と完全に一致するように思います。

一方VBAで15分(数値としては0.0104166666666667)を累算していたりすると例え"13:15"と表示されてたとしてもセルへ直接13:15と入力した場合の値(X)とはずれてしまうこともあるでしょう。(そんな奇妙なVBAは組まないだろうとは思いますが・・・)

y_waiwaiさんが「Excelはいろんな機能を詰め込んでいるために、どの機能がそこに影響してるかってのが分かりづらく」とコメントされてますが、自分もそう思います。VBAやformulaで値を設定したとき、それが期待通りの値になるかどうかについて「様々な落とし穴」がある気がします。

Comments

[koromo_t]

回答ありがとうございます！
15分おきの時刻を設定したセルには、
M4=0:00 N4=M4+time(0,15,0) として、N4を右へオートフィルしていました。
お話を聞いて、もしやと思って0:00からすべて手打ちで打ち直したところ、
期待通りの結果を得ることができました。

このことがわかってから、TIME関数がよくなかったのかと思い、
M4=0:00 N4=0:15 O4=N4+$N$4 として、O4を右へオートフィルしてみたのですが、
これでは期待外の結果となりました。
当方Googleスプレッドシートを使用しているので、
最初の２つのセルへ0:00,0:15 と入力して、それらをコピペというのはできない模様ですが、
おそらく、0:00,0:15 と入力してコピペと、やっていることとしては同じなのではと考えます。
あれ‥違うのでしょうか…。

どうもオートフィルを使うと期待外の結果となってしまうようです。
これはもう、手打ちするしかないということなのでしょうか…。

何はともあれ、無事に解決してとても嬉しいです！
アドバイスくださり、ありがとうございました！！

[KSwordOfHaste]

正直、自分もどうやったらどういう結果になるか予想がつきにくいVBのDate型なので、"9:00"のように一旦文字列を生成してDateへ変換するようなVBAを書くのが一番確実に思えました。セルへ入力する際には結局文字列として入力しそれをExcelがDateへ変換するのでしょうから、こうすれば同じ値になるような気がします。

[y_waiwai]

１日は１．０で時や分は小数点になるからそれ整数倍にしたら誤差が出るってはなしなのね。
どーすりゃいいんだろうねえ

[KSwordOfHaste]

多くの言語では日付時間を整数で扱うのでもっと単純に考えることができるのですが・・・
浮動小数点数だと神経使いますね。

[koromo_t]

なるほどー、小数を整数倍にしたら誤差が出る…、これですね。これが原因だったんですね。
だからこそ、セルの塗りつぶしが増えたり減ったりするのが、
予期できないタイミングだったのかもしれません。

そしていまさらなのですが、、、
Excelで時刻部分をオートフィルで作って（A1=0:00 A2=A1+time(0,15,0)）、
その値をコピーしてスプレッドシートの時刻部分にペーストすると、
どうやら期待通りの結果となるみたいです…？
Excelの質問として質問しているのに、不覚にも盲点でした…。
その時刻部分だけExcelからいただけばよかったみたいです。

あと、コメント欄では、当方スプレッドシート使用の件、ちらっと書いてますが、
質問の本文には書いていませんでしたね。すみませんでした…。
これは問題かもしれないので、追記しておきました。

みなさん、いろいろと教えてくださり、ありがとうございました！！

[koromo_t]

あと、この質問にはまったく関係ない話なのですが、、、
y_waiwaiさん、以前https://teratail.com/questions/117589のコメント欄でお見かけしたのですが、
https://www.sejuku.net/blog/の本文って、個別記事にはありますよね…？
どういうことかなと、機会があれば聞いてみたいと思っていたのですｗ

[y_waiwai]

時刻の参照部分にオートフィルの値をコピーする、誤差が出るならその参照にもそれをコピーするってのは一つの解決法ですね

ましかし、誤差はプラスだけじゃなくてマイナスにも出るんで、xx:15 となるはずのところが xx:14 となる場合もあると思われるので注意してください。
＃まあ、１日分の表なら出ないかも知れませんが

> 本文って、個別記事にはありますよね…？
WebアプリやらイマドキのIT技術周りのことでぐぐると、よくこのブログが上位に出てくるんですが、なんかデザインがハイソで広告あり画像あり行間開いてて、こっちは調べもんしてるんだから、手っ取り早く内容を、とスクロールしたらそこにはインタビュー記事やら広告やらしか出てこない、だったと思いますｗ（今から振り返ってみれば、ですが）
こういうことが２，３回続いたもんで、ここが出てくると避けるようになった、ということでしたね

まあ、ハード屋からこの道に入ったもので、コードや図面がないと目に止まらない、という私の悪い癖なんでしょうね

[koromo_t]

はい！ スプレッドシートのオートフィルは使えませんでしたが、
そんなときは、Excelのオートフィルが使えるということがわかったので、
今後、この知識が必要になったときには悩まずに済みそうで、嬉しいです！
な、なるほど…、気をつけますね…、とはいえ、誤差に気付けるかどうかわかりませんが…(笑)。

あっ…なるほど、皮肉的な…、
野暮なことを聞いてしまったようです、すみません…！
いや、私には普通の記事のように見えるものですから、どうしたのかなと思って…ｗ
なるほど、ハード屋…、機械を作るかたなのですね！
私にとっては、あまり知らない世界ですけども…ｗ
発言の意図、教えてくださり、ありがとうございました！