Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1496to1500
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übernahme per Formel, wenn bereits vorhanden

Datenübernahme per Formel, wenn bereits vorhanden
14.06.2016 19:47:33
Jenny
Hallo an euch alle,
wäre super lieb von euch wenn ihr mir helft,
es geht mir darum, ob das was ich hier beispielhaft in der Tabelle zeige auch per Formel geht.
Meine Originaltabelle ist in 2 Teile unterteilt, Nr. 1 der Bereich bis zur Zeile 6526
2. Teil alles was danach kommt.
In der Bsp. Tabelle hab ich den ersten Bereich auf 100 Zeilen beschränkt.
Ziel ist es, ich schreibe in den 2. Bereich neuen Text in die Spalten A und E wie in Bsp. Zeile 101
Der rotgeschriebene Text in Zelle D101 soll von einer Formel kommen, indem die Formel erkennt, dass es die Kombination aus A101 und E101 auch im ersten Teil (Zeile 80) steht und D80 ausgibt.
2. Beispiel, Zeile 102, hier wird der Text von Spalte E nicht in Spalte D gefunden wie im ersten Beispiel, sondern in Spalte E (Zeile 78) Hier soll die Formel dann D78 ausgeben.
Wie ihr seht, was ich im 2. Teil in Spalte E schreibe, kann im ersten Teil sowohl in Spalte D als auch E stehen. Der Text, den ich in Spalte A schreibe, steht in jedem Fall immer auch in Spalte A im ersten Teil.
Es kann in der Orig. Tabelle sein, dass die Kombination die ich eingebe mehrfach im ersten Teil vorkommt, ist aber nicht weiter schlimm da in all diesen Zeilen der Text in Spalte D identisch ist.
Danke für eure Hilfe Jenny
https://www.herber.de/bbs/user/106235.xlsx

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Datenübernahme per Formel, wenn bereits vorhanden
14.06.2016 21:07:29
Michael
Hi Jenny,
anbei eine umständliche Formellösung: https://www.herber.de/bbs/user/106239.xlsx
Einem der Formelprofis fällt bestimmt noch was besseres ein; ich würde ohnehin VBA nehmen, vor allem falls die Spalten A und E unterhalb von 6526 "auf einmal" (also etwa über die Zwischenablage) mit sehr vielen Werten gefüllt werden soll(t)en.
Schöne Grüße,
Michael

AW: Datenübernahme per Formel, wenn bereits vorhanden
14.06.2016 21:24:10
Jenny
Hallo Michael,
erstmal danke, nur leider (vorausgesetzt ich verstehe richtig was du da gemacht hast) läuft da was falsch. Deine Formeln in den Spalten F bis H geben das erste Auftreten des Textes aus oder?
Aber das muss ja nicht zwangsweise auch das erste auftreten sein, wo beide Texte vorkommen?
Habe aber die Tabelle um es einfacher zu machen, ein wenig umgebaut, jetzt reicht es in Spalte E zu suchen, D wird nimmer benötigt.
LG
Jenny

Anzeige
stimmt
14.06.2016 23:36:50
Michael
Hi Jenny,
stimmt, das war ein Denkfehler von mir...
Schöne Grüße,
Michael

AW: stimmt
14.06.2016 23:51:02
Jenny
Hallo Michael,
kein Ding, ne Idee hast du trotzdem nicht zufällig wie man das auch anders lösen könnte?
Gruß
Jenny

Matrixformel
15.06.2016 15:10:11
Michael
Hi Jenny,
also, Du hast geschrieben, daß Du umstrukturiert hast. Gesucht wird nur noch in Spalte E - wird das Ergebnis dann auch aus E übernommen?
Mangels einer neuen Datei habe ich die alte verwendet und bin bei D geblieben; in der Formel muß dann nur jeweils D durch E ersetzt werden.
Es handelt sich bei der mit {} um eine sogenannte Matrixformel; die {} werden nicht händisch eingegeben, sondern erscheinen automatisch, wenn man die Formel mit Strg+Umschalt+Enter abschließt.
Näheres dazu u.a. hier: http://www.online-excel.de/excel/singsel.php?f=26
In F101 die Matrixformel:
{=MAX(($A$1:$A$100=A101)*($D$1:$D$100=E101)*ZEILE($A$1:$A$100))}
hier ^    ^ ggf. durch E ersetzen...
in D101 eine normale Formel:
=WENN(F1010;INDEX($D$1:$D$100;F101);"n.v.")
hier ^    ^ ggf. durch E ersetzen...
Falls Du nicht zurechtkommen solltest, lad bitte nochmal eine Datei mit der aktuellen Struktur hoch.
Und nochmal die Frage: wieviele Daten UNTERHALB sollen verformelt werden? 10? 100? 1000? 10000? Dann würde ich doch VBA vorziehen.
Schöne Grüße,
Michael

