Ich brauche ein wenig Hilfe für ein Szenario, an dem ich mit Excel arbeiten werde. Jede Hilfe wird sehr geschätzt.
Ich werde einen immer größer werdenden Datumsbereich für bestimmte Artikel haben und muss eine Excel-Tabelle erstellen, um die durchschnittliche Anzahl von Tagen zwischen ihnen pro Artikel zu ermitteln. Im Grunde ist dies ein vereinfachtes Beispiel dafür, wie ich die Daten tabellieren möchte:
Item Code | Date
A.ITEM | January 15, 2017
B.ITEM | January 16, 2017
A.ITEM | January 22, 2017
C.ITEM | January 25, 2017
A.ITEM | January 31, 2017
C.ITEM | February 2, 2017
B.ITEM | February 12, 2017
B.ITEM | February 24, 2017
C.ITEM | March 7, 2017
Ich werde dann eine weitere Tabelle erstellen, die die durchschnittliche Dauer zwischen den Daten pro Artikel anzeigt. Ich stelle mir vor, dass sie so aussehen wird:
Item Code | Average Life Span
A.ITEM | 9 days
B.ITEM | 20.5 days
C.ITEM | 21.5 days
Welche Formel bräuchte ich, um die zweite Tabelle zu erstellen? Ich zerbreche mir schon eine Weile den Kopf darüber und weiß immer noch nicht, wie das geht, weil ich mich mit Datumsfunktionen in Excel nicht so gut auskenne. Ist das überhaupt möglich?
Danke schön!
Antwort1
Beachten Sie, dass der Durchschnitt der Differenzen einfach (max-min)/Anzahl ist: (d1-d2) + (d2-d3) + (d3-d4) +... = d1-dn
Damit können Sie eine Formel verwenden wie
(MAX(d1:dn)-MIN(d1:dn))/COUNT(d1:dn)
Dadurch würden jedoch alle Daten in einen Topf geworfen, sodass Sie zusätzlich nach Ihren Codes filtern müssen – anstatt einfach zu MAX(d1:dn)verwenden, verwenden Sie MAX(IF(a1:an=code,d1:dn,0)es als Matrixformel. Fügen Sie ähnliche ifs für MIN und COUNT hinzu (oder verwenden Sie COUNTIF); beachten Sie, dass der else-Wert für MIN nicht 0 sein kann, sondern etwas sehr Großes sein muss.
Antwort2
In diesem Beispiel steht der Artikelcode in Spalte A, das Datum in Spalte B und dann füge ich neue Daten hinzu. Zeile 1 enthält Überschriften.
Zunächst müssen Sie Ihre Daten jedes Mal sortieren, wenn Sie eine neue Zeile hinzufügen. Dies geschieht mit einer zweistufigen Sortierung:
- zuerst nach Artikelcode
- Sekunde nach Datum
Fügen Sie dann in der Spalte rechts neben „Datum“ eine „Zeit zwischen“-Berechnung hinzu und ziehen bzw. füllen Sie sie nach unten:
=IF(A2=A1,B2-B1,"")
Dann die Zeiten mitteln. Die Artikelcodes A, B, C an anderer Stelle auflisten – ich habe sie in meinem Test in Spalte F eingetragen. Neben A eingeben und nach unten ziehen/ausfüllen:
=AVERAGEIF($A$2:$A$6491,F2,$C$2:$C$6491)
Ich habe:
- Antwort: 8
- B: 19,5
- C: 20,5
(für A: 22. Jan. - 15. Jan. sind 7 Tage, 31. Jan. - 22. Jan. sind 9 Tage, Durchschnitt sind 8 Tage)