Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1620to1624
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Problem mit Sortierung per Formel

Problem mit Sortierung per Formel
05.05.2018 11:58:49
sheady
Hallo zusammen,
ich habe in einer Excel Tabelle in A3:A9 unsortierte Zahlen stehen und in B3:B9 ist jeder Zahl ein Buchstabe zugeordnet (könnten genauso gut Namen sein etc.). Nun möchte ich die Zahlen per Formel der Größe nach ordnen und die Buchstaben entsprechend auch.
Ich habe mir bislang so geholfen, dass ich zunächst in D3 folgende Formel geschrieben und sie bis D9 runtergezogen habe:
=KGRÖSSTE(A$3:A$9;ZEILE(A3)-ZEILE(A$2))
Um dann auch die Buchstaben wieder zuzuordnen habe ich in E3 folgende Formel geschrieben und sie bis E9 runter gezogen:
=SVERWEIS(D3;A$3:$B$9;2;FALSCH)
So weit würde das auch klappen, wenn einige Zahlen nicht mehrfach vorkämen, die dann alle den selben Buchstaben erhalten und nicht ihren eigenen. Gibt es da eine adäquate Lösung für oder muss man da auf VBA zurückgreifen? Da ich das ganze dynamisch gestalten möchte, steht die einfache Sortierfunktion nicht zur Option.
Das Beispiel findet ihr hier: https://www.herber.de/bbs/user/121454.xlsx
Vielen lieben Dank für Eure Zeit!
Gruß

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
So:
05.05.2018 12:15:04
lupo1
D3: =INDEX(B:B;(1-REST(WENNFEHLER(KGRÖSSTE(INDEX(A$3:A$9-ZEILE(A$3:A$9)%%;);ZEILE(A3)-ZEILE(A$2));"");1))/1%%)
Spalten danach: Tonne.
WENNFEHLER rausnehmen ...
05.05.2018 12:16:53
lupo1
D3: =INDEX(B:B;(1-REST(KGRÖSSTE(INDEX(A$3:A$9-ZEILE(A$3:A$9)%%;);ZEILE(A3)-ZEILE(A$2));1))/1%%)
... und dann meinetwegen außen drum legen.
AW: WENNFEHLER rausnehmen ...
05.05.2018 12:28:41
sheady
Wow, danke!
Um das nachvollziehen zu können. Was genau machen die %%?
%%
05.05.2018 12:37:27
lupo1
Die subtrahieren 1/10000 der zur gerankten Zahl aufgefundenen Zeilennummer.
Das wird dann klargespült mit REST, so dass die gerankte Zahl rausfliegt.
Danach wird der Bruchteil wieder um 10000 erhöht und ergibt nun die Lage, mit der man auf B:B zusteuert.
Das -Zeile()%% (statt +) ist nötig, um E vor F auszuweisen, nicht umgekehrt.
Anzeige
AW: %%
05.05.2018 12:58:06
sheady
Ein letzte Frage: Wie müsste ich die Formel denn verändern, wenn ich nicht mit ganzen Zahlen wie 10 oder 14 hantiere, sondern wenn ich auch eine oder mehrere dezimalstellen habe? Aktuell erhalte ich dann nur den Wert 0. So ganz scheine ich das noch nicht zu blicken :(
Anpassung
05.05.2018 13:03:22
lupo1
Dann musst Du diese Zahlen in der Formel nachkomma-runden und die Anzahl % vergrößern. Auch REST läuft dann nicht mit 1.
Herber zickt bei mir grad etwas rum ...
Hier ein Beispiel mit 4 Nachkommastellen
05.05.2018 13:09:17
lupo1
=INDEX(B:B;(1-REST(KGRÖSSTE(INDEX(A$3:A$9-ZEILE(A$3:A$9)%%;);ZEILE(A3)-ZEILE(A$2));1))/1%%)
wird dann zu
=INDEX(B:B;(1-REST(KGRÖSSTE(INDEX(RUNDEN(A$3:A$9;4)-ZEILE(A$3:A$9)%%%%;);ZEILE(A3)-ZEILE(A$2));1%%) /1%%)/1%%)
Anzeige
Besser jedoch ist
05.05.2018 13:12:35
lupo1
=INDEX(B:B;(1-REST(KGRÖSSTE(INDEX(A$3:A$9/1%%-ZEILE(A$3:A$9)%%;);ZEILE(A3)-ZEILE(A$2));1)) /1%%)
Du skalierst die Ausgangswerte um 4 Nullen nach oben - so kann der ursprüngliche Rest der Formel unverändert bleiben.
Danke!
05.05.2018 13:19:05
sheady
Nochmals, wow. Da brauch ich einen Moment, um mir das mal komplett reproduzieren zu können.
Vielen Dank lupo! Gut, dass es solche Leute wie Dich gibt ;)
Es darf dann aber tats nur 4 Nach,St geben (owT)
05.05.2018 13:20:58
lupo1
AW: %%
05.05.2018 12:58:16
sheady
Ein letzte Frage: Wie müsste ich die Formel denn verändern, wenn ich nicht mit ganzen Zahlen wie 10 oder 14 hantiere, sondern wenn ich auch eine oder mehrere dezimalstellen habe? Aktuell erhalte ich dann nur den Wert 0. So ganz scheine ich das noch nicht zu blicken :(
Anzeige
zum Grundverständnis
05.05.2018 13:44:56
Daniel
Hi
mal zum Grundverständnis:
damit dein Sortieren mit Formeln funktioniert, brauchst du eindeutige Werte, damit die Werte aus der zweiten Spalte auch korrekt zugeordnet werden können.
Wenn es jetzt keine Eindeutigen Werte gibt, dann erzeugt man diese künstlich, in dem man noch einen individuellen sehr kleinen Wert zur Zahl hinzuaddiert.
Dieser hinzuaddierte Wert sollte so klein sein, dass er
a) keinen Einfluss auf die Reihenfolge der Werte hat
b) sich hinterher für das Endergebnis wieder problemlos entfernen lässt, z.B. durch Abrunden
dh der größte hinzuaddierte Wert muss kleiner sein, als die kleinste Dezimalstelle deiner Zahlen.
Bei Ganzahlen: kleiner 1
Bei einer dezimalstelle: kleiner 0,1
bei zwei Dezimalstellen: kleiner 0,01
usw
um den Wert individuell zu machen, verwendet man am einfachsten einen Bruchteil der Zeilennummer:
+Zeile()/100 oder +Zeile/1000.
Welchen Divisor du da nimmst, hängt ab von der Anzahl der Dezimalstellen deiner Ausgangswerte und von der Anzahl der Zeilen in deiner Ausgangstabelle, damit die obige Bedingung auch für die größte Zeilenummer erfüllt ist.
Gruß Daniel
Anzeige
Was macht %?
05.05.2018 12:55:10
Daniel
Hi
Das "%" ist in der Formel eine Kurzschreibweise für "/100".
1% ist 0,01
1%% ist 0,0001
Gruß Daniel
AW: Problem mit Sortierung per Formel
05.05.2018 12:20:53
Sepp
Hallo Lukas,
so?
Tabelle1

 ABCDEF
1      
2   Sortiert 
310A 14E 
411B 14F 
512C 12C 
610D 11B 
714E 10A 
814F 10D 
910G 10G 
10      

Formeln der Tabelle
ZelleFormel
D3=WENNFEHLER(AGGREGAT(14;6;$A$3:$A$9-ZEILE($A$3:$A$9)*10^-6;ZEILE(A1)); "")
E3{=WENN(D3="";"";INDEX($B$3:$B$9;VERGLEICH(D3;$A$3:$A$9-ZEILE($A$3:A9)*10^-6;0)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: darfs auch ne Lösung mit Hilfsspalte sein?
05.05.2018 14:05:12
Daniel
die würde dann auch mit Kommazahlen mit beliebiger Anzahl an Dezimalstellen funktionieren.
https://www.herber.de/bbs/user/121457.xlsx
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige