☰

Wählen Sie die drei höchsten Werte aus und addieren Sie sie

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007 power-query
Wählen Sie die drei höchsten Werte aus und addieren Sie sie

Nehmen wir an, ich habe Daten, die so aussehen:

PLAYER    |Team      |Points
Smith     |Suns      |25
Jones     |Suns      |15
Martin    |Suns      |23
Chen      |Suns      |3
Williams  |Suns      |17
Quill     |Marvel    |40
Banner    |Marvel    |1
Stark     |Marvel    |1
Odinson   |Marvel    |1
Parker    |Marvel    |3
Curly     |Spurs     |2
Franke    |Spurs     |5
Wayne     |Spurs     |23
Weasley   |Wizards   |21
Potter    |Wizards   |19
Granger   |Wizards   |15
Thompson  |Bobcats   |12
Boehme    |Bobcats   |13

Wenn ich eine Formel zum Addieren der drei besten Ergebnisse aller Teams schreiben wollte, wie würde ich das tun?

Beispielsweise würden folgende Daten zurückgegeben:

Team   |Points
Suns   |65
Marvel |44
Spurs  |30
Wizards|55
Bobcats|25

Ich habe überlegt, einen Filter zu verwenden, der sagt: „Wenn der Wert größer ist als der mittlere Wert der Teammitglieder“ oder so etwas, aber wenn es weniger als 3 Mitglieder gäbe, würde das nicht funktionieren.

Ich habe es auch in Betracht gezogen sumif, aber ich denke, aus demselben Grund wäre das nicht angemessen.

Außerdem möchte ich die Summe der drei MITTLEREN Punktzahlen ermitteln, bin mir aber sicher, dass ich, wenn ich Ratschläge zum Ermitteln der oberen drei bekomme, herausfinden kann, wie ich es für die mittleren drei anpassen kann.

Ist das etwas, bei dem Power Query hilfreich sein könnte? Ich habe mich damit beschäftigt, bin aber kein Profi. (Wenn es unbedingt sein müsste, könnte ich wahrscheinlich auch etwas in VBA zusammenschustern, aber diesen Weg würde ich nach Möglichkeit lieber nicht gehen, da ich es in ein Google Sheet einfügen möchte, um es irgendwann im Web zu teilen).

Antwort1

Sortieren Sie Ihre Daten absteigend nach Team und Punktzahl. Verwenden Sie dann diese Formel, die den Erstplatzierten jedes Teams ermittelt und die nächsten drei Punktzahlen oder die Anzahl der Punktzahlen für dieses Team, falls diese niedriger sind, summiert:

=SUM(INDEX(C:C,MATCH(F2,B:B,0)):INDEX(C:C,MATCH(F2,B:B,0)+MIN(COUNTIF(B:B,F2)-1,2)))

Bildbeschreibung hier eingeben

Antwort2

Mit dieser Formel werden die drei höchsten Punktzahlen jedes Teams addiert:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E2,C$2:C$19),{1,2,3}),""))

Da es sich um eine Array-Formel handelt, muss sie mit CTRLShiftEnterund nicht nur eingegeben werden Enter.

Die Ergebnisse sind unten dargestellt:

Bildbeschreibung hier eingeben

Wie es funktioniert: Gibt IF()eine Liste der Punkte für das in Spalte E aufgeführte Team zurück. Dann LARGE()werden nur die drei höchsten Punktzahlen berücksichtigt. IFERROR()Behandelt die Fälle, in denen weniger als drei Punktzahlen vorhanden sind. SUM()Addiert sie abschließend.

BEARBEITEN: Um die Summe der mittleren drei Punktzahlen zu erhalten, habe ich versucht, die Referenzform von INDEX() mit Literal-Arrays und einer Formel zum Auswählen der „Bereichsnummer“ zu verwenden:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX(({1,2,3},{2,3,4}),,,INT(COUNTIF(B2:B19,E11)/2))),""))

Aber die Array-Konstanten wurden nicht als Referenz akzeptiert. Ich habe es schließlich zum Laufen gebracht, indem ich eine Hilfsspalte zur Angabe der Arrays verwendet habe:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX((H$1:H$3,H$2:H$4),,,INT(COUNTIF(B2:B19,E11)/2))),""))

Antwort3

Mein Ansatz unterscheidet sich nur geringfügig, um die Summe der besten drei und der mittleren drei Ergebnisse zu erhalten.

Bildbeschreibung hier eingeben

  1. Ich habe die Quelldaten in aufsteigender Reihenfolge nach dem Teamnamen als primärem Feld und in absteigender Reihenfolge nach den Punkten als sekundärem Feld sortiert. (Um die Summe der drei besten Punktzahlen manuell berechnen zu können).
  2. Ich habe eine Formel verwendet, um die Liste der Teams sowohl in aufsteigender als auch in absteigender Reihenfolge zu generieren.
  3. Schließlich habe ich die Summe der besten drei und der mittleren drei Punktzahlen mithilfe einer Formel aus einer der obigen Antworten berechnet.

Hier sind die Formeln für:

Teamliste in aufsteigender Reihenfolge:

{=INDEX($D$216:$D$233, MATCH(0, COUNTIF($J$215:J215, $D$216:$D$233), 0))}

Teamliste in absteigender Reihenfolge:

  =IFERROR(LOOKUP(2,1/(COUNTIF($G$215:G215,$D$216:$D$233)=0),$D$216:$D$233),"")

Summe der Top-3-Ergebnisse der Teams in aufsteigender Reihenfolge:

{=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{1,2,3}),0))}

Summe der mittleren 3 Punktzahlen für Teams in aufsteigender Reihenfolge:

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{2,3,4}),0))

Eine Erläuterung dieser Formel finden Sie in Anmerkung 2 weiter unten.

Anmerkungen:

  1. Ersetzen Sie J216 durch G216, um die besten und mittleren drei Ergebnisse für Teams in absteigender Reihenfolge zu erhalten.
  2. Da die maximale Anzahl der Teams 5 beträgt, bin ich davon ausgegangen, dass die mittleren 3 Ergebnisse die Nummern 2, 3 und 4 sind.Dadurch wird die höchste Punktzahl aus der Gesamtpunktzahl eliminiert, auch wenn es nur 2 oder 3 Punktzahlen gibt.Wenn Sie glauben, dass ein Team mit nur 2 oder 3 PunktenallePunkte tragen zum Gesamtergebnis bei, dann ziehen Sie eine der oben stehenden Antworten in Betracht.

verwandte Informationen