Anzeige
AW: Matrixformel
15.06.2016 15:36:54
Jenny
Hallo Michael,
ich fange mal von hinten an.
Ich habe nur in ein paar Zeilen den Inhalt von D und E vertauscht, damit in D nicht mehr gesucht werden muss. Die Struktur der Tabelle ist nach wie vor dieselbe.
Ich beziehe mich jetzt auf meeine ursprüngliche Tabelle,
habe jetzt an der alten Bsp. Mappe Zeile 102 getestet
{=MAX(($A$1:$A$100=A102)*($E$1:$E$100=E102)*ZEILE($A$1:$A$100))} und
=WENN(F1020;INDEX($D$1:$D$100;F102);"n.v.")
funktioniert, wie ich es wollte.
Zu dem Umfang der Tabelle, da werden schätze ich mal irgendwann 40-50Tsd Zeilen zusammen kommen, habe vor, immer wenn ich quasi ein Kaptitel abgeschlossen habe, so wie jetzt mit den ersten 6526 Zeilen, das abgeschlossene Kapitel dem ersten Teil hinzuzufügen, bzw. dementsprechend deine Formel abzuändern.
Wenn du jetzt fragst weshalb das ganze, der Inhalt von Spalte A und E ist einfach kopiert, was in Spalte D steht ist (in den Zeilen in denen auch was in E steht) mühsam gegooglet, ich will einfach erkennen, hey das hast du doch schonmal gegooglet kann also von früher übernommen werden, brauchst dir die Mühe nicht nochmal machen.
Verstehst du?
Gruß
Jenny

Anzeige
AW: Matrixformel
15.06.2016 17:45:41
Michael
Hi Jenny,
so in etwa, aber ich mag mich jetzt nicht tiefer eindenken (wenn ich nicht bald abspüle, krieg ich Ärger).
Nur so viel:
Excel wird bei sehr vielen Formeln irgendwann relativ langsam. Wenn Du so Sachen kapitelweise machst, kannst Du ja den oberen Bereich "entformeln", indem Du die Spalte mit Kopieren/Inhalte einfügen-Werte durch Werte ersetzt. Dann wird das auch ohne Makro sinnvoll zu handhaben sein.
(ach ja, immer wenn ich einen wesentlichen Teil ändere, speichere ich die Datei unter neuem Datum ab, z.B. Gugel-Links.xlsx auf Gugel-Links-20160615.xlsx)
Hin und wieder könnte es sinnvoll sein, Daten/Duplikate entfernen auszuführen, je nach Daten, im Beispiel waren halt viele Mehrfache...
Gutes Gelingen,
Michael

Anzeige
AW: Matrixformel
15.06.2016 20:18:41
Jenny
Hallo Michael,
ich denke am einfachsten ist, ich mache es wenn schon Makro gar nicht kapitelweise.
Ich schreibe deine Formel in Zeile 1, mache ein Makro, dass die Formel von D1 nach Dletzte Zeile kopiert, sobald ich etwas in Eletzte Zeile schreibe.
Und dann in Dletzte Zeile den Wert einfügt.
Sowas ist doch sicherlich machbar und spart denke ich die meiste Zeit, da immer nur eine Formel berechnet werden muss.
Gruß
Jenny

AW: Matrixformel
16.06.2016 00:40:34
Michael
Hi Jenny,
klar, das geht sicher auch - ´gute Idee: simpel & wirkungsvoll.
Ich habe Deine Kenntnisse wohl unterschätzt, aber umso besser.
Happy Exceling,
Michael

Anzeige
AW: Matrixformel
16.06.2016 08:45:17
Jenny
Hallo Michael,
zum unterschätzen, ich denke das ist ein zweischneidiges Schwert, nur weil man weiß dass das so gehen kann heißt das noch lange nicht das man es auch programmieren kann... Da hakt es dann nun.
LG
Jenny

VBA
16.06.2016 10:48:47
Michael
Hi Jenny,
das klang so, als könntest Du das selbst...
Ich habe Dir mal ein Makro geschrieben, das dann aber doch anders arbeitet: die Überprüfung auf die unterste Zeile schien mir hier nicht zielführend, denn die ist bei rein händischer Eingabe ok, aber wenn Du doch mal mehrere Zeilen reinkopierst, wird's hakelig.
Jetzt funktioniert es so: Du markierst den Bereich in Spalte D, der bearbeitet werden soll und drückst Strg+Umschalt+L, dann werden alle Werte für diesen Bereich gesucht (auch, wenn schon welche drinstehen), und zwar jeweils von der Zeile über der gerade bearbeiteten Zelle aus nach oben.
Also: Du markierst z.B. D101:D105, dann sucht das Makro für D101 in Zeile 1:100; für D102 in Zeile 1:101 usw.
Ich denke, das ist variabler einsetzbar.
Die Datei: https://www.herber.de/bbs/user/106279.xlsm
Schöne Grüße,
Michael

