☰

인접한 셀이 비어 있지 않으면 매 두 번째 셀마다 합계를 냅니다.

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
인접한 셀이 비어 있지 않으면 매 두 번째 셀마다 합계를 냅니다.

근무 시간을 추적하기 위해 Excel에서 근무 시간표를 만들었습니다. 모두 잘 작동하지만 작은 문제가 하나 있습니다.

내 시트의 레이아웃은 다음과 같습니다( 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. 즉, 인접한 열이 비어 있지 않은 두 번째(음수) 합계에 해당 열만 포함해야 합니다. 점심 시간 건너뛰기를 지원하는 일반적인 방법으로 이 작업을 어떻게 수행할 수 있습니까? "일반적인 방법"이란 열 F, G 및 H, I에도 적용되는 수식을 의미합니다. 따라서 이는 너무 긴 IF명령문 수식이어서는 안 됩니다.

SUMIFor 함수를 사용하고 싶었지만 SUMIFS제가 이해한 한 이 함수는 와 같은 범위만 허용 B1:G1하지만 와 같은 것은 허용하지 않습니다 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을 더합니다. 이것은 더하기인지 빼기인지를 아는 데 사용됩니다.
  2. 맨 오른쪽(예: H)에서 다음 공식을 사용합니다. =IF(ISODD(COUNT(B3:G3)),SUMPRODUCT($B$1:$G$1,B3:G3)+MAX(B3:G3),SUMPRODUCT($B$1:$G$1,B3:G3))

이것은 모든 경우에 대해 귀하의 예에서 작동합니다 (연속 두 번 떠나는 것과 같은 이상한 일은 제외). 여기에 이미지 설명을 입력하세요

설명: 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행)도 수행합니다.

세 번째 입/출력 세트를 추가하려면 그에 따라 수식을 업데이트하세요.

=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)에 입력하므로 실제 작업 시간이 더 많이 소요됩니다. 결국, 당신은 지금까지 일하고 있는 거죠?

관련 정보