Как объединить сводные таблицы, а также создать собственную формулу в ее полях - Excel 2016

Question

Я готовлю систему отслеживания материалов для моего производства бумаги из гофрированного картона с использованием MS Excel 2016 для Windows. По сути, я буду вводить данные о материалах, которые добавляются в склад, и по мере того, как и когда эти материалы используются, я делаю соответствующие записи. Я использую сводные таблицы и диаграммы для анализа своих данных.

Пример: давайте рассмотрим основное сырье, т.е. бумажные рулоны. Я подготовил рабочую книгу Roll Stock со всеми необходимыми данными, таблицами и графиками. Я создал еще одну рабочую книгу Roll Log, в которой я буду вводить детали с действиями, которые выданы и возвращены вместе с весом рулона, когда он будет вынут или возвращен обратно в магазин соответственно. Я использую уникальные номера для идентификации каждого рулона (пожалуйста, посмотрите файлы, прежде чем продолжить).

Эта проблема:

1. Чтобы отслеживать детали использования в сводной таблице Roll Log, я должен быть в состоянии выполнить несколько простых проверок, например, больше ли вес конкретного рулона, когда он выдан оператором, чем когда он был возвращен, возвращен ли тот же рулон до его Выдается снова в ту же / другую дату.... и т. Д. Возможно ли это в Excel?

2. Объединение данных из рулонного запаса и рулонного журнала:

   а. Скажем, Roll R000001 весил 789 кг при покупке у продавца 7 октября 2016 года. Запись с такими же деталями была внесена в рабочую книгу Roll Stock.

   б. R000001 был выдан оператору 8 октября 2016 года, и в рабочей книге Roll Log with Action as Issued была сделана запись с теми же деталями.

   с. Оператор использовал 560 кг и возвратил его в магазины 9 октября 2016 года, и в журнале снова была сделана соответствующая запись с действием " Возвращено". Все эти данные фиксируются в сводке Roll Log.

Теперь, как я могу использовать это значение для динамического уменьшения количества в стержне Roll Stock, чтобы каждый раз, когда я вижу панель инструментов в Roll Stock, она давала мне последний доступный запас. Я бы предпочел не использовать VBA, так как это резко замедляет мои рабочие книги

PS: Это всего лишь временная система, и я скоро пойду на ERP. Но я был бы очень благодарен, если бы кто-нибудь мог помочь мне добиться успеха, как я ожидал. Пожалуйста, не стесняйтесь предлагать любые улучшения.

Answer 1

На вопрос 2: Ваши рабочие книги не показывают никакой связи между Action (Issued/Returned) в рулонном журнале и Available Quantity в рулонном складе. Я буду считать, что это так.

Вариант, который выскакивает у меня, это использовать формулу для Available Quantity в рулонном складе. Формула должна выглядеть на последнем Quantity для рулона в Roll Log.

Используя этот пример, чтобы найти последнее вхождение значения в столбце, я бы порекомендовал следующую формулу...

=IF(ISNA(LOOKUP(2,1/('Roll Log.xlsx'!T_Roll_Log[Roll Number]=[@[Roll Number]]),'Roll Log.xlsx'!T_Roll_Log[Quantity (kg)])),[@[Received Qty (kg)]],LOOKUP(2,1/('Roll Log.xlsx'!T_Roll_Log[Roll Number]=[@[Roll Number]]),'Roll Log.xlsx'!T_Roll_Log[Quantity (kg)]))

Если функция Lookup возвращает NA (то есть бросок не появляется в журнале бросков), тогда используйте Received Qty, В противном случае используйте последнее значение, которое появляется для этого броска в журнале бросков.

Затем обновите сводную таблицу.

На вопрос 1: я бы добавил столбец в вашу таблицу Roll Log, который проверяет, что при возврате предыдущее действие для рулона было "Issued", и которое вычисляет используемое количество.

Формула будет выглядеть следующим образом...

=IF([@Action]="Returned",IF(LOOKUP(2,1/($D$4:$D5=[@[Roll Number]]),[Action])="Issued",LOOKUP(2,1/($D$4:$D5=[@[Roll Number]]),[Quantity (kg)]-[@[Quantity (kg)]]),"MISHANDLED"),"")

... и даст результаты, как это...