Wählen Sie die oberen 3 Werte und summieren Sie sie

494
phroureo

Nehmen wir an, ich habe einige Daten, die folgendermaßen 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 schreiben wollte, um die Top 3 der einzelnen Teams zusammenzufassen, wie würde ich das tun?

Zum Beispiel würde es diese Daten zurückgeben:

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

Ich habe überlegt, einen Filter zu verwenden, um zu sagen "wenn der Wert größer ist als der mittlere Wert der Mitglieder des Teams" oder etwas, aber wenn weniger als 3 Mitglieder vorhanden wären, würde das nicht funktionieren.

Ich habe auch überlegt sumif, denke aber nicht, dass dies aus demselben Grund angemessen wäre.

Ich bin auch auf der Suche nach der Summe der MIDDLE 3-Ergebnisse, aber ich bin mir sicher, wenn ich Rat bekomme, um die Top 3 zu finden, kann ich herausfinden, wie ich sie für die mittleren 3 anpassen kann.

Ist dies etwas, mit dem Power Query hilfreich sein könnte? Ich habe versucht, aber ich bin kein Profi. (Wenn ich es unbedingt tun müsste, könnte ich wahrscheinlich auch etwas in VBA aufpeppen, aber ich möchte diesen Weg möglichst nicht einschlagen, da ich ihn in ein Google Sheet stellen möchte, um ihn schließlich im Web zu veröffentlichen.)

0
Wären Sie bereit, nach Team und absteigenden Punkten zu sortieren? Scott Craner vor 6 Jahren 0
Ja, ich kann mich darauf konzentrieren, was wir brauchen. phroureo vor 6 Jahren 0

3 Antworten auf die Frage

2
Scott Craner

Sortieren Sie Ihre Daten nach Team und zählen Sie absteigend. Verwenden Sie dann diese Formel, die den ersten von jedem Team ermittelt und die nächsten drei Punkte oder die Anzahl der Punkte für dieses Team summiert, falls weniger

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

Ich bezeichne Scott als richtig, weil es derjenige ist, den ich am Ende verwendet habe (ich bin mit Arrays nicht so vertraut, daher war ich mir nicht sicher, ob ich ihn modifizieren könnte, um die mittleren 3 zu finden). Beide Antworten waren jedoch großartig. :) phroureo vor 6 Jahren 0
2
Bandersnatch

Diese Formel fasst die drei besten Punkte für jedes Team zusammen:

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

Es ist eine Matrixformel, also muss es mit eingegeben werden CTRLShiftEnter, und nicht nur Enter.

Die Ergebnisse sind unten gezeigt:

Wie es funktioniert: Die IF()gibt eine Liste der Punkte für das in Spalte E aufgeführte Team zurück. Dann werden LARGE()nur die ersten 3 Punkte erzielt. IFERROR()behandelt die Fälle, in denen weniger als 3 Punkte vorhanden sind. Schließlich SUM()fügt sie.

EDIT: Um die Summe der mittleren 3 Punkte zu erhalten, habe ich versucht, die Referenzform von INDEX () mit wörtlichen Arrays und einer Formel zur Auswahl von "area_num" zu verwenden:

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

Die Array-Konstanten würden jedoch nicht als Referenz akzeptiert. Ich habe es endlich zum Laufen gebracht, indem ich die Arrays mit einer Helfer-Spalte spezifizierte:

=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))),""))
Ich machte eine SUMIF mit mehr als dem drittgrößten, aber für Krieger wurde es immer zu viel. Dies ist ein großartiger Ansatz. Scott Craner vor 6 Jahren 0
Danke, Scott. Sehr schön, eine Ergänzung von Ihnen zu bekommen. Ich hatte Schwierigkeiten, die Summe der mittleren 3 zu erhalten, werde sie jedoch in einer Minute zu meiner Antwort hinzufügen. Bandersnatch vor 6 Jahren 0
0
Rajesh S

Mein Ansatz unterscheidet sich nur geringfügig, um die Summe der Top 3 und mittleren 3 Punkte zu erhalten.

  1. Ich habe die Quelldaten nach Teamname als Primärfeld in aufsteigender Reihenfolge und nach Punkten als Sekundärfeld in absteigender Reihenfolge sortiert. (Um die Summe der Top 3-Ergebnisse manuell berechnen zu können).
  2. Ich habe eine Formel verwendet, um die Liste der Teams in aufsteigender und absteigender Reihenfolge zu generieren.
  3. Zum Schluss habe ich die Summe der drei besten und mittleren drei Werte anhand einer Formel aus einer der obigen Antworten berechnet.

Hier sind die Formeln für:

Teamliste aufsteigend:

{=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 Scores für Teams in aufsteigender Reihenfolge:

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

Summe der mittleren 3 Punkte für Mannschaften in aufsteigender Reihenfolge:

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),),0))

In Anmerkung 2 unten finden Sie eine Erläuterung dieser Formel.

Anmerkungen:

  1. Ersetzen Sie J216 durch G216, um Top & Middle 3-Ergebnisse für Teams in absteigender Reihenfolge zu erhalten.
  2. Da die maximale Anzahl von Teams 5 beträgt, habe ich angenommen, dass die mittleren 3 Punkte die Zahlen 2,3 und 4 sind. Dies eliminiert die höchste Punktzahl aus der Gesamtzahl, auch wenn nur 2 oder 3 Punkte vorhanden sind . Wenn Sie der Meinung sind, dass ein Team mit nur 2 oder 3 Punkten alle Punkte zur Gesamtsumme beitragen sollte, sollten Sie eine der obigen Antworten in Betracht ziehen.

Verwandte Probleme