Excel, datas médias entre transações?

Question

Eu tenho um conjunto de dados de inventário com o qual estou jogando e acertei uma parede tentando descobrir algo que quero fazer.

O conjunto de dados tem uma coluna de nome (nome do item; por exemplo, "martelo" ou "serra"), uma coluna de contagem (quantos estão em estoque) e uma coluna de tempo de transação. (E há outras colunas, incluindo o tempo de transação dividido em dia, mês, ano, hora e dia da semana).

Para cada nome de item de inventário exclusivo, quero encontrar os dias médios entre

• compre para comprar,
• compra para venda e
• venda para venda.

As transações podem ser identificadas como compras ou vendas pelo fato de o estoque ("contagem") aumentar ou diminuir em relação à transação anterior para o mesmo item.

    

Answer 1

O Excel provavelmente não é a melhor maneira de fazer isso, mas eu consegui. Veja alguns dados de amostra que inventei e os resultados das minhas fórmulas:

(A imagem acima é um link para uma imagem maior, que mostra as colunas auxiliares ocultas.) Eu assumi a existência de uma coluna Date que era de um nível aceitável de granularidade. Aqui estão as fórmulas:

AA2: =VLOOKUP($A2, $A3:$C$22, 2, FALSE)                * Note: $A3, not $A2.
AB2: =$AA2-$B2
AC2: =VLOOKUP($A2, $A3:$C$22, 3, FALSE)
AD2: =IF($AB2>0, $A2,  "")
AE2: =IF($AB2>0, $AC2, "")
AF2: =IF($AB2<0, $A2,  "")
AG2: =IF($AB2<0, $AC2, "")
AH2: =IF($AB2>0, IFERROR(VLOOKUP($AD2, $AD3:$AE$22, 2, FALSE), ""), "")
AI2: =IF($AB2>0, IFERROR($AH2-$AC2,""), "")
AJ2: =IF($AB2>0, IFERROR(VLOOKUP($AD2, $AF3:$AG$22, 2, FALSE), ""), "")
AK2: =IF($AB2>0, IFERROR($AJ2-$AC2,""), "")
AL2: =IF($AB2<0, IFERROR(VLOOKUP($AF2, $AF3:$AG$22, 2, FALSE), ""), "")
AM2: =IF($AB2<0, IFERROR($AL2-$AC2,""), "")

 X2: =AVERAGEIFS($AI$2:$AI$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, ">0")
 Y2: =AVERAGEIFS($AK$2:$AK$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, ">0")
 Z2: =AVERAGEIFS($AM$2:$AM$16, $A$2:$A$16, "="&$W2, $AB$2:$AB$16, "<0")

Suponho que você tenha alguma forma de preencher a coluna W com os nomes de itens de inventário exclusivos. 22 representa a última linha que possui dados.

Explicação:

• As colunas cor-de-rosa, AA:AC , mapeiam para a próxima transação do item identificado nas colunas A:C da linha atual. Por exemplo, AA2:AC2 mapeie para A4:C4 . A coluna Delta mostra a alteração no inventário entre essas duas transações. Você poderia definir AB2 como =VLOOKUP($A2, $A3:$C$22, 2, FALSE) - $B2 e excluir a coluna AA ; Eu acredito que este caminho é mais claro.
• As colunas laranja ( AD:AG ) dividem os pares de transações das colunas AA:AC em compras ( AD:AE ) e vendas ( AF:AG ), com base no fato de o delta do inventário ser positivo ou negativo e replicar o item nome da coluna A .
• As colunas verdes claras ( AH:AI ) mapeiam cada compra para a próxima compra do mesmo item e calcular o número de dias entre eles. Então, por exemplo, AH2 é AE8 (que é C10 ) e AI2 é AE8-AE2 (ou seja, C10-C4 ).
• As colunas verdes mais escuras ( AJ:AK ) mapeiam cada compra para a próxima venda do mesmo item e calculam o número de dias entre elas.
• As colunas azuis ( AL:AM ) mapeiam cada venda para a próxima venda do mesmo item e calculam o número de dias entre elas.