Ich habe Daten, die ich unbedingt analysieren muss, und ich weiß nicht, wie ich vorgehen soll. Die Daten befinden sich in drei Spalten; die erste ist das Datum. Die Daten sind (aus verschiedenen Gründen) nicht in der richtigen Reihenfolge und neue Daten werden einfach am Ende der Liste hinzugefügt. Ich habe außerdem den Verkaufspreis eines Produkts in der zweiten Spalte und den Produkttyp in der dritten. Beispiel:
Die Berechnungen müssen auf den sieben aktuellsten Preisen eines Typs basieren. Der drittniedrigste Preis und der zweithöchste Preis werden in die Tabelle eingetragen, beginnend mit den Zellen „A“ ( F12) bzw. „B“ ( G12).
Die dritte Formel sollte den höchsten und niedrigsten der sieben letzten Preise eliminieren (ignorieren) und den Durchschnitt der verbleibenden fünf Preise berechnen. Dieser wird in die letzte Spalte der Tabelle eingetragen, beginnend mit Zelle „C“ ( H12).
Dann brauche ich ein kombiniertes Balken-/Liniendiagramm mit „Typ“ auf der horizontalen Achse und „Preis“ auf der vertikalen. Ich brauche ein gestapeltes Balkendiagramm, das den unteren Bereich (Zelle „A“) und den oberen Bereich (Zelle „B“) zeigt, und ein Liniendiagramm soll mit den Durchschnittswerten überlagert werden (Zelle „C“).
Ich bin kein Excel-Experte und habe jedes Selbsthilfe-Tutorial ausprobiert, das ich finden konnte, um das Problem zu lösen. Ich bin soweit gekommen, dass ich dachte, das VLOOKUPkönnte nützlich sein, aber jetzt bin ich ratlos.
Antwort1
OK, die von Scott angesprochene Einschränkung gegen mehrere Verkäufe desselben Typs an einem bestimmten Tag hat dies möglich gemacht. Aber es ist immer noch ziemlich kompliziert. Die Verwendung von VBA könnte viel einfacher sein, und vielleicht veröffentlicht hier jemand eine Prozedur.
Ich werde zunächst eine „Hilfstabelle“ verwenden, da diese nützlich ist, um die Zwischendaten anzuzeigen und zu überprüfen, ob die Berechnungen korrekt sind. Ich werde aber auch Formeln zeigen, die die Hilfstabelle nicht verwenden. Die Hilfstabelle wird unten zusammen mit den Ergebnissen angezeigt.
Ich habe zufällige Preise und Daten verwendet, aber die oben genannte Einschränkung bezüglich mehrerer Verkäufe eines bestimmten Typs pro Datum beachtet. Lassen Sie uns nun die Formel erstellen, die in F2 eingeht.
Zunächst benötigen wir ein Array mit den sieben aktuellsten Preisen für jeden Typ. Sobald dieses verfügbar ist, können wir aus diesem Array ganz einfach die drei Kennzahlen in der Ergebnistabelle berechnen.
Wir beginnen mit diesem Ausdruck: ($C$2:$C$55=ROW()-1). Dies ist Teil der Formel in F2, also ROW()-1gleich 1, und dieser Ausdruck ergibt ein Array von True/False-Werten, wobei True immer dann gilt, wenn Type gleich 1 ist, und False überall sonst. Beim Ausfüllen wird es ROW()erhöht, sodass in der nächsten Zeile das Array mit True ausgegeben wird, wo Type gleich 2 ist, usw.
Jetzt multiplizieren wir dieses Array mit der Datumsspalte: ($A$2:$A$55)*($C$2:$C$55=ROW()-1). Das Ergebnis ist ein Array, das das Datum enthält, wobei Typ gleich 1 und überall sonst gleich „False“ ist.
Jetzt wollen wir die letzten 7 dieser Daten und diese erhalten wir mithilfe der LARGE()Funktion. Aufgrund der oben genannten Einschränkung (es gibt an jedem Datum nur einen Verkauf vom Typ 1) erhält man hier die Daten der letzten 7 Verkäufe vom Typ 1:
LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})
Die Zahlenreihe in geschweiften Klammern gibt an, LARGE()dass der 1. bis 7. größte Wert zurückgegeben werden soll.
Jetzt verwenden wir einen , IF()um die Preise für diese Daten zu erhalten:
IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55)
Der erste Teil des IF()logischen Tests von gibt ein Array zurück, in dem das Datum in Spalte A eines der 7 oben genannten Daten ist, und dieses Array wird mit dem Array multipliziert, in dem Typ wieder = 1 ist, da an diesen 7 Daten andere Typen verkauft wurden. Der IF()Test prüft also, ob das Datum in Spalte A einem der 7 jüngsten Daten entspricht .Undob der bestimmte Verkauf an diesem Datum für Typ = 1 war. Wenn beide Bedingungen erfüllt sind, IF()wird der Verkaufspreis aus Spalte B zurückgegeben, andernfalls „False“.
Um nun die Preise in der Hilfstabelle von hoch nach niedrig aufzulisten, verwenden wir LARGE():
(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5)
Diese Formel steht in Spalte F, ist also COLUMN()-5gleich 1, und die Formel gibt den 1. größten Wert des Arrays in dieser Spalte zurück. Beim Ausfüllen wird sie COLUMN()inkrementiert, sodass in der nächsten Spalte der 2. größte Wert usw. zurückgegeben wird.
Schließlich wird die Formel so umschlossen IFERROR(), dass sie ein leeres Ergebnis zurückgibt, wenn es weniger als 7 Verkäufe eines bestimmten Typs gibt. Die endgültige Formel:
=IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5),"")
wird mit F2 eingegeben. Da es sich um eine Matrixformel handelt, muss sie mit eingegeben werden CTRLShiftEnter, nicht nur mit Enter. Bei korrekter Eingabe umschließt Excel die Formel in der Formelleiste mit geschweiften Klammern {}. Wählen Sie nach der Eingabe F2 und füllen Sie die Formel nach unten und dann quer aus, um die Hilfstabelle oben anzuzeigen.
Jetzt ist es einfach, die Ergebnistabelle auszufüllen. Diese beiden Formeln
=SMALL(F2:L2,3) and =LARGE(F2:L2,2)
Berechnen Sie den drittkleinsten und zweitgrößten Wert in der ersten Zeile der Hilfstabelle. Und diese Formel
=AVERAGE(IFERROR(LARGE(F2:L2,{2,3,4,5,6}),""))
berechnet den Durchschnitt der mittleren 5 Werte. Es handelt sich ebenfalls um eine Matrixformel, daher muss sie wie oben eingegeben werden. Durch Ausfüllen dieser Formeln erhält man die Ergebnistabelle.
Um die Ergebnistabelle ohne Verwendung einer Hilfstabelle zu füllen, verwenden Sie diese drei Array-Formeln in F, G und H12 und füllen Sie nach unten aus:
=SMALL(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),3)
=LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),2)
=AVERAGE(IFERROR(LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),{2,3,4,5,6}),""))
Ich hoffe, das hilft Ihnen weiter, und ich bin sicher, dass Sie das gewünschte Diagramm erstellen können, sobald Sie diese Daten haben.
Kommentare und Vorschläge sind willkommen.