Eu tenho um livro de registros de inventário. Parece assim:
Tran Type Txn Date Item Quantity
Opening Balance 6/30/12 Item1 4
Opening Balance 6/30/12 Item2 7
Shipping 7/14/12 Item2 -1
Opening Balance 6/30/12 Item3 3
Shipping 7/2/12 Item3 -1
Opening Balance 6/30/12 Item4 5
Shipping 7/3/12 Item4 -1
Shipping 7/3/12 Item4 -1
Shipping 7/3/12 Item4 -1
Shipping 7/5/12 Item4 -1
Shipping 7/5/12 Item4 -1
Receiving 7/9/12 Item4 10
O ledger, obviamente, apenas marca cada transação em um determinado período: saldo de abertura, expedição, recebimento e devolução.
Eu uso esse livro de registros de inventário para formar uma tabela dinâmica, que se parece com isso:
Sum of Quantity Column Labels
Row Labels 6/30/12 7/2/12 7/3/12 7/5/12 7/6/12 7/9/12
Item1 4 4 4 4 4 4
Item2 7 7 7 7 7 7
Item3 3 2 2 2 2 2
Item4 5 5 2 0 0 10
A função da tabela dinâmica é manter um total em execução dos níveis de estoque para cada item individual, por dia.O formato de tabela dinâmica é útil porque mantém esses totais para cada dia em que o nosso depósito processou transações para QUALQUER item , ao contrário dos dados de origem, que apenas listam as datas das transações como elas aconteceram para aquele item em particular.
Usando esse total de dados em execução na tabela dinâmica, gostaria de acompanhar os dias em que o estoque estava com a quantidade 0 para cada item. Aqui está a parte complicada: gostaria de manter essa contagem como parte da tabela dinâmica, para que a contagem fosse dinâmica quando eu agrupar os dados por mês / trimestre / ano (para que possamos ver o total de dias fora de estoque por item para cada período).
Se eu puder fazer algo com os dados de base que me permitirão ter essa informação de esgotado, isso é ótimo. Se eu puder fazer isso diretamente na tabela dinâmica, melhor ainda.
Outra solução mais direta no céu me permitiria também gerar dados por dias consecutivos esgotados quando um item chegar a zero e, em seguida, ver as médias para isso por período de tempo na tabela dinâmica. Eu sei que a maior parte disso tem que ser feita nos dados de origem, e eu até sei como isso precisa ser feito, mas não consigo juntar tudo em uma fórmula. Primeiro, classifiquei os dados por Data em vez de por Nome do item. Eu criei uma nova coluna para manter a execução de totais de estoque ("Running Total" é o nome da coluna). Quando o total em execução chegar a zero, preciso encontrar a próxima linha em que a data é posterior à transação atual, onde o nome do item é o mesmo e onde o tipo de transação está "recebendo". De lá, precisaria encontrar a diferença entre as datas da transação. Alguém por favor pode me ajudar a criar uma fórmula para isso também?
Por último, como nossos dados contêm apenas os dias em que o warehouse processou transações, estou pensando em saber se há uma maneira de exibir as datas ausentes (ou seja, finais de semana e feriados) exibidas na tabela dinâmica também? Isso não é importante, mas impressionaria o alto escalão.
EDITAR: Depois de ver muitas das respostas aqui, eu abri para tentar resolver este problema através do Access ou SQL Server, e eu repostou a questão em StackOverflow com foco em usando consultas e colunas calculadas para descobrir isso.
Dito isto, eu ainda estou muito aberto para resolver isso através do excel, se alguém tiver mais alguma ideia! Como posso obter os totais em execução nos meus dados de origem, estou imaginando se posso fazer o Esgotado com instruções if em uma nova coluna.
Se o total em execução for Zero, preciso localizar a transação seguinte para o mesmo item, em que o tipo de transação é "Recebendo" e, em seguida, retornar a data da próxima transação para a coluna. Não sei como combinar todos esses elementos em uma fórmula, no entanto. Depois que eu retornar a data da próxima transação de Recebimento, eu poderia subtrair a Data de Txn da linha Atual pela diferença em dias.
Ficaria assim:
Tran Type Txn Date Item Quantity Stock Out of Stock
Opening Balance 6/30/12 Item4 5 5
Shipping 7/3/12 Item4 -1 4
Shipping 7/3/12 Item4 -1 3
Shipping 7/3/12 Item4 -1 2
Shipping 7/5/12 Item4 -1 1
Shipping 7/5/12 Item4 -1 0 4 Days
Receiving 7/9/12 Item4 10 10
De lá, eu poderia fazer outra tabela dinâmica com totais por item, ou algo assim.