Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

Forumthread: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

22.04.2009 15:08:59

Michel

Hallo allerseits,
stehen z.B. in der Spalte A mehrere gleiche Suchkriterien, dann wird mit SVERWEIS ja nur der erste Datensatz ausgelesen.
Ich suche nach einer Möglichkeit/Funktion, welche mir nicht nur die erstgefundenen Daten ausgibt, sondern auch alle weiteren (in verschiedenen Zellen).
Ist es zudem möglich, in diesen SVERWEIS auch noch eine ENTKETTUNG miteinzubauen?
Der SVERWEIS dürfte sich zudem nicht auf eine gewisse Anzahl Wiederholungen beschränken, da das Suchkriterium von 1 bis n Wiederholungen aufweisen kann.
Als Anlage beigfügt, finden sie bsp-haft die Datenbasis (Tabellenblatt "Logfile_Entwurf) und das Zieldatenblatt "Datenentkettung". Im Zieldatenblatt geht es nun darum, die Segmente-Strings aus der Datenbasis in seine Einzelteile zu zerstückeln (oder eben entketten). Habe dies mit den Segmenten 0100@ & 0500@ simuliert. Beim 0500@ besteht zusätzlich die Schwierigkeit, dass es n-fach auftreten kann und ebensooft in die Zieldatei entkettet werden muss.
https://www.herber.de/bbs/user/61359.xls
Wer kann mir helfen?
Freundliche Grüsse
Michel

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

22.04.2009 15:20:05

David

Hallo Michel (hier im Forum gilt allgemein das DU),
zum "Entketten" gibt es diverse Textfunktionen (FINDEN/SUCHEN in Verbindung mit RECHTS/LINKS/TEIL etc.). Allerdings ist für mich nicht eindeutig erkennbar, wie man denn die Trennstellen erkennt, d. h. diese müssen einigermaßen eindeutig sein.
Ein "SVERWEIS mit mehreren Suchkriterien" realisiert man normalerweise mit SUMMENPRODUKT.
Schau dir mal die Lösungen auf dieser Seite an, mit "Excel gut" solltest du da einiges für dich rausziehen können.
http://www.excelformeln.de/formeln.html
Gruß
David

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

22.04.2009 16:33:51

Michel

Hallo David,
danke für deine Rückmeldung.
Die Trennstellen ergeben sich aus der Spalte E (L = Länge = Anzahl Muss-Zeichen welche pro Segmentabschnitt zur Verfügung stehen) im Tabellenblatt "Datenentkettung".
Die erste Trennstelle des Segmentes 0100@ ist somit nach 5 Zeichen fällig, die zweite Trennstelle nach 17 Zeichen (5 + 12), etc.
Als Resultat steht somit in Zelle A2 '0100@' (= die ersten 5 Zeichen des Segmentes 0100@ aus Zelle B2 des Tabellenblattes "Logfile_Entwurf"), in Zelle A3 'EDIFTP ' (= die nächsten 12 Zeichen; 6 Buchstaben und 6 x Space) etc.
Ich habe mich aufgrund deiner Hinweises mal rumexperimentiert, jedoch ohne zählbares resultat... mein Excel-Rating "gut" war wohl etwas zu optimistisch.. :((
Kannst du mir mit einem (Formel-)Tip auf die Sprünge helfen? Das wäre super!!!
Gruss, Michel

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

23.04.2009 08:49:36

David

Hallo Michel,
anbei ein Entwurf für dein Problem.
Ich habe die Segmentierung neben den Daten vorgenommen und das Blatt "Daten_Entkettung" nur als Basis für die Längen und Positionen der einzelnen Segmente genommen. Wichtig ist hier, dass dort jeder Datensatz nur EINMAL vorkommt, sonst funzen die Summenprodukt-Formeln nicht.
Ich weiß nicht, inwiefern die Darstellung untereinander für dich wichtig ist. Dies ließe sich zwar auch realisieren, allerdings würde das einen verhältnismäßig hohen Aufwand bedeuten, so daß ich dir empfehle, mit den Daten in dieser Darstellung auszukommen.
Wenn deine auszuwertende Liste erheblich länger wird, könnte es außerdem Probleme mit der Performance geben, da Summenprodukt bei langen Listen langsam wird.
https://www.herber.de/bbs/user/61366.zip
Rückmeldung wäre nett.
Gruß
David

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

25.04.2009 19:06:22

Michel

Hallo David,
Vielen Dank für die Lösung, einfach genial!
Leider müsste ich die Darstellung trotzdem vertikal haben; der Grund dafür kannst du aus der xls.Beilage sehen:
- jedes "entkettete" Datenelement referenziert auf ein spezifisches window in einem anderen System, genannt CIS1 (vgl. Spalten A-D des Tabellenblatts "EDIFTP Dat-Kat_blanko"
Aufgrund dieser Tatsache muss ich die vertikal erstellen.
Via einfachen SVERWEIS bringe ich die entkettetten Daten aus Tabellenblatt 'Logfile_INPUT' ohne Probleme in Spalte E des Tabellenblattes 'EDIFTP Dat-kat_blanko', aber nur solange die Segment-ID nicht mehr als 1Mal vorkommt. So funktioniert dieser SVERWEIS für das Segment 0500@ das erste Mal nicht mehr...
Kannst du mir anhand des Segmentes 0500@ aufzeigen, was zu tun ist? Wie muss die Formel in Zelle E268 (gelb markiert) lauten, damit dort die richtige "entekettete" Info, nämlich '200', erscheint? vgl. hierzu nachfolgenden Link.

Die Datei https://www.herber.de/bbs/user/61408.xls wurde aus Datenschutzgründen gelöscht

Vielen vielen Dank zum voraus.
Gruss Michel

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

27.04.2009 07:53:29

David

Hallo Michel,
anbei deine Datei. War eine ganz schöne Knobelei, das so hinzubekommen, dass die Formel kopierbar ist.
Ich hoffe, sie stimmt auch mit dem "Echtdaten", da das Testen nur mit den 2 Zeilen wahrscheinlich nicht alle Kombinationen aufzeigt.
Noch kurz zur Erläuterung:
Die Formel in E3 kannst du beliebig runterkopieren. Bitte dabei darauf achten, dass es eine Matrixformel ist (bei Bearbeitung mit STRG-SHIFT-ENTER abschließen). Die Daten werden aus der Matrix C1:V3 deines Input-Blattes genommen. Die Feld-Nr. in der Auswertungstabelle stellt dabei die Spalte dar, d.h. 1 = C, 2 = D usw.
Mal sehen, ob du damit klar kommst.
https://www.herber.de/bbs/user/61431.xls
Gruß
David

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

27.04.2009 09:44:04

Michel

Hallo David
Du hattest mit deiner Vermutung recht: mit 2 Datensätzen ist die Lösung (noch) nicht simulierbar.
Ich habe deine Formel mal in die Enddatei reinkopiert (Tabellenblatt "EDIFTP Dat-Kat_blanko" Zellen E9:E493 - rot markiert), mit welcher ich später arbeiten will. Der Wert der herausgegeben wird entspricht jedoch (noch) nicht dem "entketteten" Datenelement, welches eigentlich als Resultat erscheinen sollte. Wieso zieht es zb in Zelle E9 "0300@" statt richtigerweise "0100@"?
Zudem: im Tabellenblatt "Logfile_INPUT" erkennst du, dass einige Segmente ab dem Segment 0500@ (die Segmente 0100@ bis 0411@ kommen zu 100% nur immer einmal vor) 1 bis n-fach vorkommen können... ist diese "Dynamik" überhaupt beherrschbar?
Zum besseren Verständnis bzw. Nachvollziehbarkeit habe ich diesmal die gesamte Datei reingestellt.
https://www.herber.de/bbs/user/61434.zip
Wenn das wirklich funktioniert, bin ich der glücklichste Mensch auf Erden (...und das an einem Montag!!!).
Grüsse,
Michel

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

27.04.2009 11:25:50

David

Hallo Michel,
ziemliche Nuss zum Knacken. Da tatsächlich kein einheitliches Muster vorhanden ist, waren einige Hilfspalten erforderlich. Diese habe ich auf ein separates Blatt ausgelagert.
Ich würde dir raten, die Echtdaten in diese Datei zu kopieren und nicht die Formeln hieraus in deine Echt-Datei zu kopieren, da ich nicht dafür garantieren will, dass diese woanders problemlos arbeiten. Durch die Verwendung von absoluten und relativen Zeilenbezügen etc. könnte das Probleme aufwerfen.
Innerhalb dieser Datei scheint es jetzt aber soweit wie gewünscht zu funktionieren.
Außerdem könnte mit zunehmenden Datenvolumen ein Performance-Problem auftreten, da diese Matrix-Formeln und Summenprodukt-Formeln recht rechenintensiv sind.
Falls du die Formeln bearbeitest, denk bitte an die Matrixformeln, erkennbar an den {} !!! Um eine solche zu erzeugen, am Ende der Bearbeitung STRG-SHIFT-ENTER drücken!
https://www.herber.de/bbs/user/61437.zip
(um die Dateigröße unter 300kb zu kriegen, musste ich leider einige Zeilen löschen. Die Formeln lassen sich aber runterkopieren.)
Gruß
David

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

27.04.2009 14:58:00

Michel

Hallo David,
danke erstmal für die Superlösung!
Für kleine Dateien würde diese Lösung eigentlich genügen, jedoch habe ich mal absichtlich das grösstmögliche Logfile reingestellt (vgl tabellenblatt "Logfile_INPUT" Spalte B) und verarbeiten lassen, welches ich gefunden habe.
Einziges nun noch zu lösendes Problem: im Tabellenblatt "CIS1_Analyse_IST_SOLL" funzen die Formeln so nur bis Zeile 464. Einige Verweise in diversen Formeln lauten auf die Hilfstabelle und dort immer auf die Bereiche $d$2:$d$20 bzw. $h$2:$h$20.
In der Hilfstabelle wiederum hat es Bereiche lautend auf a1:a62 bzw. a2:a62 (mit und ohne $), obwohl die Hilfstabelle eigentlich bis Zeile 399 geht.
Offensichtlich stimmt bei diesen Bereichsangaben etwas noch nicht, habe deshalb versucht diese Bereiche zu erweitern (bis Zeile 399), leider ohne Erfolg. Weisst du ein letztes Mal Rat?
https://www.herber.de/bbs/user/61448.zip
Grüsse,
Michel

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

27.04.2009 15:35:15

David

Hallo Michel,
Blatt CIS1_Analyse_IST_SOLL:
CIS1_Analyse_IST_SOLL

	H	I
9	0100@	1
10	0100@	2

Zelle	Formel
H9	=TEXT(INDEX(Hilfstabelle!$D$2:$D$47;WENN(ISTFEHLER(VERGLEICH(ZEILE(B2)-2;Hilfstabelle!$H$2:$H$47;1));1;VERGLEICH(ZEILE(B2)-2;Hilfstabelle!$H$2:$H$47;1)+1));"0000")&"@"
H10	=TEXT(INDEX(Hilfstabelle!$D$2:$D$47;WENN(ISTFEHLER(VERGLEICH(ZEILE(B3)-2;Hilfstabelle!$H$2:$H$47;1));1;VERGLEICH(ZEILE(B3)-2;Hilfstabelle!$H$2:$H$47;1)+1));"0000")&"@"
I10	=WENN(H10<>H9;1;WENN(CIS1_Analyse_IST_SOLL!I9<SVERWEIS(INDEX(Hilfstabelle!$D$2:$D$47;WENN(ISTFEHLER(VERGLEICH(ZEILE(B3)-2;Hilfstabelle!$H$2:$H$47;1));1;VERGLEICH(ZEILE(B3)-2;Hilfstabelle!$H$2:$H$47;1)+1));Hilfstabelle!$D$2:$F$47;3;0);CIS1_Analyse_IST_SOLL!I9+1;1))

Tabellendarstellung in Foren

Die beiden Formeln in Zeile 10 kannst du dann runterkopieren. Wichtig hier ist die Abweichung in I9, dort nur die "1" eintragen, sonst gibt's Fehler.
Noch zur Hilfstabelle:
Spalte A: Dies ist eine Auflistung aller Segment-ID aus der Input-Tabelle, umgewandelt in Zahlen, da diese leichter handelbar sind. D.h. hier müssen die Formeln so weit runterkopiert sein, wie es Zeilen in der Input-Tabelle gibt
Spalte B: Hier wird gezählt, wie oft die Segment-ID jeweils vorkommt
Spalte C: Anzahl der Splits jeder Segment-ID
Spalte D: Dies ist eine Auflistung der Spalte A, allerdings ohne Duplikate Wenn du ungefähr weißt, wieviel IDs es geben kann, solltest du die Formeln auch nur auf diese Anzahl begrenzen, das steigert die Performance ERHEBLICH. In der Formel selbst muß natürlich der komplette Bereich der Spalte A referenziert sein, dies hatte ich noch im Entwurfsstadium, da sich das leichter handeln läßt und die Fehlersuche leichter gestaltet.
Hier nochmal die Formeln aus dem Hilfsblatt:
Hilfstabelle

	A	B	C	D	E	F	G	H
1	Überschrift	Vorkommen	Teile	Element	Vorkommen	Teile	Zeilen	Zeilen kum.
2	100	1	6	100	1	6	6	6

Zelle	Formel
A2	=WECHSELN(Logfile_INPUT!C2;"@";"")*1
B2	=ZÄHLENWENN($A$2:$A$399;A2)
C2	=ANZAHL2(Logfile_INPUT!D2:V2)-ANZAHLLEEREZELLEN(Logfile_INPUT!D2:V2)+1
D2	{=INDEX(A$2:A$399;-1+KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A$1;;;ZEILE($A$2:$H$399));A$2:A$399)=1;ZEILE($A$2:$H$399));ZEILE(A1)))}
E2	=SVERWEIS(D2;$A$2:$C$399;2;0)
F2	=SVERWEIS(D2;$A$2:$C$399;3;0)
G2	=E2*F2
H2	=SUMME($G$2:G2)

{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren

Diese kannst du dann für A:C bis 399 (oder weiter) runterkopieren, für D:H dann halt so weit, wie benötigt (siehe oben).
Hoffe, damit kommst du weiter.
Gruß
David

AW: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

27.04.2009 16:57:41

Michel

Hallo David,
es funktioniert nun alles bestens!!! Danke danke danke!
...wenn nur alle Montage so enden würden!
Wünsche dir einen schönen Abend und vielleicht ein andermal (dann hoffentlich nicht mit einer
so extremen Anforderung.. ;)
Grüsse,
Michel

Beliebteste Forumthreads (12 Monate)

Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

Forumthread: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

Forumthread: Entkettung kombi. SVERWEIS mit >1 gleiche Suchkrit

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread