Vor geraumer Zeit habe ich hier gezeigt, wie man eine Werteliste mit Hilfe der Funktionen KGRÖSSTE (bzw. KKLEINSTE) automatisch sortieren kann.
Die vorgestellte Lösung hatte allerdings einen Schönheitsfehler: Sobald die Liste mehrere gleich große Werte enthält, klappt die Sortierung nicht mehr zuverlässig.
Wie man trotzdem die automatische Sortierung hinbekommt, erkläre ich im heutigen Artikel. Die verwendeten Funktionen:
- KGRÖSSTE
- ZUFALLSZAHL
- ZEILE
- INDEX
- VERGLEICH
- ZEILEN
Und so geht’s:
Die Ausgangslage
Ich habe eine unsortierte Liste mit Städten und deren Einwohnerzahlen, die ich an anderer Stelle automatisch nach Größe sortiert ausgeben möchte (die Beispielliste kannst du hier herunterladen):
Die Ausgangslage
Links befindet sich die unsortierte Liste, rechts davon die mit Hilfe von KGRÖSSTE sortierte Version. Zur Erinnerung: Die KGRÖSSTE-Funktion gibt den k-größten Wert einer Liste aus:
=KGRÖSSTE(Liste; k)
Wer es etwas genauer wissen möchte, kann hier das Beispiel nochmal nachlesen.
Bei näherer Betrachtung sieht man, dass es in meiner unsortierten Liste zwei Städte mit exakt gleicher Einwohnerzahl gibt: Istanbul und Fantasia-City. Und das führt in der rechten sortierten Liste zu dem unschönen Ergebnis, dass Istanbul doppelt angezeigt wird:
Fehlerhaftes Ergebnis
Wodurch kommt das?
Wie man in der Bearbeitungszeile sieht, wird die Stadt mit Hilfe einer Kombination aus INDEX und VERGLEICH ermittelt. Vereinfacht gesagt sucht diese Formel nach der Einwohnerzahl und zeigt dann die erste gefundene Stadt mit genau dieser Einwohnerzahl an. Und das ist natürlich Istanbul.
Wie löst man dieses Problem?
Variante 1: Der Zufall hilft
Ein Leser hatte mir diese Lösung geschickt (Danke an Herrn Hoffmann an dieser Stelle). Der Gedankengang ist sehr einfach: Man muss nur dafür sorgen, dass es keine identischen Werte gibt. Dies lässt sich über eine Hilfsspalte realisieren, in der zur Einwohnerzahl eine Zufallszahl addiert wird:
Der Zufall hilft
Die Funktion ZUFALLSZAHL liefert einen mehr oder weniger beliebigen Wert zwischen Null und Eins. Da uns bereits ein kleiner Unterschied genügt, dividieren wir die Zufallszahl noch durch 1000. Dies wäre bei Einwohnerzahlen zwar nicht nötig, aber wenn es sich bei der Werteliste z.B. um Beträge mit Nachkommastellen handelt, macht es Sinn. Schließlich wollen wir die Beträge an sich ja nicht verändern.
Damit haben wir eine funktionierende Lösung:
Lösung 1: Hilfsspalte mit Zufallszahl
Die tatsächliche Einwohnerzahl holen wir uns über die INDEX-/VERGLEICH-Kombination.
Variante 2: Die Zeilennummer
Die Variante 1 funktioniert grundsätzlich sehr gut, hat lediglich einen kleinen Nachteil: Die verwendete Funktion ZUFALLSZAHL berechnet permanent neue Werte, sobald man irgendwo in der Tabelle einen Wert ändert (<Klugscheißmodus an> Es handelt sich um eine sogenannte volatile Funktion – dazu werde ich nächste Woche etwas mehr erzählen <Klugscheißmodus aus>)
Was dazu führt, dass die Hilfsspalte ständig „in Bewegung“ ist und die Tabelle damit etwas unruhig wirkt. Daher habe ich mir eine leicht abgewandelte Version überlegt.
Ebenfalls in einer Hilfsspalte setzte ich anstelle der ZUFALLSZAHL-Funktion die ZEILE-Funktion ein. Diese ermittelt lediglich die Zeilennummer der jeweils aktuellen Zelle. Damit sich das Ganze im hinteren Nachkommabereich abspielt, habe ich die Zeilennummer dann wieder durch eine große Zahl dividiert:
Hilfsspalte mit der ZEILE-Funktion
Da in jeder Zeile nur eine Einwohnerzahl steht, bekomme ich wieder eindeutige Werte (ok, in extrem unwahrscheinlichen Ausnahmefällen kann es auch hier zu identischen Werte kommen). Und damit ergibt sich folgendes Lösungsbild:
Lösungsvariante 2
Wie man sieht, führen in Excel fast immer verschiedene Wege zur Lösung. Wenn dir noch eine weitere einfällt, lass es uns doch unten in den Kommentaren wissen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
wieder ein interessanter Beitrag. Den Weg mit der Zeilennummer finde ich schon sehr gut. Da kann es wohl gar nicht zu Dopplungen kommen.
Zur Ergänzung noch eine Idee von mir:
=B3+(LÄNGE(A3)/10000)
Die Wahrscheinlichkeit, dass Einwohnerzahl und Wortlänge identisch sind, ist, glaube ich, gering.
Viele Grüße
Gerhard
Hallo Gerhard,
die Ergänzung von Dir mit der Wortlänge ist eine sehr gute Idee! Und wenn man das noch mit der Zeilennummer kombiniert, ist die Wahrscheinlichkeit für identische Werte so groß, wie bei einem Lotto-Sechser vom Blitz erschlagen zu werden 🙂
Schöne Grüße,
Martin
Hallo Martin,
die Formel KKLEINSTE hilft mir schon sehr weiter! Ich stehe allerdings vor dem Problem, dass ich verschiedene Zeilen nach Datum ordnen muss. Hier funktioniert leider weder Zufallszahl noch Zeile.
Hast Du vielleicht noch einen anderen Tipp auf Lager, wie ich verschiedene Einträge automatisch ohne Makro nach Datum ordnen kann?
Danke und viele Grüße
Patrick
Hallo Patrick,
mit dem Datum sollte es eigentlich genauso funktionieren, da Excel-intern ein Datum auch nur eine fortlaufende Zahl ist.
Schöne Grüße,
Martin
Hallo Martin,
also ich nutze für automatische Sortierung immer die formel RANG.GLEICH (bei früheren Excel Versionen nur RANG).
=RANG.GLEICH(Zahl;Bezug;Reihenfolge), wobei ich über den Parameter der Reihenfolge mit der gleichen Formel vorgeben kann, ob ich auf- oder absteigend sortieren möchte.
Mein Vorgehen:
Vor die unsortierte Tabelle füge ich eine Hilfsspalte ein, in die die RANG-Formel geschrieben wird.
Die erste Spalte der sortierten Tabelle ist wieder eine Hilfsspalte in der nur die Zahlen 1,2,3,4… etc. stehen.
Anschließend sortiere ich die Elemente der unsortierten Tabelle über Sverweis.
Die Hilfsspalten werden abschließend noch ausgeblendet.
Ich hoffe, dass ich meine Vorgehensweise einigermaßen verständlich beschrieben habe.
Viele Grüße Marco
p.s. auch bei dieser Formel hat man das oben beschriebene Problem mit zwei gleichen Werten, welches ich auch über die 2. Variante mit der Zeilennummer löse.
Hallo Marco,
vielen Dank für den Hinweis auf die Funktion RANG.GLEICH, das ist ein guter Tipp!
Schöne Grüße,
Martin
Hallo Martin,
dort, wo die Sortierfunktion von Excel versagt, kommt man mit Formeln tatsächlich ein wenig weiter – und dazu ist jetzt sogar alles dynamisch 🙂
Viele Dank für deine Anleitung! Du ahnst gar nicht, wie sehr du mir geholfen hast!
Beste Grüße
Maik
Hallo Maik,
vielen Dank für das nette Feedback!
Schöne Grüße,
Martin
Hallo Martin,
ein sehr interessantes Thema welches hier behandelt wird!
Ich bin aber auf der Suche nach einer Lösung mit unterschiedlichen Datumsangaben. Erstes Kriterium ist das Datum der Einstellung, sollte das identisch sein, soll der ältere Kollege genannt werden. Ich zerbreche mir seit 2 Tagen darüber den Kopf ob und ja wenn wie soetwas umsetzbar ist.
Vlt. hast du ja eine Lösung.
Viele Grüße
Alex
Danke für die tolle Anleitung. Ich habe mir hiermit gerade einen kleinen Passwortgenerator erstellt, wobei ich frei wählen kann zwischen der Anzahl der Zahlen, Sonderzeichen und Zeichen, der Länge des Passwortes und auch der erlaubten Sonderzeichen im Selbigen.
Hallo Philipp,
freut mich sehr, wenn die Anleitung eine praktische Anwendung findet.
Schöne Grüße,
Martin
Vielen Dank. Das hat mir als Ansatz schonmal sehr geholfen. Wie kann ich allerdings negative Zahlen aus der Liste herausnehmen? Wie können Daten ab einer bestimmten Anzahl (z.B. alle Städte mit mehr als 500.000 Einwohner) ausgenommen werden?
Ist meine Frage unverständlich oder hat keiner die Ahnung das umzusetzen?
Hallo Benny,
zumindest ich habe keinen Vorschlag, und offensichtlich von den anderen Lesern auch niemand. Wobei ich tatsächlich zugeben muss, dass mir nicht ganz klar ist, was genau bei der von Dir gewünschten Sortierung herauskommen soll, wenn bestimmte Werte ausgeschlossen werden sollen.
Grüße,
Martin
Bei mir wird nicht per Einwohnerzahl sortiert sondern nach Tagen. Meine aktuelle sortierte Liste sind derzeit folgendermaßen aus: -4 -3 -2 -1 0 1 2 3 4 …… Nun soll mir die sortierte Liste allerdings nur positive Werte anzeigen. Also 1 2 3 4 …….. Auch soll ein Höchstwert angegeben werden. Sprich, es sollen nur Werte bis 3 angezeigt werden, so dass meine sortierte Beispielsliste nicht -4 -3 -2 -1 0 1 2 3 4 sondern 1 2 3 ausgibt.
Hallo Benny,
tut mir leid, da muss ich passen.
Grüße,
Martin
HI Benni,
ich hoffe das hilft dir jetzt auch noch.
Also in Spalte A stehen deine Werte {-4,-3,…,3,4}.
In Spalte B errechnest du den Rang ab deinem Minimalwert (in diesem Bsp. 0)
und lässt bei überschreiten des Maximalwertes (in diesem Bsp. 3) direkt eine „0“ ausgeben.
Zelle B12 habe ich in Min umbenannt (Wert=0)
Zelle B13 habe ich in Max umbenannt (Wert=3)
Sp A Spalte B [Sp B]
-4 =WENN(A2>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 0
-3 =WENN(A3>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 4
-2 =WENN(A4>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 0
-1 =WENN(A5>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 2
0 =WENN(A6>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 1
1 =WENN(A7>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 0
2 =WENN(A8>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 3
3 =WENN(A9>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 0
4 =WENN(A10>Max;0;ZÄHLENWENNS(A$2:A$10;“=“&Min)) 0
Spalte C [Sp C]
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B2);B$2:B$10;0));““) 0
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B3);B$2:B$10;0));““) 1
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B4);B$2:B$10;0));““) 2
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B5);B$2:B$10;0));““) 3
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B6);B$2:B$10;0));““)
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B7);B$2:B$10;0));““)
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B8);B$2:B$10;0));““)
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B9);B$2:B$10;0));““)
=WENNFEHLER(INDEX(A$2:A$10;VERGLEICH(ZEILEN($B$2:B10);B$2:B$10;0));““)
Dann viel Spaß beim ausprobieren!
Viele Grüße!
Hallo Benny.
Hallo Martin.
Das dürfte sich nur über einen Zwischenschritt (zusätzliche Spalte) lösen lassen. Erst die Liste auf die gewünschten Einträge beschränken
B1=WENN(ODER(A1<=0;A1>3);““;A1)
B2=WENN(ODER(A2<=0;A2>3);““;A2)
…
und dann sortieren und die Fehler abfangen
C1=WENN(ISTFEHLER(KKLEINSTE(B$1:B$100;1));““;KKLEINSTE(B$1:B$100;1))
C2=WENN(ISTFEHLER(KKLEINSTE(B$1:B$100;2));““;KKLEINSTE(B$1:B$100;2))
…
Richtig „sauber“ lässt sich das nur über eine selbstdefinierte Funktion (VBA) lösen.
Gruß
Stefan
PS: Nette informative Seite. 🙂
Hallo Martin.
Kann man in den Antworten keine Größer-, Kleiner-, Keinergleich-Zeichen etc. verwenden? Meine Formeln werden immer falsch wiedergegeben.
In den Klammern zum Befehl ODER müsste eigentlich
(A1 kleinergleich 0;A1 größer 3) bzw. (A2 kleinergleich 0;A2 größer 3)
stehen.
Gruß
Stefan
Hallo Stefan,
ich habe Deinen ersten Kommentar jetzt entsprechend angepasst. Bei mir hat es mit den Größer-/Kleiner-Zeichen funktioniert, aber vielleicht auch nur, weil ich Admin bin. Das liegt vermutlich daran, dass diese beiden Zeichen zum Umschließen von HTML-Tags verwendet werden.
Danke auf jeden Fall für Deinen Tipp!
Schöne Grüße,
Martin
Hallo Martin,
ein weiterer Weg wäre, die Orte gleich mit zu sortieren.
Dazu bietet sich die naive Version an, hier z.B. für die Zeile 7…
=B7+(TEXT(CODE(LINKS(A7;1));“000″)&TEXT(CODE(TEIL(A7;2;1));“000″)&TEXT(ZEILE();“0000″))/10^10
… die aber nicht sauber funktioniert, wenn die Einwohner in „Einern“ statt in 1000ern angegeben werden, weil offensichtlich die Anzahl von Excels signifikanten Stellen überschritten wird.
Für München mit 1400 (in 1000) Einwohnern spuckt die Formel aus: 1400,0772520007
Eine Verbesserung wäre der Soundex-Algorithmus (z.B. hier zu finden: http://rosettacode.org/wiki/Soundex#VBScript), aber das geht halt kaum ohne eine UDF: der Vorteil liegt auf der Hand: man muß nur das 1. Zeichen in CODE umwandeln, dann folgen immer exakt 3 Ziffern.
Übrigens ist Index/Vergleich für die EW-Spalte verschenkte Rechenleistung: =Ganzzahl(hilfsspalte) tut’s auch.
Ansonsten vielen Dank für die Anregungen!
Schöne Grüße,
Michael
Hallo Martin,
erst einmal vielen Dank für die tollen Anleitungen, die ich ausgesprochen hilfreich finde. Ein Problem/Frage habe ich alerdings noch: Ich möchte die beschriebene Sortierfunktion auf Namen/Text anwenden. Auch hier kann es zu Doppellungen kommen, alo ein Name zweimal auftauchen. In der Ausgabeliste soll er dann aber nur einmal auftauchen. Wie kann man das Problem lösen?
Danke für eine Antwort.
Viele Grüße
Stephan Wölbert
Guten Tag
Als Erstes: herzlichen Dank für die Anleitungen zur autom. Sortierung Teil 1 bis 3. Selbst für einen blutigen Laien wie mich ist alles verständlich, und es funktioniert! Das will etwas heissen……
Ein Problem habe ich allerdings noch. Ich versuche zu erklären, worum es bei mir geht.
Es geht um Sport und Ranglisten. Durchgeführt werden 4 Rennserien ( Welt-Cup, Intercontinantal-Cup, Europa-Cup, Nord-Amerika-Cup), aus denen eine Weltrangliste erstellt wird über jeweils 8 Rennen maximal, egal, egal in welcher Rennserie diese absolviert wind. Am besten ist diese Weltrangliste mit der Tennis-Weltrangliste der ATP od. WTA vergleichbar. Jede Klassierung ergibt Punkte, wobei die nach Rennserien unterschiedlich sind.
Für die Erstellung der Weltrangliste (über alle 4 Serien) habe ich je eine Excel-Tabelle „Männer“ und „Frauen“, in die ich via Ergebnis-Tabellen die Resultate eingeben kann. Die Tabelle dient mir als Quelle.
Was ich jetzt auf der Basis dieser Quelle erstellen will ist eine Rangliste pro Rennserie. Und zwar so, dass ich diese nicht von Hand sortieren muss. Wie gesagt, gelingt mir das nach Anwendung von Teil 1 bis 3.
Ich habe folgendes gemacht:
Pro Rennserie existiert eine Tabelle „Ranking Männer“ und Ranking „Frauen“
Im Teil „unsortiert“ hole ich aus der Tabelle „Männer“
-alle lizenzierten Männer, egal, in welcher Serie sie mehrheitlich starten (sie können nämlich auch in mehreren Serien an den Start gehen)
-in einer weiteren Kolonne hole ich mit SVERWEIS aus der Quelle die Punkte für die entsprechende Rennserie
-in einer weiteren Kolonneermittle ich mit MAX auf der jeweiligen Zeile des Sportlers in der Quelle den SCHLECHTESTEN Rang, wobei ich den durch 100 dividiere
-um die berühmten Doppler zu umgehen, ermittle ich in einer weiteren Kolonne mit ZEILE() die zu ermittelnde Zeilen.Nr. und dividiere diese durch 10000
-jetzt addiere ich Punkte, schlechtesten Rang und Zeilennummer zu einem Wert in einer Kolonne, die ich nach KGRÖSSTE sortiere.
Wunderbar, meine Rangliste steht. Aber:
In den Regeln des Verbandes steht, dass bei Punktgleichheit das bessere Einzelergebnis entscheidet. Weil ich aber die SCHLECHTESTE Platzierung mit MAX ermittle und nach KGRÖSSTE sortiere geschieht genau das Gegenteil: der mit dem schlechteren Einzelergebnis steht bei Punktgleichheit vor dem mit dem besseren!
Klar, also das beste Ergebnis mit MIN ermitteln und mit KKLEINSTE sortieren! Einfach, oder ? Schön wär’s! Weil es eben solche gibt, die keine Rennen gefahren sind oder eines in der Serie ausgelassen haben, ist die Beste Platzierung NULL. Und dann funktioniert nichts mehr….
Ich suche jetzt verzweifelt nach einer Möglichkeit, diese Nuller auszuschliessen, fine sie aber nicht. Irgendwie etwas mit der WENN-Funktion? Aber wie müsste dann die Syntax heissen?
Ist (hoffentlich) jemand aus dem von mir Geschriebenen schlau geworden und hat darüberhinaus sogar noch eine Idee? Das wäre bärig!
Mit freundlichen Grüssen
Daniel
PS: der entsprechende Sportverband hat natürlich diese Ranglisten nach Serien auf seiner Web-Site. Aber verdammt, es reizt mich einfach, das selbst hinzukriegen!
Hallo,
super Beitrag.
Ich persönlich brauche diese Funktionskombi für ein Glossar, das ich auf einer Webseite erstellen möchte. Dazu habe ich aber eine Frage, die auch genau das Thema der identischen Einträge tangiert.
Grundsätzlich: Über eine Maske (Google-Formular) werden Daten gesammelt und Zeile für Zeil in eine Excel (Google-Tabellenblatt) übertragen. Soweit so gut. Bei einem Glossar geht es natürlich 1) in erster Linie um Stichworte und 2) in meinem Fall um Autoren in zweiter Linie. D. h. ich habe z. B. das Stichwort ‚Hammer‘ und trage darunter ein, was Herr Heidegger oder/und Herr Chomsky dazu geschrieben haben.
Die Excel-Tabelle hat also min. 3 Spalten: Stichwort, Autor, Beitrag.
Sortiert soll nun werden in einem weiteren Tabelleblatt oder -dokument zunächst nach Stichwort, dann nach Autor, sodass aus
A2 : Hammer B2 : Heidegger C2 : Funktion
A3 : Zange B3 : Wittgenstein C3 : Wille
A4 : Hammer B4 : Chomsky C4: Struktur
das Folgende rauskommt:
A2 : Hammer B2 : Chomsky C2 : Struktur
A3 : Hammer B3 : Heidegger C3 : Funktion
A4 : Zange B4 : Wittgenstein C4: Wille
Im Prinzip wäre hier die Formel sozusagen nicht über die Zufallsfunktion o. ä. zu berichtigen, sondern über den alphabetischen Rang des Werts rechts davon. Es wäre sozusagen wie das Sortieren auf zwei Ebenen.
Hast Du eine Idee?
Grüße
Dominik
Lösung gefunden:
Ich habe einfach die jeweilige Zelle in Spalte A mit der in Spalte B verkettet (=A2&B2) und meine Formel darauf bezogen.
Ich bin beim googlen nach einer Lösung auf diese Seite gestossen, allerdings möchte ich keine Spalten, sondern Zeilen in eine separate Tabelle bekommen.
Ausgangslage: 15 Teamnamen in den Feldern A1 bis O1. Die Endresultate der Teams stehen in den Feldern A24 bis O24.
Diese Werte aus Zeile 24 brauche ich jetzt in einer Tabelle mit den Teamnamen in R2 bis R16 und den Punkteständen von hoch nach tief in S2 bis S16.
Geht das?
Hallo Uwe,
ohne hier schon eine fertige Lösung parat zu haben: Probier‘ doch mal die im Artikel beschriebene Lösung und ersetze die Funktionen ZEILE bzw. ZEILEN durch die Funktionen SPALTE und SPALTEN. Vielleicht kommst Du ja damit bei Deinem Problem ein wenig weiter.
Schöne Grüße,
Martin
Hallo Martin,
danke für die Erklärung,
kann du vielleicht mir bei meinem Problem weiter helfen.
Ich habe volgende Tabelle
Spalte A – Typ Spalte B Länge so soll nach sortieren aussehen Spalte A Typ Spalte B Länge
28 30 28 27
30 31 28 30
31 28 30 29
28 27 30 31
31 30 31 28
30 29 31 30
mit welche formen kann ich das sortieren. das ich diese Ergebnis bekomme
danke für eure hilfe
Hallo Natalia,
Du könntest die beiden Spalten A und B in einer Hilfsspalte zusammenfassen (z.B. „=A1&B1“) und dann nach dieser Hilfsspalte sortieren.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die super Excel Lösung. Nachvollziehen kann ich sie, leider bin ich nicht so gut im Formel kombinieren innerhalb der Funktionen.
Gibt es eine Lösung, dass die Hilfspalten vermieden werden können? Sodass man die Formeln für die Hilfsspalten in die finale Spalte/ Zelle der Sortierung integrieren kann?
(Spalten auszublenden möchte ich vermeiden)
Vielen Dank im voraus für eine Antwort und Vorschläge!
Viele Grüße
Vincent
Hallo Vincent,
spontan fällt mir leider keine Formellösung ein, die auf eine Hilfsspalte verzichten könnte.
Schöne Grüße,
Martin
Hallo Martin,
vorab erstmal Kompliment zu den Beiträgen. Sehr gut veranschaulicht und sehr gut abstrahierbar.
Nun zu meinem Problem:
Ich benötige auch diese Sortierung und bin den Weg mit der Zeilennummer gegangen. Das ganze habe ich per Makro geschrieben, da die Tabelle on the fly erzeugt wird.
Jetzt mein Problem… das ganze Funktioniert, aber nur wenn ich einmal manuell in die Zelle hinein bin und ENTER gedrückt habe.
Auf diese Weise erzeuge ich die Formel per Makro:
Worksheets(„Tabelle_unsichtbar“).Cells(1 + 1 + iAnzahlMitspieler, 8).Formula = „=VALUE(“ & Cells(1 + 1 + iAnzahlMitspieler, 2).Address & “ + ZEILE(“ & Cells(1 + 1 + iAnzahlMitspieler, 2).Address & „)/1000)“
Daraus wird dann in Excel die korrekte Formel:
=WERT($B$3 + ZEILE($B$3)/1000)
Das Problem ist, dass in der Zelle in der Die Formel steht, jetzt „#NAME?“ steht. Erst wenn ich einmal in diese Zelle hineingeklickt habe und mit Enter wieder verlasse (ohne eine Änderung vorzunehmen), wird die Formel ausgewertet.
Also die Formel funktioniert, aber ich muss sie manuell einmal ausführen um sie upzudaten.
Was kann ich machen, damit das ganze automatisch geschieht?
Hallo Christian,
ich gebe hier eigentlich keine VBA-Tipps, aber versuche es mal mit der FormulaLocal-Variante:
Worksheets(“Tabelle_unsichtbar”).Cells(1 + 1 + iAnzahlMitspieler, 8).FormulaLocal = “=WERT(” & Cells(1 + 1 + iAnzahlMitspieler, 2).Address & ” + ZEILE(” & Cells(1 + 1 + iAnzahlMitspieler, 2).Address & “)/1000)”
Schöne Grüße,
Martin
Hallo Martin,
Super Lösungen! Vielen Dank auch für die verständliche Anleitung.
Gibt es vielleicht auch eine Möglichkeit die Formatierung der Zellen aus der unsortierten Datei mit in die sortierte Datei zu übertragen?
Viele Grüße
Dirk
Hallo Dirk,
Formatierungen lassen sich per Formel leider nicht übertragen. Es sei denn, du arbeitest in der unsortierten Liste mit bedingter Formatierung. Dann könntest du unter Umständen ähnliche Regeln für die sortierte Liste aufbauen.
Schöne Grüße,
Martin
Hallo Martin,
beachtlich, was ich hier auf Deinen Seiten alles lernen konnte. Anschaulich und nachvollziehbar erklärt. Mich ermutigen die Kommentare auch eine Anfrage zu stellen:
Ich habe eine 1-spaltige Tabelle mit 12 Dezimalzahlen. Der ganzzahlige Teil vor dem Komma soll absteigend, der Teil rechts vom Komma (6Stellen) soll bei gleichem ganzzahligen Teil aufsteigend sortiert werden.
Könntest Du bitte mit einem Hinweis helfen?
Viele Grüße
Johannes
Hallo Johannes,
damit du so sortieren kannst, musst du die Dezimalzahlen in zwei zusätzlichen Spalten aufteilen: In einer Spalte den Teil vor dem Komma, in der zweiten Spalte den Nachkommateil. Und dann kannst du nach diesen beiden Spalten in der gewünschten Reihenfolge sortieren. Die Aufteilung kannst du entweder über die Funktion „Text in Spalten“ vornehmen, oder per Formel mit LINKS bzw. RECHTS. Angenommen, in A2 steht die Dezimalzahl, dann könntest du die Aufteilung so vornehmen:
=LINKS(A2;FINDEN(„,“;A2)-1)
=RECHTS(A2;FINDEN(„,“;A2)-2)
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für Deine Hinweise. Vorab: In A2 steht die Dezimalzahl
Mit LINKS… erhalte ich in der 1. Hilfsspalte Werte, die ich erwartet habe.
Mit RECHTS… erhalte ich in der 2. Hilfsspalte Werte, die ich nicht erwarte und die ich nicht nachvollziehen kann.
Beispiele:
5,3021 wird mit Rechts zu Leerfeld
10,1030 wird mit RECHTS zu 3
25,5100 wird mit RECHTS zu 1
100,4100 wird mit RECHTS zu 41
Wenn ich Deine Idee mit 2 Hilfsspalten aufgreife, könnte in der
1. Hilfsspalte =GANZZAHL($A2) stehen und in der
2. Hilfsspalte =$A2-$B2
Wenn ich dann die 2. Hilfsspalte absteigend sortiere und die 1. Hilfsspalte aufsteigend sortiere, verlieren die Ganzzahlen ihre „richtigen“ Dezimalstellen.
Beispiel:
5,3204
5,1204
10,5533
15,3214
100,3611
100,1111
soll werden zu
5,1204
5,3204
10,3214
10,5533
100,1111
100,3611
Mir würde auch der Rang in einer Hilfsspalte genügen, weil ich den Rang ja wieder sortieren kann.
Vielleicht gibt es ja doch noch eine einfache Lösung.
Viele Grüße
Johannes
Hallo Johannes,
du hast Recht, in meiner Formel steckt ein Fehler. Und die Lösung über die GANZZAHL-Funktion ist natürlich deutlich einfacher 🙂
Aber die Sortierung funktioniert:
Du markierst alle drei Spalten (Dezimalzahl, Ganzzahl und Nachkommazahl) und wählst dann im Menü „Daten | Sortieren“ (also nicht über die A-Z Symbole). Als erste Ebene „Sortieren nach“ wählst du in dem Sortieren-Fenster das Feld Ganzzahl, als zweite Ebene das Feld Nachkommazahl. Und natürlich in der jeweils gewünschten Reihenfolge (auf- bzw. absteigend).
Und dann sollte auch die gewünschte Sortierung herauskommen.
Schöne Grüße,
Martin
Hallo Martin,
Deine KapitelÜberschrift lautet „automatisch sortieren“, deswegen habe ich angefragt.
Klar, die von Dir genannte Vorgehensweise funktioniert immer.
Ob es eine automatische Lösung gibt?
Freundliche Grüße
Johannes
Ich möchte gern für meine Ärztin eine Patientenliste erstellen mit Geburtsdaten.
Nach den markieren Sortiert sich die Liste nach dem gewünschten ABC . Nun ergibt es sich das ein Patient dazu kommt . Kann man eine Automatische Formel eingeben, daß sich der neu hinzugekommene einsortiert ohne das man wieder alles markieren muss ?
Mit freundlichen Grüssen Annett Ellendorff
Hallo Annett,
ich sehe da zwei Möglichkeiten:
Entweder man definiert von Anfang an gleich einen größeren Datenbereich (man baut also auf Vorrat weitere Zeilen ein).
Und wer Office-365 einsetzt, kann sogar auf die neuen und extrem komfortablen dynamischen Filter- und Sortierfunktionen zurückgreifen. Dazu hatte ich in den beiden Artikeln schon mal einen Vorgeschmack gegeben:
Ein Blick in die Zukunft: Dynamische Arrayfunktionen
Dynamische Arrayfunktionen (Teil 2)
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine schönen Anleitungen.
Ich habe diese genutzt, um eine automatische Geburtstagsliste für unsere Geschäftszimmerdamen zu erstellen.
Diese soll für sie extra einfach bleiben, sie müssen lediglich in das Tabellenblatt „Personal“ den Namen, Vornamen und das Geburtsdatum der neuen Kollegen eintragen.
Deine Formeln erzeugen dann auf weiteren Tabellenblättern z.B. einen übersichtlichen Geburtstagskalender, eine Übersicht darüber, welcher Kollege aktuell wie alt ist und eben dann auch eine sich selbst alphabetisch sortierende Übersicht aller Kollegen.
Bei uns existieren nun aber jeweils zwei Kollegen mit den Familiennamen „Müller“ und „Schmitt“.
Ich habe dieses Blatt nach Teil 2 deiner Anleitung erstellt und hier führt die Dopplung dieser beiden Namen dazu, dass die ZÄHLENWENN-Funktion den ersten jeweils Schmitt / Müller überspringt und stattdessen “ #NV “ auswirft. Der jeweils zweite Schmitt / Müller wird wieder ganz normal angezeigt.
In der Hilfsspalte mit der Zeilenzahl ist mir folgendes hierzu aufgefallen:
Die Kollegen befinden sich eigentlich an der 23. bzw 33. Stelle der Originaltabelle „Personal“
Aufgrund der Funktion lässt sie aber diese beiden Zeilen in der Aufzählung aus und führt stattdessen die Zeilen 24 und 34 doppelt (also der gesuchte Wert steht in Zeile … 21, 22, 24 (!), 24, 25, …).
Somit kann der Wert aus den Zeilen 23 und 33 nicht ausgegeben werden.
Kannst Du dir eine Lösung für mein Problem vorstellen, damit die Kollegen Schmitt und Müller künftig wieder ihren Geburtstag bei uns feiern dürfen?
Vielen lieben Dank im Voraus und ein fröhliches Osterfest
Sebastian Beer
Hallo Sebastian,
vielen Dank, auch Dir noch ein schönes Osterfest! Um das Problem über Formeln lösen zu können, musst du auf irgendeine Art eine Eindeutigkeit der Namen herstellen. Am einfachsten geht das über eine Hilfsspalte, in der Name und Vorname per Formel zusammengefasst werden, z.B. =A2&B2
Und die Sortierungsfunktion arbeitet dann eben mit dieser Hilfsspalte.
Noch eleganter geht es, wenn du Office 365 in der aktuellsten Version im Einsatz hast. Über die neuen Array-Funktionen SORTIEREN bzw. SORTIERENNACH spart man sich alle anderen Formeln und Hilfsspalten.
Schöne Grüße,
Martin
Hallo Martin,
das Antworten auf deinen Kommentar hat bisher nicht so gut geklappt, ich hoffe heute funktioniert es.
Vielen Lieben Dank für deine Hilfe, das hat wundervoll funktioniert und ich habe einen netten, kleinen Kniff dazugelernt.
Ich habe das Dokument nun soweit vollständig, aber wenn ich die Geburtstagsliste nach Monaten sortieren möchte (was ja eigentlich ihr Zweck ist) muss ich hierzu die Jahreszahlen ignorieren. Hierzu habe ich in der Datumsfunktion das Jahr dann als „1“ gesetzt, sprich das ausgegebene Datumsformat ist dann TT.MM.1900. So kann ich alle Geburtstage der Kollegen für jeden Monat auflisten.
Das Problem hierbei ist jedoch, dass zwei Kollegen, die am gleichen Tag Geburtstag haben, hierbei wieder einen Sonderfall bilden.
Mit deiner automatischen Sortierung (mit KKLEINSTE) wird hier der erste Wert zwei Mal ausgegeben und der zweite Wert wird nicht beachtet.
Oder: Die Kollegin, die am 25.06.1957 geboren wurde, taucht zwei Mal in der Liste auf und der Kollege, der am 25.06.1987 geboren wurde, gar nicht.
Wenn ich das Geburtsdatum zur Sortierung hinzunehme, sortiere ich ja nach der Jahreszahl und somit nach Alter der Kollegen.
Wenn ich das Geburtsdatum „TT.MM.“ mit dem zugehörigen Familiennamen kombiniere, scheitert die Sortierung gänzlich an der Kombination aus Zahlen und Text.
Auf der Arbeit kann ich mir leider nur mit der Standardlizenz verhelfen, die SORTIERENNACH Funktion wäre hier mit Hilfsspalten perfekt für meine Zwecke. Kennst du einen Weg, die Funktion nachzubasteln?
Viele Grüße
Sebastian
Hallo Sebastian,
ja, mit den neuen dynamischen Arrayfunktionen SORTIEREN und SORTIERENNACH kann man sich die ganzen Kopfstände sparen – sofern man halt Office 365 im Einsatz hat. Ansonsten musst du auf irgendeinem Weg eine Eindeutigkeit der Werte sicherstellen. Beispielsweise wie oben im Artikel beschrieben eine sehr kleine Zahl zum Datum addieren. Bruchteile von ganzen Zahlen werden in einem Datum wie Uhrzeiten behandelt und damit kommst du wieder zu eindeutigen Werten.
Schöne Grüße,
Martin
Vielen Dank für die leicht verständlichen Erläuterungen. Heute war ein erfolgreicher Tag… ich habe viel gelernt. Mein Diagramm sieht schon viel aufgeräumter aus!
Ich bin mit einer Umfrage-Auswertung beschäftigt. darin werden ja verschiedene Antworten erwartet; von positiv bis negativ… in verschiedenen Abstufungen. Ich habe nur vier Möglichkeiten vorgegeben: positiv, neutral, negativ, keine Antwort.
Nun stellt sich bei mir die Frage, ob diese Sortierung auch mit einem gestapelten Balkendiagramm (mit %-Balken) möglich ist, also wenn nach der ersten Sortierung (z.B. alle „positiv“-Nennungen) gleiche Ergebnisse ergeben, das zweite Kriterium (z.B. Anzahl „neutral“-Nennungen) hinzugezogen werden könnten.
Ich bin gespannt auf die Antwort!
Hallo Joe,
danke für das tolle Feedback!
Eine fertige Lösung für das Problem kann ich hier nicht anbieten und eine Sortierung nach mehreren Kriterien kann im Einzelfall sehr schwierig sein. Aber vielleicht helfen ja die folgenden Überlegungen weiter:
Für das Diagramm ist ja grundsätzlich die Sortierreihenfolge der Quelltabelle maßgeblich. Das heißt, du musst die Tabelle so sortieren lassen, wie das gewünschte Ergebnis im Diagramm aussehen soll. Das lässt sich vermutlich am einfachsten mit einer entsprechenden Hilfsspalte lösen. Diese Hilfsspalte sollte dann die relevanten Datenspalten in der Reihenfolge miteinander kombinieren (beispielsweise über den &-Operator), die der Ergebnisreihenfolge entspricht.
Vielleicht ist das in deinem speziellen Fall wegen des Tabellenaufbaus nicht möglich, aber das wäre zumindest meine vorgeschlagene Herangehensweise.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die hervorragende Sortierung.
Ich verwende die auch bereits bei mehreren Projekten erfolgreich.
Jetzt zu meinem Problem:
Ich müsste für eine Auswertung zwei sortierungen hintereinander vornehmen.
Einmal nach Priorisierung (was auch ohne Probleme funktioniert) und im zweiten Step nach Datum.
Im ersten Step ist die Sortierung folgendermaßen aufgebaut:
Aufgabe | Termin | Zeile | Erledigt | Bemerkung | Prio wobei nach Prio am ende sortiert wird mit KKLEINSTE und ZEILEN.
Im zweiten Step will ich nun folgendermaßen sortieren:
Aufgabe | Prio | Erledigt | Bemerkung | Zeile | Termin wobei in diesem Fall nach Termin sortiert werden soll.
Allerdings habe ich hier das Problem dass der mir in der Zelle Zeile (und folglich auch in allen anderen) ein paar Werte doppelt bzw. mehrfach anzeigt und andere verschluckt.
Habe das ganze nochmal umgestellt gehabt und die Formeln mehrfach überprüft, komme aber nicht auf den Grund warum das nicht klappen können sollte.
Ist es generell nicht möglich zwei sortierungen hintereinander zu schalten oder was könnte ich noch anders machen?
Ich hoffe auf eine Antwort.
Freundliche Grüße
Phil
Hallo Phil,
ich sehe zumindest keinen generellen Grund, warum man nicht zwei Sortierungen hintereinander schalten könnte. Es hängt also vermutlich mit dem genauen Aufbau, den Inhalten und den Formeln zusammen, die du verwendest. Einen konkreten Tipp für eine Lösung deines Problems habe ich daher leider auch nicht.
Schöne Grüße,
Martin
Hallo,
um identische Werte zu verhindern, verwende ich die Funktion Zeile wie folgt:
=(1/ZEILE())
da erhält man praktisch eine Zufallszahl wo man bei
ZEILE()/100000 oder
ZEILE(B3)/100000
einfach nur ein kleine Zahl erhält, die jedoch nicht zufällig sondern logisch sortiert ist in der Reihenfolge der Zeilen.
Liebe Grüße
Willi
Hallo Martin,
die Diskussionen auf dieser Webseite haben mich motiviert, die in Excel 365 implementierten Sortierfunktionen SORTIEREN und SORTIERENNACH mit Hilfe von VBA nachzubauen. Dazu habe ich eine benutzerdefinierte Funktion (UDF) namens ‚hbSort‘ programmiert, die sowohl die Parameterliste von SORTIEREN als auch die Parameterliste von SORTIERENNACH akzeptiert. Die Funktion ‚hbSort‘ erkennt automatisch, um welche der beiden Parameterlisten es sich handelt.
Somit kommen alle User von Excel 2007 bis 2019 nun auch in den Genuss einer Sortierfunktion.
Dadurch dass der Algorithmus (Quicksort) komplett mit Arrays im Hauptspeicher abläuft, hat die Funktion auch eine akzeptable Geschwindigkeit – für VBA überraschend schnell.
Hier findet man den Download der Funktion ‚hbSort‘ und ausführlichere Informationen – u.a. auch zur Einrichtung der Funktion als Add-In:
https://hermann-baum.de/excel/hbSort/de/
Herzliche Grüße
und DANKE für deine hervorragenden Excel-Veröffentlichungen!
Hermann
Hallo Hermann,
vielen Dank für das tolle Feedback. Und es wird die anderen Leser sicherlich freuen, dass du hier noch gleich ein Add-In erstellt hast, klasse!
Schöne Grüße,
Martin
Hallo,
ich bin ziemlicher Excel Anfänger und bräuchte mal eine Rat, wie die Sortierung von Zahlen, die mit einem „.“ getrennt sind, funktionieren kann. Ich habe in den Spalten A bis CV folgendes zu stehen: 01…01.01…02…02.01…04.02.01…01.01.01…01.02.01….01.01.02.. usw.
Je nachdem, ob Unter- und Unterunterabschnitte dazukommen, werden diese mit einem Punkt getrennt dargestellt.
Dabei können auch immer Abschnitte dazu kommen, die vor den schon vorhandenen liegen. Die Zahlen wurden von Excel in Text formatiert. Bei nur einem Punkt kann ich die Zahlen mit der Funktion Wechseln „=WENNFEHLER(–WECHSELN(N1;“.“;“,“);““)“ und KKleinste „=KKLEINSTE($O$1:$O$8;ZEILE(A1))“ sortieren. Kommt ein zweiter Punkt ins Spiel, funktioniert das ganze nicht mehr. Gibt es dafür eine Lösung?
Vielen Dank.
Gruß
Martin
Moin Martin,
suchst du etwas in der Richtung?
=INDEX($A$1:$A$7;VERGLEICH(KKLEINSTE(ZAHLENWERT(WECHSELN(WECHSELN($A$1:$A$7;“.“;“,“;1);“.“;““);“,“);ZEILE(A1));ZAHLENWERT(WECHSELN(WECHSELN($A$1:$A$7;“.“;“,“;1);“.“;““);“,“);0))
Gruß Marcel
Hallo Marcel,
wow, ja genau sowas. Vielen Dank, wäre ich nie drauf gekommen. War schon stolz auf mich, weil mir folgende Idee gekommen ist: Nämlich die Zahlen mit einer Verschachtelten =Wechseln Funktion zu Buchstaben umzuwandeln, diesen dann per =Zählewenn Funktion hier aus Teil 2 einen Rang zuzuordnen und diese dann in einer weiteren Hilfsspalte per =Kkleinste zu ordnen.
Deine Formel macht das aber wohl eleganter. Einziges Manko, die Leerzeilen sind jetzt oben und nicht am Ende. Sprich sind weniger Werte zu Ordnen als Zeilen vorhanden, sind die ersten Zeilen leer und in den letzten dann die Werte. Kann man das ändern?
Dann habe ich noch eine generelle Frage:
Ich habe mir aus dem Teil „Liste nur mit eindeutigen Werten erstellen“ folgende Formel abgeschaut: =WENNFEHLER(INDEX($B$5:$B$24;VERGLEICH(1;ZÄHLENWENN($F$4:F4;$B$5:$B$24)+($B$5:$B$24„“)*1;0));““)
Nun umfasst der Bereich bei mir 2000 Zeilen und die Arbeitsmappe ist auch schon ziemlich groß. Excel ist dann nur am „berechnen“. Ich könnte die Abfrage auch mit Power Query machen, aber ist das Ratsam? Excel rechnet dann zwar nicht mehr ewig, aber hat das andere Nachteile?
Gruß
Martin
Hallo Martin,
ich tendiere mittlerweile dazu, im Zweifelsfall Power Query einzusetzen. Vor allem, wenn die Datenmengen größer sind und mit komplexeren Formeln gearbeitet werden muss. Echte Nachteile sehe ich keine. Man muss halt nach Änderungen die Abfrage aktualisieren, während Formeln automatisch die neuen Ergebnisse anzeigen. Aber wenn die Performance damit ohnehin in die Knie geht, solltest du auf jeden Fall Power Query in Betracht ziehen.
Schöne Grüße,
Martin
Alles klar. Vielen Dank für deine Antwort!
Hallo Martin,
vielen Dank für deinen Input!
Bis jetzt hat mit dein Blog sehr geholfen, ich stehe aber vor einem weiteren Problem:
Für ein Turnier möchte ich eine Exceltabelle nach deinem Vorgehen erstellen. Soweit klappt das auch, ich würde aber gerne eine weitere Möglichkeit zum Filtern einbauen. Sprich wenn zwei Teams die gleiche Gesamtpunktzahl haben (es werden die drei besten Versuche gezählt), dann gewinnt das Team mit dem höchsten Durchschnitt (nach zehn Runden).
Hast du eine Idee wie das umzusetzen wäre?
Excelige Grüße
Julian
Hallo Julian,
in meiner Beispieldatei habe ich ja für gleiche Werte eine Unterscheidung über die ZUFALLSZAHL-Funktion erzeugt. Vielleicht könntest Du stattdessen mit MITTELWERTWENN arbeiten, für jedes Team den Durchschnitt bilden und damit zum gewünschten Ergebnis kommen?
Schöne Grüße,
Martin
Hallo Martin,
die Sortierung mit der Hilfszahl ist klasse. Nun möchte ich aber, dass alle Kategorien mit der selben Zahl zusätzlich noch nach ABC sortiert werden. Also eine zweite Formel anwenden. Kannst Du mir helfen?
viele Grüße
Josephine
Hallo Josephine,
falls du Microsoft 365 im Einsatz hast, kannst du die Funktion SORTIERENNACH nutzen. Das geht ganz einfach und ohne irgendwelche Hilfsspalten. Ansonsten muss ich mit einer reinen Formellösung passen.
Schöne Grüße,
Martin
Hallo Martin,
ich habe nun alle 3 Teile des Sortierens gelesen. Zu meinem Bedauern helfen sie mir nicht weiter, obwohl es sicher auch für mein Thema eine Lösung geben sollte.?
Ich habe eine Liste mit 6 Spalten und 25 Zeilen. In dieser stehen Namen, jedoch nicht in allen Zellen. Wie bekomme ich die vorhandenen Namen in eine Spalte alphabetisch sortiert. Ich habe Excel 2019.
Vielen Dank…
Olaf
Hallo Olaf,
wenn das Sortierfeld leere Einträge enthält, werden diese bei der Sortierung als solche berücksichtigt und stehen ganz am Ende oder ganz am Anfang, je nach Sortierrichtung. Was genau funktioniert denn da bei dir nicht? Oder was genau hättest du dir erwartet?
Schöne Grüße,
Martin
Hallo Martin,
habe das Problem mithilfe einer komplizierten Formel aus dem IT lösen können.
Habe nun das neue Problem, dass ich bei gleichem Rang mit Sverweis nicht alle dazugehörigen Namen mir anzeigen lassen kann. Bei 2 oder mehr gleichen Plätzen, gibt es nur den ersten Namen zurück. Habe auch hier im IT nach einer Lösung gesucht, aber bislang ohne tragbare Lösung. Hast Du schon etwas dazu veröffentlicht?
Schöne Abendgrüße aus Berlin…
Olaf
Hallo Olaf,
ja, das ist richtig, der SVERWEIS kann immer nur den ersten Eintrag zurückliefern. Das ist auch genau der Grund, warum ich für die INDEX-Funktion mit einer Hilfsspalte gearbeitet habe, um eben eine Eindeutigkeit zu erreichen.
Für den Fall, dass du Microsoft 365 im Einsatz hast, könntest du dir diese ganzen Zwischenschritte und Formeln sparen und es stattdessen mit der dort verfügbaren SORTIEREN-Funktion lösen. Einen besseren Tipp für eine Formellösung kann ich dir ansonsten leider auch nicht geben.
Schöne Grüße,
Martin