Excel, alte Werte behalten?
Moin, ich versuche mich kurz zu halten: In Excel habe ich eine Mappe Betriebskostenabrechnung (BKA), die den Wert für die vorausgezahlten Betriebskostenvorauszahlungen (BKVZ) aus der Mappe Controlling übernimmt; die Letztere übernimmt den Soll-Wert für die BKVZ aus der Mappe Einnahmen, wo händisch der Ist-Wert eingepflegt ist. Die Gutschriften werden in der Mappe Controlling händisch eingepflegt und dort der Soll-Wert mit dem gebuchten Ist-Wert subtrahiert. Wenn der Ist-Wert der BKVZ sich aber bspw. ändert, weil ich bspw. die BKA bereits im August ausführen wollen würde, würde sich auch der Wert für die früheren Monate ebenso ändern. Gibt es eine Formel, die vergangene Monate schreibschützt und den neuen Wert nur für den aktuellen Monat übernimmt, wo der Wert in der Mappe Einnahmen eingepflegt wurde?
4 Antworten
Gibt es eine Formel, die vergangene Monate schreibschützt
Nein, sowas kann keine Formel. Da musst Du Makros programmieren.
Ich sag' es ungern, weil man sich damit bei einem Teil der Excelianer, Calcianer und sonstigen Nutzern von anderen Tabellenkalkulationen extrem unbeliebt machen kann: Das Ganze klingt danach, dass das, was Ihr da macht, nichts mehr für Excel & Co. ist, sondern nach einer Datenbank schreit (ist aber jetzt nur so ein Bauchgefühl)
Moin, Excel passt schon, kann mehr als man denkt; ChatGPT machts:
Private Sub Worksheet_Change(ByVal Target As Range)
' Definiere die Zelle, die den Ist-Wert enthält (A1 in diesem Beispiel)
If Not Intersect(Target, Range("A1")) Is Nothing Then
' Stoppe Ereignisse, um Endlosschleifen zu vermeiden
Application.EnableEvents = False
' Definiere die aktuelle Spalte basierend auf dem aktuellen Monat
Dim currentMonth As Integer
currentMonth = Month(Date) ' Gibt den aktuellen Monat zurück (1 = Januar, 12 = Dezember)
' Schleife durch die Monate (Zellen B1 bis M1)
Dim i As Integer
For i = 2 To 13 ' B1 bis M1 (2 = Spalte B, 13 = Spalte M)
If i >= currentMonth + 1 Then
Cells(1, i).Value = Range("A1").Value ' Aktualisiere den Wert ab dem aktuellen Monat
End If
Next i
' Aktiviere Ereignisse wieder
Application.EnableEvents = True
End If
End Sub
Die Frage war nach einer Formel nicht nach einem Script.
Und da hier unter den helfenden viele Excel in einem beruflichen Kontext benutzen erfreut sich VBA dort keiner großen Beliebtheit. Da man darüber Schadcode einschleusen kann. Ich habe in meinem aktuellen Unternehmen 2 VBA Codes programmiert. Dazu stand mein ITler die ganze Zeit hinter mir. Und hat exakt diese beiden freigegeben.
aus der Mappe Einnahmen, wo händisch der Ist-Wert eingepflegt ist.
Die Gutschriften werden in der Mappe Controlling händisch eingepflegtExcel, alte Werte behalten?
Je nach Aufbau der Datei ist das selbstverständlich möglich ... und auch normal für Excel.
Probleme sehe ich in den "händischen" Eintragungen. Da wundert es mich, dass die Daten nicht aus vorhandenen (Controlling) automatisch übertragen/übernommen werden.
Grundsätzlich sollten in solch einer Datei die Soll/Ist-Werte monatlich aufgeführt werden sodass spätere Änderungen die vorhandenen nicht verändern sondern z.B. zunächst für kommende Monate fortgeführt werden.
Das ist der Fluch uns segen von Excel, das einzige was mir dazu einfallen würde wäre
- Blattberechnung auf Manuell stellen und im Anschluss nur noch die Berechnung der Blätter manuell anstoßen. So verändern sich die alten Daten nicht mehr
- Unter Optionen -> Formeln kannst du ganz oben rechts die Iterationsberechnung aktivieren, die maximale Anzahl auf 1 setzen.
Nun kannst du Zirkelbezüge nutzen.
Es wäre als zb. Denkbar das wenn 1 Monat abgeschlossen ist du in eine Zelle zb P1 abgerechnet schreibst.
Nun passt du die Formel in denen die Werte verarbeiten folgendermaßen an =wenn(p1<>"abgerechnet";{NORMALE BERECHNUNGSFORMEL};{Bezug auf sich selber})
ACHTUNG durch die Aktivierung der Iterationsberechnung kann die Excel Mappe evtl. sehr langsam werden. Kommt wie immer auf die Datenmengen an.
Alternative und besser Lösung:
Zu jedem Eintrag den du machst ein Datum hinzufügen, schon kannst du z.B. Mittels Summenprodukt nur jene Werte auswerten die vor einem bestimmten Datum liegen. Also einen betreffenden Wert nicht an eine Stelle schreiben sondern in eine Tabelle und jeder Eintrag bekommt eine neue Zelle.
Beispiel wie ich das meine:
Würde mich über Rückmeldung freuen.
Ich würde die Ist-Werte um einen Datumsgültigkeitsbereich erweitern (zwei Spalten: Gültig ab, Gültig bis) und dann den jeweils gültigen Ist-Wert mit dem Datum zusammen abholen.
Ich teile das Bauchgefühl 😉