У меня есть инвентарный регистр. Это выглядит так:
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
Регистр, очевидно, просто отмечает каждую транзакцию за определенный период: начальное сальдо, отгрузка, получение и возврат.
Я использую этот регистр инвентаря, чтобы сформировать сводную таблицу, которая выглядит следующим образом:
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
Функция сводной таблицы состоит в том, чтобы хранить промежуточные суммы уровней инвентаря для каждого отдельного предмета в день.Формат сводной таблицы полезен, потому что он поддерживает эти итоговые значения для каждого дня, когда наш склад обрабатывал транзакции для ЛЮБОГО предмета, в отличие от исходных данных, которые просто перечисляют даты транзакций, как они произошли для этого конкретного товара.
Используя эти промежуточные итоговые данные в сводной таблице, я хотел бы отслеживать дни, когда для каждого товара было 0 запасов. Вот сложная часть: я хотел бы сохранить этот счет как часть сводной таблицы, чтобы счет был динамическим, когда я группирую данные по месяцам / кварталам / годам (чтобы мы могли видеть общее количество дней на складе по элементам за каждый период).
Если я могу что-то сделать с базовыми данными, которые позволят мне получить эту информацию об отсутствии на складе, это здорово. Если я смогу сделать это непосредственно в сводной таблице, это даже лучше.
Еще одно более эффективное решение позволит мне также генерировать данные для последовательных дней отсутствия на складе, как только товар достигнет нуля, а затем просматривать средние значения за этот период в сводной таблице. Я знаю, что большая часть этого должна быть сделана в исходных данных, и я даже знаю, как это нужно сделать, но я не могу связать это вместе в формуле. Сначала я отсортировал данные по дате, а не по имени элемента. Я создал новый столбец, чтобы сохранить итоги инвентаризации ("Итоговое значение" - это имя столбца). Как только итоговое итоговое значение достигнет нуля, мне нужно найти следующую строку, где Дата будет позже, чем Текущая транзакция, где Имя элемента будет таким же, и где тип транзакции будет "получение". Оттуда мне нужно будет найти разницу между датами транзакций. Может кто-нибудь, пожалуйста, помогите мне придумать формулу для этого?
И наконец, поскольку наши данные содержат только дни, когда склад обрабатывал транзакции, мне интересно, есть ли способ отобразить отсутствующие даты (т.е. выходные и праздничные дни) в сводной таблице? Это не главное, но это поразило бы верхушку.
РЕДАКТИРОВАТЬ: После просмотра многих ответов здесь, я решил попытаться решить эту проблему также с помощью Access или SQL Server, и я повторно разместил вопрос в StackOverflow с акцентом на использование запросов и вычисляемых столбцов, чтобы понять это. из.
Тем не менее, я все еще очень открыт для решения этой проблемы с помощью Excel, если у кого-то есть еще идеи! Так как я могу получить промежуточные итоги в моих исходных данных, мне интересно, могу ли я выполнить Нет в наличии с помощью операторов if в новом столбце.
Если промежуточный итог равен нулю, то мне нужно найти самую следующую транзакцию для того же элемента, где тип транзакции - "Получение", а затем вернуть дату этой следующей транзакции в столбец. Однако я не уверен, как объединить все эти элементы в одну формулу. После того как я верну дату следующей транзакции получения, я смогу вычесть Txn Date текущей строки для разницы в днях.
Это будет выглядеть так:
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
Оттуда я мог бы сделать еще одну сводную таблицу с итогами по элементам или что-то подобное.