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

Werte aus Zeilen, Spalten und Zellen in eine Zelle

Werte aus Zeilen, Spalten und Zellen in eine Zelle
30.01.2015 00:09:39
Florian
Hallöchen zusammen,
ich hab mir bei euch im Forum immer wieder mal nützliche Tips bzgl. Excel rausfischen können, doch leider habe ich jetzt einen Fall bei dem ich nicht klar komme und hoffe mir kann jemand helfen.
Ich möchte gerne alle Werte aus verschiedenen Zeilen, Spalten und Zellen in einer Zelle zusammenfassen. Die Art.No ist immer der Ausgangspunkt, ich brauche später alles in einer Zeile um es weiterverarbeiten zu können.
Es soll später so aussehen:
Art.No | Alle Attribute
293.01 | (Größe)S+M+L+XL+2XL(Farbe)Schwarz+Grau(Hersteller Farbe)Black+Heather Grey+Charcoal
Und das hier sind meine Werte in einzelnen Spalten und Zeilen:
Art.No | Größe | Farbe | Hersteller Farbe
293.01 | S | Schwarz | Black
293.01 | M | Schwarz | Black
293.01 | L | Schwarz | Black
293.01 | XL | Schwarz | Black
293.01 | 2XL | Schwarz | Black
293.01 | S | Grau | Heather Grey
293.01 | M | Grau | Heather Grey
293.01 | L | Grau | Heather Grey
293.01 | XL | Grau | Heather Grey
293.01 | 2XL | Grau | Heather Grey
293.01 | S | Grau | Charcoal
293.01 | M | Grau | Charcoal
293.01 | L | Grau | Charcoal
293.01 | XL | Grau | Charcoal
293.01 | 2XL | Grau | Charcoal
Ich habe es schon mit Wenn und svrevs versucht aber schaffe es einfach nicht :-( Kann mir hier jemand helfen?
Grüße
Florian

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Aus deinen Daten ergibt sich aber folgendes ...
30.01.2015 05:12:38
Luc:-?
…Ergebnis, Florian,
da die ArtikelNr immer dieselbe ist:
(Größe)S+M+L+XL+2XL(Farbe)Schwarz+Grau+Blau+Rot+Lila+Grün+Braun(Hersteller Farbe)Black+Heather Grey+Charcoal+Deep Navy+Royal+Red+Burgundy+Classic Olive+Chocolate
Wenn man die ArtNrn 3teilt, ergibt sich hingegen folgendes Ergebnis:
293.01 (Größe)S+M+L+XL+2XL(Farbe)Schwarz+Grau(Hersteller Farbe)Black+Heather Grey+Charcoal
293.02 (Größe)S+M+L+XL+2XL(Farbe)Blau+Rot+Braun(Hersteller Farbe)Deep Navy+Royal+Red+Chocolate
293.03 (Größe)S+M+L+XL+2XL(Farbe)Lila+Grün(Hersteller Farbe)Burgundy+Classic Olive
(Hier absichtlich unregelmäßig geteilt, um zu zeigen, dass die ArtikelNrn nicht sortiert vorliegen müssen!)
Wenn dafür folgd KopfZeile angelegt wird, …
Art.No (Größe)…(Farbe)…(Hersteller Farbe)
…kann mit ff Fmln* gearbeitet wdn (UDF):
1. KopfZeile: =A1 bzw ="("&VJoin(B1:D1;")…(")&")"
2. ArtikelNr: @MTRANS(VSplit(VJoin(A$2:A$46;;1)))
3. Attribute: @WECHSELN(WECHSELN(F$1;"…";VJoin(WENN(A$2:A$46=E2;B$2:B$46;"");"+";1);1);"…";VJoin(WENN(A$2:A$46=E2;C$2:C$46;"");"+";1);1)&VJoin(WENN(A$2:A$46=E2;D$2:D$46;"");"+";1)
* Fmln 2+3 sind MatrixFmln, deren Eingabe mit [[Strg][Umsch]][Enter] beendet wdn muss, woran das von Xl automatisch durch = ersetzte @ erinnern soll!
Hinweis: Unter dem oben angegebenen Link sind zwar beide UDFs zu finden, aber VJoin nur in Vs1.0, die hier nicht das gewünschte Ergebnis liefern würde. Das liefert aber die Vs1.1, die sich ebenfalls im Archiv befindet. Dort steht dann auch noch eine einfache Alternative zu VSplit.
Ich habe diese Variante gewählt, da erfahrungsgemäß eine nur auf Xl-StandardFktt basierende Lösung ellenlange Fmln ergeben würde, wenn sie hier überhpt möglich (bzw vertretbar — darüber denke ich nicht weiter nach, da ich ja Besseres zV habe → viel Erfolg, Werner! ;-] ) wäre.
Morrn, Luc :-?
Besser informiert mit …

