☰

Cálculo de uma soma em uma coluna inteira quando outra coluna inclui os critérios de ano para o cálculo

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
Cálculo de uma soma em uma coluna inteira quando outra coluna inclui os critérios de ano para o cálculo

Tenho uma planilha onde as colunas contém valores da seguinte forma:

Column(A)       B            C       D     E  

NameA        01/02/2010      5       4     5  
NameB        05/06/2011      4       3     2  
NameC        12/11/2010      3       5     3  
NameD        08/14/2012      4       4     4  

-------------------------------------------------  

SUMMARY            2010      8       9     8  
                   2011      4       3     2  
                   2012      4       4     4

e assim por diante...

Quero calcular o total da coluna C onde o Ano na coluna B = 2010 (ou 2011 ou 2012, etc.) e criar uma tabela resumo (possivelmente em outra planilha) acompanhada de um gráfico.

Eu olhei para SUMIF, SUMIFS e SUMPRODUCT.

Gostaria de saber a melhor forma de realizar esta tarefa, de preferência capturando o resumo em uma planilha separada.

Responder1

ATabela dinâmicapoderia fazer o que quiser.

Aqui estão tutoriais para agrupar por data: http://www.databison.com/index.php/pivot-table-quick-tip-group-by-year-quarter-month-and-date-field/ http://www.youtube.com/watch?v=XR8x58Le0HA

Responder2

Definitivamente, uma tabela dinâmica é a melhor opção aqui. Some a coluna C arrastando-a para a área Valores e resuma por linhas para a coluna B arrastando-a para a área Linhas.

Então, o truque para chegar onde você precisa é agrupar suas linhas - como são datas, o Excel lhe dará opções úteis, como por ano, mês, etc. Clique com o botão direito no título de uma linha (ou seja, uma data), escolha Grupo > selecione Ano e desmarque todos os outros para obter apenas um resumo por ano.

Tarefa concluída.

Adicione um gráfico usando o PT como fonte de dados e você deverá obter um resultado simples.

Depois aprenda muitas outras coisas legais que você pode fazer com PTs, como adicionar filtragem, usar segmentações de dados e coisas assim.

PS: considere transformar seus dados de origem em uma tabela se quiser que o PT ainda funcione quando você adicionar mais dados posteriormente.

Responder3

Digamos que os anos estão em B97:B99. Então defina C97para

=SUMIFS(C$1:C$4, $B$1:$B$4, ">="&DATE($B97,1,1), $B$1:$B$4, "<"&DATE($B97+1,1,1))

… ajustando o  1e o  4para refletir a localização dos seus dados. Isso diz para adicionar (soma) os valores na coluna  Cpara as linhas onde o valor na coluna  Bé ≥ 1º de janeiro do ano indicado pelo número na célula,  B97mas < 1º de janeiro do ano subsequente. Em seguida, arraste/preencha até a linha  99e quantas colunas forem necessárias. É claro que isso pode ser colocado em uma planilha separada; basta usar a Sheet1!notação.

Editar

Se você quisesse uma média desses dados, você poderia fazer

=SUMIF(argumentos como acima) / COUNTIF(2º ao 5º argumentos)

Se você quiser lidar com a possibilidade de não haver dados para um determinado ano (portanto, COUNT seria zero, resultando em um erro de divisão por zero), faça

=IFERROR(Fórmula SUMIF/COUNTIF, acima, valor a ser exibido se houver erro)

informação relacionada