Excel, средние даты между транзакциями?

Question

У меня есть набор данных инвентаря, с которым я играю, и я врезался в стену, пытаясь выяснить, что я хочу сделать.

Набор данных имеет столбец имени (имя элемента; например, «молоток» или «пила»), столбец подсчета (сколько на складе) и столбец времени транзакции. (И есть другие столбцы, включая время транзакции, разделенное на день, месяц, год, время и день недели.)

Для каждого уникального имени предмета я хочу найти среднее число дней между

• покупка, чтобы купить,
• покупка к продаже, и
• продажа к продаже.

Транзакции могут быть идентифицированы как покупки или продажи в зависимости от того, увеличивается или уменьшается ли запас («количество») по сравнению с предыдущей транзакцией для того же предмета.

Answer 1

Excel, вероятно, не лучший способ сделать это, но мне это удалось.  Вот некоторые примеры данных, которые я составил, и результаты моих формул:

                           

(Изображение выше является ссылкой на увеличенное изображение, которое показывает скрытые столбцы помощника.)  Я предположил существование столбца Date который имел приемлемый уровень детализации.  Вот формулы:

AA2: =VLOOKUP($A2, $A3:$C$22, 2, FALSE)                * Примечание: $A3 , а не $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")

Я предполагаю, что у вас есть какой-то способ заполнить столбец W уникальными именами предметов инвентаря.  22 представляет последнюю строку, которая имеет данные.

Объяснение:

• Розовые столбцы AA:AC соответствуют следующей транзакции для элемента, указанного в столбцах A:C текущей строки.  Например, AA2:AC2 отображается на A4:C4 .  В столбце Delta показано изменение запасов между этими двумя транзакциями.  Можно просто определить AB2 как =VLOOKUP($A2, $A3:$C$22, 2, FALSE) - $B2 и удалить столбец AA ; Я считаю, что этот путь понятнее.
• Оранжевые столбцы (AD:AG) разбивают пары транзакций из столбцов AA:AC на покупки (AD:AE) и продажи (AF:AG) в зависимости от того, является ли дельта инвентаря положительной или отрицательной, и реплицируют имя элемента из столбца. A
• Светло-зеленые столбцы (AH:AI) сопоставляют каждую покупку со следующей покупкой того же предмета и вычисляют количество дней между ними.  Так, например, AH2 - это AE8 (то есть C10), а AI2 - это AE8-AE2 (то есть C10-C4).
• Темные зеленые столбцы (AJ:AK) сопоставляют каждую покупку со следующей продажей того же предмета и вычисляют количество дней между ними.
• Синие столбцы (AL:AM) отображают каждую продажу на следующую продажу того же предмета и вычисляют количество дней между ними.