☰

Soma cada segunda célula se a célula vizinha não estiver vazia

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Soma cada segunda célula se a célula vizinha não estiver vazia

Criei uma planilha de horas no Excel para controlar meus horários de trabalho. Tudo funciona muito bem, mas tenho um pequeno problema.

Minha planilha tem o seguinte layout ( In=começou a funcionar, Out=parou de funcionar):

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 |       |
...

Agora gostaria de calcular o tempo que trabalhei todos os dias. Isso é fácil:

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

Isso funcionará até mesmo se eu pular o almoço às vezes assim:

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 |       |
...

Mas não funciona durante o dia quando entrei o horário que comecei a trabalhar ( In), mas ainda não inseri o horário que parei de trabalhar ( 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 |       |       |
...

Pois 06.03.2014isso resultará em um tempo negativo.

O que eu gostaria de ter é o horário em que realmente trabalhei pela manhã, ou seja, 04:30em 06.03.2014. Isso significa que preciso incluir apenas as colunas na segunda soma (negativa) onde a coluna vizinha não está vazia. Como posso fazer isso de uma forma geral que ainda permite pular a hora do almoço? Com "maneira geral", quero dizer uma fórmula que também funcionaria para as colunas F, G e possivelmente H, I... Portanto, não deveria ser uma fórmula muuuito longa de IFafirmações.

Eu queria usar a função SUMIFor SUMIFS, mas pelo que entendi ela só aceita intervalos como B1:G1, mas não aceita algo como B1;D1;F1, ou seja, pulando cada segunda coluna. Não tenho ideia de como posso fazer isso porque não sou nenhum especialista em Excel... Foi o que tentei até agora, mas não funciona, só me dá #VALUE:

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

Isso deve significar: somar C, E e G e subtrair a soma de B, D e F se as células correspondentes em C, E e G não estiverem vazias. No entanto, os parênteses em torno dos intervalos parecem não funcionar. Ou o problema é outra coisa?

Responder1

Inspirei-me na abordagem de Madball, mas sem a necessidade de uma linha extra. Use isto para a linha 2 copiada

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

Ele adiciona/subtrai os tempos conforme necessário, mas adicionavoltaro último horário (ignorando-o) quando há um número ímpar de vezes inserido.

Responder2

Aqui está o que eu faria. Se forem inseridas mais colunas, a fórmula será facilmente ajustada:

  1. Adicione, na linha um, uma série de 1,-1,1,-1... Isso é usado para sabermos se é uma adição ou subtração.
  2. Na extrema direita (H no seu exemplo), use esta fórmula: =IF(ISODD(COUNT(B3:G3)),SUMPRODUCT($B$1:$G$1,B3:G3)+MAX(B3:G3),SUMPRODUCT($B$1:$G$1,B3:G3))

Isso funciona no seu exemplo para todos os casos (exceto coisas estranhas como sair duas vezes seguidas): insira a descrição da imagem aqui

Explicação: SUMPRODUCT multiplica cada célula da matriz por seu parceiro na outra matriz e, em seguida, adiciona todas elas. Então, em 3/6, faz 8*-1+12:30*1+13:00*-1+17:30*1+0*-1+0*1.

Se houver um número ímpar de entradas/saídas, ele adiciona de volta o último (mais alto) dos tempos para neutralizá-lo.

Responder3

Não tenho certeza se entendi completamente sua pergunta, mas funciona

insira a descrição da imagem aqui

Eu usei uma instrução if simples

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

Isso funcionará até mesmo em dias parciais (como a linha 4, onde apenas uma tarde foi trabalhada).

Se você adicionasse um terceiro conjunto de entrada/saída, atualize sua fórmula de acordo

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

Responder4

Que tal você tentar substituir isso:

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

com isso:

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

Esta fórmula deve colocar a hora atual em células vazias ($C2,$E2,$G2), o que deve fornecer mais tempo de trabalho real. Afinal, você está trabalhando até agora, não está?

informação relacionada