Office: (Office 2016) Bestimmte Werte zählen und absteigend in Liste ausgeben

Hallo, ich möchte eine Auswertung erstellen und komme leider nicht weiter da mir die Sache zu komplex wird. Den Namen der Person und die einzelnen Ergebnisse habe ich immer in einer Zeile eingetragen. Der höchste Einzel-Wert, welcher max. erzielt werden kann, ist 60 (5x12). Alle 60er einer Person sollen zusammengezählt und in einer Liste ausgegeben werden, Gewinner ist die Person mit den meisten 60er. Wenn zwei Personen gleich viele 60er haben dann, gewinnt die Person mit mehr 59er. Mit ZÄHLENWENN kann ich die Ergebnisse abzählen, wie bringe ich dann eine solche Auflistung bzw. einen Vergleich zustande? Danke im voraus, vielleicht kann mir da jemand behilflich sein.

 

die Auswertung 59 ist nicht notwewndig, da es diesen Wert nicht gibt.

Auswertung

	AF
1
2	13
3	7
4	18

Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle	Formel	Bereich	N/A
AF2:AF4	=ZÄHLENWENN(B2:AE2;">"& 60)

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
AF1	0	0	0	0	Fett				Calibri
AF2:AF4	0	0	0	0					Calibri

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.24 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

 

Vielen Dank für die schnelle Antwort! Die Liste ist noch nicht vollständig, der max. Wert sollte jetzt 66 sein. Ab Spalte AF habe ich die 66er, 65er usw. gezählt. Jetzt möchte ich eine Rangliste der Personen mit den meisten 66er und dann absteigend. Also Person 1 führt die Liste an (12x 66), dann Person 5 (8x 66) gefolgt von Person 4 (8x 66). Da beide gleich viel 66er haben, soll die 65er dann als Kriterium gewertet werden. Wäre das über eine Pivot-Tabelle möglich?

 

ich kann Dir leider nicht mehr helfen.
Ich habe schon eine Datei mit disem Namen und ich überschreibe nichts.
Ich bin dann raus.
Viel Erfolg noch.

Gruß Hajo

 

Hallo,

nachfolgende Formellösung bedarf Deiner Hilfsspalten rechts von Spalte AE nicht.
(Diese Spalten können gelöscht werden bzw. sind mindestens soweit nach rechts zu verschieben, so dass ab Spalte AG mindestens 4 Spalten frei sind für nachfolgende Formellösung.

Die ergebnissortierte Namensliste ermittelt sich dann mit folgender Formel in AG2, welche weit genug nach unten unten ziehend kopiert wird:

{=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(MMULT((B$2:AE$99=66)+(B$2:AE$99=65)%+(B$2:AE$99=64)%%;MTRANS(SPALTE(B1:AE1)^0))=AGGREGAT(14;6;MMULT((B$2:AE$99=66)+(B$2:AE$99=65)%+(B$2:AE$99=64)%%;MTRANS(SPALTE(B1:AE1)^0));ZEILE(A1)))/(ZÄHLENWENN(AG$1:AG1;A$2:A$99)=0)/(A$2:A$99>0);1));"")}

Die angegebenen {} um die Formel nicht eingeben sondern die Formel ohne diese mit der Tastenkombination [Strg]+[Shift]+[Enter] abschließen!

Die erzielten Ergebnisse der Namen für 66, 65 und 64 ermitteln sich dann mit folgender Formeln in AH2:

=SUMMENPRODUKT(($A$2:$A$99=$AG2)*($B$2:$AE$99=67-SPALTE(A2)))
und diese bis AJ2 ziehend und dann alle nach unten kopieren.

 

Wow, vielen Dank! Durch ziehen der zweiten Formel nach rechts, also bis A?2, werden dann die Werte auch weiter nach unten absteigend erfasst (63, 62, 61 usw.)?

Ich benötige auch noch eine weitere Formel wo der erfasste Maximalwert 60 ist. Die zweite Formel sollte dann so aussehen:
=SUMMENPRODUKT(($A$2:$A$99=$AG2)*($B$2:$AE$99=61-SPALTE(E2)))

Aber die erste Formel bzw. die Sortierung aufsteigend bekomme ich nicht hin.

 

