【再質問】時間表示について

いつもご教授いただきありがとうございます。

以前質問させていただいたのですが追加でご教授願います。
↓
http://oshiete.goo.ne.jp/qa/12213384.html

画像のようにAA列にX列〜Z4の合計時間を
5分切上げにした時間にする数式をご教授いただいたのでが、この数式に1：15：00のように秒数が00の場合、切り上げをしないように修正したいです。
=ROUNDUP(SUM(X4:Z4)/"0:05:00",0)*"0:05:00"
↓
https://gyazo.com/854e21a7887b06a3562dbed54f3a1820

どのように修正したらよいでしょうか？
何卒ご教授願います。

No.5 ベストアンサー 回答者： goomania 回答日時： 2021/08/12 10:24

No.3,4です。

>そうしますと以下のご教授いただきました数式は問題ないでしょうか？
>=ROUNDUP(SUMPRODUCT(X4:Z4-MOD(X4:Z4,"0:0:1"))/"0:05:00",0)*"0:05:00"
とのお尋ねですが、
>再度ご教授いただきました数式に関しても検証させていただきます。
とおっしゃっておられたのに検証されなかったのでしょうか?
問題ないかどうかを決定するのは、回答者ではなくご質問者自身です。
そもそも秒以下の値が入力されていないはずなのに、何故入力されていたのでしょうか?
例えば、「人為的入力ミスがあった」というならなら修正して、今後入力ミスが発生しない方策をとれば、以前の数式で解決できるはずです。
そうではなく、「勤務開始、終了の時刻は端末のログイン、ログアウトの時刻をシステムが記録・入力しており、勤務時間は秒未満も記録・入力される」というのであれば、システム変更しない限り修正できません。
このような場合は、秒未満の計算をどうするのかルールを決めなくてはいけません。
「秒未満切り捨て」というルールが認められて初めて、「上記の数式で問題ない」といえる訳です。
秒未満が入力されていることが判明した以上、新しいルールになる可能性もあるので、この場合は数式も新しいルールに沿ったものにしなければならず、上記数式では「問題あり」になるかも知れません。
余談ですが、以前私の関わった職場では、30秒未満切り捨て、30秒以上切上げで、端末のログイン・ログアウト時刻を分単位で記録して勤務時間を計算するルールでした。
つまり、極端な話、0秒50以上切上げ、0秒50未満切り捨てという新しいルールも考えられる訳で、この場合上記数式は使えません。
上記数式はあくまでも、X列、Y列、Z列の各時間に秒未満が入力されていたら切捨てるというルールが正しい場合にのみ有効です。

この回答へのお礼

ご返信が遅くなり、申し訳ありません。

データーなのですが、システムから抽出したデーターですので、変更することができません。
秒未満については回答者様の前職のルールを参考に
社内にてルール決めする予定です。

申し訳ありませんが、またルールが決まり次第改めて質問させていただきます。

この度はお手数をおかけして申し訳ありませんでした。

お礼日時：2021/08/18 18:12

No.4 回答者： goomania 回答日時： 2021/08/09 23:02

No.3です。

ご質問者は
>確かに秒数以下では入力されていないので、もう一度確認いたします。
とおっしゃっていますが、実は「秒数以下では入力されていない」ことを確認するのは結構面倒です。
添付画像①をご覧ください。
AA4セルは「１:15:0０」と表示されており、秒数が00なのに切り上げが発生し、AB4セルは「１:20:0０」と表示されています。
実はY4セルに「０:01:31.40」と入力しているのです。しかし、数式バーには「0:01:31」と表示されています。
これは、「０:01:31.40」と入力しても数式バーには秒未満をまるめた値が表示されるからです。
つまり、「秒数以下では入力されていない」ことを確認するには、数式バーに表示される値を検証しても精密な検証にならないことを意味します。
そこで、添付画像➁をご覧ください。
AD列、AE列、AF列にそれぞれX列、Y列、Z列の精査を行う数式を入れています。
AD4セルの数式はX4のセルの入力値を精査するもので、
=TEXT(X4,"hh:mm:ss.000")
という数式です。
これで、秒未満の単位が入力されているかどうかが判ります。
AE4セルの内容は「0:01:31.400」と表示されており、Y4セルに秒未満の値が入力されていることが判ります。
ここまでチェックしてみて、「秒数が00なのに切り上げが発生する原因」が「秒未満の単位で入力されているセルが存在するため」かどうかを判断する必要があります。

この回答へのお礼

こんにちわ。
ご返信が遅くなり申し訳ありません。
また、改めて秒数以下についてご説明頂きましてありがとうございました。

ご教授いただきました数式にて確認しましたところ、やはり秒数以下の数字が御座いました。
大変失礼いたしましたり。

そうしますと以下のご教授いただきました数式は問題ないでしょうか？

=ROUNDUP(SUMPRODUCT(X4:Z4-MOD(X4:Z4,"0:0:1"))/"0:05:00",0)*"0:05:00"

何卒ご教授願います。

お礼日時：2021/08/11 22:12

No.3 回答者： goomania 回答日時： 2021/08/07 23:31

前回のご質問で

=ROUNDUP(SUM(X4:Z4)/"0:05:00",0)*"0:05:00"
という数式を回答した者です。

>この数式に1：15：00のように秒数が00の場合、
>切り上げをしないように修正したい

とのことですが、秒数が00なのに切り上げが発生するという意味だと解釈しました。
X列～Z列の入力値が秒以下の単位で入力されていない限り、上記のような問題は発生しない気がします。
具体的に「このようなケースで上記の問題が発生する」という例をお示しいただけると回答しやすくなります。

ただ、X列～Z列に秒以下の単位が入力されている可能性を疑って、

=ROUNDUP(SUMPRODUCT(X4:Z4-MOD(X4:Z4,"0:0:1"))/"0:05:00",0)*"0:05:00"

という数式に修正してみてください。

これで、問題が解決するようなら、ご質問者は意図していないのに、
「１:15:00.45」のように、秒以下の単位(ex.100分の1秒単位)で入力されているセルがあるということになります。

この回答へのお礼

前回はご教授頂きましてありがとうございました。
確かに秒数以下では入力されていないので、もう一度確認いたします。
お手数おかけいたしました。

また、再度ご教授いただきました数式に関しても
検証させていただきます。
ありがとうございます。

お礼日時：2021/08/09 17:52

No.2 回答者： トモクンアヤチャン 回答日時： 2021/08/06 09:15

ちょっと邪道かと思いますが

=ROUNDUP((sum(x4:z4)-0.000011574)/"0:05:00",0)*"0:05:00"
ではどうでしょうか。
浮動小数点の誤差があるため、割り切れず、切り上がってしまうものと思われます。
-0.000011574は、1秒にわずかに足りない数値です。

No.1 回答者： chiune 回答日時： 2021/08/06 08:51

1秒引けば良いかと

=ROUNDUP(SUM(A1)/"0:05:00"-1/24/60/60,0)*"0:05:00"