Excel häufigster Wert in Spalte # den letzten Erschienen ausgeben, falls mehrere Werte gleiche Häufigkeit besitzen

Question

Hallo Excelspezialisten
.
mit MODUS.VIELF.ist es relativ einfach in einer Spalte den häufigst enthaltenen Wert zu ermitteln.
Nachteil, wenn mehrere Werte vorhanden sind mit der gleichen Häufigkeit, wird nicht unbedingt der Wert ausgegeben, welcher als letzter Wert (in der Spalte) die gleiche Häufigkeit erreichte wie seine gleichoft erschienenen "Mitstreiter" vorher.
Ich suche daher eine Excelformel, welche mir aus einer Spalte den häufigst erschienenen Wert ausgibt und falls mehrere gleiche Häufigkeit haben, den letzterschienenen Wert.
.
Super wäre es zudem, wenn man noch eine Bedingung einbauen könnte. Dass nur die Häufigkeiten von Werten zw. 1 und z.B. 50 berücksichtigt werden dürfen.

(im Suchbereich gibt es keine Leerzellen und keine Zellen mit Text - nur Zahlen)

Hoffe auf baldige Hilfe
Ciao der Revanchist
. 

Answer 1

Guten Morgen,

ich fürchte ohne eine neuere Version von Excel in der die LET Funktion verfügbar ist wird das schwierig in einer Formel (man kann aber die einzelnen Schritte in Hilfsspalten ausführen)

=LET(myinput;C3:C27;myfilter1;FILTER(myinput;(myinput>0)*(myinput<=50));myfilter2;FILTER(myfilter1;XVERWEIS(myfilter1;MODUS.VIELF(myfilter1);MODUS.VIELF(myfilter1);-1)>0);myoutput;INDEX(myfilter2;ANZAHL(myfilter2);1);myoutput)

ich erwähne noch, dass die -1 im XVERWEIS, wenn man den Bereich 1 bis 50 anders wählt und -1 da hineinfiele anders wählen müsste

Ergänzung

=INDEX(C3:C24;MAX((C3:C24=MTRANS(MODUS.VIELF(FILTER(C3:C24;(C3:C24>=1)*(C3:C24<=50)))))*ZEILE(C3:C24)*(C3:C24>=1)*(C3:C24<=50))-MIN(ZEILE(C3:C24))+1)

sollte auch funktionieren, allerdings nehme ich an bei größerer Datenmenge könnte es ein Problem werden (und ist wesentlich weniger übersichtlich)

Falls Filter nicht verfügbar ist
=INDEX(C3:C24;MAX((C3:C24=MTRANS(MODUS.VIELF(C3:C24)))*ZEILE(C3:C24)*(C3:C24>=1)*(C3:C24<=50))-MIN(ZEILE(C3:C24))+1)
allerdings kommt dann #Wert wenn der häufigste Werte ausschließlich außerhalb von 1-50 liegt

Answer 2

Hallo Anonym

das mit der LET Funktion klappt mit meiner aktuellen Excelversion 2010 nicht und auch bei Office 2019 nicht.
Deine Ergänzung aber umso besser. Habe den Bereich bis zu 250 Werte getestet - alles prima
Nur das mit der Begrenzung auf Werte zw. 1 und 50 klappt noch nicht vollständig - ist aber erst einmal nicht dramatisch - da das nicht allzuoft benötigt wird. 

Vielen herzlichen Dank für deine Hilfe

Maat et joot - wie der Kölner zu sagen pflegt

Ciao der Revanchist

Answer 3

Hallo Revanchist,

wenn ich deine Frage richtig interpretiere, dann lässt du dir ja nur ein Ergebnis anzeigen, oder irre ich mich da?

Falls das so ist, könntest du den Wertefilter wie folgt umsetzen:

Setze einen Filter auf die Spalte mit den Werten (also z.B. kleiner als 51).

Den häufigsten Wert kannst du dann wie folgt ermittelten (Bereich anpassen!):

=AGGREGAT(13;5;A1:A40)

Hier wird allerdings MODUS.EINF verwendet (mit dem gleichen Problemen, dass wohl der erste am häufigsten vorkommende Wert angezeigt wird). Du musst bei der Anwendung eines Filters die AGGREGAT-Formel verwenden, da MODUS.VIELF bzw. MODUS.EINF gesetzte Filter ignorieren.

Ich habe dir hier aber auch mal eine Lösung für MODUS.VIELF mit Hilfsspalten gebastelt, die ohne Filter auskommt: Beispieltabelle

Gruß

M.O.

Answer 4

Danke M.O
dein Lösungsvorschlag wäre auch ein denkbarer Weg. Jedoch finde ich den Ergänzungsvorschlag von Anonym für meine Zwecke durch mich einfacher einzubauen. Auch weil dieser Vorschlag ohne Hilfsspalten auskommt.

Ich gebe aber zu, wenn Anonym nichts gepostet hätte, würde ich mit deinem Lösungsweg auch schon deutlich weiter kommen.

Ciao der Revanchist

Answer 5

Ich glaube damit sollte sich auch die 1 bis 50 lösen lassen - speziell für den Fall das ein Wert außerhalb der häufigste ist - sieht aber schon etwas fürchterlich aus

=INDEX(C3:C24;MAX((C3:C24=MTRANS(WENNFEHLER(1/(1/((HÄUFIGKEIT(C3:C24;C3:C24*(C3:C24>=1)*(C3:C24<=50))=MAX(WENNFEHLER(HÄUFIGKEIT(C3:C24;C3:C24*(C3:C24>=1)*(C3:C24<=50))*(C3:C24>=1)*(C3:C24<=50);0)))*C3:C24));-100000)))*ZEILE(C3:C24)*(C3:C24>=1)*(C3:C24<=50))-MIN(ZEILE(C3:C24))+1)

Answer 6

Hallo Anonym

fürchterlich anzuschauende Mammutformeln lösen bei mir kein Unwohlsein aus!
Wichtig ist, dass diese funktionieren.

Und das tut dein Ungetüm (grins) in der Tat vortrefflich.
Ich finde leider kein Emojl was meine Freude wirklich exakt abbilden könnte.
Daher vielen vielen Dank, verbunden mit dem Wunsch, dass du diesem Forum noch sehr lange erhalten bleibst.

Ciao der Revanchist