Anzeige
AW: Aus deinen Daten ergibt sich aber folgendes ...
30.01.2015 11:05:24
Florian
Erstmal vielen lieben Dank für diesen schnellen Lösungansatz. Ich hab Ihn zwar noch nicht ganz verstanden, werde aber sofort mit dem Testen beginnen und mich dann wieder melden :-D

AW: Aus deinen Daten ergibt sich aber folgendes ...
30.01.2015 11:15:36
Florian
Ich glaube ich hab da einen Fehler gemacht , sorry :-( Die ArtNo. wechselt im Verlauf der kompletten Tabelle, ist aber immer in dem Format 000.00
Insgesamt haben wir 40.000 Zeilen und das sind ca. 1.500 verschiedene ArtNo die sich aber pro Größe, Farbe und Herstellerfarbe wiederholen. Ich muss aber die ArtNo. in einer Zeile in der ersten Zelle haben und in der zweiten Zelle daneben dürfen dann nur die Varianten zusammengefasst stehen: (Größe)S+M+L+XL+2XL(Farbe)Lila+Grün+Rot+Grau(Hersteller Farbe)Burgundy+Classic Olive+Grey+Deep Red

Anzeige
Ja, das hatte ich auch vermutet, ...
30.01.2015 11:50:43
Luc:-?
…Florian!
Wenn du in der 1.Zeile einen Kopf verwendest, der die Spalten mit den Attribut-Namen bezeichnet, kannst du das alles mit den von mir genannten Fmln in NachbarSpalten zusammenfassen, wobei sich die 1.Fml auf diese KopfZeile bezieht und eine neue erzeugt, die in der 3.Fml (1zellige MatrixFml) benötigt wird. Die 2.Fml reicht mehrzellig über alle 40Tsd Zeilen der Spalte A mit den ArtikelNrn (was ein Problem sein könnte), um hier jede vorhandene ArtikelNr nur 1× aufzuführen. Das kannst du ggf aber auch anders bewerkstelligen. Wichtig ist nur, dass in der 1.Spalte alle vorhandenen ArtikelNrn nur 1× aufgeführt wdn und eine KopfZeile des gezeigten Inhalts vorhanden ist (kannst du auch aus meiner AW als Text herauskopieren). Die entscheidende ist dann die 3.Fml, die von Zeile zu Zeile nach unten kopiert wdn kann, wenn die absoluten ZellbereichsAngaben entsprd der Realität korrigiert wdn.
Allerdings sind 40Tsd Zeilen etwas happig und ich kann nicht für allzugroße Performance garantieren, da jede einzelne Fml wieder den GesamtBereich durchgehen muss. In solchen Fällen ist eine Lösung mit einer VBA-Prozedur, die alles nur 1× durchläuft, idR besser, weil schneller (vor allem, wenn sie die Daten zuvor in Arrays oder einem Dictionary-Objekt anlegt.
Da die Daten sicher aus einer DBank stammen, wäre auch eine Lösung mit Access o.Ä. sinnvoll, denn man sollte sich keine Riesenmenge von Daten aus einer DB in Xl laden, ohne diese vorzuverdichten, was hier wohl auf eine KomplettLösung mit Access hinauslaufen würde. Aber das ist dann keine in einem XlForum anzusiedelnde Baustelle.
40Tsd DSätze könnte aber noch eine für XL erträgliche Datenmenge sein…
Gruß, Luc :-?

Anzeige
AW: Ja, das hatte ich auch vermutet, ...
30.01.2015 14:53:06
Florian
Dankeeee, soweit hab ich jetzt alles übernommen, aber leider hab ich trotzdem ein Problem. Die Artikelnummern in Spalte A kommen so oft vor wie es Attribute gibt. Es ist leider nicht möglich diese rauszubekommen, da sonst die Zuordnung der Attribute in den entsprechenden Zeilen verloren geht.
Für die eine Artikelnummer klappt das super, aber wenn die Artikelnummer in A wechselt, wird in Spalte E weiterhin die erste Artikelnummer verwendet und in F wird dann #WERT! angezeigt.

AW: Ja, das hatte ich auch vermutet, ...
30.01.2015 15:06:59
Florian
*Korrektur*
Ich habe Strg Shift Enter vergessen, jedoch besteht noch das gleiche Problem. In E wird immer nur die erste ArtNo. verwendet und somit in F in jeder Zeile das gleiche ausgegeben, egal welche ArtNo. in A steht. Den Bereich hab ich in jeder Spalte auf 40.000 erhöht.

Anzeige
AW: Ja, das hatte ich auch vermutet, ...
30.01.2015 15:54:41
Florian
Und fertig :-D
=MTRANS(VSplit(VJoin(A2:A$40000;;1))) bei A2 das $ raus und alles geht.
Vielen vielen lieben Dank und ein wunderschönes Wochenende :-D

Das war ursprünglich auch eine MatrixFml, aber ...
30.01.2015 18:02:29
Luc:-?
…über den ganzen Bereich von 40Tsd Zeilen, Florian,
also eine sog mehrzellige MatrixFml. Die hätte dann auch gar keine $ benötigt, weil hier nur einmal alle sich unterscheidenden ArtNrn ermittelt und dann dargestellt worden wären. Würdest du den Bereich für diese Fml größer wählen als dann Werte ermittelt wurden, würde in den überzähligen Zellen #NV stehen.
Wie das jetzt mit einer 1zelligen MatrixFml, bei der nur das Ende des Bereichs fixiert ist und sich der Anfang stets verschiebt, fktt, kann ich momentan nicht nachvollziehen (bin gerade auf Linux), aber du scheinst ja eine Lösung gefunden zu haben. Wichtig bei dieser Operation ist ja auch nur, dass danach alle ArtikelNrn nur 1× dastehen, denn die wdn so für die entscheidende 3.Fml benötigt, die dann tatsächlich eine 1zellige MatrixFml sein muss.
Nebenbei, eine Lösung nur mit XlStandardFktt halte ich, auch unter HilfsZellenVerwendung, für zu kompliziert, um sonderlich praxistauglich zu sein.
Übrigens, falls du eine andere oder keine Überschrift haben möchtest, müsste der Ausdruck aus 1.Fml in die 3. an entsprd Stelle als Text oder Fml eingesetzt wdn, was sie aber unübersichtlicher machen würde.
Gruß & dito schöWE, Luc :-?

Anzeige
AW: Das war ursprünglich auch eine MatrixFml, aber ...
30.01.2015 19:20:58
Florian
Praxistauglich ist es nicht wirklich. Ich hab die Datei grade extra auf meinen Spielerechner geschoben weil der mehr Power hat um das zu verarbeiten, er rechnet jetzt schon seit ca. 20 Minuten. Unterm Strich brauche ich das ganze nur ein einziges Mal um den Datenstamm einzulesen. Später wird die Tabelle nicht mehr als 1000 Einträge haben, wenn mal neue Produkte dazu kommen. Ich habe jetzt 40.000 Zeilen mit den entsprechenden Attributen in einer Zelle so wie ich das brauche, die doppelten ArtNo entferne ich einfach mittels Spezialfilter. :-) Mag vielleicht nicht die schönste Lösung gewesen sein so wie ich das gebastelt habe, aber du hast mir sehr sehr geholfen :-)
Liebe Grüße
Flo

