[Lambda] SORTBY.KEYRANGE

[LCohen]

SORTBY.KEYRANGE(Array;Keys;Header) mit

Array: Zu sortierender Bereich
Keys: Erste Zelle eines 2x9-Bereichs für bis zu 9 Sortierschlüssel (oben: Feldnr.; unten: Sortierrichtung 0 auf, 1 ab)
Header: 1 bedeutet "enthält Feldnamen", 0 oder leer bedeutet "enthält keine Feldnamen".

Motivation zu der Funktion:

4 Mängel von SORTIERENNACH()

1. Wie bei den anderen dynamischen Funktionen auch kann man keine Feldnamen mit einschließen, die unsortiert bestehen bleiben. Feldnamen sind jedoch Voraussetzung für fast alle älteren Excelfunktionen wie Autofilter, Spezialfilter und Pivot. Da wundere ich mich schon die ganze Zeit drüber! Ich muss die doch mit dem Bezug =A15# komplett versorgen können. Außerdem habe ich gern eine Spaltenbezeichnung über den Daten.
   
2. Wie bei anderen Funktionen auch, z.B. WAHL oder allen ...WENNS-Funktionen, muss man die variabel vielen Argumente der Funktion immer einzeln versorgen.
   
3. SORTIERENNACH erlaubt kein Leerlassen der Sortierrichtung; es muss bei jedem Begriff 1 oder -1 eingetragen werden. Ich finde 0 (oder leer) für aufsteigend und 1 für absteigend besser, weil ich schneller Entf drücke, als 1 ENTER. Außerdem sieht man beim Aufsteigend-Leerlassen den Unterschied zur Absteigend-1 auch optisch viel besser.
   
4. Und schließlich sind für mich Spaltennummern ggü Spaltenbezügen viel flexibler. Sie können auch kalkuliert werden (im Gegensatz zur Teilwiederholung von Adressen, die - nun vollends unverständlich! - noch nicht mal als B1 ausreichen, sondern als B1:B10 eingegeben werden müssen). Da wurde nicht zuende gedacht.
   

Dummerweise funktionieren weder ADRESSE, INDIREKT noch AUSWERTEN, um die Sortierparameter als 1 variabel langes Argument in SORTIERENNACH zur Verfügung zu stellen. Und VBA soll ja bei Verwendung von LAMBDA außen vor bleiben können. 

Ausstattung von SORTBY.KEYRANGE

Daher steuere ich die 18 Sortierparameter einzeln zugewiesen in die LET-(bzw. SORTIERENNACH)-Funktion; das bedeutet 1 bis 9 Sortierungen auf einen Schlag, die wohl noch niemand benötigt hat (falls doch: Funktion einfach schachteln). Das Feld "Keys" verwaltet dies in 2 Zeilen x 9 Spalten, die leer bleiben müssen, wenn sie nicht alle benötigt werden.

Code:

Getränk Farbe Geschmack    Herkunft            A1:D10: Array
Wein    rot   süß          Frankreich
Wein    rot   süß          Deutschland
Wein    rot   trocken      Frankreich
Wein    rot   trocken      Deutschland
Wein    weiß  halbtrocken  Frankreich
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  trocken      Frankreich
Wein    weiß  trocken      Deutschland
Bier    hell  hopfig       Belgien
                                               A12: Keys (nur erste Zelle A12 als Argument angeben!)
1       2     4            3                        (dass hier 4 genannt werden, ist nur Zufall)
        1                  1                        Bei A12 muss A12:I13 reserviert sein.
            
Getränk Farbe Geschmack    Herkunft            A15: =SORTBY.KEYRANGE(A1:D10;A12;1)
Bier    hell  hopfig       Belgien
Wein    weiß  trocken      Deutschland
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  trocken      Frankreich
Wein    weiß  halbtrocken  Frankreich
Wein    rot   trocken      Deutschland
Wein    rot   süß          Deutschland
Wein    rot   trocken      Frankreich
Wein    rot   süß          Frankreich

