Office: Formeln richtig kopieren

Hallo alle zusammen!

Ich habe folgende Frage:
Ich habe eine Tabelle mit ca. 40.000 Zeilen, welche stündlich aktualisiert wird und dadurch immer neue Zeilen dazu kommen. Wie gebe ich nun Formeln richtig ein ?
Wenn ich die Spalte markiere und dann die Formel kopiere, habe ich dann in den Zeilen, die keine Werte erhalten so komische Zeichen ( außerdem wird die Tabelle dadurch sehr umfangreich und mein Rechner kommt dann mit dem Rechnen nicht hinterher ). Wenn ich aber die Formel nur soweit kopiere wie Daten vorhanden sind, habe ich nach der Aktualisierung ja keine Daten in der Spalte mit der Formel.

Was muss ich machen, damit Daten in einem 2.Arbeitsblatt auch ständig aktualisiert werden, wenn neue Daten im 1. Arbeitsblatt ankommen ?

Vorab schon mal danke !

Gruß
derumuro

 

Hallo derumuro -

Du müßtest uns schon bitte verraten, um was für "komische Zeichen" es sich handelt; außerdem wäre es schön zu wissen, wie die Formel lautet, die Du kopieren möchtest.

Zu Deiner zweiten Frage: Die einfachste Formel, die ein zweites Tabellenblatt mit dem ersten synchronisiert, ist

• =Tabelle1!B1

oder etwas in der Art.

 

Hallo!

Die "komischen Zeichen" könntest du mit einem Fehlerabfang umgehen, z.B.
=WENN(A100="";"";DeineFormel)
Dann kannst du deine Formeln ausreichend weit herunterziehen.

(Dass dein Rechner nicht mehr hinterher kommt, kann ich mir schwer vorstellen, denn schliesslich muss er doch auch hinterher kommen, wenn deine Tabelle voll ist.)

Um die Formeln NICHT händisch eintragen zu müssen, bräuchtest du VBA, z.B. ein Worksheet_Change-Ereignis, welches immer dann aktiviert wird, wenn im Blatt (oder einem Bereich) eine Änderung auftritt.

Um Tabellenblätter zu verknüpfen, kannst du z.B. Formeln dieser Art verwenden:
=Tabelle1!A1
Ansonsten bleibt auch hier nur der Weg über VBA.

edit: Krino war schneller...

 

Hallo !

Danke für Eure Antworten !

Zu Krino

In der Zelle mit der Formel steht der Ausdruck #WERT!, wenn die Zelle auf die sich die Formel leer ist.
Die Formel lautet =DATUM(LINKS($C39134;4);TEIL($C39134;5;2);RECHTS($C39134;2)) und macht z.B. aus der Zahl 20070529 das Datum. Ist es für das Kopieren wichtig, wie die Formel lautet ?

Zu Schatzi

=WENN(A100="";"";DeineFormel) kannst Du mir kurz in Worten erklären, was die Formel aussagt ?
Ich weiß nicht, ob es an meiner Tabelle oder am Rechner liegt, dass es so langsam geht.
Ich habe nur geringe Kenntnisse in Excel, deshalb können wir das Thema VBA wahrscheinlich erst einmal vergessen.
Ich hätte vielleicht eine Idee, wie man das mit der Formel in der letzten Zeile machen könnte.
Angenommen in A10 würde ein Wert stehen. Kann ich jetzt prüfen, ob der Nachfolger von A10, also A11 nicht leer ist ? Wenn A11 nicht leer ist soll die Formel von B10 nach B11 kopiert werden ? Wenn das möglich wäre, wie würde die Formel lauten ?
=Tabelle1!A1 ( Krino schreibt in der Formel für A1=B1 ) kannst Du mir die Formel auch noch mal kurz in Worten erklären ( wenn meine Tabelle1 z.B. "Import" heißt, muss ich das in die Formel eingeben ?)


Nochmals Danke Euch Beiden !

Gruß
derumuro

 

Fehler abfangen

Hallo derumuro -

Es ist egal wie die Formel lautet; es geht ums Fehlerabfangen. Auf deutsch: Bei Bezug auf leere Zellen will man nicht irgendwelche Hieroglyphen, sondern eine leere Zelle.

Du markierst also die zu behandelnde Zelle und setzt in der Bearbeitungszeile den Cursor hinter das Gleich-Zeichen. Dann tippst Du

