Hallo Leute, die besser in Excel sind als ich! Ich bin für jede Hilfe dankbar, die ihr mir anbieten könnt. Ich werde versuchen, mich kurz zu fassen:
Ich habe eine Tabelle mit drei Spalten. Monat (als 1, 2, 3 usw.), Name des Verkäufers und Umsatz. Jede Zeile zeigt an, wie viel Dollar Umsatz ein Verkäufer in diesem Monat gemacht hat.
Die Anordnung ist folgendermaßen: Die ersten 1.000 Zeilen sind alle 1. Januar, die nächsten 1.000 alle 2. Februar usw.
Ich muss den durchschnittlichen Dollarbetrag ermitteln, den jeder Verkäufer in den letzten 3 Monaten verkauft hat. Aber – wenn einer dieser letzten 3 Monate leer ist, brauche ich trotzdem einen 3-Monats-Durchschnitt.
Die Formel muss also beispielsweise ein leeres September-Ergebnis erkennen und stattdessen das nächste aktuellere Ergebnis abrufen (August, Juli, was auch immer), sodass immer der Durchschnitt der Ergebnisse des jeweiligen Verkäufers aus den letzten drei Monaten berechnet wird und nicht nur ein oder zwei Ergebnisse.
Im Moment habe ich hierfür eine sehr einfache Pivot-Tabelle. Funktionierte für meine Zwecke prima, bis mir dieses Problem auffiel :(
Gibt es eine Möglichkeit (mit Pivot-Tabelle oder nicht), dies zu tun? Es gibt über 1000 Verkäufer, also ist es manuell nicht machbar. Ich habe viele Leute, die aus irgendeinem Grund einen Monat lang keine Zahlen haben, also muss ich die Berechnung entsprechend anpassen.
Vielen Dank für jede Hilfe! Bitte lassen Sie mich wissen, ob ich noch etwas hinzufügen kann oder ob es unklar ist.
Antwort1
Ich kenne keine gute Methode, den Durchschnitt der letzten drei nicht leeren Monate zu berechnen, aber hier ist einesehrHacky-Methode, um es zu tun. Dies kann einige bessere Möglichkeiten aufzeigen, es zu erreichen.
Erstellen Sie in Ihren Rohdaten eine Tabelle und sortieren Sie dann nach Monat (absteigend) und Person.
Filtern Sie als Nächstes nach der Spalte „Umsatz“ und deaktivieren Sie leere Felder. Kopieren Sie dann den Inhalt in einen anderen Bereich und fügen Sie ihn ein.
Erstellen Sie dann eine Spalte „Rang“. Formel für Zelle D2, die jeden Monat (aktuellster ist 1 usw.) für jede Person bewertet.
=IF(B2=B1,D1+1,1)
Erstellen Sie dann einen weiteren Bereich mit den Personen, für die Sie sich interessieren (Spalte F) und Platzhalterspalten für die letzten drei Monate (Spalten mit den Bezeichnungen 1, 2 und 3).
Formel von Cell G2:
=SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)
Ziehen Sie es auf Spaltentitel 3 und nach unten. Erstellen Sie abschließend Ihre Durchschnittsformel.
Antwort2
Bei einem anderen Ansatz, bei dem die Originaldaten unverändert bleiben, ohne sie neu anzuordnen, zu filtern oder zu kopieren, werden der Datenliste drei Hilfsspalten hinzugefügt, wie nachfolgend gezeigt.
SpalteReiheist eine einfache Indexreihe, beginnend bei 1, SpaltePerson2ist das gleiche wiePersonverwendet aber eine WENN-Funktion, um eine Nullzeichenfolge in Zeilen mit Null einzufügenVerkäufe.Anzahlliefert eine Zählung,Person, von Monaten mit Werten ungleich NullVerkäufe.
Anzahlinteragiert mit einer Ergebnisliste, die für jeden Verkäufer eine Zeile enthält, wie im Folgenden gezeigt.
In der Ergebnisliste wird in der SpalteMonatezählt einfach die Anzahl der Monate mit Umsätzen ungleich Null in der Datenliste für jeden Verkäufer. Die Formel in Zelle I2 lautet
=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")
ZeileM1zeigt dieReiheWert in der Datenliste, der dem ersten Monat mit einem Umsatz ungleich Null für jeden Verkäufer entspricht. Die Formel in Zelle J2 lautet
=MATCH(H2,$E$2:$E$21,0)
SäulenMonat1,Monat2UndMonat3sind die Monatszahlen in der SpalteAnzahl(der Datenliste) für den ersten, zweiten und dritten Monat, der zur Berechnung des 3-Monats-Durchschnitts verwendet wird, währendDer Umsatz1,Der Umsatz2UndDer Umsatz3sind die Verkaufswerte für diese 3 Monate.Durchschnittist der berechnete 3-Monats-Durchschnitt.
Die Formeln für die Zellen K2, L2 und M2 lauten jeweils
=I2-1
=K2+1
=L2+1
Die Formel in Zelle N2 lautet
=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)
und stützt sich auf die Werte in derAnzahlSpalte der Datenliste (Bereich $F$2:$F$21, siehe weiter). Zelle N2 wird in die Zellen O2 und P2 kopiert.
Der Bereich I2:Q2 kann in alle nachfolgenden Zeilen der Ergebnisliste kopiert werden.
Datenliste - SpalteAnzahl
Die Formel in Zelle F2 lautet
=IF(C2>0,1,0)
Dadurch wird 0 oder 1 in die Zelle eingefügt. 0 bedeutet, dass die Person in Zelle E2 im in Zelle angezeigten Monat keine Umsätze erzielt hat B2, während 1 bedeutet, dass dies der erste Monat mit Umsätzen für diese Person war.
Die Formel in Zelle F3 ist wesentlich komplexer und lautet
=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))
Der TRUE-Teil des ersten IF liefert einfach 0, wenn die Zeile entsprichtVerkäufevon Null.
Ansonsten,Verkäufeungleich Null sind und das zweite IF ausgelöst wird. In diesem zweiten IFPersonin Zelle E3 wird in der Ergebnistabelle nachgeschlagen und, wenn die ZeileM1Wert (Zeilennummer in der Datenliste, die dem ersten Verkaufsmonat entspricht) entspricht demReiheWert in Zelle D3, bedeutet dies, dass Zeile 3 der ersten Zeile mit von Null verschiedenen Umsätzen für die in Zelle E3 identifizierte Person entspricht.
Wenn dasZeileM1UndReiheWerte unterscheiden sich, Zeile 3 in der Datentabelle entspricht einem nachfolgenden Verkaufsmonat (nach dem ersten) für die Person in Zelle E3. In diesem Fall ergibt sich die Nummer des Monats, indem in den vorhergehenden Zeilen der Datenliste 1 zum Maximalwert in Spalte F für die Verkaufsperson in Zelle E3 addiert wird. Zur Bestimmung dieses Maximums wird eine MAXIFS-Funktion verwendet.
Die Formel in Zelle F3 wird in die nachfolgenden Zeilen der Datenliste kopiert. Durch die entsprechende Verwendung der relativen und absoluten Adressierung in der Formel wird sichergestellt, dass die Funktion MAXWENNS in den Kopien die entsprechenden Zellbereiche verwendet.
Anmerkungen
- Die Verwendung von MAXIFS erfordert Excel 2019 oder höher
- Dabei wird davon ausgegangen, dass (i) die Daten in chronologischer Reihenfolge vorliegen und (ii) für jede Kombination aus Verkäufer und Monat höchstens eine von Null verschiedene Zeile mit Verkaufsdaten vorhanden ist.
- In den Bildern kennzeichnet eine blaue Schrift eine Formel und eine schwarze Schrift einen statischen Wert.