[Lambda] UNIQUE.PARTIAL

[LCohen]

UNIQUE.PARTIAL(Array;Keys;Debugging) 
(partielles EINDEUTIG)

mit

Array: Um partielle Duplikate zu bereinigender Bereich
Keys: 1 Zeile mit 1 bis zu 9 Spalten, die die als Duplikate-Beschränkung zu verwendenden Spaltennummern von Array enthält
Debugging: Hier in der Funktion (völlig willkürlich von mir gewählt) die letzten Zustände in der UDF

Motivation zu der Funktion:

4 Mängel von EINDEUTIG()

1. Man möchte die Eindeutigkeit nicht auf das ganze Array beziehen müssen; einzelne Spalten machen es vielmehr erst interessant. 
   
2. Genau wie bei SVERWEIS gehen die Datensatznummern verloren. Gibt es solche bei VERGLEICH statt SVERWEIS, so ist das auch bei UNIQUE.PARTIAL statt EINDEUTIG so (wenn man möchte; dafür richtiges Debugging-Argument wählen). Manchmal möchte man aber genau das wissen.
   
3. EINDEUTIG ist gnadenlos. Man sieht bei sehr heterogenen Daten gar nicht, welcher Datensatz doppelt war. Das gestaltet UNIQUE.PARTIAL weicher. Es zeigt die jeweils ersten Einträge der restlichen Spalten, die aus der Duplikateauswertung rausfallen. Kehrt man das Array vorher - z.B. mit maninweb's ARRAY.REVERSE - um, bekommt man entsprechend einheitlich die letzten Einträge der Doppler. Erneutes Umkehren dann nicht vergessen!
   
4. Wenn schon dynamisch: Warum gibt es eigentlich keine Häufigkeiten der Duplikate gratis dazu? Das interessiert doch lebhaft! Es können ja auch mal 3 oder 4 partiell gleiche Sätze gewesen sein. 
   

Ausstattung von UNIQUE.PARTIAL

Ich kann mir - wieder bis zu 9 - Felder aussuchen, aufgrund deren Doppler die Daten beurteilt werden. Nach deren Wegfilterung sehe ich als neue Spalte die Häufigkeit der Ursprungssätze in den gefilterten Sätzen. Das kann ich auch weglassen - braucht man ja nicht immer. Die Ausgabe der Spalten geschieht in der Reihenfolge der Keys, im folgenden also {1.4.2}, wenn Debugging 0 (oder leer) oder 1; bei Debugging 2 werden alle Spalten in Ausgangsreihenfolge gezeigt, nützlicherweise mit Häufigkeit.

Code:

Getränk Farbe Geschmack    Herkunft            A1:D10: Array
Wein    rot   süß          Frankreich
Wein    rot   süß          Frankreich
Wein    rot   trocken      Deutschland
Wein    rot   trocken      Deutschland
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  halbtrocken  Deutschland
Wein    weiß  trocken      Frankreich
Wein    weiß  trocken      Deutschland
Bier    hell  hopfig       Belgien

Getränk Herkunft    Farbe  1                   A12: =UNIQUE.PARTIAL(A1:D10;{1.4.2};0) 0: mit Angabe Häufigkeit
Wein    Frankreich  rot    2                                                          1: ohne
Wein    Deutschland rot    2
Wein    Deutschland weiß   3
Wein    Frankreich  weiß   1
Bier    Belgien     hell   1

Getränk Farbe Geschmack    Herkunft     1      A12: =UNIQUE.PARTIAL(A1:D10;{1.4};02)  2: mit Füllspalten und Häufigkeit
Wein    rot   süß          Frankreich   3
Wein    rot   trocken      Deutschland  5
Bier    hell  hopfig       Belgien      1

Gleichzeitig entspricht die Funktion einer Pivottabelle mit ZEILEN und WERTE (=Anzahl). Die Feldnamen werden bei UNIQUE.PARTIAL als Datensatz mitgezählt, damit man die Anzahl Zeilen des Arrays mit den Häufigkeiten abstimmen kann. Will man das nicht, lässt man die Header im Argument Array mit A2:D10 einfach weg.

Lambda-Funktion (benötigt SORTBY.KEYRANGE, welches nochmals korrigiert wurde; siehe unten als zweite Funktion)

UNIQUE.PARTIAL(Array;Keys;Debugging)

=LAMBDA(Array;Keys;Debugging;LET(
a;Array;
b;Keys;
z;Debugging;
q;SPALTEN(a);
s;SPALTEN(b);
c;WENN(SEQUENZ(;q+1)>q;ZEILE(a);a);
d;SORTBY.KEYRANGE(c;b;1);
x;SEQUENZ(ZEILEN(d));
e;WENN(SEQUENZ(;q+2)>q+1;1-(x>1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;1))=INDEX(d;x;INDEX(b;1)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;2))=INDEX(d;x;INDEX(b;2)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;3))=INDEX(d;x;INDEX(b;3)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;4))=INDEX(d;x;INDEX(b;4)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;5))=INDEX(d;x;INDEX(b;5)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;6))=INDEX(d;x;INDEX(b;6)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;7))=INDEX(d;x;INDEX(b;7)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;8))=INDEX(d;x;INDEX(b;8)));1)*
WENNFEHLER((INDEX(d;x-1;INDEX(b;9))=INDEX(d;x;INDEX(b;9)));1);d);
v;INDEX(e;ZEILEN(d)+1-x;SEQUENZ(;q+2));
u;WENN(SEQUENZ(;q+3)>q+2;x;v);
f;FILTER(u;INDEX(u;x;q+2)=1);
y;SEQUENZ(ZEILEN(f));
t;WENN(SEQUENZ(;q+4)>q+3;INDEX(f;y;q+3)-(y>1)*INDEX(f;y-1;q+3);f);
g;SORTBY.KEYRANGE(t;q+1;0);
h;INDEX(g;y;WENN(SEQUENZ(;q+1)=q+1;SPALTEN(g);SEQUENZ(;q)));
i;INDEX(h;y;WENN(SEQUENZ(;s+1)=s+1;SPALTEN(h);b));
j;INDEX(i;y;SEQUENZ(;s));
WAHL(z+1;i;j;h;g;t;f;u)))