Bereich "Keys"

Oberste Sortierung: Getränk [1] wird aufsteigend [leer] sortiert
dahinter: Farbe [2] wird absteigend [1] sortiert
dahinter: Herkunft [4] wird aufsteigend [1] sortiert
dahinter: Geschmack [3] wird absteigend [leer] sortiert (wie man sieht, greift dieser Untersort im Beispiel sogar noch)

Lambda-Funktion

=LAMBDA(Array;Keys;Header;LET(
a;Array;
p;Keys;
t;Header;
offs;BEREICH.VERSCHIEBEN(p;;;2;9);
h;WENN(SEQUENZ(2)=2;1-offs*2;offs);
L;SEQUENZ(ZEILEN(a));
m;SEQUENZ(ZEILEN(a)-t;;1+t);
n;SEQUENZ(;SPALTEN(a));
o;SEQUENZ(;SPALTEN(h));
j;INDEX(h;1;o);
k;INDEX(h;2;o);
s;SORTIERENNACH(INDEX(a;m;n); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;1));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;1));INDEX(k;1);INDEX(k;1)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;2));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;2));INDEX(k;1);INDEX(k;2)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;3));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;3));INDEX(k;1);INDEX(k;3)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;4));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;4));INDEX(k;1);INDEX(k;4)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;5));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;5));INDEX(k;1);INDEX(k;5)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;6));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;6));INDEX(k;1);INDEX(k;6)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;7));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;7));INDEX(k;1);INDEX(k;7)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;8));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;8));INDEX(k;1);INDEX(k;8)); 
INDEX(a;m;WENNFEHLER(1/(1/INDEX(j;9));INDEX(j;1)));WENN(ISTFEHLER(INDEX(j;9));INDEX(k;1);INDEX(k;9))); 
u;WENN(t;WENN(L=1;INDEX(a;1;n);INDEX(s;L-1;n));s);
u))

Das u am Schluss definiere ich zunächst, um es dann einzeln als Funktions-Rückgabe nochmals zu schreiben. Vorteil: Man kann alle Zwischenschritte der Funktion zum Testen durch Ersetzen des letzten u anzeigen.

Bei den Headern habe ich "etwas herumgehampelt" ... es geht am Schluss wieder einmal um das Zusammensetzen von 2 Bereichen. Und das geht evtl. auch schlanker.

[LCohen]

Ich habe doch geschrieben, dass ich es versucht habe (bestimmt 3 Stunden lang). INDIREKT und die anderen wollten aber nicht. Funktionszusammensetzung aus Stringkomponenten ginge über VBA oder Excel4M. Aber dann benötige ich ja kein LAMBDA mehr.

Natürlich kann man hier Teile wieder ausgliedern in eigene LAMBDA's. Dann hättest Du Deine kurzen Funktionen. Aber dann muss ich auch immer mehrere Funktionen installieren! Mit erneuten Übergaben von Argumenten usw.

SORTBY.KEYRANGE ist eine monolithische, abgeschlossene Aufgabe. Beweise mir etwas anderes. Sinnvolle Zwischenoutputs gibt es in diesem Fall einfach nicht. Oder vielleicht doch: Die Beschreibung des Arrays folgt immer wieder gleichen Bedarfen:

Array.Range
Array.Zeilen(zahl)
Array.Spalten(zahl)
Array.Zeilen(laufvariable)
Array.Spalten(laufvariable)

Das ist für mich aber noch nicht genug, um es auszulagern. Bringt auch nichts, denn gebe ich damit einen 5-Wertebereich zurück, muss ich in der aufrufenden Funktion doch wieder mit INDEX herummachen.

