Textverketten, doppelt dynamisch

09.01.2025 11:31:54

daniel

Hallo
folgendes Problem:
ich habe eine Ausgangsliste mit Namen, Bedingung und Text.
diese Liste wird per Formel nach einer der Bedingungen gefiltert.
eine dritte Liste soll dann aus der gefilterten Liste die übrig gebliebenen Namen als eindeutige Liste wiedergeben und in der zweiten Spalte die dazugehörigen Texte in einer Zelle zusammen fassen.
das ganze soll sich natürlich automatisch an geänderte Inhalte anpassen.

funktioniert auch soweit, lediglich die Textverketten-Funktion will sich nicht an die dynamische Liste der eindeutigen Namen anpassen.
mit verweis auf die Einzelzelle geht es, aber dann müsste ich immer die Formel bei bedarf weiter nach unten ziehen oder überzählige löschen
wie müsste hier die Formel geschrieben werden, damit der Autospill funktioniert?
weiteres siehe Beispiel.

https://www.herber.de/bbs/user/174769.xlsx

Gruß Daniel

ps: mit PQ kenne ich mich nicht wirklich aus und würde daher eine Formellösung bevorzugen.

AW: Textverketten, doppelt dynamisch

09.01.2025 12:04:25

Peter

=MAP(SEQUENZ(ANZAHL2(I4#);1;1);LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL($E$4#;3);SPALTENWAHL($E$4#;1)=INDEX(I4#;x)))))

AW: Textverketten, doppelt dynamisch

09.01.2025 12:20:52

peter

Ich habe ausversehen abgesendet obwohl ich noch nicht fertig war.

und eine Tollefrage!
Naja die Formel ist richtig (stimmt mit deiner wunschlösung überein, am rande gute beispieldatei)

Der Brecher
=MAP(SEQUENZ(ANZAHL2(I4#);1;1);LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL($E$4#;3);SPALTENWAHL($E$4#;1)=INDEX(I4#;x)))))

Auswas besteht der
1.Deinerlogik:=
TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL($E$4#;3);WAHL($E$4#;1)=INDEX(I4#;x))

Anpassung:
x anstelle von deinem # warum sage ich gleich.- siehe lambda

spielerei
Probier mal index(i4#;1) aus (alleinestehend) dann siehst du die erste Spalte kommt zurück index(i4#;{1;2}) aus dann siehhst du die erste und zweite reihe wird zurückgegeben - das war ja die idee von dir.

2.DemLambda
lambdaCode:=LAMBDA(x;Deinerlogik(x))
übersetzt, ich habe dein code in eine Funktion umgemünzt. DeineLogik hat nun den Paramter x - das ist also der ruf.

zwischenstand
führe mal LAMBDA(x;Deinerlogik(x))(1) aus - die zweite klammer ist der eingabeparameter

sprich
Lambda(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL($E$4#;3);WAHL($E$4#;1)=INDEX(I4#;x)))(1)
Du siehst ein singel rückgabe wert. mit ({1;2}) anstatt (1) kommt der nervige #nv fehler

3.DemLoop
=Map(4.Ary;LambdaCode()) wobei besser zum verstehen Map(4.Ary;deinCode(aryElement))
und das heißt map loopt durch das ary und jedes einzelne element wird dann als paramter in deine Funktion übergeben. Nach 2.demLambda weißt du für jedes einzelne funktioniert es
jetzt bleibt also nur das aufspannen des Paramter Ary

4.Ary
:=Sequenz(anazahl2(i4#;1;1))
wir wollen ja alle 5 elemente aus i4# haben, das heißt wie kriegen wir die? unsere vier elemente werden später über Index(i4#;x) aufgerufen, wenn du das getestet hast merkst du schnell wird brauchen 1;2;3;4;5 in excel notation {1;2;...;5}
Umgangsprachlich zahlen von 1 bis 5 in einser schritten. Genau das macht
sequenz(anzahlWerte;anzahlSpalten;schritte) - anzahl werte sind 1-5 also 5 anzahl spalten ist 1 da wir ein eindimensionales array haben wollen (array hat genau eine Spalte an werten). Sprich sequenz(5;1;2) liefert das gewollte {1;2;...;5} da du es dynamisch haben willst für die länge der sequenz über die anzahl an werten in i4# getrackt (anzahl2(i4#))

hoffe ich konnte dir helfen und die erklärung ergibt sinn.

Gruß peter

AW: vielen vielen Dank

09.01.2025 13:14:40

daniel

Wow
also erstmal vielen Dank an alle beteiligten, vor allem an Peter und seine ausführliche Erklärung.
ich tue mir zwar noch schwer, aber damit sollte ich mich mal hoffentlich mit Lambda anfreunden.
auch Maps muss man erstmal verstehen.

Gruß Daniel

AW: vielen vielen Dank

09.01.2025 13:22:43

peter

moi,
freut mich das zu hören!

wenn es bei Lambda happert und du klärung bedarf hast kannst du mir eine mail an ppo.questions@gmail.com schicken dann könnten wir die tage einen teams anrufen machen
gruß

AW: vielen vielen Dank

09.01.2025 13:40:47

daniel

danke für das angebot.
machst du das professionell?

AW: vielen vielen Dank

09.01.2025 15:53:32

Peter

nein xD
Nur am spaß an der Freude

AW: vielen vielen Dank

10.01.2025 12:17:55

peter

moin Daniel,
ich habe stand jetzt noch keine anfrage an meine Mail bekommen, ist das Richtig? Ich hätte sonst am Wochenende zeit um das mehr oder weniger kurz zu erklären.

(ich schreibe das bevor dieser Fragenthread im archiv verschwindet).

gruß

AW: vielen vielen Dank

10.01.2025 12:29:16

daniel

wie gesagt, danke für dein Angebot, aber ich versuche mich da erstmal alleine dran.

Verbesserung, lesbarketi und geschwindigkeit

09.01.2025 12:32:29

peterWarDuselig

Ich habe eine verbesserung gefunden (habe was doppelt gemoppelt gemacht)

=MAP(I4#;
LAMBDA(suchWert;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL($E$4#;3);SPALTENWAHL($E$4#;1)=suchWert))))

Meine Erklärung bleibt ansich identisch, nur ist es jetzt einfacher denn i4# ist die ansammlung an suchwerten also schon direkt {Name1;...;name5} und da map dieses {} durchläuft (for each item in {name...}) ist das index hintenweggefallen.

Ich denke mit der Version ist es wesentlich besser zu verstehen.

AW: Textverketten, doppelt dynamisch

09.01.2025 12:35:16

BoskoBiati2

Hi Peter,

interessehalber: Wie sähe eine Formel aus, bei der die Hilfstabelle E4# nicht vorhanden wäre, sprich, das Ergebnis direkt aus A4:C42 über G1 ermitteln?

Gruß

Edgar

AW: Textverketten, doppelt dynamisch

09.01.2025 12:57:56

Yal

Moin,

Vielleicht habe ich was übersehen, aber folgende -relativ einfache- Formel scheint auch zu funktionieren:

=LET(x;FILTER($A$4:$C$42;$A$4:$A$42=$G$1);TEXTVERKETTEN("-";;SPALTENWAHL(FILTER(x;SPALTENWAHL(x;1)=I4);3)))

Zugegeben: ich wäre nicht auf dieser Lösung gekommen, wenn mir die Funktion "Spaltenwahl" nicht vorgezeigt wurde.

Daraus diese vielleicht strukturierter Formel:

=LET(

    a;$A$4:$C$42;

    b;FILTER(a;SPALTENWAHL(a;2)=$G$1);

    c;FILTER(b;SPALTENWAHL(b;1)=I4);

    TEXTVERKETTEN("-";;SPALTENWAHL(c;3))

)

(natürlich ohne Zeilenvorschub einzutragen)

VG
Yal

AW: Textverketten, doppelt dynamisch

09.01.2025 14:35:32

BoskoBiati2

Hi Yal,

die erste Formel bringt #CALC, die zweite spillt nicht, was ja gewünscht war.

Gruß

Edgar

er tut nicht, er will nur spillen...

09.01.2025 15:23:34

Yal

...Spillen übersehen.

Persönlich wäre ich den PQ-Weg gegangen:

let

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

    #"Gruppierte Zeilen" = Table.Group(Quelle, {"Name", "Bedingung"}, {{"Kette", each Text.Combine([Text], "-"), type text}}),

    #"Sortierte Zeilen" = Table.Sort(#"Gruppierte Zeilen",{{"Name", Order.Ascending}, {"Bedingung", Order.Ascending}}),

    #"Pivotierte Spalte" = Table.Pivot(#"Sortierte Zeilen", List.Distinct(#"Sortierte Zeilen"[Bedingung]), "Bedingung", "Kette")

in

    #"Pivotierte Spalte"

VG
Yal

AW: er tut nicht, er will nur spillen...

09.01.2025 15:38:25

daniel

Hi
naja, PQ ist nicht so meine Welt.
harmoniert PQ auch mit einen dynamischen Filter, also bspw Filter mit Zeitstempel: von "JETZT aufgerundet auf die nächste Stunde" bis "morgen, 4:00"?

Gruß Daniel

AW: er tut nicht, er will nur spillen...

09.01.2025 16:25:35

Yal

Hallo Daniel,

Du kannst in PQ die aktuelle Uhrzeit aufrufen und als Filter-Wert verwenden. Ein Von-Bis ist auch machbar.

Wenn diese Eingabe sogar die einzige Merkmale für die Filterung sind, würde es Sinn ergeben, zwecks Stabilität, PQ zu verwenden.

PQ ist kein Monster, aber natürlich alles was neu ist, muss zuerst entdeckt werden. Anbei ein guter Tutorial in einzelnen Schritt inkl. Videos (insg. 1 Std): https://excelhero.de/power-query/power-query-ganz-einfach-erklaert

Das wesentlich Vorteil von Power Query sehe ich darin, dass es dessen Anwender dazu leitet, klar strukturierte Excel-Lösung aufzubauen. Das ist in beruflichen Umfeld sehr viel Wert (es ist durchaus monetär gemeint).

VG
Yal

AW: Textverketten, doppelt dynamisch ohne Hilfstabelle

09.01.2025 12:41:58

BoskoBiati2

Hi Peter,

hat sich erledigt:

=LET(xa;FILTER(A4:C42;B4:B42=G1);xb;EINDEUTIG(INDEX(xa;;1));xc;MAP(xb;LAMBDA(suchWert;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL(xa;3);SPALTENWAHL(xa;1)=suchWert))));HSTAPELN(xb;xc))

Gruß

Edgar

AW: Textverketten, doppelt dynamisch ohne Hilfstabelle

09.01.2025 13:31:59

Peter

Schöne lösung.
Geht sogar weit über das hinweg was ich gemacht habe 👍

Schöne woche noch

AW: Textverketten, doppelt dynamisch

09.01.2025 13:11:50

Peter

Moin bosko,
auch hier wieder interessante nachfrage. (finde interesse immer toll

ENDFORMEL
variante Profi
=LET(
suchBedingung; G1;
suchMatrix; FILTER(Tabelle1;Tabelle1[Bedingung]=suchBedingung);
MAP(I4#;LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL(suchMatrix;3);SPALTENWAHL(suchMatrix;1)=x)))))

varianteKlassisch
=LET(
suchBedingung; G1;
suchMatrix; FILTER(a4:c42;b4:b42=suchBedingung);
MAP(I4#;LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL(suchMatrix;3);SPALTENWAHL(suchMatrix;1)=x)))))

Vielleich kann ein Performance junkies eine effektiviere lösung finden. Ich würde aber folgendes machen. Die hilfsstelle einfach in die zelle schreiben

ERKLÄRUNG
Die kernlogik die zugrundeliegt (nach meiner Korrektur)
=MAP(I4#;LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL($E$4#;3);SPALTENWAHL($E$4#;1)=x)))))
bleibt unverändert, wir ersten nur die hilfstabelle e4 mit einer variablen

Jetzt wollen wir die hilfstabelle dort reinkriegen. Die tabelle ist ja im bereich e4# aufgeschlagen. Aber dort steht nur die Formel "=FILTER(A4:C42;B4:B42=G1)"

also erstmal reindamit.
=MAP(I4#;LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL(FILTER(A4:C42;B4:B42=G1);3);SPALTENWAHL(FILTER(A4:C42;B4:B42=G1);1)=x)))))

umschreiben
Uns fällt auf wir nutzen a4:c42 doppelt. Bzw den gesamten Filter. Das sollte arlamglocken schrillen, oder jubelglocken denn das deutet darauf hin, dass man mit Let sich das leben vereinfachen kann.
(Let speichert werte ohne hilfszellen)

=Let(
suchBedingung; g1;
suchMatrix; FILTER(A4:C42;B4:B42=suchBedingung);
MAP(I4#;LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL(suchMatrix;3);SPALTENWAHL(suchMatrix;1)=x)))))

Das ist schon fertig und mehr oder weniger gut lesbar
Denn die hilfstabelle ist nun in der Variabel SuchMatrix, die wird später verwendet.

PROFIMOVE
Wer jetzt noch mehr lust hat der drückt in der Suchmatrix strg + t, das erstellt eine mächtige tabelle, damit erlaubt das folgendes
=Let(
suchBedingung; g1
suchMatrix; FILTER(Tabelle1;Tabelle1[Bedingung]=suchBedingung)
MAP(I4#;LAMBDA(x;TEXTVERKETTEN("-";1;FILTER(SPALTENWAHL(suchMatrix;3);SPALTENWAHL(suchMatrix;1)=x)))))

Das ist jetzt rechtgut zu lesen und benötigt keine Hilfstabellen

bei Rückfragen gerne melden

AW: Textverketten, doppelt dynamisch

09.01.2025 14:06:36

BoskoBiati2

Hallo peter,

Deine End-Formeln haben für mich noch einen Haken:
die Profi funktioniert nur mit einer intelligenten Tabelle,
beide benötigen noch die eindeutige Namensliste.

Im Übrigen arbeite ich statt mit SPALTENWAHL auch gerne mit INDEX:

=LET(xa;FILTER(A4:C42;B4:B42=G1);

xb;EINDEUTIG(INDEX(xa;;1));

xc;MAP(xb;LAMBDA(suchWert;TEXTVERKETTEN("-";1;FILTER(INDEX(xa;;3);INDEX(xa;;1)=suchWert))));

HSTAPELN(xb;xc))

Gruß

Edgar

AW: Textverketten, doppelt dynamisch

09.01.2025 12:17:02

daniel

Hi Peter

Funktionert. DANKE.
jetzt muss ich das nur noch verstehen.

Gruß Daniel

AW: Textverketten, doppelt dynamisch

09.01.2025 12:22:13

peter

Danke fürs feedback, habe die erklärung hinterhergeschoben.
Das erstellen hat etwas gedauert.

Textverketten, doppelt dynamisch

Forumthread: Textverketten, doppelt dynamisch

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Textverketten, doppelt dynamisch

Forumthread: Textverketten, doppelt dynamisch

21 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread