☰

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

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 и вычесть сумму B, D и F, если соответствующие ячейки в C, E и G не пустые. Однако скобки вокруг диапазонов, похоже, не работают. Или проблема в чем-то другом?

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

Связанный контент