Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1912to1916
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

Daten verketten

Daten verketten
21.12.2022 14:45:10
Max
Hallo Excel Profis,
ich stehe vor einer kleinen Herausforderrung und würde mich über Input freuen. :)
Zur Sache:
Ich habe eine dynamische Tabelle mit Daten.
In Spalte A stehen Sammelbegriffe (Art), im Beispiel "abc, dfg, etc."
Jetzt bräuchte ich eine Lösung, wie ich alle Adressen (Spalte B) die mit (Art) bspw. "abc" beginnen mit einander verketten kann (abcd, efgd, bsdd, usw.).
Zusätzlich dürfen immer nur 30 Ergebnisse verkettet werden.
Anschließend müsste eine neue Verkettung mit 30 Ergebnissen stattfinden.
(Also Ergebnis 31-61, usw.)
Als Ziel sollte ich dann eine zusätzliche Tabelle haben welche die entsprechenden verketteten Adressen aufzeigt. Ist das umsetzbar?
Ich habe eine kleine Beispieldatei hinzugefügt.
https://www.herber.de/bbs/user/156879.xlsx
Über Hilfe würde ich mich sehr freuen.
Beste Grüße
Max

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Daten verketten
21.12.2022 16:13:30
Yal
Hallo Max,
es geht mit Power Query ganz leicht.
Wandle deine 2-spaltige Liste in "smart Table" um: Zelle A1 markieren, Menü "Einfügen", "Tabelle",
ich gehe davon aus, dass diese Tabelle "Tabelle1" heisst. Wenn nicht, kann man in Tabellentools den Namen ändern. Alternativ in der erste Zeile des Codes unten anpassen.
Dann darauf eine Power Query Abfrage: Menü "Daten", "Aus Tabelle".
Du bist dann in Power Query Editor. Dort gehst Du in "Start", "Erweiterter Editor" und copy-paste folgenden "Code":

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Art", type text}, {"Adresse", type text}}),
#"Hinzugefügter Index" = Table.AddIndexColumn(#"Geänderter Typ", "Index0", 0, 1),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "Index", each Number.RoundUp([Index0]/ 30,0)),
#"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Index0"}),
#"Gruppierte Zeilen" = Table.Group(#"Entfernte Spalten", {"Index", "Art"}, {{"Anzahl", each _, type table}}),
#"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Gruppierte Zeilen", "Ergebnis", each Text.Combine([Anzahl][Adresse], ";")),
#"Entfernte Spalten1" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte1",{"Anzahl"})
in
#"Entfernte Spalten1"
Du kannst in dem rechten Bereich sehen, welche Arbeitsschritte daraus gemacht werden. Wenn Du auf jede einzelne klickst, bekommst einen Vorschau der Verarbeitung bis zu diesem Schritt.
Menü "Datei", "Schliessen und laden".
Fertig.
VG
Yal
Anzeige
AW: Aufgabenstellung ist mir noch immer unklar ...
21.12.2022 16:55:29
neopa
Hallo Yal,
... das Ergebnis Deines Lösungsvorschlags passt zwar mit der minimalistischen Ergebnisvorgabe überein aber in der weiteren Ergebnislistenausgabe kann ich momentan nur teilweise eine Erklärung finden.
Mein Interpretation der Aufgabenstellung wäre vor dem Einfügen der Indexspalte eine Sortierung der Spalte Art vorzunehmen und die Indexspalte mit einer 1 anstelle einer 0 als Start einzuführen und dann Deine weiteren Schritten genauso zu belassen. Das Ergebnis dessen stimmt zwar nicht mit der minimalistischen Ergebnisvorgabe überein, erscheint mir aber logischer.
Gruß Werner
.. , - ...
Anzeige
AW: Aufgabenstellung ist mir noch immer unklar ...
21.12.2022 18:22:47
Yal
Hallo Werner,
jein. Der Index0 ab Null ist wegen RoundUp([Index0]/30, 0). Dann werden alle Index0 0-29 zu Index 1, 30-59 zu Index 2, usw. Wobei ich stelle fest, dass es nicht funktioniert (*Ohren-lang-ziehen*)
Was ich aber übersehen habe, ist die Sortierung. Nur auf "Art" oder auch auf "Adresse"?
Dann taucht aber einen Denkfehler: bei der zweiten Gruppe "dfg" fängt Index0 bei 48. Somit folgt danach nur 11 Elemente in der erste Adresse-Cluster und 30 im zweiten!
Neue Version:

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Art", type text}, {"Adresse", type text}}),
#"Sortierte Zeilen" = Table.Sort(#"Geänderter Typ",{{"Art", Order.Ascending}, {"Adresse", Order.Ascending}}),
#"Gruppierte Zeilen" = Table.Group(#"Sortierte Zeilen", {"Art"}, {{"GroupBy", each _, type table}}),
#"Hinzugefügter Index" = Table.AddColumn(#"Gruppierte Zeilen","neue",  each Table.AddIndexColumn ([GroupBy], "Index0", 1, 1)),
#"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügter Index",{"GroupBy"}),
#"Erweiterte neue" = Table.ExpandTableColumn(#"Entfernte Spalten", "neue", {"Adresse", "Index0"}, {"Adresse", "Index0"}),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Erweiterte neue", "Index", each Number.RoundUp([Index0]/30,0)),
#"Entfernte Spalten1" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Index0"}),
#"Gruppierte Zeilen1" = Table.Group(#"Entfernte Spalten1", {"Index","Art"}, {{"Group2", each _, type table}}),
#"Erweiterte Anzahl" = Table.AddColumn(#"Gruppierte Zeilen1", "Ergebnis", each Text.Combine([Group2][Adresse], ";")),
#"Entfernte Spalten2" = Table.RemoveColumns(#"Erweiterte Anzahl",{"Group2"})
in
#"Entfernte Spalten2"
Es sind inzwischen soviele Trickserei drin (i.w. Index und Text-Combine in und und auf nested Table), dass man es nicht mehr als "einfach" bezeichnen kann.
VG
Yal
Anzeige
AW: so gesehen, macht es eher Sinn ...
21.12.2022 19:00:37
neopa
Hallo Yal,
... und würde ich nun auch die Aufgabenstellung besser verstehen. Danke Dir.
Nur stimmt es auch nicht mit der Ergebnisvorgabe von Max überein. Mal sehen was er meint, vielleicht hat es sich da ja nur vertan. Und ja, die Lösung ist für einen PQ-Einsteiger wohl schon nicht mehr gleich zu verstehen.
Gruß Werner
.. , - ...
AW: Aufgabenstellung ist mir noch immer unklar ...
21.12.2022 19:17:36
Max
Hallo Yal,
entschuldige, ich hatte meinen Browser noch nicht aktualisiert und somit die neusten Beiträge noch gar nicht gesehen.
Daher war ich mit meiner anderen Antwort zu voreilig.
Mit diesem Lösungsansatz scheint es aber zu passen.
Ich werde es mal in mein bestehendes System einbauen und schauen ob es funktioniert.
Wenn es noch irgendwo klemmen sollte, melde ich mich nochmal. :)
Ich nutze zwar auch häufig PowerQuery, aber das war mir doch zwei Nummern zu hoch. :D
Vielen, vielen Dank auf jedenfall!!
Beste Grüße
Max
Anzeige
AW: Daten verketten
21.12.2022 19:09:36
Max
Hi Yal,
der Lösungsansatz ist genial, leider passt es noch nicht so wie es soll.
Ich erhalte jetzt zur Art "abc" fünf Einträge mit jeweils einer unterschiedlichen Anzahl an Ergebnissen.
Keins weißt jedoch 30 Einträge auf.
Ingesamt gibt es im Beispiel 49 Einträge zu "abc".
Somit müsste ich zwei Einträge erhalten. 1. abc = 30 Ergebnisse und 2. abc = 19 Einträge.
Vielen Dank für deinen Input.
Beste Grüße
Max
AW: lies mal die Folgebeiträge owT
21.12.2022 19:14:59
neopa
Gruß Werner
.. , - ...
AW: lies mal die Folgebeiträge owT
21.12.2022 19:19:07
Max
Hi Werner,
vielen Dank. :)
Mein Browser war noch nicht aktualisiert, so dass ich diese noch nicht gesehen hatte.
Ich denke jetzt passt es.
Vielen Dank für euren super Input!
Beste Grüße
Max
Anzeige
AW: eine Klarstellung wäre schon notwendig ...
21.12.2022 19:27:40
neopa
Hallo Max,
... Du meinst mit "Ich denke jetzt passt es" sicherlich den 2. Lösungsvorschlag von Yal. Und damit auch, dass Deine "Ergebnisvorgabe" zumindest irritierend war. Oder?
Gruß Werner
.. , - ...
AW: eine Klarstellung wäre schon notwendig ...
21.12.2022 20:19:55
Max
Hi Werner,
genau der zweite Ansatz passt perfekt.
Ich hatte es doch auch so in meiner Ergebnisvorlage?
Ich wollte zumindest keine Verwirrung stiften, habe mich da vermutlich unklar ausgedrückt.
Ich freue mich zumindest sehr, dass Ihr mein Problem lösen konntet.
Vielen Dank nochmals. :)
Beste Grüße
Max
Hier zum Spielen in XLWeb für jeden!
21.12.2022 23:39:11
lupo1
https://1drv.ms/x/s!AsnnAXrDppAdnQfz8SPY3j4glyP2?e=GlceUK
In D1 bitte z.B. 30 eintragen
In D2 steht die Formel (zum Übertragen in ein lokales 365).
Ich habe die Formel MAX20221221Herber auf Zeilenaufteilung gestellt und dann mit TEXTVERKETTEN in der Zelle umrankt.
Nach dem Öffnen in XLWeb dauert das Rechnen beim ersten Mal ein paar Sekunden, da XLWeb noch Bibliotheken nachlädt. Danach dann blitzschnell.
Anzeige
AW: Die Nebenbedingung Max 30
22.12.2022 01:59:58
Sulprobil
Für die Nebenbedingung dass maximal 30 Einträge verkettet werden, splittet man das Ergebnis von sbMiniPivot am besten mit einem VBA Programm alle 30 ",".
Viele Grüße,
Bernd
AW: Daten verketten
22.12.2022 20:19:08
Piet
Hallo
mich hat die Frage intereesiert ob man das auch mit VBA lösen kann. Ich denke ja.
Würde mich freuen wenn meine Lösung richtig ist. Bitte einfach mal testen.
https://www.herber.de/bbs/user/156901.xls
mfg Piet
PS es ist ein altes Excel 2003 Format weil ich zur Zeit nur einen XP İLaptop zur Verfügung habe.
Anzeige
AW: Daten verketten
22.12.2022 22:56:13
Yal
Hallo Piet,
leider nicht ganz. Falls Du Spass daran hast, damit weiterzumachen:
sortiere zuerst die Spalte A und B (also zuerst B dann A)
Dann: solang die Wert in Spalte A ("Art") sich nicht ändert, sammle die Werte aus Spalte B ("Adresse") in einer Kette mit Trennzeichen.
Wenn diese Kette 30 Elemente hat, lege den "Art" und die Kette und fange eine neue Kette an.
Wenn der "Art" sich ändert, lege die bisherige Kette ab, und fange eine neue mit dem neuen "Art".
Anschliessend eine Nummerirung ab 1 daneben ablegen.
Die erste Kette von Art "abc" hat 30 Elemente, die zweite 19, dann kommt "dfg", usw.
Viel Spass und viel Erfolg.
VG
Yal
Anzeige
AW: Daten verketten
22.12.2022 23:20:05
Piet
Hallo Yal
vielen Dank für den Hinweis das die Daten unsortiert waren, hatte ich völlig übersehen.
Mit einer Sortierroutine ist das Ergebnis schlagartig anders. Ich hoffe das es jetzt stimmt.
https://www.herber.de/bbs/user/156907.xls Mal sehen was Max dazu sagt?
Frohe Weihnachten und ein glückliches neues Jahr an dich und alle Kollegen aus Ankara
mfg Piet
Achtung Spoiler
22.12.2022 23:35:00
Yal
Es hat mich auch interessiert, das Problem mit VBA einzugehen.
Ich habe aber die Quelle in einer "Smart Table" (ListObject) gepackt, um einfache Objekt zu haben.

