Сумма по каждой второй ячейке, если соседняя ячейка не пуста

Question

Я создал расписание в 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 .

Я хотел использовать функцию SUMIF или SUMIFS , но, насколько я понял, он принимает только диапазоны, такие как B1:G1 , но не принимает что-то вроде B1;D1;F1 , то есть пропускает каждый второй столбец. Я понятия не имею, как я могу это сделать, потому что я вообще не эксперт по Excel ... Это то, что я пробовал до сих пор, но это не работает, это только дает мне #VALUE:

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

Это должно означать: суммировать по C, E и G и вычитать сумму B, D и F, если соответствующие ячейки в C, E и G не пусты. Однако круглые скобки вокруг диапазонов, похоже, не работают. Или проблема в другом?

score 2 · Answer 1 · 2011-03-09T19:38:42

Вот что я бы сделал. Если вставить дополнительные столбцы, формула легко корректируется:

Добавьте в первой строке серию 1, -1,1, -1 ... Это используется, чтобы мы знали, является ли это сложение или вычитание.
Справа (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.

Если есть нечетное количество входов / выходов / оно прибавляет последний (самый высокий) период времени, чтобы нейтрализовать его.

score 2 · Accepted Answer · 2011-03-09T19:38:42

Я черпал вдохновение из подхода Мэдбола, но без необходимости в дополнительном ряду. Используйте это для строки 2, скопированной вниз

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

Это добавление лет / вычитает раз , сколько необходимо , но добавляет назад последний раз (игнорируя его) , когда есть нечетное число раз , вставленные.

score 1 · Answer 3 · 2011-03-09T19:38:42

Попробуйте это VBa

Это результат

Это не полностью проверено (кроме того, что показано на снимке экрана)...

Sub Button1_Click()

    startingRow = 2' UPDATE THIS FOR THE STARTING ROW (in this example, I started on row 2 since row 1 is titles)
    numberOfRows = 6 ' UPDATE THIS FOR YOUR NUMBER OF ROWS

    For rowNumber = startingRow To numberOfRows

        Dim isContinuous As Boolean

        isContinuous = True

        Dim inCols(0 To 2) As Integer

        '65 is A, '66 is B etc

        inCols(0) = 66
        inCols(1) = 68
        inCols(2) = 70

        For Each inElement In inCols

            Dim totalTime As Integer

            If (Range(Chr(inElement) & rowNumber).Value <> "") Then
                Dim inDate As Date
                inDate = Range(Chr(inElement) & rowNumber).Value

                Dim outCols(0 To 2) As Integer

                outCols(0) = 67
                outCols(1) = 69
                outCols(2) = 71

                Dim hasOutValue As Boolean

                hasOutValue = False

                For Each outElement In outCols
                    If (inElement < outElement) Then
                        If (Range(Chr(outElement) & rowNumber).Value <> "") Then
                            Dim outDate As Date
                            outDate = Range(Chr(outElement) & rowNumber).Value
                            If (isContinuous = True) Then
                                totalTime = totalTime + DateDiff("h", inDate, outDate)
                            Else
                                totalTime = DateDiff("h", inDate, outDate)
                            End If

                            hasOutValue = True
                            Exit For
                        Else
                            isContinuous = False
                        End If

                    End If
                Next
                If hasOutValue = False Then
                    totalTime = 0
                End If

            End If

        Next
        Range("H" & rowNumber).Value = CStr(totalTime)
    Next rowNumber

End Sub

score 1 · Answer 4 · 2011-03-09T19:38:42

Я не уверен, что полностью понимаю ваш вопрос, но это работает

Я использовал простое утверждение 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))

score -1 · Answer 5 · 2011-03-09T19:38:42

Как насчет того, чтобы попытаться заменить это:

= 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), что даст вам больше реального рабочего времени. В конце концов, вы работаете до сих пор, не так ли?

Сейчас выбран русский

Сумма по каждой второй ячейке, если соседняя ячейка не пуста

5 ответов5

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel microsoft-excel-2010.

Сумма по каждой второй ячейке, если соседняя ячейка не пуста

5 ответов5

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel microsoft-excel-2010.

Похожие