• wenn(A1="";"";

setzt dann den Cursor hinter das Ganze und machst noch eine Klammer zu und ENTER. Bei Dir wird es nicht A1 sein, sondern irgend eine andere Zelle, die, wenn leer, zu den Hieroglyphen führt. Besser ist es, bei der Eingabe nicht die Zelladresse zu tippen, sondern auf die Zelle zu klicken, das vermeidet Tippfehler.

 

Genau wie Krino schon schreibt:

=WENN(A1="";"";DeineFormel)
überprüft, ob in A1 etwas steht. Falls A1 leer ist, dann bleibt die Zelle ebenfalls leer; andernfalls wird deine Formel ausgeführt.

Wenn dein Blatt "Import" heisst, dann eben der Verweis
=Import!A1

 

Hallo Ihr Zwei,

ich habe die Formel =WENN(A1="";""; ausprobiert und "#Wert!" ist jetzt bei leeren Zellen weg. Dafür dauert das Öffnen der Datei sehr lange ( habe Formel in ganze Spalte kopiert )!
Was haltet Ihr eigentlich von meiner Idee ? Wenn das so funktionieren würde, wie ich es mir denke, müsste Excel nicht alle möglichen Zeilen abfragen und es würde vielleicht wieder schneller gehen ?

Andere Frage: Beim Aktualisieren der Tabelle aus der externen Datei kommt von Microsoft immer ein Sicherheitshinweis ( Dateien können gefährlichen Inhalt haben usw. ) Hat einer von Euch eine Idee, wie ich den Hinweis ausschalten kann ?

Gruß
derumuro

 

Hallo derumuro -

Ich kann mir nicht vorstellen, daß die Startverzögerung mit den neuen Formeln zu tun hat. Die Abfrage, ob eine Zelle leer ist oder nicht, dürfte so rasend schnell gehen, daß man das nur mit empfindlichen Instrumenten würde messen können.

Dann willst Du wissen, was ich von der Idee halte - meinst Du diese?

Aber das ist genau das Verfahren, von dem wir hier reden!

Der Sicherheitshinweis ist ja an sich schon sinnvoll; möglicherweise kannst Du das beeinflussen, indem Du nachschaust, was bei Dir unter EXTRAS > MAKRO > SICHERHHEIT eingetragen ist.

Edit: Logikfehler. Das Nachschauen reicht nicht, man muß auch die Einstellung ändern.

 

Hallo Kroni,

wie ich schon geschrieben hatte, habe ich Eure Formel in die ganze Spalte kopiert. Danach dauert alles ( z.B. starten oder aktualisieren ) sehr lange. Nachdem ich den Inhalt der Spalte wieder gelöscht hatte ging alles wieder so schnell wie vorher. Ich habe als Prozessor einen Athlon 3200+ und 2Gb Ram. Ich denke mal, dass das ausreichen müßte.

Die Sicherheitsabfrage habe ich auch weg bekommen.

Danke !

Gruß
derumuro

 

Hallo!

Dann könnten wir nur noch den Weg über VBA gehen.
In welche Spalte werden denn deine Werte importiert, und in welche Spalte möchtest du diese als Datum konvertiert haben?

 

Hallo,

die Tabelle beginnt in der zweiten Zeile. Die importierten Daten für das Datum stehen in Spalte C (z.B. 20070531). Diese sollen in Spalte K als Datum erscheinen
Formel: =WENN(C2="";"";DATUM(LINKS($C2;4);TEIL($C2;5;2);RECHTS($C2;2)))
Für eine spätere Sortierung sollte in den Spalten L&M der Monat und der Wochentag als Text stehen. Das nächste Problem wäre die Uhrzeit. die Werte dafür stehen in Spalte D ( z.B. 220000 für 22 Uhr ). Daraus soll in Spalte N dann die Uhrzeit werden
Formel: =ZEITWERT(LINKS(TEXT($D2;"000000");2)&":"&TEIL(TEXT($D2;"000000")
;3;2)&":"&TEIL(TEXT($D2;"000000");5;2)) .

Ich hatte die Tabelle bei meiner ersten Frage nicht so ausführlich beschrieben, weil ich gehofft hatte, dass es für mein Problem eine relativ einfache Lösung geben könnte. Falls ein Makro dafür aber zu aufwendig werden würde, wäre es nicht schlimm, wenn Du es nicht schreiben würdest .
javascript:emoticon(':cry:')
Crying or Very

Gruß
derumuro

 

Hallo!

Dann kann ich dir versuchsweise dies hier anbieten:
(Am besten erstmal an einer Kopie der Datei testen!!!)
Vorbereitend:
Lösche alle Formeln aus den Spalten K bis N
Formatiere Spalte K im gewünschten Datumsformat
Formatiere Spalte L im benutzerdefinierten Format: MMMM (für "Januar") oder MMM (für "Jan")
Formatiere Spalte M im benutzerdefinierten Format: TTTT (für "Sonntag") oder TTT (für "So")
Formatiere Spalte N im gewünschten Zeitformat

Öffne nun mit Alt+F11 den VBA-Editor
Doppelklicke im linken Fenster auf das entsprechende Tabellenblatt
Kopiere diesen Code ins rechte Code-Fenster

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Zelle As Range
On Error Resume Next
For Each Zelle In Target
If Not Intersect(Range("C2:C65536"), Zelle) Is Nothing Then
If Zelle <> "" And IsNumeric(Zelle) Then
Range(Zelle.Offset(0, 8), Zelle.Offset(0, 10)) = CDate(Right(Zelle, 2) & "." & _
Mid(Zelle, 5, 2) & "." & Left(Zelle, 4))
Else
Range(Zelle.Offset(0, 8), Zelle.Offset(0, 10)) = ""
End If
End If
If Not Intersect(Range("D2:D65536"), Zelle) Is Nothing Then
If Zelle <> "" And IsNumeric(Zelle) Then
Zelle.Offset(0, 10) = TimeValue(Left(Zelle, 2) & ":" & _
Mid(Zelle, 3, 2) & ":" & Right(Zelle, 2))
Else
Zelle.Offset(0, 10) = ""
End If
End If
Next Zelle
End Sub

Wenn jetzt in Spalte C und/oder D Werte eingetragen werden, erscheinen sie umgewandelt in den Spalten K bis N.
Werden Werte aus Spalte C und/oder D gelöscht, so verschwinden sie auch aus den Spalten K bis N.

Das sollte deine Tabelle wieder deutlich beschleunigen.

Beachte: Wenn in Spalte C und/oder D Werte eingetragen werden, die NICHT in dem von dir beschriebenen Format vorliegen, kann es zu unsinnigen Ausgabewerten kommen, aber das dürfte bei der Formelvariante ja auch so gewesen sein...

Würde mich freuen, wenn es zu deiner Zufriedenheit funktioniert.

 

Hallo,


danke für das Makro !
Ich habe es auch gleich ausprobiert! Das Starten der Tabelle geht jetzt wieder recht schnell. Leider habe ich aber auch einige Probleme, die da wären:
- bei der Uhrzeit sind Lücken vorhanden 23 leer 10 ( sollte eigentlich 1 Uhr sein) 20 ( sollte 2 Uhr sein ) sieben mal leer dann 10-23 Uhr wieder da und dann wiederholt sich alles. Ich habe gerade gesehen, dass das Programm aus der Zahl 10000 10 Uhr macht, aber es müsste eigentlich 1 Uhr werden ( 10 Uhr wäre die Zahl 100000 ). Habe ich Dir dazu zu wenige Infos gegeben ?
- wenn ich den Monat und den Tag nur durch formatieren des Datums bekomme, habe ich Probleme beim Filtern, da dann in der Abfrage bei Beiden immer die Jahreszahl steht. Kann man das nicht in Text formatieren z.B. mit der Formel =TEXT(WOCHENTAG....... ?

Gruß
derumuro

P.S. wie füge ich Smilies richtig ein ?

 

Hallo!

Dann teste mal dies:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Zelle As Range
Dim Zeit As String
On Error Resume Next
For Each Zelle In Target
If Not Intersect(Range("C2:C65536"), Zelle) Is Nothing Then
If Zelle <> "" And IsNumeric(Zelle) Then
Zelle.Offset(0, 8) = CDate(Right(Zelle, 2) & "." & _
Mid(Zelle, 5, 2) & "." & Left(Zelle, 4))
Zelle.Offset(0, 9) = MonthName(Month(Zelle.Offset(0, 8)))
Zelle.Offset(0, 10) = WeekdayName(Weekday(Zelle.Offset(0, 8), vbMonday))
Else
Range(Zelle.Offset(0, 8), Zelle.Offset(0, 10)) = ""
End If
End If
If Not Intersect(Range("D2:D65536"), Zelle) Is Nothing Then
If Zelle <> "" And IsNumeric(Zelle) Then
Zeit = Application.WorksheetFunction.Rept("0", 6 - Len(Zelle)) & Zelle
Zelle.Offset(0, 10) = TimeValue(Left(Zeit, 2) & ":" & _
Mid(Zeit, 3, 2) & ":" & Right(Zeit, 2))
Else
Zelle.Offset(0, 10) = ""
End If
End If
Next Zelle
End Sub

 

Hallo Schatzi,

Dein Programm funktioniert jetzt einwandfrei ! Danke!
Der erste Teil meines Projektes ist jetzt fertig.
Hättest Du noch Lust, mir beim zweiten Teil zu helfen ?

Gruß
derumuro