Ajuda para somar horas

[Kadu Du]

Galera, bom dia!

 

Minha dúvida está quebrando minha cabeça! Sempre realizei as somas de forma fácil com a função SOMARPRODUTO! Na planilha em anexo, na aba dados, montei as fórmulas para duas condições:

1- Somar as quantidades correspondentes ao nome e critério que desejo;

2- Somar as quantidades de horas correspondente ao nome e critério que desejo. Porém, as horas não estão somando! Elas estão ficando sempre 0. Não sei o porque está dando erro!

 

Agradeço desde já!!!

 

Eduardo

CONTROLE DE = QTDE x HORAS.xlsx

[Visitante]

Experimente a fórmula abaixo em 'B56' e arraste para baixo.

=SOMA(DESLOC(JANEIRO!$B$7;0;CORRESP(A56;JANEIRO!$B$5:$M$5);CONT.VALORES(DESLOC(JANEIRO!$B$7;0;CORRESP(A56;JANEIRO!$B$5:$M$5);30))))

Para utilizar a fórmula acima para os 12 meses você vai precisar editar a fórmula e colocar o nome de cada mês, isto seria necessário também na fórmula que você está tentando utilizar (SOMARPRODUTO), que me parece, não é aplicável a este caso.

A solução abaixo é mais prática. Veja se acha viável.

Na planilha 'DADOS', linha 55, os títulos de colunas são JAN, FEV, MAR, ... e os nomes das outras planilhas são JANEIRO, ...
Se você igualar os títulos daquelas colunas com os nomes das planilhas, ex. coloque os nomes nas planilhas de JAN, FEV, MAR, ..., então coloque a fórmula abaixo em 'B56', arraste para baixo e para a direita. A fórmula vai funcionar para os 12 meses.

=ÍNDICE(INDIRETO(B$55&"!$38:$38");1;(LIN(B2)-2)*2+3)

[Kadu Du]

Osvaldomp, boa tarde!

 

As duas fórmulas funcionaram perfeitamente!

você poderia tirar algumas dúvidas minhas quanto a:

 

1- porque que a fórmula SOMARPRODUTO não está sendo aplicável nesse caso? Na minha planilha original, realizo a soma das horas utilizando a mesma dia a dia e funcionou! O Patropi me ensinou outra forma para somar com datas utilizando a função TEXTO. Mas não consegui utilizar nesse caso!

 

2- Pode me dar uma explicação das fórmulas acima??? Vai me ajudar muito no meu aprendizado!!!

 

Muito obrigado!!

 

Eduardo

[Visitante]

1- porque que a fórmula SOMARPRODUTO não está sendo aplicável nesse caso?

Olá, Eduardo.

A observação abaixo foi extraída da Ajuda da função SOMARPRODUTO do Excel:

"Os argumentos da matriz devem ter a mesma dimensão."

Em outras palavras: as matrizes referenciadas na função SOMARPRODUTO devem possuir igual quantidade de elementos.

Esta fórmula que você colocou em 'B56'

=SOMARPRODUTO((JANEIRO!B5:M5=DADOS!A56)*(JANEIRO!B6:M6=JANEIRO!C6)*(JANEIRO!B7:M11))

referencia três matrizes: 'B5:M5' com 12 elementos (12 células), 'B6:M6' com 12 elementos e 'B7:M11' com 60 elementos. A terceira matriz diverge das outras duas quanto à quantidade de elementos, aí não atende à exigência da igualdade na quantidade de elementos, e por essa razão a função SOMARPRODUTO não é aplicável neste caso.

Cabe observar que o intervalo 'B7:M11' precisará ser ajustado na fórmula a cada novo lançamento, assim deverá ser ajustado para:  'B7:M12', 'B7:M13', ..., até 'B7:M37' (???).

 

 

2- Pode me dar uma explicação das fórmulas acima??? Vai me ajudar muito no meu aprendizado!!!

 

=SOMA(DESLOC(JANEIRO!$B$7;0;CORRESP(A56;JANEIRO!$B$5:$Microsoft5);CONT.VALORES(DESLOC(JANEIRO!$B$7;0;CORRESP(A56;JANEIRO!$B$5:$Microsoft5);30))))

A fórmula acima segue a construção da sua SOMARPRODUTO e foi feita como alternativa. Ela pode ser utilizada para obter o resultado que você deseja.

Nessa fórmula o intervalo 'B7' até 'B37' (correspondente a 'B7:M37' na sua fórmula) não precisará ser ajustado a cada novo lançamento pois a função CONT.VALORES faz a leitura do intervalo de forma dinâmica. Porém ela apresenta um inconveniente na sua construção: traz o nome da planilha origem (JANEIRO), e isso obriga a editar a fórmula para ajustar o nome do mês ao ser arrastada para a direita.

 

=ÍNDICE(INDIRETO(B$55&"!$38:$38");1;(LIN(B2)-2)*2+3)

A sua fórmula SOMARPRODUTO tenta calcular uma soma que já está disponível na linha 38 da planilha origem. A fórmula acima capta aquela soma disponível.

Funciona assim: lê o nome da planilha origem da informação na linha 2 (por isso é necessário que nessa linha os títulos sejam iguais aos nomes das planilhas) e capta a soma já disponível na linha 38 daquela planilha origem.

A fórmula pode ser arrastada para baixo, cobrindo assim todos os funcionários, e pode ser arrastada para a direita, cobrindo assim todos os meses, sem necessitar de qualquer edição para ajustes.

 

Sugiro que você verifique o funcionamento de cada função das fórmulas separadamente, inclusive utilizando o recurso Fórmulas / Avaliar Fórmula, e retorne se restar alguma dúvida.

 

[Kadu Du]

Osvaldo, boa tarde!

 

Apenas mais DUAS dúvidas:

 

1- Apenas não entendi o final da fórmula LIN(B2)-2)*2+3). O porque de *2+3?

 

2- como faço para desempatar os valores das horas? Ex: Funcionário 1 = 1:00:00, funcionário 2 = 1:00:00. Irei fazer um ranking dos 10+ e iria colocar em ordem do maior para o menor. O Patropi uma vez me ensinou uma fórmula de desempate, mas era com números; ( = A1-LIN(A1)/1000).

Depois disso consigo montar um ÍNDICE + CORRESP + MAIOR

 

Grato

 

Eduardo

[Visitante]

1- Apenas não entendi o final da fórmula LIN(B2)-2)*2+3). O porque de *2+3?

 

 

Essa expressão corresponde ao argumento da função ÍNDICE que determina a coluna a ser retornada. As colunas de interesse a serem retornadas são as colunas 'C', 'E', 'G', ..., nas quais estão os totais das horas da linha 38.

A fórmula =(LIN(B2)-2)*2+3 colocada em qualquer célula e arrastada para baixo vai gerar a sequência 3, 5, 7, ...,  esses números gerados correspondem às colunas referidas acima

 

2- como faço para desempatar os valores das horas? Ex: Funcionário 1 = 1:00:00, funcionário 2 = 1:00:00. Irei fazer um ranking dos 10+ e iria colocar em ordem do maior para o menor. O Patropi uma vez me ensinou uma fórmula de desempate, mas era com números; ( = A1-LIN(A1)/1000).

Depois disso consigo montar um ÍNDICE + CORRESP + MAIOR

Caso o Mestre Patropi decida não responder aqui, sugiro que você encerre este tópico e abra outro com essa nova questão