Anzeige
AW: VBA
16.06.2016 11:02:25
Jenny
Hallo Michael,
funktioniert natürlich, aber ich denke trotzdem mein Ansatz ist praktikabler.
Ich hatte ja gesagt die Recherche im Internet die durch das Makro erleichtert werden soll, hab ich nur in den zeilen gemacht, in denen sowohl etwas in D als auch E steht.
Das heißt ich brauche auch zukünftig das Makro nur für solche Zeilen. In den anderen Zeilen schreibe ich einfach was in Spalte D und lasse E aus. Daher meine Idee das das ganze durch einen Eintrag in Spalte E ausgelöst werden soll.
Dass ich mal mehrere Zeilen auf einmal kopiere kann zwar sein, aber dann nur von einer Stelle der Tabelle an eine andere, also hab ich in diesem Fall schon alles was ich brauche und das Makro muss nicht aktiv werden.
LG
Jenny

Anzeige
AW: VBA
16.06.2016 13:24:54
Michael
Hi Jenny,
kann sein, daß ich heute etwas begriffsstutzig bin...
Kopiere den Code bitte mal mit rein:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, z&, cc As Range, zf&
Dim gefunden As Boolean
If Target.Column = 5 And Target.Count = 1 Then
If Target  "" And Target.Offset(, -4)  "" Then
z = Target.Row
gefunden = False
Set cc = Range("A1:A" & z - 1).Find(Target.Offset(, -4).Value, _
Range("A1"), xlValues, xlWhole)
If Not cc Is Nothing Then
zf = cc.Row
Do
Set cc = Range("A1:A" & z - 1).FindNext(cc)
If cc.Offset(, 4) = Target Then
Target.Offset(, -1) = cc.Offset(, 3)
gefunden = True
End If
Loop Until cc Is Nothing Or cc.Row = zf Or gefunden
End If
If Not gefunden Then Target.Offset(, -1).Value = "n.v."
End If
End If
End Sub
Wenn Du in E was eingibst/änderst, wird das Ergebnis in D geschrieben.
Schöne Grüße,
Michael

Anzeige
AW: VBA
16.06.2016 13:44:35
Jenny
Hallo Michael,
ich wollte eigentlich ein Makro dass dasselbe macht wie deins, nur dass ich nicht einen Bereich vorher markiere, sondern nur die letzte Zeile genommen wird, und als Äuslöser nicht den Shortcut sondern eine Eingabe von mir in Spalte E in der letzten Zeile.
Teste dann jetzt mal was du da programmiert hast....
So wie ich es sehe, funktioniert das in jeder Zeile, nicht nur in der letzten. Weiß zwar noch nicht ob ich das brauchen werde, aber kann ja nicht schaden. Nehme es dann so. Danke für die Mühe
Jenny

gerne
17.06.2016 12:29:43
Michael
Hi Jenny,
der Punkt ist, daß das mit der letzten Zeile mehr Programmierung erfordert...
Naja, ich war gestern wirklich etwas "langsam", das ist das zweite If..then zusätzlich:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, z&, cc As Range, zf&
Dim gefunden As Boolean
If Target.Column = 5 And Target.Count = 1 Then
If Target.Row = Range("E" & Rows.Count).End(xlUp).Row Then ' ***
If Target  "" And Target.Offset(, -4)  "" Then
z = Target.Row
gefunden = False
Set cc = Range("A1:A" & z - 1).Find(Target.Offset(, -4).Value, _
Range("A1"), xlValues, xlWhole)
If Not cc Is Nothing Then
zf = cc.Row
Do
Set cc = Range("A1:A" & z - 1).FindNext(cc)
If cc.Offset(, 4) = Target Then
Target.Offset(, -1) = cc.Offset(, 3)
gefunden = True
End If
Loop Until cc Is Nothing Or cc.Row = zf Or gefunden
End If
If Not gefunden Then Target.Offset(, -1).Value = "n.v."
End If
End If                                 ' ***
End If
End Sub
Aber genau genommen: wozu?
Happy Exceling,
Michael

Anzeige
AW: gerne
17.06.2016 18:25:23
Jenny
Hallo Michael,
sagte doch ich nehme es so wie es war. Bin leider übers Wochenende bis Montag auf Familienbesuch, da werde ich nicht wirklich testen können.
ABer trotzdem danke nochmal für die Mühe.
Gruß
Jenny

299 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige