Gostaria de ajuda para um cenário em que estarei trabalhando com Excel. Qualquer ajuda será muito apreciada.
Terei um intervalo cada vez maior de datas para itens específicos e preciso fazer uma planilha Excel para determinar o número médio de dias entre eles por item. Basicamente, este é um exemplo simplificado de como pretendo tabular os dados:
Item Code | Date
A.ITEM | January 15, 2017
B.ITEM | January 16, 2017
A.ITEM | January 22, 2017
C.ITEM | January 25, 2017
A.ITEM | January 31, 2017
C.ITEM | February 2, 2017
B.ITEM | February 12, 2017
B.ITEM | February 24, 2017
C.ITEM | March 7, 2017
Criarei então outra tabela que exibirá a duração média entre as datas por item. Imagino que ficará assim:
Item Code | Average Life Span
A.ITEM | 9 days
B.ITEM | 20.5 days
C.ITEM | 21.5 days
Qual fórmula eu precisaria para tornar a segunda tabela possível? Já faz algum tempo que estou quebrando a cabeça e, como não tenho muita familiaridade com funções de Data no Excel, ainda não sei como. É mesmo possível?
Obrigado!
Responder1
Observe que a média das diferenças é apenas (max-min)/contagem: (d1-d2) + (d2-d3) + (d3-d4) +... = d1-dn
Com isso, você pode usar uma fórmula como
(MAX(d1:dn)-MIN(d1:dn))/COUNT(d1:dn)
Isso colocaria todas as datas em um só pote, então você precisa filtrar adicionalmente por seus códigos - em vez de simplesmente MAX(d1:dn)usar MAX(IF(a1:an=code,d1:dn,0)como uma fórmula de matriz. Adicione ifs semelhantes para MIN e COUNT (ou use COUNTIF); observe que para MIN, o valor else não pode ser 0, mas precisa ser algo muito grande.
Responder2
Neste exemplo, o código do item está na coluna A, a data está na coluna B e adiciono novos dados. A linha 1 são os títulos.
Primeiro, você precisará classificar seus dados sempre que adicionar uma nova linha, com uma classificação em duas camadas:
- primeiro por código do item
- segundo por data
Em seguida, na coluna à direita de Data, adicione um cálculo de Tempo entre e arraste/preencha para baixo:
=IF(A2=A1,B2-B1,"")
Depois, para calcular a média dos tempos. Liste os códigos dos itens A, B, C em outro lugar - coloquei-os na coluna F no meu teste. Ao lado de A, insira e arraste/preencha:
=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)
Eu obtive:
- R: 8
- B: 19,5
- C: 20,5
(para A: 22 de janeiro a 15 de janeiro são 7 dias, 31 a 22 de janeiro são 9 dias, a média é de 8 dias)