☰

Excel スケジュール - 深夜以降の時間をマイナスにならないように計算する

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
Excel スケジュール - 深夜以降の時間をマイナスにならないように計算する

これは私が抱えている問題と使用しているコードのスクリーンショットです。

40 ではなく負の数が表示される理由について、どなたか助けてくださる方がいらっしゃいましたら、どうぞお助けください。本当に感謝いたします。ありがとうございます!

答え1

ファイル > 詳細設定 > 「このブックを計算するとき」セクション (最後までスクロール) で、ブックに対して「1904 データ システムを使用する」が正しく選択されています。

これは、時間を負の数として計算できるようにするために必要です。(これをオフにすると、負の数がすべて # に変わることがわかります)

しかし、たとえば B16 と C16 では、日付なしで単に 18:00 と 01:00 に時刻が入力されます。01:00 - 18:00 は確かに負の時間です。数値として表示すると、およそ -0.71 になります。

解決策は、深夜を過ぎても対応できるように、個々の終了-開始時間の計算を変更することです。したがって、「C16-B16」を「IF(C16-B16<0,C16-B16+1,C16-B16)」に置き換えます。

これを各曜日ごとに行う必要があるため、式がかなり長くなります。

その日の労働時間を表示するために、日付の間に列を追加することを検討してください。そうすれば、合計はそれらのセルの合計のみになります。

編集: 先を越されました!

再度編集: 単位は時間ではなく日数なので、前の投稿の +24 ではなく、私の例のように +1 を使用する必要があります。

最終編集: はるかに短い解決策は、「C16-B16」を「MOD(C16-B16,1)」に置き換えることです。これは、時間の小数部分のみを保持することで機能します。時間の場合、小数点 1 は 24 時間です。

答え2

24 を超える場合の通常の計算式は次のとおりです。

 =EndTime - StartTime +(EndTime < StartTime)

これらすべてを 1 つの数式に結合して、すべての曜日を合計するのは面倒ですが、実行可能です。各ペアを個別にテストする必要があるため、配列入力された数式が必要です。

以下の数式では、すべての EndTime が偶数列にあり、StartTime が奇数列にあるという設定を活用します。

構築された 2 つの配列は 1 列だけ異なることに注意してください。Excel 2007 以降のバージョンでは、ISODD/ISEVEN 関数を使用して ODD/EVEN を直接テストできます。

この式は配列入力:

=SUM(
MOD(COLUMN($C16:$O16),2)*$C16:$O16-
(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16+
((MOD(COLUMN($C16:$O16),2)*$C16:$O16-(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16)<0))
*24

または、ISODD と ISEVEN を使用します。

=SUM(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16+
((ISODD(COLUMN($C16:$O16))*$C16:$O16-ISEVEN(COLUMN($B16:$N16))*$B16:$N16)<0))
*24

または、さらに短くなりますが、MOD 関数を使用して小数部分のみを保持するため、理解しにくくなります。

=SUM(
     MOD(
         ISODD(COLUMN($C16:$O16))*$C16:$O16-
         ISEVEN(COLUMN($B16:$N16))*$B16:$N16,
     1))
*24

に配列入力数式を入力する場合は、セルまたは数式バーに数式を入力した後、 Ctrl + Shift キー叩きながら入力これを正しく実行すると、Excel は数式の周囲に中括弧 {...} を配置します。

スクリーンショットに表示されている行の式を計算しました。結果はあなたの数字を使った場合37.9833です。

答え3

問題は、深夜を過ぎた場合です。たとえば、金曜日、土曜日、日曜日の最初の行を見てみましょう。各日の終了時間は午前 2 時です。たとえば金曜日の場合、時間を減算すると、金曜日の午後 6 時から金曜日の午前 2 時を引いた時間となり、これは午後 6 時より前になります。

マイナスの影響を避けつつ、チャートの見栄えを良く保つには、IF ステートメントを使用する必要があります。金曜日の計算には、次のようにします。

=IF(K3 < J3, K3 - J3 + 24, K3 - J3)

IF ステートメントには 3 つのセクションがあります。

（１）条件

K3 < J3

これは、K3 (終了時間) が J3 (開始時間) より小さいかどうかをチェックします。小さい場合は、負の数を返します (午前 2 時は午後 6 時より小さい)。

（２）条件が真である場合

K3 - J3 + 24

条件が真の場合、Excel はこの式を使用します。発生する可能性のある負の値を打ち消すために 24 を追加するように記述しました。

（３）条件が偽の場合

K3 - J3

条件が真の場合、Excel はこの式を使用します。

まとめ

各日の時間を計算するために使用していた式の代わりに、提供されている IF ステートメントを使用します。

答え4

私も、日付や曜日の情報がないまま真夜中を過ぎる時間を計算するときに同様の問題に遭遇しました。私の開始時間は夕方で、翌日にまたがることもあったので、いくつかの数式を組み合わせて、正しく計算できるようにしました。

=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))

(ここでは、何らかの書式設定上の理由により、小なり記号の後にスペースを追加する必要がありましたが、数式ではスペースを使用していません)

これは単純な if で、私の場合、D4 は終了時間、C4 は開始時間です。終了時間が開始時間より短い場合、午前 0 時を過ぎているはずです。中間のステートメントは、午前 0 時を過ぎたこと (+24) を補正した合計ジョブ時間を示します。最後には、通常の減算だけが必要な場合はジョブ時間が表示されます。"TEXT" と時間形式がないと、+24 の計算が小数値として表示されることがわかりました。

関連情報