Anzeige
Na, denn isset ja jut! owT
30.01.2015 20:07:51
Luc:-?
:-?

AW: Na, denn isset ja jut! owT
31.01.2015 00:45:29
Florian
Leider ist irgendwo in der Formel ein Fehler. Wenn die Größe XXS oder XS vorhanden ist wird XS oder S nicht mehr importiert :'(
Also wenn das vorhanden: XXS,XS,S,M,L,XL,2XL dann sieht es so aus XXS,M,L,XL,2XL
oder wenn so: XS,S,M,L,XL,2XL dann so XS,M,L,XL,2XL
Mache ich was falsch oder stimmt da was nicht? Hab jetzt Stunden nach dem Fehler gesucht :-(

AW: Na, denn isset ja jut! owT
31.01.2015 01:58:26
Florian
Ich habe den Fehler noch mal etwas weiter eingegrenzt und zum Test mal die Sortierung verändert. Wenn die Größen aufsteigend Größer werden funktioniert alles, solange kein XXS oder XS vor dem hier auftaucht: S,M,L,XL,2XL
Wenn man die Sortierung aber umkehrt zu 2XL,XL,L,M,S sieht das Ergebnis so aus: 2XL,M,S
Bedeutet für mich, wenn ein der Buchstabe einer Größe schon mal in Verbindung mit einem anderen Buchstaben (X) angezeigt wurde, wird dieser nicht mehr hinzugefügt und übersprungen.
GN8

Anzeige
Ja, das ist möglicherweise kein Fehler von dir,...
31.01.2015 04:05:24
dir,...
…die Daten liegen ja nunmal so vor, Florian,
aber evtl eine Ungenauigkeit in VJoin, was ich erst mal untersuchen muss. Evtl reicht aber auch schon eine kleine FmlKorrektur.
Melde mich dann noch mal.
Gruß + schöWE, Luc :-?

Ja, der Fehler tritt auf, wenn in Spalte B ...
31.01.2015 05:17:09
Luc:-?
…die längeren GrößenAngaben wie XL, 2XL, XS vor den einfachen kurzen L und S auftreten, Florian;
dann erkennt die UDF Letztere als schon vorhanden und lässt sie weg. Diese Fälle habe ich wohl nicht ausreichend im Pgm abgefangen. Bevor ich eine Vs1.2 ins Forum stelle, kannst du diesen Fall auch erstmal in der Fml abfangen, indem du das hierfür zuständige 1.VJoin-Konstrukt durch folgendes ersetzt:
GLÄTTEN(WECHSELN(VJoin(WENN(A$2:A$46=E2;" "&B$2:B$46;"");;1);"  ";"+"))
Luc :-?

Anzeige
AW: Ja, der Fehler tritt auf, wenn in Spalte B ...
31.01.2015 11:59:19
Florian
Danke für deine Geduld und deine Hilfe, aber ich bekomme es einfach nicht auf die Kette.
@WECHSELN(WECHSELN(F$1;"…";VJoin(WENN(A$2:A$40000=E2;B$2:B$40000;"");"+";1);1);"…";VJoin(WENN(A$2:A$40000=E2;C$2:C$40000;"");"+";1);1)&VJoin(WENN(A$2:A$40000=E2;D$2:D$40000;"");"+";1)
Wie muss die Formel aussehen? Ich hab es jetzt schon wieder 30 Minuten lang versucht. Als Anfäger der nicht wirklich was davon versteht, komme ich mir langsam echt doof vor :-( Tut mir leid.

Na, gemeint war doch Folgendes, ...
31.01.2015 14:42:51
Luc:-?
…Florian;
in der bisherigen Fml ist der Probleme bereitende VJoin-Teil doch der fett-blau markierte:
@WECHSELN(WECHSELN(F$1;"…";VJoin(WENN(A$2:A$40000=E2;B$2:B$40000;"");"+";1);1);"…";VJoin(WENN(A$2:A$40000=E2;C$2:C$40000;"");"+";1);1)&VJoin(WENN(A$2:A$40000=E2;D$2:D$40000;"");"+";1)
Genau den solltest du austauschen, wobei eine Variante, bei der dem Bereich vorn und hinten LeerZeichen hinzugefügt wdn und dann 3 LeerZeichen gg + ausgetauscht wdn müssen, ggf besser wäre; also dann insgesamt so:
@WECHSELN(WECHSELN(F$1;"…";GLÄTTEN(WECHSELN(VJoin(WENN(A$2:A$40000=E2;" "&
B$2:B$40000&" ";"");;1);"   ";"+"));1);"…";VJoin(WENN(A$2:A$40000=E2;C$2:C$40000;
"");"+";1);1)&VJoin(WENN(A$2:A$40000=E2;D$2:D$40000;"");"+";1)
Aber das ist nun nicht mehr nötig, weil ich die Vs1.2 fertig habe, die auch noch wie die Vs1.1 fktioniert, wenn man sie wie bisher argumentiert. Sie benötigt allerdings eine Enumeration, die separat, ganz am Anfang des Moduls angegeben wdn muss. Deshalb zeige ich hier mal den ganzen ModulAnfang:
Option Explicit
Public Enum xlTriState: xlTrue = -1: xlFalse: xlCTrue: End Enum

Rem Verbinden aller Elemente 1es belieb Vektors
'   Arg1: ZBereich or DatenFeld (aus Ausdruck);
'   Arg2: BindeTxt - fehlt LeerZchn, leer ohne,
'   Fwert lokal ListTrennZ; Arg3: fehlt/0 alle,
'   ±1 ohne leere u.Wdholgg, -1 ganze Elemente,
'   +1 auch ElemTeile abhgg v.AuftrittsRhfolge.
'   Achtung! Benötigt Enum[eration] xlTriState!
'   Vs1.2 -LSr -cd:20130904 -1pub:20130905herber -lupd:20150131t
Function VJoin(Bezug, Optional ByVal BindeZ, Optional ByVal NurUngl As xlTriState)
Dim lix As Long, pix As Long, erg, xBez As Variant
On Error Resume Next: NurUngl = Sgn(NurUngl)
If Not IsMissing(BindeZ) Then
If IsError(BindeZ) Then BindeZ = Application.International(xlListSeparator)
Else: BindeZ = " "
End If
With WorksheetFunction
If TypeName(Bezug) = "Range" Then Bezug = .Transpose(.Transpose(Bezug))
If IsError(LBound(Bezug)) Then
VJoin = Bezug
ElseIf CBool(NurUngl) Then
If NurUngl = xlTrue Then ReDim erg(0)
For Each xBez In Bezug
If NurUngl = xlTrue Then
If CBool(lix) Then
pix = 0: pix = .Match(xBez, erg, 0)
If pix = 0 Then ReDim Preserve erg(lix): _
erg(lix) = xBez: lix = lix + 1
Else: erg(0) = xBez: lix = lix + 1
End If
ElseIf Not IsEmpty(erg) Then
If xBez  "" And InStr(erg, xBez) = 0 Then _
erg = erg & BindeZ & xBez
Else: erg = xBez
End If
Next xBez
ElseIf IsError(LBound(Bezug, 2)) Then
erg = Join(Bezug, BindeZ)
Else: Bezug = .Transpose(Bezug)
If IsError(LBound(Bezug, 2)) Then
erg = Join(Bezug, BindeZ)
Else: erg = CVErr(xlErrRef)
End If
End If
End With
If NurUngl = xlTrue Then VJoin = Join(erg, BindeZ) Else VJoin = erg
End Function
Die Fml kann dann auch mit 3.VJoin-Argument=-1 formuliert wdn:
@WECHSELN(WECHSELN(F$1;"…";VJoin(WENN(A$2:A$40000=E2;B$2:B$40000;"");"+";-1);1);"…";VJoin(WENN(A$2:A$40000=E2;C$2:C$40000;"");"+";-1);1)&VJoin(WENN(A$2:A$40000=E2;D$2:D$40000;"");"+";-1)
Die oben gezeigte, korrigierte alte Fml sollte allerdings das gleiche Ergebnis liefern wie diese hier.
Viel Erfolg! Gruß, Luc :-?
Anzeige

350 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige