☰

隣接するセルが空でない場合は、2つおきのセルを合計します。

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
隣接するセルが空でない場合は、2つおきのセルを合計します。

勤務時間を記録するために Excel でタイムシートを作成しました。すべてうまく機能していますが、小さな問題が 1 つあります。

私のシートのレイアウトは次のようになります ( In= 動作を開始、Out= 動作を停止)。

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 | 13:30 | 13:45 | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 | 17:30 |       |
...

ここで、毎日働いた時間を計算したいと思います。これは簡単です。

= SUM($C2; $E2; $G2) - SUM($B2; $D2; $F2)

時々昼食を抜く場合でも、この方法は有効です。

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 |       |       | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 | 17:30 |       |
...

しかし、日中は、仕事を始めた時間 ( In) を入力したものの、仕事を終えた時間 ( Out) をまだ入力していないため、機能しません。

A          | B     | C     | D     | E     | F     | G
-----------+-------+-------+-------+-------+-------+------
Date       | In    | Out   | In    | Out   | In    | Out
01.03.2014 |       |       |       |       |       |       (weekend)
02.03.2014 |       |       |       |       |       |       (weekend)
03.03.2014 | 08:00 | 12:00 | 12:45 | 17:00 |       |
04.03.2014 | 08:45 |       |       | 17:45 |       |
05.03.2014 | 08:15 | 12:30 | 13:00 | 18:00 |       |
06.03.2014 | 08:00 | 12:30 | 13:00 |       |       |
...

これは06.03.2014マイナスの時間をもたらすことになるからです。

私が知りたいのは、午前中に実際に働いた時間、つまり です04:30。06.03.2014つまり、隣接する列が空でない列のみを 2 番目の (負の) 合計に含める必要があります。昼食時間をスキップする一般的な方法でこれを行うにはどうすればよいですか? 「一般的な方法」とは、列 F、G、場合によっては H、I にも機能する数式を意味します。したがって、IFステートメントの長い数式にする必要はありません。

SUMIF私はor関数を使いたかったのですSUMIFSが、私が理解している限りでは、 のような範囲しか受け入れずB1:G1、 のようなもの、つまり 2 列おきにスキップするものは受け入れませんB1;D1;F1。私は Excel の専門家ではないので、どうすればそれができるのか全くわかりません... これまで試したことはこれだけですが、うまくいかず、 という結果しか返されません#VALUE。

= SUM($C2; $E2; $G2) - SUMIF(($C2; $E2; $G2); "<>"; ($B2; $D2; $F2))

これは、C、E、G を合計し、C、E、G の対応するセルが空でない場合は、B、D、F の合計を減算することを意味します。ただし、範囲を囲む括弧が機能していないようです。それとも、問題は他に何かありますか?

答え1

私はMadballのアプローチからインスピレーションを得ましたが、余分な行は必要ありません。これをコピーした2行目に使用します。

=C2+E2+G2-B2-D2-F2+MOD(COUNT(B2:G2),2)*MAX(B2:G2)

必要に応じて時間を加算/減算しますが、戻る奇数の回数が挿入されている場合は、最新の時間（したがって無視されます）。

答え2

私が行うことは次のとおりです。さらに列を挿入する場合、数式は簡単に調整できます。

  1. 1 行目に 1、-1、1、-1... という数列を追加します。これは、加算か減算かを判断するために使用されます。
  2. 右端 (例では H) では、次の数式を使用します。 =IF(ISODD(COUNT(B3:G3)),SUMPRODUCT($B$1:$G$1,B3:G3)+MAX(B3:G3),SUMPRODUCT($B$1:$G$1,B3:G3))

これは、あなたの例ではすべてのケースで機能します（2 回続けて退出するなどの奇妙な場合を除く）。 ここに画像の説明を入力してください

説明: SUMPRODUCT は、配列内の各セルを他の配列内の対応するセルで乗算し、すべてを加算します。したがって、3/6 では、8*-1+12:30*1+13:00*-1+17:30*1+0*-1+0*1 が実行されます。

入力/出力の数が奇数の場合、最後の（最も高い）回数を加算して中和します。

答え3

あなたの質問を完全に理解しているかどうかはわかりませんが、これは機能します

ここに画像の説明を入力してください

簡単なif文を使用しました

=SUM(IF(C2<>"",C2-B2,0),IF(E2<>"",E2-D2,0))

これにより、部分的な日（午後のみ作業された行 4 など）でも処理されます。

3番目のin/outセットを追加する場合は、それに応じて数式を更新します。

=SUM(IF(C2<>"",C2-B2,0),IF(E2<>"",E2-D2,0),IF(G2<>"",G2-F2,0))

答え4

これを置き換えてみてはどうでしょうか:

= SUM($C2; $E2; $G2)

これとともに：

= SUM(IF($C2="";TEXT(NOW(); "hh:mm");$C2); IF($E2="";TEXT(NOW(); "hh:mm");$E2); IF($G2="";TEXT(NOW(); "hh:mm");$G2))

この数式は、現在の時刻を空のセル ($C2、$E2、$G2) に入力し、実際の作業時間をより正確に表示します。結局のところ、今まで作業してきたのですよね?

関連情報