So sollte die Formel dann aussehen wenn die Sortierung auch bis 62 gehen sollte?

=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(MMULT((B$2:AE$99=66)+(B$2:AE$99=65)%+(B$2:AE$99=64)%%+(B$2:AE$99=63)%%%+(B$2:AE$99=62)%%%%;MTRANS(SPALTE(B1:AE1)^0))=AGGREGAT(14;6;MMULT((B$2:AE$99=66)+(B$2:AE$99=65)%+(B$2:AE$99=64)%%+(B$2:AE$99=63)%%%+(B$2:AE$99=62)%%%%;MTRANS(SPALTE(B1:AE1)^0));ZEILE(A1)))/(ZÄHLENWENN(AG$1:AG1;A$2:A$99)=0)/(A$2:A$99>0);1));"")

D.h. ich muss immer alle anderen Werte dahinter anführen und das Prozentzeichen aufsteigen erhöhen?

 

Hallo ripperlgod,

ich halte von einer derartigen Auswertungserweiterung nichts. Falls wirklich der wenig wahrscheinliche Fall auftreten sollte, dass nach dem Vergleich der Anzahl der 3 größten Werte noch Gleichheit bestehen sollte würde ich als nächstes, 4. und auch letztes Kriterium ein Vergleich der erzielten Gesamtsummen in der Auswertung vornehmen.

 

Hi,

ich würde das so lösen:

in AF folgende Formel:
=SUMME(ZÄHLENWENN(B2:AE2;66);ZÄHLENWENN(B2:AE2;65)%;ZÄHLENWENN(B2:AE2;64)%%;ZÄHLENWENN(B2:AE2;63)%%%)
nach unten ziehen.

In AG das für die Auflistung der Namen:

=INDEX(A:A;VERGLEICH(AGGREGAT(14;6;$AF$2:$AF$4;ZEILE(A1));$AF$2:$AF$4;0)+1)

(+1 am Schluß, weil die Namensliste in Zeile 2 beginnt)

 

Danke für eure Unterstützung! Leider benötige ich voraussichtlich eine Rangliste mit Werten von 66 bis 55 bzw. 60 bis 50 hinunter. Da wird mir nichts Anderes übrigbleiben als die Formel zu verlängern, hoffentlich macht Excel da mit.

 

Hi,

wohl eher nicht. Bei 17 Stellen ist Schluß, also spätestens bei 59

 

Hallo Edgar,

ich meine, aller spätestens bei 66-60 möglicherweise aber schon bei dem 66-61 (wenn es mehr als 9x 66er Treffer geben könnte) kann in Excel noch mit korrekt auswertbaren Ergebnissen gerechnet werden, wenn die Auswertung so erfolgen soll, wie es der TE beschreibt (was wie ich bereits schon geschrieben, als keine gute Auswertung ansehen würde)

Zu Deinem Vorschlag einer Hilfsspaltenauswertung für die höchsten 4 Werte. Diese lässt sich wie folgt verkürzen zu:

=SUMMENPRODUKT((B2:AE2={66;65;64;63})*1%^(ZEILE(A$1:A$4)-1))

Die so aufgezeigte Vierkürzungsart könnte ich natürlich auch analog in meine direkte Auswertung einsetzen, worauf ich hier allerdings jetzt verzichte.

 

Moin ripperl,
befrag doch mal Onkel Gockel nach "Excel & Rang". Nachdem ich XL2016 nie zur Verfügung hatte, sondern aktuell nur 2019 und 2021 (kein Abo!), weiß ich nicht, ob das in der älteren Version schon existiert.

In den genannten Versionen (schon zu haben ab ca. 20.- EUR erhältlich) gibt es gleich drei Funktionen, mit denen Du solche Rankings erstellen kannst. Vielleicht lohnt sich ein Update?

Grüße

 

Hallo d`r Bastler,

für das was der TE als Zielstellung beschrieben hat, dürfte auch die aktuellste XL-Version nicht ausreichend sein und für das das was vorgeschlagen wurde reicht XL2016 völlig aus.

 

Und wie sagte Karl Marx lt. Seyfried zu seinem Sozialismus?

War ja nur so 'ne Idee von mir ;-)

Ellenlange Formeln sind definitiv nicht meins, ich bevorzuge dann doch VBA.

Schöne Grüße