Excel, średnie daty między transakcjami?

Mam zestaw danych ekwipunku, z którym bawię się i uderzyłem w ścianę, próbując wymyślić coś, co chcę zrobić.

Zestaw danych ma kolumnę nazwy (nazwę przedmiotu; np. „Młot” lub „piła”), kolumnę zliczania (ile jest w magazynie) oraz kolumnę czasu transakcji. (Są też inne kolumny, w tym czas transakcji w podziale na dzień, miesiąc, rok, godzinę i dzień tygodnia).

Dla każdej unikalnej nazwy elementu ekwipunku chcę znaleźć średnią liczbę dni między

zakup do zakupu,
od zakupu do sprzedaży, oraz
sprzedaż do sprzedaży.

Transakcje można zidentyfikować jako zakupy lub sprzedaż na podstawie tego, czy zapasy („liczba”) rosną, czy maleją w stosunku do poprzedniej transakcji dla tego samego towaru.

microsoft-excel-2010 worksheet-function

— Arrak
źródło

Wybór programu Excel wydaje się utrudniać tworzenie rozwiązania trudniejszego niż używanie języka programowania / zapytań bezpośrednio u źródła. Z czego generowany jest plik Excel?

— gogowitsch

Okej, więc nie jest to takie łatwe, jak myślałem. Pierwotnie był to plik CSV, który, jestem pewien, pochodzi z bazy danych mySQL.

— Arrak

Czy możesz zapytać o MySQL? Jeśli tak, zadaj kolejne pytanie zawierające układ DB w Stackoverflow zamiast Super User. Z pewnością można to zrobić w programie Excel, szczególnie jeśli VBA jest opcją. Poczekaj i zobacz, co wymyślą eksperci!

— gogowitsch

Excel nie jest prawdopodobnie najlepszym sposobem na zrobienie tego, ale udało mi się. Oto kilka przykładowych danych, które utworzyłem, oraz wyniki moich formuł:

(Powyższy obraz jest linkiem do większego obrazu, który pokazuje ukryte kolumny pomocnicze.) Zakładałem istnienie Datekolumny o akceptowalnym poziomie ziarnistości. Oto formuły:

AA2: =VLOOKUP($A2, $A3:$C$22, 2, FALSE) * Uwaga: $A3 nie $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")

Zakładam, że masz jakiś sposób zapełnienia Wkolumny unikatowymi nazwami przedmiotów magazynowych. 22reprezentuje ostatni wiersz, który zawiera dane.

Wyjaśnienie:

Różowe kolumny, AA:ACodwzoruj na następną transakcję dla elementu określonego w kolumnach A:Cbieżącego wiersza. Na przykład AA2:AC2odwzoruj na A4:C4. DeltaKolumna przedstawia zmianę zapasów między tymi dwiema transakcjami. Można po prostu określić AB2się =VLOOKUP($A2, $A3:$C$22, 2, FALSE) - $B2i usuwać kolumny AA; Wierzę, że ta droga jest jaśniejsza.
Pomarańczowe kolumny ( AD:AG) dzielą pary transakcji z kolumn AA:ACna zakupy ( AD:AE) i sprzedaż ( AF:AG), w zależności od tego, czy delta zapasów jest dodatnia czy ujemna, i replikują nazwę towaru z kolumny A.
Jasnozielone kolumny ( AH:AI) mapują każdy zakup do następnego zakupu tego samego produktu i obliczają liczbę dni między nimi. Na przykład AH2jest AE8(co jest C10) i AI2jest AE8-AE2 (tj C10-C4.).
Ciemniejsze zielone kolumny ( AJ:AK) mapują każdy zakup do następnej sprzedaży tego samego przedmiotu i obliczają liczbę dni między nimi.
Niebieskie kolumny ( AL:AM) mapują każdą sprzedaż do następnej sprzedaży tego samego produktu i obliczają liczbę dni między nimi.

— Scott
źródło