[v] entspricht übrigens in der Funktion dem ARRAY.REVERSE von maninweb. Ich benötige nur einmal REVERSE, weil ich mit [c] die Originalsortierung als Spalte geschrieben habe. Ich dokumentiere das vielleicht nochmal; die Buchstaben-Variablen werden dann vielleicht auch etwas länger.

Die UDF UNIQUE.PARTIAL ist ziemlich mächtig und ermöglicht enorm viele statistische Aussagen (beliebige Anzahl (bis 9) Spalten in Relation auswertend); ich habe bestimmt 12 Stunden dran gesessen oder abseits vom PC gegrübelt (ist ja noch eine neue Welt). Am schwierigsten war das Bilden der Häufigkeiten ohne ZÄHLENWENN-o.ä.-Frevel; ich wollte nicht in die Falle exponentieller Rechenzeiten geraten und bemühe statt dessen während des Durchlaufs 4 Hilfsspalten.
______________________________

SORTBY.KEYRANGE - 2. Verbesserung (Änderungen zur Ursprungsversion hier fett dargestellt)

=LAMBDA(Array;Keys;Header;LET(
a;Array;
p;Keys;
t;Header;
r;(1-(SEQUENZ(2)=2)*(ZEILEN(p)=1))*p;
offs;WENNFEHLER(r+{0.0.0.0.0.0.0.0.0;0.0.0.0.0.0.0.0.0};0);
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))

Die Änderung bei SORTBY.KEYRANGE erlaubt nun, als Key z.B. nur 4 statt {4;0} einzugeben. Sind alle Keys aufsteigend, kann die Sortierrichtung auch insgesamt im Argument weggelassen werden, also Spalten 5 und 2 z.B. als {5.2}. Muss 2 aber absteigend sortiert sein, gilt die Vollschreibweise {5.2;0.1}.

---

FAZIT:

1. Um BEREICH.VERSCHIEBEN kommt man bei gewünschtem Deformieren eines Ausgangsarrays über eine seiner Grenzen hinaus mit einer LAMBDA-Funktion nicht herum.
   
2. BEREICH.VERSCHIEBEN verarbeitet aber nur Zellbezüge, so dass das übergebene Array keine Konstante sein darf.
   
3. Gibt es die Möglichkeit einer fixen Obergrenze, kann man aber eine solche Konstante zu einem Obergrenzen-Nullarray addieren. Möglicherweise geht das auch statt Addition per Zuweisung - für andere Datentypen als Zahl. Noch nicht ausprobiert.
   

Achtung: Möglicherweise stimmt der ganze Artikel oder Teile davon nicht - da es vielleicht eine andere Lösung gibt.

Es stimmt tatsächlich vermutlich nicht. Man kann im virtuellen Raum der UDF ganz einfach weitere Zeilen und Spalten anfügen, andere löschen - und dies für notwendige Rückgaben von Arrays mit ganz anderer Größe. Man lernt halt dazu.

[LCohen]

1. Korrektur zu UNIQUE.PARTIAL

d;SORTBY.KEYRANGE(c;b;0);

statt

d;SORTBY.KEYRANGE(c;b;1);

behebt einen Auswertungsfehler.
___________________

Zwischendurch die Frage:

1. Soll ich hier (im Thread) noch weitere Lösungen posten? Wird vielleicht etwas lang ... Oder ein Moderator schiebt ein Inhaltsverzeichnis in das OP, mit LAMBDA-Fkt.Name und einem #-Link.
   
2. Wie sollte man mit dem spannenden Thema weiter aus Eurer Sicht vorgehen?
   
3. Ich könnte auch eine Datei mit den bisherigen LAMBDAs posten. Vielleicht packe ich einfach maninweb's Lösungen dazu (wie von ihm sicherlich auch von mir ohne jede Funktions-Gewähr, aber zumindest sind LAMBDAs nicht virenverdächtig); dann gibt es schon eine ganz nette kleine Bibliothek. Irgendwelche Einwände, maninweb?
   
4. Ich könnte auch meine ersten Design-Erkenntnisse posten - die müsste ich aber erst formulieren.
   

Ja, ich weiß auch, dass meine 1-Letter-Kurzvariablen grausam sind ;) Vielleicht schaffe ich es zu einem 4-Letter-Kompromiss

[LCohen]

2. Korrektur zu UNIQUE.PARTIAL

x;SEQUENZ(ZEILEN(a));
c;WENN(SEQUENZ(;q+1)>q;x;a);
d;SORTBY.KEYRANGE(c;b;0);

ersetzt

c;WENN(SEQUENZ(;q+1)>q;ZEILE(a);a);
d;SORTBY.KEYRANGE(c;b;0);
x;SEQUENZ(ZEILEN(d));

Der Gebrauch von ZEILE(a) anstelle von SEQUENZ(ZEILEN(a)) fliegt einem bei Konstantenarrays anstelle von Zellbereichen als Argument um die Ohren, weil ZEILE nur bei letzterem eine Zeileninformation mitbekommt! Eine Konstante hat im Blatt ja schließlich keine Adresse. ZEILEN wiederum benötigt keine Adresse, sondern nur eine Größe.

[LCohen]

Etwas kürzer: CONDENSE