Excel WENN-Formel, neue Zeile einfügen.

[BallerNacken]

Hi Leute,

ich stehe mal wieder vor einem Excelproblem. Und zwar habe ich eine Spalte mit Uhrzeiten in der Formatierung hh:mm:ss in knapp 45000 Zeilen. Nun möchte ich, jedes mal wenn die Differenz zwischen zwei aufeinanderfolgenden Zellen größer als 30 min ist eine Leerzeile zwischen diesen beiden Werten einfügen. Um das Ganze vielleicht noch etwas klarer darzustellen ein Minimalbeispiel:

A1 04:02:00
A2 03:59:00
A3 03:24:00
A4 03:23:00

In dem Beispiel werden die 30 Minuten also zwischen A2 und A3 überschritten. Das Ergebnis sollte dann wie folgt aussehen:

A1 04:02:00
A2 03:59:00
A3 LEERZEILE
A4 03:24:00
A5 03:23:00

Das dann noch auf die komplette Spalte anwendbar natürlich. Würde halt in etwa sowas nehmen:

=WENN((A1-A2)>30;Füge neue Zeile ein)

Vielleicht kann mir da ja einer von euch weiterhelfen?!

Viele Grüße,
BallerNacken

 

[Fellor]

Also mir würde jetzt ganz spontan ein VBA-Makro in den Sinn kommen.

 

[Momii]

Als Workaround würde auch eine neue Spalte neben deinen Uhrzeiten gehen. Mit einer Wenn-Abfrage in jeder zweiten Zelle

 

[BallerNacken]

Von VBA habe ich leider wenig bis gar keine Ahnung. Wenn das zu komplex ist, mache ich es lieber mit MATLAB (habe ich wenigstens ein wenig Ahnung). Würde immer noch dauern, aber schneller als mich in VBA einzulesen ist es allemal^^

Ergänzung (2. Oktober 2014)

Als Workaround würde auch eine neue Spalte neben deinen Uhrzeiten gehen. Mit einer Wenn-Abfrage in jeder zweiten Zelle

Das fügt mir aber ja keine neue Zeile ein. Oder verstehe ich dich nicht ganz dabei?

Ergänzung (2. Oktober 2014)

Okay versuche ich mich mal mit meinem kaum vorhandenen VBA Wissen^^

Sub Spalte_einf()
i = 2
Do
If Cells(i, i+1) > 30 Then
Rows(i+1).Insert Shift:=x1Down
i = i+1
End if
i=i+1
Loop Until i = 44025
End Sub

das wirft mir Allerdings einen Laufzeitfehler aus. Es werden bei den ersten Zeilen sogar Leerzeilen eingefügt, allerdings nicht an den richtigen Stellen. Nach der 4 Zeile wird keine mehr eingefügt. Nicht hauen für mein Code Kauderwelsch

 

[Fellor]

Sooo einfach ist es dann doch leider nicht. Und ich würde auch keine feste Anzahl für die Schleife nehmen, sondern direkt dynamisch auslesen. Die Anzahl der Zeilen ändert sich ja, wenn du eine einfügst.

Würde dir gerne helfen, hab' aber leider gerade genug zu tun :/

 

[BallerNacken]

Sooo einfach ist es dann doch leider nicht. Und ich würde auch keine feste Anzahl für die Schleife nehmen, sondern direkt dynamisch auslesen. Die Anzahl der Zeilen ändert sich ja, wenn du eine einfügst.

Würde dir gerne helfen, hab' aber leider gerade genug zu tun :/

Schade eigentlich.
Vielleicht findest sich ja noch wer anders, der Hilfestellung geben kann. In der Zwischenzeit versuche ich mal das Problem mit MATLAB zu lösen.

 

[cumulonimbus8]

    Sub Spalte_einf()
    i = 2
    While Cells(i,1)<>""
     If Cells(i, i+1) > 30 Then
      Rows(i+1).Insert Shift:=x1Down
      i=i+2
     Else
      i=i+1
     End if
    Wend
    End Sub

Das setzt in der Tat einen Datenbestend ohn Löcher (Leerzeilen) voraus - eine Macke die jeder Mensch mühelos abfängt, Programmlogik aber in den Wahnsinn treibt [nein, ich verzichte gern auf »ActiveSheet.Range("A65536").End(xlUp).Row«)
Die If/Else Konstruktion habe ich gewählt weil hier das Hochzählen um 1 oder 2 instruktiv dargestellt ist wenn man später mal auf den Code guckt.

Das wird natürlich aufwändiger wenn du das Makro noch mal über einen schon so behandelten Teil laufen lässt. Zudem darf man fragen ob nicht gleich in der eingefügten Zeile was unternommen werden könnte.

CN8

 

[simpsonsfan]

Schittebön:

Sub ins_line()
    delta = 2.08333333333333E-02
    i = 1
    Do
    If (Cells(i, 1).Value - Cells(i + 1, 1).Value > delta) Then
    Rows(i + 1).Insert Shift:=x1Down
    i = i + 1
    End If
    i = i + 1
    Loop Until i = ActiveSheet.UsedRange.Rows.Count
End Sub

Bin zwar auch nicht fluent in vba aber ich glaube das sollte funktonieren.

 

[BallerNacken]

Das von simpsonsfan funktioneirt soweit, allerdings nicht komplett. Es fügt mir Leerzeilen ein, sobald delta überschritten wird, jedoch nicht immer. Es ergeben sich in meiner Tabell 344 Leerzeilen, obwohl es 378 sein müssten. Bisher konnte ich nicht fesstellen, woran das liegt.

Die Leerzeilen benötige ich nur, um meine Daten quasi in Blöcke aufzuteilen. Sobald der Zeitunterscheid zwischen zwei Zeiten größer als 30 Minuten ist beginnt ein neuer Block.
Letztendlich kann ich auf diese Weise recht simpel die Leerzellen zählen und so feststellen wieviele Blöcke vorhanden sind.

Die ultimative Lösung wäre also ein Makro, welches mir die Anzahl der Blöcke als Laufvariable ausgibt und in der Spalte daneben die Zeilenanzahl jedes Blockes enthält. Da das aber wahrscheinlich ein nicht zu verachtender Programmieraufwand ist, habe ich danach erst gar nicht gefragt.

Es würde erstmal schon reichen, wenn das Makro von simpsonsfan bei mir einwandfrei laufen würde. Ich habe mal meine Datei angehängt, dann ist es vlt. einfacher zu verstehen, bzw zu testen.

Zum Anhängen leider zu groß, deshalb auf File-Upload: http://www.file-upload.net/download-9612408/Beispiel.rar.html

Könnten das Rundungsprobleme sein? Wenn ich mir z.B. Zeile 469 angucke, mit einem Wert von 0,021527778 liegt dieser ja sehr nahe an 0,02083333. Beachtet das Makro dann z.b. nur Zahlen mit einer bestimmten Anzahl an Nachkommastellen?

Okay, Rundungsfehler kann ich nach einigem Ausprobieren ausschließen...dan bin ich allerdings überfragt.

 

[Andy8891]

Wieso schreibst du dann nicht in Spalte D folgende Formel:
=Wenn(C1>0,0208333;"1";"0")

Dann kannst du mit der Zählenwenn Funktion die Anzahl der Blöcke zählen und wenn du nach der 1 filterst siehst du die Zeile.

Wenn du die Zeilennummern irgendwo anders haben willst, kannst du mit einer Matrixfunktion alle Zeilennummern ausgeben lassen.

 

[BallerNacken]

Wieso schreibst du dann nicht in Spalte D folgende Formel:
=Wenn(C1>0,0208333;"1";"0")

Dann kannst du mit der Zählenwenn Funktion die Anzahl der Blöcke zählen und wenn du nach der 1 filterst siehst du die Zeile.

Wenn du die Zeilennummern irgendwo anders haben willst, kannst du mit einer Matrixfunktion alle Zeilennummern ausgeben lassen.

Haha, wie einfach es doch sein kann. Auf die Idee bin ich leider nicht gekommen. So funktioniert das einwandfrei! Danke Dir!!

Ergänzung (4. Oktober 2014)

okay, jetzt brauche ich doch noch mal einen Tipp. Ich habe ja jetzt unter anderem eine Spalte in der eine 1 steht, sobald in der Zeile daneben ein Wert über 0,0208333 steht. Nun würde ich halt noch gerne von unten beginnend alle Zeitwerte zusammenzaählen, bis eine 1 kommt (diese da aber mit einrechnen). Anschließend die nächsten Zellen wieder bis zur darauffolgenden 1 zusammenrechnen usw. Folgt einer 1 direkt wieder eine 1, dann nur den Wert der zweiten 1 ausgeben.

 

[Andy8891]

Am einfachsten wärs, wenn du eine weitere Hilfsspalte erstellst die jedes mal hochzählt wenn eine 1 kommt.
Das würde ungefähr so aussehen

[table="width: 500"]
[tr]
[td]1[/td]
[td]1[/td]
[/tr]
[tr]
[td]0[/td]
[td]1[/td]
[/tr]
[tr]
[td]0[/td]
[td]1[/td]
[/tr]
[tr]
[td]1[/td]
[td]2[/td]
[/tr]
[tr]
[td]0[/td]
[td]2[/td]
[/tr]
[tr]
[td]1[/td]
[td]3[/td]
[/tr]
[/table]

Und dann kannst du die Blöcke mit Summewenn zählen.

 

[BallerNacken]

Daran habe ich auch schon gedacht. Aber da ergibt sich dann ja genau das gleiche Problem. Wenn ich die Hilfsvariable per Hand erstelle, kann ich auch gleich per Hand zählen, wenn ich das nicht iwie automatisiert mache. Es handelt sich um insgesamt knapp 400.000 Zeilen...

 

[Andy8891]

Die Hilfsvariable wird mit einer Formel erstellt, also einmal Formel eingeben und runterkopieren.
Oder verstehe ich etwas falsch?

 

[BallerNacken]

Die Spalte mit 1 und 0 habe ich, die passt auch soweit. Aber wie erstelle ich automatisiert eine weitere Hilfsspalte wie von dir vorgeschlagen, mit der anwachsenden Laufvariable die bei jeder eins um +1 steigt?!

 

[Andy8891]

Wenn du 0 und 1 als Zahlen hast, dann kannst du einfach in der Hilfsspalte z.b. E folgendes machen:

In E1 steht eine 0
In E2 dann folgende Formel:
=(E1+D2)

Wenn in Spalte D eine 1 steht, zählt es hoch, wenn nicht bleibt die Zahl gleich.

 

[BallerNacken]

Okay, das funktioniert! Danke! Jetzt muss ich nur noch ein anderes Problem lösen...hat aber eher mit den Daten an sich zu tun, was ich nur selber lösen kann.

Aber ergibt sich bestimmt nochmal ein Problem.

 

[streber89]

Hallo,

ich habe ein ähnliches Problem.
Ich habe eine Excel Tabelle mit hunderten von wertn.
Jeder Wert entspricht einem Datum und einer Uhrzeit. Also folgender Maßen:

03.08.2016 07:00 3000
03.08.2016 07:15 3001
03.08.2016 07:30 3002

Leider gibt es aber Zeilen wo es sein kann, dass das Datum fehlt oder öfters mehr als 15 minuten unterschied zwische der unteren und der oberen Zeile ist.

Ich benötige ein Makro was mir die fehlenden zeilen automatisch einfügt, also folgender Maßen:

Zuerst:

03.08.2016 07:00 3000
03.08.2016 07:15 4926
03.08.2016 07:30 4826
03.08.2016 08:15 5846


Nachher:

03.08.2016 07:00 3000
03.08.2016 07:15 4926
03.08.2016 07:30 4826
03.08.2016 07:45 5814
03.08.2016 08:00 5849
03.08.2016 08:15 5846

In meinem Beispiel müssten auch alle Werte und Uhrezeiten ab 01.08.2016 eingefügt werden.

01.08.2016 00:00
01.08.2016 00:15
01.08.2016 00:30

bis

31.08.2016 23:45

Bitte um eure Hilfe

Mit freundlichen Grüßen
Streber89