Wenn Du Dich jetzt über die 9 gleich langen Zeilen am Schluss aufregst: Die lassen sich genauso gut lesen, wie nur eine davon. Ich breche meine Funktionen ja auch immer zur Vergleichbarkeit um. Nicht aufgrund von Schachtelungsebenen. Die sind hier völlig fehl am Platz, da LET im Grunde immer nur kapselt. Und Kapselungen liest man wie im Buch ohne Einrückungen.

Erweiterbar ist die Funktion genauso, wie eine VBA-Funktion. Brauche ich z.B. 20 Sortierebenen, knalle ich die einfach dahinter.

Ich schreibe jede einzelne LET-Definition in eine Zeile. Und wenn jetzt jemand kommt, warum ich wie auf dem weiland platzbeschränkten Taschenrechner 1-Buchstaben-Variablennamen verwende, dann sage ich ihm: Ich kann es leichter lesen, als wenn die Anweisungen zu lang werden. Denn die Definition steht ja in Sichtweite.

Formelauswertung habe ich noch nie benötigt, vermisse ich also auch nicht. Ich habe doch geschrieben, dass ich das letzte U durch die meisten gebildeten Variablen ersetzen kann - und schon bekomme ich eine Zwischen- statt Endberechnung. Dann mache ich Dir ein x für ein u vor. 

Jetzt komme Du mir und sage, dass Du die Formeln im LET nicht verstehst, so schön kurz eine nach der anderen. Das nehme ich Dir nicht ab.

Und wenn die Funktion fehlerfrei ist, muss ich sie mir doch auch nicht mehr anschauen.

Wenn solche Kommentare kommen, obwohl ich auf die Schwierigkeiten im Vorfeld hingewiesen habe, werde ich einfach nicht mehr auf Schwierigkeiten und Nachteile hinweisen, weil trotzdem genau deswegen auf mir herumgetrampelt wird. Auch auf die Gefahr hin, dass Nicht-Vorab-Rechtfertigung einen dann dümmer erscheinen lässt, da zuerst die Fremd- als die Eigenkritik da war. Leider sind die meisten Menschen so.

[LCohen]

Verbesserung in =SORTBY.KEYRANGE(A1:D10;{1.2.4.3;0.1.0.1};1)

Die Zeile offs in der Funktion wird ersetzt durch offs;WENNFEHLER(p+{0.0.0.0.0.0.0.0.0;0.0.0.0.0.0.0.0.0};0);

Nun kann statt des Zellbezugs auch eine Arraykonstante verwendet werden. Für den häufigsten Fall, dass nur nach der ersten Spalte sortiert wird, ist die Anforderung 
=SORTBY.KEYRANGE(A1:D10;{1;0};1), also mindestens 1 Spalte.

Wird nach Spalte 2 absteigend und dahinter nach Spalte 1 aufsteigend sortiert, dann
=SORTBY.KEYRANGE(A1:D10;{2.1;1.0};1)

Anmerkung: Statt das Nullarray zu dem Argument zu addieren, gibt es vermutlich noch sinnvollere Lösungen.

[maninweb]

Hallo zusammen,

SORTBY.KEYRANGE finde ich eine gute Idee und gefällt mir :-)

shift-del: Ein Vorteil der LAMBDA-Funktionen ist aber schon, dass sich damit abgeschlossene Funktionen entwickeln lassen, die auch ineinander
verschachtelt werden können. Tauscht man später den Code der LAMBDA-Funktion aus, z.B. um einen Fall zu berücksichtigen oder die Funktion
zu optimieren, bleibt diese funktional. Das ist z.B. mein Ansatz, ein paar Basisfunktionen zu entwickeln. Von daher finde ich es jetzt nicht schlimm,
dass der Code einer LAMBDA-Funktion länger ist. Eine LAMBDA-Funktion sehe ich mehr als Vorteil, so denn diese dokumentiert ist. Da ist MS aber
noch in den Anfängen. Sie arbeiten dran.

Gruß

[LCohen]

2. Verbesserung SORTBY.KEYRANGE ermöglicht kürzere Schreibweise des KEY-Arguments