Sub Textverketten()
Dim R As ListRow
Dim Anzahl As Long
Dim Art As String
Dim Adressen As String
Worksheets("Ergebnisse").UsedRange.ClearContents
Art = Worksheets("Daten").Range("A2").Value
For Each R In Worksheets("Daten").ListObjects(1).ListRows
If Anzahl = 30 Or Art  R.Range(1) Then
Ablegen Art, Mid(Adressen, 2) 'ohne führende ";"
Anzahl = 0
Art = R.Range(1)
Adressen = ""
End If
Anzahl = Anzahl + 1
Adressen = Adressen & ";" & R.Range(2)
Next
Ablegen Art, Mid(Adressen, 2)
End Sub
Sub Ablegen(ByVal Art As String, AdresseListe As String)
With Worksheets("Ergebnisse").Cells(Rows.Count, "A").End(xlUp)
.Offset(1, 0) = Art
.Offset(1, 1) = AdresseListe
End With
End Sub
VG
Yal
Anzeige
AW: Achtung Spoiler
23.12.2022 00:11:57
Piet
Hallo Yal
ich bewundere immer wieder deine Programmiertechnik. sie ist um Klassen besser wie meine.
Mir macht es trotzdem Spass dabei zu sein, und mit meinen bescheidenem Wissen was ausknobbeln.
Im Thread davor eine schlaue Variante für den Autofilter. TextBox auf Tabelle - Sigrid 21.12.2022 20:05:59
mfg Piet
AW: Achtung Spoiler
23.12.2022 00:57:02
Yal
Hallo Piet,
dran bleiben! Es kommt nicht in einem Tag sondern mit viel, viel Praxis.
Also je mehr Aufgaben angenommen werden, desto mehr Übung bekommt man.
VG
Yal

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige