Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
25.04.2024 21:06:05
Duplikate entfernen
25.04.2024 18:41:19
vba Zeilen duplizieren ab aktiver Zeile bis letzte Zeile
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1632to1636
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1632to1636
1632to1636
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formeln kopieren (schnellster Weg)

Formeln kopieren (schnellster Weg)
21.07.2018 18:42:59
Christian
Hallo an euch alle,
wollte mal Profis fragen, was für das was ich vorhabe der schnellste weg ist, klar ich weiß ich kann alles händig machen, mir Hilfsspalten zur Hilfe nehmen aber ich wollte mal fragen ob es auch eine Möglichkeit mit geringerem Aufwand gibt.
Habe eine Tabelle mit ca. 12000 Zeilen und 7 Spalten, alles Text.
Mit Ausnahme der Zellen B1, C1, E1, F1, G1 und H1.
In diesen stehen Formeln.
Ich möchte erstmal nichts weiter machen, als diese 6 Formeln bis ans Ende der Liste kopieren und ab Zeile 2 die Werte einzufügen damit nachher wieder nur die 6 ursprünglichen Zellen mit Formeln da sind.
Hintergrund ist zu lange Berechnungszeit bei 120000 Formeln, auf diesem Weg werden sie nur bei Bedarf neu berechnet.
Eine Sache kommt erschwerend hinzu (betrifft nur Spalte F)
Es soll keine Zelle mit Inhalt durch eine leerzelle ersetzt werden.
Mit anderen Worten, in F1 steht die Formel
=WENN(SVERWEIS(D1;'Personen Archiv'!A:C;3;FALSCH)="";"";SVERWEIS(D1;'Personen Archiv'!A:C;3;FALSCH))
Anstatt "" auszugeben, soll der vorherige Inhalt der Zelle, also der bevor die Formel kopiert wurde, beibehalten werden.
Weiß da jemand Rat?
Danke
Christian

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

Betreff
Datum
Anwender
Anzeige
AW: VBA?
21.07.2018 19:22:37
Fennek
Hallo,
nach der Beschreibung sollte es mit VBA lösbar sein. Wäre das ein akzeptabler Weg?
mfg
AW: VBA?
21.07.2018 19:40:56
Christian
Hallo Fennek,
jein, bei einer VBA Lösung käme noch eine Problemstellung dazu, ich plane eventuell in Zukunft noch weitere Spalten einzugfügen, mit denen dasselbe getan werden soll. Mit Formellösungen traue ich mir das von allein zu, bei einer VBA Lösung bräuchte ich eine Erklärung von dir wie ich das Makro anpassen muss oder ich bräuchte erneut deine Hilfe.
Aber wenn du dazu bereit bist, nehme ich auch VBA.
Gruß
Christian
AW: VBA?
21.07.2018 20:20:29
Daniel
HI
mal abgesehen von dem Spalte F-Problem:
die Aufgabe erledigt sich doch mit ein paar Klicks:
1. Markiere B1:C1 und Doppelklickt auf die rechte untere Ecke des Markierungsrahmens.
die Formel wird dann für beide Spalten soweit nach unten ausgefüllt, wie die Tabelle Daten hat.
2. Gleiches Für die Zellen E1:H1
3. markiere die Zelle B2 und drücke STRG+SHIFT+ENDE. das markiert deine Tabelle ab Zeile 2
4. führe dann mit der Markierung ein Kopieren und Inhalte einfügen Werte aus.
dh wenn man es geschickt macht, kaum ein aufwand.
das Spalte F-Problem würde ich mit zwei zusätzlichen Spalte lösen:
in die erste Spalte (ich nehme mal G) kommt die Formel:
=SVERWEIS(D1;'Personen Archiv'!A:C;3;FALSCH)
in die zweite Spalte dann:
=Wenn(G1="";F1;G1)
und diese zweite Spalte gilt dann.
Gruß Daniel
Anzeige
AW: VBA?
21.07.2018 20:39:29
Christian
Hallo Daniel,
danke für deine Erklärung. Vor allem danke für den Tipp mit dem Doppelklick rechts unten, das habe ich noch nicht gewusst.
aber das was du da sagst mit den Hilfsspalten ist ja das was ich mir zukünftig sparen wollte.
Da stecken recht zeitintensive Formeln hinter, sprich wenn ich das alles was ich beschrieben habe von Hand mache, brauchts ca. ne Viertel Stunde.
Wenn sich das vereinfachen ließe bräuchte ich nicht die ganze Zeit drauf zu achten, wann der nächste Schritt ansteht und kann in der Zeit schonmal an anderen Stellen weiterarbeiten.
Gruß
Christian
Anzeige
AW: VBA?
21.07.2018 21:50:26
Daniel
Hi
momentan gehts ja nur um den SVerweis bzw die Formel:
=WENN(SVERWEIS(D1;'Personen Archiv'!A:C;3;FALSCH)="";"";SVERWEIS(D1;'Personen Archiv'!A:C;3;FALSCH))
mit der Hilfsspalte reduzierst du dir sogar den Rechenaufwand, weil du hier in der Formel den gleichen SVerweis 2x drin hast, in der Hilfspaltenlösung aber nur 1x.
aber es reicht natürlich auch nur eine weitere Spalte mit:
=WENN(SVERWEIS(D1;'Personen Archiv'!A:C;3;FALSCH)="";F1;SVERWEIS(D1;'Personen Archiv'!A:C;3;FALSCH))
und das Problem ist ja, wenn du in Spalte F schon Daten drin stehen hast die du gegebenenfalls noch brauchst, darfst du diese nicht überschreiben. Ob du dieses noch brauchst oder nicht, weist du ja erst wenn du den SVerweis ausgeführt hast.
Daher: die Zusätzliche Spalte muss sein.
wenn hier Rechenaufwand erzeugt wird, dann durch den SVerweis mit 4. Parameter = Falsch, wenn die Suchspalte sehr lang ist.
hier kann kann man den Rechenaufwand bei großen Datenmengen drastisch reduzieren, indem du:
1. die Suchmatrix 'Personen Archiv'!A:C nach Spalte A aufsteigend sortierst
2. den SVerweis mit 4. Parameter = WAHR verwendest.
aufgrund der Sortierung wird dann eine deutlich effizienter Suchmethode verwendet.
Du kannst die Zeitersparnis in etwas so abschätzen:
- die Datenmenge ist ungefahr x = 2^n
dann ist der Suchaufwand für den SVerweis mit 4. Parameter = Falsch proportional dem x
beim SVerweis mit 4. Parameter = Wahr proportional n
allerdings bekommst du dann, wenn der Suchbegriff nicht gefunden wird, keinen #NV-fehler, sondern den nächstkleineren Wert als Ergebnis.
Wenn das ein Problem ist und du eine Rückmeldung bei Nichtvorhandensein des Suchwerts haben willst, dann schalte folgende Prüfung vor: 
=Wenn(SVerweis(D1;'Personen Archiv'!A:A;1;wahr)=D1;SVerweis(D1;'Personen Archiv'!A:C;3;Wahr);"fehlt")
Gruß Daniel
Anzeige
AW: VBA?
21.07.2018 22:05:23
Christian
Hallo Daniel,
erstmal eine Frage am Rande. Ich dachte WAHR bedeutet ungefähre Übereinstimmung,
es gibt Texte in Spalte D die unterscheiden sich nur durch einen Buchstaben, daher habe ich FALSCH als Parameter gewählt.
Außerdem dachte ich der wirkliche Zeitfresser ist die Spalte H mit der Formel
=WENN(C1="";"";RANG(C1;G$1:G1;0)) über 12000 Zeilen, da es 12000 unterschiedliche Zellbereiche sind.
Daher war meine ursprünglihe Überlegung halt wirklich möglichst wenige Schritte die zu tun sind, damit ich mich solang anderen Aufgaben widmen kann. Ganz so schlecht fand ich da Fenneks Idee mit dem Makro nicht.
Gruß
Christian
Anzeige
AW: VBA?
21.07.2018 22:26:29
Daniel
1. Das problem mit Spalte H wird sich nicht ändern, wenn du für Spalte F eine zusätzliche Hilfsspalte einfügst!
2. Ein Makro wird auch nichts anderes machen, als die von mir beschriebenen Schritte ausführen.
dh die reine Rechenzeit bleibt die gleiche, es entfällt lediglich die Zeit, in welcher du die Maus auf dem Bildschirm verschiebst.
Das einzige was du noch machen könntest, wäre die Formeln im Makro zu hinterlegen, so dass es nicht notwendig ist, sie in Zeile 1 zu erhalten.
3. das mit der Ungefähren Übereinstimmung ist in der Hilfe unglücklich beschrieben.
der SVerweis mit 4. Parameter = Wahr funktioniert so:
wird der Suchbegriff nicht gefunden, wird der nächstkleinere Wert in der Liste als Ergebnis verwendet.
Gruß Daniel
Anzeige
AW: VBA?
22.07.2018 09:04:09
Christian
Hallo Daniel,
der Vorteil des Makros ist nunmal, ich kann in der Zeit was anderes machen, z.B. kochen, spazieren gehen, Mittagspause, was auch immer und wenn ich wiederkomme ist es fertig. Mein Hauptziel weshalb ich gepostet habe ist den Arbeitsaufwand zu verringern, das zeitlich nicht viel zu ändern ist, hast du recht, da das Makro wie du sagst nichts anderes macht als ich von Hand.
Aber noch eine Frage zu dem was du zum SVERWEIS schreibst, nächstkleinere Wert leuchtet mir bei Zahlen ein, in meinem Fall stehen aber Texte.
Gruß
Christian
AW: VBA?
22.07.2018 18:19:53
Christian
jetzt bekomme ich keine Antwort mehr?
Anzeige
AW: VBA?
22.07.2018 18:26:10
Daniel
Hi
was bist du so ungeduldig?
hier antwortet jeder freiwillig und in seiner Freizeit.
Es ist Sommer und Sonntag. Also locker bleiben.
Wenn du es eilig hast, musst du dir einen Profi nehmen.
aber ich bin ja nicht so, außerdem regnet es grad bei uns.
hier mal ein Code, der die Formeln aus Zeile 1 nach unten kopiert und danach ab Zeile 2 dann wieder die Formeln durch die neuen Ergebnisse ersetzt.
Sub test()
Dim Zelle As Range
With ActiveSheet.UsedRange
For Each Zelle In .Rows(1).SpecialCells(xlCellTypeFormulas)
Zelle.Copy
With .Columns(Zelle.Column).Resize(.Rows.Count - 1, 1).Offset(1, 0)
.PasteSpecial xlPasteFormulas
.Copy
.PasteSpecial xlPasteValues
End With
Next
End With
End Sub
Gruß Daniel
Anzeige
AW: VBA?
22.07.2018 19:42:57
Christian
Hallo Daniel,
erstmal zu dem Makro,
ich verstehe richtig, dass das Makro alle Formeln nimmt, unabhängig davon in welchen Zellen sie stehen und auch wieviele es sind. Sprich ich brauch es weder zu ändern, wenn Spalten hinzukommen (egal ob mit oder ohne Formel) als auch wenn Zeilen hinzukommen?
Das würde ja heißen, ich könnte deine Hilfsspalten die du vorgeschlagen hast auch einbauen und müsste dann zum Abschluss nur den Inhalt der 2. Hilfsspalte nach Spalte F kopieren.
Zu der Ungeduld, ja du hast Recht, Geduld war noch nie meine Stärke, ich hätte länger warten sollen, ansatt euch freiwillige Helfer unter Druck zu setzen und ihnen zu unterstellen, die Hilfe zu verweigern.
Das war nicht ok von mir, es tut mir leid.
Gruß
Christian
Anzeige
probierst doch aus, dann
22.07.2018 19:49:00
Daniel
siehst du was das Makro macht.
Gruß Daniel
AW: VBA?
27.07.2018 22:01:53
Christian
Hallo Daniel,
sorry lag krank im Bett, dass ich mich jetzt erst wieder melde. Ich bin überrascht, wie schnell es geht.
Jedoch das Spalte F Problem besteht nach wie vor.
Gruß
Christian
AW: Formeln kopieren (schnellster Weg)
21.07.2018 19:25:08
Barbaraa
Hi Christian,
wenn ich das richtig verstehe:
Du willst einen Inhalt, den Du mit einer Formel überschrieben hat, als Formelergebnis wieder gewinnen.
Dann sollte dieser Wert erst mal gerettet werden, bevor Du ihn überschreibst. Vielleicht in eine neue Spalte, die Du dafür anlegst.
Nebenbei, eine Beispieldatei von Dir erhöht die Chance auf brauchbare Hilfe.
LG,
Barbara
Anzeige
AW: Formeln kopieren (schnellster Weg)
21.07.2018 19:44:58
Christian
Hallo Barbara,
bin am verhungern. Werde mich nach dem Essen an eine brauchbare Bsp Datei begeben.
Aber du hast mich richtig verstanden, ich will dass der vorherige Zellinhalt nicht überschrieben wird, wenn die Formel "" ausgeben würde.
Das hatte ich ja zu Beginn mit "klar ich weiß ich kann alles händig machen und mir Hilfsspalten zur Hilfe nehmen", das ist ja auch mein bisheriger Weg, genauso wie ich natürlich von Hand 6 Formeln kopieren kann und die Werte einfügen kann.
Meine Frage war ja ob es einen Weg gibt, das ganze einfacher zu gestalten, also weniger Aufwand für mich als Anwender.
Gruß
Christian
AW: Formeln kopieren (schnellster Weg)
23.07.2018 09:26:11
Rob
Beispieldatei wäre sehr schön. Man kann Berechungen in Arrays blitzschnell durchführen - weshalb sollte das nicht auch mit Formeln gehen? Einen Versuch wäre es wert.

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige