Hallo liebe Excel-Freunde,
habe folgendes Problemstellung:
Habe eine Excel-Tabelle mit verschiedenen Zahlen welche entweder am 01.01.xx oder 01.07.xx erhöht werden müssen.
Bsp.: die Zahl 8 muss am 01.01.2017 um eins (auf 9) erhöht werden!!
die Zahl 5 muss am 01.07.2017 um eins (auf 6) erhöht werden!!
usw.
Wie kann ich dies bewerkstelligen, da es händisch sehr mühsam wäre?
Danke Erika
[
Bild bitte so als Datei hochladen: Klick mich!]
Hallo Erika,
Habe die Zahlen (8,5...) in Spalte B geschmissen, in Spalte D habe ich das Heutige Datum gemacht mit:
Habe in Spalte F die Tage des Monats eingegeben (immer 1)
Habe in Spalte G die Montage des Jahres eingegeben (immer 1 und 7)
Habe in Spalte H immer das Jahr angegeben das erste wurde manuell eingegeben (2017)
Die nächsten Daten habe ich mit:
Code:
=WENN(H4=H3;H4+1;H4)
geschrieben. (Meine Zahlen beginnen ab Zeile 4, wenn du sonst wo beginnst musst du die Formel umschreiben.
und zuletzt habe ich in Spalte C folgende Formel zur Berechnung geschrieben:
Code:
=WENN(D4=DATUM(H4;G4;F4);B4+1;0)
Spalte F - H kannst du ja dann ausblenden wenn man sie nicht sehen soll.
Hoffe ich hab die Aufgabe richtig verstanden
Gruß, Luffy
Hallo Luffy,
danke für die rasche Antwort.
Habe leider eine Sache vergessen.
Die Jahreszahl muss sich nach Erhöhung der Zahl auch um 2 Jahre erhöhen (siehe Beispiel) um die nächste Erhöhung zu gewährleisten.
Sorry und Danke
Erika
[
Bild bitte so als Datei hochladen: Klick mich!]
Hallo Erika,
ich denke mit VBA kann das besser klappen habe Hier mal eine vorüber gehende Lösung für dich
Habe nur die Formel Spalte C editiert, allerdings müsste man diese immer weiterschreiben. Habe sie mal für die nächsten 24 Jahre geschrieben, vielleicht bekommst du ja noch eine bessere Antwort von jemanden. (Bin auch noch nicht so lange dabei) :)
Aktuelle Formel wäre:
Code:
=WENN($D$4=DATUM(H4;G4;F4);B4+1;WENN($D$4=DATUM(H4+2;G4;F4);B4+2;WENN($D$4=DATUM(H4+4;G4;F4);B4+3;WENN($D$4=DATUM(H4+6;G4;F4);B4+4;WENN($D$4=DATUM(H4+8;G4;F4);B4+5;WENN($D$4=DATUM(H4+10;G4;F4);B4+6;WENN($D$4=DATUM(H4+12;G4;F4);B4+7;WENN($D$4=DATUM(H4+14;G4;F4);B4+8;WENN($D$4=DATUM(H4+16;G4;F4);B4+9;WENN($D$4=DATUM(H4+18;G4;F4);B4+10;WENN($D$4=DATUM(H4+20;G4;F4);B4+11;WENN($D$4=DATUM(H4+22;G4;F4);B4+12;WENN($D$4=DATUM(H4+24;G4;F4);B4+13;)))))))))))))
Gruß, Luffy
Hallo,
woran wird das festgemacht, wann die Werte erhöht werden???? Wenn es keine klare Regel gibt, dann kann man auch keine eindeutige Formel erstellen. Aus Deinem Bild ist ja keine Logik erkennbar.
Zudem wird das in einer Formel innerhalb der Zelle nicht funktioneren, das geht nur mit VBA!
(04.07.2016, 09:43)BoskoBiati schrieb: [ -> ]Hallo,
woran wird das festgemacht, wann die Werte erhöht werden???? Wenn es keine klare Regel gibt, dann kann man auch keine eindeutige Formel erstellen. Aus Deinem Bild ist ja keine Logik erkennbar.
Zudem wird das in einer Formel innerhalb der Zelle nicht funktioneren, das geht nur mit VBA!
Hallo Edgar :17: ,
als konkret geht es darum, dass sich die Zahlen im 2 Jahresrhythmus um 1 erhöhen.
Der Stichtag für die Erhöhung sind alle 2 Jahre entweder am 01.01.xx oder 01.07.xx.
Dieses möchte ich einfach automatisieren sofern möglich!!!
Danke Erika
Hallo Erika;
In deiner Anfrage steht, dass an einem bestimmten Datum der Wert erhöht werden soll, aber nichts darüber wie der Wert am Folgetag aussehen soll.
In dem Beispiel von Luffy wird der Wert nur an den festgelegten Kalendertagen erhöht einen Tag später bekommst du wieder den Startwert.
Hier mein Vorschlag mit zwei (wenn die Länge des Zyklus oder die Erhöhung des Wertes Variable ist auch vier) Hilfsspalten:
Spalte A: Zyklusstart im Beispiel: A2:="01.01.2015"
Spalte B: Startwert im Beispiel: B2:=18
Spalte C: Zyklus im Beispiel: C2:=24 (kann enfallen und in der Formel durch eine Konstante ersetzt werden)
Spalte D: Erhöhung im Beispiel: D2:=1 (kann enfallen und in der Formel durch eine Konstante ersetzt werden)
Spalte E: GStufe berechnet
und drei Lösungsvorschläge
A: Der Wert wird nur an den festgesetzten Kalendertagen erhöht:
E2:= WENN(UND(TAG(A2)=TAG(HEUTE());MONAT(A2)=MONAT(HEUTE()));B2+GANZZAHL((JAHR(HEUTE())*12+MONAT(HEUTE())-JAHR(A2)*12-MONAT(A2))/C2)*D2;B2)
B: Der Wert wird ab dem ersten Tag eines Monats erhöht:
E2:=B2+GANZZAHL((JAHR(HEUTE())*12+MONAT(HEUTE())-JAHR(A2)*12-MONAT(A2))/C2)*D2
C: Der Wert wird ab dem vorgegebenen Tag eines Monats erhöht:
E2:=B2+GANZZAHL((JAHR(HEUTE())*12+MONAT(HEUTE())-JAHR(A2)*12-MONAT(A2)-(TAG(HEUTE())#TAG(A2)))/C2)*D2
(Hierbei muss "#" durch ein kleiner ersetzt werden. Die Forumsoberfläche versteht ein kleiner wohl als Formatierungsbefehl.)
Spalte F: Der Stichtag wird nicht mehr zur Berechnung benötigt. Wenn du aber wissen willst wann die nächste Erhöhung statfindet hier die Formel.
F2=DATUM(JAHR(A2)+GANZZAHL(((I2-B2)/D2+1)*C2/12);MONAT(A2)+REST(((I2-B2)/D2+1)*C2;12);TAG(A2))
Hallo,
eine Möglichkeit:
Code:
Option Explicit
Private Sub workbook_open()
Dim loletzte As Long
Dim wks As Worksheet
Dim loa As Long
loa = 2
Set wks = Sheets("Tabelle1")
With wks
loletzte = .Cells(Rows.Count, 5).End(xlUp).Row + 1
Do Until loa = loletzte
If Date = Application.WorksheetFunction.WorkDay(.Cells(loa, 6), 1) Then
.Cells(loa, 5) = .Cells(loa, 5) + 1
.Cells(loa, 6) = DateSerial(Year(.Cells(loa, 6)) + 2, Month(.Cells(loa, 6)), 1)
End If
loa = loa + 1
Loop
End With
End Sub
Alle Formellösungen sind Makulatur, wenn die Änderungen in den vorhandenen Spalten erfolgen soll, mit
einer Hilfsspalte alls Startdatum wäre es machbar!
Hallo Edgar,
super das ging ja fix :)
Das ist machbar da ich das Startdatum für jede Zahl habe!
Die Zahlen stehen in der Spalte E3-E50 und das Startdatum in der Spalte N3-N50!!
Was muss ich jetzt beim einfügen des VBA noch beachten Zell N müsste dann 14 sein oder?
loa 2 +1 bezieht sich auf 2 Jahre und 1 Tag um Änderung durchzuführen oder?
Danke Erika
Option Explicit
Private Sub workbook_open()
Dim loletzte As Long
Dim wks As Worksheet
Dim loa As Long
loa = 2
Set wks = Sheets("Tabelle1")
With wks
loletzte = .Cells(Rows.Count, 5).End(xlUp).Row + 1
Do Until loa = loletzte
If Date = Application.WorksheetFunction.WorkDay(.Cells(loa, 14), 1) Then
.Cells(loa, 5) = .Cells(loa, 5) + 1
.Cells(loa, 14) = DateSerial(Year(.Cells(loa, 14)) + 2, Month(.Cells(loa, 14)), 1)
End If
loa = loa + 1
Loop
End With
End Sub
(04.07.2016, 11:43)Ego schrieb: [ -> ]Hallo Erika;
In deiner Anfrage steht, dass an einem bestimmten Datum der Wert erhöht werden soll, aber nichts darüber wie der Wert am Folgetag aussehen soll.
In dem Beispiel von Luffy wird der Wert nur an den festgelegten Kalendertagen erhöht einen Tag später bekommst du wieder den Startwert.
Hier mein Vorschlag mit zwei (wenn die Länge des Zyklus oder die Erhöhung des Wertes Variable ist auch vier) Hilfsspalten:
Spalte A: Zyklusstart im Beispiel: A2:="01.01.2015"
Spalte B: Startwert im Beispiel: B2:=18
Spalte C: Zyklus im Beispiel: C2:=24 (kann enfallen und in der Formel durch eine Konstante ersetzt werden)
Spalte D: Erhöhung im Beispiel: D2:=1 (kann enfallen und in der Formel durch eine Konstante ersetzt werden)
Spalte E: GStufe berechnet
und drei Lösungsvorschläge
A: Der Wert wird nur an den festgesetzten Kalendertagen erhöht:
E2:= WENN(UND(TAG(A2)=TAG(HEUTE());MONAT(A2)=MONAT(HEUTE()));B2+GANZZAHL((JAHR(HEUTE())*12+MONAT(HEUTE())-JAHR(A2)*12-MONAT(A2))/C2)*D2;B2)
B: Der Wert wird ab dem ersten Tag eines Monats erhöht:
E2:=B2+GANZZAHL((JAHR(HEUTE())*12+MONAT(HEUTE())-JAHR(A2)*12-MONAT(A2))/C2)*D2
C: Der Wert wird ab dem vorgegebenen Tag eines Monats erhöht:
E2:=B2+GANZZAHL((JAHR(HEUTE())*12+MONAT(HEUTE())-JAHR(A2)*12-MONAT(A2)-(TAG(HEUTE())#TAG(A2)))/C2)*D2
(Hierbei muss "#" durch ein kleiner ersetzt werden. Die Forumsoberfläche versteht ein kleiner wohl als Formatierungsbefehl.)
Spalte F: Der Stichtag wird nicht mehr zur Berechnung benötigt. Wenn du aber wissen willst wann die nächste Erhöhung statfindet hier die Formel.
F2=DATUM(JAHR(A2)+GANZZAHL(((I2-B2)/D2+1)*C2/12);MONAT(A2)+REST(((I2-B2)/D2+1)*C2;12);TAG(A2))
Hallo,
danke für die Info. Der Wert soll am Stichtag erhöht werden und bleibt bis zur nächsten Erhöhung (2 Jahre später) gleich.
Danke Erika